|
Problem IC76799
|
Status: Closed |
"CREATE PROCEDURE ... LANAGUAGE SQL ..." MAY FAIL WITH SQL0901 ERROR (REASON "COLUMN NUMBER OUT OF RANGE".) |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
"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 |
| Problem Summary: |
****************************************************************
* 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. *
**************************************************************** |
| Local Fix: |
End the back-end process with "db2 terminate" and then re-issue
the CREATE PROCEDURE again. |
| available fix packs: |
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
|
| Solution |
First fixed in DB2 Version 9.7, Fixpack 5. |
| Workaround |
Recycle the database then re-issue the CREATE PROCEDURE again. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.06.2011
03.01.2012
22.05.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
