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
Teradata
Times Ten

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 "Managing Memory").

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 1-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

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`

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

When to Use BATCHSQL

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.

Managing Memory

The gathering of SQL statements into batches improves efficiency but also consumes memory. To maintain optimum performance, use the following BATCHSQL options:

BATCHESPERQUEUE 
BYTESPERQUEUE 
OPSPERBATCH 
OPSPERQUEUE

As a benchmark for setting values, assume that a batch of 1,000 SQL statements at 500 bytes each would require less than 10 megabytes of memory.

Default

Disabled (Process in normal Replicat mode)

Syntax

BATCHSQL
[BATCHERRORMODE | NOBATCHERRORMODE]
[BATCHESPERQUEUE n]
[BATCHTRANSOPS n]
[BYTESPERQUEUE n]
[OPSPERBATCH n]
[OPSPERQUEUE n]
[THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])]
[TRACE]

BATCHERRORMODE | NOBATCHERRORMODE

Sets the response of Replicat to errors that occur during BATCHSQL processing mode.

BATCHERRORMODE: Causes Replicat to try to resolve errors without leaving BATCHSQL mode. It converts inserts that fail on duplicate-record errors to updates, and it ignores missing-record errors for deletes. When using BATCHERRORMODE, use the HANDLECOLLISIONS parameter to prevent Replicat from abending.
NOBATCHERRORMODE: The default, causes Replicat to disable BATCHSQL processing temporarily when there is an error, and then retry the transaction first in normal mode and then, if normal mode fails, in source mode (same transaction boundaries as on the source).

BATCHESPERQUEUE n

Controls the maximum number of batches that one memory queue can contain. After BATCHESPERQUEUE is reached, a target transaction is executed.

Minimum value is 1.
Maximum value is 1000.
Default is 50.

BATCHTRANSOPS n

Controls the maximum number of batch operations that can be grouped into a transaction before requiring a commit. When BATCHTRANSOPS is reached, the operations are applied to the target.

Minimum value is 1.
Maximum value is 100000.
Default is 1000 for nonintegrated Replicat (all database types) and 50 for an integrated Oracle Replicat.

BYTESPERQUEUE n

Sets the maximum number of bytes that one queue can contain. After BYTESPERQUEUE is reached, a target transaction is executed.

Minimum value is 1000000 bytes (1 megabyte).
Maximum value is 1000000000 bytes (1 gigabyte).
Default is 2000000 bytes (20 megabytes).

OPSPERBATCH n

Sets the maximum number of row operations that one batch can contain. After OPSPERBATCH is reached, a target transaction is executed.

Minimum value is 1.
Maximum value is 100000.
Default is 1200.

OPSPERQUEUE n

Sets the maximum number of row operations in all batches that one queue can contain. After OPSPERQUEUE is reached, a target transaction is executed.

Minimum value is 1.
Maximum value is 100000.
Default is 1200.

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.

TRACE