A Database Utility to the Rescue

How to use the IBM Informix archecker utility to restore a single table and save the day

What can be done about accidentally deleting a critical table? What if a WHERE clause in an update statement was omitted, causing important data to be overwritten? Recently, a client organization had one of those heart-stopping moments after deleting all the rows in one of its tables. In the fallout, the organization’s expectation was to have to do a full restore of its production database to another server just to recapture the missing records. However, this course of action wasn’t necessary thanks to an IBM® Informix® database utility that allows for restoring a single table.

The archecker utility, which may be familiar to some database administrators, is often used to verify complete archives, but it also allows the data to be extracted from an archive—onbar or ontape—and restored. This utility even provides the capability to roll forward from the logical logs to bring the table current as of a specific point in time.

Seeing the utility in action

The table-level restore is best explained using an example. In this case, we can perform a restore of mytable in the stores_demo database from an ontape archive. The data will be restored to a table called mytable_restore. It will then allow the restored records to be examined and provide the option to later use SQL commands to update mytable with selected records or columns.

The first step is to create a command and schema file that is used to tell the archecker utility the source table from which data is being restored and the name of the destination, or target, table. The source table schema is required so that archecker knows the format of the data within the archive, but no data will be restored to this restore_table.scm table in the following example:

--Name of the database
database stores_demo;

-- Specify where to create temporary/staging tables (optional)
set workspace to datadbs;

-- Specify the schema of the table to be restored
-- This table does NOT need to exist in the current database
-- The “in <dbspace>” clause allows archecker to identify
-- the dbspace in the archive to restore from. Note that this dbspace does
-- not have to exist in the instance that the restore is being performed to

create table mytable
id serial,
name char(30),
dob date
) in datadbs;

-- Specify the schema of the table to restore the data into
create table mytable_restore
id serial,
name char(30),
dob date

-- Specify the link between the table being restored and the destination
insert into mytable_restore
select * from mytable;

-- For point-in-time restores, can use the following syntax:
--restore to '2014-03-06 08:00:00';

The target table—mytable_restore—will be created automatically if it does not exist. If the table does exist, then records will be appended to it, unless archecker is called with the -d option, which will trigger an error if restored records already exist.

Many of the settings for the archecker utility can be specified in its configuration file, referenced by the environment variable AC_CONFIG. By default, any settings will be pulled from the supplied configuration file: $INFORMIXDIR/etc/ac_config.std. In this example, all settings are supplied to the command line or defaulted from the ONCONFIG file—TAPEDEV and LTAPEDEV.

The next step is to change the ONCONFIG file and modify TAPEDEV to the location of the backup file. LTAPEDEV needs to be changed to the location of the logical log files, if a point-in-time restore or a restore to the current time is to be performed.

If the ontape archive was backed up using a BACKUP_FILTER, or if the table to be restored contains a binary large object (BLOB) column, then a roll forward of the table using the logs is not possible. In either of these circumstances, a physical recovery can be performed to restore a table to the way it was at the time of the Level 0 archive. The following command can be run to perform the restore:

archecker -t -X -f restore_table.scm -d -v -s -l phys

The following options are used in the example:*

  • -t specifies ontape as the backup utility—use -b for onbar
  • -X is for the table-level restore
  • -f <filename> specifies the name of the schema and command file
  • -d deletes previous archecker utility files—use this option if there were several failed attempts to perform the restore
  • -v specifies verbose output
  • -s prints status messages to the screen
  • -l phys performs a physical restore only

As with ontape, when backing up and/or restoring records, the archecker utility prompts for a tape to insert before pressing Return when the command is issued. The restore will fail if the command is run in the background with nohup because there is no terminal device for input—the message simply says that an error occurred. If TAPEDEV is set to a directory, there will be no prompt for a tape.

The default message path— AC_MSGPATH—is /tmp/ac_msg.log. This file contains a detailed output from archecker and provides more useful messages than those that appear on screen.

For example, a message in the log similar to the following means that there is a problem with temporary columns that the archecker utility adds to the destination table:

ERROR: -217: Column (ifx_tlr_partnum) not found in any table in the query (or SLV is undefined).
ERROR: Unable to open table stores_demo:mytable_restore
ERROR: Table stores_demo.mytable is being skipped

Re-creating the destination table and using the -d option may resolve these errors. Following a successful run of the archecker utility, the data from the restored table will now be in the destination table specified in the schema and command file. After the restore, TAPEDEV and LTAPEDEV should be set back to the original values in the ONCONFIG file. Clean up the temporary staging files by running the following command:

archecker –DX

Saving the day

Although the need to restore a single table may rarely be necessary, the simple and effective Informix archecker utility can come to the rescue for those times when SQL mistakes are made.

Please share any thoughts or questions in the comments.

* See the documentation for additional archecker options and descriptions that apply to specific Informix releases.

[followbutton username='FastInformixDBA' count='false' lang='en' theme='light']
[followbutton username='IBMdatamag' count='false' lang='en' theme='light']