User-Visible DBA Enhancements

Enhancements have been made to internal performance that concern auditdb utility, copydb utility, raw location support, and GatherWrite threads.

Usermod Utility

Ingres now includes a usermod utility that allows users to run the modify commands on user tables. Like sysmod, which modifies system catalogs, this utility is useful for maintaining user tables on a regular basis.

Running this utility regularly, or when the table has excess overflow pages, improves performance of user applications.

Auditdb Utility

Various enhancements to the auditdb utility required by Journal Analyzer are included:

• Specification of fully qualified table names.
• Correct formatting of the output for -aborted_transaction when used with -b and -e flags.
• Corrected -aborted_transaction flag, allowing auditdb to write correct format for BT and ET records.
• Savepoint information in the auditdb output. This is achieved by printing out the abortsave record, which contains the LSN of the aborted savepoint.
• The order of output for lsn low/high fields for the ASCII output of auditdb, allowing the high lsn to be printed before low LSN.
• Two new auditdb options: -start_lsn=<LSN> -end_lsn=<LSN> for non -all cases.

For the syntax of the auditdb command, see the Command Reference Guide.

Copydb Utility

The copydb utility is modified to include several options and flags that modify the copy.in and copy.out scripts based on user requirements. The user can specify the order in which the copy and modify statements are written to the copy.in script, for example, whether to copy the data into the tables and then run a modify statement or the other way around. Other examples include the ability to remove hard-coded paths to the copy scripts, exclusion of location names, and exclusion of user-specific permissions such as grant statements.

Raw Location Support

Ingres 2.6 adds initial support for raw data locations on UNIX platforms. Raw data locations provide dramatic performance improvements over cooked locations. In this release, only one table may occupy any given raw location. Many raw locations can exist on a single raw disk slice.

GatherWrite Threads

A new internal thread type, GatherWrite, is used by the Ingres buffer manager during operations that require the flushing of multiple buffers from the cache such as write behind, consistency points, and table purges. This feature is only available on platforms that offer writev() support. Consult the appropriate Readme file to determine whether this feature is supported on your platform.

This feature is enabled on a per-server basis using the gather_write parameter in Configuration-By-Forms. The default setting is ON.

XML Import/Export Utility

XML is as a cross-platform, software- and hardware-independent tool for transmitting information. The XML import/export utility imports and exports XML data from Ingres tables to and from XML files. For the syntax of the XML import/export utility, xmlimport, see the Command Reference Guide.

Journal Analyzer

The Journal Analyzer is a powerful graphical tool that provides an interface to the journal files. You can use the Journal Analyzer to recover data from the journals and to apply journaled transactions to other databases, both local and remote. For information on the Journal Analyzer utility, see the System Administrator Guide.

Import Assistant

The Import Assistant is a wizard that simplifies the task of importing data from a standard file format to an Ingres database. For information on the Import Assistant utility, see the online help.

Automated Creation of Location Directories

Before Version 2.6, the Ingres DBA had to manually create the directories for alternate locations as prescribed in the Database Administrator Guide. This step had to be performed prior to creating a Location with ACCESSDB, or could be deferred if Locations were created using EXEC SQL CREATE LOCATION syntax. To circumvent directory permissions problems, ACCESSDB had to be run by the Ingres user whenever Locations were created, altered, or extended.

This process is clarified and simplified in Ingres 2.6 with the following changes:

• The Ingres server performs all manipulations of Location directories. This resolves the permissions problems of earlier releases and allows any ACCESSDB user with the "maintain_locations" privilege to create, alter, or extend Locations.
• The server automatically creates Location directories when a CREATE/ALTER LOCATION statement is executed, whether by ACCESSDB or user-invoked SQL. Because only missing directories are created, the DBA retains the ability to manually create as much, or all, of the Location path as wanted before creating the Location.

Using the example from the section Create an Area in UNIX in the Database Administrator Guide, the following directories will be verified or created automatically during the execution of:

CREATE LOCATION new_loc WITH AREA='/otherplace/new_area', USAGE=(DATABASE)

Notes:

• Permissions are not changed for extant directories.
• The top-level directory "/otherplace" must exist and will not be created by the server.
• Raw location directories (UNIX only) cannot be automatically created and must be made with the MKRAWAREA utility, which must be run by "root." The Locations may be created prior to MKRAWAREA but a warning will be issued noting that the utility must be run prior to their use.

Remote Command Server Enhancements

Users commonly encounter problems running utilities that require exclusive access to the iidbdb database because the Remote Command Server process (rmcmd) keeps a session open on this database. To counter this problem, rmcmd now attaches to imadb instead of iidbdb; imadb is a system database that contains no historical data; it is rarely backed up and requires little or no maintenance.

Microsoft Transaction Server Support

Support for tightly coupled XA threads and shared lock lists is now available to support Microsoft Transaction Server, using the ODBC 3.5 driver.

Concurrent Rollback

The concurrent recovery of multiple transactions is now possible.