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: 135
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: 135
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: 1738
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: 1717
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: 645
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: 10203
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 Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm


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