Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 13 May 2004 @ 18:20:39 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


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;*/


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016