db2中模擬ORACLE中的truncate操作
關於在DB2中模擬truncate操作,檢視空間釋放和HWM情況:
1.load/import from /dev/null of del replace into wm.test_truncate_table nonrecoverable;
2.delete from wm.test_truncate_table ;
3.alter table wm.test_truncate_table activate not logged initially with empty table;
--DB2資料庫版本
[431/app/etl/]db2level
DB21085I Instance "db2root" uses "64" bits and DB2 code release "SQL09018"
with level identifier "02090107".
Informational tokens are "DB2 v9.1.0.8", "s090823", "U823514", and Fix Pack
"8".
Product is installed at "/opt/IBM/db2/V9.1".
--db2 list tablespace show detail;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767360
Free pages = 1198688
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--建測試表,插入資料
--create table wm.test_truncate_table like syscat.tables in fn_tw6_01 ;
--insert into wm.test_truncate_table select * from syscat.tables with ur ;
--再次檢視錶空間使用
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016 --使用了672 Pages
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--使用load null清除表中資料
--db2 load from /dev/null of del replace into table_name;
--db2 tablespaces show detail ;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767552
Free pages = 1198496 --清除表中資料佔用的480Pages
High water mark (pages) = 864320 --HWM未降
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--結論load null立即釋放了表佔用的空間,但沒有修改高水位標記
--drop table wm.test_truncte_table;
--db2 list tablespaces show detail ;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767360
Free pages = 1198688 --釋放了表結構分配的空間192 Pages
High water mark (pages) = 864320 --HWM未降
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--上面一步測試說明,對於load null後drop表,表佔用的空間立即釋放了
--測試如果直接的drop表的情況
--db2 "create table wm.test_truncate_table like syscat.tables in fn_tw6_01 "
--db2 list tablespaces show detail;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767552
Free pages = 1198496
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--db2 "insert into wm.test_truncate_table select * from syscat.tables with ur "
--db2 list tablespaces show detail;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--drop table wm.test_truncate_table;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767360
Free pages = 1198688
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--直接drop掉表後空間也立即釋放了.
--下面演示delete資料後不會立即釋放表空間
--db2 "create table wm.test_truncate_table like syscat.tables in fn_tw6_01 "
--db2 list tablespaces show detail;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767552
Free pages = 1198496
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--db2 "insert into wm.test_truncate_table select * from syscat.tables "
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--db2 delete from wm.test_truncate_table ;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016 --delete後空間沒有釋放,HWM也沒有降
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
--使用alter table wm.test_truncate_table activate not logged initially with empty table;清除表資料,空間立即釋放
db2 "insert into wm.test_truncate_table select * from syscat.tables with ur "
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 768032
Free pages = 1198016
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
db2 alter table table_name activate not logged initially with empty table ;
Tablespace ID = 8
Name = FN_TW6_01
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1966080
Useable pages = 1966048
Used pages = 767552
Free pages = 1198496 --表中資料佔用的空間被釋放,HWM沒有降
High water mark (pages) = 864320
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1
DB2 V9.7可以執行;V9.1不行:
db2 "truncate table table_name
ignore delete trigger --truncate本身不會啟用觸發器,此設定可以指定在有關聯觸發器時如何處理,忽略或者配置為報錯.
reuse storage
immediate"
==收縮表空間
db2 reorgchk update statistics on table wm.test_truncate_table ;
db2 reorg table wm.test_truncate_table ;
DB2 V9.7可以執行如下;V9.1不行:
db2 "alter tablespace fn_tw6_01 reduce";
db2 "alter tablespace fn_tw6_01 lower high water mark";
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23937368/viewspace-1043169/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle中truncate和delete的區別Oracledelete
- oracle truncate table recover(oracle 如何拯救誤操作truncate的表)Oracle
- MySQL和Oracle中的delete,truncate對比MySqlOracledelete
- Oracle中truncate和delete的區別(例項）Oracledelete
- db2平臺下oracle goldengate配置支援truncate操作測試記錄DB2OracleGo
- Oracle中模擬修復資料塊損壞Oracle
- oracle中Job的操作Oracle
- 模擬一下goldengate中斷後，重新同步操作Go
- 在你的 Python 遊戲中模擬引力Python遊戲
- 使用Python中的字典模擬類Python
- 模擬標準c++中的RttiC++
- 在MongoDB中模擬Auto IncrementTXMongoDBREM
- truncate操作消除ORACLE SEG壞塊解析Oracle
- java：模擬棧操作Java
- Oracle中的LOB操作（轉）Oracle
- oracle中的檔案操作Oracle
- JavaScript中模擬實現jsonpJavaScriptJSON
- 關於 Linux 中模擬滑鼠Linux
- db2 如何模擬indobt transactions 用到的指令碼DB2指令碼
- oracle中INSERT對應db2中的LOCATE函式用法OracleDB2函式
- SQL資料庫中Truncate的用法SQL資料庫
- partition table中truncate應用
- Java 中模擬 C# 的擴充套件方法JavaC#套件
- 釋出訂閱模式---模擬Node中的EventEmitter模式MIT
- Oracle中truncate table後的資料恢復(Oracle資料恢復工具-ODU)Oracle資料恢復
- DB2中如何實現Oracle的相關功能DB2Oracle
- truncate操作巨慢
- ADB模擬手機操作
- Oracle DG環境中的管理操作Oracle
- Oracle中，一個Delete操作的流程Oracledelete
- Vivado使用技巧（21）：模擬中的Debug特性
- vue-cli中模擬資料的兩種方法Vue
- 模擬js中的call、apply和bind的實現JSAPP
- Oracle中的虛擬列索引-nosegment indexOracle索引Index
- 前端模擬使用者的複製操作前端
- 關於oracle死鎖的模擬Oracle
- Linux 中模擬多種系統負載的方法Linux負載
- Verilog-A/AMS在RF模擬中的應用