Technologies

DB2 9.7 Performance Tuning for Siebel 8.1 Applications

Take a look at this three-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
Figure 1. Performance test results
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 2. DB2 9.7 parameter changes

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

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.

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

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
Previous post

A Framework that Focuses on the Data in Big Data Governance

Next post

Business Intelligence in the Hadoop Era

  • Matt

    Excellent tips! Short and precise message for the developer & DBA community.

  • Praveen

    Wonderful..helped a lot..!!!

  • Immanuel Rajkumar

    Its really amazing to note that a few simple steps like these can create wonders to the application performance. Simple, Neat & Excellent. Hats off.. !!

    Thanks & Cheers..!!

  • Nishant Bhardwaj

    This is lot of quality stuff here. These performance tuning tips will help any DBA looking to improve database performance at their end. I can use it as reference for future performance tuning and troubleshooting! Thank you very much for the article!!

  • Sudhakar

    Good to see very simple steps for the database performance improvement, thanks for it.