SAS option to tell SAS to not abend upon remote DB2 query error?

[deleted_user]

I have an ETL job stream on the mainframe, the first JCL step is a SAS program that performs the Extract and Transform, and the subsequent Load steps are dependent on the SAS return code. We configured the Load steps to tolerate a maximum SAS return code of 4.

The Extract step is made up of a number (around 50) of SQL/DB2 queries executed in loop via PROC SQL in "pass-through" mode (CONNECT TO DB2). The return code of each SQL/DB2 query is retrieved (&SQLXRC, &SQLXMSG) and stored in a table. We already know that it's possible that an SQL is incorrect and returns a NOT OK code. If DB2 returns an OK code, the extracted data is then transformed and written to a flat file. If not, no big deal, skip to the next SQL.

The problem observed is that SAS abends whenever any one of the SQL's returns a NOT OK code, even if the 49 others returned OK. Weird thing is, when one of these incorrect SQL is encountered, the loop keeps on going, all return codes retrieved and stored, data transformed, etc. Everything looks good but at the very end, SAS abends. That's a serious problem as the subsequent Load steps don't execute.

Is there a SAS option to tell SAS to consider this remote DB2 query return code as "minor", and not abend?

We identified 3 solutions, all unpractical:
- OPTION NOERRORABEND: job does not abend but forces a return code=8, which could hide a real SAS problem
- execute SQL/DB2 queries in distinct JCL steps: makes retrieving and storing return codes and controlling job flow difficult
- pre-test SQL/DB2 queries: users can submit their own SQL, and their environment could be different than prod

Are there any other approaches? e.g. execute the SQL/DB2 queries via external calls to obscure modules: CALL MODULE_SQL_DB2(SQL, RC), etc.

Thanks to all.

[sbb]

Suggest you contact SAS support if you believe the SAS system behavior on z/OS MVS is not performing consistently. Be prepared to share with them details about a few different execution scenarios, while providing complete SAS log outputs.

Scott Barry
SBBWorks, Inc.

[DanielSantos]

Use the OPTION NOERRORABEND and reset the auotmatic macro variable SYSCC (which holds the condition code returned to the system).

In order to maintain the condition code of the remaining SAS code (before and after the SQL extract) you should store its value in a temporary macro variable BEFORE the call to DB2 an restore the value AFTER it.

Something like this:

[pre]options noerrorabend;

%let _SYSCC=&SYSCC;[/pre]
SQL pass-through code here!!!

[pre]%let SYSCC=&_SYSCC;[/pre]

This should work.

More on the SYSCC auto variable here:
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/tw3514-syscc-var.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[deleted_user]

That did it. I vaguely remember seeing this &SYSCC macro variable a long time ago in some program, and thinking it must be a read-only variable, I never thought you could write to it. Learn something everyday! This is so adequate yet so simple.

By the way, while implementing your solution, I also came across this cool feature that let you also save the ERRORABEND setting prior to changing it to NO, and I thought you might benefit too:

%let _OptionErrorabend = %sysfunc(getoption(errorabend));
SQL pass-through code here!!!
OPTION &_OptionErrorabend;

This way, the environment can return to exactly what it was before the PROC SQL.

Thank you very much for your quick reply. Portugal just found new fans here in Quebec, and your name made it into the SAS program as a contributor.

Cheers.

Patrice Cote @ rrq.gouv.qc.ca

[DanielSantos]

Hi Patrice.

Always nice to help.

Cheers from Portugal

Daniel Santos @ www.cgd.pt