你必须掌握的：DB2 pureScale实用运维命令
我们总结了平时运维中经常会用到的一些实用命令（以mydb数据库为例），这里只是抛砖引玉，大家可以建立起自己的运维笔记，从而节省到处查资料的时间。
1．数据库激活命令。
激活数据库：
activate database mydb
去激活数据库TEST：
deactivate database mydb
去激活某一个成员上的数据库TEST：
deactivate database mydb member 2
2．DB CFG参数配置命令。
获得当前成员的DB CFG信息：
get db cfg for mydb
DB CFG配置应用到整个集群：
update db cfg for mydb using
DB CFG配置应用到某个成员：
update db cfg for mydb member 0 using
3．CF管理命令。
检查CF状态：
select id, varchar(current_host, 10) as cur_host, varchar(state,17) as state, altert from sysibmadm.db2_cf
ID CUR_HOST STATE ALERT
----- ------------- ------ -----
128 NODE101 PRIMARY NO
129 NODE102 PEER NO
启动CF：
db2start CF 129
停止CF：
db2stop CF 129
4．成员管理命令。
检查成员状态：
select id,
varchar(HOME_HOST,10) as home_host,
varchar(current_host, 10) as cur_host,
varchar(state, 21) as state,
alert
from sysibmadm.db2_member
ID HOME_HOST CUR_HOST STATE ALERT
----- ---------- ----- ----- -----
0 NODE101 NODE101 STOPPED NO
1 NODE102 NODE102 STARTED NO
2 record(s) selected.
启动成员：
db2start member 0
停止成员：
db2stop member 0
5．维护某一个成员命令。
暂停某个成员：
db2stop member 1 quiesce
为了执行维护，例如升级操作系统补丁，停止实例：
db2stop instance on node101
完成维护后，重启实例和成员：
db2start instance on node101
db2start member 2
6．让集群某台主机进入维护状态。
需要首先停止当前主机上访问GPFS文件系统的所有进程，随后停止当前主机上所有DB2实例，最后在某台主机上执行下面的命令：
db2cluster -cm -enter -maintenance
db2cluster -cfs -enter -maintenance
退出维护状态，在某台主机上执行下面的命令：
db2cluster -cm -exit -maintenance
db2cluster -cfs -exit -maintenance
7．替换CF命令。
停止当前CF：
db2stop CF 128
停止当前CF所在主机的实例：
db2stop instance on node101
从集群中删除当前CF：
db2iupdt -drop -cf node101 db2sdin1
向集群中添加新的CF：
db2iupdt -add -cf node101:node101-ib1 db2sdin1
重启新加入的CF：
db2start CF 129
8．检测主机状态。
使用下面的命令获取主机状态：
select varchar(hostname, 10) as host, varchar(state,8) as state,varchar(instance_stopped, 7) as stopped, alert from sysib-madm.db2_cluster_host_state
host state stopped alert
----- ----- -------- -----
NODE101 ACTIVE NO NO
NODE102 INACTIVE NO YES
2 record(s) selected.
9．检测成员状态。
使用下面的命令获取成员状态：
select id, varchar(state, 21) as state, varchar(home_host, 10) as home_host, varchar(current_host, 10) as cur_host, alert from sysibmadm.db2_member
state home_host cur_host alert
----- --------- -------- ------
STARTED NODE101 NODE101 NO
WAITING_FOR_FAILBACK NODE102 NODE102 YES
2 record(s) selected.
10．检测CF状态。
使用下面的命令获取CF状态：
select id, varchar(state, 17) as state, varchar(home_host, 10) as home_host, varchar(current_host, 10) as cur_host, alert from TA-BLE(db2_get_instance_info(NULL,'','','CF',NULL))
id state home_host cur_host alert
--- ------ --------- --------- -----
128 CATCHUP(85%) NODE101 NODE101 NO
129 PRIMARY NODE102 NOD3102 NO
2 record(s) selected.
11．监控CF节点全局缓冲池。
使用下面的监控命令：
select substr(host_name, 1, 7) as host, id as hostid, current_cf_gbp_size, configured_cf_gbp_size, target_cf_gbp_size from table(mon_get_cf(NULL))
host hostid current_cf_gbp_size configured_cf_gbp_size target_cf_gbp_size
---- ----- ----------------- --------------------- -------------------
node101 128 2014 142336 142336
node102 129 2248 142336 142336
2 record(s) selected.
12．监控CF使用的锁内存。
使用下面的监控命令：
select substr(host_name, 1, 7) as host, id as hostid, current_cf_lock_size, configured_cf_lock_size, target_cf_lock_size from table(mon_get_cf(NULL))
host hostid current_cf_lock_size configured_cf_lock_size target_cf_lock_size
---- ----- ----------------- --------------------- -------------------
NODE101 128 133852 564224 564224
NODE102 129 133852 564224 564224
2 record(s) selected.
13．监控全局缓冲池命中率。
使用下面的监控命令：
select bp_name, member, POOL_DATA_GBP_L_READS+POOL_INDEX_GBP_L_READS AS LOGI-CAL_READS,
POOL_DATA_GBP_P_READS+POOL_INDEX_GBP_P_READS AS PHYSICAL_READS,
((FLOAT(POOL_DATA_GBP_L_READS+POOL_INDEX_GBP_L_READS) -FLOAT(POOL_DATA_GBP_P_READS+POOL_INDEX_GBP_P_READS))/FLOAT(POOL_DATA_GBP_L_READS+POOL_INDEX_GBP_L_READS))
* 100 AS HIT_RATIO FROM TABLE(MON_GET_BUFFERPOOL('',-2)) WHERE BP_NAME='IBMDEFAULTBP'
BP_NAME MEMBER LOGICAL_READS PHYSICAL_READS HIT_RATIO
------------ ------ ------------- --------------- ----------
IBMDEFAULTBP 1 191509 3569 98.13
IBMDEFAULTBP 0 189314 3419 98.19
2 record(s) selected.
14．监控CF节点的CPU利用率。
使用下面的监控命令：
select varchar(name, 20) as host_attribute, varchar(value, 25) as values, var-char(unit,8) as unit from sysibmadm.env_cf_sys_resources
host_attribute value unit
HOST_NAME NODE101 -
MEMORY_TOTAL 24108 MB
MEMORY_FREE 3504 MB
MEMORY_SWAP_TOTAL 4102 MB
MEMORY_SWAP_FREE 4063 MB
MEMORY_MEM_TOTAL 28211 MB
MEMORY_MEM_FREE 7568 MB
CPU_USAGE_TOTAL 96 PERCENT
HOST_NAME NODE102 -
MEMORY_TOTAL 24108 MB
MEMORY_FREE 3342 MB
MEMORY_SWAP_TOTAL 4102 MB
MEMORY_SWAP_FREE 4063 MB
MEMORY_MEM_TOTAL 28211 MB
MEMORY_MEM_FREE 7406 MB
CPU_USAGE_TOTAL 97 PERCENT
16 record(s) selected.
15．使用db2instance命令快速查看各个组件状态。
也可以使用db2instance命令快速查看集群中各个组件的状态：
db2instance -list
ID TYPE STATE HOME_HOST CURRENT_HOST ALERT PARTITION_NUMBER LOGICAL_PORT NETNAME
-- ---- ----- --------- ------------ ----- ---------------- ------------ ---------
0 MEMBER STARTED node101 node101 NO 0 0 node101
1 MEMBER STARTED node102 node102 NO 0 0 node102
128 CF PRIMARY node101 node101 NO - 0 node101
129 CF PEER node102 node102 NO - 0 node102
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
node101 ACTIVE NO NO
node102 ACTIVE NO NO
1．数据库激活命令。
激活数据库：
activate database mydb
去激活数据库TEST：
deactivate database mydb
去激活某一个成员上的数据库TEST：
deactivate database mydb member 2
2．DB CFG参数配置命令。
获得当前成员的DB CFG信息：
get db cfg for mydb
DB CFG配置应用到整个集群：
update db cfg for mydb using
DB CFG配置应用到某个成员：
update db cfg for mydb member 0 using
3．CF管理命令。
检查CF状态：
select id, varchar(current_host, 10) as cur_host, varchar(state,17) as state, altert from sysibmadm.db2_cf
ID CUR_HOST STATE ALERT
----- ------------- ------ -----
128 NODE101 PRIMARY NO
129 NODE102 PEER NO
启动CF：
db2start CF 129
停止CF：
db2stop CF 129
4．成员管理命令。
检查成员状态：
select id,
varchar(HOME_HOST,10) as home_host,
varchar(current_host, 10) as cur_host,
varchar(state, 21) as state,
alert
from sysibmadm.db2_member
ID HOME_HOST CUR_HOST STATE ALERT
----- ---------- ----- ----- -----
0 NODE101 NODE101 STOPPED NO
1 NODE102 NODE102 STARTED NO
2 record(s) selected.
启动成员：
db2start member 0
停止成员：
db2stop member 0
5．维护某一个成员命令。
暂停某个成员：
db2stop member 1 quiesce
为了执行维护，例如升级操作系统补丁，停止实例：
db2stop instance on node101
完成维护后，重启实例和成员：
db2start instance on node101
db2start member 2
6．让集群某台主机进入维护状态。
需要首先停止当前主机上访问GPFS文件系统的所有进程，随后停止当前主机上所有DB2实例，最后在某台主机上执行下面的命令：
db2cluster -cm -enter -maintenance
db2cluster -cfs -enter -maintenance
退出维护状态，在某台主机上执行下面的命令：
db2cluster -cm -exit -maintenance
db2cluster -cfs -exit -maintenance
7．替换CF命令。
停止当前CF：
db2stop CF 128
停止当前CF所在主机的实例：
db2stop instance on node101
从集群中删除当前CF：
db2iupdt -drop -cf node101 db2sdin1
向集群中添加新的CF：
db2iupdt -add -cf node101:node101-ib1 db2sdin1
重启新加入的CF：
db2start CF 129
8．检测主机状态。
使用下面的命令获取主机状态：
select varchar(hostname, 10) as host, varchar(state,8) as state,varchar(instance_stopped, 7) as stopped, alert from sysib-madm.db2_cluster_host_state
host state stopped alert
----- ----- -------- -----
NODE101 ACTIVE NO NO
NODE102 INACTIVE NO YES
2 record(s) selected.
9．检测成员状态。
使用下面的命令获取成员状态：
select id, varchar(state, 21) as state, varchar(home_host, 10) as home_host, varchar(current_host, 10) as cur_host, alert from sysibmadm.db2_member
state home_host cur_host alert
----- --------- -------- ------
STARTED NODE101 NODE101 NO
WAITING_FOR_FAILBACK NODE102 NODE102 YES
2 record(s) selected.
10．检测CF状态。
使用下面的命令获取CF状态：
select id, varchar(state, 17) as state, varchar(home_host, 10) as home_host, varchar(current_host, 10) as cur_host, alert from TA-BLE(db2_get_instance_info(NULL,'','','CF',NULL))
id state home_host cur_host alert
--- ------ --------- --------- -----
128 CATCHUP(85%) NODE101 NODE101 NO
129 PRIMARY NODE102 NOD3102 NO
2 record(s) selected.
11．监控CF节点全局缓冲池。
使用下面的监控命令：
select substr(host_name, 1, 7) as host, id as hostid, current_cf_gbp_size, configured_cf_gbp_size, target_cf_gbp_size from table(mon_get_cf(NULL))
host hostid current_cf_gbp_size configured_cf_gbp_size target_cf_gbp_size
---- ----- ----------------- --------------------- -------------------
node101 128 2014 142336 142336
node102 129 2248 142336 142336
2 record(s) selected.
12．监控CF使用的锁内存。
使用下面的监控命令：
select substr(host_name, 1, 7) as host, id as hostid, current_cf_lock_size, configured_cf_lock_size, target_cf_lock_size from table(mon_get_cf(NULL))
host hostid current_cf_lock_size configured_cf_lock_size target_cf_lock_size
---- ----- ----------------- --------------------- -------------------
NODE101 128 133852 564224 564224
NODE102 129 133852 564224 564224
2 record(s) selected.
13．监控全局缓冲池命中率。
使用下面的监控命令：
select bp_name, member, POOL_DATA_GBP_L_READS+POOL_INDEX_GBP_L_READS AS LOGI-CAL_READS,
POOL_DATA_GBP_P_READS+POOL_INDEX_GBP_P_READS AS PHYSICAL_READS,
((FLOAT(POOL_DATA_GBP_L_READS+POOL_INDEX_GBP_L_READS) -FLOAT(POOL_DATA_GBP_P_READS+POOL_INDEX_GBP_P_READS))/FLOAT(POOL_DATA_GBP_L_READS+POOL_INDEX_GBP_L_READS))
* 100 AS HIT_RATIO FROM TABLE(MON_GET_BUFFERPOOL('',-2)) WHERE BP_NAME='IBMDEFAULTBP'
BP_NAME MEMBER LOGICAL_READS PHYSICAL_READS HIT_RATIO
------------ ------ ------------- --------------- ----------
IBMDEFAULTBP 1 191509 3569 98.13
IBMDEFAULTBP 0 189314 3419 98.19
2 record(s) selected.
14．监控CF节点的CPU利用率。
使用下面的监控命令：
select varchar(name, 20) as host_attribute, varchar(value, 25) as values, var-char(unit,8) as unit from sysibmadm.env_cf_sys_resources
host_attribute value unit
HOST_NAME NODE101 -
MEMORY_TOTAL 24108 MB
MEMORY_FREE 3504 MB
MEMORY_SWAP_TOTAL 4102 MB
MEMORY_SWAP_FREE 4063 MB
MEMORY_MEM_TOTAL 28211 MB
MEMORY_MEM_FREE 7568 MB
CPU_USAGE_TOTAL 96 PERCENT
HOST_NAME NODE102 -
MEMORY_TOTAL 24108 MB
MEMORY_FREE 3342 MB
MEMORY_SWAP_TOTAL 4102 MB
MEMORY_SWAP_FREE 4063 MB
MEMORY_MEM_TOTAL 28211 MB
MEMORY_MEM_FREE 7406 MB
CPU_USAGE_TOTAL 97 PERCENT
16 record(s) selected.
15．使用db2instance命令快速查看各个组件状态。
也可以使用db2instance命令快速查看集群中各个组件的状态：
db2instance -list
ID TYPE STATE HOME_HOST CURRENT_HOST ALERT PARTITION_NUMBER LOGICAL_PORT NETNAME
-- ---- ----- --------- ------------ ----- ---------------- ------------ ---------
0 MEMBER STARTED node101 node101 NO 0 0 node101
1 MEMBER STARTED node102 node102 NO 0 0 node102
128 CF PRIMARY node101 node101 NO - 0 node101
129 CF PEER node102 node102 NO - 0 node102
HOSTNAME STATE INSTANCE_STOPPED ALERT
-------- ------ ---------------- -----
node101 ACTIVE NO NO
node102 ACTIVE NO NO