Troubleshooting
Problem
User opens a spreadsheet report (XLSX file), logs onto Controller and presses F9 (to run the report). The report takes a long time to run.
Symptom
Slow performance when running/refreshing report.
Example:
In one real-life example, when running the Excel report:
- Original time taken (before changes listed in this Technote made) = 130 seconds
- Fixed time taken (after performing steps in this Technote) = 70 seconds (45% speed improvement)
In addition, when running a consolidation:
- Original time = 330 seconds
- Fixed (afterwards) time = 150 seconds (55% speed improvement)
Cause
There are many possible causes for slow Excel link performance.
- TIP: For more examples, see separate IBM Technote #1347497.
This Technote relates to the scenario where the cause is that the DB2 database XDBxx indexes are using the default (non-clustered) settings.
- For some customer financial configurations (models), these non-clustered indexes are not optimal
- Specifically, for some customers their reports/consolidations run much faster with clustered indexes.
Environment
This technote only applies to customers using DB2 as the database storage mechanism.
- If using Microsoft SQL, the XDBxx indexes are already clustered.
Resolving The Problem
Modify the DB2 database indexes from the defaults.
- IMPORTANT: Perform 'before' and 'after' performance (speed) tests, to make sure that the index changes give improvements for your specific financial configuration/system.
Steps:
--------------------------------------------------------
The following steps assume that:
- Your database name = CCR01
- The username (used by Controller to connect to the database) = fastnet
IMPORTANT: You will need to modify all the scripts accordingly, for your database/user name!
- If you are unsure of your database/user name, you can check this by launching 'Controller Configuration' (on your application server) and opening 'Database Connections' section.
--------------------------------------------------------
1. Test a number of representative reports (and/or consolidations), to get a 'baseline' figure for the current speed of the system
Then ask your DB2 administrator to:
2. Logon to the DB2 server, as a user who is a DB2 administrator. Launch 'DB2 Command Window - administrator' and run the following commands:
DB2 UPDATE DBM CFG USING INTRA_PARALLEL NO
DB2 UPDATE DBM CFG USING MAX_QUERYDEGREE -1
db2 connect to ccr01
db2 update db cfg using DFT_DEGREE 1
3. Run the attached script "drop_indexes.db2" on the relevant database (which drops the old/non-clustered indexes)
For example:
- Launch 'Data Studio'
- Connect to the database (as db2admin)
- Open the 'SQL' icon ("New SQL Script"):
- Copy and paste the contents of the script into the relevant screen, and run those commands
- This may take 2 minutes or so.
4. Similarly, run the attached script "create_indexes.db2" (which creates new 'clustered' indexes)
- This may take 2 minutes or so.
5. Run the attached script "reorg_tables.db2" (to reorganize tables)
TIP: If you are using Data Studio, then you will need to first:
(a) Change the connection method to be "DB2 Client Command Line Processor":
(b) then add the following line at the beginning of the script: connect to CCR01;
- NOTE: This will take relatively long time (for example 10+ minutes) to complate, depending on the size of your XDB data tables.
6. Run the attached script "statistics.db2" (to update statistics)
TIP: If you are using Data Studio, then again you will need to:
(a) Make sure that the connection method is "DB2 Client Command Line Processor":
(b) then add the following line at the beginning of the script: connect to CCR01;
- NOTE: This will take reasonably long time (for example 5+ minutes) to complate, depending on the size of your XDB data tables.
7. Test the same reports (and/or consolidations) that you previously did (in step 1), to give an indication of how much speed improvement you have got.
Related Information
Was this topic helpful?
Document Information
Modified date:
21 November 2018
UID
ibm10740825