StarQuest Technical Documents

Using Truncate to Minimize Logging Activity for DB2 Targets

Last Update: 15 December 2010
Product: StarQuest Data Replicator
Version: 3.65 or later
Article ID: SQV00DR024

Abstract

This technical document describes techniques to minimize logging activity in certain situations when the destination system is running DB2.

Issue:

If the destination system is running DB2, the existing destination table contains a large number of rows, and the destination options for a subscription are configured as

• "Use existing table" (Snapshot) or "Use existing table for baseline" (incremental)
  and
  
• "Delete existing data before replication"

then running a snapshot or a baseline may result in a large amount of logging activity on the destination database while the existing rows of the table are deleted.

This is not an issue if the destination system is running other database systems such as SQL Server or Oracle, as SQDR invokes a built-in truncate function supplied by those database systems.

Solution 1 - TRUNCATE Stored Procedure (DB2 UDB for Linux, UNIX & Windows):

The IBM DeveloperWorks article Using DB2 Routines to Ease Migration describes the use of a stored procedure to quickly remove all the content from an existing table with minimal logging activity.

To use the TRUNCATE stored procedure with SQDR:

• Build, register, and test the stored procedure as described in the article. You may ignore the other stored procedures described in the article.
• Modify the SQDR subscription: on the Processing tab of the subscription, add the following as a pre-processing step for the destination:

CALL MYSCHEMA.TRUNCATE( 'SCHEMA', 'TABLE')

(where myschema is the registered location of the stored procedure, and SCHEMA and TABLE describe the location and name of the destination table).

Solution 2 - DB2 IMPORT command (DB2 UDB for Linux, UNIX & Windows):

You can also truncate the table using the DB2 IMPORT command. Use /dev/null for UNIX or NUL for Windows, or import from a blank text file.

db2 connect to DATABASE

db2 import from /dev/null of del replace into MYTABLE

or:

db2 import from NUL of del replace into MYTABLE

Solution 3 - CLRPFM command (iSeries):

On the iSeries, you can use the CLRPFM (Clear Physical File Member) command, either from a terminal session:

CLRPFM FILE(MYLIB/MYTABLE)

or by invoking a stored procedure from SQDR as a pre-processing step for the destination (this can be found on the Processing tab of the subscription properties):

call QSYS.QCMDEXC('CLRPFM MYLIB/MYTABLE', 20.00000)

See Calling an AS/400 Program with QCMDEXC for details on using QCMDEXC.

Caveat: Use caution if the destination table is also the source of an incremental subscription, as CLRPFM may cause SQDR Plus to flag the table as altered, requiring the subscription to be recreated.