DB2 for IBM i单SQL批量赋值优化IFS日志提取性能

我在IBM i的DB2环境中，使用QSYS2.IFS_READ表函数从IFS的日志文件中提取5项目标数据，每天会生成约25个这类日志文件。目前我通过游标遍历每个文件，针对每个文件执行6次查询，依靠记录数减去固定偏移量来定位并提取数据——这是我能想到的最快方法，但实际运行耗时23分钟，速度无法满足需求。想请教用CASE语句优化是否可行？

现有SQL代码如下：

BEGIN

DECLARE @path_name VARCHAR(28);
DECLARE @job VARCHAR(256);
DECLARE @run_date TIMESTAMP;
DECLARE @warnings VARCHAR(4);
DECLARE @errors VARCHAR(4);
DECLARE @job_complete VARCHAR(20);
DECLARE @elapsed VARCHAR(8);
DECLARE @rec_count INTEGER;

FOR @V1 AS @C1 CURSOR FOR
    SELECT PATH_NAME, CREATE_TIMESTAMP FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(
        START_PATH_NAME => '/<PATH_NAME>',
        SUBTREE_DIRECTORIES => 'YES'))
    WHERE REGEXP_LIKE(PATH_NAME, '\d{8}.LOG')
    AND CREATE_TIMESTAMP >= CURRENT_TIMESTAMP - 1 DAY
    ORDER BY PATH_NAME
DO
SET @rec_count = (SELECT COUNT(*) FROM TABLE(QSYS2.IFS_READ(
                PATH_NAME => @V1.PATH_NAME)));

SET @vault_job = (SELECT REPLACE((SUBSTR(LINE, 46, 10)), '/', '') FROM TABLE(QSYS2.IFS_READ(
                    PATH_NAME => @V1.PATH_NAME))
                    WHERE LINE_NUMBER = 5);

SET @errors = (SELECT SUBSTR(LINE, 78, 4) FROM TABLE(QSYS2.IFS_READ(
                PATH_NAME => @V1.PATH_NAME))
                WHERE LINE_NUMBER = (@rec_count - 18));

SET @warnings = (SELECT SUBSTR(LINE, 78, 4) FROM TABLE(QSYS2.IFS_READ(
                PATH_NAME => @V1.PATH_NAME))
                WHERE LINE_NUMBER = (@rec_count - 17));

SET @job_complete = (SELECT SUBSTR(LINE, 53, 20) FROM TABLE(QSYS2.IFS_READ(
                    PATH_NAME => @V1.PATH_NAME))
                    WHERE LINE_NUMBER = (@rec_count - 2));

SET @elapsed = (SELECT SUBSTR(LINE, 49, 8) FROM TABLE(QSYS2.IFS_READ(
                PATH_NAME => @V1.PATH_NAME))
                WHERE LINE_NUMBER = (@rec_count - 1));                

CALL SYSTOOLS.LPRINTF('----------------------------');
CALL SYSTOOLS.LPRINTF('Job: ' || @job);
CALL SYSTOOLS.LPRINTF('Path name: ' || @V1.PATH_NAME);
CALL SYSTOOLS.LPRINTF('Create Time: ' || @V1.CREATE_TIMESTAMP);
CALL SYSTOOLS.LPRINTF('errors: ' || @errors);
CALL SYSTOOLS.LPRINTF('warnings: ' || @warnings);
CALL SYSTOOLS.LPRINTF('job complete at ' || @job_complete);
CALL SYSTOOLS.LPRINTF('elapsed: ' || @elapsed);
END FOR;

END

优化思路与方案 #

原代码的核心问题是每个文件被重复读取6次（1次统计行数+5次提取字段），IO开销是导致耗时过长的主要原因。使用CASE语句配合聚合函数可以实现单次读取文件完成所有数据提取，大幅降低IO次数，提升性能。

优化后的代码示例 #

BEGIN
    DECLARE @path_name VARCHAR(28);
    DECLARE @create_time TIMESTAMP;
    DECLARE @vault_job VARCHAR(256);
    DECLARE @errors VARCHAR(4);
    DECLARE @warnings VARCHAR(4);
    DECLARE @job_complete VARCHAR(20);
    DECLARE @elapsed VARCHAR(8);

    FOR file_cursor AS 
        SELECT PATH_NAME, CREATE_TIMESTAMP 
        FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(
            START_PATH_NAME => '/<PATH_NAME>',
            SUBTREE_DIRECTORIES => 'YES'))
        WHERE REGEXP_LIKE(PATH_NAME, '\d{8}.LOG')
          AND CREATE_TIMESTAMP >= CURRENT_TIMESTAMP - 1 DAY
        ORDER BY PATH_NAME
    DO
        -- 单次读取文件，同时计算总行数和提取所有目标字段
        WITH file_data AS (
            SELECT LINE_NUMBER, LINE,
                   COUNT(*) OVER() AS total_lines
            FROM TABLE(QSYS2.IFS_READ(PATH_NAME => file_cursor.PATH_NAME))
        )
        SELECT 
            MAX(CASE WHEN LINE_NUMBER = 5 THEN REPLACE(SUBSTR(LINE, 46, 10), '/', '') END) INTO @vault_job,
            MAX(CASE WHEN LINE_NUMBER = total_lines - 18 THEN SUBSTR(LINE, 78, 4) END) INTO @errors,
            MAX(CASE WHEN LINE_NUMBER = total_lines - 17 THEN SUBSTR(LINE, 78, 4) END) INTO @warnings,
            MAX(CASE WHEN LINE_NUMBER = total_lines - 2 THEN SUBSTR(LINE, 53, 20) END) INTO @job_complete,
            MAX(CASE WHEN LINE_NUMBER = total_lines - 1 THEN SUBSTR(LINE, 49, 8) END) INTO @elapsed
        FROM file_data;

        -- 输出结果
        CALL SYSTOOLS.LPRINTF('----------------------------');
        CALL SYSTOOLS.LPRINTF('Job: ' || @vault_job);
        CALL SYSTOOLS.LPRINTF('Path name: ' || file_cursor.PATH_NAME);
        CALL SYSTOOLS.LPRINTF('Create Time: ' || file_cursor.CREATE_TIMESTAMP);
        CALL SYSTOOLS.LPRINTF('errors: ' || @errors);
        CALL SYSTOOLS.LPRINTF('warnings: ' || @warnings);
        CALL SYSTOOLS.LPRINTF('job complete at ' || @job_complete);
        CALL SYSTOOLS.LPRINTF('elapsed: ' || @elapsed);
    END FOR;
END

关键优化点 #

• 单次文件读取：每个日志文件仅调用一次QSYS2.IFS_READ，避免重复读取带来的IO浪费
• 窗口函数计算总行数：用COUNT(*) OVER()一次性获取文件总行数，无需单独统计
• 条件聚合提取字段：通过CASE配合MAX函数，在同一次查询中提取所有需要的字段，减少多次查询的开销

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