Performance tuning for both DB2 and Q Replication are an ongoing process.  However, there are three tools which provide an initial set of recommendations for bufferpools, Database Manager (DBM or instance) and database configuration parameters, indexes, and other application table objects.

DB2 Configuration Advisor

The DB2 Configuration Advisor recommends the initial values of the bufferpool size, database configuration parameters, and database manager configuration parameters.

To use the Configuration Advisor, specify the AUTOCONFIGURE command for an existing database, or specify AUTOCONFIGURE as an option of the CREATE DATABASE command.

Example:

db2 autoconfigure using mem_percent 50 num_stmts 100000 tpm 6000 admin_priority performance num_local_apps 144 num_remote_apps 0 isolation UR apply none

DB2 Design Advisor

The DB2 Design Advisor is used to improve application SQL performance by creating (or dropping) indexes, Materialized Query Tables (MQTs), and MultiDimensional Clustering (MDCs).  The Design Advisor analyzes an SQL workload against existing table structures and recommends changes to improve performance.  The workload can be derived from Dynamic Statement Cache (DSC).  The DB2 Design Advisor command can be invoked by the db2advis command.

A prerequisite is creating the DB2 EXPLAIN tables for the schema conducting the analysis.

cd /<path>/sqllib/misc

db2 –vtf EXPLAIN.DDL

Then run the db2advis command.

db2advis -d mydb -g -a myuser -x -c USERSPACE1 -f -m IMCP -o db2advis_output

Q Replication Dashboard Performance Advisor

The Q Replication Performance Advisor helps you identify latency issues with your replication environment and suggests ways to lower end-to-end latency.  The Performance Advisor tab is located within each Monitoring Group.  The Q Replication Dashboard Performance Advisor can recommend updating statistics or reorganizing tables.