Message codesDescription

USQL -007

STATEMENT CONTAINS THE ILLEGAL CHARACTER character

Explanation: The specified character is not a valid character in SQL statements.

User Response: Correct the syntax and resubmit the statement. See to the SQL Reference Manual for information about the valid SQL character set.

USQL -010

THE STRING CONSTANT BEGINNING string IS NOT TERMINATED

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

User Response: Examine the statement for missing quotation marks or apostrophes in the indicated string constant.

USQL -029

INTO CLAUSE REQUIRED

Explanation: SELECT statements embedded in an application program must have an INTO clause to denote where the results of the SELECT are to be placed. Dynamic SELECT statements do not permit the INTO clause.

User Response: Add the INTO clause to the SELECT statement and recompile the application program again.

USQL -060

INVALID type SPECIFICATION: spec

Explanation: type is either LENGTH or SCALE. spec is the specified length or scale. Length or scale must be specified by an unsigned integer constant and the value must be in the range allowed by the data type.

User Response: Correct the statement. See to the SQL Reference Manual for rules regarding length and scale.

USQL -084

UNACCEPTABLE SQL STATEMENT

Explanation: This SQL statement is unacceptable to Db2. One of the following actions has occurred:

• An attempt has been made to PREPARE or EXECUTE IMMEDIATE an SQL statement that cannot be prepared; see to the proper SQL statement as noted in the SQL Reference Manual.
• The embedded SQL statement is not an SQL statement supported by Db2.
• The statement referenced an undeclared cursor.
• An attempt was made to prepare an ALLOCATE CURSOR statement but the statement identifier is already associated with a declared cursor.

User Response: (1) If the situation involves an SQL statement that cannot be prepared, the problem is in the source of the SQL statement, not the application program. Thus, no action is necessary unless the source of the SQL statement is the application program itself.
(2) If the situation involves an SQL statement that is not supported by Db2, remove it from the application program and recompile it again.
(3) If the situation involves an invalid PREPARE of an ALLOCATE CURSOR statement, change the application program to use a statement identifier that is not associated with a declared cursor.

USQL -101

THE STATEMENT IS TOO LONG OR TOO COMPLEX

Explanation: The statement cannot be executed because it exceeds the system limits for either length or complexity. Enabling parallelism will increase the complexity of the statement.

User Response: Break the statement up into shorter or less complex SQL statements. If parallelism is enabled, try disabling it by (1) using the DEGREE(1) bind option for static SQL or (2) by setting the CURRENT DEGREE special register to “1” for dynamic SQL.

USQL -102

LITERAL STRING IS TOO LONG. STRING BEGINS string

Explanation: The string constant beginning with string has a length greater that 254 characters or 124 graphic characters. Character strings with lengths greater than 254 and graphic strings with lengths greater than 124 can be specified only through careful assignment from host variables. For SET CURRENT PACKAGESET, up to 18 characters are allowed; for SET CURRENT SQLID, up to 8 characters are allowed; for SET CURRENT DEGREE, up to 3 characters are allowed.
Two consecutive string delimiters are used to represent one string delimiter within the character string. These count as two bytes when calculating the lengths of character string constants.

User Response: The requested function is not available interactively. If the error occurred in the context of an SQL statement embedded in an application program, the desired result can be achieved by assigning the long string to a host variable, and substituting that variable for the string literal in the SQL statement.

USQL -103

literal IS AN INVALID NUMERIC LITERAL

Explanation: The indicated “literal” begins with a digit, but is not valid integer, decimal, or float literal.

User Response: Correct the invalid literal.

USQL -104

ILLEGAL SYMBOL “token”. SOME SYMBOLS THAT MIGHT BE LEGAL ARE: token-list

Explanation: A syntax error was detected where the symbol “token” occurs in the SQL statement. The list of symbols that might be legal shows some alternate symbols that could possibly be correct at that point, provided the preceding part of the statement is entirely correct.
However, the preceding part of the statement might be correct. For example, if an important keyword is omitted, Db2 detects the error at a later time, and not always immediately after the point where the keyword should appear. The list of alternate symbols is only meant to be used as a suggestion. Some of those symbols are possibly correct for statements sent to other database management systems.

User Response: Correct the statement and execute it again.

USQL -105

INVALID STRING

Explanation: The statement contains an invalid string. It is neither a character string nor a graphic string.

User Response: Specify the correct format of the string. Check for a graphic string, paired delimiters, the character G or the character N, and an even number of bytes within the string.

USQL -107

THE NAME name IS TOO LONG. MAXIMUM ALLOWABLE SIZE IS size

Explanation: The name is too long. The maximum permissible length for names of that type is indicated by size. Names for the following item cannot contain more than 64 characters:

• Version-id

Names for the following items cannot contain more than 18 characters (20 including SQL escape characters, if present):

• SQL Columns
• SQL Tables
• SQL Views
• SQL indexes
• SQL aliases
• SQL synonyms
• Collection-ids
• Check constraints

Names for the following item cannot contain more than 16 characters:

• Location-name

The following items cannot contain more than 8 characters:

• Table qualifiers
• View qualifiers
• Library member names specified in an INCLUDE statement
• Storage group names
• Database names
• Table space names
• Application plans
• Database request modules (DBRMs)
• Referential constraint names specified in CREATE or ALTER TABLE statements
• Package-id

Host variable names cannot contain more than 64 characters. Volume serial numbers cannot contain more than 6 characters. Labels cannot contain more than 30 characters.

User Response: Choose a shorter name for the object.

USQL -108

THE NAME name IS QUALIFIED INCORRECTLY

Explanation: The name name is improperly qualified. A target name on the RENAME statement may not have a qualifier.

User Response: Remove the qualifier and reissue the statement.

USQL -109

clause CLAUSE IS NOT PERMITTED

Explanation: The indicated clause is not permitted in the context in which it appears in this SQL statement for the following reasons:

• A subselect cannot have an INTO clause.
• A CREATE VIEW statement cannot have INTO, ORDER BY, or FOR UPDATE clauses.
• An embedded SELECT statement cannot have ORDER BY or FOR UPDATE clauses.
• SELECT statements used in cursor declarations cannot have an INTO clause.
• A CREATE TABLESPACE statement cannot specify the LARGE clause without the NUMPARTS option.
• A CREATE TABLESPACE statement cannot specify LOCKPART without the NUMPARTS option.
• An ALTER TABLESPACE statement cannot specify LOCKPART for a non-partitioned table space.
• A table space with LOCKPART YES cannot be altered to LOCKSIZE TABLESPACE.
• A table space with LOCKSIZE TABLESPACE cannot be altered to LOCKPART YES.

User Response: Correct the SQL statement.

USQL -110

INVALID HEXADECIMAL LITERAL BEGINNING string

Explanation: The literal beginning with the specified string contains one or more characters that are not valid hexadecimal digits.

User Response: Correct the invalid literal.

USQL -111

A COLUMN FUNCTION DOES NOT INCLUDE A COLUMN NAME

Explanation: The specification of a column function (AVG, MAX, MIN, or SUM) was invalid because such functions must include a column name in the operand.

User Response: A column name must be specified as an operand to the function. See to the SQL Reference Manual for further information about the proper usage of column functions.

USQL -112

THE OPERAND OF A COLUMN FUNCTION IS ANOTHER COLUMN FUNCTION

Explanation: The operand of a column function can be either an expression or DISTINCT followed by an expression. The operand cannot be another column function.

User Response: Correct the function specification. See to the SQL Reference Manual for further information about the proper usage of column functions.

USQL -113

INVALID CHARACTER FOUND IN string, REASON CODE nnn

Explanation: The string contains an invalid character. It can be an SQL ordinary identifier name, a host variable name, or a Double Byte Character Set (DBCS) comment. For single byte character set SQL ordinary identifiers, names of buffer pools, databases, plans, and storage groups must contain only uppercase alphabetic or national characters and numerics when CHARSET is KATAKANA; the first character must be alphabetic or national. The following reason code applies to DBCS identifiers:

• 000 An invalid character was found in the SBCS identifier.

The following reason codes apply to DBCS identifiers or comments:

• 101 An odd number of bytes exists between the shift-out and the shift-in character.
• 102 Either a shift-in or shift-out character is missing.
• 103 DBCS blanks X”4040” are not allowed.
• 104 There are no characters between the shift-out and the shift-in characters.
• 105 Shift-out cannot be the first byte of the DBCS character between the shift-out and the shift-in characters.

User Response: Correct the name.

USQL -114

THE LOCATION NAME location DOES NOT MATCH THE CURRENT SERVER

Explanation: A 3-part SQL procedure name was provided for one of the following SQL statements:

ASSOCIATE LOCATORS
CALL
DESCRIBE PROCEDURE

The first part of the SQL procedure name, designated to specify the location where the stored procedure resides, did not match the value of the SQL CURRENT SERVER special register.

User Response: Take one of the actions itemized as follows to resolve the mismatch:

• Change the location qualifier to match the CURRENT SERVER special register.
• Issue an SQL CONNECT to the location where the stored procedure resides before issuing the SQL statement. Ensure that the SQL CALL statement is issued before the ASSOCIATE LOCATORS or DESCRIBE PROCEDURE.

USQL -115

A PREDICATE IS INVALID BECAUSE THE COMPARISON OPERATOR operator IS FOLLOWED BY A PARENTHESIZED LIST OR BY ANY OR ALL WITHOUT A SUBQUERY

Explanation: A simple comparison such as: “>” must not be followed by a list of items. ANY and ALL comparisons must be followed by a subselect, rather that an expression or a list of items.

User Response: Correct the SQL statement. See to the SQL Reference Manual for information about the syntax of SQL statements.

USQL -117

THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS

Explanation: The number of insert values in the value list of the INSERT statement is not the same as the number of object columns specified.

User Response: Correct the statement to specify one and only one value for each of the specified object columns.

USQL -118

THE OBJECT TABLE OR VIEW OF THE INSERT, DELETE, OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE

Explanation: The table or view specified as the object of an INSERT, DELETE, or UPDATE statement also appears in the FROM clause of a subselect within the statement. The table or view that is the object 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.

User Response: The implied function is not supported by Db2. It may be possible to obtain the desired result by creating a temporary copy of the object table or view, and addressing the subselect to that copy. See to the SQL Reference Manual for additional information regarding the syntax of SQL statements.

USQL -119

A COLUMN IDENTIFIED IN A HAVING CLAUSE IS NOT INCLUDED IN THE GROUP BY CLAUSE

Explanation: A column identified in a HAVING clause (possibly within a scalar function) does not appear in the GROUP BY clause. Columns specified in a HAVING clause must appear within column functions or also be specified in the GROUP BY clause.

User Response: The implied function is not supported by Db2. See to the SQL Reference Manual for information regarding the proper usage of HAVING and GROUP BY clauses.

USQL -120

A WHERE CLAUSE OR SET CLAUSE INCLUDES A COLUMN FUNCTION

Explanation: A column function is not permitted in a SET clause. A column function is allowed in a WHERE clause only if the WHERE clause appears within a subquery of a HAVING clause.

User Response: The implied function is not supported by Db2. See to the SQL Reference Manual for information regarding restrictions on operands that can be specified within WHERE and SET clauses.

USQL -121

THE COLUMN name IS IDENTIFIED MORE THAN ONCE IN THE INSERT OR UPDATE STATEMENT

Explanation: The same column name is specified more than once, either in the list of object columns of an INSERT statement, or the SET clause of an UPDATE statement.

User Response: Correct the syntax of the statement so that each column name is specified only once.

USQL -122

A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND A COLUMN FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE.

Explanation: The SELECT statement contains one of the following errors:

• The statement contains a column name and a column function in the SELECT clause, but no GROUP BY clause.
• A column name is contained in the SELECT clause (possibly within a scalar function) but not in the GROUP BY clause.

Important

A HAVING clause specified without a GROUP BY clause implies a GROUP BY with no columns. Thus, no column names are allowed in the SELECT clause.

User Response: See to the SQL Reference Manual for information about the use of GROUP BY clauses in SQL statements.

USQL -125

AN INTEGER IN THE ORDER BY CLAUSE DOES NOT IDENTIFY A COLUMN OF THE RESULT

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

User Response: Correct the syntax of the ORDER BY clause so that each column identifier properly denotes a column of the result table.

USQL -126

THE SELECT STATEMENT CONTAINS BOTH AN UPDATE CLAUSE AND AN ORDER BY CLAUSE

Explanation: The SELECT statement in the declaration for a cursor contains both an UPDATE clause and an ORDER BY clause. An ORDER by clause cannot be specified in the declaration for a cursor that is to be used for update.

User Response: The implied function is not supported by Db2. A cursor that is to be used for update cannot be defined to fetch the rows of the object table in a specific order. See to the SQL Reference Manual for information about restrictions on the declarations for cursors to be used for update.

USQL -127

DISTINCT IS SPECIFIED MORE THAN ONCE IN A SUBSELECT

Explanation: The DISTINCT statement qualifier can be used only once in a SELECT statement or a subselect.

User Response: The implied function is not supported by Db2. See to the SQL Reference Manual for information about restrictions on the use of the DISTINCT qualifier.

USQL -128

INVALID USE OF NULL IN A PREDICATE

Explanation: The use of NULL in the search condition does not conform to the rules of SQL syntax.

User Response: The implied function is not supported by Db2. See to the SQL Reference Manual for information about the proper use of the NULL operand.

USQL -129

THE STATEMENT CONTAINS TOO MANY TABLE NAMES

Explanation: A subselect (including all subqueries) can have a maximum of 15 references to table names.

User Response: Break the SQL statement into two or more simpler statements with 15 or fewer table references in each. The count will include the number of base table occurrences from each table or view on the FROM list. See to the SQL Reference Manual for the definition of a subselect.

USQL -130

THE ESCAPE CLAUSE CONSISTS OF MORE THAN ONE CHARACTER, OR THE STRING PATTERN CONTAINS AN INVALID OCCURRENCE OF THE ESCAPE CHARACTER

Explanation: The ESCAPE character must be a single character, either SBCS or DBCS as appropriate. For column-name LIKE pattern, the ESCAPE character can only appear in the character string if it is followed by itself, %, or _ (underscore). The Escape Clause cannot be specified if the column name at the left of the LIKE or NOT LIKE has the mixed subtype.

User Response: Correct the string pattern or choose a different ESCAPE character and change the pattern accordingly. Eliminate the use of the Escape Clause on the LIKE or NOT LIKE predicate where the column name to the left has the MIXED subtype.

USQL -131

STATEMENT WITH LIKE PREDICATE HAS INCOMPATIBLE DATA TYPES

Explanation: If the column name at the left of LIKE or NOT LIKE is of type character, the expression at the right and the ESCAPE character must be of type character. If the column name is of type graphic, the expression at the right and the ESCAPE character must be of type graphic.

User Response: Check the data type of every operand.

USQL -132

A LIKE PREDICATE IS INVALID BECAUSE THE SECOND OPERAND IS NOT A STRING

Explanation: The token following LIKE must be a special register, a string constant, or a host variable with a string value.

User Response: The implied function is not supported by Db2. See to the SQL Reference Manual for information about the proper syntax for LIKE and NOT LIKE predicates.

USQL -133

A COLUMN FUNCTION IN A SUBQUERY OF A HAVING CLAUSE IS INVALID BECAUSE ALL COLUMN REFERENCES IN ITS ARGUMENT ARE NOT CORRELATED TO THE GROUP BY RESULT THAT THE HAVING CLAUSE IS APPLIED TO

Explanation: If a column function has a correlated column reference, it must be correlated from within a HAVING clause to the GROUP BY result that the HAVING clause is applied to. All column references in the argument must satisfy this condition.

User Response: See to the SQL Reference Manual for information about restrictions on the syntax of the HAVING clause.

USQL -134 

IMPROPER USE OF LONG STRING COLUMN column-name OR A HOST VARIABLE OF MAXIMUM LENGTH GREATER THAN 254

Explanation: A long string was referenced in a context in which long strings are not allowed. For an exhaustive list of such contexts, see to the SQL Reference Manual.

User Response: The requested operation on a long string value is not supported by Db2. See to the SQL Reference Manual for information about restrictions on the specification and manipulation of long string values.

USQL -136

SORT CANNOT BE EXECUTED BECAUSE THE SORT KEY LENGTH IS GREATER THAN 4000 BYTES

Explanation: A sort key is derived from the list of columns specified following a DISTINCT qualifier, or in an ORDER BY or GROUP BY clause. If both a DISTINCT qualifier and an ORDER BY or GROUP BY clause are present, the sort key is derived from the combination of both lists of columns.
The internal length of the sort key cannot exceed 4000 bytes. In attempting to process the SQL statement, the internal length of the sort key derived from the DISTINCT and/or ORDER BY or GROUP BY specifications was found to exceed that 4000-byte maximum.

User Response: The statement must be modified so that the internal length of the sort key will not exceed 4000 bytes. In general, this means that one or more column names must be deleted from the ORDER BY or GROUP BY clause, or the list following the DISTINCT qualifier.

USQL -137

 RESULT OF CONCATENATION IS TOO LONG

Explanation: The length of the result of a concatenation exceeds 32,764 (if character operands) or 16,382 (if graphic operands).

User Response: Ensure that the total of the lengths of the concatenated operands is less than or equal to 32,764 (for character operands) or 16,382 (for graphic operands).

USQL -138

THE SECOND OR THIRD ARGUMENT OF THE SUBSTR FUNCTION IS OUT OF RANGE

Explanation: One of the following conditions exists:

• The second argument of the SUBSTR function is less than 1 or greater than M.
• The third argument of the SUBSTR function is an integer constant 0 or an expression whose value is less than 0 or greater than M-N+1.

M is the length of the first argument, provided it is of fixed-length, or M is the maximum length of the first argument, provided it is of varying length. N is the value of the second argument.

User Response: Ensure that the second and third arguments of the SUBSTR function have legal values according to the above rules.

USQL -144

INVALID SECTION NUMBER number

Explanation: One of the following applies:

1. The user attempted to execute an invalid section.

The release of Db2 does not support the SQL statement.

The section number in the call parameter list describes one of the items listed as follows:

1. • Negative
   • An invalid duplicate
   • Greater than the maximum section number of the DBRM or package.

User Response: For case #1: If you are executing a package that was bound with SQLERROR(CONTINUE), determine whether the statement in question was bound as a valid section. You can use the following statements to query the Db2 catalog:

SELECT SQL ERROR
FROM SYSIBM.SYSPACKAGE
WHERE COLLID = collection-id AND NAME = package-id AND VERSION = version-name;

If that query returns “C”, the package was bound with SQLERROR(CONTINUE).

SELECT STMTNO, TEXT
FROM SYSIBM.SYSPACKSTMT
WHERE COLLID = collection-id AND NAME = package-id AND VERSION = version-name AND SECTNO = number AND BINDERROR = "Y";

For case #2: If the Db2 system has fallen back to a previous release, determine whether there are any SQL statements with a section number of zero that are not supported specifically by that release. You can use the following statements to query the Db2 catalog.
When executing from a DBRM, use:

SELECT *
FROM SYSIBM.SYSSTMT
WHERE SECTNO = 0
ORDER BY NAME,PLNAME,STMTNO,SEQNO;

When executing from a package, use:

SELECT *
FROM SYSIBM.SYSPACKSTMT
WHERE SECTNO = 0
ORDER BY COLLID,NAME,VERSION,STMTNO,SEQNO;

For case #3: Examine the application to determine whether the call parameter list was changed in some way. In general, you should not attempt to change the output of the precompiler.

USQL -148

THE SOURCE TABLE source-name CANNOT BE RENAMED BECAUSE IT IS A VIEW OR AN ACTIVE RLST TABLE OR HAS A SYNONYM DEFINED ON IT

Explanation: The RENAME statement cannot be used to rename a view, an active RLST table, or a table for which a synonym is defined.

User Response: Change the source name to the name of an object that can be renamed and reissue the statement.

USQL -150

THE OBJECT OF THE INSERT, DELETE, OR UPDATE STATEMENT IS A VIEW FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED

Explanation: The view named in the INSERT, UPDATE, or DELETE statement is defined in such a way that the requested insert, update, or delete operation cannot be performed upon it.
Inserts into a view are prohibited if:

• The view definition contains a join, a GROUP BY, or a HAVING clause.
• The SELECT clause in the view definition contains the DISTINCT qualifier, an arithmetic expression, a string expression, a built-in function, or a constant.
• Two or more columns of the view are derived from the same column.
• A base table of the view contains a column that does not have a default value and is not included in the view.

Updates to a view are prohibited if:

• The view definition contains a join, a GROUP BY, or a HAVING clause.
• The SELECT clause in the view definition contains the DISTINCT qualifier or a function.

In addition, a given column in a view cannot be updated (i.e., the values in that column cannot be updated) if the column is derived from an arithmetic expression, a constant, a column that is part of the key of a partitioned index, or a column of a catalog table that cannot be updated.
Deletes against a view are prohibited if:

• The view definition contains a join, a GROUP BY, or a HAVING clause.
• The SELECT clause in the view definition contains the DISTINCT qualifier or a built-in function.

User Response: The requested function cannot be performed on the view. See to the SQL Reference Manual for further information regarding inserting, deleting, and updating views.

USQL -151

THE UPDATE STATEMENT IS INVALID BECAUSE THE CATALOG DESCRIPTION OF COLUMN column-name INDICATES THAT IT CANNOT BE UPDATED

Explanation: The specified column cannot be updated for one of the following reasons:

• The object table is partitioned (i.e., resides in a partitioned table space) and the column is included in the partitioning key.
• The object table is a view and the specified column is defined (in the definition of the view) in such a way that it cannot be updated.
• The object table is a catalog table with no columns that can be updated.

The values for columns occurring in the partitioning key of a partitioned table cannot be updated.
Individual columns in a view cannot be updated for one of the following reasons:

• The column is derived from an SQL function, an arithmetic expression, or a constant.
• The column is defined for a column that is in the partitioning key of a partitioned table.
• The column is defined for a column of an underlying view that cannot be updated.

User Response: The requested function is not supported by Db2. See to the description of the UPDATE statement in SQL Reference Manual for information about restrictions on the ability to update columns in partitioned tables and views.

USQL -152

THE DROP clause CLAUSE IN THE ALTER STATEMENT IS INVALID BECAUSE constraint-name IS A constraint-type

Explanation: Either the DROP FOREIGN KEY clause of an ALTER TABLE statement tried to drop a check constraint, or the DROP CHECK clause of an ALTER TABLE statement tried to drop a referential constraint. The clause is either CHECK or FOREIGN KEY. The constraint-type is either REFERENTIAL CONSTRAINT or CHECK CONSTRAINT.

User Response: Drop the existing object with the correct DROP clause of the ALTER TABLE statement.

USQL -153

THE CREATE VIEW STATEMENT DOES NOT INCLUDE A REQUIRED COLUMN LIST

Explanation: You must specify a list of column names if the result table of the subselect has duplicate column names or an unnamed column (a column derived from a constant, function, or expression).

User Response: Correct the statement by providing a list of names for the columns of the view. See to the SQL Reference Manual for information about the syntax of the CREATE VIEW statement.

USQL -154

THE CREATE VIEW FAILED BECAUSE THE VIEW DEFINITION CONTAINS A UNION, A UNION ALL, OR A REMOTE OBJECT

Explanation: The view defined in the CREATE VIEW statement contains a UNION, a UNION ALL, or a remote object. Db2 cannot create views containing unions or a remote object.

User Response: See the SQL Reference Manual for information about restrictions on the definitions for views.

USQL -156

THE STATEMENT DOES NOT IDENTIFY A TABLE

Explanation: The statements ALTER TABLE, DROP TABLE, LOCK TABLE, and CREATE INDEX apply only to tables. Indexes can be defined only on tables.

User Response: Verify that the proper name was specified in the statement.

USQL -157

ONLY A TABLE NAME CAN BE SPECIFIED IN A FOREIGN KEY CLAUSE. object-name IS NOT THE NAME OF A TABLE.

Explanation: The indicated object was identified in a FOREIGN KEY clause or a CREATE or ALTER TABLE statement. A FOREIGN KEY clause must identify a table.

User Response: Correct the statement to specify a table name in the foreign key clause.

USQL -158

THE NUMBER OF COLUMNS SPECIFIED FOR THE VIEW IS NOT THE SAME AS THE NUMBER OF COLUMNS SPECIFIED BY THE SELECT CLAUSE

Explanation: The number of column names specified for a view in a CREATE VIEW statement must equal the number of elements (column names, SQL functions, expressions, etc.) specified in the following AS SELECT clause.

User Response: Correct the syntax of the statement to specify a column name for each column in the view to be created. See to the SQL Reference Manual for information about the syntax of the CREATE VIEW statement.

USQL -159

DROP OR COMMENT ON token IDENTIFIES A(N) token RATHER THAN A(N) token

Explanation: The DROP VIEW statement can have only a view as its object. The DROP ALIAS or COMMENT ON ALIAS statement can have only an alias as its object. You must use the DROP TABLE statement to drop a table that is neither a view nor an alias. You must use the COMMENT ON TABLE statement to comment on a table or view.

User Response: Correct the DROP VIEW, DROP ALIAS, or COMMENT ON ALIAS statement so that the view name or the alias name is specified correctly (with the proper qualifier). If you intended to drop or comment on the specified table, use the DROP TABLE or COMMENT ON TABLE statement.

USQL -160

THE WITH CHECK OPTION CANNOT BE USED FOR THE SPECIFIED VIEW

Explanation: The WITH CHECK OPTION does not apply to a view definition under either of the following circumstances:

• The view is read-only (for example, the view definition includes DISTINCT GROUP BY, or JOIN).
• The view definition includes a subquery.

User Response: See to the SQL Reference Manual for rules regarding use of the WITH CHECK OPTION in view definitions.

USQL -161

THE INSERT OR UPDATE IS NOT ALLOWED BECAUSE A RESULTING ROW DOES NOT SATISFY THE VIEW DEFINITION

Explanation: The WITH CHECK OPTION applies to the view that is the object of the INSERT or UPDATE statement. Consequently, all attempts to insert or update rows in that view are checked to ensure that all results will conform to the view definition.

User Response: Examine the view definition to determine why the requested INSERT or UPDATE was rejected. Note that this may be a data-dependent condition.

USQL -164

auth-id1 DOES NOT HAVE THE PRIVILEGE TO CREATE A VIEW WITH QUALIFICATION authorization ID

Explanation: The authorization ID auth-id does not have its own authorization ID. Specifically, the attempt to create a view with a qualifier authorization-ID is rejected.

User Response: Do not attempt to create views with other than your own ID as a qualifier. Only an authorization ID that holds SYSADM authority can create views for other authorization IDs.

USQL -170

THE NUMBER OF ARGUMENTS SPECIFIED FOR function-name IS INVALID

Explanation: An SQL statement includes the scalar function function-name with either too many arguments or too few arguments.

User Response: Correct the statement. See to the SQL Reference Manual for information about the number of arguments required by the scalar function function-name.

USQL -171

THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT nn OF function-name IS INVALID

Explanation: Either the data type, the length, or the value of argument nn of scalar function function-name is incorrect.

User Response: Correct the statement. See to the SQL Reference Manual regarding rules for each argument of the scalar function function-name.

USQL -173

UR IS SPECIFIED ON THE WITH CLAUSE BUT THE CURSOR IS NOT READ-ONLY

Explanation: The cursor is not a read-only cursor. WITH UR can be specified only if Db2 can determine that the cursor is read-only.

User Response: If the cursor is intended to be read-only but is ambiguous, add the FOR FETCH ONLY clause. If the cursor is updateable, change the isolation level specified on the WITH clause.

USQL -180

THE DATE, TIME, OR TIMESTAMP VALUE value IS INVALID

Explanation: The length or string representation of a DATE, TIME, or TIMESTAMP value does not conform to any valid format.
The value can contain one of the following:

• For a host variable, the position number of the input host variable. If the position number cannot be determined, a blank is displayed.
• For a character string constant, the character string constant. The maximum length that is displayed is the length of SQLERRM.
• For a character column, the column name. If the column is a VIEW column and it has a corresponding base column, the VIEW column name is displayed. If the column is a VIEW column but it does not have a corresponding base column, a string of *N is displayed.

Otherwise, value is a string of *N.

User Response: Correct the program to ensure the specified value conforms to the syntax of DATE, TIME, and TIMESTAMP. See to the SQL Reference Manual for a list of valid DATE and TIME formats.

USQL -181

THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE

Explanation: The string representation of a datetime is not in the acceptable range or is not in the correct format. The proper ranges for datetime values are as follows:

DATETIME NUMERIC RANGE
Years 0001 to 9999
Months 1 to 12
Days April, June, 1 to 30 September, November 1 to 30 (months 4,6,9,11) 1 to 30 February (month 2) 1 to 28 (Leap Year is 1 to 29) January, March, 1 to 31 May, July, August 1 to 31 October, December 1 to 31 (months 1,3,5,7, 1 to 31 8,10,12) 1 to 31
Hours 0 to 24 (If the hour is 24, other parts of time values are zeroes. If the hour is USA, maximum hour is 12.)
Minutes 0 to 59
Seconds 0 to 59
Microseconds 0 to 999999

User Response: Check whether the value is within the valid range and is in the proper format. See to the SQL Reference Manual for information on string data formats.

USQL -182

AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE IS INVALID

Explanation: The specified arithmetic expression contains an improperly used datetime value or labeled duration.

User Response: Correct the indicated arithmetic expression.

USQL -183

AN ARITHMETIC OPERATION ON A DATE OR TIMESTAMP HAS A RESULT THAT IS NOT WITHIN THE VALID RANGE OF DATES

Explanation: The result of an arithmetic operation is a date or timestamp that is not within the valid range of dates which are between 0001-01-01 and 9999-12-31.

User Response: Examine the SQL statement to see if the cause of the problem can be determined. The problem may be data-dependent, in which case it will be necessary to examine the data that was processed at the time the error occurred.

USQL -184

AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE CONTAINS A PARAMETER MARKER

Explanation: The specified arithmetic expression contains a parameter marker improperly used with a datetime value.

User Response: Correct the indicated arithmetic expression.

USQL -185

THE LOCAL FORMAT OPTION HAS BEEN USED WITH A DATE OR TIME AND NO LOCAL EXIT HAS BEEN INSTALLED

Explanation: The local format option has been used with a datetime value and no datetime exit has been installed. This may occur if the LOCAL DATE LENGTH or LOCAL TIME LENGTH on the Installation Application Programming Defaults Panel indicated that an exit for datetime was supplied, but in fact, the exit supplied by Db2 was not replaced. This may also occur if the datetime exit was replaced and the corresponding LOCAL DATE LENGTH or LOCAL TIME LENGTH on the Installation Application Programming Defaults Panel was not set to a non-zero value.

User Response: Contact the system programmer regarding installation of the date or time exit.

USQL -186

THE LOCAL DATE LENGTH OR LOCAL TIME LENGTH HAS BEEN INCREASED AND THE EXECUTING PROGRAM RELIES ON THE OLD LENGTH

Explanation: The local format option has been used with a datetime value and Db2 has discovered that the datetime exit routine has been changed to produce a longer local format.

User Response: If the statement receiving this error is embedded in the application program, a REBIND command must then be issued for the application plan. If the statement was dynamic SQL, the statement can then be re-entered.

USQL -187

A REFERENCE TO A CURRENT DATE/TIME SPECIAL REGISTER IS INVALID BECAUSE THE MVS TOD CLOCK IS BAD OR THE MVS PARMTZ IS OUT OF RANGE

Explanation: Db2 has encountered an invalid time-of-day (TOD) clock. The user referenced one of the special registers: (1) CURRENT DATE, (2) CURRENT TIME, (3) CURRENT TIMESTAMP, or (4) CURRENT TIMEZONE. If the user referenced CURRENT TIMEZONE, the MVS parameter PARMTZ was out of range.

User Response: For CURRENT TIMEZONE, check that the MVS parameter PARMTZ is between -24 and +24 hours. For the other CURRENT special registers, check that the MVS TOD clock has been set correctly.

USQL -188

THE STRING REPRESENTATION OF A NAME IS INVALID

Explanation: The host variable referenced in the DESCRIBE statement does not contain a valid string representation of a name. One of the following error conditions has occurred:

• The first byte of the variable is a period or a blank.
• The number of identifiers is greater than 3.
• An identifier is too long.
• A period not contained in a delimited identifier is followed by a period or a blank.
• A delimited identifier is followed by a character other than a period or a blank.
• A delimited identifier is not terminated by a quotation mark.

User Response: Correct the value of the host variable so that it is a valid string representation of a name.

USQL -189

CCSID ccsid IS UNKNOWN OR INVALID FOR THE DATA TYPE OR SUBTYPE

Explanation: To determine the subtype of an input host variable or result column, the SYSSTRINGS catalog table was accessed with the specified CCSID and:

• The CCSID is not a value of either INCCSID or OUTCCSID,
  or
• The TRANSTYPE column classifies the CCSID as GRAPHIC rather than CHARACTER.

This error can occur when SYSSTRINGS is accessed with a pair of CCSIDs to determine if a translation is defined for the pair. In this case, the error is the inconsistency between the data type of a string and the TRANSTYPE classification of its CCSID (one is GRAPHIC and the other is CHARACTER).

This error can also occur when a CCSID specified in DECP does not exist as a value in the INCCSID or OUTCCSID columns of SYSSTRINGS.

User Response: Ensure that the CCSID is valid and consistent with the data type of the string. If a valid CCSID is not listed in a built-in row of SYSSTRINGS, it can be defined by inserting a user-provided row. If a valid CCSID is misclassified in a user-provided row, that row can be updated to correct the mistake. See to the appendices of your SQL Installation Guide for more information on CCSIDs and to the SQL Reference Manual for more information regarding the SYSSTRINGS catalog table.

USQL -191

A STRING CANNOT BE USED BECAUSE IT IS INVALID MIXED DATA

Explanation: The operation required the translation of a mixed data character string to a different coded character set. The string could not be translated because it does not conform to the rules for well-formed mixed data. For example, the string does contains EBCDIC shift codes that are not properly paired.

User Response: If the string contains the intended information, the description of the column or host variable should be changed from MIXED DATA to BIT or SBCS DATA. If the description of the column or host variable is correct, the string is the problem and it must be changed to conform to the rules for the well-formed mixed data. For additional information regarding the well-formed MIXED DATA, see to the SQL Reference Manual.

USQL -197

QUALIFIED COLUMN NAMES IN ORDER BY CLAUSE NOT PERMITTED WHEN UNION OR UNION ALL IS SPECIFIED

Explanation: A SELECT statement that specified both the union of two or more tables and the ORDER BY clause cannot use qualified column names in the ORDER BY clause.

User Response: The statement is not executed.

USQL -198

THE OPERAND OF THE PREPARE OR EXECUTE IMMEDIATE STATEMENT IS BLANK OR EMPTY

Explanation: The operand (host variable or literal string) that was the object of the PREPARE or EXECUTE IMMEDIATE statement either contained all blanks or was an empty string. A DBRM built in Version 2, Release 3 cannot be used on a Version 2, Release 2 if the distributive functions were used. If this error appears on Version 2, Release 2 and the DBRM was built on Version 2, Release 3, the program needs to be precompiled again to correct the problem.

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

USQL -199

ILLEGAL USE OF KEYWORD keyword. TOKEN token-list WAS EXPECTED

Explanation: A syntax error was detected in the statement at the point where the keyword keyword appears. As an aid to the programmer, a partial list of valid tokens is provided in SQLERRM as token-list. Only those tokens that will fit are listed. Some tokens in the list might not be valid in statements to be executed by Db2; those tokens are valid for sending to other database management systems.

User Response: Examine the statement in the area of the indicated keyword. A colon or an SQL delimited might be missing.

USQL -203

A REFERENCE TO COLUMN column-name is AMBIGUOUS

Explanation: An unqualified column name is ambiguous if more than one table or view identified in the FROM clause has a column with that name, or if more than one column of a nested table expression has that name.
A qualified column name is ambiguous only if the qualifier is the correlation name for a nested table expression and the column name is not unique.

User Response: If the problem is caused by a non-unique column name in a nested table expression, you must change the nested table expression so that the column name is unique. If the problem is caused by the use of an unqualified name, qualify it with a table, view, or correlation name.

USQL -204

name IS AN UNDEFINED NAME

Explanation: The object identified by name is not defined in the Db2 subsystem. This SQLCODE can be generated for any type of Db2 object.

User Response: Verify that the object name was correctly specified in the SQL statement, including any required qualifiers. If it is correct, ensure that the object exists in the system before resubmitting the statement.
If the specified object is a stored procedure, add a row to the SYSIBM.SYSPROCEDURES catalog table to define the stored procedure. Issue the -START PROCEDURE command to activate the new definition.

USQL -205

column-name IS NOT A COLUMN OF TABLE table-name

Explanation: No column with the specified column-name occurs in the table or view table-name.

User Response: Verify that the column and table names are specified correctly (including any required qualifiers) in the SQL statement.

USQL -206

column-name IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE

Explanation: This return code is utilized to report one of the following errors:

• • In the case of 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.
  • In the case of 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.
  • There is a correlated reference in GROUP BY.
  • There is an unresolved qualified reference in HAVING.

User Response: Verify that the column and table names are specified correctly in the SQL statement. In the case of a SELECT statement, check to be sure that all of the required tables were named in the FROM clause.

USQL -208

THE ORDER BY CLAUSE IS INVALID BECAUSE COLUMN name IS NOT PART OF THE RESULT TABLE

Explanation: The statement is invalid because a column (name) specified in the ORDER BY list does not appear in the result table (that is, it is not specified by the SELECT-list). Only columns that are to appear in the result table can be used in ordering that result.

User Response: Correct the syntax of the statement, either by adding the specified column to the result table, or deleting it from the ORDER BY clause. See the SQL Reference Manual for information regarding restrictions on the use of the ORDER BY clause to order the result of an SQL SELECT.

USQL -219

THE REQUIRED EXPLANATION TABLE table-name DOES NOT EXIST

Explanation: The EXPLAIN statement assumes the existence of the explanation table and it is not defined in the Db2 subsystem as a base table. See to the SQL Reference Manual for more information.

User Response: Determine whether the required explanation table does exist. If not, create the required table.

USQL -220

THE COLUMN column-name IN EXPLANATION TABLE table-name IS NOT DEFINED PROPELY

Explanation: An error occurred during the insertion of a row into the explanation table. The table is improperly defined for the following reasons:

• A column is missing.
• Columns are defined in the wrong order.
• The table contains an extra column.
• A column description is invalid because of its name, data type, length, or null attributes.

User Response: Correct the definition of the required explanation table. See to the SQL Reference Manual for information on defining an explanation table.

USQL -221

“SET OF OPTIONAL COLUMNS” IN EXPLANATION TABLE table-name IS INCOMPLETE. OPTIONAL COLUMN column-name IS MISSING.

Explanation: The EXPLAIN statement assumes the required explanation table is defined properly. The optional column indicated is not defined in the indicated explanation table. PLAN-TABLE can have none or all of the Version 2 Release 2 optional columns (PREFETCH, COLUMN-FN-EVAL, or MIXOPSEQ. If the Version 2 Release 2 optional columns are present, it can also have all or none of the optional Version 2 Release 2 columns (VERSION,COLLID).

User Response: Correct the definition of the required explanation table to include all five optional columns, just the Version 2 Release 2 optional columns, or no optional columns whatsoever. See to the SQL Reference Manual for information on defining an explanation table.

USQL -240

THE PART CLAUSE OF A LOCK TABLE STATEMENT IS INVALID

Explanation: The LOCK TABLE statement has become invalid for one of the following reasons:

• The table space in which the table resides is not partitioned or does not have the LOCKPART YES attribute, and the PART clause is specified.
• An integer specified in the PART clause does not identify a partition of the table space.

User Response: Determine whether the specified table resides in a partitioned table space defined with LOCKPART YES.

• If it is partitioned and defined with LOCKPART YES, specify a PART clause that identifies the partition you want to lock.
• If it is partitioned but does not have the LOCKPART YES attribute and you want to lock a single partition, use ALTER TABLESPACE to change the LOCKPART attribute to YES.
• If it is not partitioned, do not specify the PART clause.

USQL -250

THE LOCAL LOCATION NAME IS NOT DEFINED WHEN PROCESSING A THREE-PART OBJECT NAME

Explanation: A three-part object name (table, view, or alias) cannot be used until the local location name is defined.

User Response: Define the local location name and then retry the function.

USQL -251

TOKEN name IS NOT VALID

Explanation: A location name cannot contain alphabetic characters. (The standard alphabetic extenders in the United States are #, @, and $.)

User Response: Correct the location name.

USQL -300

THE STRING CONTAINED IN HOST VARIABLE OR PARAMETER position-number IS NOT NUL-TERMINATED

Explanation: A host variable or parameter is invalid. Its entry in the SQLDA is indicated by position-number. The host variable or parameter is a C string variable that is one of the following:

• Used as an input parameter to a stored procedure.
• Returned as an output parameter from a stored procedure.
• Referenced as an input variable in an embedded SQL statement.
• Used to provide a value for a parameter marker of a dynamic SQL statement.

If the data type of the variable is character string, it is invalid because it does not include X”00”. If the data type of the variable is graphic string, it is invalid because it does not include X”0000”.

User Response: Append a NUL-terminator to the end of the string.

USQL -301

THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMBER position-number CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE

Explanation: Db2 received data that could not be used as specified in the statement because its data type is incompatible with the requested function.
The position-number identifies either the host variable (if the message is issued as a result of an INSERT, UPDATE, DELETE, or SELECT statement), or the parameter number (if the message is issued as the result of a CALL statement).

User Response: Correct the application program or stored procedure. Ensure that the data type of the indicated input host variable or parameter in the statement is compatible with the way it is used.

USQL -302

THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE

Explanation: Db2 received data that was invalid or too large to fit in the corresponding column of the table or the corresponding target value. The position-number identifies either the host variable number (provided the message is issued as a result of an INSERT, UPDATE, DELETE, or SELECT statement), or the parameter number (if the message is issued as the result of a CALL statement).
One of the following occurred:

• The column is defined as a string and the host variable or parameter contains a string that is too long for the column.
• The column is defined as numeric and the host variable or parameter contains a numeric value too large for the definition of the column.
• The host variable is defined as decimal, but contains invalid decimal data.
• The target value is a string constant and the host variable or parameter contains a string that is too large for the target value.
• The target value is a numeric constant and the host variable or parameter contains a numeric value that is too large for the target value.

User Response: Correct the application program or stored procedure. Check the column type and length of the value or the data type and contents of the input host variable or parameter position-number. Ensure that the value of the host variable or parameter will fit in the column or contains valid decimal data. Valid decimal data is a System/370 packed decimal number.

USQL -303

A VALUE CANNOT BE ASSIGNED TO AN OUTPUT HOST VARIABLE NUMBER position-number BECAUSE THE DATA TYPES ARE NOT COMPARABLE.

Explanation: A CALL, FETCH, or SELECT into the output host variable, whose entry in the output SQLDA is indicated by position-number, could not be performed. The data type of the variable was not compatible with the data type of the corresponding SELECT or CALL list element. The output host variable and the corresponding SELECT or CALL list element must be in one of the following categories:

• Both values must be numbers.
• Both values must be character strings.
• Both values must be graphic strings.

In addition, for both datetime and timestamp values, the host variable must be a character string variable with a correct length.

User Response: Verify that the table definitions are current and that the host variable has the correct data type.

USQL -304

A VALUE WITH DATA TYPE data-type1 CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION position-number WITH DATA TYPE data-type2

Explanation: A CALL, FETCH, or SELECT into a host variable list or structure in position number position-number failed because the host variable with data type data-type2 was not large enough to hold the retrieved value with data type data-type1.

User Response: Verify that the table definitions are current. Verify that the host variable has the correct data type. See to the explanation for SQL CODE SQL0405 for ranges of SQL data types.

USQL -305

THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE NO INDICATOR VARIABLE IS SPECIFIED

Explanation: A FETCH or embedded SELECT operation resulted in the retrieval of a null value to be inserted into the output host variable, designated by an entry number position-number of the output SQLDA, for which no indicator variable was provided. An indicator variable must be supplied if a column returns a null value.

User Response: Examine the definition of the table that is the object of the FETCH or SELECT, and correct the application program to then provide indicator variables for all host variables into which null values can be retrieved. This includes host variables for columns which can contain null values and host variables which receive the results of column functions whose result table could be empty.

USQL -309

A PREDICATE IS INVALID BECAUSE A REFERENCED HOST VARIABLE HAS THE NULL VALUE

Explanation: The statement could not be processed because a host variable appearing in a predicate such as:
column-name = host variable

had the NULL value. Such a predicate is not permitted when the host variable contains the NULL value even though the object column might contain nulls.

User Response: Rebind the plan or package containing the statement. The condition described is not an error in Db2 Version 2 Release 3 and later releases.

USQL -310

DECIMAL HOST VARIABLE OR PARAMETER number CONTAINS NON-DECIMAL DATA

Explanation: Db2 received non-decimal data from either an application (in the form of a host variable) or a stored procedure (in the form of a parameter that was passed to or from a stored procedure). The following sentence is the definition of number:

Identifies either the host variable number (if the message is issued
as a result of an INSERT, UPDATE, DELETE, or SELECT statement), or
the parameter number (provided the message is issued as the result of
a CALL statement).

User Response: Correct the application program or stored procedure. Ensure that all decimal variables or parameters contain valid System/370 packed decimal numbers.

USQL -311

THE LENGTH OF THE INPUT HOST VARIABLE NUMBER position-number IS NEGATIVE OR GREATER THAN THE MAXIMUM

Explanation: When evaluated, the length specification for input host string variable, whose entry in the SQLDA is indicated by position-number, was negative or greater than the maximum.

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

USQL -312

UNDEFINED OR UNUSABLE HOST VARIABLE variable-name

Explanation: The host variable variable-name appears in the SQL statement, but:

• The SQL statement is a prepared statement,
  or
• The attributes of the variable are inconsistent with its usage in the static SQL statement,
  or
• The variable is not declared in the application program.

User Response: Verify that the following items have been completed:

• The variable name is spelled properly in the SQL statement.
• The variable is allowed in the SQL statement.
• The application program contains a declaration for that variable.
• The attributes of the variable are compatible with its use in the statement.

USQL -313

THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS

Explanation: The number of host variables specified in the EXECUTE or OPEN statement is not the same as the number of parameter markers (question marks) appearing in the prepared SQL statement.

User Response: Correct the application program so that the number of host variables specified in the EXECUTE or OPEN statement is the same as the number of parameter markers appearing in the prepared SQL statement.

USQL -314

THE STATEMENT CONTAINS AN AMBIGUOUS HOST VARIABLE REFERENCE

Explanation: A host variable used in the statement has been defined more than once in this application program causing confusion as to which host variable definition should be used.

User Response: Make the host variable unique or use qualifications to indicate which host variable definition is to be used.

USQL -327

THE ROW CANNOT BE INSERTED BECAUSE IT IS OUTSIDE THE BOUND OF THE PARTITION RANGE FOR THE LAST PARTITION

Explanation: When a row is inserted, the calculated partition key value for the new row must be within the bounds of a partition (as specified in the VALUES clause of the CREATE INDEX statement).

User Response: Correct the statement to specify a value for the partition key that is within the bounds of the last partition of the partitioned table space.

USQL -330

A STRING CANNOT BE USED BECAUSE IT CANNOT BE TRANSLATED. REASON reason-code, CHARACTER code-point, HOST VARIABLE position-number

Explanation: A translation error occurred during the translation of a string to a different coded character set. The type of error is indicated by the reason-code:

8 Length exception (for example, expansion required for PC MIXED data exceeds the maximum length of the string).
12 Invalid code point (for example, use of the ERRORBYTE option of SYSSTRINGS).
16 Form exception (for example, invalid MIXED data).
20 Translate procedure error (for example, an exit set the length control field of the string to an invalid value).
24 SBCS character was found in a string contained in a wchar_t host variable.

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

User Response: Take one of the following actions based on the reason-code:

• If the reason-code is 8, extend the maximum length of the host variable to allow for expansion that occurs when the string is translated.
• If the reason-code is 12, change the translate table to accept the code-point or change the data to eliminate the code-point.
• If the reason-code is 16 and the string is described as MIXED data, change its description or change the string to conform to the rules for well-formed mixed data.
• If the reason-code is 20, correct the translate procedure.
• If the reason-code is 24, delete the SBCS character from the graphic string.

USQL -331

A STRING CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE IT CANNOT BE TRANSLATED. REASON reason-code, CHARACTER code-point, POSITION position-number

Explanation: The operation required the translation of a string to the coded character set of the host variable and a translation error occurred. The type of error (noted as follows) is indicated by the reason-code:

• 8 for length exception (e.g., expansion required for PC MIXED data exceeds the maximum length of the string).
• 12 for invalid code-point (e.g., use of the ERRORBYTE option of SYSSTRINGS).
• 16 for form exception (e.g., invalid MIXED data).
• 20 for translate procedure error (e.g., an exit set the length of the control field of the string to an invalid value).

If the reason-code is 12, code-point is the invalid “code point”. Otherwise, code-point is blank. The position-number is the ordinality of the output variable in the SQLDA.

User Response: If the reason-code is 8, the maximum length of the result column must be extended to allow for the expansion that occurs when the string is translated. If the reason-code is 12, the translate table must be changed to accept the code-point or the data must be changed to eliminate the code point. If the reason-code is 16, and the string is described as MIXED data, its description must be changed or the string must be changed to conform to the rules for well-formed MIXED data. If the reason-code is 20, the translate procedure must be corrected. An alternative to these corrective actions is to provide an indicator variable so that a null value and a warning can be returned rather than an error. See to the SQL Reference Manual for additional information on a coded character set.

USQL -332

SYSSTRINGS DOES NOT DEFINE A TRANSLATION FROM CCSID ccsid TO ccsid

Explanation: The operation required a translation of a string to a different coded character set, but the specific translation is not described in the SYSSTRINGS catalog table. The first ccsid identifies the coded character set of the string and the second ccsid identifies the coded character set to which it must be translated.

User Response: If the CCSIDs are correct, the only corrective action is to provide for the translation by inserting a row into SYSSTRINGS. For an SBCS translation, the row must contain a translate table or the name of a user-provided translate procedure. For a DBCS translation, the row must contain the name of a user-provided translate procedure. See the SQL Reference Manual for additional information on a coded character set.

USQL -333

THE SUBTYPE OF A STRING VARIABLE IS NOT THE SAME AS THE SUBTYPE KNOWN AT BIND TIME, AND THE DIFFERENCE CANNOT BE RESOLVED BY TRANSLATION

Explanation: The CCSID in the run time SQLDA is inconsistent with the bind time subtype of the host variable or parameter marker. Either the run time description is BIT and the bind time description was not BIT, or the run time description is not BIT and the bind time description was BIT.

User Response: Change the CCSID in the SQLDA so that the subtype of the host variable is consistent with the bind time subtype of the host variable or parameter marker. See the SQL Reference Manual for additional information on a coded character set.

USQL -338

AN ON CLAUSE IS INVALID

Explanation: This return code reports a violation of one of the following:

• One expression of the predicate must only reference columns of one of the operand tables of the associated join operator, and the other expression of the predicate must only reference columns of the other operand table.
• A VALUE or COALESCE function is allowed in the ON clause only when the join operator is a FULL OUTER JOIN or FULL JOIN.
• An operator other than = is not allowed in a FULL OUTER JOIN or FULL JOIN.

User Response: Correct the syntax of the statement so that each ON clause expression references only one of the operand tables. You may work around the second restriction by using a nested table expression with the VALUE/COALESCE function in the SELECT list of the table expression.

USQL -339

THE SQL STATEMENT CANNOT BE EXECUTED FROM AN ASCII BASED DRDA APPLICATION REQUESTOR TO A V2R2 DB2 SUBSYSTEM

Explanation: The application is connected to a Db2 Version 2 Release 3 database server. The SQL statement is using an alias or three-part name, which refers to another Db2 subsystem that is at the Version 2 Release 2 level. Db2 Version 2 Release 2 does not support character conversion. Since the execution of SQL statements from an ASCII DRDA requester to an EBCDIC Version 2 Release 2 Db2 server could require character conversion, access to the Version 2 Release 2 Db2 is denied for data integrity reasons.

User Response: Remove statements from the application that resolve to a Db2 Version 2 Release 2 subsystem.

Important

If the application must refer to the Version 2 Release 2 subsystem data, the Version 2 Release 2 Db2 subsystem must be migrated to Version 2 Release 3 where character conversion is supported.

USQL -351

AN UNSUPPORTED SQLTYPE WAS ENCOUNTERED IN POSITION “<position-number>” ON A PREPARE OR DESCRIBE OPERATION

Explanation: Some SQL data types are not supported by Db2 Version 5. “<position-number>” is the position of the first element with an invalid data type in the SQLDA. A common reason why this error occurs is when Db2 attempts to describe large object data residing at a non-Db2 server. Some of the SQLTYPEs that can cause this error are: LOB, BLOB, CLOB and DBLOB.

User Response: The application program must not include any SQL operations that describe SQL data types which Db2 Version 5 does not support when Db2 is the application requester (AR). For example, instead of dynamically selecting all columns from SYSTABLES, select only those columns that have data types defined to Db2. If the error is caused by an unsupported LOB type, exclude such data from the list of data to be described.

USQL -400

THE CATALOG HAS THE MAXIMUM NUMBER OF USER DEFINED INDEXES

Explanation: Only one hundred user-defined indexes can be created in the catalog database.

User Response: If this index must be created, another user-defined index on the catalog must be dropped. After that index has been dropped, this statement can be executed.

USQL -401

THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE

Explanation: An arithmetic operation appearing within the SQL statement has a mixture of numeric and non-numeric operands, or the operands of a comparison operation are not compatible.

User Response: Check the data types of all operands to ensure that their data types are comparable and compatible with their usage in the statement.
If all the operands of the SQL statement are correct, and if a view is then being accessed, check the data types of all the operands in the view definition.

USQL -402

AN ARITHMETIC FUNCTION OR OPERATOR arith-fop IS APPLIED TO CHARACTER OR DATETIME DATA

Explanation: A non-numeric operand has been specified for the arithmetic function or operator “arith-fop”.

User Response: Examine and correct the syntax of the SQL statement such that all operands of the specified function or operator are numeric.

USQL -404

THE UPDATE OR INSERT STATEMENT SPECIFIES A STRING THAT IS TOO LONG column-name

Explanation: An INSERT or UPDATE statement specifies a value that is longer than the maximum-length string that can be stored in the indicated column.

User Response: Check the length of the object column and correct the program or SQL statement so that the length of the insert or update string does not exceed that maximum.

USQL -405

THE NUMERIC LITERAL literal CANNOT BE USED AS SPECIFIED BECAUSE IT IS OUT OF RANGE

Explanation: The specified numeric literal is not in the proper range. The proper ranges for SQL values are displayed as follows:

• 5.4E-79 to 7.2E+75 for FLOAT values
• -(10{31} -1) to + (10{31} -1) for DECIMAL values
• -2147483648 to 2147483647 for INTEGER values
• -32768 to +32767 for small integer (SMALLINT) values.

User Response: The value of the literal should be reduced to the appropriate size for this data type.

USQL -406

A CALCULATED OR DERIVED NUMERIC VALUE IS NOT WITHIN THE RANGE OF ITS OBJECT COLUMN

Explanation: A value derived or calculated during the processing of the SQL statement was outside the range of the data type of its object column. This problem might have arisen because the values occurring in the object column were out of range, or because the SQL operation performed was not appropriate for the values in the object column.

User Response: See the explanation of SQLCODE SQL0405 for allowed ranges for all numeric data types.

USQL -407

AN UPDATE OR INSERT VALUE IS NULL, BUT THE OBJECT COLUMN column-name CANNOT CONTAIN NULL VALUES

Explanation: One of the following conditions has occurred:

• A null insert or update value was specified for a column defined as NOT NULL.
• No insert value was provided for a column that does not have a default value.

User Response: Examine the definition of the object table to determine which columns of the table have the NOT NULL attribute, and then correct the SQL statement accordingly.

USQL -408

AN UPDATE OR INSERT VALUE IS NOT COMPARABLE WITH THE DATA TYPE OF ITS OBJECT COLUMN column-name

Explanation: The data type of the value to be inserted or set in the column “column-name” by an INSERT or UPDATE statement is incompatible with the declared data type of that column. Both must be numeric or both must be graphic string; or both must be either:

• Dates or character
• Times or character
• Timestamps or character.

However, dates, times, or timestamps cannot be assigned to a specific character column that has a field procedure.

User Response: Examine the current definition for the object table and ensure that the host variable or literal value assigned to the specified column has the proper data type.

USQL -409

INVALID OPERAND OF A COUNT FUNCTION

Explanation: The operand of the COUNT function in the statement violates SQL syntax. A common error is a column name or other expression without DISTINCT.

User Response: Correct the syntax and resubmit the statement. See to the SQL Reference Manual for information about the proper form for the operands of a COUNT function.

USQL -410

THE FLOATING POINT LITERAL literal CONTAINS MORE THAN 30 CHARACTERS

Explanation: The specified floating-point literal is more than 30 characters in length. By definition, a floating-point literal has a maximum length of 30 characters.

User Response: Correct the indicated literal.

USQL -411

CURRENT SQLID CANNOT BE USED IN A STATEMENT THAT REFERENCES REMOTE OBJECTS

Explanation: A reference to the CURRENT SQLID special register is invalid in a statement that contains the three-part name or alias of an object that is remote to the remote server.

User Response: Remove the reference to CURRENT SQLID or remove the reference to the remote object.

USQL -412

THE SELECT CLAUSE OF A SUBQUERY SPECIFIES MULTIPLE COLUMNS

Explanation: In the context in which it was used in the SQL statement, the subquery can have only one column specified in its SELECT clause.

User Response: Correct the syntax of the SQL statement. See to the SQL Reference Manual for information about restrictions on the syntax for subqueries.

USQL -413

OVERFLOW OCCURRED DURING NUMERIC DATA TYPE CONVERSION

Explanation: During the processing of an SQL statement, an overflow condition arose when converting from one numeric type to another. Numeric conversion is performed according to the standard rules of SQL.

User Response: Examine the syntax of the SQL statement to determine the cause of the error. If the problem is data-dependent, it may be necessary to examine the data processed at the time of the error.

USQL -414

A LIKE PREDICATE IS INVALID BECAUSE THE FIRST OPERAND IS NOT A STRING

Explanation: The data type of the first operand of the LIKE predicate must be a character string or graphic string.

User Response: Respecify the predicate so that the data type of each operand is a character string or a graphic string.

USQL -415

THE CORRESPONDING COLUMNS, column-number, OF THE OPERANDS OF A UNION OR A UNION ALL DO NOT HAVE COMPARABLE COLUMN DESCRIPTIONS

Explanation: The column descriptions of corresponding columns of the operands of a UNION or UNION ALL must be comparable. The columns of ordinality “column-number” of the operands in this UNION or UNION ALL do not satisfy this requirement. For columns to be comparable, they must both be either numeric, character, graphic, date, time, or timestamp. They cannot be a mixture of these groups. If corresponding columns have field procedures, they must both have the same field procedure.

User Response: Check the data types of the specified columns and correct the UNION or UNION ALL statement so that all corresponding columns have comparable column descriptions.

USQL -416

AN OPERAND OF A UNION CONTAINS A LONG STRING COLUMN

Explanation: The UNION specified in the SQL statement could not be performed because one of the tables participating in the union contains a long string column (for example, a VARCHAR column with length greater than 254). The operands of a UNION cannot contain long string columns.

User Response: The implied function is not supported by Db2. See to the SQL Reference Manual for information about restrictions on the manipulation of long string columns.

USQL -417

A STATEMENT STRING TO BE PREPARED INCLUDES PARAMETER MARKERS AS THE OPERANDS OF THE SAME OPERATOR

Explanation: The statement string specified as the object of a PREPARE contains a predicate or expression where parameter markers have been used as operands of the same operator. For example:

This syntax is not permitted.

User Response: Correct the logic of the application program so that this syntax error does not occur. See to the SQL Reference Manual for information about the proper usage of parameter markers within SQL statements to be prepared.

USQL -418

A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID USE OF PARAMETER MARKERS

Explanation: Parameter markers cannot be used in the SELECT list, as the sole argument of a scalar function, or in a concatenation function. Parameter markers cannot be used in the string expression of an EXECUTE IMMEDIATE SQL statement.

User Response: Correct the logic of the application program so that this error does not occur. See to the SQL Reference Manual for information about the proper usage of parameter markers within SQL statements and for EXECUTE IMMEDIATE SQL statement restrictions.

USQL -419

THE DECIMAL DIVIDE OPERATION IS INVALID BECAUSE THE RESULT WOULD HAVE A NEGATIVE SCALE

Explanation: The decimal division is invalid because it will result in a negative scale.
The formula used internally to calculate the scale of the result for decimal division is explained in the SQL Reference Manual.

User Response: None.

USQL -420

THE VALUE OF A CHARACTER STRING ARGUMENT WAS NOT ACCEPTABLE TO THE function-name FUNCTION

Explanation: A character string argument did not conform to the requirements of the function. For example, a character string passed to the DECIMAL function did not conform to the rules for forming an SQL integer or decimal constant.

User Response: Change the argument value so it conforms to the requirements of the function as specified in the SQL Reference Manual.

USQL -421

THE OPERANDS OF A UNION OR A UNION ALL DO NOT HAVE THE SAME NUMBER OF COLUMNS

Explanation: The operands of a UNION or UNION ALL must have the same number of columns.

User Response: Correct the SQL statement so that there are exactly the same number of columns in each operand.

USQL -423

INVALID RESULT SET LOCATOR VALUE

Explanation: The value specified in a result set locator host variable does not identify a valid result set locator.

User Response: There are two common causes for this error:

• The host variable used as a result set locator was never assigned a valid result set locator value. Result set locator values are returned by the DESCRIBE PROCEDURE and ASSOCIATE LOCATORS statements. Make sure the value in your host variable is obtained from one of these two statements.
• Result set locator values are only valid as long as the underlying SQL cursor is open. If a commit or rollback operation closes an SQL cursor, the result set locator associated with the cursor is no longer valid.

USQL -426

DYNAMIC COMMIT NOT VALID AT AN APPLICATION SERVER WHERE UPDATES ARE NOT ALLOWED

Explanation: An application which is executing is using DRDA protocols. This application has attempted to issue a dynamic COMMIT statement, or a stored procedure has attempted to issue a COMMIT_ON_RETURN while connected to a location at which updates are not allowed. A dynamic COMMIT or COMMIT_ON_RETURN can be issued only while connected to a location at which updates are allowed.

User Response: The IMS or CICS protocols should be used to commit work in these environments.

USQL -427

DYNAMIC ROLLBACK NOT VALID AT AN APPLICATION SERVER WHERE UPDATES ARE NOT ALLOWED

Explanation: An application which is executing using DRDA protocols has attempted to issue a dynamic ROLLBACK statement while connected to a location at which updates are not allowed. A dynamic ROLLBACK may be issued only during the time when an application is connected to a location at which updates are allowed.

User Response: The IMS or CICS protocols should be used to rollback work in these environments.

USQL -440

THE NUMBER OF PARAMETERS IN THE PARAMETER LIST DOES NOT MATCH THE NUMBER OF PARAMETERS EXPECTED FOR STORED PROCEDURE name, AUTHID authid, and LUNAME luname. number PARAMETERS WERE EXPECTED.

Explanation: Db2 has received an SQL CALL statement for a stored procedure. Db2 found the row in the SYSIBM.SYSPROCEDURES catalog table associated with the requested procedure name. However, the number of parameters supplied on the CALL statement did not match the number of parameters defined in the PARMLIST column of the SYSIBM.SYSPROCEDURES table. The definition of a stored procedure might be cached. If you suspect that this message is being issued because the cached definition of a procedure does not match the definition of a procedure in the SYSIBM.SYSPROCEDURES table, issue the -START PROCEDURE command to refresh the cache. Note the following definitions:

name The name of the stored procedure from the PROCEDURE field in SYSIBM.SYSPROCEDURES.
authid The authorization id from the AUTHID field in SYSIBM.SYSPROCEDURES.
luname The logical unit name from the LUNAME field in SYSIBM.SYSPROCEDURES.
number The expected number of parameters for the stored procedure with the NAME, AUTHID, and LUNAME described above.

modify the SQL application to provide the correct number of parameters on the SQL CALL statement. If the PARMLIST column of the SYSIBM.SYSPROCEDURES table contains incorrect information, you must make required updates to the PARMLIST column and then issue the -START PROCEDURE command to activate the updated definition.

USQL -444

USER PROGRAM name COULD NOT BE FOUND

Explanation: Db2 received an SQL CALL statement for a stored procedure and found the row in the SYSIBM.SYSPROCEDURES catalog table associated with the requested procedure name. However, the MVS load module currently identified in the LOADMOD column of the SYSIBM.SYSPROCEDURES row could not be found. Note the following definition:

name The name of the MVS
load module that
could not be found.

User Response: If the LOADMOD column value in the SYSIBM.SYSPROCEDURES table was specified incorrectly, correct the value and issue the -START PROCEDURE command to activate the changed definition. If the LOADMOD column value is correct, use the MVS linkage editor to create the required MVS load module in one of the MVS load libraries used by your installation for stored procedures.

USQL -450

STORED PROCEDURE name, PARAMETER NUMBER number OVERLAYED STORAGE BEYOND ITS DECLARED LENGTH

Explanation: While returning parameters from a stored procedure to an application, Db2 detected an overlay of one of the parameters. A stored procedure overwrote storage beyond a parameter’s declared length. Note the following definitions:

name The name of the stored procedure from the NAME field in the SYSIBM.SYSPROCEDURES catalog table.
number The parameter from the PARMLIST field in the SYSIBM.SYSPROCEDURES catalog table.

User Response: Examine the stored procedure to determine the cause of the overlay. Ensure that all parameters are declared correctly and that they match the definitions in SYSIBM.SYSPROCEDURES for procedure name. If you suspect that an error exists in Db2, See to Section 3 of the Diagnosis Guide and Reference for information on identifying and reporting the problem.

USQL -469

SQL CALL STATEMENT MUST SPECIFY AN OUTPUT HOST VARIABLE FOR PARAMETER number

Explanation: Db2 received an SQL CALL statement for a stored procedure. Db2 found the row in the SYSIBM.SYSPROCEDURES catalog table associated with the requested procedure name. However, parameter number was identified in the PARMLIST column of the SYSIBM.SYSPROCEDURES table as an OUT or INOUT parameter. A host variable must be supplied on the SQL CALL statement for parameters defined as OUT or INOUT. Note the following definition:

number The parameter number
from the PARMLIST field
in SYSIBM.SYSPROCEDURES.

User Response: If the SQL CALL statement is coded incorrectly, you must modify the SQL application to provide an output host variable on the SQL CALL statement. If the PARMLIST column of the SYSIBM.SYSPROCEDURES table contains incorrect information, perform the required updates to the PARMLIST column and issue the -START PROCEDURE command to activate the updated definition.

USQL -470

AN SQL CALL STATEMENT SPECIFIED A NULL VALUE FOR INPUT PARAMETER number, BUT THE STORED PROCEDURE DOES NOT SUPPORT NULL VALUES

Explanation: Db2 received an SQL CALL statement for a stored procedure and found a null value in the incoming parameter list. The Db2 stored procedure was defined in the SYSIBM.SYSPROCEDURES catalog table with LINKAGE=” “, which specifies that the Db2 stored procedure does not accept null values. Note the following definition:

number The parameter number
from the PARMLIST field
in SYSIBM.SYSPROCEDURES.

Use the -START PROCEDURE command to activate the changes to the SYSIBM.SYSPROCEDURES table.

USQL -471

SQL CALL FOR STORED PROCEDURE name FAILED DUE TO REASON rc

Explanation: Db2 received an SQL CALL statement for a stored procedure. The CALL statement was not accepted because of Db2 reason code rc. Note the following definitions:

name The name of the stored procedure specified on the SQL CALL statement.
rc The DB2 reason code describing the cause of the failure. The possible values are: 00E79000, 00E79001, 00E79002, 00E79003, 00E79004, 00E79005, 00E79006, 00E79007, 00E7900B, and 00E7900C.

User Response: Correct the condition described by the Db2 reason code.

USQL -480

THE PROCEDURE procedure-name HAS NOT YET BEEN CALLED

Explanation: The procedure identified in a DESCRIBE PROCEDURE or an ASSOCIATE LOCATORS statement has not yet been called within the application process or the procedure has been called, but an explicit or implicit commit occurred before the statement.

User Response: Correct the order of the statements and rerun them.

USQL -482

THE PROCEDURE procedure-name RETURNED NO LOCATORS

Explanation: The procedure identified in an ASSOCIATE LOCATORS statement returned no result set locators.

User Response: Determine if result set locators are returned from the identified procedure by using the DESCRIBE PROCEDURE statement.

USQL -496

THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE IT REFERENCES A RESULT SET THAT WAS NOT CREATED BY THE CURRENT SERVER

Explanation: The SQL statement cannot be executed because the current server is different from the server that called a stored procedure. The SQL statement can be any of the following:

• ALLOCATE CURSOR
• DESCRIBE CURSOR
• FETCH (using an allocated cursor)
• CLOSE (using an allocated cursor)

User Response: Connect to the server that called the stored procedure which created the result set before running the SQL statement that failed.

USQL -497

THE LIMIT OF 32767 OBIDS HAS BEEN EXCLUDED FOR DATABASE database-name

Explanation: An attempt was made to create an object in database database-name, but the limit of 32767 OBIDs has been exceeded for that database.

User Response: DROP all unused objects in this database and issue a COMMIT, specify a different database, or run the MODIFY utility to reclaim unused OBIDs.

USQL -499

CURSOR cursor-name HAS ALREADY BEEN ASSIGNED TO THIS OR ANOTHER RESULT SET FROM PROCEDURE procedure-name

Explanation: An attempt was made to assign a cursor to a result set using the SQL statement ALLOCATE CURSOR and one of the following applies:

• The result set locator variable specified in the ALLOCATE CURSOR statement has been previously assigned to cursor cursor-name.
• The cursor cursor-name, specified in the ALLOCATE CURSOR statement has been previously assigned to a result set from stored procedure procedure-name.

User Response: Determine if the target result set named in the ALLOCATE CURSOR statement has been previously assigned to a cursor.

If the result set has been previously assigned to cursor cursor-name, choose another target result set or choose the call stored procedure procedure-name again and reissue the ASSOCIATE LOCATOR and ALLOCATE CURSOR statements. If the result set has not been previously assigned to a cursor, the cursor cursor-name specified in the ALLOCATE CURSOR statement has been previously assigned to some result set from stored procedure procedure-name. You can not assign cursor cursor-name to another result set, so you must specify a different cursort name in the ALLOCATE CURSOR statement.

USQL -500

THE IDENTIFIED CURSOR WAS CLOSED WHEN THE CONNECTION WAS DESTROYED

Explanation: The FETCH, UPDATE, DELETE, or CLOSE statement identifies a closed cursor that was defined with the WITH HOLD option. The cursor was closed when the connection on which it was dependent was destroyed during a commit operation. The connection was destroyed because the application process placed it in the released state, or the application plan was bound with the DISCONNECT(AUTOMATIC) option.

User Response: The correction depends on the desired state of both the cursor and the connection, as noted as follows:

• If you want the cursor closed, you must change the application program so that the cursor is not referenced in the closed state.
• If you want the cursor open, and the connection was placed in the released state by the application program, you must change the program so that the connection is not placed in the released state until the cursor is explicitly closed.
• If you want the cursor open and the connection was placed in the released state as a result of the DISCONNECT(AUTOMATIC) option, rebind the plan using DISCONNECT(CONDITIONAL).

Correct the error in the application, rebind the plan, and resubmit the job.

USQL -501

THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN

Explanation: The application program attempted to do one of the following:

1. FETCH using a cursor, or
2. CLOSE using a cursor

at a time when the specified cursor was not open.

User Response: Check for a previous SQL return code that may have closed the cursor. Commit and rollback operations close cursors. UNSUCCESSFUL SQLCODES -404, -652, -679, -901, -904, -909, -910, -911, and -913 will force the cursor to close. After the cursor is closed, any fetches or close cursor statements will receive this USQLCODE -501.

If no previous SQL return codes have been issued, correct the logic of the application program to ensure that the cursor is open at the precise time the FETCH or CLOSE statement is executed.

USQL -502

THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS ALREADY OPEN

Explanation: The application program attempted to execute an OPEN statement for a cursor that was already open.

User Response: Correct the logic of the application program to ensure that it does not attempt to execute an OPEN statement for a cursor that is already open.

USQL -503

A COLUMN CANNOT BE UPDATED BECAUSE IT IS NOT IDENTIFIED IN THE UPDATE CLAUSE OF THE SELECT STATEMENT OF THE CURSOR

Explanation: The application program attempted to update (while using a cursor) a value in a column of the object table that was not identified in the FOR UPDATE clause in the cursor declaration. Any column that is to be updated must be identified in the FOR UPDATE clause of the cursor declaration.

User Response: Correct the application program. If the column is to be updated, its name must be added to the FOR UPDATE clause of the cursor declaration.

USQL -504

THE CURSOR NAME cursor-name IS NOT DEFINED

Explanation: Cursor cursor-name was referenced in an SQL statement. One of the following statements is true.

• Cursor cursor-name was not declared (using the DECLARE CURSOR statement) or allocated (using the ALLOCATE CURSOR statement) in the application program before it was referenced.
• Cursor cursor-name was referenced in a positioned UPDATE or DELETE statement which is not a supported operation for an allocated cursor.
• Cursor cursor-name was allocated, but a CLOSE cursor statement naming cursor-name was issued and deallocated the cursor before this cursor reference.
• Cursor cursor-name was allocated, but a ROLLBACK operation occurred and it deallocated the cursor before this cursor reference.
• Cursor cursor-name was allocated, but its associated cursor which was declared in a stored procedure was not declared WITH HOLD, and a COMMIT operation occurred and subsequently deallocated the cursor before this cursor reference. The COMMIT operation can be explicit (like the COMMIT statement) or implicit (like a stored procedure defined as COMMIT_ON_RETURN = “Y” was called before this cursor reference).
• Cursor cursor-name was allocated, but its associated stored procedure was called again since the cursor was allocated, the new results sets were returned, and cursor cursor-name was deallocated.

User Response: Check the application program for completeness and for a possible spelling error in the cursor declaration or allocation. The declaration for, or allocation of, a cursor, must appear in an application program before SQL statements that reference the cursor. If the cursor-name was <UNKNOWN>, the cursor was not successfully declared or allocated. This can occur if SQL(Db2) was used, and a warning message was issued during precompilation. Check the precompile output for warning messages on the DECLARE CURSOR or ALLOCATE CURSOR statement, and then correct the statement. Regarding an allocated cursor, please note the following: If an implicit or explicit COMMIT, ROLLBACK, or CLOSE occurred since the cursor was successfully allocated, you must modify the application program logic to do one of the following tasks:

• After the COMMIT, ROLLBACK, or CLOSE operation, call the associated stored procedure again, and then reissue the ASSOCIATE LOCATORS and ALLOCATE CURSOR statements.
• For COMMIT, declare the associated cursor in the stored procedure WITH HOLD so the COMMIT operation will not deallocate the cursor.

For an allocated cursor, if the associated stored procedure was called again and the new result sets were returned because the cursor was allocated, reissue the ASSOCIATE LOCATORS and ALLOCATE CURSOR statements.

USQL -507

THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT OPEN

Explanation: The application program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement at a time when the specified cursor was not open.

User Response: Check for a previous SQL return code that might have closed the cursor. UNSUCCESSFUL SQLCODES -404, -652, -679, -901, -904, -909, -910, -911, and -913 force the cursor to close. After the cursor is closed, any fetches or close cursor statements receive USQLCODE -501. Any updates or deletes receive the USQLCODE -507. Correct the logic of the application program to ensure that the specified cursor is open at the time the UPDATE or DELETE statement is executed.

USQL -508

THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT POSITIONED ON A ROW

Explanation: The application program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement at a time when the specified cursor was not positioned on a row of the object table. The cursor must be positioned on the row that is to be updated or deleted.

Note that the cursor is no longer positioned on a row if the row is deleted or an index column of the row is updated by another cursor in the same application program.

User Response: Correct the logic of the application program to ensure that the cursor is correctly positioned on the intended row of the object table before the UPDATE or DELETE statement is executed. Note that the cursor is not positioned on a row if FETCH returned an SQLCODE = 100.

USQL -509

THE TABLE IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS NOT THE SAME TABLE DESIGNATED BY THE CURSOR

Explanation: The application program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement where the table named in that statement did not match the name of the table specified in the declaration for that cursor.

User Response: Correct the application program to ensure that the table identified in the UPDATE or DELETE statement is the same table identified in the declaration for the cursor.

USQL -510

THE TABLE DESIGNATED BY THE CURSOR OR THE UPDATE OR DELETE STATEMENT CANNOT BE MODIFIED

Explanation: The application program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement against a table or view that cannot be updated or deleted. This can occur for a delete from a read-only view or for an update in which the cursor was not defined with the FOR UPDATE clause.

This error code is also returned when the table exists at a remote location and Db2 has employed block fetching because you explicitly declared the cursor FOR FETCH ONLY, or because the application is bound CURRENTDATA(NO) and the cursor is ambiguous. This error code is also returned if Db2 has employed parallelism to execute the SELECT statement associated with the cursor named in a DELETE WHERE CURRENT OF cursor statement. Or, the error code is also returned if a DELETE WHERE CURRENT OF is issued against a row which Db2 cannot guarantee to have not been modified by another application since the specific time when the cursor was positioned upon it (in accordance with ISO(CS) semantics for an ambiguous cursor in an application bound CURRENTDATA(NO)).

User Response: The requested UPDATE or DELETE cannot be performed. See the SQL Reference Manual for information about restrictions on using UPDATE and DELETE operations against views. For a remote table, modify the DECLARE CURSOR and then rebind the PLAN. For a cursor that uses parallelism, disable parallelism for the query by using the DEGREE(1) BIND option for static SQL or by setting the CURRENT DEGREE special register to “1” for dynamic SQL. For an ambiguous cursor in a application bound CURRENTDATA(NO), make the cursor unambiguous (declare it FOR UPDATE OF) or rebind the application CURRENTDATA(YES).

USQL -511

THE FOR UPDATE CLAUSE CANNOT BE SPECIFIED BECAUSE THE TABLE DESIGNATED BY THE CURSOR CANNOT BE MODIFIED

Explanation: The result table of the SELECT statement cannot be updated. This can occur if the SELECT specifies more than one table or view in the FROM clause, provided the SELECT list contains a built-in function or DISTINCT, or if the statement contains an ORDER BY or GROUP BY or HAVING clause. This can also occur if a view is specified in the FROM clause and the view cannot be updated.

User Response: Updates cannot be performed on the result table as it is specified. See to the SQL Reference Manual for information regarding restrictions on the updating of views.

USQL -512

STATEMENT REFERENCE TO REMOTE OBJECT IS INVALID

Explanation: One of the following conditions exists:

• The statement refers to multiple locations.
• A statement with a remote reference is being EXPLAINED by a dynamic EXPLAIN statement or by the EXPLAIN(YES) option.
• An alias is used incorrectly.
• A three-part name is implicitly or explicitly used in a statement that is not supported by the Db2 private protocols.

User Response: If the object cannot be meaningfully eliminated from the statement, see to your Database Administrator for other ways to obtain the data required. See to the SQL Reference Manual for additional information about using remote objects.

USQL -513

THE ALIAS alias-name MUST NOT BE DEFINED ON ANOTHER LOCAL OR REMOTE ALIAS

Explanation: The object indicated by “alias-name” is a local or remote alias. An alias is not allowed to be defined on a local alias, and it should not be defined on a remote alias.

User Response: Modify the SQL statement to ensure that all object references are to base tables or views.

USQL -514

THE CURSOR cursor-name IS NOT IN A PREPARED STATE

Explanation: The application program has tried to use a cursor, cursor-name that is not in a prepared state. The cursor is associated with a statement that (1) has never been prepared, or (2) has been invalidated by a commit or rollback operation.

User Response: For case (1), ensure that you prepare the statement that is named in the DECLARE CURSOR statement for cursor-name before you try to open the cursor. For case (2), perform one of the following:

• Use the WITH HOLD option of DECLARE CURSOR.
• Do not execute a commit or rollback operation until you are finished using the cursor.
• Prepare the statement again after the commit or rollback.

USQL -516

THE DESCRIBE STATEMENT DOES NOT IDENTIFY A PREPARED STATEMENT

Explanation: An attempt was made to execute a DESCRIBE for a statement that had not been successfully prepared beforehand. This error can occur when the DESCRIBE originates on an application requester that supports extended dynamic SQL. Because the target statement is static on the Db2 for MVS/ESA subsystem, the DESCRIBE statement fails.

User Response: Correct the logic of the application program to ensure that a statement is properly prepared before a DESCRIBE of the statement is attempted. If the DESCRIBE is a distributed request that originated on a system that supports extended dynamic SQL, contact your System Administrator about changing the Db2 subsystem parameter DESCSTAT to YES to tolerate these DESCRIBE requests against static SQL.

USQL -517

CURSOR cursor-name CANNOT BE USED BECAUSE ITS STATEMENT NAME DOES NOT IDENTIFY A PREPARED SELECT STATEMENT

Explanation: The cursor “cursor-name” could not be used as specified because the prepared statement named in the declaration for the cursor was not a SELECT statement.

User Response: Verify that the statement-name is specified correctly in the PREPARE statement and in the DECLARE CURSOR statement for cursor “cursor-name”. Alternatively, correct the application program logic to ensure that only prepared SELECT statements are used in association with cursor declarations.

USQL -518

THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT

Explanation: One of the following conditions exists:

• The statement named in the EXECUTE statement has not been prepared.
• The statement named in the EXECUTE statement identifies a SELECT statement.
• The statement named in the EXECUTE IMMEDIATE statement identifies a SELECT statement.

User Response: Ensure that you prepare the statement prior to EXECUTE. Also, ensure that the statement you prepare is not a SELECT statement.

USQL -519

THE PREPARE STATEMENT IDENTIFIES THE SELECT STATEMENT OF THE OPENED CURSOR cursor-name

Explanation: The application program has attempted to PREPARE (actually, it has attempted to re-PREPARE) the SELECT statement for the specified cursor at a time when that cursor was already open.

User Response: Correct the logic of the application program so that it does not attempt to re-PREPARE the SELECT statement for a cursor when that cursor is open.

USQL -525

THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE IT WAS IN ERROR AT BIND TIME FOR SECTION = sectno PACKAGE = pkgname CONSISTENCY TOKEN = X”contoken”

Explanation: One of the following actions occurred:

• The statement was in error when the package was bound, but the error was ignored at the time because the option SQLERROR (CONTINUE) was used. Since the statement contains an error, it cannot be executed.
• The statement might not be an executable statement at this location, or might only be executable by a Db2 application requester (for example, DECLARE TABLE in an application running on OS/2 causes this message to occur).

The variables are as follows:

• sectno Section Number
• pkgname locid.collid.pkgid
• contoken Consistency token in hexadecimal

User Response: If the SQL statement is not required to execute at the indicated location, correct the program so that the statement in error does not execute at that point. Precompile, compile, and bind replace the package. If the SQL statement is supposed to execute at the indicated location, correct the problem which was discovered when it was bound and then bind the package over using BIND with ACTION(REPLACE). If multiple versions of the package have been bound, enter the following SELECT statement to determine which version contains the error: SELECT VERSION FROM locid.SYSIBM.SYSPACKAGE WHERE LOCATION = “ “ AND COLLID = “collid” AND NAME = “pkgid” AND HEX(CONTOKEN) = “contoken”
Where the variables are:

• locid Location name
• collid Collection id
• pkgid Program name