［求助］在DB2 CLI命令下执行SNAPSHOT_**时报SQLSTATE=58004
各们专家：我在在DB2 CLI命令下执行SNAPSHOT_**时报SQLSTATE=58004。请各位专家不吝赐教，万分感谢！
下面报错时的语句与系统提示。
db2 => SELECT deadlocks, lock_escals, locks_waiting,appls_cur_cons FROM TABLE(SNAPSHOT_DATABASE('XDZX', -1)) AS SNAPSHOT_DATABASE
SQL1042CAn unexpected system error occurred.SQLSTATE=58004
db2 => select TABLESPACE_NAME,PAGE_SIZE,TOTAL_PAGES,USED_PAGES,FREE_PAGES,case TABLESPACE_TYPE when 1 then 'SMS' else 'DMS' end TYPE,PREFETCH_SIZE,EXTENT_SIZE,TABLESPACE_STATEFROM TABLE(SNAPSHOT_TBS_CFG('XDZX', -1)) as SNAPSHOT_TBS_CFG
SQL1042CAn unexpected system error occurred.SQLSTATE=58004
所有涉及到SNAPSHOT_**的快照都无法正常执行。
其他快照还包括：
SNAPSHOT_LOCK 数据库级别上的锁信息；
SNAPSHOT_DATABASE 数据库信息。
系统环境是：hpunixB11.3+ DB2 V8
DB2 的配置如下：
db2 get dbm cfg
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Database manager configuration release level = 0x0a00
CPU speed (millisec/instruction) (CPUSPEED) = 2.597893e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
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) = /opt/java1.4
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
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) = DB2GRP1
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
Database manager authentication (AUTHENTICATION) = SERVER
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/db2inst1
Database monitor heap size (4KB) (MON_HEAP_SZ) = 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
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024
Sort heap threshold (4KB) (SHEAPTHRES) = 20000
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
Max number of existing agents (MAXAGENTS) = 400
Agent pool size (NUM_POOLAGENTS) = 200(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
Max no. of concurrent coordinating agents(MAXCAGENTS) = MAX_COORDAGENTS
Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = 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) =
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
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 4096
Number of FCM request blocks (FCM_NUM_RQB) = AUTOMATIC
Number of FCM connection entries (FCM_NUM_CONNECT) = AUTOMATIC
Number of FCM message anchors (FCM_NUM_ANCHORS) = AUTOMATIC
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 没有打开monitor switch呗 谢谢huabingl ，不过我想问一下，如何判断（或查看）monitor switch 是否已经打开？ 我刚才在网上找到如何查看“monitor switch 是否已经打开”的语句了。
日常监控
在系统运行过程中，我们可以使用 DB2 提供的快照监控器和事件监控器来收集 BPEDB 的操作和性能信息。通常，这些信息对优化系统配置参数和问题诊断（例如某些 BPC API 查询语句执行时间较长）非常有用；同时，这些日常的系统监控数据还可以作为背景信息，在出现问题的情况下可以与其进行对比来分析问题发生的原因。
通过快照监控器，可以检查 BPEDB 在特定时间点的状态。在使用快照监控器之前，先确定需要设置的监控开关。BPEDB 常用的监控开关包括以下（名称）：
锁定等待及与时间相关的锁定的信息（LOCK）
缓冲池活动信息（BUFFERPOOL）
SQL 语句信息（STATEMENT）
时间和时间戳记信息（TIMESTAMP）
在缺省情况下，除了时间和时间戳记信息的开关在缺省情况下设置为“ON”，其余信息类型相对应的开关全部设置为“OFF”。可执行下面的命令获取当前数据库会话的监控开关状态信息：
db2 GET MONITOR SWITCHES
打开（关闭）监控开关：
db2 UPDATE MONITOR SWITCHES USING – switch_name ON(OFF)
（完成语句）db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"
说明：必须具有 SYSADM、SYSCTRL、SYSMAINT 或 SYSMON 权限才能更新监控器开关或获取快照信息。
最后还是要非常感谢huabingl提点啊
页:
[1]