IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Eliminating NULL Chars in the output of DB2 SQL - UNLOAD SQL


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
tirumalg

New User


Joined: 14 Feb 2007
Posts: 6
Location: Bangalore, INDIA

PostPosted: Wed Feb 14, 2007 5:36 pm
Reply with quote

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

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Feb 14, 2007 11:23 pm
Reply with quote

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
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Thu Feb 15, 2007 12:01 am
Reply with quote

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

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Feb 15, 2007 1:32 am
Reply with quote

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
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Thu Feb 15, 2007 1:40 am
Reply with quote

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

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Feb 15, 2007 3:16 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Feb 15, 2007 4:46 am
Reply with quote

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

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Feb 15, 2007 4:53 am
Reply with quote

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 icon_biggrin.gif. How are the columns being unloaded declared in DB2 land?
Back to top
View user's profile Send private message
tirumalg

New User


Joined: 14 Feb 2007
Posts: 6
Location: Bangalore, INDIA

PostPosted: Fri Feb 16, 2007 3:12 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Feb 16, 2007 10:52 pm
Reply with quote

Hello,

Sounds like the unload was trying to "help" you by creating an x'00'-delimited file icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
Search our Forums:

Back to Top