DB2 files with NULL data moving to CSV

[Moon Pie__]

Hi folks,

I joined this forum to find out how folks work with NULL data in DB2?

I have many years of old files (FILE013000= FILE created January 30, 2000) that I am purging off the iSeries. They have nulls in the data. Nulls look like +++++ in the fields when displayed in interactive SQL.
These NULLS don't like to be worked on in interactive SQL either.

When I use a 3rd party vendor to convert to CSV, it complains too.

NULLs are driving me crazy.

One thing I found was if I copy the file from FILE013000 in MYLIB to FILE013000 to YOURLIB with *CVTFLOAT, the nulls will disappear.

The 3rd party vendor will convert FILE013000 in YOURLIB to CSV at that point nicely.

I have years upon years of files to convert with loads of NULLS and I was wondering if anyone else had this issue?

Thanks!

[DeadManWalks]

Re: DB2 files with NULL data moving to CSV

I work with nulls in sql all the time. Value(myField,' ') (or 0 if decimal). Also testing....When case Myfield is Null then .... end.

[Moon Pie__]

Re: DB2 files with NULL data moving to CSV

I work with nulls in sql all the time. Value(myField,' ') (or 0 if decimal). Also testing....When case Myfield is Null then .... end.

Hi! Thanks for your response.

I guess I wasn't clear...that is my fault.

In working with nulls in this project, I am not really using a known situation about nulls.

In my entire database of physical files residing on the iSeries...all 2,000 of them....some have nulls in fields some do not.

I want to convert all 2,000 files to CSV. So I am running my conversion, the conversion processes 50 files to CSV...the 51st file has 3 fields that contain all nulls for all records in the file. It won't convert.

So for that file, the 51st one, I should clean up all nulls, then convert.

I was just wondering if anyone ever had to work with an entire database of unknown nulls and how people using DB2 on the iSeries would work this out?

[GLS400]

Re: DB2 files with NULL data moving to CSV

Hi Moon pie:

I'm not convinced you are looking at nulls. I think you are looking at a numeric field that has spaces.

I'm not sure of your level of experiance so please do not be offended.

1. dspffd of one of your files that has nulls. Locate the starting position of the field (ending positions are displayed there)
2. dsppfm of that file.
3. in the control field key in "W" and the start position from step 1.
4. f10
5. you should now see on row 1 the character representation of your field..........row 2&3 the hex representation of your field....
6. if it is null rows 2&3 will be 00....if it is 40 .....you have bad data....spaces where numeric data should be.

h fixnbr(*zoned : *inputpacked)
fmyfile   upe  e             disk 
c                 update    myformat

I have used the above on my system to clean up the muck in externally defined files.

Best of Luck
GLS

[/code]

[tomliotta]

Re: DB2 files with NULL data moving to CSV

If *CVTFLOAT works, it'd probably be helpful if we were provided with the database definitions for some of the problem fields. The definitions would guide subsequent questions and might even indicate an actual solution. The existence of null characters (x'00') in a field wouldn't normally be a problem. But fields that are intended to be handled by *CVTFLOAT are somewhat in a class by themselves. Null characters are different from null field values, and knowing what we're dealing with is important.

[B.Hauser]

Re: DB2 files with NULL data moving to CSV

If you see +++++++++ that does not mean NULL values, but invalid values within numeric fields.
Null value are returned / displayed with - .
A NULL value is outside the valid range, but it represented by an additional flag that is set to 0 or 1 depending on whether it is a NULL value or not. These flags must be checked sparately (IS NULL).
You first need to find the invalid values and convert them into valid numeric values (or a real NULL value).

Birgitta

[Moon Pie__]

Re: DB2 files with NULL data moving to CSV

Thank you everyone for your feedback! It has helped a lot!

Using GLS400's recommendation, I discovered the data set display 40's ....where I thought were nulls.

The files are generated from RPGII and III programs still running that were created before my time, and I have been working in the iSeries for 18 years.

This has been a big help. I thought all along the values in numerics had to be nulls...but I guess I was wrong.

The DDS screen shot is attached. Bad fields are RES, PERIL, ZONE, EXCPT, FORM, FAMILY, STATUS...loads others.
I have been working with Go Anywhere...and told them there were nulls in the file, so now maybe we can do a good CSV convert knowing it is actually spaces.

..A*. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ..
************ Beginning of data *****************************
A** PHYSICAL PREMIUMS RECEIVED CURRENT MONTH
A**
A UNIQUE
A R RPABSTRE
A TRANS 2S 0
A POLICY 6S 0
A PINDEX 5S 0
A LOC 4S 0
A SA 2S 0
A AGCY 3S 0
A STATE 2S 0
A AMTINS 6S 0
A RES 7S 2
A LED 7S 2
A COMM 3S 3
A TERM 2S 0
A EFFMO 2S 0
A EFFDA 2S 0
A EFFYR 2S 0
A EXPMO 2S 0
A EXPDA 2S 0
A EXPYR 2S 0
A PERIL 2S 0
A ZONE 3S 0
A EXCPT 1S 0
A FORM 1S 0
A FAMILY 1S 0
A STATUS 1S 0
A CONST 1S 0
A PROT 2S 0
A THEFT 1S 0
A DEDT 1S 0
A DEDS 2S 0
A YRCNST 2S 0
A SUBLNE 2S 0
A BLDCNT 1S 0
A COVE 1S 0
A INST 1S 0
A ICPMO 2S 0
A ICPYR 2S 0
A KIND 1S 0
A DISCNT 1S 0
A ALTFRM 1S 0
A SKIP 1S 0
A CMSP 5S 0
A SKIP2 1
A PREFRD 1
A ZIP 9S 0
A* SKIP3 WAS 5 CHANGE FOR ICPTMO, ICPTYR
A SKIP3 1
A ICPTMO 2S 0
A ICPTYR 2S 0
A LIABCD 1
A SKIP4 1
A PID 1S 0
A PDATE 6
A AC 2
A DUMMY 2
A ABSTMO 2
A ABSTYR 2
A DELETE 1
A K POLICY
A K PINDEX

[Michael Jones]

Re: DB2 files with NULL data moving to CSV

FWIW...use of the SQL HEX function is very useful when trying to locate (SELECT query) and/or correct (UPDATE statement) bad data in numeric fields. SQL will let you query a numeric column containing invalid numeric bytes, if you wrap the column inside the HEX function. However, you still have to be able to understand what hex values are valid or invalid for the type of numeric column involved.