How to Simplify Database-to-Database Table Copying with Teradata Parallel Transporter (TPT) and tdload

The Teradata Parallel Transporter (TPT) is a Teradata Tools and Utilities (TTU) product. Teradata TPT offers under one roof an SQL-like scripting language that simplifies the syntax of old Teradata Utilities for handling external data (e.g., FastLoad, MultiLoad, TPump, BTEQ, and FastExport).

Copying Tables between Teradata Systems

A classic approach to perform a database-to-database table copy across two systems is to export the table into a file, transfer it to the target system, and import it into an empty table in the target system (see example TPT export and import scripts). The latest versions of the Teradata PT tools support operator templates (e.g., $LOAD and $INSERT), further simplifying tbuild scripts and reducing implementation and debugging times.

A mostly overlooked tool in the TPT arsenal is tdload. Compare this one-liner to copy a table from one system to another system with any FastLoad or TPT script you are manually maintaining:

tdload –SourceTdpid 127.0.0.19 –SourceUserName ETLsrc –SourceUserPassword ETL_SRC_PASS –SourceWorkingDatabase MySrcDB –SourceTable MySrcTbl –TargetTdpid 127.0.0.20 –TargetUserName ETLtrgt –TargetUserPassword ETL_TRGT_PASS –TargetWorkingDatabase MyTrgtDB –TargetTable MyTrgtTbl a_job_name

The command-line options are self-explanatory, and tdload –help provides additional details. In principle, tdload takes the source system (IP address or FQDN), logon user and password, database, and table to copy from and the respective targets. It acts as a wrapper to tbuild, passing the correct variables and operator templates, simplifying implementation, and saving debug effort.

The tool checks the target table contents and transparently decides to perform a bulk load (i.e., apply a $LOAD operator) or a transactional one (i.e., apply an $UPDATE operator). Then, it generates and executes an appropriate tbuild script, as shown in the simplified examples below.

TPT Fastload for Empty Tables

/* example with empty target table */

TDExpress1620_Sles11:~ # tdload --SourceTdpid 127.0.0.1 --SourceUserName dbc --SourceUserPassword dbc --SourceWorkingDatabase tuning --SourceTable country_code --TargetTdpid 127.0.0.1 --TargetUserName dbc --TargetUserPassword dbc --TargetWorkingDatabase tuning --TargetTable copytbl copy_MySrcTbl_MyTrgtTbl

Teradata Load Utility Version 16.20.00.02 64-BitTeradata Parallel Transporter Version 16.20.00.02 64-BitJob log: /opt/teradata/client/16.20/tbuild/logs/copy_MySrcTbl_MyTrgtTbl-3.outJob id is copy_MySrcTbl_MyTrgtTbl-3,

running on TDExpress1620_Sles11Teradata Parallel Transporter Load Operator Version 16.20.00.02$LOAD:
private log specified: LoadLogTeradata Parallel Transporter Export Operator Version 16.20.00.02$EXPORT: private log specified: ExportLog$LOAD: connecting sessions$EXPORT: connecting sessions$LOAD: preparing target table$LOAD: entering Acquisition Phase$EXPORT: sending SELECT request$EXPORT: entering End Export Phase$EXPORT: Total Rows Exported:  249$EXPORT: Total Rows Discarded: 0$LOAD: entering Application Phase$LOAD: Statistics for Target Table:  'copytbl'$LOAD: Total Rows Sent To RDBMS:      249$LOAD: Total Rows Applied:            249$LOAD: Total Rows in Error Table 1:   0$LOAD: Total Rows in Error Table 2:   0$LOAD: Total Duplicate Rows:          0$EXPORT: disconnecting sessions$LOAD: disconnecting sessions$EXPORT: Performance metrics:$EXPORT:     MB/sec in Export phase: could not be determined$EXPORT:     Elapsed time from start to Select phase:   4 second(s)$EXPORT:     Elapsed time in Select phase: < 1 second$EXPORT:     Elapsed time in Export phase: < 1 second$EXPORT:     Elapsed time from Export phase to end:   4 second(s)$EXPORT: Total processor time used = '0.044002 Second(s)'$EXPORT: Start : Mon Nov  2 17:44:57 2020$EXPORT: End   : Mon Nov  2 17:45:05 2020$LOAD: Performance metrics:$LOAD:     MB/sec in Acquisition phase: 0.002$LOAD:     Elapsed time from start to Acquisition phase:   4 second(s)$LOAD:     Elapsed time in Acquisition phase:   3 second(s)$LOAD:     Elapsed time in Application phase: < 1 second$LOAD:     Elapsed time from Application phase to end:   1 second(s)$LOAD: Total processor time used = '0.064004 Second(s)'$LOAD: Start : Mon Nov  2 17:44:57 2020$LOAD: End   : Mon Nov  2 17:45:05 2020Job step MAIN_STEP completed successfullyJob copy_MySrcTbl_MyTrgtTbl completed successfullyJob start: Mon Nov  2 17:44:57 2020Job end:   Mon Nov  2 17:45:05 2020

TPT Multiload for Populated Tables

/* second execution, target table contains data */

TDExpress1620_Sles11:~ # tdload –SourceTdpid 127.0.0.1 –SourceUserName dbc –SourceUserPassword dbc –SourceWorkingDatabase tuning –SourceTable country_code –TargetTdpid 127.0.0.1 –TargetUserName dbc –TargetUserPassword dbc –TargetWorkingDatabase tuning –TargetTable copytbl copy_MySrcTbl_MyTrgtTbl

Teradata Load Utility Version 16.20.00.02 64-BitTeradata Parallel Transporter Version 16.20.00.02 64-BitJob log: /opt/teradata/client/16.20/tbuild/logs/copy_MySrcTbl_MyTrgtTbl-4.outJob id is copy_MySrcTbl_MyTrgtTbl-4, running on TDExpress1620_Sles11Teradata Parallel Transporter Export Operator Version 16.20.00.02$EXPORT: private log specified: ExportLogTeradata Parallel Transporter Update Operator Version 16.20.00.02$UPDATE: private log specified: UpdateLog$EXPORT: connecting sessions$UPDATE: connecting sessions$UPDATE: preparing target table(s)$UPDATE: entering DML Phase$UPDATE: entering Acquisition Phase$EXPORT: sending SELECT request$EXPORT: entering End Export Phase$EXPORT: Total Rows Exported:  249$EXPORT: Total Rows Discarded: 0$UPDATE: entering Application Phase$UPDATE: Statistics for Target Table:  'copytbl'$UPDATE: Rows Inserted: 249$UPDATE: Rows Updated:  0$UPDATE: Rows Deleted:  0$UPDATE: entering Cleanup Phase$UPDATE: Error Table Statistics for Target Table :   'copytbl'$UPDATE: Total Rows in Error Table 1:   0$UPDATE: Total Rows in Error Table 2:   0$EXPORT: disconnecting sessions$UPDATE: disconnecting sessions$EXPORT: Performance metrics:$EXPORT:     MB/sec in Export phase: could not be determined$EXPORT:     Elapsed time from start to Select phase:   3 second(s)$EXPORT:     Elapsed time in Select phase: < 1 second$EXPORT:     Elapsed time in Export phase: < 1 second$EXPORT:     Elapsed time from Export phase to end:   6 second(s)$UPDATE: Performance metrics:$UPDATE:     MB/sec in Acquisition phase: 0.002$UPDATE:     Elapsed time from start to Acquisition phase:   3 second(s)$UPDATE:     Elapsed time in Acquisition phase:   5 second(s)$UPDATE:     Elapsed time in Application phase: < 1 second$UPDATE:     Elapsed time from Application phase to end:   1 second(s)$UPDATE: Total processor time used = '0.040002 Second(s)'$UPDATE: Start : Mon Nov  2 17:47:03 2020$UPDATE: End   : Mon Nov  2 17:47:12 2020$EXPORT: Total processor time used = '0.032002 Second(s)'$EXPORT: Start : Mon Nov  2 17:47:03 2020$EXPORT: End   : Mon Nov  2 17:47:12 2020Job step MAIN_STEP completed successfullyJob copy_MySrcTbl_MyTrgtTbl completed successfullyJob start: Mon Nov  2 17:47:03 2020Job end:   Mon Nov  2 17:47:12 2020

Suppose you are in a shared host environment, where many users might watch command execution. In that case, you can maintain all sensitive information in a job variables file and use the “tdload -v jobvars.txt” instead. If you want to save and see the script that tdload generates for tbuild, add the “-S” option to the tdload call. The generated scripts are kept in the job log directory for further inspection and customization. A tbuild command can readily use them.

Copying Tables across the same Teradata System

The tdload tool can be helpful in another scenario: table copy across the same system but different databases with different usernames and passwords. The typical approach is exporting from one database to a file and importing it to the second one. This file-based interaction can be avoided, as tdload can connect concurrently to the two databases with two different usernames and passwords (the SourceTdpId and TargetTdpip are now identical):

tdload --SourceTdpid 127.0.0.1 --SourceUserName ETLsrc --SourceUserPassword ETL_SRC_PASS --SourceWorkingDatabase MySrcDB --SourceTable MySrcTbl --TargetTdpid 127.0.0.1 --TargetUserName ETLtrgt --TargetUserPassword ETL_TRGT_PASS --TargetWorkingDatabase MyTrgtDB --TargetTable MyTrgtTbl a_job_name