关联方式: 通过 TM_NS_MNET_SMS_M 从db2 list utilities 找到对应的表,对应的120809165343,再从db2 list application 找对应的进程
===========================================================================================================
环境:suse linux 11 sp1 + DB2 V9.7 DPF 环境 共39个分区 (36个数据节点+2个ETL节点+1个管理节点)
硬件:刀片服务器21台(18台数据节点,1台管理节点,2台ETL节点)
db2inst1@dm-etl-1:~> db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09076"
with level identifier "08070107".
Informational tokens are "DB2 v9.7.0.6", "s120629", "IP23421", and Fix Pack
"6".
Product is installed at "/opt/ibm/db2/V9.7FP6".
问题描述:
发现load操作的时候,有时候会hang住不动了,比如如下命令,在凌晨53分提交的load 空文件,但是截止到目前还未load完毕,系统也不繁忙。
通过 db2_all "db2 list applications global show detail"|grep -i 120809165343 查看,发现所有节点的进程均是pending remote request,个人感觉应该是这个进程是僵死状态了
必须停库重启才可以。
事情紧急,还请帮仔细看一下。
2012-08-10 00:53:41 INFO - load from /dev/null of del replace into ODS.TM_NS_MNET_SMS_M NONRECOVERABLE
------------------------SQL 查看----------------------------------------------
db2inst1@dm-etl-1:~> db2 list utilities|more
ID = 461
Type = LOAD
Database Name = HEBDM
Partition Number = 1
Description = [LOADID: 110.2012-08-10-08.36.48.722025.0 (65530;32803)] [133.96.71.10.61825.120810003553] OFFLIN
E LOAD DEL AUTOMATIC INDEXING INSERT NON-RECOVERABLE CANGZHOU.TM_SC_USER_BASE_D
Start Time = 08/10/2012 08:37:32.029192
State = Executing
Invocation Type = User
ID = 460
Type = LOAD
Database Name = HEBDM
Partition Number = 1
Description = [LOADID: 373.2012-08-10-08.35.07.710208.0 (65530;32805)] [133.96.71.10.61821.120810003455] OFFLIN
E LOAD DEL AUTOMATIC INDEXING INSERT NON-RECOVERABLE XINGTAI .TM_SC_USER_BASE_D
Start Time = 08/10/2012 08:35:50.994771
State = Executing
Invocation Type = User
ID = 459
Type = LOAD
Database Name = HEBDM
Partition Number = 1
Description = [LOADID: 145.2012-08-10-08.34.47.278142.0 (65530;32811)] [133.96.71.10.61819.120810003403] OFFLIN
E LOAD DEL AUTOMATIC INDEXING INSERT NON-RECOVERABLE LANGFANG.TM_SC_USER_BASE_D
Start Time = 08/10/2012 08:35:30.554721
State = Executing
Invocation Type = User
ID = 457
Type = LOAD
Database Name = HEBDM
Partition Number = 1
Description = [LOADID: 164.2012-08-10-08.33.58.846776.0 (65530;32808)] [133.96.71.10.61226.120810003042] OFFLIN
E LOAD DEL AUTOMATIC INDEXING INSERT NON-RECOVERABLE QINHUANGDAO.TM_SC_USER_BASE_D
Start Time = 08/10/2012 08:34:42.147594
/SMS
...skipping
Description = [LOADID: 50.2012-08-10-00.53.41.196680.998 (65530;39906)] [*N998.db2inst1.120809165343] OFFLINE LOAD DEL AUTOMATIC INDEXING REPLACE NON-RECOVERABLE ODS .TM_NS_MNET_SMS_M
Start Time = 08/10/2012 00:54:28.175503
State = Executing
Invocation Type = User
ID = 40
Type = LOAD
Database Name = HEBDM
Partition Number = 1
Description = [LOADID: 51.2012-08-10-00.23.19.332212.998 (65530;40921)] [*N998.db2inst1.120809162346] OFFLINE L
OAD DEL AUTOMATIC INDEXING REPLACE NON-RECOVERABLE ODS .TM_GC_STRESS_TRADE_APP_M
Start Time = 08/10/2012 00:24:05.938650
State = Executing
Invocation Type = User
ID = 39
Type = LOAD
Database Name = HEBDM
Partition Number = 1
Description = [LOADID: 83.2012-08-10-00.23.07.815745.998 (65530;40885)] [*N998.db2inst1.120809162320] OFFLINE L
OAD DEL AUTOMATIC INDEXING REPLACE NON-RECOVERABLE ODS .TM_TER_TMNL_FUNC_M
Start Time = 08/10/2012 00:23:54.435100
State = Executing
Invocation Type = User
ID = 33
Type = LOAD
Database Name = HEBDM
Partition Number = 1
Description = [LOADID: 85.2012-08-10-00.21.41.923789.0 (65530;40997)] [133.96.71.10.51222.120809162213] OFFLINE
LOAD DEL AUTOMATIC INDEXING REPLACE NON-RECOVERABLE ODS .TM_CH_CHNL_RECHRG_PAY_D
Start Time = 08/10/2012 00:22:16.528710
State = Executing
Invocation Type = User
ID = 30
Type = LOAD
Database Name = HEBDM
db2inst1@dm-etl-1:~> db2 list applications global show detail|grep -i 120809165343
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00003 37 998 50 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0998/SQL00001/
db2inst1@dm-etl-1:~> db2_all "db2 list applications global show detail"|grep -i 120809165343
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0000/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0001/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0002/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0003/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0004/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0005/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0006/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0007/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0008/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0009/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0010/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0011/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0012/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0013/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0014/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0015/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0016/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0017/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0018/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0019/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0020/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0021/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0022/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0023/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0024/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0025/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0026/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0027/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0028/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0029/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0030/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0031/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0032/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0033/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0034/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0035/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0036/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00003 37 998 50 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0998/SQL00001/
JOB_ETL db2bp 65405946 *N998.db2inst1.120809165343 00005 37 998 0 Pending remote request 08/10/2012 00:55:06.177473 HEBDM /db2path/db2inst1/NODE0015/SQL00001/
db2inst1@dm-etl-1:~>
收起