Introduction

Informix Dynamic Server V10.0 (IDS) provides you with the ability to easily extract data in tables of databases from a 0-level backup to a specified point in time. The extracted data can be loaded in internal, external tables or ASCII files. This feature lets you:

• Extract a table or a set of tables
• Filter the retrieved data
• Retrieve just a subset of columns
• Repartition the data
• Place data in the same version of the database, or in a different database version with a different machine architecture.

To use this feature, you use the archecker utility to extract and load data to specific tables.

Overview of the archecker utility

The archecker utility requires the following:

• The archecker configuration file
• The schema command file
• The archecker command, in order to execute

The archecker utility uses a configuration file to set certain parameters. Set the AC_CONFIG environment variable to the full pathname of the archecker configuration file. By default, the AC_CONFIG environment variable is set to $INFORMIXDIR/etc/ac_config.std. If you set AC_CONFIG to a user-defined file, you must specify the entire path including the filename. The schema command file is use to specify the source and destination tables and to define the table schema.

The archecker utility uses a schema command file to specify the following:

• Source tablels
• Destination tables
• Table schemas
• Databases
• External tables
• Point in time the table is restored to
• Other options

The restore command can be defined either as point in time restore or final logical log recovery which should be performed with RESTORE TO CORRUENT by default. Restore options are:

RESTORE [TO <timestamp> | CURRENT]  [NO LOG RESTORE]. 

Note: NO LOG RESTORE means not performing a logical restore.

DATABASE testdb;
CREATE TABLE source (col1 integer, ...) IN dbspace1;
CREATE TABLE target (col1 integer, ...) IN dbspace1;
INSERT INTO target SELECT * FRM source;
RESTORE TO current ;

To set the schema command file:

• Set the AC_SCHEMA configuration parameter in the archecker configuration file.
• Use the -f command file command line option
• If both methods are specified, the -f command line option takes precedence.

To use table-level point-in-time restore mode with schema file, execute following command:

archecker –bsv –f {schema cmd file}
	-t: Use ontape interface
	-b: Use onbar interface
	-s: Print information to the screen
	-v: Verbose
	-f: Schema command file
	-X: Table Level unloads

Note: AC_SCHEMA has to be specified either in the configuration file or on the command line.

Advance preparation

For testing, set up either ISM or TSM and install and configure where IDS is installed. Create the demo database “stores7" in a new dbspace called ‘datadbs’ and perform full-backup using onbar command:

mkdir /informix/DBS
touch /informix/DBS/data_chk
chmod 660 /informix/DBS/data_chk
onspaces –c –d datadbs –p /informix/DBS/data_chk –s 10000 –o 0
dbaccessdemo stores7 -dbspace datadbs –log
onbar -b

To restore point-in-time, you need to verify timestamp in bar_act.log.

Check timestamp in /Informix/bar_act.log:

2006-03-24 21:10:02 21724  21722 /informix/bin/onbar_d -b
2006-03-24 21:10:02 21724  21722 Archive started on rootdbs, datadbs (Requested Level 0).
2006-03-24 21:10:03 21724  21722 Begin level 0 backup rootdbs.
2006-03-24 21:10:03 21724  21722 Successfully connected to Storage Manager.
2006-03-24 21:10:03 21724  21722 Completed level 0 backup rootdbs (Storage Manager copy
	 ID: 720324002 0).
:
2006-03-24 21:10:07 21724  21722 /informix/bin/onbar_d complete, returning 0 (0x00)
2006-03-24 21:10:08 21751  21749 /informix/bin/onbar_d -b -l
2006-03-24 21:10:08 21751  21749 /informix/bin/onbar_d complete, returning 0 (0x00)

Set up the environment

If you are not already logged in as user Informix, then switch user id to Informix and create a new archecker configuration file:

su - informix
cp $INFORMIXDIR/etc/ac_config.std ac_config
export AC_ACONFIG=$INFORMIXDIR/etc/ac_config

Edit ac_config file using the editor, change parameters:

AC_MSGPATH      /informix/ac_msg.log
AC_STORAGE      /tmp
AC_VERBOSE      1
AC_SCHEMA       /informix/ARCH/scm.cmd
AC_IXBAR         /informix/etc/ixbar.0

AC_IXBAR points to where the IXBAR file resides.

Recover a lost table

Here we extract a table called customer from the most recent backup of dbspace and place the data in table customer.

To simulate this situation, execute following SQL as drop customer table using dbaccess or other tools:
dbaccess stores7 - <<EOF drop table customer; EOF

Create a new command file to cmdfile1.cmd in directory ‘/Informix’ and edit thecommand file as follows:

database stores7;
create table 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)
  ) in datadbs;
insert into customer select * from customer;

You can restore customer table with the command: archecker -bvs -f /informix/cmdfile1.cmd

You may see the following message:

IBM Informix Dynamic Server Version 10.00.UC4
Program Name:   archecker
Version:        8.0
Released:       2005-11-18 22:22:21
CSDK:           IBM Informix CSDK Version 2.90
ESQL:           IBM Informix-ESQL Version 2.90.UC1
Compiled:       11/18/05 22:23  on Linux 2.4.21-27.0.2.ELsmp #1 SMP Wed Jan 12 23:35:44 
		EST 2005

AC_STORAGE               /tmp
AC_MSGPATH               /informix/ac_msg.log
AC_VERBOSE               on
AC_TAPEBLOCK             62 KB
AC_IXBAR                 /informix/etc/ixbar.0
Dropping old log control tables
Extracting table stores7:customer into stores7:customer

Scan PASSED
Control page checks PASSED
Table checks PASSED
Table extraction commands 1
Tables found on archive 1
LOADED: stores7:customer produced 28 rows.
Creating log control tables
Staging Log 48

Logically recovered stores7:customer Inserted 0 Deleted 0 Updated 0

Whenever you execute archecker command, you will see information of tables being extracted, rows extracted from physical restore, logical logs processed and logical recovery actions.

Verify recovery of customer table executing following SQL:

dbaccess stores7 - <<EOF
select count(*) from customer;
EOF

Restoring a table from a previous backup to a different table

This example extracts a table called customer from the level 0 backup of datadbs just prior to '2006-03-24 21:10:08' and restores the data into table customer2. The logical logs are used to restore the table to the point in time of the timestamp that has been defined.

Create new command file as cmdfile2.cmd in ‘/Informix’ and edit command file as follows:

database stores7;
create table 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)
  ) in datadbs;

create table customer2
  (
    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;

insert into customer2 select * from customer;
restore to '2006-03-24 21:10:08';

You can restore customer table to customer2 table with the command:

archecker -bvs -f /informix/cmdfile2.cmd

Verify recovery of customer2 table by executing following SQL:

dbaccess stores7 - <<EOF
select count(*) from customer2;
EOF

Use data filtering

This example extracts a table called customer from the most recent backup of datadbs and places the data into the table customer3 where the list conditions are only true.

Note: Filters can only be applied to a physical restore.

Create a new command file to cmdfile3.cmd in the directory “/Informix" and edit command file as follows:

database stores7;
create table 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)
  ) in datadbs;

create table customer3
  (
    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;

insert into customer3 select * from customer where customer_num > 120;
restore to current no log restore;

You can see same results after executing following two SQL statements:

dbaccess stores7 - <<EOF
select count(*) from customer3;
select count(*) from customer where customer_num > 120;
EOF

Back to top

Use external tables

This example extracts a table called customer from the most recent backup of datadbs and sends the data in ASCII format with fields delimited to the file named customer.unl.

Create new command file to cmdfile4.cmd in directory “/Informix" and edit command file as follows:

database stores7;
create table 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)
  ) in datadbs;
create external table customer4
  (
    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)
  ) using ('/informix/customer.unl',delimited);
insert into customer4 select * from customer;
restore to ‘2006-03-24 21:10:08’ no log restore;

The following table shows the unloaded file:

cat /Informix/customer.unl
101|Ludwig|Pauli|All Sports Supplies|213 Erstwild Court||Sunnyvale|CA|94086|408-789-8075|
102|Carole|Sadler|Sports Spot|785 Geary St||San Francisco|CA|94117|415-822-1289|
103|Philip|Currie|Phil's Sports|654 Poplar|P. O. Box 3498|Palo Alto|CA|94303|415-328-4543|

Back to top

Conclusion

IBM Informix Dynamic Server v10.0 provides the feature for recovering specific table from archive. When important data is lost or you need to verify old data from backup data, then the archecker easily can be used for recovering lost data or tables, and for verifying old data from backup data.

Resources

Learn

• Visit the developerWorks Informix Zone to read articles and tutorials and connect to other resources to expand your Informix skills.
  
  
• The Informix Dynamic Server v10.0 Information Center is your source for more details about the archecker utility, and everything else you need to know about IDS.
  
  
• Visit the Informix library to access Informix manuals online.
  
  
• Stay current with developerWorks technical events and Webcasts.
  
  

Get products and technologies

• Download a free trial version of Informix Dynamic Server.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Doyun Kim provides technical sales support for IBM Information Management products as part of IBM Techline. He has worked with IDS and DB2 for Linux, UNIX, and Windows for seven years.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Overview of the archecker utility
• Advance preparation
• Set up the environment
• Recover a lost table
• Restoring a table from a previous backup to a different table
• Use data filtering
• Use external tables
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers