Databases

IBM DB2 9.7 Performance Tuning for Siebel 8.1 Applications

A 3-step procedure

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:

CREATE BUFFERPOOL “SIEBEL_EIM_32K_BP” SIZE 160000 PAGESIZE 32768;

 

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:

CREATE LARGE TABLESPACE “SIEBEL_EIM_DATA32K” IN DATABASE PARTITION GROUP IBMDEFAULTGROUP

PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE

AUTORESIZE YES

INITIALSIZE 32 M

MAXSIZE NONE

EXTENTSIZE 8

PREFETCHSIZE AUTOMATIC

BUFFERPOOL SIEBEL_EIM_32K_BP

OVERHEAD 7.500000

TRANSFERRATE 0.060000

NO FILE SYSTEM CACHING

DROPPED TABLE RECOVERY ON;

 

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:

ALTER TABLE SIEBEL.EIM_ACCOUNT VOLATILE

 

No Job Name Timing – Before Change Timing – After Change Improvement
1 BIP/CNR 7 hours 24 min 1 hour 3 min 85%
2 Products – SKU 19 min 11 sec 3 min 23 sec 82%
3 Shipment – ADL 9 min 40 sec 2 min 42 sec 72%
4 Products – Consumer 10 min 21 sec 1 min 55 sec 80%
5 Products – BOM 11 min 50 sec 1 min 18 sec 90%
6 Shipment – Daily Aggregations 21 min 56 sec 56 sec 95%
7 Customers 16 min 1 sec 34 sec 96%
Figure 1. Performance test results

 

Step 2: Enable DB2 9.7 performance parameters

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

No Parameter Name Current Value New Value
1 LOGBUFSZ 2048 20480
2 HADR_SYNCMODE NEARSYNC ASYNC/SUPERASYNC
3 CUR_COMMIT DISABLED ON
4 DFT_QUERYOPT 5 3
5 STMT_CONC OFF OFF
Figure 2. DB2 9.7 parameter changes

 

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.

Job Type Performance Improvement
Interfaces 373.9 minutes
Figure 3. Performance improvement in interface jobs

 

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:

lsdev -Cc iocp

iocp0 Available I/O Completion Ports

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:

DB2SET DB2_USE_IOCP=OFF