Using the utility statement REORG, the database administrator can reorganize the catalog space, individual user spaces or also individual base tables in user spaces. During the reorganization process, SESAM/SQL ensures that storage areas that belong together logically are located next to one another physically.
The purpose of reorganization
The blocks of the catalog space, of the individual user spaces and of the base tables are concatenated in logical order. When processing begins, the physical sequence (the ascending sequence of block numbers) and the logical sequence (the content of the chained blocks) match.
During database operations, SESAM/SQL can create new blocks if the insufficient space is available for new data in the existing blocks. The logical sequence of new and old blocks is maintained through chaining, but it ceases to match the physical sequence. This can seriously reduce access speed when a space is accessed sequentially.
Reorganization ensures that the logical and physical block sequences match.
During reorganization, SESAM/SQL takes free-space reservations into consideration that the database administrator has defined previously with the statements CREATE CATALOG, CREATE SPACE, or ALTER SPACE in the PCTFREE clause for the space in question (catalog space or user space).
Utility statement REORG [CATALOG_]SPACE
If the database administrator has previously specified a new storage group for the user space to be reorganized using the SQL statement ALTER SPACE, the user space is moved physically to this storage group during reorganization, unless otherwise stipulated by the COPY specification or the specification of a work file.
SESAM/SQL reorganizes the space into a work file. The work file can be given. Otherwise SESAM/SQL uses a standard work file whose name is formed from the name of the space file together with the suffix .REORG. The standard work file can also be created by the user; it is then used as the standard work file with the defined file features.
You can issue the sequence of statements EXPORT TABLE, DROP TABLE and IMPORT TABLE to restructure a space that contains tables and indexes in such a way that the tables and indexes are located in separate spaces. First, the relevant table is exported to an export file along with the associated indexes. The table itself is deleted with DROP TABLE. You then import the table from the export file back to the original space using IMPORT TABLE. You can also specify a separate index space for the indexes (see section “Importing a base table with IMPORT TABLE”).
A simpler procedure is possible if indexes have been created on a mere table space by SESAM/SQL according to unique constraints. These indexes can be relocated to other spaces by explicitly creating them on other spaces with CREATE INDEX. SESAM/SQL then relocates the unique constraint (UNIQUE) to the explicitly defined index and deletes the implicitly defined one.
Utility statement REORG ONLINE TABLE
SESAM/SQL reorganizes a base table by modifying and copying the blocks in the user space. As no exclusive transaction locks are required for this purpose, other DML applications can both read and modify the base table.
In the case of partitioned tables a single partition can also be reorganized. The partition is defined by the ON SPACE clause. Otherwise all partitions of the base table are reorganized one after the other.