Modifying User Tables (Catalogs and Secondary Indexes)

User Guide > User Guide > Working with Databases > Modifying System Tables and User Tables > Modifying User Tables (Catalogs and Secondary Indexes)

Was this helpful?

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.

Note: You cannot modify user tables in an Actian Vector database.

Modify User Tables Dialog

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:

• General

• Options

• Output Log

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

For more information, see Modify User Tables.

General Page

This page contains the following options:

Effective User

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.

All User Tables

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

Specify Tables

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

Options Page

This page contains the following options:

Perform the Modification Online

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

Default: unchecked

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

Run Uninterrupted even if there are Errors

Runs the modification without interruption, even if errors are generated

Default: unchecked

Modify the Replicator Catalogs

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.

Default: unchecked

Wait for Database

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

Output Log

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

Modify User Tables

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

Note: You cannot modify user tables in an Actian Vector database.

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)

To modify user tables

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.

4. Select whether you want to perform the modification online.

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.

7. Click OK.

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

8. Click Close to close the dialog.

Last modified date: 04/24/2023