Teradata Parallel Transporter - Example script

Teradata Parallel Transporter ( TPT )

What is TPT ?

TPT stands for Teradata parallel transporter. As the name implies “Parallel Transporting”.
This is the new generation Load/unload utility provided by teradata.
This act’s as a integrated ETL suite which helps to EXTRACT data from multiple source, apply theTRANSFORMATION logic and LOAD the data in target Teradata database.
TPT has all the features  to run the stand alone teradata load/unload utilities i.e (mload, tpump, fastexport, fastload) .
The TPT operator equivalent to standalone utilities are listed below
Mload=Update

Tpump=Stream

Fastload=Load

FastExport=Export

As per the info the legacy utilities i.e (mload, tpump, fastexport, fastload) . will still continue to work as before and support latest teradata version but any new features addition in load/unload utility will be done on TPT.
TPT is a utility tool of  teradata and has all the capabilities of ETL along the with features of other teradata load/unload utilities.
In simple terms TPT can be described as nutshell of all teradata standalone utilities  along with additional features.
Architecture of Teradata Parallel Transporter

Why use TPT ?

TPT is a flexible and high performance load/unload tool for the Teradata Database that supports ODBC source operators.
TPT efficiently transports vast amounts of data using core data warehousing fundamentals of parallelism and scalability.
Unlike conventional utilities and products in which multiple data sources are usually processed in a serial manner, Teradata Parallel Transporter can access multiple data sources in parallel. This ability can lead to increased throughput. Teradata Parallel Transporter also allows different specifications for different data sources and, if their data is UNION-compatible, merges them together.”
The current teradata protocol i.e Mload, Tpump, Fastload, Fastexport has all the features integrated in one single utility(TPT) : POWER OF ONE
Below text as per teradata website

“Simplified data transfer between one Teradata Database and another; only one script is required to export from the production-and-load test system.
Ability to load dozens of files using a single script makes development and maintenance of the data warehouse easier.
Distribution of workloads across CPUs on the load server eliminates bottlenecks in the data load process.
Data flows through multiple instances of UPDATE OPERATOR and in-memory data streams to update tables.
Option is available to export data to in-memory data stream instead of landing data.
The open database connectivity (ODBC) operator reads from the ODBC driver, which could pull data from any database; for example, DB2 or Oracle.
Accessibility to myriad data sources via open standards is possible.
Multiple operators can scan directories for files to load and can combine the data in the in-memory data stream with UNION ALL operation and stream operator loads.
Script-building wizard is available to aid first-time users”
Combine export and load functions into a single job
The TPT script can be created in step so that incase of failure of job the job process can be restarted from the step where it has failed.
We can scale a load job using parallel load streams from multiple input files.
Automatically load  files from multiple directories.
Combine an export and load into a single job.
Load data directly from any database with the plug-in Access Modules
Symbolic substitution for the script language to determine job behavior at runtime.
Creating generic scripts that minimize maintenance cost and maximizes reusability.
Since the four utilities have converged into Teradata Parallel Transporter, only one scripting language is used. This simplicity creates new opportunities for the user, such as:
Solving more complex load scenarios.
Switching between load protocols more easily.
Unlimited symbol substitution
Fewer required scripts.
Simultaneous Teradata-to-Teradata export and load scenario.
When to use TPT ?

TPT can be used to load/unload data in teradata database environment
Since this is a new generation utility which has an integrated feature of all standalone teradata load/unload utility, as per teradata all the new advancement and features will be done on TPT rather then old utilities therefore it is recommended to develop all the new application based on TPT rather than working on old standalone utilities.
Any organization/Individual interested in increased performance, improved ease of use, and a better interface for ETL then TPT is the best way to start with , It provides the optimum use of teradata feature like parallelism ,Scalability and capability to outperform in a datawarehouse environment.
When extracting large amounts of data from external non-Teradata databases.
ODBC operator is recommended when running TPT scripts and utilities without requirement for a third party ETL/ELT adapter.  TPT does support SQL-like transformations
Important Features of TPT

The most important feature of TPT is its Scalability and Parallelism behavior.

Scalability & Performance Features of Parallel Transporter

Below are some of the features of Parallel Transporter that can be used to increase load throughput:
Parallel input file processing.
Sometimes splitting the I/O processing up over multiple input files will help overcome a performance bottleneck.
Directory scan feature – A single Parallel Transporter script can have multiple processes read multiple files in multiple directories.
Multiple processes reading the same input file – in some cases having multiple read processes reading the same file can increase data throughput.
Multiple processes sending data to the Teradata Database – performance can be improved by splitting the CPU time across multiple processes that are manipulating the data and then loading buffers to be sent to the Teradata Database.
Multiple processes exporting the same table.
How to use TPT ?

Parallel Transporter can be invoked through 4 interfaces:
Application Program Interface (API) – used by leading ETL vendors for tight, parallel, high-performance integration
Script – used when a customer doesn’t have an ETL tool
Command line (sometimes referred to as the Easy Loader interface) – used to load data from a flat file with a single command line
Wizard – used to generate simple scripts. Use this tool as a way to learn the script language and not as a production load interface.
TPT Using Script

Creating a script.

The structure of TPT script is as mentioned below

Define the job name for the script
Provide the description of job
Schema Defination
Operator Defination #1
Operator Defination #2
….
….
Operator Defination #n
Apply definition
It also requires an job Variable file.
Sample template :

DEFINE JOB load_sample_country

(
DEFINE SCHEMA countries_schema @LoadTargetTable;
APPLY $INSERT @LoadTargetTable
TO OPERATOR ($LOAD)
SELECT * FROM OPERATOR ($EXPORT(countries_schema));
);

Here is the job variable file – jobvar.txt

LoadPrivateLogName = ‘loadoper_private_log’,
LoadTargetTable = ‘sample_country’,
LoadWorkingDatabase = ‘dbname’,
LoadErrorTable1 = ‘e1__sample_country’,
LoadErrorTable2 = ‘e2_sample_country’,
LoadLogTable = ‘log_sample_country’,
LoadMaxSessions = 5,
LoadMinSessions = 1,
ExportPrivateLogName = ‘exportoper_private_log’,
ExportMaxSessions = 5,
ExportMinSessions = 1,
ExportSelectStmt = ‘select * from dbname.countries;’,
TargetUserName = ‘username’,
TargetUserPassword = ‘password’,
TargetTdpId = ‘ipaddress’,
SourceUserName = ‘username’,
SourceUserPassword = ‘password’,
SourceTdpId = ‘ipaddress’

Command for a TPT script.

Running the Script : tbuild -f <scriptname> -v <jobvariablename>

Pausing the Script  :

Stoping the Script  :

Viewing the logs     :

How to execute Macro/SP inside TPT ?

We can execute macro/SP inside the DDL operator, Its used to execute DDL task but with enhancement capabilities of TPT scripting this feature of execution was extended in DDL operator itself.

The DDL Operator

The DDL Operator is a standalone operator that allows DDL and other SQL statements to perform activities such as CREATE, ALTER, DROP, GIVE, GRANT, REVOKE, INSERT, UPDATE, DELTE and INSERT/SELECT.
Statements not supported are SELECT, HELP and SHOW.
All SQL or DDL commands must be hard-coded into the SQL statements and are submitted to TPT as part of the APPLY clause.

SCRIPT OF EXECUTION

DEFINE JOB CREATE_TABLE
(
DEFINE OPERATOR DDL_OPERATOR
DESCRIPTION ‘TPT DDL OPERATOR’
TYPE DDL
ATTRIBUTES(
VARCHAR TDPID
VARCHAR USERNAME
VARCHAR PASSWORD
);

APPLY
(‘EXEC TDWORLD_MACRO;’)
TO OPERATOR (DDL_OPERATOR());
);



Sample TPT scripts.



GT11

/**************************************************************/
/*                                                            */
/* Script Name: GT11.txt                                      */
/* Description: This Teradata Parallel Transporter sample     */
/*              script exports 10 rows from a Teradata table  */
/*              and loads the 10 rows into a different, empty */
/*              Teradata table.                               */
/*                                                            */
/**************************************************************/
/* Explanation:                                               */
/*                                                            */
/* This script uses two job steps.                            */
/*                                                            */
/* The first job step called "setup" uses the DDL Operator to */
/* setup the source and target tables.                        */
/*                                                            */
/* The second job step called "load_data" uses the Export     */
/* Operator to export the rows from a Teradata source table   */
/* and uses the Load Operator to load the rows into an empty  */
/* Teradata target table.                                     */
/*                                                            */
/* This script shows how to use the following Teradata        */
/* Parallel Transporter features:                             */
/* * Unconditional job steps.                                 */
/* * Export rows from a Teradata table and load the rows into */
/*   a different, empty Teradata table without landing the    */
/*   rows to a data file.                                     */
/*                                                            */
/**************************************************************/
/* This script also demonstrates using "job variables",       */
/* which are defined in the external file "jobvars.txt".      */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name.           */
/*                                                            */
/**************************************************************/
/* Required customizations before running this script:        */
/*                                                            */
/* Inside the file "jobvars.txt", modify the values for       */
/* the following job variables:                               */
/*                                                            */
/*    MyTdpId, MyUserName, MyPassword                         */
/*                                                            */
/* MyTdpId is the name of the Teradata Database system.       */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username.   */
/*                                                            */
/**************************************************************/
/* Execution:                                                 */
/*                                                            */
/* Here is the command to execute this script:                */
/*                                                            */
/*    tbuild -f GT11.txt -v jobvars.txt                       */
/*                                                            */
/**************************************************************/

DEFINE JOB BACKUP_EMPLOYEE_TABLE
DESCRIPTION 'BACKUP SAMPLE EMPLOYEE TABLE'
(
   DEFINE SCHEMA EMPLOYEE_SCHEMA
   DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
   (
      EMP_ID   INTEGER,
      EMP_NAME CHAR(10)
   );

   DEFINE OPERATOR DDL_OPERATOR()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
   TYPE DDL
   ATTRIBUTES
   (
      VARCHAR PrivateLogName = 'GT11_ddloper_log',
      VARCHAR TdpId          = @MyTdpId,
      VARCHAR UserName       = @MyUserName,
      VARCHAR UserPassword   = @MyPassword,
      VARCHAR AccountID,
      VARCHAR ErrorList      = '3807'
   );

   DEFINE OPERATOR LOAD_OPERATOR()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
   TYPE LOAD
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName    = 'GT11_loadoper_privatelog',
      INTEGER MaxSessions       =  32,
      INTEGER MinSessions       =  1,
      VARCHAR TargetTable       = 'TARGET_EMP_TABLE',
      VARCHAR TdpId             = @MyTdpId,
      VARCHAR UserName          = @MyUserName,
      VARCHAR UserPassword      = @MyPassword,
      VARCHAR AccountId,
      VARCHAR ErrorTable1       = 'GT11_LOADOPER_ERRTABLE1',
      VARCHAR ErrorTable2       = 'GT11_LOADOPER_ERRTABLE2',
      VARCHAR LogTable          = 'GT11_LOADOPER_LOGTABLE'
   );

   DEFINE OPERATOR EXPORT_OPERATOR()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
   TYPE EXPORT
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName    = 'GT11_exportoper_privatelog',
      INTEGER MaxSessions       =  32,
      INTEGER MinSessions       =  1,
      VARCHAR TdpId             = @MyTdpId,
      VARCHAR UserName          = @MyUserName,
      VARCHAR UserPassword      = @MyPassword,
      VARCHAR AccountId,
      VARCHAR SelectStmt        = 'SELECT * FROM SOURCE_EMP_TABLE;'
   );

   STEP setup
   (
      APPLY
      ('DROP TABLE SOURCE_EMP_TABLE;'),
      ('DROP TABLE TARGET_EMP_TABLE;'),
      ('DROP TABLE GT11_LOADOPER_ERRTABLE1;'),
      ('DROP TABLE GT11_LOADOPER_ERRTABLE2;'),
      ('DROP TABLE GT11_LOADOPER_LOGTABLE;'),
      ('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
      ('CREATE TABLE TARGET_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
      ('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
      TO OPERATOR (DDL_OPERATOR () );
   );

   STEP load_data
   (
      APPLY
      ('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')
      TO OPERATOR (LOAD_OPERATOR () [1] )

      SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
   );
);


GT44

/**************************************************************/
/*                                                            */
/* Script Name: GT44.txt                                      */
/* Description: This Teradata Parallel Transporter sample     */
/*              script exports 10 rows from a Teradata table  */
/*              to a file.                                    */
/*                                                            */
/**************************************************************/
/* Explanation:                                               */
/*                                                            */
/* This script uses two job steps.                            */
/*                                                            */
/* The first job step called "setup" uses the DDL Operator to */
/* setup the source table.                                    */
/*                                                            */
/* The second job step called "export_to_file" uses the       */
/* Export Operator to export the rows from a Teradata table   */
/* and uses the Data Connector Operator to write the rows to  */
/* a file.                                                    */
/*                                                            */
/* This script shows how to use the following Teradata        */
/* Parallel Transporter features:                             */
/* * Unconditional job steps.                                 */
/* * Export rows from a Teradata table and write the rows to  */
/*   a file.                                                  */
/*                                                            */
/**************************************************************/
/* This script also demonstrates using "job variables",       */
/* which are defined in the external file "jobvars.txt".      */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name.           */
/*                                                            */
/**************************************************************/
/* Required customizations before running this script:        */
/*                                                            */
/* Inside the file "jobvars.txt", modify the values for       */
/* the following job variables:                               */
/*                                                            */
/*    MyTdpId, MyUserName, MyPassword                         */
/*                                                            */
/* MyTdpId is the name of the Teradata Database system.       */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username.   */
/*                                                            */
/**************************************************************/
/* Execution:                                                 */
/*                                                            */
/* Here is the command to execute this script:                */
/*                                                            */
/*    tbuild -f GT44.txt -v jobvars.txt                       */
/*                                                            */
/**************************************************************/

DEFINE JOB EXPORT_EMPLOYEE_TABLE_TO_FILE
DESCRIPTION 'EXPORT SAMPLE EMPLOYEE TABLE TO A FILE'
(
   DEFINE SCHEMA EMPLOYEE_SCHEMA
   DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
   (
      EMP_ID   INTEGER,
      EMP_NAME CHAR(10)
   );

   DEFINE OPERATOR DDL_OPERATOR()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
   TYPE DDL
   ATTRIBUTES
   (
      VARCHAR PrivateLogName = 'GT44_ddloper_log',
      VARCHAR TdpId          = @MyTdpId,
      VARCHAR UserName       = @MyUserName,
      VARCHAR UserPassword   = @MyPassword,
      VARCHAR AccountID,
      VARCHAR ErrorList      = '3807'
   );

   DEFINE OPERATOR FILE_WRITER()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
   TYPE DATACONNECTOR CONSUMER
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName    = 'GT44_dataconnoper_privatelog',
      VARCHAR FileName          = 'GT44_employee.dat',
      VARCHAR IndicatorMode     = 'N',
      VARCHAR OpenMode          = 'Write',
      VARCHAR Format            = 'Unformatted'
   );

   DEFINE OPERATOR EXPORT_OPERATOR()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
   TYPE EXPORT
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName    = 'GT44_exportoper_privatelog',
      INTEGER MaxSessions       =  32,
      INTEGER MinSessions       =  1,
      VARCHAR TdpId             = @MyTdpId,
      VARCHAR UserName          = @MyUserName,
      VARCHAR UserPassword      = @MyPassword,
      VARCHAR AccountId,
      VARCHAR SelectStmt        = 'SELECT * FROM SOURCE_EMP_TABLE;'
   );

   STEP setup
   (
      APPLY
      ('DROP TABLE SOURCE_EMP_TABLE;'),
      ('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
      ('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
      TO OPERATOR (DDL_OPERATOR () );
   );

   STEP export_to_file
   (
      APPLY TO OPERATOR (FILE_WRITER() )
      SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
   );
);
GT62

/**************************************************************/
/*                                                            */
/* Script Name: GT62.txt                                      */
/* Description: This Teradata Parallel Transporter sample     */
/*              script loads 10 records from a file into an   */
/*              empty Teradata table.                         */
/*                                                            */
/**************************************************************/
/* Explanation:                                               */
/*                                                            */
/* This script uses three job steps.                          */
/*                                                            */
/* The first job step called "setup_tables" uses the DDL      */
/* Operator to setup the source and target tables.            */
/*                                                            */
/* The second job step called "export_to_file" uses the       */
/* Export Operator to export the rows from a Teradata table   */
/* and uses the Data Connector Operator to write the rows to  */
/* a file.                                                    */
/*                                                            */
/* The third job step called "load_data_from_file" uses the   */
/* Data Connector Operator to read records from a file and    */
/* uses the Load Operator to load the records into an empty   */
/* Teradata table.                                            */
/*                                                            */
/* This script shows how to use the following Teradata        */
/* Parallel Transporter features:                             */
/* * Unconditional job steps.                                 */
/* * Export rows from a Teradata table and write the rows to  */
/*   a file.                                                  */
/* * Read records from a file and load the records into an    */
/*   empty Teradata table.                                    */
/*                                                            */
/**************************************************************/
/* This script also demonstrates using "job variables",       */
/* which are defined in the external file "jobvars.txt".      */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name.           */
/*                                                            */
/**************************************************************/
/* Required customizations before running this script:        */
/*                                                            */
/* Inside the file "jobvars.txt", modify the values for       */
/* the following job variables:                               */
/*                                                            */
/*    MyTdpId, MyUserName, MyPassword                         */
/*                                                            */
/* MyTdpId is the name of the Teradata Database system.       */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username.   */
/*                                                            */
/**************************************************************/
/* Execution:                                                 */
/*                                                            */
/* Here is the command to execute this script:                */
/*                                                            */
/*    tbuild -f GT62.txt -v jobvars.txt                       */
/*                                                            */
/**************************************************************/

DEFINE JOB LOAD_EMPLOYEE_TABLE_FROM_FILE
DESCRIPTION 'LOAD SAMPLE EMPLOYEE TABLE FROM A FILE'
(
   DEFINE SCHEMA EMPLOYEE_SCHEMA
   DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
   (
      EMP_ID   INTEGER,
      EMP_NAME CHAR(10)
   );

   DEFINE OPERATOR DDL_OPERATOR()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
   TYPE DDL
   ATTRIBUTES
   (
      VARCHAR PrivateLogName = 'GT62_ddloper_log',
      VARCHAR TdpId          = @MyTdpId,
      VARCHAR UserName       = @MyUserName,
      VARCHAR UserPassword   = @MyPassword,
      VARCHAR AccountID,
      VARCHAR ErrorList      = '3807'
   );

   DEFINE OPERATOR FILE_WRITER()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
   TYPE DATACONNECTOR CONSUMER
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName    = 'GT62_dataconnoper_writer_privatelog',
      VARCHAR FileName          = 'GT62_employee.dat',
      VARCHAR IndicatorMode     = 'N',
      VARCHAR OpenMode          = 'Write',
      VARCHAR Format            = 'Formatted'
   );

   DEFINE OPERATOR EXPORT_OPERATOR()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
   TYPE EXPORT
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName    = 'GT62_exportoper_privatelog',
      INTEGER MaxSessions       =  32,
      INTEGER MinSessions       =  1,
      VARCHAR TdpId             = @MyTdpId,
      VARCHAR UserName          = @MyUserName,
      VARCHAR UserPassword      = @MyPassword,
      VARCHAR AccountId,
      VARCHAR SelectStmt        = 'SELECT * FROM SOURCE_EMP_TABLE;'
   );

   DEFINE OPERATOR LOAD_OPERATOR()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
   TYPE LOAD
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName    = 'GT62_loadoper_privatelog',
      INTEGER MaxSessions       =  32,
      INTEGER MinSessions       =  1,
      VARCHAR TargetTable       = 'TARGET_EMP_TABLE',
      VARCHAR TdpId             = @MyTdpId,
      VARCHAR UserName          = @MyUserName,
      VARCHAR UserPassword      = @MyPassword,
      VARCHAR AccountId,
      VARCHAR ErrorTable1       = 'GT62_LOADOPER_ERRTABLE1',
      VARCHAR ErrorTable2       = 'GT62_LOADOPER_ERRTABLE2',
      VARCHAR LogTable          = 'GT62_LOADOPER_LOGTABLE'
   );

   DEFINE OPERATOR FILE_READER()
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
   TYPE DATACONNECTOR PRODUCER
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName    = 'GT62_dataconnoper_reader_privatelog',
      VARCHAR FileName          = 'GT62_employee.dat',
      VARCHAR IndicatorMode     = 'N',
      VARCHAR OpenMode          = 'Read',
      VARCHAR Format            = 'Formatted'
   );

   STEP setup_tables
   (
      APPLY
      ('DROP TABLE SOURCE_EMP_TABLE;'),
      ('DROP TABLE TARGET_EMP_TABLE;'),
      ('DROP TABLE GT62_LOADOPER_ERRTABLE1;'),
      ('DROP TABLE GT62_LOADOPER_ERRTABLE2;'),
      ('DROP TABLE GT62_LOADOPER_LOGTABLE;'),
      ('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
      ('CREATE TABLE TARGET_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
      ('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
      ('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
      TO OPERATOR (DDL_OPERATOR () );
   );

   STEP setup_export_to_file
   (
      APPLY TO OPERATOR (FILE_WRITER() [1] )
      SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
   );

   STEP load_data_from_file
   (
      APPLY
      ('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')
      TO OPERATOR (LOAD_OPERATOR () [1] )

      SELECT * FROM OPERATOR (FILE_READER() [1] );
   );
);
Sample job variable file for Sample Script 1

SourceTdpId          = ‘MySourceTdpid’
,SourceUserName       = ‘MySourceUserName’

,SourceUserPassword   = ‘MySourceUserPassword’

,TargetTdpId          = ‘MyTargetTdpId’

,TargetUserName       = ‘MyTargetUserName’

,TargetUserPassword   = ‘MyTargetUserPassword’

Comparision of TPT with other Teradata utilities

1) TPT v/s Mload (Update)

2) TPT v/s TPUMP (Stream)

3) TPT v/s Fastload (Load)

4) TPT v/s FastExport (Export)

Create Profile in Teradata - Syntax and example script - assign predefined system parameters to new users

A profile is a set of system parameters that can be assigned to a group of users.

The following request creates a profile called finance_report that defines the default database to be personnel:

    CREATE PROFILE finance_report AS

      DEFAULT DATABASE = financial;

The following request assigns the finance_report profile to a new user:

---- create and empty role

create role report_user;

create user training_2

from "financial"

as

password = "training_2"

perm = 40000

default role = "report_user"

profile = finance_report

;

Next we can validate that any query from this user looks in the 'FINANCIAL' database by default.

Profile assigned defaults every search to the default database

Find the query run by each user in Teradata - username to userid mapping in dbqlogtbl

First step is to enable query logging on Teradata for the User.

We need to enable query logging from DBC user. If you try to enable query logging using any other user, you will get an error that the user does not have 'Statement' permission.

Error code:

Failure 3523 (username does not have statement permission)

Example script:

---- enable query logging so that all the queries run by a particular user is logged
begin query logging with sql on training_1 ;

The below query will now give all the queries run by user 'Training_1'


select * from dbc.users;
select * from dbc.dbase;
select db1.databasename,dblog1.*from dbc.dbqlogtbl as dblog1
inner join
dbc.dbase as db1
on dblog1.userid = db1.databaseid
where db1.databasename = 'training_1';

We can end query logging using:

End query logging on user = 'training_1';

Please give us your valuable feedback and like us on Google Plus or LinkedIn.

All about SET and MULTISET tables - Default Table type in Teradata - An example using Teradata Studio Express to explain all about SET and MULTISET tables

We generally say that tables are created as SET tables if the type is not mentioned in the CREATE TABLE statement.

The fact is, the type of the table is entirely dependent on the Transaction Mode.
In ANSI, the tables are created as MULTISET and in BTET (Teradata) mode, the tables are created as SET.

Example:

First select the transaction mode for the current session.

SELECT sessionno, transaction_mode FROM dbc.SessionInfoV
WHERE SessionNo in (select session);

Finding the Transaction mode for current Teradata session

create table financial.userset2

 (

 userid integer

 ) primary index(userid);

The above query gives a SET table in Teradata mode.

Note: If we don’t specify the primary index, table is created as multiset even in BTET mode.
If we try to create a SET table explicitly as a NoPI, we get an error in Teradata - "Cannot create a NoPI table as a SET table"

SET table with No Primary Index Error in Teradata

create table financial.userset3

 (

 userid integer

 ) no primary index;

 --- results in a multiset table if the mode is BTET as the PI is not defined

 show table financial.userset2;

 ---Insert row into set table

 insert into financial.userset2 values(1);

 ---- insert duplicate rows in multiset table

 insert into financial.userset3 values(2);

 insert into financial.userset3 values(2);

 insert into financial.userset3 values(2);

 ---- insert from multiset table to set table

 ---- error raised in ANSI mode but suppressed in Teradata mode

insert into financial.userset2 select * from financial.userset3;

---- only 2 rows, userid 1 and 2 should be present.

---- Note that no errors or warnings are raised

select * from financial.userset2;

Rows inserted into SET table without warnings in BTET mode

If we set the session to ANSI mode and then run the below query:

create table financial.users(

uid integer

,uname varchar(20)

)no primary index;

The table is created as multiset table.

show table financial.users;

---By default tables get created as multiset tables in ANSI mode

/*

CREATE MULTISET TABLE financial.users ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      uid INTEGER,

      uname VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC)

NO PRIMARY INDEX ;

*/