Archives of the TeradataForum

Message Posted: Mon, 07 Jul 2003 @ 16:39:17 GMT

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

<-- Anonymously Posted: Monday, July 07, 2003 11:44 -->

Hi Bob,

I have a perl script which does pretty much what you are looking for. It creates a fastexport and mload and a ksh script to run both using a pipe. Once the 3 scripts are created you need to change the system, userId and password in the fexp_*.ksh and mload_*.ksh before running the script movep2t_$tname.ksh. Let me know if you have any problems.

#! /usr/local/bin/perl -w

# use strict;
use DBI;
use DBD::Teradata;

gen_fexp_sql ();

die ("end of program ");

sub gen_fexp_sql {

if ($#ARGV != 2)
{ print "Usage: perl crp2tscripts.pl Dbname Tblname rt2 \n"; die "rerun
program \n"};

my $dbase = $ARGV[0];
my $tname = $ARGV[1];
my $systm = $ARGV[2];
my $tname20  = substr $ARGV[1], 0, 20;
$tname20  =~ s/(.*)_$/$1/;
open (CMPSQL, "> /u/myuserID/NCR/fexp_$tname.ksh");
open (MLDSQL, "> /u/myuserID/NCR/mld_$tname.ksh");
open (RUNSCRIPT, "> /u/myuserID/NCR/movep2t_$tname.ksh");
my @a = ' ';
my @b = '';
my @mlout = '';
my @mldml = '';
my $i = '';
my $n = 0;
my @c = '';
my $linecount = 0;

my $dbh = DBI->connect("dbi:Teradata:${systm}cop1", "myuserID",
"mypasswd",
{'RaiseError' => 1}) or die;
my $line1 = '';
my $sth;

# '\,' \|\| COLUMNNAME
if ($sth = $dbh->prepare("
select
COLUMNNAME
,':'
,'.FIELD ' || COLUMNNAME      ||  '   *    '
|| (case when columntype = 'd'
then
'decimal (' || decimaltotaldigits || ',' || decimalfractionaldigits ||
')'
else
  (case when columntype = 'cf'
   then 'CHAR('  || columnlength || ')'
   when columntype = 'cv'
   then 'VARCHAR('  || columnlength || ')'
   when columntype = 'i'
    then 'INTEGER'
 when columntype = 'i2'
    then 'SMALLINT'
 when columntype = 'BF'
   then 'BYTE('  || columnlength || ')'
 when columntype = 'F'
    then 'FLOAT'
 when columntype = 'i1'
    then 'BYTEINT'
 when columntype = 'at'
    then 'CHAR(8)'
 when columntype = 'da'
    then 'DATE'
   when columntype = 'ts'
     then 'CHAR(' || columnlength || ')'
  end)
end ) ||
';'
,':'
, columnname || ' =  :' || columnname
from dbc.columns a
where

databasename = \'$dbase\' and tablename = \'$tname\' order by columnid;"))

        {
        $sth->execute;

        print( "@{$sth->{NAME}}\n" );
        while (my $ary_ref = $sth->fetchrow_arrayref ) {
#               print CMPSQL "@$ary_ref \n";
                $a[$#a++]  = $ary_ref->[0];
                $mlout[$#mlout++]  = $ary_ref->[2];
                $mldml[$#mldml++]  = $ary_ref->[4];
                $n  = $#a; $n = $n - 1;
#               print CMPSQL " in init array $a[$n] \n";
#               $a[$#a++]  = DBI::neat_list($ary_ref);
        }
        $sth->finish;

print CMPSQL <  fexp_$tname.out
.logtable test_err_db.logfexp_$tname20;
.logon nsdw/myuserID,mypasswd;
.begin export sessions 6;
.export outfile nmpipe_$tname20 mode indicator;
Lock $dbase.$tname for access
EOF
        $linecount = 0;
        print CMPSQL "Select  \n";

        for ($i= 0; $i < $#a;  $i++)
           {@b = split(/:/,$a[$i]);
                if ($linecount == 0)
                    {print CMPSQL " $a[$i] \n";}
                else
                    {print CMPSQL " ,$a[$i] \n";};
                $linecount = $linecount + 1;
           };
        print CMPSQL "From $dbase.$tname\; \n.End export\; \n.Logoff\;
\n!~";

print MLDSQL < mld_$tname.out
.logtable test_err_db.Mldlog_$tname20;
.logon rt2/myuserID,mypasswd;
.BEGIN IMPORT MLOAD
       TABLES $dbase.$tname
   WORKTABLES $dbase.WT_$tname20
   ERRORTABLES test_err_db.ET_$tname20
               test_err_db.UV_$tname20 sessions 8;
MLDEOF
        $linecount = 0;
        print MLDSQL "\n.Layout layout_$tname20 indicators\;  \n";

       for ($i= 0; $i < $#mlout;  $i++)
           {
                if ($linecount == 0)
                    {print MLDSQL " \n$mlout[$i] \n";}
                else
                    {print MLDSQL " $mlout[$i] \n";};
                $linecount = $linecount + 1;
           };
        print MLDSQL "\n.DML label DML_$tname20\; \n Insert into $dbase.$tname \(";
        $linecount = 0;
       for ($i= 0; $i < $#mldml;  $i++)
           {
                if ($i == 0)
                    {print MLDSQL " \n$mldml[$i] \n";}
                else
                    {print MLDSQL " ,$mldml[$i] \n";};
                $linecount = $linecount + 1;
           };
        print MLDSQL "\)\;\n.Import Infile nmpipe_$tname20 \n   Layout
layout_$tname20 \n   Apply DML_$tname20 \; \n.End Mload \; \n!~";

print RUNSCRIPT <

 
 
 
 


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