Point-in-time recovery using archecker.

Question

Sometime you may need to restore a single table from an archive. In fact you may want the table exactly as it was at a specific date and time, and we can do this using archecker.

It is important to note that the format of the datetime you provide to archecker depends the values in environment variables such as DBTIME, GL_DATETIME, or CLIENT_LOCALE when the back up was taken.

Answer

The following steps are a guide for people who want to use a non-default database locale.

The first thing is to find out which datetime format should be used. This is decided by the following environment variables in this order:

a) DBTIME environment variable:
setenv DBTIME '%Y-%m-%d %H:%M:%S'

b) If DBTIME is not set, then the value of the GL_DATETIME environment variable will be used:
setenv GL_DATETIME '%Y-%m-%d %H:%M:%S'

c) If neither DBTIME or GL_DATETIME are set, the default time format from the CLIENT_LOCALE will be used.
This ESQL/C program shows the time format for a given CLIENT_LOCALE.

/* start datetime.ec */
-------------------------------------------------------------------------------
#datetime.ec
#include <stdio.h>

main(int argc, char **argv)
{
$datetime year to second dt;
char output[100];
char buffer[100];

if (argc!=2) {
printf(" USAGE : datetime <CLIENT_LOCALE value> \n");
printf(" EXAMPLE : datetime ko_kr.ksc \n");
exit(1);
}

sprintf(buffer,"CLIENT_LOCALE=%s",argv[1]);
putenv(buffer);
dtcurrent(&dt);
dttofmtasc(&dt,output,sizeof(output),NULL);
printf("%s \n",output);
}
-------------------------------------------------------------------------------
/* end datetime.ec */

You compile the ESQL/C program above with this command:
esql -o datetime datetime.ec -static

You then execute it like this:
datetime de_de.8859-1

The output looks like this:
Mi. 08 Dez. 2010, 21:19:16


Once the relevant datetime format is known, we can restore one table at a point in time. The example restores a single table from a database where DB_LOCALE & CLIENT_LOCALE are both set to de_de.8859-1, and DBTIME & GL_DATETIME are not set. the datetime will be as shown above

1. Create SQL command file for archecker with a correct time format.
-------------------------------------------------------------------------------
#Archecker SQL example...
#/informix/adm/point-in-time-recovery.cmd
database mydb;

create table t_source (
cm_cd smallint not null ,
c_cmp_cd integer not null ,
corp_ins_no char(13),
prv_no char(20),
is_rpt char(1),
rpt_ymd integer
) in mydbsp ;

create table t_target (
cm_cd smallint not null ,
c_cmp_cd integer not null ,
corp_ins_no char(13),
prv_no char(20),
is_rpt char(1),
rpt_ymd integer);

insert into t_target select * from t_source;
restore to 'Mi. 08 Dez. 2010, 01:02:03';

2. Run the archecker command for recovery. 

archecker -tvs -f /informix/adm/point-in-time-recovery.cmd 

3. See the recovery result from ac_msg.log.

http://www-01.ibm.com/support/docview.wss?uid=swg21457155