Optimize the Database

Optimizing your database gives you faster access to data. To use the advanced functions, 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.
  • Condense xacclock table (advanced option) - Allows you to condense data upto the period that is selected.
    • Up to and including Period (advanced option) - Lists all the available period values that can be selected as the upper boundary for condensing the data.

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

The time that 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.

Note: For optimal performance, IBM recommends executing optimization tasks using Batch mode. The use of Interactive mode is not recommended.

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 checkbox to delete all the data fields that contains 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 that 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 checkbox to rebuild the structure tables.
  5. To condense the data in the xacclock (period locking) table, do the following steps:
    1. In the Optimize Database window, select the Condense xacclock table checkbox.
      The Up to and including Period dropdown option is enabled.
    2. From the Up to and including Period dropdown, select the wanted period.
    3. For On-demand execution, click Run. For Batch mode execution, continue to Step 6.
  6. To schedule a job to run the optimization at another time or at regular intervals, do the following steps:
    1. Select the Scheduled at checkbox and click the two dots icon.
      Note: If you don't see the Scheduled at checkbox, 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 next to 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.
  7. 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 who are logged in to Controller. If users are logged in to Controller when the schedule job runs, it ends in error.