DB2数据库SQL语法参考手册

1、DB2数据库SQL语法参考手册DB2 提供了关连式资料库的查询语言 SQL (Structured Query Language)，是一种非常口语化、既易学又易懂的语法。此一语言几乎是每个资料库系统都必须提供的，用以表示关连式的操作，包含了资料的定义(DDL)以及资料的处理(DML)。SQL原来拼成SEQUEL，这语言的原型以"系统 R"的名字在 IBM 圣荷西实验室完成，经过IBM内部及其他的许多使用性及效率测试，其结果相当令人满意，并决定在系统R 的技术基础发展出来 IBM 的产品。而且美国国家标准学会(ANSI)及国际标准化组织(ISO)在1987遵循一个几乎是以 I

2、BM SQL 为基础的标准关连式资料语言定义。一、资料定义 DDL(Data Definition Language)资料定语言是指对资料的格式和形态下定义的语言，他是每个资料库要建立时候时首先要面对的，举凡资料分哪些表格关系、表格内的有什麽栏位主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。1.建表格:CREATE TABLE table_name(column1 DATATYPE NOT NULL NOT NULL PRIMARY KEY,column2 DATATYPE NOT NULL,.)说明:DATATYPE是资料的格式，详见表。NUT NULL可不可以允许

3、资料有空的(尚未有资料填入)。PRIMARY KEY是本表的主键。2.更改表格ALTER TABLE table_nameADD COLUMN column_name DATATYPE说明:增加一个栏位(没有删除某个栏位的语法。ALTER TABLE table_nameADD PRIMARY KEY (column_name)说明:更改表得的定义把某个栏位设为主键。ALTER TABLE table_nameDROP PRIMARY KEY (column_name)说明:把主键的定义删除。3.建立索引CREATE INDEX index_name ON table_name (column

4、_name)说明:对某个表格的栏位建立索引以增加查询时的速度。4.删除DROP table_nameDROP index_name二、的资料形态 DATATYPEssmallint16 位元的整数。interger32 位元的整数。decimal(p,s)p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值,s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。float32位元的实数。double64位元的实数。char(n)n 长度的字串，n不能超过 254。varchar(n)长度不固定且其最大长度为 n 的字串，n不能超过 4000。

5、graphic(n)和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。vargraphic(n)可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。date 包含了 年份、月份、日期。time包含了 小时、分钟、秒。timestamp包含了 年、月、日、时、分、秒、千分之一秒。三、资料操作 DML (Data Manipulation Language)资料定义好之後接下来的就是资料的操作。资料的操作不外乎增加资料(insert)、查询资料(query)、更改资料(update) 、删除资

6、料(delete)四种模式，以下分 别介绍他们的语法:1.增加资料:INSERT INTO table_name (column1,column2,.)valueS ( value1,value2, .)说明:(1)若没有指定column 系统则会按表格内的栏位顺序填入资料。(2)栏位的资料形态和所填入的资料必须吻合。(3)table_name 也可以是景观 view_name。INSERT INTO table_name (column1,column2,.)SELECT columnx,columny,. FROM another_table说明:也可以经过一个子查询(subquery)把

7、别的表格的资料填入。2.查询资料:基本查询SELECT column1,columns2,.FROM table_name说明:把table_name 的特定栏位资料全部列出来SELECT *FROM table_nameWHERE column1 = * AND column2 > yyy OR column3 <> zzz说明:(1)'*'表示全部的栏位都列出来。(2)WHERE 之後是接条件式，把符合条件的资料列出来。SELECT column1,column2FROM table_nameORDER BY column2 DESC说明:ORDER BY

8、 是指定以某个栏位做排序，DESC是指从大到小排列，若没有指明，则是从小到大排列,组合,查询组合查询是指所查询得资料来源并不只有单一的表格，而是联合一个以上的表格才能够得到结果的。SELECT *FROM table1,table2WHERE table1.colum1=table2.column1说明:(1)查询两个表格中其中 column1 值相同的资料。(2)当然两个表格相互比较的栏位，其资料形态必须相同。(3)一个复杂的查询其动用到的表格可能会很多个。整合性的查询:SELECT COUNT (*) FROM table_nameWHERE column_name = xxx说明:查询符

9、合条件的资料共有几笔。SELECT SUM(column1)FROM table_name说明:(1)计算出总和，所选的栏位必须是可数的数字形态。(2)除此以外还有 AVG() 是计算平均、MAX()、MIN()计算最大最小值的整合性查询。SELECT column1,AVG(column2)FROM table_nameGROUP BY column1HAVING AVG(column2) > xxx说明:(1)GROUP BY: 以column1 为一组计算 column2 的平均值必须和 AVG、SUM等整合性查询的关键字一起使用。(2)HAVING : 必须和 GROUP BY

10、一起使用作为整合性的限制。复合性的查询SELECT *FROM table_name1WHERE EXISTS (SELECT *FROM table_name2WHERE conditions )说明:(1)WHERE 的 conditions 可以是另外一个的 query。(2)EXISTS 在此是指存在与否。SELECT *FROM table_name1WHERE column1 IN (SELECT column1 FROM table_name2WHERE conditions )说明:(1)IN 後面接的是一个集合，表示column1 存在集合里面。(2)SELECT 出来的资料

11、形态必须符合 column1。其他查询SELECT *FROM table_name1WHERE column1 LIKE 'x%'说明:LIKE 必须和後面的'x%' 相呼应表示以 x为开头的字串。SELECT *FROM table_name1WHERE column1 IN ('xxx','yyy',.)说明:IN 後面接的是一个集合，表示column1 存在集合里面。SELECT *FROM table_name1WHERE column1 BETWEEN xx AND yy说明:BETWEEN 表示 column1 的值

12、介於 xx 和 yy 之间。3.更改资料:UPDATE table_nameSET column1='xxx'WHERE conditoins说明:(1)更改某个栏位设定其值为'xxx'。(2)conditions 是所要符合的条件、若没有 WHERE 则整个 table 的那个栏位都会全部被更改。4.删除资料:DELETE FROM table_nameWHERE conditions说明:删除符合条件的资料。说明:关于WHERE条件后面如果包含有日期的比较，不同数据库有不同的表达式。具体如下:(1)如果是ACCESS数据库，则为:WHERE mydate&g

13、t;#2000-01-01#(2)如果是ORACLE数据库，则为:WHERE mydate>cast('2000-01-01' as date)WHERE mydate>to_date('2000-01-01','yyyy-mm-dd')在Delphi中写成:thedate='2000-01-01'query1.SQL.add('select * from abc where mydate>cast('+''''+thedate+'''

14、9;+' as date)');如果比较日期时间型，则为:WHERE mydatetime>to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss')DB2基础: 编码DB2 SQL以获得最佳性能简介当要保证用 IBM DB2® Universal Database(DB2 UDB)和 Borland® 工具(如 Delphi、C+Builder 或 Kylix)构建的企业应用程序拥有最优性能时，程序员可以利用 DB2 优化器的能力来处理即使是“难以处理的”SQL 语句并

15、给出有效的存取路径。尽管如此，拙劣编码的 SQL 和应用程序代码仍可能给您带来性能问题，通过学习几条基本准则可以轻易地避免这些问题。我将向您演示 DB2 优化器的工作方式，并提供编写能发挥优化器最大效率的 SQL 的准则。但即使拥有了 DB2 的优化能力，编写有效的 SQL 语句仍可能是一件复杂的事情。如果程序员和开发人员还不熟悉关系数据库环境，这件事就尤其显得棘手。因此，在我们深入研究编码 SQL 以获得最佳性能的细节之前，先花一些时间来回顾 SQL 基础知识。基础知识由于 SQL 与过程化语言不同，它提供了更高的抽象级别，因此它可以让程序员把精力集中到他们需要 什么样的数据，而不是 如何检

16、索数据。您不必使用嵌入式数据导航指令来编码 SQL。DB2 会分析 SQL，并“在幕后”制定数据导航指令。这些数据导航指令叫作 存取路径。让 DBMS 确定到数据的最优存取路径解除了程序员肩上沉重的负担。此外，数据库可以更好地理解它存储的数据的状态，从而可以生成到数据的更有效和动态的存取路径。其结果就是适当使用的 SQL 可以用于更快的应用程序开发。另一个 SQL 特性是它不仅仅是一种查询语言。您还可以使用它来定义数据结构;控制对数据的访问;以及插入、修改和删除数据的发生。通过提供一种公共语言，SQL 简化了 DBA、系统程序员、应用程序员、系统分析员和最终用户之间的通信。当项目的所有参与者都

17、使用同一种语言时，他们之间所建立起来的协作就可以减少整体系统开发时间。历史证明，保证 SQL 成功的最重要的一个特性就是它使用类似英语的语法轻松地检索数据的能力。理解这种语言比理解数据页面的结构和程序源代码要容易得多：SELECT LASTNAMEFROM EMPWHERE EMPNO = '000010'想想看：当访问文件中的数据时，程序员必须编码指令来打开文件、开始一个循环、读取记录、检查 EMPNO 字段是否等于适当的值、检查文件结尾、回到循环的开头等。SQL 本来就是非常灵活的。它使用自由格式的结构，该结构可以让用户开发 SQL 语句来适合他们的需要。DBMS 在执行之

18、前会分析每个 SQL 请求，以检查语法是否正确和优化该请求。SQL 语句不需要从任何给定的列中开始，您可以将它们串在一行中，或者把它们拆成几行。例如，以下这条单行的 SQL 语句与我前面使用的三行示例等价：SELECT LASTNAME FROM EMP WHERE EMPNO = '000010'SQL 的另一个灵活特性是您可以用许多形式不同但功能等价的方法来制定一个请求。例如：SQL 可以连接表或嵌套查询。您始终可以将嵌套查询转换成等价的连接。您可以在大量的函数和谓词中看到这一灵活性的其它示例。具有等价功能的特性的示例包括：· BETWEEN vs <= /

19、 >= · IN vs 一系列和 OR 配合的谓词 · INNER JOIN vs FROM 子句中串在一起并用逗号分隔的表 · OUTER JOIN vs 带有 UNION 的简单 SELECT 和相关的子查询 · CASE 表达式 vs 复杂的 UNION ALL 语句SQL 展示的这一灵活性并不总是称心的，因为形式不同但功能等价的 SQL 公式可以提供非常不同的性能。我将在本文的以后部分讨论该灵活性所造成的结果，并提供开发有效的 SQL 的准则。如我所说的，SQL 指定了要检索或操作什么数据，但没有指定数据库如何完成这些任务。这就使 SQL

20、本身变得很简单。如果您能够记得关系数据库的一次处理一个集合(set-at-a-time)的特点，您就开始掌握 SQL 的本质和性质了。一条 SQL 语句可以作用于多行。作用于一组数据而不需要建立如何检索和操作数据的能力将 SQL 定义成非过程化语言因为 SQL 是一种非过程化语言，所以一条语句可以代替一系列过程。同样，由于 SQL 使用集合级别的处理以及 DB2 优化查询来确定数据导航逻辑，所以这是可能的。有时，如果不使用 SQL 语句，一条或两条 SQL 语句可以完成的任务就需要完整的过程化程序来完成。优化器优化器是 DB2 的心脏和灵魂。它分析 SQL 语句并确定可以满足每条语句的最有效的

21、存取路径(请参阅图 1)。DB2 UDB 通过解析 SQL 语句来确定必须访问哪些表和列，从而完成该操作。DB2 优化器然后查询存储在 DB2 系统目录中的系统信息和统计信息，以确定完成满足 SQL 请求所必需的任务的最佳方法。图 1. 运行中的 DB2 优化优化器在功能上等价于一个专家系统。专家系统是一个标准规则集合，当与情境数据组合时，它返回一个“专家”意见。例如，医学专家系统采用一个规则集合，用来确定哪些药可以用于哪些疾病，将规则集与描述疾病症状的数据组合，并将知识库应用于输入症状的列表。DB2 优化器会根据存储在 DB2 系统目录中的情境数据和 SQL 格式的查询输入来生成对数据检索方

22、法的专家意见。在 DBMS 中优化数据访问的概念是 DB2 最强大的能力之一。请记住，您访问 DB2 数据时应告诉 DB2 要检索什么，而不是如何检索。无论数据实际上是如何存储和操作的，DB2 和 SQL 都可以访问该数据。从物理存储特征中分离出访问标准叫作物理数据独立性。DB2 的优化器是完成该物理数据独立性的组件。如果您不要索引，DB2 仍然能够访问数据(尽管效率会降低)。如果将一列添加到正在被访问的表中，DB2 仍然可以在不更改程序代码的情况下操作数据。因为到 DB2 数据的物理存取路径并不是由程序员在应用程序中编码的，而是由 DB2 生成的，所以这种情况是完全有可能发生的。这个特点与非

23、 DBMS 系统非常不同，在那种系统中，程序员必须知道数据的物理结构。如果有索引，程序员就必须编写适当的代码来使用该索引。如果某人删除了索引，程序就不能工作，除非程序员进行更改。而使用 DB2 和 SQL 就不必如此。这一灵活性完全归功于 DB2 自动优化数据操作请求的能力。优化器根据许多信息执行复杂的计算。要使优化器的工作方式直观化，可以将优化器想象成执行一个四步骤的过程：1、接收并验证 SQL 语句的语法。2、分析环境并优化满足 SQL 语句的方法。3、创建计算机可读指令来执行优化的 SQL。4、执行指令或存储它们以便将来执行。这个过程的第二步是最有趣的。优化器怎样决定如何以它的方式执行您

24、可以发送的大量 SQL 语句?优化器有许多类型的优化 SQL 的策略。它如何选择在优化存取路径中使用这些策略中的哪一个?IBM 并没有发布优化器如何确定最佳存取路径的真正和深入的详细信息，但优化器是一个 基于成本的优化器。这意味着优化器将始终尝试为每个查询制定减少总体成本的存取路径。要实现这个目标，DB2 优化器会应用查询成本公式，该公式对每条可能的存取路径的四个因素进行评估和权衡：CPU 成本、I/O 成本、DB2 系统目录中的统计信息和实际的 SQL 语句。在DB2中提高Insert性能的技巧INSERT 处理过程概述首先让我们快速地看看insert一行时的处理步骤。这些步骤中的每一步都有

25、优化的潜力，对此我们在后面会一一讨论。1、在客户机准备 语句。对于动态 SQL，在语句执行前就要做这一步，此处的性能是很重要的;对于静态 SQL，这一步的性能实际上关系不大，因为语句的准备是事先完成的。2、在客户机，将要插入的行的各个 列值组装起来，发送到 DB2 服务器。3、DB2 服务器确定将这一行插入到哪一页中。4、DB2 在 用于该页的缓冲池中预留一个位置。如果 DB2 选定的是一个已有的页，那么就需要读磁盘;如果使用一个新页，则要在表空间(如果是SMS，也就是系统管理存储的表空间)中为该页物理地分配空间。插入了新行的每一页最后都要从缓冲池写入到磁盘。5、在目标页中对该行进行格式化，并

26、获得该行上的一个X(exclusive，独占的) 行锁。6、将反映该 insert 的一条记录写入到日志缓冲区中。7、最后提交包含该 insert 的事务，如果这时日志缓冲区中的记录还没有被写入日志文件的话，则将这些记录写到日志文件中。此外，还可能发生很多类型的附加处理，这取决于数据库配置，例如，索引或触发器的存在。这种额外的处理对于性能来说也是意义重大的，我们在后面会讨论到。insert 的替代方案在详细讨论 insert 的优化之前，让我们先考虑一下 insert 的两种替代方案：load 和 import。import 实用程序实际上是 SQL INSERT 的一个前端，但它的某些功能对

27、于您来说也是有用的。load 也有一些有用的额外功能，但是我们使用 load 而不使用 insert 的主要原因是可以提高性能。load 直接格式化数据页，而避免了由于插入导致的对每一行进行处理的大部分开销(例如，日志记录在这里实际上是消除了)。而且，load 可以更好地利用多处理器机器上的并行性。在 V8 load 中有两个新功能，它们对于 load 成为 insert 的替代方案有着特别的功效，这两个功能是：从游标装载和从调用层接口(CLI)应用程序装载。从游标装载这种方法可用于应用程序的程序代码(通过 db2Load API)，或用于 DB2 脚本。下面是后一种情况的一个例子：decla

28、re staffcursor cursor forselect * from staff;load from staffcursor of cursor insert into myschema.new_staff;这两行可以用下面一行替代：insert into myschema.new_staff select * from staff同等效的 INSERT . SELECT 语句相比，从游标装载几乎可以提高 20% 的性能。从 CLI 装载这种方法显然只限于调用层接口(CLI)应用程序，但是它非常快。这种技巧非常类似于数组插入，DB2 附带了这样的示例，使用 load 时的速度是使用经过

29、完全优化的数组插入时的两倍，几乎要比未经优化的数组插入快 10 倍。所有 insert 可以改进的地方让我们看看插入处理的一些必要步骤，以及我们可以用来优化这些步骤的技巧。1. 语句准备作为一条 SQL 语句，INSERT 语句在执行之前必须由 DB2 进行编译。这一步骤可以自动发生(例如在 CLP 中，或者在一次 CLI SQLExecDirect 调用中)，也可以显式地进行(例如，通过一条 SQL Prepare、CLI SQLPrepare 或 JDBC prepareStatement 语句)。该编译过程牵涉到授权检查、优化，以及将语句转化为可执行格式时所需的其他一些活动。在编译语句时

30、，语句的访问计划被存储在包缓存中。如果重复地执行相同的 INSERT 语句，则该语句的访问计划(通常)会进入到包缓存中，这样就免除了编译的开销。然而，如果 insert 语句对于每一行有不同的值，那么每一条语句都将被看成是惟一的，必须单独地进行编译。因此，将像下面这样的重复语句：insert into mytable values (1, 'abc')insert into mytable values (2, 'def')等等，换成带有参数标记的语句，一次准备，重复执行，这样做是十分可取的：insert into mytable values (?, ?)使用

31、参数标记可以让一系列的 insert 的运行速度提高数倍。(在静态 SQL 程序中使用主机变量也可以获得类似的好处。)2. 发送列值到服务器可以归为这一类的优化技巧有好几种。最重要的一种技巧是在每条 insert 语句中包括多行，这样就可以避免对于每一行都进行客户机-服务器通信，同时也减少了 DB2 开销。可用于多行插入的技巧有：· 在 VALUES 子句中包含多行的内容。例如，下面的语句将插入三行：INSERT INTO mytable VALUES (1, 'abc'), (2, 'def'), (3, 'ghi') ·

32、在 CLI 中使用数组插入(array insert)。这需要准备一条带参数标记的 INSERT 语句，定义一个用于存储要插入的值的数组，将该数组绑定到参数标记，以及对于每个数组中的一组内容执行一次 insert。而且，示例程序 sqllib/samples/cli/tbload.c 提供了数组插入的基本框架(但是执行的是 CLI LOAD)。从不使用数组改为使用包含 100 行的数组，可以将时间缩短大约 2.5 倍。所以应该尽可能地使用包含至少 100 行的数组。 · 在 JDBC 中使用批处理操作。这跟 CLI 中的数组插入一样，基于相同的概念，但是实现细节有所不同。当通过 pr

33、epareStatement 方法准备了 insert 语句之后，剩下的步骤是针对每一列调用适当的 setXXXX 方法(例如，setString 或 setInt)，然后是 addBatch。对于要插入的每一行，都要重复这些步骤，然后调用 executeBatch 来执行插入。要查看这方面的例子，请参阅“参考资料”一节中的 JDBC Tutorial。 · 使用 load 将数据快速地装入到一个 staging 表中，然后使用 INSERT . SELECT 填充主表。(通过这种方法节省下来的代价源于 load 的速度非常快，再加上 INSERT . SELECT 是在 DB2 内

34、(在服务器上)传输数据的，从而消除了通信上的代价。一般情况下我们不会使用这种方法，除非在 INSERT . SELECT 中还要另外做 load 无法完成的处理。如果不可能在一条 insert 语句中传递多行，那么最好是将多条 insert 语句组成一组，将它们一起从客户机传递到服务器。(不过，这意味着每条 insert 都包含不同的值，都需要准备，因而其性能实际上要比使用参数标记情况下的性能更差一些。)将多条语句组合成一条语句可以通过 Compound SQL 来实现：· 在 SQL 中，复合语句是通过 BEGIN ATOMIC 或 BEGIN COMPOUND 语句创建的。 &#

35、183; 在 CLI 中，复合语句可以通过 SQLExecDirect 和 SQLExecute 调用来建立。对于 DB2 V8 FixPak 4，另一种生成复合语句的方法是在(对一条预处理语句)发出多个 SQLExecute 调用之前设置语句属性 SQL_ATTR_CHAINING_BEGIN，并在调用之后设置语句属性 SQL_ATTR_CHAINING_END。下面是关于该话题的其他一些建议：· 如果可能的话，让客户机与要存取的数据库使用相同的代码页，以避免在服务器上的转换代价。数据库的代码页可以通过运行“get db cfg for ”来确定。 · 在某些情况下，CL

36、I 会自动执行数据类型转换，但是这样同时也会带来看不见的(小小的)性能损耗。因此，尽量使插入值直接处于与相应列对应的格式。 · 将应用程序中与插入相关的设置开销最小化。例如，当在 CLI 中使用数组插入时，对于整个一组插入，应该尽量保证对于每一列只执行一次 SQLBindParameter，而不是对每一组数组内容都执行一次。对于个体来说，这些调用的代价并不高，但是这些代价是累积的。3. 找到存储行的地方DB2 使用三种算法中的一种来确定将行插入到哪里。(如果使用了多维群集(Multi-dimensional Clustering，MDC)，则另当别论，我们在这里不予讨论。)缺省模式是

37、，DB2 搜索散布在表的各页上的自由空间控制记录(Free Space Control Records，FSCR)，以找到有足够自由空间存放新行的页。显然，如果每页上的自由空间都比较少的话，就要浪费很多的搜索时间。为了应付这一点， DB2 提供了 DB2MAXFSCRSEARCH 注册表变量，以便允许将搜索范围限制为少于缺省的 5 页。当表是通过 ALTER TABLE 以 APPEND 模式放置时，就要使用第二种算法。这样就完全避免了 FSCR 搜索，因为只需简单地将行直接放到表的末尾。当表有群集索引(clustering index)时，就要用到最后一种算法。在这种情况下，DB2 试图将每

38、一行插入到有相似键值的一页中。如果那一页没有空间了，DB2 就会尝试附近的页，如果附近的页也没有空间，DB2 就进行 FSCR 搜索。如果只考虑插入时间的优化，那么使用 APPEND 模式对于批量插入是最快的一种方法，但是这种方法的效果远不如我们这里讨论的很多其他方法那么成效显著。第二好的方法应该是采用缺省算法，但是，如果在最佳环境中，更改 DB2MAXFSCRSEARCH 的值影响很小，而在一个 I/O 约束较少的环境中，这种更改所造成的影响就比较可观了。如果有群集索引，则对 insert 的性能会有很大的负面影响，这一点也不惊奇，因为使用群集索引的目的就是通过在插入时做额外的工作来提高查询

39、(即 select)性能的。如果的确需要群集索引，那么可以通过确保有足够的自由空间来使其对插入的影响降至最小：使用 ALTER TABLE 增加 PCTFREE，然后使用 REORG 预留自由空间。不过，如果允许太多自由空间的存在，则可能导致查询时需要读取额外的页，这反而大大违反了使用群集索引的本意。另一种选择是，在批量插入之前先删除群集索引，而后再重新创建群集索引，也许这是最优的方法(创建群集索引的开销跟创建常规索引的开销差不多，都不是很大，只是在插入时有额外的开销)。4. 缓冲池、I/O 和页清除每一条 insert 在执行时，都是先将新行存储在一个页中，并最终将那个页写到磁盘上。一旦像前

40、面讨论的那样指定了页，那么在将行添加到该页之前，该页必须已经在缓冲池中。对于批量插入，大部分页都是最新指派给表的，因此让我们关注一下对新页的处理。如果表在系统管理存储的(System Managed Storage，SMS)表空间中，当需要新页时，缺省情况下是从文件系统中分别为每一页分配空间。但是，如果对数据库运行了 db2empfa 命令，那么每个 SMS 表空间就会为新页一次性分配一个区段。我们建议运行 db2empfa 命令，并使用 32 页的区段。对于数据库管理的存储(Database Managed Storage，DMS)表空间，空间是在创建表空间时就预先分配的，但是页的区段则是在

41、插入处理过程中指派给表的。与 SMS 相比，DMS 对空间的预分配可以提高大约 20% 的性能 - 使用 DMS 时，更改区段大小并没有明显的效果。如果表上有索引，则对于每个插入的行，都要添加一个条目到每条索引。这要求在缓冲池中存在适当的索引页。晚些时候我们将讨论索引的维护，但是现在只需记住，插入时对缓冲池和 I/O 的考虑也类似地适用于索引页，对于数据页也是一样。随着插入的进行，越来越多的页中将填入被插入的行，但是，DB2 不要求在 insert 或 Commit 后将任何新插入的或更新后的数据或索引写入到磁盘。(这是由于 DB2 的 writeahead 日志记录算法。但是有一个例外，这将

42、在关于日志记录的小节中论述到。)然而，这些页需要在某一时刻写到磁盘上，这个时刻可能会在数据库关闭时才会轮到。一般来说，对于批量插入，您会希望积极地进行 异步页清除(asynchronous page cleaning)，这样在缓冲池中就总有可用于新页的空余位置。页清除率，或者说总缺页率，可能导致计时上的很大不同，使得性能比较容易产生误解。例如，如果使用 100,000 页的缓冲池，并且不存在页清除，则批量插入在结束前不会有任何新的或更改过的(“脏的”)页写到磁盘上，但是随后的操作(例如选择，甚至乎关闭数据库)都将被大大推迟，因为这时有至多 100,000 个在插入时产生的脏页要写到磁盘上。另一

43、方面，如果在同一情况下进行了积极的页清除，则批量插入过程可能要花更长的时间，但是此后缓冲池中的脏页要少一些，从而使得随后的任务执行起来性能更佳。至于那些结果中到底哪个要更好些，我们并不是总能分得清，但是通常来说，将所有脏页都存储在缓冲池中是不可能的，所以为了取得最佳性能，采取有效的页清除是有必要的。为了尽可能好地进行页清除：· 将 CHNGPGS_THRESH 数据库配置参数的值从缺省的 60 减少到 5 这么低。这个参数决定缓冲池中脏页的阈值百分比，当脏页达到这个百分比时，就会启动页清除。 · 尝试启用注册表变量 DB2_USE_ALTERNATE_PAGE_CLEANI

44、NG(在 DB2 V8 FixPak 4 中最新提供)。通过将这个变量设置成 ON，可以为页清除提供一种比缺省方法(基于 CHNGPGS_THRESH 和 LSN 间隙触发器)更积极的方法。我没有评测过其效果。请参阅 FixPak 4 Release Notes 以了解这方面的信息。 · 确保 NUM_IOCLEANERS 数据库配置参数的值至少等于数据库中物理存储设备的数量。至于 I/O 本身，当需要建立索引时，可以通过使用尽可能大的缓冲池来将 I/O 活动减至最少。如果不存在索引，则使用较大的缓冲池帮助不大，而只是推迟了 I/O。也就是说，它允许所有新页暂时安放在缓冲池中，但是最

45、终仍需要将这些页写到磁盘上。当发生将页写到磁盘的 I/O 时，通过一些常规的 I/O 调优步骤可以加快这一过程，例如：· 将表空间分布在多个容器(这些容器映射到不同磁盘)。 · 尽可能使用最快的硬件和存储管理配置，这包括磁盘和通道速度、写缓存以及并行写等因素。 · 避免 RAID5(除非是与像 Shark 这样有效的存储设备一起使用)。5. 锁缺省情况下，每一个插入的行之上都有一个 X 锁，这个锁是在该行创建时就开始有的，一直到 insert 被提交。有两个跟 insert 和锁相关的性能问题：· 为获得和释放锁而产生的 CPU 开销。 · 可

46、能由于锁冲突而导致的并发问题。对于经过良好优化的批量插入，由获得每一行之上的一个 X 锁以及后来释放该锁引起的 CPU 开销是比较可观的。对于每个新行之上的锁，惟一可以替代的是表锁(DB2 中没有页锁)。当使用表锁时，耗时减少了 3%。有 3 种情况可以导致表锁的使用，在讨论表锁的缺点之前，我们先用一点时间看看这 3 种情况：· 运行 ALTER TABLE LOCKSIZE TABLE。这将导致 DB2 为随后使用该表的所有 SQL 语句使用一个表锁，直到 locksize 参数改回到 ROW。 · 运行 LOCK TABLE IN EXCLUSIVE MODE。这将导致

47、表上立即上了一个 X 锁。注意，在下一次提交(或回滚)的时候，这个表将被释放，因此，如果您要运行一个测试，测试中每 N 行提交一次，那么就需要在每次提交之后重复执行 LOCK TABLE。 · 使用缺省锁，但是让 LOCKLIST 和 MAXLOCKS 数据库配置参数的值比较小。当获得少量的行锁时，行锁就会自动地逐渐升级为表锁。当然，所有这些的缺点就在于并发的影响：如果表上有一个 X 锁，那么其他应用程序除非使用了隔离级别 UR(未提交的读)，否则都不能访问该表。如果知道独占访问不会导致问题，那么就应该尽量使用表锁。但是，即使您坚持使用行锁，也应记住，在批量插入期间，表中可能存在数千

48、个有 X 锁的新行，所以就可能与其他使用该表的应用程序产生冲突。通过一些方法可以将这些冲突减至最少：· 确保锁的升级不会无故发生。您可能需要加大 LOCKLIST 和/或 MAXLOCKS 的值，以允许插入应用程序有足够的锁。 · 对于其他的应用程序，使用隔离级别 UR。 · 对于 V8 FixPak 4，或许也可以通过 DB2_EVALUNCOMMITTED 注册表变量来减少锁冲突：如果将该变量设置为 YES，那么在很多情况下，只能获得那些符合某个谓词的行上的锁，而并不是获得被检查的所有行上的锁。 · 发出一个 COMMIT 命令以释放锁，因此如果更频

49、繁地提交的话就足以减轻锁冲突的负担。注意· 在 V7 中，存在涉及 insert 和键锁的并发问题，但是在 V8 中，由于提供了 type-2 索引，这些问题实际上已经不见了。如果要迁移到 V8 中来，那么应该确保使用带 CONVERT 关键字的 REORG INDEXES 命令，以便将索引从 type-1 转换为 type-2。 · 在 V7 中，插入过程中可能使用 W 或 NW 锁，但是在 V8 中只有在使用了 type-1 索引或者隔离级别为 RR 的情况下才会出现这两种锁。因此，应尽可能避免这两种情况。 · 一条 insert 所据有的锁(通常是一个 X

50、锁)通常不会受隔离级别的影响。例如，使用隔离级别 UR 不会阻止从插入的行上获得锁。然而，如果使用了 INSERT . SELECT，则隔离级别将影响从 SELECT 获得的锁。6. 日志记录缺省情况下，每条 insert 都会被记录下来，以用于恢复。日志记录首先被写到内存中的日志缓冲池，然后再写到日志文件，通常是在日志缓冲池已满或者发生了一次提交时写到日志文件的。对批量插入的日志记录的优化实际上就是最小化日志记录写的次数，以及使写的速度尽可能快。这里首先考虑的是日志缓冲池的大小，这由数据库配置参数 LOGBUFSZ 来控制。该参数缺省值为 8 页或 32 K，这与大多数批量插入所需的理想日志

51、缓冲池大小相比要小些。举个例子，对于一个批量插入，假设对于每一行的日志内容有 200 字节，则在插入了 160 行之后，日志缓冲池就将被填满。如果要插入 1000 行，因为日志缓冲池将被填满几次，再加上提交，所以大概有 6 次日志写。如果将 LOGBUFSZ 的值增加到 64 页(256K)或者更大，缓冲池就不会被填满，这样的话对于该批量插入就只有一次日志写(在提交时)。通过使用更大的 LOGBUFSZ 可以获得大约 13% 的性能提升。较大日志缓冲池的不利之处是，紧急事故恢复所花的时间可能要稍微长一点。减少日志写的另一种可能性是对新行要插入到的那个表使用“ALTER TABLE ACTIVATE NOT LOGGED INITIALLY”(NLI)。如果这样做了，那么在该工作单元内不会记录任何 insert 操作，但是这里存在两个与