The REORG TABLE statement compresses the data associated with the specified
table.
Invocation
This statement can be used in an application program using the DB2 CLI
functions or issued through the CLP.
Syntax
>>-REORG TABLE--table-name--+------------+---------------------><
'-int1--int2-'
Description
- REORG TABLE table-name
- Identifies the table of the reorganization operation. The name must
identify an existing table.
- int1
- The optional minimal percentage of bytes that need to be recovered.
- int2
- The minimal number of bytes that need to be recovered for the table
compression to be executed.
Rules
- The optional values int1 and int2 must be used together
or not at all.
- The optional value int1 must be a non-negative number.
- The optional value int1 must be between 0 and 100.
Notes
- A table reorganization can be invoked by DB2 Everyplace internally.
- The first optional parameter is the percentage of unusable bytes that the
table must contain (i.e.10 percent means "at least 10 percent of
the space is unusable"). The second optional parameter is the number of
unusable bytes that the table must contain (i.e. 1000 would mean
"at least 1000 bytes must be unusable space). Both criteria must be met
before an actual reorganization of the table takes place.
- If there are no parameters specified, DB2 Everyplace uses default values
for these options. The default percentage is 30 and the default bytes
is 6144. Thus, "reorg table t1" is the same as "reorg table t1 30
6144".
- If the reorganization mode is set to enabled, then DB2 Everyplace will
automatically reorganize a table. If reorganization is enabled, on a
DELETE or UPDATE, a "reorg table table_name 50 30270" is executed for the
target table after the statement is executed. If reorganization is
enabled, on a DROP TABLE, a "reorg table DB2eSYSTABLES 30 10240" (also for
DB2eSYSCOLUMNS and DB2eSYSRELS) is executed at the end of the drop table
processing.
- In a C/C++ program the reorganization mode is set by using the CLI/ODBC
function SQLSetStmtAttr with the attribute SQL_ATTR_REORG_MODE. In a
JAVA program the reorganization mode is set by the DB2eStatement interface
enableReorg method. The default value is reorganization is
enabled.
- Reorganizing a table compresses the data file that contains the table by
physically reclaiming unusable space create by deletes and updates.
Then indexes for the table are updated to point to the new physical location
of the rows.
- DB2 Everyplace System Catalog base tables can be reorganized.
- No other activity should be occurring in the database while a REORG TABLE
statement is being executed.
Examples
The VNNURSE table is compressed using the default values.
REORG TABLE VNNURSE
Related reference