Executing SQL query from JCL to handel SQL 100



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

Executing SQL query from JCL to handel SQL 100

Postby Surabhi » Thu Jan 12, 2017 4:44 pm

Hello,

I am executing a SQL query through JCL.

    //STEP010  EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
    //STEPLIB   DD  DSN=DB2.DSNLOAD,DISP=SHR            
    //SYSTSPRT DD SYSOUT=*                              
    //SYSTSIN  DD *                                    
     DSN SYSTEM(*)                                  
     RUN  PROGRAM(*) PLAN(*) PARM('SQL') -
          LIB('*')                  
    //SYSPRINT DD SYSOUT=*                              
    //SYSUDUMP DD SYSOUT=*                              
    //SYSREC00 DD DSN=TEST.PGM.FILE1,  
    //            DCB=(RECFM=FB,LRECL=80),              
    //            UNIT=SYSDA,DISP=(NEW,CATLG,DELETE)    
    //SYSPUNCH DD DSN=TEST.RESULTS.FILE,
    //            UNIT=SYSDA,SPACE=(8000,(1500,1500)),  
    //            DISP=(NEW,DELETE)                    
    //SYSIN    DD *                                    
      SELECT DISTINCT                                  
             SUBSTR(EMP,1,10),                        
      FROM  EBET_PND_BILL_NTR                          
      WHERE ENV_IT  = '003'                        
         ;                                              
    /*                                                  
   


CODE' d
Even if row is not found for the condition, i.e. SQL 100 i want the row to get written in SYSREC00 with a note NO DATA.
Is this possible within JCL itself?
Surabhi
 
Posts: 49
Joined: Thu Sep 10, 2015 7:51 pm
Has thanked: 4 times
Been thanked: 0 time

Re: Executing SQL query from JCL to handle SQL 100

Postby prino » Thu Jan 12, 2017 7:17 pm

Four word answer: No!
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 635
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: Executing SQL query from JCL to handel SQL 100

Postby NicC » Sat Jan 14, 2017 8:33 pm

No! JCL ony tells the computer what programs to run and the resources required. Can SQL do what you want? I am not sure - have you tried the COALESCE (??) keyword?

The alterantive is to check the return code of the step excecuting the SQL code and if not zero run another step that would write the value to your data set.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times


Return to JCL

 


  • Related topics
    Replies
    Views
    Last post