Oracle SQL Developer 17运行DB2查询报SQLCODE=-109错误排查

Windows环境下使用Oracle SQL Developer 17连接DB2批量执行查询语句时触发如下错误：

DB2 SQL Error: SQLCODE=-109, SQLSTATE=42601, SQLERRMC=WITH ISOLATION USE AND KEEP, DRIVER=3.69.56
受当前环境限制无法直接使用WITH UR语法，待执行的示例SQL如下：

SELECT 'test' AS Data_Item, 'RC322' AS Test, 'dc' AS Source_Table, (SELECT COUNT() FROM schema.tableA ) AS VALUE FROM SYSIBM.SYSDUMMY1
union all SELECT 'test' AS Data_Item, 'RC323' AS Test, 'dc' AS Source_Table, (SELECT COUNT() FROM schema.tableB ) AS VALUE FROM SYSIBM.SYSDUMMY1
union all SELECT 'test' AS Data_Item, 'RC324' AS Test, 'dc' AS Source_Table, (SELECT COUNT() FROM schema.tableC ) AS VALUE FROM SYSIBM.SYSDUMMY1
union all SELECT 'test' AS Data_Item, 'RC325' AS Test, 'dc' AS Source_Table, (SELECT COUNT() FROM schema.tableD ) AS VALUE FROM SYSIBM.SYSDUMMY1

SQLCODE=-109对应DB2语法非法错误，触发根源是Oracle SQL Developer 17的DB2适配模块存在兼容bug，会自动在所有提交给DB2的查询语句末尾拼接不符合DB2语法规范的隔离级别子句WITH ISOLATION USE AND KEEP，和SQL本身的写法、数据库权限没有关系。

优先使用第一个方案，改动最小生效最快：

• 调整连接的事务隔离级别配置
  右键点击当前DB2连接打开属性面板，找到事务隔离级别配置项，将默认值修改为*未提交读（Read Uncommitted）*后保存，重连数据库即可。该配置对应JDBC隔离级别值为1，等价于DB2的WITH UR效果，工具不会再自动拼接非法后缀，也不需要在SQL中额外写隔离级别的语法。
  如果图形界面找不到对应配置项，可以直接在连接的自定义JDBC参数中添加一行：

  defaultIsolationLevel=1
  

• 改写SQL为匿名块绕开自动拼接逻辑
  如果没有修改连接配置的权限，可以把查询逻辑包裹在DB2原生匿名块中，工具的自动后缀拼接逻辑不会解析块内语句，改写示例如下：

  BEGIN
  DECLARE GLOBAL TEMPORARY TABLE SESSION.QUERY_RESULT (
    Data_Item VARCHAR(10),
    Test VARCHAR(10),
    Source_Table VARCHAR(10),
    VAL BIGINT
  ) ON COMMIT PRESERVE ROWS;
  
  INSERT INTO SESSION.QUERY_RESULT
  SELECT 'test' AS Data_Item, 'RC322' AS Test, 'dc' AS Source_Table, (SELECT COUNT(*) FROM schema.tableA ) AS VAL FROM SYSIBM.SYSDUMMY1
  UNION ALL SELECT 'test' AS Data_Item, 'RC323' AS Test, 'dc' AS Source_Table, (SELECT COUNT(*) FROM schema.tableB ) AS VAL FROM SYSIBM.SYSDUMMY1
  UNION ALL SELECT 'test' AS Data_Item, 'RC324' AS Test, 'dc' AS Source_Table, (SELECT COUNT(*) FROM schema.tableC ) AS VAL FROM SYSIBM.SYSDUMMY1
  UNION ALL SELECT 'test' AS Data_Item, 'RC325' AS Test, 'dc' AS Source_Table, (SELECT COUNT(*) FROM schema.tableD ) AS VAL FROM SYSIBM.SYSDUMMY1;
  
  SELECT * FROM SESSION.QUERY_RESULT;
  END
  

• 关闭工具自动追加语句后缀开关
  打开SQL Developer首选项，找到DB2相关的SQL编辑器配置，将语句终止符从默认的;修改为/，同时关闭「自动追加事务子句」类的开关，重启工具后即可避免非法子句被自动拼接到语句末尾。

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