Informix provides the functionality to restore a single table or a subset of a table to a point in time or to the last archive. This functionality was originally released in IDS v10 and is provided through the archecker software which is included with the engine.
To perform a table level restore, you’ll need the following items:
– A level 0 archive, from either onbar or ontape
– An exact copy of the schema of the table to be restored
– An archecker command file
The archecker is capable of performing a logical or physical restore. A physical restore would be performed if your logical log files haven’t been backed up or you just want to restore the table to the time that the level 0 archive was performed. A logical restore can restore the database to any point in time that is encompassed by the logical logs. This is useful to restore data that was accidentally deleted or to recover a table that was dropped.
It should be noted that you cannot use a shared memory connection when performing a table-level restore.
Archecker command file
The archecker command file consists of 5 sections.
– Database
– Set
– Create table
– Insert into
– Restore
For the example provided here, we’ll be using the customer table from the stores_demo database provided with all IDS releases.
Database section
The database section sets the database in which the restore is being performed.
database stores_demo;
SET section
The SET statement controls the different features in the table-level unload library. There are two options that are provided for the set section, commit and workspace. The commit sets the number of records to insert before committing during a physical restore. The default is 1000. The workspace sets the dbspaces to use for the working storage space. The default is the root dbspace. You cannot use temporary dbspaces for the working storage space.
Examples:
SET COMMIT to 2000;
SET WORKSPACE to dbspace1;
Create table section
The create table section defines the table that is being restored and the table to which you want to restore it. The definition of the table being restored must be identical to the table as it existed in the database when the archive was performed. This includes the dbspace(s) where the table is located, as well as any fragmentation schemes, etc. You do not need to include the index and constraint definitions.
Current archive command file
DATABASE stores_demo;
create table "informix".customer
(customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num)
) extent size 16 next size 16 lock mode page;
The second part of the create table section is the table that we will being restoring to. When defining the restore table you have the option of doing a full restore, restoring a subset of the columns and even certain rows within the table. The table can be placed in any valid dbspace on the system. If the table doesn’t exist then it will be created; if it does exist, then it will be appended to.
create table “informix".customer_restore
(customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num)
) in datadbs extent size 16 next size 16 lock mode page;
Insert section
The insert section defines what data you would like to restore from the source table. Here is where you could limit the data you want restored, such as the columns or a subset of the rows, by using a standard where clause.
insert into customer_restore
select * from customer;
Restore section
The restore section defines the type of restore that you want to perform, logical or physical.
To perform a physical restore you would use the entry:
restore to current with no log;
For a logical restore you would use:
restore to “2014-04-20 14:00:00”;
For this example we’ll be doing a physical restore, so our completed command file will look like this:
DATABASE stores_demo;
create table "informix".customer
(customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num)
) extent size 16 next size 16 lock mode page;
create table “informix".customer_restore
(customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18),
primary key (customer_num)
) in datadbs extent size 16 next size 16 lock mode page;
insert into customer_restore
select * from customer;
restore to current with no log;
Running the archecker for a table restore
As noted above, the archecker was originally intended to verify that an archive wasn’t corrupt. The additional options allow it to perform the restore. The following command will be used to perform this restore:
archecker -tvds -f customer_restore.cmd
Expected output:
IBM Informix Dynamic Server Version 11.70.FC2
Program Name: archecker
Version: 8.0
Released: 2011-03-15 22:07:51
CSDK: IBM Informix CSDK Version 3.50
ESQL: IBM Informix-ESQL Version 3.50.FN672
Compiled: 03/15/11 22:08 on Linux 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008
AC_STORAGE /tmp
AC_MSGPATH /tmp/ac_msg.log
AC_VERBOSE on
AC_TAPEDEV /opt/IBM/informix/backup/
AC_TAPEBLOCK 32 KB
AC_LTAPEDEV opt/IBM/informix/backup
AC_LTAPEBLOCK 32 KB
Dropping old log control tables
Restore only physical image of archive
Extracting table stores_demo:customer into stores_demo:customer_restore
Archive file /opt/IBM/informix/backup/informix_pri_100_L0
Tape type: Archive Backup Tape
OnLine version: IBM Informix Dynamic Server Version 11.70.FC2
Archive date: Wed Apr 23 07:12:46 2014
Archive level: 0
Tape blocksize: 32768
Tape size: 2147483647
Tape number in series: 1
Scan PASSED
Control page checks PASSED
Table checks PASSED
Table extraction commands 1
Tables found on archive 1
LOADED: stores_demo:customer_restore produced 28 rows.
After the archecker process has been completed, the new table, customer_restore, will contain the data that was defined in the insert section.
Additional information can be found in the IBM Informix information center at