Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC71241 Status: Closed

Executing a query with left outer join and an IS NULL predicate may cause a
reverse column output or produce a SQL0901N error

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem 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.7 Fix Pack 3. 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.7 Fix Pack 3: 
 
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.7 earlier than Fix Pack 3: 
 
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
Problem Summary:
USERS AFFECTED: All 
 
PROBLEM DESCRIPTION: 
 
see ERROR DESCRIPTION 
 
PROBLEM SUMMARY: 
 
see ERROR DESCRIPTION
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.
available fix packs:
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
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

Solution
The complete fix for this problem first appears in DB2 UDB 
Version 9.7 FP3_special.  PTF number for UNIX platforms is 
_25384 and for Windows platforms is _25387. 
This SB is a pre-release of FP3a which also includes the fix.
Workaround
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.
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
17.09.2010
06.10.2010
14.10.2010
Problem solved at the following versions (IBM BugInfos)
9.7.FP3_
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.4 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.