RE: Onload with detached indexes
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_000_01BF5D3D.BC94759C
Content-Type: text/plain;
charset="iso-8859-1"
It doesn't use flat files -- the data goes directly from the
command-line-specified table in the source DB server to another specified
table (default is same tablename) in the target server. Yes, dbcopy works
on one table at a time, however, there is nothing to prevent "copying"
several tables at once in parallel! It is fairly trivial to generate a list
of all tables in the database, then feed that list to a script that does a
dbcopy for each table. That's how I did it.
Attached is the "usage" text for dbcopy.
Paul Mosser
-----Original Message-----
From: Arshad, Shehla [mailto:SArshad@purolator.com]
Sent: Wednesday, January 12, 2000 1:15 PM
To: 'mosserp@WellsFargo.COM'
Cc: 'informix-list@iiug.org'
Subject: RE: Onload with detached indexes
Thanks alot for your response! Your suggesion raises a question in my
mind...
Would dbcopy unload data to ascii files which can later be loaded into the
target or would it just do a insert into select from table kind of a thing?
Thx,
Shehla
> -----Original Message-----
> From: mosserp@WellsFargo.COM [SMTP:mosserp@WellsFargo.COM]
> Sent: Wednesday, January 12, 2000 3:06 PM
> To: SArshad@purolator.com
> Cc: informix-list@iiug.org
> Subject: RE: Onload with detached indexes
>
> Suggestion:
> 1) Get Art Kagel's "dbcopy" program in his utils2_ak package on
> http://www.iiug.org
> 2) set up empty database on target instance, but without indexes
> 3) make sure you have sufficient connectivity (I'm pretty sure you need
> trusted relationship) between source and target servers
> 4) run dbcopy to copy all data from source to target
> 5) create detached indexes on target
> 6) update stats on target (better yet, use Art's "dostats" program, also
> in
> utils2_ak package)
>
> I recently used roughly this approach to re-organize a db instance --
> worked
> very well, and much faster than dbexport/dbimport.
>
> HTH
> Paul Mosser
>
> -----Original Message-----
> From: Arshad, Shehla [mailto:SArshad@purolator.com]
> Sent: Wednesday, January 12, 2000 12:28 PM
> To: informix-list@iiug.org
> Subject: Onload with detached indexes
>
>
> Hi
>
> Anybody with onload/onunload experience, please help!
>
> We are facing a problem copying a production database from one server to
> another using onload utility. Both servers are running same version of IDS
> (i.e. 7.30.FC7) and O/S (HP-UX 11.0).
>
> The database we are trying to copy has detached indexes. When we run
> onload> utility to import the database, it complains about missing dbspaces. All
> missing dbspaces are infact the ones that hold the detached indexes at the
> source. We have tried creating all the dbspaces at the target server in
> attempt to go around this problem after which onload failed complaining
> about no more space in dbspace (one that holds the detached indexes).
>
> We want onload/onunload because its faster than dbexport/dbimport. It will
> take approx 8 hours to dbexport and we dont have that kind of a window
> available. Also, HPL is cumbersome and it will take quite an effort to
> setup
> jobs for 350 tables. Any smart suggestions!!
>
> PS: I know onload/onunload works between the two servers as we have copied
> other database many times
>
> Thx,
>
> Shehla
------_=_NextPart_000_01BF5D3D.BC94759C
Content-Type: text/plain;
name="dbcopy.txt"
Content-Disposition: attachment;
filename="dbcopy.txt"
dbcopy - Copy Informix tables from one server/database to another.
Usage:
dbcopy <-f FlushCount>
<-V> <-i> <-F> <-S> <-5>
<-l errorlog>
<-r delimiter>
<-h fromhost>
<-H tohost>
<-d fromdb>
<-D todb>
[-t table_from &| -T table_to]
<-s select statement>
<-w lock wait time>
<-p PDQPRIORITY
-f - Number of records between database commits (Default: 10000).
Commits actually occur on the next completed input buffer that
exceeds FlushCount if the -F option is invoked.
-V - Print version information and exit.
-i - Ignore duplicate record errors and do not log dups.
-F - Flush only when an optimally sized buffer fills.
Note that this option will improve throughput greatly,
however, some rows may not have been flushed if ./dbcopy is killed.
-S - Silent mode. Minimize screen output.
-l - Any records incurring an insert error will be written to the log
file in ASCII delimited format suitable for load/dbload.
(Default: dbcopy.unl.)
-r - Delimiter character to use for error log records.
-5 - Connect to hosts using 5.0 compatible source.
-h - Host to copy from ($INFORMIXSERVER).
-H - Host to copy to ($INFORMIXSERVER).
-d - Database from which to copy (Default: todb).
-D - Database to which to copy (Default: fromdb).
-t - Table from which data is to be selected (Default: table_to).
-T - Table into which data is to be inserted (Default: table_from).
-s - Select statement (Default: SELECT * FROM <table>).
You may include '%s' to have /dbcopy insert <table_from>.
-w - Number of seconds to wait for locks to free up.
If negative wait forever. (Default: 10 seconds)
-p - PDQPRIORITY to set for the task.
One of -t or -T option or both must be specified.
If column names selected differ from those in the target table then the
SELECT statment MUST include column aliases corresponding to the column names
of the target table columns.
------_=_NextPart_000_01BF5D3D.BC94759C--