Maintaining Netezza Performance Server 11.2.2.X with AutoMaint

Deployment options: IBM Netezza Appliance

With Netezza Performance Server 11.2.2.X, you can use the AutoMaint feature, which automates select maintenance activities.

With AutoMaint, you can schedule groom and genstats operations so that a database administrator does not have to run them periodically on their own.

Maintenance jobs

Learn about the different types of maintenance jobs.

Genstats

The AutoMaint genstats maintenance job generates statistics for any table in the system that has out-of-date statistics.

Groom

With this maintenance job, AutoMaint automatically issues the correct GROOM TABLE variant, depending on the type of table. The following settings are the criteria that determine whether a table of a specified type needs grooming.

Collapse

A feature that cleans up versioned tables.

SET AUTOMAINT GROOM MAXIMUM VERSIONS TO <num versions>

Specifies the number of versions that a table can have before AutoMaint collapses the table into a single version.

This applies to versioned tables, which are the result of adding and or dropping table columns.

The default is 2.

Organize

A feature that organizes CBT tables.

SET AUTOMAINT GROOM ORGANIZE THRESHOLD TO <1-99>

Specifies the threshold percentage at which the table is considered unorganized enough to require organizing. For example, if the threshold is set to 5, the table is reorganized when less than 95% of it is organized.

The default value is 4.

This applies to non-versioned clustered base tables (CBTs).

Reclaim

A feature that reclaims deleted space.

SET AUTOMAINT GROOM RECLAIM THRESHOLD TO <1-99>

Specifies the threshold percentage of reclaimable deleted records that need to be present in a table before AutoMaint grooms the table.

The default value is 4.

This applies to non-versioned tables that are not CBTs.

Common settings

Suspend materialized views

SET AUTOMAINT GROOM SUSPEND MATERIALZIED VIEW TO <on|off>

If set to on, AutoMaint suspends active materialized views before grooming a table that needs to be groomed. After the table is groomed, AutoMaint refreshes these materialized views.

If set to off, a table with active materialized views is skipped and is not groomed.

The default value is on.

Default backup sets

Each GROOM TABLE command that is issued by AutoMaint uses the default backup set for the database of the specified table to determine, which deleted rows can be reclaimed. The default backup set is generally determined automatically by Netezza Performance Server. If a database has multiple backup sets, you can override the default to be a specific backup set by running:

ALTER DATABASE <dbname> GROOM BACKUPSET {<backupsetid>|DEFAULT}

Configuring AutoMaint

To configure AutoMaint, use SQL as the admin user or a user with the MANAGE SYSTEM privilege.

Set AutoMaint settings:

SET AUTOMAINT <maintenance job> <setting> <value>

Display AutoMaint settings:

SHOW AUTOMAINT <maintenance job> <setting>

To run the SHOW AUTOMAINT command, you do not need any privileges. You can issue the command as any user, from any SQL client anywhere.

The possible choices for <setting> and the default values of the settings appear in the description of a select SET AUTOMAINT command.

Scheduling maintenance jobs

Specify a day of the week, or ALL, or a comma-separated list of days, for which the given maintenance job is supposed to run. You can input 1 (Sunday) through 7 (Saturday):
```
SET AUTOMAINT <maintenance job> DAY <day list>
```
If DAY is set to NONE, the maintenance job does not run.

Specify the start time for a maintenance job:

SET AUTOMAINT <maintenance job> TIME <start time>

Specify the duration of a maintenance job:
```
SET AUTOMAINT <maintenance job> DURATION <minutes>
```
AutoMaint runs the job for a set number of minutes. The next time that it runs the job, AutoMaint picks up where it left off. If DURATION is set to NONE, Automation runs until it has worked on all tables in the system.

Turning off maintenance jobs

Turn off groom or genstats jobs:

SET AUTOMAINT <maintenance job> DAYS NONE