存储过程执行失败与sql668n
某日监控报存储过程执行失败,查看返回码为sql668n
[db2inst1@limt bin]$ db2 ? sql668n SQL0668N Operation not allowed for reason code "<reason-code>" on table "<table-name>". Explanation: Access to table "<table-name>" is restricted. The cause is based on the following reason codes "<reason-code>":
从解释看是在执行的时候遇到权限问题,然后经过一系列折腾找到两张表处于Load Pending状态,导致存储过程执行失败,处理步骤大体如下:
# 1 通过存储过程名找到Package名 [db2inst1@limt bin]$ db2 "select substr(BNAME,1,20) as PACKAGE from syscat.ROUTINEDEP where SPECIFICNAME in (select substr(SPECIFICNAME,1,30) from syscat.PROCEDURES where PROCNAME='BONUS_INCREASE') and BTYPE='K'" PACKAGE -------------------- P764091115 1 record(s) selected. # 2 通过Package名找到此存储过程所依赖的对象 [db2inst1@limt bin]$ db2 "select BTYPE,substr(BNAME,1,30) from syscat.PACKAGEDEP where PKGNAME='P764091115'" BTYPE 2 ----- ------------------------------ T EMPLOYEE I XEMP2 I PK_EMPLOYEE 3 record(s) selected. # 3 查看表的状态,当然下面表状态是正常 [db2inst1@limt bin]$ db2 load query table db2inst1.EMPLOYEE Tablestate: Normal
至于怎么处理表Load Pending状态,请自行百度