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.

score 9 · Accepted Answer · edited Jun 21 '14 at 16:51

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).

Thanks for your response and yes its a length written preceding the actual character. I tried to give a string and length of that string is written. Anyhow thanks for resolution! — Raja Reddy, Jun 05 '11 at 13:10

score 1 · Answer 2 · answered Sep 18 '17 at 09:25

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

score 0 · Answer 3 · answered Aug 18 '17 at 14:38

0

Use CHAR function:

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

answered Aug 18 '17 at 14:38

user8484338

1

score -1 · Answer 4 · edited Apr 25 '16 at 09:51

-1

 `**SELECT COLUMN1  || CAST('|' AS CHAR(1))
  || COLUMN2  || CAST('|' AS CHAR(1))
  || COLUMN3  || CAST('|' AS CHAR(1))
  || COLUMN4  || CAST('|' AS CHAR(1))
  || COLUMN5 
FROM TABLEA  
WITH UR;**

edited Apr 25 '16 at 09:51

Robert

5,278
43
65
115

answered Apr 25 '16 at 09:33

Christof

1

How to write DB2 SELECT statement in unload job for delimiters

4 Answers4