DB2 Monitor Utilities

refer: http://www.ebenner.com/db2dba_blog/?p=168

The DB2 System Monitor is the database administrator’s ally in evaluating database performance. The benefit, however, from having detailed metrics about your database’s behavior has to be weighed against potential database performance degradation when either executing GET SNAPSHOT or activating an event monitor. In this post I compare the relative cost of using snapshot, event monitor, and other performance utilities, and demonstrate how to manage their potential risks.

First, a word about LATCHES. The System Monitor (via GET SNAPSHOT or through activation of an event monitor) acquires LATCHES ( “. . .  low level serialization mechanisms used to protect shared data structures . . . ”¹ ) to ensure accurate reporting. System Monitor latches can enqueue (delay) any database process or operation. Both user applications and database utilities may be affected. Significant global delays due to latches can cause a database hang (hang-wait) condition.² Hang-waits can have many secondary symptoms: statements appear to be in lock contention but there is no obvious resource in contention; connection attempts hanging; utilities refusing to terminate.
DB2 monitoring and problem determination utilities vary widely in their database performance impact.

• db2pd (first made available in DB2 LUW version 8.2) causes the least impact, because it does not acquire latches. db2pd as a relatively new utility is not as easily used as snapshots or event monitors, but it appears to be winning mindshare among database administrators. It’s text output is even more difficult to summarize and parse than either snapshot or event monitor output (db2pd will be the subject of a future post in this blog).
• GET SNAPSHOT has a somewhat heavier potential performance footprint (which varies by number of monitor switches active, type of workload, and mix of static vs. dynamic SQL); GET SNAPSHOT has, according to IBM Toronto Laboratory’s Steve Rees³ , anywhere from a 1-10% effect.⁴
• Event monitors have potentially the heaviest impact on a database and pose the greatest (potential) risk (depending upon the kind of monitor, transactions per minute, and event monitor parameters). Because the Event Monitor is a trace, the performance impact is directly proportionate to the number of events being captured. Carelessly configured STATEMENTS and TRANSACTIONS event monitors can do harm in a high Transactions Per Minute (TPM) environment ( TPM > 3000 ).

表开启压缩后创建字典SQL2220W错误分析

情况描述：

为表设置compress yes后，对其执行reorg ... resetdictionary创建字典并进行压缩时出现SQL2220W错误。

[root:/home/db2inst1]$ db2 alter table stmap.cust_info compress yes DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 reorg table stmap.cust_info resetdictionary  SQL2220W The compression dictionary was not built for one or more data objects.

错误分析：

SQL2220W错误的解释：

[root:/home/db2inst1]$ db2 ? SQL2220W SQL2220W The compression dictionary was not built for one or more data objects. Explanation:  A compression dictionary could not be built for one or more data object. The object(s) either contained no records, or contained no records larger than the minimum record length to be eligible for this page size. A new dictionary was not built. The operation continued to completion. If there was a dictionary prior to the operation, the dictionary is retained and the rows were subject to compression. User Response:  Refer to the Administration Log to determine which data object(s) caused the warning.

上述提到两种可能，其一，表中没有记录，其二，没有大于最小记录长度的记录

第一个原因很好理解。那第二个原因呢？

经过google发现了一篇文章：

http://www.ibm.com/developerworks/data/library/techarticle/dm-0707schurr/

该文章中提到在DB2 v9以前的版本是不支持Large表空间的，而Regular的表空间不同Pagesize对记录的长度有比较大的限制，见下表：

（表1）

Pagesize	Regular tablespace min record length	Regular tablespace max record length	Large tablespace min record length	Large tablespace max record length
4K	14	251	12	187
8K	30	253	12	580
16K	62	254	12	1165
32K	127	253	12	2335

从上表可以看出在Regular的表空间最小记录长度（min record length）随着页大小的增大有比较大的变化，而SQL2220W错误指的记录最小长度说的就是这里，如果记录最小长度不能小于规定的大小，所以如果记录最小长度接近于规定的大小表就无法进行压缩。

查询压缩表和记录长度和表空间的Pagesize：

[root:/home/db2inst1]$ db2 "select substr(a.tabname, 1, 10) as table, substr(c.tbspace, 1, 10) as tbspace, c.pagesize, b.card, b.npages, > case when (b.npages > 0) then (b.card / b.npages) else -1 end as rows_per_page, > sum(avgcollen) avg_row_size sum(avgcollen) avg_row_size > from syscat.columns a, syscat.tables b, syscat.tablespaces c > where a.tabschema = b.tabschema and a.tabname = b.tabname and b.tbspaceid = c.tbspaceid > and a.tabname = 'CUST_INFO' > group by a.tabschema, a.tabname, c.tbspace, c.pagesize, b.card, b.npages" TABLE      TBSPACE    PAGESIZE    CARD                 NPAGES               ROWS_PER_PAGE        AVG_ROW_SIZE ---------- ---------- ----------- -------------------- -------------------- -------------------- ------------ CUST_INFO  CUST_DAT         32768                54832                  217                  252           40   1 record(s) selected.

从上述的结果可以看出表空间CUST_DAT的Pagesize大小32k而表CUST_INFO的平均记录大小40，对照（表1）记录大小原小于32K的表空间最小记录的长度，所以该表中的记录无法进行压缩。接下来我们对分析的结果进行下验证。

测试环境：

表空间情况：

Tablespace ID                        = 2  Name                                 = USERSPACE1  Type                                 = Database managed space  Contents                             = All permanent data. Large table space.  State                                = 0x0000    Detailed explanation:      Normal  Total pages                          = 24576  Useable pages                        = 24544  Used pages                           = 1920  Free pages                           = 22624  High water mark (pages)              = 2112  Page size (bytes)                    = 4096  Extent size (pages)                  = 32  Prefetch size (pages)                = 192  Number of containers                 = 1  Minimum recovery time                = 2012-09-17-02.51.19.000000  Tablespace ID                        = 3  Name                                 = CUST_DAT  Type                                 = System managed space  Contents                             = All permanent data. Regular table space.  State                                = 0x0000    Detailed explanation:      Normal  Total pages                          = 961  Useable pages                        = 961  Used pages                           = 961  Free pages                           = Not applicable  High water mark (pages)              = Not applicable  Page size (bytes)                    = 32768  Extent size (pages)                  = 64  Prefetch size (pages)                = 384  Number of containers                 = 1  Minimum recovery time                = 2012-09-17-02.16.26.000000 Tablespace ID                        = 13  Name                                 = LARGE_32K_TEST  Type                                 = Database managed space  Contents                             = All permanent data. Large table space.  State                                = 0x0000    Detailed explanation:      Normal  Total pages                          = 1024  Useable pages                        = 992  Used pages                           = 96  Free pages                           = 896  High water mark (pages)              = 96  Page size (bytes)                    = 32768  Extent size (pages)                  = 32  Prefetch size (pages)                = 192  Number of containers                 = 1  Minimum recovery time                = 2012-09-17-03.02.21.000000

其中CUST_DAT为原表空间，USERSPACE1为Pagesize 4k的表空间，LARGE_32K_TEST为Pagesize 32k的Large表空间

测试方式：

测试1：在USERSPACE1上创建同样的表CUST_INFO进行压缩，并查看压缩前后情况

测试2：创建Large表空间，并创建同样的表CUST_INFO进行压缩，并查看压缩前后情况

测试脚本：

--测试1 db2 create large tablespace large_32k_test pagesize 32k managed by automatic storage bufferpool BILL_BP_32K db2 create table STMAP.CUST_INFO_TEMP_20120917 like STMAP.CUST_INFO in large_32k_test --测试2 db2 create table STMAP.CUST_INFO_TEMP_20120917 like STMAP.CUST_INFO in USERSPACE1 --测试 db2 "ALTER TABLE STMAP.CUST_INFO_TEMP_20120917 ADD PRIMARY KEY (IDTYPE, IDNO)" db2 "DECLARE C1 CURSOR FOR SELECT * from STMAP.CUST_INFO" db2 load from c1 of cursor messages msg.out insert into STMAP.CUST_INFO_TEMP_20120917 NONRECOVERABLE db2 runstats on table STMAP.CUST_INFO_TEMP_20120917 db2 ALTER TABLE STMAP.CUST_INFO_TEMP_20120917 COMPRESS YES db2 INSPECT ROWCOMPESTIMATE TABLE NAME CUST_INFO_TEMP_20120917 SCHEMA STMAP RESULTS KEEP rowcompression_output db2inspf /home/db2inst1/sqllib/db2dump/rowcompression_output /home/db2inst1/sqllib/db2dump/rowcomp1.txt more /home/db2inst1/sqllib/db2dump/rowcomp1.txt rm /home/db2inst1/sqllib/db2dump/rowcompression_output /home/db2inst1/sqllib/db2dump/rowcomp1.txt db2 reorg table STMAP.CUST_INFO_TEMP_20120917 resetdictionary db2 runstats on table STMAP.CUST_INFO_TEMP_20120917 db2 drop table STMAP.CUST_INFO_TEMP_20120917 --测试1 db2 drop tablespace large_32k_test

测试1：

[root:/home/db2inst1]$ db2 create table STMAP.CUST_INFO_temp_20120917 like STMAP.CUST_INFO in USERSPACE1 DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 "ALTER TABLE STMAP.CUST_INFO_TEMP_20120917 ADD PRIMARY KEY (IDTYPE, IDNO)" DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 "DECLARE C1 CURSOR FOR SELECT * from STMAP.CUST_INFO" DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 "load from c1 of cursor messages msg.out insert into STMAP.CUST_INFO_TEMP_20120917 NONRECOVERABLE" Number of rows read         = 54915 Number of rows skipped      = 0 Number of rows loaded       = 54915 Number of rows rejected     = 0 Number of rows deleted      = 0 Number of rows committed    = 54915 [root:/home/db2inst1]$ db2 "runstats on table STMAP.CUST_INFO_TEMP_20120917" DB20000I  The RUNSTATS command completed successfully. [root:/home/db2inst1]$ db2 "select substr(a.tabname, 1, 10) as table, c.pagesize, b.card, b.npages, > case when (b.npages > 0) then (b.card / b.npages) else -1 end as rows_per_page, > sum(avgcollen) avg_row_size sum(avgcollen) avg_row_size > from syscat.columns a, syscat.tables b, syscat.tablespaces c > where a.tabschema = b.tabschema and a.tabname = b.tabname and b.tbspaceid = c.tbspaceid > and a.tabname = 'CUST_INFO_TEMP_20120917' > group by a.tabschema, a.tabname, c.pagesize, b.card, b.npages" TABLE      PAGESIZE    CARD                 NPAGES               ROWS_PER_PAGE        AVG_ROW_SIZE ---------- ----------- -------------------- -------------------- -------------------- ------------ CUST_INFO_        4096                54915                  687                   79           40   1 record(s) selected. [root:/home/db2inst1]$ db2 "select avgrowsize from syscat.tables where tabname = 'CUST_INFO_TEMP_20120917'" AVGROWSIZE ----------         49   1 record(s) selected. [root:/home/db2inst1]$ db2 "ALTER TABLE STMAP.CUST_INFO_TEMP_20120917 COMPRESS YES" DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 INSPECT ROWCOMPESTIMATE TABLE NAME CUST_INFO_TEMP_20120917 SCHEMA STMAP RESULTS KEEP rowcompression_output DB20000I  The INSPECT command completed successfully. [root:/home/db2inst1]$ db2inspf /home/db2inst1/sqllib/db2dump/rowcompression_output /home/db2inst1/sqllib/db2dump/rowcomp1.txt [root:/home/db2inst1]$ more /home/db2inst1/sqllib/db2dump/rowcomp1.txt DATABASE: STM                                                                  VERSION : SQL09017                                                              2012-09-17-10.00.57.065790                                                                                                                                    Action: ROWCOMPESTIMATE TABLE Schema name: STMAP   Table name: CUST_INFO_TEMP_20120917 Tablespace ID: 2  Object ID: 264 Result file name: rowcompression_output     Table phase start (ID Signed: 264, Unsigned: 264; Tablespace ID: 2) : STMAP.CUST_INFO_TEMP_20120917            Data phase start. Object: 264  Tablespace: 2       Row compression estimate results:       Percentage of pages saved from compression: 62       Percentage of bytes saved from compression: 62       Percentage of rows ineligible for compression due to small row size: 0       Compression dictionary size: 14720 bytes.       Expansion dictionary size: 32768 bytes.       Data phase end.     Table phase end. Processing has completed. 2012-09-17-10.00.57.347881 [root:/home/db2inst1]$ rm /home/db2inst1/sqllib/db2dump/rowcompression_output /home/db2inst1/sqllib/db2dump/rowcomp1.txt [root:/home/db2inst1]$ db2 "reorg table STMAP.CUST_INFO_TEMP_20120917 resetdictionary" DB20000I  The REORG command completed successfully. [root:/home/db2inst1]$ db2 runstats on table STMAP.CUST_INFO_TEMP_20120917 DB20000I  The RUNSTATS command completed successfully. [root:/home/db2inst1]$ db2 "select substr(a.tabname, 1, 10) as table, substr(c.tbspace, 1, 10) as tbspace, c.pagesize, b.card, b.npages, > case when (b.npages > 0) then (b.card / b.npages) else -1 end as rows_per_page, > sum(avgcollen) avg_row_size sum(avgcollen) avg_row_size > from syscat.columns a, syscat.tables b, syscat.tablespaces c > where a.tabschema = b.tabschema and a.tabname = b.tabname and b.tbspaceid = c.tbspaceid > and a.tabname = 'CUST_INFO_TEMP_20120917' > group by a.tabschema, a.tabname, c.tbspace, c.pagesize, b.card, b.npages" TABLE      TBSPACE    PAGESIZE    CARD                 NPAGES               ROWS_PER_PAGE        AVG_ROW_SIZE ---------- ---------- ----------- -------------------- -------------------- -------------------- ------------ CUST_INFO_ USERSPACE1        4096                54915                  257                  213           40   1 record(s) selected. [root:/home/db2inst1]$ db2 "select avgrowsize from syscat.tables where tabname = 'CUST_INFO_TEMP_20120917'" AVGROWSIZE ----------         18   1 record(s) selected. [root:/home/db2inst1]$ db2 drop table STMAP.CUST_INFO_TEMP_20120917 DB20000I  The SQL command completed successfully.

测试2：

[root:/home/db2inst1]$ db2 create large tablespace large_32k_test pagesize 32k managed by automatic storage bufferpool BILL_BP_32K DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 create table STMAP.CUST_INFO_temp_20120917 like STMAP.CUST_INFO in large_32k_test [root:/home/db2inst1]$ db2 "ALTER TABLE STMAP.CUST_INFO_TEMP_20120917 ADD PRIMARY KEY (IDTYPE, IDNO)" DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 "DECLARE C1 CURSOR FOR SELECT * from STMAP.CUST_INFO" DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 load from c1 of cursor messages msg.out insert into STMAP.CUST_INFO_TEMP_20120917 NONRECOVERABLE Number of rows read         = 54915 Number of rows skipped      = 0 Number of rows loaded       = 54915 Number of rows rejected     = 0 Number of rows deleted      = 0 Number of rows committed    = 54915 [root:/home/db2inst1]$ db2 runstats on table STMAP.CUST_INFO_TEMP_20120917 DB20000I  The RUNSTATS command completed successfully. [root:/home/db2inst1]$ db2 "select substr(a.tabname, 1, 10) as table, substr(c.tbspace, 1, 10) as tbspace, c.pagesize, b.card, b.npages, > case when (b.npages > 0) then (b.card / b.npages) else -1 end as rows_per_page, > sum(avgcollen) avg_row_size > from syscat.columns a, syscat.tables b, syscat.tablespaces c > where a.tabschema = b.tabschema and a.tabname = b.tabname and b.tbspaceid = c.tbspaceid > and a.tabname = 'CUST_INFO_TEMP_20120917' > group by a.tabschema, a.tabname, c.tbspace, c.pagesize, b.card, b.npages" TABLE      TBSPACE    PAGESIZE    CARD                 NPAGES               ROWS_PER_PAGE        AVG_ROW_SIZE ---------- ---------- ----------- -------------------- -------------------- -------------------- ------------ CUST_INFO_ LARGE_32K_       32768                54915                   84                  653           40   1 record(s) selected.   [root:/home/db2inst1]$ db2 "select avgrowsize from syscat.tables where tabname = 'CUST_INFO_TEMP_20120917'" AVGROWSIZE ----------         49   1 record(s) selected.   [root:/home/db2inst1]$ db2 INSPECT ROWCOMPESTIMATE TABLE NAME CUST_INFO_TEMP_20120917 SCHEMA STMAP RESULTS KEEP rowcompression_output DB20000I  The INSPECT command completed successfully. [root:/home/db2inst1]$ db2inspf /home/db2inst1/sqllib/db2dump/rowcompression_output /home/db2inst1/sqllib/db2dump/rowcomp1.txt [root:/home/db2inst1]$ more /home/db2inst1/sqllib/db2dump/rowcomp1.txt DATABASE: STM                                                                  VERSION : SQL09017                                                             2012-09-17-11.08.38.105269                                                                                                                                    Action: ROWCOMPESTIMATE TABLE Schema name: STMAP   Table name: CUST_INFO_TEMP_20120917 Tablespace ID: 13  Object ID: 4 Result file name: rowcompression_output     Table phase start (ID Signed: 4, Unsigned: 4; Tablespace ID: 13) : STMAP.CUST_INFO_TEMP_20120917            Data phase start. Object: 4  Tablespace: 13       Row compression estimate results:       Percentage of pages saved from compression: 62       Percentage of bytes saved from compression: 62       Percentage of rows ineligible for compression due to small row size: 0       Compression dictionary size: 14720 bytes.       Expansion dictionary size: 32768 bytes.       Data phase end.     Table phase end. Processing has completed. 2012-09-17-11.08.38.366376 [root:/home/db2inst1]$ rm /home/db2inst1/sqllib/db2dump/rowcompression_output /home/db2inst1/sqllib/db2dump/rowcomp1.txt [root:/home/db2inst1]$ db2 reorg table STMAP.CUST_INFO_TEMP_20120917 resetdictionary DB20000I  The REORG command completed successfully. [root:/home/db2inst1]$ db2 runstats on table STMAP.CUST_INFO_TEMP_20120917 DB20000I  The RUNSTATS command completed successfully. [root:/home/db2inst1]$ db2 "select substr(a.tabname, 1, 10) as table, substr(c.tbspace, 1, 10) as tbspace, c.pagesize, b.card, b.npages, > case when (b.npages > 0) then (b.card / b.npages) else -1 end as rows_per_page, > sum(avgcollen) avg_row_size sum(avgcollen) avg_row_size > from syscat.columns a, syscat.tables b, syscat.tablespaces c > where a.tabschema = b.tabschema and a.tabname = b.tabname and b.tbspaceid = c.tbspaceid > and a.tabname = 'CUST_INFO_TEMP_20120917' > group by a.tabschema, a.tabname, c.tbspace, c.pagesize, b.card, b.npages" TABLE      TBSPACE    PAGESIZE    CARD                 NPAGES               ROWS_PER_PAGE        AVG_ROW_SIZE ---------- ---------- ----------- -------------------- -------------------- -------------------- ------------ CUST_INFO_ LARGE_32K_       32768                54915                   33                 1664           40   1 record(s) selected. [root:/home/db2inst1]$ db2 "select avgrowsize from syscat.tables where tabname = 'CUST_INFO_TEMP_20120917'" AVGROWSIZE ----------         18   1 record(s) selected. [root:/home/db2inst1]$ db2 drop table STMAP.CUST_INFO_TEMP_20120917 DB20000I  The SQL command completed successfully. [root:/home/db2inst1]$ db2 drop tablespace large_32k_test DB20000I  The SQL command completed successfully.

结论：

从上述结果可以看出两个测试都能正常压缩，这说明之前的压缩错误确实是因为最小记录长度限制导致了。从这次的测试可以发现另一个问题，对表空间Pagesize的盲目适用，本身这个表的记录长度就比较小没有必要存放32k的表空间上，可以直接适用4k表空间。