Teradata TPT Script

Tools & Utilities
Teradata Employee

Re: Teradata TPT Script

I would think if you have an error of this nature, and you would like assistance, you would provide a little more information than what you have provided.

What version of TPT are you using?

Provide your script.

Provide all of the output that you have available.

-- SteveF
Enthusiast

Re: Teradata TPT Script

Hey Feinholz, I finally have gotten all the packages installed on my RedHat server.  I know you said to use TPT now for high volume transfer.  I wanted to do it via a perl.  I have comma delimted files and the table is empty on the Teradata side. I am setting up my schema, however how do I incorporate the files to read into the ' DEFINE OPERATOR FILE_READER' schema.  

  DEFINE OPERATOR FILE_READER

  TYPE DATACONNECTOR PRODUCER

  SCHEMA Trans_n_Accts_Schema

  ATTRIBUTES

  (

    VARCHAR PrivateLogName = 'dataconnector_log',

    VARCHAR DirectoryPath  = @jobvar_datafiles_path,

    VARCHAR FileName       = @myfiles,   ##### how do i get the files to read here

    VARCHAR Format         = 'Delimited',

    VARCHAR OpenMode       = 'Read',

    VARCHAR TextDelimiter  = '|'

  );

Can I also assume that I can kickoff the TPT script using a system command in the perl script?

Teradata Employee

Re: Teradata TPT Script

For the FileName attribute, you either place the name of the file, or if there is more than one, you can use wildcard syntax.

Yes, from the Perl script, you just execute the "tbuild" command.

-- SteveF
Enthusiast

Re: Teradata TPT Script

Thanks.  Can I also ask, what does the '@jobvar' represent.  Is it just a placeholder for me to put in my dbname, username and password.  Or is it input into the script somehow?  Not sure what these mean?  Can you tell me what that error list means as well?

    VARCHAR TdpId          = @jobvar_tdpid,

    VARCHAR UserName       = @jobvar_username,

    VARCHAR UserPassword   = @jobvar_password,

    VARCHAR ErrorList      = '3807'

Teradata Employee

Re: Teradata TPT Script

The "@" syntax is used when you want to specify job variables.

You can hardcode a value fot eh attribute (not recommended).

Or you use the "@" syntax, provide a job variable name (of your choosing), and then create a job variable file with the values you want to pass down to the script.

Please look at the TPT documentation for all of the info.

-- SteveF
Highlighted
Enthusiast

Re: Teradata TPT Script

I've done the following:

1) Created the empty table in Teradata

2) Created the TPT script with the corresponding table schema.  However the 1st column in my table has the name '#TIMESTAMP'.  The tbuild is complaining about that.  Can I get around that?

3) Updated the '/opt/teradata/client/14.00/tbuild/twbcfg.ini' file to include a 'GlobalAttributeFile' setting.

4) Created the GlobalAttributeFile to include tdpid, username, password and tablename. 

5) I created another table with the same layout as the original, with _LOG at the end of the name.  Is this required?  I'm not sure what the layout is supposed to be for the LogTable?  

Is there an example TPT script which loads to an empty table, then moves that info to a live table.  I know I can move the data in another way, but was just curious.  Thanks again for all the help.

Teradata Employee

Re: Teradata TPT Script

Any database object name with special characters must be enclosed in double-quotes.

That is a DBS rule.

I am not sure what you mean by #5. The user does not worry about the restart log table. You give us a name in the LogTable attribute and we will create it and manage it.

-- SteveF
Enthusiast

Re: Teradata TPT Script

Almost got it.  I ran the script but accidently had an extra column in the test file I was inserting.  So after fixing that, dropping and creating the table, I am getting this error on the run of the script.  I have the deletes for the ErrorTable's in the script so not sure why they still exist here.  After I manually deleted those ErrorTable's the script does run successfully.

=> tbuild -f LoadTable_TPT.txt

Teradata Parallel Transporter Version 14.00.00.09

Job log: /opt/app/d1car1m6/opt/cap-monitoring/logs/d1car1m6-9.out

Job id is d1car1m6-9, running on hldv0153

Found CheckPoint file: /opt/teradata/client/14.00/tbuild/checkpoint/d1car1m6LVCP

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

Teradata Parallel Transporter FILE_READER: TPT19006 Version 14.00.00.09

Teradata Parallel Transporter Load Operator Version 14.00.00.09

FILE_READER Instance 2 directing private log report to 'TPTLoad_Reader_log-2'.

FILE_READER Instance 1 directing private log report to 'TPTLoad_Reader_log-1'.

LOAD_OPERATOR: private log specified: TPTLoad_Load_log

FILE_READER Instance 2 restarting.

FILE_READER Instance 1 restarting.

FILE_READER: TPT19008 DataConnector Producer operator Instances: 2

FILE_READER: TPT19003 ECI operator ID: FILE_READER-12671

LOAD_OPERATOR: connecting sessions

LOAD_OPERATOR: preparing target table

LOAD_OPERATOR: TPT10508: RDBMS error 2634: Existing ERROR table(s) or Incorrect use of Rum_Load_TPT in  Fast Load operation.

LOAD_OPERATOR: disconnecting sessions

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

LOAD_OPERATOR: Start : Mon May  5 16:54:54 2014

LOAD_OPERATOR: End   : Mon May  5 16:55:04 2014

FILE_READER: TPT19221 Total files processed: 0.

Job step Load_Trans_Table terminated (status 12)

Job d1car1m6 terminated (status 12)

So what happens if it is not successful.  Do I have to manually remove those tables?  Can I also assume that since this ran successfully, the next run will work without me having to drop and create the table, as this script will do that now?

Teradata Employee

Re: Teradata TPT Script

When you start a new job, the error tables cannot exist.

Neither can the restart log table.

On a restart, the error and restart log tables must exist.

If a job fails and it failed for a reason that could be "fixed" and restarted, you need to keep all of the tables intact so the job can be restarted.

When a job is successful, TPT will drop the error tables (if they are empty) and the log table.

(This is all outlined in the docs.)

-- SteveF
Enthusiast

Re: Teradata TPT Script

The last issue I'm having is an error from an sql to the Table after the TPT script load succeeded.  I have 2 tables currently being loaded.  One is with a DBD::ODBC module, which is very slow and the 2nd using TPT.  The table layout is identical, with the same information being loaded to both.  However when I issue the following sql I'm getting a good response from the 1st table and this error on the new TPT table:

Sql => select distinct(#timestamp (date)) ,count(*) from Thirty_Day_Tables.RUM_Load group by 1;

2014-05-06, 6481

Sql => select distinct(#timestamp (date)) ,count(*) from One_Day_Tables.Rum_Load_TPT group by 1;

DBD::ODBC::st execute failed: [Teradata][ODBC Teradata Driver][Teradata Database] Invalid date supplied for Rum_Load_TPT.#TIMESTAMP.  (SQL-22008) at ./Runedwsql.pl line 94.

DBD::ODBC::st fetchrow_array warning: no select statement currently executing at ./Runedwsql.pl line 97.

Yet when I print out the #timestamp column on each table, they look the same (I only am posting 5 examples):

Sql => select #timestamp from Thirty_Day_Tables.RUM_Load;

2014-05-05 11:08:29

2014-05-05 17:46:05

2014-05-05 15:12:01

2014-05-05 13:02:25

2014-05-05 18:01:22

Sql => select #timestamp from One_Day_Tables.Rum_Load_TPT;

2014-05-05 17:46:05

2014-05-05 18:01:22

2014-05-05 17:25:19

2014-05-05 17:58:22

2014-05-05 18:29:36