PI72726: ACCELERATED QUERY WITH VIEW THAT USES KEYWORD ROWID AS COLUMN NAME FAILS WITH SQLCODE901N FROM THE ACCELERATOR

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Accelerated query that references a view having keyword ROWID
  as a column name fails with SQLCODE901N from the accelerator.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 V11 for z/OS users of DB2 QUERY      *
  *                 ACCELERATION and queries for a table or view *
  *                 that use any of the following as column      *
  *                 names:                                       *
  *                  ROWID , CTID , OID , XMIN , CMIN , XMAX ,   *
  *                  CMAX , TABLEOID , DATASLICEID ,             *
  *                  CREATEXID , and DELETEXID                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: User requested QUERY ACCELERATION for   *
  *                      a query referencing a View that is      *
  *                      defined with a column named ROWID , and *
  *                      the query failed on the Accelerator     *
  *                      with SQLCODE -901 or -904 and message   *
  *                      text token as follows:                  *
  *                                                            . *
  *                      "Attribute 'ROWID' has a name conflict. *
  *                       Name matches an existing system ..."   *
  *                                                              *
  *                      The query should not have failed on     *
  *                      the Accelerator.                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  User requested QUERY ACCELERATION for a query on a View that is
  defined with a column named ROWID, and the query failed on
  the Accelerator returning SQLCODE901 or SQLCODE904 with message
  text token --
     "Attribute 'ROWID' has a name conflict. Name matches
      an existing system ..."
                                                                 .
  The query on the View should not have failed on the Accelerator.
  The View was a 'materialized' view.  A basic query on a 'merged'
  view or on a table using ROWID as column name does not fail.
                                                                 .
  However, the same failure can also happen for a query using
  a table expression or a Common Table Expression ( CTE ).
                                                                 .
  The failure occurs when using any of the following as column
  names in the failing contexts previosuly mentioned:
     ROWID, CTID, OID, XMIN, CMIN, XMAX, CMAX, TABLEOID, CMAX,
     TABLEOID, DATASLICEID, CREATEXID, or DELETEXID
                                                                 .
  These names are considered by the Accelerator database to be
  keyword and function names, and are not allowed as column names.
                                                                 .
  To allow queries that use these as column names to be
  successfully accelerated, DB2 should transform these names by
  adding the prefix ' ' to the column name before the query is
  sent to the Accelerator, to make the column name different from
  the Accelerator database keyword or function name.
  DB2 correctly does this transformation for a query on a table or
  'merged' view, but DB2 did not do this transformation for
  a query on a 'materialized' view, or when the query uses a table
  expression or Common Table Expression. Therefore, for those
  query cases, those keyword and function names remained 'as is'
  in the query text and the Accelerator database failed the query
  with SQLCODE -901 or -904 .
  

Problem conclusion

• DB2 code was changed to correctly transform the following when
  used as column names:
    ROWID, CTID, OID, XMIN, CMIN, XMAX, CMAX, TABLEOID, CMAX,
    TABLEOID, DATASLICEID, CREATEXID, or DELETEXID
  by adding the prefix ' ' to the column name in queries on
  a 'materialized' view, and for queries that use a table
  expression or a Common Table Expression (CTE).
                                                                 .
  To make this fix effective for a static query that was
  previously bound for acceleration (using bind option
  QUERYACCELERATION ), the DB2 application package must be rebound
  after application of this PTF.
                                                                 .
  Additional search keywords:  IDAAV4R1/K IDAAV5R1/K
                               SQLVIEW SQLTABLEEXPR SQLCTE
  

Temporary fix

• AI72726
  

Comments

APAR Information

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

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

  UI50297

Modules/Macros

• DSNXONZC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI50297

     UP17/09/29 P F709

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.