GROOM TABLE

Use the GROOM TABLE command to remove outdated and deleted records from tables while allowing access to all tables in the system. The GROOM TABLE command reclaims disk space, reorganizes tables that are based on the clustered base table organizing keys, and migrates data for tables that have multiple stored versions.

Note: This command replaces the nzreclaim command.
Restriction: You cannot run the GROOM TABLE command inside a transaction block (BEGIN/COMMIT pair) or with a stored procedure.

The GROOM TABLE command processes and reorganizes the table records in each data slice in a series of steps. Users can complete tasks such as SELECT, UPDATE, DELETE, and INSERT operations while the data grooming is taking place. The SELECT and INSERT operations run in parallel with the groom steps; the UPDATE and DELETE operations run serially between the groom steps. For CBTs, the groom steps are longer than for non-CBT tables, so INSERT, UPDATE, and DELETE operations might wait for a longer time until the current step completes.

When you specify organizing keys for an existing table to make it a CBT, the new organization can affect the compression size of the table. The new organization can create sequences of records that improve the overall compression benefit, or it can create sequences that do not compress as well. Following a groom operation, your table size can change somewhat from its size under the previous organization.

Syntax

Syntax for grooming a table:
GROOM TABLE name [mode-choice] [reclaim-choice]
where
<mode-choice>:= {RECORDS READY | RECORDS ALL | PAGES ALL | PAGES START | VERSIONS} 

<reclaim-choice>:= RECLAIM BACKUPSET { NONE | DEFAULT | <backupsetid>}

Inputs

The GROOM TABLE command takes the following inputs:

Table 1. GROOM TABLE Input
Input Description
RECORDS READY Reclaim and reorganize records in the table that are not groomed and those records that were previously groomed but marked for regrooming. This is the default for clustered base tables (CBT).
RECORDS ALL Reclaim and reorganize all records in a table. This is the default for a non-CBT.
PAGES ALL Identify and mark as 'Empty' data pages in the table with no visible record to free up disk extents.
PAGES START Identify and mark as 'Empty' leading data pages in the table with no visible record, stopping when it finds a data page that is not empty.
VERSIONS Migrate records from previous table versions. Dropped columns are not displayed and added columns show default values.

The GROOM TABLE VERSIONS command turns a versioned table into nonversioned. When this happens, you can specify a nonzero DATA_VERSION_RETENTION_TIME with the ALTER TABLE command.

For more information about DATA_VERSION_RETENTION_TIME and time travel, see Getting started with time travel.

RECLAIM BACKUPSET Controls Groom and backup synchronization. You can set RECLAIM BACKUPSET to the following values:
NONE
No backup-reclaim synchronization, which means the next backup might not be able to incrementally back up the table, and require a full backup.
DEFAULT
This is the default if no reclaim-choice is specified, and uses the default backup set, if one exists, for the database for backup-reclaim synchronization.
backupsetid
Use the most recent backup in the specified backup set for backup-reclaim synchronization.

Output

The GROOM TABLE command has the following output:

Table 2. GROOM TABLE output
Output Description
NOTICE: GROOM processed <#> pages; released <#> pages; purged <#> records. Table size grew/shrunk/unchanged from <#> extents to <#> extents. The command completed successfully.

Description

You must be the admin user, the table owner, the owner of the database or schema where the table is defined, or your account must have the Groom privilege for the table or for the Table object class.

Usage

  • To migrate data for a versioned table: 
    MYDB.SCH1(USER)=> GROOM TABLE <table> VERSIONS;
  • To reclaim deleted records in a table: 
    MYDB.SCH1(USER)=> GROOM TABLE <table> RECORDS ALL;
  • To identify data pages that contain only deleted records and to reclaim extents that are empty as a result: 
    MYDB.SCH1(USER)=> GROOM TABLE <table> PAGES ALL;
  • To organize data that is not already organized in a clustered base table: 
    MYDB.SCH1(USER)=> GROOM TABLE <table> RECORDS READY;