Teradata Studio's Copy Object Wizard allows users to copy objects by simply dragging the object from the Data Source Explorer tree and dropping it into the destination node in the Transfer View tree. Often users have created objects in their test database and wish to copy it to production, or copy objects from production into their test data lab environment. Database objects can be copied within the same database server or to a different database server. You can copy more than one object at a time by selecting multiple objects from the Data Source Explorer and dragging them to the Transfer View tree. Due to the external code location, External Stored Procedures and User Defined Functions can not be copied.
The Copy Object Wizard also allows you to filter out columns or column data when copying tables. You can also copy table data to an existing table, providing a map of source columns to destination columns.
To begin copying objects, start Teradata Studio and create connection profiles to your source and destination database systems.
There are two ways to invoke the Copy Object Wizard, drag and drop from the Data Source Explorer to the Transfer View or via the Data Transfer Wizard for copying table objects.
The Copy Object Wizard can be invoked by dragging a Teradata object from the Data Source Explorer and dropping it on the destination Teradata database in the Transfer View.
The Copy Object Wizard will display a confirmation screen showing the Source and Destination systems. If the name is not valid for the destination, an error message is displayed and you can edit the name field.
Click Next to view the generated SQL statement that will be executed to copy the database object.
Click Finish to execute the generated SQL statement. If mulitple objects are dragged to the destination, an intermediary dialog is displayed as the objects are copied one at a time.
The other way to invoke the Copy Object Wizard is via the Data Transfer Wizard. For example, you can select a Teradata table in the Data Source Explorer, right click and choose Data>Export.... In the Data Transfer Wizard, choose the Destination Type as Teradata and click Launch. This will launch the Copy Object Wizard to copy the table object. NOTE: You can also select the Data>Load... option and choose the Source Type as Teradata to launch the Copy Opject Wizard and copy another Teradata table's data into the selected table.
Next, select the connection profile and destination database for where you wish to copy the table to. You can copy the table as a 'New Table', or copy the table data to an existing Teradata table. If you wish to create a new table, check the 'Create New Table' option, else select the destination table you want to copy the data to.
The Copy Object Wizard will display a confirmation screen showing the Source and Destination systems. If the name is not valid for the destination, an error message is displayed. Click Next to filter columns and column data.
If choosing the 'New Table' option, you can filter columns and column data.
Click Next to view the SQL that is generated for the new table and copying its data.
Or, if you chose to copy the table to an existing Teradata table, you will be required to map the source columns to the columns of the destination table. You can also choose whether to append or replace the data. If you choose to replace the data, the current data will be deleted. You will be prompted to confirm that you want the Copy Object Wizard to delete the table data.
Click Next to view the SQL that is generated for selecting the data for the data copy.
Click Finish to complete the Copy. If you are copying table data, a Data Transfer job is created to perform the data copy. As with data load, the status of the Data Transfer job is displayed in the Transfer Progress View. When the job has completed, an entry is placed in the Transfer History View.
The Transfer History View displays the information about the copy data transfer, such as the name (given by the Copy wizards), timestamp, source and destination systems, status, duration, rows transfered (if known), note, and summary. This information is stored in an embedded Derby database. You can sort and filter the rows and columns, edit the name or note. The data transfer job can be re-executed by selecting the entry and pressing the Re-execute toolbar button. The output of the transfer data job is displayed by pressing the Job Output button.
How to compare objects?
Hi this describes how to copy objects. If I only want to compare 2 objects (the DDLs actually) how do I do that?
The "Compare Object Definitions" button is greyed out.
The name of the function is "Copy/Compare", but I do not understand how to do a compare?
The Compare is similar to the copy in that you open the Copy/Compare View, choose a connected profile, locate the object you want to compare to, then drag the compare object from the DSE and drop it on the compare to object. The Compare Objects button should be enabled on the toolbar when you bring up Studio. Try to 'Reset Perspective' to see if that helps. You can reset the perspective from the Window menu option. If it still does not appear, open the view by going to the Window>Show View. Teradata Compare Objects view should be in the list. If it is not, check the Others... menu option and look in the Teradata category.
'Copy object' option is really a very good addon. Could you please let us know whether copying the big tables ( >10 GB) between the TD servers would be fine using this option? I would like to understand whether it is recommended to use this feature irrespective of the table size?
Copy object can be used for large tables. It uses the JDBC FastExport and FastLoad options for copying the data. The problem comes when the column types are not supported by FastLoad, for example LOBs.
I tried to copy a table using 'Copy/compare Objects' option and it is failed with the error code 6706. The error message corresponding to the error code 6706 is 'The string contains the untranslatable character'.
I checked the faulty column values and it had the below values as part of the string
* ( ) .
Are the above chars not supported while doing JDBC fastload? or am i missing something?
I am not sure how your columns are defined, but yes some column types are not supported by JDBC FastLoad. The 6706 Untranslatable character error is from inserting data using the wrong character set. I am wondering if the data being copied contains characters not supported by the destination system charset. What is the charset for the source database compared to the destination?
The character set is LATIN in both the source/tgt database and the columns are defined with ' VARCHAR(100) CHARACTERSET LATIN NOT CASESPECIFIC'.
Do we need to configure any settings to make the data copy process faster. I have tried with 1 GB table and it takes 30 minutes.
I experiment some difficulties to copy a table from PROD system to DEV system (1 GO Table):
- COPY is beginning and stopped due to USI and NUSI on source table:
"Cannot load table TKL_CAIS_ARI unless secondary indexes and join indexes are removed."
but how to alter DDL of target table ?
On "drag & drop" on target database (creating target table): validation of DDL does not authorize editing DDL,
On "drag & drop" on a target table (Table_2 with no USI/NUSI) i just can "Compare objects definition" ...
Thanks for help,