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 insert null values in DB2

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

Active User


Joined: 14 Sep 2009
Posts: 147
Location: Coimbatore

PostPosted: Mon Dec 19, 2011 10:23 pm    Post subject: how to insert null values in DB2
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: 147
Location: Coimbatore

PostPosted: Mon Dec 19, 2011 10:50 pm    Post subject:
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: 1771
Location: Bloomington, IL

PostPosted: Mon Dec 19, 2011 10:58 pm    Post subject:
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

Site Director


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

PostPosted: Mon Dec 19, 2011 11:18 pm    Post subject:
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    Post subject:
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: 1825
Location: UK

PostPosted: Wed Dec 21, 2011 2:16 am    Post subject:
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    Post subject:
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: 670
Location: Pennsylvania

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

Quote:
it worked for me


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

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Wed Dec 21, 2011 2:49 am    Post subject: Reply to: how to insert null values in DB2
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

Site Director


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

PostPosted: Wed Dec 21, 2011 10:40 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Mon Apr 02, 2012 10:16 pm    Post subject: Reply to: how to insert null values in DB2
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    Post subject:
Reply with quote

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

Senior Member


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

PostPosted: Tue Apr 03, 2012 6:58 pm    Post subject:
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    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 BI,TO=ZD Changing Values Tyler Dunbar DFSORT/ICETOOL 1 Tue Jul 25, 2017 12:12 am
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm


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