usermod Command—Modify Tables to Currently Defined Storage Structure

The usermod command modifies the user-defined tables of a database to their currently defined storage structure and recreates any secondary indexes that are currently defined.

Similar to the sysmod command for system catalogs, usermod is a useful utility for maintaining user tables. Running the usermod utility on a regular basis or when the table has excess overflow pages improves the performance of query processing.

You can run usermod on the whole database or on specified tables. If no specific tables are specified, all tables belonging to the user are modified. The usermod command writes its files to the II_TEMPORARY directory.

Unless the -online flag is specified, usermod takes exclusive locks on each table in turn for the time it takes to modify the table, and also takes an exclusive lock during the rebuilding of the various secondary indexes.

Important! Should the process fail for any reason, some non-persistent secondary indexes may not be rebuilt. A message is displayed indicating the location of the SQL script used to perform the modify, which you can examine to determine any missing indexes.

usermod dbname |vnode::dbname[/server_class] [-uusername] [tablename] [-online]
[-noint] [‑repmod [+w|‑w]]

Specifies the name of the database, and if required, the vnode and server_class, as described in Standard Flags and Parameters (see page Standard Flags and Parameters). Do not specify the server_class as /star if the database is an Ingres Star distributed database.

Specifies the effective user for the session, as described in Standard Flags and Parameters (see page Standard Flags and Parameters).

Specifies individual tables to be modified by usermod. The table names should be separated by spaces. If omitted, all tables in the database are processed.

Performs the modification online. The –online 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.

Runs the usermod command uninterrupted, even if there are errors.

Runs the repmod utility (see page repmod Command—Modify Replicator System Tables Storage Structure) to modify the replicator catalogs also. By default, the usermod command does not modify the replicator catalogs. To use the –repmod option, the database must be replicated.

The repmod operation takes exclusive lock on the database. Use the +w or –w flags on –repmod to specify whether repmod will wait for the database to be free if it is in use. The default is -w (do not wait).