db2資料庫下幾條有用的命令

warehouse發表於2014-06-10

折騰tsm用到了db2,簡單研究一下。
[tsminst1@udev ~]$ db2gcf -s -i tsminst1

Instance  : tsminst1
DB2 State : Available
[tsminst1@udev ~]$
--=============================

[tsminst1@udev ~]$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = TSMDB1
 Database name                        = TSMDB1
 Local database directory             = /home/tsminst1/tsminst1
 Database release level               = d.00
 Comment                              = TSM SERVER DATABASE
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
--==============================

[tsminst1@udev ~]$ db2 connect to TSMDB1

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.1
 SQL authorization ID   = TSMINST1
 Local database alias   = TSMDB1
--================================

[tsminst1@udev ~]$ db2 list tablespaces show detail

           Tablespaces for Current Database

 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 10240
 Useable pages                        = 10236
 Used pages                           = 8588
 Free pages                           = 1648
 High water mark (pages)              = 8588
 Page size (bytes)                    = 16384
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 24
 Number of containers                 = 1

 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 16384
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 192
 Number of containers                 = 1

 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 10240
 Useable pages                        = 10208
 Used pages                           = 8992
 Free pages                           = 1216
 High water mark (pages)              = 8992
 Page size (bytes)                    = 16384
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 192
 Number of containers                 = 1
 Minimum recovery time                = 2014-06-10-10.11.44.000000

 Tablespace ID                        = 3
 Name                                 = TSMTEMP
 Type                                 = System managed space
 Contents                             = User Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 16384
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 4
 Name                                 = IDXSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 12288
 Useable pages                        = 12256
 Used pages                           = 8992
 Free pages                           = 3264
 High water mark (pages)              = 8992
 Page size (bytes)                    = 8192
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 5
 Name                                 = LARGESPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1024
 Useable pages                        = 992
 Used pages                           = 416
 Free pages                           = 576
 High water mark (pages)              = 416
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Minimum recovery time                = 2014-06-10-10.11.41.000000

 Tablespace ID                        = 6
 Name                                 = LARGEIDXSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1024
 Useable pages                        = 992
 Used pages                           = 416
 Free pages                           = 576
 High water mark (pages)              = 416
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 7
 Name                                 = LGTMPTSP
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 8
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2048
 Useable pages                        = 2044
 Used pages                           = 92
 Free pages                           = 1952
 High water mark (pages)              = 92
 Page size (bytes)                    = 16384
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 24
 Number of containers                 = 1
 Minimum recovery time                = 2014-06-07-14.37.26.000000

 Tablespace ID                        = 9
 Name                                 = SYSTOOLSTMPSPACE
 Type                                 = System managed space
 Contents                             = User Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 16384
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 24
 Number of containers                 = 1

[tsminst1@udev ~]$
--===============================

[tsminst1@udev ~]$ db2 get db cfg for tsmdb1 --獲得db的配置資訊

       Database Configuration for Database tsmdb1

 Database configuration release level                    = 0x0d00
 Database release level                                  = 0x0d00

 Database territory                                      = C
 Database code page                                      = 819
 Database code set                                       = ISO8859-1
 Database country/region code                            = 1
 Database collating sequence                             = IDENTITY
 Alternate collating sequence              (ALT_COLLATE) =
 Number compatibility                                    = OFF
 Varchar2 compatibility                                  = OFF
 Date compatibility                                      = OFF
 Database page size                                      = 16384

 Dynamic SQL Query management           (DYN_QUERY_MGMT) = DISABLE

 Statement concentrator                      (STMT_CONC) = OFF

 Discovery support for this database       (DISCOVER_DB) = ENABLE

 Restrict access                                         = YES
 Default query optimization class         (DFT_QUERYOPT) = 5
 Degree of parallelism                      (DFT_DEGREE) = 1
 Continue upon arithmetic exceptions   (DFT_SQLMATHWARN) = NO
 Default refresh age                   (DFT_REFRESH_AGE) = 0
 Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
 Number of frequent values retained     (NUM_FREQVALUES) = 10
 Number of quantiles retained            (NUM_QUANTILES) = 20

 Decimal floating point rounding mode  (DECFLT_ROUNDING) = ROUND_HALF_EVEN

 Backup pending                                          = NO

 All committed transactions have been written to disk    = NO
 Rollforward pending                                     = NO
 Restore pending                                         = NO

 Multi-page file allocation enabled                      = YES

 Log retain for recovery status                          = NO
 User exit for logging status                            = YES

 Self tuning memory                    (SELF_TUNING_MEM) = ON
 Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(87520)
 Database memory threshold               (DB_MEM_THRESH) = 10
 Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(4824)
 Percent. of lock lists per application       (MAXLOCKS) = AUTOMATIC(97)
 Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(3708)
 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(2049)
 Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(409)

 Database heap (4KB)                            (DBHEAP) = AUTOMATIC(2347)
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 256
 Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 13333
 Buffer pool size (pages)                     (BUFFPAGE) = 1000
 SQL statement heap (4KB)                     (STMTHEAP) = AUTOMATIC(4096)
 Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(256)
 Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40000)
 Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(4384)

 Interval for checking deadlock (ms)         (DLCHKTIME) = 10000
 Lock timeout (sec)                        (LOCKTIMEOUT) = -1

 Changed pages threshold                (CHNGPGS_THRESH) = 80
 Number of asynchronous page cleaners   (NUM_IOCLEANERS) = AUTOMATIC(1)
 Number of I/O servers                   (NUM_IOSERVERS) = AUTOMATIC(6)
 Index sort flag                             (INDEXSORT) = YES
 Sequential detect flag                      (SEQDETECT) = YES
 Default prefetch size (pages)         (DFT_PREFETCH_SZ) = AUTOMATIC

 Track modified pages                         (TRACKMOD) = ON

 Default number of containers                            = 10
 Default tablespace extentsize (pages)   (DFT_EXTENT_SZ) = 32

 Max number of active applications            (MAXAPPLS) = AUTOMATIC(40)
 Average number of active applications       (AVG_APPLS) = AUTOMATIC(1)
 Max DB files open per application            (MAXFILOP) = 61440

 Log file size (4KB)                         (LOGFILSIZ) = 131072
 Number of primary log files                (LOGPRIMARY) = 32
 Number of secondary log files               (LOGSECOND) = 0
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /tsmdb/activelog/NODE0000/
 Overflow log path                     (OVERFLOWLOGPATH) = /tsmdb/archlog/RstDbLog/NODE0000/
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0000010.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 90
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 29

 Group commit count                          (MINCOMMIT) = 1
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF

 HADR database role                                      = STANDARD
 HADR local host name                  (HADR_LOCAL_HOST) =
 HADR local service name                (HADR_LOCAL_SVC) =
 HADR remote host name                (HADR_REMOTE_HOST) =
 HADR remote service name              (HADR_REMOTE_SVC) =
 HADR instance name of remote server  (HADR_REMOTE_INST) =
 HADR timeout value                       (HADR_TIMEOUT) = 120
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0

 First log archive method                 (LOGARCHMETH1) = DISK:/tsmdb/archlog/archmeth1/
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) = /tsmdb/archlog/failarch/
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 30
 Vendor options                              (VENDOROPT) =

 Auto restart enabled                      (AUTORESTART) = ON
 Index re-creation time and redo index build  (INDEXREC) = SYSTEM (RESTART)
 Log pages during index build            (LOGINDEXBUILD) = OFF
 Default number of loadrec sessions    (DFT_LOADREC_SES) = 1
 Number of database backups to retain   (NUM_DB_BACKUPS) = 2
 Recovery history retention (days)     (REC_HIS_RETENTN) = 0
 Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = ON

 TSM management class                    (TSM_MGMTCLASS) =
 TSM node name                            (TSM_NODENAME) =
 TSM owner                                   (TSM_OWNER) =
 TSM password                             (TSM_PASSWORD) =

 Automatic maintenance                      (AUTO_MAINT) = ON
   Automatic database backup            (AUTO_DB_BACKUP) = OFF
   Automatic table maintenance          (AUTO_TBL_MAINT) = ON
     Automatic runstats                  (AUTO_RUNSTATS) = ON
       Automatic statement statistics  (AUTO_STMT_STATS) = ON
     Automatic statistics profiling    (AUTO_STATS_PROF) = OFF
       Automatic profile updates         (AUTO_PROF_UPD) = OFF
     Automatic reorganization               (AUTO_REORG) = ON

 Auto-Revalidation                          (AUTO_REVAL) = DEFERRED
 Currently Committed                        (CUR_COMMIT) = ON
 CHAR output with DECIMAL input        (DEC_TO_CHAR_FMT) = NEW
 Enable XML Character operations        (ENABLE_XMLCHAR) = YES
 WLM Collection Interval (minutes)     (WLM_COLLECT_INT) = 0
 Monitor Collect Settings
 Request metrics                       (MON_REQ_METRICS) = BASE
 Activity metrics                      (MON_ACT_METRICS) = BASE
 Object metrics                        (MON_OBJ_METRICS) = BASE
 Unit of work events                      (MON_UOW_DATA) = NONE
 Lock timeout events                   (MON_LOCKTIMEOUT) = NONE
 Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST
 Lock wait events                         (MON_LOCKWAIT) = NONE
 Lock wait event threshold               (MON_LW_THRESH) = 5000000
 Number of package list entries         (MON_PKGLIST_SZ) = 32
 Lock event notification level         (MON_LCK_MSG_LVL) = 1

 SMTP Server                               (SMTP_SERVER) =
 SQL conditional compilation flags         (SQL_CCFLAGS) =
 Section actuals setting               (SECTION_ACTUALS) = NONE


[tsminst1@udev ~]$
--=========================

[tsminst1@udev ~]$ db2 get dbm cfg  --獲得instance的資訊,dbm相當於例項

          Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x0d00

 CPU speed (millisec/instruction)             (CPUSPEED) = 2.519169e-07
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+02

 Max number of concurrently active databases     (NUMDB) = 8
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) =

 Default charge-back account           (DFT_ACCOUNT_STR) =

 Java Development Kit installation path       (JDK_PATH) = /home/tsminst1/sqllib/java/jdk64

 Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) = /home/tsminst1/sqllib/db2dump
 Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 1024

 Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = ON
   Lock                                   (DFT_MON_LOCK) = OFF
   Sort                                   (DFT_MON_SORT) = OFF
   Statement                              (DFT_MON_STMT) = OFF
   Table                                 (DFT_MON_TABLE) = OFF
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = OFF
 Monitor health of instance and databases   (HEALTH_MON) = ON

 SYSADM group name                        (SYSADM_GROUP) = TSMSRVRS
 SYSCTRL group name                      (SYSCTRL_GROUP) =
 SYSMAINT group name                    (SYSMAINT_GROUP) =
 SYSMON group name                        (SYSMON_GROUP) =

 Client Userid-Password Plugin          (CLNT_PW_PLUGIN) =
 Client Kerberos Plugin                (CLNT_KRB_PLUGIN) =
 Group Plugin                             (GROUP_PLUGIN) =
 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) =
 Server Plugin Mode                    (SRV_PLUGIN_MODE) = UNFENCED
 Server List of GSS Plugins      (SRVCON_GSSPLUGIN_LIST) =
 Server Userid-Password Plugin        (SRVCON_PW_PLUGIN) =
 Server Connection Authentication          (SRVCON_AUTH) = NOT_SPECIFIED
 Cluster manager                           (CLUSTER_MGR) =

 Database manager authentication        (AUTHENTICATION) = SERVER
 Alternate authentication           (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trust all clients                      (TRUST_ALLCLNTS) = YES
 Trusted client authentication          (TRUST_CLNTAUTH) = CLIENT
 Bypass federated authentication            (FED_NOAUTH) = NO

 Default database path                       (DFTDBPATH) = /home/tsminst1/tsminst1

 Database monitor heap size (4KB)          (MON_HEAP_SZ) = AUTOMATIC(90)
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC(484711)
 Backup buffer default size (4KB)            (BACKBUFSZ) = 1024
 Restore buffer default size (4KB)           (RESTBUFSZ) = 1024

 Agent stack size                       (AGENT_STACK_SZ) = 1024
 Sort heap threshold (4KB)                  (SHEAPTHRES) = 0

 Directory cache support                     (DIR_CACHE) = YES

 Application support layer heap size (4KB)   (ASLHEAPSZ) = 15
 Max requester I/O block size (bytes)         (RQRIOBLK) = 32767
 Query heap size (4KB)                   (QUERY_HEAP_SZ) = 1000

 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

 Priority of agents                           (AGENTPRI) = SYSTEM
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

 Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
 Initial number of fenced processes     (NUM_INITFENCED) = 0

 Index re-creation time and redo index build  (INDEXREC) = RESTART

 Transaction manager database name         (TM_DATABASE) = 1ST_CONN
 Transaction resync interval (sec)     (RESYNC_INTERVAL) = 180

 SPM name                                     (SPM_NAME) = udev_loc
 SPM log size                          (SPM_LOG_FILE_SZ) = 256
 SPM resync agent limit                 (SPM_MAX_RESYNC) = 20
 SPM log path                             (SPM_LOG_PATH) =

 TCP/IP Service name                          (SVCENAME) =
 Discovery mode                               (DISCOVER) = SEARCH
 Discover server instance                (DISCOVER_INST) = ENABLE

 SSL server keydb file                   (SSL_SVR_KEYDB) =
 SSL server stash file                   (SSL_SVR_STASH) =
 SSL server certificate label            (SSL_SVR_LABEL) =
 SSL service name                         (SSL_SVCENAME) =
 SSL cipher specs                      (SSL_CIPHERSPECS) =
 SSL versions                             (SSL_VERSIONS) =
 SSL client keydb file                  (SSL_CLNT_KEYDB) =
 SSL client stash file                  (SSL_CLNT_STASH) =

 Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
 Enable intra-partition parallelism     (INTRA_PARALLEL) = NO

 Maximum Asynchronous TQs per query    (FEDERATED_ASYNC) = 0

 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(4096)
 No. of int. communication channels   (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
 Node connection elapse time (sec)         (CONN_ELAPSE) = 10
 Max number of node connection retries (MAX_CONNRETRIES) = 5
 Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

 db2start/db2stop timeout (min)        (START_STOP_TIME) = 10

[tsminst1@udev ~]$
--================================
如果要查對應容器,db2 list tablespace containers for 表空間id:

db2 => list tablespace containers for 2

            Tablespace Containers for Tablespace 2

 Container ID                         = 0
 Name                                 = /tsmdb/db/tsminst1/NODE0000/TSMDB1/T0000002/C0000000.LRG
 Type                                 = File


db2 =>
--===============================
如何查出一個表空間中所包含的表:
db2 => select tabschema,tabname from syscat.tables where tbspace ='USERSPACE1' with ur

TABSCHEMA                                                                                                                        TABNAME                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
TSMDB1                                                                                                                           DB_INFO                                                                                                                        
TSMDB1                                                                                                                           GLOBAL_ATTRIBUTES                                                                                                              
TSMDB1                                                                                                                           BV_MASTER_SUPERNODE                                                                                                            
TSMDB1                                                                                                                           SS_CLASSES                                                                                                                     
TSMDB1                                                                                                                           SS_POOLS                                                                                                                        
...........................                                                                                                       

  139 record(s) selected.

db2 =>

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/19602/viewspace-1180002/,如需轉載,請註明出處,否則將追究法律責任。

相關文章