Improve Ontape Point-in-Time Restores for Tables

The archecker tool can be used for point-in-time restores of table data from Level 0 Informix backups in conjunction with whatever logs were created after the Level 0 backup. However, if an organization does one Level 0 backup per week, then they will have a problem if they need to do a point-in-time restore if they are working with a table that was either created or altered after the Level 0 backup. In the scenario where the table was created after the Level 0 backup and then rows were added, then no point-in-time restore is possible. In the scenario where a new field is added after the Level 0 backup, then when you attempt a point-in-time restore, you may find garbled data in the new field.

I am attaching my notes that reflect some of my experiments with archecker, in case they are helpful to anyone.

Here is the sequence to reproduce the problem:

a. Create a database named "dargan_test".

b. Do a Level 0 backup if one has not been done recently.

c. Create a table named test_data:

create table "informix".test_data
(
c_info char(20)
) extent size 32 next size 32 lock mode row;

d. Insert three rows

e. Truncate the table.

f. Force a log backup: “ontape –a” (optionally run this command a second time)

g. Create a new archecker script with the lines below. We can name the file “test”.

database dargan_test;
create table "informix".test_data
(
c_info char(20)
) extent size 32 next size 32 lock mode row;
insert into test_data select * from test_data;
restore to <point-in-time>;

h. Now run archecker:

archecker -tdvsf test

i. In the lines that appear on the screen you should be able to find this line if archecker had done its job in the expected manner:

Logically recovered dargan_test:test_data Inserted 3 Deleted 0 Updated 0

j. If you look at your table again, you should see the 3 rows now, but I don't think you will. I think you will find that the three rows are missing and you don't see the message saying that 3 rows were inserted.

The archecker tool is supposed to help with recovering data up until the moment when someone inadvertently truncates the data in the table.

I've found that if you create the table, *** do a Level 0 backup ***, then add the rows, force the new log with "ontape -a", then truncate the table, then run archecker, then the rows are restored. However, if you create the table AFTER you do the Level 0 backup, then the archecker tool fails to do its job.