Page 1 of 1

JCL and Direct SQL commands

PostPosted: Tue Oct 30, 2018 3:06 am
by mvtc19
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 ***'                  
//*--------------------------------------------------------------------
 

Re: JCL and Direct SQL commands

PostPosted: Tue Oct 30, 2018 7:45 am
by NicC
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.

Re: JCL and Direct SQL commands

PostPosted: Tue Oct 30, 2018 3:11 pm
by willy jensen
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.

Re: JCL and Direct SQL commands

PostPosted: Tue Oct 30, 2018 10:22 pm
by mvtc19
Thank you, NicC and Willy Jensen. That's what I thought, it should be embedded within a program.