DB2 z/OS V12批量插入报[42601][-104]错误及解决办法

问题说明 #

尝试通过多值INSERT语句往DB2的CLAIM_ITEM表批量插入数据时，收到错误提示[42601][-104] ILLEGAL SYMBOL ","。单条VALUES插入可以正常执行，但多条VALUES组合就会触发报错。

原因 #

使用的DB2 z/OS V12版本不支持多值INSERT语法（即VALUES后跟随多个括号包裹的数据集的写法）。

解决方案 #

改用SELECT...UNION ALL结合SYSIBM.SYSDUMMY1的方式实现批量插入，具体代码如下：

原报错语句 #

insert into
    CLAIM_ITEM  (CLAIM_NBR,CLMS_ITEM_SEQ_NBR,ITEM_TYPE_CODE,ITEM_STAT_CODE,ADD_DATE,UPD_DATE_TIME,FOL_PROC_INDT,ITEM_CODE,ALT_ITEM_DESC)
values
    (479156072, 1, 'C', 'REPT', '2023-08-14', '2023-08-14 23:20:59.436', ' ', 'TBC', 'Wood top black metal coff'),
    (479156072, 2, 'C', 'REPT', '2023-08-14', '2023-08-14 23:20:59.436', ' ', 'TVBS', 'LG TV'),
    (479156072, 3, 'C', 'REPT', '2023-08-14', '2023-08-14 23:20:59.436', ' ', 'SOFA', 'Off White fabric sofa'),
    (479156072, 4, 'C', 'REPT', '2023-08-14', '2023-08-14 23:20:59.436', ' ', 'BDHQ', 'Q Grey HB master bedroom')
;

正确执行语句 #

insert into
UNITEST.CLAIM_ITEM
(CLAIM_NBR,CLMS_ITEM_SEQ_NBR,ITEM_TYPE_CODE,ITEM_STAT_CODE,ADD_DATE,UPD_DATE_TIME,FOL_PROC_INDT,ITEM_CODE,ALT_ITEM_DESC)
SELECT 479156072, 1, 'C', 'REPT', '2023-08-14', '2023-08-14 23:20:59.436', ' ', 'TBC', 'Wood top black metal coff' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 479156072, 2, 'C', 'REPT', '2023-08-14', '2023-08-14 23:20:59.436', ' ', 'TVBS', 'LG TV' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 479156072, 3, 'C', 'REPT', '2023-08-14', '2023-08-14 23:20:59.436', ' ', 'SOFA', 'Off White fabric sofa' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 479156072, 4, 'C', 'REPT', '2023-08-14', '2023-08-14 23:20:59.436', ' ', 'BDHQ', 'Q Grey HB' FROM SYSIBM.SYSDUMMY1

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