Database constructs and workloads that require special consideration for Q Replication

Constructs and application workload checklist

The following database constructs and workloads require special considerations for replication. If none of these apply to your environment, then you can probably create Q subscriptions for all tables at once, using common replication settings.

Tables with no unique index

Tables with no primary key or unique index can be replicated, but they cannot be loaded automatically by Q Replication unless the source application is temporarily suspended. On Linux, UNIX, and Windows, the following query can be used to identify tables that do not have a unique index:

with unitab(tabname) as(
select distinct tabname from syscat.indexes
where
tabschema  IN ('YOURSCHEMA')  and not tabname like 'CD%'
and uniquerule in ('P','U')
)
select
SUBSTR(TABSCHEMA,1,12) AS TABSCHEMA,
char(t.tabname,30) tabname,
case
when u.tabname is not null then 'No problem'
when u.tabname is null then 'No unique Index!!'
end

from syscat.tables t left join unitab u on t.tabname = u.tabname
where
t.tabschema IN ('YOURSCHEMA')  AND t.type ='T' and not t.tabname like 'CD%'
ORDER BY 3,1,2 desc;

Recommendation: Use one of the following methods if you need to load target tables with no primary key or unique index:

• Use the no load option for the Q subscriptions to those tables and load them at both sites before starting these Q subscriptions.
• Let replication perform the load, but suspend any change to the source table until the load has completed at the target and the Q subscription is in active state. See Loading target tables for more detail.

Tip: Create a unique index at the target, if possible, for better replication performance. For more detail, see Index or key columns for targets.

Generated always columns

A Db2 system does not allow the Q Apply program at the target to update columns that are defined with a GENERATED ALWAYS clause by using the value that was generated at the source database.

Requirement: These columns must be altered at the target to be AS IDENTITY GENERATED BY DEFAULT. For more detail, see Replication of tables with identity columns.

Tables with identity columns

For identity columns the range of values that are generated must not overlap between the source and the target databases. For example:

CREATE TABLE CUSTOMER (ID SMALLINT
   GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 1),
   NAME VARCHAR(30));

Follow these recommendations:

• Use an odd-even scheme if you are replicating between two sites, or different ranges at each site. For example, if you use ranges, alter the table at the target:

  ALTER TABLE CUSTOMER ALTER COLUMN ID RESTART WITH 16000

  In this example, values 1 to 15999 will be used at site 1, and values 16000 and greater at site 2.
• When you use replication in an active/standby configuration (where read-write operations against the same data are allowed only on one site until failover to the standby), write a script to alter identity columns at the standby site to start from MAX(identity-column-name)+1 before failover of the application to the standby site. For example:

  SELECT MAX(ID)+1 FROM CUSTOMER
  ALTER TABLE CUSTOMER ALTER COLUMN ID RESTART WITH :val

Tip: If you have a unique constraint on the identity column, Q Replication can detect overlaps. If these occur, Q Apply follows the conflict action that is defined for the Q subscription.

For more detail, see Replication of tables with identity columns.

Database sequences

Sequences are database objects that you can use to generate values for insert or update into tables. For example, insert into t2(col1) values(next value for myseq). Follow these recommendations:

• Partition sequence generation between sites, using either an odd/even scheme or different value ranges, so that values do not overlap. A sequence that can generate overlapping values cannot be used in an active/active configuration without a high risk of conflicts.
• Active/standby configurations: In situations where the application will failover in case of a disaster or for maintenance, alter the sequence to max+1 before rerouting the application, for example: ALTER SEQUENCE ORG_SEQ RESTART SELECT NEXT VALUE FOR ORG_SEQ, ORG.* FROM ORG.

Large objects (LOBs)

LOB data can be replicated with no size limit:

• Replicating LOB data reduces the maximum replication throughput because large LOBs are fetched from the source database by the Q Capture program when it finds a change to a LOB column in the log record.
  Recommendations: Follow these recommendations:

  • Define LOBs as inline in Db2 whenever possible (Db2 for z/OS 10, Db2 for Linux, UNIX, and Windows Version 9.7 and later). Q Capture then reads the LOB data directly from the Db2 log and the performance is virtually equivalent to replicating the VARCHAR data type.
  • Use the default Q Capture parameter value lob_send_option=I for better performance. Only when the actual LOB data (for the row) exceeds the maximum IBM MQ message size that your installation can tolerate and the max_message_size value for the send queue do you need to use lob_send_option=S. MQ supports messages up to 100MB. See max_message_size parameter for more detail.
• Linux, UNIX, and Windows targets: LOB data can require a considerable amount of additional disk space at the target if the Q subscription is defined with automatic load and the Q Apply program selects the LOAD from CURSOR option that uses a nickname. In this case, Db2 at the target creates a temporary table space and materializes the entire contents of the LOB data. If LOB data is larger, use LOAD_TYPE 3 (EXPORT and LOAD utilities) for the Q subscription, or use a manual load in which you load the table outside of replication, rather than LOAD from CURSOR.

Tables with triggers that update other tables

Tables with triggers that update other tables can be replicated; however you must choose one of the following actions if both the table that includes the trigger and the table that receives the triggered value need to be replicated:

Unidirectional configurations

If you can remove the trigger at the target, do so. Nothing else needs to be done. Create Q subscriptions for both tables and let replication propagate the logged changes to both tables. This method can be attractive, because it removes the overhead of firing the trigger at the target and the cost of maintaining it.

All configurations

• Keep the triggers at both sites, create Q subscriptions for both tables, but tell replication not to replicate changes that were made by a trigger by using the Q Capture igntrig parameter. When you specify this parameter, Q Capture does not replicate any change to a table that were the result of a trigger on another table. For more details, see Descriptions of asnqcap parameters.
• Keep the triggers at both sites, but create a Q subscription only for the table that includes the trigger. This method is possible only if you do not need to replicate any other changes to the table that receives the triggered value.

Tip: For fallback after a prolonged outage, drop triggers at the database that is being brought back up for faster resynchronization. Restore them before you restart the applications on that database.

Referential-integrity constraints

Very complex RI dependencies affect the database performance and the maximum level of parallelism that the Q Apply program can achieve. Replication supports RI constraints on replicated objects, but the constraints affect Db2 and replication performance. For improved performance, avoid overly complex constraints if and when possible.

Tips: Follow these tips to improve replication performance with referential integrity constraints:

• For fallback after a prolonged outage, drop RI constraints at the site that is being brought back online for faster resynchronization. Restore the RI constraints before rerouting applications to that site.
• Use the igncasdel parameter to instruct the Q Capture program not to replicate delete operations that result from the delete of parent rows (cascading deletes). This method gives better performance because fewer changes are propagated.

Multi-row update statements on a column with a unique constraint

Some multi-row update statements that update one or more columns with a unique constraint might require database changes or application changes in order to be replicated using log-capture technology. For example, if you have a table, T1 (val integer not null unique) that contains the rows (1) and (2), the statement UPDATE T1 SET val=val+1 is a valid SQL statement that will change (1) --> (2) and (2) --> (3). While this SQL statement is being run, the table temporarily contains two rows with the value (2). Db2 checks for uniqueness only after all rows are updated. However, because Q replication replays each update as a separate statement at the target, the updates will fail.

Recommendations: Follow these guidelines:

• If possible, avoid multi-row updates on columns that have unique constraints.
• If the unique constraint is on a secondary index that is not the replication key, consider removing this unique constraint at the target. If the index is required for application performance, consider changing it to a non-unique index.
• If these multi-row updates are on the primary key, and this primary key is the replication key, a workaround is to add another unique index to the table and use this new index as the replication key. You must then make the primary key non-unique.

Tip: To generate a unique key when none can be generated from existing columns in the table, you can use a sequence. The following example illustrates the concept. These commands are for the Db2 for Linux, UNIX, and Windows command-line interface. Assume the following table is created:

create table T1(tabkey integer not null primary key);

An application updates the table by using tabkey=tabkey+1, which causes temporary duplicate values during statement execution. A solution for replicating this kind of multi-row update statement is to use another column of the table with unique values, adding one if necessary. The following example uses a sequence. First a few rows are added to demonstrate:

insert into T1 values (0),(1);

Then a new unique index and column are added:

alter table T1 add column repkey integer not null with default 0;
alter table T1 alter column repkey drop default;
alter table T1 alter column repkey set generated always as identity;

Then the primary key is dropped and recreated as a non-unique index:

alter table T1 drop primary key;
create index tabind on T1(tabkey);
update T1 set repkey = default;
reorg table T1;

Then you can look at the generated values for the new index:

select * from T1;

This statement inserts a unique incremental value into each row in the table. At the target, you must also replace the primary key with a non-unique index. When you define the subscription for this table, choose the new unique key (repkey in our example) as the replication key, instead of the primary key.

Very large batch jobs

There is no limit to the size of batch jobs that Q Replication can handle. Large batch jobs increase overall replication latency, especially if the workload mixes online transaction processing and online batch jobs. For example, an application might delete several millions rows, committing every 1000 rows, every night as part of a batch purge job.

Recommendations: Follow these recommendations to improve performance with large batch jobs:

• On z/OS, use the maxagents_correlid parameter of the replication queue map to limit the number of Q Apply agent threads that can concurrently apply transactions that were started from the same z/OS job on the source server. Using this parameter limits parallelism for batch jobs at the target that could otherwise cause lock contention because of page-level locking. Reducing the parallelism of Q Apply for these batch jobs minimizes the impact of a single batch job on the replication latency of all other concurrent transactions, as well as on any workload running at the target. See maxagents_correlid parameter for more detail.
• InfoSphere Warehouse or Db2 data partitioning feature: When replicating between partitioned databases, use buffered_inserts=y to prompt Q Apply to use the INSERT BUF bind option. This option enables the coordinator node in a partitioned database to accumulate inserted rows in buffers rather than forwarding them immediately to their destination partitions. See Improving performance for partitioned databases (Linux, UNIX, Windows) for more detail.
• Consider running a particularly large batch job at each site, and request that Q Capture exclude the transactions from replication by specifying either the plannname, authid, or authtoken in the IBMQREP_IGNTRAN table. See Specifying that a Q Capture program ignore transactions for more detail.

Enormously large transactions

Q Replication has no limit on the size of transactions that can be replicated. Even a single transaction that modifies several gigabytes of data can be replicated. However, replicating very large transactions requires modifying some default replication settings, and will affect overall replication latency, especially if this transaction is so large that it does not fit in Q Capture memory and must be spilled to disk. Q Capture sends transaction to Q Apply in source commit order and must finish sending this huge transaction before it can send subsequent changes from the log. See these topics for more detail:

• MQ message size
• MQ logging
• Planning memory requirements for Q Replication and Event Publishing

Tips:

• Use the Q Capture warntxsz parameter to detect the existence of very large transactions.
• To prevent a huge transaction from affecting the replication latency of other transactions, consider excluding this transaction from replication by using the IBMQREP_IGNTRAN table, perhaps executing the transaction at both sites. For example, a huge purge job might be scheduled at both sites and excluded from replication.

"Hot row" that is updated by all transactions

In some applications, every transaction updates the same row, creating a "hot spot." For example, an application might be getting its next application transaction ID by updating a single row in a table that contains the next available transaction ID. This application design creates contention at the Db2 level and is not a good design for replication because every database transaction depends on all previous ones, forcing replication to apply transactions serially (in source commit order) at the target.

Recommendations:

• Exclude this type of table from replication if it is not needed at the target. Excluding the "hot" table is often possible because the value that is generated is itself replicated. Then, make sure that the ranges do not overlap, either by using an odd/even scheme or different value ranges. You might also consider replacing such tables with database constructs such as sequences.
• If you need to replicate the table, consider replicating it on a separate queue. In this configuration, the "hot" table is replicated independently of the rest of the transaction, but point-in-time consistency is achieved when all changes have been applied.

Non-logged operations

Database operations that are not logged, such as loading data with the LOAD utility and tables that specify NOT LOGGED INITIALLY, require special consideration. Q Replication can detect the following types of LOAD utilities and trigger an automatic reinitialization of the Q subscription:

z/OS

• LOAD REPLACE
• LOAD RESUME SHRLEVEL NONE
• REORG DISCARD of source table
• RECOVER PIT
• CHECK DATA DELETE YES LOG NO
• EXCHANGE DATA ON CLONE TABLESPACE

On z/OS, if you have several tables in the table space that is being loaded, the Q subscriptions for all the tables are all reinitialized as specified by the CAPTURE_LOAD option for the Q subscription.

Linux, UNIX, Windows

• LOAD REPLACE
• LOAD INSERT

Recommendation: Perform any other non-logged operations at each site that is involved in replication.

Tip: Q Capture cannot detect third-party load utilities. If you want load operations to be handled automatically by Q Replication, use IBM utilities. Otherwise, stop the Q subscription and restart it after you have reloaded the table.

Incremental load and partition load

Loading individual partitions (z/OS), or LOAD INSERT (Linux, UNIX, Windows) cannot be replicated automatically. Only a full refresh can be performed automatically by replication. That is, even if a single partition is loaded, the entire table is reloaded when CAPTURE_LOAD=R and the Q subscription is defined to use an automatic load.

Recommendation: Define the Q subscription with a manual load and perform the load at both sites. See Manually loading a target table for details.

Tables with both a primary key and one or more secondary unique indexes in an active/active environment

Tables with both a primary key and one or more secondary unique indexes can be replicated without any problem for the majority of cases, but there can be issues when both the primary key and one of the secondary key are updated simultaneously at two sites. For example:

SITE A: (1,2)          SITE B: (1,2)
SITE A: insert (2,3)   SITE B: insert (5,3)
----replication sends the rows in each direction ----
SITE A: insert (5,3) ---> ERROR: SQL803 (duplicate row)  Q Apply
  follows ERROR_ACTION
SITE B: insert (2,3) ---> ERROR: SQL803 (duplicate row)  Q Apply
  follows ERROR_ACTION

Recommendation: Applications that use tables with both a primary key and one or more secondary unique indexes are ill-suited for an active/active environment. If possible, consider running these applications at only one site at a time. If such application exists in your environment and must run in active/active environment, you can use the OKSQLSTATES subscription-level option to prompt the Q Apply program to ignore certain SQL states. In this case, use SQL STATE 23505 for the OKSQLSTATES value. This state corresponds to the SQL code -803. Q Apply will ignore all -803 errors for this table. OKSQLSTATES is for errors, not conflicts. By definition, a conflict is a duplicate row error (sqlcode -803) or row not found error (sqlcode +100) on the replication key. If an -803 is encountered on the replication key, it is a conflict and the conflict action for the Q subscription is followed. If an -803 is encountered for a secondary constraint, then it is an error.

Replicating LONGVARCHAR Db2 data to a federated target other than Oracle

Automatic load is not supported for these targets because Db2 federation maps LONGVARCHAR columns to LOB data type and importing LOB data through a nickname is not supported by Db2. This is not a problem with Oracle targets because Q Replication can load the data with the Oracle SQL*LOADER, which supports LOB data. In other cases, define the Q subscription with a manual load.

DB2-to-Oracle replication: Replicating a Db2 TIME column to an Oracle DATE column

You can replicate a Db2 TIME column to an Oracle DATE column, but if the TIME column is part of the replication key, you need to define an expression on the Q subscription for this table. The reason is that when it updates the target, Oracle augments the time value to make it a complete date by using the current date at the time of the insert or update. For example, if the time 11:01.01 is inserted into Db2 at the source, it might be inserted as 2010-12-01-11:01.01 at the target because Oracle appends the current year, month, and day information to the time that came from the Db2 source. This behavior is a problem for updates, because the Q Apply WHERE clause for the old value will be completed to the current time by Oracle, and this value will not match the actual, older value that was inserted in the Oracle database.

Action: In this situation, define an expression for the Q subscription. For example, the following ASNCLP command creates a Q Replication subscription for table T1(C1, C2) with a primary key on C1 and C2 from a Db2 source to an Oracle target that correctly maps a Db2 TIME column to an Oracle DATE column:

CREATE QSUB USING REPLQMAP MYQMAP1 (SUBNAME "SUB1" username.T1 OPTIONS
  HAS LOAD PHASE I SUPPRESS DELETES N
TARGET NAME SCOTT.T1
FEDERATED AZUMA.T1 IN DB ORCL TBLSP01
TRGCOLS exCLUDE (C1,c2)  EXPRESSION 
  (":C1" target col1,"to_date('1990-01-01-'||char(:c2),'YYYY-MM-DD-HH24:MI:SS')" 
   target c2) KEYS(COL1,C2)

Restrictions

• The IBM HourGlass application cannot be used with peer-to-peer replication. HourGlass is a z/OS system application that enables sites to alter the date and time that are returned to a z/OS application when a time request is made. HourGlass is incompatible with the conflict detection and resolution method that is used in peer-to-peer replication because it is based on system timestamps.
  Recommendation: Use a bidirectional replication configuration instead, where conflict detection is based on data values rather than timestamps.
• Multi-row SQL update statements for which Db2 has to defer a uniqueness check to the end of the statement cannot be replicated without database or application changes. Multi-row update statements that update one or more columns with a unique constraint cannot be replicated, if and when the execution of this statement causes temporary duplicate values. An example of this type of statement would be an update of "k=k+1" on a table that already contain rows with the values 1 and 2 for k. This is because multiple-row statements are replayed as independent statements at the target as they were captured from the log at the source. If any of the individual statement fails, the error action that was defined for the Q subscription is followed. A database workaround is to add another unique index to this table and use this index as the replication key, or if possible, remove the unique constraint at the target.
• Tables with triggers that update other tables cannot be used in a peer-to-peer configuration.
• Federated targets other than Oracle: Db2 LONGVARCHAR columns and Db2 VARCHAR columns that are defined with a size larger than 4000 bytes cannot be replicated to federated targets other than Oracle with Q Replication. For Oracle, if automatic load is used, you must specify LOAD_TYPE 4 (SQL*Loader).
• Replication is not supported on databases that use lattice-based access control (LBAC).