Running an internal optimise in IBM Cognos Controller requires the system to be in ‘Single Mode’, with all users logged out. Scheduling the optimise allows you to schedule it to run when no users are expected to be in the database. We have another article detailing how to run a database optimise.
Select the menu, ‘Maintain / Database / Optimise’ and select the options required: –
- Remove Period Zero Values – Clears 0 values from selected period tables. Commonly not enabled. This adds to the run time. It can also remove zero entries that are genuine entries, e.g. zero headcount.
- Rebuild Structure Tables – Recalculates ownerships and rebuilds structures to give quicker access to data
- Rebuild Indexes – Rebuilds data indexes between database tables. This should then provide faster access to data, as well as reporting accuracy, particularly after structural change workings.
- Analyze Schema – This is used for Oracle database only so typically will be greyed out from usage of SQL.
To schedule the optimise, select ‘Schedule at’, choose the frequency, date and time and confirm by clicking the green ‘tick’ button. Select the ‘Run’ button.
Note:
The optimise will only run if all users are logged out at the scheduled date and time, including the user running the optimise process. To check the status of the optimise, select the ‘Maintain / Batch Queue’ menu when you expect the process to have completed and you can log back in. If run successfully, the ‘Status’ will show as ‘Finished’, if the ‘Status’ shows as ‘Finished with Error’ the optimise has not run. To find out why the optimise did not run, click on the ‘Batch Id’ and select the ‘Show details for the selected batch job’ button in the vertical tool bar to the right of the screen. Typically, an optimise may not run when scheduled because a user is logged in to the Controller database.
Also note that you maybe presented with a message stating “User XXXX is already logged in to this database. Do you want to start a new session for this user?” This means that the database optimise is still running and the application needs to remain in single user mode against the user who executed the optimisation request. Please select “no” to exit. Please do not select “yes” to regain access.
Lastly, please be considerate of database size when running this database optimise activity, particularly when re-indexing, as noted above. This consideration particularly applies to customers with “large” databases that take over 1 hour to optimise, say databases of around 200GB and above. For on-premise customers, you should consult with your IT department, it may be more appropriate for the SQL admin user to run this activity direct against the SQL server. For IBM SaaS customers, you need to be cautious of timeout settings. The IBM SaaS application will timeout after 1 hour, thus causing a conflict of access should the optimisation process still be running. To therefore avoid this conflict, please ensure you always schedule your database optimisation, as detailed above. If you require immediate action, simply schedule the optimisation forward by one minute of the present.