檢視例項資訊

[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.

DB2 入門命令練習（二）

檢視例項資訊

會話監控開關的狀態

開啟/關閉某個監控

復位效能監控值

返回例項級別的效能資訊

返回資料庫級別的效能資訊

返回動態SQL快取記憶體的內容

收集某個表的統計資訊

透過重構行來消除“碎片”資料並壓縮資訊，對錶進行重組。

重組所有的表

備份資料庫

恢復資料庫

相關文章