Control Center Operations Guide for VSE

How the DBSPACE Reorganization Tool Tool Works

When you choose full DBSPACE reorganization, Control Center generates and submits a job to:

1. Link and establish communication with the target server.
2. Connect as user SQLREORG.
3. Verify the availability of the new DBSPACE (if specified).
4. Gather system catalog information about the specified DBSPACE and create corresponding DDL statements in the Control Center Database Services Utility (DBSU) command file:
   1. Table create statements
   2. Table comments
   3. Column comments
   4. Table reload statements
   5. Referential integrity constraints
   6. Unique column definitions
   7. Index create statements
   8. Table column grants
   9. Table grants
   10. View creates/grants/comments/labels
   11. Package rebind statements
5. Unload the DBSPACE data to the specified disk or tape.
6. Execute the SQLDBSU command file from the Database Services Utility to reorganize the DBSPACE and rebind any dependent packages.
7. Update the SQLMAINT table with the date, time, and duration of the reorganization (see Chapter 5, DBSPACE Analysis Tools).

In order to retain hierarchical dependencies, Control Center issues all grants in the same chronological order in which they were originally issued.

In order to grant authority to an object, the grantor must first connect as the user who originally issued the grant. Therefore, the program must gather database connect passwords for all grantors. If a grantor does not have a connect password, a temporary password is assigned and later removed.

The database server does not remove grant information from the system catalogs when a user is removed from the SYSTEM.SYSUSERAUTH table. Consequently, the REORG job may need to connect as a nonexistent user in order to re-establish a grant. If this situation occurs, Control Center temporarily grants connect authority to the user and later revokes it.

Operational Note:

In some cases (such as a reload failure), temporarily granted IDs will not be revoked from the database. You should revoke these IDs at some point in time. The IDs are identified by the starting letters REOnnnnn (where nnnnn is a random number).