DB2 静态 SQL 和动态 SQL 的比较与实践
2009-12-24 00:00:00 来源:WEB开发网引言
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 的比较结果。
表 1. 静态 SQL 和动态 SQL 的比较
静态 SQL | 动态 SQL |
SQL 语句直接嵌入到宿主编程语言,程序需要预编译处理这些嵌入的 SQL 语句 | SQL 语句一般作为宿主语言的变量出现。嵌入式动态 SQL 应用需要预编译,非嵌入式 SQL 应用则无需预编译 |
SQL 语句在程序被编译时已知,涉及的数据库对象已存在 | SQL 语句在程序被编译时未知,涉及的数据库对象可以是运行时才创建的 |
SQL 语句在程序运行前被编译 | SQL 语句在程序运行时被编译 |
SQL 语句的编译结果在 DB2 的目录 (catalog) 中持久化保存 | SQL 语句的编译结果缓存在数据库的内存里 |
运行时仅读取目录 (catalog) | 运行时编译 SQL 语句需对目录 (catalog) 加锁 |
SQL 语句的优化是根据编译时的数据库统计信息进行的,不能完全反映运行时的情况 | SQL 语句的优化是根运行时的数据库统计信息进行的 |
对 SQL 语句所访问的数据对象的权限检查是在绑定时进行的 | 对 SQL 语句所访问的数据对象的权限检查是在运行时进行的 |
权限控制的粒度是包(package,一组 SQL 语句的编译结果),用户仅需要访问包的权限 | 权限控制的粒度是 SQL 语句,用户需要具有访问 SQL 语句中每个数据对象的权限 |
如果 SQL 语句中的对象被修改,如 DDL 执行,整个包都需要重新绑定 | 当 SQL 语句中的对象被修改时,仅执行过的语句在下次运行时需要重新编译 |
根据编程方法的不同,DB2 的应用程序开还可以分为嵌入式 SQL 编程和非嵌入式编程 :
嵌入式 SQL 编程将 SQL 语句嵌入到宿主语言 (host) 的程序中,例如 C/C++ 程序。因为宿主语言不识别 SQL 语句,先要对程序进行预编译,把 SQL 语句转变为对 DB2 服务的调用,并重写源代码,最后再使用宿主语言的编译器对应用程序进行编译。嵌入式 SQL 都需要被绑定到特定的数据库中,可分为嵌入式静态 SQL 和嵌入式动态 SQL。
非嵌入式应用程序不需要预编译,且方法较多,如 CLI、JDBC、ODBC、ADO.NET 等等,这些方法中都使用动态 SQL。表 2列举了常见的 DB2 编程接口。
表 2. DB2 的编程接口一览
编程接口 | 静态 / 动态 | 是否为嵌入式 |
嵌入式 SQL | 静态和动态 | 是 |
DB2 CLI | 动态 | 否 |
SQLJ | 静态 | 是 |
JDBC | 动态 | 否 |
ADO.NET,OLE DB | 动态 | 否 |
Perl DBI | 动态 | 否 |
PDO(PHP 数据对象 ) | 动态 | 否 |
在下面的几个章节中,我们将陆续从使用角度上描述静态和动态 SQL 在各种编程接口中的应用,并运用一些实例来介绍在具体的场景中如何选择。
静态 SQL 应用
嵌入式静态 SQL
无论是嵌入式静态 SQL 还是嵌入式动态 SQL,都需要先进行预编译,并绑定到特定的数据库。DB2 的嵌入式 SQL 应用程序支持以下几种语言:C,C++,COBOL,FORTRAN 和 REXX™。
对嵌入式静态 SQL 而言,只能使用编译时确定的 SQL 语句和访问编译时已经存在的数据库对象。清单 1是一个查询表的例子,使用 C 语言 :
清单 1. 嵌入式静态 SQL 的 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);
使用下面的命令,对上述代码进行预编译和编译:
清单 2. 嵌入式静态 SQL 的编译命令
# 需要先连接数据库
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 语句的代码片断。
清单 3. 简单的 SQLJ 程序片断
// 载入相关包
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所示。
清单 4. 在 SQLJ 使用 ExecutionContext
// 分配存储执行上下文的变量
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 结构描述结果集中一个字段的信息。
图 1. SQLDA 结构
清单 5展示了如何使用 SQLDA 结构和 DESCRIBE 语句处理 SELECT 语句。
清单 5. 使用 SQLDA 结构和 DESCRIBE 语句的伪代码
//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 语句。
清单 6. CLI 应用程序片断
/* 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展示了这个处理过程。
图 2. DB2 CLI 应用程序处理 SELECT 语句流程
与嵌入式动态 SQL 应用相比,CLI 程序的灵活性更强。表 3列举了 CLI 应用程序和嵌入式动态 SQL 应用程序的比较。
表 3. CLI 应用程序和嵌入式动态 SQL 应用程序的比较
CLI | 嵌入式动态 SQL |
DB2 CLI 应用程序使用 API 函数发送 SQL 语句,应用程序的编译、连接和运行独立于特定数据库,即无需预编译,也不需要绑定到某个数据库实例。 | 嵌入式动态 SQL 应用程序需要预编译,并且需要绑定到特定的数据库实例。 |
CLI 提供的滚动游标(scroll cursor)支持前、后向移动一行或者多行记录,而移动的起点可以是第一行、最后一行或者之前存储的位置。 | 只支持顺序前向读取游标,并使用 FETCH 语句取得记录。 |
可以获取存储过程调用返回的结果集。并支持多种 DB2 服务器。 | 可以获取存储过程的输出型或输入 - 输出型参数的值,但不能获取存储过程返回的结果集。 |
只支持 C/C++ 语言。 | 支持 C/C++, FORTRAN、COBOL 和 Java(SQLJ)。 |
使用 CLI API 函数 SQLDescribeCol()、SQLAttribute() 描述未知 SQL 语句结果集的信息,包括结果集字段长度、类型、精度等信息。 | 使用 SQLDA 结构和 DESCRIBE 语句获取未知 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 程序片断。
清单 7. JDBC 应用程序片断
try{
stmt = con.prepareCall("select * from table1");
stmt.execute();
rs = stmt.getResultSet();
while (rs!=null && 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 服务器上,并作为守护程序或者批处理程序运行。另外,很多业务规则例如计费标准可能存放在单独得小型数据库系统中。
图 3. 电信运营系统结构示意图
计费程序的工作主要包括:从话单数据库读新话单,从业务配置数据库读取计费标准,计算话单的费用和优惠,将计费完毕的话单写回话单数据库。
由于计费标准的选择经常和日期等变化的信息相关,程序中会存在一些不固定的 SQL 语句,因此静态 SQL 应用并不适用。另外,除了访问结构稳定的话单数据库,计费程序也需要访问一些变化概率较大的小型数据库,例如计费标准可能会因为运营商营销策略的调整而发生变化。如果使用嵌入式 SQL,就必须将应用程序和数据库进行绑定,这将失去灵活性。最后,为了快速对大量的话单进行计费,计费程序对性能也有较高要求,所以 CLI 应用程序比 JDBC 应用更适合计费程序。
场景 3
在基于 BS 结构的电子商务应用中,浏览器、应用服务器(包括 Web 服务器)和数据库构成了系统的三个要素。数据库为电子商务提供了事务管理、数据持久化、安全认证、联机备份等各方面的支持,是电子商务系统中的关键部分。图 4显示了一个简单的电子商务系统结构示意图。
图 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 中的电子商务案例
当然,所有的原则都是经验性的,真正的选择还取决于应用的实际情况。在时间允许的情况下,对各种选择进行比较测试也是可以考虑的方法。
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
更多精彩
赞助商链接