RE: Unloading a live database
Thanks. But it does not actually solve my problem. What I need to
unload is an entire "database" containing about 100 tables. Your
answer is for unloading single tables. Is there a way to unload the
database without interrupting updates to tables in the database?
What I need is a "snapshot" of the database at the time the unload
command is issued. "dbexport" takes an exclusive lock on the DB
and "onunload" takes a shared lock. So, neither of these is suitable.
The "load" is not a problem because it will be on a different instance.
"ontape" seems to be out because the second instance is not
guaranteed to have the same DBspace configurations as the first.
Informix Dynamic Server 7.31
AIX 4.3
Thanks.
Alanoly J. Andrews
> -----Original Message-----
> From: Pons Roca, Isidre [SMTP:ipons@dtgna.altanet.org]
> Sent: Thursday, August 05, 1999 3:00 AM
> To: informix-list@iiug.org
> Subject: Re: Unloading a live database
>
> En Alanoly Andrews va escriure el dia 4 Aug 99, a les 9:23:
>
> Option 1:
> from dbaccess do:
> unload to "file" select * from <table name> --> this in> instance 1
> load from "file" insert into <table_name> --> this in instance> 2
>
> Option 2:
> from dbaccess in instance 1 do:
> insert into databasename@informixservername:table select *
> from <table name>>
> Suggestion:
> Put the database in the second instace WITH NO LOG.
> To do that use ontape.
>
> Be fun.
>
>
> >
> > Hi,
> >
> > I need to unload a "database" while the instance is running
> > and accepting updates on the database. I see that the "onunload"
> > utility of Informix takes a lock on the database and does not allow
> > users to modify the Database during the unload process.
> > The unloaded database is to be loaded into another instance
> > which need not have the same DBspace configurations as the
> > first instance. This seems to rule out the use of archiving utilities
> like
> > "ontape" which require identical DBspace configurations for a restore.
> >
> > I'd appreciate your input into solving this problem.
> >
> > Thanks.
> >
> > Alanoly J. Andrews
> >
>
>
>
> ---------------------------------------
> Isidre PONS ROCA
> BASE - Gesti' d'Ingressos Locals
> (Diputacio de Tarragona)
> Servei de Sistemes de Informacio
> Av President Lluis Companys 12-C
> 43005 - Tarragona
> SPAIN
> Tel # +34 977 236731
> Fax # +34 977 227302
> http://www.altanet.org
> ipons@dtgna.altanet.org
> ---------------------------------------
Since the second instance may not be similar enough, I gather that you would
be planning to restore tables to different dbspaces than their original
locations. If so, then you would need a table level backup and restore,
true?? In that case, I think the solution offered is probably about the
best you're going to get. BUT, the tables will NOT be synchronized to the
beginning of the unloads.
Or, if all the tables you want are in a set of dbspaces that do not exist in
the second instance, could you do define the dbspaces in the second instance
and then do a dbspace-level restore of an ontape backup (think this would
work with ontape, but don't quote me)??
Just my humble, and often wrong (which is why no one wants them), opinion
Doug
Alanoly Andrews wrote in message <7oc4hi$ib$1@news.xmission.com>...
>
>
>Thanks. But it does not actually solve my problem. What I need to
>unload is an entire "database" containing about 100 tables. Your
>answer is for unloading single tables. Is there a way to unload the
>database without interrupting updates to tables in the database?
>What I need is a "snapshot" of the database at the time the unload
>command is issued. "dbexport" takes an exclusive lock on the DB
>and "onunload" takes a shared lock. So, neither of these is suitable.
>The "load" is not a problem because it will be on a different instance.
>"ontape" seems to be out because the second instance is not
>guaranteed to have the same DBspace configurations as the first.
>
>Informix Dynamic Server 7.31
>AIX 4.3
>
>Thanks.
>
>Alanoly J. Andrews
>
>> -----Original Message-----
>> From: Pons Roca, Isidre [SMTP:ipons@dtgna.altanet.org]
>> Sent: Thursday, August 05, 1999 3:00 AM
>> To: informix-list@iiug.org
>> Subject: Re: Unloading a live database
>>
>> En Alanoly Andrews va escriure el dia 4 Aug 99, a les 9:23:
>>
>> Option 1:
>> from dbaccess do:
>> unload to "file" select * from <table name> --> this in>> instance 1
>> load from "file" insert into <table_name> --> this in instance>> 2
>>
>> Option 2:
>> from dbaccess in instance 1 do:
>> insert into databasename@informixservername:table select *
>> from <table name>>>
>> Suggestion:
>> Put the database in the second instace WITH NO LOG.
>> To do that use ontape.
>>
>> Be fun.
>>
>>
>> >
>> > Hi,
>> >
>> > I need to unload a "database" while the instance is running
>> > and accepting updates on the database. I see that the "onunload"
>> > utility of Informix takes a lock on the database and does not allow
>> > users to modify the Database during the unload process.
>> > The unloaded database is to be loaded into another instance
>> > which need not have the same DBspace configurations as the
>> > first instance. This seems to rule out the use of archiving utilities
>> like
>> > "ontape" which require identical DBspace configurations for a restore.
>> >
>> > I'd appreciate your input into solving this problem.
>> >
>> > Thanks.
>> >
>> > Alanoly J. Andrews
>> >
>>
>>
>>
>> ---------------------------------------
>> Isidre PONS ROCA
>> BASE - Gesti' d'Ingressos Locals
>> (Diputacio de Tarragona)
>> Servei de Sistemes de Informacio
>> Av President Lluis Companys 12-C
>> 43005 - Tarragona
>> SPAIN
>> Tel # +34 977 236731
>> Fax # +34 977 227302
>> http://www.altanet.org
>> ipons@dtgna.altanet.org
>> ---------------------------------------
Alanoly, the ONLY way to get the kind of snapshot you want is by
locking the database or going through the kind of hoops that ontape
and onbar do. There just is no other way unless all of your tables
have a timestamp then you could run 100 unload scripts or use some
utility like my ul.ec or dbcopy.ec to extract/copy only rows dated
prior to the cutoff time.
Art S. Kagel
Alanoly Andrews wrote:
>
> Thanks. But it does not actually solve my problem. What I need to
> unload is an entire "database" containing about 100 tables. Your
> answer is for unloading single tables. Is there a way to unload the
> database without interrupting updates to tables in the database?
> What I need is a "snapshot" of the database at the time the unload
> command is issued. "dbexport" takes an exclusive lock on the DB
> and "onunload" takes a shared lock. So, neither of these is suitable.
> The "load" is not a problem because it will be on a different instance.
> "ontape" seems to be out because the second instance is not
> guaranteed to have the same DBspace configurations as the first.
>
> Informix Dynamic Server 7.31
> AIX 4.3
>
> Thanks.
>
> Alanoly J. Andrews
>
> > -----Original Message-----
> > From: Pons Roca, Isidre [SMTP:ipons@dtgna.altanet.org]
> > Sent: Thursday, August 05, 1999 3:00 AM
> > To: informix-list@iiug.org
> > Subject: Re: Unloading a live database
> >
> > En Alanoly Andrews va escriure el dia 4 Aug 99, a les 9:23:
> >
> > Option 1:
> > from dbaccess do:
> > unload to "file" select * from <table name> --> this in> > instance 1
> > load from "file" insert into <table_name> --> this in instance> > 2
> >
> > Option 2:
> > from dbaccess in instance 1 do:
> > insert into databasename@informixservername:table select *
> > from <table name>> >
> > Suggestion:
> > Put the database in the second instace WITH NO LOG.
> > To do that use ontape.
> >
> > Be fun.
> >
> >
> > >
> > > Hi,
> > >
> > > I need to unload a "database" while the instance is running
> > > and accepting updates on the database. I see that the "onunload"
> > > utility of Informix takes a lock on the database and does not allow
> > > users to modify the Database during the unload process.
> > > The unloaded database is to be loaded into another instance
> > > which need not have the same DBspace configurations as the
> > > first instance. This seems to rule out the use of archiving utilities
> > like
> > > "ontape" which require identical DBspace configurations for a restore.
> > >
> > > I'd appreciate your input into solving this problem.
> > >
> > > Thanks.
> > >
> > > Alanoly J. Andrews
> > >
> >
> >
> >
> > ---------------------------------------
> > Isidre PONS ROCA
> > BASE - Gestió d'Ingressos Locals
> > (Diputacio de Tarragona)
> > Servei de Sistemes de Informacio
> > Av President Lluis Companys 12-C
> > 43005 - Tarragona
> > SPAIN
> > Tel # +34 977 236731
> > Fax # +34 977 227302
> > http://www.altanet.org
> > ipons@dtgna.altanet.org
> > ---------------------------------------
The problem with unloading a 'live' database is data consistency among
tables.
You unload table 'a', then table 'b'. Table 'b' may not be in 'sync' with
table
'a' because it was unloaded at a different point in time.
I get the impression that you don't care very much about that. If, in fact,
consistency is important, the following method will not work.
This simplified (no error checking or logging) script will unload all data
tables with
parallelization you can specify.
#!/bin/ksh
DB=$1
MODFACTOR=$2
OFFSET=$3
TABLELIST=`echo "select tabname from systables where tabid > 99
and tabtype = 'T' and MOD(tabid,$MODFACTOR) = $OFFSET;" \\
| dbaccess $DB 2>/dev/null | sed '1,4d'`
echo $TABLELIST
for TABLE in $TABLELIST
do
dbaccess $DB << eo_db 2>/dev/null
set isolation to dirty read;
unload to $TABLE.unl
select * from $TABLE;eo_db
done
Notes
1. 'set isolation to dirty read' ignores all locks and locks nothing.
2. n calls to unload.sh with the first parameter = <database> ,
the second parameter = n, the third parameter from 0 to n-1
will unload your database in n parallel streams.
Thus, a single call of "unload.sh mydb 1 0" will unload all tables
of mydb sequentially.
Five calls as follows will unload mydb using 5 parallel streams
unload.sh mydb 5 0 &
unload.sh mydb 5 1 &
unload.sh mydb 5 2 &
unload.sh mydb 5 3 &
unload.sh mydb 5 4 &
Loading data into your target database would, in principle, be
similar to unloading. However, other problems exist. Of
course, you should have created the empty schema using the source
database (ideally using dbschema -ss and then modifying the
"in dbspace" clause appropriately). But there is still the problem of
constraints - you cannot load children tables until their parents are
loaded.
One method to tackle this is to disable all FK constraints, PK constraints
and indexes in that order. Then load your data. Finally enable your indexes,
PKs and FKs in that order. Unfortunately, you could still run into problems
with your FKs, because your data was not 'locked' during the unload - but,
hey, that's a cake problem. I have a script for the above - let me know
if you want it.
HTH
Rudy
Yep, footloose and fancy free...
Alanoly Andrews wrote:
> Thanks. But it does not actually solve my problem. What I need to
> unload is an entire "database" containing about 100 tables. Your
> answer is for unloading single tables. Is there a way to unload the
> database without interrupting updates to tables in the database?
> What I need is a "snapshot" of the database at the time the unload
> command is issued. "dbexport" takes an exclusive lock on the DB
> and "onunload" takes a shared lock. So, neither of these is suitable.
> The "load" is not a problem because it will be on a different instance.
> "ontape" seems to be out because the second instance is not
> guaranteed to have the same DBspace configurations as the first.
>
> Informix Dynamic Server 7.31
> AIX 4.3
>
> Thanks.
>
> Alanoly J. Andrews
>