Using Teradata PT To copy from other databases

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.
mvu
Teradata Employee

Using Teradata PT To copy from other databases

This article will show you how to use Teradata PT to copy data from one or more non-Teradata table(s) (e.g. Oracle table) to a Teradata table without using any intermediate disk storage. Teradata PT uses an ODBC operator as a producer to extract data from an Oracle table (as an example) and a Load operator as a consumer to load data into a Teradata table. You can modify the script to use other consumer operators such as Update, Stream or Inserter operator.

The best way to learn is by example. Let’s look at the following simple script, which is to copy data from an Oracle employee table to a Teradata table.

DEFINE JOB your_job_name
DESCRIPTION 'your comments'
(
DEFINE SCHEMA Oracle_DataSource_Schema
DESCRIPTION 'Schema of Oracle Data Source'
(
EmpNo DECIMAL (4,0),
EmpName VARCHAR (10),
JobTitle VARCHAR (9),
Mgr DECIMAL (4,0),
HireDate VARCHAR (10),
Salary DECIMAL (7,2),
Comm DECIMAL (7,2),
DeptNo DECIMAL (2,0)
);

DEFINE OPERATOR ODBC_Operator
DESCRIPTION 'TPT ODBC Operator'
TYPE ODBC
SCHEMA Oracle_DataSource_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = @ODBCPrivateLogName,
VARCHAR DSNName = @DSNName,
VARCHAR UserName = @ODBCUserName,
VARCHAR UserPassword = @ODBCPassword,
VARCHAR SelectStmt = @SelectStmt
);

APPLY
( 'INSERT INTO ' || @TargetTable || ' (
:EmpNo, :EmpName, :JobTitle, :Mgr, :HireDate, :Salary, :Comm, :DeptNo);'
)
TO OPERATOR ( $LOAD [@LoadInstances] )

SELECT * FROM OPERATOR ( ODBC_Operator [@ODBCInstances] );
);
);

The script has the following statements:

  • DEFINE JOB defines a Teradata PT job with a single APPLY statement not enclosed in the job STEP syntax.
  • DEFINE OPERATOR defines Teradata PT operators that will be used in a Teradata PT job.  In the example, the script uses the ODBC operator and Load operator.
  • DEFINE SCHEMA describes the structure of the data source or data target with an ordered set of column definitions.  In the example, it’s a table schema of an Oracle employee table.
  • APPLY statement defines the operations to be executed by a job.  In the example is to load data into an empty Teradata table.

If you wish to know more details on these statements, please refer to the Teradata Parallel Transporter Reference.  Only necessary information will be mentioned here to keep the article simple.

As you notice that there is only one DEFINE OPERATOR statement for the ODBC operator in the script example.  Where is the other DEFINE OPERATOR statement for the Load operator, you may ask.  Well, the definition of the Load operator is automatically imported into the job script by using a template name $LOAD in the APPLY statement.  The key word $LOAD is the template name of the standard Teradata PT-supplied Load operator.  The DEFINE OPERATOR statement for the Load operator is stored in the template file $LOAD.txt under the Teradata PT template directory ($TWB_ROOT/template).  This is an improvement in the Teradata PT 13.10 or later versions to simplify the Teradata PT script syntax.

You can find more information on the operator templates by referencing Chapter 13 in the Teradata Parallel Transporter User Guide.

Now, it’s time to examine the DEFINE SCHEMA statement in the script.  Assuming that an Oracle employee table has 8 columns defined as follows:

EmpNo             NUMBER (4,0),
EName VARCHAR (10),
Job VARCHAR (9),
Mgr NUMBER (4.0),
HireDate DATE,
Sal NUMBER (7,2),
Comm NUMBER (7,2),
DeptNo NUMBER (2,0)

Since Teradata Database 13.10 and older versions do not support NUMBER data type, the Oracle NUMBER data type will be mapped to the equivalent Teradata DECIMAL data type as shown in the DEFINE SCHEMA statement.  Another thing you need to pay attention to is the Oracle DATE data type, which is very different from Teradata DATE data type.  You need to do some data conversion on Oracle DATE column to fit the Teradata DATE column. In the script example, the function TO_CHAR is used in the SELECT statement to convert the Oracle DATE values to Teradata date format.

Next is to mention the use of job variables in the script.  They are composed of the symbol @, followed by a unique identifier.  The value of these job variables can be specified in a file following the format:

job-variable-name = value [,]

The following job variables must be defined in the job variable file:

/**********************************/
/* Values for ODBC operator */
/**********************************/
ODBCInstances = 1,
ODBCPrivateLogName = 'odbclog',
DSNName = 'Oracle_DSNName',
ODBCUserName = 'scott',
ODBCPassword = 'tiger',
SelectStmt = 'select EmpNo, EName, Job, Mgr,
TO_CHAR (HireDate, ''YYYY/MM/DD'') AS HireDate,
Sal, Comm, DeptNo from emp;'
/**********************************/
/* Values for LOAD operator */
/*********************************/
LoadInstances = 1,
LoadPrivateLogName = 'loadlog',
TargetTable = 'Teradata_target_table_name',
TargetTdpId = 'Teradata_TdpId',
TargetUserName = 'Teradata_User_Name',
TargetUserPassword = 'Teradata_User_Password',
LogTable = 'Teradata_Log_Table_Name'

If the Teradata target table resides in a different database than the default database of the specified Teradata user, you must add the job variable TargetWorkingDatabase to the file, as follows:

TargetWorkingDatabase   = ‘targetWorkingDatabase’

Finally, at the command line, you can run the script as follows:

       tbuild -f script_file -v job_variable_file

Important notes:

  1. The Teradata target table must be defined before running the Teradata PT script.  For this exercise, the target table can be defined as follows:
    CREATE TABLE orcl_emp_tbl (
    EmpNo integer,
    EmpName varchar(10),
    JobTitle varchar(9),
    Mgr integer,
    HireDate date,
    Salary decimal(7,2),
    Comm decimal(7,2),
    DeptNo smallint
    );

  2. If you use TO_CHAR in the SELECT statement, the TPT script schema must have the VARCHAR data type for the column, as shown in the example.
  3. The date conversion format used in TO_CHAR must match with that of a Teradata DATE column. Otherwise, the load job will fail and all rows will be thrown in an error table (error 2665) due to invalid date values.
21 REPLIES

Re: Using Teradata PT To copy from other databases

Useful Article...Thanks.

Re: Using Teradata PT To copy from other databases

Hi,
Can you please explain where to write this query?
Also can we do this using UNIX scripts?
Thanks in Advance..
Regards,
Arun
mvu
Teradata Employee

Re: Using Teradata PT To copy from other databases

Arun,
I assume that you're talking about Select query for the ODBC operator.
You can specify the query via the job variable 'SelectStmt' in the job variable file as mentioned in the article. You can use UNIX script to create the job variable file and
invoke the tbuild command with the job variable file.
Enthusiast

Re: Using Teradata PT To copy from other databases

Hi,

Thanks for the useful article. I am trying to load the data from Oracle to Teradata using TPT but getting this below ODBC error. Can you please help me to resolve this issue?

Teradata Parallel Transporter Version 14.00.00.08

Job log: /apps/teradata/client/14.00/tbuild/logs/<job id>.out

Job id is <job id>, running on <servername>

Found CheckPoint file: /apps/teradata/client/14.00/tbuild/checkpoint

This is a restart job; it restarts at step MAIN_STEP.

TPT_INFRA: TPT02192: Error: Cannot load shared library

           dlopen error: ld.so.1: executor: fatal: /apps/teradata/client/14.00/odbc_64/lib/libodbc.so: wrong ELF class: ELFCLASS64

TPT_INFRA: TPT02109: Cannot open shared library libodbcop.so, status = Open Error

TPT_INFRA: TPT02240: Error: Cannot create operator object, status = Operator Error

TPT_INFRA: TPT02103: Executor unable to set up its table object

Job step MAIN_STEP terminated (status 8)

mvu
Teradata Employee

Re: Using Teradata PT To copy from other databases

The TPT version 14.0 is 32-bit application and the TPT job picks up the 64-bit version of the library "libodbc.so" during the run.  You need to include the proper path to your 32-bit "libodbc.so" in the LD_LIBRARY_PATH or LIBPATH depending on your UNIX machine.  If you haven't done it, you also  need to set up the environment variable ODBCINI to point to the correct "odbc.ini" before running a TPT job.

Enthusiast

Re: Using Teradata PT To copy from other databases

Thanks for your quick response. Now I am getting this below error:

Teradata Parallel Transporter Version 14.00.00.08

Job log: /apps/teradata/client/14.00/tbuild/logs/infbiftp-107.out

Job id is <user_name>-107, running on <server_name>

Found CheckPoint file: /apps/teradata/client/14.00/tbuild/checkpoint/<user_name>LVCP

This is a restart job; it restarts at step MAIN_STEP.

Teradata Parallel Transporter Load Operator Version 14.00.00.08

LOAD_OPERATOR: private log specified: loadoper_privatelog

LOAD_OPERATOR: connecting sessions

TPT_INFRA: TPT01036: Error: Task (TaskID: 5, Task Name: SELECT_2[0001]) terminated due to the receipt of signal number 11

TPT_INFRA: TPT01037: Error: Task (TaskID: 5, Task Name: SELECT_2[0001]) core dumped

LOAD_OPERATOR: preparing target table

LOAD_OPERATOR: entering Acquisition Phase

LOAD_OPERATOR: disconnecting sessions

LOAD_OPERATOR: Total processor time used = '19.25 Second(s)'

LOAD_OPERATOR: Start : Fri May  2 13:07:24 2014

LOAD_OPERATOR: End   : Fri May  2 13:07:48 2014

Job step MAIN_STEP terminated (status 8)

Job <user_name> terminated (status 8)

mvu
Teradata Employee

Re: Using Teradata PT To copy from other databases

Do you happen to have the trace from the core file?   I also need the binary log file '/apps/teradata/client/14.00/tbuild/logs/infbiftp-107.out' to see what happens during the run.

We have seen this coredump before but not sure if it's the same one that we fixed in the latest version of 14.00.

Enthusiast

Re: Using Teradata PT To copy from other databases

Yes trace file is generated. But its not in readable format. Please let me know the details you required in this file I can share it with you.

mvu
Teradata Employee

Re: Using Teradata PT To copy from other databases

To extract the binary log in the readable format, submit the following command at the command line prompt:

    tlogivew -l infbiftp-107.out -f "*" -g -o <outputfilename>

The command will extracts all logs; but I only interests in the section belongs to the ODBC operator.  You can find it in the "outputfilename" starting with the banner "ODBC OPerator  Version ....".

However,based on the poseted console log,  I realized that the ODBC operator was not even started yet.   Maybe the follwoing command might get some useful information:

     tlogview -l infbiftp-107.out -w 'TASKNAME="SELECT_2[0001]"' > output.txt

And, give the last 50 lines of he output file.

My guess is that you might not set the environment correctly for ODBC driver to be loaded properly.   Do you have the system coredump file?  The information in the coredump file will verify  that.  We can get segmentation fault while the ODBC driver is  loaded and its dependent libraries are not in sync.  We, as TPT, has no control over that.

Is the environment ODBCINI set up correctly?