SQL0000-0999

SQL0000W

The SQL statement executed successfully, unless a warning condition occurred.

Check SQLWARN0 to ensure that it is blank. If it is blank, the statement executed successfully. If it is not blank, a warning condition exists. Check the other warning indicators to determine the particular warning condition. For example, if SQLWARN1 is not blank, a string was truncated.

sqlcode: 0

sqlstate: 00000, 01003, 01004, 01503, 01504, 01506, 01509, 01517

SQL0001N

For reasons specified in the previous messages, the bind or precompile request was unsuccessful.

No package is created.

Refer to the messages in the message file. Resubmit the command.

If installing the sample database, drop it and install the sample database again.

SQL0002N

For reasons specified in the previous messages, the bind file name cannot be used as specified.

No package is created.

Refer to the messages in the message file. Resubmit the command.

If installing the sample database, drop it and install the sample database again.

SQL0003N

For reasons specified in the previous messages, the database name cannot be used as specified.

No package is created.

Refer to the messages in the message file. Resubmit the command.

SQL0004N

The password contains characters that are not valid or the password is too long.

No package is created.

Resubmit the command with a valid password.

SQL0005N

For reasons specified in the previous messages, the message file name cannot be used as specified.

No package is created.

Refer to the messages in the message file. Check the name of the message file. Check the attributes of the message file if it exists. Resubmit the command.

SQL0006N

The value of the datetime format parameter is not in the valid range of 0 through 3.

No package is created.

Resubmit the command with a valid format parameter.

SQL0007N

The specified character is not a valid character in SQL statements. The text runtime token indicates the 20 characters of the SQL statement that preceded the character that is not valid.

Federated system users: Some data sources do not provide the appropriate values for the character and text runtime tokens. In these cases, character and text will have the following format: "<data source>:UNKNOWN", indicating that the actual values for the specified data source are unknown.

Remove or replace the character that is not valid and then resubmit the statement.

sqlcode: -7

sqlstate: 42601

SQL0008N

A host variable declaration has invalid syntax. The precompiler cannot identify the host variable.

The statement cannot be processed. None of the host variables declared in the statement (up to the semicolon) are accepted.

Examine the syntax of the host variable declaration.

SQL0009W

An attempt was made to override a precompiler option.

The option is ignored.

Ensure that all precompiler options are specified correctly.

SQL0010N

The statement contains a string constant, beginning with string, that is not terminated properly.

The statement cannot be processed.

Examine the statement for missing apostrophes in the indicated string constant.

sqlcode: -10

sqlstate: 42603

SQL0011N

The comment is not terminated properly.

The statement cannot be processed.

Examine the statement for a missing or extra comment delimiter in the indicated comment.

SQL0012W

The named column occurs in a SELECT statement, is not explicitly qualified, and occurs in a table specified in the FROM clause of an outer select. Consequently, the reference to the column in the SELECT statement has been taken to be an outer reference and correlation will occur.

The statement was processed under the assumption that correlation was intended.

Ensure that correlation was intended. It is a good practice to explicitly qualify intended outer references.

sqlcode: +12

sqlstate: 01545

SQL0013N

During precompilation, either a cursor name, statement name, database name, or authorization ID, specified as an empty string, was found. This is not valid. The statement cannot be processed.

Provide a valid cursor name, statement name, database name, or authorization ID.

SQL0014N

The source file name specified in the call to the precompiler contains invalid characters or the pointer to the source file name is invalid.

No package was created.

Ensure that the source file name is correct.

SQL0015N

If the WCHARTYPE CONVERT precompile option is in effect then the graphic host variable must be declared with the data type 'wchar_t', not 'sqldbchar'.

If the WCHARTYPE NOCONVERT precompile option is in effect (the default) and 'wchar_t' is defined as a 4-byte integer on this platform, then the graphic host variable must be declared with the data type 'sqldbchar', not 'wchar_t'.

Replace the current data type of the host variable with the data type specified in the message.

SQL0017N

The SQL function or method either does not contain a RETURN statement, or the function or method did not end with the execution of a RETURN statement.

Ensure the function or method executes a RETURN statement.

sqlcode: -17

sqlstate: 42632

SQL0020W

You can prepare SQL statements that are stored in a bind file that was generated by the precompiler, and create a package in the database using the BIND command. You can process an application program source file containing embedded SQL statements and create a package in the database using the PRECOMPILE command.

This warning is returned in the following situations:

• One or more parameters that were specified with either the PRECOMPILE command or the BIND command are not supported by the target database server.
• Values given for one or more parameters that were specified with either the PRECOMPILE command or the BIND command are not supported by the target database server.

Execute the BIND command or PRECOMPILE command again, specifying parameters and values that are supported by the target database server.

SQL0021W

The option is ignored.

SQL0022W

The precompiler option option is a duplicate.

The option is ignored.

Ensure that all precompiler options are specified only once.

SQL0023N

The specified database name is not a valid name.

Precompilation is terminated.

Ensure that the database name is spelled correctly and follows the rules for short identifiers.

SQL0024N

No database name was provided to proceed with precompilation.

Precompilation is terminated.

Provide a database name.

SQL0025W

Binding or precompilation succeeded, but warnings were issued. A package and/or bindfile was created as requested in the command.

Refer to the messages in the message file. If necessary, correct the problems and resubmit the command.

SQL0026N

The specified password is not a valid password.

Precompilation is terminated.

Ensure that the password provided follows the rules for valid passwords.

SQL0028C

The release number of the bind file is not compatible with the release number of the installed version of the database manager.

The bind file cannot be used with the current version of the database manager. The command cannot be processed.

If possible, repeat the precompile process with the current database manager. Otherwise, use only bind files created with a compatible release level of the database manager.

SQL0029N

Non-cursor SELECT or VALUES statements embedded in an application program must have an INTO clause to denote where the results of the statement are to be placed. Dynamic SELECT statements do not permit the INTO clause.

Add the INTO clause to the SELECT or VALUES statement and precompile the application program again.

sqlcode: -29

sqlstate: 42601

SQL0030N

A source file name was not provided to proceed with precompilation.

Precompilation is terminated.

Specify a source file name.

SQL0031C

The file name is required but could not be opened.

Precompilation is terminated.

Ensure that the file name specified is correct and exists in the file system, and that the file permissions are correct.

If installing the sample database, drop it and install the sample database again. If the error continues, reinstall the database manager and then install the sample database.

SQL0032C

While reading or writing file name, an error was encountered.

Precompilation is terminated.

Retry the precompilation.

SQL0033N

The bind file name specified is not a bind file.

Binding is terminated.

Ensure that the correct file name was specified.

SQL0034N

No bind file name was specified to proceed with binding.

Binding is terminated.

Provide a bind file name.

SQL0035N

The message file name could not be opened.

Binding or precompilation has been terminated.

Ensure that the system can access the file.

SQL0036N

If the file is input to the precompiler, it must have an extension with the correct extension for the language being used. If the file is input to the binder, it must have an extension of .bnd. Also, a fully resolved file name which is over the platform maximum length will cause this error.

Precompilation or binding is terminated.

Ensure that the specified file name is correct.

SQL0037W

The message file name name is not syntactically correct for this function.

The system redirects the output to the standard output device.

Ensure that the specified file name is correct.

SQL0038W

This option instructs a DRDA server to create a package even if there are SQL statements in a bind file which it considers invalid. Since not all of the DRDA servers support all of the SQL statements contained in the DB2-supplied bind files, the SQLERROR CONTINUE bind option must be used to guarantee that packages are created for all of the bind files in the list file.

No response is necessary. The required bind option, SQLERROR CONTINUE, has been specified for you. To avoid receiving this warning in the future specify the SQLERROR CONTINUE bind option.

SQL0039N

The binder could not process the bind file. The bind file may not be valid because the bind file contents were altered accidentally.

The bind file is not processed.

If possible, repeat the precompile process to create a new bind file. Otherwise, obtain a new copy of the bind file.

SQL0040N

For reasons specified in the previous messages in the message file, one or more bind files were not bound. The list of files that were not bound is composed of digits, starting from 1, that indicate the relative position of the unbound files in the list file. name does not include the path specification of the list file.

Only the first 20 bind files in error are listed in the message. If there were more than 20 bind files in error, ellipses (...) are inserted after the last bind file name in the list.

One or more packages were not created.

Refer to the messages in the message file. Check the list file to verify that it contains valid names. Resubmit the command.

SQL0041N

Some errors encountered during the binding process are considered fatal (that is, system errors, memory errors). If one of these errors occurs while processing a file in a list file, processing terminates. No attempt is made to bind the remaining files in the list file.

Such an error occurred while binding the specified bind file in the list. Note that the digit used to identify the bind file indicates the relative position of the file in the list file.

Refer to the other messages which accompanied this one to resolve the errors which occurred. Resubmit the command.

SQL0051N

The space required by all SQL statements in the program cannot fit into the column SECT_INFO in SYSIBM.SYSPLAN.

Precompilation is terminated.

Either simplify the program, or split the program into smaller, separate programs, or do both.

SQL0053W

The specified source file contains no SQL statements.

If binding, an empty package is created.

Ensure that you are precompiling or binding the correct program.

SQL0055N

The program source input file contained no data.

Precompilation is terminated.

Ensure that the correct input file was specified.

SQL0056N

An SQLSTATE or SQLCODE variable declaration is in a nested compound statement instead of the outermost compound statement in the SQL routine.

The statement cannot be processed.

Declare the SQLSTATE and SQLCODE variables only in the outermost compound statement of the SQL routine.

sqlcode: -56

sqlstate: 42630

SQL0057N

A RETURN statement is specified in the SQL function or method without specifying a value to return.

The statement cannot be processed.

Specify a value on the RETURN statement.

sqlcode: -57

sqlstate: 42631

SQL0058N

A RETURN statement is specified in the SQL procedure with a value or expression that is not of the INTEGER data type.

The statement cannot be processed.

Specify a value on the RETURN statement that has a data type of INTEGER.

sqlcode: -58

sqlstate: 428F2

SQL0060W

This message is written to the standard output device when the precompiler begins processing. The token name refers to the particular language precompiler which was invoked.

Processing continues.

No action is required.

SQL0061W

This message is written to the standard output device when the binder begins processing.

Processing continues.

No action is required.

SQL0062W

An INCLUDE statement has been encountered. The precompiler is now processing the INCLUDE file.

No action is required.

SQL0063W

The precompiler has completed processing the INCLUDE file. It will resume processing the file which contained the INCLUDE statement.

No action is required.

SQL0064N

A cyclic INCLUDE has been encountered. A precompiler input file cannot INCLUDE itself, nor be INCLUDEd by a file it INCLUDEs.

The specified file is not INCLUDEd.

Check the nesting of the INCLUDE files, and remove any cycles.

SQL0065N

A host variable declaration has an invalid syntax. An end-of-line was encountered before the declaration was complete.

The statement cannot be processed.

Examine the syntax of the host variable declaration.

SQL0078N

The routine routine-name does not have parameter names specified for all parameters. When the routine is defined with LANGUAGE SQL or SQLMACRO a parameter name is required for each parameter defined.

The statement cannot be processed.

Ensure that each defined parameter includes a parameter name.

sqlcode: -78

sqlstate: 42629

SQL0079N

The schema name name for a declared temporary table or an index on a declared global temporary table must be SESSION. The statement is specifying an explicit schema-name of schema-name for the declared global temporary table or index on a declared global temporary table. This is not allowed.

The statement cannot be processed.

sqlcode: -79

sqlstate: 428EK

SQL0081N

While precompiling or binding the program, an unexpected SQLCODE sqlcode was returned from the database manager.

The statement cannot be processed.

Inspect the SQLCODE to determine the problem and take the appropriate action.

SQL0082C

A previous, non-SQL error has caused processing to terminate.

Precompile/bind/rebind is terminated. No package was created.

Correct the situation that caused the previous error and retry the operation.

SQL0083C

During processing, there was not enough memory to continue processing.

If you continue to receive this message after attempting the suggested solutions, contact IBM Customer Support.

SQL0084N

A SELECT or VALUES statement was used in an EXECUTE IMMEDIATE statement.

The statement cannot be processed.

The implied function is not supported. Prepare the SELECT or VALUES statement. Then use OPEN, FETCH, and CLOSE.

sqlcode: -84

sqlstate: 42612

SQL0085N

A previous DECLARE statement already used the statement name specified in the current DECLARE statement.

The current DECLARE statement is not processed. The previous DECLARE statement retains use of the statement name.

Use a different name for the statement.

SQL0086C

During processing, there was not enough memory to continue processing.

SQL0087N

A variable is used in a context where it must not be the null value. For example, a cursor variable can be used in an OPEN or FETCH statement, but the value of the cursor variable must not be the null value. The statement cannot be processed.

Ensure that the value of the variable is not null in a context that does not allow the null value and try the request again.

sqlcode: -87

sqlstate: 22004

SQL0088N

Host variable name cannot be uniquely identified. More than one host variable with the same qualification can be found.

The statement cannot be processed.

Qualify the host variable further, or rename it if it is already fully qualified.

SQL0089N

The precompiler or binder quits processing after 100 errors were found.

Fix the errors indicated in the message log and resubmit the command.

SQL0091W

The precompilation or binding has terminated with the stated number of warnings and errors.

Precompilation or binding is terminated.

If warnings or errors occurred, correct the program, if necessary, and retry the precompilation or binding.

SQL0092N

Because of previous errors, no package was created.

Correct the errors and retry the precompilation or bind.

SQL0093N

While processing an SQL statement, the end of source was encountered before the statement terminated.

Precompilation is terminated.

Ensure that the SQL statement terminates properly.

SQL0094N

Binding was terminated because the user may have pressed the interrupt key sequence.

Processing is terminated. No package is created.

Resubmit the bind, if needed.

If installing the sample database, drop it and install the sample database again.

SQL0095N

Because of previous errors, no bind file was created.

No bind file is created.

Correct the errors and retry the precompilation.

SQL0097N

SQL routines (procedures, functions or methods) do not support variables or parameters of LONG VARCHAR or LONG VARGRAPHIC data types.

Do not use variables or parameters of LONG VARCHAR or LONG VARGRAPHIC data types in SQL routines. For LONG VARCHAR, use VARCHAR with an explicit length. For LONG VARGRAPHIC, use VARGRAPHIC with an explicit length.

sqlcode: -97

sqlstate: 42601

SQL0100W

No data was retrieved, updated, or deleted.

No action is required. Processing can continue.

sqlcode: +100

sqlstate: 02000

SQL0101N

This message can be returned when an attempt is made to prepare or execute an SQL statement that exceeds a limit such as a memory limit, an SQL limit, or a database limit. In this context, "memory limits" include the following examples:

• Statement heap (STMTHEAP)
• Application heap (APPLHEAPSZ)
• Application memory (APPL_MEMORY)
• Instance memory (INSTANCE_MEMORY)
• Other process or system memory limits

For example, this message can be returned for the following reasons:

• The statement heap isn't large enough to compile the statement. (The statement heap is used by the SQL or XQuery compiler as a work space when compiling SQL or XQuery statements.)
• There is not enough application memory to service application requests.
• If the statement is one that creates or modifies a packed description, the new packed description might be too large for the corresponding column in the system catalogs.

This message can also be returned when an attempt is made to execute an SQL statement that is too complex to compile. For example, the following elements contribute to the complexity of a statement:

• Constraints, such as table check constraints or foreign key constraints
• Triggers
• Row access controls and column access controls

Federated environments:

In a federated environment, this message can be returned when a statement exceeds a limit of either a federated server or a federated data source.

Code page conversion:

If character data is being converted between two different code pages, this message can be returned when the result of a data conversion exceeds a limit (even when the original data did not exceed any limits.)

Respond to this error by taking one or more of the following actions:

• Increase the size of the statement heap using the stmtheap database configuration parameter, or enable the statement heap to grow automatically by setting the stmtheap parameter to AUTOMATIC.
• Enable the amount of memory that is allocated for application requests to grow automatically by setting the appl_memory database configuration parameter to AUTOMATIC.
• Break the statement up into shorter or less complex SQL statements.
• Reduce the complexity of the statement by making one or more of the following types of changes:
  • Reduce the number of constraints, such as table check constraints or foreign key constraints, involved in the statement.
  • Reduce the number of triggers involved in the statement.
  • Reduce the number of row access controls or column access controls involved in the statement.

Federated environments:

1. Determine whether it is a federated data source that is returning the error, or the federated server that is returning the error.
2. Take one or more of the general actions already described. For example:
   • If the error is being returned by the federated server, increase the size of the statement heap using the stmtheap database configuration parameter, or set the stmtheap parameter to AUTOMATIC.
   • Reduce the complexity of the statement.

sqlcode: -101

sqlstate: 54001

SQL0102N

One of the following has occurred:

• The comment in the COMMENT ON statement is greater than 254 bytes.
• The application server name specified in the SQL CONNECT statement is greater than 18 bytes.
• The string constant beginning with string has a length greater than 32672 bytes. Character strings with lengths greater than 32672 bytes or graphic strings with lengths greater than 16336 characters can be specified only through assignment from host variables. Note that other servers in the DB2 family of products may specify a different size limit for character strings. Consult the documentation for the appropriate database product for details.
• In an XQuery expression, a string literal beginning with string has a length greater than 32672 bytes. The limit is determined on the string literal encoded in UTF-8. A string with length greater than 32672 bytes can only be specified in an XQuery expression using XML values from the database through the XQuery functions that access data or through an XQuery variable assigned a value using SQL/XML functions like XMLQUERY, XMLEXISTS, or XMLTABLE.
• Federated system users: the data source-specific limits must not be exceeded in a pass-through session. For example, a character literal larger than 254 bytes included in a statement sent to DB2 for OS/390 in a pass-through session would cause this error.
• The job ID string on the INGEST command is greater than 128 bytes.
• The date, time, or timestamp format string in a field definition on the INGEST command is greater than its maximum length. Date format strings have a maximum length of 10 bytes. Time format strings have a maximum length of 8 bytes. Timestamp format strings have a maximum length of 32 bytes.

It may be that data conversion is occurring and the resultant string is too long. For connections between applications and databases running under different codepages, string constants are converted from the application codepage to the database codepage. In certain situations, graphic string constants may be further converted from the database codepage to a UCS-2 (UNICODE) encoding, for example when the database has been created with an EUC codepage. This means that it is possible to have a resultant string which is longer than the input string.

The statement cannot be processed.

For Comment on Table or Comment on Column, reduce the size of the comment. For the SQL CONNECT statement, reduce the length of the application server name. For other string constants, the requested function is not available interactively. For an error occurring in the context of a non-CONNECT SQL statement embedded in an application program, assign the long string to a host variable and substitute that variable for the string literal in the SQL statement.

Federated system users: for a pass-through session, determine what data source is causing the error. Examine the SQL dialect for that data source to determine which specific limit has been exceeded, and adjust the failing statement as needed.

Ingest utility users: Specify an ingest job ID that is less than 129 bytes, or specify a format string that is not greater than the maximum length.

sqlcode: -102

sqlstate: 54002

SQL0103N

The indicated literal begins with a digit but is not a valid integer, decimal, or floating point literal.

Federated system users: a data source-specific literal representation error has occurred in apass-through session.

The statement cannot be processed.

Correct the invalid numeric literal.Federated system users, if the error occurred in a pass-through session, determine what data source is causing the error. Examine the SQL dialect for that data source to determine which literal representation rule has been violated, and adjust the failing statement as needed.

sqlcode: -103

sqlstate: 42604

SQL0104N

A syntax error in the SQL statement or the input command string for the SYSPROC.ADMIN_CMD procedure was detected at the specified token following the text text. The text field indicates the 20 characters of the SQL statement or the input command string for the SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM field of the SQLCA as token-list. This list assumes the statement is correct to that point.

This message can be returned when text is passed to the command line processor (CLP) in command mode and the text contains special characters that are interpreted by the operating system shell, such as single or double quotes, which are not identified with an escape character.

This error can also be returned for an explicitly qualified reference to a SYSIBM built-in function that does not support explicit use of SYSIBM as a function qualifier.

The statement cannot be processed.

Respond to this error in one of the following ways:

• Examine and correct the statement in the area of the specified token.
• If you are using the CLP in command mode and there are any special characters, such as quotes, in the command, use an escape character, such as the backslash character, to cause the operating system shell to not take any special action for those special characters. You could also issue the statement using CLP in interactive mode or batch mode to avoid any processing of special characters by the operating system shell.
• If you are using SYSIBM as the schema qualifier for a function name, ensure that the function supports use of this qualifier.

sqlcode: -104

sqlstate: 42601

SQL0105N

The statement contains a string constant beginning with string that is not valid.

The statement cannot be processed.

Specify the correct format of the string constant. Check for graphic string, paired delimiters, and an even number of bytes within the string.

sqlcode: -105

sqlstate: 42604

SQL0106N

The SQL statement was correct up to the point where no more input was found. This may be caused by failure to properly end a literal. String literals need a final quote mark.

Processing of this SQL statement has ended.

Check that the statement has all the necessary parts to complete the desired function and that all clauses are complete.

For PL/I: Check that the SQL statement is complete before the semicolon. For Assembler: Check that the continuation rules are properly followed. (A nonblank character must be in column 72, and continued lines must start in column 16 or beyond.)

For COBOL: Check that the SQL statement is complete before END-EXEC.

sqlcode: -106

sqlstate: 42601, 42603

SQL0107N

The name returned as name is too long. The maximum length in bytes permitted for names of that type is indicated by length. This does not include any escape characters, if present.

Federated system users: If in a pass-through session, a data source-specific limit might have been exceeded.

The statement cannot be processed.

Note: Where character data conversions are performed for applications and databases running under different code pages, this error can be returned because the result of the conversion exceeds the length limit.

Choose a shorter name or correct the spelling of the object name.

Federated system users: For a pass-through session, determine what data source is causing the error. Examine the SQL dialect for that data source to determine which specific limit has been exceeded, and adjust the failing statement as needed.

sqlcode: -107

sqlstate: 42622, 10901

SQL0108N

The name name is improperly qualified.

The object given the name name can only have one qualifier in this context.

A column name is qualified with a table name, which is either qualified or unqualified, or a correlation name. In some contexts, a column name requires a table name qualifier.

Identification of a module-object for an ALTER MODULE statement action must be an unqualified one-part name.

The statement cannot be processed.

Ensure that the name for the object is qualified correctly.

sqlcode: -108

sqlstate: 42601

SQL0109N

This message is returned when an SQL statement or command uses a clause in a way that is not supported.

There are many different scenarios in which this message can be returned. Some examples of scenarios in which this message can be returned include the following:

• A subquery, an INSERT statement, or a CREATE VIEW statement contains one of the following clauses: INTO, ORDER BY, or FOR UPDATE.
• The RAISE_ERROR function was used as a select list item but the result of the RAISE_ERROR function was not cast to an appropriate data type.
• A subselect isolation or lock request clause was specified in an XML context.
• Federated environments only: A data source-specific restriction was violated in a pass-through session.

Remove the clause from the statement or command and then resubmit the statement or rerun the command.

Federated system users:

For a pass-through session, perform the following troubleshooting steps:

1. Determine what data source is causing the error.
2. Examine the SQL dialect for that data source to determine which specific restriction has been violated.
3. Adjust the failing statement as needed.
4. Resubmit the statement or rerun the command.

sqlcode: -109

sqlstate: 42601

SQL0110N

Correct the constant and resubmit the statement.

sqlcode: -110

sqlstate: 42606

SQL0111N

The specification of the column function name (AVG, MIN, MAX, SUM, or COUNT(DISTINCT)) was not specified correctly because such functions must include a column name in the operand.

The statement cannot be processed.

Specify a column name in the expression that is the operand to the column function.

NOTE: This error is only applicable to releases of DB2 prior toVersion 2 .

sqlcode: -111

sqlstate: 42901

SQL0112N

In a SELECT list, the operand of an arithmetic operator cannot be a column function that includes the DISTINCT keyword.

The statement cannot be processed.

Correct the use of the column function to eliminate the invalid expression and try again.

sqlcode: -112

sqlstate: 42607

SQL0113N

An SQL-variable-name, parameter-name, security label component element, or condition-name identifier contains an invalid character.

for SQL-variable-name, parameter-name, and condition-name, only characters that are valid for an SQL ordinary identifier are allowed. Note that because the identifier is delimited, folding is not performed and uppercase and lowercase letters are treated as distinct from each other.

For security label component element, only characters that are valid for element values are allowed.

Correct the identifier and resubmit the statement.

sqlcode: -113

sqlstate: 42601

SQL0117N

The number of values can be different when:

• The number of insert values in the value list of the INSERT statement is not the same as the number of columns specified or implied. If no column list is specified, a column list that includes all columns of the table (except those implicitly hidden) or view is implied.
• The number of values on the right hand side of an assignment in a SET statement or SET clause of an UPDATE statement does not match the number of columns or variables on the left hand side.

The statement cannot be processed.

Correct the statement to specify one value for each of the specified or implied columns or variables.

sqlcode: -117

sqlstate: 42802

SQL0118N

The table or view specified as the target of an INSERT, DELETE, or UPDATE statement also appears in the FROM clause of a subquery within the statement.

The table or view that is the target of an INSERT, UPDATE, or DELETE cannot also be used to supply the values to be inserted or to qualify the rows to be inserted, updated, or deleted.

The statement cannot be processed.

This message is only applicable to Version 1.2 servers and earlier, and hosts accessed through DB2 Connect.

The implied function is not supported. To attempt to obtain the desired result, create a temporary copy of the object table or view and address the subselect to that copy.

sqlcode: -118

sqlstate: 42902

SQL0119N

The identified expression is an expression that starts with expression-start. The expression may be a single column name.

If the NODENUMBER or PARTITION functions are specified in the HAVING clause, then all partitioning key columns of the underlying table are considered to be in the HAVING clause.

The statement cannot be processed.

Correct the statement by including the expression in the GROUP BY clause that are in the SELECT clause, HAVING clause, or ORDER BY clause or by removing the column function from the SELECT statement.

sqlcode: -119

sqlstate: 42803

SQL0120N

An aggregate function or OLAP function can only be used in the select list of a fullselect, the having clause, or, with restrictions, in a WHERE clause or GROUP BY clause.

A WHERE clause can contain an aggregate function or OLAP function only if that clause appears within a subquery of a HAVING clause and the argument of the function is a correlated reference to a group.

A GROUP BY clause can contain an aggregate function or OLAP function only if the argument of the function is a correlated reference to a column in a different subselect than the one containing the GROUP BY clause.

An OLAP function cannot be used within the argument list of an XMLQUERY or XMLEXISTS expression.

The statement cannot be processed.

Change the statement so that the aggregate function or OLAP function is not used or used only where it is supported.

sqlcode: -120

sqlstate: 42903

SQL0121N

The same target name name is specified more than once as an OUT or INOUT argument of a CALL statement, or in the list of columns of an INSERT statement, the left hand side of assignments in the SET clause of an UPDATE statement, or the left hand side of the assignment statement. The target name identifies a column, SQL parameter, or variable.

Note that this error may occur when updating or inserting into a view where more than one column of the view is based on the same column of a base table.

The statement cannot be processed.

Correct the syntax of the statement so each column name is specified only once.

sqlcode: -121

sqlstate: 42701

SQL0122N

The SELECT statement has one of the following errors:

• A column name or expression and a column function are contained in the SELECT clause but there is no GROUP BY clause.
• A column name or expression is contained in the SELECT clause but not in the GROUP BY clause.

The column or expression may be contained in a scalar function.

If the DATAPARTITIONNUM, DBPARTITIONNUM, NODENUMBER, HASHEDVALUE or PARTITION functions are specified in the SELECT clause, then all database or table-partitioning key columns of the underlying table are considered to be in the SELECT clause.

The statement cannot be processed.

Correct the statement by including the columns or expressions in the GROUP BY clause that are in the SELECT clause, or by removing the columns or expressions from the SELECT clause.

sqlcode: -122

sqlstate: 42803

SQL0123N

The parameter in position n in the function name is not one of the expected elements; the parameter must be one of the following elements:

• A constant when it is required to be a constant
• A keyword when it is required to be a keyword
• A variable when it is required to be a variable
• A cast of a constant or variable when it is required to be a cast of a constant or variable

Ensure that each argument of the function conforms to the definition of the corresponding parameter.

sqlcode: -123

sqlstate: 428I9, 42601

SQL0125N

The ORDER BY or GROUP BY clause in the statement contains a column number that is either less than 1 or greater than the number of columns in the result table (the number of items in the SELECT clause).

The statement cannot be processed.

Correct the syntax of the ORDER BY or GROUP BY clause so each column identifier properly identifies a column of the result table.

sqlcode: -125

sqlstate: 42805

SQL0127N

The statement cannot be processed.

This error is only applicable to releases of DB2 prior to DB2 Version 2 and hosts accessed through DB2 Connect.

sqlcode: -127

sqlstate: 42905

SQL0129N

The SQL statement contains too many table names. A single SQL statement can reference up to 255 tables. Each table in any view referenced is included in this limit.

The statement cannot be processed.

Break the SQL statement into two or more simple statements with 255 or fewer table references.

This message is applicable to hosts accessed through DB2 Connect.

sqlcode: -129

sqlstate: 54004

SQL0130N

The escape character must be a single character no more than two bytes in length. It can only appear in the pattern string if it is followed by itself, a percent sign, or an underscore. For more information about the ESCAPE clause on the LIKE predicate, refer to the SQL Reference.

Correct the pattern string or the escape character accordingly.

sqlcode: -130

sqlstate: 22019, 22025

SQL0131N

If the expression to the left of a LIKE or NOT LIKE is of type character, the expression to the right must be of type character.

If the expression to the left is of type graphic, the expression to the right must be of type graphic.

If the expression to the left is of type binary string, the expression to the right must be of type binary string.

The statement cannot be processed.

Correct the expressions of the LIKE predicate to be the same data type.

sqlcode: -131

sqlstate: 42818

SQL0132N

This message can be returned when an invalid string expression is specified for a function such as one of the following functions:

• LIKE predicate
• LOCATE scalar function
• POSITION scalar function
• POSSTR scalar function
• STRPOS scalar function

Issue the statement again, specifying valid operands for each function.

sqlcode: -132

sqlstate: 42824

SQL0134N

The use of the string name is not permitted.

Federated system users: in a pass-through session, a data source-specific restriction can cause this error. See the SQL Reference documentation for the failing data sources.

The statement cannot be processed.

The requested operation on the string is not supported.

sqlcode: -134

sqlstate: 42907

SQL0135N

The UPDATE or INSERT is using constants, column names, or subqueries where it should be using NULL or a host variable.

A long string column is either a LONG VARCHAR, LONG VARGRAPHIC, VARCHAR(n) where n is greater than 254 but less than or equal to 32767, or VARGRAPHIC(n) where n is greater than 127 but less than or equal to 16383.

Refer to the DB2 for VM Application Programming manual for information on the use of long strings. Correct the statement. Try again.

sqlcode: -135

sqlstate: 56033

SQL0137N

The operation has produced a result string with an actual length that exceeds the maximum allowed for the operation.

If the operation is CONCAT, the result of concatenating the given operands was longer than what is supported by the result type.

Character string results are limited to 32,700 bytes unless one of the operands is a CLOB and then the limit is 2 gigabytes.

Graphic string results are limited to 16,350 characters unless one of the operands is a DBCLOB and then the limit is 1,073,741,823 (1 less than 1 gigabyte) double byte characters.

Binary string results (operands are BLOB) are limited to 2 gigabytes.

Refer to the documentation for the operation to determine how the result string is produced and the maximum length for the operation is determined. Consider whether changing the arguments of the operation will change the length of the result or the maximum length.

If the operation is CONCAT, ensure that the sum of the lengths of the operands does not exceed the supported maximum and try the operation again.

sqlcode: -137

sqlstate: 22001, 54006

SQL0138N

This message is returned when an out-of-range numeric argument is specified for a scalar function that manipulates one or more strings. Examples of such functions are:

• SUBSTR
• SUBSTRING
• LEFT
• RIGHT
• INSERT
• OVERLAY

For example, the second argument to the SUBSTR function specifies a position in a string. This message is returned if the value specified for that argument is less than 1 or greater than the length of the string. For information about the valid ranges for the arguments of a particular function, refer to the description of that function.

Review the numeric arguments of each scalar function in the statement. Correct any values that are out of range and reissue the statement.

sqlcode: -138

sqlstate: 22011

SQL0139W

The clause in the column specification is redundant.

The statement was processed successfully, but the redundant clause was ignored.

Correct the column specification.

sqlcode: +139

sqlstate: 01589

SQL0142N

A procedural SQL statement that is valid in other SQL dialects is not supported by this data server or an SQL statement that is valid for other IBM relational databases is not supported by this data server.

Federated system users: Check to see if an SQL statement was directed at a data source that does not support the SQL statement.

The statement cannot be processed.

Change the syntax of the SQL statement or remove the statement from the program.

Federated system users: If the reason is unknown, isolate the problem to the data source failing the request and examine the SQL dialect for that data source.

SQL0143W

An embedded SQL statement that may be valid for other IBM relational database products is understood but is not supported by the database manager.

The statement may produce inconsistent or unwanted results.

Change the syntax of the SQL statement or remove the statement from the program.

SQL0150N

The fullselect, view, typed table, materialized query table, range-clustered table, or staging table named in the INSERT, UPDATE, DELETE, MERGE, or TRUNCATE statement is defined so that the requested insert, update, delete, or truncate operation cannot be performed.

A view or a fullselect is read only if the SELECT statement of the view or the fullselect contains any of the following:

• A DISTINCT keyword
• A column function in the select list
• A GROUP BY or HAVING clause
• A FROM clause that identifies one of the following:
  • More than one table or view
  • A read-only view (READONLY column of SYSCAT.VIEWS is set to 'Y')
  • A system-period temporal table that is followed by a period specification
• A set operator (other then UNION ALL).
• Federated system users: a data source specific limitation that causes the view not to be updatable

Note that these conditions do not apply to subqueries of the SELECT statement or of the fullselect.

A view that directly or indirectly contains a WHERE clause that references a subquery or a routine defined with NOT DETERMISTIC or EXTERNAL ACTION cannot be used as the target of a MERGE statement.

A view defined with the WITH ROW MOVEMENT clause cannot be used as the target of a MERGE statement that includes an update operation.

A view defined with a fullselect that uses UNION ALL cannot be used as the target of a MERGE statement that specifies a period-clause in a modification operation.

Rows cannot be inserted directly into a typed table defined on a structured type that is not instantiable. Subtables of this table may allow inserts.

In general, system maintained materialized query tables and staging tables do not allow the insert, update, delete, or truncate operations.

A range-clustered table cannot be used in a truncate operation.

The statement cannot be processed.

The requested function cannot be performed on the fullselect, view, system maintained materialized query table, range clustered table, or staging table.

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: -150

sqlstate: 42807

SQL0151N

The specified column cannot be updated because one of the following was attempted.

• The object table is a view, and the specified column is derived from a scalar function, expression, keyword, constant, or column of a view where that column cannot be updated.
• The specified column is a non-updateable column of a system catalog, or a column explicitly marked as READ ONLY.
• The BUSINESS_TIME period in the table includes a column that was specified to be updated. A column of a BUSINESS_TIME period must not be modified within a trigger body.
• The BUSINESS_TIME period in the table includes a column that was specified to be updated. A column of a BUSINESS_TIME period must not be modified if the data change statement includes a period clause.

Federated system users should check to see if some other data source specific limitation prevents the column from being updated.

The statement cannot be processed.

If the specified column is derived from a scalar function, expression, keyword, or non updatable column, omit the column from the set clause of the update or the column of the insert. For a list of updatable catalogs (and the updatable columns) see the SQL Reference.

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: -151

sqlstate: 42808

SQL0152N

An attempt was made to alter or drop constraint constraint-name, but it is defined as a expected-constraint-type constraint, not as the specified actual-constraint-type constraint.

Verify the name and type of constraint to be altered or dropped.

sqlcode: -152

sqlstate: 42809

SQL0153N

The statement cannot be processed.

Provide a column name list in the CREATE VIEW statement, common table expression, CREATE TABLE statement, or name the columns in the SELECT list of the fullselect using the AS clause.

sqlcode: -153

sqlstate: 42908

SQL0155N

The trigger includes a REFERENCING clause with an OLD_TABLE or NEW_TABLE identified. A DELETE, INSERT or UPDATE triggered SQL statement has used the name specified as the OLD_TABLE or NEW_TABLE as the table to modify.

Remove the DELETE, INSERT or UPDATE triggered SQL statement from the triggered action or change the name of the transition table so that it does not conflict with the table you are attempting to modify.

sqlcode: -155

sqlstate: 42807

SQL0156N

The SQL statements ALTER TABLE, DROP TABLE, SET INTEGRITY, CREATE TRIGGER, CREATE INDEX, LOCK TABLE, and RENAME TABLE are applicable only to tables, not to views. The RUNSTATS, LOAD, and REDISTRIBUTE DATABASE PARTITION GROUP utilities are also applicable only to tables, not to views.

Federated system users: Some utilities and statements are not supported in a federated environment. See the Administration Guide for more information.

The statement or utility cannot be processed.

Verify that the correct table name is specified in the statement. If an alias name was supplied, ensure that the alias resolves to a table.

Federated system users: Verify that the object is not a nickname.

sqlcode: -156

sqlstate: 42809

SQL0157N

The object name was identified in a FOREIGN KEY clause of a CREATE or ALTER TABLE statement. A FOREIGN KEY clause must identify a base table.

The statement cannot be processed. The specified table is not created or altered.

Correct the statement to specify a base table name in the FOREIGN KEY clause.

If an alias name was supplied, ensure that the alias resolves to a base table.

sqlcode: -157

sqlstate: 42810

SQL0158N

The number of column names specified must be equal to the number of columns in the result table of the associated fullselect. If name is a staging table and the associated materialized query table has group by clause, the number of column names specified must be 2 more than the number of columns in the materialized query table for which the staging table is being defined. If the associated materialized query table has no group by clause, the number of column names specified must be 3 more than the number of columns in the materialized query table.

The statement cannot be processed.

Correct the syntax so that the list of column names defined for name specify a name for each column of the result table.

sqlcode: -158

sqlstate: 42811

SQL0159N

The object object specified as part of the statement or command refers to an object of type object-type instead of the expected type expected-object-type.

The type of the object that is specified in the statement or command must match the type that is identified by expected-object-type. For example:

• If the statement is DROP ALIAS PBIRD.T1, PBIRD.T1 must be an alias name.
• If the object-type is TABLE, it is possible that the specified table type is not supported by the statement.
• A CREATE MASK or CREATE PERMISSION statement must name a base table that exists at the current server.

Change the statement or command to specify an object of the expected object type.

sqlcode: -159

sqlstate: 42809

SQL0160N

The WITH CHECK OPTION clause cannot be used in a view definition if:

• The view is defined as read only. The view is read only if the SELECT statement contains any of the following. (Note that these conditions do not apply to subqueries of the SELECT statement.)
  • A DISTINCT keyword
  • A column function in the selected list
  • A GROUP BY or HAVING clause
  • A FROM clause that identifies either:
    • More than one table or view
    • A read-only view
  • A set operator (other than UNION ALL).
• The SELECT statement in the CREATE VIEW statement includes a subquery (except for specific statistic columns of some catalog tables).
• A view on which the specified view definition is dependent has an INSTEAD OF trigger defined.
• A view on which the specified view definition is dependent contains a text search function.

Federated system users: the WITH CHECK option is not supported on views that reference updatable nicknames.

The statement cannot be processed. The specified view was not created.

Remove the WITH CHECK OPTION clause or change the view definition so it conforms to the rules listed in this message.

sqlcode: -160

sqlstate: 42813

SQL0161N

The WITH CHECK OPTION clause was specified in the view definition that is the object of the insert or update operation or a FROM clause specified an insert or update operation with a view as the target. Consequently, all attempts to insert or update rows in that view are checked to ensure that the results conform to the view definition.

If the insert or update operation that target a view is in a FROM clause, the insert or update operation is always processed as if the view had been defined WITH CHECK OPTION.

The statement cannot be processed. No inserts or updates are performed, and the contents of the view and underlying base table remain unchanged.

Examine the view definition to determine why the requested insert or update operation was rejected. Note that this may be a data-dependent condition.

Requested insert or update operation may be trying to put out of range values in the target columns.

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 view definition for that data source.

sqlcode: -161

sqlstate: 44000

SQL0170N

The specified scalar function name has either too few or too many arguments.

The statement cannot be processed.

Ensure that the correct number of arguments is specified for the scalar function.

sqlcode: -170

sqlstate: 42605

SQL0171N

This message can be returned in two scenarios:

• The data type, length, or value of the argument for a parameter is incorrect for the routine.
• A conflicting or unsupported combination of parameters has been specified.

If the position is not applicable or unknown, a value of 0 is returned for n. If the parameter name is not applicable or unknown, the empty string is returned for parameter-name.

Determine what is incorrect about the parameter given in the token parameter-name by reviewing the rules for the parameters of the routine:

• Required and supported parameters
• Valid range of arguments for parameters
• Valid combinations of parameters

Invoke the routine again, specifying valid parameters.

sqlcode: -171

sqlstate: 5UA0J, 5UA05, 5UA06, 5UA07, 5UA08, 5UA09, 2201G, 2201T, 2201V, 10608, 22003, 22014, 22016, 22546, 42815

SQL0172N

The SQL statement includes an unknown scalar function.

The statement cannot be processed.

Ensure that you correctly spell the function name.

sqlcode: -172

sqlstate: 42601

SQL0176N

The statement cannot be processed.

Ensure that the second, third, and fourth arguments of the translate scalar function have correct values.

sqlcode: -176

sqlstate: 42815

SQL0180N

The string representation of a date, time, or timestamp value does not conform to the syntax for the specified or implied data type.

The statement cannot be processed.

Ensure that the syntax of the date, time, or timestamp value conforms to the syntax for its data type. If the string is not intended to be a date, time, or timestamp value, ensure that when used, it does not imply that data type.

Federated system users: the problem might be due to a date/time representation problem at the data source. If the reason is unknown, isolate the problem to the data source failing the request and examine the date/time representation restrictions for that data source.

sqlcode: -180

sqlstate: 22007

SQL0181N

The string representation of a date, time or timestamp value contains a value that is out of range.

This error could be caused by accessing a datetime value from an application using a territory code whose datetime format is different than the one used by the application that created it. For example, a string datetime value stored in the format dd/mm/yyyy will not be valid when read by an application expecting its format to be mm/dd/yyyy.

The correct ranges for date, time, or timestamp values are as follows:

• 0001 to 9999 for years.
• 1 to 12 for months.
• 1 to 31 for days when the month is 1, 3, 5, 7, 8, 10, and 12.
• 1 to 30 for days when the month is 4, 6, 9, 11.
• 1 to 28 for days when the month is 2 in a non-leap year.
• 1 to 29 for days when the month is 2 in a leap year.
• 0 to 24 for hours. If the hour is 24, the other parts of the time are 0. If the format is USA, the hour cannot be greater than 12.
• 0 to 59 for minutes.
• 0 to 59 for seconds.
• 0 to 999999999999 for fractional seconds.
• 001 to 365 for the day portion of a Julian calendar date when the year is not a leap year.
• 001 to 366 for the day portion of a Julian calendar date when the year is a leap year.

Federated system users: the problem might be due to a date/time representation problem at the data source. Refer to the data source's documentation for the ranges for date and time values at the data source. If the reason is unknown, isolate the problem to the data source failing the request and examine the date/time representation restrictions for that data source.

The statement cannot be processed.

Ensure that the value is within the valid range, and the application's datetime format is the same as the string value.

sqlcode: -181

sqlstate: 22007

SQL0182N

The specified expression contains an improperly used date, time, or timestamp value or labeled duration.

The statement cannot be processed.

Examine the SQL statement to determine the cause of the problem and correct the statement.

sqlcode: -182

sqlstate: 42816

SQL0183N

The result of an arithmetic operation is a date or timestamp duration which is outside the range 0001-01-01 through 9999-12-31.

The statement cannot be executed.

Examine the SQL statement to determine the cause of the problem. If the problem is data-dependent, examine the data processed when the error occurred.

sqlcode: -183

sqlstate: 22008

SQL0187N

An error indication from the operating system was detected while retrieving date/time information.

Ensure that the system TOD clock and timezone settings are correct.

sqlcode: -187

sqlstate: 22506

SQL0190N

The attributes specified in the ALTER COLUMN clause for the column column-name of the table table-name in an ALTER TABLE statement are not compatible with the attributes of the existing column. The error was returned for one of the following reasons.

• If a SET DATA TYPE clause is specified for an existing column, but:
  • the data type is not one that can be altered
  • the data type conversion of existing data or default values would result in an overflow, disallowed truncation, or casting error
  • the data type cannot be changed to the new data type
  • the current data type is SYSPROC.DB2SECURITYLABEL, which cannot be changed
  • the data type cannot be altered to SYSPROC.DB2SECURITYLABEL
• If a SET EXPRESSION clause is specified, but the existing column
  • is not defined as generated using an expression.
  • is part of a table partitioning key.
• If the DROP COLUMN SECURITY option is specified, but the column is not already protected by a security label.
• If DROP DEFAULT is specified, but the column is not defined with default attributes.
• If DROP IDENTITY is specified, but the column is not defined as an identity column.
• If DROP EXPRESSION is specified, but the column is not defined with a generation expression.
• If SET DEFAULT is specified, but the column is already defined with another form of generation (identity or expression) and there is no corresponding DROP in the same statement.
• If SET GENERATED ALWAYS AS (expression) is specified, but the column is already defined with a form of generation (default, identity, or expression) and there is no corresponding DROP in the same statement.
• If SET GENERATED ALWAYS AS IDENTITY or SET GENERATED BY DEFAULT AS IDENTITY is specified, but the column is already defined with a form of generation (default, identity, or expression) and there is no corresponding DROP in the same statement.
• If SET GENERATED ALWAYS or SET GENERATED BY DEFAULT is specified, but the column is not an identity column.
• If ALTER COLUMN is specified with SET GENERATED to change the column to a ROW-BEGIN, ROW-END, or TRANSACTION-START-ID column, but the column is already defined as a generated column.
• If ALTER COLUMN is specified to change the column to a ROW BEGIN, ROW END, or TRANSACTION START ID column, but the column is already defined with a user specified default value.
• If ALTER COLUMN SET DATA TYPE is specified, but the precision was reduced for the start column or end column of a BUSINESS_TIME period. Precision cannot be reduced.
• If ALTER COLUMN SET DEFAULT is specified, but the column is a generated column.
• If DROP DEFAULT is specified, but the column is a row-begin column, row-end column, or transaction-start-ID column
• If DROP GENERATED is specified, but the column is not a generated column.
• If DROP NOT NULL is specified, but the column is specified in the primary key of the table, or is an identity column, row change timestamp column, row-begin column, row-end column, or a random distribution key column.
• If SET NOT NULL is specified, but the column is a random distribution key column

The statement cannot be processed.

Make the attributes specified compatible with the existing column, remove the attribute specification, or specify a different column name. If a SET DATA TYPE clause was specified, check the notification log for possible entries listing conflicting rows that may have disallowed the ALTER COLUMN. If an ALTER COLUMN was specified for a column that already has a user specified default value, an ALTER TABLE statement must be issued to drop the default before the column definition can be changed.

sqlcode: -190

sqlstate: 42837

SQL0191N

Possible causes:

1. User data contained an improperly formed multi-byte character. For example, the first byte of a DBCS character was found, but the second character was not.
2. A scalar function, such as SUBSTR or POSSTR has truncated a multi-bytes string incorrectly. For these functions, start and length values must be correct in terms of bytes, in the context of the database code page. For a Unicode database, a common cause of this could be that the start or length for a UTF-8 string are incorrect.
3. A scalar function such as TRANSLATE has may have changed the multi-byte string.

Federated system users: this situation can also be detected by the data source.

1. Correct the input data and try again.
2. Change the start and length values such that when characters are converted to the database code page, the multi-byte characters are not truncated incorrectly.
3. Correct the TRANSLATE in error.

Federated system users: if the data is correct, isolate the problem to the data source failing the request and examine the DBCS restrictions of that data source. If the data appears to be correct, contact IBM service for assistance.

sqlcode: -191

sqlstate: 22504

SQL0193N

When new columns are added to a table that already exists, a value must be assigned to that new column for all existing rows. By default, the null value is assigned. However, since the column has been defined as NOT NULL, a default value other than null must be defined.

Either remove the NOT NULL restriction on the column or provide a default value other than null for the column.

sqlcode: -193

sqlstate: 42601

SQL0195N

An attempt was made to drop one or more columns using an ALTER TABLE statement. The columns cannot be dropped from table table-name because at least one of the existing columns must be preserved when altering a table.

Ensure table table-name will have at least one column once the ALTER statement is complete. Either remove the DROP of one of the columns and try the request again, or, if all of the columns should be removed, drop the table and create it again.

sqlcode: -195

sqlstate: 42814

SQL0196N

An attempt was made to drop column column-name. See the reason code for an explanation of why the column cannot be dropped.

1

The column cannot be dropped because it is part of the multi-node distribution key, table partitioning key, or MDC organizing dimension.

2

The column cannot be dropped, because a system generated column is dependent on it.

3

A column of type SYSPROC.DB2SECURITYLABEL cannot be dropped.

4

The column cannot be dropped because it is referenced in the definition of a period.

5

The column cannot be dropped because it is a column in a system-period temporal table.

6

The column cannot be dropped because it is a column in a history table.

1

The column cannot be dropped until the key or dimensions of the table are changed.

2

Change the generation expression of the dependent column.

3

Drop and recreate the table without creating the column of type SYSPROC.DB2SECURITYLABEL

4

Change the statement requesting the drop of the column.

5

Dropping a column from a system-period temporal table requires that the column also be dropped from the associated history table. The following steps can be used to drop a column from both tables.

1. Alter the system-period temporal table to drop versioning. This breaks the link between the tables.
2. Alter the system-period temporal table to drop the column.
3. Alter the table that was the history table to drop the column.
4. Alter the former system-period temporal table to add versioning. This reestablishes the link between the tables.

6

Dropping a column from a history table requires that the column also be dropped from the associated system-period temporal table. The following steps can be used to drop a column from both tables.

1. Alter the system-period temporal table to drop versioning. This breaks the link between the tables.
2. Alter the system-period temporal table to drop the column.
3. Alter the table that was the history table to drop the column.
4. Alter the former system-period temporal table to add versioning. This reestablishes the link between the tables.

sqlcode: -196

sqlstate: 42817

SQL0197N

The ORDER BY clause of a fullselect that includes a set operator (UNION, EXCEPT, INTERSECT) cannot have qualified column names.

Ensure that all column names in the ORDER BY clause are unqualified.

sqlcode: -197

sqlstate: 42877

SQL0198N

The host variable that was the object of the PREPARE or EXECUTE IMMEDIATE statement either contained all blanks or was an empty string.

The PREPARE or EXECUTE IMMEDIATE could not be completed.

Correct the logic of the program to ensure that a valid SQL statement is provided in the operand of the PREPARE or EXECUTE IMMEDIATE statement before it is executed.

sqlcode: -198

sqlstate: 42617

SQL0199N

A syntax error in the SQL statement was detected at the point in the statement when the reserved word keyword appears following text. The text field indicates the 20 characters of the SQL statement that preceded the reserved word. Clauses in the statement may be in the wrong order.

As an aid to the programmer, a partial list of valid tokens is provided in the SQLERRM field of the SQLCA as token-list. This list assumes the statement is correct to that point.

The statement cannot be processed.

Examine the statement in the keyword area. Add a colon or SQL delimiter, if missing. Verify that the clauses are in the correct order. If the reserved word identified in the messages is listed as a reserved word, make the word a delimited identifier.

NOTE: This error is only applicable to releases of DB2 prior toVersion 2 .

sqlcode: -199

sqlstate: 42601

SQL0203N

The column name needs further information to establish which of the possible table columns it is.

The statement cannot be processed.

Add a qualifier to the column name. The qualifier is the table name or correlation name. A column may need to be renamed in the select list.

sqlcode: -203

sqlstate: 42702

SQL0204N

This error is caused by one of the following:

• The object identified by name is not defined in the database.
• The object identified by name is defined in a module and is not a published module object and it was referenced from outside the module.
• The data partition identified by name is not defined on the table.
• A data type is being used. This error can occur for the following reasons:
  • If name is qualified, then a data type with this name does not exist in either the schema that matches the qualifier or the module that matches the qualifier that was found first based on user's SQL path.
  • If name is unqualified, then the user's path does not contain the schema to which the desired data type belongs or the data type is not defined in the module if the reference is within a module routine.
  • The data type does not exist in the database with a create timestamp earlier than the time the package was bound (applies to static statements).
  • If the data type is in the UNDER clause of a CREATE TYPE statement, the type name may be the same as the type being defined, which is not valid.
• A function is being referenced in one of:
  • a DROP FUNCTION statement
  • a COMMENT ON FUNCTION statement
  • the SOURCE clause of a CREATE FUNCTION statement

    If name is qualified, then the function does not exist. If name is unqualified, then a function of this name does not exist in any schema of the current path.

    Note that a function cannot be sourced on the COALESCE, DBPARTITIONNUM, GREATEST, HASHEDVALUE, LEAST, MAX (scalar), MIN (scalar), NULLIF, RID, NVL, RAISE_ERROR, TYPE_ID, TYPE_NAME, TYPE_SCHEMA, or VALUE built-in functions.

• The element named name is used on the right side of the UNDER clause in CREATE SECURITY LABEL COMPONENT statement but has not yet been defined as being ROOT or being UNDER some other element
• The security label component element named name has not yet been defined.
• One of the following scalar functions specified a security policy identified by name which is not defined in the database.
  • SECLABEL
  • SECLABEL_TO_CHAR
  • SECLABEL_BY_NAME

This return code can be generated for any type of database object.

Federated system users: the object identified by name is not defined in the database or name is not a nickname in a DROP NICKNAME statement.

Some data sources do not provide the appropriate values for name. In these cases, the message token will have the following format: "OBJECT:<data source> TABLE/VIEW", indicating that the actual value for the specified data source is unknown.

The statement cannot be processed.

Ensure that the object name (including any required qualifiers) is correctly specified in the SQL statement and it exists. If the name refers to a data partition, query the catalog table SYSCAT.DATAPARTITIONS to find the names of all the data partitions for a table. For missing data type or function in SOURCE clause, it may be that the object does not exist, OR it may be that the object does exist in some schema, but the schema is not present in your path.

For the CREATE or ALTER SECURITY LABEL COMPONENT statement, make sure that each element specified as a reference element value for positioning the location of a new element value already exists in the security label component.

For the CREATE SECURITY LABEL COMPONENT statement, make sure that each element is specified as either ROOT or as the child in an UNDER clause before specifying it as the parent in an UNDER clause.

For the scalar functions SECLABEL, SECLABEL_TO_CHAR or SECLABEL_BY_NAME, ensure that a valid security policy was specified for the argument security-policy-name.

Federated system users: if the statement is DROP NICKNAME, make sure the object is actually a nickname. The object might not exist in the federated database or at the data source. Verify the existence of the federated database objects (if any) and the data source objects (if any).

sqlcode: -204

sqlstate: 42704

SQL0205N

If object-name is a table or view, then name is a column or period that is not defined in object-name. If object-name is a structured type, then name is an attribute that is not defined in object-name.

Federated system users: object-name could refer to a nickname.

The statement cannot be processed.

If object-name is a table or view, verify that the column or period and table or view names (including any required qualifiers) are specified correctly in the SQL statement. If object-name is a structured type, verify that the attribute and type names (including any required qualifiers) are specified correctly in the SQL statement.

Also, if you receive this error during a REORG or an IMPORT, the column names in the index may violate the database manager naming rules as defined in the SQL Reference.

sqlcode: -205

sqlstate: 42703

SQL0206N

This error can occur in the following cases:

• For an INSERT or UPDATE statement, the specified column is not a column of the table, or view that was specified as the object of the insert or update.
• For a SELECT or DELETE statement, the specified column is not a column of any of the tables or views identified in a FROM clause in the statement.
• For an assignment statement, the reference name does not resolve to the name of a column or variable.
• When referencing a field in a row type variable, the reference name does not resolve to the name of any field in the row type.
• For an ORDER BY clause, the specified column is a correlated column reference in a subselect, which is not allowed.
• For a SELECT statement in a parameterized cursor constructor, the reference to name does not match a column or in-scope variable. Local variables and routine SQL parameters are not considered in scope for parameterized cursors.
• For a standalone compound SQL (compiled) statement:
  • The reference name does not resolve to the name of a column or local variable that is in scope.
  • The condition name name specified in the SIGNAL statement has not been declared.
  • When referencing a field in a row type variable, the reference name does not resolve to the name of any field in the row type.
• For a CREATE TRIGGER,CREATE METHOD,CREATE FUNCTION or CREATE PROCEDURE statement:
  • The reference name does not resolve to the name of a column, local variable or transition variable.
  • The reference name resolves to the name of a local variable that is not available in the current scope.
  • The condition name name specified in the SIGNAL statement has not been declared.
  • When referencing a field in a row type variable, the reference name does not resolve to the name of any field in the row type.
• For a CREATE TRIGGER statement:
  • A reference is made to a column of the subject table without using an OLD or NEW correlation name.
  • The left hand side of an assignment in the SET transition-variable statement in the triggered action specifies an old transition variable where only a new transition variable is supported.
• For a CREATE FUNCTION statement with a PREDICATES clause:
  • The RETURN statement of the SQL function references a variable that is not a parameter or other variable that is in the scope of the RETURN statement.
  • The FILTER USING clause references a variable that is not a parameter name or an expression name in the WHEN clause.
  • The search target in an index exploitation rule does not match some parameter name of the function that is being created.
  • A search argument in an index exploitation rule does not match either an expression name in the EXPRESSION AS clause or a parameter name of the function being created.
• For a CREATE INDEX EXTENSION statement, the RANGE THROUGH clause or the FILTER USING clause references a variable that is not a parameter name that can be used in the clause.
• For a parameterized cursor variable reference, a local SQL variable or SQL parameter referenced in the select statement used in the definition of a parameterized cursor variable is not available in the current scope.
• For a period reference, the specified period is not in any of the source or target tables or views of the statement.

The statement cannot be processed.

Verify that the names are specified correctly in the SQL statement. For a SELECT statement, ensure that all the required tables are named in the FROM clause. For a subselect in an ORDER BY clause, ensure that there are no correlated column references. If a correlation name is used for a table, verify that subsequent references use the correlation name and not the table name.

For a CREATE TRIGGER statement, ensure that only new transition variables are specified on the left hand side of assignments in the SET transition-variable statement and that any reference to columns of the subject table have a correlation name specified.

For a standalone compound SQL statement, CREATE FUNCTION, CREATE PROCEDURE, or CREATE TRIGGER statement, verify that the columns or variables are available in the scope of the current compound SQL statement.

For a fullselect embedded in XQuery using the db2-fn:sqlquery function, a reference within the fullselect must be one of the following: a column in the context of the fullselect, a global variable, or a parameter passed to the new SQL context using an additional argument of the db2-fn:sqlquery function.

sqlcode: -206

sqlstate: 42703

SQL0207N

A SELECT statement with a set operator contains an ORDER BY clause, which specifies column names. In this case, the list of columns in the ORDER BY clause must contain only integers.

The statement cannot be processed.

Specify only integers in the list of columns in the ORDER BY clause.

NOTE: This error is only applicable to releases of DB2 prior toVersion 2 and hosts accessed through DB2 Connect.

sqlcode: -207

sqlstate: 42706

SQL0208N

The statement is not valid because a column name specified in the ORDER BY list is not specified in the SELECT list and is not in the result table. Only columns in the result table can be used to order that result when the fullselect of the select-statement is not a subselect.

The statement cannot be processed.

To correct the syntax of the statement, either add the specified column to the result table or delete it from the ORDER BY clause.

sqlcode: -208

sqlstate: 42707

SQL0212N

The exposed table, view, alias, or correlation name specified by name is identical to another exposed table, view, alias, or correlation name in the same FROM clause.

If the statement is a CREATE TRIGGER, the REFERENCING clause may have specified the same name as the subject table or may have the same name for more than one of the OLD or NEW correlation names or the NEW_TABLE or OLD_TABLE identifiers.

The statement cannot be processed.

Rewrite the FROM clause of the SELECT statement. Associate correlation names with table, view, or alias names so no exposed table, view, alias, or correlation name is identical to any other exposed table, view, alias, or correlation name in the FROM clause.

For a CREATE TRIGGER statement, change the names in the REFERENCING clause so that there are no duplicates.

sqlcode: -212

sqlstate: 42712

SQL0213N

A parameter with name parameter-name does not exist in routine routine-name.

Specify an existing parameter name for the routine and resubmit the statement.

sqlcode: -213

sqlstate: 42703

SQL0214N

The expression identified by the first part of the expression expression-start-or-order-by-position in the clause-type clause is not valid for the reason specified by the reason-code as follows:

1

The fullselect of the select-statement is not a subselect. Expressions are not allowed in the ORDER BY clause for this type of select-statement. This reason code occurs only when clause-type is ORDER BY.

2

DISTINCT is specified in the select clause and the expression cannot be matched exactly with an expression in the select list. This reason code occurs only when clause-type is ORDER BY.

3

Grouping is caused by the presence of a column function in the ORDER BY clause. This reason code occurs only when clause-type is ORDER BY.

4

Expression in a GROUP BY clause cannot include a scalar-fullselect. This reason code occurs only when clause-type is GROUP BY.

5

The left side of a dereference operator in a GROUP BY clause cannot be a variant function. This reason code occurs only when clause-type is GROUP BY.

6

Expression in a ORDER BY clause cannot include an XMLQUERY or XMLEXISTS. This reason code occurs only when clause-type is ORDER BY.

7

Expression in a GROUP BY clause cannot include an XMLQUERY or XMLEXISTS. This reason code occurs only when clause-type is GROUP BY.

8

DISTINCT is specified in the LISTAGG function and the first argument expression cannot be matched exactly with the first sort-key expression of the ORDER BY specified in the WITHIN GROUP clause. This reason code occurs only when clause-type is ORDER BY.

9

The sort-key in the ORDER BY clause of the aggregate function indicated in clause-type has an incorrect data type.

10

The number of sort-key expressions in the ORDER BY clause of the aggregate function indicated in clause-type is not the same as the number of expressions.

11

The sort-key in the ORDER BY clause of the aggregate function indicated in clause-type has an incompatible data type with the data type of the corresponding expression.

The statement cannot be processed.

Modify the select-statement based on the reason specified by the reason-code as follows:

1

Remove the expression from the ORDER BY clause. If attempting to reference a column of the result, change the sort-key to the simple-integer or simple-column-name form.

2

Remove DISTINCT from the select clause or change the sort-key to the simple-integer or simple-column-name form.

3

Add a GROUP BY clause or remove the column function from the ORDER BY clause.

4

Remove any scalar-fullselect from the GROUP BY clause. If grouping is desired on a column of the result that is based on a scalar-fullselect use a nested table expression or a common table expression to first provide a result table with the expression as a column of the result.

5

Remove any variant function from the left side of the dereference operators in the GROUP BY clause.

6

Remove any XMLQUERY or XMLEXISTS expression from the ORDER BY clause. If ordering is desired on a column of the result that is based on an XMLQUERY or XMLEXISTS expression, use a nested table expression or a common table expression to first provide a result table with the expression as a column of the result.

7

Remove any XMLQUERY or XMLEXISTS expression from the GROUP BY clause. If grouping is desired on a column of the result that is based on an XMLQUERY or XMLEXISTS expression, use a nested table expression or a common table expression to first provide a result table with the expression as a column of the result.

8

Remove DISTINCT from the LISTAGG function or change the first sort-key expression of the ORDER BY specified in the WITHIN GROUP clause to match the first argument expression of the LISTAGG function.

9

Invoke the routine again with a valid data type for the sort-key in the ORDER BY clause of the aggregate function.

10

Invoke the routine again with the number of expressions equal to the number of sort-key expressions in the ORDER BY clause of the aggregate function.

11

Invoke the routine again with a compatible data type for the sort-key in the ORDER BY clause of the aggregate function as the data type of the corresponding expression.

sqlcode: -214

sqlstate: 42822

SQL0216N

A predicate includes a list of elements on the right or left side (or both sides) of the predicate operator. The number of elements must be the same on both sides. These elements may appear in a list of expressions enclosed in parentheses or as elements of a select list in a fullselect.

The statement cannot be processed.

Correct the predicate with mismatched number of elements on each side of the predicate operator.

sqlcode: -216

sqlstate: 428C4

SQL0217W

The current value of one of the Explain special registers has been set to EXPLAIN. This value allows dynamic SQL statements to be prepared and explained but prevents any dynamic statement from being executed.

Change the value of the appropriate Explain special register to a setting other than EXPLAIN by issuing the appropriate SET statement from the interface or application that is encountering this condition.

sqlcode: +217

sqlstate: 01604

SQL0219N

The Explain facility has been invoked but was unable to find the required Explain table name. The Explain tables must be created prior to invoking Explain.

Create the required Explain tables. The SQL Data Definition Language statements needed to create the Explain tables are available in the file called EXPLAIN.DDL in the misc directory under sqllib.

sqlcode: -219

sqlstate: 42704

SQL0220N

Correct the definitions of the specified Explain table. The SQL Data Definition Language statements needed to create the Explain tables are available in the file called EXPLAIN.DDL in the misc directory under sqllib.

If this error begins to occur after applying an update, it is possible that the required format of the Explain tables may have changed. In this case, adjust the definitions to the current format manually or by using the db2exmig tool.

After adjusting your Explain table definitions, re-issue the command.

sqlcode: -220

sqlstate: 55002

SQL0222N

If the SQLSTATE is 24510, then an error occurred. A positioned update or delete was attempted with cursor cursor-name, which is defined as SENSITIVE STATIC, and the current row has been identified as either a delete hole or update hole. A hole occurred when DB2 tried to update or delete a row in the database corresponding to the current row of the result table for cursor cursor-name and the corresponding row of the underlying table no longer exists.

If the SQLSTATE is 02502, then this is a warning. A delete hole or update hole has been detected while processing a fetch for cursor cursor-name. A hole occurred when DB2 tried to refetch a row from the database corresponding to the current row of the result table for cursor cursor-name and the corresponding row of the underlying table no longer exists. No data is returned.

A delete hole occurs when the corresponding row of the underlying table has been deleted.

An update hole occurs when the corresponding row of the underlying table has been updated in the underlying table such that the updated row no longer satisfies the search condition specified in the SELECT statement of the cursor.

The statement cannot be processed. The cursor remains positioned on the hole.

Issue a FETCH statement to position the cursor on a row that is not a hole.

sqlcode: -222

sqlstate: 02502, 24510

SQL0224N

A positioned UPDATE or DELETE was attempted using cursor cursor-name, defined as SENSITIVE STATIC, on a row where the column values in the result table do not match the current values in the base table row. The rows do not match because the row of the base table was updated between the time it was fetched into the result table and the time the positioned UPDATE or DELETE was processed.

The statement cannot be processed. The cursor position is unchanged.

Change isolation levels so that the base table row cannot be updated again during the cursor operation, or change the application to do a FETCH INSENSITIVE and try the positioned UPDATE or DELETE again.

sqlcode: -224

sqlstate: 24512

SQL0225N

A FETCH statement for non-scrollable cursor cursor-name been specified with one of the following scrollable cursor keywords: PRIOR, FIRST, LAST, BEFORE, AFTER, CURRENT, ABSOLUTE or RELATIVE. Only NEXT can be specified for non-scrollable cursors. No data is fetched.

The statement cannot be processed. The cursor position is unchanged.

Change the FETCH statement to remove the current fetch orientation keyword, such as PRIOR or FIRST, and replace it with NEXT. Alternatively, change the definition of the cursor to be scrollable.

sqlcode: -225

sqlstate: 42872

SQL0227N

The cursor position for cursor-name is unknown. A previous multiple row FETCH for cursor cursor-name resulted in an error (SQLCODE sqlcode, SQLSTATE sqlstate) in the middle of processing multiple retrieved rows. One or more of the requested rows could not be returned to the program following the error, leaving the position of the cursor unknown.

If an indicator structure had been provided on the previous multiple row FETCH, a positive SQLCODE would have been returned and all of the retrieved rows could have been returned to the application program.

The statement cannot be processed. The cursor position is unchanged.

Close and reopen the cursor to reset the position. For scrollable cursors, you can change the FETCH statement to specify one of the other fetch orientations, such as FIRST, LAST, BEFORE, AFTER or ABSOLUTE, to establish a valid cursor position and fetch a row of data.

sqlcode: -227

sqlstate: 24513

SQL0228N

Cursor cursor-name is defined as INSENSITIVE SCROLL, but the corresponding SELECT statement contains a FOR UPDATE clause.

The statement cannot be processed.

To define a read-only cursor, specify INSENSITIVE on DECLARE CURSOR, but do not specify the FOR UPDATE clause as part of the SELECT statement of the cursor.

sqlcode: -228

sqlstate: 42620

SQL0231W

A FETCH CURRENT or FETCH RELATIVE 0 statement was issued for scrollable cursor cursor-name. The operation is not valid, because the cursor is not positioned on a row of the result table. A FETCH of the current row is not allowed following a FETCH BEFORE or FETCH AFTER statement, or following a FETCH statement that resulted in SQLCODE +100.

The statement cannot be processed. The cursor position is unchanged.

Ensure that the cursor is positioned on a row of the result table before attempting to fetch the current row.

sqlcode: +231

sqlstate: 02000

SQL0236W

The value of the SQLN field of the SQLDA should be at least as large as the number of columns in the result set.

The database manager has not set any SQLVAR entries (and the SQLDOUBLED flag has been set to "off" (i.e. to the space character)).

Increase the value of the SQLN field in the SQLDA to the value indicated in the message (making sure the SQLDA is large enough to support that amount) and resubmit the statement.

sqlcode: +236

sqlstate: 01005

SQL0237W

Since at least one of the columns in the result set is a distinct type, space should be provided for twice as many SQLVAR entries as the number of columns in the result set. The database manager has only set the Base SQLVAR entries (and the SQLDOUBLED flag has been set off (i.e. to the space character).

If there is no need for the additional information about the distinct type(s) in the result set, then no action is required. If the distinct type information is needed, the value of the SQLN field in the SQLDA should be increased to the value indicated in the message (after making sure that the SQLDA is large enough to support that amount) and the statement should be resubmitted.

sqlcode: +237

sqlstate: 01594

SQL0238W

Since at least one of the columns in the result set is a LOB or structured type, space must be provided for twice as many SQLVAR entries as the number of columns in the result set. Note also that one or more of the columns in the result set may be a distinct type.

The database manager has not set any SQLVAR entries (and the SQLDOUBLED flag has been set off (i.e. to the space character).

Increase the value of the SQLN field in the SQLDA to the value indicated in the message (after making sure that the SQLDA is large enough to support that amount) and resubmit the statement.

sqlcode: +238

sqlstate: 01005

SQL0239W

If any of the columns in the result set is a distinct type or reference type, then space should be provided for twice as many SQLVAR entries as the number of columns in the result set.

The database manager has not set any SQLVAR entries (and the SQLDOUBLED flag has been set off (i.e. to the space character).

If the distinct type or reference type information is needed, the value of the SQLN field in the SQLDA should be increased to the value indicated in the message (after making sure the SQLDA is large enough to support that amount) and the statement should be resubmitted. If there is no need for the additional information about the distinct type(s) or reference type(s) in the result set, then it is possible to resubmit the statement only providing enough SQLVAR entries to accommodate the number of columns in the result set.

sqlcode: +239

sqlstate: 01005

SQL0242N

In a list of object names of type object-type, the object named object-name was specified more than once. The operation of the statement cannot be performed on the object more than once.

Correct the duplicated object in the list removing duplicate occurrences.

sqlcode: -242

sqlstate: 42713

SQL0243N

Cursor cursor-name is defined as SENSITIVE, but the content of the SELECT statement requires the database manager to build a temporary result table of the cursor, and the database manager cannot guarantee that changes made outside this cursor will be visible. This situation occurs when the content of the query makes the result table read-only. For example, if the query includes a join, the result table is read-only. In these cases, the cursor must be defined as INSENSITIVE or ASENSITIVE.

The statement cannot be processed.

Either change the content of the query to yield a result table that is not read-only, or change the type of the cursor to INSENSITIVE or ASENSITIVE.

sqlcode: -243

sqlstate: 36001

SQL0244N

DECLARE CURSOR
FETCH Statement
INSENSITIVE         INSENSITIVE
SENSITIVE STATIC    SENSITIVE
                    or INSENSITIVE
SENSITIVE DYNAMIC   SENSITIVE
SENSITIVE           SENSITIVE
ASENSITIVE          INSENSITIVE
                    or SENSITIVE
                    (depending on the
                     effective
                     sensitivity of the
                     cursor)

In the case of a non-scrollable cursor, a sensitivity option cannot be specified.

The statement cannot be processed.

Change or remove the sensitivity option specified on FETCH.

sqlcode: -244

sqlstate: 428F4

SQL0245N

When a routine is referenced in an SQL statement, the database manager must determine exactly which routine to invoke. For functions, this process is called function resolution, and it applies to both built-in and user-defined functions. This error is returned when an invocation of a function is ambiguous. This occurs when there are two or more possible candidate functions that satisfy the criteria for function resolution.

In this case, two candidate functions have a parameter in position position, but the data types of the parameters for the two candidate functions are not in the same data type precedence list. A best fit for the argument cannot be determined.

Change the SQL statement to explicitly cast the argument to the desired data type, the definition of a function, or the SQL path to remove the ambiguity from the set of candidate functions and try again.

sqlcode: -245

sqlstate: 428F5

SQL0257N

An attempt was made to use raw device containers but one of the following conditions is true:

• Raw devices are not supported on this platform.
• Raw device containers cannot be specified via the SET TABLESPACE CONTAINERS command for auto-resize table spaces.
• Raw device containers are not supported in a DB2 pureScale environment.

In a DB2 pureScale environment:

No response is required. You cannot use raw device containers in a DB2 pureScale environment.

In environments that are not DB2 pureScale environments:

• If creating a table space then use file containers or system-managed table spaces instead.
• If specifying a SET TABLESPACE CONTAINERS command then use file containers instead.

sqlcode: -257

sqlstate: 42994

SQL0258N

One of the following conditions is true:

1. An ALTER TABLESPACE statement involving container operations or the REBALANCE clause has previously been issued by this application or another and has yet to be committed.
2. A rebalance is in progress.

Perform the response that is appropriate to the condition:

1. If possible, roll back the uncommitted unit of work and issue a single ALTER TABLESPACE statement to perform all of the container operations. Otherwise, wait until the rebalance has completed and then try the operation again. Note that pages cannot both be added to and removed from a table space in the same ALTER TABLESPACE statement.
2. Wait until the rebalance has completed and then try the operation again.

sqlcode: -258

sqlstate: 55041

SQL0259N

The map structure keeps a record of how the table space address space is mapped to the various containers. If this gets too complicated, it does not fit in the table space file.

The table space may need to be rebalanced to more evenly distribute the data among the containers. This may simplify the mapping.

If this does not work, try making as many containers as possible the same size. Existing container sizes can be changed by backing up the table space and then using the database administration utility to change the containers. Restore the table space to the new containers.

sqlcode: -259

sqlstate: 54037

SQL0260N

Neither the distribution key nor the table partitioning key can have a LONG column, DATALINK column, XML column, structured type column, or a system hidden column implicitly added to a table enabled for row modification tracking.

The statement cannot be processed.

Do not use LONG columns, DATALINK columns, XML columns, structured type columns, or implicitly added system hidden columns for either the distribution key or the table partitioning key.

If the table needs to be distributed and only has LONG columns, DATALINK columns, XML columns, structured type columns, or implicitly added system hidden columns, either add a column that can be used for the distribution key to the table, or define the table without a distribution key in a single-partition database partition group.

If the table needs to be partitioned and only has LONG columns, DATALINK columns, XML columns, or structured type columns, either add a column that can be used for the partitioning key of the table, or define a nonpartitioned table.

sqlcode: -260

sqlstate: 42962

SQL0262N

The table table-name cannot be created in a database partition group. The table must have at least one column that can be used as the distribution key. Columns of the following data types cannot be used in a distribution key:

• BLOB
• CLOB
• DATALINK
• DBCLOB
• LONG VARCHAR
• LONG VARGRAPHIC
• XML
• Structured type
• A user-defined data type based on any of the previously listed data types

The statement cannot be processed.

Either create the table with one or more columns that can be used in a distribution key, or create the table in a single database partition group.

sqlcode: -262

sqlstate: 428A2

SQL0263N

The specified member range is not valid.

The statement cannot be processed.

Correct the member range in the statement, then try the request again.

sqlcode: -263

sqlstate: 428A9

SQL0264N

You can only add or drop a partitioning key on a table in a single database partition group.

The statement cannot be processed.

Do one of the following and try the request again:

• Define an identical table with a partitioning key.
• Redistribute the database partition group to a single database partition group.

sqlcode: -264

sqlstate: 55037

SQL0265N

For the CREATE DATABASE PARTITION GROUP statement, a database partition number can only appear once in the ON DBPARTITIONNUMS clause.

For the CREATE TABLESPACE and ALTER TABLESPACE statement, a database partition number can appear only once and in only one ON DBPARTITIONNUMS clause.

For the ALTER DATABASE PARTITION GROUP statement or REDISTRIBUTE DATABASE PARTITION GROUP command, one of the following occurred:

• The database partition number appeared more than once in the ADD DBPARTITIONNUMS or the DROP DBPARTITIONNUMS clause.
• The database partition number appeared in both the ADD DBPARTITIONNUMS and the DROP DBPARTITIONNUMS clause.
• The database partition number to be added is already a member of the database partition group.

The statement cannot be processed.

For a call WLM_ALTER_MEMBER_SUBSET statement, the member to be added is already a member of the member subset.

Ensure that the database partition names or database partition numbers in the ON DBPARTITIONNUMS, ADD DBPARTITIONNUMS, or DROP DBPARTITIONNUMS clause are unique. For the CREATE TABLESPACE and ALTER TABLESPACE statements, ensure that a database partition number appears in no more than one ON DBPARTITIONNUMS clause.

In addition, for the ALTER DATABASE PARTITION GROUP statement or REDISTRIBUTE DATABASE PARTITION GROUP command:

• Do not specify a database partition number in both the ADD DBPARTITIONNUMS and the DROP DBPARTITIONNUMS clause.
• Remove the database partition number from the ADD DBPARTITIONNUMS clause if the database partition number is already defined in the database partition group.

For the call WLM_ALTER_MEMBER_SUBSET statement, remove the member number from the member list.

sqlcode: -265

sqlstate: 42728

SQL0266N

The specified member number or database partition number member-number is not valid for one of the following reasons:

• The member number or database partition number is not in the valid range of 0 to 999.
• The member number or database partition number is not in the configuration file.
• The member number or database partition number is not part of the database partition group, therefore the requested operation cannot be processed.
• The statement referenced an event monitor that that is defined to run on a member that does not exist in the instance.
• The member number specified in the WLM_ALTER_MEMBER_SUBSET statement is not in the member subset.

The statement cannot be processed.

Depending on the condition:

• Issue the statement, command or API with member numbers or database partition numbers in the valid range.
• Follow the procedure to add a member or database partition to the system.
• Remove the member or database partition from the specified members or database partitions in the statement, command or API.
• Drop the event monitor referenced by the statement.
• Re-enter the WLM_ALTER_MEMBER_SUBSET statement with a member number that exists in the member subset.

sqlcode: -266

sqlstate: 42729

SQL0268N

The statement cannot be processed.

Wait until the redistribution is complete, then try the request again.

sqlcode: -268

sqlstate: 55038

SQL0269N

Because the database contains the maximum number of partitioning maps (32,768), you cannot create a new database partition group, alter a database partition group, or redistribute an existing one.

The statement cannot be processed.

Drop one or more database partition group in the database.

Note: Dropping a database partition group drops all database objects such as table spaces, tables and views that reside in the database partition group.

sqlcode: -269

sqlstate: 54033

SQL0270N

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

1

The primary key, each unique constraint, and each unique index must contain all distribution columns of the table.

2

Updating the distribution key column value is not supported.

3

A foreign key cannot include any nullable distribution key columns when defined with ON DELETE SET NULL. This is a special case of reason code 2 because defining such a constraint would result in attempting to update a distribution key column.

5

Views created with the WITH CHECK OPTION clause should not use functions (or reference views that use functions) that:

• are non-deterministic
• have side effects
• are related to the placement of data, for example, nodenumber or partition functions

These functions must also not be present within a referenced view if the new view is created with the CASCADED check option.

6

A transform cannot be defined for a user-defined distinct type.

7

Long fields can only be defined using a table space with a page size that is 4 KB. A LARGE TABLESPACE can only be created using a 4 KB page size.

8

Structured types are not supported as columns of a table or structured type attribute data types prior to DB2 Version 7.1.

9

Triggers are not supported on typed tables.

10

A single default table space cannot be selected because the table has one or more LOB columns that must be placed in a table space with a 4 KB page size and the rowsize or number of columns in the table requires a table space with an 8 KB page size.

11

A typed table or typed view cannot be created using a structured type that has no attributes.

12

The type of a source key parameter must be a user-defined structured type or a distinct type that is not sourced on the LOB, XML, LONG VARCHAR, or LONG VARGRAPHIC data type.

13

Check constraints cannot be defined on a typed table or the WITH CHECK OPTION clause cannot be specified on a typed view.

14

Referential constraints cannot be defined on a typed table or to a parent table that is a typed table.

15

A default value cannot be defined for reference type columns.

16

A reference data type or structured data type cannot be used as a parameter data type or a returns data type of a user-defined function prior to DB2 UDB Version 7.1. Otherwise, a scoped reference data type or an array data type cannot be used as a parameter data type or returns data type of a routine. A structured data type or an array data type cannot be used as a return column of a table or row function.

17

The SET INTEGRITY statement cannot be used for a typed table.

18

Column-level UPDATE and REFERENCES privileges cannot be granted on a typed table, typed view, or nickname.

19

A default value must be specified when defining a default for a column of a typed table.

20

ALTER TABLE is not supported for a materialized query table.

21

A column cannot be dropped or have its length, data type, security, nullability, or hidden attribute altered on a table that is a base table for a materialized query table.

22

Materialized query tables cannot be defined in a CREATE SCHEMA statement.

23

REPLICATED can be specified only for a materialized query table defined with REFRESH DEFERRED.

24

The triggered-action in a BEFORE trigger cannot reference a materialized query table defined with REFRESH IMMEDIATE.

25

Only one materialized query table can be specified for a SET INTEGRITY statement.

26

The database partition group being redistributed contains at least one replicated materialized query table.

27

A replicated materialized query table cannot be defined on a table that does not have a unique index existing on one or more columns that make up the replicated materialized query table.

28

A typed table or materialized query table cannot be renamed.

29

The FOR EXCEPTION clause cannot be specified with a materialized query table in the SET INTEGRITY statement.

30

Typed tables and typed views cannot be defined in a CREATE SCHEMA statement.

31

A distribution key cannot be defined with more than 500 columns.

32

A table defined using a multipartition database partition group or a single-partition database partition group on other than the catalog partition does not support DATALINK columns defined with FILE LINK CONTROL.

33

An underlying table of a materialized query table defined with REFRESH IMMEDIATE cannot be the child of a referential constraint with a cascading effect (that is, with the option ON DELETE CASCADE or ON DELETE SET NULL).

34

The underlying object relational feature is not supported in the current release.

35

A sequence or an identity column cannot be created in a Version 7 multinode database environment.

36

Activation of a multinode Version 7 database that contains sequences or identity columns is not allowed.

38

An index using an index extension is not supported in a multiple partition database partition group prior to DB2 UDB Version 8.1 FixPak 6.

39

Nicknames or OLE DB table functions cannot be referenced directly or indirectly in the body of an SQL function or SQL method.

40

The function IDENTITY_VAL_LOCAL cannot be used in a trigger or SQL function.

41

A single SQL variable statement cannot assign values to both a local variable and a transition variable.

42

The execution of a trigger, method, or function using SQL control statements and the execution of a dynamic compound statement in a multinode database are not allowed.

43

One or more of the specified options are currently not supported.

44

The following EXPLAIN MODES are not supported in MPP, SMP, and Data Joiner:

• COUNT CARDINALITIES
• COMPARE CARDINALITIES
• ESTIMATE CARDINALITIES

45

APPEND mode is not supported for multidimensional clustering (MDC) or insert time clustering (ITC) tables.

46

INPLACE table reorganization is not supported for multidimensional clustering (MDC) or insert time clustering (ITC) tables.

47

Index extensions are not supported for multidimensional clustering (MDC) or insert time clustering (ITC) tables.

48

Changes to the dimension specification of a multidimensional clustering (MDC) table are not supported.

49

Clustering indexes are not supported for multidimensional clustering (MDC) or insert time clustering (ITC) tables.

50

A user-defined temporary table cannot be a multidimensional clustering (MDC) or insert time clustering (ITC) table.

51

Issuing DDL operations that affect expression-based indexes from a database partition that is not the catalog database partition is not supported.

52

The expression of a generated column cannot be modified or added to a column that was not generated using an expression if that column is a distribution key column or was used in the ORGANIZE BY clause, the PARTITION BY clause, or the DISTRIBUTE BY clause.

53

A column with a LONG VARCHAR, LONG VARGRAPHIC, LOB, or XML type, a distinct type on any of these types, or a structured type cannot be specified in the select-list of a scrollable cursor.

54

INPLACE table reorganization is not supported for the specified system catalog table.

55

Federated database system support and the concentrator feature cannot be active at the same time.

56

Online index reorganization in rebuild mode is not supported for spatial indexes in ALLOW WRITE mode.

57

Online index reorganization is supported on multi-dimensionally clustered (MDC) or insert time clustering (ITC) tables in ALLOW WRITE mode only when the CLEANUP option or RECLAIM EXTENTS option is specified.

58

For a Version 8 database: the XML data type can be used only as a transient data type and cannot be stored in the database nor returned to an application.

59

A function or method that contains SQL statements cannot be used in a partitioned database environment.

60

The ALTER TABLE ALTER COLUMN SET INLINE LENGTH statement is not allowed because there is an object of type VIEW, either typed or untyped, that depends on the typed table.

61

A text search function cannot be used in the expression for check constraints or generated columns.

62

The WITH CHECK OPTION clause cannot be used with views that reference text search functions directly or depend on other views referencing text search functions.

63

A column with a LOB type, distinct type on a LOB type, A column with a LONG VARCHAR, LONG VARGRAPHIC, DATALINK, LOB, XML type, distinct type on any of these types, or structured type cannot be specified in the select-list of an insensitive scrollable cursor.

64

Federated processing is not supported on this platform.

65

Altering the nickname local type from the current type to the specified type is not allowed.

66

The built-in transform group SYSSTRUCT is not supported.

67

Nicknames or views on nicknames cannot be specified as target in a MERGE or UPDATE statement.

68

In a partitioned database, the maximum number of distinct NEXT VALUE expressions supported in an SQL statement is 55.

69

Delete from view would cause a descendent table to be delete-connected via multiple paths to two or more tables that appear in the view definition. Either a check constraint or trigger defined on the descendent table needs to be fired according to the final result which is not guaranteed.

70

A column cannot be dropped, or have its length, data type, security, or nullability altered on a table which is a base table of a view enabled for query optimization.

71

CALL statement cannot be used in a trigger, a SQL function, a SQL method or a dynamic compound statement in a partitioned database environment.

72

A nullable column cannot be changed to become an identity column.

73

Backup images in a partitioned database environment are not allowed to include logs.

74

Updating a status field in the recovery history file by time stamp is not allowed.

75

Automatic statistics profiling is not supported on a multiple database partition system, on a system where SMP is enabled, or on a federated system.

83

For versions of DB2 database prior to V9.7, the statement cannot be processed because DATA CAPTURE CHANGES and COMPRESS YES are not compatible.

87

The following types of tables cannot be defined as partitioned tables: typed tables, staging tables, user temporary tables, and range clustered tables.

89

REORG INDEX is only supported for nonpartitioned indexes on partitioned tables or for the CLEANUP ONLY ALL option.

90

For versions of DB2 database prior to Version 9.7, the PARTITIONED clause cannot be specified on the CREATE INDEX statement because partitioned indexes are not supported.

91

For versions of DB2 database prior to V9.7, the statement cannot be processed because DATA CAPTURE CHANGES is not supported for a table that has a compression dictionary.

92

Detaching from a table that is the parent of an enforced referential integrity constraint is not allowed.

93

Detaching a partition is not allowed for partitions that contain data that was appended using LOAD INSERT, and that have dependent materialized query tables or dependent staging tables that have not been incrementally refreshed with respect to the appended data.

95

A table with an attached partition whose integrity has not been verified yet cannot be altered to be a materialized query table.

97

Altering this data type is not supported for an element of the multinode distribution key, data distribution key, or MDC organizing dimension.

98

Altering the type of an identity column is not supported.

99

An alter table set data type changed the external UDF used by a check constraint.

102

A security policy cannot be added to a typed table.

103

On database servers Version 9.5 and earlier, online index reorganization in ALLOW WRITE mode is not supported for tables with XML columns.

104

In place table REORG is not allowed if an index on an XML column is defined on the table.

105

The REORG INDEX command is supported only for block indexes in CLEANUP or RECLAIM EXTENTS mode.

106

Attaching a partition to a materialized query table is not supported.

109

A text search function cannot be applied to the text index of a partitioned table if the text search function does not directly reference the partitioned table or is a member of a sub-select that contains an OUTER JOIN clause.

110

For SECLABEL, SECLABEL_BY_NAME, and SECLABEL_TO_CHAR, the security policy name parameter must be a string constant.

111

An audit policy cannot be associated with a typed table.

112

The health monitor does not support configuration of actions and notifications on non-root installations.

113

Use of a Compound SQL (compiled) statement in the body of a trigger or SQL table function is not supported in partitioned database environments.

114

Partitioned spatial indexes are not supported.

115

The function cannot be revalidated.

116

The same name was used for more than one named parameter marker in a compound SQL (compiled) statement that is dynamically prepared or executed.

119

If a field of a ROW variable has an ARRAY type, then array element values cannot be directly retrieved by specifying the corresponding index value for the element on the ROW variable field reference. Nor can a collection method be directly invoked using the field.

120

An attempt was made to insert data into an unsupported type of table.

121

The period-definition of a row-organized table must not include the MAINTAINED BY USER clause.

122

The period-definition of a column-organized table must include the MAINTAINED BY USER clause.

123

The command cannot be run unless automatic reorg recompress is disabled.

124

Issuing DML or operations at the dedicated catalog member is not supported.

125

The prerequisite catalog format changes are not yet applied to this database.

126

The following system hidden columns SYSROWID, CREATEXID, and DELETEXID of row modification tracking enabled tables cannot be specified as part of an index key.

127

The SET TENANT statement is not supported inside a routine.

128

This action is not supported within a user-defined tenant.

129

Registry variable DB2_BLOCK_REORG_PENDING is set to ON, preventing any ALTER TABLE statements that would result in the table being placed in reorg pending state.

130

SKIP LOCKED DATA clause is not supported for tables using a Compound SQL (compiled) statement in the body of a BEFORE trigger.

131

Requested function is not supported in this configuration or environment.

132

This action is only supported in the default SYSTEM tenant.

The action corresponding to the reason code is as follows:

1

Correct the CREATE TABLE, ALTER TABLE or CREATE UNIQUE INDEX statement.

2

Do not attempt to update the distribution key columns for a multipartition table, or consider deleting and then inserting the row with the new values in the distribution columns.

3

Make the distribution key column not nullable, specify a different ON DELETE action, or change the distribution key of the table so that the foreign key does not include any columns of the distribution key.

4

Either specify DATA CAPTURE NONE or ensure that the table is placed in a table space in a single-partition database partition group that specifies the catalog partition.

5

Do not use the WITH CHECK OPTION clause, or remove the function or view from the view definition.

6

Transforms are automatic for user-defined distinct types. Use the CREATE TRANSFORM statement for user-defined structured types only.

7

Use a table space with a 4 KB page size for any table that includes long fields. If you are using DMS table spaces, you can place long fields in a table space with a 4 KB page size with other table or index data in table spaces with a different page size. When defining a LARGE TABLESPACE, use PAGESIZE 4K.

8

For servers prior to DB2 UDB Version 7.1, ensure that no column data types are structured types in the CREATE TABLE statement or ALTER TYPE ADD COLUMN statement. Ensure that no attribute data types are structured types in the CREATE TYPE statement or ALTER TYPE ADD ATTRIBUTE statement.

9

Do not define triggers on typed tables.

10

Either reduce the row size or number of columns in the table or specify two table spaces such that the long data is in a table space with a 4 KB page size and the base data is in a table space with an 8 KB page size.

11

When creating a typed table or typed view, specify a structured type that has at least one attribute defined.

12

For the type of a source key parameter, use only a user-defined structured type or a distinct type that is not sourced on a LOB, XML, LONG VARCHAR, or LONG VARGRAPHIC type.

13

In a CREATE TABLE or ALTER TABLE statement for a typed table, do not specify check constraints. In a CREATE VIEW statement for a typed view, do not specify the WITH CHECK OPTION clause.

14

Do not specify referential constraints involving typed tables in a CREATE TABLE or ALTER TABLE statement.

15

Do not specify a DEFAULT clause for a column with a reference data type in a CREATE TABLE or ALTER TABLE statement.

16

For servers prior to DB2 UDB Version 7.1, do not specify a structured type parameter or returns type when creating a user-defined function. Otherwise, do not specify a scoped reference type as a parameter or returns type. Do not specify a structured type as a return column of a table or row function.

17

Do not specify a typed table in the SET INTEGRITY statement.

18

Do not include specific column names when granting REFERENCES or UPDATE privileges on a typed table, typed view, or nickname.

19

Include a specific value when specifying the DEFAULT clause on a column of a typed table.

20

Drop the materialized query table, and re-create it with the desired attributes.

21

To drop or alter a column in a table that is a base table for a materialized query table, perform the following steps:

1. Drop the dependent materialized query table.
2. Drop the column of the base table, or alter the length, data type, nullability, or hidden attribute of this column.
3. Re-create the materialized query table.

22

Issue the CREATE SUMMARY TABLE statement outside of the CREATE SCHEMA statement.

23

Either remove the REPLICATED specification or ensure that REFRESH DEFERRED is specified for the materialized query table definition.

24

Remove the reference to the materialized query table in the triggered-action in the BEFORE trigger.

25

Issue separate SET INTEGRITY IMMEDIATE CHECKED statements for each materialized query table.

26

Drop all replicated materialized query tables in the database partition group, and then issue the REDISTRIBUTE DATABASE PARTITION GROUP command again. Re-create the replicated materialized query tables.

27

Ensure that a subset of the columns defined for the materialized query table also is the set of columns that make up a unique index on the base table.

28

You can change a typed table or materialized query table name only by dropping the table and creating it again with the new name. Dropping the table might have implications on other objects that depend on the table, and the privileges on the table are lost.

29

Remove the FOR EXCEPTION clause from the SET INTEGRITY statement.

30

Issue the CREATE statement for the typed view or typed table outside of the CREATE SCHEMA statement.

31

Reduce the number of columns in the distribution key.

32

Either specify NO LINK CONTROL for the DATALINK column or place the table in a table space on a single-partitioned database partition group that specifies the catalog partition. If you are redistributing data to a multiple-partition database partition group, you must drop the table to continue with the redistribute.

33

Use one of the following approaches:

• Do not define a referential constraint with cascading effect (that is, with option ON DELETE CASCADE or ON DELETE SET NULL) on an underlying table of a materialized query table defined with REFRESH IMMEDIATE as the child.
• Do not define a REFRESH IMMEDIATE materialized query table whose underlying table is the child of a referential constraint with cascading effect (that is, with option ON DELETE CASCADE or ON DELETE SET NULL).

34

Remove the use of any unsupported object relational features.

35

Do not create or remove the GENERATED [ALWAYS | BY DEFAULT] AS IDENTITY ... attribute.

36

Drop the new node or nodes to go back to a single node configuration. If you require more nodes, drop the sequences or tables with identity columns before adding new nodes.

38

An index using an index extension cannot be created on a table in a multiple partition database partition group. A database partition group cannot become a multiple partition database partition group while an index using an index extension exists on a table in the database partition group. Either drop any such indexes and add the partition to the database partition group, in which case the indexes cannot be re-created, or leave the database partition group unchanged.

39

Remove the reference to a nickname or OLE DB table function, or remove the reference to the object that indirectly references one of these.

40

Remove the invocation of the IDENTITY_VAL_LOCAL function from the trigger definition or the SQL function definition.

41

Split the assignment into two separate statements. One statement must assign values only to SQL variables, and the other statement must assign values only to transition variables.

42

Drop the new node or nodes to return to a single node configuration. If you require more nodes, you must drop the triggers, functions, or methods containing control statements.

43

Reissue the RUNSTATS command and turn off the unsupported option.

44

You cannot use these EXPLAIN modes in SMP, MPP, and Data Joiner environments. If possible, run the query in serial mode. Otherwise, set the EXPLAIN mode to YES or EXPLAIN to provide the same information except for the actual cardinalities.

45

Do not specify the APPEND clause in an ALTER TABLE statement for multi-dimensionally clustered (MDC) or insert time clustering (ITC) tables.

46

Reissue the REORG command without specifying the INPLACE option.

47

Do not specify the EXTENSION clause in a CREATE INDEX statement for multidimensional clustering (MDC) or insert time clustering (ITC) tables.

48

Drop the multidimensional clustering (MDC) table and re-create it with the modified dimension specification.

49

Do not specify the CLUSTER clause in a CREATE INDEX statement for multidimensional clustering (MDC) or insert time clustering (ITC) tables.

50

Do not specify the ORGANIZE BY clause in a CREATE TABLE statement for declared global temporary tables.

51

Issue the CREATE INDEX statement from the catalog database partition.

52

Do not modify the expression or add it to an existing column. To change the composition of the PARTITIONING KEY clause, the ORGANIZE BY clause, the PARTITION BY clause, the DISTRIBUTE BY clause, or the generating expression of any of its members that are generated columns, drop and re-create the table and then repopulate it.

53

Modify the select-list of the scrollable cursor to not include a column with these types.

54

Reissue the REORG command without specifying the INPLACE option.

55

Turn off concentrator or federated database system support. Turn off the concentrator by setting the value of the database manager configuration parameter MAX_CONNECTIONS to be less than or equal to the value of the database manager configuration parameter MAX_COORDAGENTS. Turn off federated database system support by setting the FEDERATED parameter in the database manager configuration to NO.

56

Reissue the REORG INDEXES command, specifying ALLOW NONE or ALLOW READ.

57

Reissue the REORG INDEXES command, specifying ALLOW NONE or ALLOW READ.

58

For a Version 8 database: Input the XML data to one of the functions that accept XML input (ultimately XMLSERIALIZE) and store the output of the function in the database or return it to the application.

59

In a partitioned database environment, only use functions and methods defined with the NO SQL option.

60

Drop the views that depend on the typed table. Reissue the ALTER TABLE ALTER COLUMN SET INLINE LENGTH statement for the typed table, and re-create the views that you dropped.

61

Do not use the text search function in the expression for check constraints or generated columns. Use the LIKE function instead of CONTAINS, if possible.

62

Do not specify the WITH CHECK OPTION clause for this view.

63

Modify the select-list of the scrollable cursor to not include a column with these types.

64

Do not attempt federated processing on this platform.

65

Do not attempt to alter the nickname local type from the current type to the specified type.

66

Do not specify SYSSTRUCT as a transform group.

67

Do not specify a nickname or a view on nicknames as a target in a MERGE or UPDATE statement.

68

Reduce the number of distinct NEXT VALUE expressions in the statement, or change to a non-partitioned database.

69

Do not use DELETE FROM view-name.

70

Disable the views enabled for query optimization, drop or alter the column of the base table and then enable the views for query optimization.

71

In a partitioned database environment, do not use the CALL statement in a trigger, an SQL function, an SQL method, or a dynamic compound statement.

72

The column cannot become an identity column while it is nullable. To make this change, the table must be dropped and re-created with a not null column and then repopulated. Adding a new column as the identity column might also be considered.

73

Issue the BACKUP command without specifying the INCLUDE LOGS option.

74

Update the status field in the recovery history file by EID only.

75

Use one of the following approaches:

Disable automatic statistics profiling for this database by setting the database configuration parameters AUTO_STATS_PROF and AUTO_PROF_UPD to OFF.

Change the system to one that is on a single database partition, that does not have SMP enabled, and that is not federated.

83

If both DATA CAPTURE CHANGES and COMPRESS YES are being specified, you must only specify one of them. If one of DATA CAPTURE CHANGES or COMPRESS YES is being specified, you must not specify it as the other is already in effect on the table.

87

Create the table as a non-partitioned table.

89

Use the REORG INDEXES ALL command to reorganize all indexes on the table or data partition.

90

Create a nonpartitioned index by using the default or by explicitly specifying NOT PARTITIONED on CREATE INDEX.

91

Do not specify DATA CAPTURE CHANGES for this table.

92

Drop the referential constraint by using the following statement:

ALTER TABLE 'child-table' 

DROP CONSTRAINT ... 

Alternatively, alter the child table in the foreign key relationship so that the foreign key constraint is not enforced:

ALTER TABLE 'child-table' 

ALTER FOREIGN KEY ... NOT ENFORCED 

Note: The failing detach statement was executed from the parent table in the foreign key constraint, while the constraint enforcement is executed on the child table. These two tables are distinct unless the foreign key constraint is self-referential. Next, resubmit the ALTER TABLE ... DETACH PARTITION statement. You can now alter the child table to enforce the foreign key constraint:

ALTER TABLE 'child-table' 

ALTER FOREIGN KEY ... ENFORCED 

This will recheck the tables to ensure that the foreign key relationship is still being enforced.

93

Issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option to maintain the dependent materialized query tables or dependent staging tables with respect to the data that was appended using LOAD INSERT.

95

Issue the SET INTEGRITY statement with the IMMEDIATE CHECKED or IMMEDIATE UNCHECKED option on the table to verify the integrity of the attached partition.

97

Do not alter the column data type.

98

Drop the identity attribute, alter the type, and then re-enable the identity attribute.

99

Drop the check constraint, and then re-issue the ALTER statement.

102

Do not add a security policy to a typed table.

103

On database servers Version 9.5 and earlier, reissue the REORG INDEXES command, specifying ALLOW READ ACCESS or ALLOW NO ACCESS.

104

Remove the INPLACE option and reissue the REORG TABLE command.

105

Reissue the REORG INDEX command specifying CLEANUP, or RECLAIM EXTENT, or issue REORG INDEXES ALL to reorganize all indexes (including the block indexes) for the table.

106

Issue the ALTER TABLE statement to drop the materialized query attribute from the table before attempting to attach a partition to the table.

109

Modify the query to place the text search function in a sub-select that directly references the partitioned table that contains the text index and that does not contain an OUTER JOIN clause.

110

Provide a string constant for the security policy name parameter.

111

Do not associate an audit policy with a typed table.

112

Do not try to configure actions or notifications for the health monitor on non-root installations.

113

If possible, define the trigger or function using a compound SQL (inlined) statement, or remove the trigger or function definition.

114

Create the spatial index as a nonpartitioned index by using the NOT PARTITIONED clause on the CREATE INDEX statement.

115

Record the privileges granted to the function in SYSCAT.ROUTINEAUTH, drop the function, recreate the function, and then grant the privileges to the function.

116

Give each named parameter marker in a compound SQL (compiled) statement that is dynamically prepared or executed a unique name.

119

Assign the ARRAY field to an ARRAY variable.

120

No response required. Inserting into this type of table is not currently supported.

121

Do not include the MAINTAINED BY USER clause on this table.

122

Include the MAINTAINED BY USER clause on this table.

123

To disable automatic reorg recompress, contact IBM Support.

124

Issue the statement or operation at a non-catalog member.

125

Follow the documented process for applying catalog format changes to this database.

126

Do not include the system hidden columns in the index or constraint.

127

Ensure that the SET TENANT statement is not executed inside a routine.

128

Associate the current connection with the default SYSTEM tenant and try this action again.

129

Set registry variable DB2_BLOCK_REORG_PENDING to OFF and issue the ALTER TABLE statement. The table will be in reorg pending until REORG TABLE with the CLASSIC option is performed against the table.

130

If possible, define the BEFORE trigger using a compound SQL (inlined) statement.

131

Refer to the Db2 documentation for any limitation on the requested function to determine which one is relevant.

132

Associate the current connection with the default SYSTEM tenant and try this action again.

sqlcode: -270

sqlstate: 42997

SQL0271N

The index file for the table with fid fid is required during processing. The file is either missing, or it is not valid.

The statement cannot be processed, and the application is still connected to the database. This condition does not affect other statements that do not use the index on this table.

Ensure that all users are disconnected from the database, then issue the RESTART DATABASE command on all nodes. Then try the request again.

The index (or indexes) is re-created when the database is restarted.

sqlcode: -271

sqlstate: 58004

SQL0276N

This message can be returned for two reasons:

1. The database is in "restore pending" state

As a result of a previous operation, the database is in "restore pending" state. While the database is in "restore pending" state, attempts to connect to the database will fail.

2. The database is in "upgrade pending" state

If the db2ckupgrade utility finds that a database is ready to be upgraded, the db2ckupgrade utility will set the read-only database configuration parameter UPGRADE_PENDING to 1. While this database configuration parameter is set to 1, the database is said to be in "upgrade pending" state. While the database is in "upgrade pending" state, attempts to connect to the database will fail.

1. If the database configuration parameter UPGRADE_PENDING is set to 1, respond in one of the following ways:
   • Wait until the database is upgraded and then connect to the database.
   • Take the database out of "upgrade pending" state by issuing the db2ckupgrade command with the -resetUpgradePending parameter, and then connect to the database.
2. Otherwise, restore the database, and then connect to the database.

sqlcode: -276

sqlstate: 08004

SQL0279N

Commit processing encountered an error. The transaction has been put in the commit state, but the commit processing might not have completed. The application's database connection has been terminated.

The cause of the error is indicated by the reason-code:

1

A node or data source involved in the transaction has failed.

2

Commit was rejected on one for the nodes. Check the administration notification log for details.

Determine the cause of the error. It might be necessary to contact the system administrator for assistance because the most common cause of the error is node failure or connection failure. The RESTART DATABASE command will complete the commit processing for this transaction.

sqlcode: -279

sqlstate: 08007

SQL0280W

None required.

sqlcode: +280

sqlstate: 01595

SQL0281N

Additional containers cannot be added to a system managed table space. The exception to this is when a database partition group was modified to add a node without table spaces, then containers can be added once on the new node using the ALTER TABLESPACE command. In general, the table space must be managed by the database in order to add additional containers.

The statement cannot be processed.

To add more containers to a system managed table space, drop and recreate the table space with more containers, ensuring that each container is of the same size and less than the container size limit, or change to a DMS table space.

sqlcode: -281

sqlstate: 42921

SQL0282N

A table in the specified table space does not contain all of its parts in that table space. If more than one table spaces are specified, then a table in one of the specified table spaces does not contain all of its parts in the list. The base table, indexes, or long data may be in another table space, so dropping the table space(s) will not completely drop the table. This would leave the table in an inconsistent state and therefore the table space(s) cannot be dropped.

Ensure that all objects contained in table space tablespace-name contain all their parts in this table space before attempting to drop it, or include those table spaces containing the parts in the list to be dropped.

This may require dropping the table table-name before dropping the table space.

sqlcode: -282

sqlstate: 55024

SQL0283N

A database must contain at least one system temporary table space with the same page size as the page size of the catalog tablespace. Dropping table space tablespace-name would remove the last system temporary tablespace with a page-size page size from the database.

Ensure there will be another system temporary table space with a page-size page size in the database before attempting to drop this table space.

sqlcode: -283

sqlstate: 55026

SQL0284N

This message is returned when an attempt is made to create a table with properties that are not supported with the type of table space in which the table is being created.

This message can be returned when the following types of SQL statements are executed:

• CREATE TABLE
• CREATE GLOBAL TEMPORARY TABLE
• DECLARE GLOBAL TEMPORARY TABLE

Examples of the kinds of incompatibilities that can cause this message to be returned include the following situations:

• An attempt was made to create a regular table in a table space that is not a REGULAR or LARGE table space.
• An attempt was made to create or declare a temporary table in a table space that is not a USER TEMPORARY table space.
• An attempt was made to create an insert time clustering (ITC) table in a table space that is not a managed by database table space.
• An attempt was made to create a column-organized table in a table space that is not defined as MANAGED BY AUTOMATIC STORAGE and does not have an associated storage group.

Correct the statement to specify a table space with the correct type for the clause clause.

sqlcode: -284

sqlstate: 42838

SQL0285N

If the primary table space is a system managed table space, all table parts must be contained in that table space. A table can have parts in separate table spaces only if the primary table space, index table space and long table space are database managed table spaces.

Either specify a database managed table space for the primary table space, or do not assign the table parts to another table space.

sqlcode: -285

sqlstate: 42839

SQL0286N

The CREATE TABLE, CREATE GLOBAL TEMPORARY TABLE, or DECLARE GLOBAL TEMPORARY TABLE statement did not specify a table space, and a table space of the right type (USER TEMPORARY for created temporary table or declared temporary table) with sufficient page size (at least pagesize), over which authorization ID user-name has USE privilege, could not be found.

Sufficient page size for a table is determined by either the byte count of the row or the number of columns.

Ensure the existence of a table space of the correct type (REGULAR or USER TEMPORARY) with a page size of at least pagesize and that authorization ID user-name has USE privilege on this table space.

sqlcode: -286

sqlstate: 42727

SQL0287N

The CREATE TABLE or GRANT USE OF TABLESPACE statement specified a table space named SYSCATSPACE which is reserved for catalog tables.

Specify a different table space name.

sqlcode: -287

sqlstate: 42838

SQL0288N

A LARGE table space is defined to store large objects and long strings. These objects can only be stored in table spaces that are defined as automatic storage.

This message is returned when an attempt was made to create a large table space as a system managed table space.

To resolve the issue reported by this message, here are some examples of the actions that you can perform:

• Issue the the CREATE TABLESPACE statement without the LARGE clause.
• Issue the the CREATE TABLESPACE statement with the MANAGED BY AUTOMATIC STORAGE clause statement.

sqlcode: -288

sqlstate: 42613

SQL0289N

One of the following conditions is true on one or more database partitions:

1

One of the containers assigned to this SMS table space has reached the maximum file size. This is the likely cause of the error.

2

All the containers assigned to this DMS table space are full. This is the likely cause of the error.

3

A rebalance is in progress, but has not progressed far enough to enable the newly added space to be used.

4

A redirected restore is being done to containers that are too small.

5

A rollforward is being done following a redirected restore and all the containers assigned to this tablespace are full.

6

A rollforward skipping add containers is being done and all the containers assigned to this tablespace are full.

7

An attempt was made to create a table space with less than 5 usable extents.

8

An auto-resize table space has reached its maximum size and all of the containers are full. Alternately, there is not enough space between the current size and the maximum size for containers to be extended or added so an automatic increase in space is not possible.

9

An automatic storage table space is being created with a value for initial size that does not result in an equal distribution of container space. As a result, a higher value is being used but this value is larger than the maximum size specified.

10

A DMS table space enabled for auto-resize has not reached its maximum size but one of the file systems on which the containers exists is full and the container cannot grow.

11

A DMS table space enabled for auto-resize has not reached its maximum size and the file systems on which the table space resides are not full. However, a container operation (or a subsequent rebalance) is in progress and the auto-resize capability is suspended until it is complete.

Perform the action corresponding to the cause of the error:

1

Re-create as an AUTOMATIC STORAGE table space or an SMS table space with more directories (PATHs) such that: (number of directories) >= (max tablesize / maxfilesize) or re-create. Note that maximum file size is operating system dependent. Re-creating the table space as AUTOMATIC STORAGE allows you use storage groups.

2

Add new container(s) to the DMS table space and try the operation again, after the rebalancer has made the new pages available for use.

3

Wait for the rebalancer to finish.

4

Perform the redirected restore again to larger containers.

5

Perform the redirected restore again to larger containers.

6

Perform the rollforward again allowing add containers, or perform a redirected restore to larger containers.

7

Resubmit the CREATE TABLESPACE statement, ensuring that the table space has at least 5 usable extents.

8

Increase the maximum size for the table space.

9

Reduce the initial size or increase the maximum size for the table space.

10

Add a new stripe set of containers to the table space. Existing containers will no longer grow, only those new containers in the last range of the table space are extended when an automatic resize occurs.

11

Wait for the operation and subsequent rebalance to complete.

sqlcode: -289

sqlstate: 57011

SQL0290N

A process attempted to access a table space which is in an invalid state, for which the intended access is not allowed.

• If the table space is in a quiesced state ("Quiesced: SHARE", "Quiesced: UPDATE", or "Quiesced: EXCLUSIVE"), only processes which also hold the table space in a quiesced state are allowed access to the table space.
• If the table space is in any other state, only the process which is performing the action that caused the current table space state is allowed access to the table space.
• A system or user temporary table space cannot be dropped which contains active system temporary tables, create temporary tables, or declared temporary tables.
• The SET CONTAINER API cannot be used to set the container list unless the table space is in the "Restore pending" state.

Possible actions include:

• If the table space is in a quiesced state, attempt to acquire a quiesced share or quiesced update state on the table space. Or, attempt to quiesce reset the table space.
• If the table space is in any other state, wait until the table space has returned to normal state before attempting to access the table space.

Refer to the Administration Guide for further information about the table space states.

sqlcode: -290

sqlstate: 55039

SQL0291N

An attempt was made to change the state of a table space. Either the new state is not compatible with the current state of the table space, or an attempt was made to turn off a particular state and the table space was not in that state.

Table space states change when a backup is taken, the load completes, the rollforward completes, etc., depending on the current state of the table spaces. Refer to the systems administration guide for further information about the table space states.

sqlcode: -291

sqlstate: 55039

SQL0292N

An internal database file could not be created.

Check that the directory containing the file is accessible (for example, mounted) and writeable by the database instance owner.

sqlcode: -292

sqlstate: 57047

SQL0293N

This error may be caused by one of the following conditions:

• A container (directory, file or raw device) was not found.
• A container is not tagged as being owned by the proper table space.
• A container tag is corrupt.

This error can be returned during database startup and during the processing of the ALTER TABLESPACE SQL statement.

Try the following actions:

1. Check that the directory, file, or device exists and that the file system is mounted (if it is on a separate file system). Containers must be readable and writable by the database instance owner.
2. If you have a recent backup, try restoring the table space or database. If that fails because of the bad container and the container is not a DEVICE type, try manually removing the container first.

If the error was returned from the processing of an ALTER TABLESPACE SQL statement with the SWITCH ONLINE option, then re-issue the statement after correcting the problem.

If the error persists, call your IBM service representative.

sqlcode: -293

sqlstate: 57048

SQL0294N

Table space containers cannot be shared. The possible causes of this error include the following.

• A CREATE TABLESPACE or ALTER TABLESPACE statement included a container that is already in use by another table space.
• A CREATE TABLESPACE or ALTER TABLESPACE statement included a container from a table space that has been dropped but the drop statement has not been committed.
• An ALTER DATABASE PARTITION statement used to add a database partition used the containers of a LIKE database partition that is on the same physical database partition. These containers would therefore already be in use.
• A CREATE TABLESPACE or ALTER TABLESPACE statement is attempting to use the same container on more than one logical database partition on a single physical database partition. The same containers cannot be used for more than one database partition on the same physical database partition.
• An ADD DATABASE PARTITION command or API used the containers from the system temporary table spaces of a LIKE database partition that is on the same physical database partition. These containers would therefore already be in use.
• A CREATE TABLESPACE statement, ALTER TABLESPACE statement, or CREATE DATABASE command included a DMS container from another database that no longer exists but was not dropped properly. The container is not actually in use but it is tagged as being in use. Therefore, the DB2 data server will not allow it to be used until it is untagged. However, it is very important to verify that the container is not in use by the same database or another database when untagging it. If the container is in use when you untag it, the database (or databases) involved will be corrupted.
• A REORG attempted to automatically select a DMS temporary table space for use, and although one with the correct page size exists, it is currently in use by another REORG command.
• The ADD DBPARTITIONNUM option of the REDISTRIBUTE command, which was used to add a database partition, creates table space container names on the newly added database partition based on the table space container names of the table spaces on the lowest numbered database partition. If these container names specify absolute paths, and if the new database partition is on the same physical device as a database partition using the same container names, then the containers for the new partition would already be in use.
• A RESTORE DATABASE command has found containers from a database that no longer exists but was not dropped properly.
• A temporary staging database was created for a transport operation, and then an attempt was made to create a table space on the target database while the temporary staging database still exists.

Ensure the containers are unique.

• For the CREATE or ALTER TABLESPACE statement, specify a different container for the table space.
• For the CREATE or ALTER TABLESPACE statement including a container from a dropped table space, try again after the drop statement is committed or specify a different container.
• For the ALTER DATABASE PARTITION statement, reissue the statement using the WITHOUT TABLESPACES clause, and then use the ALTER TABLESPACE statement to create unique containers for the new database partition.
• For CREATE or ALTER TABLESPACE statements where the environment includes more than one logical database partition on a physical database partition, ensure that the same containers are not specified for such logical database partition.
• For an ADD DATABASE PARTITION command or API, reissue the statement using the WITHOUT TABLESPACES clause, and then use the ALTER TABLESPACE statement to create unique containers at the new database partition for the system temporary table spaces.
• If you are trying to use a DMS container that belonged to a database that no longer exists but was not dropped properly, then the db2untag utility can be used to remove the DB2 container tag from it. When this tag is removed DB2 considers the container to be free and the container can be used in a CREATE TABLESPACE statement, ALTER TABLESPACE statement, or CREATE DATABASE command.

  NOTE: Use extreme caution with db2untag. If you issue a db2untag command against a container that is still in use by a database, then both the database that originally used the container and the database that is now using the container will be corrupted.

• For REORG, resubmit the command once the initial REORG that is using the required table space completes, or provide another temporary table space of the correct page size for use.
• For the REDISTRIBUTE command, instead of using the ADD DBPARTITIONNUM option, before issuing the REDISTRIBUTE command issue the ALTER DATABASE PARTITION GROUP statement using the WITHOUT TABLESPACES clause, and then use the ALTER TABLESPACE statement to create unique containers for the new database partition.
• For the RESTORE DATABASE command (where a container belongs to a database that no longer exists but was not dropped properly), remove the container.

  NOTE: Ensure that the container is not in use by another database prior to removing it.

• If a temporary staging database was created for a transport operation, remove the temporary staging database after the staging database is no longer needed, and then attempt operations on the target that were blocked as a result of the existence of the staging database.

sqlcode: -294

sqlstate: 42730

SQL0295N

The total space required to store the list of containers exceeds the space allotted for this table space in the table space file.

sqlcode: -295

sqlstate: 54034

SQL0296N

This message is returned when an attempt is made to create a database object when there are already the maximum number of that type of database object defined for the database.

Respond to this error in one of the following ways:

• Delete any database objects of the same type that are not being used any more, and then reissue the CREATE statement.
• For table spaces:
  1. Move data from multiple, small table spaces into one, larger table space.
  2. Delete the original, small table spaces.
  3. Reissue the CREATE statement.
• For Hadoop tables:
  • If the limit exceeded is the number of data types defined in the database, drop a type previously created by CREATE TYPE ARRAY or CREATE TYPE ROW or drop the Hadoop table which has column of type ARRAY or ROW.

sqlcode: -296

sqlstate: 54035

SQL0297N

Shorten the path length.

sqlcode: -297

sqlstate: 54036

SQL0298N

The container path violates one of the following requirements:

• Container paths must be valid fully-qualified absolute paths or valid relative paths. The latter are interpreted relative to the database directory.
• For EXTEND, REDUCE, RESIZE and DROP operations, the specified container path must exist.
• The path must be read/write accessible to the instance id (check file permissions on UNIX-based systems).
• Containers must be of the type specified in the command (directory, file or device).
• Containers (directories) in system managed table spaces must be empty when designated as containers and must not be nested underneath other containers.
• The containers for one database must not be located underneath the directory of another database, and they may not be underneath any directory that appears to be for another database. This rules out any directory of the form SQLnnnnn, where 'n' is any digit.
• The container must be within the file size limit for the operating system.
• Containers (files) for dropped database managed table spaces can only be reused as containers (directories) for system managed table spaces after all agents terminate and vice versa.
• During a redirected restore, an SMS container was specified for a DMS table space or a DMS container was specified for an SMS table space.
• The specified type of the container for an EXTEND, REDUCE, RESIZE, or DROP operation does not match the type of the container (FILE or DEVICE) that was specified when the container was created.

This message will also be returned if any other unexpected error occurred which prevents DB2 from accessing the container.

If you are using a cluster manager, this error can be returned if the database manager failed to add the database container path to the cluster manager configuration. If the cluster manager cannot access this path, the cluster manager will not be able to successfully manage a failover involving this path. Error messages from the cluster manager will be recorded in the db2diag log file.

Specify another container location or change the container to make it acceptable to DB2 (such as changing file permissions) and try again.

If you are using a cluster manager, correct the problem and resubmit the command:

1. Review the db2diag log file for error messages from the cluster manager.
2. Respond to the cluster manager error messages in the db2diag log file to correct the underlying problem that prevented the database manager from adding the path to the cluster manager configuration.
3. Resubmit the command.

sqlcode: -298

sqlstate: 428B2

SQL0299N

The container that you are attempting to add has already been assigned to the table space.

Choose another container and try again.

sqlcode: -299

sqlstate: 42731

SQL0301N

A variable, expression, or parameter in position number could not be used as specified in the statement because its data type is incompatible with the intended use of its value.

This error can occur as a result of specifying an incorrect host variable or an incorrect SQLTYPE value in a SQLDA on an EXECUTE or OPEN statement. In the case of a user-defined structured type, the associated built-in type of the host variable or SQLTYPE might not be compatible with the parameter of the TO SQL transform function defined in the transform group for the statement. In the case of performing an implicit or explicit cast between character and graphic data types, this error indicates that such a cast was attempted with a non-Unicode character or graphic string.

The statement cannot be processed.

Verify that the data types of all host variables in the statement are compatible with the manner in which they are used.

sqlcode: -301

sqlstate: 07006

SQL0302N

The value of an input host variable was found to be out of range for its use in the SELECT, VALUES, or prepared statement.

One of the following occurred:

• The corresponding host variable or parameter marker used in the SQL statement is defined as string, but the input host variable contains a string that is too long.
• The corresponding host variable or parameter marker used in the SQL statement is defined as numeric, but the input host variable contains a numeric value that is out of range.
• The terminating NUL character is missing from the C language NUL-terminated character string host variable.
• Federated system users: in a pass-through session, a data source-specific restriction might have been violated.

This error occurs as a result of specifying either an incorrect host variable or an incorrect SQLLEN value in an SQLDA on an EXECUTE or OPEN statement.

The statement cannot be processed.

Ensure that the input host variable value is the correct type and length.

If the input host variables supply values to parameter markers, match values with the implied data type and length of the parameter marker.

Federated system users: for a pass-through session, determine what data source is causing the error.

Examine the SQL dialect for that data source to determine which specific restriction has been violated, and adjust the failing statement as needed.

sqlcode: -302

sqlstate: 22001, 22003

SQL0303N

An embedded SELECT, VALUES, FETCH or assignment statement assigns to a host variable, but the data type of the variable is not compatible with the data type of the corresponding SELECT-list, VALUES-list, or right-hand side of the assignment statement element. For a user-defined data type, the host variable might be defined with an associated built-in data type that is not compatible with the result type of the FROM SQL transform function defined in the transform group for the statement. For example, if the data type of the column is date or time, the data type of the variable must be character with an appropriate minimum length.

The statement cannot be processed.

Verify that the table definitions are current and that the host variable has the correct data type. For a user-defined data type, verify that the associated built-in type of the host variable is compatible with the result type of the FROM SQL transform function defined in the transform group for the statement.

sqlcode: -303

sqlstate: 42806

SQL0304N

A FETCH, VALUES, SELECT, or assignment into a host variable list failed because the host variable was not large enough to hold the retrieved value.

The statement cannot be processed. No data was retrieved.

Verify that table definitions are current and that the host variable has the correct data type. For the ranges of SQL data types, refer to the SQL Reference.

Federated system users: for the ranges of data types that are returned from a data source, refer to the documentation for that data source.

sqlcode: -304

sqlstate: 22001, 22003

SQL0305N

A FETCH, assignment, or embedded SELECT or VALUES operation resulted in the retrieval of a NULL value to be inserted into a host variable for which no indicator variable was provided. An indicator variable must be supplied if a column can return a NULL value.

The statement cannot be processed. No data was retrieved.

Examine the definition of the FETCH or SELECT object table, the elements of the VALUES list, or the right-hand side of the assignment statement. Correct the program to provide indicator variables for all host variables where NULL values from those columns can be retrieved.

sqlcode: -305

sqlstate: 22002

SQL0306N

The host variable name is not declared in any DECLARE SECTION.

The statement cannot be processed.

Ensure that the host variable is declared and the name spelled correctly.

SQL0307N

The host variable name has already been declared in a DECLARE SECTION.

The definition is ignored. The previous definition is used.

Ensure that the host variable name is spelled correctly and the name is defined only once for each program.

SQL0308N

The limit on the number of host variables is dependent on how many will fit in the HOST_VARS column of SYSPLAN. This limit has been reached.

The remaining variable declarations are ignored.

Either simplify the program, split the program into smaller, separate programs, or do both.

SQL0309N

The value of an input host variable was found to be NULL but the corresponding use in the SELECT, VALUES, or prepared statement did not specify an indicator variable.

The statement cannot be processed.

Ensure you need a USING clause. Otherwise, ensure an indicator variable is specified only if required.

sqlcode: -309

sqlstate: 07002

SQL0310N

The maximum number of host variables was exceeded in the statement.

The statement cannot be processed.

Ensure the statement has fewer host variables or is less complex.

SQL0311N

When evaluated, the length specification for the string host variable, whose entry in the SQLDA is indicated by <var-number> (based at 1), was negative or greater than the maximum defined for that host variable.

The statement cannot be processed.

Correct the program to ensure that the lengths of all string host variables are not negative or that they are not greater than the maximum allowed length.

sqlcode: -311

sqlstate: 22501

SQL0312N

This message can be returned when a host variable is used in the following invalid ways:

• A host variable, variable_name, is used in a dynamic SQL statement or in a DDL statement.
• A host variable, variable_name, that is a structure is used in a place where structure references are not permitted.

Take one of the following steps and then execute the statement again:

• In dynamic SQL statements, use parameter markers instead of host variables.
• In DDL statements, remove references to host variables.
• Replace the structure reference with a host variable that is not a structure.
• Remove the host variables from expression keys in any expression-based index definitions.
• If you declared multiple structure arrays, remove all but one of the declarations.

sqlcode: -312

sqlstate: 42618

SQL0313N

The number of variables specified in the EXECUTE or OPEN statement does not equal the number of variables required for the parameter markers appearing in the SQL statement.

If a parameterized cursor is referenced, the number of cursor arguments is not the same as the number expected.

Correct the application program so the number of variables specified in the EXECUTE or OPEN statement is correct for the parameter markers in the SQL statement.

Correct the parameterized cursor reference so that the correct number of arguments is specified.

sqlcode: -313

sqlstate: 07001, 07004

SQL0314N

The host variable name is not declared correctly for one of the following reasons:

• The type specified is not one that is supported.
• The length specification is 0, negative, or too large.
• An initializer is used.
• An incorrect syntax is specified.
• A host variable array with cardinality larger than the maximum allowed value is specified.

The variable remains undefined.

Ensure that you correctly specify only the declarations the database manager supports.

SQL0315N

The variable remains undefined.

Ensure that you correctly specify only the declarations the database manager supports.

SQL0317N

The end of input was reached during processing of a DECLARE SECTION.

Precompilation is terminated.

Add an END DECLARE SECTION statement to end a DECLARE SECTION.

SQL0318N

An END DECLARE SECTION statement was found, but there was no previous BEGIN DECLARE SECTION.

The statement cannot be processed.

Enter a BEGIN DECLARE SECTION before an END DECLARE SECTION.

SQL0324N

Either the INDICATOR variable name is not a small integer or the STATEMENT variable name is not a character data type.

The statement cannot be processed.

Ensure that the variable is the correct type and is specified correctly.

SQL0327N

If the operation is an insert or an update, the value of the table partitioning key for the row is not within range of values for any defined data partition.

If the operation is altering a table to become a partitioned table, then there exists one or more rows in the table with a table partitioning key value that is not within the range of values for any defined data partition.

The statement cannot be processed.

For an insert or update operation, ensure that the table partitioning key columns are within the specified ranges for the data partitions of the table.

An additional data partition may need to be added to the table.

If altering a table to become a partitioned table, ensure that the necessary ranges are specified to allow all of the data in the table to be within a range of a data partition.

sqlcode: -327

sqlstate: 22525

SQL0329N

String constant or input host variable string-constant-or-host-variable contains a path name list that is not valid. The maximum number of schema names for the SQL path (FUNCPATH bind option or CURRENT PATH special register) or package path (CURRENT PACKAGE PATH special register) has been exceeded. See the Limits appendix of the SQL Reference for details of this limit.

The statement or command cannot be processed.

Specify fewer schema names so as to not exceed the limit. Consider consolidating user-defined functions, procedures, methods, distinct types, or packages to fewer schemas.

sqlcode: -329

sqlstate: 0E000

SQL0330N

If the reason-code is 12, code-point is the invalid code point. Otherwise, code-point is either blank or an additional reason code returned by an exit. If the string is the value of an input host variable, the position-number is the ordinality of the variable in the SQLDA. If the string is not the value of a host variable, the position-number is blank.

The statement cannot be processed.

sqlcode: -330

sqlstate: 22021

SQL0332N

The operation failed because there is no code page conversion between the source-code-page and target-code-page. This may be due to one of the following reasons:

1. The character repertoires of the source code page and the target code page are incompatible, and therefore may cause character loss and corruption when converting between the source and target code pages.
2. This specific code page conversion is not supported.

Some of the possible operations that can cause this error include:

• Connecting a client to a database where the client's code page is different from the database code page.
• Executing an SQL statement where the client's code page is different from the database code page.
• Importing or exporting an IXF file where the file's code page is different from the database code page.
• Federated system users: the data source does not support the specified code page conversion.

1. Make the source and target code pages compatible with each other. To set the client's code page compatible with the database code page:
   • On Unix platforms, set the LANG, LC_CTYPE or LC_ALL environment variable to a locale whose code page is compatible with the database code page. Consult the platform documentation to see the valid locale names and the code page associated with each of them.
   • On Windows platforms, set the DB2CODEPAGE registry variable to override the client's code page with a value compatible with the database code page.
2. If the source and target code pages are compatible, then DB2 currently does not support this particular code page conversion. Contact your technical service representative to determine if such support can be added.

DB2 UDB for iSeries users should be aware that character or graphic columns with CCSID 65535 are not supported. Character or graphic columns with CCSID 65535 must be converted to a supported CCSID (using CAST) before they can be accessed using DB2 Connect.

sqlcode: -332

sqlstate: 57017

SQL0334N

During the execution of the SQL statement, a code page conversion operation has resulted in a string that is longer than the maximum size of the target object.

Modify the data to avoid the overflow condition, depending on the circumstances, by:

• decreasing the length of the source string or increasing the size of the target object (refer to the note that follows this list),
• altering the operation,
• casting the encrypted data value to a VARCHAR string with a larger number of bytes before using it in a decryption function, or
• ensuring that the application codepage and the database codepage are the same. This eliminates the need for codepage conversions for most connections.

Note: Automatic promotion of character or graphic string data types will not occur as part of character conversion. If the resultant string length exceeds the maximum length of the data type of the source string then an overflow has occurred. To correct this situation either change the data type of the source string or use data type casting to allow for an increase in the string length due to conversion.

sqlcode: -334

sqlstate: 22524

SQL0336N

The decimal number is used in a context where the scale must be zero. This can occur when a decimal number is specified in a CREATE or ALTER SEQUENCE statement for START WITH, INCREMENT, MINVALUE, MAXVALUE or RESTART WITH.

The statement cannot be processed.

Change the decimal number to remove any non-zero digits from the right side of the decimal delimiter.

sqlcode: -336

sqlstate: 428FA

SQL0338N

The statement cannot be processed.

Correct the ON clause to reference appropriate columns or remove any dereference operators.

If using full outer join ensure that all functions in the ON clause are deterministic and have no external action.

For the ON clause of a MERGE statement, remove any subqueries, scalar fullselects, inlined SQL functions, or inlined SQL methods.

sqlcode: -338

sqlstate: 42972

SQL0340N

The common table expression name name is used in the definition of more than one common table expressions in the statement. The name used to describe a common table expression must be unique within the same statement.

The statement cannot be processed.

Change the name of one of the common table expressions.

sqlcode: -340

sqlstate: 42726

SQL0341N

The common table expression name1 refers to name2 in a FROM clause within its fullselect and name2 refers to name1 in a FROM clause within its fullselects. Such forms of cyclic references are not allowed.

The statement cannot be processed.

Remove the cyclic reference from one of the common table expressions.

sqlcode: -341

sqlstate: 42835

SQL0342N

The statement cannot be processed.

Remove the keyword DISTINCT from the common table expression, add the keyword ALL following UNION, or remove the recursive reference within the common table expression.

sqlcode: -342

sqlstate: 42925

SQL0343N

The recursive common table expression name must include the specification of the column names following the identifier of the common table expression.

The statement cannot be processed.

Add column names following the identifier of the common table expression.

sqlcode: -343

sqlstate: 42908

SQL0344N

The recursive common table expression name has a column column-name that is referred to in the iterative fullselect of the common table expression. The data type, length and code page are set based on the initialization fullselect for this column. The result of the expression for the column column-name in the iterative fullselect has a different data type, length or code page that may result in failure to assign the value for the column.

The statement cannot be processed.

Correct the column used in the fullselects of the recursive common table expression so that the initialization column matches the iterative columns.

sqlcode: -344

sqlstate: 42825

SQL0345N

The statement cannot be processed.

sqlcode: -345

sqlstate: 42836

SQL0346N

The statement cannot be processed.

sqlcode: -346

sqlstate: 42836

SQL0347W

The absence of this syntax in the recursive common table expression may result in an infinite loop. The data or some other characteristic of the recursive common table expression may allow the successful completion of the statement anyway.

To prevent an infinite loop, include the expected syntax as described.

sqlcode: +347

sqlstate: 01605

SQL0348N

The statement cannot be executed.

Remove the reference to the sequence expression and resubmit the statement.

sqlcode: -348

sqlstate: 428F9

SQL0349N

Correct the syntax and resubmit the statement.

sqlcode: -349

sqlstate: 560B7

SQL0350N

The ALTER statement, CREATE statement, or DECLARE GLOBAL TEMPORARY TABLE statement is invalid for one of the following reasons:

• A LOB, LONG VARCHAR, LONG VARGRAPHIC, XML, or structured type column cannot be used in a key.
• A LOB, LONG VARCHAR, LONG VARGRAPHIC, XML, or structured type column cannot be used in a unique constraint.
• A LOB, LONG VARCHAR, LONG VARGRAPHIC, XML, or structured type column cannot be used in a generated column.
• A LONG VARCHAR, LONG VARGRAPHIC, SYSPROC.DB2SECURITYLABEL, XML, or structured type column cannot be used in a created temporary table.
• A LONG VARCHAR, LONG VARGRAPHIC, SYSPROC.DB2SECURITYLABEL, distinct type, or structured type column cannot be used in a declared temporary table.
• A LOB column cannot be used in an index definition.
• An XML column can only be used in an index definition if it is the only column and an XMLPATTERN clause is specified.
• A structured type column can only be used in an index definition if it is the only column and has an associated index extension specified.
• The definition of a column mask or a row permission cannot reference a LOB or XML column.
• A column mask cannot be defined for a LOB or XML column.
• In Hadoop tables:
  • Columns whose type is array cannot be used in a unique definition.
  • Columns whose type is row cannot be used in a unique definition.
  • Columns whose type is array cannot be used in primary key definitions.
  • Columns whose type is row cannot be used in primary key definitions.
  • Columns whose type is array cannot be used in index definitions.
  • Columns whose type is row cannot be used in index definitions.
  • The first column cannot have a data type of array or row.

The same restrictions apply to a distinct type column where the base data type is subject to one of the restrictions in the listed previously.

The statement cannot be processed.

Remove the column from the context where the data type is not supported or change the data type of the column. If indexing an XML column or structured type column, ensure that it is the only column and that the index definition includes the appropriate clauses.

sqlcode: -350

sqlstate: 42962

SQL0351N

The element of the SQLDA at position position-number is for a data type that either the application requestor or the application server does not support. If the application is not using the SQLDA directly, position-number could represent the position of an element in the select list or a parameter of a CALL statement.

The statement cannot be processed.

Change the statement to exclude the unsupported data type. For a select statement, remove the names of any columns in the select-list with the unsupported data type or use a cast in the query to cast the column to a supported data type.

sqlcode: -351

sqlstate: 56084

SQL0352N

The element of the SQLDA at position position-number is for a data type that either the application requestor or the application server does not support. If the application is not using the SQLDA directly, position-number could represent the position of an input host variable, parameter marker, or a parameter of a CALL statement.

The statement cannot be processed.

Change the statement to exclude the unsupported data type.

sqlcode: -352

sqlstate: 56084

SQL0355N

Large Object data types (BLOB, CLOB, and DBCLOB) may be created in sizes up to 2 gigabytes (2147483647 bytes). Logging of data values is allowed only upon objects which are less than or equal to than 1 gigabyte (1073741823 bytes) in size. Therefore, large objects greater than 1 gigabyte in size can not be logged.

Either explicitly indicate logging of data is not required, by using the NOT LOGGED phrase during column creation, or reduce the maximum size of the column to 1 gigabyte or below.

sqlcode: -355

sqlstate: 42993

SQL0356N

You can create a table index which includes expression-based keys. This message is returned when an attempt is made to create an index that includes expression-based keys, and one of the following conditions exists:

• There is something invalid about one of the expression-based key definitions
• The table does not support expression-based keys

The runtime token expression-number identifies which key expression in the statement is invalid. For example, if there are two key expressions in the CREATE INDEX statement, and the second key expression is invalid, the value of expression-number will be: "2". If the expression number cannot be determined, a default value of "*" will be returned.

The reason code indicates what was invalid about the key expression:

1

The key expression contained a subquery.

2

The key expression did not contain a reference to at least one column.

3

The key expression referenced a special register or the key expression referenced a function that depends on the value of special register.

5

The key expression included a user-defined function.

6

The same key expression appears more than once in the index definition.

15

The key expression referenced a global variable or the key expression referenced a function that depends on the value of a global variable.

16

The key expression referenced a sequence.

17

The key expression referenced an unsupported type of function:

• A non-deterministic function
• A function with external actions
• A function that has an unsupported access level:
  • READ SQL DATA
  • MODIFIES SQL DATA

18

The key expression included a value with an unsupported data type.

19

The key expression included an aggregate function or an OLAP specification.

20

The SCOPE clause was included in the index definition and the key expression contained one of the following:

• Dereference operation
• A TYPE predicate
• A CAST specification

21

The key expression includes an XMLQUERY expression or an XMLEXISTS expression.

22

An attempt was made to create an expression-based index on a table that does not support expression-based indexes.

23

The key expression referenced an unsupported type of function such as the LIKE predicate.

24

The result data type of the expression-based key is not indexable.

Correct the error in the key expression, and reissue the statement.

sqlcode: -356

sqlstate: 429BX

SQL0359N

The database manager attempted to generate a value for an identity column or sequence object, however all allowable values have already been assigned.

The statement cannot be processed.

For an identity column, redefine the table with a larger range of values for the identity column. If a MAXVALUE or MINVALUE specification has been made that limits the range of values to be less than the range for the data type of the column, then the column can be altered to expand the range of valid values. Otherwise, the identity column must be recreated which requires that the table be recreated. First drop the existing table, and then recreate the table with a different data type for the identity column, specifying a data type that has a larger range of values than the current data type for the identity column.

For a sequence object, redefine the sequence with a larger range of values. If a MAXVALUE or MINVALUE clause limits the range of values to less than the range for the data type of the sequence object, then alter the sequence to expand the range of valid values. Otherwise, drop the sequence object and reissue the CREATE SEQUENCE statement specifying a data type that allows a larger range of values.

sqlcode: -359

sqlstate: 23522

SQL0360W

DATALINK values in table table-name may not be valid because the table is either in Datalink Reconcile Pending (DRP) or Datalink Reconcile Not Possible (DRNP) state. While in either of these states, control of the files on the DB2 Data Links Manager is not guaranteed.

Statement processing continues.

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

sqlcode: +360

sqlstate: 01627

SQL0361W

The operation was successful as some of what was specified completed successfully, but some parts of the operation were unsuccessful.

Determine what action to take, if any, depending on the operation msg-token3: SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS - Some of the objects specified to be revalidated were not able to be revalidated successfully because at least one object that they reference does not exist or continues to be invalid. One object not revalidated is msg-token1 because of its reference to object msg-token2. If the objects not yet revalidated still need to be revalidated, then create any objects that should exist and correct any objects that continue to be invalid that are referenced by the objects to be revalidated. The SYSCAT.INVALIDOBJECTS catalog view contains information on invalid objects. If msg-token1 refers to an object of permission or mask, and msg-token2 contains CREATE PERMISSION or CREATE MASK, then ensure the user that runs ADMIN_REVALIDATE_DB_OBJECTS has SECADM authority.

sqlcode: +361

sqlstate: 0168B

SQL0364W

The exception exception-type occurred while doing an operation-type operation on a field whose data type is DECFLOAT. The exception occurred while processing an arithmetic expression.

The DECFLOAT exception may occur due to a temporary internal copy of the data that was converted to DECFLOAT for the operation.

Any of the exceptions can occur during the processing of a built-in function. If the operation-type is FUNCTION, then the exception occurred while processing either an input, intermediate, or final value. The cause could be that the value of a parameter is out of range.

Statement processing continues.

To stop this warning from being returned, examine the expression for which the warning occurred to see if the cause, or the likely cause, of the exception can be determined. The exception may be data-dependent, in which case it will be necessary to examine the data that was being processed at the time the warning occurred. See the SQL Reference for the supported ranges of DECFLOAT values.

sqlcode: +364

sqlstate: 0168C

sqlstate: 0168D

sqlstate: 0168E

sqlstate: 0168F

SQL0365N

You can represent the SQL null value in an application program by using an indicator variable with a host variable. You can also represent the default value or the unassigned value by using an extended indicator with host variables.

There are some restrictions on how and where extended indicators can be used. For example, values of default (-5) or unassigned (-7) must not be used for extended indicator variables in a VALUES clause that specifies more than one row of values.

This message is returned when a value of default (-5) or unassigned (-7) is used in a context in which these values are not allowed.

Diagnostic information in the runtime token position:

• In general, the position given by the runtime token postition is the ordinal of the host variable that is using the extended indicator.
• If position is 0, a mismatch in extended indicator support occurred. Extended indicator support was not explicitly enabled or disabled for the dynamically prepared UPDATE statement, and there was a mismatch between the extended indicator support that was explicitly enabled or disabled for the dynamically prepared SELECT statement that is the target of the update and the extended indicator support for the bind or precompile.

Change the value of the extended indicator variable to an allowable value for the context in which it is used.

If position is 0, respond in one of the following ways:

• Explicitly enable or disable extended indicator support for the dynamically prepared UPDATE statement.
• Ensure that the extended indicator support specified for the dynamically prepared SELECT statement that is the target of the update is the same as the extended indicator support for the bind or precompile.

sqlcode: -365

sqlstate: 22539

SQL0368N

The DB2 Data Links Manager dlm-name is not registered to the database. Registered DB2 Data Links Managers are ignored if the database manager configuration parameter DATALINKS is set to NO. The DB2 Data Links Manager may have been dropped using the DROP DATALINKS MANAGER command. It is possible that a new incarnation of the DB2 Data Links Manager with the same name is currently registered; in that case, the error pertains to one or more previously dropped incarnations of that DB2 Data Links Manager.

The statement cannot be processed.

Ensure that the database manager configuration parameter DATALINKS is set to YES. DATALINK values that are links to previously dropped DB2 Data Links Managers should be removed using the reconcile utility. For additional details, see the usage notes for the DROP DATALINKS MANAGER command in the Command Reference.

sqlcode: -368

sqlstate: 55022

SQL0370N

All parameters for functions defined with LANGUAGE SQL must have a parameter-name for each parameter.

The statement cannot be processed.

Include a parameter name for each parameter of the function.

sqlcode: -370

sqlstate: 42601

SQL0372N

An attempt was made to do one of the following:

• Create a table with more than one IDENTITY column.
• Add an IDENTITY column to a table which already has one.
• Create a table with more than one ROWID column.
• Add a ROWID column to a table which already has one.
• Create a table with more than one security label column.
• Add a security label column to a table that already has one.
• Create a table with more than one row change timestamp column.
• Add a row change timestamp column to a table that already has one.
• Create a table with more than one row-begin column.
• Add a row-begin column to a table that already has one.
• Create a table with more than one row-end column.
• Add a row-end column to a table that already has one.
• Create a table with more than one transaction start-ID column.
• Add a transaction start-ID column to a table that already has one.
• Define a period more than once in a table.

The ROWID data type is supported in DB2 for z/OS and DB2 for iSeries.

The statement cannot be processed.

For a CREATE TABLE statement, specify the specified attribute only once for the table. For an ALTER TABLE statement, the specified column already exists for the table. Do not attempt to define a period more than once in a table.

sqlcode: -372

sqlstate: 428C1

SQL0373N

A DEFAULT clause was specified when defining or changing column or SQL variable name. The data type for the column definition or SQL variable declaration does not support DEFAULT. A CREATE or ALTER TABLE statement cannot use the DEFAULT clause when defining the following:

• an identity column
• a ROWID column
• an XML column
• a row change timestamp column
• a security label column
• a row-begin column
• a row-end column
• a transaction-start-ID column
• a random distribution key column of a random distribution table using random by generation method

The statement cannot be processed.

Remove the DEFAULT clause and resubmit the statement.

sqlcode: -373

sqlstate: 42623

SQL0374N

The following situations may be the cause of this error.

MODIFIES SQL DATA must be specified if the body of the function defined with LANGUAGE SQL can modify SQL data or if it calls a function or a procedure that can modify SQL data.

READS SQL DATA must be specified if the body of the function defined with LANGUAGE SQL contains a subselect or if it calls a function that can read sql data.

The statement cannot be processed.

Either specify the clause or change the function body.

sqlcode: -374

sqlstate: 428C2

SQL0385W

The SQL routine includes at least one statement that assigns a value to the SQLSTATE or SQLCODE special variables. These variables are assigned values by processing of SQL statements in the SQL routine. The value assigned may therefore be over-written as a result of the SQL statement processing. Furthermore, the assignment of a value to the SQLSTATE special variable does not activate any handlers.

The routine definition was processed successfully.

None required. Remove any assignments to the SQLSTATE or SQLCODE special variable to prevent the warning.

sqlcode: +385

sqlstate: 01643

SQL0388N

One of the data types must be a user-defined type. The source type and the target type cannot be the same data type.

The statement cannot be processed.

Change the data type of either the source or the target.

sqlcode: -388

sqlstate: 428DF

SQL0389N

The statement cannot be processed.

Either chose a different function instance, change the source data type, or change the target data type.

sqlcode: -389

sqlstate: 428DG

SQL0390N

If the runtime token specific-name is an empty string, then the function resolved to the built-in function identified by the runtime token function-name.

This message can be returned in many scenario. Here is a short list of some of the possible situations in which this message can be returned:

• The specific function is a scalar, column, or row function where only a table function is expected (such as in the FROM clause of a query).
• The specified function has OUT or INOUT parameters and the context in which the function is used is not supported. A compiled function can be invoked only if the function invocation is the sole expression on the right hand side of a SET variable statement that is in a compound SQL (compiled) statement.
• The specified function is not a generic table function, but a typed-correlation clause was specified.

Respond to this error by performing one or more of the following troubleshooting steps:

• Confirm that the function name and arguments are correct.
• Confirm that the current path includes the schema where the correct function is defined.
• Change the context in which the function is used.

sqlcode: -390

sqlstate: 42887

SQL0391N

The statement cannot be processed.

Remove function-name from the context where it is not allowed.

sqlcode: -391

sqlstate: 42881

SQL0392N

The application is running with DB2 rules, and has requested that LOB data be returned as a LOB in one FETCH statement, and as a locator in another FETCH statement. This is not permitted.

The statement cannot be executed.

Either do not use DB2 rules, or change the application to not change the data type code from LOB to locator (or the reverse) in the SQLDA between successive fetches.

sqlcode: -392

sqlstate: 42855

SQL0396N

A routine object-name (specific name specific-name) attempted to execute an SQL statement (other than CLOSE cursor) during FINAL CALL (call-type = 255) processing. This is not allowed.

Change the routine to not issue SQL statements during FINAL CALL (call-type = 255) processing.

sqlcode: -396

sqlstate: 38505

SQL0401N

The operation using operator cannot be processed for one of the following reasons:

• The data types of the operands must be comparable and compatible but there is at least one pair of types that is not comparable or compatible.
• The data type of an operand is XML which cannot be compared (with itself or any other type).
• The data type of an operand is DATALINK which cannot be compared (with itself or any other type).
• The data type of an operand is a structured type which cannot be compared (with itself or any other type).
• Columns of type array from two Hadoop tables cannot be compared directly.
• Columns of type row from two Hadoop tables cannot be compared directly.

Federated system users: this data type violation can be at the data source or at the federated server.

Some data sources do not provide the appropriate values for operator. In these cases the message token will have the following format: "<data-source>:UNKNOWN", indicating that the actual value for the specified data source is unknown.

The statement cannot be processed.

Check all operand data types to ensure that they are comparable and compatible with the statement usage.

If all the SQL statement operands are correct and accessing a view, check the data types of all the view operands.

In Hadoop tables:

• For array columns, restructure the comparison by comparing one element of the array at a time, then resubmit the operation.
• For row columns, restructure the comparison by comparing one element of the row at a time, then resubmit the operation.

Federated system users: if the reason is unknown, isolate the problem to the data source failing the request and examine the data type restrictions for that data source.

sqlcode: -401

sqlstate: 42818

SQL0402N

An invalid operand is specified for the arithmetic function or operator operator.

The statement cannot be processed.

Correct the SQL statement syntax so all specified function or operator operands are valid.

A valid operand is either numeric or is capable of being implicitly cast to a numeric data type.

Federated system users: if the reason is unknown, isolate the problem to the data source failing the request and examine the operators applied to that data source.

sqlcode: -402

sqlstate: 42819

SQL0403W

The alias <name> has been defined upon:

• a table, view, module, or sequence which currently is not defined in the database
• another alias which resolves to a table, view, module, or sequence which is not defined in the database.

The object <name2> is the undefined object. This object must exist before any SQL statement (other than CREATE ALIAS) can successfully use the newly created alias. The specified alias <name> is created.

An inoperative view is considered to be undefined for the purposes of creating an alias.

Ensure that the undefined object <name2> is defined prior to using the newly created alias in an SQL statement (other than CREATE ALIAS).

sqlcode: +403

sqlstate: 01522

SQL0404N

An INSERT or UPDATE statement specifies a value that is longer than the maximum-length string that can be stored in the column indicated.

The statement cannot be processed.

Check the length of the object column and correct the program or SQL statement so the insert or update string does not exceed the maximum length.

sqlcode: -404

sqlstate: 22001

SQL0405N

The specified numeric literal is not in the acceptable range.

The proper ranges for SQL values are as follows:

• For FLOAT values:
  • 0
  • In the positive range of +2.225E-307 to +1.79769E+308
  • In the negative range of -1.79769E+308 to -2.225E-307.
• -9999999999999999999999999999999. to 9999999999999999999999999999999. for DECIMAL values
• -2147483647 to 2147483647 for INTEGER values
• -32768 to +32767 for small integer (SMALLINT) values.

In certain contexts the literal may have further restrictions that result in this error.

Federated system users: this range violation can be at the data source or at the federated server. The proper ranges for SQL values located in data source tables depend on the data source. To determine the correct range, see the appropriate documentation for the data source. Some data sources do not provide the appropriate values for literal. In these cases the message token will have the following format: "<data source>:UNKNOWN", indicating that the actual value for the specified data source is unknown.

Range-clustered tables: the ending value of a range definition is smaller than the starting value.

The statement cannot be processed.

Reduce the literal value to the appropriate size.

Federated system users: if the reason is unknown, isolate the problem to the data source failing the request and examine the data range restrictions for that data source.

Range-clustered tables: ensure that the ending value in a range definition is greater than the starting value.

sqlcode: -405

sqlstate: 42820

SQL0406N

The value of a host variable or a numeric value calculated during processing of the UPDATE or INSERT SQL statement is outside the target column range. This problem may be caused by the values occurring in the object column, the SQL operation being performed on those values, or both.

The statement cannot be processed.

These are the limits of normal decimal floating-point numbers. Valid decimal floating-point values include the special values INFINITY, -INFINITY, NAN, -NAN, SNAN, and -SNAN. These special values are illegal for all other DB2 numeric types.

NOTE: For system catalog updates, see the SQL Reference for valid ranges in various columns of updatable catalogs.

sqlcode: -406

sqlstate: 22003

SQL0407N

One of the following occurred:

• The update or insert value was NULL, but the object column was declared as NOT NULL in the table definition. Consequently:
  • NULL values cannot be inserted into that column.
  • An update cannot set values in that column to NULL.
  • A SET transition-variable statement in a trigger cannot set values in that column to NULL.
• The update or insert value was DEFAULT, but the object column was declared as NOT NULL without WITH DEFAULT in the table definition. Consequently:
  • A default value of NULL cannot be inserted into that column.
  • An update cannot set default values of NULL in that column.
  • A SET transition-variable statement in a trigger cannot set default values of NULL in that column.
• The column name list for the INSERT statement omits a column declared NOT NULL and without WITH DEFAULT in the table definition.
• The view for the INSERT statement omits a column declared NOT NULL and without WITH DEFAULT in the base table definition.
• An alter table statement attempted to alter the column with SET NOT NULL, but the existing table data contained a row with the NULL value.

If the value for name is of the form "TBSPACEID=n1, TABLEID=n2, COLNO=n3", then the column name from the SQL statement was not available when the error was issued. The values provided identify the tablespace, table, and column number of the base table that does not allow NULL value.

Federated system users: this situation can be detected by the federated server or by the data source. Some data sources do not provide the appropriate values for name. In these cases the message token will have the following format: "<data source>:UNKNOWN", indicating that the actual value for the specified data source is unknown.

HBase or Hadoop users: This situation can occur when the underlying Hadoop or HBase data is being read and one of the following happens:

• The underlying data contained NULL in a column that is declared as NOT NULL.
• An invalid value was encountered (which is implicitly treated as NULL) in a column that is declared as NOT NULL.

The value of name can be specified as schema-name.table-name.column-name.

The statement cannot be processed.

Under some circumstances, the token name may not be filled in (sqlerrmc field of the SQLCA not filled in).

Correct the SQL statement after examining the object table definition to determine which columns of the table have the NOT NULL attribute and do not have the WITH DEFAULT attribute.

If the value for name is of the form "TBSPACEID=n1, TABLEID=n2, COLNO=n3", you can determine the table name and column name using the following query:

   SELECT C.TABSCHEMA, C.TABNAME,
          C.COLNAME
      FROM SYSCAT.TABLES AS T,
           SYSCAT.COLUMNS AS C
      WHERE T.TBSPACEID = n1
      AND T.TABLEID = n2
      AND C.COLNO = n3
      AND C.TABSCHEMA = T.TABSCHEMA
      AND C.TABNAME = T.TABNAME

The table and column identified by this query may be the base table of a view for which the SQL statement failed.

If the error is returned as part of an alter table statement, when setting a column to NOT NULL, modify any row data for the column that contains the NULL value, and retry the statement.

Federated system users: if the reason is unknown, isolate the problem to the data source failing the request and examine the object definition for that data source. Remember that the defaults (NULL and NOT NULL) are not necessarily the same between data sources.

For HBase or Hadoop users, verify that the data in the underlying data files is correct, or use ALTER TABLE to change the nullability of the column.

sqlcode: -407

sqlstate: 23502

SQL0408N

The data type of the value to be assigned to the column, parameter, SQL variable, or transition variable by the SQL statement is incompatible with the declared data type of the assignment target.

For Hadoop tables:

• Array column values cannot be assigned to array variables.
• Row column values cannot be assigned to row variables.
• Complex type (array or row) column values and variables are not compatible for assignment.

The statement cannot be processed.

Examine the statement and possibly the target table or view to determine the target data type. Ensure the variable, expression, or literal value assigned has the proper data type for the assignment target.

For a user-defined structured type, also consider the parameter of the TO SQL transform function defined in the transform group for the statement as an assignment target.

sqlcode: -408

sqlstate: 42821

SQL0409N

As specified in the SQL statement, the operand of the COUNT function does not conform to the rules of SQL syntax. Only COUNT(*) and COUNT(DISTINCT column) are allowed.

The statement cannot be processed.

Specify COUNT(*) or COUNT(DISTINCT column).

NOTE: This message is only applicable to versions of DB2 prior toVersion 2 .

sqlcode: -409

sqlstate: 42607

SQL0410N

The specified value is too long. A floating-point string has a maximum length of 30 characters. A decimal floating-point string has a maximum length of 42 characters.

The statement cannot be processed.

Shorten the specified literal.

sqlcode: -410

sqlstate: 42820

SQL0412N

In the context of the SQL statement, a fullselect is specified that can have only one column as a result.

The statement cannot be processed.

Specify only one column when only a scalar fullselect is allowed.

sqlcode: -412

sqlstate: 42823

SQL0413N

During processing of the SQL statement, an overflow condition arose when converting from one numeric type to another. Numeric conversion is performed according to the standard rules of SQL.

Federated system users: numeric conversion can occur at the federated server, at data sources, or both.

The statement cannot be processed. No data was retrieved, updated, or deleted.

Examine the syntax of the SQL statement to determine the cause of the error. If the problem is data-dependent, it may be necessary to examine the data processed at the time of the error.

Federated system users: if the reason is unknown, isolate the problem to the data source failing the request and examine the data range restrictions for that data source.

sqlcode: -413

sqlstate: 22003

SQL0415N

There are various statements where this error may occur.

• It may occur within a SELECT or VALUES statement that includes set operations (UNION, INTERSECT, or EXCEPT). The corresponding columns of the subselects or fullselects that make up the SELECT or VALUES statements are not compatible.
• It may occur within an INSERT statement that is inserting multiple rows. In this case, the corresponding columns of the rows specified in the VALUES clause are not compatible.
• It may occur within a SELECT or VALUES statement where the VALUES clause is used with multiple rows. In this case, the corresponding columns of the rows specified in the VALUES clause are not compatible.
• It may occur with a UNION ALL clause with two Hadoop tables, each having a column of type ARRAY.

It may occur within the array constructor, when the data type of the two values listed in the constructor are not compatible.

See Assignments and Comparisons in the SQL Reference for details on data type compatibility.

The statement cannot be processed.

Correct the column names used in the SELECT statements or the expressions in the VALUES clause so that all corresponding columns are compatible types.

sqlcode: -415

sqlstate: 42825

SQL0416N

One of the SELECT or VALUES statements connected by a set operator specifies a result column that is longer than 254 bytes. VARCHAR or VARGRAPHIC result columns longer than 254 bytes can be used only with the UNION ALL set operator.

The statement cannot be processed.

Either use the UNION ALL operator instead of UNION, or remove the result columns longer than 254 bytes from the SELECT or VALUES statements.

sqlcode: -416

sqlstate: 42907

SQL0417N

    ? > ? 

The statement cannot be processed.

This syntax is not supported. Use a CAST specification to give at least one of the parameter markers a data type.

sqlcode: -417

sqlstate: 42609

SQL0418N

This message can be encountered in many scenarios, and can be returned for multiple reasons.

Examples of reasons this message can be returned:

• The DEFAULT keyword was specified as the only argument of a datetime arithmetic operation.
• A parameter marker is used in a statement that is not a prepared statement. (Note that references to the PARAMETER function in the first argument of the XQuery function db2-fn:sqlquery are also considered to be parameter markers.)
• A null value was specified as an argument of an XML function (such as XMLQUERY, for example.)
• All result expressions of a CASE expression are untyped.

Respond to this error by performing the following troubleshooting steps:

1. Identify all instances of the following in the statement:
   • Untyped parameter markers
   • The DEFAULT keyword
   • Untyped null value
   • Untyped expressions
2. Review reference information for the context in which any untyped parameter markers, instances of the DEFAULT keyword, untyped null values, or untyped expressions appear.
3. Correct the syntax of the statement. Suggestions:
   • Use the CAST specification to give the untyped expressions a data type
   • Replace instances of the DEFAULT keyword with a value
4. Execute the statement again.

sqlcode: -418

sqlstate: 42610

SQL0419N

A specified decimal division is not valid because it will result in a negative scale.

The formula used internally to calculate the scale of the result for decimal division is:

Scale of result = 31 - np + ns - ds 

where np is the precision of the numerator, ns is the scale of the numerator, and ds is the scale of the denominator.

Federated system users: Decimal division can occur at the federated server, at data sources, or both. The specified decimal division results in an invalid scale for that data source.

The statement cannot be processed.

Examine and ensure the precision and scale of all columns that may participate in a decimal division. Note that an integer or small integer value may be converted to a decimal for this calculation.

Federated system users: if the reason is unknown, isolate the problem to the data source failing the request and examine the data range restrictions for that data source.

sqlcode: -419

sqlstate: 42911

SQL0420N

The function function-name has a character string argument that contains a character that is not valid in a numeric SQL constant. The function may have been called as a result of using the CAST specification with function-name as the target data type or when an argument is implicitly cast to a numeric data type. The function or data type used in the SQL statement may be a synonym for function-name.

If a decimal character is specified in the DECIMAL function then that is the character that must be used in place of the default decimal character.

Ensure that the character strings that are being converted to numeric types contain only characters that are valid in numeric SQL constants, using the decimal character, if specified.

sqlcode: -420

sqlstate: 22018

SQL0421N

The operands of a set operator such as UNION, EXCEPT, or INTERSECT must have the same number of columns. The rows in a VALUES clause must have the same number of columns.

The statement cannot be processed.

Change the SQL statement so each operand, or each row of a VALUES clause, has exactly the same number of columns.

sqlcode: -421

sqlstate: 42826

SQL0423N

A locator variable is in error. Either it has not had a valid result set locator or LOB locator variable value assigned to it, the locator associated with the variable has been freed, or the result set cursor has been closed.

If variable-position is provided, it gives the ordinal position of the variable in error in the set of variables specified. Depending on when the error is detected, the database manager may not be able to determine variable-position.

Instead of an ordinal position, variable-position may have the value "function-name RETURNS", which means that the locator value returned from the user-defined function identified by function-name is in error.

Correct the program or routine so that the locator variables used in the SQL statement have valid values before the statement is executed.

A LOB value can be assigned to a locator variable by means of a SELECT INTO statement, a VALUES INTO statement, or a FETCH statement.

Result set locator values are returned by the ASSOCIATE LOCATORS statements. Result set locator values are only valid as long as the underlying SQL cursor is open. If a commit or rollback operation is run, the result set locator associated with the cursor is no longer valid. If this was a WITH RETURN cursor, ensure the cursor is opened before attempting to allocate it.

If the following statements are all true about the application code:

• the application contains a cursor declared for a query that defines a result set containing LOB columns
• the cursor declaration contains the WITH HOLD clause
• LOB locators are used to reference the LOB values in the result set of the cursor
• the unit of work is committed before the cursor is closed

Do one of the following actions to remove a factor contributing to this warning case so as to successfully upgrade your application:

• Precompile your application again using the PREP command making sure to include the SQLRULES STD option.
• If possible, alter the application so that the LOB columns are retrieved as values instead of locators
• If possible, alter the application so that the cursor is no longer declared with the WITH HOLD option and remove the commit before the cursor is closed.

sqlcode: -423

sqlstate: 0F001

SQL0426N

An application executing in a CONNECT TYPE 2 environment or Distributed Transaction Processing (DTP) environment such as CICS has attempted to execute an SQL dynamic COMMIT statement. The SQL dynamic COMMIT statement cannot be executed in this environment.

Federated system users: SQL dynamic COMMIT statements cannot be executed during a pass-through session.

Federated system users: Either comment out the COMMIT statement or code it as a static statement. Then resubmit your program.

sqlcode: -426

sqlstate: 2D528

SQL0427N

An application executing in a CONNECT TYPE 2 environment or Distributed Transaction Processing (DTP) environment such as CICS has attempted to execute an SQL dynamic ROLLBACK statement. The SQL dynamic ROLLBACK statement cannot be executed in this environment.

Federated system users: SQL dynamic ROLLBACK statements cannot be executed during a pass-through session.

Federated system users: Either comment out the ROLLBACK statement or code it as a static statement. Then resubmit your program.

sqlcode: -427

sqlstate: 2D529

SQL0428N

The SQL statement that was issued is required to execute before any other SQL statement that initiates a unit of work. Following are possible situations:

• The SQL statement must be first in the unit of work and SQL has been issued against the connection within the unit of work.
• The SQL statement must be first in the unit of work and a WITH HOLD cursor is currently open for the connection.

Note that when the statement is DISCONNECT ALL, it causes the DISCONNECT to be directed against all connections, so the request will fail if any of the connections violate the previously described restrictions.

Issue a COMMIT or ROLLBACK prior to processing the SQL statement. If there are any WITH HOLD cursors, these will need to be closed. If the statement is SET INTEGRITY, remove the COMMIT THRESHOLD clause.

sqlcode: -428

sqlstate: 25001

SQL0429N

A maximum of 4,000,000 concurrent LOB locators per unit of work, is supported by DB2.

Modify the program so that it requires fewer concurrent LOB locators and run the program again. For example, use the FREE LOCATOR statement to free LOB locators as soon as the application no longer needs them.

sqlcode: -429

sqlstate: 54028

SQL0430N

An abnormal termination has occurred while the named UDF was in control.

The UDF needs to be fixed. Contact the author of the UDF or your database administrator. Until it is fixed, the UDF should not be used.

sqlcode: -430

sqlstate: 38503

SQL0431N

A user interrupt or client interrupt has occurred while the routine, trigger or anonymous block was in control.

An anonymous block has no object name or specific name and is identified with an object type of "BEGIN...END".

Encountering this message might indicate some problem in the logic, such as an infinite loop or wait.

If the problem persists, (that is, the need to interrupt results in the same error condition), then contact the author of the routine, trigger, or anonymous block or contact your database administrator.

sqlcode: -431

sqlstate: 38504

SQL0432N

An untyped expression (parameter marker or null value) in the statement has been determined as having the user-defined type udt-name or a reference type with the target type udt-name based on the context in which it is used. An untyped expression cannot have a user-defined type or reference type as its data type unless it is part of an assignment (VALUES clause of INSERT or SET clause of UPDATE) or it is being explicitly cast to a user-defined distinct data type or reference data type using the CAST specification.

The statement cannot be processed.

Use an explicit cast to the user-defined distinct data type or reference data type for the untyped expression. An alternative is to cast the columns that are user-defined distinct data types to their corresponding source data type or columns that are reference data types to their corresponding representation type.

sqlcode: -432

sqlstate: 42841

SQL0433N

The value value required truncation by a system (built-in) cast or adjustment function, which was called to transform the value in some way. The truncation is not allowed where this value is used.

The statement cannot be processed.

If value is a literal string in the SQL statement, it is too long for its intended use.

If value is not a literal string, examine the SQL statement to determine where the transformation is taking place. Either the input to the transformation is too long, or the target is too short.

Correct the problem and rerun the statement.

sqlcode: -433

sqlstate: 22001

SQL0434W

The value that was specified for clause clause is not supported and has been replaced with the identified supported value value.

No change is required if the selected value is acceptable. Otherwise, specify a value that is valid for clause.

sqlcode: +434

sqlstate: 01608

SQL0435N

The SQLSTATE value specified by the RAISE_ERROR function or on the SIGNAL or RESIGNAL statement does not conform to the rules for an application defined SQLSTATE.

Correct the specified value for the SQLSTATE. The SQLSTATE value must be a character string containing exactly 5 characters. It must be of type CHAR defined with a length of 5, or type VARCHAR defined with a length of 5 or greater. The SQLSTATE value must follow the rules for application-defined SQLSTATEs.

sqlcode: -435

sqlstate: 428B3

SQL0436N

The value of an input host variable code in the C programming language requires a NULL-terminator character at the end of the string.

The statement cannot be processed.

Ensure that the value of the input host variable is terminated by the NULL-terminator character.

sqlcode: -436

sqlstate: 22024

SQL0437W

Query optimization is one of the factors that affect application performance. The SQL and XQuery compiler performs several steps to produce an access plan that can be executed.

In general, this message is returned when the complexity of a query requires resources that are not available or when optimization boundary conditions were encountered.

The reason code indicates in more detail why the performance might be sub-optimal:

1

The join enumeration method was altered due to memory constraints

2

The join enumeration method was altered due to query complexity

3

Optimizer cost underflow

4

Optimizer cost overflow

5

Query optimization class was too low

6

Optimizer tolerated an inconsistent statistic

13

An optimization guideline could not be applied

15

Chosen plan materializes an intermediate result set, whose size might exceed the limit imposed by the registry variable DB2_OPT_MAX_TEMP_SIZE

16

The query contains XQuery transform expressions that are not parallelized in a partitioned database environment.

The statement will be processed.

If the performance of your query is sub-optimal, perform one or more of the following actions:

• Increase the size of the statement heap (stmtheap) in the database configuration file. After increasing the stmtheap configuration parameter, cause the statement to be recompiled. (Reason code 1)
• Break the statement up into less complex SQL statements. (Reason codes 1, 2, 3, 4)
• Ensure predicates do not over-specify the answer set. (Reason code 3)
• Change the current query optimization class to a lower value. (Reason codes 1, 2, 4)
• Use the RUNSTATS command for local tables, the NNSTAT procedure for nicknames, or the ANALYZE command for Hadoop and HBase tables that are involved in the query. (Reason codes 3, 4)
• Change the current query optimization class to a higher value. (Reason code 5)
• Reissue the RUNSTATS command (for local tables), the NNSTAT procedure (for nicknames), or the ANALYZE command (for Hadoop and HBase tables) for both the tables involved in the query and their corresponding indexes, that is, use the AND INDEXES ALL clause so that table and index statistics are consistent. (Reason code 6)
• Use the Explain diagnostic facility to obtain more detailed information about why the optimization guideline could not be applied. (reason code 13).
• If the materialization is caused by a spilling sort, try creating an index that would avoid the sort. To suppress the warning, increase the value specified by the registry variable DB2_OPT_MAX_TEMP_SIZE or unset it completely. (Reason code 15)
• Set server options CPU_RATIO, IO_RATIO, or COMM_RATE or function mapping options IOS_PER_INVOC, INSTS_PER_INVOC, IOS_PER_ARGBYTE, INSTS_PER_ARGBYTE, PERCENT_ARGBYTES, INITIAL_IOS, or INITIAL_INSTS to a value that is not too high or too low. (Reason codes 3, 4)
• Rewrite the query to parallelize the transform expressions. (Reason code 16)

sqlcode: +437

sqlstate: 01602

SQL0438N

This error or warning occurred as a result of execution of the RAISE_ERROR function or the SIGNAL SQLSTATE statement in a trigger. An SQLSTATE value that starts with '01' or '02' indicates a warning.

See application documentation.

sqlcode: -438, +438

sqlstate: application-defined

SQL0439N

The function function-name was referenced in the user's statement. However, because the SOURCE clause was used in the definition of this function, it has turned out that function source-function actually implements the function. (It may be a direct or an indirect definition path from function-name to source-function.) At compile time, the encapsulator (DB2 code which acts on behalf of a function) for source-function has returned the error identified by sqlcode.

The actual error situation needs to be understood better before corrective action can be taken. Look up the explanation for sqlcode. If source-function is a built-in function, the sqlcode should indicate the problem, as in the case where a built-in function is directly referenced in the user's statement. If source-function is a user defined function, the message most likely indicates a problem with one of the arguments or with the result from the function.

Correct the problem and try again.

sqlcode: -439

sqlstate: 428A0

SQL0440N

This occurs in a reference to routine routine-name, when the database manager cannot find a routine it can use to implement the reference. There are several reasons why this could occur:

• routine-name was either incorrectly specified or does not exist in the database.
• A qualified reference was made, and the qualifier was incorrectly specified.
• The user's SQL path does not contain the schema to which the desired function or method belongs, and an unqualified reference was used.
• The user's SQL path does not contain the module to which the desired routine belongs.
• The module-qualified routine reference is made from outside the module but the module routine is not published.
• The wrong number of arguments was included.
• The right number of arguments was included in the function or method reference, but the data types of one or more of the arguments is incorrect.
• Named arguments were used which do not match the parameter names of routine routine-name.
• There are one or more routines with the same name and number of parameters declared in the current compound statement.
• There are one or more routines with the same name declared in the outer compound statements within which the current compound statement declaring a routine is nested.
• The routine does not exist in the database with the same function id that was used when the package was bound (applies to static statements).
• The mutator method corresponding to an attribute assignment used in an UPDATE statement could not be found. The data type of the new value for the attribute is not a data type that is the same or promotable to the data type of the attribute.
• The routine invoker is not authorized to execute the routine.
• The server time was reset or changed.
• The database was restored on a server with a different time or time zone.
• The time associated with the members in multi-partitionned database environments is out of sync.

Fix the problem and retry. This could involve catalog access, a change to the statement, a grant of the execute privilege to the routine invoker, the addition of new functions, and/or a change to the SQL path.

sqlcode: -440

sqlstate: 42884

SQL0441N

Correct the error and try again.

sqlcode: -441

sqlstate: 42601

SQL0442N

Too many arguments were specified in the reference to routine routine-name. The maximum allowable is 90.

Correct the statement by ensuring that the correct number of arguments has been used and try again.

sqlcode: -442

sqlstate: 54023

SQL0443N

This message is returned when a built-in or user-defined routine (procedure, function, or method) fails.

The contents of the third run-time token, text, varies:

• When this message is returned because a user-defined routine failed, the contents of the third run-time token, text, is whatever was implemented by the creator of the routine.
• In general, when this message is returned because a built-in routine failed, the contents of the third run-time token, text, will contain an embedded error code:

  Example 1 - In this example, text refers to message DBA7904N.

  
  [IBM][CLI Driver][DB2/AIX64] SQL0443N Routine
  "SYSPROC.ALTOBJ" (specific name "ALTOBJ") has
  returned an error SQLSTATE with diagnostic text
  "DBA7904, DBAD".  SQLSTATE=38553
  

  Example 2 - In this example, text refers to sqlcode -805.

  
  [IBM][CLI Driver][DB2/AIX64] SQL0443N Routine
  "SYSIBM.SQLTABLES" (specific name "TABLES") has
  returned an error SQLSTATE with diagnostic text
  "SYSIBM:CLI:-805".  SQLSTATE=38553
  

• When this message is returned because a built-in function in the SYSFUN schema failed, the third run-time token, text, is of the following format:

  
  SYSFUN:nn
  

  where nn is a reason code having the following meaning:

  01

  Numeric value out of range

  02

  Division by zero

  03

  Arithmetic overflow or underflow

  04

  Invalid date format

  05

  Invalid time format

  06

  Invalid timestamp format

  07

  Invalid character representation of a timestamp duration

  08

  Invalid interval type

  09

  String too long

  10

  Length or position in string function out of range

  11

  Invalid character representation of a floating point number

  12

  Out of memory

  13

  Unexpected error

Respond to this error in one of the following ways:

• If this message was returned because a user-defined procedure, function, or routine failed, review any diagnostic information in the third run-time token, text, and work with the author of the procedure, function, or routine to troubleshoot the cause of the problem.
• If this message was returned because a DB2 built-in procedure, function, or routine failed, perform the following troubleshooting steps:
  1. Determine which error code is embedded in the third run-time token, text.
  2. Refer to the details for the given sqlcode.
  3. Review additional diagnostic information, such as the values of any run-time tokens for the embedded error code, in the db2diag log files.

SQL0444N

The database manager is trying to access the body of code that implements routine routine-name (specific name specific-name), and cannot access it for the reason given by reason code code (the codes are listed below). The file implementing the routine is identified by library-or-path, and the function by function-code-id.

The library name, path name, or the function name might be truncated due to a limit on the maximum available token length. The following query can be used to get the complete library path and function names from the system catalog views:

     SELECT implementation
     FROM syscat.routines
     WHERE specificname = 'specific-name'

1

Path name library-or-path is longer than the maximum (255 bytes).

2

The database manager instance path name could not be retrieved from DB2.

3

The path library-or-path could not be found.

4

The file in library-or-path could not be found, because of one of the following reasons:

1. The routine library associated with the routine is not available in the location specified by the EXTERNAL clause in the routine CREATE statement definition.
2. The routine library is dependent on a shared library that cannot be located (within the concatenation of directories specified in the LIBPATH environment variable in UNIX-based systems, or the PATH environment variable in Windows systems). There can be multiple levels of this indirection involved, leading to this reason code. For example, routine body X can be found, and it needs shared library Y which can also be found. But Y needs another shared library Z that cannot be located.
3. This is a partitioned database and the library is not located in one of the partitions on which the user-defined function was run.

5

There is insufficient memory to load the library containing the function or one or more symbols could not be resolved. This reason code indicates one of the following situations:

1. One or more symbols might not have been resolved. The routine library might be dependent on a shared library that cannot be located (using the concatenation of directories specified in the LIBPATH environment variable in UNIX-based systems, the PATH environment variable in INTEL systems).
2. The routine has a 64-bit library which is not supported within a 32-bit instance, or, the routine has a 32-bit library or DLL within a 64-bit instance that is incompatible with the routine definition.
3. There was insufficient memory to load the library containing the function.

6

The function function-code-id could not be found in the library that was named.

7

The symbol given as the function name (function-code-id) is not the name of a valid function in the named library.

8

The "load" system function has failed, for reasons other than those given in this message. The object file might not have been linked properly or not have been linked at all.

9

There was insufficient memory to resolve the function name function-code-id in the library identified in library-or-path.

10

The "loadquery" system call has failed. This can only happen on UNIX operating systems, and is a symptom that the database manager has not been installed correctly.

11

The agent process is searching for a particular database manager function that should be in the libdb2.a library, and it cannot find it. This can only happen on UNIX operating systems, and is a symptom that the database manager is not correctly installed.

15

System or network access has been denied. This can happen in Windows environments if the EXTERNAL NAME specification in the routine definition statement does not specify a full path, or the function does not exist in the directory called db2_installation_path\function, necessitating a search using the PATH environment variable. For example, if the PATH contains a LAN drive prior to the directory containing your function and the instance is running under the SYSTEM account, this reason code can result.

16

Multiple Java methods that could match the function function-code-id were found.

Other:

An unidentified system failure has occurred.

Federated system users: if this user defined function is a function template (and thus no code is required to reside on the federated server), consider modifying the SQL statement or statistics to enable this function to be evaluated on the remote data source.

Given for each reason code:

1

Either the routine definition needs to be changed to specify a shorter path, or the database manager instance path name is too long. Analyze the routine definition within the DB2 catalog views to determine which action to take. It may be necessary to move the function body to a directory with a shorter path name.

2, 10, and 11

See your system administrator, and provide them with the full text of the appropriate reason code.

3, 6, and 7

See the routine creator or your database administrator. Provide them with the full text of the appropriate reason code. The routine definition or the location of the routine library will need to be corrected.

4

Possible solutions are grouped by the causes provided in the message explanation:

1. Make the routine library associated with the routine available:
   • Correct the routine definition EXTERNAL clause value or verify that the routine library is in the location specified by the EXTERNAL clause.
   • For built-in routines, if you have recently updated your database to a different fix pack level that requires updating the system catalog, issue the update database to current release command to update the system catalog including the built-in routines and restart the instance.
   • Verify that the routine library can be accessed by the database manager. For fenced routines, verify that the fenced process owner has EXECUTE access permission on the library or DLL. For unfenced routines, verify that the instance owner has read access permissions on the library or DLL.
2. The routine should be relinked or user should verify that dependent libraries are available in the LIBPATH (UNIX) or PATH (INTEL). Use the db2set command to update the LIBPATH to include the path where the library or DLL is located. For example, db2set db2libpath=/db2test/db2inst1/sqll
3. In a partitioned database, the library associated with a user-defined function must be accessible as library-or-path on all partitions of the database. Verify that the routine library has been deployed to the function directory on all partitions of the database.

5

Possible solutions are ordered to match the causes provided in the message explanation:

1. There can be multiple levels of this indirection involved, leading to this reason code. For example, routine body X can be found, and it requires shared library Y which is also found, however Y needs another shared library Z that cannot be located. The routine library should be relinked or the user should verify that all dependent libraries are available in the LIBPATH (UNIX) or PATH (INTEL). Use the db2set command to update the LIBPATH to include the path where the library is located. For example: db2set db2libpath=/db2test/db2inst1/sqll
2. Use a supported library.
3. If a lack of memory is determined then the system configuration may need to be changed to make more memory available to DB2.

8

Ensure that the module is correctly linked.

9

Contact the routine creator or your database administrator to make sure the library containing the function was correctly linked. The system configuration may need to be changed to make more memory available to database server.

15

Verify that the EXTERNAL NAME clause in the routine definition statement contains the full path, or the function exists in the directory called db2_installation_path\function.

If any of these situations is true and the error occurs due to an instance running under an account which cannot access a LAN drive prior to the directory containing your function in the PATH environment variable, you need to reconfigure the DB2 service to run under a user account that has access to all the network paths that are in the PATH environment variable.

When assigning a user account to the DB2 service, make sure that the account is granted all the required advanced user rights.

16

Verify that the library-or-path Class referenced in the EXTERNAL NAME clause in the routine definition statement contains a function function-code-id which can be resolved without ambiguity based on the SQL data-types in the routine definition statement.

If the routine was defined with PARAMETER STYLE HIVE in the routine definition statement, check the routine implementation for multiple function-code-id methods. For example, if there are multiple function-code-id methods, make sure that no two methods contain the same parameter list when those methods can use either the java.lang.String class or the org.apache.hadoop.io.Text substituted for each other.

Other:

Note the code, and see your system administrator.

Federated system users: if this user defined function is a function template (and thus no code is required to reside on the federated server), you may want to consider modifying the SQL statement or statistics to enable this function to be evaluated on the remote data source.

sqlcode: -444

sqlstate: 42724

SQL0445W

The value value was truncated when transformed in some way by a system (built-in) cast, adjustment, or codepage-conversion function. This is a warning situation.

The value being transformed may be one of:

• The output of a routine (user-defined function (UDF) or method), and is being transformed because of a CAST FROM specification in the routine definition.
• The output of a routine (user-defined function (UDF) or method) that is sourced on another function and the result needed to be transformed.
• The output of a built-in string processing routine that needed to perform codepage conversion during processing, for example, due to a string length unit specification.

Ensure that the output is as expected and that the truncation has not caused any unexpected consequences.

sqlcode: +445

sqlstate: 01004

SQL0447W

The clause keyword was included more than once in the statement. This is a warning condition.

If the redundancy was intentional, or if it is determined that no harm has resulted, then no response is necessary. The type of "harm" alluded to could include, for example, the omission of some other desired keyword.

sqlcode: +447

sqlstate: 01589

SQL0448N

Too many parameters were specified when defining routine routine-name. The routine definition statement may be a CREATE FUNCTION, CREATE PROCEDURE, CREATE TYPE (method definition), or ALTER TYPE (method definition).

Change the statement to include fewer parameters.

sqlcode: -448

sqlstate: 54023

SQL0449N

An error was found in the EXTERNAL NAME clause of the CREATE statement for user defined function (UDF), user-defined method, or procedure routine-name. The rules for the library/function identification are as follows:

For all languages, no leading or trailing blank characters are permitted between the single quotes, object identifiers, or the separating characters (for example, ' <a> ! <b> ' is invalid). Path and file names, however, may contain blanks if the platform permits.

Correct the problem and try again. A possible cause is the inclusion of a blank, or having the '!' or ':' at the beginning or end of the name.

sqlcode: -449

sqlstate: 42878

SQL0450N

This is not permitted.

This error will also be returned if the length field of the scratchpad is altered by the routine.

See your Database Administrator, or the author of the routine.

sqlcode: -450

sqlstate: 39501

SQL0451N

An error was made in the data-item part of the statement defining routine routine-name. The user's statement contained the invalid type type, or it contained a user defined type (UDT) which is based on the invalid type type. The routine definition may be a CREATE FUNCTION, CREATE PROCEDURE, CREATE TYPE (method definition), or ALTER TYPE (method definition).

data-item is a token that identifies the area of the problem in the statement. For example, "PARAMETER 2" or "RETURNS" or "CAST FROM".

In an autonomous transaction, a block of statements within the transaction can be committed even if that transaction is rolled back. You can use the AUTONOMOUS keyword with the CREATE PROCEDURE command to create a procedure that will commit its work even if it is called from within a unit of work which is rolled back.

Determine which situation has occurred, and take corrective action. Possible corrective actions include:

• Changing the routine definition to a supported type; for example, from DECIMAL to FLOAT. This may involve a change in the routine body itself, and may also involve use of cast functions upon use of the routine.
• Creating a new (suitably based) user defined type, or changing the definition of an existing UDT.

sqlcode: -451

sqlstate: 42815

SQL0452N

For reason code 01, correct the file name length, file name and/or path.

For reason code 02, specify a valid file option.

For reason code 03, ensure that the file specified exists before attempting to access the file.

For reason code 04, either delete the file if it is no longer required or specify a file name that does not currently exist.

For reason code 05, ensure that the user has access (correct file permissions) to the file.

For reason code 06, either use a different file or, if the file must be accessed, modify the application to ensure that the file is not accessed concurrently.

For reason code 07, delete unnecessary files to free up disk space or specify a file that resides on another drive/file system with sufficient disk space. Also, ensure that no operating system or user file size limits were reached. If your application code page uses a multibyte encoding scheme it is possible that only part of the last character was written, ensure that the file contains only fully formed characters.

For reason code 08, if a file is to be used for input, ensure that the file is not modified before the entire file has been read.

For reason code 09, correct all errors on the media where the file resides.

For reason code 10, ensure that the file contains valid multibyte characters based on the code page of the application or submit the request while running under the same code page as the contents of the file.

For reason code 11, ensure that character conversion support between the code page of the file, for example Japanese EUC, and the graphic code page of the application, for example UCS-2, is installed.

sqlcode: -452

sqlstate: 428A1

SQL0453N

A problem casting the result of routine routine-name has been identified. The CAST FROM data type is not castable to the RETURNS data type, and it should be. See the SQL Reference for details on casting between data types.

Change the RETURNS or CAST FROM clause so that the CAST FROM data type is castable to the RETURNS data type.

sqlcode: -453

sqlstate: 42880

SQL0454N

The signature of a function consists of the function name, the number of parameters defined for the function, and an ordered list of the types of the parameters.

The signature of a method consists of the method name, the subject type of the method, the number of parameters defined for the method, and an ordered list of the types of the parameters.

The signature of a procedure consists of the procedure name and the number of parameters defined for the procedure (data types are not considered).

This can occur when:

• There already is a function or procedure (routine-name) in the schema, module, or compound SQL (compiled) statement with the same signature as the function or procedure being created. If you are altering a module to add a routine body to a defined routine prototype, the routine already has a routine body definition.
• There already is a procedure (routine-name) in an outer compound SQL (compiled) statement with the same name as the procedure being declared.
• There already is a method (routine-name) for the subject type with the same signature as the method specification being added or the method body being created

The statement cannot be processed.

Determine if the existing routine already provides the functionality desired. If not, then the signature of the routine will have to be changed; for example, by changing the routine name.

If altering a module to add a routine body to a defined routine prototype, check that the signature matches or if the signature also needs to change and ensure that the routine name and specific name match those of the defined routine prototype. If the routine body of an existing module routine needs to be changed, alter the module to drop the routine and then alter the module to recreate the routine with the new routine body.

sqlcode: -454

sqlstate: 42723

SQL0455N

If the SPECIFIC name is specified as a two part name, the schema-name1 portion must be the same as the schema-name2 portion of the routine-name. Note that the schema-name2 portion of routine-name may have been specified directly or it may have defaulted to the authorization ID of the statement. If the routine is a method, schema-name refers to the schema name of the subject type of the method.

Correct the statement and try again.

sqlcode: -455

sqlstate: 42882

SQL0456N

The user has supplied an explicit SPECIFIC name specific-name in the definition of routine routine-name, but this name already exists as the SPECIFIC name for a function, method, or procedure in the schema or module.

Choose a new SPECIFIC name and try again.

sqlcode: -456

sqlstate: 42710

SQL0457N

Select a name for the function, method, user-defined data type, or structured data type attribute that is not reserved for system use.

sqlcode: -457

sqlstate: 42939

SQL0458N

In a reference to function, method, or stored procedure routine-name by signature, no matching function, method, or stored procedure could be found.

If a data type is used that can accept a parameter, then the type parameter is optional. For example, for CHAR(12), you can either specify the parameter (CHAR(12)) or omit it (CHAR()). If you specify the parameter, then the DBMS will only accept an exact match on the data type and the data type parameter. If you omit the parameter, then the DBMS will accept a match on data type only. The CHAR() syntax provides a way to tell the DBMS to ignore data type parameters when finding a matching function.

Note also that in the DROP FUNCTION/PROCEDURE, COMMENT ON FUNCTION/PROCEDURE and TRANSFER OWNERSHIP FUNCTION/PROCEDURE/METHOD statements, an unqualified reference is qualified with the statement authorization ID, and this is the schema where the problem can be found. In the SOURCE clause of a CREATE function, the qualification comes from the current path. In this case, there is no matching function in the entire path.

A function cannot be sourced on the COALESCE, DBPARTITIONNUM, GREATEST, HASHEDVALUE, LEAST, MAX (scalar), MIN (scalar), NULLIF, NVL, RID, RAISE_ERROR, TYPE_ID, TYPE_NAME, TYPE_SCHEMA, or VALUE built-in functions.

The statement cannot be processed.

Possible responses include:

• Changing the path to include the correct schema.
• Removing parameters from the specifications of data types.
• Using a SPECIFIC name to refer to the function or procedure instead of a signature.

sqlcode: -458

sqlstate: 42883

SQL0461N

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

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

sqlcode: -461

sqlstate: 42846

SQL0462W

An SQLSTATE of the form 01Hxx was returned to DB2 by the command or routine command-or-routine-name (with specific name specific-name), along with message text text. If command-or-routine-name is a command, then specific-name will contain the value ''*N''.

The user will need to understand the meaning of the warning. See your database administrator, or the author of the routine.

sqlcode: +462

sqlstate: Valid warning SQLSTATEs returned by a user-defined function, external procedure CALL, or command invocation.

SQL0463N

The valid SQLSTATEs that a routine can return are 38xxx (error), 38502 (error) and 01Hxx (warning). This routine routine-name (specific name specific-name) returned an invalid SQLSTATE state, along with message text text. The routine is in error.

The routine will need to be corrected. See your database administrator, or the author of the routine. The application significance of the bad SQLSTATE can also be learned from the routine author.

sqlcode: -463

sqlstate: 39001

SQL0464W

The stored procedure named by procedure-name completed normally. However, the stored procedure exceeded the defined limit on the number of query result sets the procedure can return.

generated-nbr-results

identifies the number of query result sets returned by the stored procedure.

max-nbr-results

identifies the defined limit on the number of query result sets for the stored procedure.

Only the first max-nbr-results query result sets are returned to the SQL program that issued the SQL CALL statement.

The SQL statement is successful. The SQLWARN9 field is set to 'Z'.

sqlcode: +464

sqlstate: 0100E

SQL0465N

There is a system related problem involved with the running of a fenced mode routine (user-defined function or method). The exact nature of the problem is indicated by code. This is NOT a user problem. Possible reason codes are:

Contact your Database or System administrator.

sqlcode: -465

sqlstate: 58032

SQL0466W

This message is returned as a result of issuing a CALL SQL statement. It indicates that the stored procedure procedure-name has number-results result sets associated with it.

The statement completed successfully.

None required.

sqlcode: +466

sqlstate: 0100C

SQL0467W

This message is returned as a result of closing a cursor. It indicates that another result set exists for stored procedure procedure-name and the cursor was re-opened on the next result set. There are a total of max-nbr-results result sets from the stored procedure.

The statement completed successfully.

None required. Fetches can proceed on to the next result set.

sqlcode: +467

sqlstate: 0100D

SQL0469N

A parameter in an SQL procedure is declared as OUT and the corresponding argument is not a valid assignment target. For functions only, a parameter is declared as OUT or INOUT and the corresponding argument is not an SQL variable, global variable, or SQL parameter.

A constant or an expression is an example of an invalid argument for OUT or INOUT parameters.

For example, if a function named my_function was declared to have one OUT parameter, the following function call is not valid because the argument that is passed to the OUT parameter cannot have values assigned to it:

SET my_variable = my_function( 1 + 2 );

If calling an SQL procedure, change the output argument to a valid assignment target. If invoking a function, change the OUT or INOUT argument to an SQL variable, global variable, or SQL parameter.

sqlcode: -469

sqlstate: 42886

SQL0470N

A routine has an input argument with a null value but was defined with a parameter style that does not pass null indicators, or the data type of this parameter does not support null values.

If the routine is to be called with null values, ensure the parameter style and input types are capable of accepting a null value. For functions, the function could also be created with "RETURNS NULL ON NULL INPUT".

sqlcode: -470

sqlstate: 39004

SQL0471N

The invocation of routine name failed. Use reason code reason-code to find out more details about the cause of the failure.

sqlcode: -471

sqlstate: 55023

SQL0472N

The function or method routine-name (specific name specific-name) did not close all its open cursors before the invoking statement completed. Functions and methods must close all cursors before the invoking statement completes.

Consult with the developer of the function or method. The function or method must be rewritten to ensure that all cursors are closed before the invoking statement completes.

sqlcode: -472

sqlstate: 24517

SQL0473N

This message is returned when an attempt is made to either create a user-defined data type or to upgrade a database having a user-defined data type with a name that is the same name of a built-in data type.

To create a new user-defined data type, re-issue the statement using a non-reserved data type name.

To upgrade a database which has a user-defined data type with the same name as a built-in data type in the new product version, perform the following steps:

1. Drop the user-defined data type which has the name conflict and drop database objects that reference this data type.
2. Re-create the data type using a non-reserved data type name and then re-create the other dropped objects.
3. Upgrade the database.

sqlcode: -473

sqlstate: 42918

SQL0475N

In order for the CREATE of a sourced user defined function (UDF) to be valid, the result type (type-1) of the source function must be castable to the RETURNS type (type-2) of the function being created. There is no supported cast between these data types. See the SQL Reference for details on casting between data types.

Change the RETURNS data type or the SOURCE function identification so that the result type of the SOURCE function is castable to the RETURNS data type.

sqlcode: -475

sqlstate: 42866

SQL0476N

References to a function or stored procedure without a signature is permitted, but the named function or store procedure function-name must be unique in its schema and is not. If the routine is a method, a reference without a signature is permitted but this named method must be unique for the data type.

Note that in the DROP FUNCTION/PROCEDURE and COMMENT ON FUNCTION/PROCEDURE statements, an unqualified reference is qualified with the statement authorization ID, and this is the schema where the problem can be found. In the SOURCE clause of a CREATE FUNCTION, the qualification comes from the current path. In this case, the first schema in the path containing a function with this name had other functions by the same name.

Federated system users: For a pass-through session, if the statement is a CREATE FUNCTION MAPPING statement, this error indicates that an attempt was made to create a function mapping from one remote function to more than one local function.

and try again.

sqlcode: -476

sqlstate: 42725

SQL0478N

When object B is said to be dependent on object A, actions taken on object A could significantly affect object B. For example, when you drop a table any objects that are directly or indirectly dependent on that table could be either deleted or made inoperative. Because of these possible outcomes for dependent objects, some types of actions fail if there are dependencies on the object that is target of the action.

This message is returned when an attempt to drop, alter, transfer ownership of, or revoke privileges on a target object fails because other objects directly or indirectly depend on that target object.

Examples of scenarios in which this message can be returned:

• If object-type1 is ALIAS, the dependencies for DROP might include a row permission or a column mask which references this alias.
• If object-type1 is SYNONYM, the dependencies for DROP might include a row permission or a column mask which references this synonym.
• If object-type1 is VIEW, the dependencies for DROP might include a row permission or a column mask which references this view.
• If object-type1 is TABLE:
  • Dependencies for DROP might include a row permission or a column mask which references this table.
  • A system-period temporal table might exist that uses this table as the corresponding history table.
  • If object-type2 is INDEX, there might be an expression-based index that depends on the columns of the table.
• If object-type1 is TABLESPACE, a system-period temporal table might exist for which the corresponding history table resides in this table space.
• If object-type1 is TENANT:
  • An object called object-name of the type object-type2 exists within the tenant catalogs.
• For a DB2 for z/OS server, if object-type1 is DATABASE, a system-period temporal table might exist for which the corresponding history table resides in this database.

1. Determine which objects have direct or indirect dependencies on the target object by using one or both of the following methods:
   • Consult the system catalogs.
   • Use the GET_DEPENDENCY procedure.
2. Remove the dependencies that blocked the statement.
3. Reissue the statement.

sqlcode: -478

sqlstate: 42893

SQL0480N

Either the procedure identified in an ASSOCIATE LOCATORS statement has not yet been called within the application process, or the procedure has been called, but an explicit or implicit commit occurred before the statement.

Correct the statements so that the exact syntax used to specify the procedure name on the CALL statement is the same as that on the ASSOCIATE LOCATORS statement. If an unqualified name is used to CALL the procedure, the one-part name must also be used in the other statements. Reissue the statements.

sqlcode: -0480

sqlstate: 51030

SQL0481N

where GEL represents the element shown as grouping-expression-list in the syntax diagram of the GROUP BY clause.

In some instances the value “---” will be shown for element 2. In this case “---” represents one of CUBE, ROLLUP, GROUPING SETS, or GEL.

The statement cannot be processed.

Modify the GROUP BY clause to remove the nesting.

sqlcode: -481

sqlstate: 428B0

SQL0483N

It is also possible that the path needs to be corrected in order for correct function resolution to occur.

sqlcode: -483

sqlstate: 42885

SQL0486N

One or more of the data types in the statement is BINARY or VARBINARY. This is not supported in the current version of DB2.

Change the data type(s) then resubmit the statement.

sqlcode: -486

sqlstate: 42991

SQL0487N

The program used to implement the body of a routine is not allowed to execute SQL statements. This routine routine-name (specific name specific-name) contains SQL statements or this routine calls a procedure that uses SQL statements, such as the SYSPROC.ADMIN_CMD procedure.

Remove any SQL statements then recompile the program. Investigate the level of SQL allowed as specified on the statement defining the routine.

sqlcode: -487

sqlstate: 38001

SQL0489N

The function function-name is defined for use as a predicate, returning a boolean result. Such a result is not valid in a select list.

The statement cannot be processed.

Correct the function name or remove the use of the function.

sqlcode: -489

sqlstate: 42844

SQL0490N

A number (number) was specified that is not valid in the context in which it was specified. The minimum allowed value in this context is minval. The maximum allowed value in this context is maxval. n must be within the range specified by minval and maxval (minval =< n => maxval).

If creating or altering a table space, the minimum and maximum values may be dependant on the page size of the table space. Refer to the SQL Reference for more details on table space limits.

Change the value n to a valid value in the statement or command.

sqlcode: -490

sqlstate: 428B7

SQL0491N

A required clause is missing in the definition of routine routine-name. If EXTERNAL was specified, one of the following clauses must also be specified: LANGUAGE, PARAMETER STYLE.

If defining an SQL function, the SQL function body must be included unless using the PUBLISH action of ALTER MODULE to define an SQL function prototype.

Add the missing clause, and then try again.

sqlcode: -491

sqlstate: 42601

SQL0492N

The parameter in position number of function function-name is in error and the CREATE cannot be performed. The parameter in position number of the source function is not castable to the corresponding parameter of the function being created.

sqlcode: -492

sqlstate: 42879

SQL0493N

The body of user defined function (UDF) or method routine-name (specific name specific-name) has returned an invalid date, time or timestamp value.

An example of a syntactically invalid date value is '1994-12*25': the '*' should be a '-'. An example of a numerically invalid time value is '11.71.22': there is no 71st minute in the hour.

The routine needs fixing. See your DBA, or the author of the routine.

sqlcode: -493

sqlstate: 22007

SQL0494W

The number of result set locators specified on the ASSOCIATE LOCATORS statement is less than the number of result sets returned by the stored procedure. The first “n” result set locator values are returned, where “n” is the number of result set locator variables specified on the SQL statement.

The SQL statement is successful. The SQLWARN3 field is set to 'Z'.

Increase the number of result set locator variables specified on the SQL statement.

sqlcode: +494

sqlstate: 01614

SQL0495N

The prepare of a dynamic INSERT, UPDATE, DELETE, or SELECT SQL statement resulted in a cost estimate that exceeded the error threshold value specified in the resource limit specification table (RLST).

The prepare of the dynamic INSERT, UPDATE, DELETE, or SELECT statement was unsuccessful.

If this SQLCODE was returned because the cost category value is "B", it might be that the statement is using parameter markers or that some statistics are not available for the referenced tables and columns. Make sure the administrator has run the RUNSTATS command (for Db2 tables), the NNSTAT procedure (for nicknames), or ANALYZE command (for Hadoop and HBase tables) on the referenced tables. It might also be that UDFs will be invoked when the statement is executed, or for INSERT, UPDATE, or DELETE statements, that triggers are defined on the changed table. Check the DSN_STATEMNT_TABLE or the IFCID 22 record for this statement to find the reasons this SQL statement has been put in cost category "B". If the program cannot be changed, or if statistics cannot be obtained, ask the administrator to change the value in the RLF_CATEGORY_B column in the RLST to "Y", which allows the statement to execute, or "W", which returns a warning instead of an error.

If the warning is caused by an SQL statement that is consuming too much processor resource, attempt to rewrite the statement to perform more efficiently. Another option is to ask the administrator to increase the error threshold value in the RLST.

sqlcode: -495

sqlstate: 57051

SQL0499N

An attempt was made to assign a cursor to a result set but multiple cursors have been allocated for procedure procedure-name.

Determine if the target result set was previously assigned to a cursor. If multiple cursors have been allocated for procedure procedure-name ensure that only one cursor is used to process the result sets of a stored procedure.

sqlcode: -499

sqlstate: 24516

SQL0501N

The program attempted to do one of:

• FETCH using a cursor at a time when the specified cursor was not open.
• CLOSE a cursor at a time when the specified cursor was not open.
• Reference a cursor variable in an OPEN statement and the cursor variable is not open.
• Reference a cursor scalar function, such as CURSOR_ROWCOUNT function, and the cursor variable is not open.

The statement cannot be processed.

Check for a previous message (SQLCODE) that may have closed the cursor. Note that after the cursor is closed, any fetches or close cursor statements receive SQLCODE -501.

If no previous SQLCODEs have been issued, correct the application program to ensure that the cursor is open when the FETCH or CLOSE statement is executed.

If a cursor variable is referenced in a cursor scalar function, verify that that the cursor is not null, is defined, and is open, else replace the cursor variable with one that is in that state.

sqlcode: -501

sqlstate: 24501

SQL0502N

The program attempted to execute an OPEN statement for an open cursor.

The statement cannot be processed. The cursor was unchanged.

Correct the application program to ensure it does not attempt to execute an OPEN statement for a cursor already open.

sqlcode: -502

sqlstate: 24502

SQL0503N

Using a cursor, the program attempted to update a value in a table column that was not identified in the FOR UPDATE clause in the cursor declaration or the prepared SELECT statement.

Any column to be updated must be identified in the FOR UPDATE clause of the cursor declaration.

The statement cannot be processed.

Correct the application program. If the column requires updating, add its name to the FOR UPDATE clause of the cursor declaration.

sqlcode: -503

sqlstate: 42912

SQL0504N

An UPDATE or DELETE WHERE CURRENT OF name was specified, but the cursor name was not declared in the application program.

The statement cannot be processed.

Ensure the completeness of the application program and correct spelling errors in the cursor names.

sqlcode: -504

sqlstate: 34000

SQL0505N

The cursor name specified in the DECLARE statement has already been declared.

The statement cannot be processed.

Ensure the name is spelled correctly.

SQL0507N

The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement when the specified cursor was not open.

The statement cannot be processed. No update or delete was performed.

Check for a previous message (SQLCODE) that may have closed the cursor. Note that after the cursor is closed, any fetches or close cursor statements receive SQLCODE -501 and any updates or deletes receive SQLCODE -507. Correct the logic of the application program to ensure that the specified cursor is open at the time the UPDATE or DELETE statement is executed.

sqlcode: -507

sqlstate: 24501

SQL0508N

The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement when the specified cursor was not positioned on an object table row. The cursor must be positioned on the row to be updated or deleted.

The cursor is no longer positioned on a row if the row is deleted. This includes any use of cursors within a savepoint when a ROLLBACK TO SAVEPOINT is performed.

Federated system users: the record in a remote data source has been updated and/or deleted by another application (or a different cursor within this application) and the record no longer exists.

The statement cannot be processed. No data is updated or deleted.

Correct the logic of the application program to ensure that the cursor is correctly positioned on the intended row of the object table before the UPDATE or DELETE statement is executed. Note that the cursor is not positioned on a row if FETCH returned message SQL0100W (SQLCODE = 100).

sqlcode: -508

sqlstate: 24504

SQL0509N

The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement where the table named did not match the name of the table specified in the SELECT statement that declared the cursor.

The statement cannot be processed.

Correct the application program to ensure that the table identified in the UPDATE or DELETE statement is the same table identified in the cursor declaration.

sqlcode: -509

sqlstate: 42827

SQL0510N

The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement against a table or view definition that does not permit the requested update or delete operation. For example, this error can occur in a delete from a read-only view or in an update where the cursor was not defined with the FOR UPDATE clause.

On the database manager the view is read-only if the SELECT statement includes :

• The DISTINCT keyword
• A column function in the SELECT list
• A GROUP BY or HAVING clause
• A FROM clause that identifies one of the following:
  • More than one table or view
  • A read-only view (READONLY column of SYSCAT.SYSVIEWS is set to 'Y')
• A set operator (other than UNION ALL).

Note that these conditions do not apply to subqueries of the SELECT statement.

The cursor is declared with a FOR FETCH ONLY clause.

The cursor is ambiguous and the BLOCKING ALL bind option was specified.

The cursor references a view that has an INSTEAD OF UPDATE (or DELETE) trigger.

The cursor directly or indirectly references a view that is defined with the WITH ROW MOVEMENT clause and an UPDATE WHERE CURRENT OF CURSOR was attempted.

The statement cannot be processed.

If the database manager is failing the statement and the cursor is based on a read-only SELECT or VALUES statement, do not issue any update or delete statements against it.

If the database manager is failing the statement and the cursor is not based on a read-only SELECT or VALUES statement and is defined with a FOR FETCH ONLY clause, either remove this clause from the cursor definition or do not issue any update or delete statements.

If the database manager is failing the statement and the cursor cannot be determined to be either fetch only or updatable from its definition or context, rebind the program with either the BLOCKING NO or BLOCKING UNAMBIG bind option.

If the database manager is failing the statement and the cursor is based on a view with an INSTEAD OF UPDATE (or DELETE) trigger, use a searched UPDATE (or DELETE) statement.

If the database manager is failing the statement and the cursor directly or indirectly references a view that is defined with the WITH ROW MOVEMENT clause, do not issue any update statements against it.

Federated system users: isolate the problem to the data source failing the request. If a data source is failing the request, examine the restrictions for that data source to determine the cause of the problem and its solution. If the restriction exists on a data source, see the SQL reference manual for that data source to determine why the object is not updatable.

sqlcode: -510

sqlstate: 42828

SQL0511N

The result table of the SELECT or VALUES statement cannot be updated.

On the database manager, the result table is read-only if the cursor is based on a VALUES statement or the SELECT statement contains any of the following:

• The DISTINCT keyword
• A column function in the SELECT list
• A GROUP BY or HAVING clause
• A FROM clause that identifies one of the following:
  • More than one table or view
  • A read-only view
  • An OUTER clause with a typed table or typed view
  • A data change statement
• A set operator (other than UNION ALL).

Note that these conditions do not apply to subqueries of the SELECT statement.

The statement cannot be processed.

Do not perform updates on the result table as specified.

Federated system users: isolate the problem to the data source failing the request.

If a data source is failing the request, examine the restrictions for that data source to determine the cause of the problem and its solution.

If the restriction exists on a data source, see the SQL reference manual for that data source to determine why the object is not updatable.

sqlcode: -511

sqlstate: 42829

SQL0512N

You can reference remote objects without having to use nicknames by referencing the remote objects directly with federated three-part names.

This message is returned when a statement uses a three-part name in a way that is not supported. The reason code indicates the restriction that was encountered:

1

An attempt was made to use three-part names with either the import utility or the export utility.

2

An attempt was made to issue an SQL statement that includes a three-part name with the CREATE ALIAS clause against a partitioned database.

3

An attempt was made to use three-part names in a type of SQL statement that does not support three-part names.

4

An attempt was made to use three-part names against a data source with which three-part names are not supported.

Review federated three-part name restrictions, and then modify the statement or command to use three-part name only in ways that are supported.

sqlcode: -512

sqlstate: 56023

SQL0513W

The UPDATE or DELETE statement does not contain a WHERE clause so all rows of the table or view are modified if this statement is executed.

The statement is accepted.

Federated system users: not all data sources report this warning condition. The federated server attempts to issue this warning whenever the condition exists, but there is no guarantee that the federated server can always detect this condition. Do not rely on this warning to preclude UPDATE/DELETE operations from affecting an entire table or view.

Ensure that you intend to modify the entire table or view.

sqlcode: +513

sqlstate: 01504

SQL0514N

The application program tried to use a cursor that is not in a prepared state. The cursor is associated with a statement that meets the following conditions:

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

The statement cannot be processed.

1. Prepare the statement named in the DECLARE CURSOR statement before you try to open the cursor.
2. The prepare for the cursor must be reissued.
3. The statement should be prepared again after COMMIT or ROLLBACK. Alternatively, either bind the package with KEEPDYNAMIC YES or use the ALTER PACKAGE statement to change the KEEPDYNAMIC property to YES.

sqlcode: -514

sqlstate: 26501

SQL0516N

The statement name in the DESCRIBE statement must specify a statement that was prepared in the same database transaction.

The statement cannot be processed.

Verify that the statement name specifies a statement that has been prepared.

sqlcode: -516

sqlstate: 26501

SQL0517N

The cursor name could not be used as specified because the prepared statement named in the cursor declaration was not a SELECT or VALUES statement.

The statement cannot be processed.

Verify that the statement name is specified correctly in the PREPARE and the DECLARE CURSOR for cursor name statements. Or correct the program to ensure that only prepared SELECT or VALUES statements are used in association with cursor declarations.

sqlcode: -517

sqlstate: 07005

SQL0518N

The application program tried to EXECUTE a statement that

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

The statement cannot be processed.

1. Prepare the statement before attempting the EXECUTE.
2. Ensure that the statement is not a SELECT or VALUES statement.
3. The prepare for the cursor must be reissued.
4. The statement should be prepared again after COMMIT or ROLLBACK. Alternatively, either bind the package with KEEPDYNAMIC YES or use the ALTER PACKAGE statement to change the KEEPDYNAMIC property to YES.

sqlcode: -518

sqlstate: 07003

SQL0519N

The application program attempted to prepare the SELECT or VALUES statement for the specified cursor when that cursor is already open.

The statement cannot be prepared. The cursor was not affected.

Correct the application program so it does not attempt to prepare the SELECT or VALUES statement for a cursor that is open.

sqlcode: -519

sqlstate: 24506

SQL0525N

Note that contoken is given in hexadecimal.

The statement cannot be executed.

If the SQL statement is not supposed to execute at the indicated location, then correct the program so that the statement in error does not execute at that location. Precompile, compile, and bind replace the package. If the SQL statement is supposed to execute at the indicated location, correct the problem found and reissue PREP or BIND using ACTION(REPLACE). If multiple versions of the package have been bound, issue the following SELECT statement to determine which version has the error: SELECT PKGVERSION FROM SYSCAT.PACKAGES where PKGSCHEMA='pkgschema' AND PKGNAME = 'pkgname' and HEX(UNIQUE_ID) = 'contoken'

sqlcode: -525

sqlstate: 51015

SQL0526N

You can temporarily store the results from the manipulation of data in temporary tables.

There are some restrictions on how temporary tables can be used. For example, temporary tables do not support user-defined type columns, and index compression cannot be enabled for temporary tables. This message is returned when an attempt is made to execute an SQL statement that uses temporary tables in way that is not supported.

Respond to this message in one of the following ways:

• Modify the SQL statement to remove references to created temporary tables or declared temporary tables.
• Modify the SQL statement to remove the functionality that is not supported with temporary tables.

sqlcode: -526

sqlstate: 42995

SQL0528N

A UNIQUE clause uses the same column list as the PRIMARY KEY clause, another UNIQUE clause or the PRIMARY KEY or a UNIQUE constraint that already exists for the table tablename. Duplicate unique constraints are not allowed.

name is the constraint name, if one was specified or exists. If a constraint name is not specified, name is the first column name specified in the column list of the UNIQUE clause followed by three periods.

The statement cannot be processed.

Remove the duplicate UNIQUE clause or change the column list to a set of columns that is not already part of a unique constraint.

sqlcode: -528

sqlstate: 42891

SQL0530N

A value in a foreign key of the object table is being set, but this value is not equal to any value of the parent key of the parent table.

When a row is inserted into a dependent table, the insert value of a foreign key must be equal to some value of the parent key of any row of the parent table of the associated relationship.

Similarly, when the value of a foreign key is updated, the update value of a foreign key must be equal to the value of the parent key of any row of the parent table of the associated relationship at the completion of the statement.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Some data sources do not provide the appropriate values for constraint name. In these cases the message token will have the following format: “<data source>:UNKNOWN”, indicating that the actual value for the specified data source is unknown.

The statement could not be executed. The contents of the object table are unchanged.

Examine the insert or update value of the foreign key first, and then compare it with each of the parent key values of the parent table to determine and correct the problem.

sqlcode: -530

sqlstate: 23503

SQL0531N

An operation attempted to update a parent key in the row of the parent table but the parent key in the specified row has dependent rows in the dependent table associated with it in the constraint-name constraint.

When the update rule of constraint constraint-name is NO ACTION, the value of a parent key in a parent row cannot be updated if the parent row has any dependent rows at the completion of the statement.

When the update rule of constraint constraint-name is RESTRICT, the value of a parent key in a parent row cannot be updated if the parent row has any dependent rows at the time the update of the parent key is attempted.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Some data sources do not provide the appropriate values for constraint name. In these cases the message token will have the following format: “<data source>:UNKNOWN”, indicating that the actual value for the specified data source is unknown.

The statement could not be executed. The contents of the parent table are unchanged.

Examine the parent key of the object table and the foreign key of the dependent table to determine if the value of the specified row of the parent key should be changed. If this does not show the problem, examine the contents of the object table and the dependent table to determine and correct the problem.

sqlcode: -531

sqlstate: 23001, 23504

SQL0532N

An operation attempted to delete a specified row of the parent table but the parent key in the specified row has dependent rows in the referential constraint constraint-name and the delete rule of NO ACTION or RESTRICT is specified for the relationship.

When the delete rule of constraint constraint-name is NO ACTION, a row of the parent table cannot be deleted if the dependent rows are still dependent on the parent key at the completion of the statement.

When the delete rule of constraint constraint-name is RESTRICT, a row of the parent table cannot be deleted if the parent row has any dependent rows at the time of the delete.

Note that a delete can cascade to delete other rows in dependent tables that have a delete rule of NO ACTION or RESTRICT. Thus the constraint constraint-name may be on a different table than the original delete operation.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Some data sources do not provide the appropriate values for constraint name. In these cases the appropriate fields contain values (such as “unknown”) that indicate that the actual value is unknown.

The statement could not be executed. The contents of the table are unchanged.

Examine the delete rule for all descendent tables to determine and correct the problem. The specific tables involved can be determined from the relationship constraint-name.

sqlcode: -532

sqlstate: 23001, 23504

SQL0533N

An INSERT operation with a fullselect attempted to insert multiple rows into a table that is a parent and a dependent in the same relationship of a referential constraint.

The fullselect of the INSERT operation should return no more than one row of data.

The INSERT statement could not be executed. The contents of the object table are unchanged.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Examine the search condition of the fullselect to ensure selection of no more than one row of data.

sqlcode: -533

sqlstate: 21501

SQL0534N

An UPDATE operation attempted to perform a multiple-row update of a column included in a primary key or unique index.

Multiple-row updates of columns of a primary key or unique index are not supported.

The UPDATE statement could not be executed. The contents of the table are unchanged.

Federated system users: the constraint can exist on federated server (if the child and parent tables exist as tables on federated server), or it can exist on the data source (if the child and parent tables exist on the data source).

Ensure the search condition of the UPDATE statement selects only one object table row to update.

sqlcode: -534

sqlstate: 21502

SQL0535N

A DELETE operation with a WHERE clause attempted to delete multiple rows from a table which is a parent and a dependent in the same relationship of a referential constraint with a RESTRICT or SET NULL delete rule.

The WHERE clause of the DELETE operation should select no more than one row of data.

The DELETE statement could not be executed. The contents of the object table are unchanged.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Examine the WHERE clause search condition to ensure no more than one row of data is selected.

NOTE: This is only a restriction in releases of DB2 prior to Version 2.

sqlcode: -535

sqlstate: 21504

SQL0536N

A DELETE operation was attempted with the indicated table referenced in a subquery.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Some data sources do not provide the appropriate values for name. In these cases the appropriate fields contain values (such as “unknown”) that indicate that the actual value is unknown.

The statement could not be processed.

Do not reference a table in a DELETE statement subquery when the table can be affected by the DELETE statement.

NOTE: This error is only applicable to releases of DB2 prior toVersion 2 and hosts accessed through DB2 Connect.

sqlcode: -536

sqlstate: 42914

SQL0537N

The column column-name appears more than once in a key column list. A key column list could occur in a PRIMARY KEY clause, FOREIGN KEY clause, UNIQUE clause, DISTRIBUTE BY clause, ORGANIZE BY clause, PARTITION BY clause or as a functional dependency of a CREATE or ALTER statement.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Some data sources do not provide the appropriate values for column-name. In these cases the appropriate fields contain values (such as “unknown”) that indicate that the actual value is unknown.

The statement cannot be processed.

Remove the duplicate column names.

sqlcode: -537

sqlstate: 42709

SQL0538N

The definition of the indicated foreign key does not conform to the parent key description of the table or nickname table-name-or-nickname.

Possible reasons are:

• The number of columns in the foreign key column list does not match the number of columns in the parent key column list.
• The number of columns in the foreign key column list does not match the number of columns in the primary key of the parent table or the parent nickname (a parent key column list is not specified).
• The descriptions of the corresponding columns are not compatible. Column descriptions are compatible if corresponding columns have compatible data types (both columns are numeric, character strings, graphic, date/time, or have the same distinct type).
• The list of column names in a REFERENCES clause cannot reference a parent key defined with BUSINESS_TIME WITHOUT OVERLAPS.

name is the constraint name if specified in the FOREIGN KEY clause. If a constraint name was not specified, name is the first column name specified in the clause followed by three periods.

Federated system users should note that some data sources do not provide the appropriate values for name and table-name-or-nickname. In these cases the appropriate fields contain values (such as "unknown") that indicate that the actual value is unknown.

The constraint can exist the data source (if the child and parent tables exist on the data source).

The statement cannot be processed.

Correct the statement so the foreign key description conforms to the parent key description.

sqlcode: -538

sqlstate: 42830

SQL0539N

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Some data sources do not provide the appropriate values for name and key-type. In these cases the appropriate fields contain values (such as “unknown”) that indicate that the actual value is unknown.

The statement cannot be processed. A system catalog cannot be defined as a parent in a referential constraint.

When creating a referential constraint, specify the primary key before specifying any foreign keys (constraints).

sqlcode: -539

sqlstate: 42888

SQL0540N

The table named was defined with a PRIMARY KEY clause or a UNIQUE clause. Its definition is incomplete, and it cannot be used until a unique index is defined for the primary key (the primary index) and for each set of columns in any UNIQUE clause (the required unique indexes). An attempt was made to use the table in a FOREIGN KEY clause or in an SQL manipulative statement.

The statement cannot be executed.

Define a primary index or a required unique index on the table before referencing it.

sqlcode: -540

sqlstate: 57001

SQL0541W

If name refers to a referential constraint, then a FOREIGN KEY clause uses the same foreign key and parent table or the same foreign key and parent nickname as another FOREIGN KEY clause.

If name refers to a primary key or unique constraint, then one of the following situations exists.

• A PRIMARY KEY clause uses the same set of columns as a UNIQUE clause in the statement.
• A UNIQUE clause uses the same set of columns as the PRIMARY KEY clause or another UNIQUE clause in the statement.
• A PRIMARY KEY or a UNIQUE constraint on the same set of columns already exists for the table.

name is the constraint name, if specified.

If a constraint name was not specified, name is the first column name specified in the column list of the FOREIGN KEY or UNIQUE clause followed by three periods.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Some data sources do not provide the appropriate values for name. In these cases the appropriate fields contain values (such as "unknown") that indicate that the actual value is unknown.

The indicated referential constraint or unique constraint was not created. The statement was processed successfully.

No action is required. Processing can continue.

sqlcode: +541

sqlstate: 01543

SQL0542N

The column named column-name identified in a PRIMARY KEY clause or UNIQUE clause is defined to allow null values, or an attempt was made to alter a column that is part of a PRIMARY KEY or UNIQUE constraint to allow null values.

Federated system users: some data sources do not provide the appropriate values for column-name. In these cases the appropriate fields contain values (such as "unknown") that indicate that the actual value is unknown.

The statement cannot be processed.

Correct the column, primary key or unique key definition. If this error was returned as part of an alter table statement, the PRIMARY KEY or UNIQUE constraint must be removed before the column can be altered to allow null values.

sqlcode: -542

sqlstate: 42831

SQL0543N

The delete operation cannot be executed because the target table is a parent table and is connected with a referential constraint to a dependent table with a delete rule of SET NULL. However, a check constraint defined on the dependent table restricts the column from containing a null value.

The statement cannot be processed.

Examine the foreign key and its delete rule in the dependent table and the conflicting check constraint. Change either the delete rule or the check constraint so that they do not conflict with each other.

sqlcode: -543

sqlstate: 23511

SQL0544N

At least one existing row in the table violates the check constraint that is be added in the ALTER TABLE statement.

The statement cannot be processed.

Examine the check constraint definition that was specified in the ALTER TABLE statement and the data in the table to determine why there is a violation of the constraint. Change either the check constraint or the data so that the constraint is not violated.

sqlcode: -544

sqlstate: 23512

SQL0545N

Check constraint violations can occur on either INSERT or UPDATE operations. The resulting row violated the check constraint definition on that table.

The statement cannot be processed.

Examine the data and the check constraint definition in the catalog view SYSCAT.CHECKS to determine why the INSERT or UPDATE statement failed. Change the data so that the constraint is not violated.

sqlcode: -545

sqlstate: 23513

SQL0546N

A check constraint in the CREATE TABLE or CREATE NICKNAME, ALTER TABLE, or ALTER NICKNAME statement is invalid for one or more of the following reasons:

• the constraint definition contains a subquery
• the constraint definition contains a column function
• the constraint definition contains a host variable
• the constraint definition contains a parameter marker
• the constraint definition contains a special register or a built-in function that depends on the value of a special register
• the constraint definition contains a global variable
• the constraint definition contains a variant user defined function
• the constraint definition contains a user defined function with external action
• the constraint definition contains a user defined function with the scratchpad option
• the check constraint is part of a column definition, and its check-condition contains a reference to a column name other than the column being defined.
• the constraint definition contains a dereference operation or a DEREF function where the scoped reference argument is other than the object identifier (OID) column.
• the constraint definition uses a TYPE predicate.
• the constraint definition includes a CAST specification with a SCOPE clause.
• the functional dependency is defined with attribute ENFORCED.
• a nullable column is specified in the child-set columns of the functional dependency.
• the constraint definition contains a text search function.
• the constraint definition contains an XML column.
• the constraint definition contains an XMLQUERY or XMLEXISTS expression.

The statement cannot be processed.

The user response is one of the following, depending on the cause of the error:

• change the check constraint so that it does not include the listed item.
• move the check constraint definition outside the column definition, so that it becomes a table level constraint definition.
• change the functional dependency so that it does not specify the ENFORCED attribute.
• change the nullable column to not nullable in the child-set of columns of the functional dependency.

sqlcode: -546

sqlstate: 42621

SQL0548N

A check constraint in the CREATE TABLE or CREATE NICKNAME, ALTER TABLE or ALTER NICKNAME statement is invalid for one or more of the following reasons:

• the constraint definition contains a subquery
• the constraint definition contains a column function
• the constraint definition contains a host variable
• the constraint definition contains a parameter marker
• the constraint definition contains a special register or a built-in function that depends on the value of a special register
• the constraint definition contains a global variable
• the constraint definition contains a function that is not deterministic
• the constraint definition contains a user defined function with external action
• the constraint definition contains a user defined function with the scratchpad option
• the definition contains a user defined function with the CONTAINS SQL or READS SQL DATA option
• the definition contains a reference to a generated column which is based on an expression
• the check constraint is part of a column definition, and its check-condition contains a reference to a column name other than the column being defined
• the generated column definition contains a reference to itself
• the constraint definition contains a dereference operation or a DEREF function where the scoped reference argument is other than the object identifier (OID) column
• the constraint definition uses a TYPE predicate
• the constraint definition includes a CAST specification with a SCOPE clause
• the constraint or generated column definition contains a text search function
• the constraint definition contains an XML column
• the constraint definition contains an XMLQUERY or XMLEXISTS expression
• the generated column expression references a column for which a column mask is defined

The token within the text of the error message lists the invalid item.

The statement cannot be processed.

The user response is one of the following, depending on the cause of the error:

• change the check constraint or generated column so that it does not include the listed item.
• move the check constraint definition outside the column definition, so that it becomes a table level constraint definition.

sqlcode: -548

sqlstate: 42621

SQL0549N

A program attempted to issue the indicated SQL statement that is one of several SQL statements that can be dynamically prepared only in a package exhibiting run behavior. Those SQL statements are:

• Dynamic GRANT statement
• Dynamic REVOKE statement
• Dynamic ALTER statement
• Dynamic CREATE statement
• Dynamic DROP statement
• Dynamic COMMENT ON statement
• Dynamic RENAME statement
• Dynamic SET INTEGRITY statement
• Dynamic SET EVENT MONITOR STATE statement
• Dynamic TRANSFER OWNERSHIP statement
• Dynamic CREATE USAGE LIST
• Dynamic ALTER USAGE LIST

statement

The SQL statement in error

object-type1

PACKAGE or DBRM. DBRM is valid only in a DRDA connection.

object-name1

If object-type1 is PACKAGE, object-name1 is the name of the package in the format 'location-id.collection-id.package-id'. If object-type1 is DBRM, object-name1 is the name of the DBRM in the format 'plan-name DBRM-name'.

object-type2

PACKAGE or PLAN. PLAN is valid only in a DRDA connection. If object-type1 is PACKAGE, object-type2 can be either PACKAGE or PLAN (whichever is bound with DYNAMICRULES(BIND)). If object-type1 is DBRM, object-type2 is PLAN.

The SQL statement cannot be executed.

Do one of the following to correct the error:

• If the SQL statement is embedded, remove it, precompile and compile the application program again, and reissue the BIND command.
• If appropriate, use the SQL statement with a package or plan that is bound with DYNAMICRULES(RUN).
• Issue the REBIND command with the DYNAMICRULES(RUN) option for the plan or package to which the SQL statement is bound.

sqlcode: -549

sqlstate: 42509

SQL0551N

The operation could not be performed on the specified object. In general, this message is returned because the authorization ID does not have the required authority or privilege to perform the operation. In some cases, it is returned for an object that does not allow the operation even when the authorization ID has an administrative authority.

This message can be returned for different reasons. Here are some examples of scenarios in which this message could be returned:

Scenario 1

An attempt to create or alter a table that has referential constraints fails because the user does not have REFERENCE privileges. In this scenario, the value of the runtime token operation is "REFERENCES" and the value of the runtime token object-name is the object that the constraint references.

Scenario 2

Execution of a DB2 utility or CLI application fails because the user ID that created the database no longer exists or no longer has the privileges required to execute the DB2 utility program or CLI package.

Scenario 3

Invocation of a routine fails because the user does not have EXECUTE privileges on any candidate routine in the SQL path. In this scenario, the value of the runtime token object-name is the name of a candidate routine in the SQL path.

Scenario 4

Creation of a routine fails because the user ID that was used to bind the routine does not have the privileges necessary to issue all the statements in the routine.

Scenario 5

Replacing an existing object by using a CREATE OR REPLACE statement failed because the user is not the owner of the object.

Scenario 6

An attempt to complete an online move table operation fails because the user does not have the same authorization ID as the user who initiated the online move table operation.

Scenario 7

An attempt is made to perform an unsupported operation on a system-generated statistical view that is associated with an expression-based index. It is not supported for any user to modify the privileges of or to access this type of statistical view. However, users with adequate authority and privileges on the underlying table may update the statistics of this type of statistical view, either by issuing the RUNSTATS command or by manually updating the statistics.

In Federated environments:

This message might be returned when the following actions fail because the user does not have the necessary authority:

• Altering one of the following user-mapping options:
  • REMOTE_PASSWORD
  • FED_PROXY_USER
  • USE_TRUSTED_CONTEXT
• Altering a trusted user context
• Creating or dropping a user mapping that has the FED_PROXY_USER option or the USE_TRUSTED_CONTEXT option
• Creating or dropping a server that has the FED_PROXY_USER option

The required authorization can be at the federated server, at the data source, or at both the federated server and the data source.

Some data sources do not provide appropriate values for the runtime tokens authorization-ID, operation, and object-name. In these cases, the message tokens might be in one of the following formats:

• <data source> AUTHID:UNKNOWN
• UNKNOWNM
• <data source> :TABLE/VIEW.

If the authorization ID authorization-ID does not have the required authority or privilege, grant the necessary privilege or authority, if appropriate.

Here are responses for the scenarios described in the explanation section of this message:

Scenario 1

Grant the REFERENCES privilege by using the statement GRANT REFERENCES ON object-name TO authorization-ID.

Scenario 2

Rebind the DB2 utility programs or CLI packages to the database by connecting to the database and then issuing one of the following CLP commands from the bnd subdirectory of the instance directory:

• For the DB2 utilities: DB2 bind @db2ubind.lst blocking all grant public
• For CLI packages: DB2 bind @db2cli.lst blocking all grant public

If the user does not have EXECUTE privileges on the package, grant the EXECUTE privilege on the package, by use executing the GRANT statement.

Scenario 3

Grant the EXECUTE privilege on the routine to the authorization ID by executing the GRANT statement.

Scenario 4

Grant explicit privileges to the authorization ID on the objects that the statements within the routine are attempting to access.

Scenario 5

Respond to scenario 5 in one of the following ways:

• Issue the statement as the user who is the owner of the object to be replaced.
• Transfer the ownership of the object by executing the TRANSFER OWNERSHIP statement.

Scenario 6

Call the procedure again, using the same authorization ID as the user who initiated the online move table operation.

Scenario 7

Specify a different object for the operation.

sqlcode: -551

sqlstate: 5UA0K, 42501

SQL0552N

Authorization ID authorization-ID attempted to perform the specified operation without the proper authorization.

Federated system users: some data sources do not provide the appropriate values for authorization-ID and <operation>. In these cases the message tokens will have the following format: "<data source> AUTHID:UNKNOWN", and "UNKNOWN" indicating that the actual values for authid and operation at the specified data source are not known.

The statement cannot be processed.

Ensure that authorization-ID has the authorization to perform the operation.

Federated system users: this authorization can be at the federated server, the data source, or both.

sqlcode: -552

sqlstate: 42502

SQL0553N

The reason the schema name schema-name is invalid depends on the type of object that is being created.

• Table, view, index and package objects cannot be created with the schema name SYSCAT, SYSFUN, SYSPUBLIC, SYSSTAT, SYSIBM, or SYSIBMADM. It is strongly advised that schema names should not start with SYS since additional schemas starting with these letters may be reserved for exclusive use of database products in the future.
• All other types of objects (for example: user defined functions, distinct types, triggers, schemas, aliases, usage lists) cannot be created with any schema name that starts with the letters SYS.

The statement cannot be processed.

Use a valid schema name or remove the explicit schema name and rerun the statement.

sqlcode: -553

sqlstate: 42939

SQL0554N

An authorization ID attempted to execute a statement that would grant a privilege or authority to the authorization ID itself. If this is a GRANT statement, the authorization ID itself appears as one of the entries in the authorization ID list to which privileges, authorities, security labels, or exemptions are to be granted. If this is a CREATE TRUSTED CONTEXT or an ALTER TRUSTED CONTEXT, the authorization ID itself appears as either the value for the SYSTEM AUTHID attribute or one of the authorization names specified in the WITH USE FOR clause.

The statement cannot be processed.

Remove or replace the authorization ID in the statement.

sqlcode: -554

sqlstate: 42502

SQL0555N

An authorization ID attempted to execute a REVOKE statement where the authorization ID itself appears as one of the entries in the authorization ID list from which privileges are to be revoked.

The statement cannot be processed.

Remove the authorization ID from the list.

sqlcode: -555

sqlstate: 42502

SQL0556N

The privilege, security label, exemption, or role cannot be revoked because authorization-ID does not hold the privilege, security label, exemption, or role.

The statement cannot be processed.

Change the REVOKE statement to conform to the REVOKE rules and resubmit the statement. Ensure that when a REVOKE statement lists several privileges, security labels, exemptions, or roles to be revoked and a list of authorization IDs that each authorization ID possesses at least one of the specified privileges, security labels, exemptions, or roles.

sqlcode: -556

sqlstate: 42504

SQL0557N

The statement cannot be processed.