IBM Support

Slow Excel link spreadsheet performance (when using DB2) caused by default INTRA_PARALLEL setting (intra-partition parallelism is disabled)

Troubleshooting


Problem

Excel link spreadsheets are very slow.

Symptom

In one real-life customer example, an Excel-link (XLSX file) report originally took 70 seconds to run (with intra-partition parallelism disabled).

  • After enabling intra-partition parallelism, the report took 35 seconds (twice as fast).

Cause

There are many different possible causes for slow Excel link spreadsheet reports.

  • TIP: For more examples, see separate IBM Technote #1347497.

 

This Technote specifically relates to the scenario where the cause is that the DB2 database server is using the default settings, where 'Intra-partition' parallelism is disabled.

------------------------------------------------------------------------------------

IMPORTANT: Enabling intra-partition parallelism does not always provide increased performance. Under certain circumstances it can slow down the overall speed of the Controller system. Therefore customers are urged to:

(a) avoid enabling intra-partition parallelism unless absolutely necessary

  • For most customers, keeping the default 'disabled' value is the best overall (balanced) setting.

(b) Carefully monitor/measure speeds before/after enabling intra-partition parallelism, to check that it has benefited the system speed overall.

------------------------------------------------------------------------------------

More Information:

Enabling intra-partition parallelism allows DB2 to use more than one CPU core to process a query. By default, this is disabled (= set to 'NO'), so therefore DB2 can only use one CPU core to run each query.

Environment

Database hosted on DB2.

Diagnosing The Problem

Part One:

To check if your DB2 server is currently configured to use intra-partition parallelism, ask your I.T. department's database administrator (DBA) to perform the following steps:

1. Logon to your DB2 database server

2. Launch:  DB2 Command Window - Administrator

3. Type the following:     DB2 GET DBM CFG
 
4. Scroll down until you find a section similar to:
image-20181015112144-1
Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism     (INTRA_PARALLEL) = NO
 
 

Part Two:

To check if your individual database (specifically, the main Controller application repository database) is currently configured to use intra-partition parallelism, ask your I.T. department's database administrator (DBA) to perform the following steps:

1. Logon to your DB2 database server

2. Launch:  DB2 Command Window - Administrator

3. Type the following:     DB2 connect to <database>
 (Where <database> = the name of your main Controller application repository database)
 
Example:    db2 connect to CCR01
 
4. Type:     DB2 get db cfg
 

5. Scroll down until you find a section similar to:

image-20181015150335-1

Degree of parallelism                      (DFT_DEGREE) = 1

[This means that the maximum number of CPU cores that the system will use - for one query - is 1]

 

Resolving The Problem

Enable intra-partition parallelism (on your DB2 server), and check that the databases are configured to use all CPUs.

------------------------------------------------------------------------------------

IMPORTANT: Enabling intra-partition parallelism can increase the speed of one query, but it will increase the overall load on the server (which could actually reduce the speed of the system overall).

Therefore, before enabling it:

  • Make sure that the DB2 server hardware is sufficiently powerful to cope with the increased load
  • Perform simple before/after tests (if you change the setting) to make sure that the overall speed of the system is not adversely affected (especially under load - when lots of users are using the system) after enabling intra-partition parallelism.

 

TIP: For most customers, the best overall (balanced) setting is to have intra-partition parallelism disabled (the default setting).

------------------------------------------------------------------------------------

 

Steps:

To enable intra-partition parallelism, ask your I.T. department's database administrator (DBA) to perform the following steps:

1. Logon to your DB2 database server

2. Launch:  DB2 Command Window - Administrator

3. Type the following:    db2 update dbm cfg using intra_parallel yes
 

 image-20181015114059-1

-------------------------------------------------

In most cases the 'Maximum query degree of parallelism'  (MAX_QUERYDEGREE) is already set to 'ANY', so (in most environments) you can skip step 4:

4. Type a command similar to:     db2 update dbm cfg using max_querydegree yes

-------------------------------------------------

5. Type a command similar to:     db2 connect to <database_name>

For example, if your database was called 'CCR01' then type:       db2 connect to CCR01

 

6. Type:   db2 update db cfg using DFT_DEGREE ANY

-------------------------------------------------

7. Repeat steps 5 & 6 for all your Controller application repository databases (for example 'production' and 'test' and so on).

8. Obtain a short period of downtime (no users on the system)

9. Restart the DB2 server by typing the following commands:

db2stop force

db2start

image-20181015114548-2

10. Test.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.3.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 November 2018

UID

ibm10735173

Page Feedback