Recent comments
Teradata Studio's Copy Objects via Drag and Drop
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.
Start Copy
To begin copying objects, start Teradata Studio and create connection profiles to your source and destination database systems.
.jpg)
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.
Copy via Drag and Drop
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.
Copy via Data Transfer Wizard
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.
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?
Bjorn S
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.
Hello Francine,
'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.
Hello,
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?
Regards
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.
Hello,
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,
Pierre
Hi,
continuing testing STUDIO COPY TABLE i got a successful copy from PROD to DEV system with a bypass:
1) create target table T1 on "DEV" (STUDIO DDL window, changing name T to T1 )
2) Drop/Create table T1 without USI and NUSI (TD ADMIN in this case)
3) Copy table (STUDIO COPY window)
Copy of 8870238 rows (586 MO) achieved in 32 minutes.
Connection speed of 1Gbits/s betwwen "PROD" and "DEV"
The question remains: how to get a copy with a new DDL within STUDIO ?
I'm just in an exploration stage ...
Pierre
The copy is intended as a direct copy of the table, including any indexes. Your steps look correct.
Hello there, does the copy function supports copying other vendor data sources via JDBC into Teradata space? Some sources our client has are on Oracle or MS SQL server... Thank you!
Jessie, Not at this time. The drag and drop copy is only for copying objects between Teradata systems. We have added drag and drop copy table support between Teradata and Hadoop in Teradata Studio 14.10.01 and currently working on support for Aster copy.
Thank you fgrimmer :) It'd be a nice-to-have feature, but you probably got tons of them on the list :-D
hello , is there a way we can limit no of export/fastload sessions when copying data between teradata systems?
Mack, We use the JDBC FASTEXPORT, FASTLOAD options, depending on how many rows being copied. According to the JDBC Driver User manual, you can set the number of sessions in the SESSIONS JDBC Parameter:
SESSIONS
Specifies the number of FastLoad or FastExport connections to be created, where 1 <= number of FastLoad or FastExport connections <= number of AMPs.The default value is 8 or the number of AMPs if the number of AMPs is less than 8.Note: It is generally recommended to use the default value; that is, omit the SESSIONS parameter and let the Teradata JDBC Driver create the appropriate number of FastLoad or FastExport connections.
well i have used these qizard couple of times and it turned out to be more frustrating. It doesnt really tell you why it failed. theres only an output window. Then it doesnt work with different column types like Byte. Then it trimed out spaces at the end within the data of a column.
Wouldnt recommend this for production use. maybe you can play with in your development system.
Anuj, We would like to fix the issues you are having. Can you open an incident with our support and include your error logs so we can see what is going wrong?
Anuj, What version of Teradata Studio are you running. There was a "trim" problem that was fixed in the latest version.
Thanks Francine for repsonding back. i am using version 15.0.0.2 and have incidents open for both issues. is there a place we can see logs ? apart from the job output window where theres not much to see. For example the job failed and in the output window this is is the only output Export Warning(s):
SQL WARNING from statement: State = HY000, Error Code = 1298, Warning = java.sql.SQLWarning: [Teradata JDBC Driver] [TeraJDBC 15.00.00.09] [Error 1298] [SQLState HY000] FastExport WITH NO SPOOL is being used for statement
Anuj, There are 2 log files. Both are in your workspace <USER>/workspace-studio. One is /.metadata/.log and the other is /.metadata/Teradata/teradata.log.
The warning message above is fairly common.
Francine, i have attached the logs to the inciden RECGDENNE . I was not able to make out much from it.
Hello. In the Data Transfer Wizard, for the "Source Type" "Teradata", I am only getting the option for "Destination Type" "External File (FastExport)". I don't have the destination type option for Teradata or Hadoop. How do I get these options to be able to transfer data directly to the database rather than having to transfer to a flat file?
Its really a good feature. Please let me know if there are any consequences in moving DBC from one system to another. As I wish exact replica of Users, Roles, Rights from one system to another system. Secondly is it possible to have some thing like all jobs done completely or not done at all?
Mubashar, The Copy will only copy the DDL for Databases and Users. It does not copy the Roles or Rights. You should use a product like Data Mover for that kind of operation. I am not sure what you are asking with your second comment? Please clarify.