PM72725: PANEL ADB2RT 'REVOKE TABLE PRIVILEGES' SQLCODEN526 ON ATTEMPT TO REVOKE PRIVILEGES FOR A GLOBAL TEMPORARY TABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS' panel ADB2RT 'Revoke Table
  Privileges' causes a SQLCODEN526 / SQLCODE -526 when attempting
  to revoke privileges from a GLOBAL TEMPORARY TABLE:
  
  DSNT408I SQLCODE = -526, ERROR:  THE REQUESTED OPERATION OR
  USAGE DOES NOT
           APPLY TO CREATED TEMPORARY TABLE >schema<.>GTT<
  DSNT418I SQLSTATE   = 42995 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXA09 SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = -250  0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION
  DSNT416I SQLERRD    = X'FFFFFF06'  X'00000000'  X'00000000'
  X'FFFFFFFF'
           X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
  
  as the SQL executed was:
  REVOKE ALTER,DELETE,INSERT,SELECT
     ON TABLE >schema<.>gtt<
    FROM PUBLIC
  rather than:
  REVOKE ALL TABLE >schema<.>gtt< FROM PUBLIC
  

Local fix

• Use the PROMPT feature to edit the created SQL and amend the
  statement to REVOKE ALL as you cannot revoke specific
  privileges from a GTT.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool for     *
  *                 z/OS who perform grants or revokes           *
  *                 privileges on a created global temporary     *
  *                 table.                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: When a user grants or revokes specific  *
  *                      privileges on a created global          *
  *                      temporary table, DB2 issues a           *
  *                      SQLCODE =-526 / SQLCODEN526 error:      *
  *                                                              *
  *                      DSNT408I SQLCODE = -526,                *
  *                      ERROR: THE REQUESTED                    *
  *                      OPERATION OR USAGE DOES                 *
  *                      NOT APPLY TO TEMPORARY                  *
  *                      TABLE>schema<.>GTT<.                    *
  *                      DSNT418I SQLSTATE = 42995               *
  *                      SQLSTATE RETURN CODE                    *
  *                                                              *
  *                      as the SQL executed was:                *
  *                      REVOKE ALTER,DELETE,INSERT,             *
  *                        SELECT                                *
  *                        ON TABLE >schema<.>gtt<               *
  *                        FROM PUBLIC                           *
  *                                                              *
  *                      rather than:                            *
  *                                                              *
  *                      REVOKE ALL TABLE >schema<.>gtt<         *
  *                      FROM PUBLIC                             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When a user grants or revokes specific privileges on a created
  global temporary table, DB2 issues a SQLCODE=-526 / SQLCODEN526
  error.
  

Problem conclusion

• Problem has been resolved. When using the GRANT command on
  panel ADB21T 'Tables, Views, and Aliases', if a created global
  temporary table is listed on the panel, the DB2 Administration
  Tool will issue warning message ADB397W on panel ADB2GT 'Grant
  Table Privileges'.  The new warning message will be:
  
  ADB397W
  >table-name< is a created temporary table. Only ALL or ALL
  PRIVILEGES can be granted to a created temporary table.
  
  Explanation:
  The GRANT command operates on the entire list of tables that
  is on the Tables, Views, and Aliases panel. When different
  types of tables are listed on the panel, the GRANT command will
  fail if any known restriction applies to any of the listed table
  
  System action:
  Processing continues.
  
  User response:
  Issue the GR line command for each table. Alternatively, you
  can use a different table filter on the Tables, Views, and
  Aliases panel so that only created temporary tables are listed.
  

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:

Modules/Macros

•    ADB2AT   ADB2AT0  ADB2AUD  ADB2AUS  ADB2G
  ADB2GGEN ADB2GT   ADB2RALL ADB2RT   ADB2UA   ADB21A   ADB21T
  ADB39
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK91054

     UP13/01/22 P F301

• RA20 PSY UK91055

     UP13/01/22 P F301

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.