Teradata tpt

Tools & Utilities

Teradata tpt

Hi All, I am new to this post and in teradata.

I am trying to create one unix script that will do the table sync from one to another for COB sync with tpt utility.

I need script to unload from source table and load to target table by tpt and if possible unload file in delimited.But also I need to generate the dml of the unload file and save it for future use.

Can you please help if there are anything in tpt like .ml script in fast export automatically created, else how to achieve that.

Also I want the tpt load should be upsert which tdload is not doing.I tried with tdload but it is only doing insert.

Teradata Employee

Re: Teradata tpt

The TPT documentation has sample scripts to help you with this.

When you install TPT, there is also a samples directory with sample scripts for all of the popular scenarios (like the one you seek).

TPT does not have a feature like FastExport's (to create an ML script).

As for tdload, right now it is only used for inserts.

(The original concept behind the Easy Loader feature is to quickly and easily perform inserts from a flat file into a table without the need for a script.)

-- SteveF

Re: Teradata tpt

Thanks Steve, I have gone through tpt scripts from this forum and  understood the thing.

1.Is there any way I can generate the dml of the file to flat file and save it for future use?

2.Also If I am creating delimited unload file say '|' separated, it is failing for the tables which all records are not varchar.is there any solution?confused about lot of solution on this in the forum.

3.is there any way that I can unload the table by tpt and load the unloaded file by mload? as in this case I need the dml which not being able to generate in tpt.

Teradata Employee

Re: Teradata tpt

3. Why would you want to export from TPT and load back in with MultiLoad? Why not just load back in with TPT?

2. For delimited output, it depends on which version of TPT you are using. Earlier versions of TPT only supported writing out delimited in VARCHAR format and so you had to CAST the SELECT statement, or use the SQL Selector operator and specify ReportMode='Y' which would force the DBS to convert all columns to character. For later (I think starting in 14.10 or 15.0) the DataConnector file writer will do the conversion for you, and the schema does not have to be comprised of all VARCHAR columns.

1. Please explain why you need to save the DML away for future use.

-- SteveF

Re: Teradata tpt

Hi Steve,I am using now tpt unload and load.I need to execute the below sql

SQL = 'SELECT *  FROM xxx.WZ1D02_BITPROC where CAST(fec_proceso as date format  'YYYY-MM-DD') >= '2014-12-02';'

I am keeping this in job variable file but getting error as-

Teradata Parallel Transporter Version

TPT_INFRA: TPT02026: Error: Line 12 of Local Job Variable File:

Quoted string lacks a single quote terminator character (')

Can you please advise how I can write the SQL query in job variable file.My current job variable file entery is as below and completly dinamically generated before calling the tbuild.

UsrId = 'xxxxx'

Pwd = 'xxxxx'

Tab = 'WZ1D02_BITPROC'

DB = 'xxx'

Tdp = 'xxx'

PrivateLog1 = 'file_writer_privatelog1_25559114'

PrivateLog2 = 'file_writer_privatelog2_25559114'

PrivateLog3  = 'file_writer_privatelog3_25559114'

Database_Table = 'xxx.WZ1D02_BITPROC'

OP_FILE ='WZ1D02_BITPROC.20141227200452.dat'

DIR_PATH = 'xxx/xxx/'

SQL = 'SELECT *  FROM xxx.WZ1D02_BITPROC where CAST(fec_proceso as date format 'YYYY-MM-DD') >= '2014-12-02';'

Teradata Employee

Re: Teradata tpt

A string attribute value must be enclosed with single quotes.

Thus, you need to escape the single-quotes inside that string.

You need to double all of the single quotes insidfe the quoted string.

-- SteveF

Re: Teradata tpt

thanks Syeve, it worked.

I am unloading the data by fastexport or tpt to comma separated file with select * from tab; and trying to load the same unloaded csv file to target table.

This source and target table columns are not all varchar, instead they can be char, date,timestamp,decimal  all.

Can this loading be achived by mload?if so, can you please give the options in mload.

Teradata Employee

Re: Teradata tpt

MultiLoad is only a load tool, so not sure what you mean be "archive".

MultiLoad can load csv data. The record format is called "vartext".

In the schema definition, all columns must be defined as varchar in order to use vartext loading.

The MultiLoad Reference Manual has the necessary information for you.

Question: why are you unloading with FastExport (or TPT) and loading with MultiLoad? What is the ultimate goal here? And just fyi, you should always be using TPT.

-- SteveF

Re: Teradata tpt

Hi Steve, hi everybody.

Our company is switching from Oracle to Teradata and to tell you the truth i'm getting confused to work with TPT script instead of SQL LOADER .

Please if you can show me some ressources to read in this website or other to start on feet as soon as possible with Teradata and specialy with TPT script .

Thank you.

Teradata Employee

Re: Teradata tpt

Well, this thread is probably not the proper place for this topic.

You should start a new thread/topic.

TPT is too large of a product to answer in a single post.

We have documentation for TPT in http://www.info.teradata.com/.

Look in the Tools And Utilities section.

We have a Reference Manual, User Guide and Quick Start Guide.

You can also email me at steven.feinholz@teradata.com.

-- SteveF