当前位置:首页 >> 计算机软件及应用 >>

DB2 维护和优化


DB2 维护和优化

2010 – 07

目录

1. DB2 逻辑设计和物理设计 2. DB2 维护和问题处理 3. DB2 应用优化方法 4. DB2 LOCK 基本概念 5. DB2 UTILITY介绍 6. 对应用开发的一些建议

1.逻辑设计和物理设计

数据库设计的基本步骤
概念设计(Conceptual Design)
产生描述业务需求数据和信息的数据模型 (Data Model)

逻辑设计(Logical Design)
将数据模型转换成数据库的逻辑表,并根据 正规化基本原则(Normalization Rule),分析和调整表的各属性

物理设计(Physical Design)
基于具体计算机的硬/软件特性,将逻辑设 计的对象定义到其物理介质

逻辑设计 根据提供的数据结构,确定相应的逻 辑模型 只依据所选定的数据库设计的模式方 法进行设计 (如:关系型数据库模式), 而不用考虑在具体数据库产品及机型 上的实现方式

逻辑设计所要进行的工作 确定表(table)的结构(structure) 确定列(column)的结构(structure) 确定 key 的columns 正规化 确定约束( constraints)关系

表的设计 数据模型中的每个实体(entity),对应 为关系数据库的一个表 每个表都是 1:M关系之端点 对M:M关系,必须要在其中加入中间 表,变成2个 1:M 关系 将2个1:1 的关系实体合为一个表

列的设计 一个实体中的每个属性(attribute) 对应为一个表的列 遵守列命名的规范 选择适当的数据类型及长度
DBMS提供的数据类型 用户自定义的数据类型

确定列的缺省值,或是否可为null 加 foreign key 列

列名的基本原则 命名要有意义 少用缩写 用缩写时,尽量用标准化的缩写 不同的词之间,用”_”隔开 考虑使用前后缀 相同的属性,出现在不同的表时, 命名要一致

列的顺序基本原则
将primary key columns 放在最前 接着是要频繁读的列 后面为读或修改较少的列 后面为可变长的列 最后放需要频繁修改的列
需要同时修改的列放在一起

DB2 列的数据类型
INTEGER SMALLINT FLOAT REAL DOUBLE PRECISION CHAR VARCHAR LONGVARCHAR GARPHIC VARGRAPHIC LONG VARGRAPHIC DATE BLOB ROWID TIME CLOB TIMESTAMP DBCLOB DECIMAL

Numeric

Character Graphic Date-Time Large Object System

选择数据类型的基本原则
选择与数据域存放内容最接近的数据类型 日期用DATE 型 时间用TIME型 需要同时存放日期和时间时,用 TIMESTAMP 型 对不含小数的数值数据,选择INTEGER, SMALLINT 和 DECIMAL其中的哪个类型,主 要考虑点是:存储所占空间大小及运算的效率 如果数据中仅含数字,一般用数字型数据类型
cardinality of a column determines access path selection

数字型考虑
定义 DECIMAL型列长度为奇数
DECIMAL(7,2) 和 DECIMAL(6,2)占同样长度

的存贮域 (4 bytes)
在数值范围可保证的情况下,用 SMALLINT 而不 用 INTEGER SMALLINT和 INTEGER型的计算性能比 DECIMAL型要高 DECIMAL 型显示性能比SMALLINT和 INTEGER型要好 DECIMAL 型字段总包含小数点

Date and Time vs Timestamp
DATE and TIME two columns TIMETAMP one column Comment TIMESTAMP is better for data access and modifications TIMESTAMP is easier for duration calculation DATE and TIME can save space

7 bytes to second only formatting functions are available

10 bytes

to TIMESTAMP provides greater microsecond accuracy no formatting DATE and TIME are better for function reporting

avoid choosing USA format as default, time is represented in hh:mm AM/PM, no second

VARCHAR
当列的大部分记录的数据长度小于最大 长度时,可以节约 DASD 存储空间 但此类型的每条记录,在其前,都要占额外 的2 bytes 在数据记录长度差别较大时,才可以达到 大量节约存储空间的目的 如果记录行的长度在修改时发生了变化, 可能会引起物理存储位置的变化 在对此类型的记录进行Sort时,将按该字 段定义的最大长度,先在其后面填满空格 (padded with space)后再Sort 处理此类型记录,要求较多的 CPU

VARCHAR 考虑
在DASD 空间足够时,尽量不要用可变 长列 对最大长度< 30 bytes的列,不要用可 变长类型 记录平均可节约空间<10 bytes,则不 要用可变长类型 放在表的靠后位置,但在需频繁修改的 列之前 考虑拆成多个定长的列

缺省值(DEFAULT)
NOT NULL WITH DEFAULT 在没有为该列赋值时,该列存放缺省值 Two options:
System 定义 User 定义

建议显式地赋缺省值

系统定义的缺省值
Data Type Numeric Fix-length string Varying-length string Date Time Timestamp ROWID Distinct Type (UDT) Default Value 0 blanks a string of 0 Current Date Current Time Current Timestamp System generated unique identifier default of source data type

用户自定义的缺省值
Type Constant Default Value any valid value that conform to the column data type

USER

content in the USER special register AUTHID of the process performing the Insert/Update

CURRENT SQLID

NULL

Null value

NULL
Null :用于该列无值,或无法确定该列的值 每条记录有一个单字节,作为 Null指示符 ( Indicator) 所有的表列,必须定义为如下属性之一:
Null
允许该列含null值(此为定义列时的缺省)

Not Null
不允许该列含null值

Not Null with Default
不允许该列含null值,当不赋值时,就用缺省值

NULL 用途
Null值在sort 时为最小值 Null也是一个值 对某些WHERE 或 HAVING 子句,将会将NULL值排除 在外 对null值进行算术运算时,结果也是 null primary key 不能定义为NULL 用 IS NULL 判断是否为 null条件 Null值之间不能进行大小比较 为列赋NULL值: SET column = NULL 在程序中,主变量后加INDICATOR变量(类型固定为 S9(4) COMP),此变量赋值<0,表示赋予该COLUMN NULL值

Null 考虑
对要进行算术运算的列,应避免定义为NULL 应用逻辑上,需要区分:列数据”无值”和”不知其值” 的不同 对 DATE, TIME 或 TIMESTAMP类型的列,特别是 需要放将来的日期时间信息时,可定义为NULL 对下面的统计类运算函数: AVG, COUNT, DISTINCT, SUM, MAX and MIN :以上函数将排除含null值的记录 Count(*) 函数包含有null值记录

ROWID
表定义了此类型的列,则DB2 会自动在INSERT 表记录时,生成一个唯一值放在此列 一个表只能最多有一个 ROWID 列 定义时,同时加GENERATE参数: ALWAYS 当新记录行INSERT时,DB2总会生成该 列的值(缺省) BY DEFAULT 当新记录行INSERT时, 只在未给该列赋 值时, DB2才自动生成该列的值

Keys
Unique Key
one or more columns that uniquely identifies a row

Primary Key
the most important unique key for a table only one for each table

Composite Key
a key that has more than one column

Primary Key 考虑
如果一个表不易组合出一个复合的Primary key, 或即使组出,也长度太长,可另加一个取 unique值的列作为Primary key
如: timestamp, system generated key

作为Primary key 的列数要尽量地少
持久性: 体现该行数据的基本特性 唯一性: 不同的行能够互相区分 稳定性: 其值不会被修改

Row 设计 每个page 最多可存放 255 rows 在记录长度太短或太长时,应考 虑选择适当的Pagesize,以避免 空间的浪费

正规化 NORMALIZATION

正规化 正规化处理,是尽可能地消除关系型数 据库结构上的模糊性 正规化按范式(Normal Form)步骤进 行:从最小限制 (1NF)到最大限制 (5NF).

做正规化处理的原因
增加数据库的适应性(Flexibility) 减少数据冗余 增强数据的一致性(consistency) 减少空间的浪费 保证充分地使用SQL语句所具有的 能力

第一范式 (1NF)
No multi-value attributes and no repeating group is allowed
StudentID 12345 Course 1101,1102,1103 2001,2002,2003 StudentID 12345 12345 12345 Course1 1101 2001 Course2 1102 2002 Course3 1103 2003 54321 54321 54321 Course 1101 1102 1103 2001 2002 2003

X X

54321

StudentID 12345 54321

ensure that data is represented as a table

第二范式 (2NF)
Non key attributes depend on the entire key
StdentID 12345 54321 CourseID 1101 2001 CourseName Maths English Grade A B

X
CourseName Maths English

StdentID 12345 54321

CourseID 1101 2001

Grade A B

CourseID 1101 2001

ensure that each column is defined in the correct table

第三范式 (3NF)
Non key attributes must not depend on any non-key attributes
CourseID CourseName Maths English TeacherID T01 T02 TeacherName Alan Bob

don’t store data that can either be derived from other columns or belong in another table

1101 2001

X
TeacherName Alan Bob

CourseID 1101 2001

CourseName Maths English

TeacherID T01 T02

TeacherID T01 T02

Boyce/Codd范式(BCNF)
more restricted version of 3NF every key column is a candidate key

Project 123 123 123 999 1 M :

Leader XX XX YY ZZ M : 1 :

Developer AAA BBB CCC AAA

Project 123 123 999

Leader XX YY ZZ

Leader XX XX YY ZZ

Developer AAA BBB CCC AAA

X

N M

removes dependencies between columns that are part of a Candidate Key

第四范式 (4NF)
no independently multi-valued components for the primary key

Manager XXX YYY YYY ZZZ 1 1 :

Award AW1 AW2 AW3

Report R123 R456 R789 R999

Manger

Award AW1 AW2 AW3

Manager XX XX YY

Report R123 R456 R789 R999

X

XXX YYY YYY

ZZ

M : M

removes multiple, independent dependents that are part of a Candidate Key

第五范式 (5NF)
no pairwise cyclic dependencies within composite primary key of 3 or more columns
ACTOR AAA BBB CCC DDD EEE FFF PLAY P123 P123 P246 P369 P789 P789 THEATER TH1 TH1 TH2 TH2 TH1 TH3 ACTOR AAA BBB CCC DDD PLAY P123 P123 P246 P369 P789 P789 PLAY P123 P246 P369 P789 P789 THEATER TH1 TH2 TH2 TH1 TH3 ACTOR AAA BBB CCC DDD EEE FFF THEATER TH1 TH1 TH2 TH2 TH1 TH3

X

EEE FFF

M M

:

N : M : N N

removes associations between multiple entities with interdependencies

正规化考虑
一般的数据库设计者,正规化到3NF为止. 一般而言,做前3级的正规化,可以得到总 的来说较为理想的效果 建议做到4NF, 保证设计结果在业务上容 易理解及表的KEY列的完整性的统一

约束(CONSTRAINTS)

约束(Constraint)
约束是由数据库管理器强制实施的一些规则。 包括:
唯一性约束(Unique Constraint) 参照性约束(Referential Constraint) 表数据校验约束(Table Check Constraint) 自动触发(Triggers) 域约束(Domain Constraint)

唯一性约束(Unique Constraint) 用来保证表中的KEY值插入时的唯一性 对这些KEY列值的任何修改,要效验其唯 一性 这种约束,是通过unique index来保证的 在对应多行修改时,唯一性效验会延迟到 所有的行修改结束之后才进行效验

唯一性约束的原则
定义为唯一约束KEY的每一列,必须定义 为NOT NULL 一个表,可以定义多个唯一性约束,但这 些唯一约束,其中只能一个为 primary key 对一个表中的同样的列(组),最多只能 定义一个唯一约束

参照完整性(Referential Integrity –RI)
RI 定义表之间或表内的数 据约束关系 对每个外键(foreign key) 的值,在其父键( Parent Key)所在的参照表中必须 有同样的父键值记录
Department
DEPTNO A01 B01 C01 DEPTNAME SALES PRODUCTION FINANCE MGRNO 00011 00021 00031

PK
Employee
EMPNO 00123 00246 00357 00468 FIRSTNAME AAAA BBBB CCCC DDDD LASTNAME AA BB CC DD A01 C01 A01 C01

FK
WORKDEPT

RI 术语
外键(Foreign Key)
一个表中的列(组)的值,依赖于本表或其它表的unique key 或 primary key的值 用于保证表之间的约束完整性(RI)

父键(Parent Key)
被本表或其它表的Foreign Key所参照依赖的 primary key 或 unique key

父表(Parent Table)
含有parent key的表

依赖表(Dependent Table)
含有foreign key的表

自参照表(Self Referencing Table)
Foreign Key 所参照的Parent Key 在同一个表中

DB2 RI的作用
在所有的使用DB2的环境都会起效果。如: CICS, TSO, 或 SPUFI等环境 对应用的数据起到加强的约束保证作用:
应用可以少写一些做数据效验的SQL DB2 系统会选择更有效的方式来保证约束 DB2 在效验foreign key 的值时,不会将其对应 的parent key值内容传到应用程序

RI –Insert记录时的保障 对parent table 插入记录不作限制 插入dependent table记录时,foreign key值必须在 parent table已存在 (例外 情形:foreign key 值为null) 对复合的(composite )foreign key来 说,当其一(部分)列为 null值,则必须 整个foreign key的列全为null值

RI -- Delete记录时的保障
在从parent table删除记录行时,就会自动同时对其 dependent rows 产生行为:(这些行为,在定义Foreign Key的ON Delete 规则中确定)
RESTRICT 在Foreign Key 值存在时,不能删除parent table对应记录

立即起作用(IBM SQL rules)

NO ACTION(Default) 在Foreign Key 值存在时,不能删除parent table对应记录

必须先删除dependent表记录 parent table行

在SQL语句结束时保证(SQL standard rules)
CASCADE

自动删除dependent rows
SET NULL

将dependent rows 的 foreign key 值置为 null foreign key的各列必须定义为NULL

RI - Update记录时的保障 如果父表行是parent row ,则不允 许update 其 unique key的值 如果父表行不是parent row ,则可 以update 其 unique key的值,但一 次只能updated 一行 允许update dependent table的 foreign key 值,但该值必须在 parent table存在

修改RI定义

要修改parent table 中的 parent key 的定义,必须先解除与dependent table的RI关系

RI 考虑
对主表数据行进行修改或删除操作前, 可能会需要先对(子)表的数据进行维 护 在如下情形,不要使用RI:
短期存放的数据 数据检查重复 从非DB2资源而来的每日更新的只读数 据 用 RI 不方便维护

数据效验约束(Check Constraints)
通过设置检验条件,强制表的数据列满足某特定的 取值条件。如: SEX in ( ‘M’, ‘F’, ’U’) ACC BETWEEN 1001 AND 9999 在数据 Update / Insert操作时自动进行检验 这些强制规则,不需要用应用程序来实现

设计时要考虑的其它方面内容 数据稽核(audit)要求 记录对数据进行修改操作的用户信息 数据历史要求 记录数据被修改前的数据,并考虑保 留多长时间 数据安全性(security)控制 “谁”有权利对“什么”数据进行”何种 方式”的存取

逻辑设计概要总结
逻辑设计主要进行的工作是: 对表及其列,确定其合适的数据结构 确定合适的 keys 正规化工作 约束的选择与权衡

物理设计 (Physical Design)

物理设计
将逻辑设计模型实施到具体的硬/软件 产品平台 利用DBMS的工具,针对具体的应用需 求,进行性能优化的设计

物理设计所完成的工作 生成物理的数据结构 确定数据存储的组织方式 设计并定义索引(index) 确定存储空间的分配 针对对数据的存取要求进行分析设计 针对存取的性能要求进行分析设计 保证表、列等资源符合命名规范

物理数据结构(Data Structure)

DB2 Storage Group (STOGROUP)
是DASD的一组数据卷(Volume) 表或索引所对应的数据集实际存放在这些 卷上。 一个STOGROUP之中的所有卷的设备类 型( device type)必须相同 同一DATABASE之中的不同资源可以在 不同的STOGROUP上
DB2 的STOGROUP 与DFSMS 的storage groups是不同的

STOGROUP 与 User Managed数据集的比较
DB2 的数据集,既可以是STOGROUP管理的 (DB2管理),也可以由用户自己管理
Storage Group 管理 system determines the datasets placement all datasets have the same characteristics User 管理 user determine the datasets placement different datasets can have different characteristics dataset is deleted when dataset remains even object is dropped object is dropped

SMS管理DB2 STOGROUP资源
用STOGROUP定义的数据集可以由 SMS (DFSMS in full)来管理 此时,由系统根据数据集的命名来决定 将数据集放到什么数据卷上 SMS可根据不同要求,定义不同的 storage classes 和 management classes 简化数据集管理维护工作

DB2 Database
逻辑概念(不涉及物理存储), 仅用于对 DB2的对象分组 出于性能考虑,一个database 中的 tablespaces数量不要 >50 :
对象数量增加时,DBD size会加大 在DB2的 main storage中,EDM pool的大 小是受到限制的

定义Storage Group 和Buffer Pool参 数,以免使用系统缺省值

DB2 Tablespace 定义表所在的物理VSAM dataset DB2 tablespaces有四种类型:
Simple Segmented Partitioned LOB (Large Object)

Simple Tablespace
以前的应用常用此类型 一个TS中可含有多个表,此时,在一个page 中会含有多个表的行(因此,定义此类TS常常 一个TS只含一个表) Freespace 在 tablespace层定义 在TS scan时,将会对该TS中的所有表scan 在数据行UPDATE/DELETE后,原用的空间 不会自动释放(对整个TS做REORG后才释 放) Drop表也不会释放(对整个TS做REORG后 才释放) 主要用于多个表关联性较大时(多个表的数 据可同时读出,以减少I/O) 每个数据集最大可达4GB,最多可扩展分 配16个数据集,整个TS最大可达到64GB

TB01 TB02

Segmented Tablespace
可以存放一个或多个表 多个表的数据分别放在不同的segment中 INSERT/UPDATE/DELETE时,空间管理 较好(会及时释放无用的空间) TS scan的效率较高 在mass delete效率好,因为此时只对 space map进行修改 drop tables后,空间可以马上被重用 Lock 扩大可以只到 table级 (而非直接 到 tablespace级)

Most preferred type of tablespace

Usage of Segmented Tablespace
SEGSIZE: 每个segment中的 page数量, 为4的倍 数,从4 到64 每个数据集最大可达2GB,最多可扩展分配32个数据 集,整个TS最大可达到64GB DB2数据集在做primary 和secondary空间扩展 ( extent)时,会以全 segment值进行上扩 为了优化sequential prefetch(TS scan时)的处理, 最好根据如下表定义SEGSIZE :
Buffer Pool Range 1 – 500 501 – 999 > 999 Segment Size 16 32 64

Partitioned Tablespace
一个TS中只能有一个table TS被分成不同单元,这些单元称为partition 每个partition对应不同的dataset 对large table效能高 必须同时定义Partition Index,且必须为 clustering index, 这些索引的列不会被update (V8前的要求,从V8开始,则无此限制) Partition key 范围可以用 ALTER命令修改(无 须drop tablespace 和index) 分开的datasets可以在不同的volumes (STOGROUP)上 Partition可以单独或并行处理 可以在partition 级进行REORG, COPY, RECOVER,LOAD等 可以用LOCKPART 对 partition 级上锁

Partitioned Tablespace Size
V7:最多254个分区,TS最大16TB; V8: 最多4096个PARTITION,TS最大达到128TB; 单个分区数据集空间的最大值,与是否用LARGE或DSSIZE参 数、以及NUMPARTS(分区数量)参数、PAGESIZE(V8)等 有关。 使用DSSIZE参数,可使单个分区最大为64GB. 并且,如果要求DSSIZE >4GB,则还需要 DFHSMS above V1.5, and EA-enabled等支持.

Partitioned TS 考虑
只有此类型的空间总量可以超过64GB 利用I/O, CPU 和Sysplex等系统资源进行 并行处理,提高处理的性能 可以将不同的分区放在不同的 DASD 卷,以避免资源的冲突

建索引的目的 提高性能 保证唯一性 指定数据组织和检索的顺序 定义分区表空间

用索引的好处
保证唯一性 特定的数据顺序顺序化 提高性能 表分区

直接数据存取 避免数据sort 避免tablespace scan 提高 SQL join的效率 实现RI

索引的开销
一个索引空间,需要额外的DASD空间 对记录的INSERT 和 DELETE 操作,需要对 索引做处理 对索引列做UPDATE时,要对索引做 delete 和 insert 处理 在做LOAD和REORG 功能时,为了 rebuild indexes,需要另外的资源和开销 如果经常发生index分裂(split),则需要更频 繁地进行 REORG

索引列考虑 索引列用于:
作为primary keys 作为foreign keys 限制记录唯一性 合计类 (aggregate function)的条件 存在性检验 与记录排序有关的功能 (order, group, union, distinct) 数据选取或过滤的条件

避免定义Index的情形
具有如下特性的Columns,应避免用来做索引:
值域太少的列(low cardinality) 数据取值分布很不规则的列(skewed distribution) 修改较频繁的列 数据长度较长的列 很多的取值为null的列 VARCHAR 或 VARGRAPHIC

数据量很少的table (< 10 pages)

定义Index的一些考虑
显式地定义 clustering index 在定义多列的index 时,将检索条件用得最频繁 的列放在最前面 对分区表空间,要严格限制非分区index的数目 让一个index的列数尽量最少 UNIQUE WHERE NOT NULL 为了增加数据的集的势(cardinality),需要在 index定义中考虑增加另外的列 将 index空间与表空间分开物理地址存放 对 index 和 tablespace使用不同的bufferpools
具体问题,具体分析

反正规化的(Denormalization)的理由 正规化过程,将一个数据信息元放在一个 地方 因此,正规化后,修改时对一个数据信息元 只修改一处 但要检索出完整的相互关联的数据信息, 可能要检索多处 出于提高性能的目的,进行反正规化,

反正规化的一些考虑点 如果不做反规范化,应用的性能是否可以 接受? 将要做的反规范化工作,是否会影响系统 的性能? 对某些特别的检索,反规范化是否会增加 其难度? 反规范化后,应用是否依然可靠?
反规范化仅用于提高性能

反规范化的种类
Type Reason Pre-joined Tables Save the cost of joining Mirror Tables Satisfy concurrent requirements Split Tables Allow concurrent access to different parts of the table Combined Tables Address one-to-one relationship Simplify critical reporting Report Tables Redundant Data Reduce the number of joins Repeating Group Reduce I/O Eliminate calculations Derived Data

反正规化的风险 重新造成数据的冗余 可能导致数据完整性问题

将反规范化背后的原因在 文档中描述清楚

视图(View)
View 是一个查询表达式,它选择:
(一个或多个)表的所有行的部分列 (一个或多个)表的部分行的所有列 (一个或多个)表的部分行的部分

对用户而言,View 和table可以同样地使用 可以用表与View合在一起再生成View 用来生成view 的table,称为BASE TABLE

用view的目的
基本的目的:

通过限制对某些数据的存取来 保护数据的机密性 简化对数据的存取方式

Views实例
Personnel view for personnel clerks who do not need to see salary details Salary view for salary clerks who do not need to see personnel details

DEPTNO AO1 A02 B03 B03

EMPNO 020 035 015 045

LASTNAME JAMES RIDGE INNES YOURDON

DEPTNO AO1 A02 B03 B03

EMPNO 020 035 015 045

SALARY 15000 28000 21000 26000

Personnel View

Salary View

tables和views的关系

View 考虑
要保持view 与其base tables的同步 在view定义时,要显式地写出列名,以提供完整 的定义文本 注意下面的对view 操作的一些限制:
View Type
join tables use functions use DISTINCT use GROUP BY and HAVING contain derived data using arithmetic expression contain constant eliminate columns without a default value

Restriction
cannot delete, update, or insert cannot delete, update, or insert cannot delete, update, or insert cannot delete, update, or insert cannot insert cannot insert cannot insert

物理设计其它工作
估计空间需求 进行下面的工作: software/hardware前提基础 考虑应用使用的要求 命名规范化 物理对象的详细描述 性能要求

估计DASD空间要求
Table Space/Index Space 的PRIQTY与SECQTY以 K bytes为单位分配 3390 磁盘, 1 TRACK = 48K 1 CYLINDER = 15 Tracks = 720K 每种PAGESIZE(BP)的最多可使用有效字节数目:
EDITPROC Page Size = Page Size = Page Size = Page Size =

4K 4056 4046

8K 8138 8128

16K 16330 16320

32K 32714 32704

NO YES

记录长度(Record length)的计算
Record 头(overhead):
6 byte :记录头 1 byte : 每个可null 列 2 bytes:每个可变长列 2 bytes:每个记录在id map中的RID值

Record length =
一个记录所有字段长度的和 + record overhead(8)

字段长度
Numeric data length:
Integer=4 bytes Smallint=2 bytes Float=8 bytes Real=4 bytes Decimal= packed decimal

Date/time data length:
Date=4 bytes Time=3 bytes Timestamp=10 bytes

Tablespace 空间要求
Rows per page= ((page size)-22)*((100-pctfree)/100)/ record length Number pages required=(total rows)/ (rows per page) Total pages=Number pages required* factor value Total tracks=total pages/pages per track

Index 的数据组织结构

Index 的数据组织结构

DB2 维护和问题处理

常见SQL ERROR CODE

-181-811 -805 -180 4% 4% 4% 6% -803 12%

-904 36%

-911 31%

Other 3%

-904 Other -911 -803 -180 -181 -811 -805

那些SQL语句需要检测SQLCODE?
Check SQL Codes
Cursors OPEN FETCH CLOSE Basic I/O SELECT INSERT UPDATE DELETE UOW COMMIT ROLLBACK

Check SQL Codes (cont.)
Misc GET DIAGNOSTICS CALL CONNECT SET

Skip SQL Code Checks
BEGIN DECLARE SECTION DECLARE STATEMENT DECLARE TABLE END DECLARE SECTION INCLUDE WHENEVER

SQL语句与SQLCODE的匹配
SQLCODE / SQL
+000 Normal +100 Not Found -180 –181 Invalid Date / Time -803 Duplicate Key -811 Multiple Rows -904 Unavailable Resource -911 Rollback Timeout SELECT DECLARE OPEN FETCH CLOSE INSERT UPDATE DELETE

N/A N/A N/A N/A N/A N/A N/A

SQL Code Normal
SQLCODE = 000 可能出现SQL WARNING SQLWARN0 = ‘W’ if : one of SQLWARN1-SQLWARNA = ‘W’ 例:SQLWARN1 = ‘W’ 字符型COLUMN的数据长度,比主变量 要长,SELECT时,会截断!

NOT FOUND
SQLCODE = +100 可能出现SQL WARNING CURSOR FETCH出现+100后,再 FETCH,仍会+100! 比较:VSAM 和QSAM文件出现END-OFFILE (10)后,再读出现其它错误! 注意处理,防止死循环!

MISMATCH

SQL Code -117

DB2 Error Code N/A

Cause The number of values specified does not match the number of columns implied or specified

Primary Responsibility SQL coding error

Problem Resolution Suggestions

This error typically happens when the column list of an SQL doesn’t match the host variable list. It can happen when SQL is coded to explicitly or implicitly select all columns in an SQL vs. an explicit list of host variables. When a column is added to the table, the explicit list will no longer match the select all list.

SQLState: 42802

DB2 Date, Time & Timestamp Errors
SQL Code -180 DB2 Error Code N/A Primary Responsibility Problem Resolution Suggestions Program Issue In batch programs, it may be helpful to do a DISPLAY of all dates, times and timestamps from the problem SQL to determine the cause of the problem. Make sure that all of these values are validated prior to moving them into host variables or SQL parameters. Failure to move a valid value to a newly added date, time or timestamp column after a program recompile can also cause this error. This happens when an out of range value is entered into one or more of the components of a date/time value. Display the date in the program and examine the output for the invalid value portion.

Cause An invalid date, time or timestamp value was entered into a host variable or SQL parameter.

-181

N/A

The value of a date/time value is not valid format

Program Issue

SQLState: 22007 for both SQL Codes

FETCH or CLOSE
DB2 SQL Error Code Code -501 N/A

Cause Attempted a FETCH or CLOSE on an unopened cursor

Primary Responsibility Program logic error

Problem Resolution Suggestions Check previous SQL codes for something that may have closed the cursor. If SQL codes are not methodically checked, an undetected rollback will cause a FETCH or CLOSE to get this return code.

SQLState: 24501

DB2 Duplicate Key Errors
DB2 Error Code Cause N/A Attempted to INSERT or UPDATE in violation of a UNIQUE INDEX constraint.

SQL Code -803

Primary Problem Resolution Suggestions Responsibili ty Program Issue A table can have multiple UNIQUE INDEXes. First, it is necessary to determine all of the UNIQUE INDEXes on a table. A query of SYSIBM.SYSINDEXES Next, review the program logic to make that it addresses all of the UNIQUE constraints. It may be necessary to handle this (803) SQLCODE on an INSERT or UPDATE and automatically increment a sequence number or timestamp milliseconds if the application dictates.

SQLState: 23505

DB2 Precompiler Timestamp Errors
DB2 Error Code N/A

SQL Code -805

Cause The DBRM or Package in not found in the Plan The DBRM consistency token does not match the load module

Primary Responsibility Compile Issue or JCL Issue

Problem Resolution Suggestions This can occur when a compile is partially successful or there is attempt to bind a package that is not in the plan. Determine the correct bind parameters and try again. This can occur when a compile is partially successful and the load module consistency token doesn’t match the current DBRM. If a recompile doesn’t resolve the problem, it may be necessary to STEPLIB over to the proper load libraries. In the production environment, this is usually caused by a failed production move. It may be necessary to recompile the program and move it back into production recompile can also cause this error.

-818

N/A

Compile Issue of JCL Issue

SQLState: 51002 & 51003

Consistency Tokens

DBRMlib

Loadlib

DB2 Multiple Rows Errors

DB2 SQL Error Cod Code Cause e -811 n/a More than one value was returned on an embedded SELECT.

Primary Responsibility Program or Data Issue

Problem Resolution Suggestions

This usually occurs when new data is added so that an embedded SELECT retrieves more than a single row result set. If you need to get the first row of a set based in a specific order, then the SELECT logic should be converted to a CURSOR with an ORDER BY clause followed by a FETCH.

SQLState: 21000

DB2 Unavailable Resources
SQL Code -904 DB2 Error Code C90080 or C90081 or C90097 Primary Responsibil Problem Resolution Suggestions ity DBA Issue This condition may appear for a few seconds during some database modifications and utility functions. DBAs will diagnose the cause of the problem and reset the pageset status. If this happens persistently or repeatedly, notify the DBA immediately! Program This is caused by a program which is either Issue missing commit logic or is not committing frequently enough. If commit logic is not found, it must be added to the unit of work cycle in the program. If commit logic is present, lowering commit WS002-ROWS-TOCOMMIT in the input parms may resolve the problem. DBA Issue This normally caused by a database reorg whose DRAIN request holds resources for a period that exceed the system timeout limit. This should resolve itself within a matter of seconds. If this situation persists, then contact the DBA immediately! DBA Issue This happens when a tablespace extends to the maximum number of extents or there is insufficient space to expand in the tablespace or indexspace STOGROUP. The DBA should be contacted immediately!

Cause The database resource is in a READ ONLY, STOPPED or COPY PENDING state. The maximum number of locks for a package or pageset has been exceeded.

C90096

C900BA

A utility DRAIN request exceeded the maximum time limit. A database tablespace failed to extend or find sufficient space allocation.

D70014 or D70025

SQLState: 57011

DB2 Unavailable Resources Message
SQLERRMC /SPUFI /JOB/dbidMSTR message:
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C900BE, TYPE OF RESOURCE 00000302, AND RESOURCE NAME DSNDB06 .SYSDBASE.X'000E8F' DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXOUA SQL PROCEDURE DETECTING ERROR 10.54.28 STC20414 458 458 458 458 458 458 DSNT501I -DU13 DSNIDBET RESOURCE UNAVAILABLE 458 CORRELATION-ID=ENTRTLU60046 CONNECTION-ID=AT1CUB33 LUW-ID=DSNDU10.LT1DU13.C62A5D2EC9CF=7052 REASON 00C90082 TYPE 00000200 NAME DIBSUAT .FSTCMST

Common Resource Type Codes

Type Object 100 Database 200 & 202 Tablespace 201 Indexspace 210 Partition 220 Dataset 230 Temporary File 240 Procedure 300 Page

Type Object 302 Tablespace Page 303 Indexspace Page 500 Storage Group 600 EDM Pool 700 Bufferpool 800 Plan 801 Package 901 Sort Storage

DB2 Deadlocks & Timeouts
DB2 Error Code C90088

SQL Code -911

Primary Cause Responsibility Problem Resolution Suggestions The current Program Issue This problem can be resolved by my unit of work has making sure that the logic of conflicting been rolled programs updates tables and rows in the back due to a same order. deadlock. The current unit of work has been rolled back due to a timeout. Program Issue This program waited on a lock for a DB2 pageset for a period that exceeded the system timeout limit. This can occur when there are long running units of work in the system or programs that do not commit frequently enough. If the program receiving this error is read-only, the SQL could be adjust to do uncommitted reading (WITH UR) to eliminate the locking problem.

C9008E

SQLState: 40001

DB2 MSTR MSG for TIMEOUT
10.32.40 STC15376 DSNT376I -DU11 PLAN=IBSEOPLN WITH 171 171 CORRELATION-ID=POOLAG630036 171 CONNECTION-ID=AT1CEB11 171 LUW-ID=DSNDU10.LT1DU11.C62A581240E1=18643 171 THREAD-INFO=CICSUSER:*:*:* 171 IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN= DSNTEP81 171 WITH 171 CORRELATION-ID=SQLRUN1 171 CONNECTION-ID=BATCH 171 LUW-ID=DSNDU10.LT1DU11.C62A56F80C8A=18592 171 THREAD-INFO=DIDL009:*:*:* 171 ON MEMBER DU11 10.32.40 STC15376 DSNT501I -DU11 DSNILMCL RESOURCE UNAVAILABLE 172 172 CORRELATION-ID=POOLAG630036 172 CONNECTION-ID=AT1CEB11 172 LUW-ID=DSNDU10.LT1DU11.C62A581240E1=18643 172 REASON 00C9008E 172 TYPE 00000D01 172 NAME 00000325.00003014

DB2 MSTR MSG for DEADLOCK
16.57.25 STC92331 DSNT375I -DU14 PLAN=IBSUOPLN WITH 640 640 CORRELATION-ID=ENTRTLUE0011 640 CONNECTION-ID=AT1CUB44 640 LUW-ID=BOCOMT.LT1DU14.C61F5D83FE24=74641 640 THREAD-INFO=CICSBUAT:*:*:* 640 IS DEADLOCKED WITH PLAN=IBSUOPLN WITH 640 CORRELATION-ID=ENTRFS610011 640 CONNECTION-ID=AT1CUB54 640 LUW-ID=BOCOMT.LT1DU14.C61F5D83D2B7=74636 640 THREAD-INFO=CICSBUAT:*:*:* 640 ON MEMBER DU14 16.57.25 STC92331 DSNT501I -DU14 DSNILMCL RESOURCE UNAVAILABLE 641 641 CORRELATION-ID=ENTRTLUE0011 641 CONNECTION-ID=AT1CUB44 641 LUW-ID=BOCOMT.LT1DU14.C61F5D83FE24=74641 641 REASON 00C90088 641 TYPE 00000302 641 NAME DIBSUAT .SCTAPTR .X'000040'

DB2 PE report for TIMEOUT
PRIMAUTH CORRNAME CONNTYPE ORIGAUTH CORRNMBR INSTANCE EVENT TIMESTAMP --- L O C K R E S O U R C E --PLANNAME CONNECT RELATED TIMESTAMP EVENT TYPE NAME EVENT SPECIFIC DATA ------------------------------ ----------------- -------- --------- ----------------------- ---------------------------------------CICSUSER PME1 CICS 00:23:35.03574550 TIMEOUT DRAIN W DB =DIBSPRD REQUEST =LOCK UNCONDITIONAL CICSUSER ENTR C62D95A3433D N/P OB =CMTEXAN STATE =S ZPARM INTERVAL= 60 IBSPOPLN AP1CPB22 DURATION=MANUAL INTERV.COUNTER= 1 HASH =X'000004A0' ------------ HOLDERS/WAITERS ----------HOLDER LUW=DSNDP20.LP1DP25.C62D957E0D9B MEMBER =DP25 CONNECT =UTILITY PLANNAME=DSNUTIL CORRID =B24CMDBB DURATION=ALLOCATN PRIMAUTH=CICSBPRD STATE =IX

DB2 PE report for DEADLOCK(1)
CICSUSER PEEA CICS 10:07:10.70330622 DEADLOCK COUNTER =9446K WAITERS = 2 CICSUSER POOL C62E1831CCA3 N/P TSTAMP =06/25/10 10:07:10.69 IBSPOPLN AP1CPB22 DATAPAGE DB =DIBSPRD HASH =X'029304A8' OB =PDTDCRD ---------- BLOCKER is HOLDER --*VICTIM*PAGE=X'001018A8' LUW=DSNDP20.LP1DP22.C62E1831CCA3 MEMBER =DP22 CONNECT =AP1CPB22 PLANNAME=IBSPOPLN CORRID =POOLPEEA0026 DURATION=COMMIT PRIMAUTH=CICSUSER STATE =X PROGNAME=PDOMP10 COLLID =PKGPOPD LOCATION=N/P CONTOKEN=X'18BFD2BA06EEC2FE' ---------------- WAITER ---------------LUW=DSNDP20.LP1DP26.C62E1831CB72 MEMBER =DP26 CONNECT =AP1CPB16 PLANNAME=IBSPOPLN CORRID =POOLPEEA0042 DURATION=MANUAL PRIMAUTH=CICSUSER REQUEST =LOCK WORTH = 17 STATE =U PROGNAME=PDOMP10 COLLID =PKGPOPD LOCATION=N/P CONTOKEN=X'18BFD2BA06EEC2FE'

DB2 PE report for DEADLOCK(2)
CICSUSER PEEA CICS DATAPAGE DB =DIBSPRD HASH =X'05E0339C' CICSUSER POOL C62E1831CCA3 OB =PDTDCRD ---------- BLOCKER is HOLDER ----------IBSPOPLN AP1CPB22 PAGE=X'00106F9C' LUW=DSNDP20.LP1DP26.C62E1831CB72 MEMBER =DP26 CONNECT =AP1CPB16 PLANNAME=IBSPOPLN CORRID =POOLPEEA0042 DURATION=COMMIT PRIMAUTH=CICSUSER STATE =X PROGNAME=PDOMP10 COLLID =PKGPOPD LOCATION=N/P CONTOKEN=X'18BFD2BA06EEC2FE' ---------------- WAITER -------*VICTIM*LUW=DSNDP20.LP1DP22.C62E1831CCA3 MEMBER =DP22 CONNECT =AP1CPB22 PLANNAME=IBSPOPLN CORRID =POOLPEEA0026 DURATION=MANUAL PRIMAUTH=CICSUSER REQUEST =LOCK WORTH = 17 STATE =U PROGNAME=PDOMP10 COLLID =PKGPOPD LOCATION=N/P CONTOKEN=X'18BFD2BA06EEC2FE'

Other Technical Resources

Area Experts /Monitor Tools DB2 COMMAND & UTILITY DB2 Messages DB2 Codes DB2 Web Site
www.ibm.com

DB2 COMMAND for Problem process
DISPLAY DATABASE -- Displays the status of a database. START/STOP DATABASE – Start/stop a list of databases or table spaces and index spaces. DISPLAY THREAD – Displays information about DB2, distributed subsystem connections, and parallel tasks. CANCEL THREAD – Cancels processing for specific Local or distributed threads. It can be used for parallel task threads. DISPLAY BUFFERPOOL -- Displays buffer pool information while DB2 is online. ALTER BUFFERPOOL – Sets or alters Buffer pool size while DB2 is online. DISPLAY TRACE – Displays the Status of DB2 traces. START/STOP TRACE – Start/Stop DB2 traces. MODIFY TRACE – Changes the trace events (IFCIDs) being traced for a specified active trace. TERM UTILITY – Terminates Execution Of a utility.

DB2 REPAIR UTILITY

DB2 authority

DB2 应用优化方法

什么是 Performance?

Performance 指的是完成某项功能所需的时间 和资源之总和

如何衡量Performance ?

响应时间(Response time)快慢 资源的使用量(Resource utilization) 吞吐量(Throughput)大小 并行(Concurrency)多少

影响Performance的因素

硬件环境 操作系统 DB2 子系统 应用设计 交易负荷量

硬件的影响 CPU Memory I/O Channels DASD Communication Network
System Performance and Tuning

OS的影响 CPU Management
job priorities

Workload Management
swapping

Memory Management
paging
System Performance and Tuning

DB2 子系统的影响
EDM Pool
DSNZPARM Buffer Pool Sort/Work Space

RID Pool Logging Locking

System Performance and Tuning

应用设计的影响 数据库设计 应用的设计逻辑 SQL 写法 Bind 选项 交易量
Application Performance and Tuning

Workload 影响 存取的方式
TSO, CICS, IMS, CAF

处理类型
Online, Batch

交易或处理量
Application Performance and Tuning

不同因素的影响所占比例
数据库设计 25% 应用设计 55% DB2 子系统 10% 通讯网络 5% MVS 系统 5%

8-2 法则:20% 的交易占有80% 处理量

调整优化(Tuning)的工作 调整优化(Tuning)的主要目的,是使资 源利用得到优化,以提高交易吞吐量, 减少交易之间的资源竞争,从而使应用 系统的工作能力极大化

DB2 Structure and Access Flow
Buffer Manager Data Manager Relational Data System (RDS) Application Program
Call Host Variables

Optimized Access Path
Indexes
-------------------

Pages Read Table Spaces Pages Written

Pages "Scanned"

Index Entries "Scanned" ________
________ ________ ________ Stage One ________ Qualified ________ Rows ________

________ ________ Columns ________

Rows "Scanned"

from ________ Stage Two Qualified Rows Rows "Scanned"

RID and SORT Work Areas

Temporary Work Files

RIDs Sorted

Rows Sorted

Tuning 总体概要
影响应用性能的主要因素包括:
Hardware MVS OS 参数 DB2 子系统参数 应用设计 交易流量(Workload)

为了优化应用,必须:
了解什么会要发生 了解什么正在发生 了解比较的基准 知道哪儿需要修改 知道调整优化后的影响

优化工具和操作方法

Tuning 的步骤 确定分析的范围(交易/JOB/程序) 发现是否在tablescan / non-matching index scan 分析packages处理逻辑 分析有问题的SQL语句 提出建议性的解决方案 对解决方案进行测试 解决方案的实际实施

限定分析的范围 – online(1)
For online – use DB2PM acct(short) report to find the top heavily used packages during peak time
Package ALL PROG CISOCKLS RGOMP09 SCOMCTL AIOMP05 SCSOSVCH SCSOTOA AIOMP04 CPSOCBOX AISOPMIB BCOMP08 #Occur 3158741 41469 82 341919 1134 204428 134887 4357 8362 111693 422 11.6K 5.17 9.24 3.32 4.99 12.13 108.19 10.11 3.79 SQL Avg 5.13 2.74 Elapse time 0.007322 0.03272 38.237408 0.008571 0.341714 0.006031 0.013798 0.05908 0.028796 0.00272 1.141205 CPU time 0.001316 0.026586 4.984422 0.000734 0.213271 0.000674 0.000657 0.01912 0.009558 0.000715 0.174284 Susp time 0.005539 0.000787 33.517027 0.006663 0.083179 0.003421 0.01309 0.037216 0.016702 0.001882 0.972678 #Occur*CPU 4156.903156 1102.494834 408.722604 250.968546 241.849314 137.784472 88.620759 83.30584 79.923996 79.860495 73.547848

限定分析的范围 – online(2)
another angle - use DB2PM acct(long) report to find the top heavily CLASS 7 consumers (packages)
Package CISOCKLS SCOMCTL CMOMP331 CISOACCU AIOMP05 CMOMP041 SCSOTOA EBOMP72 SCSOSVCH CMOMP061 CL7 CONSUMERS |======> 16% |===> 7% |==> 5% |==> 5% |==> 5% |=> 4% |=> 4% |> 3% |> 3% |> 3% #Occur 143889 989567 170342 281628 4849 42221 341312 2494 535551 21767 SQL Avg 2.71 5.05 3.76 4.58 8.28 54.21 4.99 82.5 3.63 52.79 Elapse time 0.08761 0.005163 0.021631 0.012709 0.730466 0.079253 0.009106 1.029202 0.004344 0.10075 CPU time 0.077242 0.001011 0.001835 0.0013 0.245264 0.006964 0.000877 0.52072 0.000964 0.00889 Susp time 0.00021 0.003702 0.019592 0.011456 0.459969 0.071195 0.008198 0.443822 0.002529 0.091269

限定分析的范围 –batch(1)
For batch – use job scheduling report (rpthist) to find the jobs in the critical path

限定分析的范围 –batch(2)
another angle - use DB2PM acct(long) report to find the top heavily CLASS 7 consumers (packages)
Package GPU080 G01B196B G01B148B G00B024B G01B194B G01B164B G00P076 G01B137B G01B001B G01B143B CL7 CONSUMERS ======> 15% ===> 7% ==> 5% ==> 4% ==> 4% => 3% ====> 8% > 1% > 1% > 1% #Occur 1 1 1 1 1 1 5 1 1 1 SQL Avg 27205 9833912 104915 25 5554473 4787137 169433.4 9 48445 20383 Elapse time 28:01.5 13:13.5 09:23.3 08:09.8 07:41.4 05:13.5 02:57.9 02:24.6 01:36.9 01:05.1 CPU time 01:42.0 04:05.4 13.72045 11.7752 05:22.5 01:55.8 27.13575 45.84065 14.83416 4.209997 Susp time 25:08.0 08:12.0 09:09.0 07:57.6 45.28782 02:43.2 02:18.9 01:22.9 01:16.3 59.62613

发现做tablescan的程序及其语句
SELECT DISTINCT A.COLLID, A.PROGNAME,A.QUERYNO,B.NAME,B.CARD,B.NPAGES FROM qualify.PLAN_TABLE A,SYSIBM.SYSTABLES B WHERE A.ACCESSTYPE='R' AND A.TNAME=B.NAME AND B.NPAGES > 10000 AND A.COLLID LIKE 'PKGeO%' ORDER BY B.NPAGES DESC;
COLLID PKGQOAI PKGQOAI PKGQOAI PKGQOAI PKGQOBE PKGQOCM PKGQOCM PKGQOCM PROGNAME AIOMP05 AIOMP05 AIOMP05 AIOMP05 BEOMP10 CMOMP041 CMOMP041 CMOMP041 QUERYNO 1442 1455 1483 1484 719 946 966 1101 NAME AITGRVS AITGRVS AITCRVS AITCRVS SCTVCHF2 SCTVCHF2 SCTVCHF2 SCTVCHF2 CARD 2822893 2822893 3056518 3056518 1018552 1018552 1018552 1018552 NPAGES 176431 176431 169807 169807 78333 78333 78333 78333

发现无匹配列的index scan程序及其语句
SELECT DISTINCT A.COLLID, A.PROGNAME,A.QUERYNO,B.NAME,B.CARD,B.NPAGES FROM qualify.PLAN_TABLE A,SYSIBM.SYSTABLES B, SYSIBM.SYSPACKAGE C WHERE A.ACCESSTYPE='I' AND MATCHCOLS=0 AND A.TNAME=B.NAME AND B.NPAGES > 10000 AND A.COLLID LIKE 'PKGQO%' ORDER BY B.NPAGES DESC;
COLLID PKGQOBE PKGQOFR PKGQOFR PKGQOFR PKGQOBC PKGQOBC PKGQOBC PKGQOBE PROGNAME BESOSCMD FROMP03 FROMP07 FROMP07 BCOMP07 BCOMP07 BCOMP10 BEOMP05 QUERYNO 983 383 580 615 382 395 427 1365 NAME BETOBLL FRTMST FRTMST FRTMST BCTDRFT BCTDRFT BCTDRFT BETCBCL CARD 10231128 2416719 2416719 2416719 386664 386664 386664 1826413 NPAGES 1023113 805573 805573 805573 77333 77333 77333 43487

分析 packages(1) 使用工具分析package中各语句的存取路径 (见后面的: Explain 和PLAN_TABLE) 查明 tablescan和non-matching index scan 语句 查明表和索引的实际行数和page数 – 如果需 要,做Runstats 列出相关表的各索引定义–查SYSINDEXES和 SYSKEYS 查明clustering index 的cluster率(ratio)

分析 packages(2)
下面的SQL例子,用来分析表记录按索引某列的数据取值的 分布情况和过滤因子(filter factor)
Select TYPE, COUNT(*) as CNT from IBSSIT.CITFXL group by TYPE order by TYPE, CNT desc;
TYPE CNT ---------+-------21 54831 20 83 1

Select ID_NO, TYPE, COUNT(*) as CNT from IBSSIT.CITFXL group by ID_NO, TYPE order by ID_NO, TYPE, CNT desc;
ID_NO TYPE CNT ---------+---------+---------+---------+--------733556890 21 1 72626948X 21 1 G51519271 21 1 H52625196 21 1 K12055223 21 1

filter factor--Default

分析有问题的SQL语句(1) 主要分析 “WHERE” 子句 如果表很小,则tablescan 是可以接受的 “OR” 是不可索引的(non-indexable) 如果用到索引列不是该索引前面的列 -> nonmatching indexscan 主变量类型/长度与列不匹配 -> non-indexable 通配语句: “%var%’ -> non-indexable “< >”谓词是 non-matching的 列函数或表达式--如:substr() ,是non-indexable

分析有问题的SQL语句(2)
IN (val1,..,valn) 是indexable 谓词的书写顺序:在indexable 时,一般没有关系 e.g. C1=x AND C2=y 与 C2=y AND C1=x, 如果C1、C2(或其一)为INDEX KEY,则两种写法等价 谓词的书写顺序:在non-indexable 时,应将过滤因子低的放在前面 e.g. C1=x AND C2=y 与 C2=y AND C1=x, 如果C1、C2都非为INDEX KEY,且C1的过滤因子低(即:排除数据 快),则用前面的写法,效率较高 有时,表的join顺序有关系 e.g. Select from table1, table2 / table2, table1 可能导致表的内部处理有区别 尽量避免仅以低cardinality 的列为条件列 e.g. TYPE=’21’ BK=‘B000000’

建议性解决方案(1) 技术层面:
拆开SQL语句 加 index 修改index,加column以提高过滤因子(filter factor) 将“OR” 改成 IN (list),如能用BETWEEN更好 改正不匹配的主变量类型/长度 加“OR 0=1”语句,抑制不愿意使用的索引 重写SQL 重新考虑业务或设计的方案逻辑

建议性解决方案(2)

业务层面
限制查询的输入数据. 如: “账号” 非空 限制查询的输出记录数. 如: 日期范围必须在一个月之内 不允许进行范围太泛的模糊查询. 如: 按”柜员号”查询时,必须至少输入一个字符 附加其它的特别的查询条件. 如: 加”地区代码”. 尽量在前端而不是在主机端做数据控制和检验

测试解决方案 在DEV/SIT 环境对修改的内容做测试 尽可能用PRD的数据量做测试 用SPUFI来估计 SQL 修改的效果 用TMONDB2 看CPU处理时间或 getpage总 数等 用TMONDB2/DB2PM/Explain等分析新SQL 语句的存取路径

解决方案的实施 遵从版本控制流程 尽可能地做预演练和回退测试 在实际环境用Explain确认存取路径 用RMF/DB2PM/TMON等工具所提供的性能报告, 监控分析运行性能

Explain 及 PLAN_TABLE

Bind 所做的处理

DBRM

CATALOG SYSIBM TABLES

SQL DB2 OPTIMIZER

DIRECTORY

ACCESS PATH

存取路径(Access Path)的信息

DB2 Catalog 表:
SYSIBM.SYSPACKDEP SYSIBM.SYSPLANDEP

独立产品:
IBM DB2PM(PE), CA Plan Analyzer, BMC SQL-Explorer,ASG TMON

EXPLAIN 表:PLAN_TABLE

Explain 功能 EXPLAIN 功能产生SQL语句的存取路径信息 在做EXPLAIN 时,Explain 表PLAN_TABLE 必须存在 生成 PLAN_TABLE 的样本脚本放在: SDSNSAMP(DSNTESC) 主要用于SELECT 语句 Explain 不能描述对 LOB 表的存取路径

做Explain的方法

对某一个 SQL 语句进行Explain :
在SQL 语句之前加 EXPLAIN命令. 如:
EXPLAIN ALL SET QUERYNO=1234 FOR SELECT COL1, COL2, COL3 FROM TABLEX WHERE COL4=?

对Plan/Package进行Explain:
Bind/Rebind 的 EXPLAIN(YES)子句

Explain 所进行的工作

PLAN_TABLE
Access Paths

PACKAGE PLAN SQL

DB2 OPTIMIZER

DSN_STATEMENT_TABLE Cost Estimates

DSN_FUNCTION_TABLE UDF Resolution

PLAN_TABLE

Explain后,在 PLAN_TABLE中的信息可用于监控、 分析和优化性能 是否用到了索引,以及是如何使用索引的 对表的存取的顺序(多表join时) 是否需要进行数据SORT 是否要求Tablespace 锁及锁的模式 检索语句能否并行处理(parallelism)

Plan_Table 列及描述(1)
QUERYNO QBLOCKNO PLANNO METHOD 0 1 2 3 4 TNAME ACCESSTYPE I I1 N M MI MU MX R MATCHCOLS ACCESSNAME INDEXONLY Y N UNIQUE IDENTIFIER FOR THIS EXPLAIN QUERY (SELECT) POSITION STEP WITHIN QUERY BLOCK FIRST TABLE ACCESS NESTED LOOP JOIN MERGE SCAN JOIN ADDITIONAL SORT HYBRID JOIN TABLE NAME INDEX ONE-FETCH INDEX SCAN INDEX USING “IN” VALUES MULTIPLE INDEX SCAN INTERSECTIN OF RID FOR MULTIPLE INDEXES UNION OF RID FOR MULTIPLE INDEXES NAME FOR ONE OF INDEXES USED TABLESPACE SCAN NUMBER OF COLUMNS OF MATCHING INDEX SCAN NAME OF INDEX INDEX ALONE SATISFIES DATA REQUEST TABLE MUST BE ACCESSED ALSO

Plan_Table 列及描述(2)
SORTN_UNIQ Y N REMOVE DUPLICATES NO SORT SORT FOR JOIN NO SORT SORT FOR ORDER BY NO SORT SORT FOR GROUP BY NO SORT REMOVE DUPLICATES NO SORT SORT FOR JOIN NO SORT SORT FOR ORDER BY NO SORT SORT FOR GROUP BY NO SORT

SORT FLAGS FOR 'NEW' TABLES

SORTN_JOIN

Y N

SORTN_ORDERBY

Y N

SORTN_GROUPBY

Y N

SORTC_UNIQ

Y N

SORT FLAGS FOR 'COMPOSITE' TABLES

SORTC_JOIN

Y N

SORTC_ORDERBY

Y N

SORTC_GROUPBY

Y N

Plan_Table 列及描述(3)
TSLOCKMODE blank NO LOCK ACQUIRED

S U X IS IX SIX N NS NIS NSS SS TIMESTAMP PREFETCH COLUMN_FN_EVAL S L R S blank MIXOPSEQ VERSION COLLID

SHARE UPDATE EXCLUSIVE INTENT SHARE INTENT EXCLUSIVE SHARE WITH INTENT EXCLUSIVE UR ISOLATION AT EXECUTION, NO LOCK FOR UR, S LOCK FOR CS OR RR AT EXECUTION, NO LOCK FOR UR, IS LOCK FOR CS OR RR AT EXECUTION, NO LOCK FOR UR, IS LOCK FOR CS, S LOCK FOR RR AT EXECUTION, IS LOCK FOR UR AND CS, S LOCK FOR RR TIME AT WHICH THE EXPLAIN STATEMEN WAS PROCESSED SEQUENTIAL PREFETCH LIST PREFETCH AT DATA RETRIEVAL (STAGE 1) AT SORT (STAGE 2) AFTER RETRIEVAL AND AFTER SORT SEQUENCE OF STEPIN A MULTIPLE INDEX OPERATION VERSIONIDENTIFIER OF THE PACKAGE BEING EXPLAINED COLLECTION ID FOR THE PACKAGE BEING EXPLAINED

Plan_Table 列及描述(4)
ACCESS_DEGREE ACCESS_PGROUP_ID JOIN_DEGREE JOIN_PGROUP_ID SORTC_PGROUP_ID SORTN_PGROUP_ID PARALLELISM_MODE I C X blank MERGE_JOIN_COLS CORRELATION_NAME PAGE_RANGE JOIN_TYPE NUMBER OF PARALLEL TASKS ACTIVATED BY A QUERY ID OF PARALLEL GROUP ACCESSING NEW TABLE NUMBER OF PARALLEL TASKS IN JOINING COMPOSITE TABLE WITH NEW TABLE ID OF PARALLEL GROUP JOINING COMPOSITE TABLE WITH NEW TABLES PARALLEL GROUP ID FOR PARALLEL SORT OF COMPOSITE TABLE PARALLEL GROUP ID FOR PARALLEL SORT OF NEW TABLE QUERY I/O PARALLELISM QUERY CPU PARALLELISM QUERY SYSPLEX PARALLELISM NO PARALLELISM NUMBER OF COLUMNS BEING JOINED IN A MERGE SACN JOIN (METHOD = 2) CORRELATION NAME FOR TABLE OR VIEW Y = TABLE IS QUALIFY FOR PAGE RANGE SCREENING FULL OUTER JOIN LEFT OUTER JOIN (ALSO RIGHT OUTER JOIN) GROUP_MEMBER MEMBER NAME OF DB2

Plan_Table 列及描述(4)

WHEN_OPTIMIZE

B R blank

AT BIND TIME BUT WILL REOPTIMIZATISE AT RUN TIME IF REOPT(VARS) IS SPECIFIED AT RUN TIME USING INPUT VARIABLES AT BIND TIME USING DEFAULTS TYPE OF SQL OPERATION PERFORMED TIMSTAMP WHEN BIND TOOK PLACE ID FOR OPTIMIZATION HINT USED DURING ACCESS PATH SELECTION OPTHINT ID IF USED

QBLOCK_TYPE BIND_TIME OPTHINT HINT_USED PRIMARY_ACCESSTYPE D blank

DIRECT ROW ACCESS NO DIRECT ROW ACCESS

检索PLAN_TABLE的信息

针对独立的 SQL语句

SELECT * FROM PLAN_TABLE WHERE QUERYNO = 1234 ORDER BY TIMESTAMP DESC, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ

针对某个PLAN 中的DBRM

SELECT * FROM PLAN_TABLE WHERE APPLNAME = ‘PLAN1’ ORDER BY PROGNAME, QUERYNO, TIMESTAMP DESC, QBLOCKNO, PLANNO, MIXOPSEQ

针对某Collection 中的某个 Package

SELECT * FROM PLAN_TABLE WHERE PROGNAME=‘ABC’ AND COLLID=‘XYZ’ ORDER BY COLLID, PROGNAME, QUERYNO,TIMESTAMP DESC, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ

如何Explain (使用TMONDB2)(1)

Online Explain
TTTTTTTTTT T SSSSS TTTTTTT PPPPPP L EEEEE X X T S T P P L E X X T S T PPPPPP L E XX T SSSSS T P L EEEEE XX T S T P L E X X T S T P L E X X T SSSSS T P LLLLL EEEEE X X *** TST SYSPLEX--BOCOM UAT/TST SYSTEM *** DATE = 11/01/06 TIME = 14:16:32 SYSTEM = T101 TERMID = TCP01510

logon applid=tdb2t11

如何Explain (使用TMONDB2)(2)
Time: 14:23:36 "A S G T M O N for D B 2" Version: 4.0 Date: 04/11/06

ASG

TTTTTTTTT MMM MMM OOOOOOO NNN NNN TTTTTTTTT MMMM MMMM OOOOOOOOO NNN NNN T TTT T MMMMMMMMM OOO OOO NNNN NNN TTT MMM M MMM OOO OOO NNNNN NNN TTT MMM MMM OOO OOO NNN NNNNN TTT MMM MMM OOO OOO NNN NNNN TTT MMM MMM OOOOOOOOO NNN NNN for TTT MMM MMM OOOOOOO NNN NNN DB2

This licensed work is confidential and proprietary, and is the property of Allen Systems Group, Inc. Reproduction, disclosure, or use without the specific written authorization of Allen Systems Group, Inc. is strictly prohibited. Copyright 1990, 2003 Allen Systems Group, Inc.

USER ID: didl010

PASSWORD:

COLOR/EDS (Y/N): Y

如何Explain (使用TMONDB2)(3)
JOBNAME:TMONDB2 DATE:04/11/06 SYSID :T101 TIME:14:29:20 T M O N f o r D B 2

VERSION: 4.0 DB2 Subsystem Selection

CYCLE: MMSS Commands:CON=dbid, DELTA=yes/no, COLUMNS _Cntls SSN Sysid Activ %Qued Complete Commits Indoubt Aborts Exceptns Status CPUTime Paging Suspnds TmOuts DLocks CheckPts PagI/O _ DU12 T101 5 0.00 2302328 23995642 0 20079496 0 ACTIVE 18.58H 92476 314459 152 34 26103 4.61

COMMAND:

如何Explain (使用TMONDB2)(4)
JOBNAME:TMONDB2 DATE:04/11/06 DB2 :DU12 TIME:14:37:48 COMMAND: 1 2 3 4 5 6 10 Current Thread Activity Current System Activity Exception Monitor Active Job Summary Collection Analysis Supertrace Q 7 8 9 10 11 12 Quick Access Menus History Analysis Monitor Controls Advanced Functions DB2 EXPLAIN Utility SQL Analyzer Data-Sharing T M O N f o r D B 2

VERSION: 4.0 Primary Selection Menu

如何Explain (使用TMONDB2)(5)
JOBNAME: TMONDB2 DB2 : DU12 COMMAND: SELECT ONE OF THE FOLLOWING OPTIONS: PLAN PROCESSOR: PLAN NAME : BYPASS DBRM(S)?: N USE SHADOW TBL?: N/A PACKAGE PROCESSOR: COLLID : PKGDOAI PACKAGE : * SQL PROTOTYPING: NEW SQL? : N PDS(MEMBER) OR SEQ DATASET : DB2 EXPLAIN Utility DATE: 11/06/06 TIME: 13:52:20

如何Explain (使用TMONDB2)(6)
JOBNAME: TMONDB2 DB2 : DU12 COMMAND: PLAN : N/A LOCATION: DSNDU10 COLLID VERSION PKGDOAI PKGDOAI PKGDOAI PKGDOAI PKGDOAI PKGDOAI PKGDOAI PACKAGE AIOERROR AIOMP03 AIOMP04 AIOMP05 AIOMP06 AIOMP07 AIOMP08 Package Summary DATE: 11/06/06 TIME: 13:52:20

COLLID : PKGDOAI PACKAGE LIST: * CONTOKEN 1760DB0408C14FC0 17AA68A40B5A2E06 17F32AFF046F3CCA 17E712E11DFD24C6 17A6A8370751A2AC 177A796F0F5F29D8 1766B1760B48AB00 TDB2T11

# PKGS: 38

BIND DATE/TIME 05/29/04 11:10:09 07/18/05 11:17:19 10/24/06 10:20:33 08/08/06 11:26:19 06/24/05 14:22:20 05/24/05 15:19:34 05/24/05 15:19:34 PF KEY ASSIGNMENTS = PA1

HELP INFORMATION = PF1

如何Explain (使用TMONDB2)(7)
JOBNAME: TMONDB2 DB2 : DU12 COMMAND: PLAN : PACKAGE : CONTOKEN: REOPTVAR: NO. STA 16 C 17 C 18 C 19 C 20 C COMMAND: NO. STA STMT# SECT# STATEMENT TYPE #QRYS #STEPS EXPLAIN DATE/TIME Current SQL Statements in DBRM/Package DATE: 11/06/06 TIME: 14:09:03

N/A AIOMP05 17E712E11DFD24C6 NO KEEPDYN: NO STMT# 1344 1352 1361 1369 1380 SECT# 3 4 5 6 7

LOCATION : COLLID : BIND DT/TM: DEFERPREP :

DSNDU10 PKGDOAI 08/08/06 11:26:19 N/A

OWNER : EXPLAIN: # STMTS: COSTING:

IBSDEV NO 108 N/A

STATEMENT TYPE #QRYS #STEPS DECLARE CURSOR 0 0 DECLARE CURSOR 0 0 DECLARE CURSOR 0 0 DECLARE CURSOR 0 0 DECLARE CURSOR 0 0 SQL Statement Prototypes

EXPLAIN DATE/TIME

HELP INFORMATION = PF1

TDB2T11

PF KEY ASSIGNMENTS = PA1

如何Explain (使用TMONDB2)(8)
JOBNAME: TMONDB2 11/06/06 DB2 : DU12 COMMAND: EXP SQL STATEMENT BROWSE DATE: TIME: 14:10:05

BROWSE SQL STATEMENT ONLY, NO CHANGES ALLOWED RECORDS: 18 LENGTH: 67 VIEW: 1..67 ROW 1...+....|....+....|....+....|....+....|....+....|....+....|.....67 00001 PLAN= STM#=1361 COLID=PKGDOAI AUTHID=TMONDB2 00002 LOC=DSNDU10 PKG=AIOMP05 PLAN_TABLE=N/A 00003 TOKEN=17E712E11DFD24C6 BIND STA=COMPILED QUAL=IBSDEV 00004 00005 DECLARE CURSOR-GRVS-110 CURSOR FOR 00006 SELECT * 00007 FROM AITGRVS 00008 WHERE 00009 ( AC = : AIRGRVS.GRVS-AC ) 00010 AND 00011 ( TX_DT >= : WS-VARIABLIES.WS-B-DATE ) 00012 AND 00013 ( TX_DT <= : WS-VARIABLIES.WS-E-DATE ) 00014 AND 00015 ( 00016 ( STS = : AIRGRVS.GRVS-STS )

如何Explain (使用TMONDB2)(9)
JOBNAME: TMONDB2 Explain Detail DATE: 11/06/06 DB2 : DU12 PAGE 1 OF 2 TIME: 14:22:56 COMMAND: TMDB21111I - EXPLAIN PROCESSING SUCCESSFUL PLAN : N/A LOCATION : DSNDU10 STMT TYP: SELECT PACKAGE : AIOMP05 COLLID : PKGDOAI STMT # : 1361 CONTOKEN: 17E712E11DFD24C6 EXPL DT/TM: 11/06/06 14:22:56 SQL COST: 4 +---------------------------- EXPLAIN DETAIL ----------------------------+ QUERYNO : 1 PARALLEL_MODE : N/A SORTN_UNIQ : NO QBLOCKNO : 1 ACCESS_DEGREE : N/A SORTN_JOIN : NO PLANNO : 1 ACCESS_PGROUP : N/A SORTN_ORDERBY: NO TABNO : 1 JOIN_DEGREE : N/A SORTN_GROUPBY: NO CREATOR : IBSDEV JOIN_PGROUP : N/A SORTN_PGROUP : N/A TNAME : AITGRVS JOIN_TYPE : N/A SORTC_UNIQ : NO TSLOCKMODE : INTENT SHARE PAGE_RANGE : N/A SORTC_JOIN : NO ACCESSTYPE : BY AN INDEX MERGE_JOIN_COL: N/A SORTC_ORDERBY: NO ACCESSCREAT: IBSDEV MATCHCOLS : 1 SORTC_GROUPBY: NO ACCESSNAME : AITGRVS_I2 INDEXONLY : NO SORTC_PGROUP : N/A PREFETCH : NO PREFETCH MIXOPSEQ : N/A WHEN_OPTIMIZE: METHOD : 1ST TABLE ACCESSED GROUP_MEMBER : DU12 COL_FN_EVAL: DATA MANIPULATION QBLOCK_TYPE : SELECT PRIMARY ACC: HINT_USED : HELP INFORMATION = PF1 TDB2T11 PF KEY ASSIGNMENTS = PA1

如何Explain (使用DB2PM)(1)

Batch Explain
//DB2PMEXP JOB CLASS=A,MSGLEVEL=(1,1),MSGCLASS=X //PMV710 EXEC PGM=DB2PM //STEPLIB DD DSN=DGO.V7R1M0.SDGOLOAD,DISP=SHR //JOBSUMDD DD SYSOUT=* //SYSPRMDD DD SYSOUT=* //SYSIN DD * GLOBAL(INCLUDE(SUBSYSTEM(DU12)) ) EXPLAIN PACKAGE(PKGDOAI.AIOMP05) SSID(DU12) ACCTYPE(ALL) LEVEL(DETAIL) FORCE(YES) EXEC

如何Explain (使用DB2PM)(2)

1REPORT ON:04/06/06 10:26:38

DB2 PM (V7) EXPLAIN SUMMARY REPORT

SUMMARY PAGE USER AUTHID: ESSQUS
PAGE NO

THE FOLLOWING 1:

1 DB2 PM EXPLAIN REQUESTS WERE PROCESSED:

DQ11 PACKAGE : ESSQUS .G00P222 DETAIL REPORT REQUESTED DBRM/PACK STMT TYP XXXXXXX 128 P MATCHING INDEX SCAN(1/2)-DATA PAGES XXXXXXX 140 P MATCHING INDEX SCAN(1/2)-DATA PAGES XXXXXXX 154 P S TABLE SPACE SCAN-NO INDEX WILL BE USED XXXXXXX 394 P MATCHING INDEX SCAN(2/2)-DATA PAGES XXXXXXX 574 P INSERT STMT WITHOUT USE OF AN INDEX XXXXXXX 613 P MATCHING INDEX SCAN(2/2)-DATA PAGES

1-1 1-4 1-6 1-8 1-11 1-13

DB2PM EXPLAIN PROCESSING COMPLETED.

如何Explain (使用DB2PM)(3)
1ACTUAL AT:04/06/06 10:26:38 LOCATION: DSNDQ10 SUBSYSTEM:DQ11 DB2PM (V7) PAGE : 1-6 EXPLAIN PACKAGE DB2 RELEASE: V7 XXXXXXX USER AUTHID: XXXXXX DETAIL CURR.SQLID : SYS006

PACKAGE LOCATION :DSNDQ10 PACKAGE COLLECTION:XXXXXX PACKAGE ID :XXXXXXX PACKAGE VERSION ID: STATEMENT NUMBER : 154 0SQL STATEMENT READ FROM SYSIBM.SYSPACKSTMT: DECLARE CURSOR-3 CURSOR WITH HOLD FOR SELECT APP_IDN_SKY, CRY_CDE, ACT_IDN_SKY, LST_UPD_DTE, LST_UPD_TME, LST_UPD_UID, LST_UPD_NBR FROM XXXXXXX WHERE ACT_IDN_SKY = :DCLTGEN222.ACT-IDN-SKY

如何Explain (使用DB2PM)(4)
THE ACCESS PATH CHOSEN BY DB2 AT 10:25:59.5 ON 2006-04-06 +------------------------------------------------------------------+ | TABLE SPACE SCAN - NO INDEX WILL BE USED | | ONLY THE ACTUAL TABLE IN THE SEGMENTED TABLE SPACE IS SCANNED | | STANDARD SEQUENTIAL PREFETCH WILL BE PERFORMED | | LOCK MODE IS SHARE LOCK FOR THE PAGE IN THE SEGMENTED TABLE | | PAGE RANGE SCAN WILL NOT BE USED | +------------------------------------------------------------------+ TABLE: XXXXXX.XXXXXXX ----------------------------------------------------STATSTIME: 2006-04-06-10.22.27.969445 TB TYPE : TABL ACTUAL AT:04/06/06 10:26:38 DB2PM (V7) PAGE : 1-7 EXPLAIN PACKAGE DB2 RELEASE: V7 LOCATION: DSNDQ10 G00P222 USER AUTHID: XXXXXX SUBSYSTEM:DQ11 DETAIL CURR.SQLID : SYS006 CREATED : 2005-12-21-17.12.41.567911 ALTERED : 2005-12-21-17.30.45.278474 ROWS : 892579, COLUMNS : 7 , ROWLENGTH: 45, EDIT PROC.: % PAGES : 90, DBASE ID: 262, AUDITING : NONE, VALIDPROC.: ACT.PAGES: 11020, TABLE ID: 1810, STATUS : , TABCREATOR: ESSS01 TABLE XXXXXX .XXXXX HAS THE FOLLOWING AVAILABLE INDEX(ES): XXXXXXX - UNIQUE: YES, CLUSTERING:YES, CLUST.RAT: 99%, (APP_IDN_SKY , CRY_CDE )

DB2选择存取路径

CATALOG

1 2

从 Catalog表获得统 计信息 对每个谓词,估计其满足条 件的记录行数占总记录行数 的百分比 产生所有可能的存取路径, 再比较每个存取路径的消耗

SYSCOLDIST SYSINDEXES SYSCOLDISTSTATS SYSINDEXPART SYSCOLSTATS SYSCOLUMNS SYSINDEXSTATS SYSLOBSTATES

SYSTABLEPART SYSTABLES SYSTABLESPACES SYSTABSTATS

DBRM

OPTIMIZER

3 4

PATH 1 $$$

PATH 2 $$

PATH 3 $$$$

选择最优的存取路径,并 将其存到directory表

ACCESS PATH

DIRECTORY

用于选择存取路径的Catalog表统计信息 (对非 分区表)
SYSTABLES CARDF NPAGES PCTROWCOMP EDPROC SYSCOLUMNS COLCARDF HIGH2KEY LOW2KEY SYSTABLESPACE SYSINDEXES NACTIVE CLUSTERED CLUSTERING CLUSTERRATIO FIRSTKEYCARDF FULLKEYCARDF NLEAF NLEVELS SYSCOLDIST COLVALUE FREQUENCYF TYPE CARDF COLGROUPCOLNO NUMCOLUMNS Total # of rows in the table Total # of pages on which rows of this table appear Percent of the active rows that are compressed Not blank indicates that an exit is used Number of distinct values for the column The second highest value of the column The second lowest value of the column Number of active pages in the tablespace Whether table is actually clustered by index Whether index was created as CLUSTER % of rows in sequence by this index # of distinct values of the first key column # of distinct values of the full key # of leaf pages in the index # of levels in the index tree Actual index column value that is being counted # of rows that contain the value, given as a % of the total # of rows Type of stats being stored (C or F) # of distinct values for column group "Set" of column no's. Associated with stats. # of columns in "Set"

用于选择存取路径的Catalog表统计信息 (对分 区表)
Same columns in SYSTABLES, SYSCOLUMNS, SYSTABLESPACE, SYSINDEXES and SYSCOLDIST as used for nonpartition SYSTABSTATS CARD NPAGES PCTPAGES NACTIVE PCTROWCOMP SYSCOLDISTSTATS COLVALUE FREQUENCYF TYPE CARDF COLGROUPCOLN O NUMCOLUMNS SYSINDEXPART LIMITKEY # of rows in the partition # of pages on which rows of the partition appear % of the total active pages in partition that contain rows # of active pages in the partition # of active pages in the partition Frequently occurring value # (multiplied by 100) gives the % of rows that contain that COLVALUE Type of statistics gathered (C or F) # of distinct values for the column group (only for C) Set of columns associated with the stats # of columns associated with the stat Limit key of the partition (not set by RUNSTATS)

Following Catalog tables contain statistics on Partition tablespace are used to aid in parallel processing and limited range scan: SYSCOLSTATS SYSINDEXSTATS SYSTABLEPART COLCARD, COLCARDDATA, HIGHKEY, LOWKEY, HIGH2KEY, LOW2KEY FIRSTKEYCARDF, FULLKEYCARDF, NLEAF, NLEVELS, CLUSTERRATIO, KEYCOUNTF CARD, FARINDREF, NEARINDREF

在PLAN_TABLE中不能得到的信息
关于RI 的存取路径 DELETE 和UPDATE要用到的索引 在做LIST PREFETCH 时,RID的sort 信息 存取路径改变所导致的运行时间变化 程序的逻辑 谓词的书写顺序

统计值
在对象生成时,DB2自动将一些缺省的统计值 存到某些catalog表 错误的统计值,会导致错误的存取路径选择 (在合适的时机)用RUNSTATS 功能,来收 集真实的统计值 V8:在CREATE TABLE 时,可用新的参数: VOLATILE,以尽量使用INDEX ACCESS PATH(不必根据缺省或RUNSTATS统计值)

使用EXPLAIN的一些考虑 在需要时,尽可能用 EXPALIN 功能来分析 在PRD上也要用PLAN_TABLE 来做趋势分析 或解决性能问题 在分析完成后,及时清除PLAN_TABLE 中的 记录

DB2 Locks

什么是锁?
锁是一套用来控制对 DB2数据进行存取的技 术 DB2的锁,由IRLM具体 实现

DB2交易上锁的目的

在多交易同时处理时,对并行性进行控制和管 理,以防止交易之间产生数据一致性问题

Lock Processing Flow

锁的粒度(Granularity of Locking) DB2 object candidate for locking Table space Partition Table Page Row

锁的模式(Mode of lock)
Mode of page/row lock
S(share) U(update) can read X(exclusive)

Mode of table or tablespace
IS(intent share) IX(intent exclusive) S(share) U(update) SIX(share with intent exclusive) X(exclusive)

锁的模式(Lock Mode) 由SQL语句决定锁的模式

SELECT …. FROM….

SELECT …. FROM…. FOR UPDATE OF

INSERT/ UPDATE/ DELETE

True lock = S Intent lock = IS

True lock = U Intent lock = IS

True lock = X Intent lock = IX

锁的层级(Hierarchy of Lock)
实际锁(TRUE locks)可能在这些 层级的任何一级 趋向锁(INTENT locks)在实际锁的 上一级

SEGMENTED
TABLESPACE

NON-SEGMENTED
TABLESPACE

TABLE

PAGE PAGE ROW

ROW

不同锁模式的兼容性
Compatibility of page lock and row lock modes

Compatibility of table and table space lock

锁的开销(Lock Cost)
用到IRLM 的资源,以及释放这些资源 每次上锁,需要250 bytes的 storage

D B M 1

I R L M

锁的对象(Lock Object)

SQL语句所涉及到的DB2 TABLESPACE
SELECT …. FROM TABLE1 WHERE ….

锁的大小(Lock Size) 在 CREATE/ALTER TABLESPACE语 句定义:
CREATE TABLESPACE PMTMPTS IN PMDB USING STOGROUP SGDBT101 PRIQTY 10000 SECQTY 1000 LOCKSIZE PAGE LOCKMAX 0 SEGSIZE 64; LOCKSIZE 参数可能的值有:ANY, ROW, PAGE, TABLE ,TABLESPACE

锁的大小
锁大小是对DB2数据上锁的数据数量 最小实际锁大小(minimum TRUE size) 由 CREATE TABLESPACE 时的LOCKSIZE 参数决定

TABLESPACE TABLE PAGE ROW ANY

锁的期限(Duration of lock)
锁的期限: 是指从上锁到释放锁的时间周期 对 tablespace /table锁: Bind option ACQUIRE(ALLOCATE/USE) Bind option RELEASE(COMMIT/DEALLOCATE) 对 page /row 锁 Application is READ ONLY or R/W Normally at commit time Bind option ISOLATION(CS/RR/UR) Bind option ISOLATION(CS) and CURRENTDATA(YES/NO) SQL DECLARE CURSOR WITH HOLD

锁的期限
静态 SQL:由 BIND 参数ACQUIRE 和 RELEASE
LOCK TYPE
Partition, table, tablespace page, row

ACQUIRE
Allocate or Use Use

RELEASE
Deallocate or Commit no later than next commit point

动态 SQL :从对象初次被存取到接下来的 commit point

锁期限长短的影响
要求上一个锁时,需要系统处理时间;持有 (holding)一个锁,要占有系统存储域 如果基于并行性最大化来考虑,锁的数据越少、 时间越短,则越好 如果基于处理效率最大化来考虑,锁的数据越 大、时间越长,则越好

BIND/REBIND的Acquire/Release参数的影响
Acquire Release Advantages Disadvantages

Allocate Deallocate

Avoid deadlock by locking all resource from start to end Best for long batch job Locks objects when it is used only Most efficient for most case Provides greatest concurrency

Reduces concurrency Turns off selective partition locking Increases the chance of deadlock Delays concurrent access Most resource consuming Deadlock frequency increases Concurrent access delay increases

Use

Deallocate

Use

Commit

Allocate Commit

Not allowed, error in BIND

Isolation 参数
隔离级别(Degree of isolation)决定了本处理 与其它并行处理操作的互相影响程度 有4种不同的isolation : Repeatable Read (RR) Read Stability (RS) isolation options Cursor Stability (CS) can be overwritten with “WITH” Uncommitted Read (UR)
clause in SQL

RR--Repeatable Read
本处理对所有被存取(Access)的rows或 pages上 锁,防止其它处理进行 UPDATE, INSERT(满足谓词条 件的ROW), or DELETE 对本处理进行最大程度的保护 因为要上很多的锁,所以容易发生锁扩大(lock escalation), 也很容易发生锁提升(lock promotion),从而阻止 INSERT 新的行

RS--Read Stability
本处理对所有满足检索条件( Qualified )的rows或 pages上锁,防止其它处理进行 UPDATE, or DELETE All 允许插入新行 允许对非Qualified 的行进行修改 比 RR的并行性要强

CS--Cursor Stability
仅holds cursor当前指向的、还没有COMMIT 的被修改记录行(或所在的page) 在防止对未commit的修改数据可以读的前提 下,提供了最大的并行性 declare cursor 时加上 “FOR UPDATE OF” 子句,可以防止同时的其它处理对读出的记录 进行修改

UR--Uncommitted Read (dirty read)
允许读未COMMIT的记录 一般仅用在“只读”(read-only)的处理中 几乎不会上锁,所以处理较快,也减少出现资 源冲突的可能 有可能读出的数据不真实

UR的使用

可以用的情形 1. 当错误不可能发生时 2. 当错误发生也可接受时 3. 当数据已经不一致时

不可以的情形 1. 当数据计算必须平衡 时 2. 当信息结果必须精准 时 3. 当数据不一致会影响 判断或处理时

锁的提升(Lock Promotion)

含义:对同一资 源的锁,由一种 模式升级到另一 种更严格的模式

X

SIX

U

IX IS

S

锁的扩大(Lock Escalation)
含义:将大量的对较小资源的锁释放掉,代之以 一个对大资源的锁 依赖参数: LOCKSIZE和LOCKMAX LOCKMAX 0 表示对TABLESPACE的LOCK数 量无限制,也不会发生锁的扩大 因为太多的锁,可能导致IRLM SOS,从而使对 DB2的存取发生错误

锁的延迟(Lock Suspension) 当应用要求对某资源上锁,却暂时无法得到 时,就会处于等待锁的状态 当所要求的锁资源得到后,应用可以继续处理 也可能导致 timeout 或deadlock 对应用性能或可用性产生严重的冲击

超时(Timeout) 应用在下列情况下,发生 Timeout : 延迟时间超过的系统预先设置的时间 此时间,在DB2 的安装参数DSNZPARM 设 置(缺省:60秒) 导致的结果: DB2 终止应用处理,同时,在 DB2 console 上会出现2条信息:SQLCODE 为 –911 或 –913、 reason code 为 00C9008E commit 和rollback 操作不会timeout.

死锁(Deadlock)

1. EMPLJCHG accesses table M, with X-lock for page B 2. PROJNCHG accesses table N, with X-lock for page A 3. EMPLJCHG requests a lock for page A of table N while still holding the lock on page B of table M. The job is suspended, because job PROJNCHG is holding an exclusive lock on page A. 4. PROJNCHG requests a lock for page B of table M while still holding the lock on page A of table N. The job is suspended, because job EMPLJCHG is holding an exclusive lock on page B. The situation is a deadlock.

Deadlock
Deadlock发生于: 两个应用同时处理时,分别 hold 了锁资源 各个应用需要对方held 的资源 各个应用都在等待资源 这些被等待的资源是不可共享的 导致的结果: 在达到系统预设的时间后 (DEADLOCK TIME,缺 省: 5 秒), DB2 将其中一个处理的UOW rollback , 或要求终止其中一个处理 在rollback 或终止后,这个处理同时释放其锁资源 另一个处理继续

Deadlock

避免Timeout或Deadlock的基本方法
避免Timeouts: 增大所有应用COMMIT的频率,以减少hold锁的时间 对”热点数据”尽快地 COMMIT 使用 LOCKSIZE ROW 避免Deadlocks 所有的应用以同样顺序对相同资源进行存取 定义CURSOR时,加 FOR UPDATE OF 参数,并在 UPDATE 时,用WHERE CURRENT OF (限制:FOR UPDATE OF 与ORDER BY参数不能同时使用) 使用 LOCKSIZE ROW V8 使用 SELECT ... ORDER BY … WITH RS USE AND KEEP UPDATE LOCKS;

Commit
The application must wait for the write operation to complete before ending the connection User1
UPDATE/ DELETE/ INSERT

User2
UPDATE/ DELETE/ INSERT

User3
UPDATE/ DELETE/ INSERT

LOG BUFFER

ACTIVE LOGS
COMMIT

Rollback
The application has to continue reading the logs, backwards, until 1st record of UOW is found

User
ROLLBACK

ACTIVE LOGS

LOG BUFFER

1st Record? NO

ARCHIVE LOGS

Commit/Rollback 考虑
COMMIT 至少在4* NUMLKUS (max. no. of locks per user) 的值之内,进行一次COMMIT,以便其它用 户能进行存取 频率适当,尽量保证ROLLBACK操作仅用log buffers 在一个UOW之内,尽量早地进行ROLLBACK

Claim 和 Drain
是除锁之外的另一种控制资源同时性处理的技术机制 可用于: simple tablespace segmented tablespace single data partition of partitioned tablespace non-partitioned index space single index partition of partitioned index

Claims
对正在使用的资源进行登记(register) 在资源首次被存取时,要求Claim 一般在commit 时释放(例外: WITH HOLD cursor 和utility) 对同一资源,允许要求多次 Claims 防止对已Claims 的资源进行Drains

Claim Classes CS
when data is read from Plan or Package bound with ISOLATIONS(CS)

RR
when data is read from Plan or Package bound with ISOLATIONS(RR)

Write
when data is deleted, inserted or updated

Drains
要求相应的资源停止(Quiescing)被Claims 由DB2 commands 和utilities提出Drain要求 在资源首次用到时,才能Drain Command或utility 完成后释放 在所有Claim Class都释放后,才能获得Drain 锁 对已Drains的资源,禁止新的Claims

Drain 类型 CS – cursor stability RR – repeatable read W - write
to acquire complete control of a resource requires CS, RR and W drain locks

Claim 与 Drain 的兼容性

Existing Claim

Existing Drain

Drain Requested W RR CS

W

RR

CS

W

RR

CS

N N N

Y N N

Y N N

Y N N

N N N

N N N

Transaction Locks 与 Claims and Drains的 对比
Transaction Locks between multiple claimers e.g. two SQL statement or SQL statement and utility Claims and Drains between claimer and drainer e.g. one command and one utility

Data Sharing

MVS COUPLING FACILITIES
COMMUNICATIONS LOCKING GLOBAL BUFFER POOL

DB2G
DB2A D B M 1 I R L M D B M 1 DB2B I R L M D B M 1 DB2C I R L M

Global Locking
用来保证在一个data sharing group 中的每个 member的数据完整性 由定义在data sharing group 的CF(coupling facility ) 上的锁结构来实现 IRLM 控制每个member之内的锁, 并同时也与CF相通 CF有两个锁结构(lock structures): 锁清单(lock list):包含所有被修改的资源名 hash table:登记着相容和不相容的lock modes

锁的避免(Lock Avoidance)

SELECT …. FROM …. FOR FETCH/READ ONLY

PACKAGE A BIND … ISOLATION (CS) CURRENTDATA (NO) lock avoidance is attempted for all rows (qualifying and nonqualifying)

PACKAGE B BIND … ISOLATION (CS) CURRENTDATA (YES) lock avoidance can occur for nonqualifying rows only.

Lock avoidance should be encouraged

热点(Hot Spots)数据

SELECT DATA PAGES UPDATE UPDATE UPDATE UPDATE SELECT

SELECT

Try to design database/program to minimize contention

推迟对数据修改(Deferred Updates)

UPDATE

UPDATE

UPDATE UPDATES UPDATE UPDATES

TABLE
SELECT SELECT SELECT SELECT

TABLE

锁的一些考虑点(1)
一般使用 LOCKSIZE ANY 选择合适的NUMLKTS 和 NUMLKUS参数 (DSNZPARM),以对锁扩大进行有效控制 选择适当的LOCKMAX值(TABLESPACE) 用LOCKPART ,使分区表的独立性最好 设置IRLM 参数要适当,既要使memory 更有效 使用,同时要避免锁资源不足所导致的性能问 题 谨慎使用 WITH HOLD 考虑减少 MAXROWS值或扩大 free space 的 值

锁的一些考虑点(2)
尽量用lock avoidance技术 上锁要尽可能的迟,释放锁要尽可能的早 对独立执行的batch job, 对整个表上 share或 exclusive锁 在数据准确性要求不太高时, 使用 ISOLATION(UR) 有时要使能锁扩大,防止处理 timeout或 deadlock

DB2 utility

DB2 utilities的类型 DB2 online utility
Run as standard MVS batch jobs or stored procedures, and they require DB2 to be running.

DB2 stand alone utility
Execute as batch jobs independent of DB2. They can be executed only by means of MVS JCL.

online utilitie的启动
Use the DB2 utilities panel in DB2I. Use the DSNU CLIST command in TSO. Use the supplied JCL procedure (DSNUPROC). Create the JCL data set yourself (DSNUTILB).

Sample DSNUPROC listing
//DSNUPROC PROC LIB='prefix.SSPGM', // SYSTEM=DSN, // SIZE=0K,UID=',UTPROC=' //* PROCEDURE-NAME: DSNUPROC //* DESCRIPTIVE-NAME: UTILITY PROCEDURE //* INPUT: //* PARAMETERS: //* LIB = THE DATA SET NAME OF THE DB2 PROGRAM LIBRARTHE

Edit the job

//stepname EXEC PGM=DSNUTILB,
Specifies the DB2 Specifies the DB2 subsystem subsystem Utility control program Utility control program

PARM='system,[uid]’
The unique identifier The unique identifier for the utility job for the utility job

COPY

Used for DB2 Table Space backup FULL COPY Copy all pages of TS, partition, Index space INCREMENTAL COPY Copy only changed pages since last FULL COPY

COPY Example
FULL
//STEP1 EXEC DSNUPROC,PARM='DB1E,CPY1',COND=(4,LT),REGION=0M

//SYSCOPY DD DSN=COPY.IMAGE.TEMPTS, // DISP=(NEW,PASS,DELETE),UNIT=3390,VOL=SER=GP1SY1, // SPACE=(TRK,(10,10)) //SYSIN DD * COPY TABLESPACE TESTDB.TESTTS

Incremental
//STEP1 EXEC DSNUPROC,PARM='DB1E,CPY1',COND=(4,LT),REGION=0M //SYSCOPY DD DSN=COPY.IMAGE.TEMPTS, // DISP=(NEW,PASS,DELETE),UNIT=3390,VOL=SER=GP1SY1, // SPACE=(TRK,(10,10)) //SYSIN DD * COPY TABLESPACE TESTDB.TESTTS FULL NO SHRLEVEL CHANGE

UNLOAD
//UNLOAD1 JOB (ACCTNUM,EXP),'PGMRNAME', // TIME=1440, // NOTIFY=&SYSUID, // REGION=0M, // CLASS=B, // MSGCLASS=X, // MSGLEVEL=(1,1) //STEP1 EXEC DSNUPROC, // SYSTEM=DB1E,UID='UNLOAD1',UTPROC='' //SYSREC DD DSN=ZHS0013.UNLOAD.TEST1, // DISP=SHR //SYSPUNCH DD DSN=ZHS0013.DB2.JCL(UNLOAD1),DISP=SHR //SYSPRINT DD SYSOUT=* //SYSIN DD * UNLOAD TABLESPACE TESTDB.TESTTS4

LOAD

load 数据到TS,并同时 rebuild index 如果TS 中已有数据,则必须选择是否将 数据LOAD到已有的数据之后或清除原 来的数据 – 选择RESUME YES 或 REPLACE 参数

LOAD Example
//STEP1 EXEC DSNUPROC,UID=‘IBMUSER.LOAD', // UTPROC='', // SYSTEM='V71A' //SYSREC DD DSN=IBMUSER.LOAD.DATA,DISP=SHR,VOL=SER=SCR031, // UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) //SYSUT1 DD DSN=IBMUSER.LOAD.STEP1.SYSUT1, // DISP=(MOD,DELETE,CATLG), // UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) //SORTOUT DD DSN=IBMUSER.LOAD.STEP1.SORTOUT, // DISP=(MOD,DELETE,CATLG), // UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) //SYSIN DD * LOAD DATA INDDN(SYSREC) RESUME YES INTO TABLE DSN8710.ACT (ACTNO POSITION( 1) INTEGER EXTERNAL(3), ACTKWD POSITION( 5) CHAR(6), ACTDESC POSITION(13) VARCHAR) ENFORCE NO

Rebuild index

用于重新建立 index的数据 Example
//STEP1 EXEC PGM=DSNUTILB,PARM='BB1A,REBUILD' //STEPLIB DD DSN=DSN710.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //UTPRINT DD SYSOUT=* //SYSIN DD * REBUILD INDEX(NGDBA2.NIHINDTL1) SORTKEYS

RECOVER Recover DB2 data to a image copy
//STEP1 EXEC PGM=DSNUTILB,PARM='DB1E,RECOVER' //STEPLIB DD DSN=DSN710.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //UTPRINT DD SYSOUT=* //SYSIN DD * RECOVER TABLESPACE NASEDB.NSCHDTL

Recover DB2 data to a point of time
//STEP1 EXEC PGM=DSNUTILB,PARM='DB1E,RECOVER' // REGION=0M //STEPLIB DD DSN=DSN710.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //UTPRINT DD SYSOUT=* //SYSIN DD * RECOVER TABLESPACE TESTDB.TESTTS6 TO RBA X’BA73F65B2911’

Reorg Tablespace
用于对TS数据进行重组(re-organization), 释 放空间中的碎片,以提高对TS存取的性能 Example
//JOBLIB DD DISP=SHR,DSN=DSN710.SDSNLOAD //PH01S18 EXEC DSNUPROC,PARM='ZB0B,REORG' //SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR //SORTOUT DD UNIT=3390,SPACE=(CYL,(10,20),,,ROUND) //SORTWK01 DD UNIT=3390,SPACE=(CYL,(10,20),,,ROUND) //SORTWK02 DD UNIT=3390,SPACE=(CYL,(10,20),,,ROUND) //SYSPRINT DD SYSOUT=* //UTPRINT DD SYSOUT=* //SYSUT1 DD UNIT=3390,SPACE=(CYL,(10,50),,,ROUND) //SYSREC DD UNIT=3390,SPACE=(CYL,(10,50),,,ROUND) //SYSIN DD * REORG TABLESPACE TEMPDB.TEMPTS SHRLEVEL NONE

Online Reorg Tablespace
REORG时加SHRLEVEL CHANGE 参数,可以在不终止应用的情况下 对TS进行在线 reorg 。但此时需要定义 MAPTABLE 。 Example:
//STEP01 EXEC DSNUPROC,PARM='DU10,REORGONL' //STEPLIB DD DISP=SHR,DSN=DB2.SDSNLOAD //SYSPRINT DD SYSOUT=* //UTPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //DATAWK01 DD UNIT=SYSDA,SPACE=(CYL,(500,500),,,ROUND) //DATAWK02 DD UNIT=SYSDA,SPACE=(CYL,(500,500),,,ROUND) //SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(500,500),,,ROUND) //SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(500,500),,,ROUND) //SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(500,500),,,ROUND) //SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(500,500),,,ROUND) //SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(500,500),,,ROUND) //SYSREC DD UNIT=SYSDA,SPACE=(CYL,(500,500),,,ROUND) //SYSIN DD * TEMPLATE MYCOPY DSN(IBSSITBK.&ICTYPE..A&JDATE..T&TIME..&DB..&TS.) MAXPRIME 500 VOLCNT(5) LISTDEF DBLIST1 INCLUDE TABLESPACE DIBSSIT.SCTTLT REORG TABLESPACE LIST DBLIST1 COPYDDN(MYCOPY) SHRLEVEL CHANGE MAPPINGTABLE IBSSIT.IBSMAPTB SORTDATA LOG NO SORTKEYS STATISTICS

RUNSTATS
收集TS、索引及分区中的数据统计信息。 DB2将这些统计信息登 记在 相应的catalog表中,并在 BIND/REBIND时利用这些信息选 择存取路径。
Example:
//RUNSTAT EXEC DSNUPROC,PARM='DU12,RUNSTATS' //STEPLIB DD DISP=SHR,DSN=DB2.SDSNLOAD //SYSIN DD * RUNSTATS TABLESPACE DIBSSIT.SCTTLT TABLE(ALL) INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1 COUNT 10 FREQVAL NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10 FREQVAL NUMCOLS 4 COUNT 10 UPDATE(ALL) REPORT NO SHRLEVEL CHANGE //

KEYCARD – gather key column combinations stats for better correlated query optimization SHRLEVEL CHANGE – allow concurrent online application to run

DSNJLOGF

初始化 ACTIVE LOG Example
//DSNTLG PROC //DSNTLG EXEC //SYSPRINT DD //SYSUDUMP DD //SYSUT1 DD //DSNTLG PEND //DSNLG1 EXEC //DSNLG2 EXEC //DSNLG3 EXEC //DSNLG4 EXEC //DSNLG5 EXEC //DSNLG6 EXEC LOG=XXXXXXXX PGM=DSNJLOGF SYSOUT=* SYSOUT=* DSN=&LOG,DISP=SHR DSNTLG,LOG=XXXX.LOGCOPY1.DS01 DSNTLG,LOG=XXXX.LOGCOPY1.DS02 DSNTLG,LOG=XXXX.LOGCOPY1.DS03 DSNTLG,LOG=XXXX.LOGCOPY2.DS01 DSNTLG,LOG=XXXX.LOGCOPY2.DS02 DSNTLG,LOG=XXXX.LOGCOPY2.DS03

DSNJU004(print log map) Print BSDS的内容, 诸如:active log状态, checkpoint记录,系统和utility 的时间戳 Example
//DSNTLOG EXEC //SYSUT1 DD //GROUP DD //SYSPRINT DD //SYSIN DD MEMBER * PGM=DSNJU004 DISP=SHR,DSN=DSNDB0E.DB1E.BSDS01 DISP=SHR,DSN=DSNDB0E.DB1E.BSDS01 SYSOUT=* *

/*

DSN1COPY

Copy DB2 VSAM data set to PS data set Copy PS data set to DB2 VSAM data set Copy DB2 IMAGE COPY data set to DB2 VSAM data set Copy between 2 DB2 VSAM data sets Copy between 2 PS data sets

DSN1COPY example
Copy from DB2 IMAGE COPY data set to DB2 VSAM data set
//STEP EXEC PGM=DSN1COPY,PARM='OBIDXLAT,FULLCOPY,RESET' //STEPLIB DD DSN=DSN710.SDSNLOAD,DISP=SHR //SYSUDUMP DD SYSOUT=A //SYSPRINT DD SYSOUT=A //SYSUT1 DD DISP=SHR, // DSN=TEMP.TEMPTS.COPY1,UNIT=3390,VOL=SER=GP1TSB //SYSUT2 DD DISP=OLD, // DSN=DSNDB0E.DSNDBC.TEMPDB.TEMPTS.I0001.A001 //SYSXLAT DD * DBID 259 259 PSID 2 2 OBID 6 13

DSN1LOGP
打印可恢复log(active和achieve)的信息, 可以为SUMMARY或 DETAIL信息 Example
//PRINT2 EXEC PGM=DSN1LOGP //STEPLIB DD DSN=DSN710.SDSNLOAD,DISP=SHR //SYSABEND DD SYSOUT=A //SYSSUMRY DD SYSOUT=A //SYSPRINT DD SYSOUT=A //BSDS DD DSN=DSNDB0E.DB1E.BSDS01,DISP=SHR //*ACTIVE1 DD DISP=SHR,DSN=DSNDB0E.DB1E.LOGCOPY1.DS01 //SYSIN DD * STARTRBA(003681820091) ENDRBA(0036818215D6) SUMMARY(ONLY) DBID(103) OBID(4)

对应用开发的一些建议

一般性建议
减少SQL 语句对DB2对象的访问数 减少检索的行数 减少检索的列数 需要时,显式使用ORDER BY 尽量编写Indexable, Stage 1 Predicates 对用AND连接的谓词,将排除记录最多的谓词放在前面;对 用OR连接或IN-list的谓词,将满足条件排除记录最少的谓词 放在前面 尽可能使用OPTIMIZE FOR n ROWS,FETCH FIRST n ROWS ONLY 尽量用Cursor方式,而非直接SELECT +UPDATE/DELETE 适当的COMMIT频率 使统计信息尽量是当前的或适用的

减少SQL对DB2的访问次数 将已读出的值放在内存,而不是每次用SQL读 自己在COBOL中编写substr、 date运算,而 不是用SQL函数做 但是,诸如列相乘、数据的join等工作,可以 用SQL语句来实现 在一个交易中,将对一个表的多个列的修改合 并在一个SQL UPDATE 语句中做

减少检索的行数
直接在WHERE中增加谓词,以减少用SQL返回的记录 数,而不是在程序中过滤数据 对分区表,尽可能增加谓词,以使DB2可能用上分区 scan或并行处理 如果谓词条件是常数,应直接写常量,而不要用主变 量

减少检索的记录列数 一般不要用 SELECT * 如果只SELECT索引的列,DB2 可以进行 IndexOnly 存取 WHERE中的列(“=”谓词的)可不出现在 SELECT 列中

显式使用 ORDER BY DB2 不会保证检索出的数据的物理顺序 如果按INDEX KEY 排序并用到了此索引, DB2 可以不用做SORT

使用Indexable, Stage 1 谓词(1)
如果谓词可以匹 配索引, DB2 就 在读data page 之前就可以直接 用index page的 数据. 但是,如 果谓词是stage 2的, DB2 则必 须读出数据行再 做判断分析. 参看:《Admin Guide 5.8.3.2 》 - Summary of predicate processing

使用Indexable, Stage 1 谓词(2)
将谓词改写成性能更好的方式

谓词的过滤性
对用AND连接的谓词,将排除记录最多的谓词放在前面; 对用OR连接或IN-list的谓词,将排除记录最少的放在前面
WHERE STATE = 'KANSAS‘ AND HAIR = 'BROWN‘ AND SEX = 'MALE'; (1%) WHERE SEX = 'MALE‘ (10%) OR HAIR = 'BROWN‘ (50%) (10%)

(50%) OR STATE = 'KANSAS'; (1%)

使用OPTIMIZE FOR n ROWS, FETCH FIRST n ROWS ONLY
DECLARE CURSOR C1 FOR SELECT COLa FROM T1 WHERE COLb = 1 OPTIMIZE FOR 1 ROWS; For DRDA, to avoid unnecessary prefetches, add the FETCH FIRST 1 ROW ONLY clause: SELECT * FROM EMP OPTIMIZE FOR 1 ROW ONLY FETCH FIRST 1 ROW ONLY;

Cursor 对比 searched

+ update/delete

Use a cursor instead of the searched update or delete DECLARE C1 CURSOR WITH HOLD FOR SELECT COLb, COLc FROM T1 WHERE COLa = :HV-COLa FOR UPDATE OF COLb, COLc; UPDATE T1 SET COLb = :HV-COLb ,COLc = :HV-COLc WHERE CURRENT OF C1 DELETE FROM T1 WHERE CURRENT OF C1

适当选择COMMIT的频率
如果不做commit,就会:
不释放锁住的资源 阻止其它交易存取锁住的资源 可能导致同时处理交易超时 阻止utilitie进行处理 在出现故障后, rollback过程涉及的数据量大、时间长

建议
不要: 锁记录时间 > 5 秒. 不要: U-X锁时间 > 总处理时间的10% 对batch ,一般处理500-1000条记录commit一次 也不要过度地commit, 如:不要每处理一条记录就 commit一次

统计信息尽量反映当前状况或合乎要求
如果某SQL语句执行的状况不太好,就做 RUNSTATS 并 REBIND 在REORG或table/index修改之后,做 RUNSTATS 不要在表为空或记录很少时做 RUNSTATS 和 REBIND 每次Bind/REBIND后,用EXPLAIN(YES) 参数来确认存 取路径是否合适

新版本的新功能
DB2的每个新版本,都有新功能,可以提高性能 V7: SQL: SAVEPOINT; ROLLBACK TO SAVEPOINT SQL: SELECT … FETCH FIRST n ROWS ONLY Utility: UNLOAD (faster than DSNTIAUL sample program) V8: Table-Control Partition ; DPSI( Data Partitioned Secondary Index) SQL: Mutli-Row FETCH、UPDATE、INSERT、DELETE; SQL: CREATE TABLE … VOLATILE Utility:BACKUP SYSTEM; RESTORE SYSTEM(仅支持整个系统恢 复) V9:
Universal table space--SEGSIZE and NUMPARTS

Clone table Utility: RECOVER支持用BACKUP SYSTEM备份数据进行TS/IS级 恢复; 在线 REBUILD INDEX (SHRLEVEL CHANGE)

问题?

Thank You!


相关文章:
DB2性能优化_图文.ppt
DB2性能优化_计算机软件及应用_IT/计算机_专业资料。经验总结 DB2 性能优化王飞鹏...工具,与ETL工具相比, CDI运行时,目标表是可用的,加载数据不需要维护时间窗口。...
db2 reorg优化及原因.doc
db2 reorg优化及原因_计算机软件及应用_IT/计算机_专业资料。db2 reorg优化及...runstats 统计信息,可以优化查询器 一个完整的日常维护规范可以帮助 DBA 理顺...
DB2基本维护.doc
DB2 数据库维护 1.数据库的启动、停止 db2start--启动 db2stop [force]--...'DB2ADMIN' and Type = 'T';--获取优化语句 db2 runstats on table DB2...
DB2性能优化_图文.pdf
维护时间需要12小时左右,影响系统正常正业; 经优化脚本生成详细维护日志,检查维护...Done 在6个LPAR上同时运行脚本后,吞吐量与DB2运行时无异,说明存储存在性能问题...
优化DB2数据库的十个最佳实践(上).doc
优化DB2 数据库的十个最佳实践(上) 结构化查询语言(SQL)对于关系型 DBMS 是...DB2 的客户不再被要求用独立的工具维护和使用他们的主机和分布式的 DB2 系统。...
优化DB2数据库的十个最佳实(下).doc
优化DB2数据库的十个最佳实(下) - ---优化 DB2 数据库的十个最佳实践(下) DB2 优化技巧 6: 使数字和数据日期类型相匹配 在先前的版本中,对于处理谓词和...
关于DB2常见性能问题的解决参考.doc
时,在并发达到一定数后,DB2 数据库资源占用很大,必须 对数据库和应用进行优化...配置参数 util_impact_limit 对实用程序进 行节流;或者关掉数据库的自动维护...
DB2数据库新形势下的维护_论文.pdf
◇科 技论坛◇ 科技 目向导 2014年1 8期 DB2 数据库新形势下的维护 王威 ...【 关键词】 DB2 数据库 ; 日常维 护; 数据安全 (1)SQ L的优化 。 在...
db2日常维护.txt
一、 DB2日常维护日操作 1、 检查管理服务器是否启动用ps命令查看是否有dasusr...命令收集表、索引和统计信息视图的统计信息,以为优化器提供准确信息进行访问方案...
DB2+性能优化快速入门_图文.doc
DB2+性能优化快速入门_计算机软件及应用_IT/计算机_专业资料。本文是针对 DB2? ...而且有可能索引也要同步维护,所以将它们放在不同的硬盘上可以使它们的读 写并行...
DB2 性能优化快速入门_图文.doc
而且有可能索引也要同步维护,所以将它们放在不同的硬盘上可以使它们的读 写并行...性能优化相关工具 在 DB2 中有很多和性能优化相关的工具和命令,下面简单地介绍...
10大DB2数据库优化技巧.doc
10大DB2数据库优化技巧 - 10 大 DB2 数据库优化技巧 为了帮助 DB2 DBA 避免性能灾难并获得高性能,我为我们的客户、用户和 DB2 专家同行总 结了一套故障诊断...
DB2 Sql优化与锁.doc
DB2 Sql优化与锁 - DB2 Sql 优化与锁 本次差旅性能测试,80%性
数据库_DB2数据库优化_图文.ppt
数据库_DB2数据库优化 - DB2数据库优化 宋智强 2011-5-9 2 标题 体系结构和进程 锁和隔离级别 索引和数据存取 SQL优化 数据库参数调优 Inspur group ...
基于DB2的数据库应用系统的性能优化.txt
基于DB2的数据库应用系统的性能优化 - 毕业论文心得日记思想的火花... 基于DB2的数据库应用系统的性能优化_互联网_...另外,索引还需要额外的磁盘空间和维护开销。...
DB2数据库日常维护-REORG_TABLE.doc
DB2数据库日常维护-REORG_TABLE - 转)DB2 日常维护REORG TABLE 命令优化数据库性能 2009-04-24 16:18 一个完整的日常维护规范可以帮助 DBA 理...
解决软件性能问题的DB2数据库优化方案.doc
解决软件性能问题的DB2数据库优化方案_计算机软件及应用_IT/计算机_专业资料。...系统维护等 13 个功能模块,覆盖 20 多个部门日常业务,是 一套符合客户其业务...
10大DB2优化技巧.txt
10大DB2优化技巧 - 让数据库飞起来 10大DB2优化技巧 2011-07-20 15:52 | 115次阅读 | 来源:中国IT实验室 【已有0条评论】发表评论 关键词:DB2,数据库...
db2数据库性能参数优化笔记整理.doc
db2数据库性能参数优化笔记整理 - [经验分享] db2 数据库性能参数优化笔记整理 数据库, 笔记, 性能, 参数, 调优 1、Application Support Layer Heap...
DB2之SQL优化浅析_图文.pdf
DB2之SQL优化浅析 - DB2之SQL优化浅析 白德全 西南区中心团队&青海PSO 目录 为什么要做SQL优化 SQL优化建议 典型SQL案例 SQL语句对数据库的影响 1、...