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:
- Extract the DDL statements that would reproduce your workload management objects by using the db2look utility, specifying the -wlm option.
- Drop all of the workload management objects of the specified type from the database.
- 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.
SELECT FUNCSHEMA, FUNCNAME,
SPECIFICNAME
FROM SYSCAT.FUNCTIONS
WHERE
FUNCID = INTEGER(
method-ID
)
sqlcode: -20010
sqlstate: 2202D
SQL20011N Transform group group-name is already defined for a subtype or supertype of data type type-name.
Explanation
A transform group called group-name already exists for a type in the same hierarchy as type-name. It may be defined for a supertype or subtype of type-name. A transform group name can only be used once within a structured type hierarchy.
The statement cannot be processed.
User response
Change the name of the transform group.
sqlcode: -20011
sqlstate: 42739
SQL20012N Type type-name does not have any associated transform groups to drop.
Explanation
There are no transforms defined for type-name. There is nothing to drop.
The statement did not drop any transform groups.
User response
Ensure the name of the type (including any required qualifiers) is correctly specified in the SQL statement and that the type exists.
sqlcode: -20012
sqlstate: 42740
SQL20013N The object super-object-name is not valid as a supertype, supertable, or superview of object sub-object-name.
Explanation
If the statement resulting in the error is creating a type, then super-object-name is a type that cannot be a supertype of sub-object-name because it is not a user-defined structured type.
If the statement resulting in the error is creating a table, then super-object-name is a table that cannot be a supertable of table sub-object-name because it is not defined as a typed table or the type of table super-object-name is not the direct supertype of the type used in defining table sub-object-name.
If the statement resulting in the error is creating a view, then super-object-name is a view that cannot be a superview of view sub-object-name because it is not defined as a typed view or the type of view super-object-name is not the direct supertype of the type used in defining view sub-object-name.
The statement cannot be processed.
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
- 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
- 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.
The statement cannot be processed.
User response
The options for a column may only be set or altered for the table or view in the typed table hierarchy or typed view hierarchy where the column is introduced. An attribute can only be dropped from the data type in the type hierarchy where the attribute was introduced.
sqlcode: -20021
sqlstate: 428DJ
SQL20022N The scope for the reference column column-name is already defined.
Explanation
The scope for the reference column column-name cannot be added because it is already defined.
The statement cannot be processed.
User response
Remove the ADD SCOPE clause from the ALTER TABLE statement.
sqlcode: -20022
sqlstate: 428DK
SQL20023N Parameter parm-number of the external or sourced function has a scope defined.
Explanation
A reference type parameter should not have a scope defined when used with external or sourced user defined functions.
The statement cannot be processed.
User response
Remove the SCOPE specification from the definition of the parameter.
sqlcode: -20023
sqlstate: 428DL
SQL20024N The scope table or view target-name is not defined with structured type type-name.
Explanation
- 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
- 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
- not a structured type; or
- a structured type that is defined as not instantiable.
The statement cannot be processed.
User response
Verify that the correct type name is used in the statement.
sqlcode: -20026
sqlstate: 428DP
SQL20027N The subtable or subview sub-object-name was not created because subtable or subview object-name with type type-name already exists.
Explanation
Within a typed table or view hierarchy, only one subtable or subview may exist of a particular subtype. The table or view sub-object-name cannot be created since there is already a table or view of type type-name defined. The table or view that already exists is object-name.
The statement cannot be processed.
User response
Verify that the subtable or subview is being created with the correct type and that the subtable is being created under the correct supertable or the subview is being created under the correct superview.
sqlcode: -20027
sqlstate: 42742
SQL20028N The table or view table-name cannot have a different schema name from the other tables or views in the same hierarchy.
Explanation
All tables in a typed table hierarchy must have the same schema name and all views in a typed view hierarchy must have the same schema name.
The statement cannot be processed.
User response
Verify that the schema name of the table or view is correct. If a hierarchy name is specified, verify that its schema name matches the schema name of the root table or view. If a subtable is being created, verify that it is being created under the correct supertable. If a subview is being created, verify that it is being created under the correct superview.
sqlcode: -20028
sqlstate: 428DQ
SQL20029N operation cannot be applied to a subtable or subview.
Explanation
The operation operation was applied to a table or view that is not the root of a table hierarchy or view hierarchy.
The subtable or subview cannot be a random distribution table.
The statement cannot be processed.
User response
Specify the root table of the table hierarchy or root view of the view hierarchy in the operation.
If the operation was DISTRIBUTE BY RANDOM, execute the CREATE TABLE statement again without the DISTRIBUTE BY RANDOM clause.
sqlcode: -20029
sqlstate: 428DR
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
The attributes of a structured type cannot be added or dropped if a typed table or typed view of the structured type or any of its subtypes currently exists. Attributes of a structured type also cannot be added or dropped when a column exists in the table that directly or indirectly uses type-name. Also, attributes of a structured type cannot be added or dropped when the type type-name or one of its subtypes is used in an index extension. The table, view, or index extension object-name is one table, view, or index extension that is dependent on the structured type type-name. There may be other tables, views, or index extensions that are dependent on the type or one of its proper subtypes.
The statement cannot be processed.
User response
Verify that the right type is being altered or drop any tables, views, and index extensions that are dependent on the structured type type-name.
sqlcode: -20030
sqlstate: 55043
SQL20031N Object may not be defined on a subtable.
Explanation
Primary key and unique constraints can only be defined on the root table of a typed table hierarchy. Similarly, unique indexes can only be defined on the root table of a typed table hierarchy.
The statement cannot be processed.
User response
Unique indexes, Primary key or unique constraints can only be defined on the root table of a table hierarchy.
sqlcode: -20031
sqlstate: 429B3
SQL20032N Index on the specified columns cannot be defined on subtable table-name.
Explanation
The columns specified for the index were all introduced at a higher level in the typed table hierarchy than subtable table-name. Therefore, the index cannot be created on this subtable.
The statement cannot be processed.
User response
Determine the table in the table hierarchy where all of the columns are included for the first time. Use this as the table name when creating the index.
sqlcode: -20032
sqlstate: 428DS
SQL20033N An expression including partial-expression does not include a valid scoped reference.
Explanation
The expression that includes partial-expression requires an operand that is reference type with a scope defined. If the expression includes the DEREF function, the argument of the function must be a reference type with a scope defined.
For the dereference operator (->), the left operand needs to be a reference type with a scope defined.
The statement cannot be processed.
User response
Correct the SQL statement syntax so the operand or argument is a reference type with a scope defined.
sqlcode: -20033
sqlstate: 428DT
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
All the data types listed in the TYPE predicate must be included in a data type hierarchy that includes the data type of the left operand of the TYPE predicate. The data type left-type-name is not a structured data type (not part of any type hierarchy) or the data type list-type-name is not included in the data type hierarchy that includes left-type-name.
The statement cannot be processed.
User response
Ensure that the data type of the expression and all listed data types in the TYPE predicate are structured data types within the same data type hierarchy. If left-type-name is SYSIBM.REFERENCE, use DEREF to make the result data type of the expression a structured data type.
sqlcode: -20034
sqlstate: 428DU
SQL20035N Invalid left operand of a dereference operator. Path expression starts with expression-string.
Explanation
- The left operand includes a column function that uses a column function as an argument.
- The left operand expression includes a column function and a reference to a column that is not in the GROUP BY clause.
User response
Correct the left operand of the dereference operator for the path expression that starts with expression-string.
sqlcode: -20035
sqlstate: 428DV
SQL20036N The object identifier column column-name cannot be referenced using the dereference operator.
Explanation
The dereference operator is used with column-name as the right operand. This column is the object identifier column of the target table of the dereference and is not valid for this operator.
The statement cannot be processed.
User response
Correct the name of the column in the dereference operation.
sqlcode: -20036
sqlstate: 428DW
SQL20037N Object identifier column is required to create the root table or root view object-name of a typed table or view hierarchy.
Explanation
When creating the root table of a typed table hierarchy, an object identifier (OID) column must be defined (using the REF IS clause) on the CREATE TABLE statement.
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 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
- 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.
More precisely, let a 1:t 1,... a n:t n be the function result columns and data types of the key transformation function. The function result columns of the range-producing function must be b 1:t 1,...,b m:t m,c 1:t 1,...,c m:t m, where m <= n and the "b" columns are the start key columns and the "c" columns are the stop key columns.
The statement cannot be processed.
User response
Specify a range-producing table function that is consistent with the key transformation table function.
sqlcode: -20040
sqlstate: 428E1
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.
Explanation
The number of target key parameters has to match with the number of results returned by the key transform function. In addition, the type of the target key parameters must exactly match the corresponding function result types.
The statement cannot be processed.
User response
Specify the correct number and type of parameters as the target key parameters.
sqlcode: -20041
sqlstate: 428E2
SQL20042N The maximum allowable parm-type parameters is exceeded in index extension index-ext-name. The maximum is max-value.
Explanation
Too many parameters were specified. If parm-typeis INDEX EXTENSION, then up to max-value instance parameters may be specified. If parm-typeis INDEX KEYS, then up to max-value key source parameters may be specified.
The statement cannot be processed.
User response
Specify no more than the maximum number of parameters.
sqlcode: -20042
sqlstate: 54046
SQL20043N Argument for routine routine-name is not valid. Reason code = reason-code.
Explanation
- 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
- 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.
The statement cannot be processed.
User response
If routine-name is not empty, specify a routine that conforms to the rules for a function or method specified in the specific clause of the CREATE INDEX EXTENSION or CREATE FUNCTION statement. Otherwise, specify a CASE expression that conforms to the rules for a CASE expression in the FILTER USING clause.
sqlcode: -20044
sqlstate: 428E4
SQL20045N The data type of instance parameter parameter-name is invalid in index extension index-ext-name.
Explanation
An instance parameter must be one of the following data types: VARCHAR, VARGRAPHIC, INTEGER, DECIMAL, or DOUBLE.
The statement cannot be processed.
User response
Specify a valid data type for the instance parameter parameter-name.
sqlcode: -20045
sqlstate: 429B5
SQL20046N SELECTIVITY clause following predicate-string can only be specified for a valid user-defined predicate.
Explanation
The SELECTIVITY clause is specified with a predicate that does not include a valid user-defined function. A valid user-defined function includes a PREDICATES clause with a WHEN clause that matches the predicate. The SELECTIVITY clause cannot be specified except with a user-defined predicate.
The statement cannot be processed.
User response
Remove the SELECTIVITY clause following the predicate.
sqlcode: -20046
sqlstate: 428E5
SQL20047N The search method method-name is not found in the index extension index-ext-name.
Explanation
The method method-name referenced in an exploitation rule of the user-defined predicate has to match one of the search methods specified in the index extension index-ext-name.
The statement cannot be processed.
User response
Specify a method defined in the index extension.
sqlcode: -20047
sqlstate: 42743
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.
Explanation
The search argument provided for method method-name does not match the argument of the corresponding search method in the index extension index-ext-name. Either the number of arguments or the type of the arguments do not match the defined number or type of the parameters.
The statement cannot be processed.
User response
Specify a search argument that matches the parameters defined in the index extension.
sqlcode: -20048
sqlstate: 428E6
SQL20049N Type of an operand following the comparison operator in the AS PREDICATE WHEN clause does not exactly match the RETURNS type.
Explanation
The definition of the user-defined predicate is not valid. In the AS PREDICATE WHEN clause, the type of the operand following the the comparison operator is not an exact match with the RETURNS type of the function.
The statement cannot be processed.
User response
Specify an the operand with the correct data type.
sqlcode: -20049
sqlstate: 428E7
SQL20050N The search target or search argument parameter-name does not match a name in the function being created.
Explanation
Each search target in an index exploitation rule has to match some parameter name of the function that is being created. Each search argument in an index exploitation rule must match either an expression name in the EXPRESSION AS clause or a parameter name of the function being created. Parameter names must be specified in the parameter list for the function.
The statement cannot be processed.
User response
Specify only valid names of the function in the search target or search argument.
sqlcode: -20050
sqlstate: 428E8
SQL20051N The argument parameter-name cannot appear as both a search target and a search argument in the same exploitation rule.
Explanation
In the exploitation clause, a function parameter cannot be specified as an argument following KEY and as an argument of the method specified following the USE keyword.
The statement cannot be processed.
User response
Specify a parameter name of the function in no more than on of the search target or the search argument.
sqlcode: -20051
sqlstate: 428E9
SQL20052N Column column-name is an object identifier column which cannot be updated.
Explanation
The UPDATE statement includes setting a column that is an object identifier (OID) column. An OID column cannot be updated.
The statement cannot be processed.
User response
Remove the SET of column-name from the UPDATE statement.
sqlcode: -20052
sqlstate: 428DZ
SQL20053N Fullselect in typed view view-name is not valid. Reason code = reason-code.
Explanation
- 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
- 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
- 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
- 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.
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: -20060
sqlstate: 22526
SQL20062N Transform function transform-type in transform group group-name for type type-name cannot be used for a function or method.
Explanation
The transform function defined in the transform group group-name for type type-name cannot be used for a function or method because the transform function is not written in SQL (defined with LANGUAGE SQL). The transform group cannot be used for this function or method.
The statement cannot be processed.
User response
Specify a transform group for type type-name that has the transform functions defined with LANGUAGE SQL.
sqlcode: -20062
sqlstate: 428EL
SQL20063N TRANSFORM GROUP clause must be specified for type type-name.
Explanation
The function or method includes a parameter or returns data type of type-name that does not have a transform group specified.
The statement cannot be processed.
User response
Specify a TRANSFORM GROUP clause with a transform group name that is defined for type-name.
sqlcode: -20063
sqlstate: 428EM
SQL20064N Transform group group-name does not support any data type specified as a parameter or returned data type.
Explanation
The transform group group-name specified in the TRANSFORM GROUP clause is not defined for any data type that is included in the parameter list or the RETURNS clause of a function or method.
The statement cannot be processed.
User response
Remove the transform group from the function or method definition.
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
- 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
- Type A has an attribute of type B
- Type B is a subtype of A, or a supertype of A.
- 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).
sqlcode: -20077
sqlstate: 428ED
SQL20078N Hierarchy object object-name of type object-type can not be processed using operation operation-type.
Explanation
The operation operation-type was attempted using a hierarchy object named object-name of type object-type. This operation does not support processing of a hierarchy object.
The statement could not be processed.
User response
Verify that the correct object name was used. For objects types TABLE or VIEW, the object must be the name of a subtable in the table or view hierarchy. In some cases, the object must specifically name the root table. For objects of type index, the name must be a name of an index created on a subtable.
sqlcode: -20078
sqlstate: 42858
SQL20080N Method specification for method-name cannot be dropped because a method body exists.
Explanation
The method specification method-name still has an existing method body that must be dropped before the method specification can be dropped.
The statement cannot be processed.
User response
Use the DROP METHOD statement with the same method specification to drop the method body and then issue the ALTER TYPE statement again to drop the method specification.
sqlcode: -20080
sqlstate: 428ER
SQL20081N The method body cannot be defined for a LANGUAGE language-type method specification method-name.
Explanation
The method specificationmethod-name is defined with LANGUAGE language-type. If the LANGUAGE is SQL, the method body must be an SQL control statement. For other languages, the EXTERNAL clause must be specified.
The statement cannot be processed.
User response
Change the method body to match the LANGUAGE specified in the method specification.
sqlcode: -20081
sqlstate: 428ES
SQL20082N The dynamic type expression-type-id of the expression is not a subtype of the target data type target-type-id in a TREAT specification.
Explanation
The dynamic data type of the result of the expression specified in the TREAT specification is expression-type-id. The specified target data type target-type-id is a proper subtype of expression-type-id which is not allowed.
The statement cannot be processed.
User response
Change the target-type-id of the TREAT specification to a supertype of expression-type-id or change the expression so that the dynamic data type of the result is a subtype of target-type-id.
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.
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
- 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.
- table and row methods
- external methods with PARAMETER STYLE JAVA
- system generated mutator and observer methods
The statement cannot be processed.
User response
Change the routine that is being defined to use a different routine name than routine-name or change the parameters of the routine.
sqlcode: -20084
sqlstate: 42745
SQL20085N A routine defined with PARAMETER STYLE JAVA cannot have structured type type-name as a parameter type or as the returns type.
Explanation
A routine is defined with PARAMETER STYLE JAVA and one of the parameter types or a returns type is defined using the structured type type-name. This is not supported by this version of DB2.
The statement cannot be processed.
User response
Change to a different parameter style for the routine or remove the structured type from the routine definition.
sqlcode: -20085
sqlstate: 429B8
SQL20086N Length of the structured type value for a column exceeds the system limit.
Explanation
The value for a structured type column exceeds 1 gigabyte in overall size, including descriptor data for the instance. The column could be one that is being inserted or updated directly or it may be a column that is generated.
The statement cannot be processed.
User response
Reduce the size of the structured type value that is being assigned to the column.
sqlcode: -20086
sqlstate: 54049
SQL20087N DEFAULT or NULL cannot be used in an attribute assignment.
Explanation
The UPDATE statement is using an attribute assignment to set the value of an attribute in a structured type column. This form of assignment statement does not allow the use of the keyword DEFAULT or the keyword NULL as the right hand side of the assignment.
The statement cannot be processed.
User response
Specify an expression for the right hand side of the attribute assignment or change the assignment so it is not using the attribute assignment syntax.
sqlcode: -20087
sqlstate: 428B9
SQL20089N A method name cannot be the same as a structured type name within the same type hierarchy.
Explanation
A specified method name is the same as the structured type which is defined for one of the supertypes or subtypes of the structured type.
The statement cannot be processed.
User response
Specify a different name for the method.
sqlcode: -20089
sqlstate: 42746
SQL20090W The use of the structured type having attribute attribute-name of type DATALINK is effectively limited to the type of a typed table or type view.
Explanation
The attribute attribute-name is defined with a type of DATALINK or a distinct type based on a DATALINK. A structured type that includes such an attribute can only be used as the type of a table or view. If used as the type of a column of a table or view it can only be assigned the null value.
The statement continued processing.
User response
Consider the intended use of the structured type. If the type will be used as a column data type, then remove the attribute attribute-name from the structured type or use a data type other than DATALINK for the attribute.
sqlcode: +20090
sqlstate: 01641
SQL20092N The statement failed because a table or view was specified in the LIKE clause and the object cannot be used in this context.
Explanation
The LIKE clause for a CREATE GLOBAL TEMPORARY TABLE statement or DECLARE GLOBAL TEMPORARY TABLE statement specified the name of a table that has a column defined as IMPLICITLY HIDDEN. Since the implicitly hidden attribute is not supported for created temporary tables and declared temporary tables, the table cannot be specified in the LIKE clause.
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 statement cannot be processed.
User response
Remove the column from either the column name list or the SET assignment statement that sets the new transition variable of a generated column and resubmit the statement.
sqlcode: -20094
sqlstate: 42989
SQL20102N The CREATE or ALTER statement for the routine routine-name specified the option-name option which is not allowed for the routine.
Explanation
The option option-name was specified when creating or altering the routine routine-name. The option does not apply to the routine because of other characteristics of the routine. For sourced procedures, only ALTER PARAMETER can be specified, and ALTER PARAMETER can only be specified for sourced procedures.
User response
For an ALTER statement, ensure that the correct routine is being specified. Otherwise, remove the failing option and reissue the statement.
sqlcode: -20102
sqlstate: 42849
SQL20108N A result set contains an unsupported data type in position position-number for the cursor cursor-name opened by the stored procedure procedure-name.
Explanation
The stored procedure named by procedure-name cannot return at least one of its query result sets named by cursor-name because at least one of the columns, column position-number contains a data type which is not supported by either the DRDA Application Requestor (client) or the DRDA Application Server (server). As such the call to the stored procedure fails.
User response
Modify the OPEN statement (and subsequent FETCH statements) for cursor cursor-name in the stored procedure procedure-name on the server to not select the unsupported data type in column position-number. The client application which invoked the stored procedure may need to be modified to reflect the change in the stored procedure.
sqlcode: -20108
sqlstate: 56084
SQL20109W Error in DB2 debugger support. Reason code: reason-code.
Explanation
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:
- Free the existing savepoints using the RELEASE SAVEPOINT statement.
- 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
SELECT FUNCSCHEMA, FUNCNAME,
SPECIFICNAME
FROM SYSCAT.FUNCTIONS
WHERE FUNCID = method-id
sqlcode: -20113
sqlstate: 22004
SQL20114W Column column-name in table table-name is not long enough for the defined length of the USER default value.
Explanation
The column column-name is defined with a length that is less than 128 bytes. The clause DEFAULT USER has been specified for this column. Since the USER special register is defined as VARCHAR(128), any attempt to assign the default value for table-name by a user with a user ID longer than the column length results in an error. A user with a user ID longer than the column length would never be able to insert or update this column to the default value.
User response
If your system standards would not allow a user ID to exceed the length of the column, then this warning may be ignored. To prevent this warning from occurring, the length of the column must be at least 128 bytes. You can change the column length by dropping and creating the table again, or, if the data type is VARCHAR, by using ALTER TABLE to increase the length of the column.
sqlcode: +20114
sqlstate: 01642
SQL20115N The routine-type routine-name cannot be used as a transform-type transform function in transform group group-name.
Explanation
If routine-type is FUNCTION, then the function defined by routine-name cannot be used as a transform function because it is a built-in function. If routine-type is METHOD, then the method defined by routine-name cannot be used as a transform function because it is a method.
The statement cannot be processed.
User response
Specify a different function for the transform-type transform function of transform group group-name.
sqlcode: -20115
sqlstate: 428EX
SQL20116N The data type of the search target parameter-name does not match that of the source key specified in the index extension index-extension-name.
Explanation
If the search target is of a built-in or distinct data type, its type must exactly match that of the source key specified in the index extension. If the data type of the search target is a structured type, it must be in the same structured type hierarchy as the data type of the source key in the index extension.
The statement cannot be processed.
User response
Specify a search target with a data type that matches that of the source key in the index extension.
sqlcode: -20116
sqlstate: 428EY
SQL20117N A window specification for an OLAP function is not valid. Reason code = reason-code.
Explanation
- 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
- 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.
Explanation
The maximum number of attributes, including inherited attributes, allowed for each structured type has been exceeded with the definition of structured type type-name. The maximum number of attributes, including inherited attributes, is max-value.
The statement cannot be processed.
User response
Ensure that the number of attributes for the structured type does not exceed the limit.
sqlcode: -20118
sqlstate: 54050
SQL20119N A ROW function must define at least two columns.
Explanation
A function that specifies ROW in the RETURNS clause must include a column list with at least two columns.
User response
Either remove the ROW keyword from the RETURNS clause to make it a scalar function or specify multiple columns in the column list of the RETURNS clause.
sqlcode: -20119
sqlstate: 428F0
SQL20120N An SQL TABLE function must return a table result.
Explanation
An SQL function that specifies TABLE in the RETURNS clause must return a result that is a table. With the exception of a scalar fullselect, a scalar expression cannot be returned as the result of an SQL TABLE function.
User response
Either remove the TABLE keyword from the RETURNS clause to make it a scalar function or specify a fullselect in the RETURN statement of the TABLE function body.
sqlcode: -20120
sqlstate: 428F1
SQL20121N Only one of WITH RETURN or SCROLL can be specified for cursor cursor-name.
Explanation
Both WITH RETURN and SCROLL were specified for cursor cursor-name, but this is not allowed.
The statement cannot be processed.
User response
Change the DECLARE CURSOR statement to either specify NO SCROLL (or remove the SCROLL keyword), or remove the WITH RETURN clause.
sqlcode: -20121
sqlstate: 428F3
SQL20123N Call to stored procedure procedure failed because the result set returned for a scrollable cursor is not positioned before the first row.
Explanation
A scrollable result set has been returned by a CALL to stored procedure procedure, and one or more of these cursors is not positioned before the first row.
The CALL to the stored procedure was not successful. All result set cursors defined in the stored procedure were closed before returning to the caller. The scrollable cursor cannot be used to FETCH from the result set. Actions done by the stored procedure are not rolled back, and any external actions initiated by the stored procedure have completed because the error was detected at the end of the execution of the stored procedure.
User response
Modify the content of the stored procedure to make sure that the result set cursors are positioned before the first row before returning to the caller.
sqlcode: -20123
sqlstate: 560B1
SQL20128N Cursor cursor-name is scrollable, but the result table cannot include output from a table function.
Explanation
The cursor cursor-name is defined to be scrollable, but the result table includes output from a table function. This combination is not supported.
The statement cannot be processed.
User response
Either modify the definition of the cursor so it is not scrollable, or ensure that the result table does not include output from a table function.
sqlcode: -20128
sqlstate: 428F6
SQL20131N Object number object-number of type object-type was specified more than once in a list of objects.
Explanation
In a list of object names of type object-type, the object numbered object-number was specified more than once. The operation of the statement cannot be performed on the object more than once.
User response
Correct the duplicated object in the list removing duplicate occurrences. (In the MDC case, the object type will be “dimension”.)
sqlcode: -20131
sqlstate: 42713
SQL20133N Operation operation-name cannot be performed on external routine routine-name. The operation can only be performed on SQL routines.
Explanation
You attempted to perform operation operation-name on external routine routine-name. However, you can only perform that operation on SQL routines. The operation did not complete successfully.
User response
Ensure the name you provide identifies an SQL routine.
sqlcode: -20133
sqlstate: 428F7
SQL20134N The SQL Archive (SAR) file for routine routine-name could not be created on the server.
Explanation
The creation of the SQL archive (SAR) for routine routine-name failed because the database manager could not find either the library or the bind file for the specified routine. Bind files are only available for SQL routines created with DB2 Version 7.1, FixPak 2 or later.
User response
Recreate the procedure on a server with DB2 Version 7.1, FixPak 2 or later, and try the operation again.
sqlcode: -20134
sqlstate: 55045
SQL20135N The specified SQL archive does not match the target environment. Reason code = reason-code.
Explanation
- 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
Ensure that the environment on which the SQL archive was created matches the target environment and reissue the command. If the environments do not match, you must manually create the SQL routine using the target environment.
sqlcode: -20135
sqlstate: 55046
SQL20136N Routine routine-name (specific name specific-name) attempted to access a federated object.
Explanation
An SQL statement in an external function or method attempted to access one or more federated objects. This statement is executed from routine routine-name (specific name specific-name). It is currently not supported to access a federated object from an external function or method.
User response
Remove the references to federated objects from the routine.
sqlcode: -20136
sqlstate: 55047
SQL20138N The statement may not be explained because the routine routine-name (specific name specific-name) is not defined as MODIFIES SQL DATA.
Explanation
The routine routine-name (specific name specific-name) is defined as either CONTAINS SQL or READS SQL DATA and attempted to explain an SQL statement. Explaining an SQL statement requires writing to the explain tables, which is only permitted in a MODIFIES SQL DATA routine.
User response
Do not attempt to explain SQL statements from a routine defined as either CONTAINS SQL or READS SQL DATA.
sqlcode: -20138
sqlstate: 42985
SQL20139N SQL statements may not be issued in routine routine-name (specific name specific-name) because of a previous statement failed or was interrupted.
Explanation
During the execution of routine routine-name (specific name specific-name), or a nested routine, either a statement failed and requires a roll back or an interrupt occurred. It is necessary for all routines invoked from the outer-most statement to finish executing and for control to be passed back to the outer-most statement so that the database manager may perform the necessary recovery. Until this recovery has been completed, SQL statements may not be issued.
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
- 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.
These sequences cannot be referenced in a DROP SEQUENCE, GRANT or REVOKE statement, or in a NEXT VALUE or PREVIOUS VALUE expression.
User response
Specify the name of a user-defined sequence object in this context.
sqlcode: -20142
sqlstate: 428FB
SQL20143N The encryption or decryption function failed, because the ENCRYPTION PASSWORD value is not set.
Explanation
The ENCRYPTION PASSWORD value is not set.
User response
Issue the SET ENCRYPTION PASSWORD statement to set the ENCRYPTION PASSWORD value. The length of the password must be a minimum of 6 bytes and a maximum of 127 bytes.
sqlcode: -20143
sqlstate: 51039
SQL20144N The encryption password is invalid because the length of the specified password was less than 6 bytes or greater than 127 bytes.
Explanation
The data must be encrypted with a password length between 6 and 127.
User response
Ensure that the password length is between 6 and 127.
sqlcode: -20144
sqlstate: 428FC
SQL20145N The decryption function failed. The password used for decryption does not match the password used to encrypt the data.
Explanation
The data must be decrypted using the same password that was used to encrypt the data.
User response
Ensure that the same password is used to encrypt and decrypt the data.
sqlcode: -20145
sqlstate: 428FD
SQL20146N The decryption function failed. The data is not encrypted.
Explanation
The data must be a result of the ENCRYPT function.
User response
Ensure that the data type is a result of the ENCRYPT function.
sqlcode: -20146
sqlstate: 428FE
SQL20147N The ENCRYPT function failed. Multiple pass encryption is not supported.
Explanation
Data that has already been encrypted cannot be encrypted again.
User response
Ensure that the data has not already been encrypted.
sqlcode: -20147
sqlstate: 55048
SQL20148N Routine routine-name with specific name specific-name must have the RETURN statement as the last SQL statement of the compound body.
Explanation
The RETURN statement must be the last SQL statement of the compound body in an SQL ROW or TABLE function. No other RETURN statement is allowed within the routine body.
User response
Ensure there is only one RETURN statement and it is the last SQL statement of the compound body.
sqlcode: -20148
sqlstate: 429BD
SQL20149W The buffer pool operation has been completed but will not take effect until the next database restart.
Explanation
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 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:
- Determine the total number of pages for this buffer pool by examining the NPAGES column of the SYSCAT.BUFFERPOOLS catalog view.
- Specify a value for NUMBLOCKPAGES that is not greater than 98 percent of the total number of pages.
sqlcode: -20150
sqlstate: 54052
SQL20151N The value specified for BLOCKSIZE is not in the valid range.
Explanation
The valid range for the value of BLOCKSIZE is 2 to 256.
User response
Change the value for BLOCKSIZE to be greater than or equal to 2 and less than or equal to 256. An optimal value would be the extent size.
sqlcode: -20151
sqlstate: 54053
SQL20152N The specified buffer pool is not currently block-based.
Explanation
The BLOCKSIZE option was used without specifying the number of pages to use in the block area of the buffer pool.
User response
Specify a value for NUMBLOCKPAGES along with the BLOCKSIZE option.
sqlcode: -20152
sqlstate: 428FF
SQL20153N The split image of the database is in the suspended state.
Explanation
The database split image cannot be used while it is in the suspended state.
User response
To resume I/O for this database split image, issue one of the following db2inidb commands:
- 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.
sqlcode: -20158
sqlstate: 42997
SQL20159W The isolation clause is ignored because of the statement context.
Explanation
The isolation clause is specified on a statement that is included in a compound statement that is processed as inline SQL. The isolation clause is ignored and the default isolation level is used for all statements in the compound statement.
User response
No response required. Remove the isolation clause to prevent this warning.
sqlcode: +20159
sqlstate: 01652
SQL20160W The authorizations were granted to USER userid. Groups were not considered because the authorization name is more than 8 bytes.
Explanation
The authorization name has a length of more than 8 bytes. The privilege is granted to the user with authorization name userid without considering groups defined in the system that might have a matching name. Processing continues.
User response
If the grant was intended for a user, no action is required. If the grant was intended for a group, consider choosing an alternate group because group names greater than 8 bytes are not supported. To avoid this warning message, specify the USER keyword before the authorization name.
sqlcode: +20160
sqlstate: 01653
SQL20161W The column name column-name is not a valid column for an event monitor table.
Explanation
The column name specified in the INCLUDES or EXCLUDES clause does not match any valid column for the event monitor table(s) being created.
User response
Examine and correct the specified column name.
sqlcode: -20161
sqlstate: 428AA
SQL20165N An SQL data change statement within a FROM clause is not allowed in the context in which it was specified.
Explanation
- the outer fullselect of a SELECT statement
- a SELECT INTO statement
- the outer fullselect of a common table expression
- the only fullselect in an assignment statement.
If any input variables are used in other parts of the query, a multiple row INSERT in the FROM clause must not include the USING DESCRIPTOR clause. A multiple row INSERT statement in the FROM clause cannot specify NOT ATOMIC. A SQL fullselect within an XQuery statement cannot include an SQL data change statement in the FROM clause. An SQL fullselect in the default clause of a global variable definition cannot include an SQL data change statement in the FROM clause.
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
- FCM connection entries
- FCM message anchors
- FCM request blocks
User response
Try the request again. If the error persists, stop and restart the instance to allow the larger shared memory set size (automatically updated by DB2) to take effect and try the request again.
sqlcode: -20167
sqlstate: 57011
SQL20168N The ALTER BUFFERPOOL statement is currently in progress.
Explanation
A buffer pool cannot be dropped or altered if an ALTER operation is already in progress.
User response
Wait until the ALTER operation has completed. Use the snapshot monitor to check the progress of the ongoing ALTER operation.
sqlcode: -20168
sqlstate: 55051
SQL20169W The buffer pool is not started.
Explanation
The statement was successful, but it will only take effect when the buffer pool is started.
User response
The statement has completed successfully, and will take effect when the database is activated. This happens after all applications disconnect. For the statement to take effect immediately, re-submit it when the buffer pool is started. To start the buffer pool, it is possible in some cases to drop and re-create the buffer pool using the IMMEDIATE option.
sqlcode: +20169
sqlstate: 01654
SQL20170N There is not enough space in the table space tablespace-name for the specified action. Reason code = reason-code.
Explanation
- 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 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.
- 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
- 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
- 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
- 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
- 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 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.
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: -20199
sqlstate: 22526
SQL20200N The install or replace of jar-id failed as url could not be located.
Explanation
The URL specified on the install or replace jar procedure did not identify a valid jar file.
User response
Reissue the install or replace jar procedure with a URL that identifies a valid jar file.
sqlcode: -20200
sqlstate: 46001
SQL20201N The install, replace or remove of jar-id failed as the jar name is invalid.
Explanation
The jar name specified on the install, replace or remove jar procedure was invalid. For example, the jar id may be of the improper format, may not exist to be replaced or removed, or can not be installed as it already exists.
User response
Ensure the jar id is of the correct format. If the jar id exists, it may need to be removed before it can be installed. For the remove or replace procedures, ensure the jar id exists.
sqlcode: -20201
sqlstate: 46002
SQL20202N The replace or remove of jar-id failed as class is in use by routine routine-name (specific name specific-name).
Explanation
The specified class in the jar file is currently in use by a defined procedure, or the replacement jar file does not contain the specified class for which a procedure is defined.
User response
Ensure all procedures referencing the classes being removed are dropped and resubmit the replace or remove procedure.
sqlcode: -20202
sqlstate: 46003
SQL20203N User defined function or procedure function-name has a Java method with an invalid signature.
Explanation
The signature of the java method used to implement the function or procedure was invalid. For example, the method may have parameters that are not mappable to the parameters on the corresponding create statement or the method for a procedure may specify a return value.
User response
Reissue the corresponding CREATE statement specifying parameters that match the Java method, or correct the parameters or return type of the Java method and rebuild the class.
sqlcode: -20203
sqlstate: 46007
SQL20204N The user defined function or procedure function-name was unable to map to a single Java method.
Explanation
The identified function or procedure either failed to find a matching Java method, or found more than 1 matching Java method.
User response
Correct either the Java method or corresponding create statement so that the function or procedure call resolves to a single Java method.
sqlcode: -20204
sqlstate: 46008
SQL20205N The user defined function or procedure function-name has an input argument with a null value that could not be passed to the method.
Explanation
A function created with "CALLED ON NULL INPUT" or procedure has an input parameter with a null value but the Java datatype of this argument does not support null values. Examples of Java datatypes that do not support null values are BOOLEAN, BYTE, SHORT, INT, LONG or DOUBLE.
User response
If the method is to be called with null values, ensure the input Java types are capable of accepting a null value. For functions, the function could also be created with "RETURNS NULL ON NULL INPUT".
sqlcode: -20205
sqlstate: 39004
SQL20206W The procedure function-name returned too many result sets.
Explanation
The specified procedure returned more results sets than were specified on the CREATE PROCEDURE statement.
User response
Modify the procedure to return fewer result sets, or drop and recreate the procedure specifying the correct number of result sets.
sqlcode: +20206
sqlstate: 0100E
SQL20207N The install or remove jar procedure for jar-id specified the use of a deployment descriptor.
Explanation
The DEPLOY or UNDEPLOY parameter of the install or replace jar procedure was non-zero; this parameter is not supported and must be zero.
User response
Reissue the procedure with the DEPLOY or UNDEPLOY parameter set to zero.
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.
sqlcode: -20223
sqlstate: 560BF
SQL20225W The buffer pool operation (DROP) will not take effect until the next database startup because the buffer pool is in use.
Explanation
The DROP BUFFERPOOL statement was issued and completed successfully, however a table space is still using this buffer pool. Even though a table space may be reassigned to a different buffer pool, the table space reassignment may only take effect on the next database restart. If the table space in question is dropped by the time this operation commits, then this buffer pool will be removed from memory at that time. Until the next database restart, this buffer pool may remain in memory and a CREATE or ALTER of any other buffer pool that would reuse this buffer pool ID may be deferred.
User response
Restart the database to delete the buffer pool from memory.
sqlcode: 20225
sqlstate: 01657
SQL20227N Required clause is missing for argument number of expression.
Explanation
expression expression requires that a clause be specified for argument number number. If expression is XMLATTRIBUTES, then an AS clause is required for the XML attribute name. If expression is XMLFOREST, then an AS clause is required for the XML element name. The statement cannot be processed.
User response
Correct the statement by providing the required clause for the argument.
sqlcode: -20227
sqlstate: 42633
SQL20230N The procedure name may not be specified by a host variable in the CALL statement and the arguments may not be specified with a USING DESCRIPTOR clause.
Explanation
The procedure name must be specified as an identifier in the CALL statement and the arguments must be explicitly provided. A host variable may not be used for the procedure name. The USING DESCRIPTOR clause may not be used to specify the arguments.
User response
The CALL statement must be rewritten to provide an identifier for the procedure name and to explicitly list the arguments. If either the procedure name or the arguments are not known until run time, use a dynamically prepared CALL statement.
For applications being migrated from a release previous to V8, the CALL_RESOLUTION DEFERRED precompile option can be used until the applications are rewritten to use the dynamically prepared CALL statement.
sqlcode: -20230
sqlstate: 42601
SQL20238N The table table-name is defined as CCSID UNICODE and cannot be used in an SQL function or SQL method.
Explanation
In a non-Unicode database, only tables with the ASCII encoding scheme may be referenced inside an SQL function or SQL method. Since the table table-name is defined as CCSID UNICODE, it may not be used in an SQL function or an SQL method.
User response
Do not use CCSID UNICODE tables in an SQL function or SQL method.
sqlcode: -20238
sqlstate: 560C0
SQL20239N Tables created in the Unicode encoding scheme cannot be a typed table, or contain graphic types, the XML type, or user-defined types.
Explanation
In a non-Unicode database, tables created in the Unicode encoding scheme cannot be a typed table and also cannot contain columns defined with any graphic type, the XML type, or user-defined type.
User response
Remove the CCSID UNICODE clause from the table definition or remove the graphic type, the XML type, or user-defined type from the table definition.
sqlcode: -20239
sqlstate: 560C1
SQL20240N Invalid specification of a column column-name of type DB2SECURITYLABEL. Reason code: reason-code.
Explanation
For a column of type DB2SECURITYLABEL the specification of an attribute in a CREATE TABLE or ALTER TABLE statement may be invalid for the following reason.
- 4
A security label column cannot be defined for a created temporary table or a declared temporary table.
- 10
A column of type DB2SECURITYLABEL cannot be defined as a generated column.
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.
Explanation
The sample size specified in the TABLESAMPLE clause must be a positive numeric value greater than zero and less than or equal to 100.
The statement was not processed.
User response
Change the sample size specified in the TABLESAMPLE clause to a valid numeric value greater than zero and less than or equal to 100.
sqlcode: -20242
sqlstate: 2202H
SQL20243N The view view-name is the target in the MERGE statement, but is missing the INSTEAD OF trigger for the operation operation.
Explanation
The view view-name is a direct or indirect target in the MERGE statement and has an INSTEAD OF trigger defined for it, but does not have INSTEAD OF triggers defined for all operations. The trigger for the operation operation is not present.
User response
Create INSTEAD OF triggers for the UPDATE, DELETE and INSERT operations on view view-name, or drop all INSTEAD OF triggers for the view.
sqlcode: -20243
sqlstate: 428FZ
SQL20247N Table table-name cannot be partitioned and contain a column with data type DATALINK.
Explanation
An attempt was made to create or alter table table-name in such a way that would create a partitioned table that contained a column with data type DATALINK. A table cannot be both partitioned and contain such a column.
User response
Create or alter the table to be either partitioned or to include a column of data type DATALINK, but not both.
sqlcode: -20247
sqlstate: 429BH
SQL20249N The statement was not processed because the package named package-name needs to be explicitly rebound.
Explanation
The package package-name contains a section that must be rebound.
User response
Explicitly rebind the package using either the REBIND command or the BIND command. To preserve function and data type resolution, specify the RESOLVE CONSERVATIVE option on the REBIND command.
sqlcode: -20249
sqlstate: 560C5