今天去现场迁移数据，迁移完成后要设置作业的时候因为客户用的是DM7，之前很少接触过，所以在设置定时删除备份时无法找到相关的函数。问了之后才知道原来DM7的定时删除远比DM8复杂，在此记录一下：

--定时删除

create or replace function SF_DEL_DB_BAK_EXPIRED(

DBNAME varchar(128),

BAKDIR varchar(256),

PARALLEL_DIR varchar(256),

EXPIRED_DAY int )

return INT as

DECLARE

V_CNT INT;

V_I INT;

V_PATH VARCHAR(256);

V_TIME INT;

V_FLAG INT;

V_NUM INT;

 

TYPE T_REC IS

RECORD (

ID INT,

NAME VARCHAR(128),

TIME VARCHAR(128),

PATH VARCHAR(256));

TYPE T_REC_ARR

IS

ARRAY T_REC[];

BAK_INFO T_REC_ARR;

 
begin

V_NUM=0;

/*初始化备份链表*/

SF_BAK_LST_INIT();

SF_BAK_LST_SET_N_PATH(1);

SF_BAK_LST_SET_PATH(0, BAKDIR);

SF_BAK_LST_SET_PARALLEL_DIR(PARALLEL_DIR);

SF_BAK_LST_COLLECT(DBNAME, '', 1);

V_CNT = SF_GET_BAK_LST_NUM(DBNAME);

/*判断某库是否存在备份*/

IF V_CNT > 0 THEN

BAK_INFO = NEW T_REC[V_CNT];

PRINT V_CNT;

ELSE RETURN -1;

END IF;

/*指向第一个备份*/

SF_BAK_LST_GET_FIRST();

/*指向某一个备份，获取其中某些信息存入数组BAK_INFO*/

FOR V_I IN 1..V_CNT

LOOP

V_PATH=SF_BAK_GET_CUR_PATH();

IF V_PATH IS NOT NULL THEN

BAK_INFO[V_I].ID =V_I;

BAK_INFO[V_I].NAME = SF_BAK_GET_NAME(V_PATH);

BAK_INFO[V_I].TIME = SF_BAK_GET_TIME(V_PATH);

BAK_INFO[V_I].PATH = V_PATH;

END IF;

/*移向下一个备份*/

SF_BAK_LST_GET_NEXT();

END LOOP;

/*用游标隐式打开从ARRAY BAK_INFO取这些值*/

FOR R IN (SELECT * FROM ARRAY BAK_INFO)

LOOP

IF DAYS_BETWEEN(CURDATE(), R.TIME) >= EXPIRED_DAY THEN

V_FLAG = SF_DEL_BAK(DBNAME,'',R.NAME,1);

END IF;

IF V_FLAG=0 THEN

V_NUM=V_NUM+1;

END IF;

END LOOP;

/*有一个失败，全部失败*/

IF V_NUM >= 1 THEN

RETURN 0;

ELSE RETURN 1;

END IF;

/*销毁备份链表*/

SF_BAK_LST_DEINIT();

end;
 
Call "SF_DEL_DB_BAK_EXPIRED"('DAMENG','/data/dmdbms/data/BAK/','',7);

--这里'DAMENG'指的是数据库名，7代表删除7天之前的备份文件）

 

使用：

--del_bak_before_7days

call SP_CREATE_JOB('del_bak_before_7days',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('del_bak_before_7days');

call SP_ADD_JOB_STEP('del_bak_before_7days', 'del_bak', 0, 'call SF_DEL_DB_BAK_EXPIRED(''DAMENG'',''/data/dmdbms/BAK'','''',7);', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('del_bak_before_7days', 'del_bak', 1, 1, 1, 0, 0, '23:50:00', NULL, '2018-11-04 09:06:18',