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