DB2 LUW循环执行CHECK/REORG表遇两类报错，求可行解决方案

我编写了一段DB2存储过程代码，用于循环对REORG_PENDING状态为Y的表执行SET INTEGRITY CHECKED和REORG操作，部分功能正常：

BEGIN
    DECLARE SQLSTATE    CHAR(5);
    DECLARE RetState    CHAR(5);
    DECLARE SqlStmt     VARCHAR(1000);
    DECLARE Not_Needed  CONDITION FOR '51027';
    DECLARE CONTINUE HANDLER for Not_Needed
        BEGIN
            SET RetState = SQLSTATE;
        END;
    FOR i AS (SELECT TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA = 'LIBRAT' AND REORG_PENDING = 'Y')
    DO
        SET SqlStmt = 'SET INTEGRITY FOR LIBRAT.'||Trim(TABNAME)||' IMMEDIATE CHECKED';
        EXECUTE IMMEDIATE SqlStmt;
        IF RetState = '51027' THEN
--          SET SqlStmt = 'REORG TABLE LIBRAT.'||Trim(TABNAME);
--          EXECUTE IMMEDIATE SqlStmt;
            CALL SYSPROC.ADMIN_CMD('REORG TABLE LIBRAT.'||Trim(TABNAME));
        END IF;
        SET RetState = NULL;
    END FOR;
END@

遇到两个问题：

1. 直接用EXECUTE IMMEDIATE执行REORG语句时，报错：

Error: An unexpected token "LIBRAT" was found following "REORG TABLE ". Expected tokens may include: "JOIN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.33.31

2. 改用SYSPROC.ADMIN_CMD执行REORG后，虽能完成操作，但FOR循环游标报错：

Error: The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.. SQLCODE=-501, SQLSTATE=24501, DRIVER=4.33.31

推测是ADMIN_CMD自动执行COMMIT导致游标失效。

解决方法1：预存表名到临时表，避免依赖游标 #

游标在事务提交后会自动关闭，先把需要处理的表名一次性查询到临时表存储，再遍历临时表处理：

BEGIN
    DECLARE SQLSTATE    CHAR(5);
    DECLARE RetState    CHAR(5);
    DECLARE SqlStmt     VARCHAR(1000);
    DECLARE v_tabname   VARCHAR(128);
    DECLARE Not_Needed  CONDITION FOR '51027';
    DECLARE CONTINUE HANDLER for Not_Needed
        BEGIN
            SET RetState = SQLSTATE;
        END;
    -- 创建临时表存储待处理表名
    DECLARE GLOBAL TEMPORARY TABLE SESSION.REORG_TABLES
        (TABNAME VARCHAR(128))
        NOT LOGGED;
    -- 插入需要处理的表
    INSERT INTO SESSION.REORG_TABLES
        SELECT TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA = 'LIBRAT' AND REORG_PENDING = 'Y';
    -- 声明游标遍历临时表
    DECLARE cur_reorg CURSOR FOR SELECT TABNAME FROM SESSION.REORG_TABLES;
    OPEN cur_reorg;
    FETCH cur_reorg INTO v_tabname;
    WHILE SQLSTATE = '00000' DO
        SET SqlStmt = 'SET INTEGRITY FOR LIBRAT.'||Trim(v_tabname)||' IMMEDIATE CHECKED';
        EXECUTE IMMEDIATE SqlStmt;
        IF RetState = '51027' THEN
            CALL SYSPROC.ADMIN_CMD('REORG TABLE LIBRAT.'||Trim(v_tabname));
        END IF;
        SET RetState = NULL;
        FETCH cur_reorg INTO v_tabname;
    END WHILE;
    CLOSE cur_reorg;
END@

临时表SESSION.REORG_TABLES的数据不受事务提交影响，即使ADMIN_CMD触发COMMIT，依然可以正常遍历。

解决方法2：用自治事务执行REORG，隔离主事务 #

创建一个带自治事务属性的存储过程执行REORG，自治事务的COMMIT不会影响主事务的游标：

首先创建自治事务存储过程：

CREATE OR REPLACE PROCEDURE REORG_TABLE(p_schema VARCHAR(128), p_tabname VARCHAR(128))
    AUTONOMOUS TRANSACTION
BEGIN
    DECLARE SqlStmt VARCHAR(1000);
    SET SqlStmt = 'REORG TABLE "'||p_schema||'"."'||p_tabname||'"';
    EXECUTE IMMEDIATE SqlStmt;
    COMMIT;
END@

修改原代码调用该存储过程：

BEGIN
    DECLARE SQLSTATE    CHAR(5);
    DECLARE RetState    CHAR(5);
    DECLARE SqlStmt     VARCHAR(1000);
    DECLARE Not_Needed  CONDITION FOR '51027';
    DECLARE CONTINUE HANDLER for Not_Needed
        BEGIN
            SET RetState = SQLSTATE;
        END;
    FOR i AS (SELECT TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA = 'LIBRAT' AND REORG_PENDING = 'Y')
    DO
        SET SqlStmt = 'SET INTEGRITY FOR LIBRAT.'||Trim(i.TABNAME)||' IMMEDIATE CHECKED';
        EXECUTE IMMEDIATE SqlStmt;
        IF RetState = '51027' THEN
            CALL REORG_TABLE('LIBRAT', Trim(i.TABNAME));
        END IF;
        SET RetState = NULL;
    END FOR;
END@

自治事务的提交仅作用于自身，主事务的游标不会被关闭，解决SQLCODE=-501的问题。

解决直接执行REORG的SQLCODE=-104问题 #

报错原因是动态SQL中表名/模式名可能需要用双引号包裹（如果对象名大小写敏感或包含特殊字符），修改拼接语句：

SET SqlStmt = 'REORG TABLE "LIBRAT"."'||Trim(TABNAME)||'"';
EXECUTE IMMEDIATE SqlStmt;

如果对象名是大写且符合标识符规则，双引号可省略；若为小写或含特殊字符则必须添加。

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