DB2 z/OS SQL 分析和调优的利器

核心提示：OSC 简介DB2 z/OS 广泛应用于各行业的企业 IT 系统中，是企业信息存储与处理的中心，DB2 z/OS SQL 分析和调优的利器，从 1983 年诞生至今，无数的企业级应用程序基于 DB2 z/OS 数据库构建，图 26. 指定需要技术支持服务的相关 SQL 或表查看原图（大图）图 27. 指定 PMR 或

OSC 简介

DB2 z/OS 广泛应用于各行业的企业 IT 系统中，是企业信息存储与处理的中心。从 1983 年诞生至今，无数的企业级应用程序基于 DB2 z/OS 数据库构建，SQL 的性能对于系统的整体性能来说非常重要。长期以来，DB2 z/OS 的 SQL 分析和优化需要具备复杂的知识以及丰富的经验，基于 PCOM 的操作界面与图形化的 Windows 交互界面相比缺乏友好性，这更使得 DB2 z/OS 的 SQL 分析和调优门槛甚高。基于 Eclipse RCP 开发的 Optimization Service Center（OSC）改变了这一现实，OSC 由 DB2 工具 Visual Explainer 发展而来，除了其易于上手的用户界面友好性之外，OSC 具备 SQL、workload 监控、SQL 注释、Catalog 统计信息分析、SQL 执行路径图解、创建执行路径提示、SQL 及 workload 报表生成等强大功能，OSC 已成为 DB2 z/OS 产品中不可或缺的重要组成部分。

开始之前

本文主要面向 DB2 z/OS 数据库管理员，系统架构师，应用程序开发者，以及对 SQL 分析和性能调优感兴趣的读者。要求读者具备 DB2 z/OS 和 SQL 的基本知识。使用 OSC 的系统要求：

DB2 for z/OS 的 v8 或 v9 子系统；

激活 OSC。使用 OSC 的所有功能需要在 DB2 子系统上创建和配置相关的表和存储过程，在“激活 OSC”中进行介绍；

500M 磁盘空间，1G 内存，Windows 2000/XP/2003/Vista；

安装 DB2 Connect 软件；

TCP/IP 网络连接；

足够的 DB2 子系统权限，简单起见，假设读者具备相应 DB2 子系统的管理员权限。

下载安装 OSC

从 IBM 站点 下载 OSC，解压缩运行 setup.exe，在选择安装路径时，确保不要安装在包含空格的路径下，如“Program Files”。安装完成后，选择 OSC.exe 或桌面快捷方式启动 OSC。下图为 OSC 的 welcome 页面，点击所列图标可进入相应的功能界面。

图 1. OSC 欢迎界面

查看原图（大图）

激活 OSC

在进行 OSC 激活之前 , 需确保 DB2 服务器已经开启并配置好以下功能：

DDF(Distributed Data Facility)，使 OSC 能通过 TCP/IP 连接到子系统；

WLM(Workload Management)，为 Java 和 C 的存储过程提供运行环境；

Java，支持 Java 存储过程的运行。

使用 OSC 的所有功能，需要对 DB2 服务器进行相关对象的创建和激活，操作均可以通过服务器端提交 JCL（Job Control Language，作业控制语言）进行，这些 JCL 在 DB2 安装好后就存在，详细操作步骤请参考 OSC Getting Started文档。激活工作完成以下操作：

创建 OSC 所需的表、表空间和索引；

绑定 OSC 的一系列包；

创建 OSC 所用的存储过程；

创建 OSC 使用的别名；

赋予包和存储过程的可执行权限；

创建安全控制相关的 RACF 组 DB2OSCA。

部分 OSC 的功能（不是必须依赖于存储过程的功能）也可以仅通过 OSC 进行客户端激活。

上面已多次提到存储过程 ,OSC 部分功能必须基于相应的存储过程 , 而对有些功能则是可选的 ( 选择使用存储过程能提高相应功能的运行速度 )。OSC 所用到的存储过程如下表：

表 1. OSC 的存储过程

存储过程名	功能
SYSPROC.DSNWZP	在 OSC 中查看子系统参数（ZPARM）。
SYSPROC.DSNUTILU	在 OSC 中调用 DB2 Utilities，如 RUNSTATS。
SYSPROC.DSNAEXP	在 OSC 中通过存储过程执行 EXPLAIN。
SYSPROC.OSC_RUNSQL	支持从动态 statement 缓存中捕获 workload；支持对 statement 缓存中 SQL 的 EXPLAIN。
SYSPROC.OSC_EXECUTE_TASK	支持 workload 的捕获、EXPLAIN、转化和监控。
SYSPROC.ADMIN_TASK_ADD	支持 Workload 控制中心的任务管理和调度。
SYSPROC.ADMIN_TASK_REMOVE	支持 Workload 控制中心的任务删除。
SYSPROC.ADMIN_COMMAND_DB2 或 SYSPROC.DSNACCMD	在 OSC 中调用并执行 DB2 Commands。

配置并连接 DB2 子系统

OSC 启动后，在 Welcome页面上选择 Configure DB2 subsystems，然后选择 Subsystem-> Add启动子系统配置向导，按步骤完成以下设置：

1. 子系统网络连接：

Subsystem alias：OSC 中记录该子系统的别名，用户可以自定义；

Location：为 DB2 子系统分配的唯一网络名；

Host name：子系统所在 z/OS 的域名或 IP 地址；

Port number：为子系统分配的网络端口号。

2. 使用数据库管理员权限用户和密码连接子系统：

连接成功后，OSC 自动检查：

OSC 包是否已绑定；

EXPLAIN 功能是否激活（PLAN 表是否已创建）；

OSC 表（workload 控制中心和监控器的表，workload 统计信息分析表）是否已创建。

如果之前没有进行服务器端激活，可在此创建，进行客户端激活。

3. 指定之前的连接用户，绑定 OSC 所有功能所需的包，若服务器端有相关改动，可通过 Free来释放并重新绑定。

图 2. OSC 包绑定

4. 创建 PLAN 表，支持 EXPLAIN 功能。

5. 创建 OSC 表。

下图表明 OSC 连接 DB2 子系统并激活成功。

图 3. OSC 连接 DB2 子系统并激活成功

查看 DB2 子系统参数

OSC 连接到 DB2 子系统后，可以查看系统参数 ZPARM。ZPARM 包含了对 DB2 众多功能的设置和开关。在 OSC 中可以方便的搜索、查看这些值，前提是必须激活表 1 中的存储过程 SYSPROC.DSNWZP。选择 Configure Subsystems视图中的 Browse Subsystem Parameters，在打开的界面上可以选择或通过关键字搜索想要查看的参数，对于每个 ZPARM 参数的作用都有详细解释。如图中，搜索关键字“HINT”，查看 OPTHINT 功能（DB2 的 SQL 执行路径提示功能）是否开启，图中“NO”表明 OPTHINT 功能未开启。

图 4. 查看 ZPARM

查看原图（大图）

选择 Options...可以对 ZPARM 的关键字搜索范围和方式进行配置。

图 5. 配置 ZPARM 关键字搜索范围和方式

查看 SQL 信息

如果你想把着眼点放在特定的单个 SQL 上，OSC 提供了多种方式让你快速定位并查看 SQL 的活动信息。在 Welcome页面上选择 View Query Activity，SQL 活动信息页面打开。在 View Queries视图中，选择已经配置和连接的子系统。

图 6. SQL 活动信息查看界面

查看原图（大图）

要查看 SQL，首先要选择 SQL 的来源（Query source），DB2 SQL 源包括：

外部 SQL 文件，包含 SQL 语句的文本文件，以分号间隔；

Statement 缓存，DB2 V8 新增功能，将最近执行动态 SQL 语句进行缓存，下次再次执行相同 SQL 时，性能大大提高；

Catalog 目录表，表中可能存储了部分 SQL 语句；

QMF；

监视器；

SQL 存储过程。

其次，在选定的源中，可以自定义试图来过滤、排序、显示想要查看的 SQL（对于监视器源，无需定义视图，通过选择定义好的 profile 来查看 SQL）。选择 View names-> New view...：

定义 SQL 的过滤条件。针对不同的源，过滤条件各不相同。通过界面提示进行选择和配置。

SQL 的排序设置。对于每个 SQL，OSC 定义了多种描述信息 , 包括 ID，SQL 文本，执行次数，执行时间，平均执行时间等，用户可根据不同的信息进行排序显示。

描述信息显示设置。用户可根据需要来选择显示关心的 SQL 描述信息。

所有符合条件的 SQL 信息被列出来，可以通过选择 Find SQL Text...在过滤结果中进行 SQL 文本的模糊查询。针对单个 SQL 的分析可通过选择 Advisors和 Tools进行，将在后面介绍。双击 SQL 所在的行可显示 SQL 文本。

图 7. 显示 SQL 文本

选择 Action-> Categorize...，可以对 SQL 进行分类，用户可以定义分类，方便对 SQL 进行管理。

图 8. SQL 分类设置

查看 workload 信息

多个 SQL 组成 Workload，对于 DBA 和应用开发者来说，通常需要从成组的 SQL 角度来分析性能。OSC 的 Workload 控制中心提供了强大的功能。在 Welcome页面上选择 View Workloads，在 View Workloads视图中，选择已经配置和连接的子系统。如果之前配置了存储过程 SYSPROC.DSNAEXP，可选择通过存储过程进行 EXPLAIN，这将获得很快的 EXPLAIN 速度。

图 9. Workload 视图

查看原图（大图）

Workloads List下列出了当前子系统中所有的 workload，用户还可以通过下列途径跟着向导创建新的 workload：

Statement 缓存中获取；

从 Catalog 目录中获取；

从 QMF 中获取；

从外部 SQL 文件获取；

从用户自定义的 SQL 类别中获取；

从其他 Workload 中获取；

从 OSC 的 workload zip 包导入。

用户可以将现有的 workload 以 OSC 的 workload zip 包格式导出，便于备份，或在其他 OSC 安装机器上导入。选择 Open-> Project，将创建一个 workload 工程，便于用户将关注点集中在此 workload 上进行分析。用户可以查看 workload 的历史事件信息。

图 10. Workload 历史信息

查看原图（大图）

查看 workload 中包含的 Statement。

图 11. Workload 的 Statements

查看原图（大图）

作为 DB2 子系统管理员，可以授予 workload 所有者在 workload 控制中心的权限，以便使用 workload 控制中心的功能。

图 12. Workload 用户管理

查看原图（大图）

对于 Workload 的操作常常比较耗时，用户可定义下列四种任务，由 Workload 控制中心来调度，任务完成后将异步通知用户（用户也可主动查询），无需同步等待。

从 Statement 缓存中捕获 SQL，加入 workload 中；

对 workload 进行 consolidate，合并文本相同的 SQL，合并执行路径相同的 SQL；

EXPLAIN 整个 workload 的 SQL；

对 workload 进行统计信息分析。

图 13. Workload 任务

查看原图（大图）

对于不再关心的 workload，在 Workload 视图中选择 Remove进行删除。

定义和使用监控器

图 14. 监控器视图

查看原图（大图）

监控器用于捕获 DB2 子系统的动态和静态 SQL，监控方式分为：

Normal，监控目标子系统中所有 SQL，开销较大；

Exception，监控目标子系统中满足异常条件的 SQL，用户定义 SQL 执行的 CPU 时间门限值，以捕获性能差的 SQL 进行分析。

监控 Profile 定义好后，可开始或停止监控器，满足条件的 SQL 信息和运行时数据将被保存用来分析。

注释和格式化 SQL

目前，许多工具都能根据语法对 SQL 进行高亮显示。OSC 对 SQL 的注释远大于此，SQL 在 DB2 的处理过程中会被转换成更合理的形式，然后进行执行路径选择。OSC 能结构化得显示出原始 SQL 和转换后的 SQL，并将表、WHERE 等谓语条件相关的统计信息注释在旁边，甚至注释出某些可能将对 SQL 性能造成影响的谓语条件，让分析人员一目了然。以下面一个 TPCD workload 的 SQL 为例，假设 TPCD 的数据库环境已在子系统中建好，新建 OSC SQL 工程，输入以下 SQL，选择 Query Annotation，执行完成后，注释信息如图 15 和 16 所示。

清单 1. 示例 SQL1

　SELECT　O_ORDERKEY,　O_ORDERDATE,　O_SHIPPRIORITY　
　FROM　SYSADM.CUSTOMER,　SYSADM.ORDER　
　WHERE　C_MKTSEGMENT　=　'BUILDING'　AND　
　O_CUSTKEY　=　C_CUSTKEY-8+10　AND　
　O_ORDERDATE　<　'1993-03-15'　
　ORDER　BY　O_ORDERDATE　
　FETCH　FIRST　100　ROWS　ONLY　

图 15. 原始 SQL 注释

查看原图（大图）

清单 2. 原始 SQL 注释

　SELECT　SYSADM.ORDER.O_ORDERKEY　
　,　SYSADM.ORDER.O_ORDERDATE　
　,　SYSADM.ORDER.O_SHIPPRIORITY　
　FROM　SYSADM.CUSTOMER　CARDF=4,500,000　
　QUALIFIED_ROWS=899,999.7　
　NPAGESF=0　
　,　SYSADM.ORDER　CARDF=45,000,000　
　QUALIFIED_ROWS=8,226,534.0　
　NPAGESF=0　
　WHERE　(　SYSADM.CUSTOMER.C_MKTSEGMENT　=　'BUILDING'　COLCARDF=5　
　MAX_FREQ=(missing)　
　FF=0.2　
　AND　SYSADM.ORDER.O_ORDERDATE　<　'1993-03-15'　COLCARDF=2,304　
　MAX_FREQ=(missing)　
　FF=0.183　
　LOW2KEY=1992-01-01　
　HIGH2KEY=1998-08-02　
　AND　SYSADM.ORDER.O_CUSTKEY　=　
　(　(　SYSADM.CUSTOMER.C_CUSTKEY　-　8　)　+　10　)　COLCARDF=3,000,000　
　MAX_FREQ=(missing)　
　FF=3.333E-7　
　)　
　ORDER　BY　2　ASC　
　FETCH　FIRST　100　ROWS　ONLY　

图 16. 转换后 SQL 注释

查看原图（大图）

清单 3. 转换后 SQL 注释

　SELECT　SYSADM.ORDER.O_ORDERKEY　
　,　SYSADM.ORDER.O_ORDERDATE　
　,　SYSADM.ORDER.O_SHIPPRIORITY　
　FROM　SYSADM.CUSTOMER　CARDF=4,500,000　
　QUALIFIED_ROWS=899,999.7　
　NPAGESF=0　
　,　SYSADM.ORDER　CARDF=45,000,000　
　QUALIFIED_ROWS=8,226,534.0　
　NPAGESF=0　
　WHERE　(　SYSADM.CUSTOMER.C_MKTSEGMENT　=　'BUILDING'　COLCARDF=5　
　MAX_FREQ=(missing)　
　FF=0.2　
　AND　SYSADM.ORDER.O_ORDERDATE　<　'1993-03-15'　COLCARDF=2,304　
　MAX_FREQ=(missing)　
　FF=0.183　
　LOW2KEY=1992-01-01　
　HIGH2KEY=1998-08-02　
　AND　SYSADM.ORDER.O_CUSTKEY　=　
　CAST(　(　(　SYSADM.CUSTOMER.C_CUSTKEY　-　8　)　+　10　)　AS　INTEGER　)　COLCARDF=3,000,000　　
　MAX_FREQ=(missing)　
　FF=3.333E-7　
　)　
　ORDER　BY　2　ASC　
　FETCH　FIRST　100　ROWS　ONLY　

从注释中可以看到 SQL 中各个表的数据量，各个谓语条件的过滤率以及各个表符合条件的记录数等信息，这些信息是 DB2 优化器决定该 SQL 执行路径的关键。

Catalog 统计信息分析

DB2 的 Catalog 表中记录了描述数据库、表空间、表、列、索引的统计和分布信息。DB2 优化器基于这些数据的统计信息来决定和优化 SQL 的执行路径，所以，为了使得 SQL 获得性能优越的执行路径，统计信息的完备性、一致性对于 SQL 性能至关重要。OSC 能够分析 SQL 和它相关的数据统计信息，通过启发式规则检测出潜在的数据倾斜分布、数据相关、统计信息不足、数据冲突等，最后生成用来修复这些问题的 RUNSTATS 命令。通过在子系统上执行这些 RUNSTATS 命令，可使得数据库保持健康和准确和统计信息，最终使 SQL 选择最有效的执行路径。实践证明，除了由于部分 SQL 写得不合理之外，大多数的性能问题是由于统计信息不足、冲突或过时所导致的。

OSC 可以从单个 SQL 或 Workload 出发，分析所涉及的统计信息。以下面的 SQL 为例，在 OSC 界面上选择 Advisors-> Run Statistics Advisor，执行完成后，得到统计信息的分析结果和解决对应问题的 RUNSTATS 命令。

清单 4. 示例 SQL2

　SELECT　N_NAME,　COUNT(*),　SUM(C_ACCTBAL)　
　FROM　CUSTOMER,　NATION_NP　
　WHERE　C_NATIONKEY　=　N_NATIONKEY　AND　
　N_NATIONKEY　<　10　
　GROUP　BY　N_NAME;　

图 17. 统计信息分析结果

查看原图（大图）

对于统计信息收集的建议，OSC 按优先级将它们分为 High和 Low：

High：执行修复性的 RUNSTATS，收集不足或丢失的信息，重新收集以处理冲突或过期信息，为解决倾斜分布和数据相关问题收集足够信息；

Low：执行全面的 RUNSTATS，收集或重新收集 SQL 或 Workload 相关的统计信息。收集数据较多，运行时间较长，用户可根据情况决定是否执行。

清单 5. 示例 SQL2 的修复性 RUNSTATS 命令

　RUNSTATS　TABLESPACE　DB4NATN.TSNATIOY　
　INDEX(SYSADM.YXN@NKNMRK　KEYCARD)　
　SHRLEVEL　CHANGE　REPORT　YES　
　
　RUNSTATS　INDEX(SYSADM.UXC@NKCK　FREQVAL　NUMCOLS　1　COUNT　10,　
　SYSADM.PXC@CKNKMS　KEYCARD)　
　SHRLEVEL　CHANGE　REPORT　YES　

清单 6. 示例 SQL2 较为完全的 RUNSTATS 命令

　RUNSTATS　TABLESPACE　DB4NATN.TSNATIOY　
　INDEX(SYSADM.YXN@NKNMRK　KEYCARD)　
　SHRLEVEL　CHANGE　REPORT　YES　
　
　RUNSTATS　TABLESPACE　DB4CUST.TSCUST　
　INDEX(SYSADM.UXC@NKCK　FREQVAL　NUMCOLS　1　COUNT　10,　
　SYSADM.PXC@CKNKMS　KEYCARD)　
　SHRLEVEL　CHANGE　REPORT　YES　

如果用户之前创建了 SYSPROC.DSNUTILU存储过程，就能选择 Run...按钮直接在 OSC 中调用 RUNSTATS Utility执行 RUNSTATS 命令，否则，用户只能在主机上通过 JCL 执行 RUNSTATS 命令。

选择 Details...可以查看详细的分析报告和数据冲突细节。

图 18. 详细的统计信息报告

查看原图（大图）

分析了 SQL 注释和统计信息之后，下面的步骤就是要查看 SQL 的执行路径，看看问题到底是由什么导致的。

SQL 执行路径图解

对于基于执行路径（Access Path 或 Access Plan）模型的数据库来说，一个 SQL 通常会有多种路径，“条条道路通罗马”，而路径选择的好坏直接决定了性能。SQL 执行路径细节众多，查看和分析起来需要经验，OSC 通过对 SQL 执行路径进行图解，使用户能够快速浏览和分析路径上的每一个节点的细节，包括表的访问方式、多表访问时的联合顺序和方式、排序选择、索引方式、每一步的输入输出以及开销等。以清单 4 中的示例 SQL2 为例，选择运行 Tools-> Access Plan Graph，得到如下图所示的执行路径图解：

图 19. SQL 执行路径图解

查看原图（大图）

通过选择右边图中的不同节点，可以查看该节点所涉及的所有属性。

图 20. 查看节点属性

若 SQL 非常复杂，执行路径节点多，用户可选择 Search Node，在不同的节点类型中进行搜索。选择 Bookmarks and History可以查看浏览的历史记录。

DB2 将执行路径以记录的形式存在 PLAN_TABLE中，从 Graph标签页切换到 Plan Table可以查看对应的数据。

图 21. 执行路径对应的 PLAN 表数据

查看原图（大图）

在执行路径图解界面上方的工具条中选择 Generate Report，可以生成文本和 HTML 格式的执行路径报告。

图 22. 生成执行路径报告

查看原图（大图）

用户还可以选择 Change Settings来自己定制自己喜欢的图解元素的颜色、字体等。

图 23. 图解的显示设置

查看原图（大图）

创建执行路径提示

DB2 优化器能够根据启发式规则自己为 SQL 选择执行路径。DB2 也提供了执行路径提示的功能，使用户能够人为的影响优化器，让优化器选择某个特定的执行路径，这是对 DB2 优化器的一个补充功能，优化器有时也会失误而选择差的执行路径。OSC 提供了友好的界面，使用户轻松的创建、验证和部署提示。返回码 +394 表示执行路径提示部署成功，+395 表示提示无效，需要分析来验证该提示的合法性。

OSC 监控和性能调优小结

前面介绍了 OSC 所提供的各大功能，这里我们小结一下使用 OSC 进行监控和性能调优的步骤。

连接到 DB2 目标子系统；

按照性能需要来定义和部署监控器，如过滤条件为执行时间超过 100 毫秒；

启动并运行监控器，当数据库应用程序运行时，OSC 捕获时间超过 100 毫秒的 SQL 并生成目标 workload；

对 workload 中的 SQL 进行注释分析，把握 SQL 的结构特点，查看表大小、谓语条件的过滤率等信息；

进行统计信息分析，生成收集和更新数据的 RUNSTATS 命令；

DBA 选择执行生成的 RUNSTATS 命令，完成后，再次监控发现，多数情况下，SQL 的执行性能能大大提升；

部分 SQL 由于结构不合理，或是缺少合适的索引，问题得不到解决，这就需要查看具体的执行路径图解，通过分析执行路径节点的细节来判断如何修改 SQL、如何建立合适的索引，高效 SQL 的编写和索引设计涉及到更深的知识，本文不作阐述；

当 DB2 优化器选择了较差的执行路径时，通过自己设计、创建和部署执行路径提示来进行修正，以提高 SQL 的性能。

图 24. OSC 监控和调优步骤

SQL 报表生成

SQL 报表包含 SQL 所涉及的三类信息：

表，包含表的统计信息、定义在之上的索引；

索引，包含索引访问的表、涉及到的谓语条件；

谓语条件，包含所涉及的表、执行路径和性能参数。

选择 Tools-> Query Reports得到如下图的界面，可以选择生成文本或 HTML 格式的所有报告或部分报告。设置保存路径，选择 Generate Report生成。

图 25. 生成 SQL 报表

查看原图（大图）

连接 IBM 技术支持服务

对于 DB2 z/OS 的用户来说，若遇到性能相关的问题，IBM 强大的技术支持服务是不可缺少并值得信赖的。为了重现用户环境，解决客户问题，OSC 提供了生成用户数据库 DDL、统计信息数据的 DML 等文件的功能。用户可以方便的直接将文件发送至 IBM 技术支持部门。

图 26. 指定需要技术支持服务的相关 SQL 或表

查看原图（大图）

图 27. 指定 PMR 或 ETR 编号 , 生成技术支持服务文件发送至 IBM

查看原图（大图）

结束语

Optimization Service Center 已成为 DB2 for z/OS 产品不可缺少的一部分，它为主机数据库的 SQL 和 Workload 性能分析提供了强大的功能，这也是 DB2 for z/OS 优于其他数据库解决方案的重要原因。