IBM Support

Slow performance when using DB2, caused by limitation of database optimise routine (in Controller 10.4.0 and earlier) relating to database statistics

Troubleshooting


Problem

Customer is experiencing slow performance in Controller, when used with DB2.

Cause

 There are many possible causes for slow performance with Controller, when using DB2.
  • TIP: For many generic examples, see separate IBM Technote #0879725.

This Technote specifically relates the scenario where the cause is a limitation of Controller 10.4.0 (or earlier) relating to how the internal 'Database Optimise' routine works, if using a DB2 database.
  • Specifically, the 'database optimise' task feature 'analyse schema' does not work correctly:
image-20200121181912-1
  • Therefore it does not update the statistics (related to the indexes) in the most efficient way.

Environment

Both of the following are true:
  • Controller 10.4.0 (or earlier)
  • Database hosted on DB2.

Diagnosing The Problem

To check if your DB2 database contains tables that need re-indexing, you can use a DB2 built-in command:
CALL REORGCHK_TB_STATS('T','ALL');
This command does not make any changes, and runs quickly (typically 1-5 minutes).
  • It uses 3 different test functions ("F1", "F2", "F3") to test the statistics of each table.
If you run it in Data Studio then you can view the results easily:
image-20190501171624-1
TIP: By default, the results are limited to 500. To see all results, click on the 'Fetched 500 records' hyperlink (or alternatively click "Window - Preferences - Data Management - SQL Development - SQL Results View Options') and increase the value of 'Max Row Count' and 'Max Row Display Count', for example to 5000:
image-20190501192608-1
  
The important column is 'REORG'. This shows the results for <F1>,<F2>,<F3>
  • No action needed:    -
  • Requires reorganising:     *
For example, in the above picture it shows that the table 'CDARCRES' was tested using F1, F2 and F3, and gave the result:  -**
In other words, it found that:
  • F1 suggested no action needed
  • F2 suggested that the table required reorganising (updating of statistics)
  • F3 suggested that the table required reorganising (updating of statistics).

Resolving The Problem

Fix:
Upgrade to Controller 10.4.1 (or later). This contains a new/improved internal 'database optimise' routine, where the option 'Analyze Schema' works correctly:
image-20200121181946-2
  • By ticking 'Analyze Schema' it causes the new/improved database optimise routine to run all the tasks mentioned below inside the 'workaround' section.
   
Workaround:
Ask your DB2 administrator (DBA) to run the following (manual) tasks on your Controller database to:
  • Check which tables need their statistics updated
  • Update those table's statistics.
Steps:
1. Check what the name of your database schema owner is (for example 'COGNOS' or 'FASTNET')
  • TIP: This is the username used inside 'Database Connections' section of 'Controller Configurations'.
2. Use your preferred tool to run scripts in
  • The following screens assume you are using IBM Data Studio.
3. Choose which script ('thorough' or 'basic') to run:
   
Method #1 - Thorough
This script checks for tables using all functions (F1, F2, F3).
  • In theory this will give the best performance improvement
  • However, it may be 'overkill' for many customers.
CALL REORGCHK_TB_STATS('T','ALL');
SELECT TABLE_SCHEMA, TABLE_NAME FROM SESSION.TB_STATS WHERE REORG LIKE '%%*' OR REORG LIKE '%*%' OR REORG LIKE '%%*' AND TABLE_SCHEMA <> 'SESSION' AND TABLE_SCHEMA ='FASTNET' ORDER BY TABLE_NAME;
Method #2 - Basic
This script checks for tables using only the function F2 (which relates to the "Effective Space Utilization of Data Pages").
  • In some cases, this is enough to identify the 'worst' tables, which gives sufficient improvement for many customer's needs.
     
CALL REORGCHK_TB_STATS('T','ALL');
SELECT TABLE_SCHEMA, TABLE_NAME FROM SESSION.TB_STATS WHERE REORG LIKE '-**' OR REORG LIKE '-*-' OR REORG LIKE '**-' AND TABLE_SCHEMA <> 'SESSION' AND TABLE_SCHEMA ='FASTNET' ORDER BY TABLE_NAME;
       
4. Modify the script, by replacing 'FASTNET' with the name of your schema owner (for example 'COGNOS')
5. Run the modified script
  
6. Inside the 'SQL results' section:
- Click tab 'History'
- Expand the 'Succeeded' entry at the bottom, by clicking on the arrow to the left of it
- Highlight the lowest 'Succeeded' (which is now visible):
image-20190501194032-1
7. Click the tab 'Result1'.
  • This will give a list of all the tables that your script thinks need their statistics reorganised, for example:
image-20190501194250-2
 TABLE_SCHEMA     TABLE_NAME
 ------------     ------------------------------
 FASTNET         CDARCRES
 FASTNET         CRDACCOUNTREPORTSANALYZE
 FASTNET         CRDACCOUNTREPORTSCODES1
 FASTNET         CRDACCOUNTREPORTSCODES2
 FASTNET         CRDACCOUNTREPORTSSUMMATIONS1
 FASTNET         CRDACCOUNTREPORTSSUMMATIONS2
 FASTNET         CRDACCOUNTVERIFYCODINGACCOUNTR
 FASTNET         CRDACCOUNTVERIFYREPORT
 FASTNET         CRDCOMPSTRREPCODESORT
 FASTNET         CRDCURCONV
 FASTNET         CRDCURCONVBIG
 FASTNET         CRDGROUPJOURNALSREP
 FASTNET         CRDICBALLANDCOMPNOTRANSCURR
 FASTNET         CRDICBALPORTCOMP
 FASTNET         CRDICBALPORTDIFF
 FASTNET         CRDJOURNALSOTHER
 FASTNET         CRDLEDGERREPORT
 FASTNET         CRDRECACCOBETYP
 FASTNET         CRDREPICACCONTS
 FASTNET         CRDREPICACCONTSBYCOUNTERCOMP
 FASTNET         CRDREPORTADHOC1112
 FASTNET         CRDREPORTADHOC15
 FASTNET         CRDREPORTADHOC610
 FASTNET         CRDREPSUMACCOUNTS
 FASTNET         CRDRIGHTSREPUSER
 FASTNET         DISABLED_TRIGGERS
 FASTNET         SABDLOG
 FASTNET         SAXCEREGEL
 FASTNET         XACCLOCK
 FASTNET         XBATCHQUEUE
 FASTNET         XBOLVKD
 FASTNET         XBOLVKDALL
 FASTNET         XBOSTRUC
 FASTNET         XBOT
 FASTNET         XCECOLS
 FASTNET         XCEREGEL
 FASTNET         XDBAUDIT
 FASTNET         XDIMNYST
 FASTNET         XEXTDIMF
 FASTNET         XFDCELLINFO
 FASTNET         XFDLINECOL
 FASTNET         XFORMLOCK
 FASTNET         XID
 FASTNET         XIDT
 FASTNET         XKOD
 FASTNET         XKODT
 FASTNET         XKONTXT
 FASTNET         XKSTRUC
 FASTNET         XKSTRUCS
 FASTNET         XKSTRUCTREE
 FASTNET         XMAPPING
 FASTNET         XMEDLEM
 FASTNET         XNYST
 FASTNET         XOMRV
 FASTNET         XOPEN
 FASTNET         XPERORDER
 FASTNET         XRBREP
 FASTNET         XRCLINET
 FASTNET         XREPSTAT
 FASTNET         XSUBM
 FASTNET         XSUM
 FASTNET         XSUMALL
 FASTNET         XSUMNIVA
 FASTNET         XSUMTREE
 FASTNET         ZFIELDS
 FASTNET         ZLOG
 FASTNET         ZLOOP
 FASTNET         ZVAR
 
8.Run a script similar to the following, which contains your schema name (for example 'FASNET') and then mentions the first table name in the list (for example 'CDARCRES') mentioned above. For example:
   
call sysproc.admin_cmd ('reorg table FASTNET.CDARCRES');
call sysproc.admin_cmd ('runstats on table FASTNET.CDARCRES with distribution and detailed indexes all');
   
9. Repeat step 8 for all of the tables mentioned above (for example 'CRDACCOUNTREPORTSANALYZE', ' CRDACCOUNTREPORTSCODES1' and so on...) which have a table_schema the same as your script mentions (for example 'FASTNET').
10. Repeat the above steps periodically (for example every 1-4 weeks) to make sure that the database is in top condition.

Document Location

Worldwide

[{"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.2.1, 10.3.0, 10.3.1, 10.4.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
21 January 2020

UID

ibm10879753

Page Feedback