PI64476: SQLCODE4742 RC3 IF USER THAT HAS ONLY EXPLAIN AUTHORIZATION TRY TO ACCELERATE AN QUERY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE4742 RC3 if user that has only explain authorization try
  to accelerate a query:
  
  Using user1: CREATE TABLE TBL ( COL01 CHAR (10) ) ;
               GRANT EXPLAIN TO user2;
  
  Add table, load table and enable it for acceleration.
  
  Using user2: SET CURRENT QUERY ACCELERATION ALL  ;
               SELECT * FROM TBL WITH UR;
  
  return sqlcode4742 rc3:
  DSNT408I SQLCODE = -4742, ERROR:  THE STATEMENT CANNOT BE
  EXECUTED BY DB2 OR IN THE ACCELERATOR (REASON 3).
  
  Code will be changed to write
  SQLCODE = -551, ERROR:  user2 DOES NOT HAVE THE PRIVILEGE TO
           PERFORM OPERATION SELECT ON OBJECT tbl
  instead of the SQLCODE -4742 Reason 3 .
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 11 for z/OS who use QUERY   *
  *                 ACCELERATION behavior ALL and the EXPLAIN    *
  *                 privilege.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: An SQLCODE -4742 reason code 3 is       *
  *                      incorrectly issued when attempting to   *
  *                      accelerate a query using QUERY          *
  *                      ACCELERATION behavior ALL and the user  *
  *                      has only the EXPLAIN privilege and      *
  *                      no privileges for the referenced table. *
  *                      Instead, the query should have actually *
  *                      failed execution with SQLCODE -551.     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  User attempted to accelerate a dynamic query using QUERY
  ACCELERATION behavior ALL, but the user only had the DB2
  privilege EXPLAIN and no privileges on the table that is
  referenced in the query.
                                                                 .
  The PREPARE of the dynamic query failed incorrectly with
  SQLCODE -4742 RC03.  Given the user had only the EXPLAIN
  privilege, the PREPARE of the dyn query should have succeeded
  with SQLCODE +4726 instead, and then properly fail any attempt
  to OPEN/execute the query cursor with SQLCODE -551.
  Only a DESCRIBE or EXPLAIN is permitted for a query when the
  user has only the EXPLAIN privilege. So the end result is that
  the query should indeed fail, just with SQLCODE -551 at
  execution-time, instead of -4742 at PREPARE-time.
                                                                 .
  For a static query bound using bind option EXPLAIN(ONLY), this
  invalid -4742 could occur during the BIND / REBIND PACKAGE, when
  the user has only the EXPLAIN privilege.
  Instead, the BIND / REBIND PACKAGE should succeed with
  SQLCODE +4726, but when the package is run, the query should
  fail with SQLCODE -551.
  

Problem conclusion

• DB2 code for this QUERY ACCELERATION ALL scenario was corrected
  so that SQLCODE -4742 RC3 no longer occurs when the user has
  only the EXPLAIN privilege. Instead, SQLCODE -551 is now the
  correct SQLCODE that is issued at execution for this case.
  
  Keywords:
  IDAAV4R1/K IDAAV5R1/K SQLEXPLAIN SQLCODE4742 SQLCODE551
  SQLCODE4726
  

Temporary fix

Comments

APAR Information

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

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

  UI39866

Modules/Macros

• DSNXONZO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI39866

     UP16/08/20 P F608

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.