PM29315: STARJOIN QUERY RETURNS 5 ROWS FIRST TIME, 0 ROWS SECOND TIME. STARJOIN AND SORT ARE INVOLVED. REPORTED QUERY IS JAVA.

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Starjoin query returns 5 rows first time, 0 rows second time.
    Starjoin and sort are involved. Reported query is JAVA.
    The failure occurs for a dynamic statement.
    With keepDynamic yes, when a prepared statement is executed the
    second time, an incorrect output may result when the query uses
    star join method.
    An example of failing query
    SELECT JPL_PERM.JPL_NAME        ,
           JPL_PERM.JPL_ACTIONS_LONG,
           JPL_PERM.JPL_NAME_LONG   ,
           JPL_PERM.JPL_ACTIONS     ,
           JPL_PERM.JPL_CLASS_ID    ,
           JPL_PERM_CLASS.JPL_CLASS
    FROM   SAPJG1.JPL_MOD_PC       ,
           SAPJG1.JPL_MOD_ACTN     ,
           SAPJG1.JPL_MOD_ACTN_PERM,
           SAPJG1.JPL_PERM         ,
           SAPJG1.JPL_PERM_CLASS
    WHERE  JPL_MOD_PC.JPL_NAME               = ?
       AND JPL_MOD_ACTN.JPL_MOD_PC_ID        = JPL_MOD_PC.JPL_ID
       AND JPL_MOD_ACTN.JPL_TYPE             = ?
       AND JPL_MOD_ACTN.JPL_NAME             = ?
       AND JPL_MOD_ACTN_PERM.JPL_MOD_ACTN_ID = JPL_MOD_ACTN.JPL_ID
       AND JPL_MOD_ACTN_PERM.JPL_PERM_ID     = JPL_PERM.JPL_ID
       AND JPL_PERM.JPL_CLASS_ID             = JPL_PERM_CLASS.JPL_ID
    ;
    
    
    Additonal Keywords: DB2INCORR/K
    

Local fix

  • Turn off starjoin by modifying the zparm or possibly using a
    hint.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of queries with    *
    *                 STAR JOIN.                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION: Queries with STAR JOIN can result in    *
    *                      an incorrect result set being returned  *
    *                      for subsequent runs after the first     *
    *                      run is successful.                      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    A query that uses starjoin can result in an incorrect result
    set being returned for subsequent runs after the first run is
    successful.
    
    An example of a failing query is as follows.
    
    SELECT S.PRODUCT ,
           S.LOCATION,
           S.TIME
    FROM   SALES S ,
           TIME T ,
           PRODUCT P ,
           LOCATION L
    WHERE  S.TIME     = T.ID
       AND S.PRODUCT  = P.ID
       AND S.LOCATION = L.ID
       AND T.YEAR     = 2005
       AND P.CLASS    = 'AUDIO'
       AND L.LOCATION = 'SAN JOSE';
    
    When the above query is repeatedly run in a dynamic application
    without being reprepared (using dynamic statement cache and
    KEEPDYNAMIC YES), the first run returns rows as expected but
    subsequent runs return 0 rows.
    
    In the reported case, the first run returns 5 rows which is as
    expected.  Each subsequent run returns 0 rows or an incorrect
    result set.
    
    An incorrect result set can also be returned from a static
    application when a cursor using the above query is reopened
    after an 'end-of-data' condition is detected during the previous
    FETCH processing.
    

Problem conclusion

  • The code is modified in DB2 bindtime to insure that runtime
    structures are built properly for a starjoin query. This
    will allow the first and subsequent executions to return the
    expected or correct result.
    
    Additional Keywords: SQLSTARJOIN SQLINCORR SQLDYNSTMTCACHE
                         SQLINCORROUT
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PM29315

  • 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

    2010-12-20

  • Closed date

    2011-01-12

  • Last modified date

    2011-05-06

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

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

    UK63903

Modules/Macros

  •    DSNXGRTS
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK63903

       UP11/01/28 P F101

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

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM29315

Modified date:

2011-05-06

Translate my page

Machine Translation

Content navigation