|
Problem IT02233
|
Status: Closed |
QUERY REFERENCING A VIEW MIGHT PRODUCE SQL0901N IF ONE OR MORE OF THE DEPENDENT TABLES HAVE BEEN ALTERED |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Under rare scenarios, DB2 might produce a SQL0901N error message
if the following conditions are true:
1) The query references one or more views which is marked as
VALID = "Y".
2) The view has one or more dependent tables which have been
altered after the view creation time
3) These altered tables happen to be CGTT or partitioned table.
4) The alter command on the tables dropped one or more columns.
5) None of the dropped columns were referenced in the view
Query gets the following error message:
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "column
number out of
range".) SQLSTATE=58004
The stack file generated in FODC_AppErr* directory has the
following stack:
0x0900000000706B50 pthread_kill + 0xB0
0x0900000006969B48 sqloDumpEDU + 0xA4
0x09000000064D2568 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 +
0xD4
0x09000000057A64E0
sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2@glue5AF + 0x98
0x09000000062ABE28 sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x320
0x0900000007ADC664 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x50
0x0900000005D62700 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc +
0x264
0x090000000721E180 sqlnn_erds__FiN41e + 0x33C
0x0900000006F009C0 sqlnn_erds__FiN41e@glue70A + 0xC0
0x0900000007D229A4 num2fcs__9sqlnq_ftbFiPP9sqlnq_fcsT1P3loc +
0x80
0x0900000007D35268 get_col_num__9sqlnq_qtbF12sqlnq_stringPiT2i +
0x144
0x0900000007D246D0
@106@sqlnq_opr_nrs_def__F20sqlnq_multipart_nameiP9sqlnq_oprPP9sq
lnq_qtbPP9sqlnq_qunPP9sqlnq_qncPi + 0x254
0x09000000097F27EC
@106@sqlnq_qb_named_ref_in_lowbox__F20sqlnq_multipart_nameiP9sql
nq_qunPP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncPi + 0x104
0x0900000007DAD8A4
.@106@sqlnq_handle_qb_named_ref.fdpr.clone.99__F20sqlnq_multipar
t_nameiP9sqlnq_oprPP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncPiT4 +
0x3C
0x0900000007D23780
@106@sqlnq_handle_named_ref__F20sqlnq_multipart_nameiP9sqlnq_qtb
P9sqlnq_oprT2PP9sqlnq_qtbPP9sqlnq_qunPP9sqlnq_qncT6Pi21sqlnq_hie
rarchy_usage + 0x334
0x0900000007D230F0
.sqlnq_sem_col_ref.fdpr.clone.219__FPP8stknode_i10actiontypePUcP
3locb + 0x1D8
0x0900000007D2B664
.sqlnq_sem_objname1.fdpr.clone.218__FPP8stknode_i10actiontypePUc
P3locb + 0x164
0x0900000007D1E07C sqlnq_sem__FPP8stknode_i10actiontypePUcP3loc
+ 0x2FC
0x0900000007D1E3C8 sqlnp_parser__FP8sqlnp_cb + 0x1A0
0x0900000007D14C78 sqlnp_parser__FP8sqlnp_cb + 0x90
0x0900000007D154DC
sqlnp_main__FP12sqlnq_stringbP3locPP9sqlnq_qur + 0x254
0x0900000007CFA81C sqlnq_handle_new_view__FP9sqlnq_qtb + 0x27C
0x0900000007CFF820
sqlnq_check_referenced_qtb__FPP9sqlnq_qtbP9sqlnq_qunP3loc21sqlnq
_hierarchy_usage + 0x7C
0x0900000007CFF45C
sqlnq_check_referenced_qtb__FP9sqlnq_qunP3loc21sqlnq_hierarchy_u
sage + 0x9C
0x0900000007CFF2AC
sqlnq_handle_from_table_ref__FP20sqlnq_multipart_namePUciPP9sqln
q_qunP9sqlnq_qtbP3locP9sqlnq_opr21sqlnq_hierarchy_usagePP8stknod
e_ + 0x130
0x0900000007CFEF48
sqlnq_handle_from_table_ref__FP20sqlnq_multipart_namePUciPP9sqln
q_qunP9sqlnq_qtbP3locP9sqlnq_opr21sqlnq_hierarchy_usagePP8stknod
e_@glue12C4 + 0x88
0x0900000007CFEE3C sqlnq_sem__FPP8stknode_i10actiontypePUcP3loc
+ 0x38
0x0900000007D1E3C8 sqlnp_parser__FP8sqlnp_cb + 0x1A0
0x0900000007D14C78 sqlnp_parser__FP8sqlnp_cb + 0x90
0x0900000007D151B0
sqlnp_main__FP12sqlnq_stringbP3locPP9sqlnq_qur + 0xB8
0x0900000007CDEFF0
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_enviT7PP9sqlnq_qur + 0x488
0x0900000007C9F764
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_env + 0x40
0x0900000007A5DE5C
sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
LOCK_INFOP16sqlra_cached_varPiPUl + 0x80C
0x0900000007A5B63C
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
SQLP_LOCK_INFOPP16sqlra_cac!
0x0900000007A58060 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x6CC
0x09000000075BE590
sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0xC0
............. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 10 or higher *
**************************************************************** |
| Local Fix: |
1) Drop and recreate the view
2) Starting DB2 V101, Regeneration of the valid views can be
forced using the ADMIN_REVALIDATE_DB_OBJECTS |
| Solution |
First fixed in Version 9.7 Fix Pack 10 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.06.2014
01.12.2014
01.12.2014 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP10
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.10
|
 |
