其实这个问题是以前同一个客户遇见的问题，当时一个工程师解决后记录的过程如下：
应用同事反映但是对应到执行存储过程，执行了2，3个小时了，还没出来结果。
存储过程主要是执行一条update sql语句，单独将语句拿出来，clp命令行执行很快，2－3s即可执行完成。
执行的SP：
call pdw.P_OCS_ACTIVE_UPDATE('20120304', )
存储过程主要业务SQL：
/***********************开始实现业务******************************/
/*******/
SET vn_Step=1;--
call papp.p_debug(txdate,vn_PrcName,vn_Step,vv_sql); --
UPDATE PODS.T_ODS_PAR_CUSTMSG a
SET run_code='UU'
WHERE sm_code in ('o3','os','om','ol','ox') AND NOT EXISTS
(SELECT * FROM PODS.T_ODS_PAR_OCSACTIVEMSG b where a.ID_NO=b.ID_NO );
COMMIT;--
/************************结束业务逻辑******************************/
根据了解，这个存储过程有些时候了，最近无改动，以前都正常。
根据具体情况，第一反应是这个存储过程中语句的执行计划不正确了。静态sql的访问计划是在第一次编译后存储在数据库的包中的，之后运行都是使用包中的执行计划。解决办法很简单，对存储过程包重新绑定一下，使用最新的数据库统计信息生成最新的访问计划。
先查出此存储过程对应的包：
SELECT bname,
pkgname,
BSCHEMA
FROM syscat.packagedep
WHERE btype='T'
AND pkgname in(select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname='P_OCS_ACTIVE_UPDATE'
AND PROCSCHEMA='PDW'))
[DWE3:/tmp]db2 "SELECT bname,
> pkgname,
> BSCHEMA
> FROM syscat.packagedep
> WHERE btype='T'
> AND pkgname in(select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname='P_OCS_ACTIVE_UPDATE'
> AND PROCSCHEMA='PDW'))
> "
BNAME BSCHEMA
--------------------------------------------------------------------------------
T_ODS_PAR_CUSTMSG P8414315 PODS
T_ODS_PAR_OCSACTIVEMSG P8414315 PODS
2 record(s) selected.
对包执行rebind，操作前需连库
[DWE3:/tmp]time db2 rebind package pdw.P8414315
DB20000I The REBIND PACKAGE command completed successfully.
real 0m0.63s
user 0m0.02s
sys 0m0.00s
对存储过程包重新绑定后，存储过程几秒钟即执行完成，恢复正常。
拿出sql单独执行计划：
[DWE3:/newdb2home/db2inst3/fengsh]more 2.out
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = Yes
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"DB2INST3"
Statement:
UPDATE PODS.T_ODS_PAR_CUSTMSG a SET run_code='UU'
WHERE sm_code in ('o3' , 'os' , 'om' , 'ol' , 'ox' )AND NOT EXISTS
(SELECT *
FROM PODS.T_ODS_PAR_OCSACTIVEMSG b
where a.ID_NO=b.ID_NO )
Section Code Page = 1386
Estimated Cost = 1512832.000000
Estimated Cardinality = 183703.968750
Coordinator Subsection - Main Processing:
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7
Subsection #1:
Access Table Name = PODS.T_ODS_PAR_OCSACTIVEMSG ID = 11,3174
| #Column