PL41536: SQLCODE -551 WHEN CREATING AN INDEX ON A PARTITIONED TABLESPACE DUE TO AN INCORRECT AUTHORIZATION CHECK ON THE DEFAULT STOGROUP.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A CREATE INDEX ON A PARTITIONED TABLESPACE USING THE VCAT OPTION
  IS TERMINATED INCORRECTLY WITH MSGDSNT408I 'SQLCODE -551
  (SQLCODE551) ERROR:DM DOES NOT HAVE THE PRIVILEGE TO PERFORM
  OPERATION CREATE INDEX ON OBJECT SYSDEFLT' WHERE DM IS THE TSO
  USERID BEING GRANTED DBADM. IT IS TRUE THAT DM DOESN'T HAVE THE
  PRIVILEGE ON SYSDEFLT BUT DB2 SHOULDN'T CARE BECAUSE THE CREATE
  INDEX HAS THE 'USING VCAT...'.
  

Local fix

• CREATE THE INDEX WITH A USER WHO HAS SYSADM AUTHORITY.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: ALL.                                         *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -551 ON CREATE INDEX FOR A      *
  *                      PARTITIONED INDEX WHEN THE USER         *
  *                      DOES NOT HAVE THE PRIVILEGE TO          *
  *                      DEFINE DATA SETS IN THE SYSDEFLT        *
  *                      STOGROUP AND THE 'USING VCAT' HAS       *
  *                      BEEN SPECIFIED FOR EACH PARTITION.      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  SQLCODE -551 IS ISSUED ON A CREATE INDEX WHEN CREATING A
  PARTITIONED INDEX AND THE 'USING VCAT' IS SPECIFIED FOR
  EACH PARTITION.  THE USER DOES NOT HAVE THE AUTHORITY TO
  DEFINE DATA SETS IN THE SYSDEFLT STOGROUP.  FOR EXAMPLE:
                                                                 .
   GRANT USE OF STOGROUP SYSDEFLT TO USER1:
   REVOKE USE OF STOGROUP SYSDEFLT FROM USER1:
   COMMIT:
   CREATE DATABASE P41536D:
   GRANT DBADM ON DATABASE P41536D TO USER1:
   CREATE TABLESPACE P41536S IN P41536D NUMPARTS 3
      (PART 1 USING VCAT MYCAT FREEPAGE 0 PCTFREE 0,
       PART 2 USING VCAT MYCAT FREEPAGE 0 PCTFREE 0,
       PART 3 USING VCAT MYCAT FREEPAGE 0 PCTFREE 0)
    LOCKSIZE PAGE BUFFERPOOL BP0 CLOSE NO:
   CREATE TABLE USER1.P41536T (PART_COL CHAR(1) NOT NULL,
                               COL2     CHAR(1) NOT NULL)
    IN P41536D.P41536S:
                                                                 .
                                                                 .
  ==> CREATE UNIQUE INDEX USER1.P41536X ON USER1.P41536T (PART_COL
  ==>  CLUSTER
  ==>      (PART 1 VALUES ('D') USING VCAT MYCAT FREEPAGE 0 PCTFRE
  ==>       PART 2 VALUES ('M') USING VCAT MYCAT FREEPAGE 0 PCTFRE
  ==>       PART 3 VALUES ('Z') USING VCAT MYCAT FREEPAGE 0 PCTFRE
  ==>  SUBPAGES 1 BUFFERPOOL BP0 CLOSE NO:
                                                                 .
  'USING VCAT' INDICATES THAT THE USER DOES NOT WANT DB2 TO
  DEFINE THE DATA SETS, SINCE THAT HAVE ALREADY BEEN ALLOCATED.
  FOR PARTITIONED INDEX SPACES, EACH PARTITION IS A SEPARATE
  DATA SET AND DSNXODDL SHOULD CHECK FOR 'USING VCAT' FOR EACH
  PARTITION.  DSNXODDL ONLY CHECKED FOR THE 'USING VCAT' UNDER THE
  BASE STATEMENT (I.E. CREATE INDEX .. USING ...) AND SHOULD HAVE
  CHECKED FOR EACH PARTITION THE 'USING VCAT' CLAUSE.
                                                                 .
  NOTE: NO AUTHORIZATION CHECKING FOR THE USE OF STORAGE GROUP
        SHOULD BE DONE WHEN 'USING VCAT' IS SPECIFIED.  THIS
        PROBLEM WAS CAUSED BY INCORRECT AUTHORIZATION CHECKING.
  

Problem conclusion

• DSNXODDL HAS BEEN CHANGED TO CHECK FOR THE 'USING VCAT' CLAUSE
  UNDER EACH PARTITION WHEN CREATING A PARTITIONED INDEX.
  NO AUTHORIZATION WILL BE PERFORMED FOR THE USE OF THE DEFAULT
  STOGROUP SYSDEFLT.
                                                               .
  ** THIS APAR HAS THE SAME ROOT PROBLEM AS PL32409 (CREATING
     A PARTITIONED TABLESPACE USING VCAT)
                                                               .
  ADDITIONAL KEYWORDS: SQLCODE551 SQLUSING SQLCREATE STOGROUP
  

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:

  UL51579 UL51580

Modules/Macros

•    DSNXODDL
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R210 PSY UL51580

     UP89/05/22 P F905

• R302 PSY UL51579

     UP89/05/22 P F905

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.