Page 1 of 2

Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Wed Sep 23, 2015 7:29 pm
by DonkeyKong
I am experimenting with heavy use of symbolics in my JCL. I recently came across a serious error with a job that caused me to lose a significant amount of data in one of the test environment databases.
I was using symbolics to generate a large portion of the SQL I was running but something didn't go right and the SQL didn't execute correctly. There is no record of the SQL with the symbolics resolved to the values they were at when it executed. I want to print the SQL that is getting executed from the sysin, with symbolics resolved, to SDSF. Is there any way I can do this?

Here is an example of what I want printed:

//SYSIN    DD  *,SYMBOLS=JCLONLY                                       
 LOCK TABLE &ENV..&SQLDB IN SHARE MODE;                                 
 SELECT DIGITS(COUNT(*))                                               
 FROM &ENV..&SQLDB                                                     
 &WHERE1.                                                               
 &WHERE2.                                                               
 FOR FETCH ONLY WITH UR;                                               
/*                                                 

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Wed Sep 23, 2015 7:46 pm
by enrico-sorichetti
why not use a simple IEBGENER to see how Your data gets mangled

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Wed Sep 23, 2015 7:49 pm
by DonkeyKong
That is a great idea!

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Wed Sep 23, 2015 7:54 pm
by DonkeyKong
but to keep this open, I would preferably like to have the SQL output from the same step. How can I be sure that I really have the right data if it's not straight from the execution step?

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Wed Sep 23, 2015 8:02 pm
by enrico-sorichetti
until You have understood what is going on
just use and additional
IEBGENER step to write the query to a dataset
and have the SQL step read from there

the dataset will be there to analyse what is going on

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Wed Sep 23, 2015 9:17 pm
by DonkeyKong
Ahhh. That is also an excellent idea. Thanks.

I used IEBGENER to resolve the same statement to the SYSOUT and was able to identify the issue. I used multiple SET statements, in a single JCL, to set the same symbolic to different values. When the JCL was 'compiled' it used only the last value assigned to the symbolic. That is why my SQL was not working correctly; the symbolics were not setting correctly.

Thanks Enrico!

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Wed Sep 23, 2015 10:14 pm
by BillyBoyo
Note, the symbolics were correct, they just weren't how you want. If they weren't correct (as documented) raise a PMR with IBM.

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Thu Sep 24, 2015 5:59 pm
by DonkeyKong
Hi Billy,

As far as I know the SET statement worked as it is designed to. So you can see what I am doing, here's a little code. The problem occurs when I try to SET the same symbolic multiple times. When the JCL is 'compiled' it assigns the symbolic the value in the final SET statement and disregards all others.

// EXPORT SYMLIST=(DB,ENV,SQLDB,WHERE1,WHERE2)                         
// SET DDATE='150915'                                                   
// SET ENV='UNIT'                                                       
//**********************************************************************
//S001     EXEC X4UCC15,PARM=P                                         
//**********************************************************************
// SET DB='MXQTD'                                                       
// SET SQLDB='MXQ_TD'                                                   
// SET FILETAG='BEFORE'                                                 
// SET WHERE1=''                                                       
// SET WHERE2=''                                                       
//**********************************************************************
//S002     EXEC GENDB2CT,                                               
//         FILETAG=&FILETAG.                                           
//**********************************************************************
//S003     EXEC GENDB2UN,                                               
//         FILETAG=&FILETAG.                                           
//**********************************************************************
// SET FILETAG=D&DDATE.                                                 
// SET WHERE1='WHERE MXQ_TD_LST_USR_ID <> ''XXXJJJ'''                 
// SET WHERE2='AND DATE(MXQ_TD_LST_UPD_DTS) < ''2015-08-01'''           
//**********************************************************************


the '_' character and the single quote character couldn't be passed to the proc to be SET again there. So I had to do all of the SET statements with special characters in the JCL and EXPORT them from there.

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Thu Sep 24, 2015 7:57 pm
by BillyBoyo
the symbolics were not setting correctly


I was referring to that bit, which due to the passive tone makes it look like the symbolics were not working. They were.

Re: Print in-stream JCL to SDSF with symbolics resolved

PostPosted: Thu Sep 24, 2015 8:03 pm
by DonkeyKong
That would have been cool if I had found a defect on a 60 year old system!!