IBM Support

IC71251: Possible incorrect result on recursive views which joins to a table on a unique colum

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A SQL statement with a recursive view may yield incorrect
    results. The SQL statement uses UNION ALL joining the table onto
    a set of columns that forms a unique key. The joined table must
    be the table used in the recursive view.
    
    
    For example:
    
    CREATE TABLE RELATION(ID INTEGER, PARENTID INTEGER, FLAG
    CHAR(1));
    -- This unique index is required to produce the incorrect
    results output
    CREATE UNIQUE INDEX UK1 ON RELATION(ID);
    
    CREATE VIEW HIRARCHY(ID, PARENTID, DEPTH)
    AS
    WITH TREE
    (ID, PARENTID, DEPTH) AS
    (SELECT ID, PARENTID, 1 AS DEPTH FROM RELATION ROOT
     UNION ALL
     SELECT CHILD.ID, TREE.PARENTID, DEPTH+1 FROM TREE, RELATION
    CHILD WHERE TREE.ID = CHILD.PARENTID)
    SELECT * FROM TREE
    ;
    
    INSERT INTO RELATION VALUES (1,2,'Y'),(2,3,'N');
    
    SELECT H.ID, H.PARENTID, DEPTH
    FROM HIRARCHY H,RELATION R
    WHERE H.ID = R.ID
    AND R.FLAG = 'Y';
    
    The correct result is 2 rows.
    
    ID          PARENTID    DEPTH
    ----------- ----------- -----------
              1           2           1
              1           3           2
    
    DB2 version 9.7 Fix Pack 3 returns only the first row in this
    example which is incorrect
    
    This problem is first introduced in DB2 version 9.7 Fix Pack 3.
    

Local fix

  • If you do not have the registry variable DB2COMPOPT set,
    Perform the following action:
    db2set DB2COMPOPT=,65536
    Please note the comma preceding the number
    
    If you already have the registry variable set to DB2COMPOPT=x,y
    where  x and y are numbers that may be present together or by
    themselves
    then perform the following with appropriate values replacing x
    and z
    db2set DB2COMPOPT=x,z
    where z=65536 + y
    
    and then restart the DB2 instance.
    

Problem summary

  • Code defect has been corrected in a fix pack release.
    

Problem conclusion

  • Fixed in DB2 Version 9.7 Fix Pack 3a.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC71251

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-09-17

  • Closed date

    2010-10-05

  • Last modified date

    2010-11-22

  • APAR is sysrouted FROM one or more of the following:

    IC71248

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"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":"9.7"}]

Document Information

Modified date:
16 September 2021