情况描述：
为表设置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发现了一篇文章：
该文章中提到在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:
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 =
Tablespace ID = 3
Name = CUST_DAT
Type = System
managed space
Contents = All permanent
data. Regular table space.
State = 0x0000
Detailed explanation:
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 =
Tablespace ID = 13
Name =
LARGE_32K_TEST
Type = Database managed space
Contents = All permanent
data. Large table space.
State = 0x0000
Detailed explanation:
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 =
|
其中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
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.
[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
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.
[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表空间。
No comments:
Post a Comment