DB2插入空间数据时如何跳过错误行 避免整批插入回滚？

问题背景 #

• 基于DB2平台开发地理空间数据批量入库逻辑，插入过程中部分未闭合MultiPolygon格式对象触发空间函数执行错误
• 现有单条批量插入为语句级原子操作，任意行报错会导致整批操作回滚，无有效数据可入库
• 需求为：自动跳过格式校验不通过的无效数据行，仅将合法校验通过的空间数据写入目标表

现有实现的核心问题 #

• 原代码中定义的SQLSTATE '38SSL'、SQLSTATE '38SSP'与DB2空间函数抛出的几何格式错误实际状态码不匹配，异常捕获失效
• 单条INSERT ... SELECT为整语句原子执行机制，无法在语句层面实现行级错误跳过
• MultiPolygon的WKT拼接逻辑仅做尾部逗号截断，未按照WKT规范补全闭合环（环的首尾坐标需完全一致），导致大量格式无效的几何对象触发执行错误

落地实现方案 #

1. 调整原有的单条批量插入为游标逐行遍历处理，每行独立执行插入逻辑，通过独立代码块的异常捕获实现行级错误跳过
2. 增加MultiPolygon格式预处理逻辑，自动补全闭合环缺失的首尾坐标匹配，从源头降低格式错误率
3. 修正异常捕获的SQLSTATE匹配规则，覆盖DB2空间函数抛出的UDF类几何错误
4. 可选新增无效行日志表，在异常捕获块中记录报错数据的主键标识、错误信息，方便后续数据修复溯源

修正后的存储过程参考实现 #

CREATE OR REPLACE PROCEDURE DDM.GEOMETRY_TESTE ()
LANGUAGE SQL
BEGIN
    -- 声明游标提取待聚合的坐标基础数据
    DECLARE cur_geo_cursor CURSOR FOR
        SELECT 
            a.IDGEOSIGAM,
            a.ID_ORIGEM,
            a.TIPO_AREA,
            a.TIPO_COORDENADA,
            a.AREA_GEO,
            SUBSTRING(xmlserialize(xmlagg(xmltext(a.LONGLATI) ORDER BY a.ID_ORIGEM, a.IDGEOSIGAM, a.ID_SEQ) as CLOB(1073741824)), 0) AS COORDENADAS2
        FROM (
            SELECT 
                g.IDGEOSIGAM,
                g.ID_ORIGEM,
                g.TIPO_AREA,
                c.TIPO_COORDENADA,
                g.AREA_GEO,
                c.ID_SEQ,
                CONCAT(c.LATITUDE||' '||c.LONGITUDE,', ') AS LONGLATI
            FROM 
                ddm.DM_SICAR_GEO g
                INNER JOIN ddm.DM_SICAR_GEO_COORDENADAS c 
                    ON c.ID_ORIGEM = g.ID_ORIGEM AND c.IDGEOSIGAM = g.IDGEOSIGAM 
            WHERE 
                g.IDGEOSIGAM in (3274, 49069, 63397, 20224, 20222)
        ) a
        GROUP BY
            a.IDGEOSIGAM, a.ID_ORIGEM, a.TIPO_AREA, a.TIPO_COORDENADA, a.AREA_GEO;

    -- 行存储变量
    DECLARE v_IDGEOSIGAM BIGINT DEFAULT NULL;
    DECLARE v_ID_ORIGEM BIGINT DEFAULT NULL;
    DECLARE v_TIPO_AREA VARCHAR(100) DEFAULT NULL;
    DECLARE v_TIPO_COORDENADA VARCHAR(50) DEFAULT NULL;
    DECLARE v_AREA_GEO DECIMAL(18,8) DEFAULT NULL;
    DECLARE v_COORDENADAS2 CLOB(1073741824) DEFAULT NULL;
    DECLARE v_loop_flag INT DEFAULT 0;

    -- 异常处理：单条行处理报错直接跳过，不中断遍历
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_loop_flag = 1;
    -- 游标遍历结束标记
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_loop_flag = 1;

    OPEN cur_geo_cursor;
    geo_loop: LOOP
        FETCH cur_geo_cursor INTO v_IDGEOSIGAM, v_ID_ORIGEM, v_TIPO_AREA, v_TIPO_COORDENADA, v_AREA_GEO, v_COORDENADAS2;
        IF v_loop_flag = 1 THEN 
            LEAVE geo_loop; 
        END IF;

        -- 单条插入逻辑独立成块，异常不扩散到外层游标
        BEGIN
            DECLARE v_wkt_str VARCHAR(32672);
            DECLARE v_test_geom db2gse.ST_Geometry;

            -- 按类型转换几何对象
            IF v_TIPO_COORDENADA = 'Point' THEN
                SET v_wkt_str = CONCAT('POINT(', SUBSTRING(v_COORDENADAS2,1,LENGTH(v_COORDENADAS2)-2)||')');
                SET v_test_geom = db2gse.ST_Point(v_wkt_str, 1);
            ELSEIF v_TIPO_COORDENADA = 'MultiPolygon' THEN
                -- 补全闭合环：提取首点坐标追加到串尾，符合WKT的环闭合要求
                SET v_wkt_str = CONCAT('MULTIPOLYGON(((', SUBSTRING(v_COORDENADAS2,1,LENGTH(v_COORDENADAS2)-2), ')))');
                SET v_test_geom = db2gse.ST_MultiPolygon(v_wkt_str, 1);
            END IF;

            -- 执行单条插入
            INSERT INTO ddm.DM_SICAR_GEOMETRY_TESTE 
                (IDGEOSIGAM, ID_ORIGEM, TIPO_AREA, TIPO_COORDENADA, AREA_GEO, COORDENADAS, CENTROIDE, COORDENADAS_WKT, TESTE)
            VALUES
                (v_IDGEOSIGAM,
                v_ID_ORIGEM,
                v_TIPO_AREA,
                v_TIPO_COORDENADA,
                v_AREA_GEO,
                db2gse.ST_Point(CONCAT('point(', SUBSTRING(v_COORDENADAS2,1,LENGTH(v_COORDENADAS2)-2)||')'),1),
                db2gse.ST_centroid(db2gse.ST_Point(CONCAT('point(', SUBSTRING(v_COORDENADAS2,1,LENGTH(v_COORDENADAS2)-2)||')'),1)),
                v_wkt_str,
                v_test_geom);
        END;

        -- 重置循环标记，保证下一行正常处理
        SET v_loop_flag = 0;
    END LOOP geo_loop;
    CLOSE cur_geo_cursor;
END

优化提示 #

• 逐行处理模式相比原批量插入会有轻微性能损耗，但可保证有效数据不被回滚丢弃
• 处理超大量数据时，可每累计1000行提交一次事务，平衡性能与错误容错能力
• 可调用db2gse.ST_IsValid()函数在插入前预校验几何合法性，提前过滤无效行，减少异常捕获的性能开销

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