DB2 FOR LUW / DB2FORLUW / A50 - DB2

Under the following circumstances, a trap will be generated in 
sqlnq_handle_module_obj_ref when creating an SQL routine: 
 
- The routine contains a query that references a materialized 
query table or statistical view; 
- The query also references a module-qualified object, such as a 
module function or module variable. 
 
During compilation of the SQL routine, the instance will trap 
during expansion of the MQT or statistical view, with a stack 
trace similar to the following: 
 
_Z27sqlnq_handle_module_obj_refiPhiS_ibP3loc + 0x0121 
_Z33sqlnq_handle_global_variable_name20sqlnq_multipart_namePP9sq 
lnq_qunPP9sqlnq_qncP3locb + 0x0734 
_Z29sqlnq_handle_named_ref_in_srcP12sqlnq_stringiPP9sqlnq_qtbPP9 
sqlnq_qunPP9sqlnq_qncP3loc21sqlnq_hierarchy_usageP20sqlnq_multip 
art_nameP9sqlnq_pid + 0x025a 
_Z9sqlnq_semPP8stknode_i10actiontypePhP3loc + 0x10623 
_Z12sqlnp_smactnP8sqlnp_cbi + 0x0a6a 
_Z12sqlnp_parserP8sqlnp_cb + 0x077f 
_Z10sqlnp_mainP12sqlnq_stringbP3locPP9sqlnq_qur + 0x0298 
_Z21sqlnq_handle_new_viewP9sqlnq_qtb + 0x0287 
_Z26sqlnq_check_referenced_qtbPP9sqlnq_qtbP9sqlnq_qunP3loc21sqln 
q_hierarchy_usage + 0x02ce 
_Z26sqlnq_check_referenced_qtbP9sqlnq_qunP3loc21sqlnq_hierarchy_ 
usage + 0x0042 
_Z27sqlnq_handle_from_table_refP20sqlnq_multipart_namePhiPP9sqln 
q_qunP9sqlnq_qtbP3locP9sqlnq_opr21sqlnq_hierarchy_usagePP8stknod 
e_ + 0x00fa 
_Z17sqlnq_expand_stmtPP8stknode_i10actiontypePhP3loc + 0x00e4 
_Z12sqlnp_smactnP8sqlnp_cbi + 0x0a8b 
_Z12sqlnp_parserP8sqlnp_cb + 0x077f 
_Z10sqlnp_mainP12sqlnq_stringbP3locPP9sqlnq_qur + 0x0298 
_Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq 
lrr_cmpl_enviiPP9sqlnq_qur + 0x0bbe 
_Z16sqlnq_expand_mstP20sqlnq_multipart_nameP3lociRP9sqlnq_qtbRP9 
sqlnq_qurib + 0x01f6 
_Z16sqlnrExpandMqtSvPK9sqlnq_qurP9sqlnq_ftbibP17sqlnq_qurElemLis 
tRS4_ + 0x0135 
_Z21sqlnr_expand_matviewsP9sqlnq_qurP17sqlnq_qurElemList + 
0x0b40 
_Z9sqlnr_exeP9sqlnq_qur + 0x04aa 
_Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq 
lrr_cmpl_env + 0x0022 
_Z10sqlrr_cmplP8sqlrr_cbP11sqlrrstrings17sqlnn_compileModehP5sql 
daPiPjPP9sqlri_shdP11compileInfo + 0x02db 
_Z13sqlrr_compileP14db2UCinterfaceP11compileInfo + 0x056c 
_ZN13psmPackageGen12addStatementEPciiP18psmPackageVariableiRt + 
0x01df 
_ZN14psmQueryString11makeSectionEP19psmPVMCodeGeneratoritR12pvmQ 
ueryDesc + 0x024c 
_ZN19psmPVMCodeGenerator8generateEP23psm_DECLARE_CURSOR_node + 
0x01de 
_ZN19psmPVMCodeGenerator8generateEP14psm_block_node + 0x03c6 
_ZN19psmPVMCodeGenerator8generateEP18psm_procedure_node + 0x0118 
_ZN19psmPVMCodeGenerator10genProgramEP10psmProgram + 0x029b 
_ZN8psm_ctrl16psm_generate_PVMEv + 0x0363 
_Z15psm_gen_programP8psm_ctrl + 0x0103 
_Z22sqlnq_proc_create_stmtPP8stknode_i10actiontypePhP3loc + 
0x0643 
_Z12sqlnp_smactnP8sqlnp_cbi + 0x0a8b 
_Z12sqlnp_parserP8sqlnp_cb + 0x077f 
_Z10sqlnp_mainP12sqlnq_stringbP3locPP9sqlnq_qur + 0x0298

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 Version 10.5 Fix Pack 7.                       * 
****************************************************************

Compile the affected routine with a query optimization level of 
0; this will prevent MQT expansion and avoid the trap.  Be aware 
that this may result in reduced performance for queries 
contained inside the routine.  You should recompile the routine 
at the default optimization level once a fix becomes available. 
 
This example sets the query optimization of 0 for the creation 
of a single routine, then resets the optimization level to the 
default: 
 
call sysproc.set_routine_opts('QUERYOPT 0') % 
 
create procedure P 
language SQL 
begin 
  -- Procedure body... 
end % 
 
call sysproc.set_routine_opts('') %

This problem is first fixed in DB2 Version 10.5 Fix Pack 7.

not known / see Local fix

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

19.03.2015
22.01.2016
22.01.2016