To optimise your IBM Cognos Controller database, you must first put the system into  ‘Single Mode’ which means you need to ensure all users log out of the Controller database to be optimised.

To view users logged in and log users off, select the menu, ‘Maintain / User / Manage Active Users’.

• Select ‘Mark all users‘ check box to select all users except yourself.
• Click the ‘Run’ button
• A question will appear – ‘The system is not locked for new users. Do you want to continue?’, choose ‘Yes’
• The user status will change to ‘Logging off’ and then the user will disappear from the list once logged off.

Once all users are logged out of the Controller database you will be able to select the menu, ‘Maintain / User / Single Mode’.   This prevents users logging in.

IMPORTANT:   Once the optimise has run, deactivate the ‘Single Mode’ by re-selecting the menu ‘Maintain / User / Single Mode’, this will allow users to log back into the database.

To run the optimise.  Select the menu, ‘Maintain / Database / Optimise’.

The options are as follows: –

• Remove Period Zero Values – Clears 0 values from selected period tables. This is typically not selected, as illustrated below. For example, an entity may wish to report a record of zero headcount that it would want to retain. 
• Rebuild Structure Tables – Recalculates ownerships, rebuilds structures to give quicker access to data
• Rebuild Indexes – This rebuilds data indexes for faster access to data. When checked this commonly results in the database taking longer to process for optimisation.  For some customers, with very large databases, this can take hours, not minutes. 
• Analyze Schema – This is used for Oracle database only so will commonly be greyed out.

Select the ‘Run’ button.    A message will appear to confirm that the optimise is complete.

REMEMBER: To de-active the ‘Single Mode’.

Please also consider the option to ‘Schedule Job’ for database optimisation – please see “How to: Schedule an internal optimise in IBM Cognos Controller”. This is how we’d advise this activity to be ran for large Controller database customers located on SaaS. For equivalent customers hosted on premise we’d advise discussing this topic with local IT and integrating this activity with SQL maintenance works.