SQL20000 - SQL20249

SQL20005N The statement failed because the maximum number of internal identifiers has been exceeded for the object type. Internal identifier limit: of `limit`. Object type: `object-type`.

Explanation

When one of several types of database object is created, declared, or altered, an internal identifier is created to uniquely identify that object. There is an upper limit on the number of unique identifier that can be created.

This message is returned when the limit for internal identifiers for this type of object has been exceeded.

User response

Respond to this error by performing the following troubleshooting steps:

Drop objects of type object-type that are no longer being used.
If object-type is DECLARE TYPE, remove the type declarations within the current compound SQL (compiled) statement for any types that are not being used.
If object-type is a workload management object, you can reset the numbering of the internal identifiers by performing the following steps:
1. Extract the DDL statements that would reproduce your workload management objects by using the db2look utility, specifying the -wlm option.
2. Drop all of the workload management objects of the specified type from the database.
3. Recreate the workload management objects of the specified type using the information from the db2look utility generated file.

sqlcode: -20005

sqlstate: 54035

SQL20010N Mutation method `method-ID` is not allowed when the instance of the structured type is NULL.

Explanation

The method method-ID is a mutator method that is specified with an instance of a structured type that is null. Mutator methods cannot be processed on a null instance. In some cases, the method name is not available.

The statement cannot be processed.

User response

Determine the null instance that is used with a mutator method. Use a constructor function to create a non-null instance of the structured type before using any mutator methods on the instance.

To determine the method name associated with the method-ID, use the following query:

SELECT FUNCSHEMA, FUNCNAME,
       SPECIFICNAME
 FROM SYSCAT.FUNCTIONS
 WHERE
   FUNCID = INTEGER(
            method-ID
            )

User response

Specify a valid type, table, or view in the UNDER clause of the CREATE statement.

sqlcode: -20013

sqlstate: 428DB

SQL20014N The transform group `group-name` `transform-type` transform function for type `type-name` is not valid. Reason code = `reason-code`.

Explanation

The transform-type transform function in transform group group-name is not valid. The reason is based on the reason-code as follows.

1: There can only be one parameter for a FROM SQL transform function.
2: The parameter of a FROM SQL transform function must be of type type-name.
3: The RETURNS data type of a TO SQL transform function must be of type type-name.
4: The RETURNS type of a FROM SQL transform function that returns a scalar must be a built-in data type other than DECIMAL.
5: All of the RETURNS types of a FROM SQL transform function that returns a scalar must be built-in data types other than DECIMAL.
6: There must be at least one parameter for a TO SQL transform function.
7: The parameter types for a TO SQL transform function must all be built-in data types other than DECIMAL.
8: The TO SQL transform function must be a scalar function.
9: The FROM SQL transform function must be written using LANGUAGE SQL, or must use another FROM SQL transform function that is written using LANGUAGE SQL.
10: The TO SQL transform function must be written using LANGUAGE SQL or use a TO SQL transform function that is written using LANGUAGE SQL.

The statement cannot be processed.

User response

1: Specify a FROM SQL transform function with a signature that has only one parameter.
2: Specify a FROM SQL transform function where the type of the parameter is the same as type-name.
3: Specify a TO SQL transform function where the RETURNS type is the same as type-name.
4: Specify a FROM SQL transform function with a RETURNS type that is a built-in data type other than DECIMAL.
5: Specify a FROM SQL transform function where each element of the row has a RETURNS type that is a built-in data type other than DECIMAL.
6: Specify a TO SQL transform function with a signature that has at least one parameter.
7: Specify a TO SQL transform function where all the parameter types are built-in data types other than DECIMAL.
8: Specify a TO SQL transform function that is a scalar function.
9: Specify a FROM SQL transform function that is written using LANGUAGE SQL, or that uses another FROM SQL transform function that is written using LANGUAGE SQL.
10: Specify a TO SQL transform function that is written using LANGUAGE SQL or that uses a TO SQL transform function that is written using LANGUAGE SQL.

sqlcode: -20014

sqlstate: 428DC

SQL20015N A transform group `group-name` is not defined for data type `type-name`.

Explanation

A specified transform group group-name is not defined for the data type type-name. The data type type-name may have been explicitly specified in the statement or may be implicit based on the use of a structured type that requires the existence of the transform group for the data type.

If group-name is empty, either the TRANSFORM GROUP bind option or the CURRENT DEFAULT TRANSFORM GROUP special register was not specified and therefore there was no transform for type-name.

The statement cannot be processed.

User response

Define transform group transform-type for data type type-name using the CREATE TRANSFORM statement. If the error occurred when dropping the transform, no action is required because the transform group did not exist for the data type.

If group-name is empty, specify the TRANSFORM GROUP bind option on the CURRENT DEFAULT TRANSFORM GROUP special register.

sqlcode: -20015

sqlstate: 42741

SQL20016N The value of the inline length associated with type or column `type-or-column-name` is too small.

Explanation

For the definition of structured type type-or-column-name, it has an INLINE LENGTH value specified that is smaller than the size returned by the constructor function (32 + 10 * number_of_attributes) for the type and is less than 292. For the altering of column type-or-column-name, the INLINE LENGTH specified is smaller than the current inline length.

The statement cannot be processed.

User response

Specify an INLINE LENGTH value that is large enough. For a structured type, that is at least the size returned by the constructor function for the type or is at least 292. For a column, it must be larger than the current inline length. If this error occurs when altering the type (or some supertype of this type) to add an attribute, either the attribute cannot be added or the type must be dropped and re-created with a larger INLINE LENGTH value.

sqlcode: -20016

sqlstate: 429B2

SQL20017N Addition of this subtype exceeds the maximum number of levels of a type hierarchy.

Explanation

The maximum number of levels in a type hierarchy is 99. Adding this type would exceed the maximum.

The statement cannot be processed.

User response

Do not add any more subtypes to this type hierarchy.

sqlcode: -20017

sqlstate: 54045

SQL20018N Row function `function-name` must return at most one row.

Explanation

The function is a defined to return a single row. The result of the processing the function is more than one row.

User response

Ensure that the function is defined in such a way that at most one row is returned.

sqlcode: -20018

sqlstate: 21505

SQL20019N The result type returned from the function body cannot be assigned to the data type defined in the RETURNS clause.

Explanation

The data type of each column returned by the function body must be assignable to the corresponding column specified in the RETURNS clause.

If the function is a scalar function, there is only one column.

User response

Change the RETURNS type or the type returned from the function body so that the data types of corresponding columns are assignable.

sqlcode: -20019

sqlstate: 42866

SQL20020N Operation `operation-type` is not valid for typed tables.

Explanation

The operation identified by operation-type cannot be performed on a typed table.

The statement cannot be processed.

User response

If it is an ALTER statement, remove the ADD COLUMN clause, ADD PERIOD clause, or SET DATATYPE clause. Columns can only be added by re-defining the table with a structured type that includes the new column as an attribute. Similarly, the data type of a column can only be changed by re-defining the table with a type that includes the column with a different data type.

If it is a CREATE TABLE statement, remove the DISTRIBUTE BY RANDOM clause. Typed tables are not allowed to be created as random distribution tables.

sqlcode: -20020

sqlstate: 428DH

SQL20021N Inherited column or attribute `name` cannot be changed or dropped.

Explanation

The value of name identifies either a column name or an attribute name (depending on the statement context) that is inherited from a typed table, typed view, or structured type above it in the table, view, or type hierarchy.

In the CREATE TABLE statement, the WITH OPTIONS clause cannot be specified for column name in the CREATE TABLE statement because it is inherited from a supertable in the table hierarchy.
In the ALTER TABLE statement, the SET SCOPE clause or COMPRESS clause cannot be specified for column name because it is inherited from a supertable in the table hierarchy.
In the CREATE VIEW statement, the WITH OPTIONS clause cannot be specified for column name in the CREATE VIEW statement because it is inherited from a superview in the view hierarchy.
In the ALTER TYPE statement, the DROP ATTRIBUTE clause cannot be specified for attribute name because it is inherited from a supertype in the type hierarchy.

not a typed table or
not a typed view or
the type of the table or view is not the same as the target type of the REF type.

The statement cannot be processed.

User response

Specify the scope of the reference using a typed table or typed view with the same type as the target type of the REF type.

sqlcode: -20024

sqlstate: 428DM

SQL20025N SCOPE is not specified in the RETURNS clause of an external function or is specified in the RETURNS clause of a sourced function.

Explanation

There are two possible causes:

A reference type must have a scope defined when used as the result of a user-defined external function.
A reference type cannot have a scope defined when used as the result of an user-defined sourced function. The function will use the scope of the source function.

The statement cannot be processed.

User response

When defining an external function with a reference type as a returns type, ensure that the SCOPE clause is specified. When defining SOURCED function with a reference type as a returns type, ensure that the SCOPE clause is not specified.

sqlcode: -20025

sqlstate: 428DN

SQL20026N The type `type-name` is not a structured type or is not an instantiable structured type.

Explanation

The statement requires a structured type that can be instantiated. The type type-name is either:

not a structured type; or
a structured type that is defined as not instantiable.

The subtable or subview cannot be a random distribution table.

The statement cannot be processed.

User response

When creating the root view of a typed view hierarchy, an object identifier (OID) column must be defined (using the REF IS clause) on the CREATE VIEW statement.

The statement cannot be processed.

User response

Add the required OID column (REF IS clause) to the CREATE TABLE or CREATE VIEW statement.

sqlcode: -20037

sqlstate: 428DX

SQL20038N The statement could not be processed because the statement contains the following incompatible clauses or elements: `keywords1` and `keywords2`.

Explanation

The keywords2 clause in the statement cannot be specified with the keywords1 clause. If CODEUNITS32 is one of the incompatible elements, note that the string unit might be set implicitly with the string_units configuration parameter.

User response

Remove either the keywords1 or the keywords2 clause from the statement.
If CODEUNITS32 is one of the incompatible elements, explicitly specify OCTETS as the string unit for the data type.

sqlcode: -20038

sqlstate: 42613

SQL20039N The definition of index `index-name` does not match the definition of index extension `index-ext-name`.

Explanation

The index definition and the index extension definition do not match. The following lists the possibilities for where the definitions do not match.

The number of arguments following the index extension name in the EXTEND USING clause is not the same as the number of instance parameters of the index extension.
The data types of the arguments following the index extension name in the EXTEND USING clause do not match exactly (including length or precision and scale) the data types of the corresponding instance parameters of the index extension.
The number of columns specified for the index is not same as the number of source key parameters of the index extension.
The data types of the index columns do not match exactly (including length or precision and scale) the data types of the corresponding source key parameters of the index extension. There is an exception to exact matching of data types for the case of subtypes. The column of the index may be a subtype of the data type specified as the corresponding source key parameter.

The statement cannot be processed.

User response

Change the index definition so that the index extension matches.

sqlcode: -20039

sqlstate: 428E0

SQL20040N Number or type of the result of the range-producing table function `range-function-name`, is inconsistent with that of key transformation table function `transform-function-name` for index extension `index-ext-name`.

Explanation

The range-producing function must:

return up to twice as many columns as returned by the key transformation function
have an even number of columns (the first half of the return columns are the start key values and the second half of the return columns are the stop key values)
have the each start key column with the same type as the corresponding stop key column
have the type of each start key column be the same as the corresponding transformation function column.

The routine routine-name could be the transformation function, the range-producing function, or a routine (function or method) referenced in the FILTER USING clause. The reason code indicates why an argument is not valid:

1: For a key transformation function, the argument is not an observer method or an index extension instance parameter.
2: The expression used as the argument uses a routine that specifies LANGUAGE SQL.
3: The expression used as the argument is a subquery.
4: The data type of an expression used as the argument cannot be a structured type.
5: The argument of a key transformation function cannot have a data type of structured data type, LOB, DATALINK, XML, LONG VARCHAR, or LONG VARGRAPHIC.
6: The expression used as an argument contains an XMLQUERY or XMLEXISTS expression.

The statement cannot be processed.

User response

Specify a valid argument for the function.

sqlcode: -20043

sqlstate: 428E3

SQL20044N The routine `routine-name` or the CASE expression is not valid in a CREATE INDEX EXTENSION or CREATE FUNCTION statement. Reason code = `reason-code`.

Explanation

A routine (function or method) routine-name used in the CREATE INDEX EXTENSION or CREATE FUNCTION statement is not valid where it is used. If routine-name is empty, then the CASE expression used for filtering is not valid. The reason code indicates the reason.

1: The key transformation function is not a table function.
2: The key transformation function is not an external function.
3: The key transformation function is a variant function.
4: The key transformation function is an external action function.
5: The range-producing function is not a table function.
6: The range-producing function is not an external function.
7: The range-producing function is a variant function.
8: The range-producing function is an external action function.
9: The index-filter function is not an external function.
10: The index-filter function is a variant function.
11: The index-filter function is an external action function.
12: The result type of a filter function or CASE expression is not an integer data type.
13: A subquery is used in a CASE expression or as an argument of a filter function.
14: The key transformation function does not have the same encoding scheme as the database.
15: The range-producing function does not have the same encoding scheme as the database.
16: The index-filter function does not have the same encoding scheme as the database.
17: The filter function is not an external function.
20: An XMLQUERY or XMLEXISTS is used in a CASE expression or as an argument of a filter function.

1

A subview definition contains a common table expression, OR a branch in a subview definition does not range over a single table, view, nickname or an alias.

2

The row-set of a branch over a table-hierarchy cannot be proven by the database manager to be distinct from the row-set of all branches over the same table-hierarchy in the rest of the typed view hierarchy.

3

The first expression of a branch over a hierarchy in the root view is:

not the object identifier column of the typed table or typed view referenced in the FROM clause and the UNCHECKED option in the REF IS clause is not used OR
if the table in the FROM clause is not typed, the column is nullable or does not have a unique index defined on only that column and the UNCHECKED option in the REF IS clause is not used OR
not identical to the expression in the branch over the same hierarchy in the subview.

4

A table or view over which a branch of a subview ranges is not a subtable or subview of the table or view referenced in any branch of the superview and either the subview uses EXTEND AS or the root view does not have the UNCHECKED option in the REF IS clause turned on.

5

The fullselect includes references to the NODENUMBER or PARTITION functions, non-deterministic functions, or functions defined to have external action.

6

A branch in a subview cannot range over an OUTER table or view if any branch in its superview ranges over a table or view in the same hierarchy without using OUTER.

7

A subview ranges over a view in its own view hierarchy.

8

A subview uses a set operation other than UNION ALL in its definition OR UNION ALL is used in the definition without specifying the UNCHECKED option in the REF IS clause of the root view.

9

A subview contains two branches of a UNION ALL that range over the same table hierarchy or view hierarchy.

10

The subview definition contains a GROUP BY or HAVING clause.

User response

Change the fullselect of the view definition based on the reason-code.

1: Use only one table or view in the FROM clause. Encapsulate complex selects into views that the typed view can range over using the UNCHECKED option of the root view's REF IS clause.
2: Specify a different table or view in the FROM clause of each branch that is not the same as one already used in the view hierarchy or use a predicates that clearly define the row-set for each branch as distinct compared to the row-sets for other branches in the typed view hierarchy.
3: Ensure that the first column of the root view conforms to the rules to be a valid object identifier column for the typed view. Consider using the UNCHECKED option in the ref is clause.
4: Specify a subtable or subview of the table or view specified in the FROM clause of a branch of the superview. Or use the UNCHECKED option in the root view definition in conjunction with the AS (without EXTEND) clause in the subview definition.
5: Remove the reference to the function from the fullselect.
6: If this is the first subview to use OUTER in a branch over this hierarchy, change the FROM clause so that OUTER is not used. If the superview uses OUTER, include OUTER in the FROM clause of the subview.
7: Do not source a subview on other views in the same hierarchy.
8: If UNION ALL was used, use the UNCHECKED option in the REF IS clause of the rootview to allow multiple branches in the subview definition. For other set operations encapsulate the set operation into a view and use the UNCHECKED option in the subview to allow sourcing on common views.
9: Unify the branches to a select over their common supertable or superview and use predicates (e.g. the type predicate) to filter for the desired rows.
10: Encapsulate the GROUP BY and HAVING clause into a view and use the UNCHECKED option in the root view to allow sourcing on common views.

sqlcode: -20053

sqlstate: 428EA

SQL20054N The operation was not performed because the table is in an invalid state for the operation. Table name: `table-name`. Reason code: `reason-code`.

Explanation

The table is in a state that does not allow the operation. The reason code indicates the state of the table that prevents the operation.

21

The table is in Datalink Reconcile Pending (DRP) state or Datalink Reconcile Not Possible (DRNP) state.

22

The generated column expression cannot be added or altered because the table is not in check pending mode.

23

The maximum number of REORG-recommended alters have been performed. Up to three REORG-recommended operations are allowed on a table before a reorg must be performed, to update the tables rows to match the current schema.

24

Executing an ALTER TABLE statement with the ADD COLUMN clause on a LOB or LONG column is not allowed in the same unit of work as an ALTER TABLE statement with the DROP COLUMN clause on the last LOB or LONG column in the table.

In addition, on database servers Version 9.7 and later, executing an ALTER TABLE statement with the ADD COLUMN clause on an XML column is not allowed in the same unit of work as an ALTER TABLE statement with the DROP COLUMN clause on the last XML column in the table.

25

The table is in set integrity pending state.

26

Executing the ALTER TABLE ... ATTACH PARTITION or the ALTER TABLE ... DETACH PARTITION statement on a table whose database partition group is currently being redistributed is not allowed.

27

The alter table or copy schema operation called an internal procedure to manage database objects. The SYSTOOLS.DB2LOOK_INFO table used by the procedure is not in a valid state for the alter or copy operation. The SYSTOOLS.DB2LOOK_INFO table could be an older version, or totally different from what the procedure expects.

29

The operation that empties or truncates a table is not allowed because the table has a partition in one of the following three states:

Attached that has not yet been checked for integrity (SYSCAT.DATAPARTITIONS.STATUS = 'A').
Detached partitions and there are dependent tables that need to be incrementally maintained with respect to these detached partitions (SYSCAT.DATAPARTITIONS.STATUS = 'D').
Detached partitions pending index cleanup (SYSCAT.DATAPARTITIONS.STATUS = 'I').

30

A LOAD operation cannot be restarted when the following three things are true:

The LOAD operation failed in the BUILD, DELETE or INDEX COPY PHASE
The target table contains an XML column
Either one of the following is also true:
- The table contains a unique index defined on an XML column
- ALLOW READ ACCESS was specified with the original LOAD command
- COPY YES was specified

43

The partitioned table has detached dependent tables and cannot support the creation of partitioned indexes.

44

The table is a source table for an attach partition operation of an ALTER TABLE statement and the table has a deferred index cleanup operation in progress as the result of an MDC rollout. Since MDC rollout using the deferred index cleanup mechanism is not supported for partitioned indexes, the attach operation will not be allowed if there are any RID indexes on the source table that will be kept during the attach operation, not rebuilt, and are pending asynchronous index cleanup of the rolled-out blocks.

49

The ALTER TABLE statement specifies SET DATA TYPE for a column that has already been changed and a REORG is pending on the table.

50

The operation could not be performed on the table because a dictionary for the table is currently being created in a background process.

User response

Respond to this error according to the reason code:

21

Refer to the Administration Guide for information on Datalink Reconcile Pending (DRP) and Datalink Reconcile Not Possible (DRNP) states to take appropriate action.

22

Use SET INTEGRITY FOR table-name OFF before altering the table. Then alter the table and use SET INTEGRITY FOR table-name IMMEDIATE CHECKED FORCE GENERATED to generate the values for the new or altered column.

23

Reorg the table using the reorg table command.

24

Complete the unit of work that dropped the last LOB, LONG, or XML column, and re-issue the command.

25

Move the table out of set integrity pending state by using the SET INTEGRITY statement with IMMEDIATE CHECKED option, and then perform the operation again.

26

Wait for the REDISTRIBUTE DATABASE PARTITION GROUP command to complete and then reissue the ALTER TABLE statement.

27

Either rename or drop the SYSTOOLS.DB2LOOK_INFO table. The internal procedure will create the correct version of the SYSTOOLS.DB2LOOK_INFO table. Resubmit the alter table or copy schema operation.

29

Depending on the state of the table partition which is blocking the operation, take the appropriate action and then issue the command again:

SYSCAT.DATAPARTITIONS.STATUS = 'A': Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED or IMMEDIATE UNCHECKED option on the table with attached partitions
SYSCAT.DATAPARTITIONS.STATUS = 'D': Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED option on the dependent immediate materialized query and staging tables of the tables that still need to be incrementally maintained with respect to the detached partitions. Query the SYSCAT.TABDETACHEDDEP catalog view to locate these detached dependent tables.
SYSCAT.DATAPARTITIONS.STATUS = 'I': Wait until index cleanup for the detached partition is completed. Query the SYSIBM.SYSTASKS to determine progress.

30

Respond to reason code 30 by following these steps:

Change the state of the table from "Load in Progress" to "Normal" using the LOAD TERMINATE command.
Rerun the original LOAD command.

43

Use the SET INTEGRITY statement with the IMMEDIATE CHECKED option to maintain the dependent tables, then create the partitioned index.

44

Wait for the deferred index cleanup operation to complete on the source table, then reissue the ALTER TABLE statement to attach the partition. To monitor the progress of the rollout cleanup, use the LIST UTILITIES command.

49

If the attempted data type change is in the same unit of work as the pending data type change, roll back the pending change and then reissue the ALTER TABLE statement. Otherwise, reorganize the table using the REORG TABLE command and then try the data type change again.

50

Wait for the background process that is creating the dictionary for the table to complete, and then reissue the command.

sqlcode: -20054

sqlstate: 55019

SQL20055N A result column data type in the select list is not compatible with the defined data type for column `column-name`.

Explanation

The data type of the select list expression corresponding to column-name is not assignable to the data type for the attribute of the structured type.

User response

Examine the current definition for the table and the associated structured type. Ensure the select list expression data type for the specified column is data type that is assignable to the attribute of the structure type.

sqlcode: -20055

sqlstate: 42854

SQL20056N Processing on DB2 Data Links Manager `name` encountered an error. Reason code = `reason-code`.

Explanation

The processing on the DB2 Data Links Manager for the statement encountered an error as indicated by the following reason codes.

01: An inconsistency was detected between the data on the DB2 Data Links Manager and a DATALINK value in a table.
02: The DB2 Data Links Manager reached a resource limit during the processing.
03: The DB2 Data Links Manager does not support file pathnames longer than 128 characters.
99: The DB2 Data Links Manager encountered an internal processing error.

The statement cannot be processed.

User response

The action is based on the reason code as follows.

01: Run the reconcile utility on the table.
02: The DB2 Data Links Manager administrator should identify the resource from the diagnostic logs and take corrective action.
03: You should ensure that the file pathname (excluding the file system prefix) to be stored in the DATALINK column does not exceed 128 characters. For example, in the URL "http://server.com/dlfiles/dir1/.../file1" -- assuming the DLFS file system prefix is "/dlfiles" -- the file pathname "/dir1/.../file1" must not exceed 128 characters.
99: Save the diagnostic logs from the DB2 Data Links Manager and the database manager and contact IBM service.

sqlcode: -20056

sqlstate: 58004

SQL20057N Column `column-name` in subview `view-name` cannot be defined as read only when the corresponding column is updatable in the superview.

Explanation

The column identified by column-name in the subview view-name is defined (implicitly) as read only. The superview of view-name includes the corresponding column that is updatable. A column cannot be changed from updatable to read only in a typed view hierarchy.

The statement cannot be processed.

User response

Change the CREATE VIEW statement so that the column of the subview view-name is updatable or drop the superview(s) and recreate them using the READ ONLY clause to force the column to be read only.

sqlcode: -20057

sqlstate: 428EB

SQL20058N The statement failed because the fullselect specified for the materialized query table `table-name` violates a restriction. Reason code = `reason-code`.

Explanation

Restrictions apply to the contents of a fullselect used in the definition of a materialized query table. Some restrictions are based on the materialized query table options, such as REFRESH DEFERRED or REFRESH IMMEDIATE. Other restrictions are based on whether or not the table is replicated. The fullselect in the statement that returned this condition violates at least one of these restrictions.

If this message is returned during the creation of a staging table, the error applies to the query used in the definition of the materialized query table with which the staging table is associated.

The reason code indicate which restriction was violated:

1

One or more of the select list elements have no name.

2

The fullselect referenced an unsupported object type.

3

The fullselect contained column references or expressions of an unsupported data type.

4

The fullselect contained column references or expressions or functions that violate one or more restrictions.

5

The REPLICATED option violated a restriction.

6

A fullselect violated a restriction when REFRESH IMMEDIATE was specified

7

The REFRESH IMMEDIATE option violated a restriction.

8

The REFRESH IMMEDIATE option was specified when a fullselect contained a GROUP BY clause and a restriction was violated.

9

The REFRESH IMMEDIATE option was specified and a fullselect was not a subselect.

10

The FROM clause referenced more than one table, and either the REFRESH IMMEDIATE option or the MAINTAINED BY REPLICATION option was specified.

11

The REFRESH IMMEDIATE option was specified and the input table expressions of a UNION ALL or a JOIN contained one or more aggregate functions.

12

The incremental maintenance of the materialized query table required space which exceeded the largest system temporary table space currently available in the database.

13

The fullselect included a CONNECT BY clause.

14

MAINTAINED BY FEDERATED_TOOL was specified in the CREATE TABLE statement and the select clause contained a reference to a base table.

15

An attempt was made to create an MQT in one of the following ways:

A column-organized MQT is not defined as MAINTAINED BY REPLICATION but references a row-organized table
A column-organized MQT does not specify the ORGANIZE BY COLUMN clause
A row-organized MQT references a column-organized table

16

An attempt was made to create a column-organized materialized query table (MQT) in one of the following ways:

Without specifying the MAINTAINED BY USER clause
Without specifying the MAINTAINED BY REPLICATION clause
By specifying the MAINTAINED BY SYSTEM clause without specifying the REFRESH DEFERRED and DISTRIBUTE BY REPLICATION clauses
By specifying the REFRESH IMMEDIATE clause

17

The MAINTAINED BY REPLICATION clause was specified to create a shadow table, but the fullselect references a table that either violates a restriction or is missing a requirement of shadow tables.

18

The MAINTAINED BY REPLICATION clause was specified to create a shadow table, but the fullselect references columns that violate a restriction or that are missing a requirement for shadow tables.

19

The MAINTAINED BY REPLICATION clause was specified to create a shadow table, but the fullselect includes an expression or clause that is not supported with creating shadow tables.

20

The MAINTAINED BY REPLICATION clause was specified to create a shadow table, but the names of the columns of the shadow table do not exactly match the names of the selected columns of the base table.

User response

Respond according to the reason code:

1

Ensure all elements have a name. You can use the AS clause to name expressions, or explicitly name all the columns in the column list of the materialized query table definition.

2

Do not reference unsupported objects.

3

Do not reference unsupported column or expression types.

4

Do not reference unsupported columns, expressions or functions.

5

Create the materialized query table as not replicated. Or correct the statement so the query refers to a single table and does not contain subqueries, aggregation, or the PARTITIONING clause.

6

Create the materialized query table as REFRESH DEFERRED, or:

remove nickname references.
remove DISTINCT.
remove special registers and built-in functions that depend on the value of a special register.
remove the not deterministic function or replace it with a deterministic function.
remove all OLAP, sampling, and text functions.
remove the aggregate function from the expression or change the expression to be a simple reference to the aggregate function.
remove the aggregate functions or add a GROUP BY clause.
correct the CREATE TABLE statement to ensure that no recursive common table expression is referenced.
remove the subquery.

7

Create the materialized query table as REFRESH DEFERRED, or:

correct the CREATE TABLE statement to ensure all GROUP BY items are in the select list.
correct the GROUP BY clause to ensure there are no duplicate grouping sets.
remove the nullable column, C, or add GROUPING(C) in the select list.
correct the CREATE TABLE statement to ensure at least one unique key from each table referenced in the query appears in the select list.

8

Create the materialized query table as REFRESH DEFERRED, or:

add COUNT(*) or COUNT_BIG(*) to the select list or remove the GROUP BY clause.
add COUNT(*) to the select list or remove SUM(C), or alter the column C to be not nullable.
remove unsupported aggregate functions or replace them with supported functions.
remove the HAVING clause.
correct the CREATE TABLE statement to ensure that the GROUP BY clause contains all of the partitioning key columns.

9

Create the materialized query table as REFRESH DEFERRED, or change the table definition to a subselect or to a UNION ALL in the input table expression of a GROUP BY.

10

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

If REFRESH IMMEDIATE is specified and the FROM clause used the explicit INNER JOIN syntax, change the FROM clause to reference a single table or replace the explicit INNER JOIN syntax with the implicit inner join syntax.
If REFRESH IMMEDIATE was specified, replace it with REFRESH DEFERRED.
If MAINTAINED BY REPLICATION was specified, change the FROM clause to reference a single table.

11

Create the materialized query table as REFRESH DEFERRED, or remove the aggregate functions from the input table of the UNION ALL or JOIN.

12

Create the materialized query table as REFRESH DEFERRED, or reduce the total row width, or number of columns of the MQT. Create a system temporary table space with suitable page size.

13

Ensure that the fullselect does not include a CONNECT BY clause. A hierarchical query cannot be used to create a materialized query table.

14

Rewrite the CREATE TABLE statement so that the select clause does not contain a reference to a base table.

15

Ensure that the column-organized MQT is referencing only column-organized tables or specifies MAINTAINED BY REPLICATION, or specifies ORGANIZE BY COLUMN. Also ensure that a row-organized MQT is referencing only row-organized tables. Then re-issue the statement.

16

Ensure that the statement contains one of the following clauses, then re-issue the statement.

MAINTAINED BY USER
MAINTAINED BY REPLICATION
MAINTAINED BY SYSTEM, with the REFRESH DEFERRED and DISTRIBUTE BY REPLICATION clauses
REFRESH DEFERRED, instead of REFRESH IMMEDIATE

17

To create a shadow table on the specified base table, alter the specified base table so that the table is supported as a base table for shadow tables.

18

To create a shadow table on the specified base table, include different columns in the fullselect or ensure that the specified columns satisfy all requirements.

19

Modify the fullselect to satisfy requirements for creating shadow tables.

20

Define the shadow table so that the names of the columns of the shadow table exactly match the names of the selected columns of the base table.

sqlcode: -20058

sqlstate: 428EC

SQL20059W The materialized query table `table-name` may not be used to optimize the processing of queries.

Explanation

The materialized query table is defined with REFRESH DEFERRED and a fullselect that is currently not supported by the database manager when optimizing the processing of queries. The rules are based on the materialized query table options (REFRESH DEFERRED or REFRESH IMMEDIATE). The fullselect in the CREATE TABLE statement that returned this condition violates at least one of the rules as described in the SQL Reference.

The materialized query table is successfully created but will only be routed to for queries that reference the materialized query table directly.

User response

No action is required. However, if the materialized query was intended to optimize the processing of queries that do not reference the materialized query table directly, then this can be achieved as follows. Create a view using the fullselect specified for table-name and then re-create the materialized query table table-name using a fullselect that simply does a "SELECT *" from the view. This way, the materialized query table can be routed to by queries that reference the view.

sqlcode: +20059

sqlstate: 01633

SQL20060N The key transform table function used by the index extension of index `index-id` of table `table-id` in `tbspace-id` generated duplicate rows.

Explanation

The key transform table function specified by the GENERATE USING clause of the index extension used by index index-id generated duplicate rows. For a given invocation of the key transform table function, no duplicate rows should be produced. This error is occurred when inserting or updating the key value for the index index-id of table table-id in table space tbspace-id.

The statement cannot be processed.

User response

The code for the key transform table function used by the index extension of index index-id must be modified to avoid the creation of duplicate rows.

To determine the index name, use the following query:

SELECT IID, INDSCHEMA, INDNAME
  FROM SYSCAT.INDEXES AS I,
       SYSCAT.TABLES AS T
  WHERE IID = <index-id>
      AND TABLEID = <table-id>
      AND TBSPACEID = <tbspace-id>
      AND T.TBASCHEMA = I.TABSCHEMA
      AND T.TABNAME = I.TABNAME

sqlcode: -20064

sqlstate: 428EN

SQL20065N Transform group `group-name` for data type `type-name` cannot be used to transform a structured type for use with a client application.

Explanation

The transform group group-name for data type type-name defines a transform function that cannot be used when performing transforms for client applications. The possible reasons for this are based on the definition of the transform function that is not supported for client applications. The unsupported transform function may be:

a FROM SQL function that is a ROW function
a TO SQL function has more than one parameter

The statement cannot be processed.

User response

For static embedded SQL, specify a different transform group using the TRANSFORM GROUP bind option. For dynamic SQL, specify a different transform group using the SET DEFAULT TRANSFORM GROUP statement.

sqlcode: -20065

SQL20066N The `transform-type` transform function is not defined in the transform group `group-name` for data type `type-name`.

Explanation

The transform-type transform function of transform group group-name for data type type-name is required for a transform group used in a function or method definition.

The statement cannot be processed.

User response

If creating a function or method, specify a different transform group in the function or method definition. If referencing a structured type in a dynamic SQL statement, specify a different transform group for the CURRENT DEFAULT TRANSFORM GROUP special register. Alternatively, add a transform-type transform function to the transform group group-name for data type type-name.

sqlcode: -20066

sqlstate: 42744

SQL20067N The `transform-type` transform function is defined more than once in the transform group `group-name` for data type `type-name`.

Explanation

A TO SQL or FROM SQL transform function can only be specified once in a transform group. The transform group group-name for data type type-name has at least two FROM SQL or TO SQL (or both) transform functions defined.

The statement cannot be processed.

User response

Delete the TO SQL or FROM SQL definitions from group-name in the transform definition so that there is only one of each.

sqlcode: -20067

sqlstate: 42628

SQL20068N The structured type `type-name` may not be defined so that one of its attribute types directly or indirectly uses itself. The attribute `attribute-name` causes the direct or indirect use.

Explanation

Direct use: Type A is said to directly use type B if one of the following is true:

Type A has an attribute of type B
Type B is a subtype of A, or a supertype of A.

Indirect use: Type A is said to indirectly use type B if the following is true:

Type A uses type C, and type C uses type B.

You cannot define a type so that one of its attribute types directly or indirectly uses itself. The type for attribute attribute-name is the cause of the direct or indirect use.

User response

Evaluate the type and remove the attribute type that causes the direct or indirect use.

sqlcode: -20068

sqlstate: 428EP

SQL20069N The RETURNS type of the `routine-type` `routine-name` is not the same as the subject type.

Explanation

The method method-name specifies SELF AS RESULT. The RETURNS data type of the method must be the same as the subject data type of the method.

User response

Change the RETURNS type of the method method-name to match the subject type.

sqlcode: -20069

sqlstate: 428EQ

SQL20075N The index or index extension `index-name` cannot be created or altered because the length of `column-name` is more than 255 bytes.

Explanation

The index could not be created or altered because the key column length is greater than 255.

index-name is the index name.
column-name is the name of the key column. If this error was returned from an ALTER TABLE or ALTER NICKNAME operation, then the value of column-name is the column number.

The index extension could not be created because a column returned by the GENERATE KEY function is greater than 255 bytes.

index-name is the index extension name.
column-name is the name of a column returned by the GENERATE KEY function.

The statement could not be processed. The specified index or index extension was not created, or the table or nickname could not be altered.

User response

If creating an index, eliminate the column from the index definition. If altering a table, reduce the new column length to the permitted maximum. If creating an index extension, specify a different GENERATE KEY function, or redefine the function to eliminate the column.

sqlcode: -20075

sqlstate: 54008

SQL20076N The instance for the database is not enabled for the specified action or operation. Reason code = `reason-code`.

Explanation

An error has been detected at the instance level. The requested operation cannot be completed because a specified functional area was not installed or because a specified functional area was not enabled to the instance.

The following is the list of reason codes and associated functional areas you can enable at the instance level:

The ability to execute distributed request operations against multiple data sources in a single statement.

User response

Enable the instance for the requested action or operation. First, install the specified functional area if it is missing. Then, enable the specified functional area. Enablement steps vary by reason-code:

For a federated server, set the DBM variable <FEDERATED> to YES and then restart the database manager.

sqlcode: -20076

sqlstate: 0A502

SQL20077N Cannot construct structured type objects that have Datalink type attributes.

Explanation

An attempt was made to invoke the constructor of a structured type which has a Datalink and/or a Reference type attribute. This functionality is currently not supported. In Version 6.1 or earlier, this error may also be issued for a structured type object with a Reference type attribute.

The statement cannot be processed.

User response

The error can be corrected by doing one of the following:

Removing the invocation of the constructor of the type from the program.
Removing any Datalink (or Reference) type attributes from the definition of the structured type (this may not be possible if there are any tables that depend on this type).

To determine the data type names for expression-type-id and target-type-id, use the following query:

SELECT TYPEID, TYPESCHEMA, TYPENAME
FROM SYSCAT.DATATYPES
WHERE TYPEID IN INTEGER(
  expression-type-id),
  INTEGER(
    target-type-id
  )
)

sqlcode: -20082

sqlstate: 0D000

SQL20083N The data type of the value returned by `routine-type` `routine-id` does not match the data type specified as RESULT.

Explanation

The method routine-id specifies SELF AS RESULT and therefore requires that the data type of the value returned must be the same as the subject data type used to invoke the method. The RETURN statement in either the SQL method body or in the TO SQL transform function for the type of an external method resulted in the incorrect data type.

The statement cannot be processed.

User response

Change the RETURN statement of the method or transform function to ensure that the data type of the returned value is always the subject type used to invoke the method.

To determine the routine name associated with the routine-id, use the following query:

SELECT FUNCSCHEMA, FUNCNAME,
       SPECIFICNAME
FROM   SYSCAT.FUNCTIONS
WHERE  FUNCID = INTEGER(
         routine-id
       )

sqlcode: -20083

sqlstate: 2200G

SQL20084N The `routine-type` `routine-name` would define an overriding relationship with an existing method.

Explanation

A method MT, with subject type T, is defined to override another method MS, with subject type S, if all of the following conditions are true:

MT and MS have the same unqualified name and the same number of parameters.
T is a proper subtype of S.
The non-subject parameter-types of MT are the same as the corresponding non-subject parameter-types of MS. (Here, “same” applies to the basic type, such as VARCHAR, disregarding length and precision).

A function and a method may not be in an overriding relationship. This means that if the function were a method with its first parameter as subject S, it must not override another method of any supertype of S and it must not be overridden by another method of any subtype of S.

Furthermore, overriding relationships are not supported for:

table and row methods
external methods with PARAMETER STYLE JAVA
system generated mutator and observer methods

User response

Specify the name of a table that is not defined with an implicitly hidden column.

In order to specify a created temporary table or declared temporary table that includes columns that are defined as IMPLICITLY HIDDEN, use the AS (fullselect) option to explicitly specify those columns in the table. As a result, the IMPLICITLY HIDDEN attribute is not copied to the column being defined.

sqlcode: -20092

sqlstate: 560AE

SQL20093N The table `table-name` cannot be converted to or from a materialized query table. Reason code = `reason-code`.

Explanation

The ALTER TABLE statement is being used to change a table from a materialized query table to DEFINITION ONLY or to convert a regular table to a materialized query table. The ALTER TABLE statement failed as indicated by the following reason codes.

1: The table is a typed table or hierarchy table.
2: The table is not a materialized query table and DEFINITION ONLY was specified.
3: The table is a replicated materialized query table and DEFINITION ONLY was specified.
4: The table has at least one trigger defined.
5: The table has at least one check constraint defined.
6: The table has at least one unique constraint or unique index defined.
7: The table has at least one referential constraint defined.
8: The table is referenced in the definition of an existing materialized query table.
9: The table is referenced directly or indirectly (through a view, for example) in the fullselect.
10: The table is already a materialized query table.
11: The number of columns of the existing table does not match the number of columns defined in the select list of the fullselect.
12: The data types of the columns of the existing table do not exactly match the corresponding columns in the select list of the fullselect.
13: The column names of the columns of the existing table do not exactly match the corresponding column names in the select list of the fullselect.
14: The nullability characteristics of the columns of the existing table do not exactly match the nullability characteristics of the corresponding columns in the select list of the fullselect.
15: The conversion cannot be performed if there are any other table alterations in the same ALTER TABLE statement.
16: The table is referenced in the definition of an existing view enabled for query optimization.
17: The table is a protected table.
18: The fullselect refers to a nickname on which caching is not allowed.
19: The table is a system-period temporal table or a history table.
20: The table has a permission or mask defined by a security administrator.
21: An attempt was made to convert the table into a shadow table by specifying the MAINTAINED BY REPLICATION clause, but the table cannot be converted to a shadow table because the table is organized by row.
22: An attempt was made to convert the table into a shadow table by specifying the MAINTAINED BY REPLICATION clause, but the table cannot be converted to a shadow table because the table has a unique constraint or index other than a primary key, or the primary key was defined as NOT ENFORCED.

User response

Respond according to the reason code:

1: The table cannot be converted to a materialized query table. Create a new materialized query table instead.
2: There is no need to convert this table. No action required.
3: A replicated table can only be a materialized query table. Create a new table instead.
4: Drop any triggers and try the ALTER TABLE statement again.
5: Drop any check constraints and try the ALTER TABLE statement again.
6: Drop any unique constraints and unique indexes. Try the ALTER TABLE statement again.
7: Drop any referential constraints and try the ALTER TABLE statement again.
8: Drop the materialized query table that references the table and try the ALTER TABLE statement again.
9: A materialized query table cannot reference itself. Modify the fullselect to remove the direct or indirect reference to the table being altered.
10: The operation is not allowed since the table is already a materialized query table.
11: Modify the fullselect to include the correct number of columns in the select list.
12: Modify the fullselect so that the result column data types exactly match the data types of the corresponding existing columns.
13: Modify the fullselect so that the result column names exactly match the column names of the corresponding existing columns.
14: The table cannot be converted to a materialized query table unless the nullability characteristics can be matched. Create a new materialized table instead.
15: Perform the other table alterations in an ALTER TABLE statement that does not include the SET MATERIALIZED QUERY AS clause.
16: Disable the view enabled for query optimization that references the table and try the ALTER TABLE statement again.
17: Either remove the protection from the table or do not convert it to a materialized query table.
18: Correct the fullselect specified in the ALTER TABLE statement so that it does not reference a nickname on which caching is not allowed.
19: Either correct the table name to specify a table that is not a system-period temporal table or history table, or alter the system-period temporal table with an ALTER TABLE statement that specifies a DROP VERSIONING clause.
20: Either remove the permissions and masks or do not convert it to a materialized query table.
21: To convert the table into a shadow table, first convert the table to be organized by column.
22: To convert the table into a shadow table, remove all unique constraints and indexes other than a primary key from the table, or define the primary key as ENFORCED.

sqlcode: -20093

sqlstate: 428EW

SQL20094N The statement failed because the column `column-name` is a generated column or is defined with the data type DB2SECURITYLABEL and cannot be used in the BEFORE trigger `trigger-name`.

Explanation

The indicated column cannot be named in the column name list of a BEFORE UPDATE trigger or set in a BEFORE trigger because it is one of the following:

A row-begin column
A row-end column
A transaction-start-ID column
A generated expression column
A column defined with the data type DB2SECURITYLABEL

The debugger support had an error situation which will disable debugging, but not affect normal execution. The following is a list of reason codes:

The debugger support is not installed.
There has been a syntax error in IP address of debugger client in the debugger table.
A timeout in the communication between the debugger backend and the debugger client occurred.
A problem occurred accessing the debugger table DB2DBG.ROUTINE_DEBUG.

User response

Verify that you installed the debugger option on the database server machine.
Make sure that the IP address in the debugger table has the correct syntax.
Check that the debugger daemon on the client is started and that the ports on the client and server match.
Verify that you have created the debugger tables with the correct layout.

sqlcode: +20109

sqlstate: 01637

SQL20111N A SAVEPOINT, RELEASE SAVEPOINT, or ROLLBACK TO SAVEPOINT statement cannot be issued in this context. Reason code = `reason-code`.

Explanation

The statement cannot be processed because it violates a restriction as indicated by the following reason code:

Savepoint cannot be issued inside a trigger.
Savepoint cannot be issued inside a global transaction.

The statement cannot be processed.

User response

Remove the SAVEPOINT, RELEASE SAVEPOINT, or ROLLBACK TO SAVEPOINT statement in the trigger or global transaction.

sqlcode: -20111

sqlstate: 3B503

SQL20112N A savepoint cannot be set because a savepoint already exists and nested savepoints are not supported.

Explanation

A savepoint is a named entity that represents the state of data and schemas at a particular point in time within a unit of work. You can use savepoints in several ways. For example, you can roll a database back to a savepoint. You can set a savepoint within a transaction using the SAVEPOINT statement. In some environments, you can also set multiple, or nested, savepoints within the same transaction.

This message can be returned when an error occurred in a SAVEPOINT statement or atomic compound SQL statement for the following reasons:

An attempt has been made to set a nested savepoint, but a savepoint already exists and nested savepoints are not supported in this environment.
An attempt has been made to set a nested savepoint for a federated data source that does not support nested savepoints.

User response

Respond to this error in one of the following ways:

Release existing savepoints before attempting to set new savepoints.
To re-establish an existing savepoint perform the following steps:
1. Free the existing savepoints using the RELEASE SAVEPOINT statement.
2. Recreate the savepoint using the SAVEPOINT statement.
For atomic compound SQL: set the savepoint after the end of the compound statement.

sqlcode: -20112

sqlstate: 3B002

SQL20113N Null cannot be returned from method `method-id` defined with SELF AS RESULT.

Explanation

The method with method identifier method-id is defined with SELF AS RESULT. The invocation of the method used a non-null instance of a structured type so the method cannot return a null instance.

User response

Change the method implementation to ensure that a null value is not returned as the return value for the method. One possibility is to set all the attributes of the returned structured type to null value. To determine the name of the method that failed, use the following query:

SELECT FUNCSCHEMA, FUNCNAME,
       SPECIFICNAME
  FROM SYSCAT.FUNCTIONS
  WHERE FUNCID = method-id

sqlcode: -20113

sqlstate: 22004

Explanation

The window specification (OVER clause) of an OLAP function invocation is not correctly specified. The incorrect specification is indicated by the reason-code.

1: RANGE or ROWS is specified without an ORDER BY in the window specification.
2: RANGE is specified and the window ORDER BY clause includes more than one sort-key-expression.
3: RANGE is specified and the data type of the range value cannot be used in a subtraction expression with the data type of the sort-key-expression in the window ORDER BY clause.
4: UNBOUNDED PRECEDING is specified after CURRENT ROW or UNBOUNDED FOLLOWING is specified before CURRENT ROW.

The statement cannot be processed.

User response

Change the window specification to correct the invalid specification indicated by the reason-code.

1: Add a window ORDER BY clause to each window specification that specifies RANGE or ROWS.
2: Ensure that each window specification that includes RANGE has exactly one sort-key-expression in the window ORDER BY clause.
3: For each window specification that includes RANGE, ensure that the range value can be subtracted from the sort-key-expression in the window ORDER BY clause, which must be a numeric type or a datetime type. For datetime sort-key-expressions, the range value must be the specific datetime duration DECIMAL type with the correct precision and scale.
4: Ensure that any window specification using “BETWEEN” and “CURRENT ROW” has the “UNBOUNDED PRECEDING” before “AND CURRENT ROW” or “UNBOUNDED FOLLOWING” after “CURRENT ROW AND”.

sqlcode: -20117

sqlstate: 428EZ

SQL20118N Structured type `type-name` has more than the maximum number of allowable attributes. The maximum is `max-value`.

SQL20135N The specified SQL archive does not match the target environment. Reason code = `reason-code`.

Explanation

The specified SQL archive does not match the target environment for one of the following reasons:

1: The operating system of the target environment is not the same as the operating system on which the SQL archive was created.
2: The database type and level of the target environment is not the same as the database type and level on which the SQL archive was created.

User response

The routine continues to execute. The routine should not execute any further SQL statements and should return control to the invoking statement as quickly as possible.

The recovery for the initial failure of interrupt will be automatically performed by the database manager once all routines have completed.

sqlcode: -20139

sqlstate: 51038

SQL20140W COMPRESS column attribute ignored because VALUE COMPRESSION is deactivated for the table.

Explanation

One of the following situations occurred.

The COMPRESS SYSTEM DEFAULT specified for the column was ignored because VALUE COMPRESS is deactivated for the table.
DEACTIVATED VALUE COMPRESSION was specified and columns are defined with COMPRESS SYSTEM DEFAULT.

User response

To allow COMPRESS to be specified for the column, ALTER the table and activate VALUE COMPRESSION for the table.

sqlcode: +20140

sqlstate: 01648

SQL20142N Sequence `sequence-name` cannot be used as specified.

Explanation

sequence-name was referenced in a context in which it cannot be used. sequence-name is a sequence generated by the system. It was generated for one of the following purposes:

An identity column.
The distribution key for a random distribution table using the random by generation method.

The buffer pool has been successfully created or altered, but the changes will not take effect immediately. The changes will take effect when the database is restarted.

The changes have been deferred for one of the following reasons:

The DEFERRED option was specified.
If an alter was specified and the operation changed either NUMBLOCKPAGES or BLOCKSIZE, then the request was deferred because all such alters are always deferred.
If an alter was specified, a previous alter of the block based area has not taken effect yet (no database restart has been done), and the new proposed size of the buffer pool is smaller than the current size of the block area of the buffer pool, then the alter must be deferred so that it takes effect when the changes to the block area also come into effect.

User response

To have the changes take effect at next database restart time, no further action is required.

sqlcode: +20149

sqlstate: 01649

SQL20150N The statement failed because the number of block pages for a buffer pool is too large for the size of the buffer pool.

Explanation

You can optimize performance for sequential prefetching by using block-based buffer pools. When creating or altering a buffer pool, you can specify the number of pages that should exist in the block-based area by using the NUMBLOCKPAGES clause of the CREATE BUFFERPOOL statement or the ALTER BUFFERPOOL statement.

The number of pages that are specified to exist in the block-based area must not exceed 98 percent of the total number of pages in the buffer pool. This message is returned when an attempt is made to configure more than 98 percent of the pages in a block-based buffer pool to exist in the block-based area.

User response

Respond to this message in one of the following ways:

Disable block-based I/O for this buffer pool by setting NUMBLOCKPAGES to zero.
Reduce the number of pages specified to exist in the block-based area:
1. Determine the total number of pages for this buffer pool by examining the NPAGES column of the SYSCAT.BUFFERPOOLS catalog view.
2. Specify a value for NUMBLOCKPAGES that is not greater than 98 percent of the total number of pages.

db2inidb <db-name> as mirror
db2inidb <db-name> as snapshot
db2inidb <db-name> as standby

In a DB2 pureScale environment, you can issue this command from any member and need to issue the command only once.

In a partitioned database environment, you must execute the db2inidb command on each database partition. You can run the command concurrently on each database partition.

sqlcode: -20153

sqlstate: 55040

SQL20154N The requested insert or update operation into view `view-name` is not allowed because no target table can be determined for a row. Reason code = `reason-code`.

Explanation

The specified view contains a UNION ALL query. The reason-code indicates that a given row either:

does not satisfy the check constraint of any underlying base table, or
satisfies all the check constraints for more than one underlying base table.

Federated system users: Some other data source specific limitation may be preventing the row from being inserted.

User response

Ensure that the check constraints used by the underlying base tables to partition their rowsets cover the set of rows to be inserted. Also, ensure that for a view defined with UNION ALL for its fullselect that WITH ROW MOVEMENT is also specified if updates are to move rows from one underlying table to another. For example, given the check constraints (T1.c1 in (1,2)) on T1, and (T2.c1 in (2,3)) on T2, and view V1 as a union of T1 and T2,

the row c1 = 4 does not satisfy the check constraints of either underlying base table and
the row c1 = 2 satisfies the check constraints of both underlying base tables.

Federated system users: If the reason is unknown, isolate the problem to the data source failing the request and examine the object definition and the update restrictions for that data source.

sqlcode: -20154

sqlstate: 23513

SQL20155N Creating or activating the event monitor failed because the specified event monitor target tables are invalid. Reason code: `reason-code`.

Explanation

The reason code indicates more specifically what the problem is:

1: At least one column name does not match an event monitor data element identifier.
2: At least one column has a data type that is incompatible with the data type for an event monitor data element identifier.
3: At least one column name matches an event monitor data element identifier, but that element is not allowed in the target table.
4: The row size for the table is too large for the page size of the table space.
5: A mandatory column is missing.
6: The table must not be declared with CCSID UNICODE if the database is not a Unicode database.
7: The table is partitioned.
8: The name of the table specified for storing event monitor output is already in use by another event monitor.
9: An event monitor target table exists for a logical data group that is not supported in the current release.
10: The table is a column-organized table.
11: The table includes an expression-based index.

User response

Check the administration notification log for detailed information and correct the table definition.

sqlcode: -20155

sqlstate: 55049

SQL20156W The event monitor was activated successfully, however some monitoring information may be lost.

Explanation

An event monitor activated successfully, however it was determined that the event monitor may lose some information because of one of the following:

The data type for a column is smaller than the data type required to hold an event monitor element identifier. This data will be truncated.
A target table was found in the SYSCAT.EVENTTABLES, but that table does not exist in the database. All monitor information for the corresponding table will not be recorded.
A target table was found in the SYSCAT.EVENTTABLES, but that table does not reside on all database partitions. Monitor information on some partitions will not be recorded
The buffer size of a statistics event monitor that writes to a file is too small to accommodate the largest record.

User response

Check the administration notification log for detailed information.If this is a statistics event monitor, check the buffer size; if it is too small, recreate the event monitor with a buffer size of at least 4. For other types of event monitors, recreate the event monitor to create all target tables if necessary.

sqlcode: +20156

sqlstate: 01651

SQL20157N User with authorization ID `authorization-ID` failed to attach to a quiesced instance, or connect to a quiesced database or a database in a quiesced instance which is in the following quiesce mode: `quiesce-mode`

Explanation

The specified authorization ID does not have the authority to attach to a quiesced instance or connect to a database in a quiesced instance when the instance is in QUIESCE RESTRICTED ACCESS mode. To be able to attach to the instance or connect to the database, the authorization ID must satisfy one of the following listed criteria:

Hold SYSADM, SYSCTRL, or SYSMAINT authority
Be the user that was specified using the USER option of the QUIESCE INSTANCE or START DATABASE MANAGER commands (or db2InstanceQuiesce or db2InstanceStart APIs)
Be a member of the group that was specified using the GROUP option of the QUIESCE INSTANCE or START DATABASE MANAGER commands (or db2InstanceQuiesce or db2InstanceStart APIs)

In the event the quiesced database is in QUIESCE DATABASE mode, then the authorization ID must satisfy one of the previously listed criteria or hold either the DBADM authority or QUIESCE_CONNECT privilege before the user can successfully connect to a quiesced database.

In the event the database in a quiesced instance is in QUIESCE INSTANCE mode, then the authorization ID must satisfy one of the above listed criteria or hold the DBADM authority before the user can successfully connect to a database in a quiesced instance.

In the event the instance is in QUIESCE INSTANCE mode, then the authorization ID must satisfy one of the above listed criteria before the user can successfully attach to the quiesced instance.

User response

You can take one of the following actions to successfully connect to a quiesced database or a database of a quiesced instance, or attach to a quiesced instance:

To connect to the database, wait for the database to be unquiesced.
To attach to the instance or connect to a database in the instance, wait for the instance to be unquiesced.
Retry connecting to the database or attaching to the instance using an authorization ID that has sufficient authority.

sqlcode: -20157

sqlstate: 08004

SQL20158N DB2 Data Links Manager is not supported in this DB2 copy version.

Explanation

The DB2 Data Links Manager is not supported in this DB2 copy version and an attempt was made to perform one of the following actions:

Restore a database backup image for a database enabled for Data Links.
Upgrade a database which uses the DATALINK data type.
Create a database object which uses the DATALINK data type. Database objects that use a DATALINK data type include tables, views, functions, methods, distinct types and structured data types.

User response

Perform one of the following actions:

RESTORE the database backup image in a DB2 copy of a previous release that supports Data Links, disable the use of Data Links, back up the database, and then re-issue the RESTORE DATABASE command using the DB2 copy version to which you want to upgrade the database.
Using a DB2 copy of a previous release that supports Data Links, disable the use of Data Links and then re-issue the UPGRADE DATABASE command.
Remove all references to the DATALINK data type and then re-issue the statement to create the database object.

The statement cannot be processed.

User response

Change the statement so that the SQL data change statement within a FROM clause is used in a supported context and is the only table-reference in the FROM clause.

sqlcode: -20165

sqlstate: 428FL

SQL20166N An SQL data change statement within a SELECT specified a view `view-name` which is not a symmetric view or could not have been defined as a symmetric view.

Explanation

The target view of the SQL data change statement within a SELECT statement must be defined WITH CASCADED CHECK OPTION or the fullselect in the view definition (or the fullselect of a view definition of a referenced view) must be able to be defined using WITH CASCADED CHECK OPTION.

A symmetric view is a view defined implicitly or explicitly with the WITH CASCADED CHECK OPTION.

The statement cannot be processed.

User response

Do not use the SQL data change statement within a SELECT with the specified view.

sqlcode: -20166

sqlstate: 428FM

SQL20167N The shared memory set is not large enough to process the request.

Explanation

There is insufficient memory in a shared memory set for one of the following resource requests:

FCM connection entries
FCM message anchors
FCM request blocks

1: The amount of space being removed is greater than the amount of space above the high-water mark.
2: The amount of space being removed would result in the tablespace having less then the required 5 extents.

User response

1: Use the snapshot monitor to find the number of useable pages in the tablespace and the tablespace high-water mark. The difference between these values is the maximum number of pages that can be removed.
2: Use the snapshot monitor to find the number of useable pages in the tablespace and the tablespace extentsize. The number of useable extents in the tablespace is equal to the useable pages divided by the extentsize. There must be at least 5 extents worth of useable pages in the tablespace.

sqlcode: -20170

sqlstate: 57059

SQL20173W The event monitor was created successfully but at least one event monitor target table already exists.

Explanation

While an event monitor was being created, the database manager tried to create one or more target tables but determined that tables with those names already exist. The event monitor was successfully created and target tables were not created to replace tables of the same name that already exist. When the event monitor is activated, it will attempt to use these tables. Event monitor activation may fail if activation processing determines these tables are not suitable.

User response

Check the administration notification log for detailed information. If necessary, drop all target tables and recreate the event monitor to create all target tables.

sqlcode: +20173

sqlstate: 01655

SQL20178N View `view-name` already has an INSTEAD OF `trigger` trigger defined.

Explanation

The view view-name already has an INSTEAD OF trigger defined for the indicated operation (UPDATE, DELETE, INSERT). Only one INSTEAD OF trigger can be defined for each operation on the view.

The statement cannot be processed. The INSTEAD OF trigger was not created.

User response

If a new INSTEAD OF trigger is required, drop the existing trigger and then create the new trigger.

sqlcode: -20178

sqlstate: 428FP

SQL20179N An INSTEAD OF trigger cannot be created because of how the view `view-name` is defined.

Explanation

An INSTEAD OF trigger cannot be defined on:

a view defined using WITH CHECK OPTION
a view on which such a view has been defined either directly or indirectly
a view on which such a view has been defined either directly or indirectly
a view that references an unfenced nickname and the Database Partitioning Feature is enabled

An INSTEAD OF UPDATE trigger cannot be defined on:

A view nested in a view defined with the WITH ROW MOVEMENT clause
The view may be the target view of the INSTEAD OF trigger or it may be a view that depends directly or indirectly on the target view of the trigger.
The statement cannot be processed. The INSTEAD OF trigger was not created.

User response

If the view is defined using WITH CHECK OPTION, remove the WITH CHECK OPTION clause.
If the view is nested in a view defined with the WITH ROW MOVEMENT clause, remove the WITH ROW MOVEMENT clause.
If the view references an unfenced nickname, specify a different view.

sqlcode: -20179

sqlstate: 428FQ

SQL20180N column `column-name` in table `table-name` cannot be altered as specified

Explanation

Column column-name cannot be altered for one of the following reasons:

ADD COLUMN cannot be specified on ALTER TABLE if the table is defined as a history table.
ALTER COLUMN cannot be specified on ALTER TABLE if the table is defined as a history table.
ALTER COLUMN with SET GENERATED cannot be specified on ALTER TABLE for a column of a history table.
ALTER COLUMN with SET GENERATED AS cannot be specified on ALTER TABLE for a column that already is a generated column or has a defined default.
DROP COLUMN cannot be specified on ALTER TABLE if the table is defined as a history table.
DROP COLUMN with DROP NOT NULL cannot be specified on ALTER TABLE for a column defined as part of a BUSINESS_TIME period.
DROP COLUMN with DROP GENERATED cannot be specified on ALTER TABLE for a column defined as row-begin or row-end on a system-period temporal table.
ALTER COLUMN with DROP GENERATED, SET NOT HIDDEN or SET IMPLICITLY HIDDEN cannot be specified on ALTER TABLE for a random distribution key column of a random distribution table using random by generation method

The statement cannot be processed.

User response

Change the name of the column to a column that can be altered or dropped, and recreate the table with the necessary attributes.

sqlcode: -20180

sqlstate: 428FR

SQL20183N The table `table-name` is not compatible with the specified table partitioning operation.

Explanation

The partitioning clause for table table-name is not valid because:

ADD PARTITION BY was specified on an ALTER TABLE statement CREATE INDEX statement or CREATE TABLE statement, but the table is already a partitioned table.

ADD PARTITION, ALTER PARTITION, DROP PARTITION, or DROP DISTRIBUTION was specified on an ALTER TABLE statement, but the table is not a partitioned table, the table is a materialized query table, or a materialized query table is defined on this table.

ADD PARTITION for HASH partitions was specified on an ALTER TABLE statement, but the table is not partitioned using hashing.

DROP PARTITION of a data partition was specified on the ALTER TABLE statement, but the PRESERVE ROWS clause was used. The PRESERVE ROWS clause is only valid for partitioned tables that have hash partitioning.

User response

Change the statement to one that is valid or specify a different table and reissue the statement.

sqlcode: -20183

sqlstate: 428FT

SQL20188N The primary key or a unique key identified by `name` is a subset of the columns in the ORGANIZE BY clause.

Explanation

All the columns in a primary or unique key are included in the table's ORGANIZE BY clause. This is not allowed, since each block of pages in the table would contain just one row, wasting all the rest of the space in the block.

name is the constraint name, if specified, of the primary key or unique constraint. If a constraint name was not specified, name is the first column name specified in the primary key or unique constraint clause followed by three periods. If a unique index is being created, name is the name of the unique index.

The statement cannot be processed.

User response

Modify the primary or unique key definition, the unique index definition or the ORGANIZE BY clause, if possible, so that the key is not a subset of the dimensions.

sqlcode: -20188

sqlstate: 429BE

SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory.

Explanation

The CREATE or ALTER BUFFERPOOL statement was issued and completed successfully, however due to insufficient memory the create/alter was done DEFERRED. The changes will take effect on the next database startup.

User response

If you do not want to wait until the next startup to activate or resize the buffer pool, you can free memory resources and try again (with the same or different size). Memory resources that can be reduced include other buffer pools, the database heap, the catalog cache, the package cache, and the utility heap. These can be reduced using the ALTER/DROP BUFFERPOOL or UPDATE DATABASE CONFIGURATION command depending on the resource. In the future, to reserve extra memory for the dynamic allocation of buffer pool memory, you can increase the DATABASE_MEMORY database configuration parameter.

If you do not try again:

If it is an ALTER BUFFERPOOL that failed, you will continue to run with the current runtime size of the buffer pool. You can use the database monitor to see the current runtime size of the buffer pool.
If it is a CREATE BUFFERPOOL that failed, any table spaces that are created in the buffer pool will temporarily (until next startup) be put in a hidden buffer pool with the matching pagesize. Since the hidden buffer pools are small, this can result in lower than desired performance.

To try again:

For ALTER BUFFERPOOL, resubmit the command.
For CREATE BUFFERPOOL drop the buffer pool and then resubmit the command.

sqlcode: +20189

sqlstate: 01657

SQL20190N Federated insert, update, or delete operation cannot be compiled because of potential data inconsistency.

Explanation

When the data source does not provide application savepoint support and the server option 'iud_app_svpt_enforce' is set to 'Y', the federated insert, update, or delete operation is blocked during precompile. This will prevent potential data inconsistency, which may occur at runtime execution if an error is encountered in the middle of federated insert, update, or delete processing.

User response

Possible solutions include:

Change server option 'iud_app_svpt_enforce' to 'N'.
Apply the insert, update, or delete directly on the data source.

sqlcode: -20190

sqlstate: 0A503

SQL20191N For an INOUT parameter in a dynamically prepared CALL statement, the same host variable must be used in both the USING and INTO clauses.

Explanation

In a CALL statement, an INOUT parameter must correspond to a single host variable. When executing a dynamically prepared CALL statement, it is necessary to specify the same host variable in both the USING and INTO clauses of the EXECUTE statement.

User response

When specifying a host variable that corresponds to a parameter marker for an INOUT parameter, use the same host variable in both the USING and INTO clauses of the EXECUTE statement. When using SQLDAs to specify a host variable that corresponds to a parameter marker for an INOUT parameter, then the SQLDATA pointers of the corresponding SQLVARs must point to the same host variable.

sqlcode: -20191

sqlstate: 560BB

SQL20192N The specified modes are only supported in a partitioned database environment.

Explanation

Either RECOMMEND PARTITIONINGS or EVALUATE PARTITIONINGS was not invoked in a partitioned database environment. These two modes are only supported in a partitioned database environment.

User response

Invoke RECOMMEND PARTITIONINGS or EVALUATE PARTITIONINGS in a partitioned database environment.

sqlcode: -20192

sqlstate: 56038

SQL20193N An error has occurred when accessing the file `file-name`. Reason code: `reason-code`.

Explanation

An attempt was made to access the file file-name on the server but an error occurred. For reason codes 4 to 10, file-name may be in the directory specified by the DB2_UTIL_MSGPATH registry variable, or possibly in the directory which is the tmp directory of the instance. The specified error encountered is indicated by the reason code as follows:

1: File format error.
2: Communication error.
3: Memory allocation error.
4: The directory path indicated by the DB2_UTIL_MSGPATH registry variable does not exist.
5: The file does not exist.
6: The fenced user ID cannot create the file (no execute access to the directory).
7: The fenced user ID cannot read the file (no read access).
8: The fenced user ID cannot write to the file (no write access).
9: Out of disk space (disk full).
10: The file cannot be removed.

User response

Use the reason code information to determine how to make the file accessible and resubmit the statement.

sqlcode: -20193

sqlstate: 560BC

SQL20194N Buffer pool `bufferpool-name` does not exist on database partition `dbpartitionnum`.

Explanation

The ALTER BUFFERPOOL statement is specifying a buffer pool, bufferpool-name, that does not exist on the database partition dbpartitionnum.

User response

Using the ALTER DATABASE PARTITION GROUP statement, add the database partition dbpartitionnum to a database partition group that has the buffer pool bufferpool-name already defined for it. If the buffer pool is not associated with any specific database partition group, then add the database partition to any database partition group or create a new database partition group for this database partition. Issue the ALTER BUFFERPOOL statement again.

sqlcode: -20194

sqlstate: 53040

SQL20195N An error was encountered while processing line number `line-number` in the path rename configuration file `config-file`. Reason code = `reason-code`.

Explanation

The path rename configuration file config-file is being used to rename containers during the current crash or rollforward recovery process. However, while processing the statements in this file, an error was encountered at line line-number that is preventing the recovery process from proceeding. An explanation of the error is indicated by the following reason code:

Invalid syntax.
The length of the container path exceeds the maximum allowed length for a container path.
The container path specified has already been listed on a previous line.
The length of the database path exceeds the maximum allowed length for a database path.
The database path specified has already been listed on a previous line.
The database path must be an absolute path.
The database path is specified incorrectly.
The wildcard character ("*") is being used incorrectly.

User response

Make the corresponding change to the configuration file as described in the following instructions, and reissue the command:

Correct the syntax error.
Specify a shorter container path.
Remove the duplicate container path from the file.
Specify a shorter database path.
Remove the duplicate database path from the file.
Use an absolute database path instead of a relative one.
Specify the database path in the form of a drive letter followed by a colon (for example C:).
If using the wildcard character, it must be the last character in both the old and new paths.

sqlcode: -20195

sqlstate: 08504

SQL20196N The one or more built-in types which are returned from the FROM SQL function or method does not match the corresponding built-in types which are parameters of the TO SQL function or method.

Explanation

The built-in types which are returned by a FROM SQL transform function or method have to match the types in the parameter list of the corresponding TO SQL transform function or method.

User response

Choose a different FROM SQL transform function or method or TO SQL transform function or method or alter either the FROM SQL transform function or method or TO SQL transform function or method to make sure that each built-in type returned from the FROM SQL function or method matches the corresponding built-in type that is a parameter of the TO SQL transform function or method.

sqlcode: -20196

sqlstate: 428FU

SQL20197N Cannot define `method-name` as an overriding method. Reason code `reason-code`.

Explanation

An attempt was made to define method-name as an overriding method. An overriding relationship between this method and an original method cannot be created because of one of the following reasons, as specified by reason-code:

1

Cannot find an original method with the same name.

2

The original method and the overriding method do not have the same number of parameters.

3

The data type of the parameter of the overriding method does not match the data type of the corresponding parameter of the original method.

4

The parameter name of the parameter of the overriding method does not match the parameter name of the corresponding parameter of the original method.

5

The locator indication of the parameter of the overriding method does not match the locator indication of the corresponding parameter of the original method.

6

The FOR BIT DATA indication of the parameter of the overriding method does not match the FOR BIT DATA indication of the corresponding parameter of the original method.

7

The RETURNS clause of the overriding method contains as AS LOCATOR clause, a CAST FROM clause or a FOR BIT DATA clause.

8

One of the following inherited method attributes is specified: SELF AS RESULT, SQL routine characteristics, or external routine characteristics.

9

Return type of the overriding method does not match the return type of the original method.

10

The return type of the overriding method is a structured type that is not a subtype of either:

the return type of the original method or
every return type for all methods that already override the original method.

User response

Modify the method definition based on the reason specified by the reason-code as follows:

1: Use a method name that identifies an existing method for the supertype of the structured type.
2: Define the same number of parameters for the overriding method as exists for the original method.
3: Change the data type to match the data type of the corresponding parameter of the original method.
4: Change the parameter name to match the name of the corresponding parameter of the original method.
5: Change the locator indication to match the locator indication of the corresponding parameter of the original method.
6: Change the FOR BIT DATA indication to match the FOR BIT DATA indication of the corresponding parameter of the original method.
7: Remove the AS LOCATOR clause, CAST FROM clause and FOR BIT DATA clause for the overriding method.
8: Remove the SELF AS RESULT method attribute and any SQL routine characteristic or external routine characteristic.
9: Change the return type to match the return type of the original method.
10: Change the return type so that it is a subtype of the original method and all methods that already override the original method.

sqlcode: -20197

sqlstate: 428FV

SQL20198N Method `method-name` calls itself recursively.

Explanation

A method attempted to call itself recursively. The recursion might be direct or indirect in nature.

User response

Modify the method definition.

sqlcode: -20198

sqlstate: 55054

SQL20199N The key transform table function used by the index extension of index `index-id` of table `table-id` in `tbspace-id` generated duplicate rows.

Explanation

The statement cannot be processed.

User response

The code for the key transform table function used by the index extension of index index-id must be modified to avoid the creation of duplicate rows.

To determine the index name, use the following query:

SELECT IID, INDSCHEMA, INDNAME
  FROM SYSCAT.INDEXES AS I,
       SYSCAT.TABLES AS T
  WHERE IID = <index-id>
      AND TABLEID = <table-id>
      AND TBSPACEID = <tbspace-id>
      AND T.TBASCHEMA = I.TABSCHEMA
      AND T.TABNAME = I.TABNAME

sqlcode: -20207

sqlstate: 46501

SQL20208N The table `table-name` was not created. Reason code: `reason-code`.

Explanation

The table cannot be created because it violates a restriction as indicated by the following reason code:

1: The table that is used to define a staging table is not a materialized query table that was defined with the REFRESH DEFERRED option.
2: The materialized query table that is used to define a staging table already has a staging table associated with it.
3: A materialized query table that references nicknames cannot be created if the CREATE TABLE statement is issued from a non-catalog database partition.
4: A materialized query table that references a protected table, a view that depends on a protected table, or a nickname on which caching is not allowed cannot be created.
5: A security policy cannot be added to a materialized query table or to a staging table.
6: A materialized query table cannot have more than one DB2SECURITYLABEL column, and that column should not be wrapped in any function.
7: A materialized query table that references protected tables with more than one DB2SECURITYLABEL column cannot be created.
8: If a referred base table has a DB2SECURITYLABEL column, the column must appear in the fullselect of the query.
9: When a table, tableA, is altered to become a materialized query table, if a column in the fullselect of the query is of type DB2SECURITYLABEL, the corresponding column of tableA must also be of type DB2SECURITYLABEL.
10: A staging table cannot be created for a shadow table.

User response

Respond according to the reason code:

1: Specify a materialized query table that was created with the REFRESH DEFERRED option to define the staging table.
2: Specify a materialized query table that is not associated with a staging table.
3: Issue the CREATE TABLE statement from the catalog database partition.
4: Correct the fullselect that is specified in the CREATE TABLE statement so that it does not violate any of the restrictions that are outlined in the explanation under reason code 4.
5: Remove the SECURITY POLICY clause from the CREATE TABLE statement
6: Reference a protected table that has at most one DB2SECURITYLABEL column and that column should not be wrapped in a function.
7: The materialized query table should have at most one DB2SECURITYLABEL column.
8: Select the same DB2SECURITYLABEL column for the materialized query table as referred to in the base table.
9: To alter a table, tableA, to become a materialized query table, define the fullselect so that any DB2SECURITYLABEL column in the fullselect corresponds to a column in tableA that is of type DB2SECURITYLABEL.
10: No action is required. A staging table cannot be created on a shadow table.

sqlcode: -20208

sqlstate: 428FG

SQL20209N The `option-type` option is not valid for table `table-name` with reason code `reason-code`.

Explanation

The specified option is not valid as indicated by the following reason codes:

1: The READ ACCESS option cannot be specified on a table that is in the Set Integrity Pending No Access state.
2: The FULL ACCESS option is not valid if the table still has integrity types that are not checked.
3: The FULL ACCESS option is only valid on a table in the Normal No Data Movement state or on a detached table.
4: The FULL ACCESS option cannot be specified with the IMMEDIATE UNCHECKED option if the table is in the Normal No Data Movement state.
5: The PRUNE option is not valid on a table that is not a staging table.
6: The PRUNE and INCREMENTAL options cannot be specified simultaneously.
7: The ALL option cannot be specified with the IMMEDIATE UNCHECKED option if the table needs integrity checking due to an operation that involved data partitioning.
8: The ALL or GENERATED COLUMN option cannot be specified with the IMMEDIATE UNCHECKED option if the table's database partitioning key, table-partitioning key, multidimensional clustering key, or range-clustering key references a generated column whose expression has been altered through an ALTER TABLE statement.
9: Neither the ALLOW READ ACCESS option nor the ALLOW WRITE ACCESS option can be specified on a table that is to have its identity column values generated for all rows nor on a materialized query table that is to be fully refreshed nor on a table that have been loaded and is to have its generated column or identity column values computed.
10: The ALLOW QUERY OPTIMIZATION USING REFRESH DEFERRED TABLES option can only be specified on a REFRESH DEFERRED materialized query table.
11: The option is not valid on a text-maintained summary table.
12: The option is not valid on a text-maintained staging table.
13: The ALL option cannot be specified with the IMMEDIATE UNCHECKED option when the SET INTEGRITY statement has more than one target table and any one of the target tables is a partitioned table that has both an attached data partition and a nonpartitioned user index.
14: The GENERATE IDENTITY option cannot be specified in a SET INTEGRITY statement if the specified table is a system-period temporal table.
15: The FORCE GENERATED option cannot be specified if the table is a system-period temporal table.
16: The FOR EXCEPTION clause cannot be specified if the table is a system-period temporal table.

User response

1: Do not specify the READ ACCESS option.
2: Use the FULL ACCESS option with the IMMEDIATE CHECKED option, or specify ALL as the integrity option with the IMMEDIATE UNCHECKED option.
3: Use the FULL ACCESS option with the IMMEDIATE CHECKED option.
4: Use the FULL ACCESS option without the IMMEDIATE UNCHECKED option.
5: Remove the table that is not a staging table from the statement.
6: Specify only one of PRUNE or INCREMENTAL.
7: Instead of specifying the ALL option, specify explicitly those integrity-options that are desired to be unchecked with the IMMEDIATE UNCHECKED option. Then issue another SET INTEGRITY statement with the IMMEDIATE CHECKED option to check the integrity of data partitioning for the table.
8: Omit the ALL or GENERATED COLUMN option from those integrity-options that are desired to be unchecked with the IMMEDIATE UNCHECKED option. Then issue another SET INTEGRITY statement with the IMMEDIATE CHECKED and FORCE GENERATED options to recompute the generated column values based on the new expression. The FORCE GENERATED option can be omitted if the data in the table are guaranteed to satisfy the new generated column expressions.
9: Specify the ALLOW NO ACCESS option on a table.
10: Remove the ALLOW QUERY OPTIMIZATION USING REFRESH DEFERRED TABLES option.
11: Remove the text-maintained summary table from the statement.
12: Remove the text-maintained staging table from the statement or specify the IMMEDIATE CHECKED option.
13: Reissue the SET INTEGRITY statement with a supported combination of options and target table. For example, issue the SET INTEGRITY statement with the ALL and IMMEDIATE UNCHECKED options while specifying only the affected partitioned table as a target; or issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option and multiple target tables instead.
14: Omit the GENERATE IDENTITY option when the NOT INCREMENTAL option is specified for a system-period temporal table.
15: Remove the FORCE GENERATED option if the table is a system-period temporal table.
16: Remove the FOR EXCEPTION clause if the table is system-period temporal table.

sqlcode: -20209

sqlstate: 428FH

SQL20211N The specification ORDER BY, OFFSET, or FETCH clause is invalid.

Explanation

An ORDER BY, OFFSET, or FETCH clause is not allowed in:

the outer fullselect of a view
the outer fullselect in the RETURN statement of an SQL Table function
a materialized query table definition
a subselect which is not enclosed in parentheses

User response

In case of:

subselect

Enclose the subselect including the ORDER BY, OFFSET, or FETCH clause in parentheses.

OFFSET or FETCH clause

Use the ROW_NUMBER() OVER() clause in conjunction with a predicate in the where clause. The following example is equivalent to SELECT NAME FROM emp OFFSET 3 ROWS FETCH NEXT 6 ROWS ONLY:

SELECT name FROM 
  (SELECT
     ROW_NUMBER() OVER() AS m, name
     FROM emp
  ) AS e
WHERE m > 3 AND m < 10

Note that LIMIT is an alternative syntax for the FETCH clause.

ORDER BY clause

Use ORDER BY in the query using the view, the materialized query table, or the SQL table function instead.

sqlcode: -20211

sqlstate: 428FJ

SQL20212N User defined routine `function-name` encountered an exception attempting to load Java class `class-name` from JAR `JAR-name`. Original exception `underlying-exception`.

Explanation

A ClassNotFoundException has occurred. The ClassNotFoundException may reference an original Java exception which further describes why the class wasn't found. For example, the underlying exception might be an I/O error that occurred trying to read a JAR file, or an SQL error occurred reading the JAR from the data dictionary. As much of the following tokens that will fit within the SQLCA limits will be returned, as appropriate.

The function-name identifies the specific name of the external Java function or procedure whose execution encountered the ClassNotFoundException.

The class-name identifies the Java class whose definition could not be found.

The JAR-name optionally identifies the installed JAR expected to contain the identified class. '(none)' will be present unless the external Java function or procedure is defined to be in an installed JAR.

The underlying-exception optionally contains the toString() of the underlying exception that resulted in this ClassNotFoundException, if any. '(none)' will be present if there is no underlying exception.

User response

Execute an ALTER PROCEDURE to specify where the class is to be found, make sure the class is in the specified JAR or the system classpath, or correction the condition reported by the underlying-exception.

sqlcode: -20212

sqlstate: 46103

SQL20223N The encryption or decryption operation failed because the encryption facility is not installed or is not functioning correctly.

Explanation

You can encrypt data in database tables by using built-in encryption functions. You can encrypt data in transit between DB2 clients and database servers by using encryption authentication.

This message is returned when an attempt is made to encrypt or decrypt data either in storage or in transit, but the encryption facility is not available to service the encryption or decryption request.

User response

To use the encryption facility, perform the following troubleshooting steps:

Verify that the the encryption facility is installed by issuing the db2ls command.
If the encryption facility is not installed, install it before using encryption or decryption operations.
If the encryption facility is installed but it is not working correctly, uninstall and then reinstall the encryption facility.

User response

Correct the specification of the column of type DB2SECURITYLABEL and resubmit the statement.

sqlcode: -20240

sqlstate: 42963

SQL20241N Writing a history file entry for dropped table `table-name` failed.

Explanation

Dropped table recovery is enabled on the table space in which the dropped table table-name resides. When a table space has dropped table recovery enabled, a history file entry is created as part of a table drop. This history file is contains data which is useful during the recovery of a dropped table. The writing of the new history file entry for the dropped table failed. As a result, the table drop could not be completed.

A possible cause for such a failure is a full file system where the history file is located.

User response

Ensure that the history file can be written to by the database manager. If you are not concerned about dropped table recovery, disable this feature and reissue the drop table statement.

sqlcode: -20241

sqlstate: 560C2

SQL20242N The sample size specified in the TABLESAMPLE clause is not valid.

SQL20000 - SQL20249

SQL20005N The statement failed because the maximum number of internal identifiers has been exceeded for the object type. Internal identifier limit: of limit. Object type: object-type.

Explanation

User response

SQL20010N Mutation method method-ID is not allowed when the instance of the structured type is NULL.

Explanation

User response

SQL20011N Transform group group-name is already defined for a subtype or supertype of data type type-name.

Explanation

User response

SQL20012N Type type-name does not have any associated transform groups to drop.

Explanation

User response

SQL20013N The object super-object-name is not valid as a supertype, supertable, or superview of object sub-object-name.

Explanation

User response

SQL20014N The transform group group-name transform-type transform function for type type-name is not valid. Reason code = reason-code.

Explanation

User response

SQL20015N A transform group group-name is not defined for data type type-name.

Explanation

User response

SQL20016N The value of the inline length associated with type or column type-or-column-name is too small.

Explanation

User response

SQL20017N Addition of this subtype exceeds the maximum number of levels of a type hierarchy.

Explanation

User response

SQL20018N Row function function-name must return at most one row.

Explanation

User response

SQL20019N The result type returned from the function body cannot be assigned to the data type defined in the RETURNS clause.

Explanation

User response

SQL20020N Operation operation-type is not valid for typed tables.

Explanation

User response

SQL20021N Inherited column or attribute name cannot be changed or dropped.

Explanation

User response

SQL20022N The scope for the reference column column-name is already defined.

Explanation

User response

SQL20023N Parameter parm-number of the external or sourced function has a scope defined.

Explanation

User response

SQL20024N The scope table or view target-name is not defined with structured type type-name.

Explanation

User response

SQL20025N SCOPE is not specified in the RETURNS clause of an external function or is specified in the RETURNS clause of a sourced function.

Explanation

User response

SQL20026N The type type-name is not a structured type or is not an instantiable structured type.

Explanation

User response

SQL20027N The subtable or subview sub-object-name was not created because subtable or subview object-name with type type-name already exists.

Explanation

User response

SQL20028N The table or view table-name cannot have a different schema name from the other tables or views in the same hierarchy.

Explanation

User response

SQL20029N operation cannot be applied to a subtable or subview.

Explanation

User response

SQL20030N Attributes of a structured type type-name cannot be added or dropped when a typed table, typed view, or index extension object-name is dependent on the type.

Explanation

User response

SQL20031N Object may not be defined on a subtable.

Explanation

User response

SQL20032N Index on the specified columns cannot be defined on subtable table-name.

Explanation

User response

SQL20033N An expression including partial-expression does not include a valid scoped reference.

Explanation

User response

SQL20034N The data type list-type-name is not included in a structured data type hierarchy that includes the data type left-type-name of the left operand of the TYPE predicate.

Explanation

User response

SQL20035N Invalid left operand of a dereference operator. Path expression starts with expression-string.

SQL20005N The statement failed because the maximum number of internal identifiers has been exceeded for the object type. Internal identifier limit: of `limit`. Object type: `object-type`.

SQL20010N Mutation method `method-ID` is not allowed when the instance of the structured type is NULL.

SQL20011N Transform group `group-name` is already defined for a subtype or supertype of data type `type-name`.

SQL20012N Type `type-name` does not have any associated transform groups to drop.

SQL20013N The object `super-object-name` is not valid as a supertype, supertable, or superview of object `sub-object-name`.

SQL20014N The transform group `group-name` `transform-type` transform function for type `type-name` is not valid. Reason code = `reason-code`.

SQL20015N A transform group `group-name` is not defined for data type `type-name`.

SQL20016N The value of the inline length associated with type or column `type-or-column-name` is too small.

SQL20018N Row function `function-name` must return at most one row.

SQL20020N Operation `operation-type` is not valid for typed tables.

SQL20021N Inherited column or attribute `name` cannot be changed or dropped.

SQL20022N The scope for the reference column `column-name` is already defined.

SQL20023N Parameter `parm-number` of the external or sourced function has a scope defined.

SQL20024N The scope table or view `target-name` is not defined with structured type `type-name`.

SQL20026N The type `type-name` is not a structured type or is not an instantiable structured type.

SQL20027N The subtable or subview `sub-object-name` was not created because subtable or subview `object-name` with type `type-name` already exists.

SQL20028N The table or view `table-name` cannot have a different schema name from the other tables or views in the same hierarchy.

SQL20029N `operation` cannot be applied to a subtable or subview.

SQL20030N Attributes of a structured type `type-name` cannot be added or dropped when a typed table, typed view, or index extension `object-name` is dependent on the type.

SQL20031N `Object` may not be defined on a subtable.

SQL20032N Index on the specified columns cannot be defined on subtable `table-name`.

SQL20033N An expression including `partial-expression` does not include a valid scoped reference.

SQL20034N The data type `list-type-name` is not included in a structured data type hierarchy that includes the data type `left-type-name` of the left operand of the TYPE predicate.

SQL20035N Invalid left operand of a dereference operator. Path expression starts with `expression-string`.

SQL20036N The object identifier column `column-name` cannot be referenced using the dereference operator.

SQL20037N Object identifier column is required to create the root table or root view `object-name` of a typed table or view hierarchy.

SQL20038N The statement could not be processed because the statement contains the following incompatible clauses or elements: `keywords1` and `keywords2`.

SQL20039N The definition of index `index-name` does not match the definition of index extension `index-ext-name`.

SQL20040N Number or type of the result of the range-producing table function `range-function-name`, is inconsistent with that of key transformation table function `transform-function-name` for index extension `index-ext-name`.

SQL20041N Number or the type of target key parameters does not match with the number or type of key transform function `function-name` for index extension `index-ext-name`.

SQL20042N The maximum allowable `parm-type` parameters is exceeded in index extension `index-ext-name`. The maximum is `max-value`.

SQL20043N Argument for routine `routine-name` is not valid. Reason code = `reason-code`.

SQL20044N The routine `routine-name` or the CASE expression is not valid in a CREATE INDEX EXTENSION or CREATE FUNCTION statement. Reason code = `reason-code`.

SQL20045N The data type of instance parameter `parameter-name` is invalid in index extension `index-ext-name`.

SQL20046N SELECTIVITY clause following `predicate-string` can only be specified for a valid user-defined predicate.

SQL20047N The search method `method-name` is not found in the index extension `index-ext-name`.

SQL20048N The search argument of method `method-name` does not match the one in the corresponding search method in the index extension `index-ext-name`.

SQL20050N The search target or search argument `parameter-name` does not match a name in the function being created.

SQL20051N The argument `parameter-name` cannot appear as both a search target and a search argument in the same exploitation rule.

SQL20052N Column `column-name` is an object identifier column which cannot be updated.

SQL20053N Fullselect in typed view `view-name` is not valid. Reason code = `reason-code`.

SQL20054N The operation was not performed because the table is in an invalid state for the operation. Table name: `table-name`. Reason code: `reason-code`.

SQL20055N A result column data type in the select list is not compatible with the defined data type for column `column-name`.

SQL20056N Processing on DB2 Data Links Manager `name` encountered an error. Reason code = `reason-code`.

SQL20057N Column `column-name` in subview `view-name` cannot be defined as read only when the corresponding column is updatable in the superview.

SQL20058N The statement failed because the fullselect specified for the materialized query table `table-name` violates a restriction. Reason code = `reason-code`.