December 14, 1999 09:44 AM
(in response to Hemant (CORP Kumar)
SQLCODE contains the SQL return code, which indicates the success
of the last SQL statement executed. SQLSTATE performs a similar
SQLCODE but is consistent across DB2 (and ANSI-compliant SQL)
Always code an IF statement in your programs immediately after
statement to check the value of the SQLCODE or SQLSTATE. In
gearing your application programs to check for SQLCODE you can
for negative values (that is, SQLCODE < 0). This takes into
positive SQLCODE values are warnings and SQLCODE = 0 means no rows
Of course, you can code a similar IF condition when using SQLSTATE,
need to take into account that SQLSTATE values that indicate
warnings have a
'1' in the second position - that is, they are of the form '01xxx'
a SQLSTATE of '02000' means no rows found - so an equivalent IF
for SQLSTATE could be coded as SQLSTATE > '02000'.
SQLSTATE values consist of five characters: a two-character class
code and a
three-character subclass code. The class code indicates the type of
and the subclass code details the explicit error within that error
Checking the SQLSTATE value can be easier when you must check for a
SQLCODEs associated with a single SQLSTATE, when you want to check
particular class of error using the two-character class code, or
program runs on multiple platforms.
For example, SQLSTATE '08001' is issued when three SQLCODEs are
-808, -30080, -30082... all are associated with communication
errors. If you
need to specifically check for this type of error it is easier to
SQLSTATE = '08001' instead of SQLCODE = -808 or SQLCODE = -30080 or
= -30082. Of course, this type of situation is not very common.
So, to summarize:
* favor SQLCODE if your programs are DB2 only and do not need to
other SQL database access
* favor SQLSTATE if you are adamant that all programs adhere to
standards or you need to check groups of error conditions by
Of course, checking either will get the job done. Be sure you
one or the other after every SQL statement in your application
would expect that most mainframe shops are using SQLCODE in most of
programs simply because it has been available longer.
Craig S. Mullins
Director, DB2 Technology Planning
[login to unmask email]
>From: Theisen, Craig [mailto:[login to unmask email]
>Sent: Monday, December 13, 1999 1:12 PM
>To: [login to unmask email]
>Subject: SQLCODE or SQLSTATE ???
> We are a fairly new 0S/390 DB2 rel 5.1 shop with a clean slate
>we are still establishing our applications standards and common
>Are any other newer IBM mainframe shops willing to share some
info on what
>they are using: ANSI- SQLSTATE or IBM's SQLCODE and why? I
>either to work equally well, but are there some subtle
advantages of 1
>over the other?