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