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 table recover(oracle 如何拯救誤操作truncate的表)Oracle
- truncate操作消除ORACLE SEG壞塊解析Oracle
- DB2中如何實現Oracle的相關功能DB2Oracle
- db2 如何模擬indobt transactions 用到的指令碼DB2指令碼
- SQL資料庫中Truncate的用法SQL資料庫
- Oracle中的虛擬列索引-nosegment indexOracle索引Index
- java：模擬棧操作Java
- 在MySQL中 Truncate Delect Drop 的區別MySql
- 在你的 Python 遊戲中模擬引力Python遊戲
- Golang 如何操作DB2的？GolangDB2
- 關於 Linux 中模擬滑鼠Linux
- 在MongoDB中模擬Auto IncrementTXMongoDBREM
- JavaScript中模擬實現jsonpJavaScriptJSON
- ADB模擬手機操作
- Vivado使用技巧（21）：模擬中的Debug特性
- db2中的SYSIBM.SYSDUMMY1DB2IBM
- Oracle磁帶備份模擬Oracle
- ORACLE透明加密場景模擬Oracle加密
- Oracle連線Db2OracleDB2
- oracle資料庫災難挽救應急方案之DDL誤操作恢復（truncate）Oracle資料庫
- 釋出訂閱模式---模擬Node中的EventEmitter模式MIT
- Java 中模擬 C# 的擴充套件方法JavaC#套件
- 模擬js中的call、apply和bind的實現JSAPP
- DB2在滲透中的應用DB2
- DB2中的混合XML儲存QMDB2XML
- 前端模擬使用者的複製操作前端
- 如何模擬實現node中的Events模組(通俗易懂版)
- Linux 中模擬多種系統負載的方法Linux負載
- SpringBoot中的response和request模擬單元測試Spring Boot
- vue-cli中模擬資料的兩種方法Vue
- [NOIP 2024 模擬2]陣列操作陣列
- Java開發中操作日誌的作用和模組Java
- 關於模擬 new操作符的實現
- 【DB筆試面試185】在Oracle中，如何讓普通使用者可以TRUNCATE其他使用者的表？筆試面試Oracle
- 細節解析 JavaScript 中 bind 函式的模擬實現JavaScript函式
- golang 模擬瀏覽器登入操作Golang瀏覽器
- 模擬Oracle行遷移和行連結Oracle
- Oracle通過scott使用者中的emp練習單表操作Oracle
- oracle 中的事務Oracle