DB2 Error codes and Handling
Error code can be handled in the program by using SQLCODE validation after each query executed.
Most of very frequent error codes described below.
The SQLCODE is equal to 000, the query executed successfully.
If the SQLCODE is positive, Then the Query executed with Warning.
If the SQLCODE is negative, then the Query returns errors.
- End of the ROWS fetched in cursor
- Rows not found in the table to select which satisfies the condition.
- Rows not found in the table to UPDATE/DELETE in the table which satisfies the condition.
- Date format error (MM:DD:YYYY) – i.e. the Date using for the
- Move/to compare is different from target date format.
- Date was not moved to the target field which is required to.
- Date internal values (MM>12, DD>31)
- Date and Month values are exceeds its maximum.
- Null values exception.
- The cursor fetches returning the null values which was not handled with NULL indicator.
- The SELECT query retrieving NULL values which are not handled with NULL indicator.
- The UPDATE/INSERT trying to insert/update NULL values without handling it through NULL indicator.
- Missing decimal values
- This will happen when any Numeric host variable is used to update the column which is having the decimal equalent column.
- Mismatch between number of host variables & number of columns selected.
- Mismatch between the number of host variables & numbers of columns selected in SELECT query or FETCH cursor.
- Cursor not opened
- The program tries to fetch the data for a particular cursor which is not opened.
- Cursor already opened.
- The program tries to open the cursor which is already opened.
- Foreign key violation
- Duplicate records
- Program tries to insert the rows which s already existed with the same key
- Program tries to update the existing row which is equalent to the key that is already existed in the table.
- Plan not found / resource not available.
- Program tries to call the program which the plan got corrupted or the plan got deleted.
- cursor not defined/select trying to retrieve multiple records
- Normal select statement in the program retrieving more than one row from the table which needs a cursor declaration.
- Timestamp mismatches error.
- As we discussed in the BIND process, if the timestamp mismatched during the compilation, the above error will be thrown when calling the particular modules which didn’t binded properly.
- Resource not available.
- DEAD LOCK with timeout.
- DEAD LOCK with roll back.
- Authorization failure.
+ SQLCODE ------ will return the SQL return code for the query provided but not for the condition provided.
All 900 series SQLCODE are Authorization and DEADLOCK errors.
DB2 NULL Statement