Java JDBC连接DB2 IDAA无法启用查询加速器问题排查

问题背景 #

我尝试通过纯JDBC方式（使用DriverManager创建连接并执行SQL）在Java中以查询加速器模式执行SQL语句，但抛出com.ibm.db2.jcc.am.SqlSyntaxErrorException错误。已按照IBM文档在连接URL中设置了特殊参数，且数据表已迁移至IBM DB2 Analytics Accelerator（IDAA）服务器，同时可通过AQT工具正常执行加速模式查询且速度极快。使用的是db2jcc4 Type4驱动，相关代码如下：

ConnectionManager类代码 #

public class ConnectionManager {
 private static final Logger LOGGER = LoggerFactory.getLogger(ConnectionManager.class);
 private static String url = "jdbc:db2://myIP:myPort/myDBName:specialRegisters=CURRENT QUERY ACCELERATION=ELIGIBLE";
 private static String driverName = "com.ibm.db2.jcc.DB2Driver";
 private static String username = "user";
 private static String password = "pwd";
 private static Connection con;
 private static String urlstring;
 public static Connection getConnection() throws SQLException, ClassNotFoundException {
 try {
 Class.forName(driverName);
 try {
 con = DriverManager.getConnection(url, username, password);
 } catch (SQLException ex) {
 // log an exception. fro example: System.out.println("Failed to create the database connection.");
 throw ex;
 }
 } catch (ClassNotFoundException ex) {
 // log an exception. for example: System.out.println("Driver not found.");
 throw ex;
 }
 return con;
 }
}

报错信息 #

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10165][10051][4.24.92] Invalid database URL syntax: jdbc:db2://IP:Port/DBName:specialRegisters=CURRENT QUERY ACCELERATION=ELIGIBLE. ERRORCODE=-4461, SQLSTATE=42815
 at com.ibm.db2.jcc.am.b6.a(b6.java:810)
 at com.ibm.db2.jcc.am.b6.a(b6.java:66)
 at com.ibm.db2.jcc.am.b6.a(b6.java:98)
 at com.ibm.db2.jcc.DB2Driver.tokenizeURLProperties(DB2Driver.java:950)
 at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:413)
 at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:113)
 at java.sql.DriverManager.getConnection(DriverManager.java:664)
 at java.sql.DriverManager.getConnection(DriverManager.java:247)
 at colruyt.spcpocxpsejb.util.ConnectionManager.getConnection(ConnectionManager.java:38)
 at colruyt.spcpocxpsejb.util.TestUtility.testAOT(TestUtility.java:98)
 at colruyt.spcpocxpsejb.util.TestUtility.main(TestUtility.java:25)
Caused by: java.util.NoSuchElementException
 at java.util.StringTokenizer.nextToken(StringTokenizer.java:349)
 at java.util.StringTokenizer.nextToken(StringTokenizer.java:377)
 at com.ibm.db2.jcc.DB2Driver.tokenizeURLProperties(DB2Driver.java:938)
 ... 7 more

问题分析与解决办法 #

嘿，我一眼就发现问题了——你的连接URL里的specialRegisters参数格式不符合DB2 JDBC驱动的解析规则！驱动在处理URL参数时，会把CURRENT QUERY ACCELERATION=ELIGIBLE里的第二个等号当成新参数的分隔符，导致解析失败，直接抛出了语法错误。

1. 修正连接URL格式（首选方案） #

对于specialRegisters这种包含空格和内部等号的参数，必须用**花括号{}**把整个参数值包裹起来，这样驱动才会把它识别为一个完整的设置项。修正后的URL应该是：

private static String url = "jdbc:db2://myIP:myPort/myDBName:specialRegisters={CURRENT QUERY ACCELERATION=ELIGIBLE}";

2. 用Properties传递参数（备选方案） #

如果还是担心URL格式出错，也可以换一种方式：通过java.util.Properties对象传递参数，而不是直接写在URL里，这种方式更清晰，也不容易出错：

public static Connection getConnection() throws SQLException, ClassNotFoundException {
    try {
        Class.forName(driverName);
        Properties props = new Properties();
        props.setProperty("user", username);
        props.setProperty("password", password);
        // 直接设置特殊寄存器参数
        props.setProperty("specialRegisters", "CURRENT QUERY ACCELERATION=ELIGIBLE");
        con = DriverManager.getConnection("jdbc:db2://myIP:myPort/myDBName", props);
    } catch (SQLException ex) {
        LOGGER.error("Failed to create the database connection.", ex);
        throw ex;
    } catch (ClassNotFoundException ex) {
        LOGGER.error("Driver not found.", ex);
        throw ex;
    }
    return con;
}

3. 关于AQT正常执行的原因 #

AQT这类工具是先执行会话级的设置语句（比如SET CURRENT QUERY ACCELERATION = ELIGIBLE;），再执行查询，和JDBC URL里设置参数的解析逻辑完全不同，所以不会遇到格式问题。

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