exporting a 35G database
Kenneth needed to move a 35GB Informix 7.30 database from an NCR Unix box (2GB file-size limit, five tables bigger than that) to IDS 9.40, with no compatible tape drive, and asked whether piping dbexport through dd/split was safe. Replies advised against the pipe trick and suggested alternatives: use the High Performance Loader (onpladm/ipload) with multiple unload/load files per big table, HPL for the big tables plus dbexport for the rest, or cross-server INSERT INTO ... SELECT from a remote database into RAW tables then ALTER back to STANDARD. Running 9.40's dbexport against the 7.30 server was tried but failed due to system-table differences; Art Kagel suggested his myexport/myschema utilities, and Alexey posted a script that generates per-table UNLOAD/LOAD statements from dbschema output. No confirmation of which approach Kenneth finally used is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
People,
We are tring to export a 35G informix 7.30 database to importing again on an
infromix 9.4 database.
The 7.3 database resides on a NCR Unix machine that does not support files
larger then 2G (unfortunately we have 5 tables larger then this limit). Due to
incompatible tape drives fitted on the machines we cannot perform an export to
tape and import it again.
What I was thinking is as follows
create a unix pipe (mknod mypipe.1 p)
In the backgroud start a process that reads data from the pipe.
dd if=mypipe.1 | split -a 5 -b 1024m &
and start the export
dbexport -d se4rt -t mypipe.1 -s 1024000 -b 32 <<!
!
However I fear that the split and the dbexport get out of sync and make the
export process useless.
Is there a better or cleaner way to do this ?
Thanks and regards
Kenneth
Forget
about dbexport on pipe ...
you have to use the high performance loader for this problem.
Best way is to extract dbschema from 7.30, create this (without indexes)
on 9.40 and use onpladm to create the unload und load jobs based on IDS
9.40.
For all tables bigger than 2 GB use several unload/load-files !!!
After the creation of these jobs, you van export the database onpload
and import it on your ids 7.30
kalu
Follow these step:
1) Build your database with tables only on the new
system.
2) For each table in the new database "ALTER TABLE
tabname TYPE (RAW)".
3) Use SQL copy, i.e. "INSERT INTO tabname SELECT *
FROM remdb@remsrvr:tabname".
4) Do row counts on both databases to make sure that
all rows were copied over since a table of type RAW is
not logged so if something goes wrong the transaction
will not roll back.
5) For each table in the new database "ALTER TABLE
tabname TYPE (STANDARD)".
6) Add all indexes, constraints and other database
objects.
I would split the INSERT statements into a few file 3
to 10 depending on the size of your smallest/slowest
machine and execute them all at the same time. With
this approach you get multiple table migrations in
parallel without the overhead of an unload and reload.
You will save a ton of time.
KENNETH PENZA <kenneth.penza@gov.mt> wrote:
People,
We are tring to export a 35G informix 7.30 database to
importing again on an infromix 9.4 database.
The 7.3 database resides on a NCR Unix machine that
does not support files larger then 2G (unfortunately
we have 5 tables larger then this limit). Due to
incompatible tape drives fitted on the machines we
cannot perform an export to tape and import it again.
What I was thinking is as follows
create a unix pipe (mknod mypipe.1 p)
In the backgroud start a process that reads data from
the pipe.
dd if=mypipe.1 | split -a 5 -b 1024m &
and start the export
dbexport -d se4rt -t mypipe.1 -s 1024000 -b 32 <
!
However I fear that the split and the dbexport get out
of sync and make the export process useless.
Is there a better or cleaner way to do this ?
Thanks and regards
Kenneth
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
Have you
tried High performance loader? (ipload/pload whatever you wanna
call it) ?
You can unload and load to multiple files.
One of the spiffiest things in Informix. Zoom.
http://www.informix.com.ua/doc/9.40/ct1t3na.pdf
This is for the 9.4 engine..I think 7.30 had it too, just not the new
pladmin thing....
pretty sure ipload was still there (trying to remember..getting old...).
-----Original Message-----
From: KENNETH PENZA [mailto:kenneth.penza@gov.mt]
Sent: Wednesday, May 26, 2004 3:40 AM
To: ids@iiug.org
Subject: exporting a 35G database [3024]
People,
We are tring to export a 35G informix 7.30 database to importing again
on an infromix 9.4 database.
The 7.3 database resides on a NCR Unix machine that does not support
files larger then 2G (unfortunately we have 5 tables larger then this
limit). Due to incompatible tape drives fitted on the machines we cannot
perform an export to tape and import it again.
What I was thinking is as follows
create a unix pipe (mknod mypipe.1 p)
In the backgroud start a process that reads data from the pipe.
dd if=mypipe.1 | split -a 5 -b 1024m &
and start the export
dbexport -d se4rt -t mypipe.1 -s 1024000 -b 32 <<!
!
However I fear that the split and the dbexport get out of sync and make
the export process useless.
Is there a better or cleaner way to do this ?
Thanks and regards
Kenneth
I had a
similar problem a while back and I used HPL to unload the large
tables,
dropped them, and used dbexport to get the rest of the tables. Worked well
because
I didn't want to have to set up HPL jobs for hundreds of small tables, and
couldn't dbexport the entire database.
HTH,
-Darryl
-----Original Message-----
From: forum.subscriber@iiug.org
To: ids@iiug.org
Sent: 5/26/2004 5:39 AM
Subject: exporting a 35G database [3024]
People,
We are tring to export a 35G informix 7.30 database to importing again
on an infromix 9.4 database.
The 7.3 database resides on a NCR Unix machine that does not support
files larger then 2G (unfortunately we have 5 tables larger then this
limit). Due to incompatible tape drives fitted on the machines we cannot
perform an export to tape and import it again.
What I was thinking is as follows
create a unix pipe (mknod mypipe.1 p)
In the backgroud start a process that reads data from the pipe.
dd if=mypipe.1 | split -a 5 -b 1024m &
and start the export
dbexport -d se4rt -t mypipe.1 -s 1024000 -b 32 <<!
!
However I fear that the split and the dbexport get out of sync and make
the export process useless.
Is there a better or cleaner way to do this ?
Thanks and regards
Kenneth
____________________________________________________________________________
The information contained in this communication may be confidential, is
intended only for the use of the recipient named above, and may be legally
privileged. If the reader of this message is not the intended recipient,
you are hereby notified that any dissemination, distribution, or copying of
this communication, or any of its contents, is strictly prohibited. If you
have received this communication in error, please re-send this communication
to the sender and delete the original message and any copy of it from your
computer system.
Thank you.
For more information please visit us at http://www.piperrudnick.com
____________________________________________________________________________
KENNETH,
You didn't mention what platform You are going to use for 9.40.
I suppose, it is not NCR, because there is no 9.40 for NCR.
If the platform You are going to migrate to is Sun, IBM or any
other supporting files larger then 2GB You can use the following approach.
Use 9.40 'dbexport' on remote host to connect to 7.30 database to
extract data from it into flat UNL files, located on the New machine.
This must work, and You should be able to export tables into files
larger then 2GB (9.40 dbexport supports large files)
Then, after properly editing SQL script generated by dbexport, just
reconnect to the 9.40 engine and import Your data into 9.40 engine.
------------------------------------------
Alexey Sonkin
> -----Original Message-----
> From: KENNETH PENZA [mailto:kenneth.penza@gov.mt]
>
> People,
>
> We are tring to export a 35G informix 7.30 database to importing again on
> an infromix 9.4 database.
> The 7.3 database resides on a NCR Unix machine that does not support files
> larger then 2G (unfortunately we have 5 tables larger then this limit).
> Due to incompatible tape drives fitted on the machines we cannot perform
> an export to tape and import it again.
>
> What I was thinking is as follows
> create a unix pipe (mknod mypipe.1 p)
>
> In the backgroud start a process that reads data from the pipe.
> dd if=mypipe.1 | split -a 5 -b 1024m &
>
> and start the export
> dbexport -d se4rt -t mypipe.1 -s 1024000 -b 32 <<!>
> !
>
> However I fear that the split and the dbexport get out of sync and make
> the export process useless.
>
> Is there a better or cleaner way to do this ?
>
> Thanks and regards
> Kenneth
Alexey,
I have tried to perform the dbexport from 9.4 connecting to the 7.3 database.
However this does not work, as Informix have introduced new system tables that
are used by the dbexport program. I have tried to create two tables but there
are more I simply gave up.
dbexportacross servers does only work within a product family. 7.x or 9.x
So you have to use HPL to transfer data.
Gerd
KENNETH PENZA wrote:
>Alexey,
>
>
> I have tried to perform the dbexport from 9.4 connecting to the 7.3
database. However this does not work, as Informix have introduced new system
tables that are used by the dbexport program. I have tried to create two
tables but there are more I simply gave up.
>
>
>
>
>
Let me understand. You are using the 9.4 version of dbexport to try to export
from a 7.3 database. As you are seeing this cannot be done. You can try my
dbexport/dbimport replacement utility package, myexport, which uses my dbschema
replacement utility myschema. Since myschema was written to support ALL
Informix versions from SE 5.xx through IDS 9.4x it autodetects the database's
server release. You may have to edit the myexport/myimport script to include a
-h servername option or set INFORMIXSERVER to the 7.3x servername, otherwise it
should work well. You can download the myexport package from the IIUG Software
Repository, myexport also requires the following packages: my utils2_ak package
(for myschema) and Jonathan Leffler's sqlcmd package (v5.7 or later).
Art S. Kagel
----- Original Message -----
From: Kenneth Penza <kenneth.penza@gov.mt>
At: 5/27 7:39
> Alexey,
>
>
> I have tried to perform the dbexport from 9.4 connecting to the 7.3 database.
> However this does not work, as Informix have introduced new system tables
that
> are used by the dbexport program. I have tried to create two tables but there
> are more I simply gave up.
Kenneth,
Sorry for the misleading advice.
I tested that approach across 9.xx,
and forgot about 7.x <-> 9.x system table discrepancy...
Any way, another extension to that approach might work for You.
In fact, some other utilities (like dbexport) also support
export to files larger 2GB. 9.40 'dbexport' is definitely able to
connect to 7.30 - at least, to 'unload to... ' tables.
I still think that it doesn't make sense to use 'onpload' -
for mid-size 35GB database, the export speed should not be dramatic
between parallel 'onpload' export or single-stream 'unload to..'
export and gain in speed doesn't worth programming efforts.
You can use 'dbschema' to generate the database schema for the new database
and generate scripts for export/import the following way (see bolow)
Then, run that script from 'dbexport'
Dbschema-generated script should be broken into two parts:
- table creation;
- indexes, constraints, procedures, triggers, etc.
In the new database, first create tables (without constraints), then load
data, then create indexes, constraints, etc
The script below is using 9.40-specific lvarchar type.
Please, change it to varchar to run against 7.xx or create
schema in 9.40 first and run the script against 9.40.
Script is tested agains 9.x
------------------ SCRIPT FOR LOAD/UNLOAD SCRIPT GENERATION ----
create function db_column_list (tname varchar(200)) returning lvarchardefine res lvarchar;
define col varchar(200);
define cnum, cnt int;
LET res=""; LET cnt = 0;
set isolation to dirty read;foreach
select colname, colno into col, cnum from 'informix'.syscolumns c,'informix'.systables t
where c.tabid = t.tabid AND t.tabname = tname order by colno
if (cnt != 0) then LET res = res || ","; else LET cnt = 1; end if;
let res = res || col;
end foreach;
return res;
end function;
unload to 'db_unload_all.sql' delimiter ";"select
"unload to '" || tabname || ".unl' select " || db_column_list(tabname) ||
" from " || tabname
from 'informix'.systables
where tabid>100 and tabtype='T'
order by 1;
unload to 'db_load_all.sql' delimiter ";"select
"lock table " || tabname || " in exclusive mode",
" load from '" || tabname || ".unl' insert into " || tabname || "(" ||
db_column_list(tabname) || ")",
" unlock table " || tabname
from 'informix'.systables
where tabid>100 and tabtype='T'
--order by tabname -- uncomment for 9.40 only
;
unload to 'db_updstat_all.sql' delimiter ";"select "update statistics high for table " || tabname
from 'informix'.systables
where tabid>100 and tabtype='T'
--order by tabname -- uncomment for 9.40 only
;
drop function db_column_list;
------------------------------------------
Alexey Sonkin
> From: Kenneth Penza <kenneth.penza@gov.mt>
> At: 5/27 7:39
>
> Alexey,
>
>
> I have tried to perform the dbexport from 9.4 connecting to the 7.3
> database.
> However this does not work, as Informix have introduced new system
> tables that
> are used by the dbexport program. I have tried to create two tables but
> there are more I simply gave up.
>