Scheduling data optimization

You can configure the automatic compressing, shrinking, repacking, and defragmenting of all tables and extents by enabling the auto_crsd Scheduler task.

You can enable and configure the auto_crsd task by updating Scheduler tables in the sysadmin database.

To enable the auto_crsd task by updating the Scheduler tables:

  1. Connect to the sysadmin database as user informix or another authorized user.
  2. Enable the auto_crsd Scheduler task by using an UPDATE statement on the ph_task table to set the value of the tk_enable column to T. For example, the following statement enables the auto_crsd task:
    UPDATE ph_task
      SET tk_enable = 'T'
      WHERE tk_name = 'auto_crsd';
  3. Optional: Change the frequency of when the task is run by running an UPDATE statement on the ph_task table to change the value of the tk_frequency column. The default value is 7 00:00:00, which indicates that the task runs once a week. For example, the following statement changes the frequency to once a day:
    UPDATE ph_task
      SET tk_frequency = '1 00:00:00'
      WHERE tk_name = 'auto_crsd';
  4. Optional: Disable individual operations by using an UPDATE statement on the ph_threshold table to set the value column for a threshold to F:
    • AUTOCOMPRESS_ENABLED: controls compression
    • AUTOREPACK_ENABLED: controls repacking
    • AUTOSHRINK_ENABLED: controls shrinking
    • AUTODEFRAG_ENABLED: controls defragmenting
    For example, the following statement disables just the defragmentation operation of the auto_crsd task:
    UPDATE ph_threshold
      SET value = 'F'
      WHERE name = 'AUTODEFRAG_ENABLED';
  5. Optional: Change the thresholds of individual operations by using and UPDATE statement on the ph_threshold table to change the value of the value column for a threshold:
    • AUTOCOMPRESS_ROWS: The threshold for compression is the number of uncompressed rows. The default threshold is 50 000 rows. A table is compressed when the number of uncompressed rows exceeds 50 000.
    • AUTOREPACK_SPACE: The threshold for repacking a table is the percentage of noncontiguous space. The default is 90%. A table is repacked when more than 90% of the space the table occupies is noncontiguous.
    • AUTOSHRINK_UNUSED: The threshold for shrinking a table or fragment is the percentage of unused, allocated space. The default is 50%. A table or fragment is shrunk when more than 50% of the allocated space is unused.
    • AUTODEFRAG_EXTENTS: The threshold for defragmenting table or fragment extents is the number of extents. The default is 100. A table or fragment is defragmented when the number of extents exceeds 100.
    For example, the following statement changes the compression threshold to 5000 rows:
    UPDATE ph_threshold
      SET value = '5000'
      WHERE name = 'AUTOCOMPRESS_ROWS';
When a threshold for an operation that you enabled is exceeded, the Scheduler runs the operation.