DB2 FOR LUW / DB2FORLUW / 970 - DB2

"CREATE PROCEDURE ... LANAGUAGE SQL ..." may fail with the 
following SQL0901 error: 
 
SQL0901N The SQL statement failed because of a non-severe system 
error.  Subsequent SQL statements can be processed. (Reason 
"column number out of range".)  LINE NUMBER=512.  SQLSTATE=58004 
 
This will more likely to happen if the procedure getting created 
references a view whose parent table had columns dropped 
previously. 
 
Here are the corresponding entries from the db2diag.log: 
 
2011-03-08-15.24.31.900411+330 I1117358A613       LEVEL: Event 
PID     : 1241266              TID  : 8499        PROC : db2sysc 
0 
INSTANCE: xxxxxxxx             NODE : 000         DB   : xxxxxx 
APPHDL  : 0-179                APPID: 
*LOCAL.xxxxxxxx.xxxxxxxxxxxx 
AUTHID  : xxxxxxx 
EDUID   : 8499                 EDUNAME: db2agent (xxxxxx) 0 
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:20 
STOP    : AUTOMATIC FODC : AppErr : success 
IMPACT  : None 
DATA #1 : String, 120 bytes 
Check 
/xxxx/xxxxxxxx/sqllib/db2dump/FODC_AppErr_2011-03-08-15.24.31.54 
1915_1241266_8499_000/ 
for diagnostic information. 
 
2011-03-08-15.24.31.900588+330 I1117972A1512      LEVEL: Severe 
PID     : 1241266              TID  : 8499        PROC : db2sysc 
0 
INSTANCE: xxxxxxxx             NODE : 000         DB   : xxxxxx 
APPHDL  : 0-179                APPID: 
*LOCAL.xxxxxxxx.xxxxxxxxxxxx 
AUTHID  : xxxxxxx 
EDUID   : 8499                 EDUNAME: db2agent (xxxxxx) 0 
FUNCTION: DB2 UDB, SW- query graph, sqlnq_ftb::num2fcs, 
probe:100 
MESSAGE : column number out of range 
DATA #1 : signed integer, 4 bytes 
176 
DATA #2 : String with size, 15 bytes 
ORDER_LINE_ITEM 
CALLSTCK: 
  [0] 0x090000001019C948 pdLog + 0xD4 
  [1] 0x090000000CB2B42C pdLog@glue425 + 0xFC 
  [2] 0x090000000DCD4398 
num2fcs__9sqlnq_ftbFiPP9sqlnq_fcsT1P3loc + 0x174 
  [3] 0x09000000101E5FBC 
get_col_num__9sqlnq_qtbF12sqlnq_stringPiT2i + 0x13C 
  [4] 0x09000000101D7320 
@107@sqlnq_opr_nrs_def__F20sqlnq_multipart_nameiP9sqlnq_oprPP9sq 
lnq_qtbPP9sqlnq_qunPP9sqlnq_qncPi 
+ 0xEC 
  [5] 0x09000000101D70C8 
.@107@sqlnq_handle_qb_named_ref.fdpr.clone.37__F20sqlnq_multipar 
t_nameiP9sqlnq_oprPP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncPiT4 
+ 0xC4 
  [6] 0x09000000101D6728 
@107@sqlnq_handle_named_ref__F20sqlnq_multipart_nameiP9sqlnq_qtb 
P9sqlnq_oprT2PP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncT6Pi21sqlnq_hi 
+ 0x2E8 
  [7] 0x09000000101D6108 
.sqlnq_sem_col_ref.fdpr.clone.355__FPP8stknode_i10actiontypePUcP 
3locb 
+ 0x1F0 
  [8] 0x09000000101DDAC4 
.sqlnq_sem_objname1.fdpr.clone.354__FPP8stknode_i10actiontypePUc 
P3locb 
+ 0x19C 
  [9] 0x09000000101C38DC 
sqlnq_sem__FPP8stknode_i10actiontypePUcP3loc + 0x2F4 
 
2011-03-08-15.24.31.900946+330 E1119485A1067      LEVEL: Info 
(Origin) 
PID     : 1241266              TID  : 8499        PROC : db2sysc 
0 
INSTANCE: xxxxxxxx             NODE : 000         DB   : xxxxxx 
APPHDL  : 0-179                APPID: 
*LOCAL.xxxxxxxx.xxxxxxxxxxxx 
AUTHID  : xxxxxxx 
EDUID   : 8499                 EDUNAME: db2agent (xxxxxx) 0 
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650 
MESSAGE : ZRC=0x80310004=-2144272380=SQLNN_E_NFOUND 
          "Could not find an internal object required to process 
the command" 
DATA #1 : String, 499 bytes 
Compiler error stack for rc = -2144272380: 
sqlnn_cmpl[300] 
sqlnp_main[250] 
sqlnp_parser[330] 
sqlnp_smactn[100] 
sqlnq_sem[170] 
sqlnq_handle_from_table_re[22] 
sqlnq_check_referenced_qtb[200] 
sqlnq_check_referenced_qtb[180] 
sqlnq_handle_new_view[158] 
sqlnp_main[250] 
sqlnp_parser[330] 
sqlnp_smactn[100] 
sqlnq_sem[220] 
sqlnq_sem_col_ref[235] 
sqlnq_handle_named_ref_in_[10] 
sqlnq_handle_named_ref[10] 
sqlnq_handle_qb_named_ref[80] 
sqlnq_opr_nrs_def[70] 
sqlnq_qtb::get_col_num[50] 
sqlnq_ftb::num2fcs[100] 
 
2011-03-08-15.24.31.905493+330 E1120553A813       LEVEL: Info 
(Origin) 
PID     : 1241266              TID  : 8499        PROC : db2sysc 
0 
INSTANCE: xxxxxxxx             NODE : 000         DB   : xxxxxx 
APPHDL  : 0-179                APPID: 
*LOCAL.xxxxxxxx.xxxxxxxxxxxx 
AUTHID  : xxxxxxx 
EDUID   : 8499                 EDUNAME: db2agent (xxxxxx) 0 
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650 
MESSAGE : ZRC=0x80310004=-2144272380=SQLNN_E_NFOUND 
          "Could not find an internal object required to process 
the command" 
DATA #1 : String, 245 bytes 
Compiler error stack for rc = -2144272380: 
sqlnn_cmpl[300] 
sqlnp_main[250] 
sqlnp_parser[330] 
sqlnp_smactn[100] 
sqlnq_call_stmt[110] 
sqlnq_procedure_tresolve[110] 
sqlnq_resolve_procedure[130] 
sqlnn_regen_procedure[310] 
sqlnn_regen_procedure[200] 
... 
 
Here's corresponding stack from the FODC data: 
 
------Function + Offset------ 
pthread_kill + 0xB0 
sqloDumpEDU + 0xA4 
sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 + 0x110 
sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2@glue5AD + 0x98 
sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x384 
sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x28 
sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x144 
sqlnn_erds__FiN41e + 0x150 
sqlnn_erds__FiN41e@glue708 + 0xC0 
num2fcs__9sqlnq_ftbFiPP9sqlnq_fcsT1P3loc + 0xEC 
get_col_num__9sqlnq_qtbF12sqlnq_stringPiT2i + 0x13C 
@107@sqlnq_opr_nrs_def__F20sqlnq_multipart_nameiP9sqlnq_oprPP9sq 
lnq_qtbPP9sqlnq_qunPP9sqlnq_qncPi 
+ 0xEC 
.@107@sqlnq_handle_qb_named_ref.fdpr.clone.37__F20sqlnq_multipar 
t_nameiP9sqlnq_oprPP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncPiT4 
+ 0xC4 
@107@sqlnq_handle_named_ref__F20sqlnq_multipart_nameiP9sqlnq_qtb 
P9sqlnq_oprT2PP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncT6Pi21sqlnq_hie 
rarchy_usage 
+ 0x2E8 
.sqlnq_sem_col_ref.fdpr.clone.355__FPP8stknode_i10actiontypePUcP 
3locb 
+ 0x1F0 
.sqlnq_sem_objname1.fdpr.clone.354__FPP8stknode_i10actiontypePUc 
P3locb 
+ 0x19C 
sqlnq_sem__FPP8stknode_i10actiontypePUcP3loc + 0x2F4 
sqlnp_parser__FP8sqlnp_cb + 0x498 
sqlnp_parser__FP8sqlnp_cb + 0x94 
sqlnp_main__FP12sqlnq_stringbP3locPP9sqlnq_qur + 0x244

**************************************************************** 
* USERS AFFECTED:                                              * 
* This will more likely to happen if the procedure getting     * 
* created references a view whose parent table had columns     * 
* droppped previously.                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Without this APAR, customer is exposed to the issue as       * 
* described in the "ERROR DESCRIPTION" section.                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7, Fixpack 5.                       * 
****************************************************************

End the back-end process with "db2 terminate" and then re-issue 
the CREATE PROCEDURE again.

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 Version 9.7, Fixpack 5.

Recycle the database then re-issue the CREATE PROCEDURE again.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

05.06.2011
03.01.2012
22.05.2012

9.7.FP5