IBM Support

PI71126: SYSTABAUTH IS MISSING A ROW OF THE SELECT PRIVILEGE HELD BY VIEWOWNER WHERE THE VIEW REFERENCES XMLTABLE. OWNER CANT SELECT VIEW

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When a view is created referencing XMLTABLE, the view owner
    is not given the SELECT privilege on the view due to a missing
    row in the SYSIBM.SYSTABAUTH table. This may lead to SQLCODE551
    at select time.
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 11 for z/OS users who create         *
    *                 a view that references the built-in          *
    *                 function XMLTABLE then select from           *
    *                 that view                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION: View owners may receive SQLCODE -551    *
    *                      when they select from the views they    *
    *                      own, if the view definition meets the   *
    *                      following conditions:                   *
    *                                                              *
    *                        1. does not reference any base tables *
    *                           or views                           *
    *                                                              *
    *                        2. reference the built-in function    *
    *                           XMLTABLE and this XMLTABLE is the  *
    *                           first table in the definition      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    View owners may receive SQLCODE -551 when they select from the
    views they own, if the view definition meets the following
    conditions:
    
      1. does not reference any base tables or views
    
      2. reference the built-in function (BIF) XMLTABLE and this
         XMLTABLE is the first table in the definition
    
    The following illustrates such a view:
    
      CREATE VIEW V1 AS
        SELECT T.*
          FROM XMLTABLE('$x/a/b'
               PASSING XMLPARSE(DOCUMENT
               '<a><b>9<c>1</c></b><b>8<c>1</c><d>2</d></b></a>'
               ) AS "x"
            COLUMNS
              C1 VARCHAR(3) PATH 'name(.)',
              C2 VARCHAR(3) PATH 'text()' DEFAULT '0') AS T;
      COMMIT;
    
      SELECT * FROM V1;  -->  SQLCODE -551
    
    DB2 fails to insert the row in SYSIBM.SYSTABAUTH for the view
    owner to have the SELECT privilege on view V1. This leads to
    SQLCODE -551 when the view owner selects from view V1.
    

Problem conclusion

  • DB2 has been modified to insert a row in SYSIBM.SYSTABAUTH
    for the view owner at CREATE VIEW time so that the view
    owner can select from the view they own. See the details
    about the view definition described in Problem Summary.
    
    Additional keywords: SYSVIEW SYSOWNER SYSTABAUTH SQLCODE551
                         SYSCREATEVIEW SYSSELECT
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI71126

  • 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-10-21

  • Closed date

    2017-01-03

  • Last modified date

    2017-02-01

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

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

    UI43645

Modules/Macros

  • DSNXOVD
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI43645

       UP17/01/18 P F701

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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
01 February 2017