 |
 |
Archives of the TeradataForum
Message Posted: Thu, 13 May 2004 @ 18:20:39 GMT
| Subj: | | Re: Fastexport Suggestions Needed |
| |
| From: | | Coffing Christopher L |
Here is a macro that I use to export an entire database. If builds the syntax and stores it in an export_build_tbl... You can then extract
the portions of the exports that you are interested in running..
If you want to run them all you can extract them all and then run...
It builds the FastExport, MultiLoad and the BAT files to run them...
I have a program that will strip all of these files apart into separate files for running also if anyone is interested...
I hope this helps...
drop table macro_db.export_build_tbl;
CREATE SET TABLE macro_db.export_build_tbl ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
databasename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
tablename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
sekwense CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
commandline VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( databasename, tablename ,sekwense );
replace macro macro_db.FastExport_Mload_InS_gen_db
(DBe char(30), IDe char(30), PWe char(30),
TDPe char(30), DBm char(30), IDm char(30), PWm char(30),
TDPm char(30), FEDIR char(30) )
as (
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00100',
'**'||trim(tablename)||'.fexp'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00105',
'.LOGTABLE'||trim(:DBe)||'.'||trim(substr(tablename,1,26))||'_log'||' ;'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00110',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00115',
'.LOGON'||trim(:TDPe)||'/'||trim(:IDe)||','||trim(:PWe)||' ;'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00120',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00125',
'.BEGIN EXPORT '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00130',
'SESSIONS 20 ;'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00135',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00140',
'.EXPORT OUTFILE'||trim(:FEDIR)||trim(tablename)||'.dat '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00142',
'mode record format fastload ; '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'00145',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct c.databasename, c.tablename,'01'||(substr(cast(c.columnid as char(6)),3,4)),
(CASE when c.columnid = m.columnid
then 'SELECT ' else ' ,'
end)||trim(c.columnname)
from dbc.COLUMNS c,
(select tablename, min(columnid) from dbc.columns
where databasename = :DBe
group by 1) m (tablename, columnid)
where c.databasename = :DBe
and c.tablename = m.tablename
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'01500',
'FROM '||trim(databasename)||'.'||trim(tablename)||';'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'01505',
' '
from dbc.tables
where databasename = :DBe
;
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'01510',
'.END EXPORT ;'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'01515',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'01520',
'.LOGOFF ;'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'01530',
'**'||trim(tablename)||'.mld'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'01535',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'02000',
'.logtable'||trim(:DBm)||'.'||trim(substr(tablename,1,26))||'_log;'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'02500',
'.logon'||trim(:TDPm)||'/'||trim(:IDm)||','||trim(:PWm)||';'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'02750',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'03000',
'database '||trim(:DBm)||';'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'03250',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'03500',
'DROP TABLE'||trim(:DBm)||'.ET_'||trim(substr(tablename,1,27))||';'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'04000',
'DROP TABLE'||trim(:DBm)||'.UV_'||trim(substr(tablename,1,27))||';'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'04500',
'DROP TABLE'||trim(:DBm)||'.WT_'||trim(substr(tablename,1,27))||';'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'04750',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'05000',
'.BEGIN IMPORT MLOAD TABLES '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'05500',
' '||trim(:DBm)||'.'||trim(tablename)
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'06000',
' WORKTABLES'||trim(:DBm)||'.WT_'||trim(tablename)
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'06500',
' ERRORTABLES'||trim(:DBm)||'.ET_'||trim(tablename)
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'07000',
' '||trim(:DBm)||'.UV_'||trim(tablename)
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'07250',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'07500',
' SESSIONS 8 ERRLIMIT 50 AMPCHECK NONE;'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'07750',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'08000',
'.layout InputLayout;'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'10'||(substr(cast(columnid as char(6)),3,4)),
'.FIELD '||columnname||' * '||
(CASE when columntype='I1' then 'BYTEINT; '
when columntype='I2' then 'SMALLINT; '
when columntype='I' then 'INTEGER; '
when columntype='DA' then 'CHAR(10); '
when columntype='AT' then 'VARCHAR(' || trim(columnlength) || ');'
when columntype='TS' then 'CHAR(26);'
when columntype='D' then 'DECIMAL(' ||
trim(decimaltotaldigits) || ',' || trim(decimalFractionalDigits) || ');
'
when columntype='CF' then 'CHAR(' || trim(columnlength) || ');'
when columntype='CV' then 'VARCHAR(' || trim(columnlength) || ');'
END)
from dbc.columns,
(select tablename, min(columnid)
from dbc.columns
where databasename =:DBe
group by 1)
as temp1 (tablenm, mincolid)
where databasename = :DBe
and tablename = tablenm
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'20250',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'20500',
'.dml label Insertit ; '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'21000',
'insert into ' || trim(tablename) || '('
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'3'||(substr(cast(columnid as char(6)),3,4)),
(CASE
when mincolid = columnid then ' ' else ' ,'
END) ||
trim(columnname) ||
'= '||':' ||
trim(columnname)
from dbc.columns,
(select tablename, min(columnid)
from dbc.columns
where databasename =:DBe
group by 1)
as temp4 (tablenm, mincolid)
where databasename = :DBe
and tablename = tablenm
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'40250',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'60500',
' ); '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'60750',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'61000',
'.import infile '||trim(:FEDIR)||trim(tablename)||'.dat'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'61500',
' format FASTLOAD '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'62000',
' layout InputLayout'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'62500',
' apply Insertit; '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'62750',
' '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'63000',
'.end mload; '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, tablename,
'63500',
'.logoff; '
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, '00000',
'70000',
'**'||trim(:DBm)||'_mload.bat'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, '00001',
'71000',
'mload < '||trim(tablename) || '.mld > '|| trim(substr(tablename,1,26)) || '_mld.log'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, '00002',
'70000',
'**'||trim(:DBe)||'_fexp.bat'
from dbc.tables
where databasename = :DBe
;
--
insert into macro_db.export_build_tbl
Select distinct databasename, '00003',
'71000',
'fexp < '||trim(tablename) || '.fexp > '|| trim(substr(tablename,1,26)) || '_fex.log'
from dbc.tables
where databasename = :DBe
;
--
);
exec macro_db.FastExport_Mload_InS_gen_db
(DBe= 'export_database', IDe= 'export_id', PWe= 'export_password',
TDPe= 'export_tdp',
DBm= 'load_database', IDm= 'load_id', PWm= 'load_password',
TDPm= 'load_tdp',
FEDIR= 'D:\');
select commandline from macro_db.export_build_tbl
order by tablename, sekwense
where databasename = 'something'
and tablename = 'something'
/*delete from macro_db.export_build_tbl all;*/
| |
