|
View previous topic :: View next topic
|
| Author |
Message |
vijayverdia
New User
Joined: 23 Sep 2008
Posts: 9
Location: pune
|
|
|
|
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 |
|
 |
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
Search the input before loading?
Fix the data while loading?
Make the column nullable? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
vijayverdia
New User
Joined: 23 Sep 2008
Posts: 9
Location: pune
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
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 |
|
|
vijayverdia
New User
Joined: 23 Sep 2008
Posts: 9
Location: pune
|
|
|
|
| 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 |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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. . .  |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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  |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
D.sch.,
Hi. I Understand. What can i say "Iam just beginner in the professional world. I am Learning". Thank You!. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| "Iam just beginner in the professional world. I am Learning". |
And you are welcome here
In addition to technical replies, we try to also provide guidance in how to perform problem determination.
Enjoy the forums,
d |
|
| Back to top |
|
|
nelson.pandian
Active User
.jpg)
Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| 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 |
|
|
nelson.pandian
Active User
Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
|
|
