DB2 SQL Error Codes

Prev
Next

DB2 Errors

A DB2 Error often looks confusing so if you use this DB2 SQL Error Code guide, you will master the fix for all the most common DB2 SQL Error Codes-

SQL Codes at a higher level

SQL Codes Error at higher level
SqlCode Meaning
0 Success - Execution is Successful
< 0 Danger - Execution failure
> 0 Warning - Execution is Successful with Warning
100 Info - No record found/No Row found for fetch, update and delete/Query resulted empty table

Important DB2 SQL Error Codes

DB2 SQL Error Codes
SqlCode Meaning
-84 DB2 cannot accept this SQL Statement. PREPARE or EXECUTE IMMEDIATE SQL statement cannot be prepared

Fix:
Verify the source of SQL query.
-101 The SQL statement is more than the maximum limit.

Fix:
Break the large SQL statements into smaller ones so that it will lower the complexity.
-104 DB2 sql error code 104 occurs due to illegal symbol token

Fix:
In the specified token, verify the SQL statement.
-105 SQL Statement has an invalid String.

Fix:
Verify that the format of the string is correct.
-111 The column function such as SUM, MAX, AVG, etc is invalid as it did not include the column name in its operand.

Fix:
Mention the column name as an operand to its function.
-117 Number of columns and the number of insert values does not match.

Fix:
Set the same number of values as the number of columns.
-119 The column in 'HAVING' clause is not present in the 'GROUP BY' clause.

Fix:
Add that column in the 'GROUP BY' clause.
-121 A column name occurs more than once while you define the INSERT or UPDATE statement.

Fix:
Hence, correct the SQL statement.
-172 Invalid function name.

Fix:
Therefore, you need to correct the function-name.
-180 DB2 SQLCODE 180 denotes an invalid syntax for the string that represents DATETIME value.

Fix:
Hence, check the datetime value and its data-type.
-203 Ambiguous reference to the column-name.

Fix:
Qualify the ambiguous column name with a correct table name.
-206 If the SQL statement try to use INSERT , UPDATE or SELECT a column name which is not part of the table, then this DB2 SQL Error code is generated.

Fix:
Verify the columns names used in the INSERT and UPDATE statement. In case of SELECT, check the column names in the FROM Clause
-208 ORDER BY clause in wrong because the column name is not a part of the result table .

Fix:
Delete the column name from the ORDER BY clause or add the required column name to the result table.
-302 SQLCODE 302 means -
  • Either the type of column and the length of the value in it is not in sync, or
  • The data type and the position number of the host variable is not in sync

    Fix:
    SQLcode 302 is little confusing, you need to check if the host variables contains a valid value.
    		•
    -305 You cannot assign a NULL value to the output host variable without using a null indicator.

    Fix:
    Examine the FETCH or SELECT statement and use null indicator variables for all those host variables which might get NULL values.
    -312 Host variable is unusable or is not declared.

    Fix:
    Check if host variables are declared properly. In addition, the properties of the variable must be compatible with the use of the variable in SQL statement
    -313 Number of host variables present in the OPEN or EXECUTE statement does not match the number of parameter markers in the SQL statement. The parameter marker is the question mark.

    Fix:
    In short, match these values properly.
    -407 A column name which is declared as NOT NULL received a NULL value after an UPDATE or INSERT Statement.

    Fix:
    Check all the NOT NULL columns and take corrective actions.
    -501 DB2 sqlcode 501 occurs when the program tries to fetch or close a cursor which is not yet opened.

    Fix:
    Check the previous steps or logic if any one step closes a cursor. In addition, you need to make sure that opening of cursor is the done prior to fetch and close at first.
    -502 Program tried to open a cursor which is already opened.

    Fix:
    Check all the previous step whether cursor is closed properly or not.
    -503 Program cannot update a column as that column name is not present in the FOR UPDATE clause of the SELECT statement of the cursor.

    Fix:
    Add the column name in the FOR UPDATE Clause of the cursor.
    -504 The name of the cursor is nor defined or declared.

    Fix:
    Declare the cursor name first.
    -507 Program tried to run an UPDATE or a DELETE cursor without even opening the cursor.

    Fix:
    Open the cursor first.
    -509 The table name in the UPDATE or DELETE clause is not the same as the table name which is used while declaring the cursor.

    Fix:
    The table name in the UPDATE or DELETE clause must be same as the table name which is used while defining the cursor.
    -530 Program tried to use the INSERT or UPDATE on the FOREIGN KEY which was invalid because this value did not match the primary key of its parent table.

    Fix:
    Match the updated or insert foreign key value with any of the primary key. This means that the updated or inserted foreign key value must be present in the parent table in its primary key position
    -531 An UPDATE to the primary key in the parent table failed because it has dependent rows.

    Fix:
    Check the relationship of the primary key with its dependent rows.
    -539 A Foreign key tried to refer a parent table but the parent table does not have a primary key.

    Fix:
    Either add a primary key to the parent table or refer to a correct table which has a primary key
    -540 Unique index or primary index is not defined for the primary key before referring the table

    Fix:
    First, you shoule create a primary index or a unique index before the table is referred.
    -542 A Particular column which might contain NULL values cannot be a part of the primary key.

    Fix:
    Make sure that the column name which is a part of primary key is defined as NOT NULL
    -602 The CREATE INDEX contains too many columns.

    Fix:
    Reduce the number of columns.
    -603 DB2 cannot create an UNIQUE INDEX because duplicate records are present for the column name which is required as a Unique Index

    Fix:
    Remove the duplicate records in the required column which is required to be as a unique index.
    -612 While creating the table, view or index, you provided a duplicate column name.

    Fix:
    Hence, you should provide only the unique column names.
    -613 Either the primary key contains too many columns or the primary key is very long.

    Fix:
    Reduce the number of columns and at the same time, you should make sure that the length of the column is not very big.
    -624 The table cannot have more than one primary key.

    Fix:
    Do not create another primary key for the same table.
    -638 You tried to create a table without adding the definitions for the columns.

    Fix:
    Check if you missed to set column definitions
    -680 Program tried to create more than 750 columns in a table.

    Fix:
    You cannot add more than 750 columns in a table.
    -687 You are trying to compare 2 columns of different data type.

    Fix:
    Verify the SQL statement to check for any comparison between incompatible fields.
    -803 You tried to insert or update a duplicate value on the unique index.

    Fix:
    Verify the duplicate record and remove it.
    -805 SQLCode code 805 is very common and it happens because the program name is not present in the PLAN

    Fix:
    Bind the program in the plan properly.
    -811 SQL error code 811 is also a common error. Either the SELECT statement returned more than one row or basic predicate contains a sub-query which results in more than one value

    Fix:
    So, it is better to bind the program in the plan properly.
    -818 The TIMESTAMP inside the LOAD MODULES which is generated by the PreCompiler differs from the TIMESTAMP inside the DBRM which is created during the BIND.

    Fix:
    Hence, Bind the program again.
    -901 The SQL statement failed to execute due to a system error

    Fix:
    Verify with the DBA or system programmer team why system error occurred.
    -904 The SQL statement failed due to Resource Not available

    Fix:
    Check the Error logs to see which resource is not available and notify the DBA at the same time.
    -905 When the program tries to use a resource for a very long time, the resource limit is exhausted and the application program fails

    Fix:
    Check why the SQL statement is exceeding the maximum time limit for using the resource. Furthermore, you should optimize the SQL Query, indexing, etc.
    -909 The application program might have dropped the table or index and tried to access the table or index respectively but fails because the table or index is already dropped.

    Fix:
    Correct the application program.
    -911 Db2 SQLCODE 911 is a common error which is a DEADLOCK or TIMEOUT issue - Execution fails and the work is rolled back until the last commit.

    Fix:
    Specifically, you can change the logic to commit frequency. Also, you can avoid using a very busy resource more frequently.
    -913 Execution fails due to DEADLOCK or TIMEOUT

    Fix:
    Develop a logic so that the work can commit or roll back to the last commit depending on the logic.
    -922 Authorization failure during connection

    Fix:
    Check if the plan is authorized or the user has proper access. In spite of these precautions, if you are not able to fix the issue, then you must involve the DB2 DBA Team
    -923 Connection failed

    Fix:
    Check with Network connectivity team. At the same time, you can follow up with the DBA.
    -924 SQL Code 924 is an internal Error for DB2 connection

    Fix:
    Check with DBA team on the connectivity issue
    Prev
    Next
    Youtube Facebook-f Twitter

    Tutorials for all brains!