TPT plain copy tables demo

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Senior Supporter

Teradata Parallel Transporter is a really nice tool which offers new capabilities in comparison to the existing "old" TD load utilities. One of many other advantages is the ability to move data without landing it to disk (see this article). There are also other post / blogs which give good introductions to TPT.

But as TPT needs a different scripting syntax it is sometimes not easy to get a start on this great tool. Therefore we developed and attached (zip download) a wrapper-like java tool which should help to get a first impression on the new capabilities without the need to care about TPT syntax.

The main functionality is to copy one table from one system to a second system in a Fastexport to pipe to Fastload style. The tool checks the table DDL, generates the TPT script and execute the needed scripts.

This is a demo version and distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

But beside this there are no constrains on the table sizes etc. Throughput will mainly depend on your network bandwidth from and to your TD systems.

The tool is working parameter driven - see documentation for more details. The central configuration is done via the XML logon files for both systems and a central XML parameter file. In the logon files you need to replace the following with your own settings:

<host>dbc_host.localdomain</host>
<user>dbc_user</user>
<pwd>dbc_passwd</pwd>

Host need to be specified as domain (if your network supports this) or plain IP address.

The minimum parameters which need to be specified are:

<tbuild_bin>/your tbuild path/tbuild</tbuild_bin>
<src_logon_file>logon_source_system.xml</src_logon_file>
<trg_logon_file>logon_target_system.xml</trg_logon_file>
<src_db>source_db</src_db>
<src_tbl>source_table</src_tbl>
<trg_db>target_db</trg_db>
<trg_tbl>target_table</trg_tbl>

In case you run this on Windows,

<tbuild_bin>tbuild</tbuild_bin>

should be sufficient if tbuild is part of your path.

Be aware that target table name can be different from source table name. The default settings will create the table on the second system, which will only work if the source table has no secondary indices defined. If SI exists on the source table just create the target table on your own without the SI and add/replace

<create_target_table>N</create_target_table>

to the parameter file.

With:

<flush_target_table>Y</flush_target_table> 

you can empty the target table prior to loading.

There are other parameters like <reader_jobs>, <writer_jobs> and <maxsessions_export> which you like to play with once you get it up and running to see the implications on throughput and run times.

<where_stmt> can be also of interest to coply only samples but need special syntax due to xml standards (please see documentation).

<newstart> can be set in case a job should be started from the beginning ignoring the restart information.

java -jar ./jar/copy_tables_plain.jar \ --param_file ./parameter/copy_tables_plain_params.xml

would be than a program call to copy the table specified in the parameter file from one system to another (assuming you are in the copy_tables_plain directory).

Due to the demo characteristic of this tool you are ask to confirm the start of the program with an y/n prompt.

The logs (two per call) can be found in the ./log subdirectory (or you specified a different one). The ctp_tpt_YYYYMMDD_HHMMSS.log contains the output of tbuild (e.g. ctp_tpt_20100730_113045.log). The ctp_YYYYMMDD_HHMMSS.log contains the output of copy_tables_plain itself (e.g. ctp_20100730_113045.log)

The program should work with TPT release 12 but was developed and tested with TPT release 13. It was tested on Linux and Windows systems but will only show full power if it runs with good network bandwidth to both systems. You need TPT and the JDBC driver installed on your system (see installations instructions in the documentation). You need to place the tdgssconfig.jar and terajdbc4.jar files or a link in the jar_extern subdir.

I would really like to encourage you to start evaluating TPT and hope that this little helper might give you a first step into this direction.