Using TPT templates to copy more than one table in a script.

Tools
Teradata Employee

Using TPT templates to copy more than one table in a script.

I am using following TPT script to load two tables from another Teradata system. 

DEFINE JOB TPTLoad
(
SET TargetWorkingDatabase = 'TGTDB';

SET LoadErrorLimit = 1;
SET DDLErrorList = ['3807','3624'];

SET TargetTable_1 = 'TAB1';
SET TargetTable_2 = 'TAB2';

STEP LoadTbl_1 (
APPLY $INSERT @TargetTable_1
TO OPERATOR (
$LOAD ATTR( TargetTable=@TargetTable_1 )
)
SELECT *
FROM OPERATOR (
$EXPORT(@TargetTable_1) ATTR(SelectStmt='Select * From ' || 'SRCDB.' || @TargetTable_1)
);
);

STEP LoadTbl_2 (
APPLY $INSERT @TargetTable_2
TO OPERATOR (
$LOAD ATTR( TargetTable=@TargetTable_2 )
)
SELECT *
FROM OPERATOR (
$EXPORT(@TargetTable_2) ATTR(SelectStmt='Select * From ' || 'SRCDB.' || @TargetTable_2)
);
);
);

There are no issues loading the first table, however, when TPT loads the second table, it still uses the schema of the first table and I get following error messages:

TPT_INFRA: TPT02640: Error: Conflicting column count. Source column count (15) Target column count (18).
$EXPORT: TPT12108: Output Schema does not match data from SELECT statement
**** 16:27:15 TPT12108: Output Schema does not match data from SELECT statement

Any suggestion on what's wrong with the TPT script as coded? I am using TPT 15.10 client targeting Teradata 15.10 systems.

Thanks

7 REPLIES
Teradata Employee

Re: Using TPT templates to copy more than one table in a script.

Well, a few things, but we will take them one step at a time.

By using the "simplified" script language and "templates", the idea is for TPT to generate the schema for you.

And we have several ways of figuring out from where the schema should be taken.

The issue with the script, is that you are introducing many different items in the script and it looks like we have a problem figuring out what you want to do (yes, a bug on our end that we will have to figure out, but still the script is a little more confusing than it has to be).

In order to figure out what we are doing wrong, it would help if I could get the output of the source and target DDLs for the 4 tables (2 source tables and 2 target tables).

In my opinion, the best way to code this script would be as follows:

DEFINE JOB TPTLoad
(
SET TargetWorkingDatabase = 'TGTDB';

SET LoadErrorLimit = 1;
SET DDLErrorList = ['3807','3624'];

SET TargetTable_1 = 'TAB1';
SET TargetTable_2 = 'TAB2';

DEFINE SCHEMA schema_1 FROM TABLE 'SRCDB'. || @TargetTable_1;
DEFINE SCHEMA schema_2 FROM TABLE 'SRCDB'. || @TargetTable_2;

STEP LoadTbl_1 (
APPLY $INSERT TO OPERATOR ($LOAD
ATTR( TargetTable=@TargetTable_1 )
)
SELECT * FROM OPERATOR ($EXPORT(schema_1)
ATTR(SelectStmt='Select * From ' || 'SRCDB.' || @TargetTable_1)
);
);

STEP LoadTbl_2 (
APPLY $INSERT TO OPERATOR ($LOAD
ATTR( TargetTable=@TargetTable_2 )
)
SELECT * FROM OPERATOR ($EXPORT(schema_2)
ATTR(SelectStmt='Select * From ' || 'SRCDB.' || @TargetTable_2)
);
);
);

This way you are letting us generate the schemas for you, based on the table you would like.

And you are associating each schema with the different Export operators.

-- SteveF
Teradata Employee

Re: Using TPT templates to copy more than one table in a script.

Steve,

Thank you for the prompt reply. Your version of the script indeed works. In my zeal to provide only the minimum needed script for diagnosis, I in fact supplied a script that's not having the problem. Please accept my apologies. I have attached an actual script that indeed has the above described problem.

DEFINE JOB TPTLoad
(
SET TargetWorkingDatabase = 'TGTDB';
SET UtilDatabase = 'UTILDB';

SET LoadErrorLimit = 1;
SET DDLErrorList = ['3807','3624'];

SET TargetTable_1 = 'TAB1';
SET TargetTable_2 = 'TAB2';

DEFINE SCHEMA Schema_1 FROM TABLE @TargetWorkingDatabase || '.' || @TargetTable_1;
DEFINE SCHEMA Schema_2 FROM TABLE @TargetWorkingDatabase || '.' || @TargetTable_2;

STEP TruncTbl_1 (
APPLY ('DELETE FROM ' || @TargetWorkingDatabase || '.' || @TargetTable_1)
TO OPERATOR ($DDL ATTR(QueryBandSessInfo='TPTJobId=' || $JOBID || ';'));
);

STEP LoadTbl_1 (
APPLY $INSERT @TargetTable_1
TO OPERATOR (
$LOAD ATTR( TargetTable=@TargetTable_1,
QueryBandSessInfo='TPTJobId=' || $JOBID || ';UtilityDataSize=NEDIUM;',
LogTable=@UtilDatabase || '.' || @TargetTable_1 || '_RL',
ErrorTable1=@UtilDatabase || '.' || @TargetTable_1 || '_ET',
ErrorTable2=@UtilDatabase || '.' || @TargetTable_1 || '_UV' )
)
SELECT *
FROM OPERATOR (
$EXPORT(Schema_1) ATTR(SelectStmt='Select * From ' || 'SRCDB.' || @TargetTable_1)
);
);

    STEP TruncTbl_2 (

        APPLY ('DELETE FROM ' || @TargetWorkingDatabase || '.' || @TargetTable_2)

            TO OPERATOR ($DDL ATTR(QueryBandSessInfo='TPTJobId=' || $JOBID || ';'));

    );

STEP LoadTbl_2 (

APPLY $INSERT @TargetTable_2

TO OPERATOR (

$LOAD ATTR( TargetTable=@TargetTable_2,

QueryBandSessInfo='TPTJobId=' || $JOBID || ';UtilityDataSize=NEDIUM;',

LogTable=@UtilDatabase || '.' || @TargetTable_2 || '_RL',

ErrorTable1=@UtilDatabase || '.' || @TargetTable_2 || '_ET',

ErrorTable2=@UtilDatabase || '.' || @TargetTable_2 || '_UV' )

)

SELECT *

FROM OPERATOR (

$EXPORT(Schema_2) ATTR(SelectStmt='Select * From ' || 'SRCDB.' || @TargetTable_2)

);

);

);

Here are the table definitions. Respective table definitions match in both Teradata systems.

CREATE MULTISET TABLE TAB1
( MNTRNG_ACCT_SKEY INTEGER NOT NULL
, MNTRNG_NBR_TXT VARCHAR(25) CS NOT NULL
, FRST_NM VARCHAR(100) CS NOT NULL
, LST_NM VARCHAR(100) CS NOT NULL
, PHN_NBR_TXT VARCHAR(15) CS NOT NULL
, STRT_NBR_TXT VARCHAR(20) CS NOT NULL
, STRT_NM VARCHAR(100) CS NOT NULL
, CITY_NM VARCHAR(100) CS NOT NULL
, ST_NM VARCHAR(5) CS NOT NULL
, ZIP VARCHAR(20) CS NOT NULL
, ACCT_TYP VARCHAR(10) CS NOT NULL
, BILL_ACTV_FLG CHAR(1) CS NOT NULL
, MNTRNG_ACTV_FLG CHAR(1) CS NOT NULL
, CRE_TS TIMESTAMP(6) NOT NULL
, UPDT_TS TIMESTAMP(6) NOT NULL
) PRIMARY INDEX(MNTRNG_ACCT_SKEY);

CREATE MULTISET TABLE TAB2
( TM_SKEY INTEGER NOT NULL
, HRS_NBR SMALLINT NOT NULL
, MINT_NBR SMALLINT NOT NULL
, SEC_NBR SMALLINT NOT NULL
, DAY_HRS_NBR SMALLINT NOT NULL
, DAY_MINT_NBR SMALLINT
, DAY_SEC_NBR INTEGER
, TM_24_HRS_DESC VARCHAR(12) CS NOT NULL
, TM_12_HRS_DESC VARCHAR(15) CS
, AM_PM_IND VARCHAR(2) CS
, GMT_OFST_HRS_NBR INTEGER
, AST_OFST_HRS_NBR INTEGER
, CST_OFST_HRS_NBR INTEGER
, MST_OFST_HRS_NBR INTEGER
, PST_OFST_NBR INTEGER
, HST_OFST_HRS_NBR INTEGER
, CRE_TS TIMESTAMP(6) NOT NULL
, UPDT_TS TIMESTAMP(6) NOT NULL
) PRIMARY INDEX(TM_SKEY);

If it is of any help with diagnosis, if I comment out the TruncTbl_2 step, the script works fine, that is, LoadTbl_2 step is able to obtain the right schema.

Thanks again.

Teradata Employee

Re: Using TPT templates to copy more than one table in a script.

Hello Steve,

Were you able to reproduce the error with above script? Any idea if this is a TPT bug or a script error?

Thanks

Enthusiast

Re: Using TPT templates to copy more than one table in a script.

Hi Steve,

I was reading this TPT string and wondering if I could use something like this to do just an export and export multiple tables to pipe delimited files.  I've been working on SQL to generate the TPT scripts for each table but if there is a cleaner way to do this, I'm all ears! :)

Thanks,

     Mike S.

Teradata Employee

Re: Using TPT templates to copy more than one table in a script.

TPT allows the combination of any producer operator to any consumer operator.

So, yes of course you can export data from tables and write out to delimited output files.

(I feel as if this was too easy of an answer and so maybe I am missing something from your question. :) )

-- SteveF
Enthusiast

Re: Using TPT templates to copy more than one table in a script.

Thanks, glad to know it's possible.  I'm not familiar with TPT and have had to tweak my script a number of times to get the delimited file to work, like using the EXPORT operator and not the SQL Selector.  I'm defining the table layout for each table and creating a single script for each table to then create a single file for each table.  If I go this route of using TPT to generate the table layout it would be much easier.  Can you share an example of such a TPT script to EXPORT 100 tables from a database, generating the schema for each table and then dumping each table's output to an individual delimited file?

Your help is much appreciated as I am a bit under the gun here...trying to get this done so I can prep for Hurrican Irma here in Florida! :))

Thanks,

        Mike

Teradata Employee

Re: Using TPT templates to copy more than one table in a script.

TPT itself does not support looping, but if you write a shell-script to run TPT jobs, then you can write a simple, generic, TPT script

that can take the table name and output file name as job variables, and have the shell-script drive for as many tables as you wish.

 

The script can look something like this:

 

DEFINE JOB <some-name>
(
   APPLY TO OPERATOR ($FILE_WRITER)
   SELECT * FROM OPERATOR ($EXPORT);
);

and then create a job variable file with the logon credentials, the SELECT statement and the file name.

 

The job variable would look something like this (you will have to consult the documentation for everything you need):

 

ExportTdpId = 'xxxx'
ExportUserName = 'xxxx'
ExportUserPassword = 'xxxx'
ExportSelectStmt = 'xxxxxx'
FileWriterFileName = 'xxxxxx'
FileWriterFormat = 'delimited'
FileWriterTextDelimiter = '|'
. . . . . .

You would obviously provide the information where the 'xxxx' are, and provide what field delimiter you would like to use.

 

-- SteveF