db2 list tablespaces show detail隐藏功能

情况：

在一次表分区清理的过程中碰到了这样一个问题。当我将已经不需要使用的多个表分区deatch后直接删除掉。这时候使用快照查看表空间free pages时发现没有变化。后来经过同事的建议用list tablespaces show detail试试看。结果执行list tablespaces show detail后发现表空间的free pages得到释放。

测试如下：

List tablespaces show detail当前表空间的情况：

Tablespace ID = 2 Name = ABC_TBSP1 Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Total pages = 5242880 Useable pages = 5242848 Used pages = 889920 Free pages = 4352928 High water mark (pages) = 1122432 Page size (bytes) = 4096

Get snapshot for tablespace当前表空间情况：

Tablespace name = ABC_TBSP1 Tablespace ID = 2 Tablespace Type = Database managed space Tablespace Content Type = All permanent data. Large table space. Tablespace Page size (bytes) = 4096 Tablespace Extent size (pages) = 32 Automatic Prefetch size enabled = Yes Buffer pool ID currently in use = 1 Buffer pool ID next startup = 1 Using automatic storage = No Auto-resize enabled = No File system caching = No Tablespace State = 0x'00000000' Detailed explanation: Normal Tablespace Prefetch size (pages) = 32 Total number of pages = 5242880 Number of usable pages = 5242848 Number of used pages = 889920 Number of pending free pages = 0 Number of free pages = 4352928 High water mark (pages) = 1122432

从上面可以看出当前表空间的free pages都为4352928，下面进行表的删除操作。因为没有进行记录可以简单说明下。删除表tb1，该表存放在表空间abc_tbsp1中，有29137 pages。直接执行drop table tb1。

删除后查看get snapshot for tablespaces：

Tablespace name = ABC_TBSP1 Tablespace ID = 2 Tablespace Type = Database managed space Tablespace Content Type = All permanent data. Large table space. Tablespace Page size (bytes) = 4096 Tablespace Extent size (pages) = 32 Automatic Prefetch size enabled = Yes Buffer pool ID currently in use = 1 Buffer pool ID next startup = 1 Using automatic storage = No Auto-resize enabled = No File system caching = No Tablespace State = 0x'00000000' Detailed explanation: Normal Tablespace Prefetch size (pages) = 32 Total number of pages = 5242880 Number of usable pages = 5242848 Number of used pages = 845280 Number of pending free pages = 44640 Number of free pages = 4352928 High water mark (pages) = 1122432

可以看出使用快照查看时表空间并没有变化，还是4352928 pages

下面使用list tablespaces show detail查看表空间容量：

Tablespace ID = 2 Name = ABC_TBSP1 Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Total pages = 5242880 Useable pages = 5242848 Used pages = 845280 Free pages = 4397568 High water mark (pages) = 1122432 Page size (bytes) = 4096 Extent size (pages) = 32 Prefetch size (pages) = 32 Number of containers = 1

使用list tablespaces show detail查看得到的free pages为：4397568 pages，相对于之前的4352928 pages 增加了 44640 pages。 而我们删除的表为29137 pages。这里反倒多15503 pages，为何会多出这些页了等我有更多知识的时候再去测试。但我们可以知道执行list tablespaces show detail后确实释放了空间。这是为什么了，信息中心给了我们答案：

When the LIST TABLESPACES SHOW DETAIL command is issued, it will attempt to free all pending free extents in the table space. If the pending free extents are freed successfully, a record will be logged.

信息中心的解释高速我们一个很重要的信息是，当我们删除表时原先使用的extent不是立即释放的，而是变成pending free extent，这时再执行list tablespaces show detail时会将这个状态的extent进行释放。

总结：

这里还有一个疑点是pending free 状态的extent会被重新使用吗？这个问题我们以后还有机会再进行测试。

这次测试说明一个问题，get snapshot for tablespaces查看到的空间不一定准确，如果希望获得准确的free pages需要使用list tablespaces show detail，注意这里必须是show detail。话说IBM还蛮会设计奇怪的东西的，这list命名看了像在查询数据的这里反倒还有处理数据的功能，让人很容易误解啊。