Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Can you suppress NULLIF char for DSNTIAUL?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Can you suppress NULLIF char for DSNTIAUL?
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts How to suppress retun code of partil ... mkk157 DB2 9 Fri May 27, 2016 6:59 am
No new posts conversion from char to packed decimal Musab Ahmed DFSORT/ICETOOL 7 Fri Apr 08, 2016 1:12 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us