数据清除算法：通过 DB2 for Linux, UNIX, and Windows 高效地删除 TB 级的数据

背景

在我们的 DB2 环境中有一个性能实验室数据库，大小为 1.8 TB，包含 250 万应用用户的数据。但是，我们的客户希望得到数据库中只有 120 万用户时的性能测试结果。为此，我们决定从现有的用户数据库中清除掉 130 万用户的数据。

回页首

概述

我们试用了多种技术从数据库中删除如此大量的数据。最终，我们选择了最佳的技术并为此开发了一种算法。首先，我们并没有删除不需要的数据，而且将有效数据导出并再次导入到父表（表层次结构的根节点）中。完成此工作后，我们使用 SET INTEGRITY 命令从附属的子表中删除相应的不需要数据。请注意，在执行清除操作时，表将保持在脱机状态。

回页首

算法

使用此清除算法前，考虑对以下 DB2 配置参数进行调优。请记住，我们调优这些参数的目的在于实现最优的清除算法性能。进行数据清除后，可以选择将这些参数重置回原始值，以实现最优的应用性能。这些参数设置在我们的环境中可实现最佳的性能。您可根据您的环境考虑是否进行调优。

配置

表 1. 调优配置文件注册表参数，实现最优的性能

表 2. 调优 DBM CFG 参数，实现最优的性能

表 3. 调优 DB CFG 参数，实现最优的性能

行计数

在完成配置后，会获得所有表的行数目并记录初始的数据库大小，以定义一个基准。在下面的每个查询中，使用针对您的数据库的相应相关值替换粗体显示的参数。

清单 1. 数据库度量（基准）

db2 -x "select 'runstats on table '||trim(tabschema)||'.'||trim(tabname)||';' from syscat.tables  where tabschema = 'SCHEMANAME' and type='T'" >runstats.sql  db2 –tvf runstats.sql -z runstats.out  db2 "select substr(tabname,1,30) tabname,card from  syscat.tables where tabschema = 'SCHEMANAME'  and type='T'" >initialCount.out  db2 "call get_dbsize_info(?,?,?,-1)" >initialDBSize.out  db2look -d DBNAME -a -e -l -x -c > initialDB2Look.ddl

较大的子表

SET INTEGRITY 将删除我们子表中相应的数据。如果子表很大，就会有出现死锁和事务日志问题的风险。为了避免出现这些问题，可以找到记录数在 3 亿个以上的表并断开其与父表的外键关系。这样在我们将有效数据重新加载到父表中或根节点中时，能够确保较大的子表不会被置于完整性暂挂 (integrity pending) 状态。3 亿这个数目是我们根据试验得到的，您可以选择最适合您的数据库的数目。

清单 2. 断开较大的子表

db2 "select substr(tabname,1,30) tabname,card from syscat.tables where tabschema = 'SCHEMANAME' and type='T' and card > 300000000" >isolatedTables.out  db2 -x "select 'alter table '||trim(a.tabschema)||'.'||trim(a.tabname)||' drop constraint ' || a.constname||';'  from syscat.references a,syscat.tables b where a.tabschema=b.tabschema and a.tabschema='SCHEMANAME' and b.type='T' and a.tabname=b.tabname and b.card> 300000000" > alterTable.sql  db2 –tvf alterTable.sql -z alterTable.out

每个已断开的表都是一个伪根节点，我们将重复使用相同的清除算法，在每次的重复执行过程中，将一个已断开的表视为根节点。成功地从原始表和伪根表层次结构中清除数据以后，我们将已断开的子表重新连接到其父表，以恢复原始的数据库结构。

回页首

导出数据

无需删除不需要的数据，可从根节点中导出有效数据。确保执行导出操作时所在的文件系统与被操作表的表空间容器所在的文件系统不同，以实现更高的 I/O 性能。重新加载有效的数据前，丢弃被操作表上的全部索引，并在成功地重新加载数据后重新创建索引（该技巧可提高性能）。注意，在丢弃强制使用主键的索引之前，需要先丢弃主键约束。

清单 3. 导出有效数据并丢弃索引

db2 export to EXPORT-PATH ROOT-TABNAME.csv  of del select * from ROOT-TABNAME   where WHERE-CLAUSE-CONDITIONS db2look -d DBNAME -t ROOT-TABNAME-OR-TABLIST  -e -o OUTPUT_FILE_NAME db2 -x "select 'alter table '||trim(st.tabschema)||'.'||trim(st.tabname)||' drop constraint '||st.constname||';'  from syscat.keycoluse sk inner join syscat.tabconst st on sk.tabname = st.tabname  and sk.tabschema=st.tabschema and st.tabschema='SCHEMANAME'  where st.type in ('P','U') and st.constname =sk.constname and exists  (select 1 from syscat.tables a where a.tabname=st.tabname and st.tabschema=a.tabschema and a.tabname in (ROOT-TABNAME))" >dropPK.sql db2 –tvf dropPK.sql -z dropPK.out db2 -x "select 'drop index '||trim(indname)||';' from syscat.indexes where tabname in (ROOT-TABNAME) and tabschema='SCHEMANAME'" >dropIndexes.sql db2 –tvf dropIndexes.sql -z dropIndexes.out 

加载数据

丢弃索引后，可使用 LOAD 命令的 REPLACE 选项重新加载有效数据。使用 REPLACE 与 LOAD 将确保表已被清空，然后可将有效数据加载到该表中。因此，清空表将删除我们不需要的数据。另外，重新加载有效的数据后设置表的完整性特性，会将该表的所有子表置于完整性暂挂状态。

清单 4. 重新加载有效数据

db2 "load from export file.csv of del modified by fastparse replace into TABNAME data buffer VALUE  sort buffer VALUE cpu_parallelism VALUE  disk_parallelism VALUE"  db2 "set integrity for TABSCHEMA.TABNAME immediate checked”

创建例外表

创建例外表，其中所有处于完整性暂挂状态的表都有两个额外的、类型为 TIMESTAMP 和 CLOB 的列。另外，通过引用我们在丢弃索引前所得到的 DB2LOOK 输出，重新创建被丢弃的索引。

清单 5. 创建例外表

db2 -x "select 'create table '||trim(tabname)||'_exp '|| 'like '  || tabname ||';' from syscat.tables where status='c' and type='t' and tabschema='SCHEMANAME'" >createExceptionTab.sql  db2 -x "select 'drop table '||trim(tabname)||'_exp;' from syscat.tables  where status='c' and type='t' and tabschema='SCHEMANAME'" > dropExceptionTab.sql  db2 -x "select 'alter table '||trim(tabname)||'_exp '|| ' add column c1  timestamp add column c2 clob; ' from syscat.tables where status='c' and  type='t' and tabschema ='SCHEMANAME'" >>createExceptionTab.sql  db2 –tvf createExceptionTab.sql -z createExceptionTab.out

设置表的完整性

使用相应的例外表，为所有处于完整性暂挂状态的表设置完整性。这将清空基本表中的无效数据，方法是将这些数据移到例外表中。将下面的 shell 命令保存为一个 shell 脚本 (setIntegrity.sh) 并使用它去除各个表的完整性暂挂状态。

清单 6. 设置子表的完整性

#!/bin/ksh before="$(date +%s)" DBName=$1 DBSchema=$2 echo "Checking and removing tables from set integrity pending state" | tee -a SetIntegrity.log db2 activate db $DBName>>output.out db2 connect to $DBName>>output.out db2 set schema $DBSchema>>output.out db2 -x "select 'SET INTEGRITY FOR '|| TABSCHEMA ||'.'||TABNAME || '  IMMEDIATE CHECKED FOR EXCEPTION IN ' ||TABNAME || ' USE ' ||TABNAME||'_exp ;' from SYSCAT.TABLES where STATUS='C' and type='T' and TABSCHEMA='$DBSchema'  order by card " >chkset_integrity.sql tabcnt=$(wc -l chkset_integrity.sql) while [[ ${tabcnt} -gt 0 ]]; do echo "**************************************************" | tee -a SetIntegrity.log echo "Number of tables in set integrity pending state : $tabcnt" | tee -a SetIntegrity.log echo "Setting integrity of table in set integrity pending state" | tee -a SetIntegrity.log echo "**************************************************" | tee -a SetIntegrity.log db2 connect to $DBName>>output.out db2 set schema $DBSchema>>output.out db2 -tvfchkset_integrity.sql>>output.out db2 "commit" >>output.out db2 -x "select 'SET INTEGRITY FOR '|| TABSCHEMA ||'.'||TABNAME || '  IMMEDIATE CHECKED FOR EXCEPTION IN ' ||TABNAME || ' USE ' ||TABNAME||'_exp ;' from SYSCAT.TABLES where STATUS='C' and type='T' and TABSCHEMA='$DBSchema'  order by card " >chkset_integrity.sql tabcnt=$(wc -l chkset_integrity.sql) done echo "No table in set integrity pending state" | tee -a SetIntegrity.log after="$(date +%s)" elapsed_seconds="$(expr $after - $before)" timediff=`echo - | awk -v "S=$elapsed_seconds" '{printf"%dh:%dm:%ds",S/(60*60),S%(60*60)/60,S%60}'` echo "Time Taken to set Integrity: $timediff" | tee -a SetIntegrity.log

回页首

重新创建外键

重复使用相同的流程来导出并重新加载所有伪节点的有效数据。清除所有表的数据后，重新创建被丢弃的外键约束（通过引用 intitialDB2Look.ddl 和 alterTable.sql），将伪根表层次结构链接回根表并恢复原始的数据库结构。另外，还要丢弃例外表，因为我们不再需要它了。

清单 7. 丢弃例外表

db2 -tvf dropExceptionTab.sql –z dropExceptionTab.out

最终的度量

由于我们从表中删除了极大量的数据，所以应执行 reorg 操作，并且如果可能，为 reorg 操作使用临时表空间。在所有表上执行 runstats 操作，以更新统计数据。然后记下最新的行数目、数据库大小以及 DB2look 输出，并将其与最初的输出（基准）进行比较。

清单 8. 了解最终的表和数据库度量值

db2 -x "select 'reorg table '||trim(tabname)||';'   from syscat.tables where type='T' and tabschema='SCHEMANAME'">reorg.sql db2 –tvf reorg.sql -z reorg.out db2 -x "select 'runstats on table '||trim(tabschema)||'.'||trim(tabname)||';'  from syscat.tables where tabschema = 'SCHEMANAME' and type='T'" >runstats.sql db2 –tvf runstats.sql -z runstats.out db2 "select substr(tabname,1,30) tabname,card from syscat.tables  where tabschema = 'SCHEMANAME' and type='T'" >finalCount.out db2 "call get_dbsize_info(?,?,?,-1)" >finalDBSize.out db2look –d DBNAME -a -e -l -x -c > finalDB2look.ddl 

利用 reorg 操作，我们降低了表空间中所使用的页面数，但是表空间的高水线 (watermark) 数值可能仍高于实际使用的页面数，所以要降低这个高水线值。

清单 9. 降低 DMS 表空间的高水线值

db2 -x "select 'alter tablespace '||trim(tbspace)||' lower high water mark ;'  from syscat.tablespaces where tbspacetype='D'">highWatermark.sql  db2 –tvf highWatermark.sql -z highWatermark.out

降低这个高水线值后，如果表空间的大小仍大于高水线值并且用备份还原了数据库，那么数据库所用的磁盘仍会高于已用页面所需的空间。为了最大限度降低这种磁盘空间需求，考虑减小表空间的大小。

回页首

监视清除流程

利用该清除算法，我们可以使用 SET INTEGRITY 命令从子表中删除数据。为了确定要删除哪些数据，我们只会引用目标表及其相应的父表。这就意味，在任何时间点，只有目标表的缓冲池及其父表正在使用中。使用 DB2TOP 进行监视并在清除流程中的每个步骤上调优活动缓冲池的大小，以实现最优的性能。

回页首

牢记以下事情

• 可以考虑修改 EXPORT 和 LOAD 命令，将 LOBS 保留在一个单独的文件系统中。
• 仔细观察数据库中各个表之间的循环依赖关系。使用 setintegrity.sh 脚本之前，需要打破某些依赖关系，否则脚本会进入无限循环中。使用下面的查询方法查找各个表之间的循环依赖关系。

db2 "select substr(a.tabname,1,20)tabname,substr(a.reftabname,1,20)         reftabname,substr(a.constname,1,20)constname from syscat.references         a where exists (select 1 from syscat.references b where          a.reftabname=b.tabname and a.tabname=b.reftabname)"

回页首

结束语

如下表所示，与传统的数据清除技术相比，数据清除算法的执行效果很好，死锁和事务日志问题很少，甚至根本没有。这种算法所需的时间最少并且让用户能够更好地控制清除流程。对于这种方法，总时间中 SET INTEGRITY 阶段占用的时间要高于 LOAD 阶段，所以不要期望时间会很短。另外，要注意的一个重要方面是，如果使用级联删除或已存储的程序方法清除数据，表仍处于联机状态，而清除算法则需要表是脱机的。

图 1. 对比各种数据清除技术

回页首

致谢

感谢 Ashish Wadnerkar 和 Abhijeet Chaudhari 对本教程的撰写所提供的帮助和指导。