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.

A table row that was marked as deleted as a result of a committed DELETE or UPDATE statement is reclaimable by the GROOM TABLE command if:
  • The table row is not visible to a concurrent transaction. A transaction is concurrent with another transaction if it started before the other transaction committed.
  • RECLAIM BACKUPSET NONE is specified, or the table's database does not have any backup set, or the row was deleted before the last backup in the backup set used by this GROOM TABLE command. Namely:
    • The backup set identified by the backupsetid in the command.
    • The default backup set of the table's database, which is the most recent backup set unless a GROOM BACKUPSET was specified explicitly by using the ALTER DATABASE command.

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.
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;