DBE_ILM

Interface Description

Implements ILM policies, ILM policy evaluation, and interface for stopping compression jobs.

**Table 1** DBE_ILM
Interface	Description
EXECUTE_ILM	Evaluates the specified data and ILM policy based on parameters. If the evaluation is passed, the corresponding compression job is generated.
STOP_ILM	Stops a compression job that is being executed based on parameters.

DBE_ILM.EXECUTE_ILM

Evaluates the ILM policy based on parameters. The prototype is as follows:

      
           DBE_ILM.EXECUTE_ILM (
schema_name         IN     VARCHAR2,
object_name         IN     VARCHAR2,
task_id             OUT    Oid,
subobject_name      IN     VARCHAR2 DEFAULT NULL,
policy_name         IN     VARCHAR2 DEFAULT ILM_ALL_POLICIES, 
execution_mode      IN     NUMBER DEFAULT ILM_EXECUTION_ONLINE);

**Table 2** DBE_ILM.EXECUTE_ILM parameters
Parameter	Description
schema_name	Schema to which an object belongs.
object_name	Object name.
task_id	Descriptor ID of the generated ADO task.
subobject_name	Name of a data subobject.
policy_name	Policy name. You can query the GS_ADM_ILMOBJECTS view to obtain the policy name. The default value DBE_ILM.ILM_ALL_POLICIES indicates all policies on the object.
execution_mode	Execution mode. The online mode (ILM_EXECUTION_ONLINE) or offline mode (ILM_EXECUTION_OFFLINE) is not involved in this phase.

DBE_ILM.STOP_ILM

Stops an ILM policy that is being executed based on parameters. The prototype is as follows:

       
            DBE_ILM.STOP_ILM (
TASK_ID                IN NUMBER DEFAULT -1,
P_DROP_RUNNING_JOBS     IN BOOLEAN DEFAULT FALSE,
P_JOBNAME         IN VARCHAR2 DEFAULT NULL);

**Table 3** DBE_ILM.STOP_ILM parameters
Parameter	Description
TASK_ID	Descriptor ID of an ADO task.
P_DROP_RUNNING_JOBS	Determines whether to stop a task that is being executed. The value true indicates that the task is forcibly stopped, and the value false indicates that the task is not stopped.
P_JOBNAME	Task name.

When there are a large number of concurrent requests and you run DBE_ILM.STOP_ILM, the system may display the message "Resources are busy, please try again later." In this case, try again later.

Examples

    
     
       
       
         gaussdb=# ALTER DATABASE set ilm = on;
gaussdb=# CREATE Schema ILM_DATA;
gaussdb=# SET current_schema=ILM_DATA;
BEGIN
    DBE_ILM_ADMIN.DISABLE_ILM();
    DBE_ILM_ADMIN.CUSTOMIZE_ILM(1, 15);
    DBE_ILM_ADMIN.CUSTOMIZE_ILM(2, 30);
    DBE_ILM_ADMIN.CUSTOMIZE_ILM(11, 1);
    DBE_ILM_ADMIN.CUSTOMIZE_ILM(12, 10);
    DBE_ILM_ADMIN.CUSTOMIZE_ILM(13, 1024);
    DBE_ILM_ADMIN.CUSTOMIZE_ILM(14, 240);
    DBE_ILM_ADMIN.ENABLE_ILM();
END;
/
-- 1.1.2 prepare test data
gaussdb=# CREATE SEQUENCE ILM_DATA.ORDER_TABLE_SE_ORDER_ID MINVALUE 1;
gaussdb=# CREATE OR REPLACE PROCEDURE ILM_DATA.ORDER_TABLE_CREATE_DATA(NUM INTEGER) IS
BEGIN
    FOR X IN 1..NUM
        LOOP
            INSERT INTO ORDER_TABLE VALUES(ORDER_TABLE_SE_ORDER_ID.nextval, 'Snack gift basket A', NOW());
        END LOOP;
    COMMIT;
END;
/
-- 1.1.3 normal procedure
-- 1.1.3.1 evaluate successed - all policy
gaussdb=# CREATE TABLE ILM_DATA.ORDER_TABLE (ORDER_ID INT, GOODS_NAME TEXT, CREATE_TIME TIMESTAMP)
    WITH (STORAGE_TYPE=ASTORE) ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;
BEGIN
    ILM_DATA.ORDER_TABLE_CREATE_DATA(5);
    PERFORM PG_SLEEP(2);
END;
/

gaussdb=# SELECT ORDER_ID, DBE_COMPRESSION.GET_COMPRESSION_TYPE('ilm_data', 'order_table', ctid::text, NULL) FROM ILM_DATA.ORDER_TABLE;
 order_id | get_compression_type 
----------+----------------------
        1 |                    1
        2 |                    1
        3 |                    1
        4 |                    1
        5 |                    1
(5 rows)

gaussdb=# SELECT ORDER_ID, DBE_HEAT_MAP.ROW_HEAT_MAP('ilm_data','order_table', NULL, ctid::text) FROM ILM_DATA.ORDER_TABLE;
 order_id |                 row_heat_map                  
----------+-----------------------------------------------
        1 | (ilm_data,order_table,,,16799,16799,"(0,1)",)
        2 | (ilm_data,order_table,,,16799,16799,"(0,2)",)
        3 | (ilm_data,order_table,,,16799,16799,"(0,3)",)
        4 | (ilm_data,order_table,,,16799,16799,"(0,4)",)
        5 | (ilm_data,order_table,,,16799,16799,"(0,5)",)
(5 rows)

DECLARE
    v_taskid number;
BEGIN
    DBE_ILM.EXECUTE_ILM(OWNER        => 'ilm_data',
                        OBJECT_NAME    => 'order_table',
                        TASK_ID        => v_taskid,
                        SUBOBJECT_NAME => NULL,
                        POLICY_NAME    => 'ALL POLICIES',
                        EXECUTION_MODE => 2);
    RAISE INFO 'Task ID is:%', v_taskid;
END;
/
INFO:  Task ID is:1

gaussdb=# SELECT ORDER_ID, DBE_COMPRESSION.GET_COMPRESSION_TYPE('ilm_data', 'order_table', ctid::text, NULL) FROM ILM_DATA.ORDER_TABLE;
 order_id | get_compression_type 
----------+----------------------
        1 |                    1
        2 |                    1
        3 |                    1
        4 |                    1
        5 |                    1
(5 rows)

gaussdb=# CALL DBE_ILM.STOP_ILM(-1, true, NULL);
 stop_ilm 
----------

(1 row)

        

      

    
   