13.3.4 Example 4: Reorganizing a schema
(1) Use SQL to check for RDAREAs to be reorganized
SELECT DISTINCT(RDAREA_NAME) FROM MASTER.SQL_TABLES 1
WHERE TABLE_SCHEMA=USR01 AND RDAREA_NAME IS NOT NULL;
SELECT DISTINCT(RDAREA_NAME) FROM MASTER.SQL_DIV_TABLE 2
WHERE TABLE_SCHEMA=USR01;
SELECT DISTINCT(RDAREA_NAME) FROM MASTER.SQL_INDEXES 3
WHERE TABLE_SCHEMA=USR01 AND RDAREA_NAME IS NOT NULL;
SELECT DISTINCT(RDAREA_NAME) FROM MASTER.SQL_DIV_INDEX 4
WHERE TABLE_SCHEMA=USR01; |
- Explanation
- Retrieves RDAREAs that store non-row-partitioned tables.
- Retrieves RDAREAs that store row-partitioned tables.
- Retrieves RDAREAs that store non-row-partitioned indexes.
- Retrieves RDAREAs that store row-partitioned indexes.
(2) Use the pdhold command to shut down RDAREAs to be reorganized
pdhold -r RDAREA1,RDAREA2,RDAREA3,RDAREA4,... |
(3) Create the control statements file for the pdrorg command
The following are the contents of the control statements file (/pdrorg/rorg01):
(a) HiRDB/Single Server
unload /pdrorg/unfile1 1
idxwork /pdrorg/idxwork 2
sort /sortwork,8192 3 |
- Explanation
- Specifies the name of the unload data file.
- Specifies the name of the directory in which an index information file is to be created. The index information file is created under this directory.
- Specifies the name of the work directory for sorting.
(b) HiRDB/Parallel Server
unload bes1:/pdrorg/unfile1 1
idxwork bes1 /pdrorg/idxwork 2
sort bes1 /sortwork,8192 3
idxwork bes2 /pdrorg/idxwork 4
sort bes2 /sortwork,8192 5 |
- Explanation
- Specifies the name of the unload data file. Because the -g option is assumed, the unload data file is created at one location.
- Specifies the name of the directory in which an index information file is to be created (for bes1). The index information file is created under this directory.
- Specifies the name of the work directory for sorting (for bes1).
- Specifies the name of the directory in which an index information file is to be created (for bes2). The index information file is created under this directory.
- Specifies the name of the work directory for sorting (for bes2).
(4) Use the pdrorg command to reorganize the table
pdrorg -k rorg -t USR01.all /pdrorg/rorg01 |
- Explanation
- Because indexes (INDEX1 and INDEX2) are to be re-created simultaneously, the -i option is omitted and the indexes are created in the batch index creation mode.
- -k: Specifies rorg for reorganization.
- -t: Specifies the authorization identifier of the schema that is to be reorganized.
- /pdrorg/rorg01: Specifies the name of the control statements file for the pdrorg command created in step (2).
- Remarks
- When reorganization by schema is specified, the -j option (reorganization when LOB data is present) and the -g option (unload data file standardization) are assumed.
(5) Back up the RDAREAs that were reorganized
(6) Use the pdrels command to release RDAREAs from shutdown status
pdrels -r RDAREA1,RDAREA2,RDAREA3,RDAREA4,... |
It is recommended that after the command has executed you check whether or not the execution results are correct. For details on how to check command execution results, see the manual HiRDB Version 8 Command Reference.