Fixes are available
APAR status
Closed as program error.
Error description
A query using a left outer join with an IS NULL predicate on one of the columns, from the null producing side (manual version of anti-join), may produce the following symptoms which are first introduced in DB2 Version 9.5 Fix Pack 6. This problem may happen only if the outer join and IS NULL predicates is done as the last operation in the query. 1. The result of the output columns in the query may be reversed eg. CREATE TABLE T1 ( "C1_PARENT_ID" INTEGER NOT NULL , "C2_CHILD_ID" INTEGER NOT NULL ); CREATE TABLE T2 ( "C1_ID" INTEGER NOT NULL , "C2_FLAG" CHAR(1) NOT NULL ); insert into T1 values (1,11),(2,22),(3,33); insert into T2 values (11,'X'),(22,'X'),(33,'X'),(44,'X'),(55,'X'); RESULT ON DB2 Version 9.5 Fix Pack 6: SELECT T2.C1_ID, T2.C2_FLAG FROM T2 LEFT OUTER JOIN T1 ON T2.C1_ID = T1.C2_CHILD_ID WHERE T1.C1_PARENT_ID IS NULL C1_ID C2_FLAG ------------- ----------- x'58' 44 x'58' 55 RESULT ON DB2 Version 9.5 earlier than Fix Pack 6: SELECT T2.C1_ID, T2.C2_FLAG FROM T2 LEFT OUTER JOIN T1 ON T2.C1_ID = T1.C2_CHILD_ID WHERE T1.C1_PARENT_ID IS NULL C1_ID C2_FLAG ----------- ------- 55 X 44 X 2. The query may fail to execute due to 'invalid svar len' error. The following error message will be returned: SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "Describe: Invalid svar len".) SQLSTATE=58004 Db2diag.log will show an entry similar to this one: 2010-09-10-10.48.35.198352+060 I146072A857 LEVEL: Severe PID : 667818 TID : 11114 PROC : db2sysc 0 INSTANCE: xxxxxxx NODE : 000 DB : xxxxxxx APPHDL : 0-2434 APPID: 172.22.212.98.25262.10091009483 AUTHID : xxxxxxxx EDUID : 11114 EDUNAME: db2agent (xxxxxxx) 0 FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:300 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 26 sqlerrmc: Describe: Invalid svar len sqlerrp : SQLRA003 sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0xFFFFFDA8 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: Stack trace may be similar to the following: ------Function + Offset------ pthread_kill + 0xB0 sqloDumpEDU + 0x74 sqldDumpContext__FP8sqeAgentiN42PCcPvT2 + 0x118 sqldDumpContext__FP8sqeAgentiN42PCcPvT2@glueAA5 + 0x94 sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x358 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x24 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x120 sqlrrSqlCode + 0x338 sqlrrSqlCode@glue906 + 0xA4 sqlraFixupSqlDD__FP8sqlrr_cbPUcP14dataDescriptorl + 0x420 sqlra_fill_var__FP8sqlrr_cbP14sqlrr_cmpl_envP5sqlcaUcbP16sqlra_c ached_var + 0xB8C sqlra_fill_var__FP8sqlrr_cbP14sqlrr_cmpl_envP5sqlcaUcbP16sqlra_c ached_var@glueDAF + 0x78 sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P16sqlra _cached_varPiPUl + 0x92C sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi T4PUcT4UsUcP14sqlra_cmpl_enviPi sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x444 sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0x138
Local fix
Perform the following action: db2set DB2_ANTIJOIN=[NO | OFF] and then restart the DB2 instance. Setting the above registry variable may impact performance of other queries using NOT EXISTS / NOT IN predicates.
Problem summary
USERS AFFECTED: All PROBLEM DESCRIPTION: see ERROR DESCRIPTION PROBLEM SUMMARY: see ERROR DESCRIPTION
Problem conclusion
The complete fix for this problem first appears in DB2 UDB Version 9.5 FixPak 6a.
Temporary fix
Perform the following action: db2set DB2_ANTIJOIN=[NO | OFF] and then restart the DB2 instance. Setting the above registry variable may impact performance of other queries using NOT EXISTS / NOT IN predicates.
Comments
APAR Information
APAR number
IZ84783
Reported component name
DB2 UDB ESE AIX
Reported component ID
5765F4100
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
YesSpecatt / Pervasive
Submitted date
2010-09-11
Closed date
2010-10-06
Last modified date
2010-10-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 ESE AIX
Fixed component ID
5765F4100
Applicable component levels
R810 PSN
UP
R820 PSN
UP
R910 PSN
UP
R950 PSN
UP
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950"}]
Document Information
Modified date:
06 October 2021