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.
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
