COBOL/DB2作业求助：DB2表字段匹配查询逻辑失效

DevOps 小满

2026-7-20

COBOL/DB2程序匹配DB2表数据失败的排查建议

问题背景

需编写COBOL程序读取DB2表DB2.SMLC_RGR_PRCT_RCS，匹配字段NR_SMLC_RGR_PRCT与输入文件变量I01-NR-SMLC-RGR-PRCT的值，仅在匹配成功时推进程序执行。但无论使用何种SQL查询命令或循环逻辑（包括FETCH循环）都无法实现预期效果，尝试各类SELECT选项仍未解决问题，恳请提供排查建议。

相关程序代码片段

*----------------------------------------------------------*
WORKING-STORAGE  SECTION.
*----------------------------------------------------------*
77  CTE-PROG                    PIC X(016)    VALUE
                                '*** GPOP9450 ***'.
77  CTE-VERS                    PIC X(006)    VALUE   'VRS001'.
77  SBVERSAO                    PIC X(008)    VALUE   'SBVERSAO'.
77  SBABEND                     PIC X(07) VALUE 'SBABEND'.
*----------------------------------------------------------*
*--------------------CONTADORES----------------------------*
01  GDA-FIM-ARQUIVO             PIC X(03).
01  GDA-REGISTROS-LIDOS         PIC 9(18) VALUE ZEROS.
01  GDA-REGISTROS-N-ENCONTRADOS PIC 9(18) VALUE ZEROS.
01  GDA_REGISTROS_ENCONTRADOS   PIC S9(4) USAGE COMP.
01  GDA_REG                     PIC S9(4) USAGE COMP.
*-------------------------------------------------------------*
*                    ARQUIVO DE ENTRADA

01 WREG-ARQENTD.
    03 I01-NR-SMLC-RGR-PRCT           PIC S9(4) USAGE COMP.
    03 I01-CODIGO-USUARIO             PIC X(08) VALUE IS SPACES.
*-------------------------------------------------------------*
*                     ARQUIVO DE SAIDA 01

01 WREG-ARQSAI-1.
    03 O001-NR-SMLC-RGR-PRCT          PIC 9(04) VALUE ZEROS.
    03 O001-CODIGO-USUARIO            PIC 9(08) VALUE IS ZEROS.
    03 O001-DATA-PESQUISA             PIC X(10) VALUE IS SPACES.
*-------------------------------------------------------------*
*                      ARQUIVO DE SAIDA 02
01 WREG-ARQSAI-2.
    03 O002-NR-SMLC-RGR-PRCT          PIC S9(4) USAGE COMP.
    03 O002-NR_SMLC_RGR_PRCT          PIC S9(4) USAGE COMP.
    03 O002-CD_OGM_RCS_SMLC           PIC S9(4) USAGE COMP.
    03 O002-TX_SMLC_RGR_PRCT          PIC X(50) VALUE SPACES.
    03 O002-DT_INC_MVT_SMLC          PIC X(10) VALUE SPACES.
    03 O002-DT_FIM_MVT_SMLC          PIC X(18)  VALUE ZEROS.
    03 O002-DATA-ALTERACAO           PIC X(26)  VALUE SPACES.
*-------------------------------------------------------------*
LOCAL-STORAGE SECTION.
*----------------------------------------------------------*
*--------------------ARÉA SQL----------------------------*
    EXEC SQL
         INCLUDE SQLCA
    END-EXEC.
*-------------------------------------------------------------*
PROCEDURE DIVISION.
000000-INICIO  SECTION.
*-------------------------------------------------------------*
     CALL SBVERSAO USING CTE-PROG CTE-VERS.


     PERFORM 0001-ABRE-ARQUIVOS
     PERFORM 0002-LER-ARQUIVO UNTIL GDA-FIM-ARQUIVO EQUAL 'S'
     PERFORM 0006-GERA-ESTATISTICAS


     CLOSE  ARQENTRD
            ARQSAI1
            ARQSAI2.

     STOP RUN.

000000-FIM. EXIT.


0001-ABRE-ARQUIVOS            SECTION.

    OPEN INPUT  ARQENTRD
         OUTPUT ARQSAI1
                 ARQSAI2
        .

0001-FIM. EXIT.


0002-LER-ARQUIVO              SECTION.

     READ ARQENTRD AT END MOVE 'S' TO GDA-FIM-ARQUIVO
                   NOT AT END
                   ADD 1 TO GDA-REGISTROS-LIDOS
                   PERFORM 01-BUSCA-DB2

     .

0002-FIM. EXIT.


01-BUSCA-DB2                  SECTION.

     EXEC SQL
       SELECT *
        INTO :GDA_REG
        FROM DB2.SMLC_RGR_PRCT_RCS
        WHERE NR_SMLC_RGR_PRCT = :I01-NR-SMLC-RGR-PRCT
     END-EXEC

    IF SQLCODE = 0
       PERFORM 0005-GRAVA-ARQSAI-1
    ELSE
    IF SQLCODE > 0
          PERFORM CHAMA-ERRO-001
    END-IF

排查建议

校验数据类型一致性
输入变量I01-NR-SMLC-RGR-PRCT为PIC S9(4) USAGE COMP，需确认DB2表中NR_SMLC_RGR_PRCT字段的类型（如是否为SMALLINT）、符号属性、长度完全匹配。类型不匹配会触发隐性转换，导致匹配逻辑失效。
修正SELECT INTO的变量映射
当前SQL使用SELECT * INTO :GDA_REG，但表中字段数量远多于单个GDA_REG变量，会直接引发SQLCODE错误（如-802）。必须明确指定查询字段，且INTO子句的变量数量、类型需与SELECT字段一一对应，示例：
```
EXEC SQL
  SELECT NR_SMLC_RGR_PRCT, CD_OGM_RCS_SMLC, TX_SMLC_RGR_PRCT
  INTO :O002-NR-SMLC-RGR-PRCT, :O002-CD_OGM_RCS_SMLC, :O002-TX_SMLC_RGR_PRCT
  FROM DB2.SMLC_RGR_PRCT_RCS
  WHERE NR_SMLC_RGR_PRCT = :I01-NR-SMLC-RGR-PRCT
END-EXEC
```
完善SQL错误码处理逻辑
当前仅处理SQLCODE=0和SQLCODE>0的情况，但DB2多数错误以负码形式返回（如-404表不存在、-802数据转换错误、-104语法错误）。需添加负码捕获逻辑，打印具体SQLCODE和SQLERRM信息，示例：
```
ELSE
    DISPLAY "SQL错误码: " SQLCODE ", 错误信息: " SQLERRM
    ADD 1 TO GDA-REGISTROS-N-ENCONTRADOS
END-IF
```
验证输入变量的实际值
在执行SQL前，打印I01-NR-SMLC-RGR-PRCT的实际值（COMP类型需转换为字符型后显示，可通过UNSTRING或专用转换逻辑实现），确认输入文件读取的值无截断、错位问题。
直接测试SQL语句有效性
在DB2客户端（如SPUFI、DSNTEP2）执行相同逻辑的SQL，替换条件为输入变量的实际值，示例：
```
SELECT * FROM DB2.SMLC_RGR_PRCT_RCS WHERE NR_SMLC_RGR_PRCT = 1234; -- 替换为实际输入值
```
若客户端能查到数据但程序不行，说明是程序变量映射或类型问题；若客户端也查不到，说明输入值在表中不存在，或表权限、数据范围有问题。
检查DB2连接与表权限
确认程序运行用户拥有DB2.SMLC_RGR_PRCT_RCS表的SELECT权限，且程序已成功连接DB2（部分环境需显式CONNECT语句，或通过绑定包确保连接正常）。

改用游标处理多行匹配场景
若表中可能存在多个匹配行，单条SELECT会返回SQLCODE=100（无匹配）或-811（多行返回），需用游标FETCH数据，示例：

WORKING-STORAGE SECTION.
* 定义游标
EXEC SQL
  DECLARE C1 CURSOR FOR
    SELECT NR_SMLC_RGR_PRCT, CD_OGM_RCS_SMLC, TX_SMLC_RGR_PRCT
    FROM DB2.SMLC_RGR_PRCT_RCS
    WHERE NR_SMLC_RGR_PRCT = :I01-NR-SMLC-RGR-PRCT
END-EXEC.

PROCEDURE DIVISION.
01-BUSCA-DB2 SECTION.
    EXEC SQL OPEN C1 END-EXEC.
    IF SQLCODE = 0
        PERFORM UNTIL SQLCODE NOT = 0
            EXEC SQL FETCH C1 INTO :O002-NR-SMLC-RGR-PRCT, :O002-CD_OGM_RCS_SMLC, :O002-TX_SMLC_RGR_PRCT END-EXEC
            IF SQLCODE = 0
                PERFORM 0005-GRAVA-ARQSAI-1
            ELSE IF SQLCODE = 100
                DISPLAY "未找到匹配记录"
            ELSE
                DISPLAY "FETCH错误: " SQLCODE
            END-IF
        END-PERFORM
    END-EXEC
    EXEC SQL CLOSE C1 END-EXEC.

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

COBOL/DB2作业求助：DB2表字段匹配查询逻辑失效

问题背景 #

相关程序代码片段 #

排查建议 #

问题背景

相关程序代码片段

排查建议