IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

how to insert null values in DB2


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Mon Dec 19, 2011 10:23 pm
Reply with quote

Hi,

I am loading a db2 table from a file. From the file I need to load a date field. The DATE field is defined as INTEGER WITH NULL. If the file has valid values its getting loaded into the date field correctly, but If it has zeros or spaces its neglecting those records from the file from updating the DB2 table.

I tried just like giving NULL from the file to load it but I was not not successful.

Could anybody please suggest me how could I accomplish it.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Mon Dec 19, 2011 10:50 pm
Reply with quote

I was able to see this comment from global moderator in the forum,

Hello,

If there is still time, i would suggest that you not use NULL. . . It may well be that others in your organization are not familiar with NULL also.

There is no actual "null" value - NULL is the absence of any value. When using NULL, a column needs 2 host variable entries - one for the actual value of the data and the other to indicate whether there is a value or not. This is known as the NULL indicator.

Suggest you talk with your dba for some samples of other tables/columns that are defined as NULLable and the code used to select/insert/update them if NULL must be used.



But I would need to load the Db2 table from file with NULL values when there are no values for DATE field in the file ??. I don know if changing the DB2 declaration to CHARACTER X(10) would resolve this ??.

Please assist me.....
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL

PostPosted: Mon Dec 19, 2011 10:58 pm
Reply with quote

Create load control cards similar to:
Code:
BOGUS_DATE POSITION(31:34) INTEGER
    NULLIF NULL_INDICATOR = X'6F'
        NULL_INDICATOR POSITION(35) CHAR(1)
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Mon Dec 19, 2011 11:18 pm
Reply with quote

Hello,

Quote:
But I would need to load the Db2 table from file with NULL values when there are no values for DATE field in the file ??.
As there is no such thing as a "null value" in db2, you cannot load the table with "null values".

You would set the null indicator.

Suggest you (and others in your organization) learn more about the use of the hull indicator BEFORE implementing and possibly getting some ugly surprises.
Back to top
View user's profile Send private message
Elixir

Active User


Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA

PostPosted: Wed Dec 21, 2011 1:46 am
Reply with quote

Null values by X'00'
and ' ' (two continuous single quotes) by Db2 spufi provided the declare table does not have "NOT NULL"
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK

PostPosted: Wed Dec 21, 2011 2:16 am
Reply with quote

x'00' is NOT a null value in DB2 - it is the value x'00' which is a real value not null.
Back to top
View user's profile Send private message
Elixir

Active User


Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA

PostPosted: Wed Dec 21, 2011 2:32 am
Reply with quote

it worked for me
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Wed Dec 21, 2011 2:47 am
Reply with quote

Quote:
it worked for me


What worked for you?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Wed Dec 21, 2011 2:49 am
Reply with quote

Quote:
it worked for me

OMG 36_2_18.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Dec 21, 2011 10:40 pm
Reply with quote

Hello,

Quote:
it worked for me
What utter nonsense. . . Just because it ran does not mean it did anything at all with "null". Why do you believe anything "worked"?

All this kind of post does is to show that the person/organizatoin is without a clue. Calling a frog a bat does not make it one even thought both might be seen to eat bugs. . .

If your organization is willing to accept x'00' as a "null value", the entire place should be quarantined. It is no wonder that there are so many awful implementations if something so basic is not only misunderstood, but insisted upon.
Back to top
View user's profile Send private message
Elixir

Active User


Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA

PostPosted: Mon Apr 02, 2012 9:56 pm
Reply with quote

To insert a NULL, move -1 to the null indicator
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Mon Apr 02, 2012 10:16 pm
Reply with quote

Good to see that the misunderstanding is clearing up icon_smile.gif

Thank you for the update.

d
Back to top
View user's profile Send private message
Elixir

Active User


Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA

PostPosted: Mon Apr 02, 2012 10:24 pm
Reply with quote

Thanks..
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India

PostPosted: Tue Apr 03, 2012 6:58 pm
Reply with quote

As we're at this thread and it had enough facets about NULL, I’m intrigued to say - "Null" is a concept, using the term "null value" is not correct and it is never equivalent to low values, which is nice mis-conception. Only database fields can be null and for a database field to be null there actually is a separate field associated to the database field. This separate field is a binary flag. True - means the field has no value, no matter what the bits look like - low-values, my name, your name, whatever, false meaning the field has a value, again, no matter what the bits look like.

And the concept of null does not exist for a file.

It's just Tuesday, amen! icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts how to eliminate null indicator value... DB2 7
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts Insert header record with record coun... DFSORT/ICETOOL 14
Search our Forums:

Back to Top