| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IT07810 | Status: Closed |
THE DB2 SERVER TERMINATES ABNORMALLY IN SQLNQ_HANDLE_MODULE_OBJ_REF WHEN CREATING A SQL ROUTINE | |
| product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
| Problem description: | |
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 | |
| Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to DB2 Version 10.5 Fix Pack 7. * **************************************************************** | |
| Local Fix: | |
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('') % | |
| Solution | |
This problem is first fixed in DB2 Version 10.5 Fix Pack 7. | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 19.03.2015 22.01.2016 22.01.2016 |
| Problem solved at the following versions (IBM BugInfos) | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 10.5.0.7 |
|
