IBM DB2 9.7 Performance Tuning for Siebel 8.1 Applications

This article focuses on three database tuning steps developed using real-time performance tuning experiences on IBM DB2 9.7 for the Siebel 8.1 global sales application. Most of the time, the performance of a Siebel application depends largely on the performance of the underlying database. This article will walk through the steps we followed during the performance tuning exercise to achieve an 83 percent performance improvement for Enterprise Integration Manager (EIM) jobs and an approximately 17 percent improvement for user interface (UI) components.

All of the performance tuning exercises were done on IBM AIX 5.3.12.4 TL12 with 48 GB memory running on a PowerPC POWER6 processor, 8 CPUs with 4204 MHz CPU clock rate and DB2 9.7 Fix Pack 5 with a High-Availability Disaster Recovery (HADR) pair between two data centers.

Step 1: Tune DB2 for Siebel EIM performance

DB2 catalog statistics should be up-to-date so DB2 Optimizer can determine the best data access path. The application team should also be aware of
SIEBSTAT API overheads such as lock-waits when running multiple EIMs in parallel.

1.1

Disable SIEBSTAT API for all EIM jobs. Set the UPDATE STATISTICS parameter to FALSE in the EIM configuration file.

1.2

Create one big buffer pool for EIM tables:

1.3

Use a separate 32 KB automatic storage table space to store all EIM tables with 8 or 16 as extent size and 48 or AUTOMATIC pre-fetch size:

1.4

Set all EIM_% tables to VOLATILE to make sure that index scans are preferred over table scans. Let the DBA job manage the statistics update on the EIM tables once a day instead of after every EIM run. For example, to set SIEBEL.EIM_ACCOUNT as volatile use the following command:

Step 2: Enable DB2 9.7 performance parameters

DB2 9.7 has many performance tuning features that can help improve application performance significantly.

We have had issues with application performance after migrating from DB2 9.1 FP 8 to DB2 9.7 FP 5 with CUR_COMMIT being ON. We observed congestion in HADR during the performance window, along with many log buffer full conditions. After tuning the LOGBUFSZ and using a new DB2 9.7 HADR_SYNCMODE SUPERASYNC, we were able to get the performance back to the normal state. With these changes we were able to achieve a gain of 373.9 minutes in our UI interface jobs. Please be aware that SUPERASYNC will not guarantee 100 percent data availability in case of a primary failure.

Step 3: Do not enable I/O completion ports on AIX 5.3

DB2 9.7 can make use of operating system I/O completion ports (IOCP) for better I/O capabilities. For instance, page cleaners and pre-fetchers will make use of this asynchronous input/output for faster data access.

We enabled IOCP for AIX 5.3 before migrating the database to DB2 9.7, keeping this new feature in mind.

To check IOCP at the OS layer, execute this command using the instance owner:

Unfortunately, IOCP affected the system’s availability. If IOCP is ON at the OS layer, turn the feature off at the DB2 instance layer by using the following command and restart the instance: