SQL1750C A failure occurred that terminated the operation operation-name on table table-name, of type table-type, which might result in invalid or inconsistent data. log-entry-ID.

Explanation

The result can be invalid or inconsistent data, or a subset of the expected data.

These are the runtime tokens:

operation-name

The type of processing, such as INSERT.

table-name

The name of the table for which the action was attempted.

table-type

Identifies whether the table is a Hadoop table or an HBase table.

log-entry-ID

The log-entry-identifier is composed of three parts:

  • A three-letter prefix that indicates which log file includes diagnostic details.
  • A three-digit number that indicates on which database partition the log file resides.
  • An identifier that is printed in the log file near the related diagnostic details.

For example, the operation might be INSERT into an HBase table. You can load multiple files of HBase data into an HBase table with one INSERT statement. However, the insert operation is not atomic and cannot be rolled back. So, if an error occurs while the data is being loaded, some data is left in the table. If the table contains indexes, it is possible that the indexes might be out of synchronization with the data in the table.

User response

Locate the diagnostic details in the appropriate log file by using the information in the log-entry-ID. You can retrieve the text of the error from the logs with the following statement:

SELECT * FROM Table 
(SYSHADOOP.LOG_ENTRY('log-entry-id'));

Determine which data were not cleanly inserted, if for an INSERT operation, and insert the data again, or drop the table and start the INSERT operation again.

sqlcode: -1750

sqlstate: 58041

SQL1751N The resulting database partition group would not contain any nodes that can be used in a partition map.

Explanation

A database partition group must contain at least one node that can be used in a partition map. If a node is added to a database partition group WITHOUT TABLESPACES, it cannot be included in the partition map because there are no containers for the table spaces defined on the database partition group. If a node is added LIKE another node and the other node does not have containers for all table spaces on the database partition group then neither node can be included in the partition map.

User response

Do not drop all the nodes in the database partition group without adding at least one node. If tablespaces are already defined on the database partition group but there are no tables, ensure that at least one node has containers for all the table spaces.

sqlcode: -1751

sqlstate: 428C0

SQL1752N The table space cannot be created in the database partition group db-partition-group.

Explanation

The database partition group IBMTEMPGROUP can be specified if and only if the table space is a system temporary table space.

User response

For system temporary table spaces, specify database partition group IBMTEMPGROUP. For other table space types, specify a database partition group other than IBMTEMPGROUP.

sqlcode: -1752

sqlstate: 429A1

SQL1753N Database partition database-partition-number does not have containers for all the system temporary table spaces defined in the database partition group IBMTEMPGROUP.

Explanation

A database partition must have containers defined for all the system temporary table spaces defined in the database partition group IBMTEMPGROUP for this database before it can be included in a database partition group.

User response

Issue the ALTER TABLESPACE statement to add containers on every database partition for every system temporary table space in the database.

sqlcode: -1753

sqlstate: 57052

SQL1754N The index table space or long table space is not in the same database partition group as the primary table space.

Explanation

All the table spaces specified in the CREATE TABLE statement must belong to the same database partition group.

User response

Make sure all the table spaces specified in the CREATE TABLE statement belong to the same database partition group.

sqlcode: -1754

sqlstate: 42838

SQL1755N Database partition dbpartnum does not have containers for all the table spaces defined in the database partition group dbpartgrpname.

Explanation

All the table spaces in a database partition group must have containers defined on all the database partitions. Otherwise, that database partition should not be included in a redistribution of a database parititon group or in a LIKE DBPARTITIONNUM clause of the ALTER DATABASE PARTITION GROUP statement.

User response

Issue the ALTER TABLESPACE statement to add containers to all the table spaces on this database partition.

SQL1756N More than one clause specifies containers without the ON DBPARTITIONNUMS clause.

Explanation

For the CREATE TABLESPACE, the USING clause without the ON DBPARTITIONNUMS clause can only be specified once.

For the ALTER TABLESPACE, the ADD clause without the ON DBPARTITIONNUMS clause can only be specified once.

The statement was not processed.

User response

Correct the statement and reissue it.

sqlcode: -1756

sqlstate: 428B1

SQL1757N A USING clause without an ON DATABASE PARTITION clause is missing.

Explanation

In CREATE TABLESPACE statement, every USING clause specifies the ON DATABASE PARTITION clause. However not all the database partitions in the database partition group are included, and therefore not all database partitions in the database partition group have containers.

The statement is not processed.

User response

Ensure that the USING clause without the ON DATABASE PARTITION clause is specified or that each database partition in the database partition group is included once in an ON DATABASE PARTITION clause.

sqlcode: -1757

sqlstate: 428B1

SQL1758W The containers not designated for specific nodes are not used by the table space on any node.

Explanation

The ALTER TABLESPACE and CREATE TABLESPACE statement includes container specification for all the database partition in the database partition group. The specification of the containers that is not followed by an ON DATABASE PARTITION clause is redundant and has been ignored.

The statement has been processed.

User response

If the containers are needed on some of the nodes, issue an ALTER TABLESPACE statement to add the necessary containers.

sqlcode: +1758

sqlstate: 01589

SQL1759W Redistribute database partition group is required to change database partitioning for objects in database partition group database-partition-group-name to include some added database partitions or exclude some dropped database partitions.

Explanation

This warning indicates that a change being made using the ALTER DATABASE PARTITION or ALTER TABLESPACE statements has not caused the partitioning map for the database partition group to be changed. The partitioning map for a database partition group is only changed immediately by these statements if there are no tables defined using any table space defined in the database partition group or the database partition being dropped is not in the partitioning map.

This warning is issued in the following cases:

  • One or more database partitions are added using ALTER DATABASE PARTITION ADD DATABASE PARTITION
  • One or more database partitions are dropped using ALTER DATABASE PARTITION DROP DATABASE PARTITION
  • Containers are added for table space and no further containers are required for a database partition to be used.

For all of these cases, a table is already defined using a table space in the database partition group.

User response

If you want the database partition included or excluded for database partitioning then issue the REDISTRIBUTE DATABASE PARTITION command or API. Another alternative is to drop all tables that use table spaces in the database partition group.

sqlcode: +1759

sqlstate: 01618

SQL1760N The CREATE PROCEDURE or ALTER MODULE statement used to define stored procedure procedure-name must have a valid LANGUAGE clause, EXTERNAL clause, and PARAMETER STYLE clause, or include an SQL procedure body.

Explanation

A required clause is missing in the CREATE PROCEDURE or ALTER MODULE statement used to define procedure procedure-name. LANGUAGE clause, EXTERNAL clause, and PARAMETER STYLE clause must be specified. An external procedure defined in a module must be completely specified since only SQL procedures allow prototypes to be published.

If defining an SQL procedure, the SQL procedeure body must be included unless using the PUBLISH action of ALTER MODULE to define an SQL procedure prototype

User response

Add the missing clause, then try again.

sqlcode: -1760

sqlstate: 42601

SQL1761N The database partition group db-partition-group is not defined for the buffer pool bpname.

Explanation

The database partition group for the table space has not been defined for the buffer pool. The table space cannot be created or altered to use this combination of database partition group and buffer pool.

The statement cannot be processed.

User response

The possible actions are:

  • Specify a buffer pool that has defined the database partition group for the table space.
  • Alter the buffer pool to add the database partition group of the table space.
  • If CREATE TABLESPACE, specify a database partition group that is defined for the buffer pool.

sqlcode: -1761

sqlstate: 42735

SQL1762N Unable to connect to database because there is not enough space to allocate active log files.

Explanation

There is not enough disk space to allocate active log files. Possible reasons include:

  • There is insufficient space available on the device used to store the recovery logs.
  • If userexits are enabled, the userexit program may be failing due to an incorrect path, incorrect install directory, sharing violation, or other problem.
  • If log archiving is enabled, the log archiving method may not be functioning or the target might be inaccessible.

User response

Based on the cause:

  • Ensure that there is sufficient space on the device for the primary logs, as DB2 may require extra space to allocate new logs so that the database will start with at least LOGPRIMARY log files. Do NOT delete recovery logs to free space, even if they appear inactive.
  • Ensure the userexit program is operating correctly by manually invoking it. Review the instructions provided in the sample userexit source code for compiling and installing the userexit program. Ensure that the archive destination path exists.
  • Ensure that the log archiving method is working correctly. If errors are being encountered then further information can be found in the db2diag.log file.

As a last resort, try reducing the values for LOGPRIMARY and/or LOGFILSIZ database configuration parameters so that a smaller set of active log files are used. This will reduce the requirement for disk space.

Reissue the connect statement after determining and correcting the problem.

SQL1763N Invalid ALTER TABLESPACE statement for table space table-space-name due to reason reason-code

Explanation

The clauses specified in the ALTER TABLESPACE statement cannot be used in conjunction with each other.

1

The CONVERT TO LARGE clause must be specified by itself.

2

The BEGIN NEW STRIPE SET container clause cannot be specified with any other container clauses (ADD, DROP, EXTEND, REDUCE or RESIZE).

3

When adding or removing space from the table space:

  1. EXTEND and RESIZE can be used in the same statement, provided that the size of each container is increasing.
  2. REDUCE and RESIZE can be used in the same statement, provided that the size of each container is decreasing.
  3. EXTEND and REDUCE cannot be used in the same statement, unless they are being directed to different database partitions.
  4. ADD cannot be used with REDUCE or DROP in the same statement, unless they are being directed to different database partitions.
  5. DROP cannot be used with EXTEND or ADD in the same statement, unless they are being directed to different database partitions.
4

A container clause cannot be used in conjunction with the REDUCE option when altering automatic storage managed table spaces.

5

A container clause must be specified when altering nonautomatic storage managed table spaces.

6

Temporary table spaces and system-managed table spaces (SMS) cannot be converted to use automatic storage.

7

There are no storage groups in the database.

8

Database-managed table spaces (DMS) that have DEVICE containers cannot be converted to use automatic storage using the ALTER TABLESPACE statement.

9

The REBALANCE clause must be specified by itself.

10

The REDUCE MAX clause or the REDUCE with a specified size clause can only be used with table spaces having the Reclaimable Storage attribute and managed by automatic storage.

11

Table spaces without the Reclaimable Storage attribute cannot use clauses to reduce or lower the high water mark.

12

The REDUCE option with the MAX, numeric value, PERCENT, or STOP clauses, and the LOWER HIGH WATER MARK option including the STOP clause, must be specified and run without any other options, including each other.

13

The ADD, DROP, RESIZE, EXTEND, REDUCE, LOWER HIGH WATER MARK, and BEGIN_STRIPE_SET clauses cannot be used in conjunction with the MANAGED BY AUTOMATIC STORAGE clause or the USING STOGROUP clause.

14

The USING STOGROUP clause cannot be specified when altering an automatic storage hybrid table space.

User response

1

Issue the CONVERT TO LARGE clause in its own ALTER TABLESPACE statement.

2

If using the BEGIN NEW STRIPE SET container operation and other operations in the same statement, move the BEGIN NEW STRIPE SET operation into its own ALTER TABLESPACE statement.

3

If both adding pages to and removing pages from a table space, use two ALTER TABLESPACE statements.

4

Remove the container clause from the ALTER statement and re-issue the statement.

5

Specify a container clause including the list of containers to reduce and re-issue the statement.

6

To convert temporary table spaces using automatic storage, create a new automatic storage-managed temporary table space and drop the old temporary table space. To convert from system-managed to automatic storage-managed table spaces, all data must be moved manually.

7

Create a storage group using the CREATE STOGROUP statement. A table space can only be managed by automatic storage if there is a storage group created.

8

To convert database-managed table spaces that use DEVICE containers to be managed by automatic storage, perform the following steps:

  1. Perform a table space or database level redirected restore operation.
  2. Issue the SET TABLESPACE CONTAINERS command with the USING AUTOMATIC STORAGE option.
9

Issue the REBALANCE clause in its own ALTER TABLESPACE statement.

10

Convert the table space to be managed by automatic storage and ensure the table space has the Reclaimable Storage attribute before using the REDUCE MAX clause or the REDUCE with a specified size clause.

11

Table spaces must have the Reclaimable Storage attribute before you can use clauses to reduce or lower the high water mark.

12

Only use a single clause to reduce or lower the high water mark in each ALTER TABLESPACE statement.

13

Remove the ADD, DROP, RESIZE, EXTEND, REDUCE, LOWER HIGH WATER MARK, or BEGIN_STRIPE_SET clause from the ALTER statement and re-issue the statement.

14

Issue the REBALANCE clause so the table space contains only automatic storage containers. After the rebalance operation completes, issue the ALTER TABLESPACE with the USING STOGROUP clause.

sqlcode: -1763

sqlstate: 429BC

SQL1764N Specified size for a RESIZE action in an ALTER TABLESPACE statement is smaller than the current size of the table space container.

Explanation

The size specified in an ALTER TABLESPACE statement using the RESIZE action is smaller than the current size of the table space container. You can only increase the size of a container.

User response

Specify a size that is larger than the current size value of the table space container.

sqlcode: -1764

sqlstate: 560B0

SQL1765W The update completed successfully. However, index creation, recreation, or reorganization may not be recovered on the secondary database server.

Explanation

When HADR is enabled, it is recommended that the database configuration parameter LOGINDEXBUILD is set to ON, and the database or database manager configuration parameter INDEXREC is set to either RESTART or ACCESS. Otherwise, any index creation, recreation, or reorganization on the current or future primary database server may not be recovered on the current or future secondary database server using HADR. Those indexes which cannot be recovered will be marked as invalid and will be rebuilt implicitly either at the end of the HADR takeover process or after the HADR takeover process when indexes are to be accessed.

User response

To enable full logging, update the database configuration parameter LOGINDEXBUILD or issue the SQL statement ALTER TABLE LOG INDEX BUILD ON. To enable redo of any logged index build operations, update the configuration parameter INDEXREC to either RESTART or ACCESS.

SQL1766W The command completed successfully. However, LOGINDEXBUILD was not enabled before HADR was started.

Explanation

If the database configuration parameter LOGINDEXBUILD is not set to ON before HADR is started, any index creation, recreate, or reorganization on the current or future primary database server may not be recovered on the current or future secondary database server using HADR.

User response

To enable full logging, set the database configuration parameter LOGINDEXBUILD to ON.

SQL1767N Start HADR cannot complete. Reason code = reason-code.

Explanation

Start HADR cannot complete. The explanation corresponding to the reason code is:

1

The database was not in roll forward-pending or roll forward-in-progress state when the START HADR AS STANDBY command was issued.

2

START HADR AS PRIMARY cannot be issued on an HADR standby database.

3

START HADR AS STANDBY cannot be issued on an active database.

9

Database upgrade is in progress on an active HADR standby.

98

A valid HADR license is not installed. The command did not complete successfully.

99

The command was issued in a multi-partition instance environment.

User response

The user response corresponding to the reason code is:

1

Initialize the standby database from a backup image or a split mirror of the primary database, then reissue the START HADR AS STANDBY command.

2

If you intend to change the standby database to a primary database, issue the TAKEOVER command.

3

If you intend to change a primary database to a standby database, issue the TAKEOVER command from the current standby. If you intend to change a standard database to a standby, the database must be deactivated first.

9

Upgrade processing must complete on the primary and standby database and the standby database must be deactivated before the command can be reissued.

98

Obtain and install a valid HADR license and resubmit the command.

99

The HADR feature is not supported in a multi-partition instance environment.

SQL1768N Unable to start HADR. Reason code = reason-code.

Explanation

The explanation corresponding to the reason code is:

1

The database is not recoverable because circular logging is in use.

2

The database has infinite active logging enabled.

3

The database has DATALINKS enabled.

4

Invalid entry for the hadr_local_host configuration parameter.

5

Invalid service name for the hadr_local_svc configuration parameter.

6

Invalid service name for the hadr_remote_svc configuration parameter.

7

The primary database failed to establish a connection to its standby database within the HADR timeout interval. There are multiple scenarios that can cause this error condition. For example, this message can be returned with reason code 7 if the value of the hadr_timeout configuration parameter or the hadr_peer_window configuration parameter on the primary database is not the same as the value on the standby database.

8

The value of one or more of the following HADR database configuration parameters is NULL:

  • hadr_local_host or hadr_local_svc
  • hadr_remote_host, hadr_remote_svc, or hadr_remote_inst in an environment other than DB2 pureScale.
  • hadr_target_list in a DB2 pureScale environment.
9

The database is configured to use raw logs. However, HADR does not support the use of raw I/O (direct disk access) for database log files.

10

The command was interrupted by HADR shutdown due to a STOP HADR command, deactivation of the database, or an internal error.

11

The entries specified by the hadr_remote_host, hadr_local_host, or hadr_target_list configuration parameter do not resolve to the same IP format.

12

Invalid entry for the hadr_remote_host configuration parameter.

13

An HADR standby database cannot start without at least one regular buffer pool. A buffer pool could not be started due to insufficient memory.

14

The entries for the hadr_target_list configuration parameter do not contain the pair specified in the hadr_remote_host and hadr_remote_svc configuration parameters.

15

An entry for the hadr_target_list configuration parameter cannot be resolved into a valid TCP/IP address.

16

The entries specified for the hadr_target_list configuration parameter are not in the correct format.

17

The hadr_replay_delay configuration parameter can only be set to a non-zero value if the hadr_syncmode configuration parameter is SUPERASYNC.

18

The hadr_replay_delay configuration parameter can only be set to 0 on a HADR primary database.

19

Cannot start as primary database because another primary database is detected.

20

The hadr_syncmode database configuration parameter cannot be SYNC or NEARSYNC in a DB2 pureScale environment.

21

The value specified by the hadr_local_svc configuration parameter conflicts with client/server communication ports.

22

The value specified by the hadr_local_svc configuration parameter conflicts with fast communication manager (FCM) communication ports.

23

The value specified by the hadr_local_svc configuration parameter conflicts with cluster caching facility (CF) communication ports.

24

Failed to allocate TCP socket buffer specified by DB2 registry variable DB2_HADR_SOSNDBUF.

25

Failed to allocate TCP socket buffer specified by DB2 registry variable DB2_HADR_SORCVBUF.

26

A table space is not available.

27

The hadr_peer_window database configuration parameter is not set to 0 in a DB2 pureScale environment.

28

SSL communication failed between the primary database and the standby database. Possible reasons for failure are:

  1. The database configuration parameter HADR_SSL_LABEL is set to an invalid certificate name
  2. The SSL_SVR_KEYDB configuration parameter or the SSL_SVR_STASH configuration parameter is not set to a valid value.
29

The hadr_syncmode database configuration is not set to SYNC or NEARSYNC when automated failover is enabled.

98

A valid HADR license is not installed. The command did not complete successfully.

99

An internal error occurred during HADR startup.

User response

The user response corresponding to the reason code is:

1

The database must be a recoverable database. Activate log archiving by setting database configuration parameter logarchmeth1 or logarchmeth2 to a value other than OFF. Take an offline database backup to make the database recoverable and reissue the command.

2

Disable infinite active logging and reissue the command.

3

Update the database manager configuration file so that DATALINKS is set to NO and reissue the command.

4

Ensure that the hadr_local_host configuration parameter can be mapped to an IPv4 or IPv6 address associated with the local host.

5

Ensure that the hadr_local_svc configuration parameter is set to a valid service name. For Unix platforms, edit the /etc/services file. For Windows, edit %SystemRoot%\system32\drivers\etc\services. Alternatively, a literal port number can be specified for this parameter.

6

Ensure that the hadr_remote_svc configuration parameter is a valid service name. For Unix platforms, edit the /etc/services file. For Windows, edit %SystemRoot%\system32\drivers\etc\services. Alternatively, a literal port number can be specified for this parameter.

7

Perform the following troubleshooting steps:

  1. Check if the old standby database is already running as a primary or standalone database. To avoid a potential split-brain scenario, the primary database will not be started if it cannot establish a connection to the standby database. If the old standby database is already running as a primary, consider starting the old primary database as a new standby database.
    1. Issue START HADR DATABASE ON dbname AS STANDBY to convert the old primary database into a new standby database.
    2. Or alternatively, reinitialize the old primary database into a new standby database via a backup image of the new primary database.
  2. If there was no forced takeover (aka failover) operation on the standby database and the standby database is not running as a primary or standalone database, then check the hadr status and configuration.
    1. Ensure that the standby database is online and started as a standby database (use 'db2pd -db dbname -hadr' or MON_GET_HADR).
    2. Confirm that the hadr_target_list, hadr_local_host, hadr_local_svc, hadr_remote_inst, hadr_remote_host, hadr_remote_svc database configuration parameters are set correctly on both the primary and standby database.
    3. Ensure that the network is functioning, and the primary and standby host can reach each other via TCP/IP (consider using the system 'ping' utility to ping the remote host and svc/port).
    4. Examine the db2diag.log files for other error messages that indicate there are incompatible configuration settings on the primary database and the standby database.
    5. Ensure that the value of the hadr_timeout configuration parameter is the same on both the primary database and the standby database.
    6. Ensure that the value of the hadr_peer_window configuration parameter is the same on both the primary database and the standby database.
    7. Ensure the hadr_timeout configuration parameter is set to a value for sufficient time for a connection and handshake to be established between the primary and standby database.
  3. If the standby database is offline and cannot be started (due to a standby system failure or other disaster), then force start the primary database.
    1. Issue START HADR DATABASE ON dbname AS PRIMARY BY FORCE on the primary database.
    2. Or alternatively, issue STOP HADR command on the primary database.
8

Ensure that the following HADR database configuration parameters have a non-NULL value where required:

  • hadr_local_host
  • hadr_local_svc
  • hadr_remote_host
  • hadr_remote_svc
  • hadr_remote_inst
  • hadr_target_list

You can determine the current value of these HADR configuration parameters by using the GET DATABASE CONFIGURATION command.

9

Reconfigure the database to use only file system storage for the log files, instead of using raw I/O (direct disk access) devices.

10

Address the cause of the HADR shutdown, and if desired, reissue the START HADR command.

11

Ensure that the hadr_local_host, hadr_remote_host, and hadr_target_list configuration parameters are in the same IP format (IPv4 or IPv6) or can be resolved to the same format.

12

Ensure that the hadr_remote_host configuration parameter can be mapped to an IPv4 or IPv6 address.

13

Ensure that the dbheap configuration parameter is configured properly and that the system has sufficient memory available for the size of the buffer pool defined. Then retry the operation. If you need to change the size of a buffer pool on the HADR standby, you need to perform a new database restore operation using a backup image from the primary database that includes the new buffer pool size. If the HADR standby has less memory than the HADR primary, you can run the HADR standby database by setting the registry variable DB2_OVERRIDE_BPF to override the size of the buffer pool defined in memory to be smaller than the size on the HADR primary.

14

Ensure that the hadr_target_list configuration parameter has an entry for the pair specified by the hadr_remote_host and hadr_remote_svc configuration parameters.

15

Ensure that all of the entries specified by the hadr_target_list configuration parameter are valid host:service pairs.

16

Ensure that the entries for the hadr_target_list configuration parameter are in the correct format.

17

Either set the hadr_replay_delay configuration parameter to 0 or change the hadr_syncmode configuration parameter to SUPERASYNC.

18

Ensure the hadr_replay_delay configuration parameter is set to 0 on the HADR primary database.

19

Determine which database should be the primary. Drop the other primary database or try converting it to a standby. If both should be primary, they should be removed from each other's hadr_target_list, so that they are independent.

20

Set the hadr_syncmode database configuration parameter to either ASYNC or SUPERASYNC.

21

Update the value specified by either the hadr_local_svc or the svcename configuration parameter so that the TCP port used by hadr_local_svc is not in the port range (inclusively) of svcename and svcename + 1.

22

Update the hadr_local_svc database configuration parameter or the system service names so that the TCP port used by hadr_local_svc is not between (inclusively) the ports defined by service name DB2_instance and DB2_instance_END, where 'instance' is the name of the database manager instance. To update system service names, on Unix platforms, edit the /etc/services file; on Windows, edit %SystemRoot%\system32\drivers\etc\services.

23

Update the hadr_local_svc database configuration parameter or the system service names so that the TCP port used by hadr_local_svc is not between (inclusively) the ports defined by the following pairs of service names:

  • DB2CA_instance_CMD and DB2CA_instance_CMD_END
  • DB2CA_instance_NOTIFY and DB2CA_instance_NOTIFY_END
  • DB2CA_instance_MGMT and DB2CA_instance_MGMT_END

In these service names, 'instance' represents the name of the database manager instance. To update system service names, on Unix platforms, edit the /etc/services file; on Windows, edit %SystemRoot%\system32\drivers\etc\services.

24

Check that DB2 registry variable DB2_HADR_SOSNDBUF is set properly. It should not exceed available memory or system level socket buffer size limit.

25

Check that DB2 registry variable DB2_HADR_SORCVBUF is set properly. It should not exceed available memory or system level socket buffer size limit.

26

Refer to db2diag.log to identify the table space that is not available. Resolve the root cause and ensure the storage for the table space is available. If required, recover the affected table space using the RESTORE DATABASE or ROLLFORWARD DATABASE command. Otherwise, you can drop the table space if it is no longer needed.

27

Set the hadr_peer_window database configuration parameter to 0 in a DB2 pureScale environment.

28

Ensure that the database configuration parameter HADR_SSL_LABEL and the database manager configuration parameters SSL_SVR_KEYDB and SSL_SVR_STASH are set to valid values. After updating the SSL_SVR_KEYDB configuration parameter or the SSL_SVR_STASH configuration parameter, stop and restart the database manager.

29

Either change hadr_syncmode database configuration to SYNC or NEARSYNC, or disable automated failover via db2haicu.

98

Obtain and install a valid HADR license and resubmit the command.

99

If the problem persists, contact IBM Support.

SQL1769N Stop HADR cannot complete. Reason code = reason-code.

Explanation

The explanation corresponding to the reason code is:

1

The command was issued on a standard database.

2

The command was issued on an active HADR standby database.

3

The command was interrupted by HADR shutdown due to a STOP HADR command, deactivation of the database, or an internal error.

98

A valid HADR license is not installed. The command did not complete successfully.

99

The command was issued in a multi-partition instance environment.

User response

The user response corresponding to the reason code is:

1

No action is required as HADR is not running on this database. Verify that the specified database alias is correct.

2

The user should deactivate the database and then reissue the command.

3

If the command was interrupted due to another STOP HADR command, no further action is necessary. Otherwise, ensure that the database instance is started and reissue the STOP HADR command.

98

Obtain and install a valid HADR license and resubmit the command.

99

The HADR feature is not supported in a multi-partition instance environment.

SQL1770N Takeover HADR cannot complete. Reason code = reason-code.

Explanation

The TAKEOVER HADR command failed. The explanation corresponding to the reason code is:

1

An unforced takeover (role switch) can be performed only when the HADR standby database is in one of the following states:

  • peer state
  • remote catchup state with SUPERASYNC mode.
  • in assisted remote catchup (applicable only in a DB2 pureScale environment)
2

The HADR standby database attempted a forced takeover. Forced takeover is not allowed when any log stream is in local catchup state.

3

The standby was created from an online backup, and takeover was attempted before the standby had replayed through to the end point of that backup in the log.

4

The command was issued against a standard or HADR primary database.

5

The command was issued against an inactive standby database.

6

An error occurred while switching the primary database to a standby database.

7

A communication error occurred during takeover.

8

The attempted takeover failed because a conflicting command (START HADR, STOP HADR, ACTIVATE DATABASE, or DEACTIVATE DATABASE) or an internal error was encountered on either the primary or the standby database.

9

The HADR standby database attempted a forced takeover with the PEER WINDOW ONLY command parameter specified. The takeover failed because the standby database was not in a peer window when it attempted the forced takeover. Either a peer window is not configured on the standby database, or the forced takeover was attempted outside any peer window that is configured on the standby database.

10

The TAKEOVER HADR command was run in a DB2 high availability environment. The cluster manager returned an error that prevented the takeover operation from completing. The takeover operation failed. The DB2 high availability disaster recovery (HADR) databases that were involved in the takeover operation remain in the state they were in before the takeover operation was attempted.

11

A takeover (either forced or unforced) is not allowed on an HADR standby that has replay delay enabled.

12

The HADR standby database attempted an unforced takeover (role switch). In a DB2 pureScale environment, unforced takeover is not allowed when any member on the primary is in member crash recovery (MCR) pending or in-progress state.

15

A takeover (either forced or unforced) is not allowed on an HADR standby that is upgrade in progress.

98

A valid HADR license is not installed. The command did not complete successfully.

99

The command was issued in a multi-partition instance environment.

User response

The user response corresponding to the reason code is:

1

Wait for the log streams to reach the allowed condition, and then retry the takeover operation. An alternative is to use the BY FORCE option of the TAKEOVER HADR command to perform a failover. The BY FORCE option will allow takeover in broader conditions, but may result in data loss and will not change the primary into a standby.

2

Wait for the log streams to finish local catchup, and then retry the takeover operation.

3

Allow the standby more time to reach the end of the online backup and reissue the command.

4

This command is not supported on a standard or HADR primary database. Issue the command on an HADR standby database only.

5

Activate the standby database, then issue the takeover command.

6

Refer to the database logs on both HADR database partitions for possible causes of this message. The roles of the database partitions may not have changed. Check the value of the hadr_db_role database configuration parameter on both database partitions.

7

Refer to the database logs on both HADR databases for possible causes of this message. You can determine this by checking the value of the hadr_db_role configuration parameter.

It is possible that there was a problem executing the role switch.

  • If the roles of the databases have not changed, reissue the TAKEOVER HADR command on the standby.
  • If the roles of both databases are STANDBY, issue the TAKEOVER HADR command with the BY FORCE option on the database that you want to become the primary.
8

Wait for the conflicting command to complete, or for the database to shut down in the case of an internal error. Check the HADR role of the primary and standby databases. Start the databases and retry the takeover if you still want to perform that operation.

9

To force the standby database to takeover outside a peer window, reissue the TAKEOVER HADR command without the PEER WINDOW ONLY parameter. Although forcing the standby database to takeover outside a peer window is supported, the risk of transaction loss is greater when the takeover happens outside a peer window.

If your primary database failed and you do not want to force the standby database to take over outside a peer window because of the risk of data or transaction loss, consider alternative responses to the failure of the primary database than a forced takeover.

In a high availability solution with a primary database and a standby database, a common strategy for responding to the failure of the primary database is to have the standby database take over database operations for the failed primary database. However, there are other strategies for responding to a failure of the primary database, including repairing and restarting the failed primary database instead of failing over to a standby database. Although this repair and restart strategy would have a larger negative impact on the availability of the database solution for user applications than the failover strategy, it has other strengths such as less risk of data or transaction loss.

10

Review the cluster manager diagnostic information to determine the error that the cluster manager returned. If possible, repair the cause of the cluster manager error and run the TAKEOVER command again.

11

Disable replay delay by setting the hadr_replay_delay configuration parameter to 0, and reissue the TAKEOVER HADR command.

12

Wait for the members to finish member crash recovery, and then retry the takeover operation.

15
  • If there is no immediate need to connect to this database, wait for the UPGRADE DATABASE command to complete on the primary database, allow the standby database time to replay all upgrade log records sent from the primary database, and then reissue the command.
  • If you need to connect to this database immediately, issue the STOP HADR command to turn the HADR role to STANDARD.
98

Obtain and install a valid HADR license and resubmit the command.

99

HADR is not supported in a multi-partition instance environment.

SQL1771N Non-recoverable databases cannot be used as either HADR primary or HADR standby database.

Explanation

Circular logging cannot be used on a database in HADR primary or standby role. HADR requires recoverable databases.

User response

Either convert the database to standard role or do not use circular logging.

SQL1772N Infinite active logging cannot be enabled on either HADR primary or HADR standby databases.

Explanation

Infinite active logging cannot be enabled on a database in either HADR primary or standby role.

User response

Either convert the database to standard role or do not use infinite logging.

SQL1773N The statement or command failed because it requires functionality that is not supported on a read-enabled HADR standby database. Reason code = reason-code.

Explanation

The statement or command failed. The explanation corresponding to the reason code is:

1

An isolation level other than UR is used and the DB2_STANDBY_ISO registry variable is turned off.

2

The query attempted to access an invalid index.

3

An attempt was made to execute a query against a read-enabled HADR standby database and there is one of the following problems with the query:

  • The query attempted to use a data types which cannot be used in queries against a read-enabled HADR standby database.
  • The query attempted to use a LOB or XML value that is not stored as inline data in the database table.
4

The query attempted to access a created or declared temporary table.

5

A write operation was attempted on an HADR standby database.

6

The operation attempted an implicit rebind on an HADR standby database.

7

An attempt was made to query a column-organized table on an HADR standby database.

8

An attempt was made to execute the SET INTEGRITY statement on an HADR standby database.

User response

The user response corresponding to the reason code is:

1

Only the UR isolation level is supported on an HADR standby database. Change the isolation level to UR or set the DB2_STANDBY_ISO registry variable to UR to enable automatic isolation coercion.

2

Invalid indexes are not automatically rebuilt on the HADR standby database. Set the LOGINDEXBUILD database configuration parameter to ON on the HADR primary database so the indexes on the HADR standby database will be kept in sync with the indexes on the primary database.

3

Change the query so that it does not use any data types that cannot be read on an HADR standby database, and does not query for XML and LOB values that are not inline.

4

Change the query so that it does not access a created or declared temporary table.

5

Change the statement or command so that no write operation is required, or connect to the HADR primary database to perform the write operation.

6

Connect to the HADR primary database to rebind the package.

7

Issue the query against the column-organized table on the primary.

8

Issue the SET INTEGRITY statement on the HADR primary database.

sqlcode: -1773

sqlstate: 51045

SQL1774N Table space restore or rollforward cannot be issued on an HADR primary or HADR standby database.

Explanation

Table space level restore or rollforward on either an HADR primary or HADR standby database is not allowed.

User response

Convert the database to standard role and then issue the restore command or rollforward on the database.

If the goal is to recover lost data on a primary and the data to be restored is available on the standby, consider running the TAKEOVER command to convert the standby database to a primary database rather than restoring the table space on the primary.

SQL1775N The HBase table was not created because a TERMINATED BY character separator-char was invalid.

Explanation

The separator that is used in the TERMINATED BY clause can be specified only as a single byte ASCII character. The specified separator, separator-char, contains more than one character, or consists of a multi-byte unicode character.

User response

Specify a valid value for the option.

sqlcode: -1775

sqlstate: 530AE

SQL1776N The command cannot be issued on an HADR database. Reason code = reason-code.

Explanation

The explanation corresponding to the reason code is:

1

The command is not supported on an HADR standby database.

2

Connection requests are not allowed while an HADR database is switching roles on takeover.

3

The UPGRADE DATABASE command is not supported on an HADR standby database.

4

Connection requests to an HADR standby database are not allowed while the replay-only window is active. The replay-only window is active when DDL or maintenance operations are being replayed on the standby.

5

Connection requests to an HADR standby are not allowed until the standby reaches the highest replay log position of a previous standby activation.

6

This database is an old primary database. It cannot be started because the standby has become the new primary through forced takeover.

7

The command cannot be processed while the standby replay is being restarted on the DB2 pureScale cluster.

9

Connection requests to an HADR standby are not allowed while database upgrade is in progress.

User response

The user response corresponding to the reason code is:

1

Consider enabling reads on standby to allow client connections, or connect to the primary for the operation.

2

Retry the connection to the intended database after the takeover operation completes. Note that since the takeover operation relocates the primary database role, your intended database may now be active on a different server than where it was prior to the takeover.

3

Issue the UPGRADE DATABASE command on the primary database and then re-create your standby after the database upgrade is successful.

4

Connect to the HADR primary database, or wait until the replay-only window is inactive on the standby database.

5

Reissue the command when replay reaches the highest replay log position of a previous activation of the standby database. Check db2diag.log for this position. Use "db2pd -hadr" command to get current replay position.

6

Consider the following scenarios when deciding on a suitable response:

6.1) If the HADR primary database is running on a different server, then the database on this server should be started as an HADR standby database. Perform the following steps:

  • Issue the START HADR AS STANDBY command on the database on this server.
  • Verify that this new HADR standby database has established a connection with the HADR primary database by examining the HADR_CONNECT_STATUS field of the db2pd -hadr command or the MON_GET_HADR() table function on the HADR primary database. Note, an ADM12513E message will also be reported in the administration notification log on the HADR primary database if a connection cannot be established.
  • If this new HADR standby database cannot establish a connection to the HADR primary database, then this new HADR standby database will need to be fully reinitialized based on a backup image of the HADR primary database. For more information, see the page "Initializing high availability disaster recovery (HADR)" in the IBM documentation.

6.2) If the HADR primary database is not running on a different server (or cannot be made to run), then the database on this server should be started as an HADR primary database. Perform the following steps:

  • Issue the STOP HADR command on the database on this server.
  • If you wish to establish an HADR standby database on a different server before starting the database on this server as an HADR primary database, see the page "Initializing high availability disaster recovery (HADR)" in the IBM documentation.
  • Issue the START HADR AS PRIMARY command on the database on this server (or the START HADR AS PRIMARY BY FORCE command if there is no HADR standby database running yet).
7

Reissue the command after the restart of the replay completes.

9

Make sure the UPGRADE DATABASE command is issued on the primary database and wait until the standby database is no longer in upgrade in progress state before reissuing the command.

sqlcode: -1776

sqlstate: 08004

SQL1777N HADR is already started.

Explanation

This error occurs when the START HADR command is issued on a database which is already running in the desired role.

User response

No action required.

SQL1778N The HBase table was not created or altered because the column mapping is not valid. Reason code: reason-code. Column or mapping name: name.

Explanation

The runtime token name represents the SQL column or the HBase column mapping name.

The reason-code is a numeric value that indicates the reason for the error:

1

The SQL column ''<name>'' is not mapped to an HBase column. All SQL columns must be specified in the HBase COLUMN MAPPING clause.

2

The SQL column ''<name>'' is mapped in multiple HBase COLUMN MAPPING clauses. You cannot specify the same SQL column in more than one HBase COLUMN MAPPING clause.

3

The HBase key or column qualifier name was provided multiple times in the COLUMN MAPPING clause

4

The KEY column mapping was not specified in the CREATE TABLE(HBASE) statement. You cannot create an HBase table without specifying a row key.

User response

Correct the error in the HBase column mapping, and reissue the statement.

sqlcode: -1778

sqlstate: 530AF

SQL1779N Java class class-name of type class-type could not be loaded. log-entry-id

Explanation

The Java class class-name that is specified in the class-type or in reference to a class, cannot be located.

The class-type indicates the context of the class, such as SERDE, INPUTFORMAT, OUTPUTFORMAT, or STORAGE HANDLER.

The log identifier in the token log-entry-id is composed of three parts:

  • A three-letter prefix that indicates which log file includes diagnostic details.
  • A three-digit number that indicates on which database partition the log file resides.
  • An identifier that is printed in the log file near the related diagnostic details.

User response

Check the spelling of the specified class name and ensure that the JAR file that contains the class can be accessed by Big SQL.

Locate the diagnostic details in the appropriate log file by using the information in the log-entry-id runtime token. You can retrieve the text of the error from the logs with the following statement:

SELECT * FROM 
Table 
(SYSHADOOP.LOG_ENTRY('log-entry-id'));

sqlcode: -1779

sqlstate: 46503

SQL1780N The table was not created because some specified functionality is not supported with the database with code page and collation. Clause specifying the unsupported functionality: clause. Database code page: code-page. Database collation: collation.

Explanation

A code page is a mapping that determines how characters are represented internally. Collation determines how characters are compared (for the purposes of sorting or merging, for example.) The code page and collation of a database are set when the database is created and cannot be changed.

For some code pages and collations, some functionality is restricted.

This message is returned when an attempt is made to create a table that uses functionality that is not supported with the database code page and collation.

User response

Respond to this error in one of the following ways:

  • Remove the specified functionality and reissue the SQL statement.
  • To use the specified functionality, drop and recreate the database with a code page and collation that is supported with the functionality

sqlcode: -1780

sqlstate: 560DB

SQL1781N An error occurred while parsing the keystore configuration file. Reason code: reason-code.

Explanation

The keystore configuration file is parsed in order to setup the database manager instance with a keystore.

This message is returned when an error occurs while parsing the keystore configuration file for one of the following reasons:

1

A line in the configuration file is invalid.

2

A line in the configuration file is too long.

3

An EOF condition was encountered in the configuration file.

4

The configuration file could not be opened.

5

The configuration file could not be read.

6

A parameter in the configuration file is missing or not set.

7

A parameter in the configuration file has an invalid value. When this message is returned with reason code 7, the name of the parameter is included in the runtime token value.

8

A parameter in the configuration file is conflicting with another parameter. When this message is returned with reason code 8, the names of the parameters are included in the runtime token value.

User response

Correct any errors in the keystore configuration file and rerun the command.

SQL1782N The command or operation failed because an error was encountered accessing the centralized key manager. Reason code reason-code.

Explanation

DB2 native encryption stores encryption keys in a centralized key manager when the KEYSTORE_TYPE database manager configuration parameter is KMIP. To use this functionality, you must perform the following set-up and configuration tasks:

  1. Configure a centralized key manager for storing encryption objects, such as secret keys.
  2. Configure SSL between the DB2 server and the centralized key manager.
  3. Create a centralized key manager configuration file.
  4. Configure the DB2 database manager to use the centralized key manager configuration file by setting encryption configuration parameters, such as KEYSTORE_TYPE and KEYSTORE_LOCATION.

This message is returned when the database manager fails to connect to the centralized key manager or an error is returned from the centralized key manager while performing one of several types of operations:

  • Creating an encrypted database
  • Creating an encrypted backup image of a database
  • Restoring an encrypted backup image
  • Issuing a DB2 command, such as db2cklog, against an encrypted database

The reason code indicates more specifically the reason for the failure:

1

Automatic creation of a new master key and master key label is not allowed.

2

The centralized key manager configuration file specified in the KEYSTORE_LOCATION database manager configuration parameter does not exist.

3

No password was given (the password required to open the SSL keystore is not stashed and no password was provided with the command.)

4

The given password (the password that is stashed or that was provided with the command) is not valid to access the SSL keystore file.

5

An SSL error was encountered. When this message is returned with reason code 5, the ssl-error is included in the runtime token value.

6

An authentication error was encountered from the centralized key manager.

7

An authorization error was encountered from the centralized key manager.

8

A KMIP error was encountered from the centralized key manager.

User response

Resolve the problem according to the reason code, and then reissue the command:

1

The ALLOW_KEY_INSERT_WITHOUT_KEYSTORE_BACKUP parameter in the centralized key manager configuration file is set to false. You can either create a new master key and master key label in the centralized kay manager and specify that master key label on the command, or set the ALLOW_KEY_INSERT_WITHOUT_KEYSTORE_BACKUP to true.

2

Set the KEYSTORE_LOCATION database manager configuration parameter to a valid file name.

3, 4

Provide the DB2 server with a valid password for accessing the SSL keystore file in one of the following ways:

  • Recreate the stash file
  • Provide the keystore password as an argument to the command
5

Review the SSL error from this list of SSL errors: http://www-01.ibm.com/support/knowledgecenter/SSAL2T_8.1.0/com.ibm.cics.tx.doc/reference/r_gskit_error_codes.html. Correct the problem that is causing the SSL error, and then reissue the command. If the SSL errors continue, contact IBM Software Support for assistance.

6

Review the authentication error that is printed in the db2diag diagnostic log file, correct the problem that is causing the authentication error, and then reissue the command.

7

Review the authorization error that is printed in the db2diag diagnostic log file, correct the problem that is causing the authorization error, and then reissue the command.

8

Review the KMIP error that is printed in the db2diag diagnostic log file, correct the problem that is causing the KMIP error, and then reissue the command. If the DEVICE_GROUP configuration parameter is set and the keystore being used is not IBM Security Key Lifecycle Manager (ISKLM), remove the parameter from the configuration file.

SQL1783N The command or operation failed because an error was encountered accessing the PKCS #11 key manager. Reason code reason-code.

Explanation

DB2 native encryption stores encryption keys in a PKCS #11 key manager when the KEYSTORE_TYPE database manager configuration parameter is PKCS11. To use this functionality, you must perform the following set-up and configuration tasks:

  1. Configure a PKCS #11 key manager for storing encryption objects, such as secret keys.
  2. Configure a PKCS #11 vendor library to access the PKCS #11 key manager.
  3. Create a PKCS #11 keystore configuration file.
  4. Configure the DB2 database manager to use the PKCS #11 keystore configuration file by setting encryption configuration parameters, such as KEYSTORE_TYPE and KEYSTORE_LOCATION.

This message is returned when the database manager fails to connect to the PKCS #11 key manager or an error is returned from the PKCS #11 key manager while performing one of several types of operations:

  • Creating an encrypted database
  • Creating an encrypted backup image of a database
  • Restoring an encrypted backup image
  • Issuing a DB2 command, such as db2cklog, against an encrypted database

The reason code indicates more specifically the reason for the failure:

1

The PKCS #11 keystore configuration file specified in the KEYSTORE_LOCATION database manager configuration parameter does not exist.

2

No password was given (the password required to access the PKCS #11 keystore is not stashed and no password was provided with the command.)

3

The given password (the password that is stashed or that was provided with the command) is not valid to access the PKCS #11 keystore.

4

The given password (the password that is stashed or that was provided with the command) is locked.

5

The given password (the password that is stashed or that was provided with the command) is expired.

6

The given password (the password that is stashed or that was provided with the command) is not initialized.

7

An unexpected error was encountered from the PKCS #11 key manager. The error is included in the runtime token value.

8

The PKCS #11 vendor library specified in the PKCS #11 keystore configuration file can not be loaded.

9

The provided master key label already exists in the PKCS #11 keystore.

10

Multiple objects with the same label exist in the PKCS #11 keystore.

11

The PKCS #11 keystore is write-protected.

12

There are no slots available in the PKCS #11 keystore.

13

The PKCS #11 slot specified by slot label or slot ID was not found.

14

The user is not authorized to perform the command or operation in the PKCS #11 keystore.

User response

Resolve the problem according to the reason code, and then reissue the command:

1

Set the KEYSTORE_LOCATION database manager configuration parameter to a valid file name.

2, 3

Provide the database server with a valid password for accessing the PKCS #11 keystore in one of the following ways:

  • Recreate the stash file
  • Provide the PKCS #11 keystore password as an argument to the command
4, 5, 6

Correct the status of the password within the PKCS #11 key manager, and then reissue the command.

7

Review the PKCS #11 key manager error that is printed in the db2diag diagnostic log file, correct the problem that is causing the error, and then reissue the command.

8

Review the error that is printed in the db2diag diagnostic log file, correct the problem that is causing the error, and then reissue the command.

9

If a master key label was not specified, then reissue the command. Otherwise, take appropriate object management actions using available vendor utilities, and then reissue the command.

10

You can either take appropriate object management actions using available vendor utilities, and then reissue the command, or you can reissue the command and specify a different master key label.

11

Certain actions may not be allowed on a write-protected PKCS #11 keystore. Review those actions using available vendor utilities.

12

Check configuration and ability to connect to the PKCS #11 keystore using available vendor utilities.

13

Verify the provided slot label or slot ID is correct, and then reissue the command.

14

Review the authorization error that is printed in the db2diag diagnostic log file, correct the problem that is causing the authorization error, and then reissue the command.

SQL1784N The operation failed because a database object has been modified in a later code level and cannot be accessed from the current code level. Database object name: object-name. Object code level marker: marker1. Current code level marker: marker2.

Explanation

This message is returned in the following scenario:

  1. You are using database software at a certain version and fix pack level, code level B, and while running you modify database object X.
  2. You fall back to an earlier code level A.
  3. While running on code level A, you try to access object X.

The database object that is encountering the code level problem is listed in the runtime token object-name. The runtime tokens marker1 and marker2 are for use by IBM support, if necessary.

User response

  1. Apply the higher fix pack code level again ("code level B" described in the explanation).
  2. If the fall back operation is necessary, drop the object named in the runtime token object-name, fall back to the earlier fix pack code level ("code level A" described in the explanation), and recreate the object.

SQL1785N The statement was not processed because an extended index is incompatible with the current product level. Index ID: index-id. Table ID: table-id. Table space ID: tbspace-id.

Explanation

You can use index extensions to specify how the database manager indexes user-defined data types. You create index extensions by issuing the CREATE INDEX EXTENSION statement. When creating an index on a table that contains user-defined data types, you can specify an index extension to manage the index by including the EXTEND USING clause in the CREATE INDEX statement. An index that uses an index extension is often referred to as an extended index.

This message is returned when a statement accesses a table that contains an extended index that was created before the current database product level was applied.

User response

Update the extended index by issuing the db2updv111 command, and then reissue the statement.

sqlcode: -1785

sqlstate: 55019

SQL1786N DB2START processing failed due to an invalid netmon.cf file.

Explanation

The member or CF failed to start due to an incorrectly configured /var/ct/cfg/netmon.cf file.

User response

Review the diagnostic (db2diag) log files to determine the misconfiguration found within the /var/ct/cfg/netmon.cf file.

Correct the misconfiguration. Stop and restart the peer domain before re-issuing the db2start command.

SQL1790W A default table space could not be found with a page size of at least page size that authorization ID authorization-id is authorized to use.

Explanation

The procedure NNSTAT was not able to create the SYSPROC.FED_STATS table to keep a history of the statement that was run. The table space with sufficient page size (at least pagesize) could not be found.

User response

Ensure that a table space with a page size of at least pagesize exists.

sqlcode: +1790

sqlcode: 01670

SQL1791N The specified server definition, schema, or nickname does not exist.

Explanation

The procedure NNSTAT accepts a server definition, schema, and nickname as input and one or more of these objects could not be found.

User response

Specify an existing server definition, schema, or nickname and resubmit the statement.

sqlcode: -1791

sqlstate: 42704

SQL1792W The statistics for the specified nicknames were not updated completely because of schema inconsistencies between the remote and local catalogs.

Explanation

The remote schema has changed. Either the remote table or view, or one of its columns or the column data types have changed since the nickname was created.

User response

Create a new nickname and resubmit the statement.

sqlcode: +1792

sqlcode: 01669

SQL1800N An invalid pointer to structure sqle_request_info was passed to the catalog admin command/api.

Explanation

The pointer to structure sqle_request_info passed as a parameter to the catalog admin command/api was invalid. For Client Configuration Assistance request this pointer must not be NULL.

User response

Specify a valid pointer to sqle_request_info and re-submit the command.

SQL1801N Invalid request type.

Explanation

The specified request type is not supported for this command.

User response

Ensure that the request type is one of the following supported request types:
  1. SQLE_CCA_REQUEST - CCA catalog node request for catalog and open scan command
  2. SQLE_DAS_REQUEST - DAS catalog node request for catalog and open scan command
  3. SQLE_CND_REQUEST - Open scan command for CCA and DAS catalog entries

SQL1802N No entry belongs to the request type.

Explanation

There is no entry in the node directory that is catalogued by the given request type.

User response

Catalog an entry with the same request type and re-submit the command.

SQL1803N The requested operation cannot be executed on a CLI dynamic SQL package that is in use, or on any package if in "No Package Lock" mode. An affected package is pkgschema.pkgname_0Xcontoken.

Explanation

An attempt to modify a package has been made that is disallowed. This could occur for one of two reasons.

  1. The package is a CLI dynamic SQL package (package schema is NULLID and package name begins with SYSSH, SYSSN, SYSLH, SYSLN) and the operation(rebind, drop or invalidation of the package) is not allowed if there is another application currently in a unit of work that is using the package.
  2. The database manager is currently operating in "No Package Lock" mode. This mode has been activated by setting the DB2_APM_PERFORMANCE registry environment variable to "ON".

In No Package Lock mode, the following classes of operations are prevented from being executed because of their impact on packages:

  • An operation that invalidates packages.
  • An operation that makes a package inoperative.
  • Binding, rebinding (explicitly or implicitly) or dropping a package.

Since the requested operation would affect the package pkgschema.pkgname_0Xcontoken in one of these ways, the operation is disallowed.

User response

  1. If the package is a CLI dynamic SQL package, then only attempt this operation when there are no other active connections that are using the CLI packages.
  2. If "No Package Lock" mode is active, do not attempt disallowed operations in "No Package Lock" mode. In order to execute the requested operation, "No Package Lock" mode must be exited. This is accomplished by unsetting the DB2_APM_PERFORMANCE environment registry variable. In order for the variable change to take effect, that database manager must be stopped and restarted.

sqlcode: -1803

sqlstate: 57056

SQL1804N The statement could not be executed on the current connection because there is already an active statement on that connection, and the data source has not been enabled for multiple active statements on a single connection. Federated data source name: data-source-name.

Explanation

You can configure a federated data source to work with ODBC drivers that only support one active statement on a connection by setting the DB2_ONE_REQUEST_PER_CONNECTION server option to 'Y'. In general, if you are using a ODBC driver that supports more than one active statement on a connection, the DB2_ONE_REQUEST_PER_CONNECTION server option should not be set at all, or should be set to 'N'.

This message is returned when an attempt is made to execute multiple statements on one connection against a federated data source for which the DB2_ONE_REQUEST_PER_CONNECTION server option has been set to 'Y'.

User response

Respond to this error in one of the following ways:

  • Execute only one statement at once on any connection.
  • Enable simultaneous execution of multiple statements on one connection by setting the DB2_ONE_REQUEST_PER_CONNECTION server option to 'N' using the ALTER SERVER statement:
    ALTER SERVER <server-name> OPTIONS(  
         ADD DB2_ONE_REQUEST_PER_CONNECTION 'N')

sqlcode: -1804

sqlstate: 57064

SQL1805N The insert, update, or delete operation was not processed because the operation uses a nickname in a way that is not supported.

Explanation

A nickname is an identifier that an application uses to reference a data source object, such as a table or view. In a federated environment, to refer to objects that are in the federated system but not in the federated database itself you must use nicknames or three-part names.

There are some restrictions on the use of nicknames in insert, update, or delete operations in federated environments. These restrictions include the following examples:

  • An INSERT, UPDATE, or DELETE statement that is nested in a subclause of a larger SQL statement cannot reference a nickname.
  • An SQL procedure that performs insert, update, or delete operations cannot reference a nickname.
  • Insert, update, or delete operations in the body of a trigger cannot reference a nickname.

This message is returned when an attempt is made to use a nickname in an insert, update, or delete operation in one of these ways that are not supported.

User response

Respond to this message in one of the following ways:

  • To update a data source object that is in a federated system but not in the federated database itself, use methods other than compound SQL statements, SQL procedures, or triggers.
  • Remove any insert, update, or delete operations against a nickname or federated three-part name from compound SQL statements, SQL procedures, or triggers.

sqlcode: -1805

sqlstate: 429A9

SQL1806N A two-phase commit transaction failed because the transaction is being distributed across a federated server topology that is not valid for two-phase commit transactions.

Explanation

There are multiple components in the X/Open Distributed Transaction Processing (DTP) model for two-phase commit transaction:

  • Transaction identifiers
  • Transaction managers
  • Resource managers

In federated systems there is an additional component:

  • The federated server becomes a federated transaction manager

The federated transaction manager performs some transaction management functions on behalf of the transaction manager, and communicates with the transaction manager using an XA interface. A federated transaction manager receives two-phase commit requests in from the client or transaction manager and sends federated two-phase commit requests out to federated data sources.

This message is returned when one federated server, B, receives a federated two-phase commit request from another federated server, A, and federated server B must send a federated two-phase commit request to a data source C.

User response

  1. Review the transaction that failed as well as the topology of the federated server and remote data sources involved in the failed transaction.
  2. Modify the topology of the federated server and the remote data sources so that no federated server must both receive federated two-phase commit requests and send federated two-phase commit requests to a data source as part of the same transaction.

sqlcode: -1806

sqlstate: 25503

SQL1807N Execution of an SQL statement against a federated data source failed because some part of the SQL statement is not supported by the specified data source. Federated data source name: data-source-name. Unsupported clause: clause.

Explanation

Different federated data sources support different SQL statement syntax. This message is returned when an attempt is made to use SQL syntax or an SQL clause that is not supported by the specified federated data source, or which is incompatible with the current configuration of the the specified federated data source.

This message can be returned for the following reasons:

  • An attempt was made to execute an SQL statement that includes the DEFAULT keyword for a value against a federated data source that does not support executing that SQL statement with the DEFAULT keyword in its query language syntax.
  • An attempt was made to open an updatable cursor with a federated data source which was configured to disallow insert, update, or delete operations. Specifically, a statement that contains the FOR UPDATE OF clause is executed against a data source for which the DB2_IUD_ENABLE server option is set to 'N'.

User response

Respond to this message according to the reason the message was returned:

  • If the statement includes the DEFAULT keyword, rewrite the statement without the DEFAULT keyword and execute the statement again.
  • If you are attempting to open a cursor, respond in one of the following ways:
    • If the statement for which the cursor is being created does not insert, update, or delete data, recreate the cursor as read-only.
    • Enable insert, update, and delete operations by setting the DB2_IUD_ENABLE to 'Y' using the following command:
      ALTER SERVER <server-name> OPTIONS(  
           ADD DB2_IUD_ENABLE 'Y')

      After the data source has been configured to allow insert, update, and delete operation, recreate the cursor.

SQL1808N The execution of an SQL statement failed because the query optimizer produced an access plan that is invalid for a federated environment. Reason code: reason-code.

Explanation

An access plan specifies an order of operations for accessing data necessary to resolve an explainable statement. The query optimizer produces an access plan whenever an explainable SQL or XQuery statement is compiled.

This message is returned when the optimizer has produced an invalid access plan for a federated environment. The reason code indicates what aspect of the access plan is invalid:

1

A part of the predicate cannot be found.

2

The statement includes a positioned update operation or a positioned delete operation. A positioned update or delete operation is an update or delete operation that modifies the data of a result set. Positioned update and delete operations require cursors to be used.

3

An element cannot be found in the columns of a nickname.

This information about what is invalid about the access plan is useful for IBM support personnel only.

User response

Respond to this error by performing the following steps:

  1. Rewrite the statement to cause the optimizer to create a different access plan.
  2. Execute the statement again.
  3. If the statement continues to fail after it has been rewritten, collect the diagnostic information and contact IBM support for assistance.

sqlcode: -1808

sqlstate: 58004

SQL1809N An insert, update, or delete operation in a trusted wrapper failed because part of the query in the body of the wrapper must be evaluated locally. Wrapper name: wrapper-name.

Explanation

Performing insert, update, or delete operations referencing a nickname or federated three-part name is not supported on local database partitions.

This message is returned when an attempt is made to open an updatable cursor on the result set of an SQL statement that uses a trusted wrapper to query a table on a local database partition.

User response

Respond to this error in one of the following ways:

  • If no insert, update, or delete operations are required, reopen the cursor as read-only.
  • Alter the wrapper so that it is no longer trusted using the ALTER WRAPPER statement with the DB2_FENCED wrapper option set to 'Y', and then reopen the cursor.

SQL1810N The insert or update statement was not executed or the cursor could not be opened because the statement contains some elements that must be evaluated locally and some elements that must be evaluated remotely.

Explanation

In a federated environment, many factors affect whether parts of an SQL statement are evaluated locally or remotely, including the following examples:

  • Sometimes parts of a SQL statement are executed locally instead of remotely because the query optimizer determines that it is more efficient to do so.
  • Other times, parts of a SQL statement are executed locally because they cannot be evaluated at the remote data source. For example:
    • A statement would have to be evaluated locally if an expression in the statement contains a function or syntax that is not supported by the remote data source.
    • An INSERT statement with a VALUES clause and an UPDATE statement with a SET clause must both be evaluated locally.

This message is returned in the following cases:

  • An attempt is made to execute a positioned update on a result set of a query that references tables at the federated data source, but an expression in the query can only be evaluated locally.
  • An attempt is made to execute a statement that contains some elements that can only be evaluated locally and some elements that can only be evaluated remotely.

User response

Respond to this error in one of the following ways:

  • Alter the statement to use neither the INSERT statement with the VALUES clause nor the UPDATE statement with the SET clause, and then execute the statement again.
  • If this message is returned when an attempt is made to open a cursor on a result set and update, or delete operations are not required, reopen the cursor as read-only.
  • Cause the federated database to send the statement to the federated data source without evaluating the statement locally by alter the statement so that the statement contains no elements that are not supported by the data source.

SQL1812W Length of column column-name was reduced from remote-column-length to column-length.

Explanation

When issuing a CREATE NICKNAME statement, the federated server defines a local data type for each column or field in that remote data source object. To improve the performance of query processing, the remote column length remote-column-length of column column-name is reduced to column-length. Truncation can occur when the remote table contains long value which exceeding column-length bytes.

User response

Examine the remote table and identify the column that use large-scale type such as STRING or BINARY. In the local column specification for the nickname, using ALTER NICKNAME statement change the data type of the column that correspond to the column identified in the previous step. Ensure that the column size is large enough to accommodate the data in the remote data source.

sqlcode: +1812

sqlstate: 0169E

SQL1815N Federation is not supported for XML data when the Database Partitioning Feature is enabled.

Explanation

You cannot create nicknames for remote tables that have XML data type columns when the Database Partitioning Feature is enabled.

User response

To use federation with XML data, create a new database instance without the Database Partitioning Feature enabled.

sqlcode: -1815

sqlstate: 55076

SQL1816N Wrapper wrapper-name cannot be used to access the type-or-version of data source (server-type server-version) that you are trying to define to the federated database.

Explanation

The wrapper that you specified does not support the type or version of data source that you want to define.

User response

Consult the documentation to find out which wrapper supports the type and version of data source that you want to define. Make sure that the wrapper has been registered to the federated database by the CREATE WRAPPER statement. Then recode the CREATE SERVER statement so that it specifies this wrapper, and run the CREATE SERVER statement again.

sqlcode: -1816

sqlstate: 560AC

SQL1817N The CREATE SERVER statement does not identify the type-or-version of data source that you want defined to the federated database.

Explanation

When a CREATE SERVER statement references the wrapper that you specified, it must also identify the type-or-version of data source that is to be defined to the federated database.

User response

In the CREATE SERVER statement, code the type-or-version option so that it designates the type-or-version of data source being defined. Then run the CREATE SERVER statement again.

sqlcode: -1817

sqlstate: 428EU

SQL1818N The ALTER SERVER statement that you submitted could not be processed.

Explanation

The ALTER SERVER statement is preceded in a unit of work by a SELECT statement that references a nickname for a table or view within the data source (or category of data sources) that the ALTER SERVER statement references.

User response

Let the unit of work finish; then resubmit the ALTER SERVER statement.

sqlcode: -1818

sqlstate: 55007

SQL1819N The DROP SERVER statement that you submitted could not be processed.

Explanation

The DROP SERVER statement is preceded in a unit of work by a SELECT statement that references a nickname for a table or view within the data source (or category of data sources) that the DROP SERVER statement references.

User response

Let the unit of work finish; then resubmit the DROP SERVER statement.

sqlcode: -1819

sqlstate: 55006

SQL1820N Action on the LOB value failed. Reason code=reason-code.

Explanation

The possible reasons codes are:
  1. Not enough buffer space to store the LOB value.
  2. The remote data source does not support the current action on LOB data type.
  3. Some internal programming limitation exceeded.

User response

Reduce the size of the LOB or replace the function which is being applied on the LOB data type. As a last resort, remove the LOB data type from the statement.

sqlcode: -1820

sqlstate: 560A0

SQL1821W The LOB value retrieved may have changed.

Explanation

The LOB value was evaluated on a deferred retrieval basis. The LOB value may have changed between the time it was first accessed and when it was actually retrieved.

User response

Set the "deferred_lob_retrieval" to "N" in SYSSERVEROPTIONS, resubmit your query, or ignore the warning.

sqlcode: +1821

sqlstate: 01621

SQL1822N Unexpected error code error-code received from data source data-source-name. Associated text and tokens are tokens.

Explanation

While referencing a data source, the federated server received an unexpected error code from the data source that does not map to a DB2 equivalent.

Possible error codes include:
  • 4901 Trying to open more than 15 cursors
  • 4902 Row size exceeds 32K limit

This error might also be returned if the data source is not available.

User response

Ensure the data source is available, then identify and correct the root cause of the problem by locating the appropriate message text and corrective action for the specified error at the data source.

sqlcode: -1822

sqlstate: 560BD

SQL1823N No data type mapping exists for data type data-type-name from server server-name.

Explanation

An attempt was made to create a nickname for an object. One or more columns of that object are of a type currently unknown to the federated server. The name of (one of) the unknown type(s) is listed in the message.

User response

Create a mapping for the specified type name on the specified server using the CREATE TYPE MAPPING statement.

sqlcode: -1823

sqlstate: 428C5

SQL1824W Some base tables in the operands of this UNION ALL may be the same table.

Explanation

A nickname can refer to a remote base table, a remote view, a remote alias/synonym, or a remote nickname. If two operands of a UNION ALL view refer to different nicknames, they may potentially be pointing to the same table (if not both of them are known to be remote base tables). This message is issued to warn the user that potentially one remote base table can get updated/deleted twice via updates/deletes through two operands.

User response

Verify if all operands point to different remote tables. If two operands point to the same remote base table, consider issuing a rollback to reverse the update/delete operation.

sqlcode: +1824

sqlstate: 01620

SQL1825N Execution of an SQL statement against a federated data source failed because some part of the SQL statement is not supported in a federated environment. Federated data source name: data-source-name. Reason code: reason-code.

Explanation

Some restrictions apply to using INSERT, UPDATE, or DELETE statements in a federated system.

The reason code indicates the particular reason this message was returned:

1

An attempt was made to insert, update, or delete LOB data through a federated database.

2

An attempt was made to execute an insert, update, or delete operation against a view, which was created with the UNION ALL clause.

User response

Respond to this message according to the reason code:

1

Perform the insert, update, or delete LOB data for the specified data source through native interfaces at the data source.

2

Rewrite the statement so that the insert, update, or delete operation is not using a UNION ALL view, and execute the statement again.

sqlcode: -1825

sqlstate: 429A9

SQL1826N An invalid value value was specified for column column-name in a system catalog object object-name.

Explanation

An invalid value value was specified for a column column-name in a system catalog object object-name.

The statement cannot be processed.

User response

Refer to the SQL Reference for valid values for the specified column in the specified system catalog object. Correct the statement and reissue it.

sqlcode: -1826

sqlstate: 23521

SQL1827N No user mapping is defined from local authorization ID auth-ID to server server-name.

Explanation

An attempt was made to drop or alter a user mapping that is not defined.

User response

If it is an ALTER USER MAPPING statement, first create a user mapping using the CREATE USER MAPPING statement. Then, alter the user mapping. If it is a DROP USER MAPPING statement, no extra action is needed because the user mapping does not exist.

sqlcode: -1827

sqlstate: 42704

SQL1828N No server option option-name is defined for remote server server-name or for a group of remote servers: server type server-type, version server-version, and protocol server-protocol.

Explanation

An attempt was made to drop or alter a server option that is not defined.

User response

If it is an ALTER SERVER statement, first create a server option using the CREATE SERVER statement. Then, alter the server option. If it is a DROP SERVER statement, no extra action is needed because the server option for the servers does not exist.

sqlcode: -1828

sqlstate: 42704

SQL1829W The federated server received the warning message warning-code from the data source server-name. The associated text and tokens are tokens.

Explanation

An unknown warning warning-code occurred at the data source server-name. The tokens for the message are tokens.

User response

Use the diagnostic information for the data source to determine what, if any, corrective action to take.

sqlcode: +1829

sqlstate: 0168O

SQL1830N RETURNS clause must be specified prior to a predicate specification using the EXPRESSION AS clause.

Explanation

The RETURNS clause is not specified before the PREDICATE clause that includes the EXPRESSION AS clause. The RETURNS clause may have been included after the predicate specification or may be missing.

The statement cannot be processed.

User response

Specify the CREATE FUNCTION statement with the RESULTS clause prior to the PREDICATE clause.

sqlcode: -1830

sqlstate: 42627

SQL1831N Table statistics for a subtable subtable-name cannot be updated.

Explanation

The statement attempts to update the statistics values of NPAGES, FPAGES, or OVERFLOW, for table subtable-name, that is defined as a subtable. For typed tables, these statistics can only be updated using the root table of the table hierarchy.

The statement cannot be processed.

User response

Update the catalog statistics for the root table of the table hierarchy instead of the subtable.

sqlcode: -1831

sqlstate: 428DY

SQL1832N The routine routine-name cannot be used to define a filter because it is defined as an SQL function.

Explanation

The routine (function or method) routine-name is specified in a FILTER clause for a user defined predicate specification or an index extension definition. The routine can not be defined with LANGUAGE SQL.

The statement cannot be processed.

User response

Specify a routine that is not defined with LANGUAGE SQL.

sqlcode: -1832

sqlstate: 429B4

SQL1833N Connection to remote Extended Search Server host_name on port port_number could not be established or was terminated.

Explanation

The Extended Search Wrapper tried to connect to the remote Extended Search Server host_name on port port_number but the connection could not be established or was terminated by the remote Server.

User response

Verify the Host name and Port number of the remote Extended Search server. Also, check that the Extended Search Server is up and running

SQL1834N User-defined column column_name is identical to a fixed column for wrapper Wrapper_name but uses a different data type.

Explanation

A CREATE NICKNAME statement or ALTER NICKNAME statement contains a user-defined column which has the same name as a fixed column for Wrapper Wrapper_name but uses a different data type.

User response

Do not specify the fixed columns for Wrapper Wrapper_name in the column definition of an CREATE NICKNAME statement. However, if the fixed columns are specified, then the fixed column name and the data type/data type length must match the fixed column definition. It is not allowed to alter a fixed column name or data type.

SQL1835N Extended Search object object_name of type object_type could not be found on the remote Extended Search Server es_host_name.

Explanation

Extended Search object object_name of type object_type could not be found on the remote Extended Search Server es_host_name.

User response

Verify that the object name is defined on this server and is of type object_type.

SQL1836N No column mapping exists between user-defined column column_name and a field name on the remote Extended Search Server es_host_name.

Explanation

None of the data sources specified in option DATASOURCE or in option CATEGORY contains a field name identical to the user-defined column column_name.

User response

Verify that the column name is defined as a field in one of the following:
  • any of the data sources specified in option DATASOURCE
  • in a data source in a category specified in option CATEGORY.

SQL1837N The required option option_name for the option_type object object_name cannot be dropped.

Explanation

Some options are required for federation. The option cannot be dropped.

User response

See the federation documentation for this data source to determine if the option can be set to another value. It might be necessary to drop the associated object.

sqlcode: -1837

sqlstate: 428GA

SQL1838N The search statement statement is not a valid Extended Search query.

Explanation

The Extended Search wrapper attempted to process the listed search statement, however the query failed because the syntax is incorrect.

User response

Correct the search statement and submit the request again. Consult the documentation for information about creating valid SQL search statements when using Extended Search Nicknames.

SQL1839N One or more search parameters are not valid.

Explanation

The Extended Search wrapper tried to use the specified search parameters, but they are not valid for Extended Search.

User response

Consult the IBM DB2 Life Sciences Data Connect Planning, Installation, and Configuration Guide (Part: Extended Search Wrapper) for information about how to write valid SQL statements.

SQL1840N The option-type option option-name cannot be added to the object-name object.

Explanation

The option cannot be added. Some options are set by the object and cannot be added or overridden by you.

User response

See the federation documentation for this data source. Determine the actions that are valid for the options that are associated with this object.

sqlcode: -1840

sqlstate: 428GA

SQL1841N The value of the option-type option option-name cannot be changed for the object-name object.

Explanation

The option value cannot be changed. Some options are set by the object and cannot be added or overridden by you.

User response

See the federation documentation for this data source. Determine the actions that are valid for the options that are associated with this object. It might be necessary to drop the associated object and create it again with a new option value. If this message is produced in response to a SET SERVER OPTION statement, it might be necessary to use an ALTER SERVER statement.

sqlcode: -1841

sqlstate: 428GA

SQL1842N Option option-name of type option-type for object object-name found near text is not valid. Reason code= reason-code.

Explanation

The syntax of the specified option is not valid or the option cannot be set to the specified value. The reason code provides more information about the error. The reason codes are:

01

Unexpected character.

02

Element or attribute name expected but not found.

03

Min/max occurrence expected after reference.

04

More than one colon found in an attribute name.

05

Min/max occurrence is not an integer value.

06

Min/max occurrence out of range.

07

Min occurrence greater than max occurrence.

08

Reference in column template option was not "column".

09

'=' delimiter missing from namespace specification.

10

Opening or closing quotation mark missing from namespace specification.

11

Duplicate reference in template.

12

The wrapper option option-name is not allowed when the database manager instance is 32-bit.

13

The two wrapper options or option values are not compatible. The wrapper option option-name is only valid when "DB2_FENCED" is set to "Y".

14

The wrapper option option-name is not supported on this particular platform.

15

The wrapper cannot be loaded into the DB2 threaded engine on this particular platform as it is not threadsafe.

16

The minimum valid value for the wait-time parameter is 1000 microseconds.

User response

See the federation documentation for this data source. Determine the correct option syntax and recode the statement. The reason codes are:

01

Examine the option value near the specified position and change or remove the invalid character.

02

Examine the option value near the specified position and correct the syntax.

03

Ensure that a range specification "[min,max]" follows each reference in the template option value.

04

Templates support only one level of name qualification. Remove the extra qualifications.

05

Ensure that the min occurrence and max occurrence values of a range specification are integers.

06

Ensure that the values for the range specification "[min,max]" are within the allowed ranges for this data source.

07

Correct the range specification. Ensure that the first number is less than or equal to the second.

08

Replace the reference in the column template option value with the token 'column'.

09,10

Recode the namespace option value in the form 'name="specification"'.

11

Recode the template. Ensure that no references are repeated.

12

Do not specify the option-name wrapper option for a 32-bit database manager instance.

13

Examine the option value for the "DB2_FENCED" wrapper option.

14

Consult the SQL Reference to verify the wrapper option that you want. Install and use the 64-bit client for this data source.

15

Specify the value "Y" for the "DB2_FENCED" wrapper option.

16

Issue the statement again with a valid value for wait-time.

sqlcode: -1842

sqlstate: 42616

SQL1843N The operator-name operator is not supported for the nickname-name.column-name nickname column.

Explanation

Some data sources restrict the operators that can be specified with nickname columns.

User response

See the federation documentation for this data source. Recode the statement and remove or change the invalid expression. Then submit the statement again.

sqlcode: -1843

sqlstate: 429BP

SQL1844W Data for column column-name were truncated between the remote data source and the federated server.

Explanation

Characters were truncated when data was transferred between a remote data source and the federated server. Truncation can occur in many situations. Some of these situations include an incorrect nickname column definition (a column is too small for the remote data source column data), or the presence of a conversion or type-cast function that restricts the size of the data returned by the remote data source.

User response

To correct this problem, examine the statement for any type-cast or conversion functions that might be restricting the size of data returned from the remote data source. If the statement contains these functions, recode the statement to allow larger data to be returned from the data source. Then submit the statement again. If the statement does not contain these functions, or if correcting the functions does not correct the problem, examine the local column specification in the nickname in the DB2 catalog. Using the ALTER NICKNAME statement or DROP NICKNAME and CREATE NICKNAME statements, change the column specification to ensure that the column size is large enough to accommodate the data returned by the remote data source.

sqlcode: +1844

sqlstate: 01004

SQL1846N The option-type-1 option option-name-1 for the object-name-1 object conflicts with option-type-2 option option-name-2 for the object-name-2 object.

Explanation

You have specified two or more incompatible options or option values.

User response

See the federation documentation for this data source. Determine the valid options that can be specified with this object. Then recode and submit the statement again.

sqlcode: -1846

sqlstate: 42867

SQL1847N Template substitution error for the object-name object-type. Reason code = reason-code. Additional text and tokens text.

Explanation

While building an XML document a wrapper encountered a problem substituting values into a template. This may be caused by conflicts between templates at the nickname and column level. The reason codes are:

01

Reference name not found in template. The additional text identifies the missing reference.

02

Cannot substitute an attribute reference with element content. The additional text identifies the reference in error.

User response

See the federation documentation for this data source. Further diagnostic information may be logged to the db2diag log file. Correct the template syntax if necessary, using the ALTER NICKNAME statemet. Then submit the original statement again.

sqlcode: -1847

sqlstate: 428G9

SQL1860N Table space tablespace-name is not compatible with table space tablespace-name. Reason code = reason-code.

Explanation

The table spaces specified are not compatible for one of the following reasons:

1

All table spaces (data, long, index) for a partitioned table must be in the same database partition group.

2

The data table spaces for a partitioned table must be either all SMS, all regular DMS, or all large DMS. The index table spaces for a partitioned index must be all regular DMS or all large DMS.

3

The page size for all data table spaces must be the same. The page size for all index table spaces must be the same. The page size for all long table spaces must be the same. However, the page sizes of data table spaces, index table spaces, and long table spaces can be different from each other.

4

The extent size for each data table space must be the same as the extent sizes of other data table spaces. The extent size for each index table spaces must be the same as the extent sizes of other index table spaces. The extent sizes for all table space used storing long data must also be the same. However, the extent sizes of table spaces used for different purposes do not need to match.

5

Long data for a partitioned table must be stored in the same table space as the data for all data partitions or it must be stored in large table spaces, each of which is distinct from the table space of the corresponding data partition. This error will occur if a LONG IN clause is used that specifies a table space that is different from the data table space but is not a large table space. LONG IN can only be used to specify regular table spaces if they are identical to the data table spaces (i.e. in the case where the LONG IN clause is redundant because it merely specifies the data table spaces, which is the same as the default behavior if LONG IN were omitted entirely).

User response

Specify a table space that matches the other table spaces for the table.

sqlcode: -1860

sqlstate: 42838

SQL1870N A row could not be inserted into a range-clustered table because a key sequence column is out of range.

Explanation

A row could not be inserted into a range-clustered table because the key sequence values fall outside the defined range.

User response

To see the range defined by the range-clustered table query the constraints defined on this table, and find the constraint named "RCT". For example: SELECT * FROM SYSIBM.SYSCHECKS WHERE NAME='RCT' AND TABLE=<rct table-name>
  • you can drop and recreate the range-clustered table with the WITH OVERFLOW option enabled. This will create a range-clustered table which can handle out-of-range records, but does not guarantee ordering and has higher locking implications.
  • you can drop and recreate the range-clustered table with a new range definition which will include this record.
  • you can define a view which is a UNION ALL of the range-clustered table records and create a separate table which contains the out-of-range records. Inserts, updates, deletes, merges, and queries should then be directed at the view.

sqlcode: -1870

sqlstate: 23513

SQL1871N This function is not supported on range-clustered tables. Reason code = reason-code.

Explanation

Certain functions are not available on range-clustered tables. Reason codes:

1

VALUE COMPRESSION and COMPRESS SYSTEM DEFAULT are not available.

2

A clustering index cannot be created on a range clustered tables.

3

Adding a column is not supported.

4

SET DATA TYPE is not supported.

5

Changing PCTFREE is not supported.

6

Changing APPEND mode is not supported.

7

Column attributes cannot be changed.

8

Typed tables are not supported.

10

The following commands are not supported on a database partition group that was used to define a table space containing a range-clustered table: ALTER TABLESPACE, REDISTRIBUTE DATABASE PARTITION GROUP.

11

The LOAD utility is not supported.

12

Table Reorganization is not supported.

13

Table Truncate is not supported.

14

Columns of type XML cannot be used.

15

COMPRESS YES is not supported.

17

This reason code is returned when an attempt is made to create a range-clustered table in a partitioned database environment and there is a problem with the relationship between the distribution key and the sequence key specified in the CREATE TABLE statement. The problem is: the distribution key, specified with the DISTRIBUTE BY HASH clause, is not a single column subset of the columns of the sequence key, specified with the ORGANIZE BY KEY SEQUENCE clause.

The following examples illustrate this relationship between the distribution key and the sequence key:

Example 1: the distribution key is a single column subset of the sequence key.

CREATE TABLE ...
  ORGANIZE BY KEY SEQUENCE (colA, colB) 
    ... DISTRIBUTE BY HASH (colA)

Example 2 (error): the distribution key is not a single column subset of the sequence key.

CREATE TABLE ...
  ORGANIZE BY KEY SEQUENCE (colA, colB) 
    ... DISTRIBUTE BY HASH (colC)
18

This reason code is returned when an attempt is made to create a range-clustered, materialized query table, and the DISALLOW OVERFLOW clause is specified in the CREATE TABLE STATEMENT. It is not possible to prevent records that have key values that fall outside the defined range from being inserted into range-clustered, materialize query tables.

19

Random distribution tables are not supported.

User response

If the unavailable function is required on this table, consider creating the table without the ORGANIZE BY KEY SEQUENCE clause. If the table already exists, you will need to drop and then create the table again. Reason codes:

1

Remove the VALUE COMPRESSION clause or the COMPRESS SYSTEM DEFAULT clause.

2

Remove the CLUSTER keyword from the CREATE INDEX statement.

3

To add a column you must drop and create the table again with the additional column.

4

Remove SET DATA TYPE clause from the ALTER TABLE statement.

5

Remove PCTFREE clause from the ALTER TABLE statement.

6

Remove the APPEND clause from the ALTER TABLE statement.

7

To change column attributes you must drop and create the table again with the new column attributes.

8

Do not use a typed table definition together with the ORGANIZE BY KEY SEQUENCE clause.

10

To alter or redistribute a database partition group that was used to define a tablespace containing a range-clustered table, perform the following steps:

  1. Drop and recreate the range-clustered table in a table space that does not belong to the database partition group that you want to alter or redistribute.
  2. Call the ALTER TABLESPACE command or the REDISTRIBUTE DATABASE PARTITION GROUP command again.
11

Consider using the IMPORT utility instead of the LOAD utility. Multiple IMPORT commands can be issued concurrently using subsets of the data to improve performance. Also consider using parallel insert applications which populate different regions of the key sequence range.

12

Table reorganization is not required for Range Clustered Tables.

13

Drop the table and recreate.

14

Remove the column defined with data type XML or use another supported data type.

15

Remove the COMPRESS YES clause.

17

To create a range-clustered table in a partitioned database environment, make one of the following changes to the CREATE TABLE statement and execute the statement again:

  • Use the DISTRIBUTE BY REPLICATION clause instead of the DISTRIBUTE BY HASH clause.
  • Specify a single column as the distribution key with the DISTRIBUTE BY HASH clause that is one of the columns specified in the sequence key with the ORGANIZE BY KEY SEQUENCE clause.
18

Execute the CREATE TABLE statement again, without specifying the DISALLOW OVERFLOW clause.

19

Remove the DISTRIBUTE BY RANDOM clause.

sqlcode: -1871

sqlstate: 429BG

SQL1880N option-name is not a supported option-type option for cursor object-name by either the wrapper wrapper-name, or the federated data source server-name, or both.

Explanation

The cursor cursor-name accessing a nickname using wrapper wrapper-name at the federated data source server-name was declared using the option option-name of type option-type, but either the wrapper or the data source itself does not support this option.

The cursor cannot be opened.

User response

Do not open the cursor with this option.

sqlcode: -1880

sqlstate: 428EE

SQL1881N The server option was not set because the option is invalid in the context in which it is being used. Option: option-name. Type of option: option-type. Object to which the option is being applied: object-name.

Explanation

You can configure a federated server by specifying options with the CREATE SERVER statement, the ALTER SERVER statement, or the SET SERVER statement. Some federated server options are valid for all data sources, while some federated server options are valid for only a few data sources.

This message is returned when an invalid server option is specified. A server option could be invalid for different reasons, including the following example reasons:

  • The specified option might not exist
  • The specified option might be invalid for the type of data source
  • The specified option might be invalid for the particular data source
  • The specified option might be invalid for the database object

User response

Review the server options that are appropriate for the data source, and then execute the statement again, specifying valid options.

sqlcode: -1881

sqlstate: 428EE

SQL1882N The option-type option option-name cannot be set to option-value for object-name.

Explanation

The value that you specified either lacks the proper delimiters or is invalid.

User response

Consult the SQL Reference to verify the value that you want. Then revise and resubmit the statement that you want to run. Be sure to delimit the value in single quotes.

sqlcode: -1882

sqlstate: 428EF

SQL1883N option-name is a required option-type option for object-name.

Explanation

You did not specify an option that DB2 requires in order to process the statement that you submitted.

User response

Consult the documentation to find out the options that are required for the statement that you want to run. Then revise and resubmit this statement.

sqlcode: -1883

sqlstate: 428EG

SQL1884N The option_name option was specified more than once for the object_type object object_name.

Explanation

You entered a statement that references the same option multiple times.

User response

Recode the statement so that the statement references the option that you want only once. Then submit the statement again.

sqlcode: -1884

sqlstate: 42853

SQL1885N The option_type option option_name is already defined for object_name.

Explanation

You entered a value for an option that already has a value.

User response

Query the appropriate catalog view to determine what value the option is currently set to. If the option value differs from the one that you want, recode the statement so that the SET keyword follows the OPTIONS keyword. Determine which catalog view contains the options for this option.

sqlcode: -1885

sqlstate: 428EH

SQL1886N The operation-type operation is not valid because the option-type option option-name has not been defined for object-name.

Explanation

You tried to change or delete a value for an option that has not been defined for the data source, data source type, or database object that you're working with.

User response

If you specified SET in the statement that you want to run, recode the statement, either omitting SET or replacing it with ADD (ADD is the default); remove any incorrect DROP clause. Then submit the statement again.

sqlcode: -1886

sqlstate: 428EJ

SQL1887N SPECIFICATION ONLY clause required.

Explanation

A CREATE INDEX statement for a nickname must have a SPECIFICATION ONLY clause.

User response

Add the SPECIFICATION ONLY clause and then resubmit the statement.

sqlcode: -1887

sqlstate: 42601

SQL1888N The port number port-number is not valid.

Explanation

The port number as specified on the Update Alternate Server command is not valid. The value is either not numeric or its length is not valid. The value must be 1 to 14 characters in length and cannot be all blanks.

User response

Verify that the port number is specified as a numeric value and that it is no more than 14 characters in length.

Resubmit the command with a valid port number.

SQL1889W The update alternate server request was ignored.

Explanation

The request can only be applied to a database server.

User response

Issue the request in a database server.

SQL1890N The host name host-name is not valid.

Explanation

The host name as specified on the Update Alternate Server command is not valid. The value must be at least 1 to 255 characters in length and cannot be all blanks.

User response

Verify that the host name is no longer than 255 characters in length.

Resubmit the command with a valid host name.

SQL1891N The address of the host name parameter is not valid.

Explanation

The application program used an address that is not valid for the host name parameter. Either the address points to an unallocated buffer or the character string in the buffer does not have a null terminator.

The command cannot be processed.

User response

Correct the application program so a correct address is used and the input string is null terminated.

SQL1892N The address of the port number parameter is not valid.

Explanation

The application program used an address that is not valid for the port number parameter. Either the address points to an unallocated buffer or the character string in the buffer does not have a null terminator.

The command cannot be processed.

User response

Correct the application program so a correct address is used and the input string is null terminated.

SQL1900N The command completed successfully.

Explanation

The command line utility successfully completed the command.

User response

No action is required.

SQL1901N The syntax of the command is not correct.

Explanation

The command line utility could not process the command.

User response

Correct and resubmit the command.