IBM Mainframe Forum

by **DonkeyKong** » Wed Sep 23, 2015 7:29 pm

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:

Select all

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

by **enrico-sorichetti** » Wed Sep 23, 2015 7:46 pm

why not use a simple IEBGENER to see how Your data gets mangled

by **DonkeyKong** » Wed Sep 23, 2015 7:49 pm

That is a great idea!

by **DonkeyKong** » Wed Sep 23, 2015 7:54 pm

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?

by **enrico-sorichetti** » Wed Sep 23, 2015 8:02 pm

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

by **DonkeyKong** » Wed Sep 23, 2015 9:17 pm

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!

by **BillyBoyo** » Wed Sep 23, 2015 10:14 pm

Note, the symbolics were correct, they just weren't how you want. If they weren't correct (as documented) raise a PMR with IBM.

by **DonkeyKong** » Thu Sep 24, 2015 5:59 pm

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.

Select all

// 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.

by **BillyBoyo** » Thu Sep 24, 2015 7:57 pm

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.

by **DonkeyKong** » Thu Sep 24, 2015 8:03 pm

That would have been cool if I had found a defect on a 60 year old system!!

IBM Mainframe Forum

Print in-stream JCL to SDSF with symbolics resolved

Print in-stream JCL to SDSF with symbolics resolved

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

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

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

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

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

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

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

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

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