SQL Error Code is used on a day to day basis for the diagnosis of programming failures as a result of SQL calls by DB2 computer programs. An important feature of DB2 programs is the error processing. The error diagnostic containing the SQL Error Code are held in the field SQLCODE within the DB2 SQLCA block. This Instant Expert Reference Card will review SQL error code processing and common SQL error conditions you may encounter in your daily work with DB2.
The SQL communications area (SQLCA) structure is used within the DB2 program to return error information to the application program. This information in the SQLCA and the SQLCODE field is updated after every API call for the SQL statement
The SQLCODE field contains the SQL error code. The code can be zero (0), negative or positive.
- SQLCODE = 0, execution was successful.
- SQLCODE > 0, execution was successful with a warning.
- SQLCODE < 0, execution was not successful.
Common SQL Error Code
| SQL Error Code | SQL Error Code Description | Explanation | Suggestion |
| -117 | THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER OF SPECIFIED OR IMPLIED 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. Alternatively, the number of values on the right side of an assignment in a SET assignment statement or the SET clause of an UPDATE statement does not match the number of columns on the left side. | Correct the statement to specify one and only one value for each of the specified object/columns. |
| -150 | THE OBJECT OF THE INSERT, DELETE, OR UPDATE STATEMENT IS A VIEW, SYSTEM-MAINTAINED MATERIALIZED QUERY TABLE, OR TRANSITION TABLE FOR WHICH THE REQUEST- ED OPERATION IS NOT PERMITTED. | One of the following occurred:A transition table was named in an INSERT, UPDATE, DELETE, MERGE, or TRUNCATE statement in a triggered action. Transition tables are read-only.The view named in the INSERT, UPDATE, DELETE, MERGE, or TRUNCATE statement is defined in such a way that the requested insert, update, delete, or truncate operation cannot be performed upon it. Inserts into a view are prohibited if:The view definition contains a join, a GROUP BY, or a HAVING clause.The SELECT clause in the view definition contains the DISTINCT qualifier, an arithmetic expression, a string expression, a built-in function, or a constant.Two or more columns of the view are derived from the same column.A base table of the view contains a column that does not have a default value and is not included in the view.Updates to a view are prohibited if:The view definition contains a join, a GROUP BY, or a HAVING clause.The SELECT clause in the view definition contains the DISTINCT qualifier or a built-in function.Also, a given column in a view cannot be updated (that is, the values in that column cannot be updated) if the column is derived from an arithmetic expression, a constant, a column that is part of the key of a partitioned index, or a column of a catalog table that cannot be updated.Deletes against a view are prohibited if:The view definition contains a join, a GROUP BY, or a HAVING clause.The SELECT clause in the view definition contains the DISTINCT qualifier or a built-in function.Truncates against a view are always prohibited.An auxiliary table or an XML table was named in a TRUNCATE statement. | Be certain to specify base DB2 table/view names for INSERT statements. If the error occurred on a CREATE TRIGGER statement, remove the INSERT, UPDATE, MERGE, or DELETE reference to the transition table. |
| -180 | THE DATE, TIME, OR TIMESTAMP VALUE value IS INVALID. | The length or string representation of a DATE, TIME, or TIMESTAMP value does not conform to any valid format.The value can contain one of the following: For a host variable, the position number of the input host variable. If the position number cannot be determined, a blank is displayed. For a character string constant, the character string constant. The maximum length that is displayed is the length of SQLERRM. For a character column, the column name. If the column is a VIEW column and it has a corresponding base column, the VIEW column name is displayed. If the column is a VIEW column but it does not have a corresponding base column, a string of ‘*N’ is displayed.Otherwise, value is a string of ‘*N’. | Verify the data value is in the correct range and value type. 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. | Verify data format with the SQL Reference Guide.Check whether the value is within the valid range and is in the proper format. Correct the syntax, and reissue the statement. |
| -204 | Name/Object IS AN UNDEFINED NAME. | The object that is identified by name is not defined in the Db2 subsystem. This SQLCODE can be generated for any type of Db2 object. | Verify that the object name was correctly specified in the SQL statement, including any required qualifiers. If it is correct, ensure that the object exists in the system before resubmitting the statement. |
| -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-nameresulted 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.If an indicator structure had been provided on the previous multiple-row FETCH, a positive SQLCODE would have been returned and all of the rows that were retrieved from Db2 could have been returned to the application program. | CLOSE and re-OPEN the cursor; For scrollable use (FIRST, LAST, BEFORE, AFTER, or ABSOLUTE) to establish valid position. |
| -305 | HE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE NO INDICATOR VARIABLE IS SPECIFIED. | A FETCH, SELECT, VALUES INTO, or SET assignment statement 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. | Add null indicator variable to SELECT statement in the format of “column:hostvarind” Examine the definition of the table that is the object of the statement 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 aggregate functions whose result table could be empty. |
| -501 | THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN. | The application program attempted either to fetch using a cursor or to close a cursor at a time when the specified cursor was not open. | Correct logic in application program to OPEN the cursor before the FETCH or CLOSE statement. Check for a previous SQL error code that might have closed the cursor. Commit and rollback operations close cursors. SQL error codes -404, -652, -679, -802, -901, -904, -909, -910, -911, -913, and -952 might force the cursor to close. An error SQLCODE during predicate processing closes the cursor.After the cursor is closed, any fetches or close cursor statements will receive SQLCODE -501.If no previous SQL error codes 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 processed. |
| -502 | THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS AL- READY OPEN. | The application program attempted to execute an OPEN statement for a cursor that was already open. | Correct logic in application program to CLOSE the CURSOR before the OPEN statement. |
| -503 | A COLUMN CANNOT BE UPDATED BECAUSE IT IS NOT IDEN- TIFIED 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. | 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. |
| -530 | THE INSERT OR UPDATE VALUE OF FOREIGN KEY constraint- name IS INVALID. | An insert or update 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 parent key of the parent table.When a row is inserted into a dependent table, the insert value of a foreign key must be equal to the value of the parent 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 parent key of some row of the parent table of the associated relationship. | Examine the insert or update value of the foreign key first, and then compare it with each of the parent key values of the parent table to determine the cause of the problem. Ensure that INSERT row for DB2 PARENT table is completed before INSERT row in CHILD table. |
| -532 | THE RELATIONSHIP constraint-name RESTRICTS THE DELE- TION 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 or NO ACTION 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 NO ACTION or the deletion cascades to a row which is a dependent in a relationship with a delete rule of RESTRICT or NO ACTION.If the statement that failed is an ALTER TABLE with ALTER PART ROTATE FIRST TO LAST, then there is a referential constraint that is defined with DELETE RESTRICT on the table, and the data in the partition that is to be reused cannot be deleted because of the referential constraint. | Examine the delete rule for all descendent tables to determine the cause of the problem. The specific tables involved can be determined from the relationship constraint-name. The specific descendent row is known by RID X rid-number.Change the program to DELETE CHILD table row before DELETE of row on PARENT table. |
| -551 | auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name. | 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 data change operation), or if auth-id is trying to create a table or view with an authorization ID other than its own.You can create a table from an auth-id other than your own only if your authorization ID is SYSADM, DBADM, or DBCTRL. You can create a view from an auth-idother than your own only if your authorization ID is SYSADM.If you are using a trusted context, the token auth-idmight return a role instead of an authorization ID. A role is returned if a role was in effect and the authorization checking is performed against the role, rather than the authorization ID of the session, when the condition was encountered. Otherwise an authorization ID is returned. A role is returned in the following format as a single token: ROLE: role-nameIf you use the RACF® access control module for authorization checking, auth-id might be different from the value that is reported in message ICH408I. The ID that is reported in message ICH408I is the user ID that RACF used to check the privilege.In addition to the situations mentioned previously, this error can occur for the following situations: When operation is GRANT ***, the keyword ALL was used in the GRANT statement, but the grantor auth-id does not have any privilege to grant. When operation is GRANT (table or view privileges) and the specified privilege cannot be granted on a view or auxiliary table. If operation is DROP PACKAGE, the object-name consists of the collection ID, the package name, and the consistency token. The consistency token uniquely identifies the version of the package that the user does not have authorization to drop. If operation is USAGE OF DISTINCT TYPE or USAGE OF JAR, the object-name identifies, respectively, the DISTINCT TYPE or JAR for which the auth-id lacks USAGE privilege. If operation is ALTER JAR, the auth-idlacks ALTERIN privilege on the schema of the JAR object-name. If this error occurs while Db2 is creating or altering a table that involves referential constraints, this error message reports that the user does not have the necessary ALTER privilege to perform a FOREIGN KEY, DROP FOREIGN KEY, DROP PRIMARY KEY, or DROP UNIQUE operation. The object-nameidentifies the object table of the CREATE or ALTER TABLE statement, not the table for which the user lacks the ALTER privilege. If this error occurs for a distributed SQL request, one of the following conditions can occur: If authorization ID translation is in effect for either the requesting Db2 site or the serving (responding) Db2 site, then auth-id is the translated authorization ID. Refer to Part 3 (Volume 1) of the Db2 Administration Guide for information on authorization ID translation. If an alias name was used in the SQL statement, the object-name is the resolved remote table name or view name. If this error occurs during invocation of a routine, the authorization ID auth-id does not have the EXECUTE privilege on any candidate routine in the SQL path. The variable for object-name is the name of a candidate routine in the SQL path. An object does not exist, and the CURRENT RULES special register is set to STD. This error might occur for packages that are bound with the DYNAMICRULES(BIND) option when authorization caching, statement caching, or both are enabled and if the following conditions exist: The access control authorization exit routine is active The AUTHEXIT_CHECK system parameter is set to PRIMARY The authorization ID of the process does not have the necessary privileges. If the access control authorization exit is active and the AUTHEXIT_CHECK system parameter is set to Db2, this error might occur if ACEE cannot be created for the authorization ID auth-id. If operation is CREATE VARIABLE or DROP VARIABLE, the auth-id lacks the necessary privileges to perform this action. | To correct the error, verify the following situations: The auth-id has the authority to perform the operation. The object-name exists. The auth-id is not trying to create a table with a schema qualifier that is not the same as auth-id. If you are using the access control authorization exit with RACF, ensure that the authorization ID auth-id is defined in RACF and was granted the required privilege to perform the operation. Check the USER value in RACF message ICH408I to determine the authorization ID that attempted to perform the operation. |
| -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 DU- PLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS Xrid. | The table that is the object of the insert or update operation is constrained to have unique values in certain columns. Completion of the requested operation would result in duplicate values.indexspace-nameThe name of a hash overflow index or the name of the index space that contains the unique index that enforces a primary key.record-idThe record ID of the row where the duplicate values would exist.When indexspace-name identifies a hash overflow index, the error occurred because a table with hash organization cannot have two rows with identical hash key values.If the index space is an XML index, the duplicate values can be caused by the values in the XML document or by the result of data type conversion and rounding.Otherwise, a primary key, unique constraint, or unique index exists over one or more columns of the table. The operation cannot be performed because one or more values would have produced a duplicate key in the unique index or constraint.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. | Examine the definitions for UNIQUE INDEX or the HASH key that is specified in the index space that is identified by indexspace-name to determine the uniqueness constraint that is imposed. Refer to SYSIBM.SYSINDEXES for the index space name and the associated index name.Update operationVerify 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.Insert operationExamine 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.Delete operationExamine 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.Merge operationVerify 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. In very rare cases, the error can be caused by another INSERT/UPDATE statement which modified the object table during the processing of MERGE statement. Under such circumstances, examine your applications and make appropriate changes to handle concurrency. For example, changing the isolation level or running the MERGE statement again might avoid the problem. |
| -805 | DBRM OR PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason-code | An application program attempted to use a DBRM or package that was not found.location-name.collection-id.dbrm-name.consistency-tokenThe package that was not found. The collection-id is blank if the CURRENT PACKAGESET special register was blank for the local program execution.plan-nameThe name of the plan.reason-codeA numeric value that indicates the reason for the failure.01 The DBRM name was not found in the member list of the plan and there is no package list for the plan. Problem determination: Query 1 Corrective action: Add the DBRM that is identified by dbrm-name to the MEMBER list of the BIND subcommand and bind the application plan that is identified by plan-name. The package name was not found because there is no package list for the plan. Problem determination: Query 2 Corrective action: Add the PKLIST option with the appropriate package list entry to the REBIND subcommand and rebind the application plan that is identified by plan-name.02The DBRM name dbrm-name did not match an entry in the member list or the package list. Any of the following conditions could be the problem:BIND conditions The collection-id in the package list was not correct when the application plan that is identified by plan-name was bound. Problem determination: Query 2 Corrective action: Correct the collection ID of the entry in the PKLIST option and use the REBIND subcommand to rebind the application plan that is identified by plan-name. The location-name in the package list was not correct when the application plan that is identified by plan-name was bound. Problem determination: Query 2 Corrective action: Correct the location name of the entry in the PKLIST option and use the REBIND subcommand to rebind the application plan that is identified by plan-name. The location-name in the CURRENTSERVER option for the BIND subcommand was not correct when the application plan that is identified by plan-namewas bound. Problem determination: Query 3 Corrective action: Correct the location name in the CURRENTSERVER option and use the REBIND subcommand to rebind the application plan that is identified by plan-name.Application conditions The CURRENT PACKAGESET special register was not set correctly by the application.When using SET CURRENT PACKAGESET = :HV, be sure to use the correct encoding scheme, which must match the :HV in subsystem parameter options. This statement does not require package or DBRM bound into the plan, so it uses the encoding scheme defined for system. The same condition applies to SET CURRENT PACKAGE PATH. Corrective action: Set the CURRENT PACKAGESET special register correctly. The application was not connected to the proper location.Corrective action: Connect to the correct location.03The DBRM name that is identified by dbrm-namematched one or more entries in the package list and the search of those entries did not find the package. Any of the conditions that are listed for reason-code value 02 are possible causes for reason-code value 03, as are the following additional conditions: The DBRM of the version of the application program being executed was not bound. (A package with the same consistency token as that of the application program was not found.) Problem determination: Query 4 Corrective action: Bind the DBRM of the version of the application program to be executed into the collection that is identified by collection-id. The incorrect version of the application program is being executed.Corrective action: Execute the correct version of the application program. The consistency token of the application program is the same as the package that was bound.04The package does not exist at the remote site, which is identified by the location-name value.Problem determination: Query 4blankThe reason-code value is blank if the length of location-name is 16, the length of collection-id is 18, and the length of dbrm-name is 8 due to the length of SQLERRMT.In a native SQL procedure, if the affected SQL statement follows a SET CURRENT PACKAGESET, SET CURRENT PACKAGE PATH, or CONNECT statement, or if it refers to an object on a remote server, additional packages must be bound using BIND COPY. Whenever the native SQL procedure is changed such that a regeneration is needed, the additional package also needs to be bound with the copy option. |
Problem determinationThe following queries aid in determining the problem. Run these queries at the local location.Query 1: Display the DBRMs in the member list for the planSELECT PLCREATOR, PLNAME, NAME, VERSIONFROM SYSIBM.SYSDBRMWHERE PLNAME = ‘plan-name‘; If no rows are returned, then the plan was bound without a member list.Query 2: Display the entries in the package list for the planSELECT LOCATION, COLLID, NAMEFROM SYSIBM.SYSPACKLISTWHERE PLANNAME = ‘plan-name‘; If no rows are returned, then the plan was bound without a package list.Query 3: Display the CURRENTSERVER value specified on the BIND subcommand for the plan.SELECT NAME, CURRENTSERVERFROM SYSIBM.SYSPLANWHERE NAME = ‘plan-name‘; Query 4: Determine if there is a matching package in SYSPACKAGEIf the package is remote, put the location name in the FROM clause.If the collection-id value in the message is blank, use this version of the query:SELECT COLLID, NAME, HEX(CONTOKEN), VERSIONFROM location-name.SYSIBM.SYSPACKAGEWHERE NAME = ‘dbrm-name‘AND HEX(CONTOKEN) = ‘consistency-token‘; If the collection-id value in the message is not blank, use this version of the query:SELECT COLLID, NAME, HEX(CONTOKEN), VERSIONFROM location-name.SYSIBM.SYSPACKAGEWHERE NAME = ‘dbrm-name‘AND HEX(CONTOKEN) = ‘consistency-token‘AND COLLID = ‘collection-id‘; If no rows are returned, the correct version of the package was not bound. |
| -811 | THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE | Execution of an embedded SELECT statement or a subselect in the SET clause of an UPDATE statement has resulted in a result table that contains more than one row. Alternatively, a subquery contained in a basic predicate has produced more than one value. | Examine the syntax of the statement to ensure that it contains the proper condition specifications. If the statement syntax is correct, there might be a problem with the data that is causing more than one row or value to be returned when you do not expect it. |
| -818 | THE PRECOMPILER-GENERATED TIMESTAMP xIN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP y BUILT FROM THE DBRM z | The timestamp that is stored in the application load module does not match the timestamp that is stored in the plan or package. If the timestamps do not match, the application is not allowed to process the SQL statement.xThe timestamp that was generated by the precompiler. This timestamp is a Db2 internal timestamp. It does not have an external interpretation.yThe bind timestamp. This timestamp is a Db2 internal timestamp. It does not have an external interpretation.zThe name of the database request module (DBRM).The SQL precompiler places timestamp y in the DBRM and timestamp 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 operation.This problem can occur in the following cases: The application was precompiled, compiled, and linked, but was not bound. The application was precompiled and bound, but was not compiled and linked for the application program. The application was bound 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. | Bind the application again, using the DBRM for the application program that matches the load module. |
| -904 | UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name | The SQL statement could not be executed because a required resource was not available.reason-codeThe Db2 reason code value that indicates the reason for the failure. For explanations of reason codes, see Db2 reason codes.resource-type and resource-nameThe type and name of the resource that the message identifies. For an explanation of resource-type and resource-name values, see Resource types.When resource-type is 402, the value of resource-nameis the z/OS® Security Server return and reason codes for the unavailable resource. When reason-code is 00E7000E and resource-type is 00001080, SQLCODE -904 was issued by IBM Db2 Analytics Accelerator for z/OS, and resource-name is message text from the accelerator that indicates the cause of the failure. | Collect a listing of the SYSLOG data set for the period of time that spans the failure. Search the SYSLOG data set for messages that are related to the error. Verify the identity of the resource that was not available. To determine why the resource was unavailable, refer to the specified reason-code.If resource-name identifies the optimum buffer pool, either define the buffer pool with that name or create the table in a table space that is defined with that buffer pool. If SQLCODE -904 was issued by IBM Db2 Analytics Accelerator for z/OS, contact IBM® Support for IBM Db2 Analytics Accelerator for z/OS, and provide them with the resource-name message text. |
| -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 | The current unit of work was the victim in a deadlock, or experienced a timeout, and had to be rolled back.reason-codeThe Db2 reason code value that indicates the reason for the failure. For explanations of reason codes, see Db2 reason codes.resource-type and resource-nameThe type and name of the resource that the message identifies. For an explanation of resource-type and resource-name values, see Resource types. | Collect a listing of the SYSLOG data set for the period of time that spans the failure. Search the SYSLOG data set for messages that are related to the error. 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. See message DSNT376I for other possible ways to avoid future deadlocks or timeouts. On receipt of the SQLCODE -911, the application should, in general, terminate. |
| -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.reason-codeThe Db2 reason code value that indicates the reason for the failure. For explanations of reason codes, see Db2 reason codes.resource-type and resource-nameThe type and name of the resource that the message identifies. For an explanation of resource-type and resource-name values, see Resource types. | Collect a listing of the SYSLOG data set for the period of time that spans the failure. Search the SYSLOG data set for messages that are related to the error. The application should either commit or roll back to the previous COMMIT. Then, generally, the application should terminate. See message DSNT376I for possible ways to avoid future deadlocks or timeouts. |
| -922 | AUTHORIZATION FAILURE: error-type ERROR. REASON reason-code | Authorization failed because of the error indicated by error-typeerror-typeThe types of authorization failure: User authorization Plan access Duplicate exit requested Installation error Connectreason-codeThe Db2 reason code associated with authorization failure | If error-type is ‘USER AUTHORIZATION’, the authorization-ID specified to Db2 through your attachment facility is not valid for Db2. Contact the system programmer or your CICS®, IMS, or TSO system administrator.If error-type is ‘PLAN ACCESS’, then the authorization ID associated with this connection is not authorized to use the specified plan name or the specified plan name does not exist. See your system administrator.If error-type is ‘DUPLICATE EXIT’, then you requested a duplicate exit.If error-type is ‘INSTALLATION ERROR’, a connection or sign-on exit denied your request. See your system programmer.If error-type is ‘CONNECT’, an SQL CONNECT request failed to connect to the local Db2 with USER/USING specified. See the reason code for a description of the failure. The application program has been placed in the connectable and unconnected state. The only SQL statements that can be successfully completed in this state are CONNECT, COMMIT, ROLLBACK, and local SET statements. Any attempt to execute other SQL statements will result in an error (SQLCODE -900).Any attempts to issue SQL statements following the -922 SQLCODE when error-type is not Connect cause unpredictable results. |
| -927 | THE LANGUAGE INTERFACE (LI) 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®, RRSAF, or call attachment facility (CAF) environment, the user has attempted to execute an application program that is not using the correct language interface module. | In the TSO environment, Db2 application programs should be invoked under the RUN subcommand of the DSN command processor. In the IMS, CICS, RRSAF, or CAF environment check that the application was link-edited with or is dynamically allocating the correct language interface module. The language interface modules required in each environment are as follows: TSO: DSNELI IMS: DFSLI000 CICS: DSNCLI RRSAF: DSNRLI CAF: DSNALIThe DYNAM option can result in the incorrect language interface module being loaded at run time. |
| 100 | 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. The result of the subselect of an INSERT statement is empty. A FETCH statement was executed when the cursor was positioned after the last row of the result table. No available rows qualified for return when SKIP LOCKED DATA was specified with isolation level CS or RS. A FETCH statement that returns a rowset was issued, but there were not enough rows after the current cursor position to reposition the cursor on a full rowset. The cursor has been positioned on a partial rowset. If a target was specified, data was returned only for the number of rows that were actually fetched for the partial rowset. The number of rows that were returned is in field SQLERRD3 of the SQLCA.When a SELECT statement is executed using SPUFI, this SQLCODE indicates normal completion.This SQLCODE is also issued when LOB data cannot be returned. This situation can occur when an application is running with isolation level UR and another application has locked the LOB table space. | ||
| 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. | 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. |
| 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: CURRENT or CURRENT ROWSET RELATIVE 0 or ROWSET STARTING AT RELATIVE 0cursor-nameName of the cursor used for the FETCH statement. | Correct the application program to establish a valid cursor position before issuing this FETCH statement. |
| 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-type2was not large enough to hold the retrieved value having data type data-type1. | 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. |
| 347 | HE RECURSIVE COMMON TABLE EXPRESSION nameMAY CONTAIN AN INFINITE LOOP | The recursive common table expression called namemay not complete. This warning is based on not finding specific syntax as part of the iterative portion of the recursive common table expression. The expected syntax includes: incrementing an INTEGER column in the iterative select list by 1. a predicate in the where clause of the iterative portion of the form “counter_col < constant” or “counter_col < :hostvar”.The absence of this syntax in the recursive common table expression may result in an infinite loop. The data or some other characteristic of the recursive common table expression may allow the successful completion of the statement anyway. | To prevent an infinite loop, include the expected syntax as described. |
| 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 performing one of the following operations on a field that has a data-type of DECIMAL, FLOAT, SMALLINT, or INTEGER: ADDITION SUBTRACTION MULTIPLICATION DIVISION NEGATION BUILT-IN FUNCTIONThe 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:DECIMAL OVERFLOWA decimal overflow exception can occur when one or more nonzero digits are lost because the destination field in any decimal operation is too short to contain the result.DIVIDE EXCEPTIONA divide exception can occur on a decimal division operation when the quotient exceeds the specified data-field size. A zero divide exception occurs on any division by zero, except when the data type is DECFLOAT.EXPONENT OVERFLOWAn exponent overflow can occur when the result characteristic of any floating-point operation exceeds 127 and the result fraction is not zero, i.e. the magnitude of the result exceeds approximately 7.2E+75.FIXED POINT OVERFLOWA fixed point overflow can occur during any arithmetic operation on either INTEGER or SMALLINT fields.INVALID OPERATION OUT OF RANGEAn error might occur during while processing an input, intermediate, or final value if the value of a parameter is out of range.OVERFLOWAn overflow can occur during the processing of a built-in function. If the operation-type is FUNCTION, then the error occurred while processing an input, intermediate, or final value.SUBNORMAL UNDERFLOWAn underflow can occur during the processing of a built-in function. If the operation-type is FUNCTION, then the error occurred while processing an input, intermediate, or final value.ZERO DIVIDE The data type displayed in the message might indicate the data type of the temporary internal copy of the data. This might differ from the actual column or literal data type due to conversions by Db2.Attention: Parts of exception-type, data-type, operation-type, and position-number might or might not be returned in SQLCA, depending upon when the error was detected. |
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.See the explanation of SQLCODE -405 for allowed ranges of numeric data types. |