DB2 10.5存储过程执行分区操作报错（SQLSTATE 55007/22004）

问题拆解与根源分析 #

你遇到的两个错误其实对应着不同的代码逻辑问题，结合DB2 10.5的特性来看：

• SQLSTATE 55007：对象被同一应用进程占用——核心原因是你用了WITH HOLD属性的游标，这个游标在事务提交后仍会保持打开状态，持续持有对SBLDGR_DET表的锁，导致后续的ALTER TABLE（DDL操作）无法修改表结构。
• SQLSTATE 22004：不允许空值的上下文指定了空值——大概率是解析分区的HIGHVALUE时出错：如果HIGHVALUE格式不符合预期（比如没有逗号分隔），LOCATE函数找不到逗号，后续的SUBSTR会返回空值，最终导致拼接ALTER TABLE ADD PARTITION语句时出现非法空值。

针对性修复方案 #

1. 解除游标锁占用问题 #

把待处理的分区信息预先写入临时表，关闭游标后再循环临时表执行操作，彻底释放原表的锁：

• 先通过游标查询所有需要处理的分区，将数据插入临时表
• 关闭游标后，从临时表读取分区信息执行DETACH/DROP/ADD操作，避免游标持续占用原表资源

2. 修复空值解析问题 #

• 对HIGHVALUE的解析增加容错逻辑，避免因格式不符返回空值
• 在拼接DDL语句前，强制检查LOWVALUE和HIGHVALUE的非空性，必要时抛出错误日志

修改后的完整存储过程代码 #

DROP SPECIFIC PROCEDURE OSLD02.OSL_CLNUP_MNTHLY_SBLDGR_DET ;
CREATE PROCEDURE OSLD02.OSL_CLNUP_MNTHLY_SBLDGR_DET(IN IN_ID_BUS_PROCSS SMALLINT, IN IN_ID_RUN SMALLINT, IN IN_DT_EOP DATE, IN IN_BATCH_SIZE INTEGER)
SPECIFIC OSLD02.OSL_CLNUP_MNTHLY_SBLDGR_DET
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN
    --------------------------------------------------------------------------
    -------------------- Declarations
    --------------------------------------------------------------------------
    declare v_id_task_log integer;
    declare v_cnt_deleted integer;
    declare v_cnt_initial integer;
    declare v_count integer;
    declare v_cd_acctg_purp character(10);
    declare v_month_eop integer;
    declare SQLCODE int default 0;
    declare v_sqlcode int;
    declare v_partitionName character(20);
    declare v_lowValue character(10);
    declare v_highValue character(10);
    declare v_detachStmt varchar(512);
    declare v_dropTable varchar(512);
    declare SQL_STMT varchar(512);
    declare v_exist int;
    declare v_stagingSchemaTable varchar(512);
    DECLARE v_outmessage VARCHAR(32672);
    DECLARE v_outstatus integer default 0;
    DECLARE v_seconds INTEGER default 300;
    declare wait_until timestamp;
    declare detach_complete_flg character(2) default 'N';
    declare loop_limit int default 0;
    
    -- 定义临时表存储待处理分区信息
    declare global temporary table session.detail_tmp (
        partition_name character(20),
        low_value character(10),
        high_value character(10)
    ) on commit preserve rows with replace not logged;

    BEGIN
        -- 第一步：批量读取待处理分区到临时表，避免游标锁原表
        DECLARE C1 CURSOR FOR
        select DATAPARTITIONNAME, LOWVALUE, HIGHVALUE
        from syscat.DATAPARTITIONS
        where tabname='SBLDGR_DET'
        and (
            -- 容错处理：当HIGHVALUE没有逗号时返回空字符串，避免SUBSTR报错
            CASE WHEN LOCATE(',', HIGHVALUE) > 0 THEN SUBSTR(HIGHVALUE, 2, LOCATE(',', HIGHVALUE)-3) ELSE '' END,
            CASE WHEN LOCATE(',', HIGHVALUE) > 0 THEN SUBSTR(HIGHVALUE, LOCATE(',', HIGHVALUE)+1) ELSE '' END
        ) in (
            select CD_ACCTG_PURP, mo_eop
            from SBLDGR_DET
            where mo_eop < cast(month(IN_DT_EOP) as char(2))
            group by CD_ACCTG_PURP, mo_eop
            having count(*)>0
        ) with ur;

        OPEN C1;
        L0: LOOP
            FETCH C1 INTO v_partitionName, v_lowValue, v_highValue;
            IF SQLCODE <> 0 THEN LEAVE L0; END IF;
            -- 插入前校验非空，过滤无效分区
            IF v_partitionName IS NOT NULL AND v_lowValue IS NOT NULL AND v_highValue IS NOT NULL THEN
                INSERT INTO session.detail_tmp (partition_name, low_value, high_value)
                VALUES (v_partitionName, v_lowValue, v_highValue);
            END IF;
        END LOOP L0;
        CLOSE C1; -- 关闭游标，释放原表锁

        -- 第二步：循环临时表执行分区操作
        DECLARE C2 CURSOR FOR
        SELECT partition_name, low_value, high_value FROM session.detail_tmp;
        
        OPEN C2;
        L1: LOOP
            FETCH C2 INTO v_partitionName, v_lowValue, v_highValue;
            IF SQLCODE <> 0 THEN LEAVE L1; END IF;

            -- 执行分离分区
            set v_detachStmt = 'ALTER TABLE SBLDGR_DET DETACH PARTITION ' || v_partitionName || ' INTO TABLE OSLSTG.SL_DET_' || v_partitionName;
            EXECUTE IMMEDIATE v_detachStmt ;
            commit;

            -- 优化等待逻辑：缩短间隔+限制循环次数，避免资源浪费
            set detach_complete_flg = 'N';
            set loop_limit = 0;
            while (loop_limit < 100 AND detach_complete_flg = 'N') DO
                call dbms_alert.sleep(10);
                IF EXISTS (
                    SELECT 1 FROM SYSCAT.DATAPARTITIONS 
                    WHERE TABSCHEMA='OSLSTG' AND TABNAME='SL_DET_' || v_partitionName AND STATUS=' '
                ) THEN
                    set detach_complete_flg = 'Y';
                END IF;
                set loop_limit = loop_limit + 1;
            end while;

            -- 删除临时表
            set v_dropTable = 'drop table OSLSTG.SL_DET_' || v_partitionName;
            EXECUTE IMMEDIATE v_dropTable ;
            commit;

            -- 重新添加分区，再次校验非空
            IF v_lowValue IS NOT NULL AND v_highValue IS NOT NULL THEN
                SET SQL_STMT = 'ALTER TABLE sbldgr_det ADD PARTITION ' || v_partitionName || ' STARTING FROM (' || v_lowValue || ' ) ENDING AT (' || v_highValue || ')';
                EXECUTE IMMEDIATE SQL_STMT;
                commit;
            ELSE
                -- 可选：记录错误日志到自定义日志表
                SET v_outmessage = 'Invalid partition values for ' || v_partitionName || ': LOWVALUE=' || COALESCE(v_lowValue, 'NULL') || ', HIGHVALUE=' || COALESCE(v_highValue, 'NULL');
                SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = v_outmessage;
            END IF;

        END LOOP L1;
        CLOSE C2;

    END ;
END ;

额外优化说明 #

• 移除了游标WITH HOLD属性，避免不必要的长期锁持有
• 优化了分区分离的等待逻辑，缩短检查间隔并限制最大循环次数，防止无限等待
• 增加了多层空值校验，从数据读取到DDL拼接全流程避免空值问题
• 对HIGHVALUE解析增加容错处理，适配格式异常的情况

内容的提问来源于stack exchange，提问作者StarFish