DB2 FOR LUW / DB2FORLUW / 970 - DB2

On rare ocasions, stored procedure creation may crash the engine 
with the following stack: 
 
Failing instruction at 00000000035BB49B offset: 
 
0000000000000067 in <sqlrxcp2> <sqlrxcmp.C:1163> 
 
 
 
 
 
offset: 0000000000000067 in <sqlrxcp2> <sqlrxcmp.C:1163> 
 
offset: 00000000000000DB in <sqlzSortZvalueArrayCheckPresorted> 
<sqlzValueUtil.C:292> 
 
offset: 0000000000000056 in <sqlzSortZvalueArray> 
<sqlzValueUtil.C:182> 
offset: 00000000000002CB in <sqlngBuildInSetBinaryOp> 
<sqlng_opr.C:23507> 
 
offset: 0000000000000065 in <sqlngBuildInList> 
<sqlng_opr.C:23247> 
offset: 0000000000000858 in <sqlng_process_parse_tree_node> 
<sqlng_par.C:1842> 
 
offset: 00000000000004FA in <sqlng_walk_BF_chain> 
<sqlng_par.C:589> 
offset: 000000000000010E in <sqlng_build_thread> 
<sqlng_aux.C:825> 
offset: 000000000000039B in <sqlng_build_CASE_op> 
<sqlng_opr.C:13712> 
offset: 000000000000174A in <sqlng_process_parse_tree_node> 
<sqlng_par.C:1208> 
 
offset: 00000000000004FA in <sqlng_walk_BF_chain> 
<sqlng_par.C:589> 
offset: 000000000000010E in <sqlng_build_thread> 
<sqlng_aux.C:825> 
offset: 0000000000000245 in <sqlng_build_FLTR_op> 
<sqlng_opr.C:5889> 
offset: 0000000000000140 in <sqlng_process_filter_op> 
<sqlng_opt.C:13551> 
 
offset: 00000000000002D4 in <sqlng_process_mate_op> 
<sqlng_opt.C:1762> 
offset: 00000000000001A2 in <sqlng_process_pipe_op> 
<sqlng_opt.C:1366> 
offset: 000000000000029F in <sqlng_process_return_op> 
<sqlng_opt.C:1051> 
offset: 00000000000000F8 in <sqlng_build_thread> 
<sqlng_aux.C:646> 
offset: 0000000000001308 in <sqlng_main> <sqlng_exe.C:1635> 
 
offset: 0000000000002850 in <sqlnn_cmpl> <sqlnn_cmpl.C:3574> 
 
offset: 000000000000003C in <sqlnn_cmpl> <sqlnn_cmpl.C:525> 
 
offset: 00000000000002E7 in <sqlrr_cmpl> <sqlrr_cmpl.C:196> 
 
offset: 000000000000058D in <sqlrr_compile> 
<sqlrr_req_nonsql2.C:3328> 
offset: 000000000000019A in <psmPackageGen::addStatement> 
<psm_packagegen.C:1175> 
 
This situation is most likely to happen if there are host 
variables "constant" in the procedure text. 
    declare     cYES   CHAR(1)  default 'Y'; 
    declare     cNO    CHAR(1)  default 'N';

**************************************************************** 
* USERS AFFECTED:                                              * 
* All using stored procedure with variable assignments within  * 
* the variable definition section                              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* see error description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to version 9.7 fixpack 4                             * 
****************************************************************

Replacing the local variable by its equivalent literal value can 
circumvent the issue.

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 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 version 9.7 Fixpack 4

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC72945 
follow-up : 

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

23.08.2010
29.04.2011
29.04.2011

9.7.FP4