SQL0750N The statement failed because the table or column cannot be renamed.

Explanation

The source table in a RENAME statement cannot be renamed for one or more of the following reasons.

  • The table is referenced in one or more existing materialized query table.
  • The table is referenced in one or more statistical view.
  • The table is involved in one or more referential constraints as a parent or dependent table.
  • The table is a target table for decomposition of one or more XSR objects.
  • The table is a system-period temporal table.
  • The table is a history table.
  • The database manager configuration parameter auto_reval is set to DISABLED and one of the following occurred:
    • The table is referenced in one or more existing views.
    • The table is referenced in one or more existing triggers. This includes triggers on the table or references in triggered SQL statements.
    • The table is referenced in one or more existing SQL functions or SQL methods.
    • The table has check constraints defined. This includes check constraints caused by generated columns.
  • The table is the latency table, SYSTOOLS.REPL_MQT_LATENCY.
  • The table has a registered spatial column.
  • The table has an expression-based index.

The column of the target table in an ALTER TABLE statement cannot be renamed for one or more of the following reasons.

  • the target table is a history table
  • the column is a random distribution key column of a random distribution table using random by generation method

User response

If the error pertains to a RENAME statement, drop the view, materialized query table, trigger, SQL function, SQL method, check constraint, referential constraint, expression-based index, or XSR object dependent on the table before issuing the RENAME statement. Objects dependent on the table can be determined by querying the catalog.

  • For views, expression-based indexes, or materialized query tables dependent on the table, query SYSCAT.VIEWDEP, where the table matches the BSCHEMA and BNAME columns.
  • For triggers dependent on the table, query SYSCAT.TRIGDEP, where the table matches the BSCHEMA and BNAME columns.
  • For SQL functions or SQL methods, query SYSCAT.ROUTINEDEP where the table matches the BSCHEMA and BNAME columns.
  • For check constraints on the table, query SYSCAT.CHECKS, where the table matches the TABSCHEMA and TABBNAME columns.
  • For tables with registered spatial columns, unregister the spatial column.
  • For referential constraints dependent on the table, query SYSCAT.REFERENCES, where the table matches the TABSCHEMA and TABNAME columns or the REFTABSCHEMA and REFTABNAME columns.
  • For XSR objects enabled for decomposition for which the table is a target, query SYSCAT.XSROBJECTDEP, where the table matches the BSCHEMA and BNAME columns.

If the error pertains to a RENAME COLUMN action of an ALTER TABLE statement, the column cannot be renamed directly while the target table is a history table. The column can be renamed by taking one of the following actions.

  • Determine the name of the system-period temporal table that uses the history table and issue the RENAME COLUMN operation with that name as the target of the ALTER TABLE statement. This renames the column in both the system-period temporal table and the associated history table.
  • Determine the name of the system-period temporal table and issue the DROP VERSIONING operation with that name as the target of ALTER TABLE statement. This changes the history table and the system-period temporal table into a regular tables. You can now rename the column of the table in your original ALTER TABLE statement. Note that if the column is renamed, the table can no longer be the history table for the table on which versioning was dropped.

If a column is a random distribution key column of a random distribution table using random by generation method, no action is required. The column is a system-generated column and cannot be renamed.

sqlcode: -750

sqlstate: 42986

SQL0751N Routine routine-name (specific name specific-name) attempted to execute a statement that is not allowed.

Explanation

The program used to implement the body of a routine is not allowed to issue any connection statements. If the routine is a function or method, COMMIT and ROLLBACK (without the SAVEPOINT option) are also not allowed. If the routine is a procedure and is called within a trigger, function, method, or dynamic compound statement, a COMMIT or ROLLBACK statement is not allowed in the procedure.

User response

Remove the statement that is not allowed, then recompile the program.

sqlcode: -751

sqlstate: 38003, 42985

SQL0752N Connecting to a database is not permitted within a logical unit of work when the CONNECT type 1 setting is in use.

Explanation

An attempt was made to connect to either another database or the same database before issuing a COMMIT or ROLLBACK statement. The request cannot be processed within a CONNECT type 1 environment.

User response

  • Submit a COMMIT or ROLLBACK statement before requesting a connection to another database.
  • If multiple databases need to be updated within a unit of work, change the connection settings to SYNCPOINT TWOPHASE and CONNECT 2 by re-precompiling or by issuing the SET CLIENT API from within the application.

sqlcode: -752

sqlstate: 0A001

SQL0773N The case was not found for the CASE statement.

Explanation

A CASE statement without an ELSE clause was found in the routine body of an SQL routine. None of the conditions specified in the CASE statement were met.

User response

Change the CASE statement to handle all conditions that can occur.

sqlcode: -773

sqlstate: 20000

SQL0774N The statement cannot be executed within an ATOMIC compound SQL statement.

Explanation

A COMMIT or ROLLBACK statement was encountered within the context of an ATOMIC compound SQL statement. These statements are not allowed in this context.

User response

Remove the COMMIT or ROLLBACK statement or change the context so that it is not an ATOMIC compound statement.

sqlcode: -774

sqlstate: 2D522

SQL0776N Use of cursor cursor-name is not valid.

Explanation

Cursor cursor-name is specified as the cursor name on a FOR statement in an SQL procedure. The cursor cannot be specified on a CLOSE, FETCH, or OPEN statement within the FOR statement.

User response

Remove the CLOSE, FETCH, or OPEN statement.

sqlcode: -776

sqlstate: 428D4

SQL0777N Nested compound statements not allowed.

Explanation

Atomic compound statements in the routine body of an SQL procedure cannot be nested.

User response

Ensure that the SQL procedure does not contain nested atomic compound statements.

sqlcode: -777

sqlstate: 42919

SQL0778N End label label is not the same as the begin label.

Explanation

Label label specified at the end of a FOR, IF, LOOP, REPEAT, WHILE or compound statement is not the same as the label at the beginning of the statement. You cannot specify an end label if a begin label is not specified.

User response

Ensure that the end label is the same as the begin label for FOR, IF, LOOP, REPEAT, WHILE, and compound statements.

sqlcode: -778

sqlstate: 428D5

SQL0779N Label label specified on a GOTO, ITERATE, or LEAVE statement is not valid.

Explanation

Label label is specified on a GOTO, ITERATE or LEAVE statement. The label is not defined or is not a valid label for the statement.

The label on an ITERATE statement must be the label for a FOR, LOOP, REPEAT, or WHILE statement.

The label on a LEAVE statement must be the label for a FOR, LOOP, REPEAT, WHILE, or compound statement.

The label on a GOTO must be defined within a scope that the GOTO statement can reach.
  • If the GOTO statement is defined in a FOR statement, label must be defined inside the same FOR statement, excluding a nested FOR statement or nested compound statement.
  • If the GOTO statement is defined in a compound statement, label must be defined inside the same compound statement, excluding a nested FOR statement or nested compound statement.
  • If the GOTO statement is defined in a handler, label must be defined in the same handler, following the other scope rules.
  • If the GOTO statement is defined outside of a handler, label must not be defined within a handler.

User response

Specify a valid label on the GOTO, ITERATE, or LEAVE statement.

sqlcode: -779

sqlstate: 42736

SQL0780N UNDO is specified for a handler when ATOMIC is not specified for the compound statement.

Explanation

UNDO is specified for a handler in a compound statement in an SQL procedure. UNDO cannot be specified unless the compound statement is ATOMIC.

User response

Either specify that the compound statement is ATOMIC or specify EXIT or CONTINUE on the handler.

sqlcode: -780

sqlstate: 428D6

SQL0781N Condition condition-name is not defined or the definition is not in scope.

Explanation

Condition condition-name was specified in a handler declaration, SIGNAL statement, or RESIGNAL statement, but the condition is not defined in the scope of the compound statement that includes the condition reference or the condition could not be found as a defined module condition.

The statement cannot be processed.

User response

Define the condition either by using the DECLARE CONDITION statement in a compound statement that includes the condition reference or by defining the condition in a module that is visible where the condition is referenced. As an alternative, you could replace the condition reference with a specific SQLSTATE value or remove the handler declaration, SIGNAL statement or RESIGNAL statement that references the condition.

sqlcode: -781

sqlstate: 42737

SQL0782N A condition or SQLSTATE value specified in a handler is not valid.

Explanation

A condition or SQLSTATE value specified in a handler in an SQL statement is not valid for one of the following reasons:
  • The condition or SQLSTATE value has already been specified by another handler in the same scope.
  • The condition or SQLSTATE value was specified in the same handler as SQLEXCEPTION, SQLWARNING, or NOT FOUND.
  • The condition, SQLSTATE value or SQLCODE value specified in a continue-handler clause of an error tolerant nested-table-expression is invalid.

User response

Remove the condition, SQLSTATE value or SQLCODE value from the handler.

sqlcode: -782

sqlstate: 428D7

SQL0783N A duplicate column name or unnamed column was specified in a DECLARE CURSOR statement of a FOR statement.

Explanation

The select list in the FOR statement must contain unique column names. The select list specified contains either duplicate column names or unnamed expressions.

User response

Specify unique column names in the select list specified in the FOR statement.

sqlcode: -783

sqlstate: 42738

SQL0784N The statement failed because the constraint named constraint-name cannot be dropped.

Explanation

A constraint that is implicitly generated when a BUSINESS_TIME period is defined cannot be dropped using an ALTER TABLE statement with either a DROP CHECK or DROP CONSTRAINT clause.

User response

Remove the DROP CHECK or DROP CONSTRAINT clause from the ALTER TABLE statement. If you need to drop the constraint, the BUSINESS_TIME period can be dropped using the DROP PERIOD clause in an ALTER TABLE statement.

sqlcode: -784

sqlstate: 42860

SQL0785N The declaration or use of the SQLSTATE or SQLCODE variable is not valid.

Explanation

SQLSTATE or SQLCODE was used as a variable in the routine body of an SQL routine, but is not valid for one of the following reasons:
  • SQLSTATE is not declared as CHAR(5)
  • SQLCODE is not declared as INTEGER
  • The variable is assigned the NULL value.

The statement cannot be processed.

User response

Declare the SQLSTATE variable as CHAR(5) and the SQLCODE variable as INTEGER. Set the variable to a valid value.

sqlcode: -785

sqlstate: 428D8

SQL0787N RESIGNAL statement is not within a handler.

Explanation

The RESIGNAL statement can only be used inside condition handlers.

User response

Remove the RESIGNAL statement or use a SIGNAL statement instead.

sqlcode: -787

sqlstate: 0K000

SQL0788N The statement was not processed because a row of target table table-name was identified more than once for update, delete or insert.

Explanation

This message is returned when the ON search-condition of a MERGE statement or WHERE search-condition of an UPDATE statement matches a single row from the target table with multiple rows of the source table reference. If a row from the target table matches multiple rows of the source table reference, the target row might be operated on more than once with an update or delete operation, which is not supported.

User response

Respond in one of the following ways:

  • Correct the search condition to ensure that each row in the target table only matches one row in the source table.
  • Aggregate the rows in the source table that match the row in the target table by using the GROUP BY clause.
  • Cleanse the data by using the ROW_NUMBER() OLAP expression.

sqlcode: -788

sqlstate: 21506

SQL0789N The data type for parameter or SQL variable name is not supported in the routine, compound SQL statement, or parameter list of a cursor value constructor.

Explanation

One of the following situations has occurred:

  • An SQL routine (function, method, or procedure) or compound SQL statement does not support parameters or SQL variables of the REFERENCE, LONG VARCHAR, LONG VARGRAPHIC, or structured data type.
  • A trigger whose body is a compound SQL (compiled) statement, a stand-alone compound SQL statement, or a routine that is an SQL method does not support parameters or SQL variables of the XML data type.
  • A default value cannot be specified for a parameter of type ARRAY, ROW, or CURSOR.
  • An external routine does not support parameters of a structured data type.
  • A parameter list of a cursor value constructor does not support parameters of ARRAY, BOOLEAN, CURSOR, ROW, LONG VARCHAR, LONG VARGRAPHIC, REFERENCE, or structured data type.

User response

Perform any of the following actions that apply:

  • Do not use parameters or SQL variables of the REFERENCE, LONG VARCHAR, LONG VARGRAPHIC, or structured data type in the SQL routine definition or compound SQL statement.
  • Do not use parameters or SQL variables of the XML data type in a trigger (whose body is a compound SQL (compiled) statement), a stand-alone compound SQL statement, or a routine that is an SQL method.
  • Do not use parameters of the structured data type in external routines.
  • Specify a different data type for the parameter or SQL variable name.
  • Do not specify the default value for parameters of type ARRAY, ROW, or CURSOR.
  • Do not use parameters of ARRAY, BOOLEAN, CURSOR, ROW, LONG VARCHAR, LONG VARGRAPHIC, REFERENCE, or structured data type in the parameter list of a cursor value constructor.

sqlcode: -789

sqlstate: 429BB

SQL0796N The grant of role role-name1 to role role-name2 is not valid as it would create a cycle.

Explanation

A role cannot be granted if it would create a cycle. The role role-name1 contains the role role-name2, possibly through another role, and so it cannot be granted to role-name2.

User response

If appropriate, to remove the potential for a cycle to be created, revoke the role role-name2 from role role-name1, or from whichever role contained in role-name1 that contains role-name2, then resubmit the statement.

sqlcode: -796

sqlstate: 428GF

SQL0797N The trigger trigger-name is defined with an unsupported triggered SQL statement.

Explanation

The trigger must be defined with a triggered SQL statement that can only include statements from the following lists.

A trigger can include the following control statements:

  • Compound SQL (compiled) statement
  • Compound SQL (inlined) statement
  • FOR statement
  • GET DIAGNOSTICS statement
  • IF statement
  • ITERATE statement
  • LEAVE statement
  • SIGNAL statement
  • WHILE statement

An AFTER trigger or an INSTEAD OF trigger can also include the following triggered SQL statements:

  • an INSERT statement
  • a searched UPDATE statement
  • a searched DELETE statement
  • a MERGE statement
  • a CALL statement
  • a fullselect
  • an assignment statement (excluding assignment of transition variables)

A BEFORE trigger that is defined using an SQL compound (compiled) statement can also include the following triggered SQL statements:

  • an INSERT statement
  • a searched UPDATE statement
  • a searched DELETE statement
  • a MERGE statement
  • a CALL statement
  • a fullselect
  • an assignment statement

If a BEFORE trigger is not defined using an SQL compound (compiled) statement, or a BEFORE INSERT trigger is defined with a SQL compound (compiled) statement and the triggering operation is an INSERT on a UNION ALL view or an UPDATE on a WITH ROW MOVEMENT view, then the triggered SQL statement cannot include:

  • a table function or a scalar function defined with a compound SQL (compiled) statement that modifies SQL data
  • a nested DELETE, INSERT, MERGE, or UPDATE statement

In some cases the trigger-name is not available for use in the message.

User response

Check the triggered SQL statements in the trigger for any statement that does not match the list described in this message and remove it.

sqlcode: -797

sqlstate: 42987

SQL0798N A value cannot be specified for column column-name which is defined as GENERATED ALWAYS.

Explanation

When inserting or updating a row in a table, a value was specified for the GENERATED ALWAYS column column-name. GENERATED ALWAYS columns should not be specified in the column-list for an INSERT or in the SET clause for an update unless the keyword DEFAULT is specified.

The INSERT or UPDATE is not performed.

User response

Remove the GENERATED ALWAYS column from the column-list or SET clause, or specify DEFAULT as the column value.

sqlcode: -798

sqlstate: 428C9

SQL0799W A SET statement for special register special-register-name was ignored because either the special register does not exist on the database server or an invalid value was specified for the special register.

Explanation

A special register is a storage area that is defined by the database manager for an application process. Special registers are used to store information that can be referenced in SQL statements.

Some special registers can be updated using the SET variable statement.

A DB2 client or driver can be configured to set special registers. When a DB2 client or driver is configured to set special registers, the client or driver sends a SET variable statement to the database server at the same time as an application SQL statement.

This message is returned when an attempt is made to update a special register, but either the special register does not exist on the database server or an invalid value was specified for the special register.

When a DB2 client or driver sends a SET variable statement to the database server at the same time as an application SQL statement, the database server processes the application SQL statement regardless or whether setting the special register succeeds or fails.

User response

If the DB2 client or driver is configured to set special registers, validate the special register settings that are specified in the client configuration file db2dsdriver.cfg.

sqlcode: +799

sqlstate: 01527

SQL0801N Division by zero was attempted.

Explanation

The processing of a column function or arithmetic expression resulted in division by zero.

The statement cannot be processed. For the INSERT, UPDATE, or DELETE statements, no inserts or updates are performed.

User response

Examine the SQL statement to determine the cause of the problem. If the problem is data dependent, it is necessary to examine the data processed when the error occurred. Refer to the SQL Reference to see the valid ranges for the data types.

Federated system users: examine the SQL statement to determine the cause of the problem. If the problem is data dependent, examine the data being processed at the data sources when the error occurred.

sqlcode: -801

sqlstate: 22012

SQL0802N Arithmetic overflow or other arithmetic exception occurred.

Explanation

The processing of a column function or arithmetic expression has resulted in an arithmetic overflow.

The statement cannot be processed. For the INSERT, UPDATE, or DELETE statements, no inserts or updates are performed.

User response

Examine the SQL statement to determine the cause of the problem. If the problem is data dependent, it is necessary to examine the data processed when the error occurred. Refer to the SQL Reference to see the valid ranges for the data types.

This error may also be returned when column functions cannot handle the value that is returned by the SQL statement. For example, issuing a SELECT COUNT statement against a table that has more rows than defined by the MAX_LONGINT_INT SQL limit will cause an arithmetic overflow error. Consider using the COUNT_BIG column function for tables with more than 2 147 483 647 rows.

Federated system users: examine the SQL statement to determine the cause of the problem. If the problem is data dependent, examine the data being processed at the data sources when the error occurred. Refer to the corresponding SQL Reference for the data source to determine the valid ranges for the data types.

sqlcode: -802

sqlstate: 22003

SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by index-id constrains table table-name from having duplicate values for the index key.

Explanation

The INSERT or UPDATE object table table-name is constrained by one or more UNIQUE indexes to have unique values in certain columns or groups of columns. Alternatively, a DELETE statement on a parent table caused the update of a foreign key in a dependent table table-name that is constrained by one or more UNIQUE indexes. Unique indexes might support primary keys or unique constraints defined on a table. The statement cannot be processed because completing the requested INSERT, UPDATE or DELETE statement would result in duplicate column values. If the index is on an XML column, the duplicate values for the index key may be generated from within a single XML document.

Alternatively, if a view is the object of the INSERT or UPDATE statement, it is the table table-name on which the view is defined that is constrained.

If index-id is an integer value, the index name can be obtained from SYSCAT.INDEXES by issuing the following query:

SELECT INDNAME, INDSCHEMA
  FROM SYSCAT.INDEXES
  WHERE IID = <index-id>
  AND TABSCHEMA = 'schema'
  AND TABNAME = 'table'

where 'schema' represents the schema portion of table-name and 'table' represents the table name portion of table-name.

The statement cannot be processed. The table remains unchanged.

User response

Examine the definition for the index identified by index-id.

For an UPDATE statement, ensure that the specified operation is not itself inconsistent with the uniqueness constraint. If this does not show the error, examine the object table content to determine the cause of the problem.

For an INSERT statement, examine the object table content to determine which of the values in the specified value list violates the uniqueness constraint. Alternatively, if the INSERT statement contains a subquery, the object table contents addressed by that subquery must be matched against the object table contents to determine the cause of the problem. Note that if the table is protected using a security policy, your LBAC credentials may not allow you to see the rows that are causing the error.

If the index is on an XML column and the statement is either INSERT or UPDATE, consider whether the XML document would result in duplicate values from within the single XML document.

For a DELETE statement, examine the identified dependent table for unique constraints on foreign keys that are defined with the rule ON DELETE SET NULL. This table has a foreign key column included in the identified unique index that cannot be set to null since there is already a null in the column for that table.

Federated system users: isolate the problem to the data source failing the request and examine the index definitions and data for the conditions listed previously.

sqlcode: -803

sqlstate: 23505

SQL0804N The application program parameters for the current request are not valid. Reason code reason-code. If a host variable or SQLVAR in the SQLDA is invalid then: host variable/SQLVAR number = var-number, SQLTYPE = sqltype, SQLLEN = sqllen, host variable/SQLVAR type = input-or-output.

Explanation

An error occurred while processing the current request.

  • The call parameter list, which is created by the precompiler, may not be correct if the application programmer has modified the output of the precompiler or over-written the call parameter list in another way.
  • The SQLDA or host variable(s) in the SQL statement are invalid.
  • The request being made is not supported or is out of context.

Reason codes are interpreted as follows:

100

The request being made is not supported or is out of context.

101

SQLDA.SQLN is less than SQLDA.SQLD

102

SQLVAR.SQLTYPE is invalid.

Federated system users: A data type that you specified is not supported by either the federated server or the data source that you want to access.

103

The length specified in SQLVAR.SQLLEN or SQLVAR2.SQLLONGLEN is incorrect for the SQL type given in SQLVAR.SQLTYPE.

104

Doubling of the SQLVARs is expected, but the SQLDOUBLED field of SQLDA.SQLDAID is not set to '2'. This may be required because of a large object type or a structured type.

105

A double-byte character Large Object has an odd value indicated by the SQLVAR2.SQLDATALEN pointer, which is always in terms of bytes, even for DBCLOBs.

106

The SQLDATA pointer is invalid or points to insufficient storage.

107

The SQLIND pointer is invalid or points to insufficient storage.

108

The SQLDATALEN pointer is invalid or points to insufficient storage.

109

A specific number of host variables/SQLVARS is expected for the current SQL statement.

110

The LOB locator is not associated with a LOB of a compatible type.

111

A LOB is indicated by the SQLTYPE of the SQLVAR, but the second SQLVAR is null.

112

The SQLDATATYPE NAME field is not valid. It does not conform to the format for identifying an existing user-defined type in the database. The format for identifying an existing user-defined type is as follows: 8 bytes, followed by a period, followed by 18 bytes.

113

The SQLFLAG4 field is not valid. If a structured type is specified the value must be X'12'. If a reference type is specified the value must be X'01'. Otherwise the value must be X'00'.

115

The XML Subtype Indicator is set in the SQLVAR.SQLNAME field for an SQLVAR.SQLTYPE that cannot contain XML data.

In SQL statements with host variables, use the host variable number to count in from the beginning of the statement (or substatement, in the case of compound SQL) to locate the invalid host variable. For statements using an SQLDA, the SQLVAR number is used to locate the invalid SQLVAR. For an input SQLDA, count only input host variables or SQLVARs; similarly for output. Note that this number is based at 1.

The statement cannot be processed.

User response

Examine the application program for any errors noted. Note that the programmer should not attempt to modify the precompiler output.

Federated system users: If you received reason code 102, specify a supported data type and resubmit the program.

sqlcode: -804

sqlstate: 07002

SQL0805N Package package-name was not found.

Explanation

The statement cannot complete because the necessary package was not found in the catalog.

The package-name is either in the form:

  • 'pkgschema.pkgname 0Xcontoken', where the consistency token is given in hexadecimal.
  • 'pkgschema.pkgname.pkgversion', if the package version is the empty string then '.pkgversion' is omitted from the name.
  • '%.pkgname' if the CURRENT PACKAGE PATH is set. The set of schema names in the CURRENT PACKAGE PATH are implied by the percent character ('%').

Possible causes for this message (SQLCODE) are:

  • The package was not bound or it was dropped.
  • If attempting to run a DB2 utility or CLI application, the DB2 utilities may need to be rebound to the database.
  • '%.pkgname' if the CURRENT PACKAGE PATH is set but no package with the name 'pkgname' could be found in any of the schemas in the CURRENT PACKAGE PATH.

Note that when version-ids are in use for the given package-schema.package-name, there may be packages defined with the same package schema and package name, but the correct package is not found since the existing packages do not match the requested version or consistency token. A package must match all three parts of the package-name. When multiple versions are being used, additional causes for this messages are:

  • the version of the application being executed was precompiled, compiled, and linked, but was not bound, or it was bound but that version of package was subsequently dropped.
  • the application was precompiled and bound, but not compiled and/or linked, so the application being executed is not up to date.
  • the package was bound from a bind file generated by a different precompile of the source file than the precompile that produced the modified source file that was compiled and linked into the application executable.
  • a new application was bound with the same name (and version) of an existing package, thus replacing the existing package. If the application associated with the replaced package is run, this error would result.

In all these cases, the consistency token of the request does not match the consistency token of the existing version and thus the package is considered to be not found.

The statement cannot be processed.

User response

Specify the correct package name or bind the program. If the application being run is not bound to the database, contact the database administrator to do the necessary binding. Ensure that the application or object module being executed is the compiled and linked modified source code associated with the precompile and bind that generated the package.

If the CURRENT PACKAGE PATH is set, ensure that the schema that includes the package is specified in the CURRENT PACKAGE PATH.

The following SQL statements can be used to query the catalog to determine if there are different versions of the package.

SELECT PKGSCHEMA, PKGNAME,
       PKGVERSION, UNIQUE_ID
FROM SYSCAT.PACKAGES
WHERE PKGSCHEMA = 'pkgschema'
      and PKGNAME='pkgname'.

Note that the UNIQUE_ID column corresponds to the consistency token.

If the DB2 utility programs need to be rebound to the database, the database administrator can accomplish this by issuing one of the following CLP command from the bnd subdirectory of the instance, while connected to the database:

  • "db2 bind @db2ubind.lst blocking all grant public" for the DB2 utilities.
  • "db2 bind @db2cli.lst blocking all grant public" for CLI.

Federated system users: ensure that the packages required for the federated server are bound at the applicable data sources. Refer to the Federated Systems Guide for more information about binding packages to data sources.

sqlcode: -805

sqlstate: 51002

SQL0808N The CONNECT statement semantics are not consistent with those of other existing connections.

Explanation

The CONNECT statement originates from a source file which was precompiled having different connection options (SQLRULES, CONNECT type, SYNCPOINT, or RELEASE type) than that of a source file for which a connection exists.

User response

Ensure that all source files are precompiled using the same CONNECT options, or if this is not possible, call the SET CLIENT api to set the desired options for the application process before issuing the first CONNECT statement.

sqlcode: -808

sqlstate: 08001

SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.

Explanation

One of the following caused the error:

  • Execution of an embedded SELECT INTO or VALUES INTO statement resulted in a result table of more than one row.
  • Execution of a scalar fullselect resulted in a result table of more than one row.

Federated system users: this situation can be detected by federated server or by the data source.

The statement cannot be processed.

User response

Ensure that the statement contains the proper condition specifications. If it does, there may be a data problem that is causing more than one row to be returned when only one is expected.

Federated system users: isolate the problem to the data source failing the request and examine the selection criteria and data for that object.

sqlcode: -811

sqlstate: 21000

SQL0817N The SQL statement cannot be executed because the statement will result in a prohibited update operation.

Explanation

The application attempted to execute an SQL statement that would result in updates to user data or to the subsystem catalog. This is prohibited for one of the following reasons:
  • The application is running as an IMS inquiry-only transaction.
  • The application is an IMS or CICS application that is attempting to update data at a remote DBMS that does not support two-phase commit.
  • The application is attempting to update data at multiple locations and one of the locations does not support two-phase commit.

These SQL statements include INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, and REVOKE.

The statement cannot be executed.

User response

If the application is running as an IMS inquiry-only transaction, see your IMS system programmer about changing the inquiry-only status of the transaction under which your application is running.

If the IMS or CICS application is attempting a remote update, either the application must be changed to run as a local application on the server DBMS, or the server DBMS must be upgraded to support two-phase commit.

If the application is attempting to update data at multiple locations, either the application must be changed, or all DBMSs involved must be upgraded to support two-phase commit.

sqlcode: -817

sqlstate: 25000

SQL0818N A timestamp conflict occurred.

Explanation

The timestamp generated by the precompiler at precompile time is not the same as the timestamp stored with the package at bind time.

This problem can be caused by the following:
  • The application was precompiled, compiled, and linked, but was not bound.
  • The application was precompiled and bound, but not compiled and/or linked, so the application being executed is not up to date.
  • The package was bound from a bind file generated by a different precompile of the source file than the precompile that produced the modified source file that was compiled and linked into the application executable.
  • A new application was bound of the same name and existing package, thus replacing the existing package. If the application associated with the replaced package is run, this error would result.

Federated system users: in addition to the causes listed previously, the problem can also be due to not binding the required packages at all applicable data sources.

The statement cannot be processed.

User response

Bind the application again, using the bind file for the program that matches the object module. Or, execute the program that corresponds to the package stored in the database.

If installing the sample database, record the number and text of this message and then contact your technical service representative.

Federated system users: in addition to the previously listed actions, ensure that the packages required for the federated server are bound at the applicable data sources. Refer to the Federated Systems Guide for more information on binding packages to data sources.

sqlcode: -818

sqlstate: 51003

SQL0822N The SQLDA contains a data address or indicator variable address which is not valid.

Explanation

The application program placed an address that is not valid in the SQLDA.

The statement cannot be processed.

User response

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

sqlcode: -822

sqlstate: 51004

SQL0840N Too many items were returned in a SELECT list.

Explanation

The number of items returned in the SELECT list exceeds the allowable maximum. The maximum for SELECT lists (other than on common table expressions) is 1012. The maximum for SELECT lists in common table expressions is 5000.

The statement cannot be processed.

User response

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 possible, rewrite the SQL statement so only the necessary items of information are returned. If all the information is necessary, break the SQL statement into two or more statements.

sqlcode: -840

sqlstate: 54004

SQL0842N A connection to server server-name already exists.

Explanation

SQLRULES(STD) is in effect and a CONNECT statement identifies an existing SQL connection.

User response

The correction depends on the error:
  • If the server name is not the intended name, correct it.
  • If SQLRULES(STD) is in effect and the CONNECT statement identifies an existing SQL connection, replace the CONNECT with SET CONNECTION or change the option to SQLRULES(DB2).

Correct the error in the application and try again.

sqlcode: -842

sqlstate: 08002

SQL0843N The server name does not specify an existing connection.

Explanation

A statement, command, or API specified a server name that does not identify an existing SQL connection of the application process.

This may have occurred using:
  • a SET CONNECTION statement
  • a RELEASE statement
  • a DISCONNECT statement
  • SET or QUERY CLIENT INFORMATION

User response

The correction depends on the error:
  • If the server name is not the intended name, correct it.
  • Ensure that a connection to the server has been established and is in a current or dormant state before issuing the request for that connection.

Correct the error in the application and try again.

sqlcode: -843

sqlstate: 08003

SQL0845N A PREVIOUS VALUE expression cannot be used before the NEXT VALUE expression generates a value in the current session for sequence sequence-name.

Explanation

A PREVIOUS VALUE expression specified sequence sequence-name, but a value has not yet been generated for this sequence. A NEXT VALUE expression must be issued in this session to generate a value for this sequence before a PREVIOUS VALUE expression for the sequence can be issued.

User response

Issue at least one NEXT VALUE expression for a sequence before issuing any PREVIOUS VALUE expression for the same sequence in a session.

sqlcode: -845

sqlstate: 51035

SQL0846N Invalid specification of an identity column or sequence object object-type object-name. Reason code = reason-code.

Explanation

For an identity column or sequence object, the specification of an attribute in a CREATE or ALTER statement may be invalid for one of the following reasons:
  1. The underlying data type of the identity column or sequence object is not supported. Identity columns and sequence objects support the following data types: SMALLINT, INTEGER, BIGINT and DECIMAL (or NUMERIC) with a scale of zero.
  2. The value for START WITH, INCREMENT BY, MINVALUE or MAXVALUE is outside of the range for the data type of the identity column or sequence object.
  3. MINVALUE must be less than or equal to MAXVALUE.
  4. An invalid value was specified for CACHE. The value must be an INTEGER with a minimum value of 2.

User response

Correct the syntax and resubmit the statement.

sqlcode: -846

sqlstate: 42815

SQL0857N Conflicting options have been specified (option1, option2).

Explanation

Conflicting options have been specified. Either option1 and option2 must be specified together, or the two options must not be specified together.

If IMPLICITLY HIDDEN is specified when defining a column, the column must also be defined as a ROW CHANGE TIMESTAMP column.

The statement cannot be executed.

User response

Depending on the options, either specify the options together or do not specify the options together.

sqlcode: -857

sqlstate: 42867

SQL0859N Access to the Transaction Manager Database failed with SQLCODE SQLCODE.

Explanation

The application was precompiled with SYNCPOINT(TWOPHASE) and requires a Transaction Manager Database to coordinate the two phase commit. Reasons why the Transaction Manager Database is not available might include the following:
  • It was not created.
  • The tm_database field of the database manager configuration file has not been updated and activated with the name of the database.
  • The database exists, but communication to the database failed.

User response

Possible actions:
  • Refer to the SQLCODE that was returned with this message, and follow the appropriate action for that SQLCODE.
  • Verify that the tm_database exists; if not, create a new database or select a database that currently exists for use as the TM database. It is recommended to create a separate database if there are no serious constraints on disk storage.
  • Update the database manager configuration for the TM database using the field tm_database if that has not already been done.
  • Verify that a connection to the tm_database can be made. For example, attempt the connection using the Command Line Processor.
  • Ensure that the tm_database chosen is not a database accessed through DB2 Connect.

sqlcode: -859

sqlstate: 08502

SQL0863W A successful connection was made, but only single byte characters should be used.

Explanation

The server database and client application are using codepages for different language types and any characters outside the 7-bit ASCII range cannot be guaranteed (only the characters in the 7-bit ASCII range exist in all codepages). For example, the connection might be between a Japanese and a Latin-1 codepage, but none of the Japanese characters will be available in the Latin-1 codepage, so all of these characters should be avoided (English characters are fine though).

Federated system users: possible causes are:
  • The federated database supports single-byte and double-byte characters but the database client system supports only single-byte characters.
  • The data source supports single-byte and double-byte characters but the federated system supports only single-byte characters.

User response

Do not submit SQL statements or commands that use characters which are not common between the application and database codepages.

Federated system users: do not submit SQL statements or commands that use characters which are not common between the client system, the federated system, and the data source.

sqlcode: +863

sqlstate: 01539

SQL0864N A referential constraint constraint-name attempted to modify a row in table table-name that was modified by an SQL data change statement within a fullselect.

Explanation

An SQL data change statement was specified in the FROM clause, but the underlying target base table of the SQL data change statement has a referential constraint which modifies the same table. This is disallowed.

The statement cannot be processed.

User response

Avoid using the SQL data change statement within a FROM clause, or change the referential constraint so that it does not modify the table that is the target of the SQL data change statement.

sqlcode: -864

sqlstate: 560C6

SQL0865N Invalid tm_database value.

Explanation

The database chosen as the tm_database in the database manager configuration is not valid. The database must be at level DB2 V2.1 or later, and cannot be a database accessed through DRDA protocol (ie. through DB2 Connect).

The statement cannot be executed.

User response

  1. Update the database manager configuration to specify a valid database for the tm_database parameter.
  2. Issue db2stop and db2start to ensure that the change takes effect.

sqlcode: -865

sqlstate: 08001

SQL0866N The data server driver or data server client terminated the connection to the database named database-name because the <alternategroup> section of the db2dsdriver.cfg file is not valid.

Explanation

You can specify multiple alternate database servers for automatic client reroute to connect to, in the event that the connection to a database fails, using the <alternategroup> section of the db2dsdriver.cfg file. This functionality is supported in DB2 for Linux, UNIX, and Windows and DB2 for z/OS servers.

There are some restrictions on the contents of the <alternategroup> section of the db2dsdriver.cfg file when connecting to DB2 for z/OS database servers. If a database, named db_A, is located on a DB2 for z/OS database server, there can be no more than one <database> entry in the <alternategroup> section for db_A.

This message is returned when one of these situations occur:

  1. Alternate groups is not supported on the database server where the database named database-name resides.
  2. The db2dsdriver.cfg contents do not satisfy the DB2 for z/OS restrictions for the <alternategroup> section.

User response

In the db2dsdriver.cfg file, modify the <alternategroup> sections that are related to the given database, database-name, so that those entries and sections satisfy the <alternategroup> support restrictions:

  1. Remove the <alternategroup> section in the db2dsdriver.cfg file.
  2. Specify only one database entry in the <alternategroup> section.

sqlcode: -866

sqlstate: 08001

SQL0868N A CONNECT using a USER/USING clause was attempted to a server for which a connection already exists.

Explanation

A current or dormant connection to a server exists, and an attempt to CONNECT to this server using the USER/USING clause is invalid.

User response

Possible actions:
  • Use the SET CONNECTION statement to make the connection to the DORMANT connection current.
  • Use the CONNECT statement without USER/USING if SQLRULES(DB2) are being used by the application.
  • Complete the existing unit of work, disconnect, and then reconnect using USER/USING.

sqlcode: -868

sqlstate: 51022

SQL0873N Objects encoded with different encoding schemes cannot be referenced in the same SQL statement.

Explanation

All tables, SQL functions, and SQL methods referenced in the SQL statement are not defined with the same encoding scheme.

This situation can occur when:
  • a table created with either the ASCII or Unicode encoding scheme is referenced in a statement with a table that was not created with the same encoding scheme
  • an SQL function or SQL method created with either the ASCII or Unicode encoding scheme is referenced in a statement with a table that was not created with the same encoding scheme
  • a sourced function is created with a different encoding scheme than the source function
  • an exception table is created with a different encoding scheme than the base table for the operation

User response

Correct the SQL statement so that only objects with the same encoding scheme are referenced.

sqlcode: -873

sqlstate: 53090

SQL0874N The CCSID of all the parameters must match the PARAMETER CCSID of the routine.

Explanation

All the parameters of a routine must use the same encoding scheme as the routine itself. If the CCSID is specified for a parameter, it must match the implicitly or explicitly specified PARAMETER CCSID option for the routine.

User response

Remove the CCSID option from the parameter or change the statement so the same CCSID value is specified throughout.

sqlcode: -874

sqlstate: 53091

SQL0880N SAVEPOINT savepoint-name does not exist or is invalid in this context.

Explanation

An error occurred issuing a RELEASE or ROLLBACK TO SAVEPOINT savepoint-name statement. Either a savepoint with that name does not exist, or it was established outside the current atomic execution context.

The statement cannot be processed.

User response

Correct the name of the savepoint in the statement and reissue the statement.

sqlcode: -880

sqlstate: 3B001

SQL0881N A SAVEPOINT with name savepoint-name already exists, but this savepoint name cannot be reused.

Explanation

The name savepoint-name has already been used in a SAVEPOINT statement. The savepoint name cannot be reused because at least one of the SAVEPOINT statements that used this name also specified the UNIQUE keyword, which asserts that the name must be unique.

The statement cannot be processed. The new savepoint is not set. The old savepoint with the same name still exists.

User response

Choose another name for this savepoint and reissue the SAVEPOINT statement. If this existing savepoint name must be reused, issue the RELEASE SAVEPOINT statement to free the existing savepoint. However, be aware that the RELEASE SAVEPOINT statement also releases any savepoints that were established in the transaction since the specified savepoint was established. For more information, refer to the SQL Reference.

sqlcode: -881

sqlstate: 3B501

SQL0882N Savepoint does not exist.

Explanation

An error occurred issuing a ROLLBACK TO SAVEPOINT statement. Issuing a ROLLBACK TO SAVEPOINT without specifying a specific savepoint name is not allowed when there are no existing savepoints.

The statement cannot be processed.

User response

Issue a different statement, or try rolling back the entire transaction with the ROLLBACK statement.

sqlcode: -882

sqlstate: 3B502

SQL0900N The application state is in error. A database connection does not exist.

Explanation

A connection to a database does not exist. This may be due to one of the following reasons:
  • A serious error in the application state has caused the database connection to be lost.
  • The application may have disconnected from a database and not established a new current connection before executing the next SQL statement.
  • A request to switch the user in a trusted connection was unsuccessful.

User response

Reestablish a current connection by either switching to an existing dormant connection (using CONNECT TO or SET CONNECTION), by establishing a new connection (using CONNECT), or by successfully switching to another user in a trusted connection.

sqlcode: -900

sqlstate: 08003

SQL0901N The SQL statement or command failed because of a database system error. (Reason reason.)

Explanation

There are many scenarios in which this message can be returned. Here are two examples of scenarios in which this message can be returned:

  • Windows operating systems: One reason that this error can be returned on Windows operating systems is that anti-virus or firewall software is preventing the database manager from reading from or writing to DB2 database or database manager-related files.
  • Migration and upgrade scenarios: One reason that this error can be returned after an upgrade to a new version or after the application of a fix pack is because statistics about one or more tables, associated indexes, or statistical views are not up-to-date.

In some cases, although this attempt to execute the SQL statement failed, future attempts to execute this SQL statement or other statements might succeed.

The runtime token, reason, might be empty or it might contain English text that is intended for IBM software support personnel only.

User response

Respond to this error by performing the following troubleshooting steps:

Windows operating systems:

Disable anti-virus or firewall software.

If anti-virus or firewall software is installed and running on the computer on which DB2 database is installed, determine whether the anti-virus or firewall software is causing the problem by disabling the anti-virus or firewall software and executing the statement again.

If disabling anti-virus or firewall software resolves the problem, add exceptions for the following DB2 database-related directories before enabling the anti-virus or firewall software again:

  • "IBM\\sqllib" - DB2 database application files
  • "IBM\\DB2" - database manager instances
  • "<install-drive>\\DB2" - Database partition directories
Migration and upgrade scenarios:

Update statistics using the RUNSTATS command, and then execute the statement again.

Federated environments:
  1. Determine which federated data source or federated database server is returning the error.
  2. Collect diagnostic information and perform troubleshooting steps for data source or federated database server that is returning the error.

    Refer to diagnostic and troubleshooting reference information for the data source or federated database server that is returning the error because the problem determination procedures for data sources vary.

If the error continues after performing the troubleshooting steps described, contact IBM software support for assistance:

  1. Collect diagnostic information using trace facilities such as the DB2 trace and the Independent Trace Facility.
  2. Collect the following diagnostic information:
    • Problem description
    • SQLCODE
    • Reason reason
    • SQLCA contents if possible
    • Trace files, if possible.
  3. Contact IBM software support.

sqlcode: -901

sqlstate: 5UA0L, 58004

SQL0902C A system error occurred. Subsequent SQL statements cannot be processed. IBM software support reason code: reason-code.

Explanation

This message is returned when the database manager encounters a critical error, such as a severe operating system error, or an error accessing storage media, which might prevent a database from continuing to be usable. There are different scenarios in which the database manager might encounter an operating system error, or a severe media error. Here is one example of a scenario in which this message can be returned:

  • This message can be returned when a database manager resource requires a semaphore, but there are not enough semaphores to satisfy the request.

The runtime token, reason-code, might sometimes be empty, and is intended to assist IBM software support personnel only.

User response

Respond to this message by performing the following troubleshooting steps:

  1. Review diagnostic information in db2diag log files to identify errors that occurred before this message was returned.
  2. If there are messages in the db2diag log files indicating that too many semaphores have been requested, or that there are not enough semaphores to serve the database manager requests, increase the number of semaphores using operating system parameters.

If the error continues after performing the troubleshooting steps described, contact IBM software support for assistance:

  1. Collect diagnostic information using trace facilities such as the DB2 trace and the Independent Trace Facility.
  2. Collect the following diagnostic information:
    • Problem description
    • SQLCODE
    • Reason reason
    • SQLCA contents if possible
    • Trace files, if possible
  3. Contact IBM software support.

Federated system users: isolate the problem to the data source failing the request and take the necessary diagnostic steps for that data source. The problem determination procedures for data sources vary, so refer to the applicable data source manuals.

sqlcode: -902

sqlstate: 58005

SQL0903N COMMIT statement failed, transaction rolled back. Reason code: reason-code.

Explanation

One or more of the servers participating in the current unit of work was unable to prepare the database to be committed. The COMMIT statement has failed and the transaction has been rolled back. If there is only one server participating in the current unit of work, the transaction may have been committed instead.

Possible reason codes are:

01

A connection to one of the databases participating in the unit of work was lost.

02

One of the databases or nodes participating in the unit of work was accessed, but unable to prepare to commit.

Federated system users: if the database you connected to is a federated server database where nicknames are used, one of the data sources required for a nickname is unable to prepare to commit.

03

A DB2 Data Links Manager participating in the unit of work was unable to prepare to commit.

04

One or more created temporary tables, or declared temporary tables are in an inconsistent state.

05

An unexpected error occurred. Check the administration notification log for details.

Federated system users: if the database you connected to is a federated server database where nicknames are used, a connection to one of the data sources required for a nickname within the database was lost.

06

Unable to send Resync Information to one of the participants. The participant only supports IPv4. Please enable dual-stack mode for the participant.

User response

If a connection to a database was lost, reestablish the connection. If the failure was not connection related, reference the error diagnostic logs on the remote system to determine the nature of the failure and what action might be required. Rerun the application.

sqlcode: -903

sqlstate: 40504

SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: reason-code, type of resource: resource-type, and resource name: resource-name.

Explanation

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 the Problem Determination documentation of Db2® for z/OS® for an explanation of resource type codes.

User response

Verify the identity of the resource that was not available. To determine why the resource was unavailable, refer to the specified reason-code.

sqlcode: -904

sqlstate: 57011

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

Explanation

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.

User response

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.

sqlcode: -905

sqlstate: 57014

SQL0906N The SQL statement cannot be executed because this function is disabled due to a prior error.

Explanation

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.

User response

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.

sqlcode: -906

sqlstate: 24514, 51005, 58023

SQL0907N An attempt was made to modify the target table, table-name, of the MERGE statement by constraint or trigger name.

Explanation

The MERGE statement caused constraint or trigger name to activate, which attempted to update, insert into or delete from table table-name that is also a target table, or table within the same table hierarchy, of the MERGE statement. This is not allowed.

User response

Change the MERGE statement to remove the operation that causes the constraint or trigger to activate, or alter the table that has the constraint or trigger to remove the reference to the target table.

sqlcode: -907

sqlstate: 27000

SQL0908N bind-type error using auth-id authority BIND, REBIND, or AUTO_REBIND operation is not allowed.

Explanation

For BIND and REBIND, the indicated authorization ID is not allowed to perform the indicated bind-type against a plan or package. An entry in the resource limit specification table (RLST) prohibits binding and rebinding by this authorization ID, or all authorization IDs. For AUTO-REBIND, the system parameter controlling AUTO-REBIND operations is set to disallow AUTO-REBIND.
bind-type
Type of bind operation (BIND, REBIND or AUTO-REBIND).
auth-id
Authorization ID of the invoker of the BIND subcommand or primary authorization ID of the invoker of the plan for AUTO-REBIND operations.

User response

If the indicated authorization id should be allowed to bind, change the entry in the active RLST table. If AUTO-REBIND operations are disabled, rebind the package before reexecuting the package.

sqlcode: -908

sqlstate: 23510

SQL0909N The object has been deleted.

Explanation

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.

User response

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.

sqlcode: -909

sqlstate: 57007

SQL0910N The SQL statement cannot access an object on which a modification is pending.

Explanation

The application program attempted to access an object within the same unit of work in which either:

  • The application program issued a DROP against the object or a related object (for example, an index for a table)
  • The application program issued a statement against the object that added or dropped a constraint.
  • The application program issued a DROP TRIGGER or CREATE TRIGGER statement that affected the object, either directly or indirectly.
  • The application program issued a ROLLBACK TO SAVEPOINT statement, which placed the object in the modification pending state.
  • The application program issued a statement that caused all rows of a NOT LOGGED created temporary table or declared temporary table to be deleted.
  • The application program issued an ALTER TABLE ... ADD PARTITION, ALTER TABLE ... ATTACH PARTITION or ALTER TABLE ... DETACH PARTITION statement against the object (in this case a table). If a unit of work contains an ALTER TABLE ... ATTACH PARTITION, then subsequently in the same unit of work, an ALTER TABLE ... DETACH PARTITION cannot be done on the same table.
  • The application program issued a DETACH to create this object from a data partition in a partitioned MDC table.
  • The application program issued an SQL statement that attempted to access a trusted context object on which a modification is pending. The SQL statement may be one of the following:
    • ALTER TRUSTED CONTEXT
    • CREATE TRUSTED CONTEXT
    • DROP TRUSTED CONTEXT
  • The application program issued a CREATE USER TEMPORARY TABLESPACE statement or ALTER TABLESPACE statement. If a unit of work contains a CREATE USER TEMPORARY TABLESPACE statement or ALTER STATEMENT statement, then subsequently in the same unit of work, a reference to a created temporary table that instantiates the table cannot be made.

or the application program issued an SQL statement that attempted to access a WLM object on which a modification is pending. The SQL statement may be one of the following:

  • ALTER HISTOGRAM TEMPLATE
  • ALTER THRESHOLD
  • ALTER SERVICE CLASS
  • ALTER WORK ACTION
  • ALTER WORK CLASS
  • ALTER WORKLOAD
  • CREATE HISTOGRAM TEMPLATE
  • CREATE THRESHOLD
  • CREATE SERVICE CLASS
  • CREATE WORK ACTION
  • CREATE WORK CLASS
  • CREATE WORKLOAD
  • DROP of any of these objects: HISTOGRAM TEMPLATE, THRESHOLD, SERVICE CLASS, WORK ACTION, WORK CLASS, WORKLOAD
  • GRANT (Workload privileges)
  • REVOKE (Workload privileges)

Federated system users: in addition to the previously listed causes, other data source specific restrictions may exist that prevent access to the object.

The SQL statement cannot be processed.

User response

Modify the application program so there is no attempt to access an object within the same unit of work in which the modification was made. In general, it is better to isolate Data Definition Language (DDL) statements in a different unit of work than Data Manipulation Language (DML) statements that access the same objects.

The unit of work must be committed or rolled back before the statement that failed can be processed successfully. If the committed modification caused an object to be dropped, then the object may have to be recreated in order for the failing SQL statement to be processed successfully.

If the object is modified within a SAVEPOINT, modify the application program so that it does not attempt to access the object after the application issues a ROLLBACK TO SAVEPOINT statement. Any cursor that accesses the modified object and is open at the time of the ROLLBACK TO SAVEPOINT will not be accessible. Modify the application so that it closes the cursor.

If an insert, delete or update statement involving a NOT LOGGED created temporary table or declared temporary table fails, then all rows in that table are deleted. Any cursor that is open against that table at the time of the failure will not be accessible and should be closed by the application.

If an ALTER TABLE ... DETACH PARTITION fails because of a pending ALTER TABLE ... ATTACH PARTITION, use two different units of work to do the two operations.

If the SQL statement attempted to access a trusted context object, then reissue the statement later.

If the SQL statement attempted to access a WLM object, then reissue the statement later.

Federated system users: if the previous action does not solve the problem, isolate the request and determine what additional constraints exist on the data source that are preventing access to the object. Ensure that the application is not violating any of those constraints.

sqlcode: -910

sqlstate: 57007

SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code reason-code.

Explanation

The current unit of work was involved in an unresolved contention for use of an object and had to be rolled back.

The reason codes are as follows:

2

The transaction was rolled back due to a deadlock.

68

The transaction was rolled back due to a lock timeout.

72

The transaction was rolled back due to a DB2 Data Links Manager error during the transaction.

73

The transaction was rolled back because a queuing threshold such as the CONCURRENTDBCOORDACTIVITIES threshold caused two or more activities to reach a deadlock state.

74

Similar to reason code 73, the transaction was rolled back because two or more activities reached a deadlock state.

75

The transaction was rolled back because two or more applications are deadlocked waiting for a combination of resources governed by workload manager admission control and locks.

76

The transaction was rolled back because two or more applications are deadlocked waiting for resources governed by workload manager admission control.

The application was rolled back to the previous COMMIT.

User response

The changes associated with the unit of work must be entered again.

To help avoid deadlock or lock timeout, issue frequent COMMIT operations, if possible, for a long-running application, or for an application likely to encounter a deadlock.

Federated system users: the deadlock can occur at the federated server or at the data source. There is no mechanism to detect deadlocks that span data sources and potentially the federated system. It is possible to identify the data source failing the request (refer to the problem determination guide to determine which data source is failing to process the SQL statement).

Deadlocks are often normal or expected while processing certain combinations of SQL statements. It is recommended that you design applications to avoid deadlocks to the extent possible.

If a deadlock state was reached because of a queuing threshold such as the CONCURRENTDBCOORDACTIVITIES threshold, increase the value of the queuing threshold.

If the deadlock state was reached because the application is waiting for resources governed by workload manager admission control, increase the resourceshare for the service class in which the last statement of the application was executing. To help avoid deadlocks involving resources governed by workload manager admission control, reduce the number of concurrently executing SQL statements in applications by closing SQL cursors when they are no longer needed.

sqlcode: -911

sqlstate: 40001

SQL0912N The maximum number of lock requests has been reached for the database. Reason code = reason-code

Explanation

The maximum number of locks for the database was reached because of insufficient memory for the lock request.

The reason code indicates the type of memory limit that was reached:

1

Local lock manager memory, which is configured using the LOCKLIST database configuration parameter.

2

Global lock manager memory, which is configured using the CF_LOCK_SZ database configuration parameter.

The statement cannot be processed.

User response

The application should submit a COMMIT or ROLLBACK statement before submitting other SQL statements. Consider increasing the value of the database configuration parameter indicated by the reason code to support more lock requests.

1

Increase the LOCKLIST database configuration parameter, which manages lock memory allocated for the local lock manager.

2

Increase the CF_LOCK_SZ database configuration parameter, which manages lock memory allocated for the global lock manager.

sqlcode: -912

sqlstate: 57011

SQL0913N Unsuccessful execution of a distributed transaction caused by deadlock or timeout. Reason code reason-code.

Explanation

The request issued by a distributed transaction was involved in an unresolved contention for use of an object and the execution failed.

The reason codes are as follows:

2

transaction branch failed due to deadlock.

68

transaction branch failed due to lock timeout.

72

transaction rolled back due to an error concerning a DB2 Data Links Manager involved in the transaction.

80

statement failed due to timeout.

208

statement failed because the database manager detected that the row being updated or deleted has been modified by another application.

The text of this message and the runtime tokens might vary, depending on the version and platform of the database server or client.

User response

  • For reason code 80, you can retry the failed statement without terminating the application. If the application accesses multiple remote databases, it may be better to rollback the transaction to avoid the possibility of a global deadlock.
  • For other reason codes, issue a request to rollback the transaction. The transaction cannot be committed due to the failure of the current transaction branch.
  • To help avoid deadlock or lock timeout, issue frequent COMMIT operations, if possible, within long-running applications or applications requiring data with high concurrent access.

sqlcode: -913

sqlstate: 57033

SQL0917N Bind package failed.

Explanation

An error has occurred which prevents the package from being created.

This SQLCODE can be issued during bind or commit processing. If issued during commit processing, all changes to the database are rolled back. If issued during bind processing, only package creation fails and other changes within the logical unit of work are still able to be committed.

User response

This problem is usually caused by one or more SQL statements which did not get bound because of an error.

Determine which statements are causing the error and correct them. Reissue the command to create the package.

sqlcode: -917

sqlstate: 42969

SQL0918N Application must execute a rollback.

Explanation

The unit of work has already been rolled back in the database but other resource managers involved in this unit of work might not. To ensure integrity of this application, all SQL requests will be rejected until the application issues a rollback.

User response

All SQL requests will be rejected until the application issues a rollback. For example, in a CICS environment, this would be a CICS SYNCPOINT ROLLBACK command.

sqlcode: -918

sqlstate: 51021

SQL0920N Data on a database client system cannot be accessed from other database client systems.

Explanation

The workstation has been configured as a client or a server with local clients. Any databases created on this system cannot be shared by other workstations.

The function cannot be processed.

User response

Request data only from server workstations.

sqlcode: -920

sqlstate: 57019

SQL0925N SQL COMMIT invalid for application execution environment.

Explanation

COMMIT is disallowed in the following cases:
  • In a Distributed Transaction Processing environment such as CICS, a static SQL COMMIT statement was attempted, but a commit statement specific to the environment is required. For example, in a CICS environment this would be the CICS SYNCPOINT command.
  • A DB2 application precompiled or set to use CONNECT 2 in a non-TP Monitor environment has issued a dynamic SQL COMMIT statement, whereas only static SQL COMMITs are allowed.
  • When issued from a stored procedure, SQL COMMIT is also not allowed if the calling program is executing in a distributed unit of work or Distributed Transaction Processing environment.

User response

Correct the problem by doing one of the following:
  • Remove the statement issuing the COMMIT and replace it with a statement which does the valid equivalent for the environment
  • In the case of a connect type 2 in a non-TP Monitor environment, use only static COMMIT.
  • In the case of a stored procedure, remove the COMMIT

sqlcode: -925

sqlstate: 2D521

SQL0926N SQL ROLLBACK invalid for application execution environment.

Explanation

ROLLBACK is disallowed in the following cases:
  1. In a Distributed Transaction Processing environment such as CICS, a static SQL ROLLBACK statement was attempted, but a rollback statement specific to the environment is required. For example, in a CICS environment this would be the CICS SYNCPOINT ROLLBACK command.
  2. A DB2 application precompiled or set to use CONNECT 2 has issued a dynamic SQL ROLLBACK statement, whereas only static SQL ROLLBACKs are allowed.
  3. When issued from a stored procedure, SQL ROLLBACK is also restricted if the calling program is executing in a distributed unit of work (CONNECT type 2) or Distributed Transaction Processing environment.

User response

  1. Remove the statement issuing the ROLLBACK and replace it with a statement which does the valid equivalent for the environment.
  2. In the case of a connect type 2, use only static COMMIT.
  3. In the case of a stored procedure, remove it entirely.

sqlcode: -926

sqlstate: 2D521

SQL0930N There is not enough storage available to process the statement.

Explanation

A request was made to the database that required another memory page but no more pages are available to the database manager.

The statement cannot be processed.

User response

Possible solutions include:

  • Verify that your system has sufficient real and virtual memory.
  • Remove background processes.
  • If the error occurs during DUOW resynchronization, reduce the RESYNC_INTERVAL database manager configuration parameter value.
  • If the error occurs on a statement which referenced a routine (UDF, stored procedure or method), it may be that the storage required to contain the arguments and return values was more than the memory available in the system. This might occur if the routine is defined with BLOB, CLOB, DBCLOB parameters or return values, having a large size (2GB for example).

If these solutions do not solve the problem, it may be necessary to consider changing the definition of the routine.

sqlcode: -930

sqlstate: 5UA0L, 57011

SQL0931C Operating system file table overflow occurred. Subsequent SQL statements cannot be processed.

Explanation

An Operating system limit has been reached. The application program is not permitted to issue additional SQL statements. The database is marked as needing recovery and all applications using the database are prevented from accessing the database.

User response

Terminate all the applications using the database. Restart the database.

To help prevent the problem from recurring:
  • Change the MAXFILOP database configuration parameter to a smaller value (this will reduce DB2's use of the operating system file table), and/or
  • Terminate other applications that are using files, if appropriate, and/or,
  • Refer to the operating system documentation for increasing the operating system file table limit. In most UNIX environments this can be accomplished by updating the kernel configuration with a larger value. (On AIX, this may only be possible by increasing the amount of memory in your machine).

sqlcode: -931

sqlstate: 58005

SQL0949N An invalid operating system operation was attempted by a UTL_FILE module routine. Operating system error = error-text.

Explanation

The operation attempted by a UTL_FILE module routine caused the operating system to return an error, as indicated in error-text.

User response

Refer to the operating system error message information to determine how to correct the problem reported by error-text.

sqlcode: -949

sqlstate: 58024

SQL0950N The table or index cannot be dropped because it is currently in use.

Explanation

A DROP TABLE or DROP INDEX statement cannot be issued when an open cursor is currently using the table or index.

The statement cannot be processed. The table or index is not dropped.

User response

Close any cursors required and resubmit the statement.

sqlcode: -950

sqlstate: 55006

SQL0951N The object object-name of type object-type cannot be altered because it is currently in use by the same application process.

Explanation

An ALTER statement, SET INTEGRITY, or TRUNCATE statement statement for an object cannot be issued when it is either locked or in use.

The statement cannot be processed. The object is not altered.

User response

Close any cursors that depend either directly or indirectly on the object object-name and resubmit the statement.

sqlcode: -951

sqlstate: 55007

SQL0952N Processing was cancelled due to an interrupt.

Explanation

The user may have pressed the interrupt key sequence.

The statement processing is terminated. Some changes may have been applied to the database, but not committed, before termination occurred.

You might also receive this error if Call Level Interface (CLI) issues an interrupt because a query timed out. For example: if an application uses the SQLSetStmtAttr() function to configure the SQL_ATTR_QUERY_TIMEOUT statement attribute to a non-zero value, then if a SQL statement or XQuery expression does not complete in the specified amount of time, CLI will issue an interrupt to cancel the execution and return to the application.

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

User response

Continue the application.

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

If you want to prevent CLI from issuing an interrupt when a query times out, you can disable query timeout behavior by setting QUERYTIMEOUTINTERVAL=0 in the db2cli.ini file. Disabling query timeout behavior will allow queries run to completion, unless there is a deadlock or lock wait timeout value set at the server.

sqlcode: -952

sqlstate: 57014

SQL0954C Not enough storage is available in the application heap to process the statement.

Explanation

All available memory for the application has been used.

The statement cannot be processed.

User response

Terminate the application on receipt of this message. If the applheapsz database configuration parameter is set to AUTOMATIC, you will need to increase either the APPL_MEMORY database configuration setting, or the INSTANCE_MEMORY database manager configuration setting. Otherwise, increase the applheapsz database configuration parameter to allow a larger application heap.

When updating configuration parameters, it is recommended to change them by 10% of the current size at a time until the error condition is resolved. To change applheapsz, enter a command similar to the following, which sets applheapsz to size 4000 for database sample:
db2 UPDATE DB CFG FOR sample USING APPLHEAPSZ 4000

If all associated configuation parameters are set to either AUTOMATIC or COMPUTED, then the memory demands of the instance exceed the amount of memory configured on the machine. Possible solutions include reducing the database workload, enabling the connection concentrator feature, or adding additional memory to the machine.

sqlcode: -954

sqlstate: 57011

SQL0955C Sort memory cannot be allocated to process the statement. Reason code = reason-code.

Explanation

Insufficient virtual memory is available to the database agent for sort processing, as indicated by the reason code:

1

Insufficient private process memory.

2

Insufficient shared memory in the database-wide shared memory area designated for sort processing.

3

Insufficient shared memory for queries on column-organized tables.

The statement cannot be processed but other SQL statements might be processed.

User response

Respond to this message by performing the corresponding action for each reason code:

1

Increase the memory resources available at the instance or system level. Alternatively, reduce configured memory requirements. For example, reduce the value of the SORTHEAP database configuration parameter.

2

Increase the memory resources available at the database, instance, or system level. Alternatively, reduce configured memory requirements. For example, reduce the value of the SORTHEAP database configuration parameter.

3

Increase the value of the SORTHEAP database configuration parameter. You might need to increase the setting of higher-level database configuration parameters such as SHEAPTHRES_SHR.

sqlcode: -955

sqlstate: 57011

SQL0956C Not enough storage is available in the database heap to process the statement.

Explanation

All available memory for the database has been used.

The statement cannot be processed.

User response

Terminate the application on receipt of this message.

Increase the dbheap database configuration parameter to allow a larger database heap. If the number of I/O servers is near the high limit, reducing this number might also help.

If the dbheap database configuration parameter is set to AUTOMATIC, you will need to increase either the DATABASE_MEMORY database configuration setting, or the INSTANCE_MEMORY database manager configuration setting.

When updating configuration parameters, it is recommended to change them by 10% of the current size at a time until the error condition is resolved. To change dbheap, enter a command similar to the following, which sets dbheap to size 2400 for database sample:

db2 UPDATE DB CFG FOR sample
  USING DBHEAP 2400

To change dbheap when disconnected from the database, enter a command similar to the following

db2 CONNECT RESET;
db2 UPDATE DB CFG FOR sample
  USING DBHEAP 2400

If all associated configuation parameters are set to either AUTOMATIC or COMPUTED, then the memory demands of the instance exceed the amount of memory configured on the machine. Possible solutions include reducing the database workload, enabling the connection concentrator feature, or adding additional memory to the machine.

sqlcode: -956

sqlstate: 57011

SQL0958C The maximum number of open files has been reached.

Explanation

The maximum number of file handles available to the database has been reached.

The statement cannot be processed.

User response

Increase any parameter that affects the maximum number of open files allowed at the location of the database. This includes increasing the configuration parameter ( maxfilop) to allow more file handles for the instance and terminating other sessions to reduce the number of file handles being used.

sqlcode: -958

sqlstate: 57009

SQL0959C Not enough storage is available in the communication heap of the server to process the statement.

Explanation

All available memory in the server communication heap has been used.

The command or statement cannot be processed.

User response

Terminate the application on receipt of this message. Increase the size of the communication heap ( comheapsz) parameter in the server workstation database manager configuration file.

NOTE: This message is applicable only for releases of DB2 prior toVersion 2 .

sqlcode: -959

sqlstate: 57011

SQL0960C The maximum number of objects have been created in the table spaces with the following names or identifiers table-space-list.

Explanation

You cannot create a new object in the table space because the maximum number of objects for the table space has been reached.

User response

Specify a different table space in which to create the object or table. If the table space is a SYSTEM TEMPORARY table space, there are too many concurrent temporary tables in use.

sqlcode: -960

sqlstate: 57011

SQL0964C The transaction log for the database is full.

Explanation

All space in the transaction log is being used.

If a circular log with secondary log files is being used, an attempt has been made to allocate and use them. When the file system has no more space, secondary logs cannot be used.

If an archive log is used, then the file system has not provided space to contain a new log file.

This message can also be returned when the database has used almost all of the possible log sequence numbers. The database manager identifies database log records using a unique identifier, called a log sequence number (LSN). When transactions cause database logs to be written, the database uses new LSN values.

The application cannot perform any transactions that cause the database to write log records.

The statement cannot be processed.

User response

Execute a COMMIT or ROLLBACK on receipt of this message (SQLCODE) or retry the operation.

If the database is being updated by concurrent applications, retry the operation. Log space may be freed up when another application finishes a transaction.

Issue more frequent commit operations. If your transactions are not committed, log space may be freed up when the transactions are committed. When designing an application, consider when to commit the update transactions to prevent a log full condition.

If deadlocks are occurring, check for them more frequently. This can be done by decreasing the database configuration parameter DLCHKTIME. This will cause deadlocks to be detected and resolved sooner (by ROLLBACK) which will then free log space.

If the condition occurs often, increase the database configuration parameter to allow a larger log file. A larger log file requires more space but reduces the need for applications to retry the operation. Transaction configuration parameters which may need to be adjusted are LOGFILSIZ, LOGPRIMARY, LOGSECOND.

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

If this message was returned because the database used nearly all of the possible LSN values, you can reset the LSN values to zero by doing the following:

  1. unload all data from the database
  2. drop and recreate the database
  3. load all the data

sqlcode: -964

sqlstate: 57011

SQL0965W There is no message text corresponding to SQL warning SQLCODE in the message file on this workstation. The warning was returned from module name with original tokens token-list.

Explanation

Database server returned code SQLCODE to your application. The warning code does not correspond to a message in the DB2 message file on this workstation.

User response

Refer to your database server documentation for more information about the specified SQLCODE.

SQL0966N The error mapping file name specified in the Database Connection Services directory cannot be found or cannot be opened.

Explanation

One of the following conditions is true:
  • The error mapping file does not exist.
  • The error mapping file is currently opened by another application.
  • The error mapping file is not located in the expected path.
  • The error mapping file is damaged.

The error mapping data could not be retrieved.

User response

Release the file from the application in which it is open or reinstall or restore the original file.

sqlcode: -966

sqlstate: 57013

SQL0967N The format of the error mapping file name specified in the Database Connection Services directory is not valid.

Explanation

An error occurred while the program was reading the error mapping file.

The error mapping data could not be retrieved.

User response

Correct all syntactic errors in the error mapping file.

sqlcode: -967

sqlstate: 55031

SQL0968C The file system is full.

Explanation

One of the file systems containing the database is full. This file system may contain the database directory, the database log files, or a table space container.

In a DB2 pureScale environment, if you are adding a member to the database manager instance, there is not enough disk space to create the additional members files.

The statement cannot be processed.

User response

Free system space by erasing unwanted files. Do not erase database files. If additional space is required, it may be necessary to drop tables and indexes identified as not required.

On unix-based systems, this disk full condition may be due to exceeding the maximum file size allowed for the current userid. Use the chuser command to update fsize. A reboot may be necessary.

This disk full condition may be caused when containers are of varying sizes. If there is sufficient space in the file system, drop the table space and recreate it with containers of equal size.

If the statement that could not be processed referenced LOB data types:

  • Ensure that any cursors used in the application are closed immediately after their use.
  • Ensure that within the application that COMMIT statements are periodically executed.
  • Add additional containers to the system temporary tablespace to hold the temporary LOB data during this statement's execution.

In a DB2 pureScale environment, free system space by erasing unwanted files or adding capacity to the file system, then rerun the command.

sqlcode: -968

sqlstate: 57011

SQL0969N There is no message text corresponding to SQL error error in the message file on this workstation. The error was returned from module name with original tokens token-list.

Explanation

The Database server returned SQLCODE error to your application. The error code does not correspond to a message in the DB2 message file on this workstation.

User response

Refer to your database server documentation for information about the specified SQLCODE. Take action specified in the documentation for the database server to fix this problem.

Federated system users: isolate the problem to the data source failing the request. Find the error in the applicable manual at the data source. If the problem is data dependent, it may be necessary to examine the data being processed at the data source at the time the error occurred.

SQL0970N The system attempted to write to a read-only file.

Explanation

A file used by the database is marked read-only or does not exist. The database requires write access to this file.

The statement cannot be processed.

User response

Terminate the application on receipt of this message (SQLCODE). Ensure that all database files are allowed both read and write access. Check for unnecessary blank spaces within the specified filename.

sqlcode: -970

sqlstate: 55009

SQL0972N The database drive does not contain the correct diskette.

Explanation

The diskette in the drive is not the database diskette.

The statement cannot be processed.

User response

Place the required diskette into the drive. Do not remove a diskette if the application starts using a database that resides in it.

sqlcode: -972

sqlstate: 57019

SQL0973N Not enough storage is available in the heap-name heap or stack to process the statement.

Explanation

All available memory for this heap has been used.

The statement cannot be processed.

User response

Terminate the application on receipt of this message (SQLCODE). Modify the heap-name configuration parameter to increase the heap or stack size.

If the heap-name configuration parameter is set to AUTOMATIC, you will need to increase the APPL_MEMORY database configuration setting, the DATABASE_MEMORY database configuration settings, or the INSTANCE_MEMORY database manager configuration setting. Otherwise, modify the heap-name configuration parameter value to increase the heap size.

The package cache and catalog cache have soft limits. For these you will need to increase the DATABASE_MEMORY database configuration setting or the INSTANCE_MEMORY database manager configuration setting.

When modifying the DATABASE_MEMORY database configuration parameter, setting the parameter to AUTOMATIC will tell the database manager to manage database memory automatically.

For the application shared heap size, you will need to increase either the APPL_MEMORY database configuration setting, or the INSTANCE_MEMORY database manager configuration setting.

When updating configuration parameters, it is recommended to change them by 10% of the current size at a time until the error condition is resolved.

For example, if heap-name is UTIL_HEAP_SZ and the database name is TORDB1, to update this database configuration parameter to 10000, issue the following command:

  db2 update db cfg
  for TORDB1
  using UTIL_HEAP_SZ 10000

To view a list of the database configuration parameters, use the GET DATABASE CONFIGURATION command.

To update a database manager configuration parameter, say MON_HEAP_SZ, to a new size of 100, issue the following command:

  db2 update dbm cfg
  using MON_HEAP_SZ 100

To view a list of the database manager configuration parameters, use the GET DATABASE MANAGER CONFIGURATION command.

If all associated configuation parameters are set to either AUTOMATIC or COMPUTED, then the memory demands of the instance exceed the amount of memory configured on the machine. Possible solutions include reducing the database workload, enabling the connection concentrator feature, or adding additional memory to the machine.

sqlcode: -973

sqlstate: 57011

SQL0974N The drive containing the database is locked.

Explanation

The system reported that the drive containing the database is locked.

The statement cannot be processed.

User response

Ensure no other processing (for example, CHKDSK) that can lock the drive is occurring on the system. Retry the operation.

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

sqlcode: -974

sqlstate: 57020

SQL0975N A new transaction could not be started because database or instance name is quiesced by user username. Quiesce type: type.

Explanation

Another user has quiesced the instance or database that you are attempting to use, and no new transactions are allowed until the instance or database is no longer in the quiesced state.

Quiesce type type refers to the instance or database already quiesced and is a '1' for an instance and a '2' for a database.

User response

Contact the user who currently has the instance or database quiesced to determine when the instance or database will no longer be quiesced, and retry the request at that time.

sqlcode: -975

sqlstate: 57046

SQL0976N The specified I/O device is not ready.

Explanation

The I/O device or media is not ready to accept I/O commands.

The statement cannot be processed.

User Respose:

Ensure the I/O device or media is ready and retry the operation.

User response

sqlcode: -976

sqlstate: 57021

SQL0977N COMMIT state unknown.

Explanation

The tm_database has become unavailable during COMMIT processing, and this has caused the result of the COMMIT to be unknown. Resynchronization of the databases will occur when the tm_database becomes available. Note that the transaction may be rolled back during resynchronization. Further execution of SQL statements can safely occur, however locks might be held until the resynchronization process is complete.

User response

Verify that the connection to the tm_database can be made, for example by using CLP. If it cannot be made, follow the necessary actions for the SQLCODE returned to ensure that a connection can be established.

sqlcode: -977

sqlstate: 40003

SQL0978N The storage medium is write-protected.

Explanation

A write operation was attempted to the database but the database storage medium is write protected.

The statement cannot be processed.

User response

Verify that you are using the correct storage media. Remove write protection from the storage medium, if necessary.

sqlcode: -978

sqlstate: 55009

SQL0979N COMMIT has failed against num databases for an application process running with SYNCPOINT of NONE. The failures include the following database alias and SQLSTATE pairs (a maximum of four can be returned): alias/SQLSTATE1, alias/SQLSTATE2, alias/SQLSTATE3, alias/SQLSTATE4.

Explanation

An application was connected to multiple databases, and a COMMIT was issued which failed for one or more of these connections.

Federated system users: if one of the failed connections is a federated server database where nicknames are used, then a commit against one of the data sources required for a nickname has failed.

User response

Depending upon the nature of the application and the data being updated, the user might wish to discontinue the processing being done, log the failure, and issue the appropriate SQL to ensure that the changes intended by the application are consistently reflected across all databases involved.

If a full list of databases affected by COMMIT errors could not be returned please refer to the diagnostic log for a full list.

sqlcode: -979

sqlstate: 40003

SQL0980C A disk error occurred. Subsequent SQL statements cannot be processed.

Explanation

A disk error occurred that prevented successful execution of the current and subsequent SQL statements. The application program is not permitted to issue additional SQL statements. For example, a recovery routine associated with the application program cannot issue additional SQL statements. The database is marked as needing recovery and all applications using the database are prevented from accessing the database.

The statement cannot be processed.

User response

Record all error information from the SQLCA, if possible. Terminate all applications using the database. Determine if the error is a hardware error. Restart the database. If recovery is not possible, restore the database from a backup copy.

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

sqlcode: -980

sqlstate: 58005

SQL0982N A disk error occurred. However, subsequent SQL statements can be processed.

Explanation

A disk error occurred during processing of a temporary system file that prevented successful execution of the current SQL statement. However, subsequent SQL statements can be processed.

The statement cannot be processed.

User response

Terminate the application on receipt of this message (SQLCODE).

sqlcode: -982

sqlstate: 58004

SQL0983N The transaction log does not belong to the current database.

Explanation

The signature stored in the log file does not match the database dependent signature. This error usually occurs when the user specified that the log file be stored in a directory different from where the database is stored. File redirection can be involved.

The statement cannot be processed.

User response

Resubmit the command with the proper access to the log file.

sqlcode: -983

sqlstate: 57036

SQL0984C COMMIT or ROLLBACK was not successful. Subsequent SQL statements cannot be processed.

Explanation

A commit or rollback operation could not be processed successfully because of a system error. The application program is not permitted to issue additional SQL statements. For example, a recovery routine associated with the application program may not issue additional SQL statements. The database is marked as needing recovery and all applications using the database are prevented from accessing the database.

The statement cannot be processed.

User response

Record the message number (SQLCODE) and all SQLCA error information, if possible. Terminate all applications using the database. Restart the database. If installing the sample database, drop it and install the sample database again.

If recovery is not possible, restore the database from a backup copy.

If trace was active, invoke the Independent Trace Facility at the operating system command prompt. Contact your technical service representative with the following information:

Information required:

  • Problem description
  • SQLCODE
  • SQLCA contents if possible
  • Trace file if possible.

Federated system users: isolate the problem to the data source failing the request and take the necessary diagnostic steps and database recovery procedures for that data source. The troubleshooting procedures and database recovery procedures for data sources vary, so refer to the applicable data source manuals.

sqlcode: -984

sqlstate: 58005

SQL0985C A file error occurred while processing the database catalogs. The database is not usable.

Explanation

The system is unable to recover from an I/O error on a catalog file.

The system cannot process any statements using the database.

User response

Restore the database from a backup copy.

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

sqlcode: -985

sqlstate: 58005

SQL0986N A file error occurred while processing a user table. The table is not usable.

Explanation

The data in the table is no longer valid.

The system cannot process any statements using the table.

User response

Restore the database from a backup version if the database is inconsistent.

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

sqlcode: -986

sqlstate: 58004

SQL0987C The application control shared memory set cannot be allocated.

Explanation

The application control shared memory set cannot be allocated. This error is caused by insufficient memory resources for either the database manager or the environment in which its operations are being attempted. Memory resources that can cause this error include:
  • The number of shared memory identifiers allocated in the system.
  • The amount of paging or swapping space available in the system.
  • The amount of physical memory available in the system.

User response

One or more of the following:
  • Ensure that sufficient memory resources are available to satisfy the database manager requirements and those of the other programs running on the system.
  • Reduce the database manager memory requirement for this memory set by reducing the database configuration parameter, app_ctl_heap_sz, which affects it.
  • Reduce one or more of the database configuration parameters dbheap, util_heap_sz, and buffpage. Refer to the Administration Guide for the parameters affecting the amount of database global memory allocated.
  • Reduce the database manager configuration parameter, sheapthres, if intra_parallel is set to yes, or set intra_parallel to no.
  • Where appropriate, stop other programs using the system.

sqlcode: -987

sqlstate: 57011

SQL0989N AFTER trigger trigger-name attempted to modify a row in table table-name that was modified by an SQL data change statement within a FROM clause.

Explanation

An SQL data change statement was specified in the FROM clause, but the underlying target base table of the SQL data change statement has an AFTER trigger defined which modifies the same table. This is disallowed.

The statement cannot be processed.

User response

Avoid using the SQL data change statement within a FROM clause, or change the trigger so that it does not modify the table that is the target of the SQL data change statement.

sqlcode: -989

sqlstate: 560C3

SQL0990C An index error occurred. Reorganize the table.

Explanation

An index has had considerable activity that used all the free space for indexes.

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

The statement cannot be processed.

User response

Commit your work and retry the command. If the error continues, roll back your work. If errors still persist, reorganize the table, if possible.

Federated system users: isolate the problem to the data source failing the request and follow the index re-creation procedures for that data source.

SQL0992C The release number of the precompiled program is not valid.

Explanation

The release number of the precompiled program (package) is not compatible with the release number of the installed version of the database manager.

The precompiled program (package) cannot be used with the current version of the database manager. The command cannot be processed.

User response

Use only programs that are precompiled with a compatible release level of the database manager.

sqlcode: -992

sqlstate: 51008

SQL0993W The new path to the log (newlogpath) in the database configuration file is not valid.

Explanation

The path to the log file is not valid for one of the following reasons:
  • The path does not exist.
  • A file with the correct name was found in the specified path, but it is not a log file for this database.
  • The database manager instance id does not have permission to access the path or a log file.

The requested change is not made.

User response

To change the path to the log file, submit a database configuration command with a valid value.

sqlcode: +993

sqlstate: 01562

SQL0994N Invalid usage of the application savepoint.

Explanation

The application savepoint function has been used inconsistently. The program attempted to perform one of the following:
  • Request more than one active savepoint.
  • Issue an end savepoint call without an active savepoint.
  • Issue a rollback savepoint call without an active savepoint.

The function cannot be processed.

User response

Correct the savepoint usage in the program.

SQL0995W The current path to the log file (logpath) is not valid. The log file path is reset to the default.

Explanation

The path to the log file is not valid for one of the following reasons:
  • The path does not exist.
  • A file with the correct name was found in the specified path, but it is not a log file for this database.
  • The database manager instance id does not have permission to access the path or log file.

For circular logging, the log file is created in the default log path. For archive logging, the next log file is created in the default log path. The requested change is not made.

User response

To change the path to the log file, submit a configuration command with a valid value.

sqlcode: +995

sqlstate: 01563

SQL0996N Cannot free pages for an object in a table space.

Explanation

There are corrupted internal database pages or internal logic errors in a table space.

User response

Discontinue use of the object or table space. Contact IBM service to inspect the object and the table space.

sqlcode: -996

sqlstate: 58035

SQL0997W General informational message for transaction processing. Reason Code = XA-reason-code.

Explanation

The SQLCODE 997 is only passed between components of the database manager and will not be returned to an application. It is used to carry XA return codes for non-error situations. The possible reason codes are:
  • XA_RDONLY (3) - the transaction branch was read-only and has been committed.
  • 64 - TM database indicates transaction to be committed on DUOW resynchronization
  • 65 - TM database indicates transaction to be rolled back on DUOW resynchronization

User response

No action required.

SQL0998N Error occurred during transaction or heuristic processing. Reason Code = reason-code. Subcode = subcode.

Explanation

An error is detected when processing a distributed transaction. The transaction is:

  • Running under a Distributed Transaction Processing environment (such as those from CICS or other transaction managers).
  • Performing heuristic operations.
  • Updating multiple nicknames within a federated database, where each updated nickname represents a different data source. In this case, one of the data sources has failed during transaction processing. The reason code returned in this case is the reason for the failure at the data source, not the federated database.

The possible reason codes (corresponding X/Open XA reason codes are shown in parenthesis) are:

  • 01 - (XAER_ASYNC) asynchronous operation already outstanding.
  • 02 - (XAER_RMERR) a resource manager error occurred in the transaction branch.
  • 03 - (XAER_NOTA) the XID is not valid.
  • 04 - (XAER_INVAL) invalid arguments were given. Possible subcodes are:
    • 01 - xa_info pointer is invalid, eg. the XAOpen string is null.
    • 02 - database name exceeds maximum length.
    • 03 - username exceeds maximum length.
    • 04 - password exceeds maximum length.
    • 05 - username specified but not a password.
    • 06 - password specified but not a username.
    • 07 - too many parameters in the xa_info string.
    • 08 - multiple xa_opens generate different RM ids for the same database name.
    • 09 - database name not specified.
    • 10 - invalid exe_type.
    • 11 - the Sybase LRM name is not present in the xa_config file or the xa_config file does not exist.
  • 05 - (XAER_PROTO) routine invoked in improper context.
  • 06 - (XAER_RMFAIL) resource manager unavailable.
  • 07 - (XAER_DUPID) the XID already exits.
  • 08 - (XAER_OUTSIDE) RM doing work outside global transaction.
  • 09 - registration (ax_reg) with the transaction manager failed. Possible subcodes are:
    • 01 - Joining XID not found
    • 02 - dynamic library specified in the tp_mon_name configuration parameter could not be loaded.
  • 10 - attempted to start a different transaction while suspended.
  • 12 - unregistering (ax_unreg) with the transaction manager failed.
  • 13 - ax interface failure: ax_reg() and ax_unreg() not found.
  • 14 - The enlistment with the Microsoft Distributed Transaction Coordinator has failed. MSDTC service may be down. You must terminate the current transaction.
  • 15 - The transaction does not exist.
  • 16 - A failure occurred with the Microsoft Distributed Transaction Coordinator (MSDTC). Possible subcodes are:
    • 01 - The database manager is unable to communicate with the MSDTC.
    • 02 - The MSDTC is unable to register the connection. The subcode represents the Microsoft error code.
    • 03 - MSDTC is unable to enlist the connection in the Distributed Transaction. The subcode represents the Microsoft error code.
    • 04 - The connection cannot be enlisted as there is an active transaction.
    • 05 - No transaction exists. The connection has not been enlisted.
  • 35 - Heuristic operations are invalid for non-XA database.
  • 36 - The XID is not known by the database manager.
  • 37 - The transaction has already been heuristically committed.
  • 38 - The transaction has already been heuristically rolled back.
  • 39 - The transaction is not an indoubt transaction.
  • 40 - Only rollbacks are allowed for this transaction.
  • 41 - Transaction is not heuristically committed on some MPP subordinator nodes because of node failure.
  • 42 - DB2 Connect XA support is only available to outbound connections that were established using TCPIP.
  • 43 - A connection could not be established because the server does not support native XA.
  • 69 - Database log ID mismatch during DUOW resynchronization.
  • 85 - As a result of heuristic processing, the transaction has partially committed and rolled back.
  • 210 - Only heuristic commits are allowed for this transaction. Some nodes are already in commit state.
  • 221 - The version of DBMS on the host requires that all applications that participate in the same XA transaction use the same user ID to connect to the database.
  • 222 - The version of DBMS on the host requires that all applications that participate the same XA transaction have the same CCSID.
  • 223 - DB2 Connect XA support is only available to local clients or remote clients that use TCPIP to establish the inbound connection.
  • 224 - DB2 Connect XA support is only available to clients that are at least version 7.1.
  • 225 - Operation Invalid in an XA Loosely Coupled Transaction. Possible subcodes are:
    • 01 - DDL statements are not allowed.
    • 02 - updates and deletes specifying WHERE CURRENT OF clause are not allowed.
    • 03 - buffered insert operations are not allowed.
    • 04 - only rollbacks are allowed for this transaction.
    • 05 - implicit rebind operations are not allowed.
  • 226 - The transaction has already been rolled back.
  • 227 - Heuristic request should be run on the coordinator node.
  • 228 - There are open cursors.
  • 229 - The previous transaction is not yet complete.
  • 230 - The transaction has already been committed.

User response

For reason codes 1 through 8, an entry will be made in the system log because sometimes the SQLCA cannot be returned to the caller.

If the error is due to a failed data source associated with a nickname, the location of the failed data source will always appear in federated server's system log.

For reason code 4, verify the content of the xa open string and make necessary corrections.

For reason code 4, subcode 11, ensure xa_config file exists in $SYBASE/$SYBASE_OCS directory and the LRM name that is specified as the NODE server option exists in the file.

For reason code 9, subcode 02, ensure that the tp_mon_name configuration parameter contains the name of the dynamic library in the external product which has the ax_reg() function used for dynamic registration of transactions.

For reason code 14, ensure that the MSDTC service is active.

For reason code 15, this error may occur when the MSDTC transaction timeout value is too low. Increase the timeout value and see if the error continues.

For reason code 16:

  • subcode 01, ensure that the MSDTC service is active.
  • subcode 02, MSDTC was unable to register the connection. For more information refer to the db2diag log file or the Windows Event Viewer for an entry titled MicrosoftXARMCreate.
  • subcode 03, MSDTC was unable to enlist the connection into a Transaction. For more information refer to the db2diag log file or the Windows Event Viewer for an entry titled MicrosoftEnlishWithRM. The most common error is that the current transaction has been explicitly or implicitly rolled back. This may occur when the MSDTC transaction timeout is too low. Increase the timeout value and see if the error continues.
  • subcode 04, there has been an attempt to enlist a connection that is currently active in a different distributed transaction.
  • subcode 05, enlist the connection before attempting to issue an SQL statement.

For reason code 35, you attempted to perform a heuristic operation against a database that only participates as a read-only resource manager in a global transaction. Examples are any DRDA databases such as DB2 on MVS. These type of non-XA databases would not have any XA indoubt transactions.

For reason codes 36, 37, and 38, you attempted to perform an invalid heuristic operation on an indoubt transaction. Either you specified the wrong XID, or a heuristic or resync operation had taken place since you recorded this XID. Perform a Heuristic Query request to get the current list of indoubt transactions to verify if you still need to perform your heuristic operation.

For reason code 39, the XID specified is for a transaction that has ended and is waiting for the two-phase commit process to begin. You can only perform heuristic operations on transactions that have entered the two-phase commit process and become indoubt transactions.

For reason code 40, an SQL statement was attempted under a transaction which has failed. An example of this is to attempt an SQL statement in a transaction thread that has successfully registered after a tightly coupled thread participating in the same transaction has abnormally terminated.

For reason code 41, you can find more information about the problem in the administration notification log. You need to restart the database manager on the failed nodes. It may be necessary to contact the system administrator for assistance.

For reason code 42, an outbound connection can be established by modifying the gateway catalogs to use TCPIP communication protocol.

For reason code 43, if connecting to DB2 for z/OS server or DB2 for i server, catalog the database using DB2 Connect and then start the sync point manager.

For reason code 69, either the transaction manager (TM) database or the resource manager (RM) database or both are different from the ones when the indoubt transaction was generated. In other words, the TM database or RM database names could be referencing different database instances. The log ID mismatch may be caused by the following reasons:

  • The database directory for the TM database at the RM instance is incorrect.
  • The configuration may have been changed after the indoubt transaction was generated.
  • The database may have been dropped and recreated. In that case, you can only commit or rollback the indoubt transaction heuristically.

For reason code 85, you are updating multiple data sources, and some of the data sources have been heuristically rolled back or committed, resulting in the transaction having partially committed and rolled back. With this reason code data is now in an inconsistent state, you must manually check every data source updated by the transaction to correct the data.

For reason code 210, some of the nodes are already in commit state. You have to perform heuristic commit to resolve the indoubt transaction.

For reason code 221, ensure all applications that participate in the same XA transaction use the same user ID to connect to the database.

For reason code 222, ensure all applications that participate in the same XA transaction have the same CCSID.

For reason code 223, modify the application and client set-up to use local clients, or for remote clients use TCPIP as the communication protocol to connect to the gateway.

For reason code 224, update the client to version 7.1 or later.

For reason code 225, ensure the application did not contain any of the invalid operations in an XA Loosely Coupled Transaction. Common application server environments where XA Loosely Coupled Transaction can be used are IBM Encina Transaction Server, IBM WebSphere Application Server, Microsoft Transaction Server, and BEA Tuxedo. If you encounter this reason code, then you must review your application and ensure none of the indicated actions are performed.

For reason code 226, the transaction has reached a rollback state, and is now rolled back on this node.

For reason code 227, heuristic request should be run on the coordinator node, see the db2diag log file for the coordinator node number.

For reason code 228, ensure that the cursors are closed prior to issuing the request.

For reason code 229, ensure that the previous transaction is complete prior to issuing the request.

For reason code 230, the transaction has already been committed.

General information collection procedure:

If you cannot resolve the problem identified by the reason codes, record the message number (SQLCODE), reason code, and the optional subcode in the message or the SQLCA in the system log.

If the failure is from a federated database, you should also record the location of the failed data source found in the federated server's system log.

If trace was active, invoke the Independent Trace Facility at the operating system command prompt. Then, contact your technical service representative with the following information:

  • Problem description
  • SQLCODE, embedded reason code, and subcode
  • SQLCA contents (if possible)
  • Trace file (if possible)
  • Location of the failed data source if the failure is from a federated server.

There might be additional information in the console or message logs of the transaction manager and the database manager.

sqlcode: -998

sqlstate: 58005