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.

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.

To reduce the retention period:

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

The following example sets the retention period to 25 days:

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

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);