RE: dbexport too slow. Any other ideas?
GREG_MAY@Non-HP-Roseville-om1.om.hp.com wrote:
> dbexport exports a complete database. But, its way too slow. Is there
> a way to export and load a database faster.
>
> g.
Greg:
I'm presuming that ontape/onbar etc aren't going to do it for you. There =
is an alternative, but it requires a little bit of work on your behalf. =
I hate dbexport - if your database is bigger than about 15GB, it is =
hopelessly inefficient. It also doesn't let you move tables around and =
play with extent sizes etc prior to execution (in v5 you couldn't even =
use -ss). Here's what I did when we migrated from v5 to v7 a while back:
Generate a full dbschema and edit it to suit your new DBspaces, extents =
etc. Ensure logging is turned off and run it on your new instance. Then =
on the old instance, execute the following:
UNLOAD TO "table.data" DELIMITER "|"
SELECT tabname, ncols, ncols * nrows
FROM systables
WHERE tabid > 99
ORDER BY 3 DESC;
The order by is to get the tables in rough order from largest to =
smallest. Then use awk or perl to read this file and generate 4 unload =
scripts in a round-robin fashion, where each one unloads to a different =
disk. Depending on what you're trying to achieve, you could possibly use =
onunload instead. Something like this:
perl -e '
open(Unld0, "> unload_1.sql") || die;
open(Unld1, "> unload_2.sql") || die;
open(Unld2, "> unload_3.sql") || die;
open(Unld3, "> unload_4.sql") || die;
open(Dbld0, "> unload_1.cmd") || die;
open(Dbld1, "> unload_2.cmd") || die;
open(Dbld2, "> unload_3.cmd") || die;
open(Dbld3, "> unload_4.cmd") || die;
@dsk =3D ("/disk_a","/disk_b","/disk_c","/disk_d"); #array of disks =
for writes
while(<>){
($tab, $cols) =3D split(/\\|/);
$r=3D$n++%4; #modulo to cycle through scripts: $n =3D Row number
$uno=3Dsprintf("Unld%d", $r);
$dno=3Dsprintf("Dbld%d", $r);
printf $uno "UNLOAD TO %s/%s.unl DELIMITER \\"|\\"\\n", $dsk[$r], $tab;
printf $uno " SELECT * FROM %s;\\n\\n",$tab;
printf $dno "FILE %s/%s.unl DELIMITER \\"|\\" %d;\\n", $dsk[$r], $tab, =
$cols;
printf $dno "INSERT INTO %s;\\n\\n", $tab;
}' table.data
Then execute the four unload_*.sql scripts in parallel. You can run as =
many of these as you have disks/capacity. I chose to use four.
As they finish, kick off the corresponding dbload script. One of the =
other benefits of this method over dbimport is that you don't have to =
start from scratch if you have a problem. You can fairly easily restart =
the dbloads from virtually any point.
Actually, when I did this I had one dbload script per table, and three =
processes polling for completed unloads. As they were identified, the =
corresponding dbload would be executed. Using this method I moved over =
20GB of data in about 10 hours, including rebuilding indexes, statistics =
etc. The previously attempted dbexport was killed after 56 hours, 'cos =
we were running out of outage and were nowhere near complete!
Hope this helps.
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus IT |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+