Teradata Parallel Transporter (Teradata PT) supports moving data from a table on a Teradata Database system, or from an ODBC-compliant RDBMS, to a Teradata table on a different Teradata Database system without landing the data to disk.

There are performance benefits, cost savings, and ease of script maintenance associated with using Teradata PT to move data without landing it to disk.

Performance benefits

Unlike the traditional Teradata utilities, Teradata PT can start data exporting and data loading at the same time without having to wait for data exporting to complete. A Teradata PT producer operator, such as the Export operator, can export data from a table and send it to a Teradata PT data stream. A Teradata PT consumer operator, such as the Load operator, can consume the data from the data stream and load it into a Teradata Database table. Data streams consist of in-memory buffers where data is passed from a producer to a consumer operator.

A user of the traditional Teradata utilities can run the Teradata FastExport utility to export data to a disk file and then the Teradata FastLoad utility to load data from the disk file to an empty Teradata Database table. If a user FastExports data into a named pipe, the user can then use FastLoad to read the data from the named pipe.

We should add that when two scripts are needed to move data, there is always the possibility of inadvertently running FastLoad, for example, before FastExport.

Cost savings

When a very large amount of data (greater than 50 gigabytes) is moved through a Teradata PT data stream without landing the data to disk, a Teradata PT user saves on the cost of purchasing additional disk drives. A user can use the disk space that was used for some other purpose.

Single-script maintenance

Unlike the traditional Teradata utilities, a single Teradata PT job script can be coded to move data without landing it to disk. If a user needs to make a change to the Teradata PT job, the user only needs to change the Teradata PT job script.

Traditional Teradata utilities require two scripts: one FastExport script to export the data and one script to load the data.

Sample scripts

Here are two sample Teradata PT job scripts.

In each script, the Export operator extracts the data from the SOURCE_EMP_TABLE table and the Load operator loads the data into an empty table called TARGET_EMP_TABLE.

Both scripts use job variables. But the second script uses more job variables than the first, in addition to concatenating quoted scripts and job variables.

Sample Script 1

DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK

DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'

(

   /*** Schema Definition ***/

 

   DEFINE SCHEMA EMPLOYEE_SCHEMA

   DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'

   (

      EMP_ID   INTEGER,

      EMP_NAME CHAR(10)

   );

 

   /*** Export Operator Definition ***/

 

   DEFINE OPERATOR EXPORT_OPERATOR

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

   TYPE EXPORT

   SCHEMA EMPLOYEE_SCHEMA

   ATTRIBUTES

   (

      VARCHAR PrivateLogName = 'exportoper_privatelog',

      INTEGER MaxSessions    =  8,

      INTEGER MinSessions,

      VARCHAR TdpId          = @SourceTdpId,

      VARCHAR UserName       = @SourceUserName,

      VARCHAR UserPassword   = @SourceUserPassword,

      VARCHAR SelectStmt     = 'SELECT * FROM SOURCE_EMP_TABLE;'

   );

 

   /*** Load Operator Definition ***/

 

   DEFINE OPERATOR LOAD_OPERATOR

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'

   TYPE LOAD

   SCHEMA EMPLOYEE_SCHEMA

   ATTRIBUTES

   (

      VARCHAR PrivateLogName = 'loadoper_privatelog',

      INTEGER MaxSessions    = 16,

      INTEGER MinSessions,

      VARCHAR TargetTable    = 'TARGET_EMP_TABLE',

      VARCHAR TdpId          = @TargetTdpId,

      VARCHAR UserName       = @TargetUserName,

      VARCHAR UserPassword   = @TargetUserPassword,

      VARCHAR ErrorTable1    = 'LOADOPER_ERRTABLE1',

      VARCHAR ErrorTable2    = 'LOADOPER_ERRTABLE2',

      VARCHAR LogTable       = 'LOADOPER_LOGTABLE'

   );

 

   /*** Apply Statement ***/

 

   APPLY

   ('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')

   TO OPERATOR (LOAD_OPERATOR [1])

 

   SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);

);

Sample job variable file for Sample Script 1

 SourceTdpId          = 'MySourceTdpid'

,SourceUserName       = 'MySourceUserName'

,SourceUserPassword   = 'MySourceUserPassword'

,TargetTdpId          = 'MyTargetTdpId'

,TargetUserName       = 'MyTargetUserName'

,TargetUserPassword   = 'MyTargetUserPassword'

Sample Script 2

DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK

DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'

(

   /*** Schema Definition ***/

 

   DEFINE SCHEMA EMPLOYEE_SCHEMA

   DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'

   (

      EMP_ID   INTEGER,

      EMP_NAME CHAR(10)

   );

 

   /*** Export Operator Definition ***/

 

   DEFINE OPERATOR EXPORT_OPERATOR

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

   TYPE EXPORT

   SCHEMA EMPLOYEE_SCHEMA

   ATTRIBUTES

   (

     VARCHAR PrivateLogName  = 'exportoper_privatelog',

     INTEGER MaxSessions     =  8,

     INTEGER MinSessions,

     VARCHAR TdpId           = @SourceTdpId,

     VARCHAR UserName        = @SourceUserName,

     VARCHAR UserPassword    = @SourceUserPassword,

     VARCHAR SelectStmt = 'SELECT * FROM ' || @SourceTable || ';'

   );

 

   /*** Load Operator Definition ***/

 

   DEFINE OPERATOR LOAD_OPERATOR

   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'

   TYPE LOAD

   SCHEMA EMPLOYEE_SCHEMA

   ATTRIBUTES

   (

      VARCHAR PrivateLogName    = 'loadoper_privatelog',

      INTEGER MaxSessions       = 16,

      INTEGER MinSessions,

      VARCHAR TargetTable       = @TargetTable,

      VARCHAR TdpId             = @TargetTdpId,

      VARCHAR UserName          = @TargetUserName,

      VARCHAR UserPassword      = @TargetUserPassword,

      VARCHAR ErrorTable1       = @ErrorTable1,

      VARCHAR ErrorTable2       = @ErrorTable2,

      VARCHAR LogTable          = @LogTable

   );

 

   /*** Apply Statement ***/

 

   APPLY

   ('INSERT INTO ' || @TargetTable || ' (:EMP_ID, :EMP_NAME);')

   TO OPERATOR (LOAD_OPERATOR [1])

 

   SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);

);

Sample job variable file for Sample Script 2

 SourceTdpId          = 'MySourceTdpid'

,SourceUserName       = 'MySourceUserName'

,SourceUserPassword   = 'MySourceUserPassword'

,TargetTdpId          = 'MyTargetTdpId'

,TargetUserName       = 'MyTargetUserName'

,TargetUserPassword   = 'MyTargetUserPassword'

,SourceTable          = 'SOURCE_EMP_TABLE'

,TargetTable          = 'TARGET_EMP_TABLE'

,ErrorTable1          = 'LOADOPER_ERRTABLE1'

,ErrorTable2          = 'LOADOPER_ERRTABLE2'

,LogTable             = 'LOADOPER_LOGTABLE'

For information on the concatenation of quoted strings and job variables, see Teradata Parallel Transporter User Guide.

Discussion
Rajesh.Prabhu 1 comment Joined 08/06
26 Oct 2010

As per this article we can export+load more than 50Gig data using in-memory buffers,
-Is there any limitations of IN-Memory Buffer size or we can process any size/number of data?
-If we process 50Gig data do we need to do any special settings or allot a space for IN-Memory buffer size?

TonyL 51 comments Joined 12/09
29 Oct 2010

Teradata Parallel Transporter can process any size/number of data.

No special settings are needed. No space allotment is needed.

The TPT producer operator puts the data on the TPT data stream and the TPT consumer operator gets the data off the TPT data stream.

zaino22 1 comment Joined 10/10
06 Nov 2010

we have mainframe channel attached environment, and we use JCL to connect to TD. We have two seperate Teradata systems: System A, and System B. if we wish to update System B table with System A table we usually run JCL with System A login info, and Fast Export scrip to extract data, and then run JCL with System B login info and run Multi-Load to laod into System B. I figured I cannot use named pipe or TPT via script embedded into JCL since login info have to be provided for both systems and in our environment we usually have one system login info in each JCL (export or Load). let me know if i am wrong... I think its the way Mainframe is setup with System A, and B that i can only connect with one system (as per JCL Job header info). Now there is a way, in my opinion we do have Datastage, so I am pretty sure we can do TPT with Datastage because it allows multiple login parameters. any suggestion/comments???

TonyL 51 comments Joined 12/09
11 Nov 2010

You can embed a TPT job script in the JCL. Let me know if you need a sample JCL with the TPT job script embedded.

You can specify login info for System A and System B in a TPT job variable file. See the sample job variable file at the end of this article. In the sample job variable file, the SourceTdpId, SourceUserName, and SourceUserPassword can be the login info for System A. The TargetTdpId, TargetUserName, and TargetUserPassword can be the login info for System B.

In the TPT job script, you can change "TYPE LOAD" to "TYPE UPDATE" to use the Update operator. The TPT Update operator uses the MultiLoad protocol to load the data.

I'm not familiar with Datastage.

jasondaniel009 1 comment Joined 02/10
26 Nov 2010

Hi Tony,
Can yu please send me sample JCL wuth the TPT script embedded...My email Id jasondaniel.007@gmail.com

go161300 11 comments Joined 06/09
05 Jan 2011

Hi, Tony
FastExport the following columns may be used without definition.
SELECT * FROM $ {From_DB}. $ {Tbl_Name};
. END EXPORT;
But if your TPT sentence shall specify all the columns. You do not use it any other way do you have?
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);

TonyL 51 comments Joined 12/09
06 Jan 2011

Currently, TPT requires a schema definition with all of the columns listed. TPT needs to know the record layout in order to process the job.

daveemc 3 comments Joined 08/10
14 Jan 2011

Great article - almost there; I want to copy table from server A to server B. The table does not exist on server B. I want TPT to create table structure that matches server A on server B, and infer the structure when copying data to server B. How do you do this with above technique?

feinholz 76 comments Joined 05/08
14 Jan 2011

TPT is a load tool with similar capabilities as the standalone legacy utilities (i.e. FastLoad, MultiLoad, TPump) in that the user is responsible for setting up the tables prior to loading the data.

TPT provides that capability in its script language through the use of the DDL operator.

What you are looking for is basically a table copy (or table move) and the Teradata Data Mover tool is built for that type of scenario.

--SteveF

ia230042 1 comment Joined 01/11
30 Jan 2011

Hi Tony,

We want to extract data from Oracle and load it directly to Teradata. Can you please elaborate on the options we have with TPT and which one gives better performance. Is there any specific ODBC driver that is required? How about using AXSMOD?

feinholz 76 comments Joined 05/08
31 Jan 2011

TPT offers an ODBC operator which allows the copying of data from Oracle to Teradata without the need to land or stage the data in between.

We only certify with the ODBC drivers from DataDirect, and those must be purchased separately. We do not bundle ODBC drivers with TPT.

As for an AXSMOD, we will support any user-written AXSMOD as long as it conforms to the Access Module API. We do not supply an access module that reads from Oracle.

--SteveF

daveemc 3 comments Joined 08/10
05 Feb 2011

In our world the teradata move tool is a "dba only" tool (I'm not a DBA) - does Teradata have a command-line tool to move table/structure from server A to server B, creating table on server B infering DDL from server A, or, any other suggestions for performing same with TPT?

06 Feb 2011

very intersting article moving data without disk

srinu

feinholz 76 comments Joined 05/08
09 Feb 2011

We do not currently have a command-line driven tool that will create the table for you prior to moving the data.

--SteveF

emilwu 34 comments Joined 12/07
18 Feb 2011

Tony, I do have a question in regarding to TPT instance count parameter. Can you help?
Who can explain TPT instance count and its details
In TPT apply clause, you can specify number of instances used for each operator. For example

APPLY ('INSERT INTO TARGET TABLE (COL1, COL2) VALUES (:COL1, :COL2);') IGNORE DUPLICATE ROWS TO OPERATOR ( LOAD_OPERATOR[2])
SELECT * FROM OPERATOR(READ_OPERATOR[3]);

In this sample, my load_operator is a update operator, while read_operator is DATACONNECTOR PRODUCER hook up to a single file.

specifying the instance on the loader and reader makes me wondering what will happen in reality:

1. does that mean the the file reader will have multiple file reading process reading different portions of file in parallel to boost reading throughput or the file reader is still one instance and the reader process file in blocked fasion first. Then the buffer is dispatcehd to different threads to process data (match schema, split fields etc)?
2. Load operator is the one really interesting here. UPDATE operator is equivalent to MLOAD. specifying multiple instance of UPDATE operator makes me wondering what is happening here. It is unlikely to have multiple MLOAD jobs insert into the same target table. Then how does the 2 load operator works? does that mean that it is still one mload job, but by specifying 2 instance, number of sessions will be doubled? If not so, how does the multiple instance of load operator helps the load performance?

feinholz 76 comments Joined 05/08
18 Feb 2011

Instances are parallel processes that help share the workload for the particular operator.

In the case of the "file reader", multiple instances can be used to read from the same file to help boost performance. Each instance reads the entire file, but sends only a subset of rows to the loading operator (since most data is variable in length, it would be pretty difficult to have each instance reading a separate area of the file because each instance would not know where the row boundaries are).

We have shown that multiple instances reading from the same file *can* show an improvement in performance, but only if you have the CPU bandwidth.

So, if you have 2 file reader instances, both instances will read all of the data, but instance #1 will send rows 1, 3, 5, 7, etc., and instance #2 would send rows 2, 4, 6, 8, etc.

For the loading operator (i.e. Load, Update or Stream), each instance helps parallelize the loading of the data by pulling data out of the data streams.

It works a little differently, though, for the consumer operators.

TPT does not push the data through the data streams in a round-robin fashion. The data will flow to the first instance of the loading operator and if that operator instance can keep up with the rate at which the data is coming through the data streams, then that instance will get all of the work.

Only when the data stream queue for one instance becomes backed up will data be sent to the next instance (instance #2).

So, it is possible (depending on the amount of data and the speed at which the data is produced onto the data stream) for some instances to get no work at all.

We cannot tell you the optimum number of instances to use because it depends on a LOT of factors.

Try a load job with a lot of data with one instance for the loading operator. Then increase the instance count to 2, then to 3. Take a look at the log for the loading instance and it will tell you how many rows were processed for each instance.

You can then get an idea for the optimum number for that load job. I doubt you would ever need more than 3 or 4. The loading operators are pretty fast.

Another note regarding instances:

It is important to note that when you provide the max session count to the operator, that number is divided by the number of instances and each instance will connect a subset of that maximum.

So, if you set MaxSessions to 30 and you have 2 instances of the Update operator, each instance will connect 15 sessions. If you have 3 instances, each instance will connect 10 sessions. And if you find that instance #3 is getting very little work, it might be a waste of resources to have that 3rd instance.

So, picking the number of instances is a delicate balancing act between resource utiliziation (sessions, CPU, memory, processes, etc.).

And also note that even though you are using multiple instances of the Load or Update operator, to Teradata it looks like a single FastLoad or MultiLoad job.

--SteveF

emilwu 34 comments Joined 12/07
18 Feb 2011

feinholz, thanks for the detail answer for the reader and loader. I do want a little drill down into the loader. It looks to me that the update loader (consumer) still logon as a single mload job . the difference is that the TPT will decide what sessions to send data. So.. theoratically, If i have the defined number of cPU cycles devnoted to a single instance of load operator with max session of 30, it should be identical to 2 instances of load operator with total session = 30. The performance gain is to have multiple load operator which can take advantage of today's multi-core/multi-cpu system by using cpu cycles on the client side more efficiently(or use more cpu cycles comparing to a single load operator). Is that a correct understanding?

emilwu 34 comments Joined 12/07
18 Feb 2011

also one interesting point to put out there is that the load operator will only help the aquisition phase, rather than application phase.
Is that a correct understanding?

feinholz 76 comments Joined 05/08
18 Feb 2011

Yes, your understanding is correct on both replies. Having multiple processes work in parallel on the data takes advantage of multiple CPUs. And yes, it only helps the acquisition phase. The application phase is performed solely on the DBS side and the client application just waits for the DBS to finish.

--SteveF

emilwu 34 comments Joined 12/07
18 Feb 2011

great! that solves all my questions! Feiholz, you rock!

sughesh 2 comments Joined 11/10
30 Apr 2011

Can I use Bteq and Mload in one single script?

sughesh 2 comments Joined 11/10
30 Apr 2011

Can I use combination of Bteq, Mload, Fload and Fast Export in one single script?

feinholz 76 comments Joined 05/08
04 May 2011

Are you talking about the legacy utilities (just asking because this is a TPT thread)?
If so: MLoad, FLoad, FastExport and BTEQ all have their own script languages, so the answer would be no.
However, you can always call each utility from a batch or Perl script.
But you must be asking for a specific reason. Maybe if you elaborated on the reasons behind the questions, I could answer them better.
Thanks!

--SteveF

jkwight 13 comments Joined 06/10
10 May 2011

Ulrich,

We enjoyed your article and you are right - TPT is an excellent tool and one that TD probably doesn't understand its full potential. We @ Swift Transportation have developed an environment that creates/manipulates inter system source data to TD via object libraries, pulling out objects into TPT jobs @ run time. The primary purpose is to do our data sourcing from AS400 (DB2), Oracle & SQL Server, loading data into two separate TD systems in one TPT job to provide our dual load requirements - one of TPT's greatest strengths that is not available in the 'old' utilities. Because TPT is 'object oriented', the environment we created basically generates object definitions for all load types and operators for every table (TD and our sources). From the library, we select the specific objects, assemble them and then run with supplied parameters. It is too bad there is not a good user GUI for this, something that the TPT Wizard falls far short on. JK Wight & Karlo Borboa

JK - TD-PS
Arizona Territories

m.ajay.k 1 comment Joined 05/11
11 May 2011

Can Any body provide a sample JCL with the TPT job script embedded. my mail id is m.ajay.k@gmail.com

anjalisarma_86 1 comment Joined 09/11
07 Sep 2011

Hi,
I am new to TPT. I have created a job for loading data to Teradata table from a comma delimited flat file. First time , the job started and asked for DBC name, user id ,password and ACCOUNT ID. I am not sure what is an ACCOUNT ID and due to which job failed several times. Please explain what is an ACCOUNT ID in Teradata and from where I can get that?

My secound question is I created a new job but it is throwing error like 'JOB ATTRIBUTE NAME IS INVALID' while specifying Job Name, Checkpoint Interval etc in RUN JOB window.

Please suggest on this.

feinholz 76 comments Joined 05/08
07 Sep 2011

An AccountId string is optional information that is sometimes specified when an account is created. If the account is yours and you have no knowledge of an AccountId string, then there probably is no AccountId associated with that account and you can ignore.

Please provide all of the information you are receiving into this thread. It is difficult to determine the real problem without seeing the actual messages and their context.

Also, what product are you using?
Script-based TPT?
The TPT Wizard?
"tdload"?

Thanks!

--SteveF

super25 4 comments Joined 07/11
09 Sep 2011

I am using this for the 1st time since yesterday, through OLE Load GUI i have genertated a TPT script and runing to from our production server to dev server but it is dead slow ( inserted only 30k records in the last 12 hrs, is this how it is or is there any other way of export/import a table from between different servers?

Om 1 comment Joined 08/11
13 Sep 2011

In tpt 13.10 from mainframe how to identify how many records have been loaded by tpt so far, is there any option that you have to explicitly set? I am loading 100 million records
and I would like to see the status that fastload produces such as loading 100,000..200,000 and so on. Below is the part of the message by TPT. It doesn't tell how many records sent until records are completely sent.

**** 11:54:35 Number of records per buffer for this job: 316
**** 11:54:39 Starting to send data to the RDBMS
**** 11:54:41 Checkpoint complete. Rows sent: 1550
**** 14:49:49 Checkpoint complete. Rows sent: 100000930
**** 14:49:51 Finished sending rows to the RDBMS

Also, how do I get data connecter producer with multiple instance to read a single file on mainframe? When I set varchar multiplereader='y', I get condition of 8. Without that attribute, having multiple readers, there is always 1 instance reading the file. Any help is appreciated.

Thanks,
TD9

feinholz 76 comments Joined 05/08
15 Sep 2011

Due to the nature of the parallelism of TPT, we cannot output periodic messages indicating the number of records processed, because we do not have an accurate count.

User often use multiple instances of an operator to gain performance and enhance the loading task with parallel processing.
These multiple instances work in a master-slave type of configuration and the slave instances that are loading data do not communicate with the master regarding number of rows loaded until checkpoint time.

Thus, only the master would be able to report rows processed and that number would not be accurate.

As for the last question (about condition 8), we are aware of the issue and we are fixing it in 13.10.00, efix #7 (due out towards the end of September).

--SteveF

Mainframer 1 comment Joined 07/11
25 Oct 2011

Om,

I've been waiting on the MultipleReader fix as well. In the mean time you can split your main file into multiple files and then use UNION ALL with multiple load operators and single read operator.

Ex:
TO OPERATOR (LOAD_OPERATOR[2])
SELECT * FROM OPERATOR (READER_OPERATOR1[1])
UNION ALL
SELECT * FROM OPERATOR (READER_OPERATOR2[1])
UNION ALL
SELECT * FROM OPERATOR (READER_OPERATOR3[1]);

" Be nice to people on your way up, because you will need them on your way down " - Unknown.

feinholz 76 comments Joined 05/08
26 Oct 2011

TPT has a concept of "directory scan" which means you can place more than one file in a directory (or "PDS" on the mainframe) and just tell us how many reader instances you want to use, and we will read all of the files in parallel.

This would simplify the syntax and not require the use of UNION ALL. The syntax would be:

APPLY

TO OPERATOR (LOAD_OPERATOR[2])
SELECT * FROM OPERATOR (READER_OPERATOR[n]);

where 'n' is the number of reader instances to use.

From one of our mainframe developers:

On z/OS, the DirectoryPath attribute may refer to a HFS path, or a PDS (Partitioned Dataset) if the name is prefixed with “DD:”. Wildcards in the FileName attribute will invoke a scan of the HFS path or the PDS directory for matching names, depending on which type of object is named by DirectoryPath. I think this correct, but Tom would know for sure.

--SteveF

MichaelJin 1 comment Joined 11/09
23 Feb 2012

Thank you, TonyL.

I just completed data move between QA and DEV using Parallel Transporter as you suggested. Programing was easy. Performance was good.

mansi 2 comments Joined 04/12
12 Apr 2012

Hi TonyL,I want to migrate oracle database to teradata datawarehouse.Could you please help me on this by providing a general code for such jobs...thanks

feinholz 76 comments Joined 05/08
12 Apr 2012

We have an ODBC operator to extract data from Oracle.
So, in the script you would define an ODBC operator and provide the DSN information and the SELECT statement.
You then provide an operator for loading the data into Teradata (i.e. the Load, Update or Stream operator).
Please refer to the TPT User Guide. We probably have samples in there.

--SteveF

mansi 2 comments Joined 04/12
16 Apr 2012

ok Thanks...1 more thing...Do we need to install Teradata parallel transporter exclusively?..i thought it is part of teradata installation package set up..I have created the script,but not able to execute the same because TPT is not installed in my machine..From where i can install TPT?I am not able to find the same.

TonyL 51 comments Joined 12/09
16 Apr 2012

Teradata Parallel Transporter is not part of the Teradata Database installation package.
It's part of the Teradata Tools and Utilties installation package.
It's available on the Teradata Tools and Utilities media.

mjasrotia 1 comment Joined 08/11
18 Apr 2012

Tony,
Like other MLOAD and FLOAD utilities can we invoke a TPT via a Unix Shell Script ? Would be great if someone can post a sample UNIX script for any TPT job.

Secondly I hope that it can be used like other utilities without paying more more that.

feinholz 76 comments Joined 05/08
18 Apr 2012

Yes, you can invoke TPT via a Unix shell script like you can with FastLoad and MultiLoad.
(I do not have a sample, but it would be no different than your FastLoad/MultiLoad ones.)

If you already have a FastLoad and MultiLoad license, you will be able to use TPT and its Load and Update operators (and the other operators that do not require licenses) without the need for a new license.

Check with your account rep for more info.

--SteveF

dwhraghav 1 comment Joined 01/07
29 May 2012

Feinholz,
Could you please provide a sample JCL to call TPT script involving all the load operators to deal with EMP table. My email id id dwhraghav@gmail.com
Thanks in adv.

feinholz 76 comments Joined 05/08
30 May 2012

What version of TPT are you using?
We provide sample JCL when you install TPT on your mainframe.

--SteveF

suhailmemon84 64 comments Joined 09/10
16 Oct 2012

Feinholz, I have 2 questions based on your following comment on MaxSessions on 18 Feb 2011:

"So, if you set MaxSessions to 30 and you have 2 instances of the Update operator, each instance will connect 15 sessions. If you have 3 instances, each instance will connect 10 sessions. And if you find that instance #3 is getting very little work, it might be a waste of resources to have that 3rd instance."

1. Which is better? 2 instances with 15 sessions each(full utilized) or 3 instances with 10 sessions each but with 3rd instance as underutilized or not utilized at all?

2. If I have a job with 3 instances and 30 sessions as Maxsessions, you indicated that every instance will utilized 10 sessions each. In this case , if the job is not utilizing the 3rd instance at all, do the 10 sessions for the 3rd instance go waste and the job runs with 2 instances, 10 sessions each? or the 10 sessions of the 3rd instance get equally divided among the first 2 instances as 15/15 each?

feinholz 76 comments Joined 05/08
17 Oct 2012

1. In most cases (unless you have a very slow network between the client server and Teradata, or your Teradata system is really slow), 2 instances of the Update operator should be enough. (In fact, in some cases, you would only need 1 instance, but you will have to experiment since you know your environment.)

2. Once you indicate the number of instances, that is the number that will get started up as the operators are being executed. However, at runtime we do not know how many will be used until the data starts flowing. As long as the first instance can keep up with the rate at which data is flowing through the data streams, it will get all of the work. If the first instance's data buffers become full, then data will be directed to the 2nd instance. Only when the 2nd instance cannot also keep up with the rate at which data flows through the data streams will data get directed to the 3rd instance.

That 3rd instance will always be there with connected sessions. If the 3rd instance never gets used, then you are, in fact, wasting resources. You would be better of with just the 2 (using 15 sessions each).

There if no formula to determine the number of instances (because data is not fed to the instances in a round-robin fashion). The less instances you use, the more sessions each instance can connect.

--SteveF

suhailmemon84 64 comments Joined 09/10
17 Oct 2012

So if the 3rd instance is never used, the 10 sessions allocated to this instance are never used. They just sit there as idle connected to the system and do not do any form of work. Is that correct?

feinholz 76 comments Joined 05/08
17 Oct 2012

Yes.

--SteveF

suhailmemon84 64 comments Joined 09/10
17 Oct 2012

So this means that a job with 2 instances, 15 sessions each will run faster than the same job running with 3 instances ,10 sessions each but the 3rd instance never getting used. Correct?

feinholz 76 comments Joined 05/08
17 Oct 2012

Potentially, depending on your environment (speed of the database, throughput of the network, size of the job).
You will have to experiment and see what works best for you for that job.
We output all of the stats, so it will be obvious to you when you look at the output from the operator.

--SteveF

suhailmemon84 64 comments Joined 09/10
17 Oct 2012

Great....That resolves all my doubts.

This is just wishful thinking....but is it possible to have a feature (in future versions in teradata) that dynamically calculates the correct number of instances for the job?

Something like the "PACKMAXIMUM" feature of TPUMP.

feinholz 76 comments Joined 05/08
17 Oct 2012

Most likely, no. Although we would like to get there some day, it is very difficult for TPT to know what the user wants to do, the size of the data, the source of the data, the speed of the network, the speed of the database, etc.

As for TPump, that is a very slow utility that uses plain SQL sessions to populate/update tables. As much as most people want TPump to run as fast as possible, the database will always be the bottleneck for TPump jobs. Also, TPump can only read from a single file at a time.

TPT has the capability to read from entire directories of files. We cannot read the users' minds, and thus trying to determine the optimum number of instances and sessions for a particular job would be quite difficult.

--SteveF

suhailmemon84 64 comments Joined 09/10
17 Oct 2012

Thanks feinholz.

mayanktiwari 1 comment Joined 10/12
18 Oct 2012

Can Any body provide a sample JCL with the TPT/BTEQ job script embedded. my mail id is mayank.tiwari@outlook.com.

Thanks in advance. :)

mortyy 1 comment Joined 11/12
11 Dec 2012

We are attempting to use TPT to copy data from Oracle to Teradata. We have been able to get the TPT wizard 13.0 to generate a script for us, after loading a temp version of DATA DIRECT ODBC driver, on a local machine. The job runs and copies the sample table from Oracle 10g to TD 13.10 table, on the local machine. We have DATA DIRECT ODBC driver installed on our UNIX servers. We are having trouble getting a KSH to '" find " the driver in the ODBC.ini file.
In addition, we are trying to run this job from the mainframe via channel attach as well, with no luck. We can run 'normal' TPT jobs from the mainframe, but it doesn't seem to find the ODBC driver for Oracle ? Any suggestions ? thanks.

RG255025 1 comment Joined 02/13
19 Feb 2013

Hi New to the Teradata.

Could you please send me sample JCL with the TPT script embedded... Im using TPT 13.10
My email Id is rgopalsamy@gmail.com

Thanks
Rajesh G

Rajesh Gopalsamy

mceoni 2 comments Joined 03/12
14 Mar 2013

Hello!
Could you please send me a sample JCL with the TPT script ??
My email is mceoni@hotmail.com
We usually call BTEQ, FEXP and MLOAD programs, calling it on EXEC PGM parameter,
and using the script on SYSIN parameter, and the logon on a different DDNAME. (stored on a single line/ called by ".run file" on script)
 
Does TPT has better performance over FEXP/MLOAD ? Or the big question is just the disk space ?
And what about the "Pre Processor 2" ? Is there any ideal situation for it ?
 
Tks !
M Ceoni

TonyL 51 comments Joined 12/09
18 Mar 2013

See Appendix A in the TPT User Guide for information on sample JCL with a TPT script.
TPT should have better performance over FEXP/MLOAD when the bottleneck is the single process FEXP/MLOAD. TPT can scale to multiple processes to perform the job.
I am not familiar with the Preprocessor2.

ravi_shankar 1 comment Joined 04/13
13 Apr 2013

1) If we have a single flat file to be loaded into a table. Which better utility to go for ?  Fastload, Multiload, TPUMP or a TPT ? Please explain Why ?
 
2) In case I'm reading from multiple files then which utility should I opt for ???

feinholz 76 comments Joined 05/08
17 Apr 2013

1. We will always prefer that you use TPT. TPT is the load/unload tool moving forward.
2. TPT. TPT is the only load/unload application that can load multiple files in parallel.

--SteveF

AndrewSchroter 5 comments Joined 11/06
24 Apr 2013

Interested in being able to use a jobvariable to establish MaxSessions, Producer and Consumer instances in jobvariable file or command line.  Is that possible.
Useful when testing various session/instance scenarios.
 

TonyL 51 comments Joined 12/09
24 Apr 2013

Yes, it's possible.
See the TPT User Guide on how to use job variables.

AndrewSchroter 5 comments Joined 11/06
25 Apr 2013

I was successful with MAXSESSIONS, but it appears that for the instance specification [1] an integer is required and no substitute variable is accepted.
 

TonyL 51 comments Joined 12/09
25 Apr 2013

You can use a job variable for the instance specification.
Example 1: Using a job variable file:
MyInstances = 2
Example 2: Using the command line:
-u "MyInstances = 2"
In the TPT job script, use @MyInstances to reference the instance value.

AndrewSchroter 5 comments Joined 11/06
27 Apr 2013

I found that my problem was trying to put the instance definition in the Operator Definition, referring back to the job variable for # of instances. 
 

suhailmemon84 64 comments Joined 09/10
22 May 2013

Should TPT run faster than the legacy utilities(considering we use 1 reader instance, 1 writer instance) just because one is 64 bit technology and another is 32 bit technology?
Regards,
Suhail
 

iliyaskhan 4 comments Joined 10/12
05 Jun 2013

Hi,
 
I am new to teradata and trying to execute the sample script give above "Sample Script 1".  I have replace all the job variables with proper values and running the scipt using below command line -
tbuild -f tpt_emp
where tpt_emp is my tpt control file.
However, i am receiving below error while executing the tpt
 
Teradata Parallel Transporter Version 13.10.00.12
TPT_INFRA: TPT04013: Error: opening temporary job script output file: "No such file or directory" (2).
Job script compilation failed.
Job terminated with status 12.
 
It seems that i do not have proper permission but could not find which file/directory, I should be looking into.
Please provide your sugestion.
 
-Iliyas
 

TonyL 51 comments Joined 12/09
05 Jun 2013

Can you send us your TPT job script named "tpt_emp"?
What is the OS platform that you are running your TPT job on?
Are other TPT users having the same TPT0413 error?

iliyaskhan 4 comments Joined 10/12
06 Jun 2013

Hi Tony,
I am running the TPT script at AIX operating system. Other teams are successfully executing TPT system on same filesystem.
Below is snapshot of TPT job script -
 
DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK
DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'
(
   /*** Schema Definition ***/
   DEFINE SCHEMA EMPLOYEE_SCHEMA
   DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
   (
      EMP_ID   INTEGER,
      EMP_NAME CHAR(10)
   );
 
   /*** Export Operator Definition ***/
   DEFINE OPERATOR EXPORT_OPERATOR
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
   TYPE EXPORT
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName = 'exportoper_privatelog',
      INTEGER MaxSessions    =  8,
      INTEGER MinSessions,
      VARCHAR TdpId          = @SourceTdpId,
      VARCHAR UserName       = @SourceUserName,
      VARCHAR UserPassword   = @SourceUserPassword,
      VARCHAR SelectStmt     = 'SELECT * FROM SOURCE_EMP_TABLE;'
   );
   /*** Load Operator Definition ***/
   DEFINE OPERATOR LOAD_OPERATOR
   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
   TYPE LOAD
   SCHEMA EMPLOYEE_SCHEMA
   ATTRIBUTES
   (
      VARCHAR PrivateLogName = 'loadoper_privatelog',
      INTEGER MaxSessions    = 16,
      INTEGER MinSessions,
      VARCHAR TargetTable    = 'TARGET_EMP_TABLE',
      VARCHAR TdpId          = @TargetTdpId,
      VARCHAR UserName       = @TargetUserName,
      VARCHAR UserPassword   = @TargetUserPassword,
      VARCHAR ErrorTable1    = 'LOADOPER_ERRTABLE1',
      VARCHAR ErrorTable2    = 'LOADOPER_ERRTABLE2',
      VARCHAR LogTable       = 'LOADOPER_LOGTABLE'
   );
   /*** Apply Statement ***/
   APPLY
   ('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')
   TO OPERATOR (LOAD_OPERATOR [1])
   SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);
);
I am using below command to run TPT script -
tbuild -f tpt_emp_1 "SourceTdpId = 'SourceTdpId', SourceUserName ='SourceUserName', SourceUserPassword = 'SourceUserPassword', TargetTdpId = 'TargetTdpId', TargetUserName ='TargetUserName', TargetUserPassword = 'TargetUserPassword'"

TonyL 51 comments Joined 12/09
06 Jun 2013

What is the file permission for the TPT's $TWB_ROOT/logs directory?
TWB_ROOT is the environment variable that contains the TPT's install directory.
The default for the TPT 13.10 TWB_ROOT is:
/opt/teradata/client/13.10/tbuild

iliyaskhan 4 comments Joined 10/12
06 Jun 2013

Hi Tony,
logs directory has all permissions
ls -lrt $TWB_ROOT
drwxrwxrwx    2 root     system        78336 Jun 06 13:18 logs

iliyaskhan 4 comments Joined 10/12
07 Jun 2013

I did not change anything in script or in TPT configuration but script run succesfully today.. Do not know what happened all of sudden.

suhailmemon84 64 comments Joined 09/10
02 Sep 2013

Should TPT run faster than the legacy utilities(considering we use 1 reader instance, 1 writer instance) just because one is 64 bit technology and another is 32 bit technology?
Regards,
Suhail

atulkumarsharma 1 comment Joined 07/13
04 Sep 2013

Hi All
I am trying to run a TPT script to load data from the oracle environment to my teradata environment.But on executing the script am getting the following errror:
Teradata Parallel Transporter Version 13.10.00.12
Job log: /ngs/app/tbuild/teradata/client/13.10/tbuild/logs/slsaudd-10446.out
Job id is slsaudd-10446, running on abc
Found CheckPoint file: /ngs/app/tbuild/teradata/client/13.10/tbuild/checkpoint/slsauddLVCP
This is a restart job; it restarts at step MAIN_STEP.
TPT_INFRA: TPT02192: Error: Cannot load shared library    
           dlopen error:        0509-022 Cannot load module /ngs/app/tbuild/teradata/client/13.10/tbuild/lib/libodbcop.so.
        0509-150   Dependent module libodbc.a(odbc.so) could not be loaded.
        0509-022 Cannot load module libodbc.a(odbc.so).
        0509-026 System error: A file or directory in the path name does not exist.
        0509-022 Cannot load module /ngs/app/tbuild/teradata/client/13.10/tbuild/lib/libodbcop.so.
        0509-150   Dependent module /ngs/app/tbuild/teradata/client/13.10/tbuild/lib/libodbcop.so could not be loaded.
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)
Job slsaudd terminated (status 8)
 
 
In refrence to the above post "

TPT offers an ODBC operator which allows the copying of data from Oracle to Teradata without the need to land or stage the data in between.
We only certify with the ODBC drivers from DataDirect, and those must be purchased separately. We do not bundle ODBC drivers with TPT.
As for an AXSMOD, we will support any user-written AXSMOD as long as it conforms to the Access Module API. We do not supply an access module that reads from Oracle."
I want to know do we need some additional driver to perform loading from the oracle environment?
Please suggest...

 

TonyL 51 comments Joined 12/09
05 Sep 2013

Suhail,
The bit-ness should not have anything to do with performance. The performance should be similar.

TonyL 51 comments Joined 12/09
05 Sep 2013

Make sure the DataDirect library path is in the library path environment variable.

25 Sep 2013

Hi Tony,
We are getting below error for the same type of script. We are using exporter & load operators.
Please let me know which file it is going to write & where.
====================
Teradata Parallel Transporter Version 13.10.00.12
TPT_INFRA: TPT02022: Error: Writing to file 'temporary file':
 "No space left on device" (error code 28)

Job script preprocessing failed.
Job terminated with status 12.
===================
Thanks,
ABhijeet

25 Sep 2013

++ Adding in previous comment
As you updated us TPT won't land any data to filesystem, but here we can see cleary something is going to write on temporary file.
I am not sure what is the use this temporty file.
When I used another fs this TPT script loaded data into table successfully.
 
SO Please can you elaborate whats happening here.
 
Thanks,
Abhijeet

Poongundrandk 2 comments Joined 08/11
01 Oct 2013

I am trying to do a TPT from Oracle to Teradata  using ODBC operator and getting below error.(Unix environment)

OBBC_OPERATOR: connecting sessions

OBBC_OPERATOR: TPT17122: Error: unable to connect to data source

OBBC_OPERATOR: TPT17101: Fatal error received from ODBC driver:

              STATE=81, CODE=0,

              MSG='523 80'

 

I have set odbc.ini  in .profile file 

 

My odbcinst.ini says

 

DataDirect 6.1 Oracle Wire Protocol=Installed

[DataDirect 6.1 Oracle Wire Protocol]

Driver=/usr/lib/DWora25.so

APILevel=0

ConnectFunctions=YYY

DriverODBCVer=3.52

FileUsage=0

Setup=/usr/lib/DWora25.so

SQLLevel=0

 

And I am refering this lib file in my odbc.ini.

Am I missing something fundamentally wrong?

 

TonyL 51 comments Joined 12/09
01 Oct 2013

What is the exact error message from the driver?  There are so many reasons that can cause the failure in connecting a session.
Is the user account info correct?  Is the DSN set up correctly?  Is the library path set up correctly for the ODBC driver libraries?

Poongundrandk 2 comments Joined 08/11
03 Oct 2013

Thanks Tony for helping me on this.
I am getting the below issue now.
OBBC_OPERATOR: TPT17122: Error: unable to connect to data source
OBBC_OPERATOR: TPT17101: Fatal error received from ODBC driver:
              STATE=IM003, CODE=0,
            MSG='[DataDirect][ODBC lib] Specified driver could not be loaded'
I have set .odbc.ini in PATH as ODBCINI .Also I have set  /informatica/Informatica/PowerCenter91/ODBC6.1/lib in LD_LIBRARY_PATH and PATH
The below is .odbc.ini
[ODBC]
InstallDir=/informatica/Informatica/PowerCenter91/ODBC6.1
Trace=1
TraceDll=/td/teradata/client/13.10/odbc_32/lib/odbctrac.so
TraceFile=/u/poongs/trace.log
#TraceAutoStop=0
[ODBC Data Sources]
Oracle Wire Protocol=DataDirect 6.1 Oracle Wire Protocol
default=DWora25.so
MYDATASOURCE=DWora25.so

[MYDATASOURCE]
Driver=/informatica/Informatica/PowerCenter91/ODBC6.1/lib/DWora25.so
APILevel=0
ConnectFunctions=YYY
DriverODBCVer=3.52
FileUsage=0
HelpRootDirectory=/informatica/Informatica/PowerCenter91/ODBC6.1/help
Setup=/informatica/Informatica/PowerCenter91/ODBC6.1/lib/DWora25.so
SQLLevel=0
and ldd command says
warning: ldd: /informatica/Informatica/PowerCenter91/ODBC6.1/lib/DWora25.so: is not executable
        libDWicu25.so =>         /informatica/Informatica/PowerCenter91/ODBC6.1/lib/libDWicu25.so
        libodbcinst.so =>        /informatica/Informatica/PowerCenter91/ODBC6.1/lib/libodbcinst.so
        libsocket.so.1 =>        /usr/lib/sparcv9/libsocket.so.1
        libdl.so.1 =>    /usr/lib/sparcv9/libdl.so.1
        libCrun.so.1 =>  /usr/lib/sparcv9/libCrun.so.1
        libnsl.so.1 =>   /usr/lib/sparcv9/libnsl.so.1
        librt.so.1 =>    /usr/lib/sparcv9/librt.so.1
        libm.so.1 =>     /usr/lib/sparcv9/libm.so.1
        libc.so.1 =>     /lib/64/libc.so.1
        libmp.so.2 =>    /lib/64/libmp.so.2
        libmd.so.1 =>    /lib/64/libmd.so.1
        libscf.so.1 =>   /lib/64/libscf.so.1
        libaio.so.1 =>   /lib/64/libaio.so.1
        libdoor.so.1 =>  /lib/64/libdoor.so.1
        libuutil.so.1 =>         /lib/64/libuutil.so.1
        libgen.so.1 =>   /lib/64/libgen.so.1
        libm.so.2 =>     /lib/64/libm.so.2
Is it pertaining to 32 or 64 bit related issue . I dont have no clue how to find this oracle DataDirect driver is right version or not .
My OS version is Solaris 10 64-bit sparcv9 kernel modules .
 

vijaydf 5 comments Joined 06/12
29 Oct 2013

Hi Tony,
We are using named pipe as input file, when i tried loading the 8 named pipe in parallel i am getting the below error.
File_Loader: entering Acquisition Phase
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 65028
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 65028
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 65028
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 65028
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 65028
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 65028
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 65028
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
TPT_INFRA: TPT02610: Error: Could not allocate a data message of size 65028
TPT_INFRA: TPT02284: Error: Cannot create data buffer, Data Stream status = 3
 
Can you please provide your sugestion.
 

Vijay Mani

TonyL 51 comments Joined 12/09
29 Oct 2013

Vijai,
Try to use the tbuild -h option to increase the shared memory size as follow:
 tbuild -h 100M -f <TPT script> ...
-h 100M means to use 100 mb of shared memory.

vijaydf 5 comments Joined 06/12
29 Oct 2013

Thanks a lot Tony.. just a quick question. does the Shared memory is allocated from client system or is it the database space?

Vijay Mani

TonyL 51 comments Joined 12/09
29 Oct 2013

Vija, it's allocated from client system.

vijaydf 5 comments Joined 06/12
30 Oct 2013

Thanks Tony..

Vijay Mani

TDLEARNER01 2 comments Joined 11/13
19 Nov 2013

Hi Tony/Feinholz,
 
I have a requirement in my project to extract my data from DB2 tables to Teradata without landing the data in disk. Is it possible to write a BTEQ to extract data from DB2 tables and load in Teradata staging tables with TPT.
We have tried executing a TPT script in Mainframe JCL but we dont have the appropriate Lib file so unable to do so. Could you please help/guide on whether a BTEQ script can be written to extract and load the data into Teradata tables with TPT.
 
Thank you.
Regards,
Deepak

Thanks,
Deepak

TDLEARNER01 2 comments Joined 11/13
19 Nov 2013

Just to add, can a TPT job script be executed using tbuild -f from UNIX to extract the data from DB2 Tables to teradata tables. I am currently trying to execute a sample TPT job script using Tbuild and facing the below error message 

Teradata Parallel Transporter Load Operator Version 13.10.00.03
Teradata Parallel Transporter Export Operator Version 13.10.00.03
LOAD_OPERATOR: private log not specified
DATACONN: private log not specified
LOAD_OPERATOR: TPT10309: Invalid 'TenacitySleep' attribute value
LOAD_OPERATOR: Total processor time used = '0.004092 Second '
LOAD_OPERATOR: Start : Tue Nov 19 16:16:35 2013
LOAD_OPERATOR: End   : Tue Nov 19 16:16:35 2013
DATACONN: TPT10551: CLI '224' occurred while connecting to the RDBMS
DATACONN: TPT10507: CLI Error 224: MTDP: EM_NOHOST(224): name not in HOSTS file or names database.
DATACONN: Total processor time used = '0.021271 Second '
DATACONN: Start : Tue Nov 19 16:16:35 2013
DATACONN: End   : Tue Nov 19 16:16:35 2013
Job step MAIN_STEP terminated (status 8)
Job sample terminated (status 8)

 

Any inputs would be of great help!

 

 

Thanks,
Deepak

TonyL 51 comments Joined 12/09
19 Nov 2013

BTEQ cannot extract data from DB2 tables because BTEQ can only interface with a Teradata database.
You can use TPT to extract data from a DB2 table and load the data into Teradata without landing the data to disk. See the "Moving External Data into Teradata Database" chapter in the TPT User Guide for more information.

bbdd.tsunix 6 comments Joined 04/13
20 Nov 2013

Hi,
I need load date from oracle database to teradata in linux machine. Do you know, how i can do this with mload script??
Someboy can tell me a example od how i can to connect to oracle database directly from the mload script??
 
Regards

TonyL 51 comments Joined 12/09
20 Nov 2013

When you say "I need load date from oracle to teradata in linux machine", I assume you meant "I need to load data from oracle to teradata in linux machine".
If my assumption is correct, then you cannot do this with a MultiLoad script because MultiLoad cannot interface with Oracle. MultiLoad can only interface with a Teradata Database.
You can use TPT to extract data from Oracle database and load the data into a Teradata Database. See the "Moving External Data into Teradata Database" chapter in the TPT User Guide for more information.

bbdd.tsunix 6 comments Joined 04/13
29 Nov 2013

I am trying to use TPT to move data from a table  Oracle 11 R2 to Teradata 14 using the ODBC operator with Oracle ODBC driver and Teradata Driver
I have linux machine , from this i need connect to Oracle database (unix) and Teradata database (Linux) with a script
I installed Oracle CLient 11.2 in the machine and TTus 14.0
I have configured the odbc.ini and odbcinst.ini and the tnsnames with the connection to Oracle Database ans Teradata
****odbc.ini ***
[ODBC]
InstallDir=/opt/teradata/client/14.00/odbc_32
Trace=0
TraceDll=/opt/teradata/client/14.00/odbc_32/lib/odbctrac.so
TraceFile=/usr/joe/odbcusr/trace.log
TraceAutoStop=0
[ODBC Data Sources]
default=tdata.so
testdsn=tdata.so
[TRAINING]
Driver=Teradata
Description=Teradata running Teradata V1R5.2
DBCName= dir IP
LastUser=dbc
Username=*****
Password=*****
Database=training
DefaultDatabase=training
[AUX_BBDD]
Driver=/opt/teradata/oracle11/11.2.0/lib/libsqora.so.11.1
DSN=Oracle
ServerName=AUX_BBDD
UserID=system
Password=orBD02.11
***********************************
*********odbcinst.ini
[ODBC DRIVERS]
Teradata=Installed
[Teradata]
Driver=/opt/teradata/client/14.00/odbc_32/lib/tdata.so
APILevel=CORE
ConnectFunctions=YYY
DriverODBCVer=3.51
SQLLevel=1

[Oracle]
Description= Oracle ODBC Driver for Oracle
Driver=/opt/teradata/oracle11/lib/libsqora.so.11.1
FileUsage= 1
Driver Logging=7
**********************************************
I did script tpt for loadind data from Oracle and executed with tbuild -f , but this have error

DEFINE JOB CARGA_TABCAERR
DESCRIPTION 'carga de datos'
(
DEFINE SCHEMA TABCAERR
DESCRIPTION 'Schema of Oracle Data Source'
(
        CODMEN VARCHAR(35),
    TITMEN VARCHAR(30),
    TEXMEN VARCHAR(200),
    ICOMEN VARCHAR(11),
    BOTMEN VARCHAR(16),
    DEFMEN DECIMAL(1,0)
);
 

DEFINE OPERATOR ODBC_Operator
DESCRIPTION 'TPT ODBC Operator'
TYPE ODBC
SCHEMA TABCAERR
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ODBC_OPERATOR',
VARCHAR DSNName = 'AUX_BBDD',
VARCHAR UserName = '*****',
VARCHAR UserPassword = '*****',
VARCHAR SelectStmt = 'select CODMEN,TITMEN,TEXMEN,ICOMEN,BOTMEN,DEFMEN  from cat.tabcaerr;'
);
 

DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'loadoper_privatelog',
Varchar WorkingDatabase = 'TRAINING',
VARCHAR TargetTable = 'TABCAERR',
VARCHAR TdpId = 'BAR02201',
VARCHAR UserName = 'DBC',
VARCHAR UserPassword = 'trBD02.11',
VARCHAR ErrorTable1 = 'TRAINING.ERRTABLE1',
VARCHAR ErrorTable2 = 'TRAINING.ERRTABLE2',
VARCHAR LogTable = 'TRAINING.TABCAERR_LOG'
);
 

APPLY
( 'INSERT INTO training.TABCAERR (:CODMEN,:TITMEN,:TEXMEN,:ICOMEN,:BOTMEN,:DEFMEN);'
)
TO OPERATOR ( LOAD_OPERATOR)
SELECT * FROM OPERATOR (ODBC_Operator);
);
);
 
***************************
THE EXIT os this execution is :
Teradata Parallel Transporter Version 14.00.00.09
Job log: /opt/teradata/client/14.00/tbuild/logs/root-17.out
Job id is root-17, running on bar02202
Found CheckPoint file: /opt/teradata/client/14.00/tbuild/checkpoint/rootLVCP
This is a restart job; it restarts at step MAIN_STEP.
Teradata Parallel Transporter Load Operator Version 14.00.00.09
LOAD_OPERATOR: private log specified: loadoper_privatelog
Teradata Parallel Transporter ODBC Operator Version 14.00.00.09
ODBC_Operator: private log specified: ODBC_OPERATOR
ODBC_Operator: connecting sessions
ODBC_Operator: TPT17122: Error: unable to connect to data source
ODBC_Operator: TPT17101: Fatal error received from ODBC driver:
              STATE=IM003, CODE=0,
              MSG='[DataDirect][ODBC lib] Specified driver could not be loaded'
ODBC_Operator: disconnecting sessions
ODBC_Operator: TPT17124: Error: unable to disconnect from data source
ODBC_Operator: TPT17101: Fatal error received from ODBC driver:
              STATE=08003, CODE=0,
              MSG='[DataDirect][ODBC lib] Connection not open'
ODBC_Operator: Total processor time used = '0 Second(s)'
ODBC_Operator: Start : Fri Nov 29 10:11:54 2013
ODBC_Operator: End   : Fri Nov 29 10:11:54 2013
LOAD_OPERATOR: connecting sessions
LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: entering Acquisition Phase
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '0.25 Second(s)'
LOAD_OPERATOR: Start : Fri Nov 29 10:11:54 2013
LOAD_OPERATOR: End   : Fri Nov 29 10:11:57 2013
Job step MAIN_STEP terminated (status 12)
Job root terminated (status 12)
 
Can you tell me , what I can load the Driver from script JOb /tbuild) from read in Oracle database ??

TonyL 51 comments Joined 12/09
02 Dec 2013

Use the DataDirect ODBC driver because TPT is certified with the DataDirect ODBC driver.

GALPGR 10 comments Joined 08/09
02 Dec 2013

Hello

 

I have two systems

 

Table Source: 5555H

Table Target: 2690

 

The two systems are interconnected to a server with 4 1GbE links

 

Teradata Parallel Transporter Load Operator Version 13.10.00.04

Teradata Parallel Transporter Export Operator Version 13.10.00.06

Teradata Database Version: 13.10

 

- Test 1

Table source: 80,000,000 rows

Performance: Ok (30 seconds to export and import)

 

- Test 2

Table source: 507 million rows

Performance: Very very slow!

After 40 minutes the step "SELECT request sending" continues processing...

 

Why?

(In the source table no locks)

 

 

Regards,

 

 

GALPGR 10 comments Joined 08/09
02 Dec 2013

This is the output of the job:

This is the output of the job

Almost an hour processing!

 

How I can improve permformance?

 

 

 

Teradata Parallel Transporter Version 13.10.00.16

Job log: /opt/teradata/client/13.10/tbuild/logs/root-70.out

Job id is root-70, running on GALICIABAR

Teradata Parallel Transporter SQL DDL Operator Version 13.10.00.16

DDL_TVA: private log specified: /opt/teradata/client/13.10/teradata/client/13.10/tbuild/logs/exportoper_privatelog.log

DDL_TVA: connecting sessions

DDL_TVA: sending SQL requests

DDL_TVA: Rows Deleted:  0

DDL_TVA: disconnecting sessions

DDL_TVA: Total processor time used = '0.02 Second(s)'

DDL_TVA: Start : Mon Dec  2 16:34:10 2013

DDL_TVA: End   : Mon Dec  2 16:34:10 2013

Job step DDL_TVA_DELETE completed successfully

Teradata Parallel Transporter Load Operator Version 13.10.00.04

Teradata Parallel Transporter Export Operator Version 13.10.00.06

LOD_P_MKT_TABLES_ING_FINANCIEROS: private log specified: /opt/teradata/client/13.10/tbuild/logs/loadoper_privatelog.log

EXP_P_MKT_TABLES_ING_FINANCIEROS: private log specified: /opt/teradata/client/13.10/tbuild/logs/exportoper_privatelog.log

LOD_P_MKT_TABLES_ING_FINANCIEROS: connecting sessions

EXP_P_MKT_TABLES_ING_FINANCIEROS: connecting sessions

LOD_P_MKT_TABLES_ING_FINANCIEROS: preparing target table

LOD_P_MKT_TABLES_ING_FINANCIEROS: entering Acquisition Phase

EXP_P_MKT_TABLES_ING_FINANCIEROS: sending SELECT request

EXP_P_MKT_TABLES_ING_FINANCIEROS: entering End Export Phase

EXP_P_MKT_TABLES_ING_FINANCIEROS: Total Rows Exported:  570020103

LOD_P_MKT_TABLES_ING_FINANCIEROS: entering Application Phase

LOD_P_MKT_TABLES_ING_FINANCIEROS: Statistics for Target Table:  'RESTORE_STG.ING_FINANCIEROS'

LOD_P_MKT_TABLES_ING_FINANCIEROS: Total Rows Sent To RDBMS:      570020103

LOD_P_MKT_TABLES_ING_FINANCIEROS: Total Rows Applied:            570020103

LOD_P_MKT_TABLES_ING_FINANCIEROS: disconnecting sessions

EXP_P_MKT_TABLES_ING_FINANCIEROS: disconnecting sessions

LOD_P_MKT_TABLES_ING_FINANCIEROS: Total processor time used = '89.7 Second(s)'

LOD_P_MKT_TABLES_ING_FINANCIEROS: Start : Mon Dec  2 16:34:14 2013

LOD_P_MKT_TABLES_ING_FINANCIEROS: End   : Mon Dec  2 17:29:34 2013

EXP_P_MKT_TABLES_ING_FINANCIEROS: Total processor time used = '123.38 Second(s)'

EXP_P_MKT_TABLES_ING_FINANCIEROS: Start : Mon Dec  2 16:34:14 2013

EXP_P_MKT_TABLES_ING_FINANCIEROS: End   : Mon Dec  2 17:29:35 2013

Job step P_MKT_TABLES_ING_FINANCIEROS completed successfully

Job root completed successfully

 

TonyL 51 comments Joined 12/09
02 Dec 2013

Can you provide the TPT job's private logs?
You can use this sample command to retrieve the TPT private logs:
tlogview -j root-70 -f "*" -g -o tpt_privatelogs.txt
Run the command on the GALICIABAR system.
The TPT private logs will be saved in the tpt_privatelogs.txt file.

bbdd.tsunix 6 comments Joined 04/13
03 Dec 2013

Where can i dowload the DataDirect ODBC driver? is it included in ttu 14.0.0??
how can i configured the driver for odbc in the file odbc.ini???
thanks for all

GALPGR 10 comments Joined 08/09
03 Dec 2013

Hi TonyL

 

How do I attach the file?

How do I send the file?

 

file = tpt_privatelogs.txt

 

Regards,

TonyL 51 comments Joined 12/09
03 Dec 2013

Pablo,
You can send me the tpt_privatelogs.txt file.
My email address is:
TL122451@teradata.com

TonyL 51 comments Joined 12/09
03 Dec 2013

The DataDirect ODBC driver is not on the TTU media.
You can download the DataDirect ODBC driver and the installation/configuration guide from this link:
http://www.datadirect.com/products/datadirect-connect/odbc-drivers

DT 1 comment Joined 12/13
30 Dec 2013

Requires to use the data direct for a data extraction  from Oracle tpt to Teradata, or I can use the Oracle ODBC native.

gaurav_942 1 comment Joined 10/12
13 Jul 2014

Hi Tony,
 
I am executing few TPT scripts.
Many of the scripts are facing following issue.

body { margin: 0 0 0 0; padding:0 0 0 0 }
td,div { font-family:Segoe UI;font-size:9pt;vertical-align:top }
/* Copyright IBM Corp. 2012 All Rights Reserved. */
body { margin: 0 0 0 0; padding:0 0 0 0; overflow:hidden; }
.transcript { background-color:#d2d2d2; }
.messageBlock { padding-left:10px; padding-right:10px; margin-bottom:3px }
.message { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre; white-space:pre-wrap;}
.messageCont { padding-left:20px; margin-left:95px; word-wrap:break-word; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap;}
.other { font-size:11px;color:#39577a;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }
.myself { font-size:11px;color:#da8103;font-style:normal;font-weight:bold;font-style:normal;float:left; width:95px; }
.otherCont { font-size:8px;text-align:right; color:#39577a;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.myselfCont { font-size:8px;text-align:right; color:#da8103;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.system { font-size:11px; word-wrap:break-word;color:#da8103;font-style:normal;font-weight:normal; white-space:-moz-pre-wrap; _white-space:pre;white-space:pre-wrap; }
.showTimestamp { padding-left:20px; font-size:11px; float:right; color:#999999;font-style:normal;font-weight:normal; }
.other1 { font-size:11px; color:#ac2000;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }
.otherCont1 { font-size:8px;text-align:right; color:#ac2000;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.other2 { font-size:11px; color:#3c9fa8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }
.otherCont2 { font-size:8px;text-align:right; color:#3c9fa8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.other3 { font-size:11px; color:#e25614;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }
.otherCont3 { font-size:8px;text-align:right; color:#e25614;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.other4 { font-size:11px; color:#0b6ac8;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }
.otherCont4 { font-size:8px;text-align:right; color:#0b6ac8;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.other5 { font-size:11px; color:#b23290;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }
.otherCont5 { font-size:8px;text-align:right; color:#b23290;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.other6 { font-size:11px; color:#02e7c7;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }
.otherCont6 { font-size:8px;text-align:right; color:#02e7c7;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.other7 { font-size:11px; color:#5b3284;vertical-align:top;font-weight:bold;font-style:normal;float:left; width:95px; }
.otherCont7 { font-size:8px;text-align:right; color:#5b3284;font-family:Arial,Lucida Grande;font-style:normal;vertical-align:top;font-weight:bold;float:left; width:95px; }
.highlight { background-color:#bed6f8; }
.datestamp { padding-right:0px; font-size:11px; cursor:default; margin-bottom:1px; background-color:#c0c0c0; width:100%; float:left; text-align:right; color:#ffffff; font-weight:bold; font-style:italic; }
#chatAlert { float:left; border-bottom:1px solid #E8D091; padding:6px; width:100%; color:#A5754C; }
#chatAlertImage { float:left; }
#chatAlertText { float:left; margin-left:6px; }
#chatAlertClose { float:right; margin-right:10px; padding-right:6px; margin-top:0px; }
#chatAlertText a { color:#A5754C; }
#chatAlertText a:hover { color:#A5754C; text-decoration:none; }

.tsDisplay { display:block }.dsDisplay { display:none }

"Missing concatenation operator -- '||' -- between adjacent tokens. "
and many of them are running fine, I have writtenn all in same manner...
Can you please guide me?
 

vincent91 20 comments Joined 02/10
01 Oct 2014

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

fnewbrough 1 comment Joined 05/11
01 Oct 2014

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
;

TonyL 51 comments Joined 12/09
01 Oct 2014

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.

vincent91 20 comments Joined 02/10
02 Oct 2014

 
Fnewbroug : thank you very much for your code
Tony : it didn't know tpt easyloader. 
This forum is very helpful:). thank you

vincent91 20 comments Joined 02/10
09 Oct 2014

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 ?

TonyL 51 comments Joined 12/09
09 Oct 2014

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.

Sikee 2 comments Joined 10/14
27 Nov 2014

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

Sikee 2 comments Joined 10/14
28 Nov 2014

Any body who can help :( ?
Regards,
Sik

selvamr_2003 2 comments Joined 01/14
13 Jun 2015

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

TonyL 51 comments Joined 12/09
15 Jun 2015

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.

selvamr_2003 2 comments Joined 01/14
20 Jun 2015

Thanks your response Tony
1. Does "10 schema" mean "10 tables"?
 each schema conations 10,20,50 etc.. tables

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

3. Do you need to move the data from one Teradata database to another Teradata database?
Sources is Oracle 11g Databases

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)"?
We need subset of data not entire tables. For example: employee table has 1b rows but need move 10m rows to Teradata.
Thanks,
Selvam
 
 

TDThrottle 11 comments Joined 11/11
02 Dec 2015

Hi,
We have similar requirement to extract data from Oracle and load it to Teradata using TPT. Hope DataDirect is the only certified ODBC driver to connect Oracle and run FastExport utility.
Hope TD 15.10 TTU support Oracle Wire Protocol and I believe this is certified or authorized methos to extract data from Oracle. If yes, do we still need to dependent on DataDirect which includes additional cost.
Teradata tbuild ODBC client: /opt/teradata/client/15.10/tbuild/odbc
Looking forward for your expertise inputs.
Thanks!!
 
 

jammy19031989 9 comments Joined 07/13
05 Jan 2016

 
Besides, I would like to know whether the Teradata system would take up more resource while loading from Oracle to Teradata Without Landing to Disk.
When the Script start to execute, it connects to load session in Teradata first and then only it connects to ODBC sessions in oracle.
My question is whether the resource is unnecessarily getting utilised in Teradata until the time ODBC Export is happening from Oracle perspective. 

 

Kamma1166 1 comment Joined 07/13
27 Jan 2016

Hi Tony,
 
i want migrate 6TB of data in Teradata Database, could you please help me how TPT can help me for this.

TonyL 51 comments Joined 12/09
27 Jan 2016

Kamma1166, what is the source of the 6TB data?
Is the source data on 1 or more disk files?
Is the source data on an ODBC-complaint database, such as Oracle or DB2?
Is the source data on a different Teradata Database?

samy@7 6 comments Joined 06/16
01 Jun 2016

I am new to tpt..I want to tansfer data from oracle to teradata table.I wrote a script for that.Will u plz tell me how to run it.I m using unix(putty).Plz tell me what are the requirements to run the tpt script.Also tell me which operators need to use in the tpt script.
thanku..

TonyL 51 comments Joined 12/09
03 Jun 2016

samy@7, You need to use the TPT "tbuild" command to run a TPT script.
For example: tbuild -f <TPT script> <TPT job name>
See the "tbuild" command in chapter 1 of the TPT Reference.
You need to have the following 5 Teradata packages installed on your machine in order to run a TPT script:
* Teradata TeraGSS package
* Teradata tdicu package
* Teradata cliv2 package
* Teradata Parallel Transporter Base package
* Teradata Parallel Transporter Stream Operator package
You must use the TPT ODBC Operator to extract rows from Oracle.
You can use one of these TPT operators to load into a Teradata table:
* TPT Load operator
* TPT Update operator
* TPT Stream operator
* TPT SQL Inserter operator
For more information on the TPT operators, see the TPT Reference.

samy@7 6 comments Joined 06/16
05 Jun 2016

Thanku Tony..useful information.But I am having one doubt..you said that use the TPT ODBC Operator to extract rows from Oracle..My doubt is which dirvers should I use?means teradata ODBC drivers or Oracle ODBC divers?I have downloaded DATADIRECT_ODBC_TERADATA_SOL_SPARC_32.tar i.e. ODBC divers for solaris,is it correct? and during download I chose oracle daatbase.please tell me..I am confused a lot.

TonyL 51 comments Joined 12/09
06 Jun 2016

samy@7, you can use either Progress DataDirect ODBC drivers or Simba ODBC drivers.
The Progress DataDirect ODBC drivers are bundled with the TPT package starting with these TPT versions:
1. TPT 14.10.00.06 and later
2. TPT 15.00.00.02 and later
3. TPT 15.10.00.00 and later
For more information, see the TPT Reference.

samy@7 6 comments Joined 06/16
08 Jun 2016

Hi Tony,
As you told..use Progress DataDirect ODBC drivers so I downloaded it.But the file is in tar.Z format.In unix I tried to unzip it by using tar -xvf filename.tar.Z command but it is showing following errors:
uncompress: filename.tar.Z: corrupt input  
tar: directory checksum error

and the filename.tar.Z contains zip files inside it.How to fix this error?
How to unzip it? It is necessary to unzip for installation of ODBC drivers. 
Thanku.

VishnuVardhan 3 comments Joined 08/10
08 Jun 2016

Sam@7, you have to use gunzip command to first unzip the file and then use the tar command as follows:

 

#gunzip filename.tar.Z

 

#tar -xvf filename.tar

 

once you do that, you can find install.sh file to run it to install Progress drivers.

 

Having Said that, If you are using the following versions as Tony mentioned, thr Progress DataDirect ODBC drivers are bundled with TPT and will be installed with it. You can find more information in the TPT Reference Manual on how to use these drivers. 

 

If you do not have a Data Direct ODBC driver permanent license, please contact your Teradata Account Representative or Teradata Customer Support to procure a permanent license. Alternatively, you are can order the Data Direct Driver Connector license from Progress Software.

 

-- Vishnu Vardhan --
 

samy@7 6 comments Joined 06/16
12 Jun 2016

Hi,
I tried as you told..but still same error..I have downloaded PROGRESS_DATADIRECT_ODBC_ORACLE_SOL_SPARC_32.tar.Z.I tried it as you said..but its not working.When I run gunzip filename.tar.Z in unix it will show following error
gunzip PROGRESS_DATADIRECT_ODBC_ORACLE_SOL_SPARC_32.tar.Z;

gzip: PROGRESS_DATADIRECT_ODBC_ORACLE_SOL_SPARC_32.tar.Z: corrupt input. Use zcat to recover some data.
 
According to error I tried zcat command.It is showing following output.
 zcat PROGRESS_DATADIRECT_ODBC_ORACLE_SOL_SPARC_32.tar.Z| tar xvf -
x autorun.dat, 2904 bytes, 6 tape blocks
x etc/lang/license.txt, 59166 bytes, 116 tape blocks
x etc/lang/msg.dat, 30 bytes, 1 tape blocks
x etc/lang/useng.msg, 18233 bytes, 36 tape blocks
uncompress: PROGRESS_DATADIRECT_ODBC_ORACLE_SOL_SPARC_32.tar.Z: corrupt input
 
It is showing some files but after that again error.
please give solution for this.
Thanku.

VishnuVardhan 3 comments Joined 08/10
13 Jun 2016

Sam@7,
Looks like the file is corrupt and hence the message.
Did you download the file directly from a linux/Sparc box or copied it from a windows machine?
Did the file download complete successfully?
If thats the case, Did you copy the file in Binary format? to the Sparc machine?

-- Vishnu Vardhan --
 

nickkyboy 1 comment Joined 10/11
20 Jun 2016

Hi,
I'm trying to configure TPT to fetch data from MQ Websphere and write directly into Teradata table. 
TPT: v15.10 64bit (Window)
MQ Websphere: v8 64bit
TD database: v13.10
Data in MQ Queue:
a|s|d|f|g
c|d|f|g|h
 
I managed to fetch 1 row from MQ and also inserted into the target table. But when i uploaded 2 rows of record into the MQ queue and run the same TPT script again, it fails due to the below error:
Error:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Teradata Parallel Transporter Version 15.10.01.02 64-Bit

Teradata Parallel Transporter DataConnector Operator Version 15.10.01.02

MQ_READER[2]: Instance 2 directing private log report to 'dataconnector_log-2'.

MQ_READER[1]: Instance 1 directing private log report to 'dataconnector_log-1'.

MQ_READER[1]: DataConnector Producer operator Instances: 2

Teradata Parallel Transporter Stream Operator Version 15.10.01.02

STREAM_OPERATOR: private log specified: stream_log-1

**** 13:31:18 MQA19080 Teradata Websphere MQ AMOD, Version 15.10.01.001

**** 13:31:18 MQA19081 COPYRIGHT 2001-2016, Teradata Corporation.  ALL RIGHTS RESERVED.

**** 13:31:18 MQA19083 Code version 15.10.01.01

**** 13:31:18 MQA19084 Compiled for 64-bit WIN64

**** 13:31:18 MQA19085 pmdcomt_HeaderVersion: 'Common 15.10.00.00' - packing 'pack (push, 1)'

**** 13:31:18 MQA19086 pmddamt_HeaderVersion: 'Common 15.10.00.00' - packing 'none'

**** 13:31:18 MQA19087 MQAM Parameters in effect:

     .        Queue Manager Name: 'xxx_LOCAL'

     .        Queue Name: 'xxx.TPT'

     .        Checkpoint: File-based to pathname CKFILE_xxx.log

     .        Media flush: No

     .        Logging: none

     .        Terminate on duplicate message: yes

     .        MQ convert message data: no

     .        Queue open: NON-EXCLUSIVE.

     .        BlockSize: 32000

     .        MQ open retry tenacity: 5

     .        MQ open wait interval: 5

     .        MQ read wait interval: 15

MQ_READER[1]: ECI operator ID: 'MQ_READER-7592'

MQ_READER[1]: Operator instance 1 processing file 'DD:DATA'.

STREAM_OPERATOR: Start-up Rate: UNLIMITED statements per Minute

STREAM_OPERATOR: Operator Command ID for External Command Interface: STREAM_OPERATOR7588

STREAM_OPERATOR: connecting sessions

STREAM_OPERATOR: The job will use its internal retryable error codes

STREAM_OPERATOR: The job will use its internal data-related error codes

MQ_READER[1]: TPT19134 !ERROR! Fatal data error processing file 'DD:DATA'. Delimited Data Parsing error: Column length o

verflow(s) in row 1.

 

MQ_READER[1]: TPT19015 TPT Exit code set to 12.

STREAM_OPERATOR: disconnecting sessions

MQ_READER[1]: Total files processed: 0.

STREAM_OPERATOR: Total processor time used = '0.624004 Second(s)'

STREAM_OPERATOR: Start : Tue Jun 21 13:31:18 2016

STREAM_OPERATOR: End   : Tue Jun 21 13:31:57 2016

Job step MAIN_STEP terminated (status 12)

Job sckhaw terminated (status 12)

Job start: Tue Jun 21 13:31:14 2016

Job end:   Tue Jun 21 13:31:57 2016
---------------------------------------------------------------------- end error ----------------------------------------------------------------------------------------
And here's my script:

---------------------------------------------------------------------- TPT script ----------------------------------------------------------------------------------------

DEFINE JOB MQ_LOAD

DESCRIPTION 'Load a Teradata table using MQSeries'

(

DEFINE SCHEMA MQ_SCHEMA

(

Associate_Name VARCHAR(10),

DOJ VARCHAR(10),

Designation VARCHAR(15),

Loan_Amount VARCHAR(1),

Martial_Status VARCHAR(1)

);

 

DEFINE OPERATOR MQ_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA MQ_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'dataconnector_log',

VARCHAR FileName = 'DD:DATA',

VARCHAR Format = 'Delimited',

VARCHAR OpenMode = 'Read',

VARCHAR TextDelimiter ='|',

VARCHAR AccessModuleName = 'libmqsc',

VARCHAR AcceptMissingColumns = 'Y',

VARCHAR AccessModuleInitStr = '-qnm xxx.TPT

                               -qmgr ..._LOCAL

                               -TRCL 4 MQTRCE_xxx

                               -CKFILE CKFILE_xxx.log'

);

 

DEFINE OPERATOR STREAM_OPERATOR

TYPE STREAM

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'stream_log',

VARCHAR TdpId = 'xxx.xxx.xxx.xxx',

VARCHAR UserName = 'xxx',

VARCHAR UserPassword = 'xxx',

VARCHAR LogTable = 'xxx.sanity_test_MQS_log',

VARCHAR ErrorTable = 'xxx.sanity_test_MQS_error'

);

 

APPLY

('INSERT INTO xxx.nick_MQS_result VALUES (

:Associate_Name,

:DOJ,

:Designation,

:Loan_Amount,

:Martial_Status

);

')

TO OPERATOR (STREAM_OPERATOR[2])

SELECT * FROM OPERATOR (MQ_READER[2]);

);

---------------------------------------------------------------------- End TPT script -----------------------------------------------------------------------------------

 

Appreciate your advice on this.

 

-- Nickkyboy --

Karthikk 2 comments Joined 10/15
05 Jul 2016

Hi All,
I have a TPT script to fetch data from ORACLE and load it into Teradata Table. I am using ODBC operator and LOAD operator for this activity. Now I want to load the data from oracle into 2 different TD systems at the same time. I have tried this scenario by defining two LOAD OPERATORS but only one TD system is getting loaded.
Please advice on thie scenario if its possible to load 2 TD servers at the same time.
Thanks in advance :)
Regards,
Karthik

--Karthik

chandrasekharp 1 comment Joined 07/16
07 Jul 2016

Hi All,
I would like to use TPT to extract data from Oracle and load it into teradata. I understand we need to use ODBC operator to connect oracle with using ODBC data direct connection. is there any way to connect to oracle with out ODBC data direct connection?
like using native oracle client drivers instead odbc.
when we use odbc connection to export data , it will take more time than oracle client. How can achieve the performance. I need to run this every day for 40 tables.
 
Thanks,
chandra
 

TonyL 51 comments Joined 12/09
12 Jul 2016

Karthik, yes, it's possible to load to multiple TD servers at the same time.
You need to define different values for the TdpId attribute for each of the 2 Load operators in order to load to 2 TD servers.
See the "Multiple APPLY feature" in the TPT User Guide for more information.
 

TonyL 51 comments Joined 12/09
12 Jul 2016

Chandra, you can use the Simba ODBC drivers.
See the "ODBC Operator" chapter 10 in the TPT 15.10 Reference for more information.

Karthikk 2 comments Joined 10/15
18 Jul 2016

Hi Tony,
  I have refered the manual and I am able to load multiple target servers using MULTIPlLE APPLY feature of TPT. Thank you for your assistance on the same
 
Regards,
Karthik

--Karthik

samy@7 6 comments Joined 06/16
11 Aug 2016

Hi,
I want to write TPT script that will sync the data from oracle to teradata table.I am confused with the variables,for example what will be the TdpId,PrivateLogName etc.Please tell me with example.Also how to write TPT script step by step.As I am beginner not able to understand how start with it.
 
Thanku.

samy@7 6 comments Joined 06/16
08 Sep 2016

Hi,

I am trying to install oracle ODBC drivers(PROGRESS_DATADIRECT_ODBC_ORACLE_SOL_SPARC_32.tar) in unix to connect to teradata.When I was trying for 1st time,up to some steps it was correctly working.But after some steps an error has occured.So I removed that .tar.Z file from directory in unix to intalll ODBC drivers again.But now it is showing following error.

$ gunzip PROGRESS_DATADIRECT_ODBC_ORACLE_SOL_SPARC_32.tar.Z
$tar -xvf PROGRESS_DATADIRECT_ODBC_ORACLE_SOL_SPARC_32.tar
autorun.dat
etc/lang/license.txt
etc/lang/msg.dat
etc/lang/useng.msg
etc/lic/makelica
etc/lic/makelich
etc/lic/makelichx
etc/lic/makelicl
etc/lic/makelics
etc/lic/makelicsx
install.mi
odbc/solaris/license.txt
odbc/solaris/NOTICES.TXT
odbc/solaris/ODBCFIXES.TXT
odbc/solaris/ODBCREADME.TXT
odbc/solaris/scr1
odbc/solaris/tarfiles/brand.tar
odbc/solaris/tarfiles/dbase.tar
odbc/solaris/tarfiles/drda.tar
tar: Unexpected EOF in archive
tar: rmtlseek not stopped at a record boundary
tar: Error is not recoverable: exiting now

please any one tell me what is the problem.I tried it plenty of times.
Thanku!!

You must sign in to leave a comment.