如何用代理键实现 DB2 UDB 主键

本文描述了三种实现： 使用传统方法。使用键管理器。使用DB2 UDB 特性。代理键也叫 内键（internal key）。当创建一个表时，可以添加一个额外的列作为代理键。这个列应该是 NOT NULL，并且没有商业意义。可以将该代理列指定为主键列。例如可以有一个数字代理列。代理键的值从某一个数字开始，例如 "1"，以这个数字作为该列在表中第一行的值，之后的每一行中该列的值都按 1 递增。 例如，如果我们有表 EMPLOYEE： CREATE TABLE EMPLOYEE ( FIRSTNAME CHAR(64), LASTNAME CHAR(64), SALARY DECIMAL(10, 2)) 那么可以添加一个代理键列 SERIALNUMBER，并将其指定为主键列。这样，这个表的定义就变为： CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL, FIRSTNAME CHAR(64), LASTNAME CHAR(64), SALARY DECIMAL(10, 2), PRIMARY KEY (SERIALNUMBER)) 那么，怎样将惟一的值赋给每一行的 SERIALNUMBER 列呢？首先需要为代理键生成惟一的值。下面我将讨论三种可行的解决方案。回页首使用传统方法解决方案的思想传统方法是使用简单的 SQL 或触发器生成惟一的值。示例以表EMPLOYEE 为例。您可以在 INSERT 语句中实现代理键生成函数： INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME, SALARY) VALUES ((SELECT MAX(SERIALNUMBER) FROM EMPLOYEE)+1, ‘John’, ‘Smith’, 999.99) SQL 语句 " (SELECT MAX(SERIALNUMBER) FROM EMPLOYEE)+1 " 将找出最大的 SERIALNUMBER 并将其加 1，这样新行就有一个惟一的 SERIALNUMBER。 这样做存在的一个问题是，当将第一行插入表中时，可能会得到如下错误： SQL0407N Assignment of a NULL value to a NOT NULL. SQLSTATE=23502. 得到上述错误的原因是，当表为空时，" SELECT MAX(SERIALNUMBER) FROM EMPLOYEE " 的返回为 NULL。因此，我们必须使用 COALESCE() 来处理这个问题： INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME, SALARY) VALUES (COALESCE((SELECT MAX(SERIALNUMBER) FROM EMPLOYEE), 0)+1, ‘John’, ‘Smith’, 999.99) 另一种传统方法是使用触发器来生成代理键： CREATE TRIGGER AUTOSURROGATEKEY NO CASCADE BEFORE INSERT ON EMPLOYEE REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET N.SERIALNUMBER = COALESCE((SELECT MAX(SERIALNUMBER) FROM EMPLOYEE), 0)+1END 优点及问题传统方法易于理解，而且容易在所有系统上实现。但是，这种实现实际上会导致事务处理系统中出现并发问题。因为该实现只允许一次执行一条 INSERT *** 作。 因此，在获得最大的 SERIALNUMBER 之前，" SELECT MAX(SERIALNUMBER) FROM EMPLOYEE " 必须等待其他所有事务完成对表 EMPLOYEE 的 INSERT 或 UPDATE *** 作。例如，如果有两个事务正在对 EMPLOYEE 表进行 INSERT *** 作，那么其中有一个事务会被另一个事务阻塞。显然，这种“逐次插入”的解决方案不适合多用户的事务处理系统。 回页首使用键管理器解决方案的思想很多大型的应用程序使用键管理器方法维护所有表的代理键。键管理器可以是一个助手类。每当需要向表插入一个行时，便可以调用键管理器生成新的键值，然后将获得的键值插入新行。示例首先，需要创建表 KEYS 来记录每个表的当前代理键值。键管理器类将使用该表生成新键值。 CREATE TABLE KEYS ( TABLENAME CHAR(256), COLNUMNAME CHAR(256), SURROGATEKEYVALUE BIGINT, INCREMENT BIGINT, PRIMARY KEY(TABLENAME, COLNUMNAME))第二，将新表（例如表 EMPLOYEE）注册到表 KEYS 中。 INSERT INTO KEYS (TABLENAME, COLUMNNAME, SURROGATEKEYVALUE, INCREMENT) VALUES (‘EMPLOYEE’, ‘SERIALNUMBER’, 0, 1)第三，编写 KeyManger 类来维护每个已注册表的代理键。KeyManager 将提供两个方法： /** *Intialize the KeyManger */ KeyManager.singleton()/** *Return the unique surrogate key value according to the input table *name and column name. */ KeyManager. GetSurrogateKey(String tableName, String columnName)要查看更详细的 KeyManger 的代码，请参考附录。 第四，调用 KeyManger 来获得主键值： … KeyManager km = KeyManager.singleton()Long surrogateKey = km.getSurrogateKey("EMPLOYEE", "SERIALNUMBER")… 优点和问题显然，键管理器是模块化设计的一个很好的例子。键管理器封装了代理键生成函数。这种实现也易于定制。您可以在 KEYS 表中为 SURROGATEKEYVALUE 或 INCREMENT 指定不同的值，以得到不同的代理键。而且，这种实现可以在大多数数据库系统上实施。但是，为了进行维护，需要一个单独的表和编写代码。所以，这种方法更适合于大型的跨数据库系统。 回页首使用DB2 UDB 特性DB2 UDB 提供了三种方法来生成惟一值。您可以使用这些方法来实现代理键。DB2 UDB Version 6.1 中的 GENERATE_UNIQUE() SQL 函数。 DB2 UDB Version 7.2 中 CREATE TABLE 语句的 IDENTITY 选项。DB2 UDB Version 7.2 中的 SEQUENCE 对象。 GENERATE_UNIQUE()解决方案的思想GENERATE_UNIQUE() 最初是在 DB2 UDB Version 6.1 中提供的一个 SQL 函数。该函数返回当前系统时间戳。我们可以使用该函数为代理键列生成惟一值。示例 CREATE TABLE EMPLOYEE ( SERIALNUMBER CHAR(13) FOR BIT DATA NOT NULL, FIRSTNAME CHAR(64), LASTNAME CHAR(64), SALARY DECIMAL(10, 2), PRIMARY KEY (SERIALNUMBER)) 然后可以用下面的 SQL 语句插入一行： INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME, SALARY) VALUES(GENERATE_UNIQUE(), ‘John’, ‘Smith’, 999.99) 优点和问题这里需要清楚两件事情。首先，当多个事务在同一时刻插入行时，GENERATE_UNIQUE() 可能会返回相同的时间戳。在这种情况下，GENERATE_UNIQUE() 不能为每个事务生成一个惟一的返回值，因而这种方法不适合有大量事务的系统。第二，一旦系统时钟需要向后调整，那么 GENERATE_UNIQUE() 将可能返回重复的值。 由于上述限制，我决不会在生产系统中使用 GENERATE_UNIQUE()。但是，当您需要在有限的时间内完成一个原型时，这也许是一种选择。 CREATE TABLE 语句中的 IDENTITY 选项解决方案的思想IDENTITY 是 DB2 UDB Version 7.1 和后期版本提供的 CREATE TABLE 语句中的一个选项。在创建表时，可以将某个列指定为 IDENTITY 列。对于每条 INSERT 语句，DB2 将负责为其中的这一列生成一个惟一的值。 示例 CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), FIRSTNAME CHAR(64), LASTNAME CHAR(64), SALARY DECIMAL(10, 2), PRIMARY KEY (SERIALNUMBER)) 然后可以用下面的语句插入一行： INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, SALARY) VALUES ( ‘John’, ‘Smith’, 999.99) INSERT 语句不需要指定 SERIALNUMBER 列的值。DB2 UDB 将根据列的定义自动生成惟一值，即 "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)"。优点和问题IDENTITY 函数在大多数情况下是代理键函数的一个好的解决方案。DB2 import 和 export 实用程序也支持 IDENTITY 选项。然而，在某种情况下，这种解决方案不大方便。在运行 INSERT 语句之后，应用程序将永远都不知道放入了主键列中的是什么值。如果应用程序必须继续向子表插入一个行，那么它就不得不对父表运行一条 SELECT 语句，以得到主键值。不过，如果这一点对于您的系统不成问题的话，那么使用 IDENTITY 选项是一个好主意。 SEQUENCE 对象解决方案的思想SEQUENCE 对象是在 DB2 UDB Version 7.2 中引入的一个特性。用户可以在数据库中创建一个 SEQUENCE 对象，就像创建表对象或视图对象一样，然后从 SEQUENCE 中请求值。DB2 保证用户每次可以得到一个惟一的序列值。 示例您可以在数据库中创建一个 SEQUENCE 对象： CREATE SEQUENCE EMPSERIAL AS BIGINT START WITH 1 INCREMENT BY 1 如果有一个如下所示的 EMPLOYEE 表： CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL, FIRSTNAME CHAR(64), LASTNAME CHAR(64), SALARY DECIMAL(10, 2), PRIMARY KEY (SERIALNUMBER)) 那么可以用下面的语句插入一个行： INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME, SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'John', 'Smith', 99.99) 在这里使用 " NEXTVAL FOR EMPSERIAL " 从 SEQUENCE 中获得惟一值。 您可以使用 " PREVVAL FOR EMPSERIAL " 获得当前连接会话中最近生成的序列值。应用程序就可以知道放入主键列中的是什么值，从而继续向子表插入一个行。这里，“在当前连接会话中”这一点很重要，这意味着 "PREVVAL" 将只返回在相同连接会话中生成的值。 例如，考虑这样的情况：有两个应用程序连接到数据库，并按照如下顺序运行下面的 SQL 语句。 （假设 SEQUENCE " EMPSERIAL " 的当前值是 3）。 应用程序 1：INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME, SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'Martin', 'Wong', 1000.00)从EMPSERIAL 生成的 " NEXTVAL " 是 4。 应用程序 2：INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME, SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'Patrick', 'Chan', 99.99) 从EMPSERIAL 生成的 " NEXTVAL " 是 5。 应用程序 1：SELECT PREVVAL FOR EMPSERIAL FROM EMPLOYEE " PREVVAL " 将返回 4，而不是 5。 而且， PREVVAL 和NEXTVAL 的值不会受事务回滚的影响。 例如，假设 SEQUENCE " EMPSERIAL " 的当前值是 30。某个应用程序开始了一个事务： INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME, SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'William', 'Chen', 99.99) 执行ROLLBACK *** 作。 然后，如果运行： SELECT PREVVAL FOR EMPSERIAL FROM EMPLOYEE 则" PREVVAL " 将返回 31，而不是 30。 优点和问题SEQUENCE 是最近 DB2 UDB 为生成惟一值而实现的函数。它还有一个缓存函数，用于提高性能（要了解详细信息，请参阅 IBM DB2 UDB SQL Reference）。该函数比 IDENTITY 函数更灵活，因为它是数据库中的一个独立对象。必要时候，可以通过运行 ALTER SEQUENCE 语句更改其设置。 如果系统只在 DB2 UDB 上运行，那么 SEQUENCE 也许是最好的解决方案，因为它易于使用，而且不像键管理器那样需要额外的代码，并且可以随需求的变化很轻易对其进行更改。回页首结束语本文描述了实现作为主键的代理键的三种方法。文中主要讨论了如何为代理键生成惟一的序列值。传统方法适合于简单的、单用户（非并发）系统。对于实现对于大型系统和跨平台系统，键管理器是一个好选择。但是，如果项目只在 DB2 UDB 上运行的话，可以考虑 DB2 UDB 提供的特性。IDENTITY 和 SEQUENCE 函数提供了一种容易的、灵活的解决方案。在创建 IDENTITY 列和 SEQUENCE 对象时，可以使用很多选择。请参阅 IBM DB2 UDB Administration Guide和IBM DB2 UDB SQL Reference，以获得完整的细节。 回页首附录 import java.sql.Connectionimport java.sql.DriverManagerimport java.sql.PreparedStatementimport java.sql.ResultSetimport java.sql.SQLExceptionpublic class KeyManager { private static KeyManager singletonprivate PreparedStatement getKeyStmt = nullprivate PreparedStatement updateKeyStmt = nullstatic final String db2Driver = "COM.ibm.db2.jdbc.app.DB2Driver"static final String db2UrlPfx = "jdbc:db2:"public KeyManager Singleton() throws ClassNotFoundException, SQLException{ if (singleton == null) { if (singleton == null) singleton = new KeyManager()} return singleton} private KeyManager() throws ClassNotFoundException, SQLException{ Class.forName(db2Driver)Connection connection = DriverManager.getConnection(db2UrlPfx+ "dbName", "userName", "password")getKeyStmt = connection.prepareStatement("SELECT SURROGATEKEYVALUE FROM KEYS WHERE TABLENAME = ? AND COLUMNNAME = ?")updateKeyStmt = connection.prepareStatement("UPDATE KEYS SET SURROGATEKEYVALUE = SURROGATEKEYVALUE + INCREMENT WHERE TABLENAME = ? AND COLUMNNAME = ?")} public Long getSurrogateKey(String tableName, String columnName) throws SQLException{ Long keyValue = nullgetKeyStmt.setString(1, tableName)getKeyStmt.setString(2, columnName)updateKeyStmt.setString(1, tableName)updateKeyStmt.setString(2, columnName)updateKeyStmt.execute()ResultSet rs = getKeyStmt.executeQuery()if (rs.next() == true) { keyValue = new Long(rs.getLong(1))} return keyValue} } 参考资料 您可以参阅本文在 developerWorks 全球站点上的 英文原文.IBM DB2 UDB Administration GuideIBM DB2 UDB SQL Reference关于作者Jason Zhang 是 IBM 加拿大多伦多的实验室的一名软件开发人员，从事电子商务开发已经有 5 年多的时间。他主要从事于电子商务后台解决方案的研究，例如 EJB、登台服务器（staging server）和数据库应用程序。关闭[x]关于报告滥用的帮助报告滥用谢谢! 此内容已经标识给管理员注意。关闭[x]关于报告滥用的帮助报告滥用报告滥用提交失败。 请稍后重试。关闭[x]developerWorks：登录IBM ID：需要一个 IBM ID？忘记IBM ID？密码：忘记密码？更改您的密码 保持登录。单击提交则表示您同意developerWorks 的条款和条件。 使用条款 当您初次登录到 developerWorks 时，将会为您创建一份概要信息。您在developerWorks 概要信息中选择公开的信息将公开显示给其他人，但您可以随时修改这些信息的显示状态。您的姓名（除非选择隐藏）和昵称将和您在 developerWorks 发布的内容一同显示。所有提交的信息确保安全。关闭[x]请选择您的昵称：当您初次登录到 developerWorks 时，将会为您创建一份概要信息，您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的，并且出于隐私保护的原因，不能是您的电子邮件地址。昵称：（长度在 3 至 31 个字符之间）单击提交则表示您同意developerWorks 的条款和条件。 使用条款. 所有提交的信息确保安全。为本文评分评论回页首

性能问题总是数据库领域里面永恒的话题，使用 DB2 作为底层数据平台的 SAP 系统为我们提供了许多方法监控和检测数据库性能问题。本文从数据库性能问题检测的一般思路和方法论入手，介绍了如何通过 SAP 系统对 DB2 的性能进行监控和分析。回页首数据库性能问题检测的思路及方法论在数据库运维工作中遇到性能问题时，通常会让数据库管理员感到无从下手，不容易断定问题的根源所在。如果能有一种可 *** 作的一般性的方法作为指导，我们通常就能够发现大部分数据库性能问题的根源。那么，首先根据我们对数据库性能监控的一些实践经验来总结一下在遇到性能问题时应该进行哪些检查。我们不能保证通过文中介绍的方法就能找到所有性能瓶颈，性能问题的原因很多，解决方法也多种多样，我们在这里只是提供一些普遍的，一般性的方法，具体的实施还需要根据系统的具体情况和不断的实践经验积累。同时，通过进行这些监控，也有利于为技术支持人员提供更详细诊断信息，以便于快速定位性能问题。当遇到一个性能问题，我们首先进行以下排查: 性能问题是在何时发生的性能问题持续存在的还是间断性的系统范畴的性能问题还是数据库本身的性能问题性能问题是否只存在于某一个应用性能问题是随机出现的还是必然出现的。如果性能问题存在于所有应用，我们可以进行以下排查: 如果发现系统存在大量的 I/O *** 作: 检查设备使用情况检查排序情况和临时表空间检查查询性能是否有效的得到优化检查缓冲池的使用状况。 如果发现 CPU 具有很高的负载: 检查排序状况检查缓冲池的使用状况。 如果发现 CPU 和 I/O *** 作的负荷都很大: 检查用户数量检查排序状况。 如果发现 CPU 和 I/O *** 作的负荷都很小而数据库响应仍然很慢: 检查并发性和锁的使用状况检查缓冲池的使用状况。如果性能问题可以被定位在一个应用，我们可以进行以下排查: 检查排序情况。 检查并发性和锁的使用状况。 检查统计信息是否更新。回页首通过SAP 系统的工具对 DB2 UDB 进行监控通过前一部分的描述，我们了解到了在遇到性能问题以后应该用什么思路寻找性能的瓶颈，从而想办法解决性能问题。在对数据库进行检查的过程中，我们通常会用到数据管理工具，命令行以及 *** 作系统的工具，还要结合 SAP 的自身特点寻找性能问题的根源，这将是一个比较繁琐和费事的工作。在使用 SAP 作为底层数据库的 SAP 系统中，由于 SAP 实现了与 DB2 紧密的结合。SAP 的 DBA Cockpit 提供了许多功能来支持数据库的管理工作，使得数据库性能监控和分析变得更加简单。下面我们就来看看，SAP 为 DB2 性能监控和分析提供了哪些支持。磁盘I/O 性能监控概念对于数据库来说，最消耗时间的 *** 作实际上是从磁盘中检索数据。这是由磁盘的物理特性决定的。尽管磁盘存储技术已经取得了极大的进步，但磁盘的读写速度与内存的读写速度仍然相差几个数量级。从性能调整的角度来说，如果一个机器的磁盘出现问题，那么其他的任何优化工作都无法提供帮助。因此我们应该保证运行数据库的磁盘系统是健康的。SAP 系统为我们提供了监控磁盘读写速度的功能，让我们可以直接了解当前磁盘的性能状况。监控我们首先进入 SAP 的 DBA Cockpit ( 可以直接输入 st04)，然后在 Performance 的目录下双击 Database, Buffer Pool 的标签内，可以看到当前数据库磁盘的读写状况。图1. 磁盘 I/O 信息从图中我们可以看到磁盘物理平均读速度为 3.25ms，写速度为 7.45ms。分析磁盘物理读写速度反应了磁盘子系统的性能。一般情况下，磁盘读写速度应该小于 5ms，读速度一般要大于写速度，但在一些具有大量缓存的存储系统中，写速度可能会快于读速度。磁盘性能的优化已经超出了本文讨论的范围，这里只是提供一些基本的指导。缓冲池监控概念缓冲池是数据库单独开辟的一块存储区域，这片区域用来缓存从磁盘读出的包含数据表和索引的数据页。当数据第一次被检索出来以后便被暂时缓存在缓冲池中，当数据下次被访问时，数据库将直接从缓冲池中读取数据，这样减少了相对缓慢的磁盘 I/O *** 作。因此，缓冲池的配置对于数据库性能十分重要。在缓冲池中，目前还不支持存储大对象和长数据记录。反映缓冲池质量的一个重要性能指标是缓冲池命中率。缓冲池命中率指数据库管理器不需从磁盘读入页就能处理页请求的时间百分比。其计算方法为:缓冲池命中率 = (1 - (( 缓冲池数据物理读 + 缓冲池索引物理读 ) / ( 缓冲池数据逻辑读 + 缓冲池索引逻辑读 ) ) ) * 100%另外两个重要性能指标是索引命中率和数据命中率。索引命中率反映了可以在缓冲池中找到的页面能够满足的对索引页的所有读请求所占的百分比。其计算方法为:索引命中率 = (1 - ( 缓冲池索引物理读 / 缓冲池索引逻辑读 ) ) ) * 100%数据命中率说明了可以在缓冲池中找到的页面能够满足的对数据页的所有读请求所占的百分比。其计算方法为:数据命中率 = (1 - ( 缓冲池数据物理读 / 缓冲池数据逻辑读 ) ) ) * 100%监控我们可以从三个级别来看缓冲池的质量，这三个层次分别是数据库级，缓冲池级，表空间级。在 SAP 系统中我们可以使用 DBA Cockpit 来查看不同级别的缓冲池质量。首先可以在数据库级查看缓冲池的质量，我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Buffer Pool 的标签内，可以看到当前数据库总体的缓冲池质量。图2. 缓冲池总体状况我们从图中可以看出，数据库缓冲池的总体命中率是 99.81%，数据命中率为 99.86%，索引命中率为 99.70%。如果在 st04 中选中 Performance ->Buffer pools， 我们也可以在缓冲池级别看到命中率，如果一个数据库只有一个缓冲池，那么这个命中率与我们在数据库级别看到的命中率相同。图3. 数据库级别缓冲池信息如果在 st04 中选中 Performance ->Tablespaces，我们就可以在表空间级别看到缓冲池的命中率。图4. 表空间级别缓冲池信息分析缓冲池的理想命中率对于索引应该大于 90%, 对于数据应该大于 95%。要提高缓冲池的命中率，可以增加缓冲池的大小，也可以为不同类型数据分配不同缓冲池，可以为每个经常访问的具有自己的表空间的大型表使用一个缓冲池，也可以为一组小型表使用一个缓冲池。缓存监控概念数据库的缓存主要有包缓存 (Package Cache) 和编目录缓存 (Catalog Cache)。它们与数据库的查询性能息息相关。包缓存(Package Cache) :SQL 语句编译通常消耗的资源比较大，为了提高系统性能，动态 SQL 语句在被编译后一般存放于包缓存中。当用户下一次请求同一条 SQL 语句，就无需再次编译 SQL 语句。包缓存的质量一般通过包缓存命中率来衡量，它表明了包缓存的设置是否成功的避免了 SQL 语句的重新编译。其计算方法为:包缓存命中率 = (1 - ( 在包缓存中的插入次数 / 查询包缓存的次数 )) * 100编目录缓存 (Catalog Cache):编目录缓存用来缓存系统编目录信息，如系统表，权限，系统存储过程。系统编目录的访问速度对于系统的性能有着十分重要的影响。在 DPF 环境下，系统编目录的访问速度至关重要。通过使用编目录缓存可以大大提高访问系统编目录的速度。编目录缓存质量一般通过编目录命中率来衡量，它表明了编目录缓存是否成功的避免了从磁盘中读取编目录信息。其计算方法为:包缓存命中率 = (1 - ( 在编目录缓存中的插入次数 / 查询编目录缓存的次数 )) * 100监控我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Cache 的标签内，可以看到当前数据库缓存的统计信息。图5. 数据库缓存信息从图中我们可以看到编目录缓存的质量是 99.93%，在图中的 quality 就是我们前面所说的命中率。当前数据库编目录缓存的大小为 10240KB，没有缓存溢出。在左边一栏，我们可以看到，包缓存的质量是 97.64%，包缓存的大小为 62080KB，没有缓存溢出。分析包缓存的理想命中率应该大于 98%，用户通常不用关注包缓存的大小，如果 PCKCACHESZ 被设置为 automatic，其大小由 DB2 自动调节。编目录缓存的理想命中率也应该大于 98%，其大小应该保证编目录缓存不应该发生任何溢出。我们可以调整数据库配置参数 CATALOGCACHE_SZ 来改变编目录缓存大小，由于编目录缓存是从数据库堆中分配的，因此，在改变 CATALOGCACHE_SZ 变量的同时，应该注意到数据库堆的大小也会相应改变。排序监控概念DB2 在运行过程中时经常要做排序 *** 作。一般说来，在 OLTP 类型的数据库中，排序 *** 作通常少于 OLAP 类型的数据库环境。排序 *** 作通常会在三种情况下发生，第一种情况是数据的查询处理，比如 order by, group, 哈希连接，索引 *** 作，内存的表 *** 作等等。第二种是当我们载入 *** 作的对象是带有索引的表时，再载入 *** 作过程中就会涉及到对索引键的列表和排序，这样就会产生排序 *** 作。第三种情况发生在创建索引的时候。排序的效率因而直接影响到数据库的响应时间，我们必须对排序进行有效监控。监控我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Sorts 的标签内，可以看到当前数据库的排序状况。图6. 数据库排序状况可以从图中看出，共享排序堆的大小为 1676KB, 私有排序堆的大小为 1340KB。如果没有索引满足所取的行的要求顺序，或者 DB2 查询优化器认为排序的代价低于索引扫描，那么就需要在排序堆中进行排序。DB2 的排序分为私有排序和共享排序。私有排序发生在代理的私有代理内存中，而共享排序发生在数据库的数据库共享内存中。我们还可以看出，排序堆溢出次数 1174 次，总的排序次数为 310642 次。分析如果数据库分配的排序堆大小不够大，就会出现排序溢出的情况，这样就需要动用临时表空间来辅助排序的进行，由于临时表空间存在于磁盘，这将大大影响排序的速度。理想情况下，排序溢出率 ((Sort overflows * 100) / Total sorts ) 不应该超过 1%。如果这个溢出率过高，那么数据库中很可能发生了大的排序，我们就需要调查出现过度排序的原因。在发现根源之前，一个简易的解决方案是增加 SORTHEAP 的大小。然而，这样做通常是治标不治本并且掩盖了真实的性能问题。比较彻底的解决方案应该是确定引起排序的 SQL 并更改该语句，或通过增加索引来避免或减少排序开销。并发性和锁的监控概念数据库的锁是数据库管理器用来控制应用程序并发访问数据库数据并且保证数据库数据的一致性的重要机制，数据库中行和表都可以上锁。数据库的锁在保证了数据库数据一致性同时也在一定程度上降低了数据库的响应速度。锁等待和死锁是影响数据库相应速度的重要因素，糟糕的应用程序设计和不合理的 SQL 查询计划的生成都会导致锁等待和死锁。锁升级 (Lock Escalation):一个锁通常作为一个记录存储在内存锁表中，锁表的大小可以由数据库自动调节。锁升级一般发生在锁的数量超过了数据库配置参数 MAXLOCKS 所指定的大小，为了减少锁的数量，数据库会把若干行一级的锁合并为表锁。这样数据库的并发性就会受到影响。监控我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Locks and Deadlocks 的标签内，可以看到当前数据库的锁的状况。图7. 数据库锁状况从图中可以看到，当前锁表的大小为 22144KB，已经使用了 94KB。锁等待的平均时间为 10.40ms，没有锁升级和死锁被检测到。分析影响数据性能的有关锁的问题主要集中在锁等待，死锁和锁升级。这些问题的根源很可能是数据库应用的设计问题。因此，我们应该仔细调查造成死锁，锁等待和锁升级的应用程序，以及其用到的 SQL 语句。同时，在问题定位之前，我们也可以通过下面方法来解决数据库锁造成的性能问题。锁等待和死锁:如果要避免锁等待和死锁的问题我们需要注意数据库参数中的 DLCHKTIME 和 LOCKTIMEOUT 两个参数的设置。其中 DLCHKTIME 单位是毫秒，是 DB2 检查死锁的间隔时间，如果该值为 10000ms，则表明每隔 10 秒钟数据库会检查一下有无死锁存在，如有死锁，会选择回滚其中的某一个事务，让另外一个事务完成交易。LOCKTIMEOUT 单位是秒，是锁等待最长时间，超过该时间仍未获得锁，则返回错误。LOCKTIMEOUT 的默认值为 -1，这意味着锁等待时间无限大，一般不推荐这种设置。DLCHKTIME 时间通常要设得比 LOCKTIMEOUT 时间小一些，否则还未发现死锁，就会返回锁等待超时错误 (SQL0911N 返回码 68) 。锁升级:要避免锁升级，我们应该正确设置数据库参数 LOCKLIST 和 MAXLOCKS。LOCKLIST 表明分配给锁表的内存大小。每个数据库都有一个锁表，锁表包含了并发连接到该数据库的所有应用程序所持有的锁。MAXLOCKS 定义了应用程序可以占有锁表空间的百分比，当一个应用程序所使用的锁表百分比达到 MAXLOCKS 时，数据库管理器会升级这些锁，用表锁代替行锁，从而减少列表中锁的数量。我们一般可以通过增加锁表大小的方法解决锁升级问题。日志性能监控概念DB2 事务日志对于恢复来说极其重要。它们记录对数据库对象和数据所做的更改。在 DB2 中数据和索引的改变都会先被写入日志缓冲区，保证对数据所做的修改在记录到数据库之前，总是被具体化为日志文件。日志缓冲区的数据由日志处理器写入磁盘。在下列情况下，查询处理必须等待日志数据写入磁盘后才能进行: 事务提交时。在将相应数据页写入磁盘之前，因为 DB2 使用预写日志记录。预写日志记录的好处是当执行 COMMIT 语句完成事务之后，并非所有更改的数据和索引页都需要写入磁盘。 在元数据更改(一般通过执行 DDL 语句产生的)之前。 日志缓冲区已满。DB2 以这种方法管理向磁盘写入日志数据的目的是尽可能地缩短处理延迟时间。在存在许多较小的并发事务的环境中，许多处理延迟是由 COMMIT 造成的，因为它必须等待日志数据写入磁盘后才能进行。因此，日志处理器进程频繁地将少量日志数据写入磁盘会造成大量处理延迟，另外一些延迟是由日志 I/O 开销造成的。监控我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Logging 的标签内，可以看到当前数据库日志的状况。图8. 数据库日志状况我们在图中可以看到日志文件以及日志缓冲区的情况。包括日志文件的数量，大小，数据库使用的日志空间以及可用日志空间的大小。还可以看到日志缓冲区的情况，当前日志缓冲区的命中率为 100%。分析由于数据库中的处理必须等待日志数据写入磁盘才能进行，日志文件的读写速度对数据库的响应速度也会产生很大影响。因此，应该把日志文件放到速度比较快的磁盘上，以减少磁盘 I/O 开销。日志文件写入的性能可以通过平均写时间来观察。另外，我们可以通过调整数据库配置参数 LOGBUFSZ 来指定日志缓冲区的大小。如果数据库对于日志磁盘有相当多的读 *** 作，或者希望有较高的磁盘利用率。一般来说，如果日志缓冲区的命中率小于 98%，那么可以增加这个缓冲区的大小以提高命中率。当增加这个参数的值时，也要考虑 DBHEAP 参数，日志缓冲区使用的空间是 DBHEAP 参数所定义的内存空间的一部分。数据库统计信息监控概念数据库的统计信息反映了表及其相关索引的物理特点。统计信息主要包含: 表信息:表的行数，使用的数据页，溢出的行数，列的平均长度，列的最大最小值等。 索引信息:索引条目的数量，索引所关联列的不同值的数量，索引的层数等。这些信息被数据库查询优化器用来生成查询计划。好的访问计划对于 SQL 语句的快速执行至关重要。我们需要想办法保证统计信息准确地反映了当前数据库的状况。由于数据库的表和索引总是随着数据库处理各种更新请求而不断发生变化的，因此，总会出现统计信息过时，而不能正确反映数据库数据现状的情况。这时，数据库查询优化器生成的查询计划可能不是最优的，这将大大影响数据库的查询性能。我们应该经常检查数据库的统计信息是否为最新的，并及时更新统计信息。SAP 系统为我们提供了监控和执行统计信息收集的方法。监控我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Tables, 在 Table 列表中双击要监控的表，在 Table 的标签中，我们可以看到当前表的基本信息。这时，如果我们点击 Count 按钮，就会看到统计信息的质量。图9. 表的统计信息状况从图中我们看到，表的当前行数为 27，Deviation 为 8%。分析如果我们监控到一个表的 Deviation 超过了 15%，我们就应该重新收集这个表的统计信息。在 SAP 中我们可以选择手动收集统计信息。我们也可将系统配置成自动收集统计信息，这样大大减少了系统手动维护的工作量。自动统计信息收集通常每隔 2 个小时触发一次，它会自动选择在 24 小时之内没有收集过统计信息的持久的基表。如果 SAP 系统进行 Client Copy 或在 BI 表中加载大量数据，由于这些 *** 作在短时间就可以改变表的数据状况及分布，因此会导致统计信息的过时。在 DB2 V9.5 中，为了解决这个问题，提供了一种 RTS (Real Time Statistics) 的特性，该特性可以允许在查询被处理并优化前对表的统计信息进行收集或采样，如果优化器认为重新收集统计信息比用过时的统计信息进行查询的速度快，那么会在处理该查询之前重新收集表的统计信息，从而达到实时收集统计信息的目的。我们一般需要将数据库配置参数 AUTO_STMT_STATS 设为 ON 来开启 RTS 特性，但在 SAP 系统中，RTS 已经是默认设置，我们无需进行任何改变。回页首结束语本文从数据库问题检测的一般思路和方法论出发，介绍了如何通过 SAP 系统对 DB2 的性能进行监控。

---