Enlarging the dbspace for the QMF object control tables on VM

Periodically, QMF objects might become too large for the dbspace that contains the QMF object control tables Q.OBJECT_DIRECTORY, Q.OBJECT_DATA, AND Q.OBJECT_REMARKS.

Use the DB2 for VM DBS utility to enlarge the dbspace for the QMF object control tables:

1. Archive the database so that a backup copy is available for recovery if needed.
2. Unload the dbspace to a CMS sequential file using the UNLOAD dbspace command of the DBS utility.

   Table 59 shows the dbspace names and default sizes for the QMF object control tables. Dbspace names for other QMF control tables are shown in "Appendix D. QMF Control Tables and Dbspaces Used by QMF" on page 325.

   All dbspaces for the QMF control tables are public. The sizes are given in pages, where each page is one 4,096-byte block.

   Table 59. Dbspaces for control tables that store QMF objects

   Dbspace name	Contents	Default size
   DSQTSCT1	Q.OBJECT_DIRECTORY table	256 pages
   DSQTSCT2	Q.OBJECT_REMARKS table	256 pages
   DSQTSCT3	Q.OBJECT_DATA table	5120 pages

3. Drop the dbspace using the DBS utility or ISQL.
4. Acquire a larger public space for the dbspace using either the DBS utility or ISQL. For example:

   ACQUIRE PUBLIC DBSPACE NAMED PUBLIC.DSQxxxxx
      (PAGES=xxx, PCTFREE=25, LOCK=ROW)

5. Use the DBS utility to reload the QMF object control tables into the new dbspace with the file you specified when you unloaded the tables as the new input file. Use the NEW keyword for the RELOAD dbspace command.
6. Recreate indexes for the reloaded tables using the DBS utility or ISQL. Make sure that:
   • The indexes are unique.
   • The index name for the Q.OBJECT_DIRECTORY table is OBJECT_DIRECTORYX and is keyed on the OWNER and NAME columns.
   • The index name for the Q.OBJECT_DATA table is OBJECT_OBJDATAX and is keyed on the OWNER, NAME, and SEQ columns.
   • The index name for the Q.OBJECT_REMARKS table is OBJECT_REMARKSX and is keyed on the OWNER and NAME columns.
7. Recreate views if the dbspaces for Q.OBJECT_DIRECTORY or Q.OBJECT_REMARKS were dropped. For example: To provide access to this view to all QMF users, grant SELECT authority.

   CREATE VIEW Q.DSQEC_QMFOBJS
      (OWNER, TNAME, TYPE, SUBTYPE, MODEL, RESTRICTED, REMARKS, LABEL,
       LOCATION, OWNER_AT_LOCATION, NAME_AT_LOCATION)
   AS SELECT
      A.OWNER, A.NAME, A.TYPE, SUBTYPE, MODEL, RESTRICTED,
        REMARKS, ' ', ' ', ' '
      FROM Q.OBJECT_DIRECTORY A, Q.OBJECT_REMARKS B
      WHERE A.OWNER = B.OWNER AND A.NAME = B.NAME
      AND (A.OWNER = USER OR RESTRICTED = 'N')

   to PUBLIC:

   GRANT SELECT ON Q.DSQEC_QMFOBJS TO PUBLIC

8. Alter the dbspace to allow the free space on occupied pages to e used. For example:

   ALTER DBSPACE PUBLIC.DSQTSCT1 (PCTFREE=5)

9. If you change the QMF control tables, reload the QMF SQL packages with the install exec DSQ2PREP EXEC on VM.

For more information on enlarging dbspaces, see the appropriate DB2 Server Database Administration Guide. For instructions and syntax of the DBS utility and ISQL commands, see theDB2 Server for VSE and VM Database Services Utility manual and the DB2 Server for VSE and VM SQL Reference manual.