SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.
Explanation
The program attempted to do one of:
- FETCH using a cursor at a time when the specified cursor was not open.
- CLOSE a cursor at a time when the specified cursor was not open.
- Reference a cursor variable in an OPEN statement and the cursor variable is not open.
- Reference a cursor scalar function, such as CURSOR_ROWCOUNT function, and the cursor variable is not open.
The statement cannot be processed.
User response
Check for a previous message (SQLCODE) that may have closed the cursor. Note that after the cursor is closed, any fetches or close cursor statements receive SQLCODE -501.
If no previous SQLCODEs have been issued, correct the application program to ensure that the cursor is open when the FETCH or CLOSE statement is executed.
If a cursor variable is referenced in a cursor scalar function, verify that that the cursor is not null, is defined, and is open, else replace the cursor variable with one that is in that state.
sqlcode: -501
sqlstate: 24501
SQL0502N The cursor specified in an OPEN statement is already open.
Explanation
The program attempted to execute an OPEN statement for an open cursor.
The statement cannot be processed. The cursor was unchanged.
User response
Correct the application program to ensure it does not attempt to execute an OPEN statement for a cursor already open.
sqlcode: -502
sqlstate: 24502
SQL0503N A column cannot be updated because it is not identified in the FOR UPDATE clause of the SELECT statement of the cursor.
Explanation
Using a cursor, the program attempted to update a value in a table column that was not identified in the FOR UPDATE clause in the cursor declaration or the prepared SELECT statement.
Any column to be updated must be identified in the FOR UPDATE clause of the cursor declaration.
The statement cannot be processed.
User response
Correct the application program. If the column requires updating, add its name to the FOR UPDATE clause of the cursor declaration.
sqlcode: -503
sqlstate: 42912
SQL0504N The cursor name is not defined.
Explanation
An UPDATE or DELETE WHERE CURRENT OF name was specified, but the cursor name was not declared in the application program.
The statement cannot be processed.
User response
Ensure the completeness of the application program and correct spelling errors in the cursor names.
sqlcode: -504
sqlstate: 34000
SQL0505N The cursor name is already defined.
Explanation
The cursor name specified in the DECLARE statement has already been declared.
The statement cannot be processed.
User response
Ensure the name is spelled correctly.
SQL0507N The cursor specified in the UPDATE or DELETE statement is not open.
Explanation
The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement when the specified cursor was not open.
The statement cannot be processed. No update or delete was performed.
User response
Check for a previous message (SQLCODE) that may have closed the cursor. Note that after the cursor is closed, any fetches or close cursor statements receive SQLCODE -501 and any updates or deletes receive SQLCODE -507. Correct the logic of the application program to ensure that the specified cursor is open at the time the UPDATE or DELETE statement is executed.
sqlcode: -507
sqlstate: 24501
SQL0508N The cursor specified in the UPDATE or DELETE statement is not positioned on a row.
Explanation
The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement when the specified cursor was not positioned on an object table row. The cursor must be positioned on the row to be updated or deleted.
The cursor is no longer positioned on a row if the row is deleted. This includes any use of cursors within a savepoint when a ROLLBACK TO SAVEPOINT is performed.
Federated system users: the record in a remote data source has been updated and/or deleted by another application (or a different cursor within this application) and the record no longer exists.
The statement cannot be processed. No data is updated or deleted.
User response
Correct the logic of the application program to ensure that the cursor is correctly positioned on the intended row of the object table before the UPDATE or DELETE statement is executed. Note that the cursor is not positioned on a row if FETCH returned message SQL0100W (SQLCODE = 100).
sqlcode: -508
sqlstate: 24504
SQL0509N The table specified in the UPDATE or DELETE statement is not the same table specified in the SELECT for the cursor.
Explanation
The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement where the table named did not match the name of the table specified in the SELECT statement that declared the cursor.
The statement cannot be processed.
User response
Correct the application program to ensure that the table identified in the UPDATE or DELETE statement is the same table identified in the cursor declaration.
sqlcode: -509
sqlstate: 42827
SQL0510N UPDATE or DELETE is not allowed against the specified cursor.
Explanation
The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement against a table or view definition that does not permit the requested update or delete operation. For example, this error can occur in a delete from a read-only view or in an update where the cursor was not defined with the FOR UPDATE clause.
On the database manager the view is read-only if the SELECT statement includes :
- The DISTINCT keyword
- A column function in the SELECT list
- A GROUP BY or HAVING clause
- A FROM clause that identifies one of the following:
- More than one table or view
- A read-only view (READONLY column of SYSCAT.SYSVIEWS is set to 'Y')
- A set operator (other than UNION ALL).
Note that these conditions do not apply to subqueries of the SELECT statement.
The cursor is declared with a FOR FETCH ONLY clause.
The cursor is ambiguous and the BLOCKING ALL bind option was specified.
The cursor references a view that has an INSTEAD OF UPDATE (or DELETE) trigger.
The cursor directly or indirectly references a view that is defined with the WITH ROW MOVEMENT clause and an UPDATE WHERE CURRENT OF CURSOR was attempted.
The statement cannot be processed.
User response
If the database manager is failing the statement and the cursor is based on a read-only SELECT or VALUES statement, do not issue any update or delete statements against it.
If the database manager is failing the statement and the cursor is not based on a read-only SELECT or VALUES statement and is defined with a FOR FETCH ONLY clause, either remove this clause from the cursor definition or do not issue any update or delete statements.
If the database manager is failing the statement and the cursor cannot be determined to be either fetch only or updatable from its definition or context, rebind the program with either the BLOCKING NO or BLOCKING UNAMBIG bind option.
If the database manager is failing the statement and the cursor is based on a view with an INSTEAD OF UPDATE (or DELETE) trigger, use a searched UPDATE (or DELETE) statement.
If the database manager is failing the statement and the cursor directly or indirectly references a view that is defined with the WITH ROW MOVEMENT clause, do not issue any update statements against it.
Federated system users: isolate the problem to the data source failing the request. If a data source is failing the request, examine the restrictions for that data source to determine the cause of the problem and its solution. If the restriction exists on a data source, see the SQL reference manual for that data source to determine why the object is not updatable.
sqlcode: -510
sqlstate: 42828
SQL0511N The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified.
Explanation
The result table of the SELECT or VALUES statement cannot be updated.
On the database manager, the result table is read-only if the cursor is based on a VALUES statement or the SELECT statement contains any of the following:
- The DISTINCT keyword
- A column function in the SELECT list
- A GROUP BY or HAVING clause
- A FROM clause that identifies one of the following:
- More than one table or view
- A read-only view
- An OUTER clause with a typed table or typed view
- A data change statement
- A set operator (other than UNION ALL).
Note that these conditions do not apply to subqueries of the SELECT statement.
The statement cannot be processed.
User response
Do not perform updates on the result table as specified.
Federated system users: isolate the problem to the data source failing the request.
If a data source is failing the request, examine the restrictions for that data source to determine the cause of the problem and its solution.
If the restriction exists on a data source, see the SQL reference manual for that data source to determine why the object is not updatable.
sqlcode: -511
sqlstate: 42829
SQL0512N The statement or command failed because a federated three-part name is being used in a way that is not supported. Three-part name: name. Reason code: reason-code.
Explanation
You can reference remote objects without having to use nicknames by referencing the remote objects directly with federated three-part names.
This message is returned when a statement uses a three-part name in a way that is not supported. The reason code indicates the restriction that was encountered:
- 1
An attempt was made to use three-part names with either the import utility or the export utility.
- 2
An attempt was made to issue an SQL statement that includes a three-part name with the CREATE ALIAS clause against a partitioned database.
- 3
An attempt was made to use three-part names in a type of SQL statement that does not support three-part names.
- 4
An attempt was made to use three-part names against a data source with which three-part names are not supported.
User response
Review federated three-part name restrictions, and then modify the statement or command to use three-part name only in ways that are supported.
sqlcode: -512
sqlstate: 56023
SQL0513W The SQL statement will modify an entire table or view.
Explanation
The UPDATE or DELETE statement does not contain a WHERE clause so all rows of the table or view are modified if this statement is executed.
The statement is accepted.
Federated system users: not all data sources report this warning condition. The federated server attempts to issue this warning whenever the condition exists, but there is no guarantee that the federated server can always detect this condition. Do not rely on this warning to preclude UPDATE/DELETE operations from affecting an entire table or view.
User response
Ensure that you intend to modify the entire table or view.
sqlcode: +513
sqlstate: 01504
SQL0514N The cursor name is not in a prepared state.
Explanation
The application program tried to use a cursor that is not in a prepared state. The cursor is associated with a statement that meets the following conditions:
- was never prepared
- was made not valid by either an explicit or implicit rebind of the package
- was prepared in a previous transaction and the application's package is bound with KEEPDYNAMIC NO
The statement cannot be processed.
User response
- Prepare the statement named in the DECLARE CURSOR statement before you try to open the cursor.
- The prepare for the cursor must be reissued.
- The statement should be prepared again after COMMIT or ROLLBACK. Alternatively, either bind the package with KEEPDYNAMIC YES or use the ALTER PACKAGE statement to change the KEEPDYNAMIC property to YES.
sqlcode: -514
sqlstate: 26501
SQL0516N The DESCRIBE statement does not specify a prepared statement.
Explanation
The statement name in the DESCRIBE statement must specify a statement that was prepared in the same database transaction.
The statement cannot be processed.
User response
Verify that the statement name specifies a statement that has been prepared.
sqlcode: -516
sqlstate: 26501
SQL0517N The cursor name identifies a prepared statement that is not a SELECT or VALUES statement.
Explanation
The cursor name could not be used as specified because the prepared statement named in the cursor declaration was not a SELECT or VALUES statement.
The statement cannot be processed.
User response
Verify that the statement name is specified correctly in the PREPARE and the DECLARE CURSOR for cursor name statements. Or correct the program to ensure that only prepared SELECT or VALUES statements are used in association with cursor declarations.
sqlcode: -517
sqlstate: 07005
SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement.
Explanation
The application program tried to EXECUTE a statement that
- was never prepared,
- is a SELECT or VALUES statement,
- was made not valid by either an explicit or implicit rebind of the package, or
- was prepared in a previous transaction and the application's package is bound with KEEPDYNAMIC NO.
The statement cannot be processed.
User response
- Prepare the statement before attempting the EXECUTE.
- Ensure that the statement is not a SELECT or VALUES statement.
- The prepare for the cursor must be reissued.
- The statement should be prepared again after COMMIT or ROLLBACK. Alternatively, either bind the package with KEEPDYNAMIC YES or use the ALTER PACKAGE statement to change the KEEPDYNAMIC property to YES.
sqlcode: -518
sqlstate: 07003
SQL0519N The PREPARE statement identifies the SELECT or VALUES statement of the open cursor name.
Explanation
The application program attempted to prepare the SELECT or VALUES statement for the specified cursor when that cursor is already open.
The statement cannot be prepared. The cursor was not affected.
User response
Correct the application program so it does not attempt to prepare the SELECT or VALUES statement for a cursor that is open.
sqlcode: -519
sqlstate: 24506
SQL0525N The SQL statement cannot be executed because it was in error at bind time for section = section-number package = pkgschema.pkgname consistency token = Xcontoken.
Explanation
- The statement was in error when the package was bound, but the error was ignored then because the option SQLERROR (CONTINUE) was used. Since the statement contains an error, it cannot be executed.
- The statement might not be an executable statement at this location, or might only be executable by a DB2 application requester
Note that contoken is given in hexadecimal.
The statement cannot be executed.
User response
If the SQL statement is not supposed to execute at the indicated location, then correct the program so that the statement in error does not execute at that location. Precompile, compile, and bind replace the package. If the SQL statement is supposed to execute at the indicated location, correct the problem found and reissue PREP or BIND using ACTION(REPLACE). If multiple versions of the package have been bound, issue the following SELECT statement to determine which version has the error: SELECT PKGVERSION FROM SYSCAT.PACKAGES where PKGSCHEMA='pkgschema' AND PKGNAME = 'pkgname' and HEX(UNIQUE_ID) = 'contoken'
sqlcode: -525
sqlstate: 51015
SQL0526N The statement was not processed because the statement refers to a created temporary table or a declared temporary table and includes functionality that cannot be used with temporary tables.
Explanation
You can temporarily store the results from the manipulation of data in temporary tables.
There are some restrictions on how temporary tables can be used. For example, temporary tables do not support user-defined type columns, and index compression cannot be enabled for temporary tables. This message is returned when an attempt is made to execute an SQL statement that uses temporary tables in way that is not supported.
User response
Respond to this message in one of the following ways:
- Modify the SQL statement to remove references to created temporary tables or declared temporary tables.
- Modify the SQL statement to remove the functionality that is not supported with temporary tables.
sqlcode: -526
sqlstate: 42995
SQL0528N The table or nickname tablename-or-nickname already has a unique constraint that is a duplicate of constraint name.
Explanation
A UNIQUE clause uses the same column list as the PRIMARY KEY clause, another UNIQUE clause or the PRIMARY KEY or a UNIQUE constraint that already exists for the table tablename. Duplicate unique constraints are not allowed.
name is the constraint name, if one was specified or exists. If a constraint name is not specified, name is the first column name specified in the column list of the UNIQUE clause followed by three periods.
The statement cannot be processed.
User response
Remove the duplicate UNIQUE clause or change the column list to a set of columns that is not already part of a unique constraint.
sqlcode: -528
sqlstate: 42891
SQL0530N The insert or update value of the FOREIGN KEY constraint-name is not equal to any value of the parent key of the parent table.
Explanation
A value in a foreign key of the object table is being set, but this value is not equal to any value of the parent key of the parent table.
When a row is inserted into a dependent table, the insert value of a foreign key must be equal to some value of the parent key of any row of the parent table of the associated relationship.
Similarly, when the value of a foreign key is updated, the update value of a foreign key must be equal to the value of the parent key of any row of the parent table of the associated relationship at the completion of the statement.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
Some data sources do not provide the appropriate values for constraint name. In these cases the message token will have the following format: “<data source>:UNKNOWN”, indicating that the actual value for the specified data source is unknown.
The statement could not be executed. The contents of the object table are unchanged.
User response
Examine the insert or update value of the foreign key first, and then compare it with each of the parent key values of the parent table to determine and correct the problem.
sqlcode: -530
sqlstate: 23503
SQL0531N The parent key in a parent row of relationship constraint-name cannot be updated.
Explanation
An operation attempted to update a parent key in the row of the parent table but the parent key in the specified row has dependent rows in the dependent table associated with it in the constraint-name constraint.
When the update rule of constraint constraint-name is NO ACTION, the value of a parent key in a parent row cannot be updated if the parent row has any dependent rows at the completion of the statement.
When the update rule of constraint constraint-name is RESTRICT, the value of a parent key in a parent row cannot be updated if the parent row has any dependent rows at the time the update of the parent key is attempted.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
Some data sources do not provide the appropriate values for constraint name. In these cases the message token will have the following format: “<data source>:UNKNOWN”, indicating that the actual value for the specified data source is unknown.
The statement could not be executed. The contents of the parent table are unchanged.
User response
Examine the parent key of the object table and the foreign key of the dependent table to determine if the value of the specified row of the parent key should be changed. If this does not show the problem, examine the contents of the object table and the dependent table to determine and correct the problem.
sqlcode: -531
sqlstate: 23001, 23504
SQL0532N A parent row cannot be deleted because the relationship constraint-name restricts the deletion.
Explanation
An operation attempted to delete a specified row of the parent table but the parent key in the specified row has dependent rows in the referential constraint constraint-name and the delete rule of NO ACTION or RESTRICT is specified for the relationship.
When the delete rule of constraint constraint-name is NO ACTION, a row of the parent table cannot be deleted if the dependent rows are still dependent on the parent key at the completion of the statement.
When the delete rule of constraint constraint-name is RESTRICT, a row of the parent table cannot be deleted if the parent row has any dependent rows at the time of the delete.
Note that a delete can cascade to delete other rows in dependent tables that have a delete rule of NO ACTION or RESTRICT. Thus the constraint constraint-name may be on a different table than the original delete operation.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
Some data sources do not provide the appropriate values for constraint name. In these cases the appropriate fields contain values (such as “unknown”) that indicate that the actual value is unknown.
The statement could not be executed. The contents of the table are unchanged.
User response
Examine the delete rule for all descendent tables to determine and correct the problem. The specific tables involved can be determined from the relationship constraint-name.
sqlcode: -532
sqlstate: 23001, 23504
SQL0533N The INSERT statement is not valid because a relationship restricts the result of the fullselect to one row.
Explanation
An INSERT operation with a fullselect attempted to insert multiple rows into a table that is a parent and a dependent in the same relationship of a referential constraint.
The fullselect of the INSERT operation should return no more than one row of data.
The INSERT statement could not be executed. The contents of the object table are unchanged.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
User response
Examine the search condition of the fullselect to ensure selection of no more than one row of data.
sqlcode: -533
sqlstate: 21501
SQL0534N Invalid multiple-row update.
Explanation
An UPDATE operation attempted to perform a multiple-row update of a column included in a primary key or unique index.
Multiple-row updates of columns of a primary key or unique index are not supported.
The UPDATE statement could not be executed. The contents of the table are unchanged.
Federated system users: the constraint can exist on federated server (if the child and parent tables exist as tables on federated server), or it can exist on the data source (if the child and parent tables exist on the data source).
User response
Ensure the search condition of the UPDATE statement selects only one object table row to update.
sqlcode: -534
sqlstate: 21502
SQL0535N The DELETE statement is not valid because a self-referencing relationship restricts the deletion to one row.
Explanation
A DELETE operation with a WHERE clause attempted to delete multiple rows from a table which is a parent and a dependent in the same relationship of a referential constraint with a RESTRICT or SET NULL delete rule.
The WHERE clause of the DELETE operation should select no more than one row of data.
The DELETE statement could not be executed. The contents of the object table are unchanged.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
User response
Examine the WHERE clause search condition to ensure no more than one row of data is selected.
NOTE: This is only a restriction in releases of DB2 prior to Version 2.
sqlcode: -535
sqlstate: 21504
SQL0536N The DELETE statement is not valid because table name can be affected by the operation.
Explanation
A DELETE operation was attempted with the indicated table referenced in a subquery.
- A dependent of the DELETE object table in a relationship with a CASCADE or SET NULL delete rule.
- A dependent of another table in a relationship with a CASCADE or SET NULL delete rule and deletions from the DELETE object table can cascade to that table.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
Some data sources do not provide the appropriate values for name. In these cases the appropriate fields contain values (such as “unknown”) that indicate that the actual value is unknown.
The statement could not be processed.
User response
Do not reference a table in a DELETE statement subquery when the table can be affected by the DELETE statement.
NOTE: This error is only applicable to releases of DB2 prior toVersion 2 and hosts accessed through DB2 Connect.
sqlcode: -536
sqlstate: 42914
SQL0537N A key column list identifies column column-name more than once.
Explanation
The column column-name appears more than once in a key column list. A key column list could occur in a PRIMARY KEY clause, FOREIGN KEY clause, UNIQUE clause, DISTRIBUTE BY clause, ORGANIZE BY clause, PARTITION BY clause or as a functional dependency of a CREATE or ALTER statement.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
Some data sources do not provide the appropriate values for column-name. In these cases the appropriate fields contain values (such as “unknown”) that indicate that the actual value is unknown.
The statement cannot be processed.
User response
Remove the duplicate column names.
sqlcode: -537
sqlstate: 42709
SQL0538N FOREIGN KEY name does not conform to the description of the parent key of table or nickname table-name-or-nickname.
Explanation
The definition of the indicated foreign key does not conform to the parent key description of the table or nickname table-name-or-nickname.
Possible reasons are:
- The number of columns in the foreign key column list does not match the number of columns in the parent key column list.
- The number of columns in the foreign key column list does not match the number of columns in the primary key of the parent table or the parent nickname (a parent key column list is not specified).
- The descriptions of the corresponding columns are not compatible. Column descriptions are compatible if corresponding columns have compatible data types (both columns are numeric, character strings, graphic, date/time, or have the same distinct type).
- The list of column names in a REFERENCES clause cannot reference a parent key defined with BUSINESS_TIME WITHOUT OVERLAPS.
name is the constraint name if specified in the FOREIGN KEY clause. If a constraint name was not specified, name is the first column name specified in the clause followed by three periods.
Federated system users should note that some data sources do not provide the appropriate values for name and table-name-or-nickname. In these cases the appropriate fields contain values (such as "unknown") that indicate that the actual value is unknown.
The constraint can exist the data source (if the child and parent tables exist on the data source).
The statement cannot be processed.
User response
Correct the statement so the foreign key description conforms to the parent key description.
sqlcode: -538
sqlstate: 42830
SQL0539N Table or nickname name does not have a key-type key.
Explanation
- The table or nickname name was specified as a parent in a FOREIGN KEY clause but the table or nickname is not defined as a parent because it does not have a primary key.
- The ALTER TABLE statement attempted to create the FOREIGN KEY for the table name but the table or nickname does not have a primary key.
- The ALTER TABLE statement attempted to drop the primary key for the table name but the table does not have a primary key.
- The ALTER TABLE statement attempted to drop the partitioning key for the table name but the table does not have a partitioning key.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
Some data sources do not provide the appropriate values for name and key-type. In these cases the appropriate fields contain values (such as “unknown”) that indicate that the actual value is unknown.
The statement cannot be processed. A system catalog cannot be defined as a parent in a referential constraint.
User response
When creating a referential constraint, specify the primary key before specifying any foreign keys (constraints).
sqlcode: -539
sqlstate: 42888
SQL0540N The definition of table table-name is incomplete because it lacks a primary index or a required unique index.
Explanation
The table named was defined with a PRIMARY KEY clause or a UNIQUE clause. Its definition is incomplete, and it cannot be used until a unique index is defined for the primary key (the primary index) and for each set of columns in any UNIQUE clause (the required unique indexes). An attempt was made to use the table in a FOREIGN KEY clause or in an SQL manipulative statement.
The statement cannot be executed.
User response
Define a primary index or a required unique index on the table before referencing it.
sqlcode: -540
sqlstate: 57001
SQL0541W The referential, primary key, or unique constraint name is ignored because it is a duplicate constraint.
Explanation
If name refers to a referential constraint, then a FOREIGN KEY clause uses the same foreign key and parent table or the same foreign key and parent nickname as another FOREIGN KEY clause.
If name refers to a primary key or unique constraint, then one of the following situations exists.
- A PRIMARY KEY clause uses the same set of columns as a UNIQUE clause in the statement.
- A UNIQUE clause uses the same set of columns as the PRIMARY KEY clause or another UNIQUE clause in the statement.
- A PRIMARY KEY or a UNIQUE constraint on the same set of columns already exists for the table.
name is the constraint name, if specified.
If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY or UNIQUE clause followed by three periods.
Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).
Some data sources do not provide the appropriate values for name. In these cases the appropriate fields contain values (such as "unknown") that indicate that the actual value is unknown.
The indicated referential constraint or unique constraint was not created. The statement was processed successfully.
User response
No action is required. Processing can continue.
sqlcode: +541
sqlstate: 01543
SQL0542N The column named column-name cannot be a column of a primary key or unique key constraint because it can contain null values.
Explanation
The column named column-name identified in a PRIMARY KEY clause or UNIQUE clause is defined to allow null values, or an attempt was made to alter a column that is part of a PRIMARY KEY or UNIQUE constraint to allow null values.
Federated system users: some data sources do not provide the appropriate values for column-name. In these cases the appropriate fields contain values (such as "unknown") that indicate that the actual value is unknown.
The statement cannot be processed.
User response
Correct the column, primary key or unique key definition. If this error was returned as part of an alter table statement, the PRIMARY KEY or UNIQUE constraint must be removed before the column can be altered to allow null values.
sqlcode: -542
sqlstate: 42831
SQL0543N A row in a parent table cannot be deleted because the check constraint constraint-name restricts the deletion.
Explanation
The delete operation cannot be executed because the target table is a parent table and is connected with a referential constraint to a dependent table with a delete rule of SET NULL. However, a check constraint defined on the dependent table restricts the column from containing a null value.
The statement cannot be processed.
User response
Examine the foreign key and its delete rule in the dependent table and the conflicting check constraint. Change either the delete rule or the check constraint so that they do not conflict with each other.
sqlcode: -543
sqlstate: 23511
SQL0544N The check constraint constraint-name cannot be added because the table contains a row that violates the constraint.
Explanation
At least one existing row in the table violates the check constraint that is be added in the ALTER TABLE statement.
The statement cannot be processed.
User response
Examine the check constraint definition that was specified in the ALTER TABLE statement and the data in the table to determine why there is a violation of the constraint. Change either the check constraint or the data so that the constraint is not violated.
sqlcode: -544
sqlstate: 23512
SQL0545N The requested operation is not allowed because a row does not satisfy the check constraint constraint-name.
Explanation
Check constraint violations can occur on either INSERT or UPDATE operations. The resulting row violated the check constraint definition on that table.
The statement cannot be processed.
User response
Examine the data and the check constraint definition in the catalog view SYSCAT.CHECKS to determine why the INSERT or UPDATE statement failed. Change the data so that the constraint is not violated.
sqlcode: -545
sqlstate: 23513
SQL0546N The check constraint constraint-name is invalid.
Explanation
A check constraint in the CREATE TABLE or CREATE NICKNAME, ALTER TABLE, or ALTER NICKNAME statement is invalid for one or more of the following reasons:
- the constraint definition contains a subquery
- the constraint definition contains a column function
- the constraint definition contains a host variable
- the constraint definition contains a parameter marker
- the constraint definition contains a special register or a built-in function that depends on the value of a special register
- the constraint definition contains a global variable
- the constraint definition contains a variant user defined function
- the constraint definition contains a user defined function with external action
- the constraint definition contains a user defined function with the scratchpad option
- the check constraint is part of a column definition, and its check-condition contains a reference to a column name other than the column being defined.
- the constraint definition contains a dereference operation or a DEREF function where the scoped reference argument is other than the object identifier (OID) column.
- the constraint definition uses a TYPE predicate.
- the constraint definition includes a CAST specification with a SCOPE clause.
- the functional dependency is defined with attribute ENFORCED.
- a nullable column is specified in the child-set columns of the functional dependency.
- the constraint definition contains a text search function.
- the constraint definition contains an XML column.
- the constraint definition contains an XMLQUERY or XMLEXISTS expression.
The statement cannot be processed.
User response
The user response is one of the following, depending on the cause of the error:
- change the check constraint so that it does not include the listed item.
- move the check constraint definition outside the column definition, so that it becomes a table level constraint definition.
- change the functional dependency so that it does not specify the ENFORCED attribute.
- change the nullable column to not nullable in the child-set of columns of the functional dependency.
sqlcode: -546
sqlstate: 42621
SQL0548N A check constraint or generated column that is defined with check-condition-element is invalid.
Explanation
A check constraint in the CREATE TABLE or CREATE NICKNAME, ALTER TABLE or ALTER NICKNAME statement is invalid for one or more of the following reasons:
- the constraint definition contains a subquery
- the constraint definition contains a column function
- the constraint definition contains a host variable
- the constraint definition contains a parameter marker
- the constraint definition contains a special register or a built-in function that depends on the value of a special register
- the constraint definition contains a global variable
- the constraint definition contains a function that is not deterministic
- the constraint definition contains a user defined function with external action
- the constraint definition contains a user defined function with the scratchpad option
- the definition contains a user defined function with the CONTAINS SQL or READS SQL DATA option
- the definition contains a reference to a generated column which is based on an expression
- the check constraint is part of a column definition, and its check-condition contains a reference to a column name other than the column being defined
- the generated column definition contains a reference to itself
- the constraint definition contains a dereference operation or a DEREF function where the scoped reference argument is other than the object identifier (OID) column
- the constraint definition uses a TYPE predicate
- the constraint definition includes a CAST specification with a SCOPE clause
- the constraint or generated column definition contains a text search function
- the constraint definition contains an XML column
- the constraint definition contains an XMLQUERY or XMLEXISTS expression
- the generated column expression references a column for which a column mask is defined
The token within the text of the error message lists the invalid item.
The statement cannot be processed.
User response
The user response is one of the following, depending on the cause of the error:
- change the check constraint or generated column so that it does not include the listed item.
- move the check constraint definition outside the column definition, so that it becomes a table level constraint definition.
sqlcode: -548
sqlstate: 42621
SQL0549N The statement statement is not allowed for object-type1 object-name1 because the bind option DYNAMICRULES RUN is not in effect for the object-type2.
Explanation
A program attempted to issue the indicated SQL statement that is one of several SQL statements that can be dynamically prepared only in a package exhibiting run behavior. Those SQL statements are:
- Dynamic GRANT statement
- Dynamic REVOKE statement
- Dynamic ALTER statement
- Dynamic CREATE statement
- Dynamic DROP statement
- Dynamic COMMENT ON statement
- Dynamic RENAME statement
- Dynamic SET INTEGRITY statement
- Dynamic SET EVENT MONITOR STATE statement
- Dynamic TRANSFER OWNERSHIP statement
- Dynamic CREATE USAGE LIST
- Dynamic ALTER USAGE LIST
- statement
The SQL statement in error
- object-type1
PACKAGE or DBRM. DBRM is valid only in a DRDA connection.
- object-name1
If object-type1 is PACKAGE, object-name1 is the name of the package in the format 'location-id.collection-id.package-id'. If object-type1 is DBRM, object-name1 is the name of the DBRM in the format 'plan-name DBRM-name'.
- object-type2
PACKAGE or PLAN. PLAN is valid only in a DRDA connection. If object-type1 is PACKAGE, object-type2 can be either PACKAGE or PLAN (whichever is bound with DYNAMICRULES(BIND)). If object-type1 is DBRM, object-type2 is PLAN.
The SQL statement cannot be executed.
User response
Do one of the following to correct the error:
- If the SQL statement is embedded, remove it, precompile and compile the application program again, and reissue the BIND command.
- If appropriate, use the SQL statement with a package or plan that is bound with DYNAMICRULES(RUN).
- Issue the REBIND command with the DYNAMICRULES(RUN) option for the plan or package to which the SQL statement is bound.
sqlcode: -549
sqlstate: 42509
SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: authorization-ID. Operation: operation. Object: object-name.
Explanation
The operation could not be performed on the specified object. In general, this message is returned because the authorization ID does not have the required authority or privilege to perform the operation. In some cases, it is returned for an object that does not allow the operation even when the authorization ID has an administrative authority.
This message can be returned for different reasons. Here are some examples of scenarios in which this message could be returned:
- Scenario 1
An attempt to create or alter a table that has referential constraints fails because the user does not have REFERENCE privileges. In this scenario, the value of the runtime token operation is "REFERENCES" and the value of the runtime token object-name is the object that the constraint references.
- Scenario 2
Execution of a DB2 utility or CLI application fails because the user ID that created the database no longer exists or no longer has the privileges required to execute the DB2 utility program or CLI package.
- Scenario 3
Invocation of a routine fails because the user does not have EXECUTE privileges on any candidate routine in the SQL path. In this scenario, the value of the runtime token object-name is the name of a candidate routine in the SQL path.
- Scenario 4
Creation of a routine fails because the user ID that was used to bind the routine does not have the privileges necessary to issue all the statements in the routine.
- Scenario 5
Replacing an existing object by using a CREATE OR REPLACE statement failed because the user is not the owner of the object.
- Scenario 6
An attempt to complete an online move table operation fails because the user does not have the same authorization ID as the user who initiated the online move table operation.
- Scenario 7
An attempt is made to perform an unsupported operation on a system-generated statistical view that is associated with an expression-based index. It is not supported for any user to modify the privileges of or to access this type of statistical view. However, users with adequate authority and privileges on the underlying table may update the statistics of this type of statistical view, either by issuing the RUNSTATS command or by manually updating the statistics.
- In Federated environments:
This message might be returned when the following actions fail because the user does not have the necessary authority:
- Altering one of the following user-mapping options:
- REMOTE_PASSWORD
- FED_PROXY_USER
- USE_TRUSTED_CONTEXT
- Altering a trusted user context
- Creating or dropping a user mapping that has the FED_PROXY_USER option or the USE_TRUSTED_CONTEXT option
- Creating or dropping a server that has the FED_PROXY_USER option
- Altering one of the following user-mapping options:
The required authorization can be at the federated server, at the data source, or at both the federated server and the data source.
Some data sources do not provide appropriate values for the runtime tokens authorization-ID, operation, and object-name. In these cases, the message tokens might be in one of the following formats:
- <data source> AUTHID:UNKNOWN
- UNKNOWNM
- <data source> :TABLE/VIEW.
User response
If the authorization ID authorization-ID does not have the required authority or privilege, grant the necessary privilege or authority, if appropriate.
Here are responses for the scenarios described in the explanation section of this message:
- Scenario 1
Grant the REFERENCES privilege by using the statement GRANT REFERENCES ON object-name TO authorization-ID.
- Scenario 2
Rebind the DB2 utility programs or CLI packages to the database by connecting to the database and then issuing one of the following CLP commands from the bnd subdirectory of the instance directory:
- For the DB2 utilities: DB2 bind @db2ubind.lst blocking all grant public
- For CLI packages: DB2 bind @db2cli.lst blocking all grant public
If the user does not have EXECUTE privileges on the package, grant the EXECUTE privilege on the package, by use executing the GRANT statement.
- Scenario 3
Grant the EXECUTE privilege on the routine to the authorization ID by executing the GRANT statement.
- Scenario 4
Grant explicit privileges to the authorization ID on the objects that the statements within the routine are attempting to access.
- Scenario 5
Respond to scenario 5 in one of the following ways:
- Issue the statement as the user who is the owner of the object to be replaced.
- Transfer the ownership of the object by executing the TRANSFER OWNERSHIP statement.
- Scenario 6
Call the procedure again, using the same authorization ID as the user who initiated the online move table operation.
- Scenario 7
Specify a different object for the operation.
sqlcode: -551
sqlstate: 5UA0K, 42501
SQL0552N authorization-ID does not have the privilege to perform operation operation.
Explanation
Authorization ID authorization-ID attempted to perform the specified operation without the proper authorization.
Federated system users: some data sources do not provide the appropriate values for authorization-ID and <operation>. In these cases the message tokens will have the following format: "<data source> AUTHID:UNKNOWN", and "UNKNOWN" indicating that the actual values for authid and operation at the specified data source are not known.
The statement cannot be processed.
User response
Ensure that authorization-ID has the authorization to perform the operation.
Federated system users: this authorization can be at the federated server, the data source, or both.
sqlcode: -552
sqlstate: 42502
SQL0553N An object cannot be created with the schema name schema-name.
Explanation
The reason the schema name schema-name is invalid depends on the type of object that is being created.
- Table, view, index and package objects cannot be created with the schema name SYSCAT, SYSFUN, SYSPUBLIC, SYSSTAT, SYSIBM, or SYSIBMADM. It is strongly advised that schema names should not start with SYS since additional schemas starting with these letters may be reserved for exclusive use of database products in the future.
- All other types of objects (for example: user defined functions, distinct types, triggers, schemas, aliases, usage lists) cannot be created with any schema name that starts with the letters SYS.
The statement cannot be processed.
User response
Use a valid schema name or remove the explicit schema name and rerun the statement.
sqlcode: -553
sqlstate: 42939
SQL0554N An authorization ID cannot grant a privilege or authority to itself.
Explanation
An authorization ID attempted to execute a statement that would grant a privilege or authority to the authorization ID itself. If this is a GRANT statement, the authorization ID itself appears as one of the entries in the authorization ID list to which privileges, authorities, security labels, or exemptions are to be granted. If this is a CREATE TRUSTED CONTEXT or an ALTER TRUSTED CONTEXT, the authorization ID itself appears as either the value for the SYSTEM AUTHID attribute or one of the authorization names specified in the WITH USE FOR clause.
The statement cannot be processed.
User response
Remove or replace the authorization ID in the statement.
sqlcode: -554
sqlstate: 42502
SQL0555N An authorization ID cannot revoke a privilege from itself.
Explanation
An authorization ID attempted to execute a REVOKE statement where the authorization ID itself appears as one of the entries in the authorization ID list from which privileges are to be revoked.
The statement cannot be processed.
User response
Remove the authorization ID from the list.
sqlcode: -555
sqlstate: 42502
SQL0556N An attempt to revoke a privilege, security label, exemption, or role from authorization-ID was denied because authorization-ID does not hold this privilege, security label, exemption, or role.
Explanation
The privilege, security label, exemption, or role cannot be revoked because authorization-ID does not hold the privilege, security label, exemption, or role.
The statement cannot be processed.
User response
Change the REVOKE statement to conform to the REVOKE rules and resubmit the statement. Ensure that when a REVOKE statement lists several privileges, security labels, exemptions, or roles to be revoked and a list of authorization IDs that each authorization ID possesses at least one of the specified privileges, security labels, exemptions, or roles.
sqlcode: -556
sqlstate: 42504
SQL0557N The specified combination of privileges cannot be granted or revoked.
Explanation
- The GRANT or REVOKE statement contains a combination of privileges that are of different classes. The privileges must all be of one class. Examples are DATABASE, PLAN, or TABLE.
- The GRANT statement attempted to grant a privilege for a view that is not allowed. ALTER, INDEX and REFERENCES cannot be granted for a view.
The statement cannot be processed.
User response
Correct and resubmit the statement.
sqlcode: -557
sqlstate: 42852
SQL0558N An attempt to revoke a privilege from authorization-ID was denied because authorization-ID holds control privilege.
Explanation
authorization-ID holds the control privilege. The privilege to be revoked is implicitly provided by the control privilege, so it cannot be revoked unless the control privilege is also revoked.
The valid values for control are as follows:
- DBADM
- CONTROL
- CREATE NOT FENCED ROUTINE
The statement cannot be executed. No privileges are revoked.
User response
Revoke the control privilege, if desired.
sqlcode: -558
sqlstate: 42504
SQL0562N The statement was not executed because the specified database or schema privileges cannot be granted to group PUBLIC, directly or indirectly through a role.
Explanation
You can grant privileges to user or groups by issuing the GRANT statement.
This message is returned when an attempt is made to grant unsupported privileges to the group PUBLIC, directly or indirectly by using a role.
User response
No response is required.
It is not supported to grant administrative authorities, such as DBADM or SCHEMAADM, to PUBLIC.
sqlcode: -562
sqlstate: 42508
SQL0567N The operation failed because the specified authorization name does not meet the identifier naming rules. Authorization name: authorization-name.
Explanation
Rules exist for the naming of all database objects, user names or authorization names, passwords, groups, files, and paths. Some of these rules are specific to the platform you are working on.
This message is returned when a specified authorization name does not satisfy the identifier naming rules. For example this message can be returned when an authorization name contains characters other than a through z, A through Z, 0 through 9, and the supported special characters.
The statement cannot be processed.
User response
Perform the operation again, specifying an authorization name that satisfies the identifier naming rules.
sqlcode: -567
sqlstate: 42602
SQL0569N Authorization ID authorization-name does not uniquely identify a user, a group or a role in the system.
Explanation
The authorization ID specified by the GRANT or REVOKE statement does not uniquely identify a user, a role, or a group in the security namespace. The reference to authorization-name is ambiguous. Note that when using DCE security, the USER, GROUP or ROLE keyword is always required.
User response
Change the statement to explicitly specify the USER, GROUP or ROLE keyword to uniquely identify the specified authorization id.
sqlcode: -569
sqlstate: 56092
SQL0570W Not all requested privileges on object object-name of type object-type were granted.
Explanation
A GRANT operation was attempted on object object-name of type object-type, but some or all of the privileges were not granted. The authorization ID that issued the statement does not have all of the privileges to be granted with the GRANT option or does not have ACCESSCTRL or SECADM authority.
All valid requested privileges were granted.
User response
Obtain the required authority and try the operation again.
sqlcode: +570
sqlstate: 01007
SQL0572N Package pkgname is inoperative.
Explanation
The package pkgname is marked as inoperative and must be explicitly rebound (without specifying RESOLVE CONSERVATIVE) before it can be used. This package cannot be used because one or more user-defined functions that it depends upon have been dropped.
User response
Explicitly rebind the named package using either the REBIND (without specifying RESOLVE CONSERVATIVE) or the BIND command.
sqlcode: -572
sqlstate: 51028
SQL0573N A column list specified in the references clause of constraint name does not identify a unique constraint of the parent table or nickname table-name.
Explanation
A list of column names was specified in the references clause for the constraint identified by name that does not match the column names of the primary key or any unique key of the referenced table table-name.
name is the constraint name, if specified. If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY clause followed by three periods.
The statement cannot be processed.
User response
Correct the column list in the references clause or add the unique constraint to the referenced table or nickname.
sqlcode: -573
sqlstate: 42890
SQL0574N DEFAULT value or IDENTITY attribute value is not valid for column column-name in table table-name. Reason code: reason-code.
Explanation
- 1
- The value is not assignable to the column because the constant does not conform to the format for a constant of that data type, the value has an incorrect length or precision, or the function returns an incorrect data type.
- 2
- A floating-point constant is specified and the column is not a floating point data type
- 3
- A decimal constant is specified and non-zero digits would be truncated when assigned to the column
- 4
- The value is more than 254 bytes, including quotes for strings, introducer characters such as the X for a hex constant, fully qualified function names, and parentheses. Non-significant blanks in the value are ignored. In an unequal code page environment, the value may be more than 254 bytes due to expansion of the string in the database code page.
- 5
- The USER special register is specified and the length attribute of the character string data type is less than 8.
- 6
- A datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP) is specified and does not match the data type of the column.
- 7
- A function was specified that is not supported. The specified function must be system-generated cast function or one of the built-in functions BLOB, DATE, TIME, or TIMESTAMP.
- 8
- The argument to a datetime function was not a string constant or the corresponding datetime special register.
- 9
- A system-generated cast function was specified and the column is not defined with a user-defined distinct type.
- 10
- A value with non-zero scale was specified for the START WITH or INCREMENT BY option of an identity column.
- 11
- A special register is specified as the DEFAULT value and the length attribute of the character string data type is less than 128.
- 12
- A decimal floating-point constant is specified and the column is not a decimal floating point data type.
- <0
- Any reason code that is less than zero is an SQLCODE. The error in the DEFAULT value specification can be determined by checking the error message corresponding to this SQLCODE.
The statement cannot be processed.
User response
Correct the DEFAULT value or IDENTITY attribute value based on the reason code that was returned.
sqlcode: -574
sqlstate: 42894
SQL0575N View or materialized query table name cannot be used because it has been marked inoperative.
Explanation
- COMMENT ON
- DROP VIEW or DROP TABLE
- CREATE ALIAS
- CREATE VIEW or CREATE TABLE
The statement cannot be processed.
User response
If name is a view, recreate the view by issuing a CREATE VIEW statement using the same view definition as the inoperative view. If name is a materialized query table, recreate the materialized query table by issuing the CREATE TABLE statement using the same materialized query table definition as in the inoperative materialized query table.
sqlcode: -575
sqlstate: 51024
SQL0576N Alias name cannot be created for name2 as it would result in a repetitive alias chain.
Explanation
The alias definition of name on name2 would have resulted in a repetitive alias chain which could never be resolved. For example, "alias A refers to alias B which refers to alias A" is a repetitive alias chain which could never be resolved.
The statement cannot be processed.
User response
Change the alias definition for name or revise the definition of one of the other alias definitions in the alias chain to avoid a repetitive chain.
sqlcode: -576
sqlstate: 42916
SQL0577N User defined routine routine-name (specific name specific-name) attempted to modify data but was not defined as MODIFIES SQL DATA.
Explanation
The program used to implement the body of a routine is not allowed to modify SQL data.
User response
Remove any SQL statements that modify data then recompile the program. Investigate the level of SQL allowed as specified when defining the routine.
sqlcode: -577
sqlstate: 38002
sqlstate: 42985
SQL0579N Routine routine-name (specific name specific-name) attempted to read data but was not defined as READS SQL DATA or MODIFIES SQL DATA.
Explanation
The program used to implement the body of a routine is not allowed to read SQL data.
User response
Remove any SQL statements that read data then recompile the program. Investigate the level of SQL allowed as specified when defining the routine.
sqlcode: -579
sqlstate: 38004
sqlstate: 42985
SQL0580N The result-expressions of a CASE expression cannot all be NULL.
Explanation
There is a CASE expression in the statement that has all the result-expressions (expressions following THEN and ELSE keywords) coded with the keyword NULL.
The statement cannot be processed.
User response
Change the CASE expression to include at least one result-expression that is other than the keyword NULL.
sqlcode: -580
sqlstate: 42625
SQL0581N The data types of the result-expressions of a CASE expression or DECODE function are not compatible.
Explanation
There is a CASE expression or a DECODE function in the statement that has result-expressions (expressions following THEN and ELSE keywords for CASE expressions) that are not compatible.
The data type of a CASE expression or the result of a DECODE function is determined using the "Rules for Result Data Types" on the result-expressions.
The statement cannot be processed.
User response
Correct the result-expressions so that they are compatible.
sqlcode: -581
sqlstate: 42804
SQL0582N A CASE expression in a VALUES clause, IN predicate, GROUP BY clause, or ORDER BY clause cannot include a quantified predicate, an IN predicate using a fullselect, or an EXISTS predicate.
Explanation
- a quantified predicate (one using SOME, ANY, or ALL),
- an IN predicate using a fullselect or
- an EXISTS predicate
- a VALUES clause
- an IN predicate
- a GROUP BY clause or
- an ORDER BY clause.
Such CASE expressions are not supported. The CASE expression may be part of a function written in SQL.
The statement cannot be processed.
User response
Remove the use of the quantified predicate, IN predicate, or EXISTS predicate from the CASE expression. If the CASE expression is part of a function, the query may need to be written without the function that causes the error.
sqlcode: -582
sqlstate: 42625
SQL0583N The use of routine or expression name is invalid because it is not deterministic or has an external action.
Explanation
The routine (function or method) or expression is defined as non-deterministic or as having external action. This is not supported in the context in which it is used. The contexts in which these are not valid are:
- as the first operand of a BETWEEN predicate
- in the expression prior to the first WHEN keyword in a simple-case-expression.
- as the first argument of a DECODE function
- as the argument of a RATIO_TO_REPORT function
- in an expression of a GROUP BY clause
- in an expression of an ORDER BY clause (external action only)
- in an expression of a PARTITION BY clause
- in a FILTER clause of a user-defined predicate specification or an index extension definition
- in the default expression for a global variable or parameter of a procedure definition
The statement cannot be processed.
User response
If the use of a non-deterministic or external action routine or expression was not intended, substitute a routine without these characteristics. If the behavior associated with the non-deterministic or external action routine or expression is intentional, use the alternate form of the statements that make that intent explicit.
- Instead of the BETWEEN predicate, use the corresponding statement using the equivalent combination of comparison predicates (a>=b and a<=c instead of a between b and c).
- Instead of a simple-when-clause or DECODE function, use the corresponding searched-when-clause where the routine would get specified in each search-condition.
- Push the argument of the RATIO_TO_REPORT function into a nested query.
- Remove the non-deterministic or external action routine or expression from the GROUP BY clause. If grouping is desired on a column of the result that is based on a non-deterministic or external action routine or expression use a nested table expression or a common table expression to first provide a result table with the expression as a column of the result.
- Remove the external action routine from the ORDER BY clause. If the column is part of the result set of the query, change the expression in the ORDER BY clause to the simple-integer or simple-column-name form of the sort key.
- Remove the non-deterministic or external action routine or expression from the FILTER clause.
- Remove the non-deterministic or external action routine or expression from the default clause for the global variable or parameter of a procedure definition.
sqlcode: -583
sqlstate: 42845
SQL0584N The statement failed because of an invalid use of the NULL keyword or the DEFAULT keyword.
Explanation
When creating a table, you can define default values for one or more columns. When you insert or merge data into a table with this default behaviour defined for some columns, specifying "DEFAULT" in a VALUES clause will cause default values to be inserted or merged into those columns.
You can indicate an absence of data for a particular column in a statement by using NULL keyword,
There are requirements and restrictions for how the DEFAULT keyword and the NULL keyword can be used. Here are just a few examples:
- Example 1: The DEFAULT keyword can be used in a VALUES clause with an INSERT statements and MERGE statements.
- Example 2: A VALUES clause that is not part of an INSERT statement must have a value other than NULL in at least one row for each column, unless the data type for the NULL can be resolved based on the context of its use in the statement.
- Example 3: In Federated environments, the DEFAULT keyword cannot be used in a VALUES clause of an INSERT statement where the object is a nickname.
User response
Modify the statement so that the NULL keyword or the DEFAULT keyword is not being used in an unsupported way, and then resubmit the statement.
sqlcode: -584
sqlstate: 42608
SQL0585N The schema name schema-name appears more than once in the set special-register statement.
Explanation
The SET statement for special register special-register includes the schema schema-name more than once.
The statement cannot be processed.
User response
Verify the list in the SET statement that contains the duplicate. If the error is only in entering a schema-name incorrectly, which happens to duplicate another entry, enter the schema name correctly and reissue the statement. If the entry is really a duplicate no action is required.
sqlcode: -585
sqlstate: 42732
SQL0586N The total length of special-register special register cannot exceed length.
Explanation
The special-register is defined as a length smaller than the value specified on the SET statement. The content of the string includes each schema name delimited with double quotes and separated from the next schema name by a comma. The total length of the string of all schema names in the special register cannot exceed the maximum length of the special register. A SET PATH or SET CURRENT PACKAGE PATH statement could result in this message.
The statement or command cannot be processed.
User response
Remove schema names to reduce the total length to fit the length of the special register. If the statement that failed was SET PATH and if all the schema names are required, it may be necessary to consolidate some user-defined functions, procedures, methods, or distinct types, so that fewer schema names are required on the SQL PATH.
sqlcode: -586
sqlstate: 42907
SQL0590N Name name specified in context context-tag is not unique.
Explanation
Name name is specified as a parameter, SQL variable, cursor, label, or condition in a context defined by context-tag. The name is not unique.
If context-tag is "BEGIN...END", then the context of the error is a dynamic SQL compound statement. Otherwise, the context of the error is a trigger or routine and context-tag is the trigger name or routine name containing the compound statement.
- If name is a parameter name, then it must be unique within the parameter list and the EXPRESSION AS clause of a routine.
- If name is a state variable name, then it must be unique within the state variable list.
- If name is an SQL variable name, cursor name or condition, then it must be unique within a compound statement .
- A label must be unique within a compound statement and different from the labels of the statements in which it is nested.
User response
Change the name so that it is unique.
sqlcode: -590
sqlstate: 42734
SQL0593N NOT NULL must be specified for a ROW CHANGE TIMESTAMP column, ROW BEGIN column, ROW END column, or column of a period. column-name.
Explanation
Row change timestamp, row-begin, and row-end columns do not support null values. When such a column is defined on a CREATE TABLE or ALTER TABLE statement, the NOT NULL clause must be specified for these columns.
A column of a period must be defined as NOT NULL.
The statement cannot be executed.
User response
Change the statement to specify NOT NULL for the column column-name.
sqlcode: -593
sqlstate: 42831
SQL0595W Isolation level requested-level has been escalated to escalated-level.
Explanation
The isolation level specified is not supported by DB2. It has been escalated to the next higher level of isolation supported by DB2.
User response
To avoid this warning, specify an isolation level which is supported by DB2. DB2 supports isolation levels Repeatable Read (RR), Read Stability (RS), Cursor Stability (CS), and Uncommitted Read (UR).
sqlcode: +595
sqlstate: 01526
SQL0597N Unable to retrieve a DATALINK value. Reason code = reason-code.
Explanation
- 01
- The DB2 Data Links Manager does not authorize the DB2 user to retrieve a DATALINK value embedded with a write token for modifying the DATALINK value referenced file.
User response
- 01
- Contact the DB2 Data Links Manager administrator to grant the write access privilege to this file.
sqlcode: -0597
sqlstate: 42511
SQL0598W Existing index name is used as the index for the primary key or a unique key.
Explanation
An index was required for an ALTER TABLE operation that defined a primary key or a unique key, and the indicated index matches the required index.
If the index description of a nonpartitioned primary key or a nonpartitioned unique key index identifies the same set of columns (in any order) as the primary or unique key and if it is specified as unique, then it is considered to match, regardless of the ascending or descending specifications.
However, partitioned indexes whose columns are not a superset of the partitioning key columns are not considered to match.
The statement is processed successfully.
User response
No action is required.
sqlcode: +598
sqlstate: 01550
SQL0599W Comparison functions are not created for a distinct type based on a long string data type.
Explanation
Comparison functions are not created for a distinct type based on a long string data type (BLOB, CLOB, DBCLOB, LONG VARCHAR, or LONG VARGRAPHIC) since the corresponding functions are not available for these built-in data types.
This is a warning situation. The statement is processed successfully.
User response
No action is required.
sqlcode: +599
sqlstate: 01596
SQL0600N A routine routine-name could not be generated because of a duplicate signature or because it would override an existing routine.
Explanation
During the CREATE or ALTER operation, a system-generated cast function, observer method, mutator method, or constructor function could not be created because another function or method with the same name and signature already exists in the schema, or because the method or function would override an existing method.
User response
Choose another name for the user-defined type, attribute, or cast function that causes the conflict, or drop the function or method with the same name as the function or method that could not be generated.
sqlcode: -600
sqlstate: 42710
SQL0601N The name of the object to be created is identical to the existing name name of type type.
Explanation
The CREATE or ALTER statement tried to create or add an object name when an object of type type already exists with that name on the application server or in the same statement.
If type is FOREIGN KEY, PRIMARY KEY, UNIQUE, or CHECK CONSTRAINT, the name is the constraint name specified in the ALTER NICKNAME, ALTER TABLE, CREATE NICKNAME or CREATE TABLE statement or generated by the system.
If type is ROLE, the name is the role name specified in the CREATE or ALTER ROLE statement.
If type is DATA PARTITION, the name is the data partition name specified in the ALTER TABLE or CREATE TABLE statement.
This error can also occur when registering an XML schema repository object using a REGISTER command or the one of the procedures XSR_REGISTER, XSR_DTD, or XSR_EXTENTITY. The error occurs when the name for the XSROBJECT already exists.
Federated system users: some data sources do not provide the appropriate values for the name and type message tokens. In these cases, name and type will have the following format: "OBJECT:<data source> TABLE/VIEW", and "UNKNOWN" indicating that the actual values at the specified data source are not known.
If type is permission, name is the permission name specified in the CREATE PERMISSION statement.
If type is mask, name is the mask name specified in the CREATE MASK statement.
If type is member subset, name is the member subset name specified in the CALL SYSPROC.WLM_CREATE_MEMBER_SUBSET statement.
The statement cannot be processed. No new object is created, and the existing object is not altered or modified.
User response
Either drop the existing object or choose another name for the new object.
Federated system users: if the statement is a CREATE FUNCTION MAPPING or a CREATE TYPE MAPPING statement, the user can also consider not supplying a type mapping name and the system will automatically generate a unique name for this mapping.
sqlcode: -601
sqlstate: 42710
SQL0602N The index was not created or altered because too many columns, periods, or expression-based keys were specified.
Explanation
There is an upper limit to the number of columns that can be included in an index. The number of columns that are allowed in a given index is influenced by factors such as: the type of table, and whether the columns that are being included in the index use random ordering.
This message is returned when an attempt is made to create an index, alter an index, or create an index extension, and the resulting number of columns in the index would exceed the upper limit.
Federated system users:
- The limit on the number of columns that are allowed in an index varies for different data sources.
- This problem might be detected on the federated server or on the data source.
User response
Review the restrictions related to the maximum number of columns in an index. Then modify the index definition to have fewer columns.
Federated system users: change the index definition to conform to the column limit for the data source.
sqlcode: -602
sqlstate: 54008
SQL0603N A unique index cannot be created because the table contains data that would result in duplicate index entries.
Explanation
The index defined in the CREATE INDEX statement could not be created as unique, because the specified table already contains rows that duplicate the values of the identified columns and periods. If defining an index on an XML column, there may be duplicate index values generated from a single XML document.
If a partitioned table has a newly attached partition (that is, a partition with a value of 'A' in the STATUS column in the SYSDATAPARTITIONS catalog table), the duplicate index entry may exist in the newly attached partition. The CREATE INDEX statement creates an index partition on the newly attached table partition, even if you cannot yet view the data in that partition.
Federated system users: this situation may also be detected by the data source.
The statement cannot be processed. The specified index is not created.
User response
Examine the data to determine if the duplicate data is permitted. Alternatively, consider creating a non-UNIQUE index.
If the index is partitioned and there are newly attached partitions on the table, run the SET INTEGRITY statement to bring the newly attached partitions online. The duplicate data might be removed by the SET INTEGRITY statement for violating other constraints, so you should issue the CREATE INDEX statement again. If a unique index is not created again, then check for data that would result in duplicate index entries.
sqlcode: -603
sqlstate: 23515
SQL0604N The length, precision, or scale attribute for column, distinct type, structured type, array type, attribute of structured type, routine, cast target type, type mapping, or global variable data-item is not valid.
Explanation
There is an error in the data type specification in a CREATE or ALTER statement, or a CAST specification. An invalid length, precision or scale attribute may have been specified, or it may be that the data type itself is incorrect or not permitted in this context. The location of the error is given by data-item as follows:
- For a CREATE or ALTER TABLE statement, data-item gives the name of the column containing the error or the data type containing an error. If the column data type is a structured or XML data type, then the INLINE LENGTH value must be at least 292 and cannot exceed 32673. For a LOB data type, the INLINE LENGTH value must be at least the size of the LOB descriptor (see the CREATE TABLE statement) and cannot exceed 32673
- For a CREATE FUNCTION statement, data-item is a token that identifies the area of the problem in the statement. For example, "PARAMETER 2" or "RETURNS" or "CAST FROM". In some cases, it may also be the data type containing the error.
- For a CREATE DISTINCT TYPE statement, data-item gives the name of the type being defined or the source data type containing the error.
- For a CREATE TYPE(array) statement, data-item gives the data type containing an error. The integer value specified inside the square brackets must be an integer greater than or equal to 1 and not larger than 2147483647.
- For a CREATE or ALTER TYPE statement, data-item gives the type of the attribute containing the error or the name of the structured type having an incorrect inline length value. The inline length cannot be smaller than 292 and the size returned by the constructor function for the structured type.
- For a CREATE VARIABLE statement, data-item gives the name of the variable having an incorrect data type. The data type of a global variable can be of any built-in data type with the exception of LONG types, LOBs, ARRAY, and structured types. Distinct types and reference types are supported.
- For CAST( expression AS data-type ), data-item is "CAST" or the data type containing the error.
- For XMLCAST( expression AS data-type ), data-item is "XMLCAST" or the data type containing the error.
- For a reverse type mapping, [p..p] expression cannot be used for the remote data type. For example, the following statement (reverse type mapping) is incorrect.
CREATE TYPE MAPPING tm1 FROM SERVER drdasvr TYPE CHAR([1..255]) TO SYSIBM.VARCHAR
Whereas, the following statement (forward type mapping) is correct.
CREATE TYPE MAPPING tm1 TO SERVER drdasvr TYPE CHAR([1..255]) FROM SYSIBM.VARCHAR
Federated system users: if the statement is a CREATE TYPE MAPPING statement, an attempt was made to create a type mapping where a type attribute for either the local data type or the remote data type is not valid. Possible reasons include:
- The local length/precision is set to 0 or a negative value.
- The length/precision attribute is specified for data types such as date/time/timestamp, float, or integer.
- The scale attribute is specified for data types such as character, date/time/timestamp, float, or integer.
- The FOR BIT DATA clause is specified for a non-character type.
- The remote precision is set to 0 for remote types other than Informix datetime.
- An invalid field qualifier is being used in a type mapping for an Informix datetime type.
- An ending value is lower than the starting value in a precision/scale range.
The statement cannot be processed.
User response
Correct the syntax and try again.
sqlcode: -604
sqlstate: 42611
SQL0605W The index was not created because an index name with a matching definition already exists.
Explanation
A CREATE INDEX statement attempted to create a new index which matches an existing index definition.
Two index definitions match if they identify the same columns in the same order, with the same ascending or descending specifications, and both enforce uniqueness or only the new index does not enforce uniqueness.
Two index definitions also match if they identify the same columns in the same order, with the same or reverse ascending or descending index key order, and at least one index supports both forward and reverse scans.
For partitioned tables, two index definitions do not match if one is partitioned and the other is nonpartitioned, even if all other specifications match. Partitioned and nonpartitioned indexes of otherwise similar definitions can coexist on the same table.
The new index was not created.
User response
No action is required unless the existing index name is not a suitable index. For example, the existing index name is not a suitable index if it does not allow reverse scans, and the required one does (or vice versa). In this case, the index name must be dropped before the required index can be created.
sqlcode: +605
sqlstate: 01550
SQL0606N The COMMENT ON or LABEL ON statement failed because the specified table or column is not owned by owner.
Explanation
An attempt was made to comment or label on a table or column which does not exist or is not owned by the owner specified in the message text.
Processing of the SQL statement has ended.
User response
Correct the statement. Try again.
sqlcode: -606
sqlstate: 42505
SQL0607N operation is not defined for system objects.
Explanation
The operation specified in the SQL statement cannot be performed on system objects. One of the following was attempted:
- ALTER, DROP or TRANSFER OWNERSHIP of a system owned object such as a system catalog table, built-in function or built-in data type
- COMMENT ON a system owned built-in function.
- INSERT, DELETE, or TRUNCATE on a system catalog table.
- UPDATE directly on a system catalog table. Some columns of a subset of system catalog tables are updatable. For UPDATE operation on these catalog tables, updatable views in SYSSTAT schema should be used. For a description of updatable catalog views (SYSSTAT views), see the SQL Reference.
- CREATE or DROP of an index on a system table
- CREATE of a trigger on a system table
- A non-updatable system table was identified in the FROM clause of a SELECT statement containing a FOR UPDATE clause. For a list of updatable system catalogs see the SQL Reference.
- DROP or ALTER of a system table space.
- DROP or ALTER of a system database partition group.
- DROP of a system default workload.
- ALTER of SYSDEFAULTUSERWORKLOAD with ENABLE, DISABLE, POSITION, ADD, or DROP option specified.
- ALTER of SYSDEFAULTADMWORKLOAD with option other than COLLECT specified.
- ALTER of a workload with SYSDEFAULTADMWORKLOAD or SYSDEFAULTUSERWORKLOAD specified as the value of the POSITION BEFORE or AFTER option.
- REDISTRIBUTE of the IBMCATGROUP or IBMTEMPGROUP database partition group.
- Transfer ownership on a schema whose name begins with "SYS".
- CREATE of a work action set or a threshold for the default administration workload SYSDEFAULTADMWORKLOAD.
- INSERT to the SYSTOOLS.ADMINTASKS table used by the Administrative Task Scheduler (ATS).
- UPDATE of a restricted column in the SYSTOOLS.ADMINTASKS table used by the Administrative Task Scheduler (ATS).
- ALTER of the SYSTOOLS.ADMINTASKS table used by the Administrative Task Scheduler (ATS).
An online admin move table operation cannot be performed on any tables in a reserved schema.
The statement cannot be processed.
User response
Do not attempt to modify any system objects except for those columns of the system catalog tables which are updatable through updatable SYSSTAT views. See the SQL Reference for more information.
sqlcode: -607
sqlstate: 42832
SQL0612N name is a duplicate name.
Explanation
A statement was issued with the same name appearing more than once where duplicates are not allowed. Where these names appear varies depending on the type of statement.
- CREATE TABLE statements cannot have the same column name defined for two columns.
- CREATE TABLE statements specifying table partitioning cannot define an identical data partition name for two data partitions.
- ALTER TABLE statements cannot add or attach a data partition to a table specifying a partition name that is identical to a partition name of an existing data partition in the table.
- CREATE VIEW statements or common table expression definitions cannot have the same column name in the column name list. If no column name list is specified, then the column names of the columns in the select list of the view must be unique.
- ALTER TABLE statement cannot add a column to a table using the name of a column that already exists or is the same as another column being added. Furthermore, a column name can only be referenced in one ADD, DROP COLUMN, or ALTER COLUMN clause in a single ALTER TABLE statement.
- CREATE INDEX cannot have a column name specified more than once as part of the index key or the INCLUDE columns of the index.
- CREATE TRIGGER cannot have a column name specified more than once in the list of columns that cause an update trigger to be activated.
- CREATE TABLE OF statements cannot have the same name defined for the REF IS column and any attribute of the structured type.
- CREATE TYPE statements cannot have the same name defined for two fields in a ROW data type or two attributes in a structured type. Field and attribute names must be unique within the type and all supertypes.
- ALTER TYPE statements cannot add an attribute to a structured type using the name of an attribute that already exists in the type or any of its subtypes or is the same as another attribute being added. Also, the name of the attribute may not be the same as the REF IS column in any table created from the structured type. Furthermore, an attribute name can only be referenced in one ADD or DROP ATTRIBUTE clause in a single ALTER TYPE statement.
- CREATE INDEX EXTENSION statements cannot have the same name defined for two parameters.
- A column name can only be referenced in one ALTER COLUMN clause in a single ALTER NICKNAME statement.
- An XMLQUERY, XMLEXISTS, or XMLTABLE argument list contained two arguments with the same name.
- An XMLTABLE column list contains two columns with the same name.
- The typed-correlation clause of a SELECT statement cannot contain two columns with the same name.
- CREATE or ALTER TABLE statements must not specify the same period more than once in a unique key.
- CREATE or ALTER TABLE statements must not define a period and a column with the same name.
- A period cannot be dropped at the same time as it is being added or altered in the same ALTER TABLE statement. For example, the same period name cannot appear in both a DROP PERIOD clause and an ADD PERIOD clause in the same ALTER TABLE statement.
The statement cannot be processed.
User response
Specify unique names as appropriate for the type of statement.
sqlcode: -612
sqlstate: 42711
SQL0613N The primary key, unique key, or table partitioning key identified by name is too long or has too many columns and periods.
Explanation
This error is caused by one of the following:
- The sum of the internal lengths of columns in the PRIMARY KEY clause or a UNIQUE clause identified by name exceeds the index key length limit or the number of columns and two times the number of periods exceeds the maximum of 64. Also, a primary key or a unique key cannot be defined using a LONG VARCHAR column. If the primary key or unique constraint is defined on a typed table, there is additional index overhead that reduces the maximum number of specified columns to 63 and limits the length by 4 bytes.
- The number of columns in the PARTITION BY clause exceeds the maximum of 16.
name is the constraint name, if specified, of the primary key or unique constraint. If a constraint name was not specified, name is the first column name specified in the primary key or unique constraint clause followed by three periods.
The index key length limit is based on the page size of the tablespace used by the index:
Max Key Length Page size
-------------- ---------
1K 4K
2K 8K
4K 16K
8K 32K
For a table partitioning key, name is the column name of the column which exceeds the limit.
The statement cannot be processed.
User response
Modify the primary key, unique key or table partitioning key definition by eliminating one or more key columns or periods to conform to the column limit of 64 and the key length limit.
sqlcode: -613
sqlstate: 54008
SQL0614N The index or index extension index-name cannot be created or altered because the combined length of the specified columns is too long.
Explanation
The index could not be created or altered because the sum of the key column internal lengths would exceed the index key length limit. Also, an index cannot be created using a LONG VARCHAR, LONG VARGRAPHIC, or LOB column. If the index is defined on a typed table, there is additional index overhead that reduces the maximum length by 4 bytes. The index may be altered by an ALTER TABLE or ALTER NICKNAME statement that alters the data type of one or more columns.
An index extension could not be created because the sum of the columns returned by the GENERATE KEY function would exceed the index key length limit.
The index key length limit is based on the page size of the tablespace used by the index:
Max Key Length Page size
-------------- ---------
1K 4K
2K 8K
4K 16K
8K 32K
The statement cannot be processed. The specified index or index extension was not created or the table or nickname could not be altered.
User response
To modify the index definition or alter column, eliminate one or more key columns and reduce the key length to the permitted maximum. For an index extension definition, specify a different GENERATE KEY function, or redefine the function to reduce the length of the returned row.
sqlcode: -614
sqlstate: 54008
SQL0615N The object object-name of type object-type cannot be dropped because it is currently in use.
Explanation
A DROP statement of an object cannot be issued when it is in use.
The statement cannot be processed. The object is not dropped.
User response
Close any cursors that depend either directly or indirectly on the object object-name and resubmit the statement.
For temporary table spaces, resubmit the statement when the table space is not in use.
sqlcode: -615
sqlstate: 55006
SQL0620N The CREATE TABLE statement failed because user-id has no private, recoverable dbspaces with less than 255 tables.
Explanation
The dbspace name was not specified in the CREATE TABLE statement, so the database manager tried to find a private dbspace owned by user-id. This message will appear under one of the following conditions:
- There were no private dbspaces found in the DB2 for VM database for the user-id.
- There were one or more private dbspaces found for the user-id, but each contained 255 tables.
- The private dbspaces were located in nonrecoverable storage pools. Only private dbspaces that exist in recoverable storage pools will be available when the CREATE TABLE statement does not specify the dbspace name.
Processing of the SQL statement has ended.
User response
Suggestions for the three possible conditions are:
- Acquire a private dbspace in a recoverable storage pool. You may require a database administrator's assistance.
- Drop a table in a private dbspace residing in a recoverable storage pool to free up an entry, or take the action indicated in (1).
- If you want to create the table in a dbspace existing in a non-recoverable storage pool, then specify the dbspace name on the CREATE TABLE command. Otherwise, take the action indicated in (1).
Then rerun the CREATE TABLE statement.
If appropriate, acquire a private dbspace for the user.
sqlcode: -620
sqlstate: 57022
SQL0622N The clause clause is invalid for this database.
Explanation
The clause indicated is invalid because it is incompatible with the defined characteristics for this database.
- CCSID ASCII and PARAMETER CCSID ASCII cannot be specified when connected to a database created using a Unicode code page.
- CCSID UNICODE and PARAMETER CCSID UNICODE cannot be specified when connected to a database created using a non-Unicode code page before the alternate collating sequence is specified in the database configuration.
- CCSID UNICODE or PARAMETER CCSID UNICODE cannot be specified in this statement when connected to a database created using a non-Unicode code page.
- FOR SBCS DATA can only be specified when connected to a database created using a single byte code page.
- FOR MIXED DATA can only be specified when connected to a database created using a double byte or Unicode code page.
- The IN database-name.table-space-name or IN DATABASE database-name clause was specified using a database name that does not match the name of the database to which the application is currently connected.
The statement cannot be processed.
User response
Change or remove the clause and re-issue the SQL statement.
To allow Unicode objects in a non-Unicode database, update the database configuration to specify the alternate collating sequence (ALT_COLLATE).
sqlcode: -622
sqlstate: 56031
SQL0623N A clustering index already exists on table name.
Explanation
The CREATE INDEX statement would create a second clustering index on the specified table. A given table can have only one clustering index.
The statement cannot be executed.
User response
Check to determine the identity and validity of the existing clustering index on table name. Consider creating the index without the CLUSTER attribute.
sqlcode: -623
sqlstate: 55012
SQL0624N Table name already has a primary key or unique constraint using the specified columns and periods.
Explanation
A primary key or unique constraint cannot be defined in an ALTER TABLE statement because the indicated table already has a key or constraint of this type.
The statement cannot be executed.
User response
A table cannot have more than one primary key. A table cannot have a unique constraint that duplicates an existing constraint.
sqlcode: -624
sqlstate: 42889
SQL0628N Multiple or conflicting keywords involving the clause-type clause are present.
Explanation
There are several possible reasons why this condition might have been diagnosed for the statement. The cause is indicated by the value of clause-type. The possible reasons include:
- The keyword might not be specified in the same statement as some other keyword.
- The keyword might be part of a sequence of keywords where the order in which they are specified is not enforced. A keyword in such a sequence might have been specified with the contradicting keyword also specified.
- The keyword might appear more than once with different associated values.
- The keyword might require the specification of other particular keywords in the same statement which were not specified.
- When ALTERING an object, a keyword was specified that conflicts with an existing property of the object.
- When ALTERING a partitioned table, the ADD, ATTACH and DETACH clauses are not supported with any other clauses.
- When CREATING or DECLARING a table with the LIKE table-name clause where table-name identifies a nickname, the INCLUDING COLUMN DEFAULTS clause has no effect and column defaults are not copied.
- The parameter mode OUT or INOUT was used to define a parameter in a function that does not support OUT or INOUT parameters.
- A generic table function was being created using the CREATE PROCEDURE statement, with the RETURNS GENERIC TABLE clause, and one of the following errors occurred:
- A language other than JAVA was specified with the LANGUAGE clause
- A parameter style other than DB2GENERAL was specified with the PARAMETER STYLE clause
- When CREATING a trigger, the same trigger event was specified more than once.
- When CREATING a table space, the 'USING STOGROUP' clause cannot be used with DMS or SMS table spaces
- When CREATING a table space, STOGROUP cannot be changed for temporary automatic storage table spaces
- When CREATING a table space, DATA TAG cannot be set for a temporary table space
- When CREATING a table space, DATA TAG cannot be set for the system catalog table space
- When CREATING or ALTERING a table VERSIONING cannot be specified with LIKE for as-result-table or materialized-query-definition.
- When ALTERING a random distribution table using random by generation method, the DROP DISTRIBUTION clause is not supported.
User response
Check that the statement conforms to the syntax and rules defined for the statement. Correct any invalid occurrences of duplicate or conflicting keywords.
sqlcode: -628
sqlstate: 42613
SQL0629N SET NULL cannot be specified because either the column or FOREIGN KEY name cannot contain null values.
Explanation
The SET NULL option of the ALTER TABLE statement or indicated FOREIGN KEY clause is not valid. For an ALTER TABLE statement, the column name cannot be altered to not allow NULL values because this column was the last nullable column in the foreign key constraint with action type 'on delete set NULL'.
For the FOREIGN KEY clause, no column of the key allows null values. name is the constraint name, if specified. If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY clause followed by three periods.
The statement cannot be processed.
User response
For an ALTER TABLE statement, either remove the foreign constraint or change at least one other column in this constraint to be nullable. For the FOREIGN KEY clause, change either a column of the key to allow an assignment to the null value or change the delete rule.
sqlcode: -629
sqlstate: 42834
SQL0631N FOREIGN KEY name is too long or has too many columns.
Explanation
The sum of the column internal lengths identified in the FOREIGN KEY clause in a CREATE TABLE statement exceeds the index key length limit, or the number of columns identified exceeds 64. Also, a foreign key cannot be defined using a LONG VARCHAR column.
name is the constraint name, if specified, in the FOREIGN KEY clause. If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY clause followed by three periods.
The index key length limit is based on the page size of the tablespace used by the index:
-------------- ---------
1K 4K
2K 8K
4K 16K
8K 32K
The statement cannot be processed.
User response
To modify the foreign key definition, eliminate one or more key columns and conform to the 64 column limit and the key length limit.
sqlcode: -631
sqlstate: 54008
SQL0632N FOREIGN KEY name is not valid because the table cannot be defined as a dependent of table table-name because of delete rule restrictions (reason code = reason-code).
Explanation
- (01) The relationship is self-referencing and a self-referencing relationship already exists with the SET NULL delete rule.
- (02) The relationship forms a cycle of two or more tables that cause the table to be delete-connected to itself (all other delete rules in the cycle would be CASCADE).
- (03) The relationship causes the table to be delete-connected to the indicated table through multiple relationships and the delete rule of the existing relationship is SET NULL.
The delete rules of the existing relationships cause an error, not the delete rule specified in the FOREIGN KEY clause of the CREATE TABLE or ALTER TABLE statement.
name is the constraint name, if specified, in the FOREIGN KEY clause. If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY clause followed by three periods.
The statement cannot be processed.
User response
If possible, eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.
sqlcode: -632
sqlstate: 42915
SQL0633N The delete rule of FOREIGN KEY name must be delete-rule (reason code = reason-code).
Explanation
- (01) The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).
- (02) The referential constraint is self-referencing and the table is dependent in a relationship with a delete rule of CASCADE.
- (03) The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).
name is the constraint name, if specified, in the FOREIGN KEY clause. If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY clause followed by three periods.
The statement cannot be processed.
User response
If possible, change the delete rule.
sqlcode: -633
sqlstate: 42915
SQL0634N The delete rule of FOREIGN KEY name must not be CASCADE (reason code = reason-code).
Explanation
- (01) A self-referencing constraint exists with a delete rule of SET NULL, NO ACTION or RESTRICT.
- (02) The relationship would form a cycle that would cause a table to be delete-connected to itself. One of the existing delete rules in the cycle is not CASCADE, so this relationship may be definable if the delete rule is not CASCADE.
- (03) The relationship would cause another table to
be delete-connected to the same table through multiple paths with
different delete rules or with delete rule equal to SET NULL.
name is the constraint name, if specified, in the FOREIGN KEY clause. If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY clause followed by three periods.
The statement cannot be processed.
User response
If possible, change the delete rule.
sqlcode: -634
sqlstate: 42915
SQL0636N Range specified for data partition partition-name is not valid. Reason code = reason-code.
Explanation
The range specified for the partitioning key is not valid for one of the following reason codes:
- 1
The STARTING value for the partition is greater than the ENDING value. Generally the STARTING value for a partition must be less than the ENDING value. However, the starting value can be EQUAL to the ending value if BOTH bounds are inclusive. A bound which includes MINVALUE or MAXVALUE is exclusive. For example, a partition defined as STARTING (1, MINVALUE) ENDING (1, MINVALUE) is illegal (with reason code 1). because the bound values are equal and both are exclusive. Even if only one bound (e.g. only a STARTING bound) is explicitly specified, this reason code will be returned if the resulting bounds (after the missing bounds are generated) violate this rule.
- 3
STARTING value is not specified for the partition with the lowest key.
- 4
ENDING value is not specified for the partition with the highest key.
- 5
A STARTING value is not specified when the previous ENDING value was not specified.
- 7
On DB2 UDB for iSeries only: The data partitions are not specified in ascending sequence.
- 9
The STARTING or ENDING value for the partition is too long. The total length of the table partitioning key limit values must be less than or equal to 512 bytes.
- 10
The range overlaps with another partition. Each data partition must have a well defined starting and ending boundary and each data value must go into one and only one data partition. Also, if the same value (except MINVALUE or MAXVALUE) is used in the ending bound of one partition and the starting bound of the next partition, then at least one of these bounds must be defined as EXCLUSIVE. For an existing table, to determine whether the bounds of a partition are INCLUSIVE or EXCLUSIVE, query the SYSCAT.DATAPARTITIONS catalog table as follows (substituting the appropriate values for 'table-schema' and 'table-name'):
SELECT DATAPARTITIONID, DATAPARTITIONNAME, LOWINCLUSIVE, LOWVALUE, HIGHINCLUSIVE, HIGHVALUE FROM SYSCAT.DATAPARTITIONS WHERE TABSCHEMA='table-schema' AND TABNAME='table-name' ORDER BY SEQNO
- 11
MINVALUE and MAXVALUE cannot be specified when the EVERY clause is specified.
- 12
The value specified for the EVERY clause must be greater than zero and large enough to define unique partitions.
- 13
A constant cannot be specified following MINVALUE or MAXVALUE in the STARTING or ENDING value for a partition. Once a MINVALUE or MAXVALUE is specified, all subsequent (less significant) columns must be MINVALUE or MAXVALUE, respectively.
- 14
Decimal floating-point special values cannot be specified as the starting bound, ending bound or increment when the EVERY clause is specified.
- 15
The STARTING value and ENDING value for the partition cannot be the null value. Note that a character string value with a length of zero is treated as a null value when the varchar2_compat database configuration parameter is set to ON.
If the value for partition-name is of the form "PARTITION=value", then the partition name was not available when the error occurred. The value provided identifies the starting or ending value of the offending partition in the partition list clause.
The statement cannot be processed.
User response
Correct the invalid ranges for the data partitions.
sqlcode: -636
sqlstate: 56016
SQL0637N Keyword keyword is not valid as used.
Explanation
The SQL statement contains a duplicate or conflicting specification for a clause with the indicated keyword. For example:
- DEFAULT, UNIQUE, and PRIMARY can only be specified once in a column definition
- UNIQUE and PRIMARY cannot both be specified for the same column definition
- PRIMARY can only be specified once in a CREATE TABLE statement
- the attribute-string specified for the PREPARE statement cannot specify an option more than once, or there are conflicting options
- ACTIVATE or DEACTIVATE ROW ACCESS CONTROL can only be specified once
- ACTIVATE or DEACTIVATE COLUMN ACCESS CONTROL can only be specified once
The statement cannot be processed.
User response
Correct the statement to specification of duplicate or conflicting clauses.
sqlcode: -637
sqlstate: 42614
SQL0638N Table name cannot be created because no column definitions were specified.
Explanation
The CREATE TABLE statement does not contain any column definitions.
The statement cannot be processed.
User response
Add one or more column definitions to the statement.
sqlcode: -638
sqlstate: 42601
SQL0644N Invalid value specified for keyword keyword in statement statement-type.
Explanation
The keyword keyword is followed by an invalid value as allowed by the description of statement-type. For numeric values, the value may be outside a defined range. For other types, the value is not in the defined set of valid values.
User response
Determine from reference documentation for the statement-type what are valid values and make the appropriate change.
sqlcode: -644
sqlstate: 42615
SQL0647N Bufferpool bufferpool-name is currently not active.
Explanation
The bufferpool bufferpool-name is not active in the current database environment. An attempt was made to find another bufferpool with the same page size but there are no such bufferpools active in the current database environment. The bufferpool bufferpool-name was recently defined but has not yet been activated.
The statement cannot be processed.
User response
The database must be stopped and started again to activate the required bufferpool.
sqlcode: -647
sqlstate: 57003
SQL0648N The foreign key cannot be defined because it will cause table owner1.table-name1 to be delete-connected to table owner2.table-name2 through multiple paths. Reason Code = reason-code.
Explanation
- 01
- The relationship will cause table owner1.table-name1 to be delete-connected to table owner2.table-name2 through multiple paths with the same delete rule of SET NULL.
- 02
- The relationship will cause table owner1.table-name1 to be delete-connected to table owner2.table-name2 through multiple paths with different delete rules.
Processing of the SQL statement has ended.
User response
Correct the statement. Try again.
sqlcode: -648
sqlstate: 42915
SQL0650N The ALTER statement was not executed. Reason code: reason-code.
Explanation
The ALTER of an index or a table cannot be executed as specified.
The reason code indicates more specifically the nature of the problem:
- 23
Compression cannot be altered for the following indexes: MDC or ITC block indexes, global temporary table indexes, generated range-clustered table indexes, and index specifications.
- 30
An attempt was made to alter the latency table, SYSTOOLS.REPL_MQT_LATENCY.
- 31
An attempt was made to change a column in the base table of a shadow table to a data type that is not supported in base tables of shadow tables.
User response
Correct the ALTER statement to avoid the restriction indicated by the reason code, and then issue the statement again.
sqlcode: -650
sqlstate: 56090
SQL0658N The statement failed because the object named name cannot be explicitly dropped, altered, or replaced.
Explanation
This message can be returned when an attempt is made to either explicitly alter or explicitly drop an object that is not supported by the ALTER statement or the DROP statement:
- Objects which are restricted from being explicitly altered include functions declared as LANGUAGE SQL, and sourced functions. Objects which are restricted from being explicitly dropped include default row permissions.
This message can also be returned when an object cannot be explicitly altered, dropped, or replaced due to indirect dependencies or other errors, including the following examples:
- Dropping an object failed because the object was implicitly created by the system. Examples of objects:
- A cast or comparison function that was generated when the CREATE TYPE statement was issued
- A package that was created for use with an SQL procedure
- An attempt was made to issue the CREATE VIEW statement with the OR REPLACE clause, specifying the name of a view that was created for use with an expression-based index.
- An attempt was made to use the DROP THRESHOLD statement to drop a threshold that was defined in the context of a work action set.
- An attempt was made to alter a nickname to disallow caching, but the nickname is referenced by a fullselect query of a materialized query table definition.
User response
Respond to this error by performing the following troubleshooting steps:
- Review restrictions for the ALTER statement that are related to the object named in the runtime token name. For objects that cannot be altered explicitly by using the ALTER statement, drop and then recreate the objects with the desired changes.
- To drop objects that are system-generated, drop the objects that caused the system-generated objects to be created.
- To drop a threshold that was defined in the context of a work action set, use the ALTER WORK ACTION SET statement or the DROP WORK ACTION SET statement.
- To alter a nickname to disallow caching, remove all references to the nickname from any materialized query table definition.
- To drop default row permissions for a table, issue the ALTER TABLE statement, specifying the DEACTIVATE ROW ACCESS CONTROL clause.
sqlcode: -658
sqlstate: 42917
SQL0659N Maximum size of a table object has been exceeded.
Explanation
- Data: This is where basic column data is stored
- Index: This is where all indexes on the table are stored
- Long Data: This is where LONG VARCHAR and LONG VARGRAPHIC column data is stored
- Lob/Lob Allocation: This is where BLOB, CLOB, and DBCLOB column data and control information are stored
- XML: This is where XML data is stored.
Once a storage object has grown to its maximum size, it cannot be extended further.
User response
- Reorganize the table
- Delete unnecessary rows from the table
- Drop an index from the table
- Update rows to reduce the amount of data (a reorg may be required after this action in order to reclaim unused storage)
- Delete unnecessary XML documents
sqlcode: -659
sqlstate: 54032
SQL0663N Number of partitioning values is not correct for data partition partition-name.
Explanation
The wrong number of data partition key limit values was specified in the STARTING or ENDING clause for the data partition. If the data partition specification included an EVERY clause, there must be only one table partitioning key column with a numeric or datetime data type. If the value for partition-name is of the form "PARTITION=value", then the partition name was not available when the error occurred. The value provided identifies the starting or ending value of the offending partition in the partition list clause.
User response
Change the number of values specified in the STARTING or ENDING clause to match the number of columns in the table partitioning key. Alternatively, change the number of columns in the table partitioning key.
sqlcode: -663
sqlstate: 53038
SQL0667N The FOREIGN KEY name cannot be created because the table contains rows with foreign key values that cannot be found in the parent key of the parent table.
Explanation
The definition of the indicated foreign key failed because the table being altered contains at least one row where the foreign key does not match the parent key value in the parent table.
name is the constraint name, if specified. If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY clause followed by three periods.
The statement cannot be processed. The specified table is not altered.
User response
Remove the erroneous table rows and define the foreign key.
sqlcode: -667
sqlstate: 23520
SQL0668N Operation not allowed for reason code reason-code on table table-name.
Explanation
Access to table table-name is restricted. The cause is based on the following reason codes reason-code:
- 1
Reason code 1 can be returned in multiple scenarios, including the following examples:
- The table is in the Set Integrity Pending No Access state, which means the integrity of the table is not enforced and the content of the table might be invalid.
- An operation on a parent table or an underlying table that is not in the Set Integrity Pending No Access state may also receive this error if a dependent table is in the Set Integrity Pending No Access state.
- An attempt is made to issue the SET INTEGRITY statement against a user-maintained materialized query table without specifying the IMMEDIATE UNCHECKED clause.
- 2
The table is in the No Data Movement state. When in this state, operations that cause data movement are disallowed. Data movement operations include REDISTRIBUTE, update of database partitioning key, update of multidimensional clustering key, update of range clustering key, update of table partitioning key and REORG TABLE.
- 3
The table is in the Load Pending state. A previous LOAD attempt on this table resulted in failure. No access to the table is allowed until the LOAD operation is restarted or terminated.
- 4
The table is the Read Access state. This state can occur during on-line LOAD processing (LOAD INSERT with the READ ACCESS option), or after an on-line LOAD operation, but before all constraints have been validated in the newly appended portion of the table using the SET INTEGRITY statement. This state can also occur if the READ ACCESS clause was used when turning off integrity checking with the SET INTEGRITY statement. No update activity is allowed on this table.
- 5
The table is in the Load In Progress state. The LOAD utility is currently operating on this table, no access is allowed until the LOAD is finished.
- 6
Materialized query tables that reference a nickname cannot be refreshed in DB2 Enterprise Server Edition.
- 7
The table is in the reorg pending state. This can occur after an ALTER TABLE statement containing a REORG-recommended operation.
- 8
The table is in the alter pending state. This can occur when using the table in the same unit of work as an ALTER TABLE statement containing a REORG-recommended operation.
- 9
The table is in Redistribute Pending state. The REDISTRIBUTE utility is not completed on this table, no access is allowed until the REDISTRIBUTE is finished.
- 10
The table is the source table for an ongoing ADMIN_MOVE_TABLE operation. The attempted operation is restricted until the move is completed or canceled.
- 11
The table, which has a nonpartitioned index, has a new data partition that the operation is attempting to access in the same transaction as the add or attach operation that created the partition, but the transaction does not have the table locked in exclusive mode.
User response
Respond to this error according to the reason code:
- 1
Bring the table named table-name out of the Set Integrity Pending No Access state according to the type of table:
- For a user-maintained materialized query table, execute the statement with the IMMEDIATE UNCHECKED option.
- For materialized query tables that are not user-maintained, execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option.
- 2
Execute REFRESH TABLE statement on the dependent immediate materialized query tables and staging tables of table table-name. The contents of these dependent immediate materialized query tables and staging tables can be incrementally maintained from the appended data of table-name through previous LOAD INSERT operations and from the attached data of table-name through previous ALTER TABLE statements with the ATTACH clause.
- 3
Move the table out of "Load Pending" state.
- 4
If the Read Access state was due to a LOAD, issue the LOAD QUERY command to check whether the table is in the process of being loaded. If yes, wait until the LOAD utility has completed, or if necessary, restart or terminate previously failed LOAD operation. If LOAD is currently not in progress, issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option, to validate constraints in the newly loaded portion of the table.
If the Read Access state was due to turning off integrity checking, issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option.
- 5
Wait until the current LOAD operation has finished. You can use the LOAD QUERY command to monitor the progress of load.
- 6
Define a materialized query table using the MAINTAINED BY USER option. Then, use an INSERT statement with a subquery to populate the materialized query table.
- 7
Reorganize the table using the REORG TABLE command.
For a table in the reorg pending state, note that the following clauses are not allowed when reorganizing the table:
- The INPLACE REORG TABLE clause
- The ON DATA PARTITION clause for a partitioned table when table has nonpartitioned indexes defined on the table
- 8
Complete the unit of work, and re-issue the command.
- 9
If the REDISTRIBUTE utility is working, wait until it finishes working on the current table. You can use the LIST UTILITIES command to monitor the progress of the REDISTRIBUTE utility. If a previous REDISTRIBUTE operation failed and left the table in this state, issue the REDISTRIBUTE utility again with the CONTINUE or ABORT option and let it finish on this table.
- 10
Complete or cancel the table move operation and reissue the command. You can query the SYSTOOLS.ADMIN_MOVE_TABLE table for information about the status of the move operation.
- 11
Modify the application so that transactions that include add or attach partition operations are committed prior to the application accessing the new partitions where the target table has a nonpartitioned index. Alternatively, modify the application so that a table that has a nonpartitioned index and that is affected by the online add or attach operation is exclusively locked when a new partition is being accessed in the same transaction as the add or attach operation that created the partition.
sqlcode: -668
sqlstate: 57007
SQL0669N A system required index cannot be dropped explicitly.
Explanation
The DROP INDEX statement attempted to drop an index required to:
- enforce the primary key constraint on a table
- enforce a unique constraint on a table
- enforce the uniqueness of the object identifier (OID) column of a typed table hierarchy
- maintain a replicated materialized query table
- maintain an XML column in the table.
- maintain an HBase row key definition
A system required index cannot be dropped using the DROP INDEX statement.
The statement cannot be processed. The specified index is not dropped.
User response
If you do not want to keep the primary or unique constraint, use the DROP PRIMARY KEY clause or the DROP CONSTRAINT clause of the ALTER TABLE statement to remove the primary key or unique constraint. If the index was created only for enforcing the primary or unique key, then the index will be dropped. If not, the DROP INDEX statement could then be processed.
The index for an OID column can only be dropped by dropping the table.
The index required to maintain a replicated materialized query table can only be dropped by first dropping the replicated materialized query table.
The system-required indexes associated with one or more XML columns in a table cannot be dropped explicitly. Such indexes are maintained by the database manager to support the XML column in the table. The index specified in the DROP INDEX statement cannot be dropped without dropping the table.
For HBase users, the index can be implicitly dropped only when the associated HBase table is dropped.
sqlcode: -669
sqlstate: 42917
SQL0670N The statement failed because the row or column size of the resulting table would have exceeded the row or column size limit: limit. Table space name: tablespace-name. Resulting row or column size: calculated-size.
Explanation
The row size of a table is the sum of the byte counts of the columns, including the inline lengths of all structured or XML type columns. Therefore, the row size is affected by the total number of columns, the total number of LOB columns, and the data type of the columns.
The row size limit is the maximum number of bytes of a given table. This limit consists of a defined value in some cases or a variable value that depends on the table space page size.
In a column-organized table, a column size limit consists of the maximum size of an uncompressed column data value that can fit on one page of the table.
The message is returned when an attempt is made to create or modify a table by using one of the following statements:
- CREATE TABLE
- ALTER TABLE statement
- DECLARE GLOBAL TEMPORARY TABLE
- CREATE GLOBAL TEMPORARY TABLE
The message can also be returned when a multidimensional clustering (MDC) table of extended row size has a VARCHAR, VARGRAPHIC, or VARBINARY dimension key that is greater than 24 bytes.
This message can also be returned when a change to a column-organized base table causes the associated synopsis table to exceed the size limit. When this happens, the limit token will indicate that it is the synopsis table which has exceeded the limit.
User response
To resolve the issue reported by this message, perform the following steps:
For row size limits:
- Calculate the row size of the table that you are trying to create or modify.
- Determine the limit or restriction exceeded by the row size.
- For row-organized tables: the row size limit is determined by the table space page size.
- For column-organized tables: the limit is the column-organized table maximum row size.
- Perform one of the following actions:
- Reduce the row size by reducing the lengths of one or more columns, specifying a different data type, or eliminating one or more columns.
- For row-organized tables only: Specify in the statement a table space with a page size that fits the row size that you calculated.
- If reducing the row size or finding a table space with a larger page size is not possible, enable the extended row size configuration parameter, extended_row_sz, which allows row length to exceed the maximum record length for the page size of the table space.
For column size limits:
- Determine the maximum column size for the table that you are trying to create or modify, based on the column type and the underlying page size.
- Reduce the length of the affected column so that it does not exceed the limit.
sqlcode: -670
sqlstate: 54010
SQL0672N Operation DROP not allowed on table table-name.
Explanation
The DROP operation failed for one of the following reasons:
- The table being dropped has the RESTRICT ON DROP attribute.
- The table space or database partition group being dropped contains the specified table, which has the RESTRICT ON DROP attribute.
The DROP statement cannot be executed.
User response
If the problem occurred during a DROP TABLE statement, issue an ALTER TABLE statement using the DROP RESTRICT ON DROP clause. Then re-issue the DROP TABLE statement.
If the problem occurred during a DROP TABLESPACE or DROP DATABASE PARTITION GROUP statement, ensure that there are no other tables within the table space or database partition group with the RESTRICT ON DROP attribute. The following select statement can help identify the tables:
SELECT TB.TABNAME, TB.TABSCHEMA,
TS.TBSPACE, TS.NGNAME
FROM SYSCAT.TABLES TB,
SYSCAT.TABLESPACES TS
WHERE TYPE = 'T' AND
DROPRULE = 'R' AND
TB.TBSPACEID = TS.TBSPACEID
ORDER BY TS.NGNAME, TS.TBSPACE,
TB.TABSCHEMA, TB.TABNAME;
After identifying tables with the RESTRICT ON DROP attribute, issue an ALTER TABLE statement using the DROP RESTRICT ON DROP clause for each table with the RESTRICT ON DROP attribute. Then re-issue the DROP TABLESPACE or DROP DATABASE PARTITION GROUP statement.
sqlcode: -672
sqlstate: 55035
SQL0673N A primary or unique key index cannot be created because the table contains rows which are duplicates with respect to the values of the identified primary or unique key columns of constraint name.
Explanation
The primary or unique key definition of the constraint identified by name failed because the table being altered already contains rows with duplicate values for the PRIMARY KEY or UNIQUE clause columns.
name is the constraint name, if specified. If a constraint name was not specified, name is the first column name specified in the primary key or unique constraint clause followed by three periods.
The statement cannot be processed. The specified table is not altered.
User response
Remove the erroneous rows from the table before attempting to define the primary or unique key.
sqlcode: -673
sqlstate: 23515
SQL0678N The literal literal must be compatible with the data type data-type of column column-name.
Explanation
The literal literal specified for the STARTING, ENDING or EVERY clause is not compatible with the data type of column column-name. The data type of the column is data-type. The value of a literal used in the EVERY clause must not be zero or negative. The EVERY clause must specify a duration when the PARTITION BY clause specifies a datetime data type.
User response
Specify a valid literal in the STARTING, ENDING, or EVERY clause.
sqlcode: -678
sqlstate: 53045
SQL0680N The statement failed because too many columns were specified for a table, view, table function, or nickname, or because too many fields were specified for a user-defined row data type.
Explanation
The maximum number of columns that you can define for a table depends on the type of object.
The maximum number of columns that you can define for a view, table function, or nickname and the maximum number of fields permitted for a user-defined row data type are specified in SQL and XML limits.
For tables, the maximum number of columns is affected by the number of LOB columns specified. The section Row size limit in the CREATE TABLE statement includes a formula to calculate the row size of a table.
For relational database sources in federated environments, nicknames might also be limited by the maximum number of columns for tables or views on their data source when these limits are less than the limits specified in SQL and XML limits.
This message is returned for the following scenarios:
- An attempt is made to create an object such as a table, a view, a table function, or a nickname for a table with more columns than the maximum number of permitted columns.
- An attempt is made to create a user-defined row data type with more fields than than the maximum number of permitted fields.
User response
Ensure that the number of columns or fields does not exceed the limit.
To resolve the issue reported by this message, perform the following types of troubleshooting actions:
- Creating a table
- Review database manager SQL and XML limits. Particularly review database manager SQL and XML limits that are specific to page size.
- Redesign the table to have fewer columns, use a data type different than LOBs to reduce the row size, or indicate a table space with a page size that allows a higher number of columns and issue the CREATE TABLE statement again.
- Creating a view or table function on a table
- Review the database manager limits, redesign the table to meet the SQL limits, and then create the view or table function again.
- Creating a nickname on a table or view in a federated data source
- Review the database manager limits and the data source SQL limits for tables or views, redesign the nickname to meet these limits, and then create it again.
- Creating a row data type
- Review SQL and XML limits, redesign the data type to have fewer fields in order to satisfy the SQL limits, and then create the row data type again.
sqlcode: -680
sqlstate: 54011
SQL0683N The specification for column, attribute, user-defined type or function data-item contains incompatible clauses.
Explanation
There is an error in a data item specification in a CREATE statement, ALTER statement, an XMLTABLE expression, or a typed-correlation clause of a SELECT statement which is referencing a generic table function. Incompatible specifications are present, such as: "INTEGER and FOR BIT DATA". If the column is of type DB2SECURITYLABEL, incompatible specifications include NOT NULL WITH DEFAULT. The location of the error is given by data-item as follows:
- For a CREATE TABLE statement, ALTER TABLE statement, an XMLTABLE expression, or a typed-correlation clause of a SELECT statement, data-item gives the name of the column containing the error.
- For a CREATE FUNCTION statement, data-item is a token that identifies the area of the problem in the statement. For example, "PARAMETER 3" or "RETURNS" or "CAST FROM".
- For a CREATE DISTINCT TYPE statement, data-item gives the name of the type being defined.
- For a CREATE or ALTER TYPE statement, data-item identifies the clause containing the error or gives the name of the attribute containing the error.
- For a CREATE or ALTER TABLE statement, the columns for a BUSINESS_TIME period must be defined as DATE, or TIMESTAMP(p) where p is between 0 and 12 (inclusive).
- For a CREATE or ALTER TABLE statement, data-item for a ROW BEGIN, ROW END, or TRANSACTION START ID column must be TIMESTAMP(12).
- For an ALTER TABLE statement, ALTER COLUMN with COMPRESS OFF or COMPRESS SYSTEM DEFAULT cannot be specified for a random distribution key column of a random distribution table using random by generation method.
The statement cannot be processed.
User response
Remove the incompatibility and try the statement again.
sqlcode: -683
sqlstate: 42842
SQL0695N The value provided (seclabel) could not be converted to a security label. In the security policy with a policy ID of policy-id, the element value element-value does not correspond to any element in the component in ordinal position ordinal.
Explanation
An INSERT or UPDATE statement specified a value for the column of type DB2SECURITYLABEL. The value cannot be converted to a valid security label for the security policy protecting the table. If the seclabel value is *N, then the actual value has been written in the db2diag log file.
The value has a problem in the component indicated by the component number. The ordinal position refers to the position of the component in COMPONENTS clause of the CREATE SECURITY POLICY statement used to create the policy. The indicated element value does not correspond to a valid element in that component.
User response
Check the INSERT or UPDATE statement and make sure that the value being set in the security label column is valid for the security policy protecting the table. Sometimes an invalid value can be generated as the result of a SELECT done from a table that is not protected with the same security policy as the target table. You should only copy security labels between tables protected by the same security policy.
To get the security policy name for the given policy ID use this SQL query. Replace policy-id with the policy ID number given in the error message.
SELECT SECPOLICYNAME
FROM SYSCAT.SECURITYPOLICIES
WHERE SECPOLICYID = policy-id
sqlcode: -695
sqlstate: 23523
SQL0696N The definition of trigger trigger-name includes an invalid use of correlation name or transition table name name. Reason code=reason-code.
Explanation
The trigger definition included an invalid use of name. The value of reason-code identifies the roblem:
- 1
NEW correlation name and NEW TABLE name are not allowed in a DELETE trigger if the DELETE trigger is not defined using a compound SQL (compiled) statement.
- 2
OLD correlation name and OLD TABLE name are not allowed in an INSERT trigger if the INSERT trigger is not defined using a compound SQL (compiled) statement.
- 3
OLD TABLE name and NEW TABLE name are not allowed in a BEFORE trigger.
- 4
OLD TABLE name and NEW TABLE name are not allowed in a trigger if the trigger is defined using a compound SQL (compiled) statement.
The statement cannot be processed.
User response
Remove the invalid correlation name or transition table name along with the preceding keyword.
SQL0697N OLD or NEW correlation names are not allowed in a trigger defined with the FOR EACH STATEMENT clause.
Explanation
The trigger, as defined, includes a REFERENCING clause with OLD or NEW correlation names specified (or both) and the FOR EACH STATEMENT clause. These cannot be specified together.
The statement cannot be processed.
User response
Remove any OLD or NEW correlation names from the REFERENCING clause or replace FOR EACH STATEMENT with FOR EACH ROW.
sqlcode: -697
sqlstate: 42899
SQL0707N The name name cannot be used because the specified identifier is reserved for system use.
Explanation
The following list provides rules about reserved names and qualifiers:
- A table space name cannot start with SYS.
- A storage group name cannot start with SYS.
- A database partition group name cannot start with SYS or IBM.
- A module name cannot start with SYS.
- An object name for an object defined in a module cannot start with SYS_ except for the module initialization procedure..
- A savepoint name cannot start with SYS.
- A role name cannot start with SYS, and it cannot be ACCESSCTRL, DATAACCESS, DBADM, NONE, NULL, PUBLIC, SECADM, SQLADM, or WLMADM.
- A workload management object name cannot start with SYS.
- A member subset name cannot start with SYS.
User response
Select a name that is not reserved.
sqlcode: -707
sqlstate: 42939
SQL0713N The replacement value for special-register is invalid.
Explanation
Either the value specified in the SET statement is not a valid value of the indicated special register, the value specified is valid for the special register but cannot be used on the current server, or the value specified is NULL as a result of an indicator variable.
The statement cannot be executed.
User response
Correct the replacement value or any indicator variables.
sqlcode: -713
sqlstate: 42815
SQL0719N Bind error for user auth-id. Package package-name already exists.
Explanation
- auth-id
- Authorization ID of the invoker of the BIND or PREP
- package-name
- Name of the package in the form 'pkgschema.pkgname.pkgversion'. If the package version is the empty string, then '.pkgversion' is omitted from the name.
The package is not created.
User response
- Check the SYSCAT.PACKAGES catalog view for names of existing application packages. Re-invoke the PRECOMPILE or BIND with a 'pkgschema.pkgname.pkgversion' that is not in use.
- Reissue the PREP or BIND statement without the ACTION ADD option. This will replace the existing package.
sqlcode: -719
sqlstate: 42710
SQL0720N Attempting to replace package pkgschema.pkgname WITH VERSION pkgversion, where this version already exists.
Explanation
An attempt is made to create a version of a package that already exists using the ACTION REPLACE REPLVER option. The version specified in the REPLVER keyword is different from the version specified in the VERSION precompile option. The version specified in the VERSION precompile option already exists in the catalog. The combination of 'pkgschema.pkgname.version' must be unique in the SYSCAT.PACKAGES catalog view.
A common mistake is that the user may believe that the version that is being created is the one specified in the REPLVER keyword. This is not the case. The version specified in the REPLVER keyword is the name of the version being replaced. The version that will be created is the version that is given to the program in the VERSION option.
No package is created.
User response
Ensure that the version being created does not already exist.
- Precompile the program again with a new version name and reissue the original PREP or BIND command.
- The other approach is to reissue the PREP or BIND command specifying the ACTION REPLACE clause without the REPLVER specification. In this case, the REPLACE will replace the version that matches the version specified in the VERSION option.
sqlcode: -720
sqlstate: 42710
SQL0721N Package pkgschema.pkgname with consistency token = 0Xcontoken cannot be created since it is not unique.
Explanation
An attempt is made to add or replace a package with a consistency token that is not unique for that package. The combination of 'pkgschema.pkgname.contoken' already exists. This is most likely caused by using the LEVEL option of the PRECOMPILE to specify the consistency token.
The package is not created.
User response
SELECT PKGSCHEMA,PKGNAME
FROM SYSCAT.PACKAGES
WHERE HEX(UNIQUE_ID) = 'contoken';
If the LEVEL option on the precompile was used, reissuing the precompile and specifying a different LEVEL value would change the consistency token. It is recommended that the LEVEL option is not used; not specifying the LEVEL option of the precompile would ensure that the consistency token is a current timestamp value.
SQL0722N Error performing bind-command for user auth-id. Package package-name does not exist.
Explanation
- bind-command
- Type of bind command (BIND | REBIND) issued. Note that the value 'BIND' will be used for a precompile as well.
- auth-id
- Authorization ID that issued the bind or rebind.
- package-name
- Name of the package in the form 'pkgschema.pkgname.pkgversion'. If the package version is the empty string, then '.pkgversion' is omitted from the name.
The package was not bound or rebound.
Explanation
Check the SYSCAT.PACKAGES catalog view for the correct 'pkgschema.pkgname.pkgversion' to specify in the REPLVER option.
sqlcode: -722
sqlstate: 42704
SQL0723N An error occurred in a triggered SQL statement in trigger trigger-name. Information returned for the error includes SQLCODE sqlcode, SQLSTATE sqlstate and message tokens token-list.
Explanation
An SQL statement in the trigger trigger-name has failed during execution of the trigger. The sqlcode, sqlstate and message token list (each token is separated by the vertical bar character) are provided. The message tokens may be truncated. See the corresponding message for the sqlcode for further explanation of the error.
The trigger and the original SQL statement that caused the trigger to execute cannot be processed.
User response
Check the message associated with the SQLCODE of the SQL statement that failed. Follow the action suggested by that message.
sqlcode: -723
sqlstate: 09000
SQL0724N The activation of object-name of type object-type would exceed the maximum level of indirect SQL cascading.
Explanation
Cascading of indirect SQL occurs when a trigger activates another trigger (possibly through referential constraint delete rules) or a routine, containing SQL, invokes another routine. The depth of this cascading is limited to 16 for triggers and 64 for routines.
Note that recursive situations where a trigger includes a triggered SQL statement that directly or indirectly causes the same trigger to be activated, or where a routine directly or indirectly invokes itself, is a form of cascading that is very likely to cause this error if there are no conditions to prevent cascading from exceeding the limit.
The object-type is one of TRIGGER, FUNCTION, METHOD, or PROCEDURE.
The object-name specified is one of the objects that would have been activated at the seventeenth level of cascading.
User response
Start with the objects that are activated or invoked by the statement that received this error. If any of these objects are recursive, ensure that there is some condition that prevents the object from being activated or invoked more than the limit allows. If this is not the cause of the problem, follow the chain of objects that are activated or invoked to determine the chain that exceeds the cascading limit.
sqlcode: -724
sqlstate: 54038
SQL0727N An error occurred during implicit system action type action-type. Information returned for the error includes SQLCODE sqlcode, SQLSTATE sqlstate and message tokens token-list.
Explanation
The processing of a statement or command has caused the database manager to implicitly perform additional processing. During this processing an error was encountered. The action attempted is shown by the action-type:
- 1
implicit rebind of a package
- 2
implicit prepare of a cached dynamic SQL statement
- 3
implicit revalidation of an object
- 4
This return code is reserved for use by DB2.
- 5
incremental bind of a static SQL statement, not bound during package bind time
- 6
implicit prepare of a reoptimizable statement containing host-variables, special registers, or parameter markers
- 7
implicit regeneration of a trigger
- 8
implicit regeneration of a function
- 9
implicit regeneration of a check constraint
- 10
implicit instantiation of a global variable
- 11
implicit invocation of module initialization routine
- 12
implicit verification that the data conforms to an integrity constraint after altering a column data type
- 13
implicit verification that existing generation expression column values are equal to the generation expression after altering a column data type
The sqlcode, sqlstate and message token list (each token is separated by the vertical bar character) are provided. The message tokens may be truncated. See the corresponding message for the sqlcode for further explanation of the error.
The original SQL statement or command that caused the action-type cannot be processed and the implicit system action was not successful.
Federated system users: You may have received this message because you dynamically prepared an SQL statement in a pass-through session and then tried to execute the statement after the session was closed.
User response
Check the message associated with the SQLCODE of the SQL statement that failed. Follow the action suggested by that message.
For an invalid package, the REBIND command can be used to recreate the error or to explicitly validate the package once the cause of the error has been resolved.
For a failure that occurred while a database object was being revalidated:
- Determine the name of the database object that could not be revalidated by locating the revalidation failure message in the administration notification log.
- To avoid this error in the future, you can do one of the following:
- If the database object that is causing this error is no longer needed, drop the database object.
- If the statement or command that caused this database object to be revalidated can be changed, change the statement or command so that the next time that statement is executed or that command is run the database object will not be revalidated.
- Revalidated the database object now by doing one of the following:
- Execute an SQL statement that directly references the database object.
- Call the ADMIN_REVALIDATE_DB_OBJECTS procedure for the database object.
Federated system users: If the statement that failed was dynamically prepared in a pass-through session, open another pass-through session, write and prepare the statement again, and execute it while the session is still open.
sqlcode: -727
sqlstate: 56098
SQL0740N Routine routine-name (specific name specific-name) is defined with the MODIFIES SQL DATA option, which is not valid in the context where the routine is invoked.
Explanation
The routine routine-name (specific name specific-name) is defined with MODIFIES SQL DATA and is therefore not allowed in:
- a BEFORE trigger that is not defined using a compound SQL (compiled) statement
- a correlated subquery
- a search-condition (such as a WHERE clause or check constraint)
Module functions and functions defined with a compound SQL (compiled) statement as the function body that are defined with the MODIFIES SQL DATA option can only be the sole expression on the right hand side of an assignment statement in a compound SQL (compiled) statement.
User response
Ensure that routine resolution resolved to the desired routine. Remove the routine from the failing statement or redefine the routine with an SQL access indicator other than MODIFIES SQL DATA.
sqlcode: -740
sqlstate: 51034
SQL0746N The routine named routine-name (specific name specific-name) failed because the routine violated nested SQL statement rules when attempting to perform operation operation on the table named table-name.
Explanation
This message is returned when an application calls a routine that attempts to access a table in a way that conflicts with other uses of the table by either the application, or a routine invoked directly or indirectly from that application.
The value of the token operation indicates the nature of the conflict:
- If the operation is "READ", then the table table-name is currently being written to by either the application or another routine.
- If the operation is "MODIFY", then the table table-name is already being read from or written to by the application or another routine.
This message can be returned in multiple scenarios:
- If table-name references an explain table and the statement that receives this error message is either a PREPARE statement or an EXECUTE IMMEDIATE statement, then a conflict occurred when inserting explain information into the explain table.
- This message can be returned when a trigger attempts to call a stored procedure that accesses tables that have been previously modified within the context of the outermost invoking statement. You can use the DB2_RESOLVE_CALL_CONFLICT registry variable to enforce that all modifications to tables are completed in compliance with the SQL Standard rules for triggers before executing the CALL statement.
User response
Retry the operation.
If the operation continues to fail with this error, take the following steps to avoid this error:
- Redesign either the application or the routine to avoid the conflict.
- If the conflict occurred when inserting explain information for a dynamic statement, then disable explain for dynamic statements by using the CURRENT EXPLAIN MODE special register, and then execute the PREPARE statement or EXECUTE IMMEDIATE statement again.
- If this message is returned when a trigger attempts to call a stored procedure that access tables that have been previously modified within the context of the outermost invoking statement, set the DB2_RESOLVE_CALL_CONFLICT registry variable to YES.
sqlcode: -746
sqlstate: 57053