SQL0000W Statement processing was successful.

Explanation

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

User response

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 Binding or precompilation did not complete successfully.

Explanation

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

No package is created.

User response

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 The bind file name is not valid.

Explanation

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

No package is created.

User response

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 The database name is not valid.

Explanation

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

No package is created.

User response

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

SQL0004N The password is not valid.

Explanation

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

No package is created.

User response

Resubmit the command with a valid password.

SQL0005N The message file name is not valid.

Explanation

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

No package is created.

User response

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 datetime format parameter is not valid.

Explanation

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

No package is created.

User response

Resubmit the command with a valid format parameter.

SQL0007N The statement was not processed because a character that is not supported in SQL statements was included in the SQL statement. Invalid character: character. Text preceding the invalid character: text.

Explanation

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.

User response

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

sqlcode: -7

sqlstate: 42601

SQL0008N The token token found in a host variable declaration is not valid.

Explanation

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.

User response

Examine the syntax of the host variable declaration.

SQL0009W An attempt to override a precompilation option was ignored.

Explanation

An attempt was made to override a precompiler option.

The option is ignored.

User response

Ensure that all precompiler options are specified correctly.

SQL0010N The string constant beginning with string does not have an ending string delimiter.

Explanation

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

The statement cannot be processed.

User response

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

sqlcode: -10

sqlstate: 42603

SQL0011N The comment is not terminated.

Explanation

The comment is not terminated properly.

The statement cannot be processed.

User response

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

SQL0012W Correlation without qualification has occurred for the column column.

Explanation

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.

User response

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

sqlcode: +12

sqlstate: 01545

SQL0013N An empty delimited identifier is not valid.

Explanation

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.

User response

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

SQL0014N The source file name is not valid.

Explanation

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.

User response

Ensure that the source file name is correct.

SQL0015N The host variable data type token-1 is not valid. Use token-2 instead.

Explanation

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'.

User response

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

SQL0017N A RETURN statement must be specified and executed in an SQL function or method.

Explanation

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.

User response

Ensure the function or method executes a RETURN statement.

sqlcode: -17

sqlstate: 42632

SQL0020W The bind or precompile command parameters or parameter values in the following list were ignored because they are not supported by the target database: parameter-names-or-values.

Explanation

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.

User response

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

SQL0021W Invalid precompiler option option is ignored.

Explanation

The option specified in the message is not a valid precompiler option.

The option is ignored.

User response

Ensure that all precompiler options are specified correctly.

SQL0022W Duplicate precompiler option option is ignored.

Explanation

The precompiler option option is a duplicate.

The option is ignored.

User response

Ensure that all precompiler options are specified only once.

SQL0023N The database name is not valid.

Explanation

The specified database name is not a valid name.

Precompilation is terminated.

User response

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

SQL0024N A database name was not specified.

Explanation

No database name was provided to proceed with precompilation.

Precompilation is terminated.

User response

Provide a database name.

SQL0025W Binding or precompilation completed with warnings.

Explanation

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

User response

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

SQL0026N The password is not valid.

Explanation

The specified password is not a valid password.

Precompilation is terminated.

User response

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

SQL0028C The release number of the bind file is not valid.

Explanation

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.

User response

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 INTO clause required.

Explanation

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.

User response

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 specified.

Explanation

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

Precompilation is terminated.

User response

Specify a source file name.

SQL0031C File name could not be opened.

Explanation

The file name is required but could not be opened.

Precompilation is terminated.

User response

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 Unable to use file name.

Explanation

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

Precompilation is terminated.

User response

Retry the precompilation.

SQL0033N name is not a valid bind file.

Explanation

The bind file name specified is not a bind file.

Binding is terminated.

User response

Ensure that the correct file name was specified.

SQL0034N A bind file name was not specified.

Explanation

No bind file name was specified to proceed with binding.

Binding is terminated.

User response

Provide a bind file name.

SQL0035N The file name cannot be opened.

Explanation

The message file name could not be opened.

Binding or precompilation has been terminated.

User response

Ensure that the system can access the file.

SQL0036N The syntax of the file name name is not valid.

Explanation

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.

User response

Ensure that the specified file name is correct.

SQL0037W The syntax of the message file name is not valid.

Explanation

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

The system redirects the output to the standard output device.

User response

Ensure that the specified file name is correct.

SQL0038W The bind option SQLERROR CONTINUE has been activated since it is required when binding this DB2-supplied list file to DB2/MVS, SQL/DS, or OS/400.

Explanation

The SQLERROR CONTINUE bind option is required when binding the following DB2-supplied list files:
  • ddcsmvs.lst
  • ddcsvm.lst
  • ddcsvse.lst
  • ddcs400.lst

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.

User response

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 did not finish processing because the bind file is not valid.

Explanation

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.

User response

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

SQL0040N An error occurred on one or more bind files in list name. The following files were not bound: list.

Explanation

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.

User response

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

SQL0041N No attempt was made to bind the files following file number number in list name because a fatal error occurred which terminated processing.

Explanation

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.

User response

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

SQL0051N The space required to hold all SQL statements for a single program has exceeded its allowed maximum.

Explanation

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

Precompilation is terminated.

User response

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

SQL0053W No SQL statements were found in the program.

Explanation

The specified source file contains no SQL statements.

If binding, an empty package is created.

User response

Ensure that you are precompiling or binding the correct program.

SQL0055N Source input file is empty.

Explanation

The program source input file contained no data.

Precompilation is terminated.

User response

Ensure that the correct input file was specified.

SQL0056N An SQLSTATE or SQLCODE variable declaration is in a nested compound statement.

Explanation

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.

User response

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

sqlcode: -56

sqlstate: 42630

SQL0057N A RETURN statement in an SQL function or method must include a return value.

Explanation

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

The statement cannot be processed.

User response

Specify a value on the RETURN statement.

sqlcode: -57

sqlstate: 42631

SQL0058N The data type of the RETURN statement value in an SQL procedure must be INTEGER.

Explanation

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.

User response

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

sqlcode: -58

sqlstate: 428F2

SQL0060W The name precompiler is in progress.

Explanation

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.

User response

No action is required.

SQL0061W The binder is in progress.

Explanation

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

Processing continues.

User response

No action is required.

SQL0062W Starting INCLUDE of file name.

Explanation

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

User response

No action is required.

SQL0063W Completed INCLUDE of file name.

Explanation

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

User response

No action is required.

SQL0064N File name directly or indirectly INCLUDEs itself.

Explanation

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.

User response

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

SQL0065N An unexpected end-of-line was encountered in a host variable declaration.

Explanation

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

The statement cannot be processed.

User response

Examine the syntax of the host variable declaration.

SQL0078N Parameter names must be specified for routine routine-name.

Explanation

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.

User response

Ensure that each defined parameter includes a parameter name.

sqlcode: -78

sqlstate: 42629

SQL0079N The Schema name for the declared global temporary table or index name must be SESSION, not schema-name.

Explanation

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.

User response

Change the statement in one of the following ways:
  • Change the schema name to SESSION.
  • Remove the schema name and let DB2 use the default value, SESSION.

sqlcode: -79

sqlstate: 428EK

SQL0081N SQLCODE sqlcode returned during precompilation/bind.

Explanation

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

The statement cannot be processed.

User response

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

SQL0082C An error has occurred which has terminated processing.

Explanation

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

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

User response

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

SQL0083C A memory allocation error has occurred.

Explanation

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

User response

Possible solutions include:
  • Ensure that your system has sufficient real and virtual memory.
  • Remove background processes.

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

SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES statement.

Explanation

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

The statement cannot be processed.

User response

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

sqlcode: -84

sqlstate: 42612

SQL0085N The statement name name is already defined.

Explanation

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.

User response

Use a different name for the statement.

SQL0086C A memory allocation error has occurred.

Explanation

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

User response

Possible solutions include:
  • Verify that your system has the required memory.
  • Remove background processes.

SQL0087N A null value was specified in a context where a null value is not allowed.

Explanation

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.

User response

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 is ambiguous.

Explanation

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

The statement cannot be processed.

User response

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

SQL0089N Processing ended after 100 errors were found.

Explanation

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

User response

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

SQL0091W Precompilation or binding was ended with number-1 errors and number-2 warnings.

Explanation

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

Precompilation or binding is terminated.

User response

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

SQL0092N No package was created because of previous errors.

Explanation

Because of previous errors, no package was created.

User response

Correct the errors and retry the precompilation or bind.

SQL0093N The end-of-input was encountered before the statement terminator for EXEC SQL.

Explanation

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

Precompilation is terminated.

User response

Ensure that the SQL statement terminates properly.

SQL0094N Binding was ended because of a user interrupt request.

Explanation

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

Processing is terminated. No package is created.

User response

Resubmit the bind, if needed.

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

SQL0095N No bind file was created because of previous errors.

Explanation

Because of previous errors, no bind file was created.

No bind file is created.

User response

Correct the errors and retry the precompilation.

SQL0097N Variables or parameters of LONG VARCHAR or LONG VARGRAPHIC data types are not supported in SQL routines.

Explanation

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

User response

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 row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.

Explanation

One of the following conditions is true:
  • No row was found that meets the search conditions specified in an UPDATE or DELETE statement.
  • The result of a SELECT statement was an empty table.
  • A FETCH statement was executed when the cursor was positioned after the last row of the result table.
  • The result of the SELECT used in an INSERT statement is empty.

No data was retrieved, updated, or deleted.

User response

No action is required. Processing can continue.

sqlcode: +100

sqlstate: 02000

SQL0101N The statement was not processed because a limit such as a memory limit, an SQL limit, or a database limit was reached.

Explanation

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.)

User response

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 The string constant beginning with string is too long.

Explanation

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.

User response

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 numeric literal literal is not valid.

Explanation

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.

User response

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 An unexpected token token was found following text. Expected tokens may include: token-list.

Explanation

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.

The statement cannot be processed.

User response

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.

sqlcode: -104

sqlstate: 42601

SQL0105N The string constant beginning with string is not valid.

Explanation

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

The statement cannot be processed.

User response

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 SQL statement begins properly but is incomplete.

Explanation

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.

User response

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 name is too long. The maximum length is length.

Explanation

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.

User response

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 has the wrong number of qualifiers.

Explanation

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.

User response

Ensure that the name for the object is qualified correctly.

sqlcode: -108

sqlstate: 42601

SQL0109N The statement or command was not processed because the following clause is not supported in the context where it is used: clause.

Explanation

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.

User response

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 string is an invalid hexadecimal constant.

Explanation

The hexadecimal constant string is invalid. The problem is one of the following:
  • An invalid hexadecimal digit was specified. Only '0 to 9', 'A to F', and 'a to f' are allowed.
  • An uneven number of hexadecimal digits was specified.
  • More than 8000 hexadecimal digits were specified.

User response

Correct the constant and resubmit the statement.

sqlcode: -110

sqlstate: 42606

SQL0111N The column function name does not include a column name.

Explanation

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.

User response

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 The operand of the column function name includes a column function, a scalar fullselect, or a subquery.

Explanation

The operand of a column function cannot include:
  • a column function
  • a scalar fullselect
  • a subquery
  • an XMLQUERY or XMLEXISTS expression except as an operand of an XMLAGG column function.

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.

User response

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

sqlcode: -112

sqlstate: 42607

SQL0113N identifier contains a character that is not allowed or does not contain any characters.

Explanation

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.

User response

Correct the identifier and resubmit the statement.

sqlcode: -113

sqlstate: 42601

SQL0117N The number of values assigned is not the same as the number of specified or implied columns or variables.

Explanation

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.

User response

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 that is the target of the INSERT, DELETE, or UPDATE statement is also specified in a FROM clause.

Explanation

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.

User response

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 An expression starting with expression-start specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.

Explanation

The SELECT statement has one of the following errors:
  • The identified expression and a column function are contained in the SELECT clause, HAVING clause, or ORDER BY clause but there is no GROUP BY clause
  • The identified expression is contained in the SELECT clause, HAVING CLAUSE, or ORDER BY clause but is not in the GROUP BY clause.

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.

User response

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 Invalid use of an aggregate function or OLAP function.

Explanation

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.

User response

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 target name name is specified more than once for assignment in the same SQL statement.

Explanation

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.

User response

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

sqlcode: -121

sqlstate: 42701

SQL0122N A SELECT statement with no GROUP BY clause contains a column name or expression and a column function in the SELECT clause, or a column name or expression is contained in the SELECT clause but not in the GROUP BY clause.

Explanation

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.

User response

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 must be a constant, a keyword, a variable, or a cast of a constant or variable.

Explanation

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

User response

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

sqlcode: -123

sqlstate: 428I9, 42601

SQL0125N The column number in the ORDER BY or GROUP BY clause is either less than one or greater than the number of columns in the result table.

Explanation

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.

User response

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 DISTINCT is specified more than once.

Explanation

The DISTINCT qualifier cannot be used:
  • In both the SELECT clause and a column function
  • In two or more column functions in the same SELECT statement.

The statement cannot be processed.

User response

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 statement contains too many table names.

Explanation

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.

User response

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 clause is not a single character, or the pattern string contains an invalid occurrence of the escape character.

Explanation

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.

User response

Correct the pattern string or the escape character accordingly.

sqlcode: -130

sqlstate: 22019, 22025

SQL0131N The operands of a LIKE predicate have incompatible data types.

Explanation

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.

User response

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

sqlcode: -131

sqlstate: 42818

SQL0132N The statement was not processed because a value was specified for a LIKE predicate or a scalar function where a string expression was expected.

Explanation

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

User response

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

sqlcode: -132

sqlstate: 42824

SQL0134N Improper use of a string column, host variable, constant, or function name.

Explanation

The use of the string name is not permitted.

An expression resulting in a CLOB, DBCLOB, BLOB, LONG VARCHAR, or LONG VARGRAPHIC data type is not permitted in:
  • A SELECT DISTINCT statement
  • A GROUP BY clause
  • An ORDER BY clause
  • A column function with DISTINCT
  • A SELECT or VALUES statement of a set operator other than UNION ALL.

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.

User response

The requested operation on the string is not supported.

sqlcode: -134

sqlstate: 42907

SQL0135N The input for a long string column in an INSERT statement or UPDATE statement must be from a host variable or be the keyword NULL.

Explanation

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.

User response

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 length resulting from operation is greater than maximum-value.

Explanation

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.

User response

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 The statement was not executed because a numeric argument of a scalar function is out of range.

Explanation

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.

User response

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 A redundant clause appears in the specification for column column.

Explanation

The clause in the column specification is redundant.

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

User response

Correct the column specification.

sqlcode: +139

sqlstate: 01589

SQL0142N The SQL statement is not supported.

Explanation

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.

User response

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 The SQL statement is not supported; invalid syntax is ignored.

Explanation

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.

User response

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

SQL0150N The target fullselect, view, typed table, materialized query table, range-clustered table, or staging table in the INSERT, DELETE, UPDATE, MERGE, or TRUNCATE statement is a target for which the requested operation is not permitted.

Explanation

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.

User response

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 column name cannot be updated.

Explanation

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.

User response

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 Constraint constraint-name is a actual-constraint-type constraint instead of a expected-constraint-type constraint.

Explanation

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.

User response

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

sqlcode: -152

sqlstate: 42809

SQL0153N The statement does not include a required column list.

Explanation

A column list must be specified in a CREATE VIEW statement, a common table expression or in a CREATE TABLE statement containing an as-subquery-clause when:
  • any element of the SELECT list in the fullselect is other than a column name and is not named using the AS clause.
  • any two elements are the same column name that were not renamed using the AS clause.

The statement cannot be processed.

User response

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 A trigger transition table cannot be modified.

Explanation

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.

User response

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 name used for this operation is not a table.

Explanation

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.

User response

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 name is not allowed in a FOREIGN KEY clause because it does not identify a base table.

Explanation

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.

User response

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 columns specified for name is not the same as the number of columns in the result table.

Explanation

The identifier name could identify:
  • a view named in a CREATE VIEW statement
  • a table name of a common table expression
  • a correlation name of a nested table expression
  • a materialized query table named in a CREATE TABLE or ALTER TABLE statement
  • a function named in a CREATE FUNCTION statement
  • a method named in a CREATE METHOD statement.
  • a staging table name in a CREATE STAGING TABLE statement

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.

User response

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 statement references an object that identifies an unexpected object type. Object: object. Object type: object-type. Expected object type: expected-object-type.

Explanation

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.

User response

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

sqlcode: -159

sqlstate: 42809

SQL0160N The WITH CHECK OPTION clause is not valid for the specified view.

Explanation

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.

User response

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 resulting row of the insert or update operation does not conform to the view definition.

Explanation

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.

User response

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 number of arguments for function name is incorrect.

Explanation

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

The statement cannot be processed.

User response

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

sqlcode: -170

sqlstate: 42605

SQL0171N The statement was not processed because the data type, length or value of the argument for the parameter in position n of routine routine-name is incorrect. Parameter name: parameter-name.

Explanation

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.

User response

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 name is not a valid function name.

Explanation

The SQL statement includes an unknown scalar function.

The statement cannot be processed.

User response

Ensure that you correctly spell the function name.

sqlcode: -172

sqlstate: 42601

SQL0176N The second, third or fourth argument of the TRANSLATE scalar function is incorrect.

Explanation

The statement is not correct for one or more of the following reasons:
  • The translate scalar function does not allow replacement of a character by another character which is encoded using a different number of bytes. For example, a single-byte character cannot be replaced with a double-byte character nor can a double-byte character be replaced with a single-byte character.
  • The second and third arguments of the translate scalar function must end with correctly formed characters.
  • The fourth argument of the translate scalar function must be a correctly formed single-byte character if the first argument is CHAR or VARCHAR.
  • The fourth argument of the translate scalar function must be a correctly formed double-byte character if the first argument is GRAPHIC or VARGRAPHIC.

The statement cannot be processed.

User response

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

sqlcode: -176

sqlstate: 42815

SQL0180N The syntax of the string representation of a datetime value is incorrect.

Explanation

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.

User response

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 datetime value is out of range.

Explanation

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.

User response

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 An expression with a datetime value or a labeled duration is not valid.

Explanation

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

The statement cannot be processed.

User response

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

sqlcode: -182

sqlstate: 42816

SQL0183N A datetime arithmetic operation or a datetime scalar function has a result that is not within the valid range of dates.

Explanation

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.

User response

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 A reference to a current date/time special register is invalid.

Explanation

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

User response

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

sqlcode: -187

sqlstate: 22506

SQL0190N ALTER TABLE table-name specified attributes for column column-name that are not compatible with the existing column.

Explanation

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.

User response

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 Error occurred because of a fragmented MBCS character.

Explanation

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.

User response

  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 In an ALTER TABLE statement, the column column-name has been specified as NOT NULL and either the DEFAULT clause was not specified or was specified as DEFAULT NULL.

Explanation

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.

User response

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 Last column of table-name cannot be dropped.

Explanation

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.

User response

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 Column column-name in table-name cannot be dropped. Reason code = reason-code.

Explanation

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.

User response

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 A qualified column name is not allowed in the ORDER BY clause.

Explanation

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

User response

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

sqlcode: -197

sqlstate: 42877

SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE statement is blank or empty.

Explanation

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.

User response

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 The use of the reserved word keyword following text is not valid. Expected tokens may include: token-list.

Explanation

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.

User response

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 A reference to column name is ambiguous.

Explanation

The column name is used in the statement and there is more than one possible column to which it could refer. This could be the result of:
  • two tables specified in a FROM clause that have columns with the same name
  • the ORDER BY clause refers to a name that applies to more than one column in the select list
  • a reference to a column from the subject table in a CREATE TRIGGER statement does not use the correlation name to indicate if it refers to the old or new transition variable.

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

The statement cannot be processed.

User response

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 name is an undefined name.

Explanation

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.

User response

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 Column, attribute, or period name is not defined in object-name.

Explanation

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.

User response

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 name is not valid in the context where it is used.

Explanation

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.

User response

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 column name is not allowed in the ORDER BY clause of a SELECT statement used with a set operator.

Explanation

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.

User response

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 ORDER BY clause is not valid because column name is not part of the result table.

Explanation

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.

User response

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 name is a duplicate table designator or is specified more than once in the REFERENCING clause of a trigger definition.

Explanation

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.

User response

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 Parameter parameter-name not in routine routine-name.

Explanation

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

User response

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

sqlcode: -213

sqlstate: 42703

SQL0214N An expression in the ORDER BY clause in the following position, or starting with expression-start-or-order-by-position in the clause-type clause is not valid. Reason code = reason-code.

Explanation

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.

User response

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 The number of elements on each side of a predicate operator does not match. Predicate operator is predicate-operator.

Explanation

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.

User response

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

sqlcode: -216

sqlstate: 428C4

SQL0217W The statement was not executed as only Explain information requests are being processed.

Explanation

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.

User response

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 required Explain table name does not exist.

Explanation

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.

User response

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 The Explain table name, column name2 does not have the proper definition or is missing.

Explanation

The Explain facility has been invoked but the Explain table name did not have the expected definition. The definition could be incorrect due to:
  • Incorrect number of columns defined (if name2 is numeric)
  • Incorrect data type assigned to columns (if name2 is a column name).
  • Incorrect CCSID for the table.

User response

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.

sqlcode: -220

sqlstate: 55002

SQL0222N Operation was attempted against a hole using cursor cursor-name.

Explanation

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.

User response

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

sqlcode: -222

sqlstate: 02502, 24510

SQL0224N The result table does not agree with the base table using cursor cursor-name.

Explanation

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.

User response

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 FETCH statement for cursor cursor-name is not valid because the cursor is not defined as SCROLL.

Explanation

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.

User response

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 FETCH NEXT, PRIOR, CURRENT or RELATIVE is not allowed because cursor cursor-name has an unknown position (sqlcode, sqlstate).

Explanation

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.

User response

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 FOR UPDATE clause specified for read-only cursor cursor-name.

Explanation

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

The statement cannot be processed.

User response

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 Current position of cursor cursor-name is not valid for FETCH of the current row.

Explanation

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.

User response

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 SQLDA has only provided integer1 SQLVAR entries. integer2 SQLVAR entries are required for integer3 columns. No SQLVAR entries have been set.

Explanation

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)).

User response

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 SQLDA has only provided integer1 SQLVAR entries. Since at least one of the columns being described is a distinct type, integer2 SQLVAR entries should have been specified. None of the Secondary SQLVAR entries have been set.

Explanation

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).

User response

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 SQLDA has only provided integer1 SQLVAR entries. Since at least one of the columns being described is a LOB or structured type, integer2 SQLVAR entries are required for integer3 columns. No SQLVAR entries have been set.

Explanation

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).

User response

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 SQLDA has only provided integer1 SQLVAR entries. Since at least one of the columns being described is a distinct type or reference type, integer2 SQLVAR entries are required for integer3 columns. No SQLVAR entries have been set.

Explanation

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).

User response

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 The object named object-name of type object-type was specified more than once in the list of objects.

Explanation

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.

User response

Correct the duplicated object in the list removing duplicate occurrences.

sqlcode: -242

sqlstate: 42713

SQL0243N SENSITIVE cursor cursor-name cannot be defined for the specified SELECT statement.

Explanation

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.

User response

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 SENSITIVITY sensitivity specified on FETCH is not valid for cursor cursor-name.

Explanation

The sensitivity option sensitivity specified on FETCH conflicts with the sensitivity option in effect for cursor cursor-name. The following list shows what can be specified on FETCH:
 
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.

User response

Change or remove the sensitivity option specified on FETCH.

sqlcode: -244

sqlstate: 428F4

SQL0245N The invocation of routine routine-name is ambiguous. The argument in position position does not have a best fit.

Explanation

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.

User response

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