DB2中,删除包含LOB字段的数据之后,如何释放LOB所占用表空间?
在db2中,某个表包括LOB字段,删除完数据之后,如果对这个表REORG的方式不对,则LOB字段所占用的空间并不能得到释放。
2. 删除完数据之后,发现DATA_OBJECT_L_SIZE和LOB_OBJECT_L_SIZE都没有变化:
参考资料:
在db2中,某个表包括LOB字段,删除完数据之后,如果对这个表REORG的方式不对,则LOB字段所占用的空间并不能得到释放。
测试:
1. 新建一个表T2,并插入数据,观察到所占空间:DATA_OBJECT_L_SIZE为512byte, LOB_OBJECT_L_SIZE为131460
db2 "create table t2(id int, address clob (32 k)) in tbs2 long in LONGTBS"
//insert some data here
db2 "SELECT substr(TABSCHEMA,1,10) as TABSCHEMA, substr(TABNAME,1,10) as TABNAME, DATA_OBJECT_L_SIZE, LOB_OBJECT_L_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME='T2' "
TABSCHEMA TABNAME DATA_OBJECT_L_SIZE LOB_OBJECT_L_SIZE
---------- ---------- -------------------- --------------------
MIAOQINGSO T2 512 131460
1 record(s) selected.
2. 删除完数据之后,发现DATA_OBJECT_L_SIZE和LOB_OBJECT_L_SIZE都没有变化:
db2 "delete from t2"
db2 "SELECT substr(TABSCHEMA,1,10) as TABSCHEMA, substr(TABNAME,1,10) as TABNAME, DATA_OBJECT_L_SIZE, LOB_OBJECT_L_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME='T2' "
TABSCHEMA TABNAME DATA_OBJECT_L_SIZE LOB_OBJECT_L_SIZE
---------- ---------- -------------------- --------------------
MIAOQINGSO T2 512 131460
1 record(s) selected.
db2 "reorg table t2"
db2 "SELECT substr(TABSCHEMA,1,10) as TABSCHEMA, substr(TABNAME,1,10) as TABNAME, DATA_OBJECT_L_SIZE, LOB_OBJECT_L_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME='T2' "
TABSCHEMA TABNAME DATA_OBJECT_L_SIZE LOB_OBJECT_L_SIZE
---------- ---------- -------------------- --------------------
MIAOQINGSO T2 256 131460
1 record(s) selected.
db2 "reorg table t2 LONGLOBDATA"
db2 "SELECT substr(TABSCHEMA,1,10) as TABSCHEMA, substr(TABNAME,1,10) as TABNAME, DATA_OBJECT_L_SIZE, LOB_OBJECT_L_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME='T2' "
TABSCHEMA TABNAME DATA_OBJECT_L_SIZE LOB_OBJECT_L_SIZE
---------- ---------- -------------------- --------------------
MIAOQINGSO T2 256 260
1 record(s) selected.
参考资料:
也有一些例外情况,reorg时不需要加上LONGLOBDATA的,参考如下
http://www-01.ibm.com/support/docview.wss?uid=swg21661779