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 check for NULL records

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

New User


Joined: 23 Sep 2008
Posts: 9
Location: pune

PostPosted: Sun Sep 28, 2008 3:12 pm    Post subject: how to check for NULL records
Reply with quote

There is a column in a table defined as IND_SUB NOT CHAR (01) NOT NULL
There are a few records which have a null character at the IND_SUB’s byte in the input file. While the table is being loaded the job abends due to these records. How will we identify these records and make sure that the job runs successfully.
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sun Sep 28, 2008 3:41 pm    Post subject:
Reply with quote

Search the input before loading?
Fix the data while loading?
Make the column nullable?
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: Sun Sep 28, 2008 3:50 pm    Post subject:
Reply with quote

Hello,

Quote:
There are a few records which have a null character at the IND_SUB’s byte in the input file.
In a sequential file, there is no such thng as a null character. . . .

Quote:
While the table is being loaded the job abends due to these records.
How is the job being "abended"? You need to post any abend or other diagnostic info including any messages including the message id.
Back to top
View user's profile Send private message
vijayverdia

New User


Joined: 23 Sep 2008
Posts: 9
Location: pune

PostPosted: Sun Sep 28, 2008 4:06 pm    Post subject: Reply to: how to check for NULL records
Reply with quote

how it is possible that we can not have NULL in a sequential file then where the actual data in db2 stored --- hope so in LDS.
how a db2 comes to know about the null.
we have a DB2 table which contains some null values and we have commited it in a LDS. now we want to read records from that LDS but don't want to retrieve those values which has null
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: Sun Sep 28, 2008 4:36 pm    Post subject:
Reply with quote

Hello,

You need to talk with your dba so you may come to better understand how NULL works in db2. There is no null value n db2 either. Keep in mind that the "null value" means "there is NO value". In addition to the nullable column, there is also a null indicator.

Quote:
we have a DB2 table which contains some null values and we have commited it in a LDS. now we want to read records from that LDS but don't want to retrieve those values which has null
This is not consistent with your initial post. Initially you explained that the job abends while loading.

Quote:
how it is possible that we can not have NULL in a sequential file then where the actual data in db2 stored --- hope so in LDS.
It would be best to completely ignore the Linear DataSets - you should not access them programatically. Access to them is thru SQL and utilities.
in db2 there is no null data stored - the null indicator is stored.

None of this has much to do with your initial problem with the abend. For someone here to help, you need to post the requested info.
Quote:
You need to post any abend or other diagnostic info including any messages including the message id
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: Chennai

PostPosted: Sun Sep 28, 2008 4:37 pm    Post subject:
Reply with quote

Vijayverdia,

Quote:
how it is possible that we can not have NULL in a sequential file then where the actual data in db2 stored --- hope so in LDS.


What do you mean with this? In Sequential file, how can you store a NULL values? n Do you have any such scenario? LDs will be used for DB2 and store data in it and we will not use it for other normal purpose.

First of all, I feel, you have misunderstood the meaning of NULL values.

If you would have declare a column with NOT NULL then, DB2 would not allow any data with no values and hence, I feel, your job has got abent.

Quote:
we have a DB2 table which contains some null values and we have commited it in a LDS. now we want to read records from that LDS but don't want to retrieve those values which has null


Explain crearly what do you wanna do n whats the problem exaclty with the job abend code or details as Dick as proposed above.
Back to top
View user's profile Send private message
vijayverdia

New User


Joined: 23 Sep 2008
Posts: 9
Location: pune

PostPosted: Sun Sep 28, 2008 4:48 pm    Post subject:
Reply with quote

My question is that I am loading my DB2 table from an input file which contains null value but in my db2 table i have mentioned the respective colu,mn to be not null so now i want to avoid that records fromt the input file so that my job will not abend
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: Chennai

PostPosted: Sun Sep 28, 2008 4:59 pm    Post subject:
Reply with quote

Vijayverdia,

Quote:
input file which contains null value


Again, as already dick explained to you, I am telling you there is no such scenario with to have NULL values in sequential file. I hope, you need to refresh it again.

For Example:

Submission_Indicator CHAR (10) NOT NULL >> This means that, this must be hold some values, if your file has no values then, it says, column Submission_Indicator has null values but declared as NOT NULL, so it have values.

I feel, you have understand this. Post Abend code and details clearly as requested, otherwise don't ask question again.
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: Sun Sep 28, 2008 5:06 pm    Post subject:
Reply with quote

Hello,

Quote:
My question is that I am loading my DB2 table from an input file which contains null value but in my db2 table i have mentioned the respective colu,mn to be not null
Excellent. Your table does not support NULLS and the sequential cannot have NULLS, so you need to refocus on what is the real problem. . .

To do this, you need to post the info that has been requested multiple times. For you to post anything else at this time is just a waste of everyone's time.

If it a guarantee that your problem could have been fixed some time ago, but we cannot help if you will not provide the info. . . icon_neutral.gif
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Sep 29, 2008 11:58 am    Post subject:
Reply with quote

Hi vijayverdia,

Let me get this straight. You are loading a DB2 TABLE using INPUT DATASET which contains null values. i.e., the dataset which you are using to load the table, comes from another table which had/has null values. So you are saying this DATASET is having NULL VALUES. And the table you are loading now, this particular column in that table(IND_SUB NOT CHAR (01)) doesn't support NULL values.

So you want to load the table using the input dataset which has values(i.e., which is NOT NULL) for this particular column IND_SUB NOT CHAR (01)

If this is the case try this in the LOAD PARM,

Code:
INTO TABLE DBNAME.TABLENAME
WHEN IND_SUB IS NOT NULL
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 Sep 29, 2008 8:12 pm    Post subject:
Reply with quote

Hello,

Quote:
Let me get this straight.
The only thing that will help get this straight is for vijayverdia to post the info that has been requested.

Even if you guess a way for the load to process, you have not done vijayverdia a favor. People who want to use the forum need to learn to follow the suggestions given and post back results.

A shotgun approach of anyone who thinks of some possible approach does not help the person with the problem learn - it may get around the current obstacle, but they will not have learned anything about how they should approach a problem when new situatons arise.

IMHO, one of the best things the forum can provide is helping people learn how to diagnose their problems rather than just throwing out some syntax.

If one is working on a hobby web-site and goes to some forum to get "just anything that will work", that may be just fine. Working on a corporate system should be much more professional and technicians need to learn how/why things happen on their systems. FWIW icon_smile.gif
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 30, 2008 9:43 am    Post subject:
Reply with quote

D.sch.,

Hi. I Understand. What can i say "Iam just beginner in the professional world. I am Learning". Thank You!.
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: Tue Sep 30, 2008 10:55 am    Post subject: Reply to: how to check for NULL records
Reply with quote

Hello,

Quote:
"Iam just beginner in the professional world. I am Learning".
And you are welcome here icon_smile.gif

In addition to technical replies, we try to also provide guidance in how to perform problem determination.

Enjoy the forums,

d
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ

PostPosted: Wed Oct 01, 2008 12:59 pm    Post subject:
Reply with quote

hI sushanth,
There is way to fix the issue. U can fix it with a ice tool step.
U Can eliminate the records with Null values and u can go ahead with the loading step.
The contrl card tht will do the trick is:

//CTL1CNTL DD * *** CONSTANT CONTROL CARDS ***
OPTION COPY
OUTFIL FNAMES=O1,OMIT=(Starting pos,length,BI,EQ,X'00')
/* END OF INPUT

Accordin to the collacting seq on our system installation NULL's equivalent hex code is X'00'. U can check with yr installation by making a NOT NULL field to NULL and then extract to DSN and chekc the HEX value by usuing the command HEX ON.

If the value is X'00', then u can use the above mentioned JCL to exclude the NULL records and go ahead with the loading step, therby avoiding tha abort.

Let me know if you require clarification.

Regards,
Sam.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Oct 01, 2008 1:17 pm    Post subject: Reply to: how to check for NULL records
Reply with quote

Quote:
ccordin to the collacting seq on our system installation NULL's equivalent hex code is X'00'.



that' not according to any collating sequence...
00 is a perfectly valid bit configuration....

NULLness is a convention wher some bit configuration is recognized as ...
( choose any term You like )

it' s Your shop that interprets a 00 byte as a NULL something
not a collating/charset dogma

I had a customer where the absence of valid data ( NULL in Your terminology)
was indicated by a "FF" -1

NULL being represented by a binary 0 is a C language convention when doing pointer processing
( an address of 0 is universally understood aa NULL or invalid )

still the whole thread is based on a complete lack of understanding/agreement of/on terminology and conventions

a dataset record cannot have NULL fields in se

a db2 column can have a NULL indicator
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ

PostPosted: Wed Oct 01, 2008 2:27 pm    Post subject:
Reply with quote

What you said is correct.

My suggestion to eliminate the NULL records:
Each installation may have different values for NULL. On identifying the value of NULL for that particular installation we can OMIT the records based on that value. The identified NULL value may not be same acrose the various installation.
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 Oct 01, 2008 7:47 pm    Post subject:
Reply with quote

Hello,

Quote:
The identified NULL value may not be same acrose the various installation.
There can be no such thing as "the identified null value". A NULL by definition is no bit pattern (x'00' is a bit pattern). NULL is nothingness, having no value.

The best we can do is allocate some other place in storage and let it be the indicator that some field is NULL.

NULLs do not exist in sequential files. . .

It does not matter if your manager or your client wants to call certain values of things nulls - they are mistaken. You will still need to meet the business requirement, but keep in mind you need to understand the correct terminology so there are not other problems later.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Oct 03, 2008 11:39 am    Post subject:
Reply with quote

Thanks for the information pandiyan,
Quote:
U can fix it with a ice tool step.
U Can eliminate the records with Null values and u can go ahead with the loading step.
The contrl card tht will do the trick is:

//CTL1CNTL DD * *** CONSTANT CONTROL CARDS ***
OPTION COPY
OUTFIL FNAMES=O1,OMIT=(Starting pos,length,BI,EQ,X'00')
/* END OF INPUT


And Enrico Thank You too,
Quote:
it' s Your shop that interprets a 00 byte as a NULL.... I had a customer where the absence of valid data was indicated by a "FF" -1...
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 6 Fri Nov 18, 2016 1:48 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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