Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Hi Tony,

I am very upset by the fact that the SCHEMA is required with operator EXPORT and LOAD.

We use a  generic script  export-to-load  based on FastExport and FastLoad and I dont need to specify the schema since I export/load all the colunms of the table.

I want to write a generic export-to-load job with TPT (without landing data). Because of the generic behavior I can't not define the SCHEMA in the job.

question :

Is it possible to specify in my export_to_load.job a schema which would be defined in an another file ? I mean is it possible to specify an import file in my job ?

Thanks for all your suggestions

Vincent

Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Vincent you can use a view with a template. Here is what I do.

DEFINE JOB Export_Data
(

STEP Export_Table
    ( APPLY TO OPERATOR
            ($FILE_WRITER[1]
  ATTR (
   FileName       = '/load/TD_QA/EDWRPT/EDWRPT.RPT_DYNAMIC_DATE_ANCHOR.DATA'
  ,Format         = 'delimited'
  ,QuotedData     ='Y'
  ,OpenQuoteMark  = 'ô'
  ,CloseQuoteMark = 'ô'
  ,TextDelimiter  = 'î'
  ,OpenMode       = 'write'
  ))   
  SELECT
  *
  FROM OPERATOR
                     ($EXPORT[1]
   ATTR (
    SpoolMode            ='NoSpool'
   ,TdpId                = @TdServer
   ,UserName             = @TdUserName
   ,UserPassword         = @TdPassword
   ,MaxSessions       = 10
   ,QueryBandSessInfo    = '' 
   ,SelectStmt           = 'SELECT * FROM HSTLD.X_RPT_DYNAMIC_DATE_ANCHOR47;'
   )
                      );
);

STEP Drop_View
     ( APPLY
           ('DROP VIEW ' || 'HSTLD' || '.X_RPT_DYNAMIC_DATE_ANCHOR47;')
       TO OPERATOR
             ($DDL
      ATTR
                        (
                UserName = @TdUserName,
    UserPassword = @TdPassword,
    TdpId = @TdServer
                         )
             );
);

);

I also do one better and have a procedure that generates my view for me.

DEFINE JOB Create_View
(
STEP Create_View
     ( APPLY
               ('CALL SYSDBA.CREATE_EXPORT_VIEW('||'''EDWRPT'''||','||'''RPT_DYNAMIC_DATE_ANCHOR'''||','||'''X_RPT_DYNAMIC_DATE_ANCHOR47'''||','||'''HSTLD'''||','||'''N'''||','||''''''||');')
       TO OPERATOR
       ($DDL
        ATTR
           (
            UserName = @TdUserName,
            UserPassword = @TdPassword,
            TdpId = @TdServer
           )
        );
      );

);

I use this procedure to generate my export views:

REPLACE PROCEDURE CREATE_EXPORT_VIEW

(

IN TGT_DB VARCHAR(30),

IN TGT_TB VARCHAR(30),

IN TMP_TBL_NAME VARCHAR(30),

IN WRK_DB VARCHAR(30),

IN LF_FLG CHAR(1),

IN UNLOAD_WHERE_CLAUSE VARCHAR(12000)

)

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Procedure Name : CREATE_EXPORT_VIEW

-- Purpose : Will Create an Export View for a specified table.

-- Version : V 1.00

-- Create Date : 2014-09-03

-- Created by : Fred Newbrough

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Parameters : TGT_DB = Database Name

-- : TGT_TB = Table Name

-- : TMP_TBL_NAME = Name of View to be created

-- : WRK_DB = Database where view will be created.

-- : CRG_STR_FLG = Carriage Return/Line Feed strip flag.

-- : Unload Where Clause = Constraint for view.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Change History

-- 2014-08-27 Fred Newbrough Added Line Feed/Carriage Return strip flag.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

--====================== STORED PROCEDURE CODE STARTS HERE

BEGIN

DECLARE v_view VARCHAR(31000);

SET v_view='';

IF LF_FLG = 'N' THEN

FOR loopvar1 AS cur1 CURSOR FOR

SELECT view_txt FROM

(SELECT section,columnid,view_txt

FROM

(SELECT

1 (INT) AS section, ColumnId,

CASE WHEN ROW_NUMBER () OVER( ORDER BY ColumnId) = 1 THEN (CASE WHEN ColumnType='DA' THEN 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT '||TRIM(ColumnName)||'( FORMAT '''||TRIM(ColumnFormat)||''')'||'(CHAR('||TRIM(CHAR_LENGTH(TRIM(ColumnFormat))) || '))' WHEN ColumnType='D' THEN 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT TRIM('||TRIM(ColumnName)||'(VARCHAR(40))) '||TRIM(ColumnName) ELSE 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT '||TRIM(ColumnName) END) ELSE ','||(CASE WHEN ColumnType='DA' THEN TRIM(ColumnName)||'( FORMAT '''||TRIM(ColumnFormat)||''')'||'(CHAR('||TRIM(CHAR_LENGTH(TRIM(ColumnFormat))) || '))' WHEN ColumnType='D' THEN 'TRIM('||TRIM(ColumnName)||' (VARCHAR(40))) '||TRIM(ColumnName) ELSE ColumnName END) END (VARCHAR(1000)) AS view_txt

FROM DBC.COLUMNS

WHERE

DatabaseName =TRIM(:TGT_DB)

AND

TABLENAME=TRIM(:TGT_TB)

UNION ALL

SELECT * FROM (SELECT 2 (INT) AS section,1 AS ColumnId, ' FROM '||TRIM(:TGT_DB)||'.'||TRIM(:TGT_TB)||'' AS view_txt) a ) b ) view_def

ORDER BY section,columnid

DO

SET v_view= (v_view|| loopvar1.view_txt);

END FOR;

END IF;

 

IF LF_FLG = 'Y' THEN

FOR loopvar1 AS cur1 CURSOR FOR

SELECT view_txt

FROM

(

SELECT section,columnid,view_txt

FROM

(

SELECT

1 (INT) AS section, ColumnId,

CASE WHEN ROW_NUMBER () OVER(

ORDER BY ColumnId) = 1 THEN (

CASE

WHEN ColumnType='DA' THEN 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT '||TRIM(ColumnName)||'( FORMAT '''||TRIM(ColumnFormat)||''')'||'(CHAR('||TRIM(CHAR_LENGTH(TRIM(ColumnFormat))) || ')) '||TRIM(ColumnName)

WHEN ColumnType='D' THEN 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT TRIM('||TRIM(ColumnName)||'(VARCHAR('||'40'||'))) '||TRIM(ColumnName)

WHEN ColumnType IN ('CV','CF') THEN 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT OREPLACE(OREPLACE(TRIM(TRANSLATE('||TRIM(ColumnName)||' USING LATIN_TO_UNICODE WITH ERROR)),CHR(13),''''),CHR(10),'''') (VARCHAR('||TRIM(ColumnLength+2 (FORMAT 'Z(20)') )||')) '||TRIM(ColumnName)

ELSE 'REPLACE VIEW '||TRIM(:WRK_DB)||'.'||TRIM(TMP_TBL_NAME) ||' AS LOCKING ROW FOR ACCESS SELECT '||TRIM(ColumnName)

END )

ELSE ','||(

CASE

WHEN ColumnType='DA' THEN TRIM(ColumnName)||'( FORMAT '''||TRIM(ColumnFormat)||''')'||'(CHAR('||TRIM(CHAR_LENGTH(TRIM(ColumnFormat))) || ')) '||TRIM(ColumnName)

WHEN ColumnType='D' THEN 'TRIM('||TRIM(ColumnName)||'(VARCHAR('||'40'||'))) '||TRIM(ColumnName)

WHEN ColumnType IN ('CV','CF') THEN 'OREPLACE(OREPLACE(TRIM(TRANSLATE('||TRIM(ColumnName)||' USING LATIN_TO_UNICODE WITH ERROR)),CHR(13),''''),CHR(10),'''') (VARCHAR('||TRIM(ColumnLength+2 (FORMAT 'Z(20)') )||')) '||TRIM(ColumnName)

ELSE ColumnName

END )

END (VARCHAR(1000)) AS view_txt

FROM DBC.COLUMNS

WHERE

DatabaseName =TRIM(:TGT_DB)

AND

TABLENAME=TRIM(:TGT_TB)

UNION

ALL

SELECT *

FROM (

SELECT 2 (INT) AS section,1 AS ColumnId, ' FROM '||TRIM(:TGT_DB)||'.'||TRIM(:TGT_TB)||'' AS view_txt) a ) b ) view_def

ORDER BY section,columnid

DO

SET v_view= (v_view|| loopvar1.view_txt);

END FOR;

END IF;

SET v_view=v_view||' '||COALESCE(UNLOAD_WHERE_CLAUSE,'')||';';

CALL DBC.SYSEXECSQL(v_view);

END

;

Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Vincent, you can use the TPT EasyLoader command called tdload. The TPT tdload command allows you to perform an export-to-load job (without landing data and without specifying the schema and without writing a TPT script).

Here is an example:

tdload --SourceTdpId MySourceTdpId --SourceUserName MySourceUserName --SourceUserPassword MySourceUserPassword --SourceTable MySourceTable --TargetTdpId MyTargetTdpId --TargetUserName MyTargetUserName --TargetUserPassword MyTargetUserPassword -t MyTargetTable

See the TPT Reference manual for more details.

Enthusiast

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Fnewbroug : thank you very much for your code

Tony : it didn't know tpt easyloader. 

This forum is very helpful:). thank you

Enthusiast

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Another thing about the SCHEMA and how to bypass it by using an OPERATOR template.

Obviously when using OPERATOR template such as $LOAD or $EXPORT, we don't need to define the SCHEMA :

set TargetTable = 'comdb.tab1' ;

set LogTable = 'comdb.tab1_LOG' ;

set ErrorTable1 = 'comdb.tab1_E1' ;

set ErrorTable2 = 'comdb.tab1_E2' ;

 APPLY $INSERT TO OPERATOR ($LOAD

        ATTR (

                UserName = @TERAUSER,

                UserPassword  =  @TERAPWD,

                PrivateLogName = 'LOAD.log',

                TdpId = @TERASYSTEM

             )              )

  SELECT * FROM OPERATOR ($EXPORT

        ATTR (

             UserName = @TERAUSER,

             UserPassword  =  @TERAPWD,

             PrivateLogName = 'EXPORT.log',

             TdpId = @TERASYSTEM ,

             SelectStmt= 'SELECT * FROM comdbrct.tab1;'

             )

why is that ?

Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

When you use the OPERATOR templates, you do not need to define the schema, because TPT can infer the schema based on certain criteria in the job. See the "Simplifying Scripts with Operator Templates and Generated Schemas" section in chapter 13 of the TPT User Guide for more details.

Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Tony can you please send me sample JCL script with TPT. So that I can understand how JCL is being used with TPT and what new commands in there. Thanks for your help.

Email: sikandar.nawaz.2012@gmail.com

Best Regards,

Sik

Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Any body who can help :( ?

Regards,

Sik

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Hi Tony,

I have two databases (around 10 schema) with two different AIX box with 200TB data and need move the Teradata database. There were created three TD databases and we are not going to move 200TB data into TD and it subset of data has to move to TD(around 150TB).Please let me the TPT will suitable for this size of data and TPT will suitable for initial load or need to check any other option for initial load.

Both are different data center and two different locations within USA.

Applicate your response.

Thanks,

Selvam

Highlighted
Teradata Employee

Re: Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk

Hi Selvam,

I have some questions:

1. Does "10 schema" mean "10 tables"?

2. Is the 200TB data on the 2 different AIX boxes?

3. Do you need to move the data from one Teradata database to another Teradata database?

4. What do you mean when you said "There were created three TD databases and we are not going to move 200TB data into TD and it subset of data ha to move to TD(around 150TB)"?

Thanks.