APPLY STATEMENT SCHEMA ISSUE. Using TPT to copy table from one server to another no landing to disk.

Tools
Fan

APPLY STATEMENT SCHEMA ISSUE. Using TPT to copy table from one server to another no landing to disk.

After reading an excellent article posted here by TonyL called "Use Teradata Parallel Transporter to Move Data Without Landing the Data to Disk", I wrote the code below to use that allows any table to be moved from one server to another.

The table name is supplied in the command line as a job variable. I love that I can get the SCHEMA from the source table without listing out and hard coding the column names, but when it comes time for the target table SCHEMA in the "APPLY" statement, I have to hard code all the columns of the destination table ('INSERT INTO DatabaseName.' || @strTableName || '(:BASE_TIME_TYPE,:BASE_TIME,:TIME_RU,:RU_TYPE,:YR_PST,:inv_max_timeframe);').

This seems crazy that I have to do this.

Does anyone have any suggestions on how I can have get around this and just be able to insert all the columns from the source without specifying them in the code?

Thank You

James

DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK
DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'
(
DEFINE SCHEMA MY_SCHEMA FROM TABLE '''DatabaseName.' || @strTableName || '''';

DEFINE OPERATOR EXPORT_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA MY_SCHEMA
ATTRIBUTES
(
VARCHAR PRIVATELOGNAME = 'FILE_WRITER_PRIVATELOG',
INTEGER MaxSessions = 8,
INTEGER MinSessions,
VARCHAR TDPID = 'ServerSource',
VARCHAR USERNAME = 'Username',
VARCHAR USERPASSWORD = '$tdwallet(password_stored)',
VARCHAR SelectStmt = 'SELECT * FROM DatabaseName.' || @strTableName || ';'
);

DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA MY_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'loadoper_privatelog',
INTEGER MaxSessions = 16,
INTEGER MinSessions,
VARCHAR TargetTable = 'DatabaseName.' || @strTableName,
VARCHAR TDPID = 'TargetServer',
VARCHAR USERNAME = 'Username',
VARCHAR USERPASSWORD = '$tdwallet(password_stored)',
VARCHAR ErrorTable1 = 'DatabaseName.LOADOPER_ERRTABLE1',
VARCHAR ErrorTable2 = 'DatabaseName.LOADOPER_ERRTABLE2',
VARCHAR LogTable = 'DatabaseName.LOADOPER_LOGTABLE'
);

APPLY
('INSERT INTO DatabaseName.' || @strTableName || '(:BASE_TIME_TYPE,:BASE_TIME,:TIME_RU,:RU_TYPE,:YR_PST,:inv_max_timeframe);')
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);
);

  • SCHEMA
  • TPT Export
  • TPT LOAD
6 REPLIES
Junior Contributor

Re: APPLY STATEMENT SCHEMA ISSUE. Using TPT to copy table from one server to another no landing to d

There's an '$INSERT' macro, see this answer from Steve Feinholz.

 

 

Fan

Re: APPLY STATEMENT SCHEMA ISSUE. Using TPT to copy table from one server to another no landing to d

Hey Dieter, I have been reading your posts for years now. It is an honor to have you reply to one of my posts! :) Thank You!

An as far as the article from Steve Feinholz, I have read it before and remember the part where he talks about the '$INSERT'  and I just didnt understand it at the time.

I tried it this morning and it works! Awesome. Thank you for the quick solution, I very much appreciate it.

 

On another note, I am looking to embed my new code far below into a shell script. I like doing this with "Fastload, Fastexport, Bteq, etc" because it is easier to manage one shell scripts instead of multiple files.

I do this by putting the terdata code between the <<EOF and EOF statements illustrate below and it works like a charm, but it doesn't seem to work with TPT. I remember reading an response from you to a similiar question (which stated it cant be done) but I just wanted to ask it again just in case something might have changed. Thank You

bteq <<EOF
.run file=/home/myfolder/.tdlogon

   ...Teradata code

EOF

 

--Move table from one server to another code

DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK
DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'
(
DEFINE SCHEMA MY_SCHEMA FROM TABLE '''DatabaseName.' || @strTableName || '''';

DEFINE OPERATOR EXPORT_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA MY_SCHEMA
ATTRIBUTES
(
VARCHAR PRIVATELOGNAME = 'FILE_WRITER_PRIVATELOG',
INTEGER MaxSessions = 8,
INTEGER MinSessions,
VARCHAR TDPID = 'ServerSource',
VARCHAR USERNAME = 'Username',
VARCHAR USERPASSWORD = '$tdwallet(password_stored)',
VARCHAR SelectStmt = 'SELECT * FROM DatabaseName.' || @strTableName || ';'
);

DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA MY_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'loadoper_privatelog',
INTEGER MaxSessions = 16,
INTEGER MinSessions,
VARCHAR TargetTable = 'DatabaseName.' || @strTableName,
VARCHAR TDPID = 'TargetServer',
VARCHAR USERNAME = 'Username',
VARCHAR USERPASSWORD = '$tdwallet(password_stored)',
VARCHAR ErrorTable1 = 'DatabaseName.LOADOPER_ERRTABLE1',
VARCHAR ErrorTable2 = 'DatabaseName.LOADOPER_ERRTABLE2',
VARCHAR LogTable = 'DatabaseName.LOADOPER_LOGTABLE'
);
APPLY $INSERT
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);
);

Junior Contributor

Re: APPLY STATEMENT SCHEMA ISSUE. Using TPT to copy table from one server to another no landing to d

Afaik there's no interactive mode for TPT, but you might be able to simplify it to a single additional file

- either a generic template file plus passing the variables on command line using tbuild -u

- or a temporary file created within the shell script

 

Fan

Re: APPLY STATEMENT SCHEMA ISSUE. Using TPT to copy table from one server to another no landing to d

Too bad. TPT in interactive mode would be an awesome addition.

I will definately just use the code as a generic template and call it from the script file.

 

Thank you for all your help!

Teradata Employee

Re: APPLY STATEMENT SCHEMA ISSUE. Using TPT to copy table from one server to another no landing to d

I know that I need to re-organize/re-write the TPT documentation a bit, but one thing we are trying to push onto the users is the use of "templates".

And then all of the metadata is controlled through the use of job variables.

No hardcoding of attributes and values in scripts.

This eliminates the need to code the DEFINE OPERATOR statements in the script.

If you want to write a general purpose script to move a table from one database to another, there are a few options:

 

1. use "tdload" (command line interface to the "tbuild" command; no script; also known as Easy Loader)

2. use "tbuild" with a simple general purpose script and a job variable file

 

A general purpose script using "templates" would look like this:

 

DEFINE JOB <some name>

(

   APPLY $INSERT TO OPERATOR ($LOAD)

   SELECT * FROM OPERATOR ($EXPORT);

);

 

and then you have a job variable file with all of the metadata.

The job variable names to be used in the job variable file when using templates can be found in the templates themselves (we do not put them in our documentation).

The templates can be found in the "templates" directory where TPT is installed (usually somethine like /opt/teradata/client/15.10/tbuild/. . . .).

This location assumes Linux/Unix and a TTU version of 15.10. Adjust the location based on platform and TTU version.

-- SteveF
Fan

Re: APPLY STATEMENT SCHEMA ISSUE. Using TPT to copy table from one server to another no landing to d

Many thanks Steve! I have read many of your posts and always find them valuable. And I agree 100% to not hard code anything and use templates instead. I am going to refine the code to have a job variable file.

Thanks again!