Troubleshooting
Problem
Customer would like to know what an 'internal' Controller application database optimize does, and how often they should run one.
Symptom
There are two types of database optimisations that can be done:
(1) 'Internal' database optimisation.
- These are triggered/launches from 'inside' the Controller client interface (inside the Controller GUI)
- To launch them, click "Maintain - Users - Single Mode" and then click "Maintain - Database - Optimize"
(2) 'External' database optimisation.
- These are optimisation techniques that are triggered outside of the Controller GUI (typically by using third-party database utilities)
- Examples are given inside separate IBM Technotes such as #1396973 & 1346962.
Cause
This Technote specifically only relates to an 'internal' database optimisation (launched inside the main Controller client GUI).
Resolving The Problem
What does a database optimise do?
The database optimize function has several core and optional functions. Below is a description of each of the actions:
- (1) Remove Period Zero Values
- Clears the value 0 from selected period tables
- Recalculation of ownerships, summation structures and other information which gives quicker access to data
- Rebuilds all indexes in the Microsoft SQL database
- This will enhance performance and give quicker access to data
- TIP: If you have a database maintenance plan (recommended) configured, then (typically) this is configured to run the rebuild automatically once a week anyway. See Technote 1396973 for an example.
- For Controller 10.2.1 (and earlier), selecting this option causes the following Microsoft SQL command to be triggered: DBCC DBREINDEX
- For Controller 10.3.0 (and later), selecting this option causes the following Microsoft SQL command to be triggered: Select 'ALTER INDEX ALL ON ' + name + ' REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);' From sysobjects Where type='U' And Substring(name,1,1)<> 'F' Order By name
- Re-analyzes the schema for Oracle database
- This will enhance performance and give quicker access to data
(2) Rebuild Structure Tables
(3) Rebuild Indexes (advanced option)
- NOTE: This is optional. There is no need to tick/enable this if you already have a SQL maintenance plan in place (run by your I.T. department's DBA) which automatically re-indexes. For example, most customers automatically re-index the database once a week via a SQL Maintenance plan.
Technical details:
(4)Analyze Schema (advanced option)
How often should a customer generally run a database optimise?
The database optimise function should be run periodically, for example:
- Once a week - when in the development phase
- Once a month - when in the production phase
Is there a specific trigger which causes a customer to need to run a database optimise?
In addition to running the optimise "from time to time" (see above) it is generally worthwhile running a database optimise after doing any of the following:
- (a) After importing or entering large amounts of data, for example after using any of the following menu options extensively:
- Transfer > External Data > Import from Flat Files...
- Transfer > External Data > Import from Staging Tables...
- Transfer > External Data > Import from Framework Manager...
- Transfer > External Data > Import General Ledger Journals...
- Transfer > Import Data (Period Values and Investment Register values)
- Company > Data Entry - Reported Values
- Company > Data Entry _ Company Journals
- Excel Link (using fExpVal import specifications for importing data to Controller)
(b) After making significant structure changes (under special circumstances)
How long should a database optimise take?
The time that it takes to run optimize is determined by the size and complexity of the database. The 'rebuilding indexes' stage can be an especially time-consuming process.
Is there a method to schedule a Database Optimise to occur at a specific time?
From Controller 10.3.1 onwards, it is now possible to schedule internal database optimises (to a specific date/time).
- For more details, see separate IBM Technote #2010668.
Related Information
[{"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.3.1;10.3;10.2.1;10.2.0","Edition":"Edition Independent","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
1042328
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21367485