DBE_ILM

API Description

Implements ILM policies and APIs for manually triggering and stopping ADO tasks.

• DBE_ILM.EXECUTE_ILM

  Evaluates the specified data and ILM policy based on parameters. If the evaluation is passed, the corresponding compression job is generated. The prototype is as follows:

  1 2 3 4 5 6 7

  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 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. This parameter is not supported in the current version and is reserved.

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

  1 2 3 4

  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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

gaussdb=# CREATE DATABASE ilmtabledb with dbcompatibility = 'ORA';
gaussdb=# \c ilmtabledb
gaussdb=# ALTER DATABASE set ilm = on;
gaussdb=# CREATE Schema ILM_DATA;
gaussdb=# SET current_schema=ILM_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;
/
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;
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'order_id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
BEGIN
    ILM_DATA.ORDER_TABLE_CREATE_DATA(100);
    PERFORM PG_SLEEP(2);
END;
/
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
SELECT * FROM pg_sleep(3);
 pg_sleep 
----------

(1 row)

gaussdb=# EXECUTE DIRECT ON DATANODES 'SELECT A.DBNAME, A.JOB_STATUS, A.ENABLE, A.FAILURE_MSG FROM PG_JOB A WHERE A.DBNAME = ''ilmtabledb'' AND A.JOB_NAME LIKE ''ilmjob$_%'' ORDER BY A.JOB_NAME DESC LIMIT 1';
  dbname  | job_status | enable | failure_msg 
----------+------------+--------+-------------
 dbeilmdb | s          | f      | 
 dbeilmdb | s          | f      | 
 dbeilmdb | s          | f      | 
 dbeilmdb | s          | f      | 
 dbeilmdb | s          | f      | 
 dbeilmdb | s          | f      | 
(6 rows)

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

(1 row)
-- In the distributed system, no error is reported when a parameter is abnormal. Instead, an empty value is returned.