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

Obtain the fix for this APAR.

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 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.