IBM Support

Controller DB2 database optimisation and maintenance Proven Practice / best practice

Troubleshooting


Problem

Customer is hosting their Controller database using IBM DB2. Customer would like to: - maximise performance - make regular backups - keep the database in good condition. How can the customer maintain their SQL databases, in order to maximise performance/speed and minimise system problems?

Symptom

If regular maintenance is not performed on the Controller database, then the following can occur:

1. Poor performance, caused by out-of-date database statistics
    • The solution being "Data access optimization" (statistics collection)

2. Poor performance, caused by out-of-date (fragmented) database indexes
    • The solution being "data defragmentation" (table or index reorganization)
3. Data loss caused by lack of backups.

Cause

Database tables and indexes can become fragmented over time. Therefore, periodically it is necessary to reorganize (defragment) tables and indexes.
  • DB2 has built-in functionality for finding and fixing defragmented tables and indexes. In fact, DB2 has a feature that can perform this maintenance automatically.
  • Tips and instructions (to achieve this) are explained below.

Environment

Customer's Controller database hosted on IBM DB2 server.

This Technote is written assuming that the customer is using DB2 10.5.
  • The advice can generally be used for other versions of DB2
  • However, do check with your I.T. department's DB2 expert to make sure that the advice (in this Technote) meets your needs.

Resolving The Problem

---------------------------------------------------------
There are several methods to ensure that the DB2 indexes are optimised.
  • TIP: See separate IBM Technote #0879725 for more examples of other methods.
This Technote relates to the method of creating a maintenance plan on your DB2 server.
---------------------------------------------------------
Steps to create a maintenance plan on your DB2 server
Consult your I.T. department's database server administrator (DBA) to ensure that your database(s) are regularly optimised (and of course backed up).
  • There are many different methods to achieve the same objective. This Technote contains information on some of the possible methods that you can employ.
  • Your DBA should be experienced in database optimise techniques, and should also be able to tune those techniques to work best in your specific environment.

Suggested Example
As a simple starting point for experienced DBAs (or as an overall guide for inexperienced DBA administrators) IBM Support recommends that customers create a standard 'automatic' DB2 database maintenance plan to automatically/regularly perform some tasks (in the background).
  • For most customers, implementing this automatic maintenance routine should be sufficient for their needs. However, if you have more advanced needs (or questions), then these should be answered by using the links at the end of this Technote.
 

Creating an Automatic maintenance routine:

There are different methods to create an automatic maintenance routine. The following steps are based on:

  • DB2 10.5
  • running on Windows.
  • Using the "Data Studio 4.1.0.1 Client" to perform the configuration inside a GUI

The steps may need to be modified slightly for other environments.
  • TIP: For more details on the automatic maintenance routines, see link 'Automatic maintenance' (at the end of this Technote).
 

1. Logon to your DB2 server

2. Launch 'Data Studio' client

3. Locate/select the relevant Controller database (for example 'CCR')

4. Right-click on the database, and choose 'Set Up and Configure' then 'Configure Automatic Maintenance':


5. In the 'Options' section, enable/tick ALL options':




IMPORTANT: If your DB2 administrator has already configured backups (via a different/alternate method) then untick 'Automatic database backup'.

6. Inside 'Online Maintenance Window' choose the dates/times when you are happy for the system to run slightly slower than normal (because it will be performing maintenance)

  • During these times, the system will still be online (functioning), with (potentially) users in Controller.
 

For example, you may be happy for the system to be slightly slower (but still in online) between 8pm and 6am every day (except Sunday):


7. Inside 'Offline Maintenance Window' choose the dates/times when you are happy for the system to be unavailable (downtime), because the users are aware that it will be performing maintenance

  • During these times, the system will be offline (disabled), with no users in Controller.
 

For example, you may be happy for the system to be have downtime between 2am and 8am every every Sunday morning:




8. Inside 'Backup Policy', implement a sensible backup strategy.
  • This strategy will be affected by whether-or-not your databases are configured to use 'circular logging'.

For example:


In the above example, 'circular logging' is enabled. This causes all backups to only be performed during offline.
  • For most customers, it is preferable to NOT use circular logging. This will allow Online backups!

===============================================
VITAL: Database backups are the most important part of any database maintenance routine.
  • MAKE SURE that your chosen settings are appropriate for your needs
  • CONSULT with an experienced database administrator (DBA) if you are unsure
  • REGULARLY TEST your backup routines are (a) creating backup files and (b) these files are being archived (for example to test) and (c) these backup files can be successfully restored
===============================================

9. Inside 'Reorg Policy', choose setting similar to the following:


10. Inside 'RunStats Policy', choose setting similar to the following:


==================================
TIP: At this stage, if desired you can click 'Preview Command' to see what the SQL command would be to implement this routine. For example:
  • CONNECT TO CCR;
    UPDATE DATABASE CONFIGURATION USING auto_db_backup ON auto_reorg ON auto_prof_upd ON auto_stats_prof ON;
    CALL SYSPROC.AUTOMAINT_SET_POLICY ('MAINTENANCE_WINDOW', BLOB('<?xml version="1.0" encoding="UTF-8"?><DB2MaintenanceWindows xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"><OnlineWindow Occurrence="During" startTime="20:00:00" duration="10"><DaysOfWeek> Mon Tue Wed Thu Fri Sat</DaysOfWeek><DaysOfMonth>All</DaysOfMonth><MonthsOfYear> All</MonthsOfYear></OnlineWindow><OfflineWindow Occurrence="During" startTime="02:00:00" duration="6"><DaysOfWeek> Sun</DaysOfWeek><DaysOfMonth>All</DaysOfMonth><MonthsOfYear> All</MonthsOfYear></OfflineWindow></DB2MaintenanceWindows> ') );
    CALL SYSPROC.AUTOMAINT_SET_POLICY ( 'AUTO_BACKUP', BLOB('<?xml version="1.0" encoding="UTF-8"?><DB2AutoBackupPolicy xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"><BackupOptions mode="Offline"><BackupTarget><DiskBackupTarget><PathName>D:\DB2_Backups</PathName></DiskBackupTarget></BackupTarget></BackupOptions><BackupCriteria numberOfFullBackups="1" timeSinceLastBackup="24" logSpaceConsumedSinceLastBackup="6400"/></DB2AutoBackupPolicy> ') );
    CALL SYSPROC.AUTOMAINT_SET_POLICY ( 'AUTO_REORG', BLOB('<?xml version="1.0" encoding="UTF-8"?><DB2AutoReorgPolicy xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"><ReorgOptions dictionaryOption="Keep" indexReorgMode="Online" useSystemTempTableSpace="false"/><ReorgTableScope ><FilterClause>TABSCHEMA NOT LIKE ''SYS%''</FilterClause></ReorgTableScope ></DB2AutoReorgPolicy> ') );
    CONNECT RESET;
==================================

11. Click the 'Run' button, and ensure that the result is successful:

12. VITAL: It is important that you regularly check (for example at least every month) that the maintenance routine is working OK.
  • Most importantly, check that the backup routine is working OK.


More Information (different methods)
As stated previously, there are several different methods to achieve the same goal (database maintenance). Below is some information giving alternative ideas and methods:
(1) This link http://www.ibm.com/developerworks/data/library/techarticle/dm-1203automaintdb2luw/ gives a description of how to use the DBA application "Data studio" (a free tool from IBM), which can be used for all common DBA tasks (for example configuring database parameters such as 'automatic maintenance').
  • In other words, it gives more information on the steps described in the above Example.

(2) To configure the parameters using SQL and stored procedures see here: http://www.ibm.com/developerworks/data/library/techarticle/dm-0801ganesan/

(3) For a description of database configuration parameters (where it is possible to read more about automatic maintenance) see here:
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/t0005243.html

(4) For more information on using the old (DB2 9 and older) GUI tool 'Control Center' (to configure automatic maintenance of tables and indexes), see here: This includes how to configure it from CLP command prompt.

(5) Further reading.
If anyone would like to handle this all by themselves it is possible to manually run scripts that create scripts that can be run. This demands much more knowledge of DB2 database.

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

Document Information

Modified date:
03 April 2019

UID

swg21981728

Page Feedback