BATCHSQL

Valid For

Replicat

Description

Use the BATCHSQL parameter to increase the performance of Replicat. BATCHSQL causes Replicat to organize similar SQL statements into arrays and apply them at an accelerated rate. In its normal mode, Replicat applies one SQL statement at a time.

BATCHSQL is valid for:

  • Db2 for i (except V5R4 or i6.1)

  • Db2 LUW

  • Db2 on z/OS

  • Oracle

  • PostgreSQL

  • SQL Server

  • Sybase

  • Teradata

  • Times Ten

When to Use BATCHSQL

Before using BATCHSQL, use parallel Replicat and see if that alleviates any Replicat lag. If it does not, then try enabling BATCHSQL. If enabling BATCHSQL improves performance, then keep it enabled otherwise, disable it, and consider other alternative tuning exercises for the Replicat like ensuring all tables have primary or unique keys, limited large batch operations, and ensuring that parallel Replicat has plenty of memory and IO for optimal performance, which implies that the Replicat is not IO bound or memory bound.

When Replicat is in BATCHSQL mode, smaller row changes will show a higher gain in performance than larger row changes. At 100 bytes of data per row change, BATCHSQL has been known to improve the performance of Replicat by up to 300 percent, but actual performance benefits will vary, depending on the mix of operations. At around 5,000 bytes of data per row change, the benefits of using BATCHSQL diminish.

Usage Restrictions

SQL statements that are treated as exceptions include:

  • Statements that contain LOB or LONG data.

  • Statements that contain rows longer than 25k in length.

  • Statements where the target table has one or more unique keys besides the primary key. Such statements cannot be processed in batches because BATCHSQL does not guarantee the correct ordering for non-primary keys if their values could change.

  • (SQL Server) Statements where the target table has a trigger.

  • Statements that cause errors.

When Replicat encounters exceptions in batch mode, it rolls back the batch operation and then tries to apply the exceptions in the following ways, always maintaining transaction integrity:

  • First Replicat tries to use normal mode: one SQL statement at a time within the transaction boundaries that are set with the GROUPTRANSOPS parameter. See "GROUPTRANSOPS" for more information.

  • If normal mode fails, Replicat tries to use source mode: apply the SQL within the same transaction boundaries that were used on the source.

When finished processing exceptions, Replicat resumes BATCHSQL mode.

Table 2-9 Replicat Modes Comparison

Source Transactions (Assumes same table and column list) Replicat Transaction in Normal Mode Replicat Transaction in BATCHSQL Mode Replicat Transactions in Source Mode

Transaction 1:

INSERT

DELETE

Transaction2:

INSERT

DELETE

Transaction 3:

INSERT

DELETE

INSERT

DELETE

INSERT

DELETE

INSERT

DELETE

INSERT (x3)

DELETE (x3)

Transaction 1:

INSERT

DELETE

Transaction 2:

INSERT

DELETE

Transaction 3:

INSERT

DELETE

How BATCHSQL Works

In BATCHSQL mode, Replicat organizes similar SQL statements into batches within a memory queue, and then it applies each batch in one database operation. A batch contains SQL statements that affect the same table, operation type (insert, update, or delete), and column list. For example, each of the following is a batch:

  • Inserts to table A

  • Inserts to table B

  • Updates to table A

  • Updates to table B

  • Deletes from table A

  • Deletes from table B

Note:

Oracle GoldenGate analyzes foreign-key referential dependencies in the batches before executing them. If dependencies exist among statements that are in different batches, more than one SQL statement per batch might be required to maintain the referential integrity.

Controlling the Number of Cached Statements

The MAXSQLSTATEMENTS parameter controls the number of statements that are cached. See "MAXSQLSTATEMENTS" for more information. Old statements are recycled using a least-recently-used algorithm. The batches are executed based on a specified threshold (see "batchsql.html#GUID-2ED88418-6ACB-484D-B140-364232EC419A__I2789998").

Default

Disabled (Process in normal Replicat mode)

Syntax

BATCHSQL
[CHECKUNIQUEKEYS | NOCHECKUNIQUEKEYS]
[THREADS (threadID[, threadID][, ...][, thread_range [,thread_range][, ...])]
[TRACE]
[CHECKUNIQUEKEYS | NOCHECKUNIQUEKEYS]

Valid for Replicat.

CHECKUNIQUEKEYS forces a flush when a table with a unique key is encountered that bypasses the BATCHSQL processing. This ensures that the original order of operations is maintained if the unique key changes.

The default value is NOCHECKUNIQUEKEYS and does not force a flush when a table with unique keys is encountered that allows BATCHSQL processing to continue.

THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])

Valid for BATCHESPERQUEUE, BATCHTRANSOPS, and BYTESPERQUEUE. Applies these options to the specified thread or threads of a coordinated Replicat.

threadID[, threadID][, ...]

Specifies a thread ID or a comma-delimited list of threads in the format of threadID, threadID, threadID.

[, thread_range[, thread_range][, ...]

Specifies a range of threads in the form of threadIDlow-threadIDhigh or a comma-delimited list of ranges in the format of threadIDlow-threadIDhigh, threadIDlow-threadIDhigh.

A combination of these formats is permitted, such as threadID, threadID, threadIDlow-threadIDhigh.