Modifying User Tables (Catalogs and Secondary Indexes)

You may modify user tables (catalogs) of user databases to their currently defined storage structure and recreate any secondary indexes currently defined. Running usermod on a regular basis or when tables have excess overflow pages improves the performance of query processing.

The following conditions must be met before you can modify user tables:

• The Instance Explorer must be connected to the installation in which the database resides (see Connect to an Actian Database Instance).

• A user database node must be selected in the Instance Explorer.

The Modify User Tables dialog lets you set options for modifying user catalogs of a database to its currently defined storage structure. This dialog contains the following pages:

Many of the options on this dialog are mutually exclusive, meaning you may choose only one.

Specifies the effective user name for the session. Valid only for a privileged user, DBA, or sessions that have the db_admin database privilege.

Some commands, including Backup Database (see Backup Database Dialog), Restore Database (see Restore Database Dialog), Verify Database (see Verify Database Dialog), Create Database (see General Page (New Database)), and Delete Database (see Delete Database Dialog), restrict the use of the effective user option to privileged users.

Note: This option does not assume the group of the effective user.

Specifies that all user tables in the database be modified to their defined storage structure

Lets you choose which user tables to modify to their defined storage structure. Click the Tables button to select the user tables to modify.

Performs the modification online. This option is equivalent to the WITH CONCURRENT_UPDATES option on the MODIFY statement in SQL.

Note: During an online modify operation, normal read and update access to the table is permitted, except for a brief period at the end, where exclusive access to the table is required.

Note: To use online modification, the database and the respective tables must be journaled (see Backup Database Dialog).

Runs the modification without interruption, even if errors are generated

Runs the repmod utility (see “repmod command” in the Ingres Command Reference Guide) to modify the replicator catalogs also. By default, the user modification command does not modify the replicator catalogs. To include the replicator catalogs in the modification, the database must be replicated.

Modifying the replicator catalogs takes an exclusive lock on the database. Use the Wait option to specify whether repmod will wait for the database to be free if it is in use.

Specifies whether to wait until the database is free before starting the modification. This option is available only if the Modify the Replicator Catalogs option is chosen.

Default: Don’t Wait

This page displays the progress and output of the modification when you click OK on the Modify User Tables dialog.

To modify user tables (catalogs) to their currently defined storage structure and recreate any secondary indexes currently defined, the Instance Explorer must be connected to the installation that contains the database (see Connect to an Actian Database Instance).

You modify user tables using the Modify User Tables dialog (see Modify User Tables Dialog). You can open this dialog in the following ways:

• From the context menu of a user database node: DBA Utilities, Modify User Tables

• On the main menu ribbon when a user database node is selected in the Instance Explorer: Database, Database, Modify User Tables (see Database Tab)

1. Open the Modify User Tables dialog in one of the ways explained previously.

2. On the General page, specify whether you want to modify all user catalogs for the database or specific tables. If you choose to specify particular user tables, click Tables to select them.

3. Click the Options link in the “Select a page” portlet to display the Options page.

5. Choose whether you want the modification to continue running without interruption even if there are errors.

6. (For replicated databases only) Specify whether you want to modify the Replicator catalogs. Also choose whether you want to wait until the database is free before starting the modification.

The Output Log page is displayed, and output is displayed on the text page.