DB2 SQL Error Codes — TutorialBrain (2023)

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

Fix:
Verify the source of SQL query.

-101The SQL statement is more than the maximum limit.

Fix:
Break the large SQL statements into smaller ones so that it will lower the complexity.

-104DB2 sql error code 104 occurs due to illegal symbol token

Fix:
In the specified token, verify the SQL statement.

-105SQL Statement has an invalid String.

Fix:
Verify that the format of the string is correct.

-111The 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.

-117Number of columns and the number of insert values does not match.

Fix:
Set the same number of values as the number of columns.

-119The column in 'HAVING' clause is not present in the 'GROUP BY' clause.

Fix:
Add that column in the 'GROUP BY' clause.

-121A column name occurs more than once while you define the INSERT or UPDATE statement.

Fix:
Hence, correct the SQL statement.

-172Invalid function name.

Fix:
Therefore, you need to correct the function-name.

-180DB2 SQLCODE 180 denotes an invalid syntax for the string that represents DATETIME value.

Fix:
Hence, check the datetime value and its data-type.

-203Ambiguous reference to the column-name.

Fix:
Qualify the ambiguous column name with a correct table name.

-206If 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

-208ORDER 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.

-302SQLCODE 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.

-305You 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.

-312Host 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

-313Number 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.

-407A 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.

-501DB2 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.

-502Program tried to open a cursor which is already opened.

Fix:
Check all the previous step whether cursor is closed properly or not.

-503Program 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.

-504The name of the cursor is nor defined or declared.

Fix:
Declare the cursor name first.

-507Program tried to run an UPDATE or a DELETE cursor without even opening the cursor.

Fix:
Open the cursor first.

-509The 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.

-530Program 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

-531An 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.

-539A 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

-540Unique 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.

-542A 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

-602The CREATE INDEX contains too many columns.

Fix:
Reduce the number of columns.

-603DB2 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.

-612While creating the table, view or index, you provided a duplicate column name.

Fix:
Hence, you should provide only the unique column names.

-613Either 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.

-624The table cannot have more than one primary key.

Fix:
Do not create another primary key for the same table.

-638You tried to create a table without adding the definitions for the columns.

Fix:
Check if you missed to set column definitions

-680Program tried to create more than 750 columns in a table.

Fix:
You cannot add more than 750 columns in a table.

-687You are trying to compare 2 columns of different data type.

Fix:
Verify the SQL statement to check for any comparison between incompatible fields.

-803You tried to insert or update a duplicate value on the unique index.

Fix:
Verify the duplicate record and remove it.

-805SQLCode 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.

-811SQL 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.

-818The 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.

-901The SQL statement failed to execute due to a system error

Fix:
Verify with the DBA or system programmer team why system error occurred.

-904The 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.

-905When 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.

-909The 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.

-911Db2 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.

-913Execution 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.

-922Authorization 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

-923Connection failed

Fix:
Check with Network connectivity team. At the same time, you can follow up with the DBA.

-924SQL Code 924 is an internal Error for DB2 connection

Fix:
Check with DBA team on the connectivity issue