DB2 靜態 SQL 和動態 SQL 的比較與實踐

2009 年 10 月 26 日

數據庫系統為用戶提供了多樣的編程接口，以適用于各種應用需求。本文闡述了 DB2 中靜態 SQL 和動態 SQL 的區別和特點，給出兩種 SQL 在不同編程接口下的支持情況和典型應用，并通過實際案例，向讀者介紹了選擇靜態或動態 SQL 及編程接口的原則。

引言

SQL 語言作為標準的查詢語言，幾乎被所有的數據庫管理系統 (DBMS) 所支持，并成為國際標準。標準的 SQL 語言一般包括三類，即 DDL (Data Definition Language, 數據描述語言 ) 、DML (Data Manipulation Language, 數據操縱語言 ) 和 DCL(Data Control Language，數據控制語言 )。通過這些標準的 SQL 語句，使得各種數據庫能以一種較為統一的方式被訪問。

DB2（本文以下專指 DB2 UDB for Linux, Unix 和 Windows 版本）允許用戶通過多種編程接口發送 SQL 語句到數據庫引擎，然后由引擎統一編譯并且運行。SQL 語句從編譯和運行的角度可以分為兩種，靜態 SQL和 動態 SQL，這兩種 SQL 在使用方式、運行機制和性能表現等方面各有特點 :

• 靜態 SQL：靜態 SQL 語句一般用于嵌入式 SQL 應用中，在程序運行前，SQL 語句必須是確定的，例如 SQL 語句中涉及的列名和表名必須是存在的。靜態 SQL 語句的編譯是在應用程序運行前進行的，編譯的結果會存儲在數據庫內部。而后程序運行時，數據庫將直接執行編譯好的 SQL 語句，降低運行時的開銷。
• 動態 SQL：動態 SQL 語句是在應用程序運行時被編譯和執行的，例如，使用 DB2 的交互式工具 CLP 訪問數據庫時，用戶輸入的 SQL 語句是不確定的，因此 SQL 語句只能被動態地編譯。動態 SQL 的應用較多，常見的 CLI 和 JDBC 應用程序都使用動態 SQL。

表 1列舉了靜態 SQL 和動態 SQL 的比較結果。

根據編程方法的不同，DB2 的應用程序開還可以分為嵌入式 SQL 編程和非嵌入式編程 :

• 嵌入式 SQL 編程將 SQL 語句嵌入到宿主語言 (host) 的程序中，例如 C/C++ 程序。因為宿主語言不識別 SQL 語句，先要對程序進行預編譯，把 SQL 語句轉變為對 DB2 服務的調用，并重寫源代碼，最后再使用宿主語言的編譯器對應用程序進行編譯。嵌入式 SQL 都需要被綁定到特定的數據庫中，可分為嵌入式靜態 SQL 和嵌入式動態 SQL。
• 非嵌入式應用程序不需要預編譯，且方法較多，如 CLI、JDBC、ODBC、ADO.NET 等等，這些方法中都使用動態 SQL。表 2列舉了常見的 DB2 編程接口。

在下面的幾個章節中，我們將陸續從使用角度上描述靜態和動態 SQL 在各種編程接口中的應用，并運用一些實例來介紹在具體的場景中如何選擇。

靜態 SQL 應用

嵌入式靜態 SQL

無論是嵌入式靜態 SQL 還是嵌入式動態 SQL，都需要先進行預編譯，并綁定到特定的數據庫。DB2 的嵌入式 SQL 應用程序支持以下幾種語言：C，C++，COBOL，FORTRAN 和 REXX?。

對嵌入式靜態 SQL 而言，只能使用編譯時確定的 SQL 語句和訪問編譯時已經存在的數據庫對象。清單 1是一個查詢表的例子，使用 C 語言 :

                        //test.sqc
                        EXEC SQL INCLUDE SQLCA;
                        EXEC SQL BEGIN DECLARE SECTION;
                        sqlint32 t_seq = 0;
                        char t_name[64]={0};
                        EXEC SQL END DECLARE SECTION;
                        ...
                        t_seq = 5;
                        EXEC SQL SELECT c_name INTO :t_name FROM test_tbl WHERE seq=:t_seq;
                        printf("c_name = %s \n", t_name);
                        

使用下面的命令，對上述代碼進行預編譯和編譯：

                        # 需要先連接數據庫
                        db2 connect to TESTDB
                        # 使用 PREP 命令對 sqc 源文件進行預編譯，這將生成 test.c 源文件
                        db2 PREP test.sqc
                        # 使用 C 編譯器對 test.c 進行編譯
                        xlC -q64 -I$DB2PATH/include -g -L$DB2PATH/lib -ldb2 -o test test.c
                        

SQLJ

SQLJ 是應用于靜態 SQL 的 Java 編程接口。使用 SQLJ 編寫應用程序和使用其他的語言接口相似，一個典型的 SQLJ 應用程序主要包括以下幾個方面：

• 載入包含 SQLJ 和 JDBC 的 Java 包。
• 定義收發數據的載體變量。
• 連接至數據庫，運行相應的 SQL 語句并且正確處理錯誤情況，最后從數據庫斷開。

清單 3是 SQLJ 中執行 SELECT 語句的代碼片斷。

                        // 載入相關包
                        import sqlj.runtime.*;
                        import java.sql.*;
                        // 連接至數據庫
                        Connection con0 = DriverManager.getConnection(url);
                        // 執行相關的 SQL 語句
                        #sql [ctx] iter = {SELECT NAME FROM EMP};
                        // 得到結果
                        while (iter.next()) {
                        System.out.println(iter.LASTNAME());
                        }
                        

在 SQLJ 應用程序中，可以使用 ExecutionContext 類去控制和監控 SQL 語句的執行，如 清單 4所示。

                        // 分配存儲執行上下文的變量
                        ExecutionContext exeCtx=new ExecutionContext();
                        // 關聯變量和要執行的語句
                        #sql [connCtx, exeCtx] {DELETE FROM EMP WHERE SALARY > 10000};
                        // 獲取結果
                        System.out.println("Deleted " + exeCtx.getUpdateCount() + " rows");
                        

動態 SQL 應用

嵌入式動態 SQL

與嵌入式靜態 SQL 相同，嵌入式動態 SQL 也需要預編譯。不同的是，嵌入式動態 SQL 將 SQL 語句存放在宿主語言的字符型變量中，這樣的 SQL 語句在預編譯時是不被處理的，而是被當作主機變量對待，直到程序運行時才被編譯執行。

得益于動態 SQL 的優點，嵌入式動態 SQL 可以處理運行時才確定的 SQL 語句，例如由程序運行時拼接的 SQL 語句。為了處理返回結果未知的 SELECT 語句，嵌入式動態 SQL 使用 SQLDA(SQL descriptor area) 結構和 DESCRIBE 語句獲取結果集的結構和屬性。SQLDA 結構如 圖 1所示。HEADER 描述整個結果集的信息，而每個 SQLVAR 結構描述結果集中一個字段的信息。

清單 5展示了如何使用 SQLDA 結構和 DESCRIBE 語句處理 SELECT 語句。

                        //test1.sqc
                        // 聲明兩個 SQLDA 指針，minsqlda 將是一個最小的 SQLDA 結構，用于 PREPARE 語句，
                        // 此時結果集的字段數量未知，所以只需一個最小的 SQLDA，即包含 HEADER 和一個 SQLVAR
                        struct sqlda * minsqlda = new sqlda;
                        struct sqlda * fulsqlda = NULL;
                        strcpy(hostVarStmt, "SELECT name FROM TEST_TBL");
                        // PREPARE 將填寫 minsqlda 的 header，sqldabc 為 SQLDA 總長度，sqln 為 SQLVAR 數量，即字段數量
                        EXEC SQL PREPARE STMT INTO :*minsqlda FROM :hostVarStmt;
                        // 根據從 minsqlda 中獲取的長度，分配完整的 SQLDA 結構 fulsqlda，其中將包括合適數量的 SQLVAR 結構
                        fulsqlda = (struct sqlda *)malloc(SQLDASIZE(minsqlda->sqln));
                        // 使用 DESCRIBE 語句，獲取結果集中每個字段的描述信息，包括各字段的類型 (sqltype) 和長度 (sqllen)
                        EXEC SQL DESCRIBE STMT INTO :fulsqlda;
                        Loop
                        {
                        // 根據每個字段的長度，分配內存，將地址存儲在對應 SQLVAR 的 sqldata 中
                        }
                        // 聲明游標
                        EXEC SQL DECLARE c1 CURSOR FOR STMT;
                        EXEC SQL OPEN c1;
                        // 讀取記錄，記錄中每個字段的內容將寫入 fulsqlda 中對應 SQLVAR 結構的 sqldata 指向的內存
                        EXEC SQL FETCH c1 USING DESCRIPTOR :*fulsqlda;
                        // 循環讀取所有記錄
                        while (sqlca.sqlcode != 100)
                        {
                        EXEC SQL FETCH c1 USING DESCRIPTOR :*fulsqlda;
                        }
                        EXEC SQL CLOSE c1;
                        

DB2 CLI

DB2 CLI（Call Level Interface）基于微軟的 ODBC（Open Database Connectivity）標準，同時也增加了 DB2 特有的功能。它允許開發人員使用 C/C++ 語言訪問 DB2 并通過函數調用將動態 SQL 語句傳遞給 DB2。DB2 CLI 一方面在 ODBC 的環境中作為 ODBC 驅動被 ODBC 管理器加載，另一方面，應用程序也可以直接使用 DB2 CLI API，此時具有更好的性能。清單 6展示了 CLI 如何執行一個 DELETE 語句。

                        /* SQL statements to be executed */
                        SQLCHAR * stmt1 = (SQLCHAR *)"delete from test1 where col1 = 5";
                        /* directly execute statement 1 */
                        cliRC = SQLExecDirect(hstmt, stmt1, SQL_NTS);
                        

對一個返回結果未知的 SELECT 查詢語句，需要使用相關的 CLI API 函數動態地獲取對結果集的描述，并取回數據。圖 2展示了這個處理過程。

與嵌入式動態 SQL 應用相比，CLI 程序的靈活性更強。表 3列舉了 CLI 應用程序和嵌入式動態 SQL 應用程序的比較。

JDBC

JDBC 是 Java 編程語言訪問關系型數據庫的工業標準，類似于 ODBC，為訪問基于 SQL 的數據庫提供了一組調用級的 API。在 JDBC 規范中，存在 4 種類型的 JDBC 驅動，DB2 LUW V9.7 中支持 Type 2 和 Type 4 兩種類型。更多關于 JDBC 的信息可參考 DB2 UDB for Linux, UNIX 和 Windows 中的 Java 開發概述（參見 參考資源部分）。

JDBC 以 Java 包的形式提供了一組接口和類，用來連接數據庫和執行 SQL 語句，這里的 SQL 語句也是動態 SQL，無須預編譯和數據庫綁定。清單 7所示為 JDBC 程序片斷。

                        try{
                        stmt = con.prepareCall("select * from table1");
                        stmt.execute();
                        rs = stmt.getResultSet();
                        while (rs!=null &amp&amp rs.next()) {
                        System.out.println("output: = " + rs.getInt(1));
                        }
                        }catch(SQLException sqle) {
                        System.out.println("Error, SQLCODE = " + sqle.getSQLState());
                        con.rollback();
                        }
                        

除了編程語言和運行基礎架構的不同，JDBC 應用和 CLI 應用具有很多相似的特性，例如它們都支持后向游標，分布式事務等。

ADO.NET 和 OLE DB

ADO.NET 是 Microsoft .NET Framework 基礎類庫的一部分，提供了訪問關系型數據庫系統和其他數據源的能力。ADO.NET 主要包括兩個部分：DataSet 和 DataProvider。使用 ADO.NET，有三種 DataProvider 可以用來訪問 DB2：

• OLE DB .NET Data Provider 和 ODBC .NET Data Provider 是兩種橋接式 provider，它們將 ADO.NET 請求轉換為對 IBM OLE DB Provider 或 IBM ODBC Driver 的請求。
• DB2 .NET Data Provider 是訪問 DB2 時推薦使用的 .NET provider，由于沒有額外的 OLE DB 或 ODBC 層，它具有更好的性能。

OLE DB 是一種較早出現的數據訪問模型，它基于 Microsoft? 的 COM 技術，提供了訪問不同信息源的統一方法。OLE DB 定義了 OLE DB 消費者和提供者，OLE DB 消費者可以通過 IBM OLE DB Provider for DB2 訪問 DB2 中的數據。使用 OLE DB 訪問 DB2 的應用程序可能包括 Microsoft? Visual Studio C++ 應用，Microsoft Visual Basic 應用，ATL 應用等。

其他應用程序開發接口

Perl 程序員可以使用 Perl 數據庫接口 DBI 來訪問 DB2。IBM 提供的 DBI 驅動為 IBM?DB2?Database Driver for Perl DBI (the DBD::DB2 driver)。Perl DBI 同樣使用動態 SQL，而且 Perl DBI 的接口與 CLI 和 JDBC 很相似，易于使用。

PHP 在 Web 應用開發領域一直占據很大份額，IBM 提供了下面兩種 PHP 擴展以訪問 DB2 數據庫：

• ibm_db2，是一種過程化的應用編程接口，與 PHP 應用一起編譯，提供一系列的 PHP 函數來訪問數據庫。
• pdo_ibm，是 IBM 提供的 PHP Data Objects (PDO) 驅動。

此外，IBM 對 Python，Ruby on Rails 等編程語言都提供了相應的 DB2 訪問支持。這些開發接口同樣都使用動態 SQL。

應用場景以及如何選擇

本節將結合幾個應用場景來介紹如何選擇 DB2 編程接口以及靜態 / 動態 SQL。

場景 1

銀行中賬目統計系統比較穩定，一般運行在一個特定的數據庫中，其目的是整合業務數據庫上數據，并通過分析數據得到一些相關的統計信息。這樣的系統具有以下一些特點 :

• 穩定，系統邏輯不會有什么變化，訪問固定的數據庫以及表。
• 定時運行，賬目統計信息通常需要在一段時間內進行整合。
• 數據量比較大，對性能要求較高。

針對這些特點，建議使用主要由靜態 SQL 語句組成的應用程序，這樣該系統在建立的時候會被編譯，然后相關的編譯信息會被存儲到數據庫里，在以后的運行過程中避免重復編譯，同時，這種應用由于結構簡單使得其很容易維護。當然，由于系統的數據變化較快，而且數據量很大，所以建議定時重新編譯程序，使得數據庫對每條 SQL 語句可以選擇最優的運行方式。

場景 2

考慮電信運營商對用戶話單的計費場景，圖 3顯示了一個電信運行系統結構圖。業務系統產生的各種話單會集中存放在一個關系型數據庫中，該數據庫可能位于大規模的高可靠性服務器。而計費程序則往往作為一個單獨的應用部署在其他規模較小的 UNIX 服務器上，并作為守護程序或者批處理程序運行。另外，很多業務規則例如計費標準可能存放在單獨得小型數據庫系統中。

計費程序的工作主要包括：從話單數據庫讀新話單，從業務配置數據庫讀取計費標準，計算話單的費用和優惠，將計費完畢的話單寫回話單數據庫。

由于計費標準的選擇經常和日期等變化的信息相關，程序中會存在一些不固定的 SQL 語句，因此靜態 SQL 應用并不適用。另外，除了訪問結構穩定的話單數據庫，計費程序也需要訪問一些變化概率較大的小型數據庫，例如計費標準可能會因為運營商營銷策略的調整而發生變化。如果使用嵌入式 SQL，就必須將應用程序和數據庫進行綁定，這將失去靈活性。最后，為了快速對大量的話單進行計費，計費程序對性能也有較高要求，所以 CLI 應用程序比 JDBC 應用更適合計費程序。

場景 3

在基于 BS 結構的電子商務應用中，瀏覽器、應用服務器（包括 Web 服務器）和數據庫構成了系統的三個要素。數據庫為電子商務提供了事務管理、數據持久化、安全認證、聯機備份等各方面的支持，是電子商務系統中的關鍵部分。圖 4顯示了一個簡單的電子商務系統結構示意圖。

在電子商務系統的實現中，數據庫訪問接口的選擇很大程度上依賴于所選擇的應用集成方案。例如傳統的 J2EE 應用中使用 JDBC 實現數據庫訪問。又如開源框架 STRUTS，SPRING 和 HIBERNATE，由 HIBERNATE 完成對 JDBC 的封裝，提供對數據庫的訪問能力。基于 PHP 開發的 Web 應用程序同樣非常流行，如果選擇 PHP 作為開發語言，那么使用 PDO（PHP Data Objects）和 IBM 提供的 pdo_ibm 驅動即可訪問 DB2 數據庫。對基于 .Net 框架開發的 Web 應用而言，ADO.NET 則是最合適的數據庫訪問接口。

靜態 SQL 和動態 SQL 的選擇原則

在以下情況下，適合選用靜態 SQL：

• 程序需要處理 SQL 語句頻率高，壓力較大
• SQL 語句較為簡單且已知不變
• SQL 語句訪問的數據庫對象變化很少
• 程序使用的 SQL 語句可以統一認證權限
• 同一條 SQL 語句重復執行的次數很少
• 很少運行 RUNSTATS 更新統計信息

在以下情況下，更適合選用動態 SQL：

• SQL 語句在應用程序執行時才生成
• SQL 語句訪問的對象在程序運行前不存在
• 希望 SQL 語句根據運行時的數據庫統計信息進行最好的優化
• 在程序運行中可能更改 SQL 語句的編譯環境，如配置參數和特殊寄存器的值
• 程序運行時，同時有較多 DDL 語句執行
• 選用特定的應用程序框架，例如場景 3 中的電子商務案例

當然，所有的原則都是經驗性的，真正的選擇還取決于應用的實際情況。在時間允許的情況下，對各種選擇進行比較測試也是可以考慮的方法。