Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to load nullify integer field for 0?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
cvijay784
Warnings : 1

New User


Joined: 18 Jun 2008
Posts: 54
Location: Colombo

PostPosted: Tue Jul 12, 2011 12:25 pm    Post subject: How to load nullify integer field for 0?
Reply with quote

Hi,

I have a db2 load statement as shown below.
-----------------------------------------------------------------------------------------------------------------------------------
LOAD DATA RESUME YES LOG YES SHRLEVEL CHANGE ENFORCE CONSTRAINTS INTO TABLE table_name

(column1 POSITION (1:6) Char,
column2 POSITION (7:10) Integer)
-----------------------------------------------------------------------------------------------------------------------------------

Input for the integer field column2 is S9(9) COMP. I need to nullify the field when value is 0. I tried below nullif statement; but it throwed invalid keyword '0'. I dont know how to compare binary signed value in nullif. Please help me.

-----------------------------------------------------------------------------------------------------------------------------------
LOAD DATA RESUME YES LOG YES SHRLEVEL CHANGE ENFORCE CONSTRAINTS INTO TABLE table_name

(column1 POSITION (1:6) Char,
column2 POSITION (7:10) Integer nullif (7:10 = 0))
-----------------------------------------------------------------------------------------------------------------------------------
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jul 12, 2011 12:52 pm    Post subject:
Reply with quote

Use something like

Code:

Column2 POSITION(7:10) integer NULLIF(6)=X'FF'


where 6 is a position in the load file having null indicators.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Tue Jul 12, 2011 1:09 pm    Post subject:
Reply with quote

would it not be:

Code:
(column1 POSITION (1:6) Char,
 column2 POSITION (8:11) Integer NULLIF (7) = X'FF')
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jul 12, 2011 1:25 pm    Post subject:
Reply with quote

It can be either way... I assumed that file would have a null indicator at position 6.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Tue Jul 12, 2011 1:35 pm    Post subject:
Reply with quote

gylbharat wrote:
It can be either way... I assumed that file would have a null indicator at position 6.

gylbharat,
i don't understand how it can be in 6 or 7.

the first 6 are the char column.

then the integer column for 4.
null ind for 1.

so would it not be 6 1 4
or 1-6 for 6 (for the char(6) column)
7-7 for 1 (for the null ind)
and 8-11 for 4? (for the int column)

by my way of thinking,
if the null ind was at 6,
then the char column would only have 5 instead of 6.

i was not disagreeing with your syntax,
only the correct column positions.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jul 12, 2011 2:15 pm    Post subject:
Reply with quote

Thanks Dbz... Yes you are correct... i was thinking in some other way...
Back to top
View user's profile Send private message
cvijay784
Warnings : 1

New User


Joined: 18 Jun 2008
Posts: 54
Location: Colombo

PostPosted: Tue Jul 12, 2011 2:17 pm    Post subject: Reply to: How to nullify integer field for value 0?
Reply with quote

Hi,

Thanks for your responses. I think you are checking only the 1st byte of the input integer (S9(9) Comp) value. I want to check the whole 4 bytes from 7 to 10 for zeroes. Please help me.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jul 12, 2011 2:18 pm    Post subject: Reply to: How to load nullify integer field for 0?
Reply with quote

I'm not sure , but doesn't this work ?
Code:
column2 POSITION (7:10) Integer NULLIF (7:10) = X'00000000')
Back to top
View user's profile Send private message
cvijay784
Warnings : 1

New User


Joined: 18 Jun 2008
Posts: 54
Location: Colombo

PostPosted: Tue Jul 12, 2011 2:20 pm    Post subject: Reply to: How to load nullify integer field for 0?
Reply with quote

Adding some more information. Input of integer field is coming from cobol output in S9(9) Comp format.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7314

PostPosted: Tue Jul 12, 2011 2:52 pm    Post subject: Re: Reply to: How to load nullify integer field for 0?
Reply with quote

cvijay784 wrote:
Adding some more information. Input of integer field is coming from cobol output in S9(9) Comp format.


Just out of interest, what are any Cobol programs doing with this field?
Back to top
View user's profile Send private message
cvijay784
Warnings : 1

New User


Joined: 18 Jun 2008
Posts: 54
Location: Colombo

PostPosted: Tue Jul 12, 2011 3:14 pm    Post subject:
Reply with quote

Cobol program gives the feed to table load step.

And i tried the following statement. It works fine. Thanks.

column2 POSITION (7:10) Integer NULLIF (7:10) = X'00000000')
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7314

PostPosted: Tue Jul 12, 2011 3:28 pm    Post subject:
Reply with quote

Sorry, I guessed that bit. What I was wondering is if the Cobol is doing any calculations with that. The reason being that a 9 digit binary is less efficient for calculation that either an 8 digit or a 10 digit. If doing none or not much calculation, don't worry about it (now). If doing truckloads upon truckloads it might be something to think about.

I just tend to avoid 9 digit binary, in the same way as 1, 2, 3, 5, and 7 digit. And unsigned numerics. And even-number of digits for packed. And defining as numeric things which will never be used for calculation even if they have the word "number" in their normal name (account number, part number, catalogue number etc ).
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Tue Jul 12, 2011 3:50 pm    Post subject:
Reply with quote

cvijay784 and gylbharat,

sorry for my post.
was not on topic and provided a non-solution to a different problem.

will attempt to follow the TS's requirement better in the future.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Incrementing current time field by 30... arunsoods DFSORT/ICETOOL 11 Thu Oct 26, 2017 3:22 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Partial color change of a field in CI... waseem0424 CICS 5 Fri Sep 29, 2017 7:56 pm
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us