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
 

 

NULLS in DB2 tables

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

New User


Joined: 15 Apr 2009
Posts: 47
Location: Bangalore

PostPosted: Tue Jul 21, 2009 8:23 pm    Post subject: NULLS in DB2 tables
Reply with quote

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
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7906
Location: Bellevue, IA

PostPosted: Tue Jul 21, 2009 9:00 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed Jul 22, 2009 12:56 am    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Jul 22, 2009 1:00 am    Post subject:
Reply with quote

wanderer wrote:
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?


Hex '00' is not a null, null in DB2 means that there is no value for that column, any char column in DB2 may contain x'00', usually it's because some programmer was careless.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed Jul 22, 2009 1:35 am    Post subject:
Reply with quote

I never said hex '00' is null. I was referring to the original poster's question. Sorry if it caused confusion. icon_sad.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 Jul 22, 2009 7:49 am    Post subject:
Reply with quote

Hello,

Quote:
we see NULLS present in the DB2 Tables itself
As there is no such thing as a "null", what is it that you see in the db2 tables (other than the CA)?

Keep in mind that every value from x'00' to x'FF' is a valid non-null value. X'CA' is not a null.

Quote:
Is it a Good practice to validate each of the 20 data values for NULLS?
No, as there is no way to do this - there is no "null value" to test for. Yes, it is a very good idea to validate as far as possible the content of every external field that is being put into a database. . . What to consider valid/invalid is completely application specific.
Back to top
View user's profile Send private message
sibi Yohannan

New User


Joined: 15 Apr 2009
Posts: 47
Location: Bangalore

PostPosted: Wed Jul 22, 2009 9:15 am    Post subject: NULLS in DB2 tables
Reply with quote

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.
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 Jul 22, 2009 9:53 am    Post subject:
Reply with quote

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
Quote:
Could someone give me a clear definition of what is NOT NULL for a columns means?
Suggest you look in the db2 sql manual for your version of db2 (several are available the "IBM Manuals" link at the top of the page. NULL / NOT NULL is presence or absence of a value. As there is no NULL value to test, using NULL in a db2 table requires use of an additional field known as the "null indicator". Complete info is in the manual and i'd recommend spending some time understanding how this works if your tables may support nulls.

If you find something that is not clear, post what you find and what is not clear. Someone should be ab le to clarify.
Back to top
View user's profile Send private message
sibi Yohannan

New User


Joined: 15 Apr 2009
Posts: 47
Location: Bangalore

PostPosted: Wed Jul 22, 2009 10:53 am    Post subject: NULLS in DB2 tables
Reply with quote

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
1354­320
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 1354­320 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.
Back to top
View user's profile Send private message
sibi Yohannan

New User


Joined: 15 Apr 2009
Posts: 47
Location: Bangalore

PostPosted: Wed Jul 22, 2009 11:44 am    Post subject: NULLS in DB2 tables
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jul 22, 2009 5:02 pm    Post subject:
Reply with quote

36_11_6.gif
Back to top
View user's profile Send private message
sibi Yohannan

New User


Joined: 15 Apr 2009
Posts: 47
Location: Bangalore

PostPosted: Wed Jul 22, 2009 9:06 pm    Post subject: NULLS in DB2 tables
Reply with quote

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.
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: Thu Jul 23, 2009 2:04 am    Post subject:
Reply with quote

Hello,

Quote:
Anyone can shed some light on this???

You are doing something wrong with the f p'.'. . . On my system, the { and the } (shown below in hex) are not "found" by a f p'.' icon_confused.gif Hopefully, they have the same hex values on your system.

Code:
{ }
C4D
000

Had you spent just a effort, you would have found
Code:

A picture string is a quoted string that is preceded or followed by the 
letter "P".  It can contain blanks, alphabetic and numeric characters   
which represent themselves, or any of the special characters listed     
below, each of which represents a class of characters.                   
    =  any character                   .  invalid characters             
    @  alphabetic characters           -  non-numeric characters         
    #  numeric characters              <  lower case alphabetics         
    $  special characters              >  upper case alphabetics         
    ¬  non-blank characters                                             
                                                                         
in the F1 help info. . .

Quote:
Now I doubt about the use of this Particular command as well.
There is no doubt about the command. . . It always works as advertised.

Quote:
As an example the below data
1354­320
If that is really the data, there will be no space, period, or hyphen magically inserted when it is "moved". The information provided is not complete and/or accurate.

Not much we can do until the real situation is known to us. And you are the only one who can provide this. . .
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 Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm
No new posts New DB2 tables creation kishpra DB2 3 Tue Feb 02, 2016 7:00 pm
No new posts TABLES/MS from Specialized Solutions ... Gary Jacek COBOL Programming 0 Thu Sep 03, 2015 12:08 am


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