IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Can you suppress NULLIF char for DSNTIAUL?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
HammerTime

New User


Joined: 26 Sep 2012
Posts: 14
Location: UK

PostPosted: Tue Dec 09, 2014 10:00 pm
Reply with quote

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' icon_lol.gif ), 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:
Code:
                       
" "                                    POSITION(      1         )
CHAR(                      2)        ,                           
" "                                    POSITION(      3         )
CHAR(                      5)                                     
     NULLIF(      9)='?',                                       
" "                                    POSITION(     10:      14)
DECIMAL                     ,
" "                                    POSITION(     15         )
CHAR(                      2)                                                                                                       


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!):
Code:

" "                                    POSITION(      1         )
CHAR(                      2)        ,                           
" "                                    POSITION(      3         )
CHAR(                      5)                                                                         
" "                                    POSITION(      9:      13)
DECIMAL                     ,
" "                                    POSITION(     14         )
CHAR(                      2)                           


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.
Back to top
View user's profile Send private message
HammerTime

New User


Joined: 26 Sep 2012
Posts: 14
Location: UK

PostPosted: Tue Dec 09, 2014 10:51 pm
Reply with quote

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. icon_lol.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DSNTIAUL and DB2 scalar functions DB2 3
No new posts COnvert a column with mix of hex ,cha... DB2 5
No new posts How do i verify the char field is hav... PL/I & Assembler 1
No new posts how to convert Hex String t char hex PL/I & Assembler 6
No new posts Insert System Runtime in HEADER In SY... SYNCSORT 9
Search our Forums:

Back to Top