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:

  1. was never prepared
  2. was made not valid by either an explicit or implicit rebind of the package
  3. was prepared in a previous transaction and the application's package is bound with KEEPDYNAMIC NO

The statement cannot be processed.

User response

  1. Prepare the statement named in the DECLARE CURSOR statement before you try to open the cursor.
  2. The prepare for the cursor must be reissued.
  3. 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

  1. was never prepared,
  2. is a SELECT or VALUES statement,
  3. was made not valid by either an explicit or implicit rebind of the package, or
  4. was prepared in a previous transaction and the application's package is bound with KEEPDYNAMIC NO.

The statement cannot be processed.

User response

  1. Prepare the statement before attempting the EXECUTE.
  2. Ensure that the statement is not a SELECT or VALUES statement.
  3. The prepare for the cursor must be reissued.
  4. 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

One of the following:
  • 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.

The indicated table referenced in a subquery of the DELETE statement can be affected because it is either:
  • 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

One of the following occurred:
  • 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

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

One of the following occurred:
  • 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

The DEFAULT value or IDENTITY attribute value for column column-name in table table-name is not valid. The possible reason codes are as follows:
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

The view or materialized query table name has been marked inoperative because a table, view, alias, or privilege upon which it is dependent has been removed. The view cannot be used by any SQL statement other than one of the following:
  • 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 search condition of a CASE expression is:
  • a quantified predicate (one using SOME, ANY, or ALL),
  • an IN predicate using a fullselect or
  • an EXISTS predicate
and the CASE expression is part of:
  • 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

A DATALINK value could not be retrieved. The possible reason codes are as follows:
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

The action is based on the reason code as follows.
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:

  1. There were no private dbspaces found in the DB2 for VM database for the user-id.
  2. There were one or more private dbspaces found for the user-id, but each contained 255 tables.
  3. 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:

  1. Acquire a private dbspace in a recoverable storage pool. You may require a database administrator's assistance.
  2. 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).
  3. 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.

Possible reasons:
  • 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:

Max Key Length  Page size
--------------  ---------
   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

A referential constraint cannot be defined because the object table of the CREATE TABLE or ALTER TABLE statement cannot be defined as a dependent of table table-name for one of the following reason codes:
  • (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

The delete rule specified in a FOREIGN KEY clause of the CREATE TABLE or ALTER TABLE statement is not valid. The indicated delete rule is required for one of the following reason codes:
  • (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

The CASCADE delete rule specified in the FOREIGN KEY clause of the CREATE TABLE or ALTER TABLE statement is not valid for one of the following reason codes:
  • (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

The foreign key cannot be defined because one of the following reason codes = reason-code:
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

One or more of the objects which make up a table has reached its maximum size. The storage objects that make up a table are:
  • 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

To make existing space within the object available to store new data, the following actions may be taken:
  • 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:

  1. Calculate the row size of the table that you are trying to create or modify.
  2. 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.
  3. 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:

  1. 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.
  2. 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

An attempt is made, using the ACTION ADD option of PRECOMPILE or BIND to add a package that already exists. The combination of 'pkgschema.pgkname.pkgversion' must be unique in the SYSCAT.PACKAGES catalog view.
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

Possible ways to ensure that there is no attempt to add a duplicate entry include:
  • 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.

There are two approaches to solve this problem:
  • 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

Check the SYSCAT.PACKAGES catalog table for names of existing application packages with the indicated consistency token. Reissue the PREP or BIND command such that the 'pkgschema.pkgname.contoken' is unique within the catalog. The following SQL statement can be used to query the catalog:
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 or rebind was issued against a package that does not exist. The error would occur if ACTION REPLACE was specified and the REPLVER option specified a version that doesn't exist.
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:

  1. Determine the name of the database object that could not be revalidated by locating the revalidation failure message in the administration notification log.
  2. 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