Optimize the Database

Optimizing your database gives you faster access to data. To use the advanced functions Rebuild Indexes and Analyze Schema, you must have administrative rights.

You can use this function to make the following database changes:

  • Remove Period Zero Values - clears the value 0 from selected period tables.
  • Rebuild Structure Tables - which gives quicker access to data.
  • Rebuild Indexes (advanced option) - Rebuilds all indexes in the database to enhance performance and give quicker access to data.
  • Analyze Schema (advanced option) - Reanalyzes the schema for Oracle database.

You can run the optimization immediately or create a scheduled job to run the optimization at another time.

The time it takes to run the optimization depends on the size of the databases and the capacity of your computer. Rebuild indexes can take a long time.

Before you begin

To run the optimization immediately, the system must be in single user mode. Go to Maintain > User > Single Mode and set the system to single mode.

To schedule a job to run the optimization later or at regular intervals, the system must be in batch mode. For more information about batch mode, see the BATCHMODE parameter at Parameter List.

Procedure

  1. Click Maintain > Database > Optimize. The Optimize Database window opens.
  2. Select the Remove Period Zero Values check box to delete all data fields containing the amount zero. The list box displays the years in the period, for which all zero values will be deleted.
  3. Select the period tables you want to remove zero values from. To select one period, click the period. To select several periods, press the CTRL key and click the next period. To select a range of periods, click the first period and then press the SHIFT key and click the last period in the range. Click Select All to select all periods or click Deselect All to deselect all periods.
  4. Select the Rebuild Structure Tables check box to rebuild the structure tables.
  5. To schedule a job to run the optimization at another time or at regular intervals, do the following steps:
    1. Select the Scheduled at check box and click the two dots icon.
      Note: If you don't see the Scheduled at check box, you have not enabled batch mode. See the Before you begin section for more information.
    2. In the Schedule Job window, choose the interval that you want the job to run at. Use the calendar to choose a specific date.
    3. Choose a time in the time field.
    4. If you want this job to run after an existing scheduled job, click the 3 dots icon beside Dependent by, select a job in the View Batch Queue window, and click OK.
      The Batch ID that you selected appears in the Dependent by field.
    5. Click OK to save your changes.
  6. Click Run.
    If you created a scheduled job to run the optimization, a message displays showing the Batch Id of the job.

Results

If you did not create a scheduled job, the optimization process runs immediately. During the optimization process information about the processing is shown on the status row. You are notified when the optimization is complete.

If you created a scheduled job to run the optimization, you can check that the job appears in the batch queue by going to Maintain > Batch Queue > View > .
Important: When the scheduled job runs, there must not be any users logged in to Cognos Controller. If users are logged in to Cognos Controller when the schedule job runs, it ends in error.