Before getting into how to groom Netezza table and database, first let’s understand what grooming is?
Use the GROOM TABLE command to maintain the user tables by reclaiming disk space for deleted or outdated rows. You can also use GROOM TABLE command to reorganize the tables by their organizing keys columns. End user can execute DML statements such as SELECT, UPDATE, DELETE, and INSERT operations while the data grooming is running. The SELECT operations run in parallel with the grooming operations and any INSERT, UPDATE, and DELETE operations run serially between the groom steps.
Command
Syntax for groom table:
GROOM TABLE <name> <mode-choice> <reclaim-choice>
Syntax for grooming database using nzreclaim:
nzreclaim [options]
GROOM TABLE replaced this option but can be used in grooming database
Where
<mode-choice> = RECORDS READY | RECORDS ALL | PAGES ALL | PAGES START | VERSIONS
<reclaim-choice> = RECLAIM BACKUPSET {NONE | DEFAULT | <backupsetid>}
Meaning:
- RECORDS READY – Reclaim and reorganize records in the table that is not groomed and those tables previously groomed but marked for re-grooming. This is the default for clustered base tables.
- 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
For example, Grooming table command;
TESTDB.TEST1(USER1)=> GROOM TABLE Test_table RECORDS ALL;
Groom database using nzreclaim;
nzhost:/data/home/user1>nzreclaim -records -db NZ_DATABASE;
Groom using Aginity
You can perform the grooming operation using aginity SQL development tool for Netezza. Below are the some of the options that are available in Aginity
Groom Database in Aginity
Click on the objects -> Databases -> finally, select “Groom Database…”
Select tables (or check all tables to grooming database) in particular schema to be groomed and press “start” button (available next to connect) to initiate grooming. 
Groom Table in Aginity
You can generate groom query on the individual tables in Aginity. Follow below step to get grooming query
Right click on table name -> advanced->Groom
Change the grooming option, choose one from list
You will get pop up saying groom query generated successfully
check query and execute to groom table query
Table Lock during Netezza Grooming operation
There is some discussion on Netezza grooming and table locks. When you start grooming process Netezza perform number of steps to process data on table in each data slices. During this process, all DML operations are locked on tables. Only SELECT can be performed.
Conclusion is, grooming a table will lock the table for all operations (INSERT, UPDATE, and DELETE) but SELECT.
Also Read:
- nzsql command and its Usage
- Netezza Generate Statistics and Best Practices
- Working with Netezza Zone Maps
- Netezza Skew and How to Avoid it
- How Netezza Update Records in Table?
- Netezza SET CATALOG command