Controlling the size of the command_history table

You can reduce the retention period or remove rows from the command_history table to prevent it from becoming too large.

Before you begin

You must be connected to the sysadmin database as user informix or another authorized user.

By default, rows in the command_history table are automatically removed after a 30 days. The retention period is controlled by the COMMAND HISTORY RETENTION row in the ph_threshold table.

About this task

To reduce the retention period:

Procedure

Use an UPDATE statement to modify the value of the COMMAND HISTORY RETENTION row in the ph_threshold table.

Example

The following example sets the retention period to 25 days:

UPDATE ph_threshold
SET value = "25"
WHERE name = "COMMAND HISTORY RETENTION";

What to do next

You can use SQL commands like DELETE or TRUNCATE TABLE to manually remove data from this table. You can also create a task in the ph_task table to purge data from the command_history table.

The following example shows a task that monitors the amount of data in the command_history table and purges data when it becomes too old.

INSERT INTO ph_task
( tk_name, tk_type, tk_group, tk_description, tk_execute,
tk_start_time, tk_stop_time, tk_frequency )
VALUES
("mon_command_history",
"TASK",
"TABLES",
"Monitor how much data is kept in the command history table",
"delete from command_history where cmd_exec_time < (
        select current - value::INTERVAL DAY to SECOND
        from ph_threshold
        where name = 'COMMAND HISTORY RETENTION' ) ",
DATETIME(02:00:00) HOUR TO SECOND,
NULL,
INTERVAL ( 1 ) DAY TO DAY);