Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 6966
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: 6966
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: 1278
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: 7223

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: 7223

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: 6966
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
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us