JCL and Direct SQL commands



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

JCL and Direct SQL commands

Postby mvtc19 » Tue Oct 30, 2018 3:06 am

Hi, can you check SQL return code in JCL COND statements? I have step JOBX001 that does table updates and the following step JOBX002, to check for return code.
My goal is to check for SQL return code, so that I can execute a ROLLBACK or COMMIT depending on SQL return code, but I'm not getting the result I want. Somebody here knows how to do it?

//JOBX001  EXEC DB2TTSO                                                
//*                                                                    
//DB2TTSO2.SYSTSPRT DD SYSOUT=*                                        
//DB2TTSO2.SYSPRINT DD SYSOUT=*                                        
//*                                                                    
//DB2TTSO2.SYSIN DD DATA,DLM=$$                                        
UPDATE PROD1.CUSTOMER_VWLOG                                            
  SET CUSTNBR = '0012354'                                              
 WHERE SQE_NBR = 11;                                                    
                                                                       
$$                                                                      
//*                                                                    
//*--------------------------------------------------------------------
//JOBX002  EXEC PGM=PNOTIFY,                                            
//             COND=(0,EQ,JOBX001.DB2TTSO2),                            
//             PARM='1500 *** TABLE UPDATE FAILED ***'                  
//*--------------------------------------------------------------------
 
mvtc19
 
Posts: 3
Joined: Tue Oct 30, 2018 2:46 am
Has thanked: 2 times
Been thanked: 0 time

Re: JCL and Direct SQL commands

Postby NicC » Tue Oct 30, 2018 7:45 am

Somebody here knows how to do it?

The same way everyone else does it - write a program.
JCL checks program return codes not query return codes. They are checked by the program executing the query.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic

These users thanked the author NicC for the post:
mvtc19 (Thu Nov 01, 2018 12:21 am)
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

Re: JCL and Direct SQL commands

Postby willy jensen » Tue Oct 30, 2018 3:11 pm

It depends, does the program executing the UPDATE command set a return code? In which case it should be set as the step rc too. Another method is to use i.e. SORT to scan the output dataset for a ok/nok message and set the step rc accordingly. But then we are of course talking about a program.

These users thanked the author willy jensen for the post:
mvtc19 (Thu Nov 01, 2018 12:22 am)
willy jensen
 
Posts: 455
Joined: Thu Mar 10, 2016 5:03 pm
Has thanked: 0 time
Been thanked: 69 times

Re: JCL and Direct SQL commands

Postby mvtc19 » Tue Oct 30, 2018 10:22 pm

Thank you, NicC and Willy Jensen. That's what I thought, it should be embedded within a program.
mvtc19
 
Posts: 3
Joined: Tue Oct 30, 2018 2:46 am
Has thanked: 2 times
Been thanked: 0 time


Return to JCL

 


  • Related topics
    Replies
    Views
    Last post