Summary of DB2 and Q Replication Performance Tuning Techniques

Posted by Frank Fillmore on March 25, 2014 under DB2 for Linux Unix Windows, Q-Replication, SQL Tuning. Tags: , .

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.

Q Replication Dashboard v10.1 Fixpack 1 Now Available

Posted by Frank Fillmore on June 16, 2011 under InfoSphere, Q-Replication. Tags: , , .

On June 9, 2011 IBM released Fixpack 1 of the Q Replication Dashboard v10.1.  Cool new features include an Alert Manager that sends e-mail messages when replication thresholds are breached (e.g. end-to-end latency exceeds 30 seconds).  After some tire-kicking, I will let you know if this is a viable substitute for the asnmon monitoring feature of Q Replication.  The dashboard has also been integrated with Tivoli Enterprise Portal.

Full details and the free download are at https://www-304.ibm.com/support/docview.wss?rs=3557&uid=swg24023065&wv=1