This command can be embedded in a host language or executed interactively.
No special authorization is required.
COMPACT
{
LOCATION [AND INDEX] [NO CLUSTER] [NOSTOP] [RULES DB2]|
DIRECTORIES |
TABLE table-name [AND INDEXES] [NO CLUSTER] [RULES DB2]|
TABLES [LIKE pattern] [IN DATABASE db-name] [NOSTOP] [CLUSTER BY INDEX index-name] [RULES DB2]|
INDEXES [LIKE pattern] [IN DATABASE db-name] [NOSTOP]
}
Keyword order is significant. When used, keywords must appear in the order specified below.
| table-name | Identifies the name of the table that you want to compact. |
| index-name | Identifies the name of a table index that determines the physical order of records in the table. |
| db-name | Identifies the name of the database that you want to compact. |
| pattern | Defines pattern-matching criteria for a 2-part table name (use same pattern-matching syntax as for the LIKE Operator). For example, SYS%.SYSC% and SALES%.%. |
If the keyword LOCATION appears in the command syntax, the entire location is packed (including all tables and dictionary files), and all location indexes rebuilt. If a clustered index exists on a table, then the records in that table are also reclustered (swapped so they are arranged in the same physical order as the clustering index), unless the NO CLUSTER option is specified.
Use the RULES DB2 clause to pad char fields (non-FOR BIT DATA only) with blank characters during the compacting process. This option is useful for converting tables initially populated in XDB mode to standard DB2 format for character data.
Use the NOSTOP clause to enable the XDB Server to continue processing after an error. Without the NOSTOP clause, the server stops executing the COMPACT command as soon as it encounters the first error. With the NOSTOP clause, if the server encounters an error as it processes a table, it writes the error to a trace file then continues packing the next table. This option is useful for performing a COMPACT in unattended mode.
The keyword DIRECTORIES packs only location dictionary files to remove information about deleted data objects. Table and index files are not affected.
The TABLE keyword is used to compact a single table. The optional AND INDEXES and NO CLUSTER clauses function the same on a single table as with multiple tables (when using the TABLES keyword).
The optional CLUSTER BY INDEX clause is used to physically reorder the table records according to the index-name specified. The CLUSTER BY INDEX and NO CLUSTER clauses are mutually exclusive. If you use both CLUSTER BY INDEX and NO CLUSTER, the NO CLUSTER clause is ignored.
Use the RULES DB2 clause to pad char fields (non-FOR BIT DATA only) with blank characters during the compacting process. This option is useful for converting tables initially populated in XDB mode to standard DB2 format for character data.
If the keyword TABLES appears without the AND INDEXES clause, only the table files within the current location are compacted. Indexes are rebuilt only if a table contains deleted records. If a table contains deleted records and a clustered index, then the clustered index is rebuilt and the table records reordered (unless the NO CLUSTER clause is added).
Including the optional AND INDEXES clause forces all table indexes in the location (including all primary, unique and foreign keys) to be dropped and rebuilt. All tables with clustered indexes are therefore reclustered, unless the NO CLUSTER clause is specified.
Use the NOSTOP clause to enable the XDB Server to continue processing after an error. Without the NOSTOP clause, the server stops executing the COMPACT command as soon as it encounters the first error. With the NOSTOP clause, if the server encounters an error as it processes a table, it writes the error to a trace file then continues packing the next table. This option is useful for performing a COMPACT in unattended mode.
Use the RULES DB2 clause to pad char fields (non-FOR BIT DATA only) with blank characters during the compacting process. This option is useful for converting tables initially populated in XDB mode to standard DB2 format for character data.
The INDEXES keyword rebuilds location indexes only and leaves the tables alone. If clustered indexes exist on any tables, those tables are reclustered unless NO CLUSTER is specified.
Use the NOSTOP clause to tell the XDB Server to automatically continue to with the next index when an error occurs in the one it is processing. (If this option is not used, the entire process terminates immediately on error.) COMPACT writes the error to a trace file (XDBTRACE.OUT) before continuing to the next index. This option is useful for performing a COMPACT in unattended mode.
To compact the CUSTOMER table use the command:
COMPACT TABLE customer
To compact only the dictionary files of the current location, use the command:
COMPACT DIRECTORIES
To compact the entire current location, including the dictionary files and all tables, use the command:
COMPACT LOCATION
To compact the EMPLOYEE table, and physically reorder the records by PAYRATE column values (assuming the index payidx has already been built on the PAYRATE column of the EMPLOYEE table) use the following command:
COMPACT TABLE employee CLUSTER BY INDEX payidx
If the payidx index was built as a clustered index the command:
COMPACT TABLE employee
could be used without the explicit CLUSTER BY INDEX clause.
To rebuild all indexes that exist on the EMPLOYEE table, issue the command:
COMPACT TABLE employee AND INDEX
To compact all tables in the current location, issue the command:
COMPACT TABLES
To compact all indexes in the current log, issue the command:
COMPACT INDEXES
To compact all XDB Server system tables, issue the command:
COMPACT TABLES LIKE SYSXDB%.%
Description
XDB Server locations consist of table, index and dictionary files. When these object are altered or deleted, the files may become disorganized, increasing the amount of space occupied and the amount of time required to perform searches. To optimize table and dictionary file structures, you can pack specific location objects using the COMPACT command.
During processing, the COMPACT command must create a temporary copy of the specified file(s).. If the size of the largest file exceeds the free space on the storage device you are using, an error results, and the object being packed is restored to its original condition.