Hi,

I am running FOCUS 7.2 on a mainframe and access DB2 and have hit a small problem. When the procedure accesses the DB2 tables and receives a -911 deadlock SQLCODE, this does not set the FOCERRNUM to a non 0 value as other invalid SQLCODES do. I am guessing that this is because although an error, it is not a true error, more a 'please replace the receiver and re-dial later' type of error.

Across the system that I am working on, there are numerous FOCEXECs that all have the following code after a DB2 access:
-IF &FOCERRNUM NE 0 GOTO FATAL;
-IF &LINES EQ 0 GOTO NOLINES;

According to the manual, the SQLCODE code is passed back in a Focus variable, FOCERROR. What I want to do is to add in to the line that references the &FOCERRNUM variable an 'OR FOCERROR NE 0' line, but I am struggling.
Specifying the FOCERROR without an & gives an error.
Specifying &FOCERROR does not recognise the variable.
Specifying the &FOCERROR after defining it with a value of 0 at the beginning of the exec, does not get it updated after the -911 has been hit.

Any guidance that you can give would be much appreciated.

Regards,

Marc Lodge

The reason for your request was not stated. I assume you want to stop the batch job after a db2 error. Here is a snippet of code that is used here. Focus release 7.11.
................
table code reading db2 table
...........................
..........................
-RUN
-SET &STEP='ERROR READING ?????? DB2 TABLE';
-SET &ERRMSG=&RETCODE;
-SET &ERRMSG2=&FOCERRNUM;
-IF &RETCODE NE 0 GOTO BAD;
.................................
more code -- repeat above if going against more db2 tables
.......................................
-GOTO GOOD
-BAD
-TYPE PROBLEM HERE
-TYPE STEP: &STEP
-TYPE RETURN CODE: &ERRMSG (NEGATIVE NUMBERS ARE SQL RETURN CODES)
-TYPE FOCUS ERROR: &ERRMSG2
-QUIT FOCUS 0016
-*---------------------------------------------------------------------
-GOOD
-EXIT

Many thanks ET for your answer. I do want to stop the job if an invalid SQLCODE is found, and RETCODE does indeed return the SQLCODE, but not for a -911 dead lock.

To test this, I coded an invalid SQL statement, and RETCODE showed the SQLCODE. I then corrected the SQL, locked the table it was using and ran again. The RETCODE (displayed in the exec) was 0.

Sorry, hope someone else can help. I'm not a db2 interface expert. I'm just told to include this code after every read against a db2 table here to force an abend. It appears to work as this code has been used widely here for years without any problems. Either we have never encountered a 911 or it works here because of differences in how the focus db2 interface/environment is configured compared to your site. Hope some of the IBI focus db2 wizards will join in on this and give you the answer. Good luck and happy holidays.

This what I did for DB2 a long time ago:

TABLE FILE FMSTDAT
PRINT DB2_BOM_BATCH
IF USERID EQ ' '
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS SAVE01
END
-RUN

-IF &FOCERRNUM NE 0 GOTO WF_ERROR;

-* blah blah


-* WebFOCUS Error --------------
-WF_ERROR
-IF &RETCODE GE 0 GOTO NON_SQL_ERR;

-SQL_ERR
-SET &ERR_MSG = DECODE &RETCODE(
- '-551' 'App Security Access Error - SQL Error Code: &RETCODE.EVAL'
- ELSE 'SQL Error Code: &RETCODE.EVAL');

-NON_SQL_ERR
-SET &ERR_MSG = DECODE &RETCODE(
- '99' 'Unauthorised to access App Web Reporting. Error Code: &RETCODE.EVAL'
- ELSE 'Non-SQL Error Code: &RETCODE.EVAL');

Pietro, thanks for your reply, but I have simialr code. The problem seems to be that the FOCERRNUM (and RETCODE) on return from a -911 are set to 0.

I'm a little perplexed because the DB2 and SQL/DS Read/Write iInterface Users Manual Release 7, specifically mentions on 12-20 that 'The RDBMS produces a return code, the SQLCODE, that reflects the success or failure of SQL statements; FOCUS stores this return code in the variable FOCERROR'

Try as I might, I do not seem to be able to access this variable. Others I've tried (FOCERRNUM, and RETCODE) do not contain the SQLCODE after a -911.

If anybody has any ideas on this, please let me know, otherwise I am going to have to code a loop to try and access the data.

Wild Guess:

In our unix environment, we discovered that amper_RETCODE always returned a zero value from the execution of an opsys command (like -UNIX ls filename.stuff to determine whether this file exists). It's a long story, but the Focus STATE command didn''t work early on, so this kind of coding was an alternative.

We were told to use amper_EXITRC to access this type of return code. It seems to work in 5.3.2, and we were advised that it also works in 5.2.7. It did not work in prior releases.

Maybe you have a release that you could try this with.

Thanks for your suggestion Suzy, but unfortunately that did not work either. I shall just have to code a loop if 0 lines are found in the report.

Marc