SQL20500 - SQL20999

SQL20500N Invalid use of row data type value in a list of values.

Explanation

The use of a row type in a list is invalid.

Invalid uses of a row variable in a list include:

In a list of source variables for an assignment statement or the assignment clause of an update operation.
In a list of target variables for a simple assignment statement or an assignment statement in a SELECT INTO, FETCH, or VALUES INTO statement.
In a list of expressions representing a row in the VALUES clause of an insert operation.

The statement cannot be executed.

User response

Rewrite the statement so that the list contains no row variable or replace the list with a single row variable value.

sqlcode: -20500

sqlstate: 428HR

SQL20501N The explain facility failed because the specified section could not be found. Reason code = `reason-code`.

Explanation

The explain facility was invoked to provide access plan information from a section, and the specified section could not be found. The reason codes are as follows:

1: The section identified by the specified executable ID could not be found in the specified source location.
2: The section identified by the specified activity information could not be located in the specified activity event monitor.
3: The section identified by the specified package and section information could not be located in the catalogs.
4: The section was not captured by the activity event monitor.

User response

The user response corresponding to the reason code is:

1: Verify that the executable ID and section source location were specified correctly. Confirm that the section with the specified executable ID is still present in the source location. If the source location is the in-memory package cache, the section may already have been removed from the cache, in which case, no further action can be taken.
2: Verify that the specified activity information is correct and corresponds to an activity captured by the specified activity event monitor.
3: Verify that the specified package and section information corresponds to a section in the catalogs.
4: Ensure that section collection has been enabled for the activities of interest using the COLLECT ACTIVITY WITH DETAILS, SECTION clause on a workload management object.

sqlcode: -20501

sqlstate: 4274L

SQL20502N The explain facility failed because the specified activity event monitor `evmon_name` is not a write-to-table event monitor.

Explanation

The Explain facility has been invoked to provide access plan information from a section captured by an activity event monitor. The event monitor must be a write-to-table event monitor type. The activity event monitor specified as the section source location is not a write-to-table event monitor.

User response

Provide the name of a write-to-table activity event monitor. The CREATE EVENT MONITOR ...WRITE TO TABLE statement can be used to create a write-to-table event monitor.

sqlcode: -20502

sqlstate: 55074

SQL20503N The explain facility is not supported for the specified section. Reason code: `reason-code`.

Explanation

The explain facility has been invoked to provide access plan information from a section but the section does not support this action. The reason codes indicates the reason for the failure:

1: The section specified as input was captured in a release prior to DB2 Version 9.7. The explain facility cannot be used on a section captured from a release prior to DB2 V9.7.
2: The section specified as input was captured in a release prior to the current DB2 version and the explain facility is no longer supported on sections in that release.
3: The section specified as input was captured in a release later than the current DB2 version. The explain facility cannot be used on a section captured from a release later than the current release.
4: The section specified as input is not recognized as a valid section.
5: The section does not contain access plan information. Some sections for statements such as DDL do not contain an access plan. The explain facility cannot be used on these sections.
6: The section does not exist because the statement is invalid. The statement was in error when the package was bound.
7: The section does not exist because the statement is an incremental bind statement. Incremental bind statements do not have a section stored in the catalogs. These statements are bound during the execution of an application process.

User response

Respond according to the reason code:

1: Invoke the explain facility specifying a section captured from a valid release.
2: Invoke the explain facility specifying a section captured from a valid release.
3: Invoke the explain facility specifying a section captured from a valid release.
4: Invoke the explain facility specifying a valid section as input.
5: Invoke the explain facility specifying a section that contains access plan information.
6: If the statement is supposed to execute at this database server, correct the problem found and reissue the PRECOMPILE or BIND command using the ACTION REPLACE option. After correcting the problem, retry the explain facility.
7: To use the explain facility on the section for an incremental bind statement, follow a process similar to dynamic statements: identify the statement in the package cache after it has been bound, and invoke the explain facility specifying the executable ID corresponding to the section for the statement.

sqlcode: -20503

sqlstate: 55075

SQL20504N The statement failed because the target object of the anchored data type is unsupported or is being used in an unsupported context.

Explanation

An anchored data type is a data type that is defined to be the same as that of another object. If the underlying object data type changes, the anchored data type also changes. An anchored data type can reference a variety of objects, but certain objects cannot be referenced.

This message is returned when an attempt is made to reference an unsupported target in an anchored data type, or to reference a target in an anchored data type in an unsupported context.

User response

Remove any anchored data type references that are unsupported or that are used in unsupported contexts and then reissue the statement.

sqlcode: -20504

sqlstate: 428HS

SQL20505N The WITH ORDINALITY clause is not valid with UNNEST of an associative array.

Explanation

The WITH ORDINALITY clause must not be specified when the argument of the UNNEST table function is an associative array. An associative array is not organized according to ordinal position.

User response

Remove the WITH ORDINALITY clause or change the argument of the UNNEST function to an ordinary array. Try the statement again.

sqlcode: -20505

sqlstate: 428HT

SQL20506N The cursor variable could not be used in an OPEN statement in the current scope because the cursor constructor value was assigned in a different scope.

Explanation

A cursor variable cannot be used in an OPEN statement that is outside the scope in which the cursor constructor value is assigned to the cursor variable.

User response

Use the cursor variable in an OPEN statement that is in the same scope as the assignment of the cursor constructor to the cursor variable.

sqlcode: -20506

sqlstate: 51044

SQL20507N The query associated with the cursor variable used in an OPEN or FETCH statement recursively invokes another cursor operation using the same cursor.

Explanation

The query associated with a cursor variable used in an OPEN or FETCH statement includes an invocation of a function that is passed a cursor variable as an argument and that cursor argument is used to perform some cursor operation within the function. If the query specifies the same variable name as specified in the cursor operation, or specifies a cursor variable that references the same cursor, then the function would be recursively operating on the same cursor. Such recursive cursor operations are not supported.

The statement cannot be processed.

User response

Change the query associated with the cursor variable used in the OPEN or FETCH statement so that the function which is passed a cursor variable argument is using a different cursor variable that does not reference the same cursor as the OPEN or FETCH statement. Try the statement again.

sqlcode: -20507

sqlstate: 24525

SQL20508N An error occurred during the revalidation of object `object-name`. Operation `operation` failed with SQLCODE `sqlcode`, SQLSTATE `sqlstate`, and message tokens `token-list`.

Explanation

The processing of statement operation by the SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS procedure failed. During this processing an error was encountered. The SQLCODE, SQLSTATE and message token list (each token is separated by the vertical bar character) are provided. The message tokens might be truncated. See the corresponding message for the sqlcode for further explanation of the error.

User response

Check the message associated with the SQLCODE of the SQL statement that failed. Follow the action suggested by that message.

sqlcode: -20508

sqlstate: 5UA03

SQL20509N The statement was not processed because the specified alias cannot be used as the target of the statement. Specified alias: `alias-name`.

Explanation

When you create a new database object, you specify a name for the new object with the CREATE statement. After the object is created, you can also define one or more aliases for that object.

This message is returned when an alias is specified as the target of a statement when the name of the object must be specified instead of an alias.

User response

Resubmit the SQL statement, specifying the name of the object to which the alias alias-name refers instead of specifying the alias.

sqlcode: -20509

sqlstate: 560CT

SQL20510N Invalid context of use for a compound SQL (compiled) statement.

Explanation

Compound SQL (compiled) statements can be used in contexts including the following:

As a standalone statement
As the body of a SQL procedure
As the body of a SQL scalar function
As the body of a trigger when the trigger is not defined using the:
- FOR EACH STATEMENT clause
- REFERENCING OLD TABLE clause
- REFERENCING NEW TABLE clause

User response

Do one of the following and try the request again:

Remove the invalid compound SQL (compiled) statement.
Replace the invalid compound SQL g(compiled) statement with a compound SQL (inlined) statement.
If used within a trigger definition, modify the trigger definition so that the clause that restricts the use of the compound SQL (compiled) statement is removed.

sqlcode: -20510

sqlstate: 429C6

SQL20511N The attempt to put data into the message buffer failed because there is not enough available space in the message buffer. Message buffer name: `buffer-name`.

Explanation

The attempt to put data into the message buffer failed because the available free space in the buffer is not large enough for the data.

User response

Respond to this message in one of the following ways:

For the DBMS_OUTPUT buffer, take one of the following actions:
- Call the DBMS_OUTPUT.GET_LINE procedure or DBMS_OUTPUT.GET_LINES procedure to retrieve the data from the local message buffer to free up space.
- Increase the size of the buffer using the DBMS_OUTPUT.ENABLE procedure.
For the DBMS_PIPE buffer, invoke DBMS_OUTPUT.SEND_MESSAGE function to send the content in the message buffer through the pipe.
For the UTL_TCP.READ_LINE buffer, decrease the amount of data per line transmitted by the sender.

sqlcode: -20511

sqlstate: 5UA0P

SQL20512N No alert has been registered previously with the DBMS_ALERT.REGISTER procedure.

Explanation

No alert has been registered previously with the DBMS_ALERT.REGISTER procedure for the current session.

User response

Call the DBMS_ALERT.REGISTER procedure to register an alert.

sqlcode: -20512

sqlstate: 5UA04

SQL20513N The UTL_FILE procedure `procedure-name` failed to delete or rename the file `file-name`. Operating system error = `error-text`.

Explanation

The procedure procedure-name in module UTL_FILE failed to either delete or rename the specified file, file-name, and received the error error-text from the operating system.

User response

The system error error-text provides more details on the error situation and an appropriate response might be: Verify that the arguments of the procedure are valid. Verify that the file file-name exists and has the appropriate attributes. If renaming the file file-name, verify that the target file does not exist or set the OVERRIDE parameter to TRUE.

sqlcode: -20513

sqlstate: 5UA0C, 5UA0D

SQL20514N A UTL_SMTP module routine encountered an SMTP server error. SMTP error code = `error-code`.

Explanation

A UTL_SMTP module routine encountered either a transient or permanent SMTP server error.

User response

The error code error-code provides more details on the specific SMTP error encountered. If the error code value is in the range 400 to 499, the error is a transient error and the same request may be successful at a later time. If the error code value is in the range 500 to 599, the error is a permanent error and the exact error code can help to determine how to resolve the error situation. You might need to contact the person responsible for the SMTP server to have them fix the error with the SMTP server or you might choose a different SMTP server.

sqlcode: -20514

sqlstate: 5UA0E, 5UA0F

SQL20515N A dynamic statement name cannot be used in the cursor value constructor.

Explanation

A dynamic statement name is specified in a cursor value constructor that:

also specifies one or more named parameters in a cursor value constructor parameter list.
is assigned to a variable with a strongly typed cursor data type.

The statement cannot be processed.

User response

Do one of the following and try the request again:

Replace the dynamic statement name with a SELECT statement.
Remove the parameter list from the cursor value constructor.
Change the cursor variable to a weakly typed cursor data type.

sqlcode: -20515

sqlstate: 428HU

SQL20516W The statement compilation was successful but the access plan for this statement could not be preserved. Reason code = `reason-code`.

Explanation

The statement was compiled successfully during a BIND, REBIND, or PRECOMPILE, but the previous access plan could not be preserved. The reason codes are as follows:

101: The package was last bound or rebound in a version prior to DB2 Version 9.7. The access plan of a section generated by a version prior to DB2 Version 9.7 cannot be preserved.
102: The package was last bound or rebound in a version of the database product that is previous to the version installed, but access plans from that previous version cannot be preserved in the version installed.
103: The package was last bound or rebound in a version of the database product later than the version installed, but access plans from later versions might not be able to be preserved in earlier versions. This can occur when the installed version of the database manager is reverted back to an earlier fix pack level or version.
104: A section within the package is not recognized as a valid section.
105: The query could not be found in the existing package. New statements may have been introduced, the statement text may have changed or new host variables may have been created in the source file.
106: The internal representation of the query changed in such a way that the previous access plan is no longer applicable. Examples of changes that might have occurred are: changes to referenced database objects, changes to the database manager's configuration, and changes to the SQL query compiler.
107: The query compiler could no longer produce the same access plan due to changes in the circumstances under which a query is compiled. Examples of this are: the previous access plan accesses an index which no longer exists, or the query is being compiled at a different optimization level than before and the new optimization level does not allow the access plan strategy that was previously chosen.

User response

In most situations, you can choose to ignore this warning because the failure to preserve the previous access plan can be the result of normal and expected database object changes or database configuration changes. In some circumstances, you might want to monitor the performance of the queries or use the explain facility to examine the access plans and compare them to the access plans generated in the past for the same query. You can then follow accepted query performance tuning and monitoring procedures to verify that the query performs well, or if necessary, improve the performance. To avoid having this warning returned, take one of the following actions based on the reason code:

101: Bind or rebind the package with a version of at least DB2 Version 9.7 before enabling the access plan reuse option of the BIND, REBIND, or PRECOMPILE command.
102: Bind or rebind the package with the installed version before enabling the access plan reuse option of the BIND, REBIND, or PRECOMPILE command.
103: Bind or rebind the package with the installed version or a version whose access plans are supported for access plan reuse by the installed version before enabling the access plan reuse option of the BIND, REBIND, or PRECOMPILE command.
104: To report this unexpected result, contact IBM software support and provide the db2diag diagnostic log files and the contents of the diagnostic directory path (as specified by the DIAGPATH database manager configuration parameter).
105: If preserving existing access plans for this query is essential, do not reformat or change the queries, or refer to new host variables. Alternatively you can confirm that the new access plan is satisfactory.
106: If preserving existing access plans for this query is essential, do not change referenced database objects or the database manager configuration (in some cases, an access plan cannot be reused because of changes to the database manager code). Alternatively you can follow accepted query performance tuning and monitoring procedures to verify that the query performs as expected.
107: If preserving existing access plans for this query is essential, do not change referenced database objects, the database manager configuration, or optimization options. Alternatively you can follow accepted query performance tuning and monitoring procedures to verify the query performs as expected or use the explain facility diagnostics to examine why the previous access plan could not be preserved.

sqlcode: +20516

sqlstate: 01602

SQL20518N The operation is invalid because the UTL_SMTP module routine `routine_name` is called out of sequence.

Explanation

The SMTP protocol requires that the operations be performed in a particular order. The routine routine-name was called but the SMTP protocol requires that another operation be performed before the operation this routine was attempting to perform can complete successfully. For example, the UTL_SMTP.WRITE_DATA routine must be called after the UTL_SMTP.OPEN_DATA routine completes successfully, and the UTL_SMTP.RCPT routine must be called after the UTL_SMTP.MAIL routine completes successfully.

User response

Refer to the SMTP protocol and ensure that the UTL_SMTP module routines are called in the correct order.

sqlcode: -20518

sqlstate: 5UA0N

SQL20519N There is no data in the local message buffer to unpack.

Explanation

You can call one of the DBMS_PIPE.UNPACK_MESSAGE procedures to receive the next data item from the local message buffer and assign that data to a variable. This message is returned when there is no more data in the local message buffer to receive.

User response

Change the application logic to check if DBMS_PIPE.NEXT_TYPE() returns a non-zero value before calling one of the DBMS_PIPE.UNPACK_MESSAGE procedures.

sqlcode: -20519

sqlstate: 55019

SQL20521N Error occurred processing a conditional compilation directive near `string`. Reason code=`rc`.

Explanation

The SQL compiler was processing a statement that included conditional compilation directives. An error occurred at or near the statement text given in the token string. Possible reasons are given by the reason code.

1: A global variable reference in a selection directive is not a valid data type. Valid data types are BOOLEAN, INTEGER, or VARCHAR.
2: A global variable reference in a selection directive is not defined as a constant.
3: A global variable reference in a selection directive is defined as a constant, but requires the evaluation of an expression.
4: A constant was found that is not a BOOLEAN, INTEGER, or VARCHAR constant.
5: The search condition specifies an unsupported expression or predicate.
6: An invalid conditional compilation directive was specified. A single underscore character (or dollar character) is used as a prefix, but the characters that follow do not match a supported conditional compilation directive.
7: A conditional compilation directive was specified in a context where conditional compilation directives are not supported.
8: A new selection directive is specified in the code fragment of a selection directive. Nesting of selection directives is not supported.
9: Keywords for a selection directive are incorrectly specified. A required keyword is missing or a keyword is used in the incorrect order.
10: An inquiry directive could not be processed because the value of the SQL_CCFLAGS database configuration parameter is not valid.

User response

Correct the error based on the reason code.

1

Change or remove the global variable reference from the selection directive or replace the global variable so that it has a supported data type.

2

Change or remove the global variable reference from the selection directive or replace the global variable so that it is defined using a CONSTANT clause.

3

Change or remove the global variable reference from the selection directive or replace the global variable so that it is defined using a CONSTANT clause with a simple literal value.

4

Replace the constant with a BOOLEAN, INTEGER, or VARCHAR constant.

5

Ensure that there are no expressions in the search condition and that only basic predicates or NULL predicates are used. Remove any unsupported expressions or predicates.

6

Ensure that the conditional compilation prefix character is used only with supported directives. If an inquiry directive was intended, the prefix character must be doubled. If use of conditional compilation was not intended, an identifier must be delimited with double quotation mark characters or have the underscore character (or dollar character) removed.

7

Ensure that the context in which the compilation directive is used is valid. Valid contexts include: SQL procedure definitions, compiled SQL function definitions, compiled trigger definitions, and PL/SQL package definitions.

8

Ensure that no selection directive is started before an already started selection directive is ended. Use separate selection directives without nesting, or make use of additional _ELSEIF or _ELSE blocks to determine the code fragments that are to be selected for compilation.

9

Check the syntax for the selection directive to ensure that each _IF and _ELSEIF is followed by a corresponding _THEN keyword. Also, ensure that the _END keyword completes the selection directive.

10

Perform the following steps:

[Optional] Temporarily override the SQL_CCFLAGS database configuration parameter using the CURRENT SQL_CCFLAGS special register.
Set the SQL_CCFLAGS database configuration parameter to a valid value.

sqlcode: -20521

sqlstate: 428HV

SQL20522N The statement failed because of an invalid specification of the WITHOUT OVERLAPS clause. Reason code `reason-code`.

Explanation

The specification of the WITHOUT OVERLAPS clause is invalid for one of the following reasons:

1: A partitioned index must not specify BUSINESS_TIME WITHOUT OVERLAPS if the table partitioning key includes the begin column or end column of the BUSINESS_TIME period.
2: The columns of the specified period must not be specified in the constraint clause.
3: For a DB2 Database for Linux, UNIX, and Windows server, WITHOUT OVERLAPS can only be specified in a CREATE INDEX statement and only if the index is defined as UNIQUE. For a DB2 for z/OS, WITHOUT OVERLAPS can only be specified in a CREATE or ALTER INDEX statement if the index is defined as UNIQUE or UNIQUE WHERE NOT NULL.
4: A unique index must not specify BUSINESS_TIME WITHOUT OVERLAPS if the table distribution key includes the begin column or end column of the BUSINESS_TIME period.
5: The columns of the specified period must not be specified in the index specification.

The statement cannot be processed.

User response

Correct the syntax and resubmit the statement.

sqlcode: -20522

sqlstate: 428HW

SQL20523N Table `table-name` was specified as a history table, but the table definition is not valid for a history table. Reason code `reason-code`.

Explanation

For a table specified as a history table in a CREATE or ALTER statement, the table definition is invalid for the reason code indicated:

1

The table must not be an existing system-period temporal table, history table, declared global temporary table, created global temporary table, materialized query table, typed table, or view. For a DB2 for z/OS server, the table must also not be an auxiliary table, clone table, table with a clone defined on it, or a table that was implicitly created for an XML column.

2

For a DB2 for z/OS server, the table must not have an incomplete table definition.

3

For a DB2 for z/OS server, the table must be the only table in the table space.

4

The table must not contain an identity column, row change timestamp column, row-begin column, row-end column, transaction-start-ID column, or generated expression column.

5

The table must not include a period definition.

6

The table must not be involved in any referential integrity constraints.

7

The history table must have the same number and order of columns as the table used as the system-period temporal table.

8

The table must not contain a security label column or have a security policy associated with it.

9

For a DB2 for z/OS server, if a column of the system-period temporal table is defined as ROWID, then the corresponding history column should be defined as ROWID with the same generation attribute (GENERATED ALWAYS or GENERATED BY DEFAULT).

10

The corresponding columns of the system-period temporal table and associated history table must have these same attributes:

name
data type
length (including inline LOB length, precision, and scale)
null attribute
hidden attribute
field procedure (DB2 for z/OS only)
subtype (FOR BIT, SBCS, or MIXED DATA attribute) and CCSID

11

For a DB2 for z/OS server, the table must not have a column mask or row permission defined on it.

The statement cannot be processed.

User response

Correct the syntax and resubmit the statement.

sqlcode: -20523

sqlstate: 428HX

SQL20524N The statement failed because of an invalid period specification or period clause for period `period-name`. Reason code `reason-code`.

Explanation

A period specification or period clause is invalid for the reason code indicated:

1

The period name was specified more than once for the table reference.

2

The SYSTEM_TIME period was specified, but the table is not a system-period temporal table.

3

The specified expression is unsupported for one of the following reasons:

The expression contains an invalid operand
The expression returns an unsupported data type

4

For a DB2 for LUW server, the period specification was specified with a view where the view definition includes a compiled function or external function with a data indication other than NO SQL.

5

For a DB2 for z/OS server, an expression must not include a time zone and must not have a precision greater than the precision of the columns of the period.

6

FOR SYSTEM_TIME was specified but the value of the CURRENT TEMPORAL SYSTEM_TIME special register is not null and the SYSTIMESENSITIVE bind option is set to YES.

7

FOR BUSINESS_TIME was specified but the value of the CURRENT TEMPORAL BUSINESS_TIME special register is not null and the BUSTIMESENSITIVE bind option is set to YES.

8

The period clause was specified for a table that is not an application-period temporal table, or for a view for which an application-period temporal table is not referenced in the outermost FROM clause of the view definition, or an instead of trigger is defined on the view.

9

A period specification or period clause was specified for a nickname or a remote object.

The statement cannot be processed.

User response

Correct the syntax and resubmit the statement.

sqlcode: -20524

sqlstate: 428HY

SQL20525N The requested action is not valid for table `table-name` because the table is of the wrong type. Reason code `reason-code`.

Explanation

The table cannot be used as specified for the reason code indicated:

1: An ALTER TABLE statement was specified with an ADD PERIOD clause, but the table is a history table and a period cannot be defined for a history table.
2: An ALTER TABLE statement was specified with a DROP PERIOD clause, but the table is a system-period temporal table and the period cannot be dropped.
3: An ALTER TABLE statement was specified with an ALTER VERSIONING clause, but the table is not a system-period temporal table.
4: An ALTER TABLE statement was specified with a DROP VERSIONING clause, but the table is not a system-period temporal table.
5: An ALTER TABLE statement was specified with a DROP VERSIONING clause with the RESTRICT keyword, but system data versioning cannot be dropped if there are any triggers, functions, procedures, views, or materialized query tables that reference historical versions of rows in the table.
6: An ALTER TABLE statement was specified with a DROP PARTITION clause for a system-period temporal table or history table.
7: For DB2 for z/OS, an ALTER TABLE statement was specified with a ROTATE PARTITION clause for a system-period temporal table or h istory table.
8: For DB2 for z/OS, an ALTER TABLE statement was specified with an ADD CLONE clause was specified for a table that is a system-period temporal table or a history table.
9: A TRUNCATE statement attempted to truncate a table, but the table is a system-period temporal table.
10: For DB2 for z/OS, an ALTER TABLESPACE statement attempted to alter the CCSID of a table space that contains a system-period temporal table or history table.
11: A CREATE or ALTER TABLE statement attempted to define a referential constraint where either the parent or child table is a history table.
12: For DB2 for Linux, UNIX, and Windows, an ALTER TABLE statement with a DETACH PARTITION clause was specified for a system-period temporal table.
13: An ALTER TABLE statement with an ADD COLUMN clause was specified for a history table.
16: An ALTER TABLE statement was specified to add a generated column to a system-period temporal table.
17: For DB2 for Linux, UNIX, and Windows, an ALTER TABLE statement was specified with ACTIVATE NOT LOGGED INITIALLY for a system-period temporal table or a history table.
18: For DB2 for Linux, UNIX, and Windows, an ALTER TABLE statement was specified with ADD SECURITY POLICY for a system-period temporal table or a history table.

User response

Change the statement to specify the name of table for which the action is valid, or specify a different action for the table.

sqlcode: -20525

sqlstate: 428HZ

SQL20526N The variable `variable-name` is the target of two or more assignments with no defined order of assignment.

Explanation

A variable is the target of two or more assignments with no defined order of assignment. Multiple assignments of a variable can occur when a variable on the left hand side of a SET variable statement is also used as an output parameter to a function on the right hand side of the SET variable statement.

In the following example, assume that the function my_function was declared with one OUT parameter. The variable called my_variable is the target of two assignments in the example:

CREATE VARIABLE my_variable INTEGER;

SET my_variable = my_function( my_variable );

User response

Change one or more of the variable references to a different variable.

sqlcode: -20526

sqlstate: 42810

SQL20527N The statement failed because referenced period `period-name` is not a period in table `table-name`

Explanation

No period with the specified period-name occurs in the table table-name.

The statement cannot be processed.

User response

Verify that the period and table names are specified correctly (including any required qualifiers) in the SQL statement. Resubmit the statement.

sqlcode: -20527

sqlstate: 4274M

SQL20528N The target of the data change operation is a table `table-name`, which includes a period `period-name`. The data change operation failed because a row that it attempted to modify was also modified by another transaction.

Explanation

Table table-name is a system-period temporal table. The table includes a period period-name. The requested data change operation attempted to change a row that has been modified by another transaction, and would result in a situation where the row-begin column value of the associated row in the history table is greater than the end column value. This can occur for one of the following reasons:

Another transaction updated or inserted a row after the transaction of the failing statement had started. This resulted in a later timestamp value in the row begin column than the failing statement would use when it updated or deleted that same row.
Data has been loaded into the system-period temporal table with values for the row begin column that override the generated values. The row begin column value that was loaded is in the future relative to the timestamp that the failing transaction would use.

The requested operation cannot be performed.

User response

Retry the statement. The systime_period_adj configuration parameter can be set to allow the affected values to be adjusted so that they are unique. Contact the system administrator.

sqlcode: -20528

sqlstate: 57062

SQL20529N The argument to the WRAP function or to the CREATE_WRAPPED procedure is not valid.

Explanation

The argument to the WRAP function or to the CREATE_WRAPPED procedure is not a valid SQL PL or PL/SQL statement.

User response

Ensure that the argument to the WRAP function or to the CREATE_WRAPPED procedure is valid. If the statement is already wrapped by an unsupported application server, it cannot be processed using this version of DB2.

sqlcode: -20529

sqlstate: 5UA0O

SQL20530N An obfuscated statement is not valid. Reason code= `rc`.

Explanation

An error occurred processing a data definition statement that includes the WRAPPED clause. Possible reasons are:

1: The statement was wrapped on a platform or version that is not supported.
2: The obfuscated portion of the statement is corrupted.

User response

Ensure that the statement was wrapped on a supported platform, and that the statement is not corrupted.

sqlcode: -20530

sqlstate: 42638

SQL20531N The version number `flowed-version` specified in a binary XML value is not supported. The highest supported version is `highest-version`.

Explanation

The version specified does not support binary XML format. A value of 0 (zero) for the highest supported version indicates that the platform does not support binary XML format. A value of *N for the version number specified indicates that the version cannot be determined, because the server could not read binary XML format.

User response

Use a server that supports binary XML format.

sqlcode: -20531

sqlstate: 22544

SQL20532N The command or API function call failed because the command or API function is discontinued. Command or API function name: `command-or-function-name`.

Explanation

This message is returned when an attempt is made to run a command or call an API function that has been discontinued.

User response

Use other commands or API functions to accomplish the same task.

Update automated scripts and applications to use different commands or API functions to accomplish the same task.

sqlcode: -20532

sqlstate: 560CZ

SQL20533N The SELECT statement failed because an unsupported data type was specified in the typed-correlation clause. Column with unsupported data type: `column-name`.

Explanation

The typed-correlation clause in the sub-select of a SELECT statement is used to define the appearance and contents of the table generated by a generic table function.

This message is returned when the data type specified in the typed-correlation clause is not supported.

User response

Execute the SELECT statement again, specifying only supported data types in the typed-correlation clause.

sqlcode: -20533

sqlstate: 429BB

SQL20534W The schema `schema-name` includes one or more tables with the attribute `data-capture-option1` which is different from the schema attribute `data-capture-option2`.

Explanation

The DATA CAPTURE setting for one or more tables in the schema are different from the schema-level setting.

You can set different DATA CAPTURE attribute at the table and schema levels because the setting of the DATA CAPTURE attribute for a table is independent of the setting at the schema level.

After the DATA CAPTURE attribute is set at the schema level, newly created tables inherit the schema-level setting if one is not specified at the table level.

User response

To find the list of tables that have DATA CAPTURE attribute set to CHANGES, issue the following query:

SELECT TABNAME, TABSCHEMA FROM SYSCAT.TABLES
  WHERE TYPE IN ('T','S','L') 
    AND DATACAPTURE <> 'N'

To find the list of tables that have DATA CAPTURE attribute set to NONE, issue the following query:

SELECT TABNAME, TABSCHEMA FROM SYSCAT.TABLES
  WHERE TYPE IN ('T','S','L')
    AND DATACAPTURE = 'N'

sqlcode: +20534

sqlstate: 01696

SQL20535N The data change operation `operation` is not supported for the target object `object-name` because of an implicit or explicit period specification involving `period-name`. Reason code: `reason-code`.

Explanation

The data change operation is not supported because the target of the operation references a temporal table and a period specification was specified. The period specification was either implicitly specified by using a special register, or explicitly specified in the fullselect specified as the target. More information is provided by the reason code indicated:

1

The CURRENT TEMPORAL SYSTEM_TIME special register contains a non-null value and the target of the data change operation is a system-period temporal table (directly or indirectly). The data in a system-period temporal table cannot be changed when a period specification is in effect. The target of the data change statement is one of the following:

a system-period temporal table
a view that is defined with an outer fullselect that references a system-period temporal table in the FROM clause (directly or indirectly) and that does not have an INSTEAD OF trigger defined for the data change operation
a fullselect that references a system-period temporal table in the FROM clause (directly or indirectly)

2

The CURRENT TEMPORAL SYSTEM_TIME special register contains a non-null value and the target of the data change statement is a view defined with the WITH CHECK OPTION. The data change statement cannot be processed because the view definition includes a WHERE clause containing one of the following syntax elements:

a subquery that references a system-period temporal table (directly or indirectly)
an invocation of an SQL routine that has a package associated with it
an invocation of an external routine with data access indication other than NO SQL

3

The target of the data change statement is specified as a fullselect that references a view in the FROM clause which is followed by a period specification for SYSTEM_TIME. The referenced view is defined with the WITH CHECK OPTION. The data change statement cannot be processed because the view definition includes a WHERE clause containing one of the following syntax elements:

a subquery that references a system-period temporal table (directly or indirectly)
an invocation of an SQL routine that has a package associated with it
an invocation of an external routine with data access indication other than NO SQL

4

The CURRENT TEMPORAL BUSINESS_TIME special register contains a non-null value and the target of the data change statement is a view defined with the WITH CHECK option. The data change statement cannot be processed because the view definition includes a WHERE clause containing one of the following syntax elements:

a subquery that references an application-period temporal table (directly or indirectly)
an invocation of an SQL routine that has a package associated with it
an invocation of an external routine with data access indication other than NO SQL

5

The target of the data change statement is specified as a fullselect that references a view in the FROM clause which is followed by a period specification for BUSINESS_TIME. The referenced view is defined with the WITH CHECK OPTION. The data change statement cannot be processed because the view definition includes a WHERE clause containing one of the following syntax elements:

a subquery that references an application-period temporal table (directly or indirectly)
an invocation of an SQL routine that has a package associated with it
an invocation of an external routine with data access indication other than NO SQL

The statement cannot be processed.

User response

Take an appropriate action for the reason-code:

1: Set the CURRENT TEMPORAL SYSTEM_TIME special register to the null value and try the data change operation again. If the statement is included in an application package that should not be sensitive to the setting of the CURRENT TEMPORAL SYSTEM_TIME special register, then bind the package using SYSTIMESENSITIVE NO.
2: Set the CURRENT TEMPORAL SYSTEM_TIME special register to the null value and try the data change operation again. If the statement is included in an application package that should not be sensitive to the setting of the CURRENT TEMPORAL SYSTEM_TIME special register, then bind the package using SYSTIMESENSITIVE NO. Another possible alternative could be to replace the reference to the view with another view that is defined without the WITH CHECK OPTION if the associated checking of data changes is not required.
3: Remove the period specification in the target fullselect of the data change operation. Another possible alternative could be to replace the reference to the view with another view that is defined without the WITH CHECK OPTION if the associated checking of data changes is not required.
4: Set the CURRENT TEMPORAL BUSINESS_TIME special register to the null value and try the data change operation again. If the statement is included in an application package that should not be sensitive to the setting of the CURRENT TEMPORAL BUSINESS_TIME special register, then bind the package using BUSTIMESENSITIVE NO.
5: Remove the period specification in the target fullselect of the data change operation and use explicit predicates in the WHERE clause of the fullselect to specify the target rows for the data change operation.

sqlcode: -20535

sqlstate: 51046

SQL20536N The operation cannot be processed because it involves a text index. Reason code = `reason-code`.

Explanation

The operation either targets a text index directly or has a text index that depends on the result of the operation. The operation cannot proceed because of the text index. The specific reason is indicated by the reason code.

1: A text index exists on the table that the operation requested to drop.
2: A text index is the target of a RUNSTATS command.
3: A text index is the target of a REORG command.
4: A text index exists on the nickname that the operation requested to drop.

User response

1: If the table must be dropped, then first drop the the text indexes on the table.
2: Remove the reference to the text index from the RUNSTATS command. Statistics cannot be collected on text indexes.
3: Remove the reference to the text index from the REORG command. Text indexes cannot be reorganized.
4: If the nickname must be dropped, then first drop the the text indexes on the nickname.

SQL20537W The precompiler detected a combination of arrays with different cardinalities in the same SQL statement. The application was compiled to populate all arrays in the applicable statement with the minimum cardinality: `minimum_cardinality`

Explanation

The COMPATIBILITY_MODE ORA precompiler option allows the use of host variable arrays, indicator arrays, or a structure array in an embedded SQL statement. When multiple arrays that are declared with different cardinalities are used in the same SQL statement, all the arrays are precompiled with the same cardinality.

This warning message is returned when the COMPATIBILITY_MODE ORA precompile option is specified and there are arrays in an SQL statement that are declared with different cardinalities. The statement was successfully compiled, but all the arrays were precompiled with the same cardinality.

User response

To avoid this warning during the precompile process, use the same cardinality for all arrays that are used in the same SQL statement.

sqlcode: +20537

sqlstate: 01697

SQL20538W A permission or mask was changed for the table named `table-name`. This change might require a change to the permissions or masks of a materialized query table (MQT) based on this table to maintain the security of the data.

Explanation

A permission or mask was created, altered, or dropped for table table-name. At least one MQT is based on this table that has access controlled using masks or permissions, other than the default permission. The MQT includes data from the base table table-name. In order to maintain data security, the access control change made to the base table of the MQT must be reflected in the permissions and masks of the MQT.

User response

Verify the permissions and masks of each MQT that is based on table table-name is consistent with the access control on the base table table-name.

sqlcode: +20538

sqlstate: 01698

SQL20539N The query failed because a negative value or the null value is used in the clause keywords.

Explanation

A query can include a FETCH FIRST clause or a LIMIT clause to control the number of rows that are returned from a query. A query can also include an OFFSET clause to begin fetching rows starting at a specified row number. The row count value for these clauses can be specified as an expression that is evaluated at query open time and must result in a positive numeric constant value for the query.

The expression specified for the indicated clause returns a negative value or the null value.

User response

Change the expression so that it returns a positive number and reissue the query.

sqlcode: -20539

sqlstate: 2201W, 2201X

SQL20540N An autonomous transaction executing a procedure has been terminated abnormally. Routine name: `routine-name`. Specific name: `specific-name`. Reason code: `reason-code`.

Explanation

A procedure can be defined as AUTONOMOUS, to make its execution occur within a separate autonomous transaction. The execution of the procedure may encounter an error that required the procedure to terminate and the autonomous transaction to be rolled back without rolling back the parent or calling transaction. This message is returned when the autonomous procedure terminated with an error that normally causes a transaction to roll back. In this case, the autonomous transaction for the autonomous procedure is rolled back but without affecting the calling transaction. Possible reason codes for this message are the SQLCODEs that terminated the autonomous procedure.

User response

Check the message associated with the SQLCODE in the reason code and use the information there to determine the reason that the autonomous procedure was terminated. Correct any problems in the autonomous procedure or the calling application and run the procedure again.

sqlcode: -20540

sqlstate: 560D2

SQL20541N An error code was received from an in-database analytics provider. Error code: `error-code`. Provider name: `provider-name`. Associated text and tokens: `tokens`.

Explanation

You can perform in-database analytics using an embedded analytics provider.

While referencing an in-database analytics provider, an unexpected error code was received from the specified provider.

User response

Respond to this error by performing the following troubleshooting steps:

Ensure that the process for the provider is available.
Identify and correct the root cause of the problem by locating the appropriate message text and corrective action for the specified error at the provider.
Check any appropriate diagnostic logs that are generated by the provider.

sqlcode: -20541

sqlstate: 560D4

SQL20542N The statement was not executed because the connection to the database server was dropped, and the automatic client reroute (ACR) feature failed to successfully re-execute the statement.

Explanation

The ACR feature attempts to reconnect to the database when a connection to a database server is dropped. This message is returned when the ACR feature is unable to seamlessly re-execute the statement after a successful reconnect.

When this message is returned, the database connection is in an open state.

User response

Execute the statement again.

If the error persists, perform the following troubleshooting steps:

Collect information from diagnostic logs on the client and the server.
Investigate why the connection to the database server is being lost.

sqlcode: -20542

sqlstate: 54068

SQL20544N The statement failed because an error was encountered while the ANALYZE_TABLE expression was being processed. Analytics provider name: `provider-name`. Reason code: `reason-code`.

Explanation

You can use the ANALYZE_TABLE expression to invoke an analytics routine from the provider. The routine performs analytics processing based on the input table and returns a set of rows as output. Details about routine implementation, such as information about the analytics provider, the name of the routine to use, and the table in which the routine source is located, are specified in an implementation string. The IMPLEMENTATION parameter passes the implementation string to the ANALYZE_TABLE expression.

This message is returned when the specified routine cannot be found or loaded for the ANALYZE_TABLE expression. In general, this message is returned when there is a problem with the routine source table or with the routine source. The reason code indicates the cause of the problem more specifically:

1

The format or content of the implementation string is not valid. It must begin with PROVIDER=xxx, where xxx identifies a supported provider. This must be followed by name=value pairs for the specified provider, each pair ending with a semicolon.

2

The specified routine source table does not contain the minimum required columns, correctly defined for the provider. If PROVIDER=SAS is specified, there are four minimum required columns:

"MODELNAME"
"MODELDS2"
"MODELFORMATS"
"MODELMETADATA"

3

The specified routine source table is not a base table. The name of the routine source table must not identify a global temporary table, a view, a common table expression, or a nickname.

4

The combined, total length of a set of the columns is too long in a row of the specified routine source table. If PROVIDER=SAS is specified, the columns in the set are named "MODELDS2", "MODELFORMATS", and "MODELMETADATA".

5

In one or more rows of the specified routine source table, the contents of one or more of the columns are empty, but not null. If PROVIDER=SAS is specified, it could be the column named "MODELDS2".

6

The specified routine source name could not be found in the specified routine source table.

7

The routine source table does not include a required primary key constraint. If PROVIDER=SAS is specified, the MODELNAME column must be defined as the primary key.

8

The specified provider is not supported.

User response

Respond to this message according to the reason code:

1: Specify a correctly formatted implementation string with content that is required by the specified provider.
2: Modify the definition of the routine source table so that the table contains the minimum required columns.
3: Correct the implementation string to specify a routine source table name that identifies a base table.
4: Modify the definition of the routine source table so that the combined total length of the set of columns does not exceed the maximum length permitted in a routine source table.
5: Review the contents of the columns that cannot have empty string values in all rows of the routine source table. If PROVIDER=SAS is specified, correct any rows that contain empty (zero-length) strings in the column named "MODELDS2".
6: Correct the implementation string to specify a valid routine source table and routine source name.
7: Modify the routine source table to include the required primary key constraint.
8: Specify a supported provider in the implementation string.

sqlcode: -20544

sqlstate: 5507A

SQL20545N The statement failed because a connection to the in-database analytics provider could not be established or maintained. Analytics provider name: `provider-name`. Reason code: `reason-code`.

Explanation

You can perform in-database analytics using an embedded analytics provider. To successfully perform analytics using an embedded provider, the database manager must establish and maintain a connection with the provider.

This message is returned when the database manager encounters an error while attempting to communicate with an embedded provider. The reason code indicates the source of the problem:

1: The embedded provider process is not up and running.
2: An attempt to connect to the embedded provider timed out.
3: The database manager is unable to allocate enough shared memory from the FMP memory set to communicate with the embedded provider.

User response

In general, respond to this message by taking the following actions:

Collect diagnostic information from the db2diag log files.
Troubleshoot the installation and configuration of the embedded analytics provider.

More specifically, when the provider name is 'SAS', respond to this message according to the reason code:

1

Respond to reason code 1 according to the value of the ENABLE_SAS_EP parameter of the DB2_SAS_SETTINGS registry variable:

If ENABLE_SAS_EP is set to TRUE, wait for the instance to automatically restart the embedded process. If the process does not restart after approximately 10 seconds, collect the following diagnostic information and troubleshoot the embedded analytics provider:
- The values of the parameters of the DB2_SAS_SETTINGS registry variable.
- Related information in the db2diag diagnostic log files.
If ENABLE_SAS_EP is not set or if ENABLE_SAS_EP is set to FALSE, manually start up the embedded process by running the db2ida command.

2

Respond to reason code 2 by performing the following steps:

Wait until the embedded process is restarted.
Rerun the query.
If the same error occurs again, collect the following diagnostic information and troubleshoot the embedded analytics provider:
- The values of the parameters of the DB2_SAS_SETTINGS registry variable.
- Related information in the db2diag diagnostic log files.

3

Respond to reason code 3 by performing one of the following actions:

Wait for some queries to finish processing and then rerun the failed query.
Increase the amount of memory in the FMP memory set by increasing the DB2_FMP_COMM_HEAPSZ registry variable.

sqlcode: -20545

sqlstate: 57065

SQL20546N The statement cannot be processed because it violates one of the restrictions for using the ANALYZE_TABLE expression.

Explanation

This message is returned when an attempt is made to run a statement that uses the ANALYZE_TABLE expression in a way that is not supported.

User response

Rewrite the application so that none of the ANALYZE_TABLE expression restrictions is violated.

sqlcode: -20546

sqlstate: 429CA

SQL20547N The statement failed because the target of an assignment is a read-only variable. Variable name: `variable-name`.

Explanation

Global variables can generally be used as the target of an assignment in various SQL statement with the appropriate write access. However, the definition of some global variables makes them read only. This applies to some built-in global variables and also to user-defined variables that are defined using the CONSTANT clause,

A variable that is read only cannot be used in an SQL statement as the target of an assignment.

User response

Change the name of the target variable so that it does not refer to a read-only variable.

sqlcode: -20547

sqlstate: 428I3

SQL20548N The statement failed because a column of the input table of an ANALYZE_TABLE expression has an unsupported data type. Unsupported type: `type-name`.

Explanation

The input table of an ANALYZE_TABLE expression can be specified using a table name, a view name or the name of a common table expression specified at the beginning of the query. This message is returned when a column of the specified input table is of a type that is not supported.

User response

Respond to this error in one of the following ways:

Change the input table to remove the column with the unsupported data type or cast the column to a valid data type.
If the input table is specified with a table name or a view name, a column can be removed or cast to another data type by specifying a common table expression at the beginning of the query and using the name of the common table expression as the input table to the ANALYZE_TABLE expression.

sqlcode: -20548

sqlstate: 428H2

SQL20549N The statement cannot be precompiled because an indicator variable array contains fewer elements than its the corresponding host variable array. Indicator variable array name: `var_name1`. Required array size: `cardinality_of_array2`.

Explanation

An indicator variable contains a small integer value that indicates some information about an associated host variable. For example you can use an indicator variable to indicate that the associated host variable contains a null value. In embedded SQL applications, when parameters of type array are passed to CALL statements, information about the contents of those arrays can be stored in corresponding indicator variable arrays. When an indicator variable array is specified for an array parameter, the size of the indicator variable array must be greater than or equal to the size of the corresponding host variable array.

User response

Modify the embedded SQL application to declare any indicator variable array as the same size as, or larger than, the associated host variable array size.
Recompile and rerun the embedded SQL application.

SQL20551N Constructing an associative array failed because the input data includes at least one duplicate array index value. Duplicated index value: `value`.

Explanation

In an associative array, each element is associated with a unique index value. When constructing an array using an ARRAY_AGG aggregate function, if more than one element is associated with the same array index value, only one of the element values can be included in the array. This potential loss of element data is the reason that index values must be unique within the input data used to construct an associative array.

User response

Change the input data so that there are no duplicate array index values. When using an ARRAY_AGG aggregate function, this might require adding predicates to the query that produces the aggregation grouping sets to ensure that none of the rows include duplicate values for the array index expression argument of the ARRAY_AGG.

sqlcode: -20551

sqlstate: 22545

SQL20552N The cast or assignment failed because the value does not conform to the data type constraint of the user-defined type. User-defined type: `type-name`. Value: `value`.

Explanation

A user-defined distinct type can include a NOT NULL constraint and a CHECK constraint that limit the valid values for the data type. These data type constraints can be violated when a value is cast to the user-defined type or a value is assigned to a variable, parameter, or column defined with that user-defined type. An assignment can also happen implicitly using the default for the variable, parameter, or column.

User response

Ensure that the values being assigned or cast are valid for the data type constraints of the user-defined type by taking actions such as the following actions:

Change the value to conform to the data type constraints.
Change the data type to a different data type. This might be most applicable when performing an explicit cast to a user-defined type.
If the invalid value is the result of using a default, change the definition of the variable, parameter, or column to use a default value that is valid for the user-defined type.
Change the definition of the user-defined type by dropping the type and creating it with different data type constraints that would allow the value.

sqlcode: -20552

sqlstate: 23528

SQL20556N The operation failed because multiple result values cannot be returned from a scalar function `function-name`.

Explanation

A scalar function was invoked with arguments that disallow the return of multiple result values for a single invocation. The processing of the scalar function determined a result that consisted of multiple values.

User response

One of the following options can be used to avoid the error:

Change the values of the input arguments to the scalar function to return a single value from the multiple value result.
If possible, as an alternative to the scalar function invocation, create a user-defined table function to return multi-valued results.

sqlcode: -20556

sqlstate: 22547

SQL20557N The statement failed because of an error in the Hive metastore. Log entry identifier: `log-entry-identifier`. Reason code: `reason-code`.

Explanation

In IBM Big SQL, you can use standard DDL and SQL to create and query tables for which data is stored in a Hadoop Distributed File System (HDFS). The DDL and SQL processing is handled by both a processing engine in Big SQL as well as the Hive metastore.

The reason code indicates the type of problem:

1: The Hive metastore encountered an error processing the DDL or SQL.
2: Data in the hive metastore is missing or incorrect. For example, a table property might be missing or contain an invalid value.

The log identifier in the token log-entry-identifier is composed of three parts:

A three-letter prefix that indicates which log file includes diagnostic details.
A three-digit number that indicates on which database partition the log file resides.
An identifier that is printed in the log file near the related diagnostic details.

User response

Locate the diagnostic details in the appropriate log file by using the information in the log-entry-identifier runtime token.
Resolve the cause of the problem.
Submit the statement again.

sqlcode: -20557

sqlstate: 58039

SQL20558N The statement failed because the regular expression string is not valid. Reason code: `reason-code`. Function name: `function-name`. Position in regular expression string: `position`. Regular expression string: `string`.

Explanation

This message is returned when the regular expression string specified as the argument of a regular expression function is not a valid regular expression. The position in the string provides and indication of where the regular expression is not correct. If the position could not be determined, a value of 0 is returned. The reason code provides additional information about the error in the pattern.

1: Syntax error in regular expression pattern.
2: Unrecognized backslash escape sequence in pattern.
3: Incorrect Unicode property name.
4: Use of regular expression feature that is not supported.
5: Incorrectly nested parentheses in regular expresion pattern.
6: Decimal number is too large.
7: Error in {min,max} interval.
8: Maximum is less than minimum in {min,max} interval.
9: Back-reference is made to a non-existent capture group.
10: Look-behind pattern matches must have a bounded maximum length.
11: Regular expressions cannot have UnicodeSets containing strings.
12: Octal character constants must be less than or equal to 0377.
13: Missing a closing bracket on a bracket expression.
14: In a character range [x-y], x is greater than y.

User response

Determine what is incorrect about the regular expression argument of the regular expression function using the position and the reason code. Invoke the function again with a valid regular expression.

sqlcode: -20558

sqlstate: 2201S

SQL20559W The analytics operation succeeded with a warning. Routine name: `routine-name`. Analytics message identifier: `message-ID`. Diagnostic details: `runtime-details`.

Explanation

You can perform analytics on data stored in databases by calling IBM in-database analytics routines:

You can build analytics models for input data
You can perform classification, clustering, regression, and other analytics operations by applying generated models
You can manage analytics meta data

This message is returned when an IBM in-database analytics routine has completed successfully, but encountered a minor error or warning at run time.

The runtime tokens provide more information about the problem:

The runtime token routine-name identifies the routine.
The token message-ID provides a unique identifier for an analytics error message that explains the problem in detail.
Depending on the nature of the problem, the token runtime-details might be empty, or might contain addition details that clarify the source of the problem.

User response

Learn the details of the problem by reading the text of the message identified by message-ID.
If necessary, take action to address the cause of the underlying error or warning and then call the routine again.

sqlcode: +20559

sqlstate: 01H58

SQL20560N The routine did not run because the formatting or content of the specified string argument is invalid. Routine name: `routine-name`. Reason code: `reason-code`. Sub-string: `sub-string`. Analytics message identifier: `message-ID`. Diagnostic details: `runtime-details`.

Explanation

The IBM in-database analytics routines take one argument: a string containing a list of parameter-value pairs, separated by a comma. This message is returned when an analytics routine is unable to successfully process a given string argument.

The runtime tokens provide more information about the problem:

The runtime token routine-name identifies the routine.
The source of the problem in the string is in or near to the sub-string shown in the runtime token sub-string.
Depending on the nature of the problem, the token message-ID might be empty, or might contain a unique identifier for an analytics error message that explains the problem in detail.
Depending on the nature of the problem, the token runtime-details might be empty, or might contain addition details that clarify the source of the problem.

The runtime token reason-code indicates more specifically the type of the problem:

1: The string argument is not in the expected format: <parameter>=<value> pairs, separated by a comma.
2: An unsupported parameter was specified.
3: A parameter was specified more than once.
4: Mutually exclusive parameters were specified.
5: Only one parameter was specified of a pair of parameters that must be specified together.
10: The value specified with a parameter is not in the expected format.
11: An unsupported value was specified with a parameter for which only one of a specific list of values may be specified.
12: A value of the wrong data type was specified with a parameter.
13: An out-of-range numeric value was specified with a parameter.
14: A string value was specified that is longer than the maximum-allowed length for that parameter value.
15: A string value was specified that contains an unsupported control character, such as a horizontal tab, line-feed, or carriage-return character.
16: An empty value was specified for a parameter.
20: In the value specified with the incolumn parameter, a column was specified more than once.
21: In the value specified with the incolumn parameter, the properties string that was specified for a column is not in the expected format.
22: In the value specified with the incolumn parameter, an unsupported property was specified for a column.
23: In the value specified with the incolumn parameter, a value of the wrong data type was specified for the weight property.

User response

Call the routine again, specifying a valid parameter string.

sqlcode: -20560

sqlstate: 22548

SQL20561N The analytics operation could not be performed because of a problem with a table. Routine name: `routine-name`. Table name: `table-name`. Analytics message identifier: `message-ID`. Diagnostic details: `runtime-details`.

Explanation

When you call IBM in-database analytics routines, you specify input tables such as:

The table containing the data upon which the analytics will be performed
A column properties table
A table indicating weighting that should be applied to instance or class data
A table containing sample data with which to validate a generated analytical model
A table containing the meta data for an analytical model

This message is returned when there is a problem with an input table that has been specified with an analytics routine.

The runtime tokens provide more information about the problem:

The runtime token routine-name identifies the routine.
The token table-name indicates which table has a problem.
The token message-ID provides a unique identifier for an analytics error message that explains the problem in detail.
Depending on the nature of the problem, the token runtime-details might be empty, or might contain addition details that clarify the source of the problem.

User response

Learn the details of the problem by reading the text of the message identified by message-ID.
Correct the table.
Call the routine again.

sqlcode: -20561

sqlstate: 22549

SQL20562N The analytics operation could not be performed because the specified options are not valid. Routine name: `routine-name`. Analytics message identifier: `message-ID`. Diagnostic details: `runtime-details`.

Explanation

You can perform analytics on data stored in databases by calling IBM in-database analytics routines. To perform analytics, you must specify options that control the behavior of the analytics, such as the following examples:

The name of an input table containing input data
A collection of analytics-related properties that are to be applied to the columns of the input table
Information about weighting to be applied to data points
Metrics to be used for scoring the quality of the generated analytics model

This message is returned when a collection of options that is unsupported, inconsistent, or otherwise invalid has been specified with an IBM in-database analytics routine.

The runtime tokens provide more information about the problem:

The runtime token routine-name identifies the routine.
The token message-ID provides a unique identifier for an analytics error message that explains the problem in detail.
Depending on the nature of the problem, the token runtime-details might be empty, or might contain addition details that clarify the source of the problem.

User response

Learn the details of the problem by reading the text of the message identified by message-ID.
Call the routine again, specifying valid options.

sqlcode: -20562

sqlstate: 22550

SQL20563N The analytics operation failed. Routine name: `routine-name`. Analytics message identifier: `message-ID`. Diagnostic details: `runtime-details`.

Explanation

You can perform analytics on data stored in databases by calling IBM in-database analytics routines:

You can build analytics models for input data
You can perform classification, clustering, regression, and other analytics operations by applying generated models
You can manage analytics meta data

This message is returned when an IBM in-database analytics routine fails because of a runtime error.

The runtime tokens provide more information about the problem:

The runtime token routine-name identifies the routine.
The token message-ID provides a unique identifier for an analytics error message that explains the problem in detail.
Depending on the nature of the problem, the token runtime-details might be empty, or might contain addition details that clarify the source of the problem.

User response

Learn the details of the problem by reading the text of the message identified by message-ID.
Resolve the problem.
Call the routine again.

sqlcode: -20563

sqlstate: 560DA

SQL20564N The analytics operation could not be performed because the authorization ID `authorization-ID` does not have the required privilege to perform the operation. Operation: `operation`. Analytics message identifier: `message-ID`. Diagnostic details: `runtime-details`.

Explanation

To call IBM in-database analytics routines requires appropriate privileges. This message is returned when a user attempts to call a routine (such as LIST_PRIVILEGES or DROP_MODEL, for example) and the authorization ID does not have the required privilege to call that routine.

User response

Call the routine as an authorization ID with the required privilege.

sqlcode: -20564

sqlstate: 42526

SQL20566N The CREATE FUNCTION or ALTER MODULE statement used to define `routine-name` failed because the identified component routine is not valid. Component routine clause: `component-routine-clause`. Specific name: `specific-name`. Reason code: `reason-code`.

Explanation

The identified component routine for the clause component-routine-clause with specific name specific-name when creating an aggregate interface function is not valid for the reason specified by the reason code as follows:

1: The data types of one or more parameters of the identified component routine are incorrect.
2: The mode of one or more parameters of the identified component routine are incorrect.
3: The data type of the return value of the identified component routine is incorrect.
4: The number of parameters of the identified component routine is incorrect.
5: One or more options of the identified component routine are incorrect.

User response

Determine what is incorrect about the identified component routine and either correct the definition of that routine or specify a different routine for that component routine that does have the valid characteristics based on the reason by the reason-code as follows.

1: Ensure the data types of all parameters of the identified component routine are correct.
2: Ensure the modes of all parameters of the identified component routine are correct.
3: Ensure the data type of the return value of the identified component routine is correct.
4: Ensure the number of parameters of the identified component routine is correct.
5: Ensure that all options of the identified component routine are correct.

sqlcode: -20566

sqlstate: 428IA

SQL20568W The table was successfully created with random distribution. However, since the distribution key that was chosen by the database manager may not guarantee unique values, the data distribution of this table might be sub-optimal. Columns in the distribution key: `column-name-list`.

Explanation

When a random distribution table is created with multiple unique or primary keys, the database manager uses the set of overlapping columns from all unique and primary keys as the distribution key. If the distribution key contains duplicate values, this could end up skewing data to a subset of the members.

User response

The distribution key that was selected by the database manager was column-name-list. If the values for these columns contain duplicate values you may experience skew with data in the table. To avoid this, recreate the table and explicitly set a different distribution key. The distribution key can be explicitly set using the DISTRIBUTE BY HASH clause.

sqlcode: +20568

sqlstate: 0169C

SQL20569N The external table operation failed due to a problem with the corresponding data file or diagnostic files. File name: `file-name`. Reason code: `reason-code`.

Explanation

The data for an external table is stored in a file that can be located locally or remotely.

This message is returned when a statement attempts to create, insert into, or select from an external table, but the corresponding operation failed due to a problem with the corresponding data file or log/bad files.

The reason code identifies the specific problem:

1: Either data file could not be reached or log/bad files could not be written.
2: The user ID that issued the statement is not authorized to access the data file.
3: The attempt to process the data file ended unexpectedly. Probably, the contents of the data file are corrupt. For example, data fields may have delimiters or nullvalue as part of data and requires an escape character.
4: The data file met with operating system level size limitations, I/O issues or insufficient memory issues.

User response

Take action that corresponds to the reason code:

1

Perform the following troubleshooting steps:

Verify that the data file was specified correctly.
Verify that the data file exists in the specified location.
For a remote data file, verify that the network connection is open.
For log/bad files, verify if filesystem has enough space.

2

Set the access permissions of the data file so that the user ID that issued the statement can read from and write to the data file.

3

Correct the field data in data file containing delimiters or nullvalue as part of data using proper escape character.

4

Make sure that the external table operation with data file is limited to operating system constraints.

sqlcode: -20569

sqlstate: 428IB

SQL20570N The statement failed because the number of rejected rows exceeds the limit set in the MaxErrors option value. Log file name: `log-file-name`.

Explanation

The data for an external table is stored in a file that can be located locally or remotely.

The data in the file might not perfectly match the column definitions of the external table. When you select data from an external table, the database manager might need to alter or convert the data to enable the SELECT statement to return a result set. However, sometimes the data from a particular row of data cannot be altered or converted successfully. When that happens, the row is rejected and is not included in the result set.

You can use the MaxErrors external table option to specify a threshold for the number of rejected rows. When that threshold is exceeded, the attempt to process the SELECT statement is canceled.

This message is returned when a SELECT statement is issued for data in an external table and the attempt to process the statement fails because more rows have been rejected than the threshold set by the MaxErrors option.

User response

Address this error in one or more of the following ways:

To reduce the number of rejected rows, alter the data in the file so that it matches the column definitions of the external table.
To reduce the number of rejected rows, alter the column definitions of the external table so it better matches the data in the file.
To allow the statement to be processed despite the mismatches between the column definitions of the external table and the data in the file, increase the value of the MaxErrors option.

sqlcode: -20570

sqlstate: 22551

SQL20500 - SQL20999

SQL20500N Invalid use of row data type value in a list of values.

Explanation

User response

SQL20501N The explain facility failed because the specified section could not be found. Reason code = reason-code.

Explanation

User response

SQL20502N The explain facility failed because the specified activity event monitor evmon_name is not a write-to-table event monitor.

Explanation

User response

SQL20503N The explain facility is not supported for the specified section. Reason code: reason-code.

Explanation

User response

SQL20504N The statement failed because the target object of the anchored data type is unsupported or is being used in an unsupported context.

Explanation

User response

SQL20505N The WITH ORDINALITY clause is not valid with UNNEST of an associative array.

Explanation

User response

SQL20506N The cursor variable could not be used in an OPEN statement in the current scope because the cursor constructor value was assigned in a different scope.

Explanation

User response

SQL20507N The query associated with the cursor variable used in an OPEN or FETCH statement recursively invokes another cursor operation using the same cursor.

Explanation

User response

SQL20508N An error occurred during the revalidation of object object-name. Operation operation failed with SQLCODE sqlcode, SQLSTATE sqlstate, and message tokens token-list.

Explanation

User response

SQL20509N The statement was not processed because the specified alias cannot be used as the target of the statement. Specified alias: alias-name.

Explanation

User response

SQL20510N Invalid context of use for a compound SQL (compiled) statement.

Explanation

User response

SQL20511N The attempt to put data into the message buffer failed because there is not enough available space in the message buffer. Message buffer name: buffer-name.

Explanation

User response

SQL20512N No alert has been registered previously with the DBMS_ALERT.REGISTER procedure.

Explanation

User response

SQL20513N The UTL_FILE procedure procedure-name failed to delete or rename the file file-name. Operating system error = error-text.

Explanation

User response

SQL20514N A UTL_SMTP module routine encountered an SMTP server error. SMTP error code = error-code.

Explanation

User response

SQL20515N A dynamic statement name cannot be used in the cursor value constructor.

Explanation

User response

SQL20516W The statement compilation was successful but the access plan for this statement could not be preserved. Reason code = reason-code.

Explanation

User response

SQL20518N The operation is invalid because the UTL_SMTP module routine routine_name is called out of sequence.

Explanation

User response

SQL20519N There is no data in the local message buffer to unpack.

Explanation

User response

SQL20521N Error occurred processing a conditional compilation directive near string. Reason code=rc.

Explanation

User response

SQL20522N The statement failed because of an invalid specification of the WITHOUT OVERLAPS clause. Reason code reason-code.

Explanation

User response

SQL20523N Table table-name was specified as a history table, but the table definition is not valid for a history table. Reason code reason-code.

Explanation

User response

SQL20524N The statement failed because of an invalid period specification or period clause for period period-name. Reason code reason-code.

Explanation

User response

SQL20525N The requested action is not valid for table table-name because the table is of the wrong type. Reason code reason-code.

Explanation

User response

SQL20526N The variable variable-name is the target of two or more assignments with no defined order of assignment.

Explanation

User response

SQL20527N The statement failed because referenced period period-name is not a period in table table-name

Explanation

User response

SQL20528N The target of the data change operation is a table table-name, which includes a period period-name. The data change operation failed because a row that it attempted to modify was also modified by another transaction.

SQL20501N The explain facility failed because the specified section could not be found. Reason code = `reason-code`.

SQL20502N The explain facility failed because the specified activity event monitor `evmon_name` is not a write-to-table event monitor.

SQL20503N The explain facility is not supported for the specified section. Reason code: `reason-code`.

SQL20508N An error occurred during the revalidation of object `object-name`. Operation `operation` failed with SQLCODE `sqlcode`, SQLSTATE `sqlstate`, and message tokens `token-list`.

SQL20509N The statement was not processed because the specified alias cannot be used as the target of the statement. Specified alias: `alias-name`.

SQL20511N The attempt to put data into the message buffer failed because there is not enough available space in the message buffer. Message buffer name: `buffer-name`.

SQL20513N The UTL_FILE procedure `procedure-name` failed to delete or rename the file `file-name`. Operating system error = `error-text`.

SQL20514N A UTL_SMTP module routine encountered an SMTP server error. SMTP error code = `error-code`.

SQL20516W The statement compilation was successful but the access plan for this statement could not be preserved. Reason code = `reason-code`.

SQL20518N The operation is invalid because the UTL_SMTP module routine `routine_name` is called out of sequence.

SQL20521N Error occurred processing a conditional compilation directive near `string`. Reason code=`rc`.

SQL20522N The statement failed because of an invalid specification of the WITHOUT OVERLAPS clause. Reason code `reason-code`.

SQL20523N Table `table-name` was specified as a history table, but the table definition is not valid for a history table. Reason code `reason-code`.

SQL20524N The statement failed because of an invalid period specification or period clause for period `period-name`. Reason code `reason-code`.

SQL20525N The requested action is not valid for table `table-name` because the table is of the wrong type. Reason code `reason-code`.

SQL20526N The variable `variable-name` is the target of two or more assignments with no defined order of assignment.

SQL20527N The statement failed because referenced period `period-name` is not a period in table `table-name`

SQL20528N The target of the data change operation is a table `table-name`, which includes a period `period-name`. The data change operation failed because a row that it attempted to modify was also modified by another transaction.

SQL20530N An obfuscated statement is not valid. Reason code= `rc`.

SQL20531N The version number `flowed-version` specified in a binary XML value is not supported. The highest supported version is `highest-version`.

SQL20532N The command or API function call failed because the command or API function is discontinued. Command or API function name: `command-or-function-name`.

SQL20533N The SELECT statement failed because an unsupported data type was specified in the typed-correlation clause. Column with unsupported data type: `column-name`.

SQL20534W The schema `schema-name` includes one or more tables with the attribute `data-capture-option1` which is different from the schema attribute `data-capture-option2`.

SQL20535N The data change operation `operation` is not supported for the target object `object-name` because of an implicit or explicit period specification involving `period-name`. Reason code: `reason-code`.

SQL20536N The operation cannot be processed because it involves a text index. Reason code = `reason-code`.

SQL20537W The precompiler detected a combination of arrays with different cardinalities in the same SQL statement. The application was compiled to populate all arrays in the applicable statement with the minimum cardinality: `minimum_cardinality`

SQL20538W A permission or mask was changed for the table named `table-name`. This change might require a change to the permissions or masks of a materialized query table (MQT) based on this table to maintain the security of the data.

SQL20540N An autonomous transaction executing a procedure has been terminated abnormally. Routine name: `routine-name`. Specific name: `specific-name`. Reason code: `reason-code`.

SQL20541N An error code was received from an in-database analytics provider. Error code: `error-code`. Provider name: `provider-name`. Associated text and tokens: `tokens`.

SQL20544N The statement failed because an error was encountered while the ANALYZE_TABLE expression was being processed. Analytics provider name: `provider-name`. Reason code: `reason-code`.

SQL20545N The statement failed because a connection to the in-database analytics provider could not be established or maintained. Analytics provider name: `provider-name`. Reason code: `reason-code`.

SQL20547N The statement failed because the target of an assignment is a read-only variable. Variable name: `variable-name`.

SQL20548N The statement failed because a column of the input table of an ANALYZE_TABLE expression has an unsupported data type. Unsupported type: `type-name`.

SQL20549N The statement cannot be precompiled because an indicator variable array contains fewer elements than its the corresponding host variable array. Indicator variable array name: `var_name1`. Required array size: `cardinality_of_array2`.

SQL20551N Constructing an associative array failed because the input data includes at least one duplicate array index value. Duplicated index value: `value`.

SQL20552N The cast or assignment failed because the value does not conform to the data type constraint of the user-defined type. User-defined type: `type-name`. Value: `value`.

SQL20556N The operation failed because multiple result values cannot be returned from a scalar function `function-name`.