DB2 入門命令練習（二）
檢視例項資訊
[db2inst1@xttdb ~]$ db2ilist
db2inst1
db2 => connect to test02
Database Connection Information
Database server = DB2/LINUX 10.1.0
SQL authorization ID = DB2INST1
Local database alias = TEST02
會話監控開關的狀態
檢視狀態
db2 => get monitor switches
Monitor Recording Switches
Switch list for member 0
Buffer Pool Activity Information (BUFFERPOOL) = OFF
Lock Information (LOCK) = OFF
Sorting Information (SORT) = OFF
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 01/21/2015 22:40:24.937611
Unit of Work Information (UOW) = OFF
開啟/關閉某個監控
db2 => update monitor switches using statement on
DB20000I The UPDATE MONITOR SWITCHES command completed successfully.
db2 => get monitor switches
Monitor Recording Switches
Switch list for member 0
Buffer Pool Activity Information (BUFFERPOOL) = OFF
Lock Information (LOCK) = OFF
Sorting Information (SORT) = OFF
SQL Statement Information (STATEMENT) = ON 01/21/2015 23:17:30.465078
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 01/21/2015 22:40:24.937611
Unit of Work Information (UOW) = OFF
復位效能監控值
db2 => reset monitor all
DB20000I The RESET MONITOR command completed successfully.
返回例項級別的效能資訊
db2 => get snapshot for dbm
Database Manager Snapshot
Node type = Database Server with local and remote clients
Instance name = db2inst1
Number of members in DB2 instance = 1
Database manager status = Active
Product name = DB2 v10.1.0.0
Service level = s120403 (LINUXIA32101)
Private Sort heap allocated = 0
Private Sort heap high water mark = 0
Post threshold sorts = Not Collected
Piped sorts requested = 0
Piped sorts accepted = 0
Start Database Manager timestamp = 01/21/2015 22:40:24.937611
Last reset timestamp = 01/21/2015 23:21:46.586908
Snapshot timestamp = 01/21/2015 23:23:47.709914
.
.
.
返回資料庫級別的效能資訊
db2 => get snapshot for all on test02
.
.
.
Database Lock Snapshot
Database name = TEST02
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00002/MEMBER0000/
Input database alias = TEST02
Locks held = 0
Applications currently connected = 1
Agents currently waiting on locks = 0
Snapshot timestamp = 01/21/2015 23:26:28.233044
Application handle = 52
Application ID = *LOCAL.DB2.150121151445
Sequence number = 00001
Application name = db2fw0
.
.
Table Snapshot
First database connect timestamp = 01/21/2015 23:14:24.260893
Last reset timestamp = 01/21/2015 23:21:46.586908
Snapshot timestamp = 01/21/2015 23:26:28.233044
Database name = TEST02
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00002/MEMBER0000/
Input database alias = TEST02
Number of accessed tables = 0
返回動態SQL快取記憶體的內容
db2 => get snapshot for dynamic sql on test02
Dynamic SQL Snapshot Result
Database name = TEST02
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00002/MEMBER0000/
Number of executions = 1
Number of compilations = 1
Worst preparation time (ms) = 470
Best preparation time (ms) = 470
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 5
Internal rows updated = 0
Rows written = 0
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool xda logical reads = Not Collected
Buffer pool xda physical reads = Not Collected
Buffer pool temporary xda logical reads = Not Collected
Buffer pool temporary xda physical reads = Not Collected
.
.
.
收集某個表的統計資訊
db2 => runstats on table db2inst1.test01
DB20000I The RUNSTATS command completed successfully.
透過重構行來消除“碎片”資料並壓縮資訊，對錶進行重組。
db2 => reorg table db2inst1.test01
DB20000I The REORG command completed successfully.
重組所有的表
db2 => reorgchk on table all
.
.
.
Table: SYSIBM.SYSXDBMAPGRAPHS
Index: SYSIBM.INDXDBMAPGRAPHS01
0 1 0 1 0 0 16 16 442 442 0 100 - - 0 0 -----
Table: SYSIBM.SYSXDBMAPSHREDTREES
Index: SYSIBM.INDXDBMAPSHREDTREES01
0 1 0 1 0 0 16 16 442 442 0 100 - - 0 0 -----
Table: SYSIBM.SYSXMLPATHS
Index: SYSIBM.INDXMLPATHS01
0 1 0 1 0 0 4 4 822 822 0 100 - - 0 0 -----
Index: SYSIBM.INDXMLPATHS02
0 1 0 1 0 0 3 3 896 896 0 100 - - 0 0 -----
.
.
.
檢視資料庫當前配置的延遲值和當前值
db2 => get db cfg show detail
Database Configuration for Database
Description Parameter Current Value Delayed Value
---------------------------------------------------------------------------------------------------------------
Database configuration release level = 0x0f00
Database release level = 0x0f00
Database territory = CN
Database code page = 1208
Database code set = utf-8
Database country/region code = 86
Database collating sequence = IDENTITY
.
.
.
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF OFF
Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 20
Vendor options (VENDOROPT) =
Auto restart enabled (AUTORESTART) = ON ON
.
.
.
備份資料庫
db2 => backup database test02 to "/ibm/db2/backup"
Backup successful. The timestamp for this backup image is : 20150122000011
恢復資料庫
db2 => restore database test02 from "/ibm/db2/backup"
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29320885/viewspace-1411791/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DB2 入門命令練習（一）DB2
- 入門練習
- db2入門學習DB2
- JAVA入門第二季4-2練習Java
- Docker 入門學習筆記二：常用命令Docker筆記
- sql查詢入門練習題SQL
- CAD入門級練習題
- 入門MySQL——查詢語法練習MySql
- 英語口語練習--職業入門篇
- Docker入門系列(二)--docker操作命令Docker
- Java入門第二季 6-1綜合練習程式碼Java
- Find命令練習
- ReactJS入門學習二ReactJS
- 深度學習入門實戰（二）- 用 TensorFlow 訓練線性迴歸深度學習
- 慕課網Python入門練習題---Python
- mysql 基礎命令入門學習MySql
- MYSQL練習二MySql
- db2 入門常用操作DB2
- HTML入門學習筆記（二）HTML筆記
- webpack入門學習手記（二）Web
- Hive入門學習隨筆（二）Hive
- gRPC入門學習之旅（二）RPC
- [MySQL光速入門]008 SQL強化練習MySql
- 遊戲關卡設計如何入門和練習？遊戲
- 編譯器背後的故事（入門練習）編譯
- C語言入門級練習（敲程式碼）C語言
- [MySQL光速入門]009 SQL強化練習答案MySql
- java入門第三季6-5練習Java
- Oracle入門查詢練習題及參考答案Oracle
- 21 個 curl 命令練習
- Redis 命令練習彙總Redis
- Pytorch入門演練PyTorch
- Redis入門及常用命令學習Redis
- 目標檢測入門系列手冊二：RCNN訓練教程CNN
- find 命令入門
- webpack4入門學習筆記(二)Web筆記
- Egg入門學習(二)---理解service作用
- Linux入門（二） ~ Linux的常用命令Linux