db2清理大批量数据的实践分享

“案发”当日（２月２９日，四年才相逢的日子）：

ecif数据库中一张关键表crossindex（ecif客户与其他源系统客户对照关系表，下文均用表A代替）在批量作业处理时异常增大，停止时该表数据为1.4亿条数据。

数据异常增大的原因本文就不在赘述了，详细原因足够写一个长篇回忆录了，说多了全是眼泪。之前做过统计，此表正常数据应当为1200万条左右。看到下图结果的时候，有点小崩溃。

一个简单的count（1）后查询“仅”用了3分20秒，异常数据总条数在1.3亿条左右，并且查看了当时的表空间，数据表空间已剩余不多，索引表空间全部被占满了。确切的说是因为索引表空间占满了，批量数据才得以停止，否则数据量会更大。

命题：

一个有业务系统使用的数据库，需要将其中的一张表，从１.4亿条数据中删除1.3亿，最终只保留有用的1200万数据，且索引表空间已满，无法增加表空间。磁盘空间不足，无法备份。

前提：

幸运的是，数据库之前做过优化。具体有以下几个参数：

db2set DB2_SKIPINSERTED=on

db2set DB2_EVALUNCOMMITTED=on

这两个参数主要是提高数据库的并行处理能力，确保批量程序处理过程中，其它应用可以查询数据。其实就是延迟行锁定，当在表扫描或索引扫描期间执行行锁定时，DB2 会先锁定已扫描的每一行然后再确定该行是否符合查询要求，直到确定某行符合查询要求为止。

调整数据库锁列表比例，控制应用在过多情况占用锁资源；

update db cfg for ecifdb using MAXLOCKS 30 （目前生产环境大多数系统是采取自动配置，大小为97%）

调整锁资源大小；

update db cfg for ecifdb using LOCKLIST 204800（目前生产环境大多数系统是采取自动配置，大小为4968页）

调整锁等待超时时长；

update db cfg for ecifdb using LOCKTIMEOUT 40（目前生产环境大多数系统是采取自动配置，大小为1800秒）

直白的说，有了以上数据库参数的调整，即使我们去进行数据删除时，也不会导致数据库的崩溃，毕竟数据量有点大。

解题：

1、介于环境、磁盘空间因素，想将里面有效的数据导出后再load空表，最后将导出数据再load回去，这种方式不可取。

2、由于索引表空间已经全部占满，删除时需要额外开销维护索引，所以必须先对索引表空间进行处理，否则依旧是删不动的。根据删除条件，暂时只保留时间戳上的索引\"IDX_CROSSINDEX_CREATE_TM\"，将该表剩余的3个索引暂时drop。

DROP INDEX \"ECIFMODEL\".\"IDX_CROSSINDEX_CONT_ID\"

DROP INDEX \"ECIFMODEL\".\"IDX_CROSSINDEX_SRC_SYS_CUST_NO\"

DROP INDEX \"ECIFMODEL\".\"IDX_CROSSINDEX_UPDATE_TM\"

3、在通过分析批量日志后，发现从2016年2月29日18点到2016年3月1日凌晨4点后均生成的是垃圾数据。很关键的是这张表有时间戳字段且有对应索引，删除效率应该不会太低。如果直接delete，数据库的事务日志肯定会顶满，最终删除方式只能以不记数据库日志的方式进行删除，其中NOT LOGGED的属性在这个事务结束(commit)之后就不再有效。

db2 connect to ecifdb

db2 commit

db2 alter table CROSSINDEX activate not logged initially

db2 \"delete from CROSSINDEX where create_src_sys_cd = 99 and create_TM > timestamp('2016-03-01 03:00:00')\"

db2 commit

4、为了能实现删除，采取每次只删除有限时间段内的数据，以上数据根据测算，大概有500万条左右，在执行删除语句的过程中,打开db2监控视图，

db2 –d ecifdb之后按l

根据上图所示，在CPU以及磁盘IO读写均占用100%的情况下，数据吞吐量大概每秒4万条左右。粗略计算了下，删除这些数据大概需要60分钟左右，当然这只是理想状态。由于之前的数据库锁列表比例的设置，在删除过程中，仍然有交易上来访问数据库，

删除的吞吐量会大大缩小。

5、为了证实执行删除操作时，实时交易没有影响，拨打96155测试电话银行，服务确实正常。虽然是在凌晨2点之后操作，但中途仍遇到有客户在做交易，可能会将删除的动作回滚。最终，在删除了3个小时之后，数据量终于到达了1200万左右。删除过后，查看该表状态，一切正常。

6、清理了1.3亿数据后，数据表空间并没有降下来，最后再将此表进行reorg操作

db2 \"reorg table ecifmodel.crossindex\"

db2 \"reorg indexes all for table ecifmodel.crossindex\"

db2 \"runstats on table ecifmodel.crossindex\"

操作完毕后，查看表空间，明显下降。

7、再将之前删除的索引添加上，最终表恢复了正常。

总结：

1、系统关键的表记录时间戳还是很有必要的。

2、项目初期数据库的基本参数一定要根据系统定位进行适当调整，默认参数虽然好用，但不一定是最适合的。

3、之所以总结出来，主要以上步骤在生产系统上执行过，验证过，给大家做一个简单的参考。