RE: exporting a 35G database [3047]
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.
>
sending to informix-list
On Fri, 28 May 2004 12:18:50 -0400, Alexey Sonkin wrote:
OR, just get my package myexport (along with the utils2_ak & sqlcmd packages
which support it), which does all of that for you (and can do the unload/load
in parallel using hploader to boot - Zoom Zoom)!
OR, get my utils4_ak package which includes awk scripts that do most of this
for you by post-processing dbschema/myschema output.
Art S. Kagel
> 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 lvarchar define 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.
>>
>>
> sending to informix-list