NULLS in DB2 tables

sibi Yohannan · New User Joined: 15 Apr 2009 Posts: 47 Location: Bangalore

Hi,

Let me explain the current issue that we are facing. Data is been extracted from the Mainframe system and is been trying to load in an Oracle database. At Oracle side the data is getting rejected saying there are NULLS in the data file. When we traced back we see NULLS present in the DB2 Tables itself. At this point there are many questions infront of us.

We checked in SYSCOLUMNS Table and see that all the Columns in a TABLE are defined as NOT NULL. I am not sure how the nulls are able to insert in the columns?.

Nulls are present in between characters, in data descriptions. We see those NULLS as HYPHENS(-) in the mainframe system, as CA when we make HEX ON.
Is there any quick way to correct those and to avoid the same getting inserted either through batch or Online in the future.

I see in the Online programs INSPECT Variable REPLACE NULLS by SPACES.
Is this an effective command to avoid nulls, or any loop holes with this.
If I consider a Batch program that inserts data into a Table, and the Table has 20 Columns, Is it a Good practice to validate each of the 20 data values for NULLS?

Please advice and let me know if you need any further information.

Thanks
Sibi
_________________
Thanks
Sibi Yohannan

Robert Sample · Posted: Tue Jul 21, 2009 9:00 pm

One thought that comes to mind -- since I don't know DB2, it'll probably be my only thought on this topic -- is that you could be loading data as DBCS (double byte character set) either directly or by selecting a code page that uses two bytes per character. If the character can fit into a single byte, many times there will be a hex '00' inserted in front of the byte to make it a double byte. The character is not null because a byte of hex '00' in a double byte character is not considered a null.

If this is the case, you may need to convert characters to a different code page before loading into Oracle.

wanderer · Posted: Wed Jul 22, 2009 12:56 am

This is a bit strange, if columns are not NULL in DB2, they won't have NULLs.

Did you find which column has NULL in unload file, then run an SQL on table with COL1 IS NULL predicate and get any output?

Craq Giegerich · Posted: Wed Jul 22, 2009 1:00 am

wanderer · Posted: Wed Jul 22, 2009 1:35 am

I never said hex '00' is null. I was referring to the original poster's question. Sorry if it caused confusion.

dick scherrer · Posted: Wed Jul 22, 2009 7:49 am

Hello,

sibi Yohannan · New User Joined: 15 Apr 2009 Posts: 47 Location: Bangalore

In mainframe system, If I look into the Table
in BROWSE mode I can see a .(dot)
in EDIT mode I can see a HYPHEN there
If I copy the same data to a Notepad I see a e (small letter e) with a ^ on the tope of e.

When we make HEX ON, we see the same as CA. These type of data when it treis to load in Oracle tables, getting rejected.

When I checked in SYSCOLUMNS Table of the IBM, I see that the column NULLS {CHAR(1)} is smentioned as N
I believe N means the column data cannot be a NULL value. Could someone give me a clear definition of what is NOT NULL for a columns means?

In a data file or in a DB2 Table if I give F P'.' (Find Option) I can identify the NULLS present in the file or in any columns. Is this the correct way to identify NULLS.

I am an application programmer, it woud be good for me if someone can advice and explain me in detail.

dick scherrer · Posted: Wed Jul 22, 2009 9:53 am

Hello,

A couple of suggestions. . .

1
In tso/ispf and issue the f p'.' command with an error of some kind (i'm not connected and can't provide the exact example). Then press F1 to invoke the help screens. Among them is the list of various values you can use other than the period in the f p'.'. If my memory is working, f p'.' finds characters that are not displayable.

2

sibi Yohannan · New User Joined: 15 Apr 2009 Posts: 47 Location: Bangalore

Thank you for your quick reply.

I am sorry to say that I am not abale to get any information on the first option, Still not sure why we use the command F P'.'

I checked the manuals on the NULLS information.
I see that as I have mentioed, the HEX char CA is not a NULL.

As an example the below data
1354320
If I copy the above data to a Mainframe file or Table it will display as
1354-320 ---> There is a HYPHEN in between
If I select a table with 1354320 as input, the selected column shows as
1354.320 -----> In BROWSE mode a dot
1354 320 -----> In EDIT mode a space in between

I believe now you have an idea on this, I think we can rule out the saying NULLS about this.

Please advice

This is the exact problem tat we are facing.

sibi Yohannan · New User Joined: 15 Apr 2009 Posts: 47 Location: Bangalore

The command F P'.' is detecting the sign }, even }/{ is availabale through keyboard.

Now I doubt about the use of this Particular command as well.

dbzTHEdinosauer · Posted: Wed Jul 22, 2009 5:02 pm

sibi Yohannan · New User Joined: 15 Apr 2009 Posts: 47 Location: Bangalore

Anyone can shed some light on this???

I just tried the coding with INSPECT to remove the e with ^ to remove from the file to be loaded in Oracle.

dick scherrer · Posted: Thu Jul 23, 2009 2:04 am

Hello,