SQL20250 - SQL20499

SQL20250N The number of data partitions exceeds the number of table spaces for the table.

Explanation

The partitioned table was defined with NO CYCLE and there are not enough table spaces defined for the number of specified table partitions.

The statement cannot be processed.

User response

In the CREATE TABLE statement, either specify additional table spaces for the table or remove the NO CYCLE specification for the table.

sqlcode: -20250

sqlstate: 428G1

SQL20251N The last data partition cannot be detached from the table `table-name`.

Explanation

The ALTER TABLE statement would have resulted in the last remaining data partition being detached from the table. This operation is not allowed. A partitioned table must have at least one data partition whose status is normal or attached. A partitioned table cannot have only data partitions whose status is detached. To determine the status of partitions, query the catalog view, SYSCAT.DATAPARTITIONS.

The statement cannot be processed.

User response

Ensure that the ALTER TABLE statement leaves at least one data partition in the table.

sqlcode: -20251

sqlstate: 428G2

SQL20253N The BEFORE trigger or generated column `name` cannot be created, altered, or executed because doing so would cause the table on which the BEFORE trigger or generated column is defined to be delete-connected to at least one ancestor table through multiple relationships with conflicting delete rules. The conflict is between the delete rules of constraints `constraint-name1` and `constraint-name2`. Reason code = `reason-code`.

Explanation

The definition for the BEFORE trigger or generated column name in the CREATE TRIGGER, CREATE TABLE or ALTER TABLE statement is not valid for the reason specified by the reason-code as follows:

The execution of the delete rule of constraint constraint-name1 will fire the BEFORE trigger name and the body of this BEFORE trigger modifies a column that is part of the foreign key of constraint constraint-name2 or modifies a column that is referenced by a generated column which is part of the foreign key of constraint constraint-name2.
The execution of the delete rule of constraint constraint-name1 will trigger the update of the generated column name and the generated column itself is part of the foreign key of constraint constraint-name2.
With the addition of the BEFORE trigger or generated column, name, the execution of both constraints constraint-name1 and constraint-name2 would cause an update of the same column.

The statement cannot be processed.

User response

The action corresponding to the reason code is:

Change the BEFORE trigger definition so that the BEFORE trigger will not be fired when the delete rule of constraint constraint-name1 is executed, or change the body of the BEFORE trigger so that it does not modify a column that is part of the foreign key of constraint constraint-name2 nor modify a column that is referenced by a generated column which is part of the foreign key of constraint constraint-name2.
Change the generated column expression so that the generated column will not be updated when the delete rule of constraint constraint-name1 is executed or change foreign key of constraint constraint-name2 so that it does not include the generated column.
Change the BEFORE trigger definition or generated column expression so that the execution of both constraints constraint-name1 and constraint-name2 would not cause an update of the same column.

sqlcode: -20253

sqlstate: 42915

SQL20254N FOREIGN KEY `name` is not valid since it would cause table `table-name` to be delete-connected to itself through a cycle that contains a delete rule of either RESTRICT or SET NULL. Reason code = `reason-code`.

Explanation

A referential cycle must not contain a delete rule of RESTRICT or SET NULL. The delete rule specified for FOREIGN KEY name in the CREATE TABLE or ALTER TABLE statement is not valid for the reason specified by the reason-code as follows:

The delete rule specified is RESTRICT or SET NULL and the referential relationship would cause table table-name to be delete-connected to itself.
The delete rule specified is CASCADE but the referential relationship would cause table table-name to be delete-connected to itself by a cycle that contains a delete rule of either RESTRICT or 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

The action corresponding to the reason code is:

Change the delete rule to CASCADE or NO ACTION or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.
Change the delete rule to NO ACTION, RESTRICT, or SET NULL or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.

sqlcode: -20254

sqlstate: 42915

SQL20255N FOREIGN KEY `name` is not valid because it would cause a descendent table `descendent-table-name` to be delete-connected to its ancestor table `ancestor-table-name` through multiple relationships with conflicting delete rules. The conflict is between the delete rules of constraints `constraint-name1` and `constraint-name2` on the descendent table. Reason code = `reason-code`.

Explanation

The referential constraint definition specified for FOREIGN KEY name in the CREATE TABLE or ALTER TABLE statement is not valid. The reason code indicates more specifcally what the problem is:

1: The relationship is self-referencing and is specified with a delete rule of RESTRICT or SET NULL and the table is a dependent in a relationship with a delete rule of CASCADE.
2: The relationship is specified with a delete rule of CASCADE, and a self-referencing relationship with the delete rule of RESTRICT or SET NULL already exists on the table.
3: The foreign key overlaps with a foreign key of an existing relationship and their delete rules are either not the same or one of them is SET NULL.
4: The relationship is specified with a delete rule of CASCADE which would cause the descendent table to be delete-connected to its ancestor table through multiple relationships where at least two such relationships have overlapping foreign keys and their delete rules are either not the same or one of them is SET NULL.
5: At least one of the conflicting referential constraints is specified with a delete rule of SET NULL and its foreign key definition overlaps with either the partitioning key, or a dimension of an MDC table.
6: The relationship is specified with a delete rule of CASCADE which would cause the descendent table to be delete-connected to its ancestor table through multiple relationships where at least one of the relationships is specified with a delete rule of SET NULL and its foreign key definition overlaps with either the partitioning key, or a dimension of an MDC table.
7: The new referential constraint definition conflicts with existing referential constraints when BEFORE triggers and generated columns are considered.
8: The new referential constraint will cause another pair of conflicting referential constraints to co-exist in the delete-connected graph when BEFORE trigger and generated columns are considered.

User response

Respond according to the reason code:

1: Change the delete rule to CASCADE or NO ACTION or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.
2: Change the delete rule to NO ACTION, RESTRICT, or SET NULL or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.
3: Do not specify a column in the foreign key definition that is already contained in the existing foreign key definition or if the delete rule of the existing referential constraint is not SET NULL, change the delete rule of the new constraint to be the same.
4: Change the delete rule to NO ACTION, RESTRICT, or SET NULL or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.
5: Do not specify a column in the foreign key definition that is already contained in the existing partitioning key definition or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.
6: Change the delete rule to NO ACTION, RESTRICT, or SET NULL or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.
7: Change the delete rule to NO ACTION or remove the triggers fired by the referential constraint or do not create the new referential constraint.
8: Change the delete rule to NO ACTION, RESTRICT, or SET NULL or eliminate the particular FOREIGN KEY clause from ALTER TABLE statement.

sqlcode: -20255

sqlstate: 42915

SQL20256N FOREIGN KEY `name` is not valid because it would cause two tables `table-name1` and `table-name2` to be delete-connected to each other while they are delete -connected to the same ancestor table `ancestor-table-name` through CASCADE relationships. Reason code = `reason-code`.

Explanation

When two tables are delete-connected to the same table through CASCADE relationships, the two tables must not be delete-connected to each other where the delete rule of the last relationship in each path is RESTRICT or SET NULL. The delete rule specified in a FOREIGN KEY clause of the CREATE TABLE or ALTER TABLE statement is not valid for the reason specified by the reason-code as follows:

The relationship is specified with a delete rule of RESTRICT or SET NULL and would cause the two tables to be delete-connected to each other.
The relationship is specified with a delete rule of CASCADE and would cause two tables to be delete-connected to each other where the delete rule of the last relationship in each path is RESTRICT or SET NULL. The statement cannot be processed.

User response

The action corresponding to the reason code is:

Change the delete rule to CASCADE or NO ACTION or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.
Change the delete rule to NO ACTION, RESTRICT, or SET NULL or eliminate the particular FOREIGN KEY clause from the CREATE TABLE or ALTER TABLE statement.

sqlcode: -20256

sqlstate: 42915

SQL20257N FINAL TABLE is not valid when the target view `viewname` of the SQL data change statement in a fullselect has an INSTEAD OF trigger defined.

Explanation

The fullselect includes an INSERT or UPDATE statement that targets a view that has an INSTEAD OF trigger defined for the SQL data change operation. The FINAL TABLE results of an INSTEAD OF trigger cannot be returned.

The statement cannot be processed.

User response

Change FINAL TABLE to NEW TABLE or drop the INSTEAD OF trigger.

sqlcode: -20257

sqlstate: 428G3

SQL20258N Invalid use of INPUT SEQUENCE ordering.

Explanation

The ORDER BY clause specifies INPUT SEQUENCE and the FROM clause of the fullselect does not specify an INSERT statement.

The statement cannot be processed.

User response

Use INPUT SEQUENCE when the FROM clause of the fullselect specifies an INSERT statement.

sqlcode: -20258

sqlstate: 428G4

SQL20259N Column `column-name` cannot be selected from the target of the data change statement in the FROM clause of the fullselect.

Explanation

A column has been selected that cannot be specified in the select list of this query. This column is based on a column in a view or fullselect that is the target of an INSERT or UPDATE statement that is specified in a FROM clause using either NEW TABLE or FINAL TABLE. The column selected is based on:

A subquery
A function that reads or modifies SQL data
A function that is deterministic or has external action
An OLAP function
A next value for a sequence.

The statement cannot be processed.

User response

Change the select list to remove the column.

sqlcode: -20259

sqlstate: 428G6

SQL20260N The assignment clause of the UPDATE statement must specify at least one column that is not an INCLUDE column.

Explanation

The UPDATE statement has specified INCLUDE columns and the only assignments are made to INCLUDE columns. At least one of the assignments in an UPDATE statement must be to a column of the target of the UPDATE statement.

The statement cannot be processed.

User response

Change the statement to specify an assignment to a column of the target of the UPDATE statement.

sqlcode: -20260

sqlstate: 428G5

SQL20261N Invalid row movement to table `table-name` within the UNION ALL view `view-name`.

Explanation

A check-constraint on table table-name caused an updated row to be rejected. When attempting to insert the row into another underlying table of view-name, the table table-name accepted the row. The destination table of a moved row cannot be identical to the table from which it originated.

The statement cannot be processed.

User response

Verify the interaction between before update and before insert triggers on the target table. When performing row movement, DB2 executes before update triggers first, which may modify the row. The rejected row may then be processed by before insert triggers which may again modify the row in a way that it is now accepted by the check constraints of the target table.

Change the triggers so that this does not happen.

sqlcode: -20261

sqlstate: 23524

SQL20262N Invalid usage of WITH ROW MOVEMENT in view `view-name`. Reason code = `reason-code`.

Explanation

The view view-name has been defined with the WITH ROW MOVEMENT clause. This clause is not applicable for the view because of one of the following:

The view's outermost fullselect is not a UNION ALL.
The view contains nested UNION ALL operations other than in the outermost fullselect.
Not all view columns are updatable.
Two columns of the view are based on the same column of the base table.
One of the underlying views has an INSTEAD OF UPDATE trigger defined on it.
The view contains references to system-period temporal tables or application-period temporal tables.

The view cannot be created.

User response

Depending on the reason code, do the following:

Omit the WITH ROW MOVEMENT clause. It is not applicable for views without UNION ALL.
Rewrite the view body so that UNION ALL only occurs on the outermost fullselect.
Omit columns that are not updatable from the view definition.
Rewrite the view body so that each column of a base table is only referenced once in the view definition.
Omit the WITH ROW MOVEMENT clause and use an INSTEAD OF UPDATE trigger on the newly defined view.
Remove any reference to a system-period temporal table or an application temporal table.

User response

Rewrite the query so that the table function is the last table reference in the FROM clause.
Move the table function to be in the outermost subselect.
Remove the table reference not correlated to in the table function or include an argument in the table function that correlates to the table reference.
Remove the table function from the body of the view definition.
Remove the table function from the fullselect in the XQuery context.
Remove the OUTER JOIN operator or do not place table function in a nested explicit join.
Remove the function from the default clause for the global variable or parameter of a procedure definition.

For reason codes 1, 2, and 3, the query can be rewritten using a common table expression to isolate the table function invocation. For example:

SELECT c1 FROM
   (SELECT c1 FROM t1, t2,
     TABLE(tf1(t1.c1) AS tf), t3)
      AS x, t4

Can be rewritten as:

WITH cte1 AS (SELECT c1 FROM t1,
   TABLE(tf1(t1.c1)) AS tf),
   x AS (SELECT c1 FROM t2, cte1, t3)
     SELECT c1 FROM x, t4;

sqlcode: -20267

sqlstate: 429BL

SQL20268N Collation cannot be applied. Reason code = `reason-code`.

Explanation

The collation cannot be applied for reason code reason-code. The reason codes and their meanings are:

1: The result of applying the collation is greater than 32,742 bytes.
2: The collation cannot be applied to the functions LOCATE, POSITION, and POSSTR.
3: The collation cannot be applied to a LIKE predicate or %WLDCRD function.
4: The collation cannot be applied to a FOR BIT DATA string.

User response

For each reason code:

1: The generated collation key is longer than the source string and is dependent on the collation selected. Either reduce the length of the source string, or choose a different collation.
2: Either omit the function or specify a non-UCA collation.
3: Either omit the function or specify a non-UCA collation.
4: Do not use a FOR BIT DATA string.

sqlcode: -20268

sqlstate: 429BM

SQL20269N The nickname `nickname` cannot be referenced in an enforced referential constraint.

Explanation

A referential constraint in the CREATE TABLE statement or the ALTER TABLE statement is invalid. The constraint definition specifies ENFORCED but refers to a nickname.

The statement cannot be processed.

User response

Change the referential constraint to specify NOT ENFORCED or remove the reference to the nickname.

sqlcode: -20269

sqlstate: 428G7

SQL20271W The name at ordinal position `number` in the statement, with name `name`, was truncated.

Explanation

At least one name in the described statement was truncated. The first name that was truncated is identified by the name name and ordinal position number. If performing a describe output of a prepared query, the ordinal position is relative to the select list column of the query. If performing a describe output of a CALL statement, the ordinal position is relative to the OUT or INOUT parameters of the procedure to which the CALL resolved. If performing a describe input of a CALL statement, the ordinal position is relative to the IN or INOUT parameters of the procedure to which the CALL resolved.

The column name, parameter name, user defined type name or user defined type schema name was either too long, or became too long after code page conversion.

Note that the length of the column name, parameter name, user defined type name, and user defined type schema name are limited when using the SQLDA structure.

User response

If the exact name or schema is significant, do one of the following:

Use a client whose code page does not cause expansion beyond the supported maximum length.
For a column name, change the table, view, or nickname so the column has a shorter name.
For a parameter name, change the procedure so the parameter has a shorter name.
For a user defined type name or user defined type schema name, drop and recreate the user defined type with a shorter name.
For a schema name, change the table, view, procedure, or user defined type.

sqlcode: +20271

sqlstate: 01665

SQL20273N The nickname statistics cannot be updated because the database is not enabled for federation.

Explanation

An error has been detected at the instance level. The requested operation cannot be completed because federation was not enabled for the instance.

User response

Set the DBM variable FEDERATED to YES and then restart the database manager.

sqlcode: -20273

sqlstate: 55056

SQL20274W Some of the nickname statistics cannot be updated.

Explanation

User response

If the truncation has caused an unexpected consequence, then expand the length of the target column and issue the statement again.

sqlcode: +20277

sqlstate: 01004

SQL20278W The view `viewname` may not be used to optimize the processing of queries.

Explanation

The fullselect of the view includes elements that prohibit the use of the statistics on the view for optimization when the view is not referenced directly in a query. These elements of the fullselect may include:

aggregations functions
distinct operations
set operations (union, except or intersect).

The view is successfully altered to enable query optimization.

User response

No action is required. If the intent of the view is to optimize queries that do not directly reference the view, then the view could be dropped or have query optimization disabled. You could also consider defining the fullselect of the view to remove the elements that prevent optimization.

sqlcode: +20278

sqlstate: 01667

SQL20279N The view `view-name` cannot be enabled for query optimization. Reason code = `reason-code`.

Explanation

The view cannot allow the ENABLE QUERY OPTIMIZATION option because the fullselect does not conform to the set of queries that can take advantage of this capability. The statement failed for one of the following reasons:

The view directly or indirectly references an existing materialized query.
The view is a typed view.
The view references a function with external action.

The statement cannot be processed.

User response

The action is based on the reason code as follows:

Remove the reference to the materialized query table.
There is no way to specify statistics on a typed view. It may be possible to define a similar view that is not a typed view to enable optimization using the statistics.
Remove the reference to any function with external action from the view query.

sqlcode: -20279

sqlstate: 428G8

SQL20280W Insufficient permission to create or write to the `log-file-path` file.

Explanation

The statement completed successfully, but the user does not have permission to create or write to the specified log file, log-file-path.

User response

To write the details to the log file, make sure that you have write access to the log file path specified. Alternatively, omit the optional log file path.

sqlcode: -20280

sqlcode: 42501

SQL20282N .NET procedure or user-defined function `name`, specific name `specific-name` could not load .NET class `class`. Reason code `reason-code`.

Explanation

The .NET class given by the EXTERNAL NAME clause of a CREATE PROCEDURE or CREATE FUNCTION statement could not be loaded. The reason codes are:

The assembly of the .NET routine was not found.
Class was not found in the assembly specified.
A method with types matching those specified in the database catalogs could not be found in the class specified.

User response

Ensure that correct assembly file is given, including any file extensions. If the full path is not specified, ensure that only one instance of the assembly exists in the system PATH, as the first instance of the assembly found in the PATH will be loaded.
Ensure that the assembly was specified correctly as described in response 1. Ensure that the case sensitive class name was specified correctly and that it exists in the specified assembly.
Ensure that the class was specified correctly as described in response 2. Ensure that the case sensitive method name was specified correctly, and that it exists in the specified class as a "public static void" method.

sqlcode: -20282

sqlstate: 42724

SQL20284N No plan was possible to create for federated data source `server-name`. Reason = `reason-code`.

Explanation

While building a federated query access plan, the query fragment for one or more data sources cannot be processed because of a missing predicate or a problem with the query syntax as indicated by the following reason code:

A required predicate is missing.
A predicate that can be processed by the data source is combined with another predicate either using the OR operator or a BETWEEN predicate.

User response

See the federation documentation for this data source. Correct the query syntax as needed and resubmit the statement. The action corresponding to the reason code is:

Supply the missing predicate.
Change the statement syntax so that predicates for one data source are separated from predicates for another data source using the AND operator and not the OR operator.

sqlcode: -20284

sqlcode: 429BO

SQL20285N The statement or command was not processed either because the table named `table-name` has detached dependent tables or because an asynchronous partition detach operation on the table is not complete. Reason code = `reason-code`.

Explanation

You can manage ranges of data by using data partitioning. The process of detaching a data partition occurs in two phases:

The ALTER TABLE...DETACH PARTITION operation logically detaches the data partition from the partitioned table.
An asynchronous partition detach task converts the logically detached partition into a stand-alone table.

Tables that need to be incrementally maintained with respect to the detached data partition are referred to as detached dependent tables.

The specific reason this message was returned is indicated by the value of the runtime token reason-code:

1: The table is the target table of a detach operation and has detached dependents which are required to be incrementally maintained with respect to the current content of this table in order to preserve the integrity of the detached dependents. The statement or command is not allowed because execution of the statement or command would prevent the detached dependents from being incrementally maintained.
2: The table is the target table of a detach operation and is not available because the asynchronous partition detach task has not completed.
3: The table is the source table of a detach operation and has logically detached partitions. The statement or command is not allowed because the asynchronous partition detach task has not completed.
4: The table has detached partitions and there are dependent tables that need to be incrementally maintained with respect to these detached partitions. The statement is not allowed because execution of the statement or command would invalidate the incremental maintenance of the dependent tables.

User response

Respond to this message according to the reason code:

1

Respond to reason code 1 in one of the following two ways:

Maintain the detached dependent tables and then re-issue the statement or command by performing the following steps:
1. Identify the detached dependents of the table by querying the SYSCAT.TABDETACHEDDEP catalog view.
2. Perform integrity processing on the detached dependent tables by issuing the SET INTEGRITY statement with the IMMEDIATE CHECKED option.
3. Re-issue the statement or command.
Issue the statement or command without maintaining the detached dependent tables by performing the following steps:
1. Reset the detached property of the table by issuing the SET INTEGRITY statement with the FULL ACCESS option on the table.
2. Re-issue the statement or command.
3. Perform full integrity processing on subsequent SET INTEGRITY statements on the remaining detached dependent tables.

2

Monitor the progress of the asynchronous partition detach task by using the LIST UTILITIES command and wait for the asynchronous partition detach task to complete. Then re-issue the statement or command.

3

Perform the following steps:

Monitor the progress of the asynchronous partition detach task by using the LIST UTILITIES command and wait for the asynchronous partition detach task to complete.
Verify that the table does not have any logically detached partitions by querying the SYSCAT.DATAPARTITIONS catalog view
Re-issue the statement or command.

4

Perform the following steps:

Identify the dependent tables that require incremental maintenance.
Perform integrity processing on the dependent tables by issuing the SET INTEGRITY statement with the IMMEDIATE CHECKED option on these dependent tables.
Re-issue the statement or command.

sqlcode: -20285

sqlstate: 55057

SQL20287W The environment of the specified cached statement is different than the current environment. The current environment will be used to reoptimize the specified SQL statement.

Explanation

The Explain facility has been invoked to explain a statement that has been previously reoptimized with REOPT ONCE, but the current environment is different than the environment in which the cached statement was originally compiled. The current environment will be used to reoptimize the specified statement.

The statement will be processed.

User response

In order to ensure that the plan matches the plan in the cache, reissue the EXPLAIN in an environment that matches the one in which the original statement was reoptimized and cached.

sqlcode: -20287

sqlstate: 01671

SQL20288N Statistics could not be updated for the object. Object name: `object-name`. Object type: `object-type`. Reason code: `reason-code`.

Explanation

You can collect and update statistics on tables, indexes, and statistical views to provide the optimizer with accurate information for access plan selection by using the RUNSTATS utility.

This message is returned when statistics could not be collected. The reason code indicates the cause of the failure:

1: Collecting statistics is not supported for the target object.
2: An attempt was made to collect statistics for a view, but RUNSTATS options were specified that are not supported with a view. For example, the FOR INDEXES option cannot be specified for views.
3: Updating some statistics of the target object type is not supported. For example, data page statistics is not applicable for XML paths indexes.

User response

Respond to this error according to the reason code:

1: Verify that collecting and updating statistics is supported for the specified object. For example, to enable the collecting and updating of statistics for a view, execute the ALTER VIEW statement specifying the ENABLE QUERY OPTIMIZATION clause.
3: Remove RUNSTATS options that are not supported with a view.
3: Verify that the statistics of the target object type can be updated.

sqlcode: -20288

sqlstate: 428DY

SQL20289N Invalid string unit `unit` in effect for function `function-name`.

Explanation

The statement invoked a built-in function using a string unit that is not valid with the type of data that is provided to the function. This can occur for the following reasons:

non-string data that is specified with an explicit string length unit unit for the LENGTH function. A string unit cannot be specified for non-string data.
bit data or binary data was specified with a string unit other than OCTETS. CODEUNITS16 or CODEUNITS32 are not valid with bit data or binary string data.
OCTETS was used with graphic data and the start parameter is not odd or the length parameter is not even.
OCTETS was used with graphic data and the start parameter is not odd.
OCTETS was used with graphic data and the code-units parameter is not even.
OCTETS or CODEUNITS16 was used with a source string argument that is defined as CODEUNITS32.

The statement cannot be executed.

User response

Change the invocation of the function to remove the invalid string length unit or change it to a unit valid for the data type and values being processed.

sqlcode: -20289

sqlstate: 428GC

SQL20290N The SQL statement references routine `routine-name` (specific name `specific-name`) which cannot be run on partition `partition-number`.

Explanation

The routine routine-name (specific name specific-name) was called with an invalid partition number partition-number.

User response

Specify -1 for the partition number parameter to run the routine on the current partition.

sqlcode: -20290

sqlstate: 560CA

SQL20296N The ALTER TABLE statement is not allowed as it modifies some physical attribute of the table `table-name` that has detached partitions and there are dependant tables that need to be incrementally maintained with respect to these detached partitions.

Explanation

The table has detached partitions and there are dependent tables that need to be incrementally maintained with respect to these detached partitions. Modifying any physical attribute of the table is not allowed as doing so would invalidate the incremental maintenance of the dependent tables. The restricted physical attribute modification may include: adding a column, altering a column; or altering one of the following values: data capture, value compression, append, compact, logged, activate not logged initially.

User response

Identify the dependent tables that require incremental maintenance and issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option on these dependent tables before re-issuing the failing ALTER TABLE statement.

The following query can be used to identify the dependent tables that require incremental maintenance, where <schema name> is the qualifier of table-name and <table name> is the table name portion of table-name.

WITH
DEP_CNT(TOTAL_DEP) AS (SELECT COUNT(*) FROM 
  SYSCAT.TABDEP),
DEP_TAB(SCHEMA, NAME, TYPE, PROPERTY, REFRESH, 
  STATUS, CONST_CHECKED, LEVEL) AS
  (SELECT TABLES.TABSCHEMA, TABLES.TABNAME, 
       TABLES.TYPE, TABLES.PROPERTY, 
       TABLES.REFRESH, TABLES.STATUS, 
       TABLES.CONST_CHECKED, 0
   FROM SYSCAT.TABLES TABLES
   WHERE TABLES.TABSCHEMA='<schema name>' 
   AND TABLES.TABNAME='<table name>'
     UNION ALL
   SELECT TABDEP.TABSCHEMA, TABDEP.TABNAME, 
       TABDEP.DTYPE, TABLES.PROPERTY, 
       TABLES.REFRESH, TABLES.STATUS, 
       TABLES.CONST_CHECKED, 
       DEP_TAB.LEVEL + 1
   FROM SYSCAT.TABDEP TABDEP, DEP_TAB, 
     SYSCAT.TABLES TABLES
   WHERE TABDEP.DTYPE IN ('S', 'V', 'W', 'T') 
       AND TABDEP.BSCHEMA = DEP_TAB.SCHEMA 
       AND TABDEP.BNAME = DEP_TAB.NAME 
       AND TABLES.TABSCHEMA = TABDEP.TABSCHEMA
       AND TABLES.TABNAME = TABDEP.TABNAME
       AND DEP_TAB.LEVEL < (SELECT 
         DEP_CNT.TOTAL_DEP FROM DEP_CNT))
SELECT DISTINCT * FROM
(SELECT DEP_TAB.SCHEMA, DEP_TAB.NAME 
 FROM DEP_TAB
 WHERE STATUS='C'
       AND (DEP_TAB.TYPE = 'S' 
       AND DEP_TAB.REFRESH = 'I' 
       AND SUBSTR(DEP_TAB.CONST_CHECKED,5,1)<>'F'
       OR DEP_TAB.TYPE = 'T' 
       AND SUBSTR(DEP_TAB.PROPERTY,2,1) = 'Y' 
       AND SUBSTR(DEP_TAB.CONST_CHECKED,7,1)<>'F')
 ) X;

sqlcode: -20296

sqlstate: 55057

SQL20302W More table spaces than required were specified in the IN or LONG IN clause. The extra table spaces are ignored.

Explanation

If the table being created is a non-partitioned table, then more than one table space is specified in the IN or LONG IN clause. The first table space specified is used to store the table data or long data. Only one table space should be specified in the IN or LONG IN clause for a non-partitioned table.

If the table being created is a partitioned table, then the number of table spaces specified in the IN or LONG IN clause was more than the number of partitions defined for the table. When adding partitions to a partitioned table that was created with the long data in the same table space as regular data, the LONG IN clause provided in the ADD PARTITION clause is ignored.

The statement was processed successfully, but the extra table spaces were ignored.

User response

None.

sqlcode: +20302

sqlstate: 01675

SQL20303N The partitioned unique index was not created because either the index definition did not include all of the partitioning columns, or the index was being created over XML data.

Explanation

You can create a partitioned index for a partitioned table by specifying the PARTITIONED clause in the CREATE INDEX statement. When you create a partitioned index for a partitioned table, each data partition is indexed in its own index partition.

When you are defining a partitioned unique index, you must include all columns from the table-partitioning key in the index key. Note that any column that is included in an expression-based index key must also be specified plainly, without being included as part of an expression.

This message can be returned for different reasons:

An attempt was made to create a partitioned unique index that fails to plainly include one or more of the columns from the table-partitioning key.
An attempt was made to create a unique partitioned index over XML data on a partitioned table.

User response

Submit the CREATE INDEX statement again, specifying all the partitioning columns in the index key.

sqlcode: -20303

sqlstate: 42990

SQL20304N The index was not created because of a problem with an XMLPATTERN clause or a column of data type XML. Reason code: `reason-code`.

Explanation

This message can be returned when a column defined with a data type of XML is included (or missing) in the columns for the index.

The reason code indicates what is invalid about the index definition:

1: There is one column specified that is defined with a data type of XML, but the definition is missing the XMLPATTERN clause to specify the index keys to generate for each XML value.
2: An XMLPATTERN clause is present but there are multiple index columns specified.
3: The index is defined as UNIQUE and the XMLPATTERN includes descendant, descendant-or-self axes, '//', xml-wildcards, node(), or processing-instruction().
4: An XMLPATTERN clause is present but the single index column specified is not defined with data type XML.
5: The path expression specified in the XMLPATTERN clause does not start with '/' or '//'.
6: The pattern expression does not include a name test or kind test following '/'.
7: The pattern expression uses an unsupported axis in an axis step. Only the following forward axes are supported: child, attribute, descendant, self, and descendant-or-self.
8: The pattern expression specifies an invalid kind test, or some syntax that is not a valid name test or kind test as supported in a pattern expression.
9: The pattern expression specifies a predicate (an expression enclosed in square brackets), but predicates are not supported for pattern expressions in an XMLPATTERN clause.
10: The pattern expression includes other XQuery syntax not supported in an XMLPATTERN clause to which the preceding reason codes do not apply.
11: On a z/OS database server, the number of steps in the pattern expression exceeds the limit of 50 steps when it does not contain a function, or 44 steps when it does contain a function.
12: The argument of a function in the pattern expression does not follow the requirements as specified in the syntax, or the index data type is not correct.
13: The index is defined with at least one part of the index key based on an XMLPATTERN expression.

User response

Respond to this error according to the reason code:

1

Specify the XMLPATTERN clause.

2

If the index is intended as a key for XML data, ensure that only one column defined with a data type of XML is specified. Otherwise, remove the XMLPATTERN clause.

3

Either remove the UNIQUE specification or change the XMLPATTERN definition to remove descendant, descendant-or-self axes, '//', xml-wildcards, node(), or processing-instruction().

4

Either remove the XMLPATTERN clause or change the specified column to a single column defined with a data type of XML.

5

Ensure that the pattern expression begins with '/' or '//'. The XQuery syntax for which '/' or '//' is an abbreviation is not supported for the XMLPATTERN clause of the CREATE INDEX statement.

6

Add a name test or a kind test to the pattern expression following '/'.

7

Remove any reverse axes from the pattern expression. For example, the parent axis must not be used in a pattern expression.

8

Check the pattern expression for kind tests or name tests that do not match the syntax specification for the XMLPATTERN clause of the CREATE INDEX statement. Correct or remove unsupported syntax.

9

Remove any predicates from the pattern expression in the XMLPATTERN clause.

10

Change the pattern expression to remove any syntax that is not supported for the XMLPATTERN clause of the CREATE INDEX statement. If the pattern expression includes an asterisk ('*'), ensure that the name tests in the pattern expression correctly use the wildcard. If used with a QName, there must be a colon before or after the wildcard character, otherwise it becomes a multiplication operator. The asterisk cannot be used as a multiplication operator in the pattern expression. You should also ensure that the XMLPATTERN clause does not include any other XQuery operators, XQuery functions or XQuery FLWR expressions (for, let, where, return).

11

Reduce the number of steps in the pattern expression. If the value to be indexed in the XML documents requires more than 50 steps, the index cannot be created.

12

Check the argument of the function in the pattern expression to determine whether it matches the syntax specification of the XMLPATTERN clause of the CREATE INDEX statement, and check the data type of the indexed values. Also, check that the function you specified is supported in the XMLPATTERN clause. Correct or remove unsupported syntax.

13

Modify the index definition in one of the following ways:

Remove the XMLPATTERN clause
Remove any expression-based keys

sqlcode: -20304

sqlstate: 429BS

SQL20305N An XML value cannot be inserted or updated because of an error detected when inserting or updating the index identified by `index-id` on table `table-name`. Reason code = `reason-code`. For reason codes related to an XML schema the XML schema identifier = `xml-schema-id` and XML schema data type = `xml-schema-data-type`.

Explanation

The statement cannot proceed and the table and index remain unchanged because of one of the following reason codes. The schema data type and schema identifier are not applicable for all reason codes. See specific reason codes for details.

1: At least one XML node value in an XML value being inserted or updated exceeds a length constraint imposed by the identified index. The index on an XML column is defined to use the SQL type VARCHAR with a specific length specified. The length of one or more XMLPATTERN result values exceeds the user-specified length constraint for the VARCHAR data type.
2: On database servers Version 9.5 and earlier, at least one XML node value in an XML value being inserted or updated is a list data type node that cannot be indexed by the identified index. List data type nodes are not supported in the index.
3: On database servers Version 9.5 and earlier, at least one XML node value is a valid XML value but cannot be cast to the schema data type because it is outside the supported range of values.
4: At least one XML node value is a valid XML value but cannot be cast to the index data type for the identified index because it is outside the supported range of values.
5: At least one XML node value is an invalid XML value for the target index data type for the identified index and the index definition specifies that invalid XML values must return an error (REJECT INVALID VALUES).

User response

Determine the index name and the XML pattern clause.

The index name (<index-name>,<index-schema>) can be obtained from SYSCAT.INDEXES by issuing the following query using index-id:

SELECT INDNAME,INDSCHEMA
  FROM SYSCAT.INDEXES
  WHERE IID =<index-id> AND 
    TABSCHEMA ='schema ' AND TABNAME ='<table-name>'

After the index name (<index-name>,<index-schema>) is found, it can be used to obtain the index data type and XML pattern from SYSCAT.INDEXES by issuing the following query:

SELECT DATATYPE, PATTERN
FROM SYSCAT.INDEXXMLPATTERNS
WHERE INDSCHEMA = 'index-schema' AND 
 INDNAME = 'index-name'

If the <xml-schema-id> is not "*N", obtain the name of the XML schema containing the identified schema data type by issuing the following query using <xml-schema-id>:

SELECT OBJECTNAME
  FROM SYSCAT.XSROBJECTS
  WHERE OBJECTID = '<xml-schema-id>'

1: Use the XML pattern to identify the set of matching XML nodes to determine which string values exceed the length constraint specified by the index. If the XML pattern contains an fn:upper-case function, it is possible that for certain languages the string value after uppercase conversion exceeds the length constraint specified by the index.
2: On database servers Version 9.5 and earlier, use the XML schema name and XML pattern to identify the set of matching XML nodes to inspect and compare against the identified schema data type xml-schema-data-type found in the corresponding schema. Determine which node values are list data type nodes.
3: On database servers Version 9.5 and earlier, use the XML schema name and XML pattern to find the set of matching XML nodes to inspect and compare against the identified schema data type xml-schema-data-type found in the corresponding schema. Determine which node values exceed the supported range of values for the XML schema data type.
4: Use the XML pattern to identify the set of matching XML nodes to inspect and compare against the data type specified for the identified index. Determine which node values exceed the supported range of values for the index data type.
5: Use the XML pattern to identify the set of matching XML nodes to inspect and compare against the data type specified for the index being created. Determine which node values are invalid for the index data type.

sqlcode: -20305

sqlstate: 23525

SQL20306N An index on an XML column cannot be created because of an error detected when inserting the XML values into the index. Reason code = `reason-code`. For reason codes related to an XML schema the XML schema identifier = `xml-schema-id` and XML schema data type = `xml-schema-data-type`.

Explanation

The statement cannot proceed and the table remains unchanged because of one of the following reason codes. The schema data type and schema identifier are not applicable for all reason codes.

1: At least one XML node value in an XML value being inserted exceeds a length constraint imposed by the index that is being created. The index on an XML column is defined to use the SQL type VARCHAR with a specific length specified. The length of one or more XML pattern result values exceeds the user-specified length constraint for the VARCHAR data type.
2: On database servers Version 9.5 and earlier, at least one XML node value in an XML value being inserted into the index during index creation is a list data type node that cannot be indexed by the specified index. List data type nodes are not supported in the index.
3: On database servers Version 9.5 and earlier, at least one XML node value is a valid XML value but cannot be cast to the schema data type because it is outside the supported range of values.
4: At least one XML node value is a valid XML value but cannot be cast to the index data type for the identified index because it is outside the supported range of values.
5: At least one XML node value is an invalid XML value for the target index data type for the identified index and the index definition specifies that invalid XML values must return an error (REJECT INVALID VALUES).

User response

Determine the XML pattern clause from the index definition.

If the <xml-schema-id> is not "*N", obtain the name of the XML schema containing the identified schema data type by issuing the following query using <xml-schema-id>:

SELECT OBJECTNAME
  FROM SYSCAT.XSROBJECTS
  WHERE OBJECTID = '<xml-schema-id>'

1

Use the XML pattern to identify the set of matching XML nodes to determine which string values exceed the length constraint specified by the index. If the XML pattern contains an fn:upper-case function, it is possible that for certain languages the string value after uppercase conversion exceeds the length constraint specified by the index.

2

On database servers Version 9.5 and earlier, use the XML pattern to identify the set of matching XML nodes to inspect and compare against the identified schema data type xml-schema-data-type found in the corresponding schema. Determine which node values are list data type nodes.

3

On database servers Version 9.5 and earlier, use the XML pattern to find the set of matching XML nodes to inspect and compare against the identified schema data type xml-schema-data-type found in the corresponding schema. Determine which node values exceed the supported range of values for the XML schema data type.

4

Use the XML pattern to identify the set of matching XML nodes to inspect and compare against the data type specified for the index. Determine which node values exceed the supported range of values for the index data type.

5

Use the XML pattern to identify the set of matching XML nodes to inspect and compare against the data type specified for the index. Determine which node values are invalid for the index data type.

If the index is partitioned and there are newly attached partitions (that is, partitions with the value of 'A' in the STATUS column in the SYSDATAPARTITIONS catalog table), you might not be able to find the invalid node values for the index data type. In this situation, run the SET INTEGRITY statement to bring the newly attached partitions online. The invalid node values might be removed by the SET INTEGRITY statement for violating other constraints, so issue the statement again. If the statement cannot be processed again, then check for invalid node values for the index data type.

sqlcode: -20306

sqlstate: 23526

SQL20307N Table `source-tablename` cannot be attached to table `target-tablename`. Reason code = `reason-code`.

Explanation

The characteristics of the target table do not sufficiently match the characteristics of the source table in the ALTER TABLE...ATTACH statement. The reason-code indicates the type of mismatch.

1: The number of columns (the COLCOUNT column in SYSCAT.TABLES) of the source and target table does not match.
2: The compression clauses in the source and target tables do not match. There are two possible mismatches: VALUE COMPRESSION and COMPRESSION SYSTEM DEFAULT do not match; and COMPRESS YES STATIC and COMPRESS YES ADAPTIVE do not match.
3: The APPEND mode of the tables does not match.
4: The code pages of the source and target table do not match.
5: The source table is a partitioned table with more than one data partition or with ATTACHED or DETACHED data partitions. A source table that is partitioned can only be attached if it has only one data partition and no ATTACHED or DETACHED data partitions.
6: The source table is a system table, a view, an MQT, a typed table, a table ORGANIZED BY KEY SEQUENCE, a created temporary table, a declared temporary table, a system-period temporal table, or a history table. The source table must be a base table that does not correspond to any of the listed tables.
7: The target and source table are the same. You cannot attach a table to itself.
8: The NOT LOGGED INITIALLY clause was specified for either the source table or the target table, but not both.
9: The DATA CAPTURE CHANGES clause was specified for either the source table or the target table, but not both.
10: The distribution clauses of the tables do not match. The distribution key must be the same for the source table and the target table.
11: The ORGANIZE BY DIMENSIONS clauses of the tables do not match. Only one of the tables has an ORGANIZE BY DIMENSIONS clause specified or the organizing dimensions are different.
15: The security protection on the tables is not identical. The tables are protected by different security policies.
16: Compress clause (COMPRESS NO or COMPRESS YES) does not match.
17: The source table does not have an index that is compatible with the index on the target partitioned table. The index on the target table is either unique or defined on an XML column with the REJECT INVALID VALUES clause.
18: The source table does not have an index that is compatible with the index on the partitioned table and the REQUIRE MATCHING INDEXES clause is specified on the ALTER TABLE statement.
19: The record format of the XML column of source table being attached is not compatible with the record format of the target partitioned table.
20: The source table has row access control activated but the target table does not have row access control activated.
21: The source table has column access control activated with at least one enabled column mask but the target table does not have column access control activated.
22: The target table is an application-period temporal table, but the source table is not an application-period temporal table that contains equivalent BUSINESS_TIME period columns.
30: Only one of the source or target table is a random distribution table using the random by generation method.
99: The target table exists in a table space that has been converted to a large table space, and its indexes have not yet been reorganized or rebuilt to support large RIDs.

User response

Correct the mismatch between the tables by modifying the source table to match the characteristics of the target table, or by modifying the target table to match the characteristics of the source table.

For reason codes 4, 5, 6 and 11 the characteristics of neither the source or target table can be easily modified for compatibility. (For reason code 5, source-tablename is partitioned, so it must have exactly one visible (neither attached or detached) data partition.)

In cases where it is difficult to modify the characteristics of either the source or target table for compatibility, create a new table that is compatible with the target table, and copy the data from source-tablename to this new table.

1

You can add columns to the table which is missing one or more columns using the statement:

ALTER TABLE ...
  ADD COLUMN ...
  DEFAULT ...

Make sure that the default value of the added column is the same as the default value of the column in the table where the column already exists. Query SYSCAT.COLUMNS.IMPLICITVALUE for the column in the table where the column already exists. If the value is not NULL, then use the default clause of the ALTER TABLE ADD COLUMN statement to set the default value to match the value in SYSCAT.COLUMNS.IMPLICITVALUE.

Note: You can only add columns after the last existing column of the table. If the missing column is not the last column, create a new source table.

Alternatively, you can drop a column from the table that has the extra column:

ALTER TABLE ... DROP COLUMN ....

2

To achieve agreement between value compression values use one of the following statements:

ALTER TABLE ... ACTIVATE VALUE COMPRESSION

ALTER TABLE ... DEACTIVATE VALUE
    COMPRESSION

To achieve row compression agreement use one of the following statements:

ALTER TABLE ... COMPRESS YES

ALTER TABLE ... COMPRESS NO

To achieve adaptive compression agreement use one of the following statements:

ALTER TABLE ... COMPRESS YES ADAPTIVE

ALTER TABLE ... COMPRESS YES STATIC

3

To achieve append mode agreement use one of the following statements:

ALTER TABLE ... APPEND ON

ALTER TABLE ... APPEND OFF

4

Create a new source table.

5

Detach data partitions from the source table until there is a single visible data partition using the statement:

ALTER TABLE ... DETACH PARTITION

Include any necessary set integrity statements. Otherwise, create a new source table.

6

Create a new source table that is not one of the disallowed types.

7

Determine the correct table to use as the source or target table.

8

Either make the table which is not logged initially be logged by issuing the COMMIT statement, or make the table which is logged be not logged initially by entering the statement:

ALTER TABLE .... ACTIVATE NOT LOGGED
    INITIALLY

9

To enable data capture changes on the table that does not have data capture changes turned on, run the statement:

ALTER TABLE ... DATA CAPTURE CHANGES

To disable data capture changes on the table that does have data capture changes turned on, run the statement:

ALTER TABLE ... DATA CAPTURE NONE

10

It is recommended that you create a new source table in this case, because you cannot change the distribution key of a table spanning multiple database partitions. To change a distribution key on tables in single-partition database, run the statements:

ALTER TABLE ... DROP DISTRIBUTION

ALTER TABLE ... ADD DISTRIBUTION
    (key-specification)

11

Create a new source table which matches the target table with respect to the ORGANIZE BY DIMENSIONS clause.

15

The two tables must be protected using the same security policy, have the same column defined as type SYSPROC.DB2SECURITYLABEL, and have the same set of protected columns.

16

Use ALTER TABLE tablename COMPRESS [YES | NO], to ensure that the COMPRESS attributes of the source and target table match.

17

To find the list of indexes on the target partitioned table that do not have a compatible index on the source table, see the administration notification log. Create the unique or XML index on the source table that is compatible with the target partitioned table.

18

Do one of the following:

To find the list of indexes on the target table that do not have a compatible index on the source table, see the administration notification log. Create the missing index on the source table and try the request again.
Remove the REQUIRE MATCHING INDEXES clause from the ALTER TABLE statement and try the request again.

19

Update the XML record format of the source table to match the record format of the target table. There are several methods you can use to update the XML record format of a table. Either of the following two methods updates the XML record format of a table:

Perform an online table move on table using the ADMIN_MOVE_TABLE procedure.
Perform the following steps:
1. Use the EXPORT command to create a copy of the table data.
2. Use the TRUNCATE statement to delete all the rows from the table and release the storage allocated to the table.
3. Use the LOAD command to add the data into the table.

20

Ensure the table to attach to has the required access controls or deactivate the access controls on the table being attached.

21

Ensure the table to attach to has the required access controls or deactivate the access controls on the table being attached.

22

Respond to reason code 22 in one of the following ways:

If the source table does not have a BUSINESS_TIME period defined, alter the columns in the source that correspond to the BUSINESS_TIME period columns in the target table by using the ALTER TABLE statement with the ADD PERIOD BUSINESS_TIME action on the source table.
If the source table has a BUSINESS_TIME period defined, perform the following steps:
1. Remove from the source table the period that does not match the period in the target table by using the ALTER TABLE statement with the DROP PERIOD BUSINESS_TIME action on the source table.
2. Add columns to the source table that correspond to the BUSINESS_TIME period columns ing the target table by using the ALTER TABLE statement with the ADD PERIOD BUSINESS_TIME action on the source table.

30

Ensure that both tables are random distribution tables using the random by generation method.

99

Convert the indexes on the target table to support large RIDs by issuing the command: REORG INDEXES ALL FOR TABLE target-tablename ALLOW NO ACCESS.

sqlcode: -20307

sqlstate: 428GE

SQL20308N Parsing with the STRIP WHITESPACE option is not allowed when the input includes a text node string value with only whitespace characters that is more than 1000 bytes long.

Explanation

The option to STRIP WHITESPACE during XML parsing will fail if a text node string value is encountered that consists only of whitespace characters and has a length of more than 1000 bytes.

The statement cannot be processed.

User response

Use the PRESERVE WHITESPACE option or modify the input to remove whitespace in excess of 1000 bytes from any text nodes with a string value consisting of only whitespace characters.

sqlcode: -20308

sqlstate: 54059

SQL20309N Invalid use of an error tolerant nested-table-expression.

Explanation

An error tolerant nested-table-expression (a nested-table-expression that specifies the RETURN DATA UNTIL clause) cannot be referenced in the following places:

The fullselect of a materialized-query-definition.
The same statement or compound statement that contains an insert, update, or delete operation.
The SELECT statement for a cursor of a positioned DELETE or positioned UPDATE statement.

The statement cannot be processed.

User response

Remove the RETURN DATA UNTIL clause of the nested-table-expression and resubmit the statement.

sqlcode: -20309

sqlstate: 428GG

SQL20316N Invalid compilation environment. Reason code = `reason-code`.

Explanation

The compilation environment provided is invalid for the reason specified in the accompanying reason code.

The format of the compilation environment provided is not correct.
The version of the compilation environment provided is not supported.
The size of the compilation environment provided is not valid.
The codepage used by the compilation environment provided is not compatible with this database.

The statement cannot be executed.

User response

The action is based on the reason codes as follows:

Acquire the compilation environment again and ensure that it is not altered in any fashion prior to use.
Acquire the compilation environment again using a compatible level of software.
Acquire the compilation environment again and ensure that it is not altered in any fashion prior to use.
Acquire the compilation environment again using a database with the same codepage as this one.

sqlcode: -20316

sqlstate: 51040

SQL20317N The command or statement failed because the operation is only supported when there is at least one storage group defined for the database against which the command or statement was executed.

Explanation

Automatic storage simplifies storage management for table spaces. When you create a storage group, you specify the storage paths where the database manager will place your data. After a storage group is created in a database, there is always a default storage group for the database. As you create and populate automatic storage table spaces, the database manager will manage the container and space allocation for those automatic storage table spaces to use the default or specified storage group.

This message is returned when an attempt is made to perform an action that require a storage group against a database that has no storage groups defined, such as the following examples:

Creating an automatic storage table space
Altering a table space to use automatic storage
Using automatic storage for a restore operation by running the SET TABLESPACE CONTAINERS command with the USING AUTOMATIC STORAGE clause
Dropping storage paths from the database

User response

To use automatic storage functionality with this database, create at least one storage group.

sqlcode: -20317

sqlstate: 55060

SQL20318N The ALTER TABLESPACE statement failed because the change is not allowed for the type of table space. Table space name: `tablespace-name`. Table space type: `tablespace-type`. Incompatible clause: `clause`.

Explanation

The following are operations that are incompatible with a corresponding table space type, as indicated.

A table space defined as MANAGED BY AUTOMATIC STORAGE has its containers managed by the database manager. This means that the database manager will automatically extend existing containers or create new containers based on the storage paths defined for the storage group with which the table space is associated. Containers for table space defined as MANAGED BY AUTOMATIC STORAGE cannot be altered by the ALTER TABLESPACE statement.
The INHERIT option cannot be specified for the DATA TAG, OVERHEAD, or TRANSFERRATE clauses with DMS or SMS table spaces
The clause 'USING STOGROUP' cannot be used with DMS or SMS table spaces
STOGROUP cannot be changed for temporary automatic storage table spaces
DATA TAG cannot be set for a temporary table space or a system catalog table space

Storage paths are being included in a call to the RESTORE DATABASE API.
Storage paths are being specified with the ON option of the RESTORE DATABASE command.
Storage paths are being specified with the SET STOGROUP PATHS command.

For versions of DB2 database prior to Version 10:

Prior to Version 10, a database in the backup image had to be defined to use automatic storage in order to specify storage paths. This message is returned by database servers prior to Version 10 when an attempt is made to perform an action that requires automatic storage in a database backup image that has not been enabled for automatic storage.

User response

Resolve the problem using the appropriate action:

Do not include any storage paths when calling the RESTORE DATABASE API.
Do not specify the ON option of the RESTORE DATABASE command.
Do not specify the SET STOGROUP PATHS command.

If the xsrobject-type is EXTERNAL ENTITY, then an XML external entity is required for parsing or validation of an XML value. The identification of the external entity is based on uri-type1 of SYSTEM ID with the system identifier as uri1 and uri-type2 of PUBLIC ID with the public identifier as uri2. If uri2 is empty, then no public identifier was available.

The statement or command cannot be processed.

User response

User response

Examine the constraints on the object referenced by the operation to determine the cause of the violation. Look in the db2diag log file for more information about the constraint that was violated.

sqlcode: -20333

sqlstate: 23527

SQL20334N A SOAP Fault was received from the web services data source `tokens`. Associated text and tokens are `text-and-tokens`.

Explanation

The web services data sourcedata-source-name that is using the Simple Object Access Protocol (SOAP) sent a SOAP Fault to the federated server. Further information about this error situation is in tokens.

User response

Use the information provided intokens to identify and correct the root cause of the SOAP Fault at the data source, data-source-name.

sqlcode: -20334

sqlstate: 560CB

SQL20335N More than one `xsrobject-type` exists identified by XML `uri-type1` `uri1` and `uri-type2` `uri2` exists in the XML schema repository.

Explanation

There is more than one registered XSROBJECT that matches the URI used to identify an XML schema or external entity.

If xsrobject-type is XMLSCHEMA, then identification for the XML schema is based on uri-type1 of NAMESPACE with the XML schema target namespace as uri1 and uri-type2 of LOCATION with XML schema location as uri2. If ui2 is empty, then no schema location was specified.

If xsrobject-type is EXTERNAL ENTITY, then identification for the external entity is based on uri-type1 of SYSTEM ID with the system identifier as uri1 and uri-type2 of PUBLIC ID with the public identifier as uri2. If uri2 is empty, then no public identifier was specified.

The statement cannot be processed.

User response

If both uri1 and uri2 are indicated, then either drop duplicate XSROBJECTs of type xsrobject-type from the XML schema repository or, for XML schemas, explicitly specify the XMLSCHEMA ID for one of these XML schemas. If the schema location (uri2) is empty, it may also be possible to explictly specify XMLSCHEMA URI and LOCATION to uniquely identify an XML schema.

sqlcode: -20335

sqlstate: 22533, 4274B

SQL20336N A value with data type `source-data-type` cannot be XMLCAST to type `target-data-type`.

Explanation

The statement contains a CAST with the first operand having a data type of source-data-type to be cast to the data type target-data-type. This cast is not supported.

User response

Change the data type of either the source or target so that the cast is supported. For predefined data types these are documented in the SQL Reference. For a cast involving a user-defined distinct type, the cast can be between the base data type and the user-defined distinct type or from a data type that is promotable to the base data type to the user-defined distinct type.

sqlcode: -20336

sqlstate: 42846

SQL20337N The BY REF clause is missing or used incorrectly. Reason code=`reason-code`.

Explanation

The following explain the error based on possible values for reason-code.

The BY REF clause cannot be specified when the corresponding data type is other than XML for an argument of a XMLQUERY, XMLEXISTS, or XMLTABLE function. This error may also occur when the BY REF clause is issued within an XMLTABLE column definition and the type of the column is not XML.
The BY REF clause must be specified when XMLTABLE clause defines a column with data type XML.
The BY REF clause must be specified in an XMLQUERY function that returns an XML sequence. The BY REF clause can be specified explicitly following the RETURNING SEQUENCE clause or in the PASSING clause.

User response

Take the following action based on the reason-code.

Remove the BY REF clause where it is associated with a data type other than XML.
Specify the BY REF clause where the data type argument of an XMLTABLE function is XML.
Specify the BY REF clause following the RETURNING SEQUENCE keywords or specify a PASSING BY REF clause.

sqlcode: -20337

sqlstate: 42636

Explanation

The TRANSFER statement specified to transfer the database object to authorization ID auth-ID. The authorization ID is already the owner of the database object. There is no need to transfer the database object.

User response

No action is required.

sqlcode: +20341

sqlstate: 01676

SQL20342N `auth-ID` does not have one or more required privileges `privilege-list` on object `object-name` of type `object-type` necessary for ownership of the object.

Explanation

The TRANSFER statement attempted to transfer ownership of the object to authorization ID auth-ID which does not have the necessary privileges to be the owner of the object. The privileges privilege-list on the object object-name are the privileges that are missing.

The statement cannot be processed.

User response

Grant the authorization ID auth-ID all the privileges necessary, as indicated by privilege-list, on object object-name, for the authorization ID to be the owner of the object being transferred.

sqlcode: -20342

sqlstate: 42514

SQL20344N Transferring ownership of `object-name1` failed because of a dependency involving `object-name2`. Reason code: `reason-code`.

Explanation

When an object is created, one authorization ID is assigned ownership of the object. Ownership means the user is authorized to reference the object in any applicable SQL or XQuery statement. You can transfer ownership of a database object by using the TRANSFER OWNERSHIP statement. This message is returned when an attempt to transfer the ownership of an object fails. The reason code indicates the specifics of the failure:

1: The table object-name1 in table hierarchy object-name2 cannot be transferred because the table is a subtable.
2: The view object-name1 in view hierarchy object-name2 cannot be transferred because the view is a subview.
3: The index object-name1 cannot be transferred because the table that the index is defined on,object-name2, is a declared global temporary table.
4: The method body or function object-name1 cannot be transferred because the method body or function was implicitly generated when user-defined type object-name2 was created.
5: The package object-name1 cannot be transferred because an SQL routine, a trigger, or an index object-name2 depends on the package.
6: The event monitor object-name1 cannot be transferred because the event monitor is active.
7: The transfer of the view object-name1 failed because the view is a system-generated statistical view for the expression-based index named object-name2.

User response

Respond to this message according to the reason code:

1: Transfer the whole table hierarchy.
2: Transfer the whole view hierarchy.
3: Drop the declared global temporary table.
4: Drop the type that created this method or function.
5: Transfer the SQL procedure.
6: Set the event monitor state to inactive.
7: Transfer the ownership of the expression-based index.

1: Verify that the user mapping plugin exists and is in the correct location.
2: Ensure that the user mapping plugin is using a federation supported version of the user mapping plugin API and that it is reporting the correct version number.
3: Verify that the user mapping repository is up and running and that the repository connection parameters of the user mapping plugin are correct.
4: Create a corresponding user mapping entry on the user mapping repository.
5: Ensure that the decryption logic in the user mapping plugin is implemented correctly.
6: Check if the user mapping repository and network are up and running.
7: Ensure that all the parameter values passed onto the user mapping plugin are correct.
8: Verify that federation has the authority to invoke the user mapping plugin.
9: Verify if plugin level global resources can be released.
10: Ensure the plugin maps the error to one of the defined errors. Trace information for the error may be available in the db2diag log file.

User response

The requested operation on the data type type-name is not supported. You may be able to change the data type of the value to a data type that is supported using a cast or some other function.

sqlcode: -20353

sqlstate: 42818

SQL20354N Invalid specification of a row change timestamp column for table `table-name`.

Explanation

The specification of a row change timestamp column is invalid for one of the following reasons. A row change timestamp column cannot:

be a column of a primary key.
be a column of a foreign key.
be a column of a functional dependency DEPENDS ON clause.
be a column of a database partitioning key.
be defined for a temporary table.

The statement cannot be executed.

User response

Correct the syntax and resubmit the statement.

sqlcode: -20354

sqlstate: 429BV

SQL20356N The table `table-name` cannot be truncated because DELETE triggers exist for the table, or the table is the parent in a referential constraint.

Explanation

The TRUNCATE statement could not be processed for one of the following reasons:

The TRUNCATE statement would result in the activation of DELETE triggers. However, the statement specifies RESTRICT WHEN DELETE TRIGGERS (implicitly or explicitly). This can occur when DELETE triggers exist for the table to be truncated, and the result of the TRUNCATE statement would activate the triggers.
The table to be truncated is the parent table in a referential constraint.

The statement cannot be processed.

User response

If the statement failed because of the existence of DELETE triggers, specify the IGNORE DELETE TRIGGERS clause on the TRUNCATE statement. If the statement failed because of referential constraints, use the ALTER TABLE statement to drop the referential constraints, and then re-issue the TRUNCATE statement.

sqlcode: -20356

sqlstate: 428GJ

SQL20357N One or more federated one-phase commit data source sites have failed commit or rollback processing. As a result, the transaction outcome might not be consistent across all sites. Subsequent SQL statements can be processed. Reason code `reason-code`.

Explanation

A federated server has sent a transaction commit or rollback request and one or more of the federated one-phase commit data source sites cannot complete the request. This may be caused by a server failure, communication link failure, or other failure. The transaction is not completely committed or rolled back.

The reason code indicates the specific situation.

Commit failure does not involve an update federated data source.
Commit failure involves an update federated data source.
Rollback failure does not involve an update federated data source.
Rollback failure involves an update federated data source.
Commit or rollback processing encountered an error due to db2fmp process abnormal termination at the federated server, error '-430'.

User response

Check the administration notification log for details. Compensating actions may need to be applied manually at the federated one-phase commit data source sites. It may be necessary to contact the system administrator for assistance.

For reason 5, disconnect the application from the federated server, e.g. issue a 'connect reset' clp command and reconnect.

sqlcode: -20357

sqlstate: 40003

SQL20358N Commit or rollback processing encountered an error. The transaction at some federated two-phase commit data source sites could be indoubt. Subsequent SQL statements can be processed. Reason code `reason-code`.

Explanation

A federated server has sent a transaction commit or rollback request and one or more of the federated two-phase commit data source sites cannot complete the request. This may be caused by a server failure, communication link failure, or other failure. The transaction might be indoubt at the federated two-phase commit data source site.

The reason code indicates the specific situation.

Missing commit acknowledgement from federated data source site.
Missing abort acknowledgement from federated data source site.
Rollback processing encountered an error. The transaction might be indoubt at the federated two-phase commit data source site.
Commit or rollback processing encountered an error due to db2fmp process abnormal termination at the federated server, error '-430'.

User response

Determine the cause of the error. It may be necessary to contact the system administrator for assistance since the most common cause of the error is a federated server failure, federated data source site failure, or connection failure. For a federated server failure, the RESTART DATABASE command will complete the commit or rollback processing for this transaction. For a federated data source site failure, restart the failed data source. For a connection failure, consult with your network administrator and/or communication expert to determine the cause of the problem.

For reason 3, check possible indoubt transactions on the federated data source site, and manually resolve them if existing.

For reason 4, disconnect the application from the federated server, e.g. issue a 'connect reset' clp command and reconnect.

sqlcode: -20358

sqlstate: 08007

SQL20360W A trusted connection cannot be established for the specified authorization ID.

Explanation

A trusted connection cannot be established for the specified authorization ID. A matching trusted context is not currently defined or the server does not support trusted context.

The attempt to create a trusted connection was not successful and a normal connection was created.

Federated system users: If this message is returned during a federated request, then the attempt to create a trusted connection between the federated database and the remote server was not successful, and a normal connection was created instead.

User response

Ensure that the server supports trusted contexts and that the trusted context has been correctly defined, and try to re-establish a trusted connection.

Federated system users: Ensure that the remote server supports trusted contexts and that the trusted context has been correctly defined on the remote server.

sqlcode: +20360

sqlstate: 01679

SQL20361N The switch user request using authorization ID `authorization-name` within trusted context `context-name` failed with reason code `reason-code`.

Explanation

The switch user request within the trusted context context-name failed. The trusted connection is in an unconnected state.

User response

Use the authorization ID authorization-name and the following explanations for reason code reason-code to determine what action to take.

1: The authorization ID is not an allowed user of the trusted context. Provide an authorization ID that is an allowed user of the trusted context as described in the trusted context definition.
2: The switch user request did not include the authentication token. Provide the authentication token for the authorization ID.
3: The trusted context object is either disabled, dropped, or its system authorization ID altered. Only the authorization ID that established the trusted connection is allowed. Provide this authorization ID.

sqlcode: -20361

sqlstate: 42517

SQL20362N Attribute `attribute-name` with value `value` cannot be dropped or altered because it is not part of the definition of trusted context `context-name`.

Explanation

Attribute attribute-name was specified for a trusted context, but the trusted context is not defined with an attribute with this name. The statement could not be processed.

User response

Remove the name of the unsupported attribute and re-issue the statement.

sqlcode: -20362

sqlstate: 4274C

SQL20363N Attribute `attribute-name` with value `value` is not unique for trusted context `context-name`.

Explanation

During the create or alter of trusted context context-name, a duplicate value value was specified for the attribute-name attribute. Each pair of attribute name and value must be unique for a trusted context.

The statement cannot be processed.

User response

Remove the non-unique specification of attribute-name and and re-issue the statement.

sqlcode: -20363

sqlstate: 4274D

SQL20364N The identifier with the name `name` at ordinal position `number` in the statement is too long.

Explanation

At least one identifier in the described statement is too long to fit in the destination buffer. The first name that is too long is identified by the name name and ordinal position number. If performing a describe output of a prepared query, the ordinal position is relative to the select list column of the query. If performing a describe output of a CALL statement, the ordinal position is relative to the OUT or INOUT parameters of the procedure to which the CALL resolved. If performing a describe input of a CALL statement, the ordinal position is relative to the IN or INOUT parameters of the procedure to which the CALL resolved.

The column name, parameter name, user defined type name or user defined type schema name was either too long, or became too long after code page conversion. Note that the lengths of the column name, parameter name, user defined type name, and user defined type schema name are limited when using the SQLDA structure.

User response

Do one of the following:

Use a client whose code page does not cause expansion beyond the supported maximum length.
For a column name, change the table, view, or nickname so the column has a shorter name.
For a parameter name, change the procedure so the parameter has a shorter name.
For a user defined type name or user defined type schema name, drop and recreate the user defined type with a shorter name.
For a schema name, change the table, view, procedure, or user defined type.

sqlcode: -20364

sqlstate: 42622

SQL20365W A signaling NaN was encountered in an arithmetic operation or function involving DECFLOAT

Explanation

A signaling NaN (sNaN) was encountered in an expression, arithmetic operation, or function involving a DECFLOAT column or value. The result is NaN

User response

To stop the warning from being returned, find the tuple or tuples containing a signaling NaN and exclude them from the query.

sqlcode: +20365

sqlstate: 01565

SQL20371W The ability to use trusted context `context-name` was removed from some, but not all authorization IDs specified in the statement.

Explanation

An ALTER TRUSTED CONTEXT statement for context-name contained a DROP USE FOR clause with multiple authorization IDs or PUBLIC, but one or more authorization IDs were not defined to use the trusted context. One or more users were removed from the definition of the trusted context, but one or more other users were not previously allowed to use the trusted context.

The statement was processed.

User response

Verify that the ability to use the trusted context was removed from all of the authorization IDs that was intended.

sqlcode: +20371

sqlstate: 01682

SQL20372N The trusted context `context-name` specified authorization ID `authorization-name` which is already specified for another trusted context.

Explanation

A CREATE TRUSTED CONTEXT or ALTER TRUSTED CONTEXT statement for context-name specified SYSTEM AUTHID authorization-name, but this authorization ID is already defined to use a different trusted context. A system authorization ID that is defined as the SYSTEM AUTHID for a trusted context cannot be associated with any other trusted context as the SYSTEM AUTHID.

Use the following query to determine which trusted context is already using the authorization ID:

SELECT CONTEXTNAME FROM SYSCAT.CONTEXTS 
WHERE SYSTEMAUTHID = <authorization-name>

SQL20388N Too many elements are specified for the security label component `component-name`.

Explanation

A security label component of type SET or TREE can have no more than 64 elements. More than this number are specified for security label component component-name.

For a security label component of type ARRAY, the maximum number of elements is 65 535. If elements are added via the ALTER SECURITY LABEL COMPONENT statement, the actual limit may be lower than this maximum due to the way DB2 assigns an encoded value to each new element.

User response

For TREE and SET security label components, remove elements so that there are no more than 64. For ARRAY security components, if the maximum has not been reached, drop and create the component again with all the desired elements specified.

sqlcode: -20388

sqlstate: 54061

SQL20389N The component element `element` is not defined in the security label component `component-name`.

Explanation

The component element element does not exist in the definition of security label component component-name.

User response

Provide a valid element for the component. You can submit the following query to list the valid elements for the security label components:

SELECT ELEMENTVALUE FROM 
    SYSCAT.SECLABELCOMPONENTELEMENTS 
  WHERE COMPID=(SELECT COMPID FROM 
    SYSCAT.SECLABELCOMPONENTS 
    WHERE  COMPNAME = component-name)

If you get this error when executing the scalar function SECLABEL, then also check the security label string to make sure that the values are listed in the same order that their components are listed in the security policy.

sqlcode: -20389

sqlstate: 4274F

SQL20390N The security label component `component-name` is not defined in the security policy `security-policy` so that component cannot be used in the security label `security-label`.

Explanation

Security labels can only contain values for those components that are defined in the security policy that the label is part of. The security label component component-name is not part of the security policy security-policy. The security label security-label is part of that security policy so the component component-name cannot be used in that security label.

User response

Provide a security label component that is part of the security policy security-policy. You can execute the following query to list the security label components that are part of the security policy:

SELECT COMPNAME FROM 
  SYSCAT.SECURITYLABELCOMPONENTS
  WHERE COMPID=(SELECT COMPID FROM 
    SYSCAT.SECURITYPOLICYCOMPONENTRULES
    WHERE SECPOLICYID = (SELECT 
      SECPOLICYID FROM 
      SYSCAT.SECURITYPOLICIES 
      WHERE SECPOLICYNAME = 
        '<security-policy>') )

sqlcode: -20394

sqlstate: 4274H

SQL20395N The GRANT of security label `security-label1` conflicts with security label `security-label2` that is also granted to authorization ID `authorization-name`.

Explanation

If the user, group, or role is granted two distinct labels, one for WRITE access and another for READ access, they must satisfy the following rules:

For security label components of type ARRAY, the value must be the same in both security labels.
For security label components of type SET, the values given in the security label used for WRITE access must be a subset of the values given in the security label used for READ access.
for security label components of type TREE, either the values must be the same, or the values given in the security label used for WRITE access must be one of the sub tree values of the security label used for READ access.

User response

Grant a different security label or make one of these modifications to the security label that is being granted:

For security label components of type ARRAY, make sure the value given is the same in both security labels.
For security label components of type SET, make sure the values given in the security label used for WRITE access are a subset of the values given in the security label used for READ access.
For security label components of type TREE, make sure either the values are the same or the value given in the security label used for WRITE access is one of the sub tree values of the security label used for READ access.

sqlcode: -20395

sqlstate: 428GQ

SQL20396N The security label named `security-label-name` cannot be found for the security policy `policy-name`.

Explanation

The security label named security-label-name cannot be found for the security policy policy-name. This caused execution of the built-in function SECLABEL_BY_NAME to fail.

User response

Check the spelling of security-label-name. Make sure you are using the correct security policy name.

sqlcode: -20396

sqlstate: 4274I

SQL20397W Routine `routine-name` execution has completed, but at least one error, `error-code`, was encountered during the execution. More information is available.

SQL20404N The security label object `policy-name.object-name` cannot be dropped because it is currently in use. Reason code `reason-code`.

Explanation

The security label object object-name could not be dropped. The reason it could not be dropped is specified by the reason code reason-code:

It is granted to one or more users, groups, or roles.
It is being used to protect one or more columns.

User response

The user response corresponding to the reason code is:

Revoke this label from all users, groups, or roles who have been granted this security label for this security policy. The following query can be used to find all the users who has been granted with this label.

SELECT GRANTEE FROM SYSCAT.SECURITYLABELACCESS 
  WHERE SECLABELID = (SELECT SECLABELID FROM 
  SYSCAT.SECURITYLABELS  
  WHERE SECLABELNAME = '<object-name>' AND   
    SECPOLICYID = (SELECT SECPOLICYID FROM 
    SYSCAT.SECURITYPOLICIES 
  WHERE SECPOLICYNAME = '<policy-name>' ) )

For all the tables that use this security label to protect a column, either alter the table to drop this security label or drop the table. The following query can be used to find all protected tables and all the columns that are protected with this label.

SELECT TABNAME, COLNAME FROM SYSCAT.COLUMNS
  WHERE SECLABELNAME = '<object-name>' AND
    TABNAME = (SELECT TABNAME FROM 
    SYSCAT.TABLES
  WHERE SECPOLICYID = (SELECT SECPOLICYID FROM 
  SYSCAT.SECURITYPOLICIES
  WHERE SECPOLICYNAME = '<policy-name>' ) )

sqlcode: -20404

sqlstate: 42893

SQL20405N The security policy object `object-name` cannot be dropped because it is currently in use. Reason code `reason-code`.

Explanation

The security object object-name could not be dropped. The reason it could not be dropped is specified by the reason code reason-code:

It is being used to protect one or more tables.
There are one or more security labels that use it.
There are one or more exemptions granted on one or more of its rules.

User response

The user response corresponding to the reason code is:

Drop the security policy from the tables protected by it using the ALTER TABLE statement. The following query can be used to find all the tables protected by this security policy:
```
SELECT TABNAME FROM SYSCAT.TABLES  
  WHERE SECPOLICYID=(SELECT SECPOLICYID FROM 
  SYSCAT.SECURITYPOLICIES 
  WHERE SECPOLICYNAME = '<object-name>' )
```

Drop all the security labels that are part of this security policy. The following query can be used find all security labels that belongs to this security policy:

SELECT SECLABELNAME FROM SYSCAT.SECURITYLABELS  
  WHERE SECPOLICYID=(SELECT SECPOLICYID FROM 
  SYSCAT.SECURITYPOLICIES 
  WHERE SECPOLICYNAME = '<object-name>' )

Revoke all the exemptions granted on rules used by this security policy from all users. The following query can be used find all users that have been granted exemptions on rules used by this security policy.
```
SELECT GRANTEE, ACCESSRULENAME FROM 
  SYSCAT.SECURITYPOLICYEXEMPTIONS
  WHERE SECPOLICYID=(SELECT SECPOLICYID FROM 
  SYSCAT.SECURITYPOLICIES 
  WHERE SECPOLICYNAME = '<object-name>' )
```

sqlcode: -20405

sqlstate: 42893

SQL20406N The security label component object `object-name` cannot be dropped because it is part of a security policy.

Explanation

The security label component object object-name cannot be dropped because one or more security policies refer to it.

User response

Drop all the security policies that refer to this security component. The following query can be used find all the security policies that refer to this security component:

SELECT SECPOLICYNAME FROM SYSCAT.SECURITYPOLICIES
  WHERE SECPOLICYID = (SELECT SECPOLICYID FROM 
  SYSCAT.SECURITYPOLICYCOMPONENTRULES
  WHERE COMPID = (SELECT COMPID FROM 
  SYSCAT.SECURITYLABELCOMPONENTS
  WHERE COMPNAME = '<object-name>' ) )

sqlcode: -20406

sqlstate: 42893

SQL20408N Table `source-tablename` cannot be attached to table `target-tablename` because column `source-columnname` of the source table and its associated column `target-columnname` of the target table do not match. Reason code = `reason-code`.

Explanation

An attribute of the target table column does not match the attribute of the corresponding column (aligned by ordinal position) in the source table in the ALTER TABLE ... ATTACH PARTITION statement. The reason-code indicates the type of mismatch and in which column of the SYSCAT.COLUMNS view to find the table attribute.

In cases where it is difficult or impossible to modify the characteristics of either the source or target table for compatibility, you can create a new table that is compatible with the target table, and copy the data from source-tablename to this new table.

1

The data type of the columns (TYPENAME) does not match.

2

The nullability of the columns (NULLS) does not match.

3

The implicit default value (IMPLICITVALUE) of the columns are incompatible.

Implicit defaults must match exactly if both the target table column and source table column have implicit defaults (if IMPLICITVALUE is not NULL). Refer to the SQL Reference Manual for details on interpreting the values in IMPLICITVALUE.

4

The code page (COMPOSITE_CODEPAGE) of the columns does not match.

5

The system compression default clause (COMPRESS) does not match.

6

The security label protecting the source-columnname for the source table is not identical to that of target-columnname for the target table. The two tables must have the same set of protected columns and each pair of corresponding columns must be protected with same security label.

7

The row change timestamp attribute of the columns does not match.

8

For structured, XML, or LOB data types, the inline length of the columns (INLINE LENGTH) do not match.

9

If a column mask on source-columnname for the source table exists and is enabled, a column mask on target-columnname for the target table must also exist and be enabled.

10

The ROW BEGIN system-generated attributes of the columns do not match.

11

The ROW END system-generated attributes of the columns do not match.

12

The TRANSCATION START ID system-generated attributes of the columns do not match.

User response

Correct the mismatch in the tables.

1

To correct a mismatch in data type, issue the statement:

ALTER TABLE ... ALTER COLUMN ...
   SET DATA TYPE ...

2

Alter the nullability of the column that does not match for one of the tables by issuing one of the following statements:

ALTER TABLE... ALTER COLUMN...
   DROP NOT NULL ALTER TABLE... 
      ALTER COLUMN...   SET NOT NULL

3, 4

Create a new source table.

5

To alter the system compression of the column issue one of the following statements to correct the mismatch:

ALTER TABLE ... ALTER COLUMN ...   
   COMPRESS SYSTEM DEFAULT
      ALTER TABLE ... ALTER COLUMN ...
         COMPRESS OFF

6

To alter the security protection of a column, issue one of the following statements:

ALTER TABLE ... ALTER COLUMN ...
   SECURED WITH ... ALTER TABLE ...
      ALTER COLUMN ...
         DROP COLUMN SECURITY

7

When the target-columnname of the target table is defined as a row change timestamp column, the source-columnname must also be defined as a row change timestamp column.

8

Alter the inline length of the column that does not match by issuing the following statement:

ALTER TABLE ... ALTER COLUMN ...
   SET INLINE LENGTH ...

9

Create an enabled mask on the column target-columnname by issuing the following statement, and then try ALTER again.

CREATE MASK ON target-table...
    FOR target-columnname... ENABLE

10

When the target-columnname of the target table is defined as a ROW BEGIN system-generated column, the source-columnname must also be defined as a ROW BEGIN system-generated column.

11

The GRANT statement attempted to grant an authority or privilege to an authorization name that is not allowed to receive that authority or privilege.

The statement cannot be processed

User response

Grant a different authority or privilege or grant it to an authorization ID that can receive the authority or privilege.

sqlcode: -20414

sqlstate: 42521

SQL20415N Update, delete, or Insert into a UNION ALL view failed because one of the underlying tables is protected.

Explanation

Updates, deletes, and Inserts are not allowed into a UNION ALL view if that view is created on one or more protected tables.

The statement cannot be processed

User response

Do not update, delete, or insert into the UNION ALL view.

sqlcode: -20415

sqlstate: 429BZ

SQL20416N The value provided (`seclabel`) could not be converted to a security label. Labels for the security policy with a policy ID of `policy-id` should be `correct-length` characters long. The value is `value-length` characters long.

Explanation

An INSERT or UPDATE statement specified a value for a column of type DB2SECURITYLABEL. The value cannot be converted to a valid security label because it is not the correct length for security labels that are part of the security policy that is protecting the table. If the seclabel value is *N, then the actual value has been written in the db2diag log file.

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 a 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

User response

Ensure that the table definition includes at least one column that is not defined as implicitly hidden.

sqlcode: -20422

sqlstate: 428GU

SQL20423N Error occurred during text search processing on server `server-name` using index `index-name`. The error message is `text-search-error-msg`.

Explanation

An error described by text-search-error-msg occurred during the processing of a text search function that used the text search index index-name.

User response

Use text-search-error-msg to determine the cause of the error. If the error message is truncated, you can see the complete message in the db2diag log file.

The first word in text-search-error-msg is an error identifier. If the error identifier starts with 'CIE', use the db2ts command to obtain more details, for example, db2ts help error-identifier.

If the error identifier does not start with 'CIE', use the DB2 Text Search documentation to obtain more details about text-search-error-msg.

sqlcode: -20423

sqlstate: 38H10

SQL20424N Text search support is not available. Reason code = `reason-code`.

Explanation

An error occurred while attempting to use text search.

The reason code provides more information about the error:

11: Socket communication with the search server failed.
12: Unrecoverable error during a search system operation.

User response

Restart the text search instance service and try the operation again. If the error reoccurs, contact your system administrator to ensure that support for text searching has been installed, properly configured, and has been started on your system.

sqlcode: -20424

sqlstate: 38H11

SQL20425N The statement failed because the column named `column-name` in the table named `table-name` was specified as an argument to a text search function, but an active text search index does not exist for the specified column.

Explanation

A text search function, such as CONTAINS or SCORE, specified a column as an argument but a text search index does not exist for this column.
The text search index is inactive.
The text search index is invalid. A text search index may be invalidated automatically, for example, if the underlying base table was dropped and recreated.
The query combines different types of text search indexes.

Text search processing cannot be performed for the specified column.

User response

Ensure that the database is enabled for text search and a text search index has been created on the specified column.
Activate the text index with the ALTER INDEX command, with the ACTIVATE option.
If the table column has an invalid text search index, drop the text index and create a new one.
If the query combines multiple text search indexes, make sure that all indexes are active and of the same type.

sqlcode: -20425

sqlstate: 38H12

SQL20426N Conflicting text search administration procedures or commands running on the same index.

Explanation

Two or more conflicting administration procedures or commands are running on the same index.

User response

Invoke the administration procedure or command after the currently running procedure or command completes. If the operation attempted is DISABLE, retry this operation later or use the FORCE option of the command.

sqlcode: -20426

sqlstate: 38H13

SQL20427N An error occurred during a text search administration procedure or command. The error message is `text-search-error-msg`.

Explanation

A text search administration operation failed with an error message text-search-error-msg.

User response

Use the error message text-search-error-msg to determine the cause of the error. If the message is truncated, you can see the db2diag log file.

The first word in text-search-error-msg is an error identifier. If the error identifier starts with 'CIE', use the db2ts command to obtain more details, for example, db2ts help error-identifier.

If the error identifier does not start with 'CIE', use the DB2 Text Search documentation to obtain more details about text-search-error-msg.

sqlcode: -20427

sqlstate: 38H14

SQL20428N URI specified in the ACCORDING TO XMLSCHEMA clause is an empty string.

Explanation

The target namespace URI specified following the URI keyword or the schema location URI specified following the LOCATION keyword is an empty string.

User response

Ensure that every target namespace URIs and every schema location URI in the ACCORDING TO XMLSCHEMA clause is a valid URI that is not an empty string.

sqlcode: -20428

sqlstate: 428GV

SQL20429N The XML operation `operation-name` is not allowed on strings that are not FOR BIT DATA on this database.

Explanation

The specified operation could not be performed on the string because it is not FOR BIT DATA. operation-name is either XMLPARSE or another prohibited operation.

User response

The database configuration parameter ENABLE_XMLCHAR is set to NO. To allow XMLPARSE on strings that are not FOR BIT DATA, set ENABLE_XMLCHAR to YES. Note that character substitution may occur if the database is not Unicode and the XML value being parsed contains characters that are not present in the database code page.

Alternatively, rework the statement to use a host variable, expression or parameter marker of type XML.

sqlcode: -20429

sqlstate: 428GW

SQL20430N Global variable `variable-name` cannot be set or referenced in this context.

Explanation

In some contexts a global variable cannot be set and in other contexts it cannot be referenced. This message can be returned in the following situations:

A statement attempted to set the named global variable in one of the following contexts:
- compound SQL (inline) statements
- functions where the body is not a compound SQL (compiled) statement
- methods
- triggers where the body is not a compound SQL (compiled) statement
A global variable is being referenced in a procedure that is defined as AUTONOMOUS

User response

Change the variable or parameter to be of type ARRAY.

sqlcode: -20437

sqlstate: 428H0

SQL20438N The data type of an array index expression is not assignable to the array index type.

Explanation

The type of an array index expression in an expression of the form object[array_index] must be assignable to INTEGER if the object is an ordinary array. If the object is an associative array, then the type of the array index expression must be assignable to the data type of the array index (INTEGER or VARCHAR).

User response

Change the data type of the array index expression to be one that is supported.

sqlcode: -20438

sqlstate: 428H1

SQL20439N Array index with value `value` is out of range or does not exist.

Explanation

The value of an array index for an array element specification or the second argument to TRIM_ARRAY is out of range or the null value. If the array index is specified in an array element specification for an associative array and is valid for the array index data type, the element with array index value value does not exist in the array.

User response

For an associative array:

Specify a value that is not the null value.
If the array index data type is integer, specify a value that is a number within the range of integers.
Specify an array index value that exists in the array.

For an ordinary array:

If the value is an array index used in an array element specification that is the target of an assignment statement, change the value to be not the null value, not less than 1 and not greater than the maximum cardinality defined for the array.
If the value is an array index used in an array element specification that is part of an expression or the value is the second argument to the TRIM_ARRAY function, change the value to be not less than 0 and not greater than the cardinality of the array.

sqlcode: -20439

sqlstate: 2202E

SQL20440N Array value with cardinality `cardinality` is too long. The maximum cardinality allowed is `max-cardinality`.

Explanation

The array value required truncation from its cardinality of cardinality to a maximum cardinality of max-cardinality. A system (built-in) cast or adjustment function was called to transform the value in some way. The truncation is not allowed where the value is used.

The array value being transformed is one of the following:

An argument to a stored procedure call
The result of a call to the ARRAY_AGG function
The result of an array constructor used in the right side of a SET statement
An argument to a cast function

User response

Examine the SQL statement to determine where the transformation is taking place. Either the input to the transformation is too long, or the target is too short. Explicitly reduce the cardinality of the input, or increase the cardinality that the target can support.

sqlcode: -20440

sqlstate: 2202F

SQL20441N A `type-name` data type is not supported in the context where it is being used.

Explanation

The data type can be specified in multiple contexts, including the following.

Parameters to SQL functions:
- Defined in a module
- With a compound SQL (compiled) statement as function body not defined in a module
Return types from SQL functions:
- Defined in a module
- With a compound SQL (compiled) statement as function body not defined in a module
Parameters to SQL procedures
Local variables declared in SQL functions:
- Defined in a module
- With a compound SQL (compiled) statement as function body not defined in a module
Local variables declared in SQL procedures
Local variables declared in triggers with a compound compiled SQL statement as trigger body
Expressions in SQL statements within compound compiled SQL statements
Global variables

The following is a list of some invalid contexts:

Parameters or variables in external routines
Return type of a function with a function body definition defined by a compound SQL (inlined) statement
Columns in tables
Data types in SQL statements outside of SQL PL contexts
Global variable (includes module-variable) being referenced outside of an SQL PL context.
Input or output parameter to a procedure or function being invoked from outside of an SQL PL context.
In a partitioned database environment (DPF) or symmetric multiprocessor (SMP) environment, only top level SET and CALL statements can reference objects defined in nested types. A sub-query cannot reference objects with nested types.
Nested types with global variables, module variables, or PL/SQL package variables in autonomous routines.

User response

Refer to the documentation for the most up to date list of supported contexts and for restrictions on the use of this data type. Remove any data types used in unsupported contexts.

If referencing the data type in a routine reference from a command interface, invoke the routine from within an SQL PL context or provide a global variable of the specified data type as the routine argument.

sqlcode: -20441

sqlstate: 428H2

SQL20442N There is not enough storage to represent the array value.

Explanation

The amount of memory required to represent an array value is larger than the maximum allowed for the system.

User response

Possible solutions may include:

Correct the statement that is attempting to create the array value
Reduce the number of elements in the array or the sizes of some of the elements
Increase the value of APPLHEAPSZ or APPL_MEMORY if they are not set to AUTOMATIC
Increase the amount of physical memory available to the system.

There are unsupported format elements.
The format does not contain a format element, is the empty string, or is all blanks.
A format element is specified more than once.
The length of the format string argument exceeds the maximum allowable length.
There are unsupported separator characters.
There are too many format elements.
A prefix or suffix format element is specified in the wrong place in the string.
There are multiple format elements that represent the same component. For example, the TIMESTAMP_FORMAT function YYYY and YY (format element to indicate the YEAR) must not both be specified in the format string.

The statement cannot be processed.

User response

Change the format string argument and call the function-name function again.

sqlcode: -20447

sqlstate: 22007

SQL20448N `string-expression` cannot be interpreted using format string `format-string` for the TIMESTAMP_FORMAT function.

Explanation

The TIMESTAMP_FORMAT function was invoked with string-expression and format-string. The name used to invoke the function could also be TO_DATE or TO_TIMESTAMP. The value string-expression cannot be interpreted with the format string format-string to produce a timestamp value. This error can occur for any of the following reasons:

string-expression is too short for the specified format string
string-expression is too long for the specified format string
string-expression does not conform to the template specified in the format string. For example:
- too many digits were specified in string-expression for the corresponding format element in the format string, such as in the specific case where 92007 is not a valid value for YYYY
- a value in string-expression is not valid for the corresponding format element in the format string, such as in the specific case where 45 is not a valid value for DD

The statement cannot be processed.

User response

Change the format string argument of the TIMESTAMP_FORMAT function. For more information, see the TIMESTAMP_FORMAT function in the SQL Reference.

sqlcode: -20448

sqlstate: 22007

SQL20449N Tree element `element-value` is not valid where specified.

Explanation

The tree element element-value is an existing element in the tree, but is not valid where it is specified in the statement. The tree structure of the tree component must be maintained.

For example, in an ALTER SECURITY LABEL COMPONENT statement, the element-name specified in the OVER clause of the ADD ELEMENT clause is not an immediate child element of the tree element specified immediately after the UNDER keyword.

User response

Specify a valid tree element that would maintain the tree structure. For example, only specify existing tree elements that are immediate children elements of the tree element specified immediately after the UNDER keyword.

sqlcode: -20449

sqlstate: 428H3

SQL20450N Recursion limit exceeded within a hierarchical query.

Explanation

Hierarchical queries using the CONNECT BY clause are limited to a recursive depth of 64 levels. The query has exceeded this level.

User response

Validate the correctness of the START WITH and CONNECT BY clauses. If recursions deeper than 64 levels are expected, rewrite the query using a recursive common table expression. Recursive common table expressions support arbitrary recursive depth.

sqlcode: -20450

sqlstate: 54066

SQL20451N Cycle detected in a hierarchical query.

Explanation

The hierarchical query using the CONNECT BY clause has found a row which is a direct or indirect parent to itself, given the CONNECT BY search condition.

The statement cannot be processed.

User response

Validate the correctness of the CONNECT BY clause, paying particular attention to the PRIOR operator.

If no cycles are expected, validate that the result table provided by the FROM clause does contain cyclic data based on the search condition specified in the CONNECT BY clause.
If the presence of cycles is expected, add the NOCYCLE keyword to the CONNECT BY clause. For example:
```
    SELECT PK FROM T START WITH PK = 5 
    CONNECT BY NOCYCLE PRIOR PK = FK
```

sqlcode: -20451

sqlstate: 560CO

SQL20452N Hierarchical query construct `name` is used out of context.

Explanation

One of the following constructs has been found outside of the context of a hierarchical query:

the LEVEL pseudocolumn
the PRIOR or CONNECT_BY_ROOT unary operator
the SYS_CONNECT_BY_PATH() function
the ORDER SIBLINGS BY clause

The reason can be one of the following:

Within the subselect where name is specified no CONNECT BY clause is present. Note that name cannot be correlated.
ORDER SIBLINGS BY has been specified, but there is a DISTINCT, GROUP BY or HAVING clause present which has destroyed the partial order provided by the hierarchical query
One of the arguments of name is an aggregate function or an OLAP function
name is itself an argument to a hierarchical query construct
PRIOR has been specified outside of the context of a CONNECT BY clause
name has been specified in an implicit join predicate in the WHERE clause
name has been specified in the START WITH clause.

The statement cannot be processed.

User response

If no hierarchical query is intended and name is LEVEL, validate that columns or variables matching name do exist and can be resolved within the query. These identifiers are considered pseudocolumns only after the usual identifier resolution has failed.
Verify that the CONNECT BY clause is present within the subselect containing name
When combining aggregate functions and hierarchical queries, consider using name as an argument to the aggregate function instead of the reverse. For example, use MAX(CONNECT_BY_ROOT name) instead of CONNECT_BY_ROOT MAX(name)
Remove name from the query.

sqlcode: -20452

sqlstate: 428H4

SQL20453N The task `task-name` cannot be removed because it is currently executing.

Explanation

An attempt to remove task task-name failed because it is currently executing.

User response

Wait until the task completes and then use the SYSPROC.ADMIN_TASK_REMOVE procedure to remove the task. The SYSTOOLS.ADMIN_TASK_STATUS view can be used to check the execution status of the task.

sqlcode: -20453

sqlstate: 5UA01

SQL20454N Invalid use of an outer join operator. Reason code = `reason-code`.

Explanation

The outer join operator (specified as "(+)") was used incorrectly in an SQL statement. The meaning of the invalid use is given by the reason code reason-code.

1: The outer join operator is used in a subselect that uses the JOIN syntax in the FROM clause.
2: The outer join operator is used in an AND-factor that has a correlated reference to another subselect.
3: The outer join operator is used on columns from more than one table-reference within an AND-factor.
4: The outer join operator is used in an AND-factor that references columns from more than two table-references.
5: The outer join operator is missing from some column references for the NULL-producer within an AND-factor.
6: The outer join operator is specified in an AND-factor where the column references are from only one table-reference and there are no other AND-factors that perform an outer join using the same NULL-producer table-reference.
7: The same table-reference is used as the NULL-producer in more than one outer join.
8: The same table-reference is used as the NULL-producer and the outer table in separate outer joins that form a cycle.
9: The outer join operator is used with an identifier that is not the name of a column. The identifier could be a global variable, local variable, transition variable, or parameter name.

WITH ROW CHANGE COLUMNS ALWAYS DISTINCT was specified as a prepare attribute, but the database manager is unable to return distinct row change columns.

The statement was prepared successfully.

User response

If distinct row change columns are desired, use the following reason-code values to determine what to do.

1: The prepared select-statement is not eligible for row change columns. For example, it includes a GROUP BY, join, or UNION. Modify the select-statement so that it supports the returning of distinct row change columns.
2: A column defined AS ROW CHANGE TIMESTAMP does not exist in the table. Alter the table so it has a column with the AS ROW CHANGE TIMESTAMP attribute.
3: A column defined AS ROW CHANGE TIMESTAMP was added to the table, but the table has not yet been reorganized. Reorganize the table.

sqlcode: +20462

sqlstate: 0168T

SQL20464N An attempt to revoke the SECADM authority from `authorization-ID` was denied because it is the only external authorization ID of type user with SECADM authority.

Explanation

The SECADM authority must be held by at least one external authorization ID of type user, and the REVOKE statement is attempting to revoke the authority from the last authorization ID of type user that holds this authority. The statement cannot be executed. The SECADM authority is not revoked.

User response

Grant the SECADM authority to another external authorization ID of type user to allow the SECADM authority to be revoked from authorization-ID.

sqlcode: -20464

sqlstate: 42523

SQL20465N The binary XML value is incomplete or contains unrecognized data at location `position` starting with the hex data `text`. Reason code = `reason-code`.

Explanation

An error occurred when processing a binary XML value. The first eight bytes of XML data in hexadecimal at position bytes is text. If the unrecognized data is located within the last eight bytes of the binary XML data, only the data starting at location position up to the end of the binary XML data is displayed. The reason code indicates the specific problem:

The XDBX data specified is incomplete.
An XML sequence was specified for an insert operation and this is not supported.
The XDBX data specified for an insert operation contains an unsupported tag.
The referenced string ID is not previously defined.
The length specified is not correct.

The statement cannot be processed.

User response

Fix the problem in your XML data or change your application to use textual XML format for data transfer.

sqlcode: -20465

sqlstate: 22541

SQL20467N The statement was not executed because an expression cannot be computed as a single value for the query. The invalid expression is in the statement near the syntax element `text`.

Explanation

The expression in the statement near the syntax element text must result in a single value and must consist of one of the following elements:

A constant
A special register
A parameter marker
A variable
A deterministic function with no external action
An expression whose operands are any of the previously listed elements

The expression cannot include any of the following elements:

A column name
A non-deterministic function
A function with external action
An expression whose operands are any of the previously listed elements

User response

Run the statement again, after changing the expression to remove any elements that cannot be included.

sqlcode: -20467

sqlstate: 428H7

SQL20469N Row or column access control activation for table `table-name` has failed due to reason code `reason-code`.

Explanation

Row access control cannot be activated explicitly or implicitly, or column access control cannot be activated explicitly for table table-name. The reason code indicates more specifically why the activation failed:

37: A trigger, object-name, is defined for the table and the trigger is not defined as secure.
38: A view is defined on the table, a trigger, object-name, is defined for that view, and the trigger is not defined as secure.
40: The table is either a typed table, or a catalog table.
41: The table is referenced by a materialized query table and the materialized query table references functions with NOT SECURED attribute.
48: The table is the base table of a shadow table.

The statement cannot be processed.

User response

1: Specify the name of a row permission or column mask that can be changed.
2: For the invalid state, disable the row permission or column mask, then drop and recreate it.

sqlcode: -20472

sqlstate: 428H9

SQL20473N The function `function-name`, created with the NOT SECURE option failed. The function referenced column `column-name` which has a column mask with column access control activated for the table.

Explanation

The input argument of an insecure function must not reference a column which has a column mask defined with column access control activated for the table.

The statement cannot be processed.

User response

Do not reference such a column as the input argument to the function.
Disable or drop the column mask then invoke the function again.

sqlcode: -20473

sqlstate: 428HA

SQL20474N The CREATE PERMISSION or CREATE MASK statement failed on the database object `object-name` of object type `object-type` due to reason code `reason-code`.

Explanation

The CREATE PERMISSION or CREATE MASK statement could not be processed because the definition in the CREATE PERMISSION or CREATE MASK statement references an unsupported object. The reason code indicates what is unsupported:

1: The table for which the row permission or the column mask is being defined
2: A table function
3: A user-defined function that is not secure
4: A function that is defined as not deterministic or is defined to have an external action
5: An OLAP specification
6: An XMLEXISTS predicate
7: A ROW CHANGE expression
8: A sequence reference
9: A created or declared temporary table
10: A table that was implicitly created for an XML column
11: A * or name.* in a SELECT clause
12: A column that is defined with a FIELDPROC
13: A language element that requires multiple encoding scheme processing
14: An ordinary SQL identifier that contains a dash (-)
16: A row permission or column mask that includes a period specification
17: A row permission or column mask that references a history table or a table that is defined within a period
18: An aggregate function that does not specify a SELECT clause
19: A nickname
20: A method
21: A pseudocolumn
22: An XMLQUERY scalar function
24: The base table of a shadow table
33: A data type of the return expression that is not the same as the data type of the column on which the column mask is defined
34: A length attribute of the return expression that is not the same as the length attribute of the column on which the column mask is defined
35: A null attribute of the return expression that is not the same as the null attribute of the column on which the column mask is defined
36: A subtype or encoding scheme that is not the same as the corresponding attribute of the column on which the column mask is defined
37: An attribute of the return expression other than the attributes covered
38: The column is referenced in an expression that defines a generated column.
51: A row permission cannot be created for a table that has a security label column.
52: A row permission cannot reference a table that has a security label column.

The statement cannot be processed.

User response

Correct the error and run the CREATE statement again.

sqlcode: -20474

sqlstate: 428HB

SQL20475N The CREATE MASK statement failed because a column mask is already defined for the specified column. Column name: `column-name`. Table name: `table-name`. Existing mask name: `mask-name`.

Explanation

Only a single mask can be defined for a column.

The statement cannot be processed.

User response

Drop the existing column mask by issuing the DROP MASK SQL statement.
Then create the new column mask by issuing the CREATE MASK statement again.

sqlcode: -20475

sqlstate: 428HC

SQL20476N The `function-name` function was invoked with an invalid format string `format-string`.

Explanation

An invalid format string was specified for the function-name function. The value for function-name could be VARCHAR_FORMAT or DECFLOAT_FORMAT, even if the name used to invoke the function was TO_CHAR or TO_NUMBER. A valid format string for the VARCHAR_FORMAT function must:

Have an actual length of the data type that is not greater than 254 bytes
Only contain supported format elements
Not result in a string with an actual length that is greater than the length attribute of the result

A valid format string for the DECFLOAT_FORMAT function must:

Have an actual length of the data type that is not greater than 254 bytes
Contain at least one format element
Only contain supported format elements

The statement cannot be processed.

User response

Change the format string argument of the function-name function. For more information, see the corresponding description of the function in the SQL Reference.

sqlcode: -20476

sqlstate: 22018

SQL20477N The `function-name` function is not able to use format string `format-string` to interpret the argument `string-expression`.

Explanation

The function-name function was invoked with format-string and string-expression. The value for function-name would be DECFLOAT_FORMAT, even if the name used to invoke the function was TO_NUMBER. The value of the argument string-expression cannot be interpreted with the format string format-string to produce a DECFLOAT(34) value.

This error can occur for any of the following reasons:

string-expression is too long for the specified format string
string-expression does not conform to the template specified in the format string; for example:
- Too many digits were specified in string-expression for the corresponding format element in the format string, such as in the specific case where 1234 is not a valid value for 999
- A value in string-expression is not valid for the corresponding format element in the format string, such as in the specific case where $ is not a valid value for S

The statement cannot be processed.

User response

Change the arguments of the function-name function to valid values. For more information, see the corresponding description of the function in the SQL Reference.

sqlcode: -20477

sqlstate: 22018

SQL20478N The statement failed because the column mask `mask-name` defined for column `column-name` exists and the column mask cannot be applied or the column mask conflicts with the failed statement. Reason code `reason-code`

Explanation

A reference to a column mask is not supported. The cause of the error is described by the following reason codes:

1: The result table of the select is derived from a set operation that involves an EXCEPT, EXCEPT ALL, INTERSECT, INTERSECT ALL or UNION set operator. Change the query to not reference the column in the select list or do not use the set operation in this context.
22: The column column-name is input to a table or row function in the statement. Some references to the function result require the column mask mask-name be applied to the function input, and some references to the function result do not require the column mask. If the specified column is input to a table or row function, all references to the function result must have the same mask requirement.
30: An INSERT or UPDATE operation uses a masked value returned from the column mask mask-name for column-name. The expression specified in the THEN or ELSE clause of the column mask definition that is used to return the masked value is not a simple reference to the column column-name. For the specified INSERT or UPDATE operation, the return expression in the column mask definition must be a simple reference to the column for which the mask is defined.

The statement cannot be processed.

User response

Remove the reference to the column in the INSERT or UPDATE operation and retry the operation.
Contact the Security Administrator to have the return expression in the column mask definition modified.

sqlcode: -20478

sqlstate: 428HD

SQL20479N The ALTER or RENAME statement failed on the table `table-name` because the table is part of row or column access control definitions. Reason code `reason-code`.

Explanation

The table table-name in an ALTER or RENAME statement cannot be altered as specified for one of the following reasons:

1: The table is referenced in one or more column mask or row permission definitions.
2: A column in the table is referenced in one or more column mask or row permission definitions.

The statement cannot be processed.

User response

Drop the permission or mask and recreate them after the alter or rename operation is completed.
Consider temporarily protecting the table on which the permission or mask was defined at the row level to ensure there is no window in which the table remains without row and column access control protection.

1: The table is already defined as a system-period temporal table or history table.
2: The table does not have a SYSTEM_TIME period or a transaction-start-ID column. A system-period temporal table must have a SYSTEM_TIME period and a transaction-start-ID column.
3: The table is a materialized query table.
4: For a DB2 for z/OS server, the table has a column mask or row permission defined.
5: The transaction-start-ID column must be defined with the same data type, length, precision, and scale as the row-begin column and row-end column of the SYSTEM_TIME period in the table.
6: The table or the associated history table has the NOT LOGGED INITIALLY attribute activated.
7: The table has a security policy associated with it.

The statement cannot be processed.

User response

Correct the syntax and resubmit the statement.

sqlcode: -20490

sqlstate: 428HM

SQL20491N The statement failed because the specification of a period `period-name` is not valid. Reason code `reason-code`.

Explanation

The specification of a period attribute in a CREATE or ALTER statement is invalid for the reason code indicated:

1: The row-begin column name must not be the same as the row-end column name for the period.
2: The name of a column in a period must not be the same as a column used in the definition of another period for the table.
3: The data type, length, precision, and scale for the row-begin column must be the same as for the row-end column.
4: The type of timestamp specified for the row-begin column must be the same as the type of timestamp specified for the row-end column.
5: For a BUSINESS_TIME period, the column must not be a column defined with a GENERATED clause.
6: For a SYSTEM_TIME period, the row-begin column must be defined as ROW-BEGIN and the row-end column must be defined as ROW-END.

The statement cannot be processed.

User response

Correct the syntax and resubmit the statement.

sqlcode: -20491

sqlstate: 428HN

SQL20494N A public alias name, `name`, can be qualified only with SYSPUBLIC and not the schema name `schema-name`.

Explanation

A qualified name was specified for a public alias but the qualifier was not SYSPUBLIC.

User response

Change the two-part name to a one-part name or specify the qualifier SYSPUBLIC. If a public alias was not intended and PUBLIC was specified in the statement, remove the keyword PUBLIC.

sqlcode: -20494

sqlstate: 428EK

SQL20495N The definition of the module initialization procedure SYS_INIT is not valid. Reason code `rc`.

Explanation

Initialization procedure definitions can be invalid for the following reasons:

1: The SYS_INIT procedure definition contains parameters.
2: The SYS_INIT procedure definition returns result sets.
3: The SYS_INIT procedure cannot be published.

User response

Modify the initialization procedure definition so that it conforms to the valid syntax.

sqlcode: -20495

sqlstate: 428HP

SQL20496N The routine `name` cannot be invoked because it is only a routine prototype.

Explanation

Routine name is defined as a prototype in a module, but is not fully implemented. A routine prototype with the specified routine name was found in the module, but without a routine body. The routine definition is incomplete and therefore cannot be invoked.

User response

Alter the module and add a full routine definition. Try again.

sqlcode: -20496

sqlstate: 55019

SQL20498N The data type that was specified for the following field is not supported: `field-name` Specified data type `type-name`.

Explanation

The data type specified for a row in the CREATE TYPE statement or CREATE HADOOP TABLE statement with columns of type array is not valid. The following restrictions apply when specifying a data type.

The following data types are not supported:
- XML
- LONG VARCHAR
- LONG VARGRAPHIC
- User-defined type based on any of these previously listed types
- REF (an OID-column-name reference)
- BOOLEAN (prior to Version 9.7.5)
- CURSOR
- SYSPROC.DB2SECURITYLABEL
- User-defined structured data type
- Anchor to a table column or a variable of a data type not supported in row data types
- Anchor to a row of a weakly-typed cursor
- User-defined weakly typed distinct type that includes a data type constraint
- Nested types in local types declared inside an SQL routine
ARRAY types or ROW types can be nested as elements in other ROW type field types, but the maximum nesting level has been exceeded.
For Hadoop tables, the only supported nesting is array of rows.

User response

Ensure that the data types specified for fields in the CREATE TYPE (row) statement, CREATE HADOOP TABLE statement with columns of type array, fields defined by the columns of the anchor table, or anchor view are supported

sqlcode: -20498

sqlstate: 429C5

SQL20499N The data type `typename` is not valid for the operand of the `keywords` predicate.

Explanation

The predicate indicated by keywords does not support the data type typename as the operand of the predicate.

User response

Change the operand to have a data type that is supported by the predicate or remove the predicate with the invalid operand data type.

sqlcode: -20499

sqlstate: 428HQ

SQL20250 - SQL20499

SQL20250N The number of data partitions exceeds the number of table spaces for the table.

Explanation

User response

SQL20251N The last data partition cannot be detached from the table table-name.

Explanation

User response

Explanation

User response

SQL20254N FOREIGN KEY name is not valid since it would cause table table-name to be delete-connected to itself through a cycle that contains a delete rule of either RESTRICT or SET NULL. Reason code = reason-code.

Explanation

User response

Explanation

User response

SQL20256N FOREIGN KEY name is not valid because it would cause two tables table-name1 and table-name2 to be delete-connected to each other while they are delete -connected to the same ancestor table ancestor-table-name through CASCADE relationships. Reason code = reason-code.

Explanation

User response

SQL20257N FINAL TABLE is not valid when the target view viewname of the SQL data change statement in a fullselect has an INSTEAD OF trigger defined.

Explanation

User response

SQL20258N Invalid use of INPUT SEQUENCE ordering.

Explanation

User response

SQL20259N Column column-name cannot be selected from the target of the data change statement in the FROM clause of the fullselect.

Explanation

User response

SQL20260N The assignment clause of the UPDATE statement must specify at least one column that is not an INCLUDE column.

Explanation

User response

SQL20261N Invalid row movement to table table-name within the UNION ALL view view-name.

Explanation

User response

SQL20262N Invalid usage of WITH ROW MOVEMENT in view view-name. Reason code = reason-code.

Explanation

User response

SQL20263N Invalid attempt to update view view-name1 because view view-name2 is defined WITH ROW MOVEMENT.

Explanation

User response

SQL20264N For table table-name, authorization ID auth-id does not have access-type access to the column column-name.

Explanation

User response

SQL20267N The function function-name (specific specific-name) modifies SQL data and is invoked in an illegal context. Reason code = reason-code.

Explanation

User response

SQL20268N Collation cannot be applied. Reason code = reason-code.

Explanation

User response

SQL20269N The nickname nickname cannot be referenced in an enforced referential constraint.

Explanation

User response

SQL20271W The name at ordinal position number in the statement, with name name, was truncated.

Explanation

User response

SQL20273N The nickname statistics cannot be updated because the database is not enabled for federation.

Explanation

User response

SQL20274W Some of the nickname statistics cannot be updated.

Explanation

User response

SQL20275N The XML name xml-name is not valid. Reason code = reason-code.

Explanation

User response

SQL20276N The XML namespace prefix xml-namespace-prefix is not valid. Reason code = reason-code.

Explanation

User response

SQL20277W Characters were truncated while performing conversion from code page source-code-page to code page target-code-page. The maximum size of the target area was max-len. The source string length was source-len and its hexadecimal representation was string.

Explanation

User response

SQL20278W The view viewname may not be used to optimize the processing of queries.

Explanation

User response

SQL20279N The view view-name cannot be enabled for query optimization. Reason code = reason-code.

Explanation

User response

SQL20280W Insufficient permission to create or write to the log-file-path file.

Explanation

User response

SQL20282N .NET procedure or user-defined function name, specific name specific-name could not load .NET class class. Reason code reason-code.

Explanation

User response

SQL20251N The last data partition cannot be detached from the table `table-name`.

SQL20254N FOREIGN KEY `name` is not valid since it would cause table `table-name` to be delete-connected to itself through a cycle that contains a delete rule of either RESTRICT or SET NULL. Reason code = `reason-code`.

SQL20256N FOREIGN KEY `name` is not valid because it would cause two tables `table-name1` and `table-name2` to be delete-connected to each other while they are delete -connected to the same ancestor table `ancestor-table-name` through CASCADE relationships. Reason code = `reason-code`.

SQL20257N FINAL TABLE is not valid when the target view `viewname` of the SQL data change statement in a fullselect has an INSTEAD OF trigger defined.

SQL20259N Column `column-name` cannot be selected from the target of the data change statement in the FROM clause of the fullselect.

SQL20261N Invalid row movement to table `table-name` within the UNION ALL view `view-name`.

SQL20262N Invalid usage of WITH ROW MOVEMENT in view `view-name`. Reason code = `reason-code`.

SQL20263N Invalid attempt to update view `view-name1` because view `view-name2` is defined WITH ROW MOVEMENT.

SQL20264N For table `table-name`, authorization ID `auth-id` does not have `access-type` access to the column `column-name`.

SQL20267N The function `function-name` (specific `specific-name`) modifies SQL data and is invoked in an illegal context. Reason code = `reason-code`.

SQL20268N Collation cannot be applied. Reason code = `reason-code`.

SQL20269N The nickname `nickname` cannot be referenced in an enforced referential constraint.

SQL20271W The name at ordinal position `number` in the statement, with name `name`, was truncated.

SQL20275N The XML name `xml-name` is not valid. Reason code = `reason-code`.

SQL20276N The XML namespace prefix `xml-namespace-prefix` is not valid. Reason code = `reason-code`.

SQL20277W Characters were truncated while performing conversion from code page `source-code-page` to code page `target-code-page`. The maximum size of the target area was `max-len`. The source string length was `source-len` and its hexadecimal representation was `string`.

SQL20278W The view `viewname` may not be used to optimize the processing of queries.

SQL20279N The view `view-name` cannot be enabled for query optimization. Reason code = `reason-code`.

SQL20280W Insufficient permission to create or write to the `log-file-path` file.

SQL20282N .NET procedure or user-defined function `name`, specific name `specific-name` could not load .NET class `class`. Reason code `reason-code`.

SQL20284N No plan was possible to create for federated data source `server-name`. Reason = `reason-code`.

SQL20285N The statement or command was not processed either because the table named `table-name` has detached dependent tables or because an asynchronous partition detach operation on the table is not complete. Reason code = `reason-code`.

SQL20288N Statistics could not be updated for the object. Object name: `object-name`. Object type: `object-type`. Reason code: `reason-code`.

SQL20289N Invalid string unit `unit` in effect for function `function-name`.

SQL20290N The SQL statement references routine `routine-name` (specific name `specific-name`) which cannot be run on partition `partition-number`.

SQL20296N The ALTER TABLE statement is not allowed as it modifies some physical attribute of the table `table-name` that has detached partitions and there are dependant tables that need to be incrementally maintained with respect to these detached partitions.

SQL20304N The index was not created because of a problem with an XMLPATTERN clause or a column of data type XML. Reason code: `reason-code`.

SQL20306N An index on an XML column cannot be created because of an error detected when inserting the XML values into the index. Reason code = `reason-code`. For reason codes related to an XML schema the XML schema identifier = `xml-schema-id` and XML schema data type = `xml-schema-data-type`.

SQL20307N Table `source-tablename` cannot be attached to table `target-tablename`. Reason code = `reason-code`.

SQL20316N Invalid compilation environment. Reason code = `reason-code`.

SQL20318N The ALTER TABLESPACE statement failed because the change is not allowed for the type of table space. Table space name: `tablespace-name`. Table space type: `tablespace-type`. Incompatible clause: `clause`.

SQL20322N The database name provided does not match `server-name`, the name of the database that the application is connected to.

SQL20323N The storage path `storage-path` already exists for the storage group or is specified more than once.