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.