

Introduction to Teradata Data Mover: Create your first job
Teradata Data Mover (TDM) is a relatively new product that allows users to copy database objects, such as tables and statistics, from one Teradata Database system to another. TDM can copy join/hash indexes, journals, and triggers as well.
TDM is an enterprise application that consists of the three main components: the command-line interface, the Daemon, and one or more Agents. Please see the architecture diagram below.
TDM Architecture
TDM's command-line interface allows users to define their intent for copying objects between two Teradata systems via a simple XML file. Users only need to provide the logon credentials for the source and target systems and the objects they want to copy as input. TDM will take care of the rest by automatically generating a job plan to execute the data movement based on the current state of the source and target systems. TDM accomplishes this by utilizing ARC, TPTAPI, and JDBC under the covers to perform the object copy. This abstracts the complexity of the underlying methods to perform the data movement, which means users no longer have to generate low-level utility scripts to copy objects between Teradata systems.
Create your first TDM job
Now that we've gone through the overview of the product, let's step through the process of executing your first TDM job. First, let's create a table on our source Teradata system. In this example, the source Teradata system will be dmdev
and the target Teradata system will be dmsmp
. The source table will get created under the jg185041
user and we will do a full table copy of it under the jg185041
user on the target system. Please change these values to accommodate your environment when executing the test.
This is the SQL file needed to create the table we will copy:
CREATE SET TABLE items1 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( deptId INTEGER, prodId INTEGER, salesdate DATE FORMAT 'YYYY-MM-DD', amount DECIMAL(10,2)) PRIMARY INDEX ( prodId ) PARTITION BY RANGE_N(salesdate BETWEEN DATE '2001-01-01' AND DATE '2010-12-31' EACH INTERVAL '1' MONTH ); COMMIT;
Click 'expand source' below to see the contents of SQL file to insert data into the table that we will be copying:
insert into jg185041.items1 values ('1', '1','2005-12-29', '2.54'); insert into jg185041.items1 values ('2', '1','2008-08-09', '2.54'); insert into jg185041.items1 values ('3', '1','2002-12-12', '2.54'); insert into jg185041.items1 values ('4', '1','2008-08-11', '2.54'); insert into jg185041.items1 values ('5', '1','2007-11-18', '2.54'); insert into jg185041.items1 values ('6', '1','2010-08-18', '2.54'); insert into jg185041.items1 values ('7', '1','2002-09-01', '2.54'); insert into jg185041.items1 values ('8', '1','2006-02-26', '2.54'); insert into jg185041.items1 values ('9', '1','2004-09-13', '2.54'); insert into jg185041.items1 values ('10', '1','2004-11-02', '2.54'); insert into jg185041.items1 values ('1', '2','2002-05-26', '82.61'); insert into jg185041.items1 values ('2', '2','2001-03-29', '82.61'); insert into jg185041.items1 values ('3', '2','2010-07-11', '82.61'); insert into jg185041.items1 values ('4', '2','2002-02-22', '82.61'); insert into jg185041.items1 values ('5', '2','2006-01-21', '82.61'); insert into jg185041.items1 values ('6', '2','2008-10-09', '82.61'); insert into jg185041.items1 values ('7', '2','2009-11-25', '82.61'); insert into jg185041.items1 values ('8', '2','2010-12-19', '82.61'); insert into jg185041.items1 values ('9', '2','2006-10-05', '82.61'); insert into jg185041.items1 values ('10', '2','2006-09-28', '82.61'); insert into jg185041.items1 values ('1', '3','2009-07-28', '21.9'); insert into jg185041.items1 values ('2', '3','2005-07-05', '21.9'); insert into jg185041.items1 values ('3', '3','2009-01-20', '21.9'); insert into jg185041.items1 values ('4', '3','2008-02-29', '21.9'); insert into jg185041.items1 values ('5', '3','2001-01-14', '21.9'); insert into jg185041.items1 values ('6', '3','2007-06-28', '21.9'); insert into jg185041.items1 values ('7', '3','2008-11-27', '21.9'); insert into jg185041.items1 values ('8', '3','2004-10-03', '21.9'); insert into jg185041.items1 values ('9', '3','2008-11-08', '21.9'); insert into jg185041.items1 values ('10', '3','2007-01-21', '21.9'); insert into jg185041.items1 values ('1', '4','2004-05-12', '50.76'); insert into jg185041.items1 values ('2', '4','2008-12-16', '50.76'); insert into jg185041.items1 values ('3', '4','2003-07-21', '50.76'); insert into jg185041.items1 values ('4', '4','2003-02-13', '50.76'); insert into jg185041.items1 values ('5', '4','2006-12-04', '50.76'); insert into jg185041.items1 values ('6', '4','2003-11-18', '50.76'); insert into jg185041.items1 values ('7', '4','2010-06-09', '50.76'); insert into jg185041.items1 values ('8', '4','2001-03-02', '50.76'); insert into jg185041.items1 values ('9', '4','2004-09-28', '50.76'); insert into jg185041.items1 values ('10', '4','2001-08-20', '50.76'); insert into jg185041.items1 values ('1', '5','2008-02-21', '0.64'); insert into jg185041.items1 values ('2', '5','2001-08-25', '0.64'); insert into jg185041.items1 values ('3', '5','2004-12-10', '0.64'); insert into jg185041.items1 values ('4', '5','2005-08-30', '0.64'); insert into jg185041.items1 values ('5', '5','2007-09-11', '0.64'); insert into jg185041.items1 values ('6', '5','2001-07-11', '0.64'); insert into jg185041.items1 values ('7', '5','2008-08-27', '0.64'); insert into jg185041.items1 values ('8', '5','2008-03-02', '0.64'); insert into jg185041.items1 values ('9', '5','2002-05-17', '0.64'); insert into jg185041.items1 values ('10', '5','2004-02-09', '0.64'); insert into jg185041.items1 values ('1', '6','2001-04-14', '25.08'); insert into jg185041.items1 values ('2', '6','2001-11-12', '25.08'); insert into jg185041.items1 values ('3', '6','2002-02-27', '25.08'); insert into jg185041.items1 values ('4', '6','2008-07-07', '25.08'); insert into jg185041.items1 values ('5', '6','2002-02-23', '25.08'); insert into jg185041.items1 values ('6', '6','2002-09-23', '25.08'); insert into jg185041.items1 values ('7', '6','2005-07-05', '25.08'); insert into jg185041.items1 values ('8', '6','2008-08-05', '25.08'); insert into jg185041.items1 values ('9', '6','2003-01-06', '25.08'); insert into jg185041.items1 values ('10', '6','2009-02-18', '25.08'); insert into jg185041.items1 values ('1', '7','2008-06-27', '25.99'); insert into jg185041.items1 values ('2', '7','2007-01-05', '25.99'); insert into jg185041.items1 values ('3', '7','2001-06-26', '25.99'); insert into jg185041.items1 values ('4', '7','2009-01-24', '25.99'); insert into jg185041.items1 values ('5', '7','2001-09-05', '25.99'); insert into jg185041.items1 values ('6', '7','2005-07-26', '25.99'); insert into jg185041.items1 values ('7', '7','2006-08-25', '25.99'); insert into jg185041.items1 values ('8', '7','2010-12-30', '25.99'); insert into jg185041.items1 values ('9', '7','2003-10-15', '25.99'); insert into jg185041.items1 values ('10', '7','2007-08-17', '25.99'); insert into jg185041.items1 values ('1', '8','2006-12-05', '85.96'); insert into jg185041.items1 values ('2', '8','2002-06-17', '85.96'); insert into jg185041.items1 values ('3', '8','2005-08-23', '85.96'); insert into jg185041.items1 values ('4', '8','2008-08-22', '85.96'); insert into jg185041.items1 values ('5', '8','2004-02-24', '85.96'); insert into jg185041.items1 values ('6', '8','2006-11-10', '85.96'); insert into jg185041.items1 values ('7', '8','2008-12-06', '85.96'); insert into jg185041.items1 values ('8', '8','2006-10-16', '85.96'); insert into jg185041.items1 values ('9', '8','2004-09-20', '85.96'); insert into jg185041.items1 values ('10', '8','2008-06-04', '85.96'); insert into jg185041.items1 values ('1', '9','2003-03-20', '23.55'); insert into jg185041.items1 values ('2', '9','2008-02-06', '23.55'); insert into jg185041.items1 values ('3', '9','2010-04-03', '23.55'); insert into jg185041.items1 values ('4', '9','2006-09-27', '23.55'); insert into jg185041.items1 values ('5', '9','2009-10-08', '23.55'); insert into jg185041.items1 values ('6', '9','2009-10-08', '23.55'); insert into jg185041.items1 values ('7', '9','2009-06-09', '23.55'); insert into jg185041.items1 values ('8', '9','2007-08-05', '23.55'); insert into jg185041.items1 values ('9', '9','2004-12-20', '23.55'); insert into jg185041.items1 values ('10', '9','2004-09-09', '23.55'); insert into jg185041.items1 values ('1', '10','2010-12-13', '20.78'); insert into jg185041.items1 values ('2', '10','2004-05-28', '20.78'); insert into jg185041.items1 values ('3', '10','2004-10-31', '20.78'); insert into jg185041.items1 values ('4', '10','2006-03-11', '20.78'); insert into jg185041.items1 values ('5', '10','2008-01-15', '20.78'); insert into jg185041.items1 values ('6', '10','2009-02-24', '20.78'); insert into jg185041.items1 values ('7', '10','2002-07-21', '20.78'); insert into jg185041.items1 values ('8', '10','2006-09-14', '20.78'); insert into jg185041.items1 values ('9', '10','2007-09-25', '20.78'); insert into jg185041.items1 values ('10', '10','2005-12-22', '20.78'); insert into jg185041.items1 values ('1', '11','2010-07-11', '26.36'); insert into jg185041.items1 values ('2', '11','2003-08-02', '26.36'); insert into jg185041.items1 values ('3', '11','2007-09-18', '26.36'); insert into jg185041.items1 values ('4', '11','2008-04-24', '26.36'); insert into jg185041.items1 values ('5', '11','2008-12-23', '26.36'); insert into jg185041.items1 values ('6', '11','2002-09-05', '26.36'); insert into jg185041.items1 values ('7', '11','2010-07-24', '26.36'); insert into jg185041.items1 values ('8', '11','2005-08-07', '26.36'); insert into jg185041.items1 values ('9', '11','2009-12-07', '26.36'); insert into jg185041.items1 values ('10', '11','2003-12-03', '26.36'); insert into jg185041.items1 values ('1', '12','2007-07-02', '37.52'); insert into jg185041.items1 values ('2', '12','2007-04-15', '37.52'); insert into jg185041.items1 values ('3', '12','2010-08-05', '37.52'); insert into jg185041.items1 values ('4', '12','2006-03-23', '37.52'); insert into jg185041.items1 values ('5', '12','2006-10-15', '37.52'); insert into jg185041.items1 values ('6', '12','2009-05-11', '37.52'); insert into jg185041.items1 values ('7', '12','2002-01-16', '37.52'); insert into jg185041.items1 values ('8', '12','2005-03-06', '37.52'); insert into jg185041.items1 values ('9', '12','2005-06-23', '37.52'); insert into jg185041.items1 values ('10', '12','2001-05-03', '37.52'); insert into jg185041.items1 values ('1', '13','2007-11-07', '41.99'); insert into jg185041.items1 values ('2', '13','2008-05-17', '41.99'); insert into jg185041.items1 values ('3', '13','2010-05-12', '41.99'); insert into jg185041.items1 values ('4', '13','2001-06-27', '41.99'); insert into jg185041.items1 values ('5', '13','2009-11-28', '41.99'); insert into jg185041.items1 values ('6', '13','2005-06-14', '41.99'); insert into jg185041.items1 values ('7', '13','2001-06-16', '41.99'); insert into jg185041.items1 values ('8', '13','2004-04-26', '41.99'); insert into jg185041.items1 values ('9', '13','2009-10-09', '41.99'); insert into jg185041.items1 values ('10', '13','2005-12-09', '41.99'); insert into jg185041.items1 values ('1', '14','2006-01-06', '2.85'); insert into jg185041.items1 values ('2', '14','2010-05-12', '2.85'); insert into jg185041.items1 values ('3', '14','2003-10-21', '2.85'); insert into jg185041.items1 values ('4', '14','2006-09-15', '2.85'); insert into jg185041.items1 values ('5', '14','2005-10-28', '2.85'); insert into jg185041.items1 values ('6', '14','2003-04-23', '2.85'); insert into jg185041.items1 values ('7', '14','2007-05-30', '2.85'); insert into jg185041.items1 values ('8', '14','2005-01-23', '2.85'); insert into jg185041.items1 values ('9', '14','2007-04-19', '2.85'); insert into jg185041.items1 values ('10', '14','2006-12-13', '2.85'); insert into jg185041.items1 values ('1', '15','2010-04-09', '34.35'); insert into jg185041.items1 values ('2', '15','2008-09-01', '34.35'); insert into jg185041.items1 values ('3', '15','2006-07-28', '34.35'); insert into jg185041.items1 values ('4', '15','2010-09-29', '34.35'); insert into jg185041.items1 values ('5', '15','2001-05-03', '34.35'); insert into jg185041.items1 values ('6', '15','2003-09-02', '34.35'); insert into jg185041.items1 values ('7', '15','2005-07-08', '34.35'); insert into jg185041.items1 values ('8', '15','2007-04-18', '34.35'); insert into jg185041.items1 values ('9', '15','2009-03-03', '34.35'); insert into jg185041.items1 values ('10', '15','2010-09-20', '34.35'); insert into jg185041.items1 values ('1', '16','2008-12-25', '67.5'); insert into jg185041.items1 values ('2', '16','2003-03-11', '67.5'); insert into jg185041.items1 values ('3', '16','2008-12-02', '67.5'); insert into jg185041.items1 values ('4', '16','2008-03-09', '67.5'); insert into jg185041.items1 values ('5', '16','2001-02-01', '67.5'); insert into jg185041.items1 values ('6', '16','2006-04-14', '67.5'); insert into jg185041.items1 values ('7', '16','2007-04-12', '67.5'); insert into jg185041.items1 values ('8', '16','2007-10-14', '67.5'); insert into jg185041.items1 values ('9', '16','2008-12-28', '67.5'); insert into jg185041.items1 values ('10', '16','2005-10-11', '67.5'); insert into jg185041.items1 values ('1', '17','2008-03-07', '55.59'); insert into jg185041.items1 values ('2', '17','2006-07-02', '55.59'); insert into jg185041.items1 values ('3', '17','2003-10-21', '55.59'); insert into jg185041.items1 values ('4', '17','2005-03-31', '55.59'); insert into jg185041.items1 values ('5', '17','2010-01-06', '55.59'); insert into jg185041.items1 values ('6', '17','2003-05-31', '55.59'); insert into jg185041.items1 values ('7', '17','2003-04-24', '55.59'); insert into jg185041.items1 values ('8', '17','2002-07-24', '55.59'); insert into jg185041.items1 values ('9', '17','2007-09-04', '55.59'); insert into jg185041.items1 values ('10', '17','2007-05-14', '55.59'); insert into jg185041.items1 values ('1', '18','2010-10-02', '93.73'); insert into jg185041.items1 values ('2', '18','2006-02-03', '93.73'); insert into jg185041.items1 values ('3', '18','2005-05-24', '93.73'); insert into jg185041.items1 values ('4', '18','2007-10-15', '93.73'); insert into jg185041.items1 values ('5', '18','2007-07-13', '93.73'); insert into jg185041.items1 values ('6', '18','2009-11-11', '93.73'); insert into jg185041.items1 values ('7', '18','2003-11-22', '93.73'); insert into jg185041.items1 values ('8', '18','2008-11-21', '93.73'); insert into jg185041.items1 values ('9', '18','2001-03-20', '93.73'); insert into jg185041.items1 values ('10', '18','2004-12-02', '93.73'); insert into jg185041.items1 values ('1', '19','2009-05-23', '18.96'); insert into jg185041.items1 values ('2', '19','2009-11-29', '18.96'); insert into jg185041.items1 values ('3', '19','2004-12-31', '18.96'); insert into jg185041.items1 values ('4', '19','2003-10-31', '18.96'); insert into jg185041.items1 values ('5', '19','2002-12-19', '18.96'); insert into jg185041.items1 values ('6', '19','2001-12-06', '18.96'); insert into jg185041.items1 values ('7', '19','2007-12-03', '18.96'); insert into jg185041.items1 values ('8', '19','2009-08-07', '18.96'); insert into jg185041.items1 values ('9', '19','2006-04-20', '18.96'); insert into jg185041.items1 values ('10', '19','2008-01-05', '18.96'); insert into jg185041.items1 values ('1', '20','2008-12-07', '67.41'); insert into jg185041.items1 values ('2', '20','2006-12-07', '67.41'); insert into jg185041.items1 values ('3', '20','2010-09-19', '67.41'); insert into jg185041.items1 values ('4', '20','2002-06-07', '67.41'); insert into jg185041.items1 values ('5', '20','2003-05-17', '67.41'); insert into jg185041.items1 values ('6', '20','2007-12-25', '67.41'); insert into jg185041.items1 values ('7', '20','2010-10-30', '67.41'); insert into jg185041.items1 values ('8', '20','2005-04-01', '67.41'); insert into jg185041.items1 values ('9', '20','2003-10-30', '67.41'); insert into jg185041.items1 values ('10', '20','2007-06-27', '67.41'); COMMIT;
Please refer to The Friday Night Project #5 - TZA-Database article on how to use Ant and Eclipse to execute both SQL files so that our table is created properly in the dmdev
database.
Create XML
The next step in creating our first TDM job is to generate the XML file that specifies the intent of the job. We want to copy the jg185041.items1
table from dmdev
to dmsmp
, so here is the XML to do so:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <dmCreate xmlns="http://schemas.teradata.com/dataMover/v2009" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.teradata.com/dataMover/v2009/DataMover.xsd"> <job_name>jg_ppi_devX</job_name> <source_tdpid>dmdev</source_tdpid> <source_user>jg185041</source_user> <source_password>jg185041</source_password> <target_tdpid>dmsmp</target_tdpid> <target_user>jg185041</target_user> <target_password>jg185041</target_password> <overwrite_existing_objects>true</overwrite_existing_objects> <log_level>1</log_level> <online_archive>false</online_archive> <database selection="unselected"> <name>jg185041</name> <table selection="included"> <name>items1</name> </table> </database> </dmCreate>
Execute TDM job
Now that we have the XML file, we can execute the job with TDM by using the move
command. The move
command creates the job in TDM's repository and starts it. If all 3 components of TDM (command-line, Daemon, and Agent) have been installed properly, you can execute the move
command with the following:
datamove move -f <XML_file>
This is the output of the move command for our job:
dm-daemon3:/home/jg185041/DataMover/testing/ppi # datamove move -f jg_ppi_devX.xml Data Mover Command Line 13.01.00.04 Move mode Command parameters: - job_name: jg_ppi_devX - source_tdpid: dmdev - source_user: jg185041 - source_password: ******** - source_logon_mechanism: - source_logon_mechanism_data: - source_account_id: - target_tdpid: dmsmp - target_user: jg185041 - target_password: ******** - target_logon_mechanism: - target_logon_mechanism_data: - target_account_id: - overwrite_existing_objects: true - online_archive: false - log_level: 1 - log_to_tdi: Starting move command... Creating job definition... Connected to Daemon version 13.01.00.04 Creating job in Daemon... Job created. Job name is: jg_ppi_devX Requesting job start... Job started. Job Execution Name is: jg_ppi_devX-20101008140355EDT
This command successfully created the job in the Daemon's repository and started its execution.
Job status
If we want to see how the job executed we can use TDM's status
command to look at the results. Use the following to execute the status
command:
datamove status -job_name <name_of_job> -output_level 3
This is the output of the status
command:
dm-daemon3:/home/jg185041/DataMover/testing/ppi # datamove status -job_name jg_ppi_devX -output_level 3 Data Mover Command Line 13.01.00.04 Status mode Command parameters: - job_name: jg_ppi_devX - output_level: 3 Requesting status... Connected to Daemon version 13.01.00.04 Job Name: jg_ppi_devX Job Execution Name: jg_ppi_devX-20101008140355EDT TYPE ID STATUS CURRENT STEP START TIME DURATION TIME ------------------------------------------------------------------------------------------------------------------ Job: 1 COMPLETED_SUCCESSFULLY 4 10/8/10 2:03 PM 0:0:12 TYPE ID STATUS STEP TYPE START TIME DURATION TIME ---------------------------------------------------------------------------------------------------------------------------------- Step: 3 COMPLETED_SUCCESSFULLY MOVE_TABLE_DATA 10/8/10 2:03 PM 0:0:10 Step: 4 COMPLETED_SUCCESSFULLY RESOLVE_TABLE_AFTER_LOAD 10/8/10 2:03 PM 0:0:1 TYPE ID NAME MOVE PHASE STATUS TYPE ROWS BYTES AGENT UTILITY TIMESTAMP ------------------------------------------------------------------------------------------------------------------------------------------------------ Task: 5 items1 MOVING_DATA ARCHIVE table 200 9864 Agent1 ARC 10/8/10 2:04 PM Task: 5 items1 MOVING_DATA COPY table 200 9864 Agent1 ARC 10/8/10 2:04 PM Task: 5 items1 MOVING_DATA COMPLETE table 200 9864 Agent1 ARC 10/8/10 2:04 PM Task: 6 items1 POST_DATA_MOVE COMPLETE unknown 0 0 Agent1 SQL 10/8/10 2:04 PM
By looking at this output we can see that TDM used ARC to copy this table and that the job completed successfully in 12 seconds. This concludes our example of executing your first Data Mover job. You should now be able to see the jg185041.items1
table on your target system.
The next TDM article will cover how to execute partial table copies.
Is it a replace table if the object exists on the target system?
Dave
If ARC is used then the table is replaced on the target system.
If TPTAPI/JDBC is used, rows from the target table are deleted and the source rows are inserted into the target table. This is done by moving the source rows to a staging table and then doing an INSERT/SELECT or MERGE from the staging table.
There is a property called "overwrite_existing_objects" that is defined at the job level. If this property is set to false, then Data Mover will give you an error if object exists on the target.
There is another property called "compare_ddl". When set to true, DM gives an error if source and target table DDLs are different.
Jaison
hi Jason,
We recently opened production flood gates for TDM jobs. I can say bcz of the too many TDM jobs system is getting hampered in terms of the performance and resources. I am thinking to apply a throttle to delay and restrict the TDM jobs. But as this is first tiime on our box, i would like to know your thoughts on imposing the throttle rules on TDM jobs.
Please share your thoughts.
Thanks,
Geeta.
Thanks,
Geeta