How to write DB2 SELECT statement in unload job for delimiters

Question

I have been over this... seems silly but couldnt figure out!

I wanna UNLOAD a table but with a delimiter '|' in between the fields. Here is the JCL used to unload the table:

//JS020    EXEC PGM=IKJEFT01,                     
//             DYNAMNBR=20                        
//*                                               
//SYSTSPRT DD  SYSOUT=*                           
//SYSPRINT DD  SYSOUT=*                           
//SYSUDUMP DD  SYSOUT=*                           
//SYSPUNCH DD  SYSOUT=*                           
//*                                               
//SYSTSIN  DD  *                                  
  DSN SYSTEM(XXXX)                                
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')
//*                                               
//SYSREC00 DD  DSN=TABLEA.UNLOAD.FILE,            
//             DISP=(NEW,CATLG,DELETE),           
//             UNIT=SYSDA,LRECL=80                
//SYSIN    DD  *                                  
  SELECT  COLUMN1                                 
         ,'|',COLUMN2                                 
         ,'|',COLUMN3                                 
         ,'|',COLUMN4                                 
         ,'|',COLUMN5                                 
  FROM   TABLEA                                   
  WITH UR;                                        
/*                                                
//*                                               

Output yields

VALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEE
VALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEE
VALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEE
VALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEE
VALUEA..|VALUEB..|VALUEC..|VALUED..|VALUEE

But I wish to have like below

VALUEA|VALUEB|VALUEC|VALUED|VALUEE
VALUEA|VALUEB|VALUEC|VALUED|VALUEE
VALUEA|VALUEB|VALUEC|VALUED|VALUEE
VALUEA|VALUEB|VALUEC|VALUED|VALUEE
VALUEA|VALUEB|VALUEC|VALUED|VALUEE

I'm not able to figure our why that .. is preceeding the delimiter '|'. Any guesses what does that value mean? Thanks for your interest.

Answer 1

Try changing your SELECT as follows:

SELECT  COLUMN1                                   
       ,CHAR('|'),COLUMN2   
       ,CHAR('|'),COLUMN3  
       ,CHAR('|'),COLUMN4  
       ,CHAR('|'),COLUMN5  
FROM   TABLEA  
WITH UR;  

Placing the string constant '|' in your input creates a variable length character string on output. Variable length character strings are preceded by a 2 byte binary field giving the length of the string. In your case that would be 01 (try viewing the output with HEX ON). Since the length is a binary integer value it does not display as you were expecting.

The scalar function CHAR converts a variable length character string into a fixed length character string, which is what you were expecting.

Note: DSNTIAUL is different from SPUFI, which I suspect you are more familiar with. DSNTIAUL does not convert selected data to character, SPUFI does. So, if you used DSNTIAUL to select a column containing numeric data (eg. DECIMAL), it will be written to your output file in binary. VARCHAR data will be written with a leading 2 byte length field (as was the '|' character in your example). Columns defined as fixed length character (eg. CHAR(5)) will be written as fixed length character strings (no leading binary length field).

Answer 2

SELECT trim(COLUMN1)||
'|'||trim(COLUMN2)||
'|'||trim(COLUMN3)||
'|'||trim(COLUMN4)||
'|'||trim(COLUMN5) FROM TABLEA WITH UR;

In the select statement U'll trim the output of every column and append '|' using concatenate operator.As a result of using trim there will be no spaces in between column and '|'.For more Ref:IBM Manual On Trim