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

3 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