使用 SQLJ 與 JDBC 獲取最佳 DB2 效能

簡介

本教學提供技巧與技術，協助您最佳化存取 DB2 V8.1 的 Java 應用程式，並充分發揮 IBM DB2 Universal Driver for SQLJ and JDBC 1.0 的效能。文中也說明如何避免影響應用程式效能的常見錯誤。讀者需具備 Java 程式設計與 DB2 基礎知識，熟悉 SQL 與 JDBC 將有助於理解，但非必要。

教學內容概覽

• SQLJ 與 JDBC 比較
• SQLJ 基礎語法
• 靜態 SQL 與 SQLJ
• 透過 Java 應用程式最佳化 DB2 效能
• 避免 SQLJ 與 JDBC 常見錯誤
• 安全性、SQLJ 與 JDBC
• 同一程式中的 SQLJ 與 JDBC 混用
• 所需工具

Java 語言與資料存取演進

Java 語言具備物件導向與跨平台兩大特性。在資料存取方面，Java 提供兩種開放標準介面：JDBC（低層級呼叫介面）與 SQLJ（嵌入式 SQL）。

JDBC

JDBC 透過動態 SQL 存取關聯式資料庫。範例：

java.sql.PreparedStatement prepStmt =
    context.prepareStatement("SELECT EMPNO FROM EMP_ACT WHERE PROJNO =?");
prepStmt.setString(1, strName);
java.sql.ResultSet rs = prepStmt.executeQuery();
// 擷取資料
rs.close();

SQLJ

SQLJ 建構於 JDBC 之上，允許在 Java 程式碼中直接嵌入 SQL 陳述式，減少底層 API 呼叫。同樣查詢的 SQLJ 寫法：

#sql [context] {SELECT EMPNO INTO :strEmpNo FROM EMP_ACT WHERE PROJNO =:strProjNo};

主要差異：JDBC 僅支援動態 SQL；SQLJ 可透過定製化過程使用靜態 SQL，大幅降低執行期開銷。此外，SQLJ 需經預編譯器將偽指令轉換為 Java 代碼。

為何選擇 SQLJ？

• 完成相同資料存取任務，SQLJ 代碼行數更少，開發、除錯、維護成本更低。
• 定製化過程可在編譯期檢查 SQL 語法，消除執行期錯誤風險。
• SQLJ 使用游標與陳述式實作 SQL，對熟悉其他語言嵌入式 SQL 的開發者學習曲線較平緩。

JDBC 唯一優勢在於執行動態 SQL，但可在同一程式中混用 JDBC 與 SQLJ（詳見後章）。

SQLJ 基礎知識

環境準備

在應用程式目錄或 CLASSPATH 中包含：

• db2jcc.jar (JDBC Type 4 驅動程式)
• sqlj.zip (SQLJ 轉換器類別檔)

兩者均位於 SQLLIB/java 目錄。代碼中匯入：

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

基本 SQLJ 語法規則

1. 陳述式必須以 #sql 開頭。
2. 陳述式必須以分號 ; 結尾。
3. 建議將 SQL 陳述式置於大括號內，並指定執行上下文（可選）。

#sql [context] {DELETE FROM EMP_ACT};

主機變數傳遞資料

使用主機變數（以冒號 : 開頭）從 Java 應用程式傳遞資料至 SQL 陳述式：

void m (String empno, String projno, int actno) throws SQLException {
    #sql [context] {INSERT INTO EMP_ACT (EMPNO, PROJNO, ACTNO) VALUES (:empno, :projno, :actno)};
}

完整應用程式範例

包含註冊驅動程式、建立連線、插入資料、異常處理與關閉連線：

import java.sql.Connection;
import java.sql.DriverManager;
import sqlj.runtime.ref.DefaultContext;

public class SqlJDemo {
    static {
        try { Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); }
        catch (Exception e) { e.printStackTrace(); }
    }
    static Connection con;
    static DefaultContext ctx;

    #sql iterator NamedIterator (String empno, String projno, int actno);
    #sql iterator PosIterator (String, String, int);

    public static void main (String args[]) {
        SqlJDemo demoApp = new SqlJDemo();
        demoApp.makeConnection();
        demoApp.insertData();
        try {
            #sql [ctx] {commit};
            ctx.close(); con.close();
        } catch (java.sql.SQLException sqle) { sqle.printStackTrace(); }
    }
    public void makeConnection() {
        try {
            ctx = DefaultContext.getDefaultContext();
            if (ctx == null) {
                String url = "jdbc:db2://localhost:50000/SAMPLE";
                con = DriverManager.getConnection(url, "myusername", "mypassword");
                con.setAutoCommit(false);
                ctx = new DefaultContext(con);
                DefaultContext.setDefaultContext(ctx);
            }
        } catch (Exception e) { e.printStackTrace(); }
    }
    public void insertData() {
        try {
            String empno = "01", projno = "10"; int actno = 1;
            #sql [ctx] {INSERT INTO EMP_ACT (EMPNO, PROJNO, ACTNO) VALUES (:empno, :projno, :actno)};
            #sql [ctx] {commit};
        } catch (Exception e) { e.printStackTrace(); }
    }
}

擷取多筆資料：迭代器

SELECT 陳述式返回結果集，需宣告迭代器遍歷。迭代器必須在方法外宣告（預編譯時會轉換為獨立類別）。

具名迭代器

宣告時指定欄位名稱與型別：

#sql iterator NamedIterator (String empno, String projno, int actno);

使用方式：

NamedIterator namedIter = null;
#sql [ctx] namedIter = {SELECT EMPNO, PROJNO, ACTNO FROM EMP_ACT};
while (namedIter.next()) {
    System.out.println(namedIter.empno() + " " + namedIter.projno() + " " + namedIter.actno());
}
namedIter.close();

位置迭代器

宣告時僅指定型別順序：

#sql iterator PosIterator (String, String, int);

需宣告主機變數接收資料，使用 FETCH ... INTO 語法：

String empno = null, projno = null; int actno = -1;
PosIterator posIter = null;
#sql [ctx] posIter = {SELECT EMPNO, PROJNO, ACTNO FROM EMP_ACT};
#sql {FETCH :posIter INTO :empno, :projno, :actno};
while (!posIter.endFetch()) {
    System.out.println(empno + " " + projno + " " + actno);
    #sql {FETCH :posIter INTO :empno, :projno, :actno};
}
posIter.close();

編譯 SQLJ 程式

使用 sqlj 轉換器將 .sqlj 檔轉換為 .java 檔：

sqlj SqlJDemo.sqlj

轉換器產生 SqlJDemo.java 及每個連接上下文的輪廓檔 SqlJDemo_SJProfile0.ser。下一步需對輪廓檔進行定製化，在 DB2 中建立靜態 SQL 包。

靜態 SQL

什麼是靜態 SQL？

DB2 執行 SQL 三步驟：語法檢查、最佳化（決定存取路徑）、執行。動態 SQL 每次執行皆重複三步驟；靜態 SQL 在預編譯時完成前兩步驟，結果儲存為包，執行時直接綁定，開銷更低、效能更佳。

從 Java 代碼建立包

使用 db2sqljcustomize 對輪廓檔定製化，自動將生成的包綁定至資料庫：

db2sqljcustomize -user myusername -password mypassword -url jdbc:db2://localhost:50000/sample -onlinecheck YES SqlJDemo_SJProfile0.ser

亦可單獨使用 db2sqljbind 綁定，或用 db2sqljprint 列印可讀版輪廓檔。

最佳化 DB2 效能

停用 AutoCommit

JDBC 連線預設開啟 AutoCommit，每個 SQL 陳述式自動提交，雖減少編碼但增加開銷，並可能影響交易完整性。建議關閉：

con = DriverManager.getConnection(url, userid, password);
con.setAutoCommit(false);

限制選取欄位

避免使用 SELECT *，僅選取所需欄位，減少網路傳輸與 I/O 開銷。

使用參數標記

對重複執行的動態 SQL，使用參數標記 (?) 避免重複編譯，重複利用存取計劃。

批次更新

大量 INSERT/UPDATE/DELETE 時，使用 PreparedStatement.addBatch() 與 executeBatch() 減少網路往返。

調整 Fetch Size

根據應用程式需求設定適當的 setFetchSize()，平衡記憶體使用與網路往返次數。

連線池

Web 環境中務必使用連線池（如 WebSphere DataSource），避免頻繁建立/關閉連線的高昂成本。

避免常見錯誤

• 未關閉資源： ResultSet、Statement、Connection 必須在 finally 區塊中關閉。
• 不當的隔離等級： 選擇最低可接受的隔離等級（預設 Cursor Stability），減少鎖競爭。
• 忽略 Runstats： 定期執行 RUNSTATS 更新統計資訊，確保最佳化器產生最佳存取計劃。
• 索引濫用： 避免建立過多或重複索引，增加維護成本。

安全性考量

使用參數標記防止 SQL 注入；限制資料庫帳號權限；加密敏感資料傳輸（SSL）。

同一程式中混用 SQLJ 與 JDBC

需動態 SQL 時可在 SQLJ 程式中穿插 JDBC 代碼。注意共用同一連線上下文時的交易邊界管理。

工具支援

IBM 提供 Developer Workbench、DB2 Control Center 等工具協助開發、除錯與效能調優。