IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PI64476

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-06-21

  • Closed date

    2016-08-04

  • Last modified date

    2016-09-02

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



Document information

More support for: DB2 for z/OS

Software version: B10

Reference #: PI64476

Modified date: 02 September 2016


Translate this page: