SQLCODE
The number Db2 returns after every SQL statement. It says if the statement worked.
Also written SQLSTATE, SQL return code
Why it matters. A program that ignores SQLCODE carries on after a failure. Reading it is the first step of every Db2 fix.
See it
Db2 sets SQLCODE after every SQL statement. Always check it.
Try it SQLCODE
- 1
You're in the SYSIN lines of job SQLERR, a DSNTEP2 step. Write a statement that asks for all the rows of a table that does not exist: M1B.ACCOUNT, with a letter missing.
SYSIN SELECT * FROM M1B.ACCOUNT; - 2
Add a second statement: set the balance to 0 for an account that is not in the table, NOACCT99.
SYSIN UPDATE M1B.ACCOUNTS SET BALANCE = 0 WHERE ACCTNO = 'NOACCT99'; - 3
Submit the job SQLERR from your DEV JCL library.
SUBMIT 'ZB0001.DEV.JCL(SQLERR)' - 4
Read the output of the job. Find the SQLCODE for each statement.
OUTPUT SQLERR
Remember
- 0 means it worked. A negative number is an error. 100 means no row was found. Other positive numbers are warnings.
- A COBOL program reads SQLCODE after every EXEC SQL.
- -204 is a name Db2 does not know. -803 is a duplicate key. -811 is more than one row where one was expected.
The usual mistake
A program that never looks at SQLCODE
If a statement fails, the program carries on as if it worked. Wrong or missing data goes on to the next step.
IF SQLCODE NOT = 0, deal with it before going on
On real z/OS
The SQLCODE and SQLSTATE values and message texts follow IBM's tables. The SQLERRP module names and SQLERRD numbers in MF/OS are representative only, and messages such as -803 leave out the index space and RID that real Db2 prints.
Where can I practise SQLCODE?
Right here, at ZedBank. You take a junior job at a simulated bank and use SQLCODE in real work, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- Embedded SQLSQL statements written directly inside a COBOL program, marked off so they can be prepared before compiling.
- CursorA mechanism for stepping through a set of rows one at a time, when a query returns more than one result.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.
- SQLThe language used to query and change relational data. The same SQL used everywhere else, running against mainframe tables.
- AbendAn abnormal end: a program stopping in an uncontrolled way rather than finishing and reporting a result.
- Production supportKeeping live systems running: diagnosing failures, fixing what broke, and getting the service back.