数据跑批-数据分区的设计

已于 2024-08-27 16:23:43 修改
阅读量283 收藏 3
点赞数 5
文章标签： 数据库 etl工程师 数据库开发
于 2024-08-27 16:02:10 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45225089/article/details/141605019
版权

DB2重跑数据-数据分区的设计

1.背景

系统重新跑批时会导致当天的数据重复，单表数据量约2G，故重跑数据时需要先删除当天的数据，再重新跑当天的数，然后加载在业务表中。

2.思路

我们可以根据数据日期对业务表进行数据分区，每天跑数的时候先删除分区（即删除当天的数据），然后重新给业务表添加分区，然后再跑当天的数据。

3.实现

删除数据分区 - IBM 文档

在这里插入图片描述

单个业务表每天数据量最大会有2G左右，数据量较大，故而先把数据分区分离到目标表中进行删除。

  1. 把业务表的分区拆到目标表，
  2. 删掉目标表。
  3. 给业务表重新添加分区。

3.代码（存储过程中的实现）

添加分区

CREATE OR REPLACE PROCEDURE "REPORT"."PROC_ADD_TABLE_PARTITION" (
    IN "P_AS_OF_DATE"	DATE,
    IN "P_SCHEMA"	VARCHAR(20),
    IN "P_TABLE_NAME"	VARCHAR(80) )

/*=====================================================================+
  函数名称：PROC_ADD_TABLE_PARTITION
  参    数:
			输入参数 P_AS_OF_DATE 数据日期
          	输入参数 P_SCHEMA SCHEMA名称
          	输入参数 P_TABLE_NAME 表名
  返 回 值：
  功能描述：添加分区,如果分区存在则删除分区重新创建
  目 标 表：
  源    表：
  编写人员：
  编写日期： 
  修改历史：
  修改日期：
  修改人员：
  修改原因：
-----------------------------------------------------------------------
-----------------------------------------------------------------------
+=====================================================================*/
SPECIFIC SQL220830152116953
BEGIN
		/*************************开始定义变量*********/       
		DECLARE V_SQL VARCHAR(200);--sql
		DECLARE SQLERRM VARCHAR(2000); --SQL错误信息
		DECLARE V_COUNT INTEGER; --计数
		DECLARE V_DPT_PID INTEGER DEFAULT -1;       -- 该分区的dpid
		DECLARE V_COUNT_TEMP INTEGER; --计数临时表
		DECLARE V_TEMP_TABLE VARCHAR(80); --临时表
		DECLARE V_PARTITION VARCHAR(15); --分区名称
		DECLARE V_DATE VARCHAR(10);--字符串日期
		DECLARE V_TIMESTAMP TIMESTAMP; --时间
		DECLARE RET_FLG INTEGER DEFAULT 0; --
		DECLARE RET_MSG VARCHAR(1024) DEFAULT ''; --
		/*************************结束变量定义**********/   
		--异常处理                                                                  		
      DECLARE EXIT HANDLER FOR SQLEXCEPTION                                   		
      BEGIN  
			 GET DIAGNOSTICS EXCEPTION 1 SQLERRM = MESSAGE_TEXT; -- 取得错误信息
			 ROLLBACK;-- 回滚
       CALL REPORT.PROC_ETL_LOG(P_AS_OF_DATE, 'PROC_ADD_TABLE_PARTITION-'||UPPER(P_TABLE_NAME), UPPER(P_TABLE_NAME)||'添加分区', 0, 0, -1, 1, SQLERRM); --  记录日志                                                           
      END;--
		/*初始化变量--开始*/
		SET V_TEMP_TABLE = 'TEMP'||UPPER(P_TABLE_NAME);--
		SET V_PARTITION = 'P'||TO_CHAR(P_AS_OF_DATE,'YYYYMMDD');--
		SET V_DATE = TO_CHAR(P_AS_OF_DATE,'YYYY-MM-DD');--
		/*初始化变量--结束*/
		--开始日志
		CALL REPORT.PROC_ETL_LOG(P_AS_OF_DATE, 'PROC_ADD_TABLE_PARTITION-'||UPPER(P_TABLE_NAME), UPPER(P_TABLE_NAME)||'添加分区', 0, 0, 0, '', ''); --  记录日志   
		
		--查看临时表是否存在，如果存在需要先删除临时表，否则会报错
		SELECT COUNT(*) INTO V_COUNT_TEMP FROM SYSCAT.TABLES B WHERE B.TABNAME = V_TEMP_TABLE AND B.TABSCHEMA = UPPER(P_SCHEMA);--
		--V_COUNT_TEMP>0临时表存在
		IF V_COUNT_TEMP > 0 THEN 
			 --删除临时表
			 EXECUTE IMMEDIATE 'DROP TABLE '||UPPER(P_SCHEMA)||'.'||V_TEMP_TABLE;--
			 COMMIT; --
    END IF;--
		
		--查看分区是否存在
	  SELECT COUNT(*) INTO V_COUNT FROM SYSCAT.DATAPARTITIONS A WHERE A.TABNAME =UPPER(P_TABLE_NAME) AND A.TABSCHEMA =UPPER(P_SCHEMA) AND A.DATAPARTITIONNAME = V_PARTITION;--
		--V_COUNT>0分区存在
    IF V_COUNT > 0 THEN
		 
		  SELECT  DATAPARTITIONID INTO V_DPT_PID 
          FROM SYSCAT.DATAPARTITIONS 
         WHERE TABSCHEMA =UPPER(P_SCHEMA) 
	         AND TABNAME = UPPER(P_TABLE_NAME) 
	         AND DATAPARTITIONNAME=V_PARTITION;--
			 --删除分区，将分区移入临时表
			 EXECUTE IMMEDIATE 'ALTER TABLE '||UPPER(P_SCHEMA)||'.'||UPPER(P_TABLE_NAME)||' DETACH PARTITION '||V_PARTITION||' INTO '||UPPER(P_SCHEMA)||'.'||V_TEMP_TABLE;--
			 COMMIT;--
			 --分区移入临时表有一定延迟，此处等待一会后删除临时表
			 
			/* --记录当前时间
			 SET V_TIMESTAMP = CURRENT TIMESTAMP;--
			 --循环等待，根据时间判断，等待5秒
			 WHILE (TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - V_TIMESTAMP))) <=10 DO
	
		   END WHILE; */
			 
			 CALL REPORT.SP_PRC_WAITDETACH(UPPER(P_SCHEMA),UPPER(P_TABLE_NAME),V_DPT_PID,RET_FLG,RET_MSG); --
			 
			 --删除临时表，完成删除分区
			 EXECUTE IMMEDIATE 'DROP TABLE '||UPPER(P_SCHEMA)||'.'||V_TEMP_TABLE;--
			 COMMIT; --
			 
    END IF;--
		--创建分区
		EXECUTE IMMEDIATE 'ALTER TABLE '||UPPER(P_SCHEMA)||'.'||UPPER(P_TABLE_NAME)||' ADD PARTITION '||V_PARTITION||' STARTING('''||V_DATE||''') ENDING('''||V_DATE||''')' ;--
		COMMIT;--
		--结束日志
		CALL REPORT.PROC_ETL_LOG(P_AS_OF_DATE, 'PROC_ADD_TABLE_PARTITION-'||UPPER(P_TABLE_NAME), UPPER(P_TABLE_NAME)||'添加分区', 0, 0, 1, '0', '执行成功'); --  记录日志
		
END

检查分区分离

CREATE OR REPLACE PROCEDURE "REPORT"."SP_PRC_WAITDETACH" (
    IN I_SCMNAM	VARCHAR(128),
    IN I_TABNAM	VARCHAR(128),
    IN I_PATID	INTEGER DEFAULT -1,
    OUT O_RTCD	INTEGER,
    OUT O_SYSMSG	VARCHAR(128) )


SPECIFIC SQL220830151953749
BEGIN
 
 DECLARE V_DPID int;--
  DECLARE V_DPSTS char;--
  DECLARE DONE boolean DEFAULT FALSE;--
  DECLARE V_CURCLOSE boolean DEFAULT FALSE;--
  DECLARE oldLockTimeout INTEGER;--
  
  DECLARE allDetachCheck CURSOR FOR
      select DATAPARTITIONID, STATUS FROM syscat.datapartitions
        where tabschema = I_SCMNAM and tabname = I_TABNAM and
        (status = 'L' OR status = 'D') WITH CS;--

  DECLARE oneDetachCheck CURSOR FOR
      select DATAPARTITIONID, STATUS FROM syscat.datapartitions
        where tabschema = I_SCMNAM and tabname = I_TABNAM and
        DATAPARTITIONID = I_PATID WITH CS;--

  -- Add signal handlers FOR errors that you consider non-fatal here
  -- example FOR no row found and lock timeout shown below
  DECLARE continue handler FOR NOT FOUND
  BEGIN
    -- If no record was found there is nothing to wait FOR, so we are DONE
    SET DONE = TRUE;--
  END;--

  DECLARE continue handler FOR SQLSTATE '40001'
  BEGIN
    -- on a lock timeout we are not DONE, we want to loop again
    SET DONE = FALSE;--
    -- the CURSOR will be CLOSEd as part of the rollback during the lock timeout
    SET V_CURCLOSE = TRUE;--
  END;--

  -- initialize return code to 0
  SET O_RTCD = 0;--

  -- save CURRENT lock timeout value
  values CURRENT lock timeout INTO oldLockTimeout;--
  SET CURRENT lock timeout -1;--

  -- if table does not exist in syscat.datapartitions, return error
  IF NOT EXISTS(select DATAPARTITIONID FROM syscat.datapartitions
        where tabschema = I_SCMNAM and tabname = I_TABNAM
        WITH UR)  
  THEN
  --IF tableExists (I_SCMNAM, I_TABNAM) = 0
    SET O_SYSMSG = 'Table ' || I_SCMNAM || '.' || I_TABNAM || ' not found';--
    SET O_RTCD = -1;--
    GOTO EXIT;--
  END IF;--

  WHILE DONE = FALSE DO
    SET V_CURCLOSE = FALSE;--

    IF I_PATID <> -1
    THEN
      OPEN oneDetachCheck;--
      FETCH oneDetachCheck INTO V_DPID, V_DPSTS;--

      -- two cases here:
      --  (i) detach has already completed hence partition entry not
      --      found in catalogs (indicated by DONE = TRUE, handled later)
      -- (ii) detach in progress, partition state should not be visible
      IF DONE <> TRUE AND (V_DPSTS = '' OR V_DPSTS = 'A')
      THEN
        SET O_SYSMSG = 'Cannot SP_PRC_WAITDETACH if DETACH was not issued on ' ||
                  'DATAPARTITIONID ' || I_PATID;--
        SET O_RTCD = -1;--
        GOTO EXIT;--
      END IF;--

      IF V_CURCLOSE <> TRUE
      THEN
        CLOSE oneDetachCheck;--
      END IF;--
    ELSE
      OPEN allDetachCheck;--
      FETCH allDetachCheck INTO V_DPID, V_DPSTS;--
      IF V_CURCLOSE <> TRUE
      THEN
        CLOSE allDetachCheck;--
      END IF;--
    END IF;--

  END WHILE;--

  IF I_PATID <> -1
  THEN
    SET O_SYSMSG = 'DETACH completed on DATAPARTITIONID ' || I_PATID;--
  ELSE
    SET O_SYSMSG = 'All detach operations completed on table ' ||
               I_SCMNAM || '.' || I_TABNAM;--
  END IF;--

EXIT:
  -- restore lock timeout value
  SET CURRENT lock timeout oldLockTimeout;--

  RETURN O_RTCD;--
	
END

4.说明

  • 为了使 ALTER TABLE…DETACH 在 DB2® 版本 9.7 FP 1 及更高发行版中尽可能快地执行，异步分区拆离任务以异步方式完成拆离操作。故在删除目标表前需要调用SP_PRC_WAITDETACH检查数据分区分离状态。
  • SP_PRC_WAITDETACH中为防止锁超时导致的操作失败，timeout loke设为不会超时，操作完成后再恢复。
不过而而
关注
  • 5
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值