Fixes are available
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
APAR status
Closed as program error.
Error description
Signal #5 (SIGTRAP) error happens at sqlnr_reorder_IJ_qgm() in db2agent when a query below is issued. In this case, you see this signal in the db2agent trap file as follows. Signal #5 (SIGTRAP): si_code is 8 (SI_UNDEFINED:siginfo_t contains partial information.) si_pid is 0, si_uid is 0, si_value is 00000000 The following stack trace is the eye-catcher for this APAR. 0x0900000009260260 pp__17sqlnq_qunqncchildFv + 0xC 0x0900000009276F24 sqlnr_reorder_IJ_qgm__FP9sqlnq_qunP9sqlnq_oprPP13sqlnq_deplistPi P3loc + 0x1F60 0x0900000009115F2C sqlnr_qrwprep_phase2__FP3locPi + 0xA48 0x090000000908C2B8 sqlnr_exe__FP9sqlnq_qur + 0x10A0 0x090000000DCC3078 sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_compi leModeT3P14sqlrr_cmpl_enviT7PP9sqlnq_qur + 0x5724 0x090000000DCBD8D0 sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_compi leModeT3P14sqlrr_cmpl_env + 0x64 0x090000000B3C6BBC sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P16sqlra _cached_varPiPUl + 0x14CC 0x090000000B420F00 sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUs UiPUcT5T4UcP14sqlra_cmpl_enviPiT11_T12_N311_T12_P1 4SQLP_LOCK_INFOPP16sqlra_cached_varT12_PUlb + 0xACC 0x090000000B3BFA60 sqlra_get_var__FP8sqlrr_cbiPiT2bT3 + 0xE70 0x090000000B9CCEE0 sqlrr_prepare__FP14db2UCinterfaceP13UCprepareInfo + 0x1F8 ---------------------------------------- A sample query of reproduction -- Table and Primary key definition create table t1 (pk int not null primary key, c12 int); create table t2 (c21 int, c22 int); create table t3 (c31 int, c32 int); create table t4 (c41 int, c42 int); -- The simplest repro query found so far SELECT 1 FROM T2 LEFT OUTER JOIN T3 ON c21 = c31 INNER JOIN T1 ON c22 = pk AND c12 <> 1 WHERE c22 IN (SELECT f1.pk FROM T1 f1 LEFT OUTER JOIN T1 f2 ON f1.c12 = f2.pk WHERE EXISTS (SELECT * FROM T4 WHERE c41 = f2.pk)) AND c22 IN (SELECT f1.pk FROM T1 f1 LEFT OUTER JOIN T1 f2 ON f1.c12 = f2.pk WHERE f1.pk = 2 OR f2.pk = 2) ; ---------------------------------------- Conditions to hit the trap 1. T1 must have primary key. Call that column pk. 2. Query outer block should have outer join and inner join at the same time. 3. The inner join has join pred "pk=c22", while c22 are involved in two (or more) IN pred. Call IN pred subquery INSQ1 and INSQ2. The inner join should have a local pred, "c12 <> 1". This local pred can not be equalty pred. 4. Both INSQ1 and INSQ2 should have outer join and output pk. 5. INSQ1 has correlated subquery, i.e. EXISTS pred in the query. 6. INSQ2 has OR pred whose subterms involve columns from both row preserving side and null producing side. For example, f1.pk = 2 OR f2.pk = 2.
Local fix
Replace "c22 IN " with "pk IN ". The above sample query can be rewriten to: SELECT 1 FROM T2 LEFT OUTER JOIN T3 ON c21 = c31 INNER JOIN T1 ON c22 = pk AND c12 <> 1 WHERE pk IN (SELECT f1.pk FROM T1 f1 LEFT OUTER JOIN T1 f2 ON f1.c12 = f2.pk WHERE EXISTS (SELECT * FROM T4 WHERE c41 = f2.pk)) AND pk IN (SELECT f1.pk FROM T1 f1 LEFT OUTER JOIN T1 f2 ON f1.c12 = f2.pk WHERE f1.pk = 2 OR f2.pk = 2) ;
Problem summary
Instance crash happens on a query sql which has the outer join and inner join with a join predicate "primary key=<column>" at the same time, and of which table has primary key, two IN predicates following outer JOIN. Workarond of this issue is to use a primary key in two IN predicates.
Problem conclusion
Problem was the first fixed in Version 9.1 FixPak 5
Temporary fix
Comments
APAR Information
APAR number
JR27383
Reported component name
DB2 UDB EXE WIN
Reported component ID
5724E4901
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2007-09-05
Closed date
2008-06-27
Last modified date
2008-06-27
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 UDB EXE WIN
Fixed component ID
5724E4901
Applicable component levels
R910 PSN
UP
Document Information
Modified date:
12 October 2021