IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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:

    IC71241 IC71333

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":"BU053","label":"Cloud \u0026 Data Platform"},"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