Tuning the Capture program to minimize CPU usage

There are certain general recommendations to follow when minimizing CPU usage for the Capture program. This section details general recommendations on how to minimize CPU usage for the Capture program.

Use the following general recommendations to minimize CPU usage for the Capture program:
  • Consider capturing only changes made to registered columns.
    You can control whether the Capture program writes a row to a CD table for changed source rows according to the following row-capture rules:
    1. Capture changes only when registered columns change
    2. Capture changes when any column (registered or not) changes
    These rules correspond to the CHGONLY column of the IBMSNAP_REGISTER table.

    If you register a subset of columns and usually nonregistered columns are updated in the source table, the Capture program can skip many rows when capturing data from the Db2® log, and thus decrease its CPU usage. For this case, specify the first row-capture rule (Capture changes only when registered columns change). However, if you register all columns, or if usually only registered columns are updated in the source table, do not specify the first row-capture rule because no rows (or few rows) can be skipped, and the Capture program's CPU usage can increase unnecessarily. Check the statistics in the Capture monitor (IBMSNAP_CAPMON) table to verify whether the Capture program is able to skip rows because of the row-capture rule; see Measuring replication performance for more information.

    In the Replication Center, specify either of the row-capture rules: Capture changes to registered columns only or Capture changes to all columns. For the System i ADDDPRREG command, specify either GENCDROW=*ALLCHG or GENCDROW=*REGCOLCHG.

  • For z/OS® environments, use dynamic SQL caching.

    Dynamic SQL caching allows the SQL PREPARE statement to be cached, and therefore reused when the identical statement is reissued.

  • For System i environments, register all columns of a source table and include only the afterimage columns in the CD table.

    For example, if you have a source table that contains 100 columns, and you decide to save some disk space for the CD table by registering 94 of the source columns (perhaps because the other 6 columns are not needed for the target tables), you will discover that CPU usage for the Capture program will increase to filter the extra columns out for every insert into the CD table.