Database updates on secondary servers

You can enable applications connected to secondary servers to update database data. If you enable write operations on a secondary server, DELETE, INSERT, MERGE, and UPDATE operations are propagated to the primary server.

Use the UPDATABLE_SECONDARY configuration parameter to control whether the secondary server can update data and to configure the number of connections that update operations use.

Both data definition language (DDL) statements and data manipulation language (DML) statements are supported on secondary servers.

The dbimport utility is supported on all updatable secondary servers.

You cannot use the dbexport utility on HDR secondary servers or shared disk (SD) secondary servers. The dbexport utility is supported on a remote standalone (RS) secondary server only if the server is set to stop applying log files. Use the STOP_APPLY configuration parameter to stop application of log files.

The dbschema utility is supported on all updatable secondary servers.

The dbschema utility is also supported on read-only secondary servers. However, the dbschema utility displays a warning message when running on these servers.

Most applications that use DDL or DML can run on any of the secondary servers in a high-availability cluster; however, the following DDL statements are not supported:
  • CREATE DATABASE (with no logging)
  • CREATE EXTERNAL TABLE
  • CREATE RAW TABLE
  • CREATE TEMP TABLE (with logging)
  • CREATE XADATASOURCE
  • CREATE XADATASOURCE TYPE
  • DROP XADATASOURCE
  • DROP XADATASOURCE TYPE
  • UPDATE STATISTICS

In cluster environments, the SET CONSTRAINTS, SET INDEXES, and SET TRIGGERS statements are not supported on updatable secondary servers. Any session-level index, trigger, or constraint modes that the SET Database Object Mode statement specifies are not redirected for UPDATE operations on table objects in databases of secondary servers.

Client applications can insert, update, and delete rows on a secondary server only if the secondary server image matches that of the primary server. The following data types are supported:
  • BIGINT
  • BIGSERIAL
  • BLOB
  • BOOLEAN
  • BSON
  • BYTE (stored in the table)
  • CHAR
  • CLOB
  • DATE
  • DECIMAL
  • DATETIME
  • FLOAT
  • INT
  • INT8
  • INTERVAL
  • JSON
  • MONEY
  • NCHAR
  • NVCHAR
  • SERIAL
  • SERIAL8
  • SMALLFLOAT
  • SMALLINT
  • TEXT (stored in the table)
  • VARCHAR

BYTE and TEXT data types that are stored in blobspaces are not supported because blobspace data is not replicated.

The following data types are also supported if they do not receive a pointer reference to a different partition:
  • COLLECTION
  • LIST
  • LVARCHAR
  • MULTISET
  • ROW
  • SET
  • UDTVAR

Any difference between the primary server image and the secondary server image causes an SQL error and the rollback of any changes.

You cannot use the following utilities on HDR secondary servers, remote standalone (RS) secondary servers, or shared disk (SD) secondary servers:
  • archecker
  • dbload
  • ondblog
  • onload
  • ON-Monitor
  • onparams
  • onspaces
  • onunload

SD secondary servers are not supported in Windows™ environments.

Byte range locking is not supported on secondary servers configured for updates. Byte range locks on secondary servers are promoted to full object locks.

Replicate smart large objects

You might receive one or more of the following error messages while working with updatable secondary servers:
  • 12014
  • 12015
  • 12233
These errors generally indicate a problem with a smart large object file descriptor. These errors can be caused by any of the following conditions:
  • A smart large object identifier is passed to another transaction or process before committing the transaction. Because all objects including smart large objects are uncommitted until the transaction is committed, do not allow other transactions to use the smart large object. In particular, dirty reads can access locked smart large objects.
  • Smart large objects are not closed after opening them. At the end of a transaction, all smart large objects must be closed on secondary servers, especially those that are created and then the transaction is rolled back. Leaving smart large object file descriptors open causes memory to remain allocated until the session terminates.
  • Another process has deleted the smart large object on the primary server. Share locks are not automatically propagated from secondary servers to the primary server so a different secondary server might access a smart large object that has actually been deleted on the primary. These accesses work until either the log record containing the delete is replayed on the secondary server or the secondary server is updated by the primary server.
Three additional error codes might be returned when processing dirty read information.
  • -126 (ISAM error: bad row id)
  • -244 (SQL error: Could not do a physical-order read to fetch next row)
  • -937
Try your query again if you receive any of the previous codes.

LOCK TABLE statement behavior on secondary servers

You can set an exclusive lock on a table from an updatable secondary server in a high-availability cluster. For exclusive mode locks requested from a secondary server, sessions can read the table but not update it. This behavior is similar to shared access mode on a secondary server; that is, when one session has an exclusive lock on a given table, no other session can obtain a shared or exclusive lock on that table.

On read-only secondary servers, the session issuing the LOCK TABLE statement does not lock the table and the database server does not return an error to the client.

Shared mode locks in a cluster behave the same as for a standalone server. After a LOCK TABLE statement runs successfully, users can read the table but cannot modify it until the lock is released.