How to Improve Initial Sync Performance for Large Tables

For large Db2 for z/OS tables, you can improve initial sync performance by temporarily assigning dedicated buffer pools to the table space and the table’s clustering index before starting the sync.

Db2 uses buffer pools to cache table and index pages in memory. Assigning larger, page-fixed buffer pools to the table space and clustering index can reduce disk I/O during the initial sync.

Prerequisites

Before you begin, identify the following values:

Placeholder	Description
`<database_name>`	Name of the Db2 database that contains the table space
`<tablespace_name>`	Name of the table space that contains the table
`<indexspace_name>`	Name of the index space for the clustering index
`<index_name>`	Name of the clustering index
`<ts_bufferpool>`	Dedicated buffer pool for the table space
`<idx_bufferpool>`	Dedicated buffer pool for the clustering index
`<vpsize>`	Number of buffers to allocate
`<table_name>`	Name of the table
`<numcols>`	Number of leading index columns for frequency statistics
`<count>`	Number of frequent values to collect

The table space and index buffer pools must use the correct page size for their assigned objects.

To allocate dedicated buffer pools for a table:

Activate and configure dedicated buffer pools — one for the table space and one for the clustering index:
```
-ALTER BUFFERPOOL(<ts_bufferpool>) VPSIZE(<vpsize>) PGFIX(YES) FRAMESIZE(1M)
-ALTER BUFFERPOOL(<idx_bufferpool>) VPSIZE(<vpsize>) PGFIX(YES) FRAMESIZE(1M)
```
Replace <vpsize> with the appropriate number of buffers to allocate for your environment.
These are Db2 commands, not SQL statements. Issue them from a z/OS® console, a DSN session under TSO, a DB2I panel (DB2 COMMANDS), an IMS or CICS® terminal, or a program using the instrumentation facility interface (IFI). For more information, see the ALTER BUFFERPOOL command documentation.

Assign the table space and index space to the dedicated buffer pools:

ALTER TABLESPACE <database_name>.<tablespace_name> BUFFERPOOL <ts_bufferpool>;
ALTER INDEX <index_name> BUFFERPOOL <idx_bufferpool>;

For the buffer pool assignments to take effect, stop and restart the table space and index space:
Stopping a table space or index space makes the object temporarily unavailable and may impact running workloads. Run these commands only during a scheduled maintenance window or on a non-production subsystem, and follow your organization's change-management process.
```
-STOP DATABASE(<database_name>) SPACENAM(<tablespace_name>)
-STOP DATABASE(<database_name>) SPACENAM(<indexspace_name>)
-START DATABASE(<database_name>) SPACENAM(<tablespace_name>)
-START DATABASE(<database_name>) SPACENAM(<indexspace_name>)
```
These are Db2 commands, not SQL statements. Issue them from a z/OS® console, a DSN session under TSO, a DB2I panel (DB2 COMMANDS), an IMS or CICS® terminal, or a program using the instrumentation facility interface (IFI). For more information, see the STOP DATABASE command documentation and the START DATABASE command documentation.

Run RUNSTATS to refresh catalog statistics. RUNSTATS also scans the table space and indexes, which pre-loads data pages into the buffer pool before the sync begins:

RUNSTATS TABLESPACE <database_name>.<tablespace_name>
  INDEX (ALL) KEYCARD
  FREQVAL NUMCOLS <numcols> COUNT <count> TABLE (<table_name>) COLUMN (ALL)
  REPORT NO SHRLEVEL CHANGE

After the buffer pools are assigned and statistics are current, start the initial sync.
After the initial sync completes, reassign the table space and clustering index to their original buffer pools to release the dedicated resources: