Fixes are available
DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
APAR status
Closed as program error.
Error description
DB2 V9.5 may crash when compiling a query containing an OLAP function with a duplicate ORDER BY column in the window order clause. For example: SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2, C2) FROM MYTABLE The stack trace after the trap will show entries for "sqlnq_pid5arity" and "sqlnq_normalize_scalag_PB_and_OB_clause": 00002B2B16F36A61 ossDumpStackTrace + 0x007d (/opt/ibm/db2/V9.5/lib64/libdb2osse.so.1) 00002B2B16F3275E _ZN11OSSTrapFile4dumpEmiP7siginfoPv + 0x00aa (/opt/ibm/db2/V9.5/lib64/libdb2osse.so.1) 00002B2B13452E20 sqlo_trce + 0x0342 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) 00002B2B1348FB3F sqloEDUCodeTrapHandler + 0x0105 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) 00002B2B10E96C10 __invoke_dynamic_linker__ + 0x0380 (/lib64/libpthread.so.0) 00002B2B11EBEC54 _ZN9sqlnq_pid5arityEv + 0x0000 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) 00002B2B12EFBDEA _Z39sqlnq_normalize_scalag_PB_and_OB_clauseP9sqlnq_pidS0_P3loc + 0x0116 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) 00002B2B12EFF140 _Z22sqlnq_scalar_aggregatePP8stknode_i10actiontypePhP3loc + 0x3022 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) PROBLEM SUMMARY: This is a new defect in DB2 V9.5 LUW. An optimization introduced in V9.5 to remove redundant ORDER BY columns in aggregation specifications can cause an invalid memory access when the specification contains two or more duplicate ORDER BY columns. Earlier versions of DB2 are not affected by this problem.
Local fix
Where possible, rewrite your query to eliminate the duplicate column specification. For example, SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2, C2) FROM MYTABLE becomes: SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2) FROM MYTABLE
Problem summary
ERROR DESCRIPTION: DB2 V9.5 may crash when compiling a query containing an OLAP function with a duplicate ORDER BY column in the window order clause. For example: SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2, C2) FROM MYTABLE The stack trace after the trap will show entries for "sqlnq_pid5arity" and "sqlnq_normalize_scalag_PB_and_OB_clause": 00002B2B16F36A61 ossDumpStackTrace + 0x007d (/opt/ibm/db2/V9.5/lib64/libdb2osse.so.1) 00002B2B16F3275E _ZN11OSSTrapFile4dumpEmiP7siginfoPv + 0x00aa (/opt/ibm/db2/V9.5/lib64/libdb2osse.so.1) 00002B2B13452E20 sqlo_trce + 0x0342 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) 00002B2B1348FB3F sqloEDUCodeTrapHandler + 0x0105 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) 00002B2B10E96C10 __invoke_dynamic_linker__ + 0x0380 (/lib64/libpthread.so.0) 00002B2B11EBEC54 _ZN9sqlnq_pid5arityEv + 0x0000 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) 00002B2B12EFBDEA _Z39sqlnq_normalize_scalag_PB_and_OB_clauseP9sqlnq_pidS0_P3loc + 0x0116 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) 00002B2B12EFF140 _Z22sqlnq_scalar_aggregatePP8stknode_i10actiontypePhP3loc + 0x3022 (/opt/ibm/db2/V9.5/lib64/libdb2e.so.1) Only DB2 V9.5 is affected.
Problem conclusion
First fixed in DB2 UDB Version V9.5 fixpak 2
Temporary fix
When possible, rewrite the query to eliminate the duplicate column specification. For example, SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2, C2) FROM MYTABLE becomes: SELECT ROW_NUMBER() OVER(PARTITION BY C1, ORDER BY C2) FROM MYTABLE
Comments
APAR Information
APAR number
LI72795
Reported component name
DB2 UDB WSE LIN
Reported component ID
5765F3504
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2007-11-30
Closed date
2008-05-07
Last modified date
2008-05-07
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 WSE LIN
Fixed component ID
5765F3504
Applicable component levels
R950 PSN
UP
Document Information
Modified date:
16 October 2021