|
View previous topic :: View next topic
|
| Author |
Message |
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
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 |
|
 |
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Elixir
Active User
Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
Elixir
Active User
Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
| it worked for me |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
What worked for you? |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
OMG  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
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 |
|
|
Elixir
Active User
Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
| To insert a NULL, move -1 to the null indicator |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Good to see that the misunderstanding is clearing up 
Thank you for the update.
d |
|
| Back to top |
|
|
Elixir
Active User
Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA
|
|
|
|
| Thanks.. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
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! |
|
| Back to top |
|
|
|
|
