删除数据库行会锁表吗

您是否有一个包含大量已删除行的IBM DB2表？ 尽管这似乎是一个基本问题，但有趣的是，许多IBM i商店都不知道答案。 通常，大多数IBM i商店都没有数据库管理员来监视这些类型的详细信息。 我在一次客户参与中看到的最糟糕的情况是一张表中有26亿（是的，即十亿）已删除的行。 而且那个桌子经常被扫描。 许多客户不仅不确定他们的数据库中有多少删除的行，而且他们也不了解这些删除的行所产生的影响。

让我们看看是否可以帮助您回答两个问题：

哪些表具有大量已删除的行？ 这些删除的行是否浪费了处理器和内存资源？

当然，删除的行会占用磁盘空间，但是多少呢？ 我们还想了解这些删除的行是否引起额外的磁盘操作，占用内存空间以及在扫描操作上浪费处理器利用率。 当将来将新行添加到表中时，最好知道是否要重用已删除行消耗的存储。

首先，让我们找到其中删除行最多的DB2表和物理文件。 在开始此分析之前，您需要对一个或多个主要生产数据库执行以下数据收集。 使用以下命令为此收集数据创建一个库：

CRTLIB DELETDROWS

对每个库运行以下CL命令（将LIBNAME替换为您的数据库名称或*ALLUSR ）。 这些命令收集有关命名库中文件的信息。

DSPFD FILE(LIBNAME/*ALL) TYPE(*MBR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(DELETDROWS/DSPFD_MBR) OUTMBR(*FIRST *ADD) DSPFD FILE(LIBNAME/*ALL) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(DELETDROWS/DSPFD_ATR) OUTMBR(*FIRST *ADD)

在这两个新创建的文件上创建几个索引，以提高分析的性能。

CREATE INDEX DELETDROWS.DSPFD_ATR_IX ON DELETDROWS.DSPFD_ATR(PHFILE); CREATE INDEX DELETDROWS.DSPFD_MBR_IX ON DELETDROWS.DSPFD_MBR(MBFTYP, MBFILE, MBNDTR);

从任何SQL接口运行以下过程调用。 此存储过程将SQL计划缓存条目转储到DELETDROWS库中名为PCSS的表中。

CALL QSYS2.DUMP_PLAN_CACHE('DELETDROWS', 'PCSS')

步骤1 –识别具有大量已删除行的表

以下SQL查询返回删除行数最大的前25个表。 如果您在查询输出中看到大量使用的表，请考虑重新组织它们以消除已删除的行。

SELECT DISTINCT F.MBFILE AS FILENAME, F.MBNAME as Member, MBLIB AS LIBRARY, MBNRCD AS "Number Non-Deleted Rows", MBNDTR AS "Number Deleted Rows", PHRUSE AS "Reusing Deleted Rows", integer(mbndtr/(Case when mbnrcd+mbndtr=0 then 1 else mbnrcd+mbndtr end) * 100) as "Percent Deleted", Case when MBDSSZ >0 then MBDSSZ ELSE MBDSZ2 END as Size, Integer(Case when MBDSSZ >0 then MBDSSZ ELSE MBDSZ2 END * (mbndtr/(Case when mbnrcd+mbndtr=0 then 1 else mbnrcd+mbndtr end))) as "Deleted Space" FROM DELETDROWS.DSPFD_MBR AS F JOIN DELETDROWS.DSPFD_ATR AS A ON F.MBFILE = A.PHFILE WHERE MBFTYP = 'P' AND MBNDTR > 10000 ORDER BY MBNDTR DESC FETCH FIRST 25 ROWS ONLY

您可以修改此查询以查找超出已删除行阈值的表。 在此示例中，阈值设置为10,000行(mbndtr > 10000）。 您还可以按“ 已删除百分比”值对结果进行排序，以在顶部列出删除行百分比最高的表。

图1 –步骤1查询的示例输出

步骤2 –查找浪费资源SQL语句

一些SQL语句会消耗更多的系统资源，以根据运行时实现中使用的访问方法来处理已删除的行。 本节重点介绍由于表中已删除的行而使两种方法更昂贵的方法。

执行表扫描SQL语句

由于删除了行，因此表扫描操作显然更昂贵。 当DB2扫描表以查找符合搜索条件的未删除的行时，将访问每个已删除的行。 即使使用更高级的方法来跳过不符合搜索条件的行，也可能需要更多的磁盘I / O操作。 需要执行更多操作，因为通常，表中的页面稀疏地填充有未删除的行。

以下SQL查询标识作为表扫描目标的表，并说明已处理的删除行总数最大。

with bigdel as ( SELECT distinct mbfile as File, mbname as Member, mblib as Library, mbnrcd as NumberRecords, mbndtr as NumberDeletedRecords, phruse as ReuseDeleted FROM deletdrows.DSPFD_MBR F join deletdrows.DSPFD_ATR a on f.MBFILE=a.PHFILE WHERE mbftyp ='P' AND mbndtr >10000 ) -- Join table scan info from the plan cache SELECT QQTLN as LIBrary, QQTFN as "Table Name", D.REUSEDELETED as "Reusing Deleted Rows" , Max(QQTOTR )as "Number Non-Deleted Rows", max(d.numberDeletedRecords) as "Number Deleted Rows" , SUM(d.numberDeletedRecords) as "Total Deleted Rows Scanned", count(*) as TotalScans FROM deletdrows.PCSS M JOIN bigdel d ON d.mblib=m.qqtln AND d.mbfile=m.qqtfn AND d.Member=m.qqtmn WHERE qqrid = 3000 AND QQC11 <>'Y' GROUP BY qqtln, qqtfn, d.reusedeleted ORDERY BY SUM(d.numberDeletedRecords) DESC FETCH FIRST 25 ROWS ONLY OPTIMIZE FOR ALL ROWS

它不在本文的讨论范围之内，但是您应该查找导致这些表扫描操作SQL语句，并查看它们是否创建其他索引可以消除表扫描方法的使用。 但是，这并不能消除减少数据库中这些非常常用的DB2表的大小的需要。 从图1中可以看到，每个DELTEST的表扫描都会处理210 MB的已删除行占用的存储空间。

图2 –步骤2查询的示例输出

执行临时索引构建SQL语句

索引构建必须执行更多的I / O操作，以引入具有已删除行的存储页面，而只能在创建索引期间跳过已删除的行。 其中一些索引构建可能会发生在小型表上，但是多次创建临时索引可能会放大那些被删除的行的开销。

以下SQL查询标识了涉及在具有大量已删除行（大于10000）的表上进行临时索引构建SQL语句。

with bigdel as ( Select mbfile, mblib,F.MBNAME as Member, mbftyp, MBNRCD as NumberRecords, MBNDTR as numberDeletedRecords, PHRUSE as REUSEDELETED, MBDSSZ as MBRSIZE FROM deletdrows.DSPFD_MBR F join deletdrows.DSPFD_ATR a ON f.MBFILE=a.PHFILE WHERE MBFTYP ='P' AND mbndtr >10000) -- Join Index builds to the tables with large number of deleted rows Select QQTLN as Library, QQTFN as "Table Name", D.REUSEDELETED as "Reusing Delweted Rows", Max(QQTOTR )as "Non-Deleted Rows", max(d.numberDeletedRecords) as "Deleted Rows", SUM(d.numberDeletedRecords) as "Total Deleted Rows Scanned", SUM(M.QQRIDX) as "Total index entries created", QQIDXD as Index_Advised_Columns, Sum(case when QQC16='N' then qqi6 else 0 end) as total_keys_built, Sum(case when QQC16='N' then 1 else 0 end ) as indexCreated, Sum(case when QQC16='Y' then 1 else 0 end ) as indexreused, Count(*) as TotalIXsCreated FROM deletdrows.PCSS M JOIN bigdel d ON d.mblib=m.qqtln and d.mbfile=m.qqtfn and d.Member= m.qqtmn WHERE qqrid = 3002 GROUP BY qqtln, qqtfn ,qqtmn, d.REUSEDELETED, m.QQRCOD , QQIDXD ORDER BY SUM(d.numberDeletedRecords)DESC FETCH FIRST 25 ROWS ONLY OPTIMIZE FOR ALL ROWS

该查询的输出有助于识别那些很适合重组的表。 该报告的另一个好处是，这也是查看某些可能需要创建以消除临时索引构建的索引的好方法。

步骤3 –查看访问量高的表

如果您在系统上运行IBM i 7.1或更高版本，则可以使用新的访问计数器来识别经常访问的表。 对于SQL和非SQL接口，这些计数器都由DB2自动递增。 要标识访问量更高的DB2表，请运行以下查询并查找具有大量已删除行的表。

SELECT table_schema, Table_name, Data_size, Number_Deleted_Rows, Logical_Reads, Physical_reads, Sequential_reads, Random_reads FROM qsys2.systablestat ORDER BY Logical_reads DESC FETCH FIRST 25 rows ONLY

在此示例中，图3中的查询输出仅显示一个表DELTEST，该表具有大量已删除的行。

图3 –来自高度访问的表查询的示例结果

步骤4 –修正有问题的表格

要从以前的分析方法确定的表中删除已删除的行，我们需要使用“重新组织物理文件成员（RGZPFM）”系统命令。 但是，在执行重组操作之前，需要考虑一些重要点。 如果重新组织它们使用的一个或多个表，则某些应用程序可能无法正常工作。 发生这种情况的原因有两个：

该应用程序依赖于某些行的物理位置。 您将必须获取特定行的相对记录号（RRN），并将该值存储在其他位置，以便以后可以使用该值访问该行。 对于某些本机记录级别的访问请求或SQL RRN函数，可能需要它。 这种情况不太可能，但是有可能。 但是，这不是推荐的编程习惯。 该应用程序可能依赖于到达顺序中存储的行。 也就是说，在另一行之后插入的行将具有较高的RRN值。

另一个关键考虑因素是文件属性，该属性控制是否将删除的记录空间重新用于要插入表中的新行。 如果文件正在重用已删除的行，则除非永不删除行，否则应用程序将不具有到达顺序依赖性。

重组有两个基本选项。 第一种选择是向上滑动所有行以填充已删除的漏洞，然后在最后截断一组已删除的行。 以这种方式执行重组可以维护到达顺序，因此，具有到达顺序顺序依赖性的应用程序继续起作用。

第二种选择是将未删除的行从表的末尾移到表的开头的已删除的行位置。 此方法破坏了行的到达顺序序列，但通常比第一种方法执行得更好。 有关RGZPFM命令的更多详细信息，请参阅参考资料小节中列出的IBM Redpaper™。

监视和管理已删除的行空间是DB2 for i数据库工程师应定期执行的关键活动之一。 如果您没有DB2 for i数据库工程师，则可以阅读博客文章以更好地了解该职位的角色和职责。

希望本文可能使您有动力开始关注已删除的行空间，并允许您采取集中行动以最大程度地减少浪费在已删除行上的系统资源。

您还可以使用“阶段2系统限制”支持来跟踪最大文件中的更改，包括删除操作。 有关系统限制支持的更多信息，请参阅第2阶段 ， 获得对具有系统限制的i DB2的深入了解。

翻译自: https://www.ibm.com/developerworks/ibmi/library/i-db2-table-with-deleted-rows/index.html