DWHPro Insights

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

By Roland Wenzlofsky ·

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 -j jobvars.txt a_job_name

Define source and target connection, database and table variables in an owner-only jobvars.txt file. Invoke tdload -j jobvars.txt a_job_name; tdload --help documents the variables. Do not place passwords in command arguments, shell history or published logs. The -v option reports the tdload version; tbuild uses different options.

The tool checks the target table contents and transparently decides to perform a bulk load (i.e., apply a $LOAD operator) or a MultiLoad-protocol bulk update (i.e., apply an $UPDATE operator, not TPump). 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 -j jobvars.txt a_job_name
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 */

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

TDExpress1620_Sles11:~ # tdload -j jobvars.txt a_job_name

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 -j 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

For a same-system copy, INSERT INTO target SELECT … FROM source is often simpler when one account has the required grants. tdload can also connect with separate source and target accounts. Its copy appends rows: rerunning is not idempotent. The audit copied two rows with EXPORT/LOAD, then appended two more with EXPORT/UPDATE; GROUP BY source key and COUNT(*) showed each row twice. Compare source/target counts and duplicate keys before accepting a copy. Historical logs below are from 2020; cross-system networking was not retested.

tdload -j jobvars.txt a_job_name

Building or improving a data pipeline?

Discuss ingestion, transformations, orchestration, quality checks and operational handover.

Explore data engineering services

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “How to Simplify Database-to-Database Table Copying with Teradata Parallel Transporter (TPT) and tdload”

  1. Nice explanation. I tried the utility to copy data between 2 different teradata systems using source and target options, however am getting a strange error saying target table does not exist. I did give TargetWorkingDatabase as it is a different db than the default, but same error prevails. I am sure that the table exists. Please share ur insights into this.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam