Home | Mainframe Wiki | Free Downloads | Sample Programs | More References | Mainframe Forum

IBMMainframes.com

Quick References for IBM Mainframe Programming

DB2 SQL CODES

DB2 RETURN CODES / SQL ERROR CODES

+100 | +117 | +162 | +204 | +205 |+231 | +304 | +347 | +625 | +802

-101 | -104 | -117 | -122 | -150 | -180 | -181 | -199 | -204 | -227 | -304 | -305 | -311 | -501 | -502 | -503

-510 | -530 | -532 | -545 | -551 | -552 | -803 | -805 | -811 | -818 | -904 | -911 | -913 | -922 | -927

 

Code

Reason

Description

System Action

User Response

0

SUCCESSFUL EXECUTION

Unqualified successful execution or successful execution with one or more warnings. If SQLWARN0 is blank, there are no warnings. If SQLWARN0 = 'W', at least one of the other warning indicators in the SQLCA has been set to indicate a warning condition. For example, SQLWARN1 is used to indicate that a value of a string column was truncated when assigned to a host variable.

NA

NA

-7

STATEMENT CONTAINS THE ILLEGAL CHARACTER

The specified 'character' is not a valid character in SQL statements.

The statement cannot be executed

Correct the syntax and resubmit the statement.

-10

THE STRING CONSTANT BEGINNING WITH 'string' IS NOT TERMINATED

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

The statement cannot be executed.

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

-29

INTO CLAUSE REQUIRED

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.

The statement cannot be executed.

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

-60

INVALID type SPECIFICATION : specification-value

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

The statement cannot be executed.

Correct the syntax, and reissue the statement.

 

-84

UNACCEPTABLE SQL STATEMENT

This SQL statement is unacceptable to DB2.  One of the following has occurred:*   An attempt has been made to PREPARE or EXECUTE IMMEDIATE a SQL  statement that cannot be prepared; refer to the proper SQL statement in SQL Reference*   The embedded SQL statement is not an SQL statement supported by DB2.

The statement cannot be executed.

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. If the situation involves a SQL statement that is not supported by DB2, remove it from the application program and precompile again.

-87

A NULL VALUE WAS SPECIFIED IN A CONTEXT WHERE A NULL IS NOT ALLOWED

An SQL parameter or SQL variable having a null value was specified in a context in which a null value is not allowed. For example, an SQL statement can be specified with an SQL parameter or SQL variable on the PREPARE and EXECUTE statements. In these contexts the value of an SQL parameter or SQL variable must not be null..

The statement cannot be executed.

Correct the SQL statement.

+100

ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE

One of the following conditions occurred: *   No row met the search conditions specified in an UPDATE or DELETE statement. *   The result of a SELECT INTO statement was an empty table. *   A FETCH statement was executed when the cursor was positioned after the last row of the result table. *   The result of the subselect of an INSERT statement is empty. When a SELECT statement is executed using SPUFI, this SQL code indicates normal completion.

No data was retrieved, updated, or deleted.

If expecting data, verify WHERE clause for accuracy and completeness.

-101

THE STATEMENT IS TOO LONG OR TOO COMPLEX

The statement cannot be executed because it exceeds the system limits for either length or complexity.

The statement cannot be executed.

Break the statement up into shorter or less complex SQL statements.

-102

THE LENGTH OF THE STRING CONSTANT BEGINNING string IS>250 CHARACTERS OR 124 GRAPHIC CHARACTERS

The string constant beginning with 'string' has a length greater than 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 assignment from host variables.

The statement cannot be executed.

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.

-103

literal IS AN INVALID NUMERIC LITERAL

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

The statement cannot be executed.

Correct the invalid literal.

-104

ILLEGAL SYMBOL token VALID SYMBOLS ARE token-list

A syntax error in the SQL statement was detected at 'token'. A partial list of valid tokens, 'token-list', is provided.  This list assumes that the statement is correct up to that point, and only as many tokens are listed as will fit.

The statement cannot be executed.

Examine the statement in the area of the specified token.If you are executing the SQL statement dynamically under TSO, be sure that if the data set containing the DSNHDECP load module has not been included in your LINKLSTxx, it has been included in STEPLIB statements in:1.  The DB2 start-up procedure 2.  The TSO logon procedure or batch job JCL.

-105

INVALID STRING

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

The statement cannot be executed.

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

-107

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

The name returned as 'name' is too long.  The maximum permissible length for names of that type is indicated by 'size'.Names for the following cannot contain more than 64 characters:*   Version-id Names for the following 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-id Names for the following cannot contain more than 16 characters:*   Location-name The following 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)*   Constraint names specified in CREATE or ALTER TABLE statements.*   Package-idHost variable names cannot contain more than 64 characters.  Volume serial numbers cannot contain more than 6 characters.  Labels cannot contain more than

The statement cannot be executed.

Choose a shorter name for the object. For an SQL version ID, the name must not exceed 64 EBCDIC bytes, or 122 UTF-8 bytes.

-109

clause CLAUSE IS NOT PERMITTED

The indicated clause is not permitted in the context in which it appears in this SQL statement. 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.

The statement cannot be executed.

Correct the SQL statement.

-110

INVALID HEXADECIMAL LITERAL BEGINNING string

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

The statement cannot be executed.

Correct the invalid literal.

-111

A COLUMN FUNCTION DOES NOT INCLUDE A COLUMN NAME

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

The statement cannot be executed.

A column name must be specified as an operand to the function.  Refer to Chapter 4 of SQL Reference for information about the proper usage of column functions.

-112

THE OPERAND OF A COLUMN FUNCTION IS ANOTHER COLUMN FUNCTION OR DISTINCT FOLOWED BY AN EXPERSSION

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

The statement cannot be executed.

Correct the function specification.  Refer to Chapter 4 of SQL Reference for information about the proper usage of column functions.

-113

INVALID CHARACTER FOUND IN NAME: name.

The name contains an invalid character.  The name can be an SQL ordinary identifier name or a host variable name.For SBCS 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 codes apply to SBCS identifiers:000            An invalid character was found in the SBCS identifier (including the situation where a DBCS identifier is used where only an SBCS identifier is allowed.)The following reason codes apply to DBCS identifiers.101           An odd number of bytes exists between the shift-out and the shift-in.102  No shift-in was found at the end of the identifier.103 DBCS blanks X'4040' are not allowed.104    There are no characters between the shift-out and the shift-in.105   Shift-out can not be the first byte of the DBCS character between the shift-out and the shift-in.

The statement cannot be executed.

Correct the name.

-115

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

A simple comparison like '>' must not be followed by a list of items.  ANY and ALL comparisons must be followed by a subselect, rather than an expression or a list of items.

The statement cannot be executed.

Correct the SQL statement. 

+117

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

The number of insert values in the value list of the insert operation is not the same as the number of object columns specified.

A valid plan or package will be created if no errors are detected. The statement is bound dynamically on each execution of the statement.

For better performance, rebind the plan or package after correcting the statement. To correct the statement, specify one and only one value for each of the specified object columns.

-117

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

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

The statement cannot be executed.  No data was inserted into the object table

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

-118

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

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.

The statement cannot be executed.  No data was inserted, updated, or deleted.

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.

-119

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

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.

The statement cannot be executed.

The implied function is not supported by DB2. 

-120

A WHERE OR SET CLAUSE INCLUDES COLUMN FUNCTION OR A WHERE CLAUSE REFERENCES COLUMN col-name WHICH IS DERIVED FROM A COLUMN FUNCTION OR EXPRESSION IN A VIEW DEFINITION

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.

The statement cannot be executed.  Note:  The 'column-name' may or may not be returned in SQLCA, depending on the nature of the error occurring in the SQL statement.

Change the statement so that the aggregate function or OLAP specification is not specified in a context where it is not allowed.

 

-121

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

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.

The statement cannot be executed.  No data was inserted or updated in the object table.

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

-122

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

The SELECT statement contains one of these two types of 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.

The statement cannot be executed.

You can correct the statement by:

Including the columns or expressions in the GROUP BY clause that are in the SELECT clause, or
Removing the columns or expressions from the SELECT clause.

-125

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

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

The statement cannot be executed.

Correct the syntax of the ORDER BY clause such that each column identifier properly denotes a column of the result table.

-126

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

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.

The statement cannot be executed.  The cursor remains undefined in the application program.

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. 

-127

DISTINCT IS SPECIFIED MORE THAN ONCE IN A SUBSELECT

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

The statement cannot be executed.

The implied function is not supported by DB2. 

-128

INVALID USE OF NULL IN A PREDICATE

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

The statement cannot be executed.

The implied function is not supported by DB2. 

-129

THE STATEMENT CONTAINS TOO MANY TABLE NAMES

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

The statement cannot be executed.

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. 

-131

STATEMENT WITH LIKE PREDICATE HAS INCOMPATIBLE DATA TYPES

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.

The statement cannot be executed.

Check the data type of every operand.

-132

A LIKE PREDICATE IS INVALID BECAUSE THE FIRST OPERAN IS NOT A COLUMN OR SECOND OPERAND IS NOT A STRING

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

The statement cannot be executed.

The implied function is not supported by DB2. 

-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

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.

The statement cannot be executed.

Correct the syntax of the HAVING clause.

 

-134

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

A long string was referenced in a context in which long strings are not allowed.  For an exhaustive list of such contexts, refer to Varying Length Character Strings in Chapter 3 of SQL Reference.

The statement cannot be executed.  Note:  The 'column-name' might not be returned in SQLCA, depending on the nature of the error and the syntax in which it occurred.

The requested operation on a long string value is not supported by DB2. 

-136

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

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.

The statement cannot be executed.

The statement must be modified such 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.

-137

RESULT OF CONCATENATION TOO LONG

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

The statement cannot be executed.

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

-138

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

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, if it is of fixed-length, or M is the maximum length of the first argument, if it is of varying-length.  N is the value of the second argument.

The statement cannot be executed.

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

-150

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

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.

The statement cannot be executed.  No data was inserted, updated, or deleted.

Be certain to specify base DB2 table/view names for INSERT statements.

-151

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

The specified column cannot be updated due to one of the following:  *   The object table is partitioned (that is, 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.  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.

The statement cannot be executed.  No data was updated in the object table or view.

The requested function is not supported by DB2.

-153

THE CREATE VIEW STATEMENT DOES NOT INCLUDE A REQUIRED COLUMN LIST

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

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

Correct the statement by providing a list of names for the columns of the view. 

-154

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

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.

The CREATE VIEW statement cannot be executed.  The specified view is not created.

The implied function is not supported.

 

-156

THE ALTER TABLE, DROP TABLE, LOCK TABLE OR CREATE INDEX STATMENT IDENTIFIES A VIEW

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

The statement cannot be executed.  The specified view or remote object was not altered, dropped, or locked, or the index was not created.

Verify that the proper name was specified in the statement.

-157

VIEW view-name IS IDENTIFIED IN FOREIGN-KEY CLAUSE

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

The statement cannot be executed.

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

-158

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

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.

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

Correct the syntax of the statement to specify a column name for each column in the view to be created. 

-159

THE NAME SPECIFIED ON DROP VIEW IS A TABLE NAME

The object specified in the DROP VIEW statement, DROP ALIAS statement, or COMMENT ON ALIAS statement identifies a table instead of a view or an alias.

The statement cannot be executed.

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.

-160

THE WITH CHECK OPTION CANNOT BE USED FOR THE SPECIFIED VIEW

The WITH CHECK OPTION does not apply to a view definition under either of the following circumstances:  *   The view is defined as 'read-only' (for example, the view is defined     on more than one base table or other view).  *   The SELECT statement of the view contains a subselect, a GROUP BY or     HAVING clause, or the DISTINCT keyword.

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

Correct the syntax of the WITH CHECK OPTION keyword in the CREATE VIEW statement.

 

-161

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

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 the results will conform to the view definition.

The statement cannot be executed.  No inserts or updates were performed, and the contents of the object view (and underlying base table) remain unchanged.

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

+162

TABLESPACE database-name.tablespace-name HAS BEEN PLACED IN CHECK PENDING

The indicated table space is in the CHECK PENDING state because ALTER TABLE was used to specify a referential constraint on a populated table.  The table space is not generally available until the CHECK PENDING state is removed from the table space.

The table space has been placed in CHECK PENDING state.

The use of CHECK DATA is advised.

-164

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

The authorization ID 'auth-id' does not have the authority necessary to create views with qualifiers other than its own authorization ID.  Specifically, the attempt to create a view with qualifier 'authorization ID' is rejected.

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

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.

-170

THE NUMBER OF ARGUMENTS SPECIFIED FOR function-name IS INVALID

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

The statement cannot be executed.

Correct the statement.

-171

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

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

The statement cannot be executed.

Correct the invocation of the function, and reissue the statement. If the encoding scheme is EBCDIC or ASCII, a possible reason for this error is that a character argument was specified for a built-in function that expects a graphic argument, or a graphic argument was specified for a built-in function that expects a character argument. The UNICODE encoding scheme supports the mixing of character and graphic arguments, but EBCDIC and ASCII do not.

-172

function-name IS NOT A VALID FUNCTION NAME

NA

NA

NA

-173

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

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

The statement cannot be executed.

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

-180

THE STRING REPRESENTATION OF THE DATE, TIME, OR TIMESTAMP VALUE HAS INVALID SYNTAX

The string representation of a Date or Time or Timestamp value does not conform to the syntax for the specified or implied data type.

The statement cannot be executed.

Correct the program to ensure the specified value conforms to the syntax of DATE, TIME, and TIMESTAMP.

-181

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

The string representation of a datetime is not in the acceptable range or is not in the correct format.

The statement cannot be executed.

Check whether the DATETIME value is within the valid range and is in the proper format. 

-182

AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE IS INVALID

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

The statement cannot be executed.

Correct the indicated arithmetic expression.

-183

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

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.

The statement cannot be executed.

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.

-184

AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE CONTAINS A PARAMETER MARKER

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

The statement cannot be executed.

Correct the indicated arithmetic expression.

-185

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

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.

The statement cannot be executed.

Contact the system programmer about installation of the date or time exit.

-186

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

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.

The statement cannot be executed.

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

-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

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

The statement cannot be executed.

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.

-198

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

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  release 2.3 cannot be used on a release 2.2 system if the distributive functions was used.  If this error appears on release 2.2 and the DBRM was built on 2.3, the program needs to be precompiled again to correct the problem.

The statement cannot be executed.

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.

-199

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

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'.  This list assumes that the statement is correct up to that point, and only as many tokens are listed as will fit.

The statement cannot be executed.

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

-203

A REFERENCE TO COLUMN column-name IS AMBIGUOUS

Two or more of the tables specified in a FROM clause in the statement contain columns with the specified 'column-name'.  The column name needs further qualification to establish which of the possible table-columns it is.

The statement cannot be executed.

Qualify the ambiguous column name with a table name or correlation name.

+204

name IS AN UNDEFINED NAME

The object identified by name is not defined in the DB2 subsystem. This return code can be generated for any type of DB2 object.

A valid plan or package will be created if no errors are detected. The statement is bound dynamically on each execution of the statement.

For better performance, rebind the plan or package after correcting the statement. To correct the statement, determine that the object name was correctly specified in the SQL statement (including any required qualifiers). If so, ensure that the object exists in the system before resubmitting the statement.

-204

name IS AN UNDEFINED NAME

The object identified by 'name' is not defined in the DB2 subsystem.  This return code can be generated for any type of DB2 object. This return code is issued when an alias is used and the table the alias is defined on does not exist.  When you create an alias, you will get a warning message that the table does not exist.  When you use the alias, you will see this message.

The statement cannot be executed.

Determine that the DB2 CREATOR or OBJECT NAME was correctly specified in the SQL statement (including any required qualifiers).  If so, ensure that the object exists in the system before resubmitting the statement.

+205

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

For ALTER TABLE ALTER COLUMN, column column-name is not a column in table table-name.

A valid plan or package will be created if no errors are detected. The statement is bound dynamically on each execution of the statement.

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

-205

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

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

The statement cannot be executed.

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

-206

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

This return code is used to report one of these 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.

The statement cannot be executed.  No data was retrieved, inserted, or updated.

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.

-207

THE ORDER BY CLAUSE IS INVALID BECAUSE IT INCLUDES A COLUMN NAME BUT IT APPLIES TO THE RESULT OF A UNION OR UNION ALL

The ORDER BY clause in the statement is invalid because column names are specified in the ORDER BY list, and the statement contains a subselect with the UNION or UNION ALL operator.  If an ORDER BY clause applies to the result of a UNION or UNION ALL, integers (rather than column names) must be used in the ORDER BY list to identify the columns that are to be used in ordering the result table.

The statement cannot be executed.

Correct the statement by replacing the column names in the ORDER BY list with numeric column identifiers. 

-208

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

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

The statement cannot be executed.

Correct the syntax of the statement, either by adding the specified column to the result table, or deleting it from the ORDER BY clause. 

-219

THE REQUIRED EXPLANATION TABLE table-name DOES NOT EXIST

The EXPLAIN statement assumes the existence of the explanation table and it is not defined in the DB2 subsystem as a base table.  Refer to Chapter 6 of SQL Reference for more information.

The statement cannot be executed.

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

-220

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

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.

The statement cannot be executed.  The explanation information is not generated.

Correct the definition of the required explanation table. 

-227

FETCH fetch-orientation IS NOT ALLOWED, BECAUSE CURSOR cursor-name HAS AN UNKNOWN POSITION (sqlcode,sqlstate)

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

The statement cannot be processed. The cursor position is not changed

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

+231

CURRENT POSITION OF CURSOR cursor-name IS NOT VALID FOR THE SPECIFIED FETCH ORIENTATION OF THE CURRENT ROW OR ROWSET

The  cursor was not positioned on a row or rowset, and one of the following fetch orientations specified that the cursor was to be positioned relative to its current position:
1. CURRENT or CURRENT ROWSET
2. RELATIVE 0 or ROWSET STARTING AT RELATIVE 0

The statement cannot be processed. No data was fetched, and the cursor position is unchanged.

Correct the application program to establish a valid cursor position before issuing this FETCH statement. Be certain to FETCH to position on a row after opening a cursor. If cursor is declared SENSITIVE STATIC SCROLL, the row may be a hole, from which no values can be fetched.

-301

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

The input host variable in the input SQLDA whose entry number is indicated by 'position-number', could not be used as specified in the statement because its data type is incompatible with the requested function.

The statement cannot be executed.

Verify that the data type of the indicated input host variable in the statement is compatible with the manner in which it is used.

-302

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

The value of the input host variable, whose entry in the input SQLDA is indicated by 'position-number', was found to be invalid or to be too large to fit in the corresponding column of the table or the corresponding target value.  One of the following has occurred:

The statement cannot be executed.

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

-303

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

A FETCH or SELECT into the output host variable, whose entry in the output SQLDA is indicated by 'position-number', could not be performed because the data type of the variable was not compatible with the data type of the corresponding SELECT-list element.  The output host variable and the corresponding SELECT-list element must fall 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 datetime, and timestamp values, the host variable must be a character string variable with a proper length.

The FETCH or SELECT cannot be executed.  No data was retrieved.

Verify that table definitions are current, and that the host variable has the proper data type.

+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

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

The FETCH or SELECT could not return the data for the indicated SELECT item, the indicator variable is set to negative two (-2) to indicate a null value returned. Processing continues.

Verify DCLGEN host variable definitions are current with DB2 catalog table/view attributes.

-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

A CALL, FETCH, SELECT, VALUES INTO, or SET assignment statement with 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.

The statement cannot be executed.  No data was retrieved. If the statement was a FETCH then the CURSOR remains open.

Verify that table definitions are current, and that the host variable has the proper data type.  See the explanation for SQLCODE -405 for ranges of SQL data types.

-305

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

A FETCH or embedded SELECT operation resulted in the retrieval of a null value to be inserted into the output host variable, designated by 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.

The statement cannot be executed.  No data was retrieved.

Examine the definition of the table that is the object of the FETCH or SELECT, and correct the application program to 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.

-309

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

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 in the case in which the host variable contains the NULL value--even though the object column may in fact contain nulls.

The statement cannot be executed.

Rebind the plan or package containing the statement. The condition described is not an error in DB2 Version 2 Release 3 and later.

-311

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

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.

The statement cannot be executed.

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.

-312

UNDEFINED OR UNUSABLE HOST VARIABLE variable-name

The host variable 'variable-name' appears in the SQL statement, but either no declaration for a variable of that name appears in the application program, or the attributes are improper for the specified usage.

The statement cannot be executed.

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

-313

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

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.

The statement cannot be executed.

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.

+347

THE RECURSIVE COMMON TABLE EXPRESSION name MAY CONTAIN AN INFINITE LOOP

The recursive common table expression called name may not complete. This warning is based on not finding specific syntax as part of the iterative portion of the recursive common table expression.

The statement cannot be executed.

To prevent an infinite loop, include the expected syntax as described.

-401

THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE

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

The statement cannot be executed.

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, then, if a view is being accessed, check the data types of all the operands in the view definition.

-402

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

A nonnumeric operand has been specified for the arithmetic function or operator 'arith-fop'.

The statement cannot be executed.

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

-404

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

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

The statement cannot be executed.  Note:  'column-name' may or may not be returned in SQLCA, depending on the syntax of the INSERT or UPDATE statement.

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.

-405

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

The specified numeric literal is not in the proper range.  The proper ranges for SQL values are as follows:  *   5.4E-79 to 7.2E+75 for FLOAT values *   -(10 -1) to +(10 -1) for DECIMAL values *   -2147483648 to 2147483647 for INTEGER values *   -32768 to +32767 for small integer (SMALLINT) values.

The statement cannot be executed.

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

-406

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

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

The statement cannot be executed.

See the explanation of SQLCODE -405 for allowed ranges for numeric data types.

-407

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

The update or insert value was NULL, but the object column was declared as NOT NULL in the table definition. Consequently: *   Null values cannot be inserted into that column. *   Values in that column cannot be set to NULL by an update.

The statement cannot be executed. Note:  'column-name' may or may not be returned in SQLCA, depending on the syntax of the SQL statement in which the error was detected.

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

-408

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

The data type of the value to be inserted into 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 character column that has a field procedure.

The statement cannot be executed.  No data was inserted or updated.

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.

-409

INVALID OPERAND OF A COUNT FUNCTION

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

The statement cannot be executed.

Correct the syntax and resubmit the statement. Refer to Chapter 4 of SQL Reference for information about the proper form for the operands of a COUNT function.

-410

THE FLOATING POINT LITERAL literal CONTAINS MORE THAN 30 CHARACTERS

The specified floating-point literal is more than 30 characters in length.  A floating-point literal has a maximum length of 30 characters.

The statement cannot be executed.

Correct the indicated literal.

-411

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

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.

The statement cannot be executed.

Either remove the reference to CURRENT SQLID or the reference to the remote object.

-412

Either remove the reference to CURRENT SQLID or the reference to the remote object.

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

The statement cannot be executed.

Correct the syntax of the SQL statement. 

-414

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

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

The statement cannot be executed.

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

-415

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

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.

The statement cannot be executed.

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.

-416

AN OPERAND OF A UNION CONTAINS A LONG STRING COLUMN

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.

The statement cannot be executed.

The implied function is not supported by DB2. 

-417

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

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.

The statement cannot be executed.

Correct the logic of the application program so that this syntax error does not occur. 

-418

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

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

The statement cannot be executed.

Correct the logic of the application program so that this error does not occur. 

-419

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

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 Chapter 3 of SQL Reference.

The statement cannot be executed.  No data was retrieved, updated, or deleted.

Examine the precision and scale of all columns that may have participated in a decimal division.  Note that an integer or small integer value may have been converted to decimal for this calculation.

-421

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

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

The statement cannot be executed.

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

-501

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

The application program attempted to either:  1.   FETCH using a cursor, or  2.   CLOSE a cursor  at a time when the specified cursor was not open.

The statement cannot be executed.

Check for a previous SQLCODE that may have closed  the cursor.  Commit and rollback operations close cursors.  SQL codes  -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 SQLCODE -501.  If no previous SQLCODEs have been issued, correct the logic of the  application program to ensure that the cursor is open at the time the  FETCH or CLOSE statement is executed.

-502

THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS ALREADY OPEN

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

The statement cannot be executed.  The cursor was  unchanged (that is, it was not 'reopened').

Correct logic in application program to CLOSE the CURSOR before the OPEN statement.

-503

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

The application program attempted to update (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.

The statement cannot be executed.  No data was updated in  the object table.

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.

-504

THE CURSOR NAME cursor-name IS NOT DEFINED

Cursor 'cursor-name' was not declared in the application  program before it was referenced.

The statement cannot be executed.

Check the application program for completeness and  for a possible spelling error in the cursor declaration.  The declaration  for a cursor must appear in an application program before statements that  reference the cursor.

-507

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

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.

The statement cannot be executed.  No update or delete was  performed.

Check for a previous SQLCODE that may have closed  the cursor.  SQL codes -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 SQLCODE -501.  Any updates  or deletes will receive this SQLCODE -507.  Correct the logic of the  application program to ensure that the specified cursor is in fact open at  the time the UPDATE or DELETE statement is executed.

-508

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

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.

The statement cannot be executed.  No data was updated or deleted.d

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.

-509

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

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.

The statement cannot be executed.  The update or delete  was not performed.

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.

-510

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

kkkkThe 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 if the application specifies limited block protocol during bind.

dThe statement cannot be executed.  No data was updated or deleted in the object table.

The requested UPDATE or DELETE cannot be performed. 

-511

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

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, if 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.

The statement cannot be executed.  The specified cursor  remains undefined in the application program.

Updates cannot be performed on the result table as  it is specified. 

-514

THE CURSOR cursor-name IS NOT IN A PREPARED STATE

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 either (1) has never been prepared, or (2) has been  invalidated by a COMMIT or ROLLBACK (or the IMS/VS or CICS equivalent).

The statement cannot be executed.

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), either do not issue COMMIT or  ROLLBACK until after you are finished using the cursor, or prepare the  statement again after the COMMIT or ROLLBACK.

-516

THE DESCRIBE STATEMENT DOES NOT IDENTIFY A PREPARED STATEMENT

An attempt was made to execute a DESCRIBE for a statement  that had not been successfully prepared beforehand.

The statement cannot be executed.

Correct the logic of the application program to  ensure that a statement is properly prepared before a DESCRIBE of the  statement is attempted.

-517

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

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.

The statement cannot be executed.

Verify that the statement-name is specified  correctly in the PREPARE statement and 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.

-518

THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT

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.

The statement cannot be executed.

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

-519

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

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

The statement cannot be executed.  The cursor was not  affected.

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.

-530

THE INSERT OR UPDATE VALUE OF FOREIGN KEY constraint name IS INVALID

An UPDATE or INSERT operation attempted to place a value in a foreign key of the object table; however, this value was not equal to some value of the primary key of the parent table.When a row is inserted into a dependent table, the insert value of a foreign key must be equal to the value of the primary key of some row of the parent table in the associated relationship.When the value of the foreign key is updated, the update value of a foreign key must be equal to the value of the primary key of some row of the parent table of the associated relationship.

The UPDATE or INSERT statement cannot be executed. The object table is unchanged.

Ensure that INSERT row for DB2 PARENT table is completed before INSERT row in CHILD table.

-531

THE PRIMARY KEY IN A PARENT ROW CANNOT BE UPDATED BECAUSE IT HAS ONE OR MORE DEPENDENT ROWS IN RELATIONSHIP constraint name

An UPDATE operation attempted to update a primary key in the specified row of the object table; however, the primary key in the specified row had dependent rows associated with it.The value of a primary key in a parent row cannot be updated if the parent row has any dependent rows.

The UPDATE statement cannot be executed.  The object table is unchanged.

Examine the primary key of the object table and the foreign key of the dependent table to determine if the value of the specified row of the primary key should be changed.  If this does not expose the problem, examine the contents of the object table and the dependent table to determine the cause of the problem.

-532

THE RELATIONSHIP constraint name RESTRICTS THE DELETION OF ROW WITH RID X'rid number'

A DELETE operation attempted to delete a specified parent row in the object table and all related descendent rows in the descendent tables.  However, a delete rule of RESTRICT was specified for one or more descendent tables.A row of the table cannot be deleted because it has a dependent in a relationship with a delete rule of RESTRICT or the deletion cascades to a row which is a dependent in a relationship with a delete rule of RESTRICT.

The DELETE statement cannot be executed.  The contents ofthe object table are unchanged

Change the program to DELETE CHILD table row before DELETE of row on PARENT table.

-533

INVALID MULTIPLE ROW INSERT

An INSERT operation with a subselect attempted to insert multiple rows into a self referencing table.The subselect of the INSERT operation should return no more than one row of data.

The INSERT statement cannot be executed.  The contents of the object table are unchanged.

Examine the search condition of the subselect to make sure that no more than one row of data is selected.

-534

THE PRIMARY KEY CANNOT BE UPDATED BECAUSE OF MULTIPLE ROW UPDATE

An UPDATE operation attempted to update a primary key on multiple rows of the object table.An UPDATE statement updating the primary key cannot be used to update more than one row of the object table.

The UPDATE statement cannot be executed.  The contents of the object table are unchanged.

Examine the search condition of the UPDATE statement to make sure that no more than one row of the object table is selected to be updated.

-535

INVALID UPDATE OR DELETE WITH WHERE CURRENT OF

An UPDATE operation with WHERE CURRENT OF cannot be used to update a primary key of the object table, or a DELETE operation with WHERE CURRENT OF cannot be used to delete from a self referencing table.

The UPDATE or DELETE statement cannot be executed.  The contents of the object table are unchanged.

Do not update a primary key with WHERE CURRENT OF in the UPDATE statement, or do not delete from a self referencing table with WHERE CURRENT OF in the DELETE statement.

-536

THE DELETE STATEMENT IS INVALID BECAUSE TABLE table name CAN BE AFFECTED BY THE OPERATION

A DELETE operation with the indicated table referenced in a subquery was attempted.If 'T' is the object table of the DELETE, the indicated table is one of the following: *   A dependent of 'T' in a relationship with a delete rule of CASCADE or SET NULL *   A dependent of another table in a relationship with a delete rule of CASCADE or SET NULL in which deletions from 'T' can cascade to that table.

The DELETE statement cannot be executed.  The contents of the object table are unchanged.

Do not attempt to reference a table in a subquery of a DELETE statement when the table can be affected by the DELETE statement.

-537

THE PRIMARY KEY CLAUSE, A FOREIGN KEY CLAUSE, OR A UNIQUE CLAUSE IDENTIFIES COLUMN column name MORE THAN ONCE

PRIMARY KEY, FOREIGN KEY, or UNIQUE can each be followed by a list of column names.  The statement violates the rule that no column name can appear more than once in any such list.

The statement cannot be executed.

Correct the statement to specify unique names for each column.

-538

FOREIGN KEY name DOES NOT CONFORM TO THE DESCRIPTION OF THE PRIMARY KEY OF TABLE table name

The code is used to report that the definition of the indicated foreign key does not conform to the description of the primary key of the indicated table. Either the keys do not have the same number of columns or their descriptions are not identical. The requirement for identical descriptions includes data type, length attribute, and field procedure.  The 'name' is the constraint name specified in the FOREIGN KEY clause or, if a constraint name was not specified, the first column name specified in the clause.

The statement cannot be executed.

Correct the statement so that the description of the foreign key conforms to that of the primary key of the indicated table.

-539

TABLE table name DOES NOT HAVE A PRIMARY KEY

The code is used to report that the indicated table, which is nominated as a parent in a FOREIGN KEY clause, cannot be defined as a parent because it does not have a primary key.

The statement cannot be executed

Correct the statement so that the table nominated as a parent in the FOREIGN KEY clause references a table with a primary key, or define a primary key for the table nominated as a parent.

-540

THE DEFINITION OF TABLE table name IS INCOMPLETE BECAUSE IT LACKS A PRIMARY INDEX OR A REQUIRED UNIQUE INDEX

The table named was defined with a PRIMARY KEY clause or a UNIQUE clause.  Its definition is incomplete, and it cannot be used until a unique index is defined for the primary key (the primary index) and for each set of columns in any UNIQUE clause (the required unique indexes). An attempt was made to use the table in a FOREIGN KEY clause or in a SQL manipulative statement.

The statement cannot be executed.

Define a primary index or a required unique index on the table before referencing it.

-541

FOREGIN KEY name IS INVALID BECAUSE IT IS A DUPLICATE REFERENTIAL CONSTRAINT

NA

NA

NA

-542

column name CANNOT BE A COLUMN OF A PRIMARY KEY OR A UNIQUE CONSTRAINT BECAUSE IT CAN CONTAIN NULL VALUES

The code is used to report that a column identified in a PRIMARY KEY or a UNIQUE constraint clause is defined to allow null values.

The statement cannot be executed

Correct the statement and rerun it.

-545

THE REQUESTED OPERATION IS NOT ALLOWED BECAUSE A ROW DOES NOT SATISFY THE CHECK CONSTRAINT check-constraint

Check constraint violations occurred on an INSERT, UPDATE, or MERGE statement. The resulting row violated the check constraint definition on the table.

The INSERT, UPDATE, or MERGE statement cannot be executed. The contents of the table are unchanged.

Examine the data and the check constraint definition in the SYSIBM.SYSCHECKS catalog table to determine why the INSERT, MERGE, DELETE, or UPDATE statement was rejected. The data must be changed to satisfy the check constraint.

-551

auth id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object name

Not authorized to access DB2.

Authorization ID 'auth id' attempted to perform 'operation' on object 'object name' without having been granted the proper authority to do so.  This error might also occur if the object is a read only view(for INSERT, DELETE, or UPDATE), or if 'authorization ID' is trying to create a table or view with an authorization ID other than its own.  Only if your authorization ID is SYSADM, DBADM, or DBCTRL can you create a table from an 'authorization ID' other than your own.  Only if your authorization ID is a SYSADM can you create a view from an 'authorization ID' other than your own.If this error occurs while DB2 is creating or altering a table involving referential constraints, this code reports that the user does not have the necessary ALTER privilege to perform a FOREIGN KEY, DROP FOREIGN KEY, or DROP PRIMARY KEY operation.  The 'object name' identifies the object table of the CREATE or ALTER TABLE statement, not the table for which the user lacks the ALTER privilege.

The statement cannot be executed.

Ensure that 'authorization ID' has been granted the authority to perform the desired operation, that the 'object name' exists, and that 'authorization ID' is not trying to create a table with a different authorization ID. Contact the DBA to GRANT the needed privilege.

-552

auth id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation

Authorization ID 'auth id' has attempted to perform the specified 'operation' without having been granted the authority to do so.

The statement cannot be executed.Installation Action: Check for an attempted authorization violation.

Ensure that the authorization ID has been granted the authority necessary to perform the desired operation.

-554

AN AUTHORIZATION ID CANNOT GRANT A PRIVILEGE TO ITSELF

An authorization ID has attempted to execute a GRANT statement in which that ID itself appears as one of the entries in the list of 'grantee' authorization IDs. An authorization ID cannot GRANT a privilege to itself.

The statement cannot be executed.  No privileges were granted.

Remove the reference to the authorization ID that issued the GRANT statement from the list of grantee authorization IDs.

-555

AN AUTHORIZATION ID CANNOT REVOKE A PRIVILEGE FROM ITSELF

An authorization ID has attempted to execute a REVOKE statement in which that ID itself appears as one of the entries in the list of authorization IDs to be revoked.An authorization ID cannot REVOKE its own privilege.

The statement cannot be executed.  No privileges were revoked.

Take one of the following actions and reissue the REVOKE statement:

Remove the authorization ID that issues the REVOKE statement from the list of authorization IDs to be revoked.
Issue the REVOKE statement from a different authorization ID.

-556

A PRIVILEGE CANNOT BE REVOKED BECAUSE GRANT IS TO PUBLIC

The REVOKE statement was not successful because of one of the following reasons: *   The revokee, 'authid2', does not possess the 'privilege' *   The revoker, 'authid1', did not explicitly grant the privilege to 'authid2'  *   'Authid2' is the owner of the specified object.An authorization ID can REVOKE only the 'privileges' that it has explicitly granted to other authorization IDs, unless the authorization ID has SYSADM or SYSCTRL authority and specifies the BY clause.  No authorization ID, not even SYSADM, can revoke 'privileges' on an object from the object owner.

The statement cannot be executed.  No 'privileges' were revoked from any authorization ID.

Check the appropriate authorization catalog tables to verify that the revokee 'authid2' possesses the privilege to be revoked.  Queries can be made with GRANTEE = 'authid2' and the privilege column not = blanks.  Correct and re issue the REVOKE statement.Note:   If a user holding SYSADM or SYSCTRL authority receives this SQLCODE, the 'BY' clause may have been omitted from the REVOKE statement.

-557

INCONSISTENT GRANT/REVOKE KEYWORD keyword.  PERMITTED KEYWORDS ARE keyword list

The GRANT or REVOKE statement contains a syntax or spelling error at or before the specified 'keyword'.  As an aid to the programmer,'keyword list' provides a list of the keywords that would be permitted in this context. Alternatively: *   The mixture of privileges specified on the GRANT or REVOKE statement is not permitted.  The privileges must all be of one type, and consistent with the form of the GRANT or REVOKE statement. *   REVOKE UPDATE (column list) is not permitted; only REVOKE UPDATE is valid.

The statement cannot be executed.

Correct the syntax of the GRANT or REVOKE statement.

558

THE WITH GRANT OPTION IS IGNORED

The GRANT statement contained one of the following situations: PUBLIC was within the list of 'grantee' authorization IDs.*   BINDAGENT privilege was being granted.*   ANY package privilege on collection-id.* was being granted.The WITH GRANT option may not be used in the above situations.

The offending privilege(s) in the authorization specification are granted without the GRANT option.  If the grantee is PUBLIC, all the privileges in the authorization specification are granted without the GRANT option.

NA

-559

ALL AUTHORIZATION FUNCTIONS HAVE BEEN DISABLED

The authorization mechanism has been disabled in the DB2 subsystem.  Consequently, GRANT and REVOKE statements are ignored.

The statement cannot be executed.  No privileges were granted or revoked.

Do not attempt to execute GRANT or REVOKE statements unless and until the authorization mechanism is enabled in the DB2 subsystem.

+560

THE WITH GRANT OPTION IS IGNORED BECAUSE GRANT IS TO PUBLIC

NA

NA

NA

-601

THE NAME OF THE OBJECT TO BE CREATED IS IDENTICAL TO THE EXISTING NAME name OF THE OBJECT TYPE obj type

The CREATE statement sought to create an object 'name' of type 'obj type' when there is already an object of that type with the same name defined in the DB2 subsystem.  If 'obj type' is constraint, the 'name' was specified in the FOREIGN KEY clause of either a CREATE or ALTER TABLE statement.

The CREATE or ALTER statement cannot be executed.  No new object was created, and the existing object was not altered or modified

Either the existing object must be dropped or another name must be chosen for the new object.  If 'obj type' is data set, an IDCAMS DELETE of the data set must be done before the CREATE is retried. 

-602

TOO MANY COLUMNS SPECIFIED IN A CREATE INDEX

The number of columns specified in the CREATE INDEX statement exceeds 64, the maximum permitted by DB2.

The statement cannot be executed.  The specified index was not created.

The index definition must be modified to conform to the system imposed column limit of 16.

-603

A UNIQUE INDEX CANNOT BE CREATED BECAUSE THE TABLE CONTAINS ROWS WHICH ARE DUPLICATES WITH RESPECT TO THE VALUES OF THE IDENTIFIED COLUMNS

The index defined in the CREATE INDEX statement could not be created as unique because the specified table already contains rows that are duplicates with respect to the values of the identified columns.

The statement cannot be executed.

Examine the data to ascertain whether or not the duplicate data is valid.  Alternatively, consider creating a nonunique index.

-604

A COLUMN DEFINITION SPECIFIES AN INVALID LENGTH, PRECISION, OR SCALE ATTRIBUTE

A column definition in the CREATE or ALTER TABLE statement contains an invalid length, precision, or scale attribute specification. Alternatively, the specification of data type may be incorrect or invalid.

The statement cannot be executed.  The specified table was not created or altered.

Correct the syntax, and resubmit the statement.

-607

OPERATION operation IS NOT DEFINED FOR SYSTEM TABLES

The 'operation' specified in the SQL statement cannot be performed on system tables.  You cannot insert into or delete from a DB2 catalog table.

The statement cannot be executed.

Do not attempt to insert into or delete from a catalog table.

-612

column name IS A DUPLICATE COLUMN NAME

The CREATE INDEX or CREATE TABLE or CREATE VIEW statement specifies the same 'column name' for two (or more) columns of the index,table, or view.  Column names must be unique within an index, a table, or a view.

The statement cannot be executed.  The specified index,table, or view was not created.

Correct the CREATE statement to specify unique names for each of the columns of the index, table, or view.

-613

THE PRIMARY KEY OR A UNIQUE CONSTRAINT IS TOO LONG OR HAS TOO MANY COLUMNS

In a list of columns following either PRIMARY KEY or UNIQUE,the number of columns is greater than 64 or the sum of the column length attributes is greater than the number allowed for the type of index.

The CREATE or ALTER statement cannot be executed. The specified table cannot be created or altered.

Change the table definition to keep within the prescribed limits.

-614

THE INDEX CANNOT BE CREATED BECAUSE THE SUM OF THE INTERNAL LENGTHS OF THE IDENTIFIED COLUMNS IS GREATER THAN THE ALLOWABLE MAXIMUM

The index could not be created because the sum of the internal lengths of the key columns would exceed the allowable maximum. The maximum length permitted is a function of whether the index is partitioned or not, whether the index is unique or not, and the number of subpages of the index. For a nonpartitioned index with less than 8 subpages (either unique or not), the maximum permitted key length is 254.

The statement cannot be executed.  The specified index was not created.

The definition for the index must be modified (possibly by eliminating one or more key columns) to reduce the length of the key to the permitted maximum. 

-616

obj type1 obj name1 CANNOT BE DROPPED BECAUSE IT IS REFERENCED BY obj type2 obj name2

Some types of objects cannot be dropped if there are other objects which are dependent upon them.  For example, a storage group cannot be dropped if there are one or more existing table spaces that use that storage group. Execution of the specified DROP statement would drop object 'obj name1' of type 'obj type1' on which object 'obj name2' of type 'obj type2' is dependent.

The statement cannot be executed.  The specified object was not dropped.

Verify that the object specified in the DROP statement was indeed the object to be dropped.  If so, all the existing objects that have a dependency on that object must be dropped first.

-618

OPERATION operation IS NOT ALLOWED ON SYSTEM DATABASES

System databases cannot be the object of certain types of operations.  The attempted 'operation' cannot be performed on system data bases.

The statement cannot be executed.  No changes were made to the specified system database.

Do not attempt to perform the requested operation on system databases.

-619

OPERATION DISALLOWED BECAUSE THE WORKFILE DATABASE IS NOT STOPPED

The statements CREATE, ALTER or DROP for a table space in the workfile database (DSNDB07) cannot be processed unless the workfile database is stopped (use the STOP statement).

The statement cannot be executed.

Issue the  DISPLAY DATABASE(DSNDB07) command to verify that the workfile database is stopped before resubmitting the statement.

-620

KEYWORD keyword IN stmt type STATEMENT IS NOT PERMITTED FOR A TABLE SPACE IN THE WORK FILE DATABASE

The specified keyword in the SQL statement is not allowed for a table space in the workfile database (DSNDB07).

The statement cannot be executed.

Correct and resubmit the statement.

-621

DATABASE CANNOT BE CREATED BECAUSE ASSIGNED DBID dbid IS THE SAME ONE AS ASSIGNED TO DATABASE dbname

The current database being created was assigned a DBID of 'dbid', which is identical to the DBID assigned to database 'database name'.  An inconsistency exists between the DB2 catalog and directory.

The statement cannot be executed. No new object was created, and the existing object was not altered or modified.

Notify the system programmer. The inconsistency must be corrected before CREATE DATABASE will be successful. System  

-623

A CLUSTERING INDEX ALREADY EXISTS ON TABLE table name

The CREATE INDEX statement would create a second cluster index on the specified table.  A given table can have only one cluster index.

The statement cannot be executed.

Check to determine the identity and validity of the existing cluster index on the object table.  Alternatively, consider creating the index without the CLUSTER attribute.

-624

TABLE table name ALREADY HAS A PRIMARY KEY

The code is used to report that a primary key cannot be defined in an ALTER TABLE statement because the indicated table already has a primary key.

The statement cannot be run.

A table cannot have more than one primary key.

-625

TABLE table name DOES NOT HAVE AN INDEX TO ENFORCE THE UNIQUENESS OF THE PRIMARY KEY

The code is used to report that an ALTER TABLE statement is invalid because the table does not have a unique index with a key that is identical to the nominated PRIMARY KEY.

The statement cannot be executed.

Make sure the key list specified on the ALTER TABLE statement identifies an existing unique index of the table.

+625

THE DEFINITION OF TABLE table-name HAS BEEN CHANGED TO INCOMPLETE

This warning code is used to report that the definition of the indicated table is incomplete because DROP INDEX was used to drop its primary index.  The table is not generally available until a new primary index is created.

The definition of the table is marked as incomplete.Subsequent references to the table in DDL or DML statements will cause SQL code -540 to be returned.  Attempts to run already bound plans referencing the table will result in SQL -904 or -923 with resource unavailable reason code 00C9009F.  CHECK and LOAD utilities involving the table are not allowed.  If they are used, you will receive message DSNU305I or DSNT500I with reason code 00C9009F.  Utilities other than CHECK and LOAD are allowed.

Recreate the primary index using CREATE INDEX.  This will remove the table from INCOMPLETE DEFINITION status. Note:  The reason that a primary index may need to be dropped and recreated is to change the STOGROUP or SUBPAGES parameters of the index.

-626

THE ALTER STATEMENT IS NOT EXECUTABLE BECAUSE THE PAGESET IS NOT STOPPED

An ALTER statement specifies a USING, PRIQTY, SECQTY, or ERASE clause, but the pageset is not stopped.

The SQL statement cannot be executed.

Stop the pageset before resubmitting the statement.

-627

THE ALTER STATEMENT IS INVALID BECAUSE THE PAGESET HAS USER MANAGED DATA SETS

This code is used if a PRIQTY, SECQTY, or ERASE clause is specified, USING STOGROUP is not specified, and the pageset has user managed data sets.

The SQL statement cannot be executed

Verify that the correct table or partition is specified. The primary and secondary space allocation of a user managed data set cannot be altered by means of an ALTER statement.

-628

A SEGMENTED TABLESPACE MAY NOT BE PARTITIONED

The CREATE TABLESPACE statement contains both the SEGSIZE and NUMPARTS specification, but they are mutually exclusive.

The statement cannot not be executed.

If the table space has to be partitioned then eliminate the SEGSIZE specification.

-629

SET NULL CANNOT BE SPECIFIED BECAUSE FOREIGN KEY name CANNOT CONTAIN NULL VALUES

The code is used to report that the SET NULL option of the indicated FOREIGN KEY clause is invalid because no column of the key allows null values.  The 'name' is the constraint name specified in the FOREIGN KEY clause or, if a constraint name was not specified, the first column name specified in the clause.

The statement cannot be run.

Change a column of the key to allow null values or change the delete rule.

-631

FOREIGN KEY name IS TOO LONG OR HAS TOO MANY COLUMNS

This code is used to report that the sum of the length attributes of the columns identified in the indicated FOREIGN KEY clause is greater than 254 bytes or the number of columns identified is greater than 64.  The 'name' is the constraint name specified in the FOREIGN KEY clause or, if a constraint name was not specified, the first column name specified in the clause

The statement cannot be executed.

The table definition must be modified to conform to the system imposed limit of the sum of the length attributes of the columns identified in the PRIMARY KEY clause.

-632

THE DELETE RULE MUST BE delete rule

The code is used to report that the 'delete rule'specified in a FOREIGN KEY clause of the ALTER TABLE statement is invalid.  The indicated 'delete rule' is required because:*   A self referencing constraint must have a 'delete rule' of CASCADE. *   The relationship would cause the table to be delete connected to the same table through multiple paths and such relationships must have the same 'delete rule'.

The statement cannot be executed.

The statement cannot be executed.

-633

THE DELETE RULE MUST BE delete rule

The code is used to report that the 'delete rule'specified in a FOREIGN KEY clause of the ALTER TABLE statement is invalid.  The indicated 'delete rule' is required because:*   A self referencing constraint must have a 'delete rule' of CASCADE. *   The relationship would cause the table to be delete connected to the same table through multiple paths and such relationships must have the same 'delete rule'.

The statement cannot be executed.

Change the 'delete rule' in the FOREIGN KEY clause.

-634

THE DELETE RULE MUST NOT BE CASCADE

The code is used to report that the CASCADE delete rule specified in the FOREIGN KEY clause of an ALTER TABLE statement is invalid because: *   The relationship would form a cycle that would cause a table to be delete connected to itself. *   The relationship would cause another table to be delete connected to the same table through multiple paths with different delete rules or with a delete rule equal to SET NULL.

The statement cannot be executed.

Change the delete rule.

-635

THE DELETE RULES CANNOT BE DIFFERENT OR CANNOT BE SET NULL

The code is used to report that the delete rules specified in two FOREIGN KEY clauses of the CREATE TABLE statement are invalid because the table would be delete connected to the same table through multiple paths involving relationships with different delete rules or with delete rules of SET NULL.

The statement cannot be executed.

Change the delete rule.

-636

THE PARTITIONING KEYS ARE NOT SPECIFIED IN ASCENDING OR DESCENDING ORDER

In the CREATE INDEX statement for the CLUSTER index for a partitioned table (that is, a table residing in a partitioned table space), the partitioning key values specified in the limit key value specifications were not in either ascending or descending order.

The statement cannot be executed.  The specified cluster index was not created.

Correct the limit key value specifications in the CREATE INDEX statement so that the limit key values for successive partitions are in strictly ascending or descending order.

-637

DUPLICATE keyword KEYWORD

The SQL statement contains a duplicate specification for the keyword 'keyword'.

The statement cannot be executed.

Correct the statement to eliminate all but one specification for the keyword.

-638

TABLE table name CANNOT BE CREATED BECAUSE COLUMN DEFINITION IS MISSING

The CREATE TABLE statement does not contain any column definition.

The SQL statement cannot be executed.

Add column definition to the statement.

-644

INVALID VALUE SPECIFIED FOR KEYWORD keyword IN stmt type STATEMENT

The value specified for the 'keyword' parameter in the 'stmt type' SQL statement is not a permitted value.

The SQL statement cannot be executed.

Correct the statement. 

-646

TABLE table name CANNOT BE CREATED IN PARTITIONED/DEFAULT TABLESPACE tspace name BECAUSE IT ALREADY CONTAINS A TABLE

The table space specified in a CREATE TABLE statement is a partitioned or default table space in which an existing table already resides.  Only one table may reside in a partitioned or default table space.

The statement cannot be executed.  The specified table was not created.

Verify that the correct table space was specified in the CREATE statement.  Do not attempt to create more than one table in a partitioned or default table space.

-647

BUFFERPOOL bp name CANNOT BE SPECIFIED BECAUSE IT HAS NOT BEEN ACTIVATED

The buffer pool specified in a CREATE or ALTER statement for a table space or index (index space) is not activated (that is, the buffer pool was not activated during the start up of DB2). Table spaces and indexes (index spaces) can only be assigned (or reassigned) to buffer pools that are currently activated.

The statement cannot be executed.  The specified table space or index(space) was not created or altered.

Verify that the proper buffer pool was specified in the CREATE or ALTER statement.  If so, ensure that the buffer pool is activated before resubmitting the statement for execution.

-652

VIOLATION OF INSTALLATION DEFINED EDIT OR VALIDATION PROCEDURE proc name

The result of the SQL statement has been rejected by the installation defined edit or validation procedure 'proc name' for the object table.

The statement cannot be executed.  The contents of the object table were not modified.

Determine the requirements imposed by the edit or validation procedure for inserts and updates of the object table.

-653

TABLE table name IN PARTITIONED TABLESPACE tspace name IS NOT AVAILABLE BECAUSE ITS PARTITIONED INDEX HAS NOT BEEN CREATED

An attempt has been made to insert or manipulate data in or create a view on a partitioned table (that is, a table residing in a partitioned table space) before the partitioned index for that table has been created.A table residing in a partitioned table space cannot be referenced in any SQL manipulative statement or a CREATE VIEW statement before the partitioned index for that table has been created.

The statement cannot be executed.

Verify that the correct table was specified in the statement. If so, ensure that the partitioned index for the table has been created successfully before attempting to execute any SQL manipulative statements that reference that table.

-660

INDEX index name CANNOT BE CREATED ON PARTITIONED TABLESPACE tspace name BECAUSE KEY LIMITS ARE NOT SPECIFIED

The CREATE INDEX statement attempted to create a cluster index for a partitioned table (that is, a table residing in a partitioned table space) without specifying limit key values for the partitions of the table space.

The statement cannot be executed.  The specified cluster index was not created.

Verify that the correct table was specified in the CREATE INDEX statement. If so, the definition for the partitioned table space must be examined so that a proper definition for the cluster index for the table may be constructed.

-661

INDEX index name CANNOT BE CREATED ON PARTITIONED TABLESPACE tspace name BECAUSE THE NUMBER OF PART SPECIFICATIONS IS NOT EQUAL TO THE NUMBER OF PARTITIONS OF THE TABLESPACE

The CREATE INDEX statement for the cluster index on a partitioned table (that is, a table residing in a partitioned table space) does not contain the same number of PART specifications as there are partitions in the table space. The definition for the cluster index for a partitioned table must contain exactly as many PART specifications as there are partitions in the table space in which that table resides. Also, the part numbers must be valid and unique.

The statement cannot be executed.  The specified cluster index was not created.

Examine the definition of the partitioned table space to determine how many partitions have been specified, and then correct the syntax of the CREATE INDEX statement to provide the proper number of PART specifications. 

-662

A PARTITIONED INDEX CANNOT BE CREATED ON A NON PARTITIONED TABLESPACE tspace name

The CREATE INDEX statement contains PART specifications, but the specified object table is not partitioned (that is, does not reside in a partitioned table space).

The statement cannot be executed.  The specified index was not created.

Verify that the proper object table was specified in the statement. 

-663

THE NUMBER OF KEY LIMIT VALUES IS EITHER ZERO, OR GREATER THAN THE NUMBER OF COLUMNS IN THE KEY OF INDEX index name

The number of limit key value specifications provided in at least one of the PART specifications of the CREATE INDEX statement is either zero or greater than the number of columns in the index key.

The statement cannot be executed.  The specified index was not created.

Correct the statement so that each PART specification contains exactly the same number of limit key value specifications as there are columns in the index key.

-664

INTERNAL LENGTH OF LIMIT-KEY FIELDS FRO PARTITIONED INDEX inded-name EXCEEDS LENGH IMPOSED BY INDEX MGR.

NA

NA

NA

-665

THE PART CLAUSE OF AN ALTER STATEMENT IS OMITTED OR INVALID

The ALTER statement is invalid for one of the following reasons: *   The table space or index is not partitioned and the PART clause is specified. *   The table space or index is partitioned, a partition attribute (FREEPAGE or PCTFREE) is specified, and the PART clause is not specified. *   The integer specified in the PART clause does not identify a partition of the table space or index. *   A USING, PRIQTY, SECQTY, or ERASE clause is used to alter storage attributes, but the partition is not specified.

The statement cannot be executed.  The specified index or table space was not altered.

Determine whether the table space or index you want to alter is partitioned.  If it is partitioned, specify a PART clause that identifies the partition you want to alter.  If it is not partitioned, do not specify the PART clause.

-666

stmt verb object CANNOT BE EXECUTED BECAUSE function IS IN PROGRESS

The SQL statement could not be executed because the named function was executing at the time.  In the message, 'stmt verb' is the type of data definition language (DDL) statement, 'object' is the DB2 object type, and 'function' is a utility, the governor, or the distributed data facility (DDF). If the object is part of the communications database, it cannot be dropped while the DDF is active

The statement was not executed.

If the function is a utility, wait for the function to complete or stop and then resubmit the statement for execution.  If the function is the governor, the statement cannot be executed until the resource limit facility is stopped or switched to a different resource limit control table.  If the function is the DDF, the facility must be stopped before the object can be dropped.

-667

THE CLUSTERING INDEX FOR A PARTITIONED TABLESPACE CANNOT BE EXPLICITLY DROPPED

The DROP INDEX statement attempted to drop the cluster index for a table residing in a partitioned table space. The cluster index for such a table cannot be dropped explicitly with the DROP INDEX statement.

The statement cannot be executed.  The specified index was not dropped.

The statement cannot be executed.  The specified index was not dropped.

-668

THE COLUMN CANNOT BE ADDED TO THE TABLE BECAUSE THE TABLE HAS AN EDIT PROCEDURE

The ALTER TABLE statement attempted to add a column to a table that has an edit procedure.  If a table has an edit procedure, no columns can be added to it.

The statement cannot be executed.  The specified table was  not altered.

Verify that the correct table was specified in the ALTER statement.  Do not attempt to ALTER the definition of a table that has an installation written edit procedure associated with it.

-669

A TABLE IN A PARTITIONED TABLESPACE CANNOT BE EXPLICITLY DROPPED

The DROP TABLE statement attempted to drop a table that resides in a partitioned table space.

The statement cannot be executed.  The specified table was not dropped.

The table of a partitioned table space can only be dropped implicitly when the table space itself is dropped.

-670

THE RECORD LENGTH OF THE TABLE EXCEEDS THE PAGE SIZE LIMIT

The row length for a table cannot exceed the page size of the table space in which that table resides (or is to reside).  The page size of the table space is determined by the buffer pool used by that table space. One of three conditions may occur:  *   As defined in a CREATE TABLE statement, the row length for the table would exceed the page size of the specified (or default) table space. *   In the case of an ALTER TABLE statement, addition of the specified column would cause the row length of the table to exceed the page size of the table space. *   The row length in the result of a join exceeds the page size of a workfile table space.

The statement cannot be executed.  The object table was not created or altered

In the case of CREATE TABLE, either (1) the row length of the table must be reduced (by eliminating or reducing the lengths of one or more of the columns), or (2) the table must be assigned to a table space that uses the 32K byte buffer pool (assuming that the row length of the table does not exceed that page size limit). In the case of ALTER TABLE, either (1) the length of the column to be added to the table must be reduced or, (2) if the row length of the table is already at the maximum, the table cannot be altered to add any additional columns. In the case of a row length that exceeds the page size of a workfile table space, eliminate columns from the result of the join.

-671

THE BUFFERPOOL ATTRIBUTE OF THE TABLESPACE CANNOT BE ALTERED AS SPECIFIED BECAUSE IT WOULD CHANGE THE PAGE SIZE OF THE TABLESPACE

The change to the buffer pool attribute for the table space specified in the ALTER TABLESPACE statement would change the page size of the table space  either from 4K to 32K bytes, or vice versa. Use of the ALTER TABLESPACE statement to change the page size of a table space is not permitted.

The statement cannot be executed.  The table space definition was not altered.

If the table space uses one of the 4K byte buffer pools (for example, BP0, BP1, or BP2), it can be reassigned to one of the other 4K byte buffer pools (but not buffer pool BP32K).  If, however, it is assigned to buffer pool BP32K, the buffer pool assignment cannot be subsequently altered.

-676

A 32K PAGE BUFFERPOOL MAY NOT BE USED FOR AN INDEX

A buffer pool having a 32K byte page size was specified in the CREATE INDEX statement.  Only 4K byte buffer pools (that is, BP0, BP1,and BP2) can be specified for indexes.

The statement cannot be executed.  The specified index was not created.

Correct the statement to specify a 4K byte buffer pool. 

-677

INSUFFICIENT VIRTUAL STORAGE FOR BUFFERPOOL EXPANSION

An attempt to either open (create) or expand a buffer pool has failed because insufficient virtual storage was available.This error may occur under either of two circumstances: *   An attempt to create a buffer pool while opening a table space or index(space), or  *   An attempt to expand a buffer pool from its current size to its maximum size.

The statement cannot be executed.

If this error should occur during interactive execution of an SQL statement or execution of an application program,    installation administration should be notified. Installation Action: It may be necessary to reexamine the buffer pool storage strategy. One of the following messages has also been sent to the MVS console: DSNB601I, DSNB603I, or DSNB605I.

-678

THE LITERAL literal SPECIFIED FOR THE INDEX LIMIT KEY MUST CONFORM TO THE DATA TYPE data type OF THE CORRESPONDING COLUMN column name

The index limit key value 'literal' has been specified incorrectly in the CREATE INDEX statement. Limit key value specifications must conform to the data type of the corresponding index key column.  In this case, the 'literal' must be of data type 'data type' to conform to the data type of column 'column name'.

The statement cannot be executed.  The specified index was not created.

Correct the statement so that each limit key value literal is of precisely the same data type as that of the corresponding index key column.

-679

THE OBJECT name CANNOT BE CREATED BECAUSE A DROP IS PENDING ON THE OBJECT

The application program has executed a DROP for the specified object, and then tried to re create an object with the same name`(and of the same type) before the DROP was completed.

The statement cannot be executed.  The specified object was not created.

The logic of the application program must be modified to issue a COMMIT (or the IMS/VS or CICS equivalent) between the DROP and CREATE statements.

-680

TOO MANY COLUMNS SPECIFIED FOR A TABLE

The maximum number of columns permitted per table is 750.

The statement cannot be executed.

Do not attempt to CREATE or ALTER a table to contain more than 750 columns.  Create separate tables as required to hold the additional information.

-681

COLUMN column name IN VIOLATION OF INSTALLATION DEFINED FIELD PROCEDURE.  RT: return code, RS: reason code, MSG: message token

An installation field procedure returned an error for 'column name'.  The 'reason code' and 'message token' are defined by the field procedure.  They may give additional information to help determine the cause of the problem.  Return code    Error  4              Invalid value on encode or decode or invalid column description on define  8              Invalid parameter value 12             Field procedure error on any function Use 'reason code' and 'message token' for additional information.

The statement cannot be executed.

If it is not a field procedure error, determine the requirements imposed by the field procedure.  If it is a field procedure error, examine the field procedure.

-682

FIELD PROCEDURE procedure name COULD NOT BE LOADED

The field procedure 'procedure name' cannot be loaded

The statement cannot be executed.

The application should either commit or roll back to previous COMMIT.  Then, in general, the application should terminate.

-683

INVALID COLUMN TYPE FOR FIELDPROC, BIT DATA, SBCS DATA, OR MIXED DATA OPTION, column name

The FIELDPROC, BIT DATA, SBCS DATA, or MIXED DATA option was specified for column 'column name'.  This is invalid.  Data subtypes can only be specified for character string columns; and field procedures can only be specified for string columns that allow nulls or have no default value.

The statement cannot be executed.

Correct the SQL statement.

-683

INVALID COLUMN TYPE FOR FIELDPROC, BIT DATA, SBCS DATA, OR MIXED DATA OPTION, column name

The FIELDPROC, BIT DATA, SBCS DATA, or MIXED DATA option was specified for column 'column name'.  This is invalid.  Data subtypes can only be specified for character string columns; and field procedures can only be specified for string columns that allow nulls or have no default value.

The statement cannot be executed.

Correct the SQL statement.

-684

THE LENGTH OF LITERAL LIST BEGINNING string IS TOO LONG

The length of the literal list beginning with 'string', excluding insignificant blanks and delimiting parentheses is greater than 254.

The statement cannot be executed.

Correct the SQL statement.

-685

INVALID FIELD TYPE, column name

The field description returned by the field procedure is invalid.  The data type code denotes a long string or has an invalid value.

The statement cannot be executed.

Correct the field procedure so that it returns a valid data type code.

-686

COLUMN DEFINED WITH A FIELD PROCEDURE CAN NOT COMPARE WITH ANOTHER COLUMN WITH DIFFERENT FIELD PROCEDURE

The columns specified are not compatible.  Different field procedures are specified, or only one field procedure is specified.

The statement cannot be executed.

Correct the SQL statement. 

-687

FIELD TYPES INCOMPARABLE

One column cannot be compared with another column that has incompatible field types.

The statement cannot be executed.

Correct the SQL statement. 

-688

INCORRECT DATA RETURNED FROM FIELD PROCEDURE, column name, msgno

Unexpected data returned from field procedure for column 'column name'.  For more information see 'msgno'.

The statement cannot be executed.

Correct the field procedure so that it returns values that are consistent with their descriptions.

-802

EXCEPTION ERROR 'exception type' HAS OCCURRED DURING 'operation type' OPERATION ON 'data type' DATA, POSITION 'position number'

The processing of a SQL arithmetic function or arithmetic expression that was either in the SELECT list of a SQL select statement,in the search condition of a SELECT, UPDATE, or DELETE statement, during the evaluation of a column function, or in the SET clause of the UPDATE statement has encountered an exception error, possibly indicated by 'exception type'. Possible values for 'exception type' are FIXED POINT OVERFLOW, DECIMAL OVERFLOW, ZERO DIVIDE, DIVIDE EXCEPTION, or EXPONENT OVERFLOW. 'data type' may indicate the data types of the items being manipulated, and 'operation type' may indicate the arithmetic operation that was being performed at the time of the error.  If the error was in the SELECT list of an outer SELECT statement then 'position number' may indicate the position of the expression in the select list.  The possible data types are INTEGER, SMALLINT, DECIMAL, and FLOAT.

The statement cannot be executed.  In the case of an INSERT or UPDATE statement, no data is updated or deleted.  If the statement was a cursor controlled FETCH then the CURSOR will remain open, else if it was a cursor controlled OPEN then the CURSOR will remain closed.

Examine the SQL statement to see if the cause (or the likely 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 being processed at the time the error occurred.If the arithmetic expression in error was within the SELECT list of the outer SELECT statement then it is advisable to include an indicator variable for all expressions in the SELECT list.  This will enable processing to continue such that non error column and expression values may be returned. See the explanation of SQLCODE  405 for allowed ranges of numeric data types.

+802

EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number

The exception error 'exception-type' occurred while doing an ADDITION, SUBTRACTION, MULTIPLICATION, DIVISION, or NEGATION operation on a field whose 'data-type' is DECIMAL, FLOAT, SMALLINT, or INTEGER.  The error occurred while processing an arithmetic expression in the SELECT list of an outer SELECT statement, and the position in the select list is denoted by 'position-number'.  The possible exception types are FIXED POINT OVERFLOW, DECIMAL OVERFLOW, DIVIDE EXCEPTION, EXPONENT OVERFLOW, and ZERO DIVIDE.  The data type displayed in the message may indicate the data type of the temporary internal copy of the data, which may differ from the actual column or literal data type due to conversions by DB2.A fixed point overflow can occur during any arithmetic operation on either INTEGER or SMALLINT fields.A decimal overflow exception can occur when one or more non-zero digits are lost because the destination field in any decimal operation is too short to contain the result.

For each expression in error the indicator variable is set to negative two (-2) to indicate a null value returned.  The data variable is unchanged.  Execution of the statement continues with all non-error columns and expressions of the outer SELECT list being returned.  If the statement is cursor controlled then the CURSOR will remain open.

Examine the expression for which the warning occurred to see if the cause (or the likely 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 being processed at the time the error occurred. Check arithmetic operation for divide by zero or result to exceed size of host variable.

-803

AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS.  RID OF EXISTING ROW IS X'rid'

Duplicate key on insert or update

The table that is the object of the INSERT or UPDATE operation is constrained (by UNIQUE INDEX in the INDEX SPACE 'indexspace name') to have unique values in certain columns.  Completion of the requested INSERT or UPDATE would result in duplicate values occurring in row 'rid'.If a view is the object of the INSERT or UPDATE statement, the table that defines the view is constrained.  The update might also be caused by a DELETE operation of a parent row that cascades to a dependent row with a delete rule of SET NULL.

The INSERT, UPDATE, or DELETE statement cannot be executed.  The object table is unchanged.

Examine the definitions for UNIQUE INDEX in the INDEX SPACE 'indexspace name' to determine the uniqueness constraint imposed.  Please refer to SYSIBM.SYSINDEXES for the 'indexspace name' and the associated 'index name'. For an UPDATE statement, verify that the specified operation is consistent with the uniqueness constraint.  If this does not indicate the error, examine the object table to determine the cause of the problem.For an INSERT statement, examine the object table to determine which values violate the uniqueness constraint.  If the INSERT statement contains a subquery, match the contents of the table addressed by the subquery and the contents of the object table to determine the cause of the problem. For a DELETE statement, examine the index key columns in the table that defines the index.  These columns contain a foreign key, which when set NULL on a cascade delete from the object table, causes the duplicate values.

-804

AN ERROR WAS FOUND IN THE APPLICATION PROGRAM INPUT PARAMETERS FOR THE SQL STATEMENT

The call parameter list or the SQLDA is invalid.  One of the following conditions exist: *   The call parameter list, which is created by the precompiler, may be invalid if the application programmer has modified the output of the precompiler, used a variable name beginning with 'SQL' in the application program, or overwritten the call parameter list in some other way. *   The SQLDA, which is created by the application program, has an invalid length or may contain an invalid data type or data length.*   The value of SQLDABC is not consistent with the value of SQLD.

The statement cannot be executed.

Examine the application program for any of the errors noted under the explanation above.  Note that, in general, the application programmer should not attempt to modify the output of the precompiler.

-805

PROGRAM NAME location name.collection id.program name.consistency token NOT FOUND IN PLAN plan name

DBRM or package not found in plan

An attempt was made to execute the application program 'location name.collection id.program name.consistency token' (this token can be 'location name..program name.consistency token' if the current package set special register is blank for the local program execution) that has not been found due to one or more or the following reasons: *    The program 'program name' has not been bound as the member part of the application plan 'plan name' or *    The 'collection id' in the PKLIST was not correct when the application plan 'plan name' was bound or *    The current package set special register was not set correctly or *    The 'location name' in the CURRENTSERVER was not correct when the application plan 'plan name' was bound, or *    The application was not connected to the proper location or *    The location id in the package list was not correct when the application 'plan name' was bound or  *    The DBRM of the version of the application program being executed was not bound.

The statement cannot be executed.

Is plan name correct?

Ensure COLLECTION name is in DB2 PLAN. Recompile and BIND the DB2 program. Verify correct LOAD library is being used.

Based on the reasons, the programmer can perform the following operations to correct the error. *    Bind the program 'program name' as the member part of the application plan 'plan name' or *  Correct the collection id in the PKLIST and bind the application plan 'plan name'  or *    Set the current package set special register correctly or *    Put the correct 'location name' in the CURRENTSERVER of the BIND command and bind the application plan 'plan name' or *    Connect to the correct RDB name or *    Correct the location id in the PKLIST and bind the application plan 'plan name' or  *    Bind the DBRM of the version of the application program to be executed

-811

THE RESULT OF AN EMBEDDED SELECT STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF THE SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE

More than one row retrieved in SELECT INTO

Execution of an embedded SELECT statement has resulted in a result table containing more than one row.  Alternatively, a subquery contained in a basic predicate has produced more than one value.

The statement cannot be executed.

Ensure COLLECTION name is in DB2 PLAN. Recompile and BIND the DB2 program. Verify correct LOAD library is being used.

-815

A GROUP BY OR HAVING CLAUSE IS IMPLICITLY OR EXPLICITLY SPECIFIED IN AN EMBEDDED SELECT STATEMENT OR A SUBQUERY OF A BASIC PREDICATE

An embedded SELECT statement or a subquery of a basic predicate either (1) directly contains a GROUP BY or HAVING clause, or (2) specifies as its object a view having a definition that includes a GROUP BY or HAVING clause.  Neither construct is permitted.

The statement cannot be executed.  No data was retrieved.

The implied function is not supported by DB2. In the case of a view, embedded SELECT statements cannot be used to retrieve data from that view. The SELECT INTO statement should be changed to  a DECLARE CURSOR statement in order to use the GROUP BY/HAVING.  In the case of the subquery, there is no coding workaround.  A GROUP BY/HAVING cannot be used within the subquery of a basic predicate because the subquery is allowed to return only a single value.  For more information on basic predicate refer to SQL Reference .

-817

THE INSERT, UPDATE, DELETE, DDL, OR AUTHORIZATION STATEMENT CANNOT BE EXECUTED BECAUSE THE IMS/VS TRANSACTION IS INQUIRY ONLY

One of the following situations has occurred: 1.  An application which makes local SQL queries is running as an inquiry only transaction but has attempted to execute an INSERT, UPDATE, DELETE data definition (CREATE, ALTER, or DROP), or authorization (GRANT or REVOKE) statement.  Such statements result in updates to the user data or the subsystem catalog, and cannot be executed by an application running as an inquiry only transaction. 2.  An application which does remote unit of work SQL queries has attempted to execute an INSERT, UPDATE, DELETE data definition(CREATE, ALTER, or DROP), or authorization (GRANT or REVOKE) statement.  Such statements result in updates to the user data or the subsystem catalog, and cannot be executed by an application doing remote unit of work queries.

The statement cannot be executed.

If the application is doing local SQL queries, see your IMS/VS system programmer about changing the inquiry only status of the transaction under which your application is running.

-818

THE PRECOMPILER GENERATED TIMESTAMP x IN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP y BUILT FROM THE DBRM z

Plan and program: timestamp mismatch

The SQL precompiler places timestamp 'y' in the DBRM, and time stamp 'x' in the parameter list in the application program for each SQL statement.  At BIND time, DB2 stores the DBRM timestamp for run time use.  At run time, timestamp 'x', for the SQL statement being processed,is compared with timestamp 'y' derived from the DBRM 'z' at BIND time.  If the two timestamps do not match, the DBRM and the application program were not the result of the same precompile. This problem can occur if you:  *   Precompile, compile, and link, without doing a BIND of the application, *   Precompile and BIND, without doing the compile and link for the application program, or *   BIND the application using a DBRM that resulted from a different precompile of the application program than that which produced the object module that is linked into the application module. The timestamps 'x' and 'y' are DB2 internal timestamps.  They do not have an external interpretation.

The statement cannot be executed.

Recompile and BIND the application again, using the DBRM for the application program that matches the object module.

-819

THE VIEW CANNOT BE PROCESSED BECAUSE THE LENGTH OF ITS PARSE TREE IN THE CATALOG IS ZERO

SYSIBM.SYSVTREE.VTREE is a varying length string column that contains the parse trees of views.  In processing a view, the length control field of its parse tree was found to be zero.

The statement cannot be executed.

This is a system error. 

-820

THE SQL STATEMENT CANNOT BE PROCESSED BECAUSE catalog table CONTAINS A VALUE THAT IS NOT VALID IN THIS RELEASE

A column of the indicated catalog table contains a value that prevents further processing of a SQL statement.  The meaning of the value is unknown to the release of DB2.  If a fall back has occurred, the value is probably the result of the use of new function prior to the fall back.

The statement cannot be executed.

Verify that the statement refers to the intended tables or views and that the problem is the result of a fall back.  If this is the case, the statement cannot be corrected because it depends on a function that is not supported in the current release.  If the problem is not the result of a fallback,  820 is a system error. 

-821

VIEW view-name HAS BEEN DROPPED DURING MIGRATION CREATOR OF SYNONYM synonym-name COULD NOT BE RESOLVED.

NA

NA

NA

-822

THE SQLDA CONTAINS AN INVALID DATA ADDRESS OR INDICATOR VARIABLE ADDRESS

The application program has placed an invalid address in the SQLDA.

The statement cannot be executed.

Correct the application program such that valid addresses are placed in SQLDA.

-840

TOO MANY ITEMS RETURNED IN A SELECT OR INSERT LIST

The number of items returned in the select list or presented in an insert list exceeds the allowable maximum of 750.

The statement cannot be executed.

Determine whether all the information is actually needed.  (Note that the number of items returned by the select list * in the SQL statement SELECT * FROM A, B, C is the sum of the number of columns in all three tables.)  If not, rewrite the SQL statement so that only the necessary items of information are returned.  If so, break the SQL statement up into two or more statements, as required.

-901

UNSUCCESSFUL EXECUTION CAUSED BY A SYSTEM ERROR THAT DOES NOT PRECLUDE THE SUCCESSFUL EXECUTION OF SUBSEQUENT SQL STATEMENTS

A system error has occurred that prevented successful execution of the current SQL statement.  However, the error was not of a kind that would preclude successful execution of further SQL statements.The error may have occurred because the length of the SQL statement is less than 0, or it exceeds the DB2 maximum length for a statement.

The statement cannot be executed.  An 0FE abend is requested for the application.  However, the application program may have a recovery routine and may retry SQL statements.

Notify the system programmer for analysis of the abend that caused this return code. An application program receiving this return code may retry and is not prohibited from executing further SQL statements.

-904

UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE.  REASON reason code, TYPE OF RESOURCE resource type, AND RESOURCE NAME resource name

Unavailable resource. Someone is locking the data you need
you may choose to terminate the program

The SQL statement could not be executed because resource'resource name' of type 'resource type' was not available at the time for the reason indicated by 'reason code'.  Refer to Figure 6 in Appendix B,Problem Determination in topic APPENDIX1.2 for an explanation of resource type codes.  Refer to Section 4. DB2 Codes in topic 4.0 for an explanation of the given reason code.

The SQL statement cannot be executed.

-904 is usually caused because a database utility job has started the desired DB2 object in utility mode. Check DB2 Master Log for more details on the resource name contact DBA.

-905

UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT BEING EXCEEDED,RESOURCE NAME = resource name LIMIT = limit amount1 CPU SECONDS (limit amount2 SERVICE UNITS) DERIVED FROM limit source

The execution of the SQL statement was terminated because a resource limit was exceeded.The name of the resource whose limit was exceeded is 'resource name'.  It is also the name of the column in the resource limit specification table from which the limit was derived.  The limit that was exceeded in CPU seconds is 'limit amount1' and in service units it is 'limit amount2'.The 'resource name' may be ASUTIME, which is the number of CPU seconds permitted for each SQL statement.  The maximum number of CPU seconds permitted is 'limit amount1'.  The maximum number in service units is 'limit amount2'.The source used to derive the limit amount is 'limit source' and is either the name of a resource limit specification table or a 'system parameter'.If the source is a system parameter, the resource limit specification table did not contain an applicable entry or an error occurred while accessing the table.  In either case, the limit is obtained from an install (system) parameter.

The execution of this SQL statement is terminated.  A record containing more detailed information about this failure is generated.  If there is an SQL cursor associated with the failed instruction, its position is unchanged and a CLOSE or PREPARE command may be issued.  If any other operation is attempted with the cursor, it cannot be executed and a 'SQLCODE  905' is returned.  If there is no cursor, this statement has been rolled back.

Determine why this SQL statement took so long and take appropriate action.  Consider simplifying the SQL statement, restructuring tables and indexes, or contacting the installation group responsible for maintaining the resource limit specification tables. An application program that receives this return code can execute additional SQL statements.

-906

THE SQL STATEMENT CANNOT BE EXECUTED BECAUSE THIS FUNCTION IS DISABLED DUE TO A PRIOR ERROR

Execution of the SQL statement failed because the requested function had been disabled by a prior error.  This situation can arise if the application program has intercepted an abend (for instance, by an ON ERROR condition in a PL/I program) and continued to execute SQL statements.  This situation may also arise if a DB2 CICS transaction encountered a create thread error yet continued to issue SQL requests without issuing a SYNCPOINT ROLLBACK first.

The statement cannot be executed.

In general, an application program should terminate upon receipt of this return code.  All subsequent attempts by the application to execute other SQL statements will also fail with the same return code.  In the case of a DB2 CICS transaction, if the SQLERRP field in the SQLCA contains the module name DSNCEXT1, the transaction may issue a SYNCPOINT ROLLBACK and continue processing.  If the transactions chooses to ROLLBACK and continue processing, it must be capable of correcting the situation that caused the create thread error to occur originally.

-909

THE OBJECT HAS BEEN DELETED

The application program has either (1) dropped a table and then attempted to access it, or (2) dropped an index and then tried to access its object table using that index.

The statement cannot be executed.

The logic of the application program must be corrected such that it does not attempt to access or use an object after it has been dropped. Dropping indexes within an application program is especially hazardous, because there is no way of determining whether or not the plan that has been generated for the application (by BIND or REBIND) actually uses a particular index for access to its object table.

-910

THE SQL STATEMENT CANNOT ACCESS AN OBJECT ON WHICH A DROP OR ALTER IS PENDING

The application program has issued a DROP or ALTER against an object, and then attempted to access that object before the DROP or ALTER is completed.

The statement cannot be executed.

In the case of ALTER, the logic of the application program must be modified so that a COMMIT (or the IMS/VS or CICS equivalent) is executed between the ALTER and the failing SQL statement. For DROP, the logic of the application program should be modified such that there is no attempt to access an object after the DROP has been executed.

-911

THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT.  REASON reason code, TYPE OF RESOURCE resource type, AND RESOURCE NAME resource name

Deadlock or timeout. Rollback has been done.

The current unit of work was the victim in a deadlock, or experienced a timeout, and had to be rolled back. The reason code indicated whether a deadlock or timeout occurred. SQLERRD(3) also contains the reason-code which indicates whether a deadlock or timeout occurred. The most common reason codes are:
00C90088 - deadlock
00C9008E - timeout

The statement cannot be executed. The application is rolled back to the previous COMMIT.

A long running application, or an application that is likely to encounter a deadlock, should (if possible) issue frequent COMMIT commands.  This can lessen the possibility of a deadlock occurring. : Review DB2 Master Log to find process holding DB2 locks. Consider adding additional COMMITs to program holding the DB2 resource.

-913

UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT.  REASON CODE reason code, TYPE OF RESOURCE resource type, AND RESOURCE NAME resource name

Your program was the victim of a deadlock or timeout.
NO rollback has been done.
You should do a ROLLBACK.

The application was the victim in a deadlock or experienced a timeout.  The reason code indicates whether a deadlock or timeout occurred.

The statement cannot be executed.

The application should either commit or roll back to the previous COMMIT.  Then, in general, the application should terminate. See message DSNT376I for possible ways to avoid future deadlocks or timeouts.

-913

UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT.  REASON CODE reason code, TYPE OF RESOURCE resource type, AND RESOURCE NAME resource name

The application was the victim in a deadlock or experienced a timeout.  The reason code indicates whether a deadlock or timeout occurred.

The statement cannot be executed.

The application should either commit or roll back to the previous COMMIT.  Then, in general, the application should terminate. See message DSNT376I for possible ways to avoid future deadlocks or timeouts.

+922

THE SQLDA CONTAINS AN INVALID DATA ADDRESS OR INDICATOR VARIABLE ADDRESS

Authorization failed because of the error indicated by 'error type', which may be one of the following: *   User authorization *   Plan access *   Duplicate exit requested *   Installation error

NA

NA

-922

CONNECTION AUTHORIZATION FAILURE: error-type ERROR

Authorization failed because of the error indicated by 'error type', which may be one of the following: *   User authorization *   Plan access *   Duplicate exit requested *   Installation error

The statement cannot be processed. The connection to DB2 is not established.

Connection to DB2 has failed due authority for USER or PLAN. Contact DBA to check DB2 authorizations.

-923

CONNECTION NOT ESTABLISHED

The connection to DB2 failed for the reason indicated by condition, which can be any of the following:

DB2 not up
DB2 not operational
DB2 shutdown in progress
DB2 restricted access mode
Allocation error
DB2 - CICS attachment not up
DB2 - CICS ENTRY disabled
The object is dependent on facilities of a release of DB2 that is newer than the release that you are currently running (fall back).
DB2 restarted in light mode

NA

If the connection failed because either DB2 or a required database, table space, table, or index was unavailable, wait until it is available before invoking the application again.

If allocation failed for an application plan, REBIND the plan to determine the problem.

-924

DB2 CONNECTION INTERNAL ERROR

Connection to DB2 has failed because of an unexpected internal error, identified by the reason-code value.

NA

The requested function-code and return-code values might provide additional information. Any attempts to issue SQL statements after the SQLCODE -924 might have unpredictable results.

-925

COMMIT NOT VALID IN IMS/VS OR CICS ENVIRONMENT

An application executing in either an IMS or CICS environment when DB2 is not the only resource manager has attempted to execute a COMMIT statement. The SQL COMMIT statement cannot be executed in these environments.

NA

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

If a stored procedure is being called from IMS or CICS, ensure that the stored procedure is not defined to perform a commit on return.

-926

ROLLBACK NOT VALID IN IMS/VS OR CICS ENVIRONMENT

An application executing in either an IMS or CICS environment when DB2 is not the only resource manager has attempted to execute a ROLLBACK statement. The SQL ROLLBACK statement cannot be executed in these environments.

NA

The IMS or CICS protocols should be used to roll back work in these environments.

-927

THE LANGRAGE INTERFACE WAS CALLED WHEN THE CONNECTING ENVIRONMENT WAS NOT ESTABLISHED. THE PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND

In the TSO environment, the user has attempted to execute an application program without first establishing the correct execution environment by issuing the DSN command. In the IMS, CICS, or call attachment facility (CAF) environment, the user has attempted to execute an application program that is not using the correct language interface module.

NA

In the TSO environment, DB2 application programs should be invoked under the RUN subcommand of the DSN command processor. In the IMS, CICS, or CAF environment check that the application was link-edited with or is dynamically allocating the correct language interface module.

-952

PROCESSING WAS INTERRUPTED BY A CANCEL REQUEST FROM A CLIENT PROGRAM

A client program issued a cancel request that terminated processing of the SQL statement.

The SQL statement was terminated and any changes made by that statement have been undone. If an SQL cursor is associated with the interrupted SQL statement, that cursor is closed.

NA

 

^Note: If the SQL Code not listed above, please use our Free ABEND ASSIST software. For any technical support, ask our Experts in our Mainframe Experts Forum

  

Back to QUICK REFERENCES

Copyright 2003-2016 IBMMAINFRAMES.com

Home | Mainframe Wiki | Downloads | Programs | JCLs | Info | References | Mainframe Forum