Troubleshooting
Problem
APAR IC77455 was opened to address +w option's incorrect behavior for insert/update/delete statements to suppress warning messages. Whenever warning message is not reported, sqlcode and return code should imply that statement is executed successfully. APAR IC98702 was opened later for same issue with the select statement. The documentation for 9.7 does not show the updated information. However, v10.1 and v10.5 correctly states the return code values for +w option. Documentation missing in 9.7 - When a CLP command is used with the +w option, warnings are suppressed, 1 and 2 codes are never returned, and 0 is returned in such cases.
Symptom
When the command line processor finishes processing a command or an SQL statement, it returns a return (or exit) code. These codes are transparent to users executing CLP functions from the command line, but they can be retrieved when those functions are executed from a shell script.
Code Description
0 DB2® command or SQL statement executed successfully
1 SELECT or FETCH statement returned no rows
2 DB2 command or SQL statement warning
4 DB2 command or SQL statement error
8 Command line processor system error
As the repro below shows - when we use db2 +w "select * from t1"
In 9.7fp9 and lower - return code is 1 == > as per apar IC98702, behavior was correct in db2_v97fp10 and later. Hence corrected the fixpacks. If I am not correct, please do the hanges as necessary.
In 9.7fp10 - return code is 0
In DB2 v9.7.0.7
$ db2 +w "select * from t1"
C1
-----------
0 record(s) selected.
$ echo $?
1
In DB2 v9.7.0.11
$ db2 +w "select * from t1"
C1
-----------
0 record(s) selected.
$ echo $?
0
Resolving The Problem
Upgrade to v9.7 fp10 and later.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21978272