Copy View Data | Teradata Data Mover - 17.10 - About Copying View Data - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.10
Release Date
June 2021
Content Type
User Guide
Publication ID
B035-4101-061K
Language
English (United States)

Data Mover can copy view data when underlying data is being copied from a view to a view between Teradata systems and Teradata tables. When copying data from a view, specify the copyData="true" attribute along with the view element.

The copyData attribute is optional, with a default value of false. When copyData is set to true, it may be followed with the optional view_data_table element.

If view_data_table is not specified, Data Mover copies the view data to a target view. The element view selection = ”included” must be specified to avoid an exception at job creation time. For example:
<views>
	<view selection="included" copyData="true">
		<name>deptsals</name>
		<database>MyDb</database>
	</view>
</views>
In the example above, the data from the view deptSals is selected using "SELECT *from MyDb.deptSals" and loaded to the view MyDb.deptSals on the target. If the view MyDB.deptSals does not exist on the target, it is created using the source view definition. If the view MyDB.deptSals exists on the target, it is overwritten using the source view definition.
If view_data_table is specified, target_table indicates the table to which the underlying view data is copied and target_database indicates the database in which the target table exists. If the table does not already exist on the target, Data Mover creates the table. If the table already exists on the target, the view data is loaded to it. For example:
<views>
	<view selection="included" copyData="true">
		<name>deptsals</name>
		<database>MyDb</database>
		<view_data_table>
			<target_table>TargetTable</target_table>
			<target_database>TargetDb</target_database>
		</view_data_table>
	</view>
</views>

In the example above, the data from the view deptSals is selected using "SELECT * from MyDb.deptSals" and loaded to the table TargetDb.TargetTable. If TargetDb.TargetTable does not exist on the target, Data Mover creates the target table.

When Data Mover creates a target table, the table has the following characteristics:
  • Is a multiset table
  • Is created with the same column names and column types as the view
  • Is a no-fallback table without journaling
  • Does not contain a secondary index
In addition, if the view whose data is being copied contains a Primary Index column, that column is chosen as the Primary Index of the target table. If the view does not contain a Primary Index column, the first column in the view is chosen as the Primary Index.
When copying view data to a target view, the following apply:
  • The source view must not reference more than one table. If the view has multiple reference tables, a runtime exception occurs.
  • Any compare_ddl element is ignored because Data Mover creates the target view definition using the source view definition.
  • The table referenced by the source view must exist on the target or must be moved with the job.
When copying view data to a target view, the source view data is first copied to a target staging table and the target view, if one exists, is overwritten with the source view definition. Then the source view data is copied from the staging table to the target view. It is possible that the target reference table could include null values if the view does not include all of the columns of that reference table. Therefore, exercise care when using this feature.
The following rules apply to the use of load utilities when copying view data to either a target table or view:
  • Do not use DSA as the load utility if a source staging table is not used.
  • Specifying DSA without using a source staging table or TPTAPI_LOAD as the force_utility value when copying view data results in a create time error.
  • View data is copied using the TPTAPI_UPDATE operator unless force_utility is specified as TPTAPI_STREAM, JDBC, or DSA with source staging table.
  • When view data is being copied along with other tables, the view data is copied using the TPTAPI_UPDATE operator but the other data tables can be copied using the TPTAPI_LOAD operator. This happens if force_utility is unspecified and source staging table is not used, or if force_utility is specified as TPTAPI.