View previous topic :: View next topic
|
Author |
Message |
tirumalg
New User
Joined: 14 Feb 2007 Posts: 6 Location: Bangalore, INDIA
|
|
|
|
Hi Flots,
We are unloading the SQL using program DSNTIAUL and Plan DSNTIB81. The SQL output was fine except it is giving one X'00' Char after each and every field.
Please advise me on how to avid that char. Your help is highly appricated.
The sample JCL is as below.
*********************************************
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=4096K
//STEPLIB DD DSN=DB2D.RUNLIB.LOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSABOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2D)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB81) PARMS('SQL')
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=XXXXxxxx..SYSREC(+1),
// DISP=(NEW,CATLG,DELETE),
// DCB=(GDG.MODEL),
// SPACE=(16384,(20,20),,,ROUND),
// UNIT=SYSDA
//SYSPUNCH DD DSN=XXXXXX.DB2.SYSPNCH(+1),
// DISP=(NEW,CATLG,DELETE),
// DCB=(GDG.MODEL,LRECL=80,BLKSIZE=800,RECFM=FB),
// SPACE=(16384,(20,20),,,ROUND),
// UNIT=SYSDA
//SYSIN DD *
************************************************ |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
tirumalg,
the null indicator only appears on columns that can be null, If the column is null and if you eliminate the null indicator on the unload, you need to have the column populated with something. Can you define a value for each column when it's null? |
|
Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
|
|
Quote: |
The SQL output was fine except it is giving one X'00' Char after each and every field.
|
David, doesn't that sound like a unix style null delimited file? |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
Bill,
This is standard format using the unload utility. We get this all the time when we unload tables. x'00' = not null x'6F' = null. x'6F' = '?'. Now, these only appear on columns that can have a null value. If the column was declared as 'NOT NULL' the null ind char will not appear. The only way that I know if eliminating the null indicator char is to use 'COALESCE(column,default)' when unloading. |
|
Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
|
|
Well, with the original poster's discription of "one X'00' after each and every field", I don't quite agree that represents X'6F' and null columns between all the known column....What say you? |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
tirumalg,
We seem to be having some confusion on what's happening in your unload. You've read the posts. Are the x'00' null indicators, or null terminators, as in a null terminated char string? or null delimited fields? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
My guess is that this is not the null indicator as this is happening on "each and every field". That would include the field(s) that make up the primary key - which won't have nulls. |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
Very true, However, you are not required to have a primary key, and does he really mean every column?
tirumalg, give us some help here . How are the columns being unloaded declared in DB2 land? |
|
Back to top |
|
|
tirumalg
New User
Joined: 14 Feb 2007 Posts: 6 Location: Bangalore, INDIA
|
|
|
|
David,
Sorry for the confusion between NULL and X'00'. I mean X'00' not NULL. Now this issue is resolved after I used CHAR function for CHAR fields and DIGITS function for INT, SmalINT and DEC.
But still I don't understand what difference it makes when we use functions. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Sounds like the unload was trying to "help" you by creating an x'00'-delimited file |
|
Back to top |
|
|
|