PM40770: SQLCODE811 ISSUED BY DSNXREMS FOR A SELECT WITH RID LIST ACCESS WHEN THE SUBQUERY WITH A COLUMN FUNCTION RETURNS AN EMPTY RESULT

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • SQLCODE811 issued by DSNXREMS at SQLERRD1 -140 for a SELECT
    using rid list access when the subquery with a column function
    returns an empty result
    .
    V9 resulted in RC00E20003 or RC00E20016 with part of the code
    this V10 APAR is fixing. In V10, RC00E20003 and RC00E20016 are
    also possible. V9 APAR PM40789 fixes this code error.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of an scalar       *
    *                 subselect with a column function.            *
    ****************************************************************
    * PROBLEM DESCRIPTION: SQLCODE811 was incorrectly issued for   *
    *                      a scalar subselect. The scalar          *
    *                      subselect contains a column function.   *
    *                      An infinite loop or storage problem     *
    *                      is also a possible symptom of this      *
    *                      problem.                                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
      The problem occurs processing a subselect with a column
    function where there are no rows that qualify for the subselect.
    End of file is not set correctly and two rows are returned
    from the subselect instead of one.  The -811 sqlcode is
    incorrectly issued.
      The problem occurs with a rid list access path where the
    rids are written to a workfile.  There are no rows from the
    workfile that qualify when the predicates of the subselect are
    applied.  The plan table shows index access with list prefetch.
      Here is an example query where the problem may occur and
    the -811 sqlcode is incorrectly issued:
    CREATE TABLE T2
      ( C1 CHAR(8) NOT NULL WITH DEFAULT,
        C2 CHAR(8) NOT NULL WITH DEFAULT,
        C3 CHAR(2) NOT NULL WITH DEFAULT,
        DT DATE    NOT NULL WITH DEFAULT,
        TS TIMESTAMP NOT NULL WITH DEFAULT);
    
    CREATE  INDEX IX4 ON T2
           (DT,C1,TS);
      SELECT  *
          FROM T2 A
         WHERE A.C1 = 'abc'
           AND A.C2 = '123'
           AND A.C3= '00'
         AND A.TS = (SELECT  MAX(B.TS)
                           FROM T2 B
                          WHERE B.C1  = 'abc'
                            AND B.C3 = '00'
                            AND B.C2 = '123'
                            AND B.DT <= CURRENT DATE);
    
    
    The following example uses pairwise join and can result
    in an infinite loop, incorrect output and/or storage problems
    such as abends or excessive storage use.
    
    SELECT SUM(F.K6), COUNT(*)
      FROM T1 AS F, T2 AS D1, T3 AS D2, T4 AS D3
      WHERE F.K1 = D1.K1 AND F.K2 = D2.K2
        AND F.K3 = D3.K3 AND F.K5 > 5
        AND D1.I1 < 2000   AND D2.I2 < 1500
        AND D3.I3 < 1000 FOR FETCH ONLY
    

Problem conclusion

  • DB2 has been fixed to correctly check record not found for a
    rid list in a workfile where no rows qualify.
    
    ADDITIONAL KEYWORDS : SQLSETFUNCTION SQLSUBQUERY SQLPAIRWISEJOIN
                          INCORROUT SQLINCORR
                          SQLINCORROUT DB2INCORR/K
                          SQLSTORAGE DB2STGLK/K LOOP DSNXRSFN
                          SQLSUM SQLAVG SQLMAX SQLMIN SQLCOUNT
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM40770

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-06-03

  • Closed date

    2011-06-28

  • Last modified date

    2011-08-01

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

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

    UK69266

Modules/Macros

  •    DSNXREOJ DSNXRFF  DSNXRGF  DSNXROJ1 DSNXROSI
    DSNXRSFN DSNXRSGB DSNXRT1J DSNXSBUC DSNXSIND DSNXSING
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK69266

       UP11/07/13 P F107

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM40770

Modified date:

2011-08-01

Translate my page

Machine Translation

Content navigation