All Forums Tools
padhia 35 posts Joined 06/10
26 May 2016
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

feinholz 1234 posts Joined 05/08
26 May 2016

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

padhia 35 posts Joined 06/10
27 May 2016

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.

padhia 35 posts Joined 06/10
01 Jun 2016

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

You must sign in to leave a comment.