Maintaining Databases

This chapter discusses various database maintenance tools and techniques. Maintaining your databases keeps them in good condition and helps you to more quickly identify any problems.

Ways to View Database Objects

The DBA must make sure important database objects, such as tables and views, are available, devise a way to separate temporary objects from important objects, and keep private objects to a minimum.

In VDBA, you can view a list of database objects in the Database Object Manager window. You can view details for any object in the tree by selecting it and using the panes to the right of the tree structure. For more information, see online help.

By default, when you open a Database Object Manager window, only the objects belonging to you are visible, not the objects belonging to other users.

You can also view database objects using the help statement. This statement has various options, such as index, table, and view, to obtain information on various types of database objects. For more information, see the SQL Reference Guide.

View Database Objects that Belong to Another User

To view and work with database objects belonging to another user, you must impersonate that user (which requires the security privilege).

To impersonate another user, select that user from the Users branch in the Virtual Nodes window in VDBA and open a Database Object Manager window. The objects belonging to that user and those belonging to the DBA appear in the window, where you can view and manage them.

List All Tables and Their Owners

The iifile_info view permits you to select all tables and their owners.

For example, the following query lists all user tables not owned by the DBA:

select tablename, table_owner, table_type
  from iitables
  where table_owner != '$INGRES' and
  table_owner != 'DBA';

Ways to Delete Database Objects

Database objects, such as tables, views, secondary indexes, and synonyms, can be deleted (dropped). When you drop a table, objects that are directly dependent on that table, such as indexes and views, are automatically dropped.

In VDBA, you can perform these tasks in the Database Object Manager window. The online help topic Dropping Objects gives a generic description for dropping any type of database object. Each type of object has its own help topic, such as Dropping a Table or Dropping a View.

You can also accomplish this task using the drop statement. For more information, see the SQL Reference Guide.

If for some reason you cannot drop tables in VDBA, you can use another method. More information can be found in Verifying Databases.

Routine Database Maintenance Tips

To keep your tables in good condition, we recommend that you run the following maintenance tools periodically:

• Modify database tables periodically if they are subject to frequent updates or inserts. Frequent updates and inserts to all table structures except B-tree cause overflow data pages to be created, which are inefficiently searched.

  If you do not have enough disk space to modify a large B-tree table, modify the table to shrink the B-tree index. This improves the structure of the B-tree index pages, but does not require the amount of free disk space required by other modify options.

  For details on how to modify tables, see the chapter "Maintaining Storage Structures."

  Note: Choosing the correct storage structure for your needs makes maintaining the database easier. For a discussion of the four main storage structures, see the chapter "Choosing Storage Structures and Secondary Indexes." If the storage structure you are using is not the best one, modify it using the information in the chapter "Maintaining Storage Structures."

• Run system modification on the database if the database is active (that is, users frequently create or modify tables, views, or other database objects). Both system catalog data page overflow and locking contention is reduced by regular use of system modification. For details, see Example: Before and After Optimization in the chapter "Using the Query Optimizer."
• Use optimization to help maintain databases. When you optimize a database, data distribution statistics are collected that help queries run more quickly and use fewer system resources. We recommend that you optimize your database when its data distribution patterns change.

  Optimization cannot be run on all columns of all tables in your database. Instead, run it only on those columns that are commonly referenced in the where clauses of queries. Collecting more statistics than you need consumes extra disk space and requires the query optimizer to consume more system resources to arrive at an appropriate query execution plan.

  For details on optimization, see Database Statistics in the chapter "Using the Query Optimizer."

Note: You can set up these routine maintenance tasks to be done inside maintenance batch jobs to avoid the need to run them interactively.

Operating System Maintenance Tips

It is important for you, as the DBA, to monitor the operating system. If you are not also the system administrator, you must work closely with your system administrator so that you are aware of any operating system problems.

Ingres relies on the operating system to access data in tables. If the operating system develops problems, such as system resource shortages, lack of free disk space, or hardware errors, this can affect the responsiveness of the Ingres system and its ability to process requests on behalf of its clients.

Disk errors, memory errors, or operating system resource shortages are the problems most likely to affect the quality of operation. Most hardware errors are dependably logged by the operating system. Make sure that the system administrator is aware of your concern about the efficiency of the operating system.

The operating system offers tools to check and verify the health of the hardware. These include disk drive verification programs and diagnostic programs for memory boards.

Windows: Windows lets the system administrator check for and optionally fix problems in a file system. Free disk space and system configuration can be monitored with the Windows Diagnostics. System-wide performance data, such as CPU usage, can be monitored using the Performance Monitor. Certain system-wide errors and events are monitored in the Event Log, which can be viewed with the Event Viewer. For information on these and other administrative tools, see the Windows documentation.

UNIX: Most UNIX vendors have a fsck program to check for unreferenced disk blocks, unreferenced inodes, and inconsistencies in operating system tables. Free disk space in your file systems is easily monitored with operating system tools such as df and du. The pstat (BSD) or sar (System V) UNIX commands have options to show the use and distribution of various operating system resources. Every vendor also provides a variety of system maintenance utilities that are menu-driven and easy to use, but which are generally specific to a particular operating system vendor. Make full use of any operating system tools such as these.

VMS: VMS offers the analyze command which, among other operations, analyzes readability and validity of files and disk volumes. The show device command shows the amount of free disk space. The VMS Monitor Utility (MONITOR) monitors classes of system-wide performance data, such as CPU usage, at a specified interval. These are only a few of the system maintenance utilities that VMS provides. Consult the VMS Help facility and your VMS System Manager for more information on these and other useful operating system tools.

Verifying Databases

The Verify Database operation lets you verify the integrity of a database and repair certain table-related problems.

You can verify one or more databases by specifying an operation, and then choosing an appropriate scope and mode for that operation. Operations include:

• Checking specified tables for inconsistencies and recommending ways to repair them
• Checking database system catalogs for inconsistencies and recommending ways to repair them
• Purging temporary tables, which can be left on the disk inadvertently when the system does not have time to shut down in an orderly fashion (for example, if the machine is rebooted or stops due to power loss)
• Purging expired tables
• Dropping tables that cannot be dropped in the normal manner (for example, if the underlying disk file for the table was deleted at the operating system level) by removing all references to them from the database system catalogs
• Checking the specified databases to determine if they can be and indicates whether the user can connect to the database accessed

In VDBA, use the Verify Database dialog. For details on how to specify an operation using the Verify Database dialog, see online help.

You can also accomplish these tasks using the verifydb system command. For more information, see the Command Reference Guide.

To use the verify database operation, you must be the DBA for all the databases you want to verify, or a user with the security or the operator privilege.

Databases Shared Among Multiple Users

Follow these rules for databases that are shared among multiple users:

• Have users use only application programs to access data in the database. Discourage users from using Ingres tools, such as a terminal monitor or VDBA, to access data. Permitting users to access data only by means of an application program guarantees that the executing queries were written by an application programmer and are not ad hoc queries that can damage or delete data, or cause lock contention delays.
• Ensure that reports are run with readlock=nolock. You can do this by including all reporting tools in application programs and setting readlock there, or by running all reports from operating system scripts, which set lockmode before the report runs. Doing this avoids locking contention problems that can lead to severe concurrent performance problems in the database.