db2 查看表占用磁盘空间_同时将DB2 for i表和索引移动到固态磁盘

DB2 for i媒体首选项概述

2009年中对V5R4和IBM i 6.1发行版的IBM i添加了固态磁盘（SSD）支持。 使用SSD驱动器的速度大大快于传统磁盘，因此可以为大多数应用程序提供性能优势。 但是，SSD的价格曾经并且仍然比传统磁盘高，因此必须让客户能够最大程度地利用他们在SSD上的投资。 最大化对SSD的投资的方法是将DB2 for i数据放置在SSD上，这将最大限度地提高应用程序的性能。

因此，应将哪些数据放在SSD上以最大化性能？

三种关键数据类型通常不适合使用SSD。

1. 很少访问的数据。 例如，如果您的历史数据存储在很少访问的表中，则不希望其位于价格更高的SSD存储器中。
2. 主要是写入密集型的数据。 尽管SSD写入比传统的磁盘写入要快，但是磁盘写入缓存通常可提供良好的磁盘写入性能，并可以平衡两种类型之间的竞争环境。 因此，由于日记记录接收器主要是写密集型的设备，因此它不是放置SSD的不佳选择。
3. 主要按顺序读取的数据。 依次读取IBM i中的数据时，将使用复杂的预分配逻辑和缓冲将数据提前放入内存中以供使用。

SSD的最佳选择是DB2 for i对象，其中包含经常随机访问的数据。 例如，针对存储在SQL索引或键控逻辑文件中的键值进行随机键查找操作。

有多种方法可以将DB2 for i对象放置在SSD上，但是最有效的方法之一是使用DB2介质首选项属性。 可以为索引或键控文件指定媒体首选项，以请求在SSD存储器上分配基础索引对象。 还可以在表或物理文件上指定媒体首选项，以请求将数据本身分配在SSD存储器上。 但是，由于数据本身通常比索引大得多，因此在SSD上分配最频繁和随机访问的索引可能会更好地利用价格较高的SSD存储。

创建索引或数据或将其移动到SSD的先前方法

V5R4中对SSD的最初支持允许用户在创建时指定媒体首选项，或者稍后使用change命令对其进行更改，如以下示例所示：

CRTPF FILE(MJATST/pf1) UNIT(255)
    
CRTLF FILE(MJATST/lf1) UNIT(255)
    
CHGPF FILE(MJATST/pf1) UNIT(255)
    
CHGLF FILE(MJATST/lf1) UNIT(255)

在IBM i 6.1发行版中，将UNIT（* SSD）添加到CL命令中，作为UNIT（255）的更有用的替代方法。 IBM i 6.1发行版中还添加了支持，以允许用户在SQL语句中指定媒体首选项，如以下示例所示：

CREATE TABLE sales 
 (unid INT primary key, salesdate DATE, itemnbr INT, quantity INT, price DECIMAL(9,2) )
UNIT SSD
    
CREATE INDEX salesinx on sales (unid) UNIT SSD
    
ALTER TABLE sales ALTER UNIT SSD

如果SQL表是分区表，则每个分区（成员）都可以具有单独的媒体首选项。 这允许用户对表进行分区，以使一个表分区中的频繁访问数据可以位于SSD上，而不那么频繁访问的数据可以置于传统磁盘上的另一个表分区中。 例如，当前数据可能在SSD上的分区中，而历史数据可能在传统磁盘上的分区中：

CREATE TABLE sales 
 (unid INT primary key, salesdate DATE, itemnbr INT, quantity INT, price DECIMAL(9,2) )
 PARTITION BY RANGE(salesdate NULLS LAST)
 (PARTITION sales2006 
      STARTING ('2006-01-01') ENDING('2006-12-31') INCLUSIVE UNIT ANY, 
 PARTITION sales2007 
      STARTING ('2007-01-01') ENDING('2007-12-31') INCLUSIVE UNIT ANY, 
 PARTITION sales2008 
      STARTING ('2008-01-01') ENDING('2008-12-31') INCLUSIVE UNIT SSD )

ALTER TABLE sales ALTER PARTITION sales2007 
      STARTING ('2007-01-01') ENDING('2007-12-31') INCLUSIVE UNIT ANY

尽管V5R4（尤其是IBM i 6.1）对媒体首选项的支持广泛，但仍存在差距：

• 可以在SQL分区表的分区（成员级别）上控制媒体首选项，但是对于具有多个成员的DDS创建的物理文件则无法做到这一点。
• 无法在分区SQL索引的不同分区（成员）上指定不同的媒体首选项，更不用说DDS创建的具有多个成员的键控逻辑文件了。
• 最后，所有现有的语句和CL命令都需要对整个文件进行排他锁定。

现在，通过在以下PTF中还提供了CHGPFM和CHGLFM命令，可以解决这些缺陷：IBM i 6.1（SF99601版本19）和IBM i 7.1（SF99701版本6）。 没有计划在V5R4上支持这些增强功能。

使用CHGPFM将数据移至SSD

CHGPFM命令将把成员（或分区）的数据移入或移出SSD。 例如：

CHGPFM FILE(MJATST/SALES) MBR(SALES2007) UNIT(*SSD) 
    
CHGPFM FILE(MJATST/SALES) MBR(SALES2007) UNIT(*ANY)

请注意，如果对物理文件进行了键控（可能是因为它具有“主键”约束），则也可以使用这些命令将其索引移入或移出SSD。

使用CHGPFM和CHGLFM将索引移至SSD

将索引移至或移出SSD有点复杂。 在几种不同的情况下创建索引：

• 为键控的物理或逻辑文件创建索引。 这包括DDS创建的文件和SQL索引，以及具有“主键”约束的物理文件。 CHGPFM或CHGLFM命令将在SSD上打开或关闭与键控或逻辑文件关联的索引。 例如：

CHGPFM FILE(MJATST/pf1) MBR(m1) UNIT(*SSD)

CHGLFM FILE(MJATST/lf1) MBR(m1) UNIT(*SSD)

• 为唯一或外键约束创建索引。 CHGPFM不会在SSD上移动唯一索引或外键索引。
• 可以为DDS创建的联接逻辑文件创建多个索引。 CHGLFM会将与连接逻辑文件关联的所有二级索引移入或移出SSD。

那么在以下情况下您会怎么做？

1. 您有一个加密的物理文件，但是只希望在SSD上拥有索引，而不需要数据本身，因为物理文件中的数据量太大。
2. 您有一个与唯一或外键约束关联的索引，该约束在要放入SSD的查询中大量使用。
3. 您有一个联接逻辑文件，但只希望SSD上的键的索引，而不是辅助索引。

处理每种情况的方法是创建简单的键控逻辑文件或SQL索引，它们共享您想要的SSD上的索引（或访问路径）。

使用CHGLFM和索引共享将索引移至SSD

当数据库管理器检测到您正在创建相同或在某些情况下已经存在的索引子集的索引时，就会发生索引共享。 共享相同的索引，而不是创建必须维护的全新索引。

当多个文件共享索引时，如果任何共享文件的媒体首选项指定了SSD，则索引将具有SSD的媒体首选项。 如果没有共享索引的文件均未指定SSD，则索引的媒体首选项为ANY。

假设您有一个DDS创建的键控物理文件，并且只需要SSD上的索引而不是数据。 您可以创建键控逻辑文件或共享物理文件索引SQL索引。 这是完成索引共享SQL Create Index语句的示例。

CREATE INDEX inx1 ON pf1 (key1) PAGESIZE 8

然后，您可以对新的逻辑文件或SQL索引发出CHGLFM，以将索引移至SSD上或移出SSD，并且数据将不会移动：

CHGLFM FILE(MJATST/inx1) UNIT(*SSD)

请注意，如果在创建共享索引时指定了SSD属性，则该索引将在此时移至SSD。

要共享逻辑文件或索引，索引属性必须兼容。 例如，具有LIFO属性的索引不能与具有FIFO属性的索引共享。 以下属性必须兼容：

• 关键字段和关键字段的顺序（有时允许使用关键字段的子集）
• 上升或下降
• 重复的键顺序（UNIQUE，UNIQUE WHERE NOT NULL，LIFO，FIFO和FCFO）
• 排序顺序或替代整理顺序
• 逻辑页面大小（例如，在上面的CREATE INDEX语句中，需要PAGESIZE）
• 最大大小（* MAX1TB，* MAX4GB或EVI）

确定现有索引的属性是什么以及确定所创建的索引是否实际共享访问路径的最简单方法是使用System i Navigator中的Show Indexes函数。 “显示索引”任务显示您希望共享的现有索引的所有属性（请参见下面的图1和2）。

图1.启动表的显示索引

图2.显示索引示例

显示索引也仅显示真实索引，而不显示仅共享另一个文件索引的文件。 创建新索引后，刷新“显示索引”列表。 如果在“显示索引”列表中看到它，则这些属性之一必须不兼容。 将新索引的属性与您要共享的索引的属性进行比较，以查看不匹配的内容。 请更正CREATE INDEX语句或逻辑文件的DDS中的属性，然后重试。 Show Indexes功能还显示每个索引的媒体首选项，因此，一旦创建共享索引并将媒体首选项更改为SSD，刷新Show Indexes列表即可显示SSD媒体首选项。

CHGPFM和CHGLFM并发

如前所述，以前所有更改媒体首选项的机制都需要对整个文件进行排他锁定。 如果任何应用程序当前正在访问该文件或该文件的任何成员，这可以防止更改媒体首选项。 相反，如果能够获取排他锁，则并发应用程序将无法访问该文件，并且可能因锁超时而失败。

CHGLFM和CHGPFM不需要像以前的方法那样的排他锁。 * SHRUPD锁是必需的，但这仅与在文件上具有* SHRNUP，* EXCLRD或* EXCL锁的另一个作业冲突。 这时，DB2引擎获取了一个专用的seize，以同步移动数据，因此，在不会发生锁定超时的同时，其他作业将等待移动完成。 在某些时候，我们可能会更改命令以异步移动数据，这不会导致其他作业等待移动完成。

您如何知道SSD上实际有多少索引或数据？

当您在SSD上移动对象时，如果SSD上没有足够的空间，则只有部分对象将最终出现在SSD上。 因此，您可能有时会想知道实际上在SSD上分配了多少对象。 幸运的是，通过以下PTF提供了另一组增强功能，以解决此问题。

• V5R4（SF99504版本28）
• IBM i 6.1（SF99601版本16）
• IBM i 7.1（SF99701版本4）

提供了新的目录视图，以返回有关索引和数据的实际磁盘分配的信息。

• QSYS2.SYSPARTITIONDISK是一个视图，它将返回DB2 for i表和物理文件的分配信息。 例如，以下Select语句将报告模式（库）MJATST中所有SQL表或物理文件的数据分配：

SELECT MAX(table_schema) AS table_schema, MAX(table_name) AS table_name, 
   MAX(table_partition) AS table_partition, 
   SUM(CASE WHEN unit_type = 1 THEN unit_space_used ELSE null END) AS ssd_space,
   SUM(CASE WHEN unit_type = 0 THEN unit_space_used ELSE null END) AS non_ssd_space
FROM qsys2.syspartitiondisk a
WHERE system_table_schema = 'MJATST'
GROUP BY a.table_schema, a.table_name, table_partition
ORDER BY 1,2,3

以下SELECT语句将返回在模式（库）MJATST中对SSD进行了一些分配的所有SQL表或物理文件。

SELECT 	DISTINCT table_schema, table_name, table_partition
FROM qsys2.syspartitiondisk a
WHERE system_table_schema = 'MJATST' and UNIT_TYPE = 1

• QSYS2.SYSPARTITIONINDEXDISK是一个视图，它将返回DB2 for i索引（即，键控文件，约束索引和SQL索引）的分配信息。 例如，以下Select语句将报告表（库）MJATST中表或物理文件上所有索引的索引分配：

SELECT index_schema, index_name, index_member, index_type, 
  SUM(CASE unit_type WHEN 1 THEN unit_space_used ELSE 0 END) AS ssd_space, 
  SUM(CASE unit_type WHEN 0 THEN unit_space_used ELSE 0 END) AS nonssd_space 
FROM qsys2.syspartitionindexdisk  b 
WHERE system_table_schema = 'MJATST' 
GROUP BY index_schema, index_name, index_member, index_type
order by 5 desc, 6 desc

注意：如果您有跨度索引，则需要除以max（partnbr）。

SELECT index_schema, index_name, index_member, index_type, 
  SUM(CASE unit_type WHEN 1 THEN unit_space_used ELSE 0 END)/max(partnbr) AS ssd_space, 
  SUM(CASE unit_type WHEN 0 THEN unit_space_used ELSE 0 END)/max(partnbr) AS nonssd_space 
FROM qsys2.syspartitionindexdisk  b 
WHERE system_table_schema = 'MJATST' 
GROUP BY index_schema, index_name, index_member, index_type
order by 5 desc, 6 desc

以下SELECT语句将返回在模式（库）MJATST中对SSD进行了一些分配的表或物理文件上的所有索引：

SELECT DISTINCT index_schema, index_name, index_member, index_type
FROM qsys2.syspartitionindexdisk  b 
WHERE system_table_schema = 'MJATST' and UNIT_TYPE = 1;

摘要

CHGPFM和CHGLFM提供了一种轻松灵活的方式来将索引和数据移入或移出SSD，因此您可以充分利用SSD的投资。 增强的命令不需要像以前的方法一样的排他锁。

翻译自: https://www.ibm.com/developerworks/ibmi/library/i-db2ssd/index.html