If a PROCEDURE or TABLE PROCEDURE sets an SQLSTATE value, the SQLSTATE value returned to the calling program is always 38000, regardless of what was set.
This is the way the interface was designed to work. In the following text, the word "procedure" is used to indicate both PROCEDURE and TABLE PROCEDURE.
If a procedure sets an SQLSTATE value, it must be of the form 38xxx. If it is, then the SQLSTATE returned to the SQL session is 38000. The value set by the procedure can be seen in the text accompanying the message. For example ...
The code may do something like this:
IF DB-ANY-ERROR THEN DO.
MOVE '38001' TO SQLPROC-SQLSTATE.
If a call to such a procedure drives the above code, the output will look something like this:
SELECT * FROM IJHSQL.COUNTRY WHERE ID='DK';
*+ Status = -4 SQLSTATE = 38000 Messages follow:
*+ DB001075 C-4M321: Procedure COUNTRYD exception 38001 SEQ=0002 STAT= 326
If the procedure sets a value that does not match 38xxx, then the message returned to SQL is DB001077 with an SQLSTATE of 39001, as opposed to the DB001075/38000.
This behavior is consistent whether the procedure is being invoked from OCF, IDMSBCF or an application program.
For more information, see the CA IDMS SQL Reference Guide, Appendix C "SQL Communication Area", "SQLSTATE".