状況情報を収集してユーザーの出力形式を設定します。 戻された情報は、コマンドが発行されたときのデータベース・マネージャーの操作状況のスナップショット です。
効力範囲
区分データベース環境では、 このコマンドは db2nodes.cfg ファイルのどのノードからでも呼び出せます。 このコマンドはそのノードまたは区分上でのみ実行します。
許可
以下のいずれかになります。
必須接続
インスタンス。 インスタンスの接続がない場合は、 省略時のインスタンス接続が作成されます。
リモート・インスタンスのスナップショットを取得するには、 まずそのインスタンスに接続する必要があります。
>>-GET SNAPSHOT FOR---------------------------------------------> >-----+-+-DATABASE MANAGER-+---------------------------------------+> | +-DB MANAGER-------+ | | '-DBM--------------' | +-ALL--+-----+--DATABASES------------------------------------+ | '-DCS-' | +-ALL--+-----+--APPLICATIONS---------------------------------+ | '-DCS-' | +-ALL BUFFERPOOLS--------------------------------------------+ +-+-----+--APPLICATION----+-APPLID--appl-id-------+----------+ | '-DCS-' '-AGENTID--appl-handle--' | +-FCM FOR ALL NODES------------------------------------------+ +-LOCKS FOR APPLICATION--+-APPLID--appl-id-------+-----------+ | '-AGENTID--appl-handle--' | +-ALL REMOTE_DATABASES---------------------------------------+ +-ALL REMOTE_APPLICATIONS------------------------------------+ '--+-ALL-----------------------------+---ON--database-alias--' +-+-----+--+-DATABASE-+-----------+ | '-DCS-' '-DB-------' | +-+-----+--APPLICATIONS-----------+ | '-DCS-' | +-TABLES--------------------------+ +-TABLESPACES---------------------+ +-LOCKS---------------------------+ +-BUFFERPOOLS---------------------+ +-REMOTE_DATABASES----------------+ +-REMOTE_APPLICATIONS-------------+ '-DYNAMIC SQL--+----------------+-' '-WRITE TO FILE--' >-----+-------------------+------------------------------------>< +-AT NODE--nodenum--+ '-GLOBAL------------'
注: | 統計を取得するためには、 モニター・スイッチをオンにする必要があります (UPDATE MONITOR SWITCHESを参照してください)。 |
コマンド・パラメーター
例
以下の出力リストのサンプルでは、 適切なデータベース・システム・モニターの記録スイッチが オンになっているかどうかによって、一部の情報が得られないことがあります (UPDATE MONITOR SWITCHESを参照してください)。 情報が得られない場合、 Not Collected というメッセージが出力に表示されます。
以下は、データベース・マネージャー情報の要求によって得られる典型的な出力です。
Database Manager Snapshot Node type = Database Server with local clients Instance name = smith Number of nodes in DB2 instance = 0 Database manager status = Active Product name = Product identification = Service level = Sort heap allocated = 0 Post threshold sorts = 0 Piped sorts requested = 0 Piped sorts accepted = 0 Start Database Manager timestamp = 02-25-1999 13:26:53.126518 Last reset timestamp = Snapshot timestamp = 02-25-1999 13:45:42.257720 Remote connections to db manager = 0 Remote connections executing in db manager = 0 Local connections = 1 Local connections executing in db manager = 0 Active local databases = 1 High water mark for agents registered = 3 High water mark for agents waiting for a token = 0 Agents registered = 3 Agents waiting for a token = 0 Idle agents = 1 Committed private Memory (Bytes) = 3670016 Buffer Pool Activity Information (BUFFERPOOL) = ON 02-25-1999 13:32:14 Lock Information (LOCK) = ON 02-25-1999 13:32:40 Sorting Information (SORT) = ON 02-25-1999 13:32:40 SQL Statement Information (STATEMENT) = ON 02-25-1999 13:32:14 Table Activity Information (TABLE) = ON 02-25-1999 13:32:40 Unit of Work Information (UOW) = ON 02-25-1999 13:32:14 Agents assigned from pool = 2 Agents created from empty pool = 3 Agents stolen from another application = 0 High water mark for coordinating agents = 3 Max agents overflow = 0 Hash joins after heap threshold exceeded = 0 Total number of gateway connections = 0 Current number of gateway connections = 0 Gateway connections waiting for host reply = 0 Gateway connections waiting for client reply = 0 Gateway inactive connection pool agents = 0 Gateway connection pool agents stolen = 0
以下は、データベース情報の要求によって得られる典型的な出力です。
Database Snapshot Database name = SAMPLE Database path = /home/smith/smith/NODE0000/SQL00001/ Input database alias = Database status = Active Catalog node number = 0 Catalog network node name = Operating system running at database server= AIX Location of the database = Local First database connect timestamp = 02-25-1999 13:31:33.886214 Last reset timestamp = Last backup timestamp = Snapshot timestamp = 02-25-1999 13:40:08.337902 High water mark for connections = 1 Application connects = 1 Secondary connects total = 0 Applications connected currently = 1 Appls. executing in db manager currently = 0 Agents associated with applications = 1 Maximum agents associated with applications= 1 Maximum coordinating agents = 1 Locks held currently = 1 Lock waits = 0 Time database waited on locks (ms) = 0 Lock list memory in use (Bytes) = 432 Deadlocks detected = 0 Lock escalations = 0 Exclusive lock escalations = 0 Agents currently waiting on locks = 0 Lock Timeouts = 0 Total sort heap allocated = 0 Total sorts = 0 Total sort time (ms) = 0 Sort overflows = 0 Active sorts = 0 High water mark for database heap = 316084 Buffer pool data logical reads = 1 Buffer pool data physical reads = 0 Asynchronous pool data page reads = 0 Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index logical reads = 0 Buffer pool index physical reads = 0 Asynchronous pool index page reads = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 Total buffer pool read time (ms) = 0 Total buffer pool write time (ms) = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 LSN Gap cleaner triggers = 0 Dirty page steal cleaner triggers = 0 Dirty page threshold cleaner triggers = 0 Time waited for prefetch (ms) = 0 Direct reads = 0 Direct writes = 0 Direct read requests = 0 Direct write requests = 0 Direct reads elapsed time (ms) = 0 Direct write elapsed time (ms) = 0 Database files closed = 0 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Commit statements attempted = 2 Rollback statements attempted = 0 Dynamic statements attempted = 10 Static statements attempted = 2 Failed statement operations = 0 Select SQL statements executed = 2 Update/Insert/Delete statements executed = 0 DDL statements executed = 0 Internal automatic rebinds = 0 Internal rows deleted = 0 Internal rows inserted = 0 Internal rows updated = 0 Internal commits = 1 Internal rollbacks = 0 Internal rollbacks due to deadlock = 0 Rows deleted = 0 Rows inserted = 0 Rows updated = 0 Rows selected = 16 Binds/precompiles attempted = 0 Log space available to the database (Bytes)= 0 Log space used by the database (Bytes) = 0 Maximum secondary log space used (Bytes) = 0 Maximum total log space used (Bytes) = 0 Secondary logs allocated currently = 0 Log pages read = 0 Log pages written = 0 Appl id holding the oldest transaction = 0 Package cache lookups = 2 Package cache inserts = 1 Package cache overflows = 0 Package cache high water mark (Bytes) = 108757 Application section lookups = 10 Application section inserts = 1 Catalog cache lookups = 1 Catalog cache inserts = 1 Catalog cache overflows = 0 Catalog cache heap full = 0 Number of hash joins = 0 Number of hash loops = 0 Number of hash join overflows = 0 Number of small hash join overflows = 0
以下は、DCS データベース情報の要求によって得られる典型的な出力です。
DCS Database Snapshot DCS database name = DCSDB Host database name = GILROY First database connect timestamp = 02-25-1999 17:00:05.003421 Most recent elapsed time to connect = 0.001200 Most recent elapsed connection duration = 3.443780 Host response time (sec.ms) = 0.000320 Last reset timestamp = Number of SQL statements attempted = 12 Commit statements attempted = 6 Rollback statements attempted = 2 Failed statement operations = 4 Total number of gateway connections = 0 Current number of gateway connections = 1 Gateway conn. waiting for host reply = 0 Gateway conn. waiting for client reply = 1 Gateway communication errors to host = 0 Timestamp of last communication error = None High water mark for gateway connections = 1 Rows selected = 0 Outbound bytes sent = 0 Outbound bytes received = 0
以下は、アプリケーション情報の要求によって得られる典型的な出力です (アプリケーション ID、アプリケーション・ハンドル、すべてのアプリケーション、 またはデータベースのすべてのアプリケーションのいずれかを指定)。
Application Snapshot Application handle = 3 Application status = UOW Waiting Status change time = 02-25-1999 13:33:41.446676 Application code page = 819 Application country code = 1 DUOW correlation token = *LOCAL.smith.990225183133 Application name = db2bp Application ID = *LOCAL.smith.990225183133 Sequence number = 0001 Connection request start timestamp = 02-25-1999 13:31:33.886214 Connect request completion timestamp = 02-25-1999 13:31:34.434114 Application idle time = 6 minutes and 42 seconds Authorization ID = SMITH Client login ID = smith Configuration NNAME of client = Client database manager product ID = SQL06000 Process ID of client application = 27918 Platform of client application = AIX Communication protocol of client = Local Client Outbound communication address = Outbound communication protocol = APPC Inbound communication address = Database name = SAMPLE Database path = /home/smith/smith/ NODE0000/SQL00001/ Client database alias = sample Input database alias = Last reset timestamp = Snapshot timestamp = 02-25-1999 13:40:23.773540 The highest authority level granted = Direct DBADM authority Direct CREATETAB authority Direct BINDADD authority Direct CONNECT authority Direct CREATE_NOT_FENC authority Direct IMPLICIT_SCHEMA authority Direct LOAD authority Indirect SYSADM authority Indirect CREATETAB authority Indirect BINDADD authority Indirect CONNECT authority Indirect IMPLICIT_SCHEMA authority Indirect LOAD authority Coordinating node number = 0 Current node number = 0 Coordinator agent process or thread ID = 26160 Agents stolen = 0 Agents waiting on locks = 0 Maximum associated agents = 1 Priority at which application agents work = 0 Priority type = Dynamic Locks held by application = 1 Lock waits since connect = 0 Time application waited on locks (ms) = 0 Deadlocks detected = 0 Lock escalations = 0 Exclusive lock escalations = 0 Number of Lock Timeouts since connected = 0 Total time UOW waited on locks (ms) = 0 Total sorts = 0 Total sort time (ms) = 0 Total sort overflows = 0 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Buffer pool data logical reads = 1 Buffer pool data physical reads = 0 Buffer pool data writes = 0 Buffer pool index logical reads = 0 Buffer pool index physical reads = 0 Buffer pool index writes = 0 Total buffer pool read time (ms) = 0 Total buffer pool write time (ms) = 0 Time waited for prefetch (ms) = 0 Direct reads = 0 Direct writes = 0 Direct read requests = 0 Direct write requests = 0 Direct reads elapsed time (ms) = 0 Direct write elapsed time (ms) = 0 Number of SQL requests since last commit = 5 Commit statements = 2 Rollback statements = 0 Dynamic SQL statements attempted = 10 Static SQL statements attempted = 2 Failed statement operations = 0 Select SQL statements executed = 2 Update/Insert/Delete statements executed = 0 DDL statements executed = 0 Internal automatic rebinds = 0 Internal rows deleted = 0 Internal rows inserted = 0 Internal rows updated = 0 Internal commits = 1 Internal rollbacks = 0 Internal rollbacks due to deadlock = 0 Binds/precompiles attempted = 0 Rows deleted = 0 Rows inserted = 0 Rows updated = 0 Rows selected = 16 Rows read = 25 Rows written = 0 UOW log space used (Bytes) = 0 Previous UOW completion timestamp = 02-25-1999 13:31:34.434114 Elapsed time of last completed uow (sec.ms)= 0.919533380 UOW start timestamp = 02-25-1999 13:33:41.392167 UOW stop timestamp = UOW completion status = Open remote cursors = 0 Open remote cursors with blocking = 0 Rejected Block Remote Cursor requests = 0 Accepted Block Remote Cursor requests = 2 Open local cursors = 0 Open local cursors with blocking = 0 Total User CPU Time used by agent (s) = 0.100000 Total System CPU Time used by agent (s) = 0.020000 Package cache lookups = 2 Package cache inserts = 1 Application section lookups = 10 Application section inserts = 1 Catalog cache lookups = 1 Catalog cache inserts = 1 Catalog cache overflows = 0 Catalog cache heap full = 0 Most recent operation = Select Most recent operation start timestamp = 02-25-1999 13:33:41.394260 Most recent operation stop timestamp = 02-25-1999 13:33:41.446740 Agents associated with the application = 1 Number of hash joins = 0 Number of hash loops = 0 Number of hash join overflows = 0 Number of small hash join overflows = 0 Statement type = Dynamic SQL Statement Statement = Select Section number = 201 Application creator = NULLID Package name = SQLC28A4 Cursor name = SQLCUR201 Statement node number = 0 Statement start timestamp = 02-25-1999 13:33:41.394260 Statement stop timestamp = 02-25-1999 13:33:41.446740 Elapsed time of last completed stmt(sec.ms)= 0.000000 Total user CPU time = 0.000000 Total system CPU time = 0.000000 SQL compiler cost estimate in timerons = 30 SQL compiler cardinality estimate = 47 Degree of parallelism requested = 1 Number of agents working on statement = 1 Number of subagents created for statement = 1 Statement sorts = 0 Total sort time = 0 Sort overflows = 0 Rows read = 8 Rows written = 0 Rows deleted = 0 Rows updated = 0 Rows inserted = 0 Rows fetched = 0 Number of subsections = 0 Dynamic SQL statement text: select * from org
以下は、DCS アプリケーション情報の要求によって得られる典型的な出力です (DCS アプリケーション ID、DCS アプリケーション・ハンドル、 すべての DCS アプリケーション、 またはデータベースのすべての DCS アプリケーションのいずれかを指定)。
DCS Application Snapshot Client application ID = 09151251.04D6.980521202839 Sequence number = 0001 Authorization ID = NEWTON Application name = db2bp Application handle = 0 Application status = waiting for request Status change time = 05-21-1998 16:35:27.670354 Client DB alias = MVSDB Client node = antman Client release level = SQL05020 Client platform = AIX Client protocol = TCP/IP Client codepage = 819 Process ID of client application = 35754 Client login ID = user1 Host application ID = G9151251.G4D7.980521202840 Sequence number = 0000 Host DB name = GILROY Host release level = DSN05011 Host CCSID = 500 Outbound communication address = 9.21.21.92 5021 Outbound communication protocol = TCP/IP Inbound communication address = 9.31.12.34 334 First database connect timestamp = 05-21-1998 16:28:39.517919 Time spent on gateway processing = 0.334215 Last reset timestamp = Rows selected = 0 Number of SQL statements attempted = 2 Failed statement operations = 0 Commit statements = 1 Rollback statements = 0 Inbound bytes received = 392 Outbound bytes sent = 136 Outbound bytes received = 178 Inbound bytes sent = 190 Number of open cursors = 0 Application idle time = 53 seconds UOW completion status = Committed - Commit Statement Previous UOW completion timestamp = UOW start timestamp = 05-21-1998 16:35:27.252375 UOW stop timestamp = 05-21-1998 16:35:27.670290 Inbound bytes received for UOW = 180 Outbound bytes sent for UOW = 136 Outbound bytes received for UOW = 178 Inbound bytes sent for UOW = 190 Most recent operation = Static Commit Most recent operation start timestamp = 05-21-1998 16:35:27.284183 Most recent operation stop timestamp = 05-21-1998 16:35:27.670290 Statement = Static Commit Section number = 0 Application creator = NULLID Package name = SQLC28A0 SQL compiler cost estimate in timerons = 0 SQL compiler cardinality estimate = 0 Statement start timestamp = 05-21-1998 16:35:27.284183 Statement stop timestamp = 05-21-1998 16:35:27.670290 Rows fetched = 0 Time spent on gateway processing = 0.333740 Inbound bytes received for statement = 0 Outbound bytes sent for statement = 10 Outbound bytes received for statement = 54 Inbound bytes sent for statement = 0
以下は、バッファー・プール情報の要求によって得られる典型的な出力です。
Bufferpool Snapshot Bufferpool name = IBMDEFAULTBP Database name = SAMPLE Database path = /home/user1/user1/... NODE0000/SQL00011/ Input database alias = SAMPLE Buffer pool data logical reads = 32 Buffer pool data physical reads = 13 Buffer pool data writes = 0 Buffer pool index logical reads = 55 Buffer pool index physical reads = 23 Total buffer pool read time (ms) = 364 Total buffer pool write time (ms) = 0 Database files closed = 0 Asynchronous pool data page reads = 0 Asynchronous pool data page writes = 0 Buffer pool index writes = 0 Asynchronous pool index page reads = 0 Asynchronous pool index page writes = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 Direct reads = 34 Direct writes = 0 Direct read requests = 4 Direct write requests = 0 Direct reads elapsed time (ms) = 1 Direct write elapsed time (ms) = 0 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0
以下は、表情報の要求によって得られる典型的な出力です。
Table Snapshot First database connect timestamp = 12-27-1999 23:28:58.699766 Last reset timestamp = Snapshot timestamp = 12-27-1999 23:42:02.881998 Database name = FOO Database path = /home/mckeough/mckeough/NODE0000/SQL00001/ Input database alias = FOO Number of accessed tables = 3 Table List Table Schema = MCKEOUGH Table Name = FOO Table Type = User Rows Read = 0 Rows Written = 4 Overflows = 0 Page Reorgs = 0 Table Schema = SYSIBM Table Name = SYSCOLUMNS Table Type = Catalog Rows Read = 0 Rows Written = 1 Overflows = 0 Page Reorgs = 0 Table Schema = SYSIBM Table Name = SYSUSERAUTH Table Type = Catalog Rows Read = 0 Rows Written = 1 Overflows = 0 Page Reorgs = 0
以下は、表スペース情報の要求によって得られる典型的な出力です。
Tablespace Snapshot First database connect timestamp = 04-04-1997 14:29:55.197659 Last reset timestamp = Snapshot timestamp = 04-04-1997 14:32:14.151875 Database name = SAMPLE Database path = /home/user1/user1/NODE0000/SQL00011/ Input database alias = SAMPLE Number of accessed tablespaces = 3 Tablespace name = SYSCATSPACE Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Buffer pool data logical reads = 26 Buffer pool data physical reads = 11 Asynchronous pool data page reads = 0 Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index logical reads = 55 Buffer pool index physical reads = 23 Asynchronous pool index page reads = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 Total buffer pool read time (ms) = 342 Total buffer pool write time (ms) = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 Direct reads = 34 Direct writes = 0 Direct read requests = 4 Direct write requests = 0 Direct reads elapsed time (ms) = 1 Direct write elapsed time (ms) = 0 Number of files closed = 0 Tablespace name = TEMPSPACE1 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Buffer pool data logical reads = 0 Buffer pool data physical reads = 0 Asynchronous pool data page reads = 0 Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index logical reads = 0 Buffer pool index physical reads = 0 Asynchronous pool index page reads = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 Total buffer pool read time (ms) = 0 Total buffer pool write time (ms) = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 Direct reads = 0 Direct writes = 0 Direct read requests = 0 Direct write requests = 0 Direct reads elapsed time (ms) = 0 Direct write elapsed time (ms) = 0 Number of files closed = 0 Tablespace name = USERSPACE1 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Buffer pool data logical reads = 6 Buffer pool data physical reads = 2 Asynchronous pool data page reads = 0 Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index logical reads = 0 Buffer pool index physical reads = 0 Asynchronous pool index page reads = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 Total buffer pool read time (ms) = 22 Total buffer pool write time (ms) = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 Direct reads = 0 Direct writes = 0 Direct read requests = 0 Direct write requests = 0 Direct reads elapsed time (ms) = 0 Direct write elapsed time (ms) = 0 Number of files closed = 0
以下は、ロック情報の要求によって得られる典型的な出力です。
Database Lock Snapshot Database name = FOO Database path = /home/newton/newton/NODE0000/SQL00001/ Input database alias = FOO Locks held = 22 Applications currently connected = 1 Agents currently waiting on locks = 0 Snapshot timestamp = 12-27-1999 23:41:59.166963 Application handle = 2 Application ID = *LOCAL.newton.991228042858 Sequence number = 0001 Application name = db2bp Authorization ID = NEWTON Application status = UOW Waiting Status change time = Application code page = 819 Locks held = 5 Total wait time (ms) = 0 Lock Object Name = 33 Node number lock is held at = 0 Object Type = Table Tablespace Name = SYSCATSPACE Table Schema = SYSIBM Table Name = SYSUSERAUTH Mode = IX Status = Granted Lock Escalation = NO Lock Object Name = 558 Node number lock is held at = 0 Object Type = Row Tablespace Name = SYSCATSPACE Table Schema = SYSIBM Table Name = SYSTABAUTH Mode = W Status = Granted Lock Escalation = NO Lock Object Name = 13 Node number lock is held at = 0 Object Type = Table Tablespace Name = SYSCATSPACE Table Schema = SYSIBM Table Name = SYSTABAUTH Mode = IX Status = Granted Lock Escalation = NO Lock Object Name = 3078 Node number lock is held at = 0 Object Type = Row Tablespace Name = SYSCATSPACE Table Schema = SYSIBM Table Name = SYSTABLES Mode = W Status = Granted Lock Escalation = NO Lock Object Name = 2 Node number lock is held at = 0 Object Type = Table Tablespace Name = USERSPACE1 Table Schema = NEWTON Table Name = FOO Mode = Z Status = Granted Lock Escalation = NO
以下は、動的 SQL 情報の要求によって得られる典型的な出力です。
Dynamic SQL Snapshot Result Database name = SAMPLE Database path = /home/smith/smith/NODE0000/SQL00001/ Number of executions = 2 Number of compilations = 1 Worst preparation time (ms) = 126 Best preparation time (ms) = 126 Rows deleted = 0 Rows inserted = 0 Rows read = 24 Rows updated = 0 Rows written = 0 Statement sorts = 0 Total execution time (sec.ms) = 0.060226 Total system cpu time (sec.ms) = 0 Total user cpu time (sec.ms) = 0 Statement text = select * from org
使用上の注意
リモート・インスタンス (または別のローカル・インスタンス) からスナップショットを取得するには、 まずそのインスタンスに接続する必要があります。 異なるインスタンスにあるデータベースの別名が指定されると、 エラー・メッセージが戻されます。
統計を取得するには、 データベース・システム・モニターのスイッチがオンになっていなければなりません。
以下のいずれかが当てはまる場合、 表の情報の要求によってデータは戻されません。
追加情報