How to get SQLCODE in DB2 COBOL Program without SQLCA

Below are the best techniques either ways you can use. As your wish if you want you can give SQLCA or without it also you can get SQLCODE.

Option-1 -Without SQLCA

  • Declare SQLCODE and SQLSTATE  as stand-alone host variables. If you specify the STDSQL(YES) precompiler or SQL statement coprocessor option, these host variables receive the return codes, and you should not include an SQLCA in your program.
SQLCA in DB2
SQLCA in DB2

Details:

When you specify STDSQL(YES), which indicates conformance to the SQL standard, you should not define an SQLCA. The stand-alone variable for SQLCODE must be a valid host variable in the DECLARE SECTION of a program. It can also be declared outside of the DECLARE SECTION when no variable is defined for SQLSTATE. The stand-alone variable for SQLSTATE must be declared in the DECLARE SECTION. It must not be declared as an element of a structure.

Example(s) for stand alone declaration of SQLCODE:

01 SQLCODE S9(9) COMP.
exec sql open cursor pub_id end-exec.

PARAGRAPH-1:
exec sql fetch pub_id into :PUB_NAME end-exec.
IF SQLCODE = 0 GOTO PARAGRAPH-1.

Example(s) for Stand alone declaration of SQLSTATE:

WORKING-STORAGE SECTION.
01 SQLSTATE PIC x(5)
. . .
exec sql whenever sqlerror perform ERR-PARA
end-exec
. . .
ERR-PARA.
IF sqlstate = “ZD000” or
sqlstate = “ZE000” or
sqlstate = “ZF000” or
sqlstate = “ZG000” or
sqlstate = “ZH000”
DISPLAY “Unexpected results were ignored”
ELSE
IF sqlstate = “08001” or sqlstate = “08000”
DISPLAY “Connection failed-quitting”
STOP RUN
ELSE
DISPLAY “A non-results, non-connect
— error occurred”
END_IF
END_IF

Option-2 – SQLCA

  • Provide a structure named SQLCA. The SQLCA includes the SQLCODE and SQLSTATE, in addition to other status information. You can provide an SQLCA by using the INCLUDE SQLCA statement. You can call the DSNTIAR sample subroutine from your application program to format the contents of the SQLCA, or you can retrieve fields from the SQLCA yourself.

So, final thought is SQLCA is not mandatory in DB2 programs.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.