这台L3的DB2库上周安排清理我拿着DbVisualizer一头扎进去前前后后踩了一堆坑。这个环境不是生产但属于验收前最后一道门数据是真实业务导入过的测试数据又杂又乱开发的人不敢随便动DBA又没有专职盯着。最后活儿落到了我头上三天时间用DbVisualizer把该清的清掉该整理的整理掉中间还踩了驱动不匹配、锁表超时、类型转换报错几个经典大坑。今天把整个过程和解决办法写下来给以后要碰类似环境的兄弟留个参考。说明一下背景库是DB2 LUW 11.1工具是DbVisualizer 24.x清理对象是一套业务联调遗留的测试数据涉及几十张表、上千万行垃圾数据以及一堆没人认领的临时表。这篇文章适合谁看如果你是DBA、数据运维或者经常用DbVisualizer连DB2做批量清理的开发里面每个坑都是我实际遇到过并且解决的SQL也都验证过可以直接参考。如果你只是偶尔上去跑个SELECT也能从里面避开几个会让你怀疑人生的低级问题。1. 任务背景L3这个环境到底什么情况1.1 L3环境的分层逻辑很多公司的测试环境分L0到L3L0是开发本地L1是功能测试L2是集成测试L3一般是UAT/预生产验收环境。L3的数据结构基本和生产一致但数据内容是测试期的脏数据。我这个L3库不大总容量才几百GB但对象特别乱正式表、临时导入表、开发随手建的分析表全混在一起单单SCHEMA下面就有两百多张表。清理这种库最忌讳上来就DELETE必须先把环境摸清楚。1.2 这次清理的两类目标我梳理下来需要动的基本是两块一是按业务条件删除过期数据比如两年前的订单流水、联调期间的批量导入记录二是清理垃圾对象比如名字带tmp、bak、test的临时表以及一些只有一行数据的断点残留表。前者的难点在于外键关系和锁后者的难点在于确认“这张表到底还有没有人用”。这种判断在L3尤其重要因为很多表开发自己也说不清还有没有用只能靠查询上下文和对象依赖关系去推断。1.3 为什么选DbVisualizer而不是CLP很多人会觉得清理DB2就该用命令行CLP但我这次偏选了DbVisualizer主要有几个原因第一它把数据库树形结构展示得很清楚表和视图、存储过程一眼就能全貌第二SQL Commander里写多语句脚本方便结果集也能直接排序筛选不用来回命令回显第三这个项目除了DB2还要同时连Oracle和MySQL做数据比对一个工具统一入口省得来回切换。实际使用下来轻量度和响应速度都够用唯一要注意的是驱动配置必须提前弄好否则连接那一步就能卡半天。2. 连接阶段就踩的坑驱动和会话配置2.1 JDBC驱动不匹配导致连不上库这是我这次踩的第一个坑而且是最没技术含量但最耗时间的一个。DbVisualizer默认自带的DB2驱动模板对应的是比较早的db2jcc版本直接连DB2 11.1虽然能弹连接窗口但真正建立连接时Log窗口抛了一串异常说什么ClassNotFoundException后面跟的类名是com.ibm.db2.jcc.DB2Driver。原因很简单DbVisualizer的驱动管理器里选的是内置IBM DB2 (JCC)驱动但JAR包太旧。解决办法是去下载新的DB2 Data Server Driver包解压后找到db2jcc4.jar和db2jcc_license_cu.jar在DbVisualizer的Tools - Driver Manager里编辑IBM DB2 (JCC)驱动把旧JAR移除添加这两个文件。版本上建议找跟目标库小版本接近的驱动太老的驱动对11.x的有些新特性支持不全。注意如果你是在官网下载驱动包时选错平台包里面是找不到db2jcc4.jar的。认准“Data Server Driver”类型的包里面才有JDBC驱动文件。2.2 连接URL与Schema的两处关键配置连上之后还有两个配置细节容易忽略URL里面要不要指定database名打开连接后默认Schema是什么。DbVisualizer的DB2连接URL格式一般是jdbc:db2://host:port/dbname这个不会出问题。但连接属性里有个currentSchema默认是空DbVisualizer会根据登录用户推断一个Schema。结果就是你在SQL Commander里写SELECT * FROM T_ORDERDbVisualizer自动去匹配当前用户同名的Schema如果实际数据在另一个Schema下面就直接报SQL0204N提示T_ORDER是个未定义名称。很多人第一次遇到以为是表不存在其实换个Schema就能查到。我在连接属性里把currentSchema显式配成了L3Schema所有脚本就都正常了。2.3 自动提交开关对清理操作的影响DbVisualizer的SQL Commander默认是开启Auto Commit的工具栏上那个Auto Commit按钮默认是亮的。这对于日常查询没影响但对于批量DELETE就有大问题。我在清理一张千万级的分区表时因为自动提交开着每一批DELETE都立即提交事务日志疯狂增长跑到一半直接报SQL0964C日志满了。后来我把Auto Commit关掉改成手动提交每删完一批执行一次Commit日志压力一下就降下来了。你可能会问DbVisualizer里关掉自动提交后SQL窗口下方的Commit按钮是怎么用的其实就是把当前手工开启的事务做提交。这个细节清楚了批量删除才有操作空间。3. 清理操作的完整实操过程3.1 先用系统视图摸清各表体量清理的第一步不是删数据而是找出哪些表值得删。我通过连接树找到L3Schema然后直接在SQL Commander里跑了一个查系统目录的语句SELECT TABNAME, CARD AS ROW_COUNT, NPAGES AS PAGE_COUNT, TBSPACE, TYPE FROM SYSCAT.TABLES WHERE TABSCHEMA L3SCHEMA ORDER BY CARD DESC;SYSCAT.TABLES里面的CARD字段是表的统计行数NPAGES是大致的数据页数。如果表没有做过RUNSTATSCARD可能不准或者为零但对摸底已经足够。我拿这个结果把行数排名前二十的表全列了出来再用表空间大小一对照基本就能锁定清理目标。另外我还会加一个过滤条件把TABSCHEMA里的临时表捞出来名字带TMP、BAK、TEST的直接列入待清理清单。要注意的是DbVisualizer的查询结果默认只显示前500行如果表特别多可以顺手把Max Rows调到2000。3.2 理清外键关系再动手删除有几张表明显有主外键关系比如订单主表和订单明细表。直接先删主表肯定会报外键约束错误所以删除顺序必须从子表到主表。要拿到依赖关系我查了SYSCAT.REFERENCESSELECT TABNAME, REFTABNAME, CONSTNAME, DELETE_RULE FROM SYSCAT.REFERENCES WHERE TABSCHEMA L3SCHEMA ORDER BY REFTABNAME, TABNAME;这个结果会告诉我每张表的外键引用自哪张表。我整理完发现L3Schema里有五十多个外键关系整体呈现一个比较浅的树形结构。这一步做完我才开始写删除脚本先清理叶子节点表再清理父表基本没有遇到过外键报错。如果项目里外键特别深建议直接把全链路依赖导出成临时表按依赖深度分批跑。3.3 大批量删除用分批方案而不是一条DELETE如果要删除的表数据量比较大比如超过几十万行一条DELETE跑到底存在几个问题事务日志容易撑爆锁持有时间太长容易和其他会话冲突一旦某个语句执行到一半报错整个事务都可能回滚白跑很长时间。我的做法是分批删除。DB2下我常用这种写法DELETE FROM L3SCHEMA.T_ORDER_DETAIL WHERE ORDER_ID IN ( SELECT ORDER_ID FROM ( SELECT ORDER_ID, ROW_NUMBER() OVER(ORDER BY ORDER_ID) AS RN FROM L3SCHEMA.T_ORDER_DETAIL WHERE ORDER_DATE 2022-01-01 ) T WHERE T.RN 2000 );每一批删2000行然后手动提交一次再继续跑直到影响行数为0。为什么是2000因为一次事务里的操作量刚好不大不小日志压力小而且就算出了问题回滚成本也可控。你可以在DbVisualizer里把这段SQL单独选中执行看到“2000 rows affected”之后点Commit然后F5换一批。有的DBA习惯用存储过程做循环但L3这种环境我建议先用这种半手动的方式至少你能随时停下来检查数据状态不会一头扎进无人驾驶状态。3.4 能TRUNCATE的前提和外键限制对于彻底不要的临时表我不想用DELETE逐行清直接TRUNCATE更快。但TRUNCATE在DB2里有几个限制最典型的是如果有外键引用这张表无论当前表是否作为父表还是子表TRUNCATE都不会让你直接执行会给你一个约束错误。另外TRUNCATE不能按条件删它只清空整表。所以我在方案里只对两类表用了TRUNCATE一是没有外键关系的孤立临时表二是确认所有数据都不需要的备份中间表。执行完TRUNCATE之后表空间里的存储空间有没有释放取决于版本和表的组织方式在DB2 11.1中如果建表时开了REUSE那么空出来的空间会被后续数据复用但不会立刻还给操作系统。3.5 锁等待和死锁的排查办法清理过程中有一张业务表特别邪门DELETE跑了不到两分钟就弹SQL0911N提示锁超时或者死锁。刚开始我以为是自己会话的问题后来用DbVisualizer连接树里看会话发现有一台应用服务器残留的旧连接还挂在同一批数据上一直没提交。排查锁等待我在DbVisualizer里跑了一个查询SELECT SUBSTR(APPLICATION_ID,1,30) AS APP_ID, AGENT_ID, LOCK_NAME, LOCK_MODE, TABNAME FROM SYSIBMADM.SNAPLOCK;SYSIBMADM.SNAPLOCK能看到当前数据库实例中的锁信息。APP_ID里包含主机名和连接号一看就能知道是哪个源头发起的连接。找到旧连接之后我用DbVisualizer的连接树找到对应的Session右键Terminate把它断开DELETE立刻就通畅了。这里有个经验清理窗口期越小越好最好能提前和业务方确认没有其他人在用这个库不然你前脚清锁后脚新会话又会把锁堵上。4. 高频翻车点数字字符串的类型转换4.1 清理脚本为什么会碰上“判断数字字符串”的需求这次清理里有个很典型的场景T_ORDER表的ORDER_NO字段是VARCHAR(32)理论上全是数字流水号但早期导入程序写过非数字的异常单号进去。清理需求是“删除所有小于某数值范围的历史单号”于是有人顺手写成DELETE FROM L3SCHEMA.T_ORDER WHERE CAST(ORDER_NO AS INTEGER) 100000;一执行就报SQL0408N类型转换错误。原因是ORDER_NO里面有几个非数字的值CAST直接失败。这种需求在数据清洗场景里太常见了你想把一个存数字的字符串字段跟数值比较但内存里混了字母、符号和空格。关键不是写不写CAST而是得在CAST之前先安全地把非数字字符串筛掉。4.2 用TRANSLATE函数判断纯数字字符串我在DB2里最常用的安全判断是用TRANSLATE函数。这个函数的用途类似字符替换把字符串里指定字符集的字符替换成另一个字符。利用它可以把数字字符全部“抹掉”如果抹完剩下的内容为空说明原字符串只有数字。具体SQL是这样SELECT ORDER_NO, CASE WHEN ORDER_NO IS NULL THEN 0 WHEN RTRIM(ORDER_NO) THEN 0 WHEN TRANSLATE(RTRIM(ORDER_NO), , 0123456789) THEN 1 ELSE 0 END AS IS_NUMERIC FROM L3SCHEMA.T_ORDER;这里有个细节DB2的CHAR类型列向右补空格所以必须先RTRIM再判断否则TRANSLATE把数字抹掉之后剩下的全是空格判断结果就会出错。REGEXP_LIKE也可以做到同样效果写法是REGEXP_LIKE(RTRIM(ORDER_NO), ^[0-9]$)DB2 LUW 11.1已经内置支持。但TRANSLATE的写法不依赖正则扩展兼容性更好我最后在删除脚本里直接用了TRANSLATE配合CASE先查出所有数字型单号再执行删除。4.3 空格、NULL和边界情况的处理顺序写数字字符串判断的时候条件顺序挺重要。如果你先写TRANSLATE判断再处理NULL那么NULL传入函数之后会返回NULLNULL 的判断结果又是UNKNOWNCASE往往处理不到你想要的分支。所以我的习惯是先把NULL单独列出来再处理空字符串再去判断全数字。否则你用WHERE过滤非数字时NULL行会被漏掉后面删除或更新时又因NULL产生别的麻烦。还有一个细节有些字段左侧也带空格比如既有右填充又有左填充那最好用LTRIM(RTRIM(...))双保险。清洗类脚本稍微多写两步后面就能少加一次班。4.4 DbVisualizer里执行多语句脚本的分隔符问题这个坑和工具强相关。DbVisualizer的SQL Commander默认按分号切分脚本。你写一个包含CASE WHEN的SELECT没问题但如果你在脚本命令里嵌了一个完整的存储过程或者匿名块过程体里的分号会被DbVisualizer误识别成语句结束符结果就是报语法错误。解决方式有两种一种是选中要执行的那段SQL用“Execute Selected”而不是“Execute All”这样DbVisualizer只把选中内容作为整体发送给DB2执行另一种是改Statement Delimiter为别的符号比如在SQL Commander工具栏的Delimiter框输入。我实际用下来执行清理动作时选中执行最方便也最不容易把无关语句一起跑出去。5. 常见问题速查与清理后的收尾工作5.1 清理过程中常见的错误码速查表我在这次任务里整理了一张常见错误码对照表基本都是DB2清理时会遇到的老面孔以后遇到类似报错可以对着查。错误码/报错典型原因快速解决办法SQL0204N表或视图不存在多半是Schema不对检查连接属性里的currentSchemaSQL0408N类型转换失败如CAST VARCHAR为非数字用TRANSLATE或REGEXP_LIKE先过滤SQL0911N锁超时或死锁事务被回滚查询SYSIBMADM.SNAPLOCK断开冲突会话SQL0964C事务日志满提交频率太低或事务太大关掉自动提交改成多批提交或扩大日志配置SQL0286N表空间满先做REORG回收空间或清理临时数据SQL0104N语法错误特别是多语句脚本分隔符问题检查DbVisualizer的Delimiter设置5.2 DbVisualizer执行脚本时最容易忽略的三个操作细节第一个是选中执行和全部执行要分清。清理期间我在SQL Commander里同时放着查询语句和删除语句如果不小心点了执行全部可能把不该删的也删了。所以我养成了一个习惯先选中目标SQL再点Execute Selected。第二个是查询结果里查出来的行数不能完全当作实际行数因为DbVisualizer默认强制结果集只显示500行。如果你跑SELECT * FROM x看着只有500行以为表很小其实表里可能有几十万行。第三个是连接树里的Session面板这个功能平时不怎么用但排查锁等待时特别有价值可以在树形界面上直接看到活动的连接及其状态。5.3 清理完别急着走RUNSTATS和REORG必须做大表删了几十万行之后统计信息还是旧数据优化器可能基于错误的统计信息选择极差的执行计划。所以清理完数据我第一件事是重建统计信息。DB2下可以用ADMIN_CMD调RUNSTATSCALL SYSPROC.ADMIN_CMD( RUNSTATS ON TABLE L3SCHEMA.T_ORDER WITH DISTRIBUTION AND DETAILED INDEXES ALL );这样执行完SYSCAT.TABLES里的CARD信息就会更新。对于高水位线很高的表我还会做一次REORGCALL SYSPROC.ADMIN_CMD(REORG TABLE L3SCHEMA.T_ORDER);REORG能把表的物理存储重新整理一遍尤其是删除大量数据之后数据页上的空洞能回收掉。DbVisualizer的SQL Commander直接调用CALL没有障碍跟CLP里执行效果一致。5.4 清理动作之前的备份和演练习惯最后再说说清理前必做的两件事。第一件是把所有要动的表结构导出保存DDL。DbVisualizer的数据库树里右键选表可以把CREATE语句复制出来一份完整的DDL存档成本很低但万一误删结构恢复时能省太多事。第二件是先在事务里做演练关闭自动提交执行DELETE确认要删除的行数符合预期然后选择Rollback回滚数据不变再正式执行并Commit。我自己这次清理就是先演练了两张表把删除条数和预期对上之后才放宽到全量。别觉得这一步浪费时间L3环境经常有测试数据被业务方临时更新过你以为要删一万行实际可能已经变成十万行先演练能避免很多不必要的事故。这次清理下来我最深的体会是用DbVisualizer连DB2做批量清理大部分时间不是在跟SQL语法较劲而是在跟驱动版本、连接配置、锁会话、事务日志这些“环境因素”打交道。工具本身很成熟但DB2的运维习惯和工具默认配置之间确实存在几个容易忽略的磨合点。尤其是数字字符串的判断这个不起眼的小函数被我列到这次最有价值的排查点里因为类似问题在未来清洗场景里一定会反复出现。如果你马上也要在DbVisualizer里对DB2动手建议先把驱动版本和Schema配置确认好再在正式执行前跑一段小数据量验证脚本。剩下的坑大部分都能通过一边查系统视图一边解决。