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
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Eliminating NULL Chars in the output of DB2 SQL - UNLOAD SQL
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    Post subject: Re: Eliminating NULL Chars in the output of DB2 SQL - UNLOAD
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: 3158
Location: Tucson AZ

PostPosted: Thu Feb 15, 2007 12:01 am    Post subject:
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    Post subject: Re: Eliminating NULL Chars in the output of DB2 SQL - UNLOAD
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: 3158
Location: Tucson AZ

PostPosted: Thu Feb 15, 2007 1:40 am    Post subject:
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    Post subject: Re: Eliminating NULL Chars in the output of DB2 SQL - UNLOAD
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

Site Director


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

PostPosted: Thu Feb 15, 2007 4:46 am    Post subject:
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    Post subject: Re: Eliminating NULL Chars in the output of DB2 SQL - UNLOAD
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    Post subject: Re: Eliminating NULL Chars in the output of DB2 SQL - UNLOAD
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

Site Director


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

PostPosted: Fri Feb 16, 2007 10:52 pm    Post subject:
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    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 Incorrect output when trying to add n... monica1 PL/I & Assembler 10 Fri Jan 13, 2017 5:02 pm
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm


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