Designing Clients to Use the Secondary Database Server

To achieve a degree of load balancing when you use data replication, have some client applications use the secondary database server in a data-replication pair. Design all client applications that use the secondary database server with the following points in mind:

• Any SQL statements that attempt to modify data fail.
• Locking and isolation levels are not the same as on the standard database server.
• Temporary dbspaces must be used for sorting and temporary tables.

The following sections discuss these considerations in more detail.

No Data Modification Statements

SQL statements that update dbspaces in logical-recovery mode on the secondary database server are not allowed. For example, the ALTER FRAGMENT and CREATE DATABASE statements would produce errors. For a complete list of data modification statements, see the IBM Informix Guide to SQL: Syntax.

To prevent clients that are using the secondary database server from issuing updating statements, you can take either of the following actions:

• Write client applications that do not issue updating statements.
• Make all updating statements conditional.

To make statements that perform an update conditional, make sure that client applications test slqwarn6 of the sqlwarn field in the ESQL/C sqlca structure (and equivalent values for other SQL APIs). The database server sets slqwarn6 to W when it runs as a secondary database server.

Locking and Isolation Level

Because all clients that use the secondary database server only read data, locking to ensure isolation between those clients is not required. However, a client that uses the secondary database server is not protected from the activity of users on the primary database server because the logrecvr threads that perform logical recovery do not use locking.

For example, if a client connected to the secondary database server reads a row, nothing prevents a user on the primary database server from updating that row, even if the client connected to the secondary database server has issued a SET ISOLATION TO REPEATABLE READ statement. The update is reflected on the secondary database server as the logical-log records for the committed transaction are processed. Thus, all queries on the secondary database server are essentially dirty with respect to changes that occur on the primary database server, even though a client that uses the secondary database server might explicitly set the isolation level to something other than Dirty Read.

Use of Temporary Dbspaces for Sorting and Temporary Tables

Even though the secondary database server is in read-only mode, it does write when it needs to perform a sort or create a temporary table. Temporary Dbspaces explains where the database server finds temporary space to use during a sort or for a temporary table. To prevent the secondary database server from writing to a dbspace that is in logical-recovery mode, you must take the following actions:

• Ensure that one or more temporary dbspaces exist. For instructions on creating a temporary dbspace, see Creating a Dbspace that Uses the Default Page Size.
• Then, choose one of the following actions:
  • Set the DBSPACETEMP parameter in the ONCONFIG file of the secondary database server to the temporary dbspace or dbspaces.
  • Set the DBSPACETEMP environment variable of the client applications to the temporary dbspace or dbspaces.