db2資料庫下幾條有用的命令
折騰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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle幾個有用的命令Oracle
- 幾條Oracle資料庫開發的原則歸納（下）Oracle資料庫
- DB2資料庫常用命令集：部分資料庫維護命令DB2資料庫
- 教你快速掌握DB2資料庫中的相關命令DB2資料庫
- rman 刪除日誌的幾有用的命令
- 誰知道TORQUE在DB2資料庫下的配置DB2資料庫
- MySql資料庫最佳化的幾條核心建議MySql資料庫
- db2 資料庫DB2資料庫
- 資料庫系統常用的幾個工具和命令資料庫
- 幾條Oracle資料庫開發的原則歸納（上）Oracle資料庫
- DB2 資料庫中的資料型別DB2資料庫資料型別
- DB2頁清除的幾個觸發條件DB2
- 有用的資料
- 有用的命令
- 記下改變資料庫密碼的命令資料庫密碼
- DB2資料庫中的各資料型別DB2資料庫資料型別
- 定製sql*plus的幾條命令SQL
- 淺談DB2資料庫的備份與恢復(下) (轉)DB2資料庫
- DB2資料庫的解除安裝DB2資料庫
- db2 建立資料庫與資料放置DB2資料庫
- DB2 資料庫日常管理DB2資料庫
- VB下幾個非常有用的函式 (轉)函式
- unix下幾個有用的小shell指令碼(轉)指令碼
- Linux下DB2資料庫安裝過程詳解LinuxDB2資料庫
- DB2中安裝sample資料庫和TOLLSDB資料庫DB2資料庫
- DB2建立聯邦資料庫DB2資料庫
- [DB2]資料庫建立實驗DB2資料庫
- DB2查詢資料庫大小DB2資料庫
- DB2資料庫配置問題？？DB2資料庫
- 24個有用的PHP類庫分享（下）PHP
- db2move和幾個常用的db2命令DB2
- 在 Linux 下 9 個有用的 touch 命令示例Linux
- RPM中幾個生闢但有用的命令引數(轉)
- 有用的幾個網站網站
- 幾個有用的Function.Function
- 常見資料庫系統之比較 - DB2資料庫(轉)資料庫DB2
- db2資料庫的啟動和關閉DB2資料庫
- DB2的資料庫備份與恢復DB2資料庫