Hi folks, got a problem which has had me puzzled for quite some time now, and i'm sure there must be a simple way around this.
I have put together a complex DB2 unload using DSNTIAUL (multiple joins required) which includes a CASE statement. What I have found is that when the CASE statement has retrieved something from 'the ELSE bit' (as opposed to 'the THEN bit' ), the output file contains a dummy warning character to tell me this has happened.
The problem this gives me is that my unload file format effectively changes if the CASE statement needs to get the data from X rather than Y. And unfortunately whether it does this or not could change from run to run, meaning I can't just feed this straight into a program due to the inconsistent nature of the file length.
An example of the unload JCL:
Code:
//UNLOAD1 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=SYS1.DSNDSNx.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSNx)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL') -
LIB('SYS1.DSNDSNx.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*
//SYSREC00 DD DSN=FILEOUT.DATA,
// SPACE=(CYL,(100,100),RLSE),
// DISP=(NEW,CATLG,DELETE),
// UNIT=(DISK,59)
//SYSIN DD *
SELECT CHAR(A.COL1),
CASE WHEN A.COL2 = ' ' AND A.COL3 <> 'the way the wind blows'
THEN CHAR(SUBSTR(B.COLY,1,3))
ELSE CHAR(SUBSTR(B.COLZ,1,3))
END,
COALESCE(B.COL2,DECIMAL(-1,5,0)),
CHAR(COALESCE(B.COL3,' ')),
FROM TABLE1 A
LEFT OUTER JOIN
TABLE2 B
ON A.COL1 = B.COL1
FOR FETCH ONLY
WITH UR;
My SYSPUNCH can then contain something like this in the event the initial CASE criteria was not met and the result had to be pulled from the ELSE location:
If I run it when the wind is blowing in another direction (when the criteria of the CASE statement is always met as true), the same unload may have a SYSPUNCH looking like this (note - no NULLIF warning indicator!):
Is it possible to suppress this 'NULLIF' indicator? Or alternatively just force it so it always exists?
If there isn't a simple way of doing this, i'll probably end up having to write a REXX or SORT statement to inspect the SYSPUNCH for this NULLIF warning, and choose whether or not I need to reformat it on the fly. Only issue I have with this approach is that the unload is retrieving a large volume of data (3000+ cylinders to be more precise!) and ideally i'm trying to make this as efficient as possible.
Our resident techie was able to get me sorted. The missing ingredient was a COALESCE outside of the CASE statement!
So now i'm using:
Code:
//SYSIN DD *
SELECT CHAR(A.COL1),
CHAR(COALESCE(
(CASE WHEN A.COL2 = ' ' AND A.COL3 <> 'the way the wind blows'
THEN CHAR(SUBSTR(B.COLY,1,3))
ELSE CHAR(SUBSTR(B.COLZ,1,3))
END)
,CHAR(SUBSTR(B.COLZ,1,3)) )),
COALESCE(B.COL2,DECIMAL(-1,5,0)),
CHAR(COALESCE(B.COL3,' ')),
FROM TABLE1 A
LEFT OUTER JOIN
TABLE2 B
ON A.COL1 = B.COL1
FOR FETCH ONLY
WITH UR;
I have a feeling i'm going to have a lot of handover documentation to complete for our maintenance guys.
), the output file contains a dummy warning character to tell me this has happened.
