create (Aster Database) | Commands | Teradata Data Mover - create (Aster Database) - Teradata Data Mover

Purpose

The create command creates a job on the daemon from the syntax parameters and the object list of databases and tables. A job definition consists of the parameters and object list.

Data Mover supports moving tables between Teradata Database and Aster Database.

Syntax

See http://schemas.teradata.com/unity/DataMover.xsd.

Parameters - Aster Specific

See Parameter Order.

aster_options

Defines optional Aster connector-specific details.

Optional Parameter	Description
data_streams	Number of instances that should participate in parallel TPT import into Aster Database or export to Teradata Database. The value of this clause cannot exceed the number of virtual workers (vWorkers) in Aster Database or the number of AMPs in Teradata Database. For Aster to Teradata jobs, the data_streams value must be the same as the virtual workers (vWorkers) count; otherwise, Data Mover ignores the user input value.
log_level	Log level for log file output. Valid value is either 0 or 99. If not supplied, the default is 0.
query_band	In Aster 6.0 or later, specifies the values to populate TPT connection attributes. Clauses must end in a semi-colon. Default values: load_from_teradata - "APPLICATIONNAME=ASTEREXP;" load_to_teradata - "APPLICATIONNAME=ASTERLOAD;"
query_timeout 1	Specifies the response time allowed in seconds. If not supplied, the default timeout is 30 minutes. Data Mover allows all non-negative values; however, values greater than 86400 (24 hours) cause the job to fail at runtime.
source_sessions | target_sessions 2	Specifies the limit on the total number of sessions (from all Aster vWorkers) that can be established with Teradata AMPs (with all AMPs).
1In the CLI, this parameter takes the form aster_query_timeout. 2Where source_sessions is for Teradata to Aster and target_sessions is for Aster to Teradata.

source_aster_system | target_aster_system

Defines required connection details for the source or target Aster system. When passing this information using an .xml file, nest the options below the parameter.

Option	Description
system_name	Aster Database Queen node IP address or hostname
port	[Optional] Aster Database server port
user_name	Aster Database user name
password	[Optional] Aster Database password
password_encrypted	[Optional] Source or target Aster Database encrypted password. Not a valid option if password option is specified.

The parameters for passing equivalent information when using the CLI are as follows:

• source_aster_system_name | target_aster_system_name
• source_aster_port|target_aster_port
• source_aster_user_name | target_aster_user_name
• source_aster_user_password | target_aster_user_password
• source_aster_user_password_encrypted | target_aster_user_password_encrypted

Parameters - Common

See Parameter Order.

broker.port

[Optional] You may enter a broker port to overwrite the default value specified in the commandline.properties file in order to connect to a different ActiveMQ server (and therefore a different daemon) at runtime.

Example: 61616

broker.url

[Optional] You may enter a broker URL to overwrite the default value specified in the commandline.properties file in order to connect to a different ActiveMQ server (and therefore a different daemon) at runtime.

Example: dm-server1

compare_ddl

[Optional] Designates whether to compare DDL for the source and target objects specified.

Valid Values

• unspecified (default) the daemon.default.compareDDL.enabled parameter value from the Data Mover daemon configuration file is used. If the parameter is specified as true or false, that value overrides the parameter value in the Data Mover daemon configuration file.
• true - the job steps are not recreated each time the job is started and DDL is compared on source and target.
• false - the job steps are recreated each time the job is started and DDL is not compared on source and target.

freeze_job_steps

[Optional] Freezes the job steps so that they are not recreated each time the job is started. Should only be set to true if the source and target environments do not change after the job is created.

Valid Values

• true - the job steps are not recreated each time the job is started
• false - the job steps are recreated each time the job is started
• unspecified (default) - the value is set to false

Example: true

job_name

[Optional] Name for this job. The name must be unique and up to 32 characters.

job_priority

[Optional] Specifies the execution priority for the job. Supported values are: HIGH, MEDIUM, and LOW, and UNSPECIFIED. If no value is specified, the default of MEDIUM is used at runtime.

Example: MEDIUM

log_to_event_table

[Optional] Specifies the event table to be used for this job. For more information, see Using Event Tables.

max_agents_per_task

[Optional] Maximum number of Data Mover agents to use in parallel when moving tables, databases, or journals.

Example: 4

The default value is dynamically calculated by Data Mover.

online_archive

[Optional] Allows read and write access to the source table(s) while the tables are being copied with Teradata ARC or Teradata DSA. Updates occur to the source table during the copy, but are not transferred to the target table. After a successful copy, the data contained in the target table matches the data that was in the source table at the beginning of the copy.

Valid Values

Value	Description
True	Enables online archive
False	Disables online archive
Unspecified	Default – the value is set to the value in the Data Mover daemon configuration file

Example: true

overwrite_existing_objects

[Optional] Job overwrites objects that already exist on the target.

Valid Values

Value	Description
True	Enables overwriting
False	Disables overwriting
Unspecified	Default – the value is set to the value in the Data Mover daemon configuration file

If the parameter is not specified, the value is set to the overwrite_existing_objects parameter value in the Data Mover daemon configuration file. If the parameter is specified as true or false, that value takes precedence over the parameter value in the Data Mover daemon configuration file.

Example: true

response_timeout

[Optional] Amount of time, in seconds, to wait for response from the Data Mover daemon.

Example: 60

source_tdpid | target_tdpid

Source or target Teradata Database.

source_user | target_user

[Optional] Source or target Teradata logon id. If you do not specify a logon id for this parameter, the command prompts you to enter it interactively.

source_password | target_password

[Optional] Source or target Teradata logon password. Not a valid parameter if source_password_encrypted | target_password_encrypted is specified. If you do not specify a password for this parameter, the command prompts you to enter it interactively. Input is masked with a set number of asterisks, regardless of the length of the password.

source_password_encrypted | target_password_encrypted

[Optional] Source or target Teradata encrypted logon password. Not a valid parameter if source_password | target_password is specified.

table

Defines the source or target sites for Aster data. Data Mover supports moving tables and columns from Aster to Teradata.

Option	Description
name	Name of the table to be moved.
target_name	[Optional] Renames the table. If no value is specified, the original table name is used.
target_database	[Optional] Moves the table to a different database. If no value is specified, the original database is used as the target.

Parameters - Teradata to Aster

See Parameter Order.

aster_table_params

Table to be copied from Teradata to Aster

Parameter	Description
target_name	[Optional] Renames the table. If no value is specified, the original table name is used.
target_database	[Optional] Moves the table to a different database. If no value is specified, the original database is used as the target.
target_schema_name	[Optional] Moves the table to specific schema in target Aster system. If not supplied, the default is PUBLIC.
target_table_type	[Optional] Aster table type. Value is FACT or DIMENSION. If not supplied, the default is FACT.
target_table_distribution	[Optional] Aster table distribution type. Value is DISTRIBUTE_BY_HASH or DISTRIBUTE_BY_REPLICATION. If not supplied, the default is DISTRIBUTE_BY_HASH.
target_table_distribution_key_column	If the target table distribution type is DISTRIBUTE_BY_HASH. Value is the column name. Based on the column value, the rows of the table are distributed across the vWorkers. By default, Data Mover considers the source table columns in the following priority: Primary Index, Secondary Index, First Column of the table.

preserve_column_case

Specifies case for column names. Values:

• No: Default. Changes all table names to lowercase.
• Yes: Keeps the existing case for column names when transferring to Aster Database.

In the CLI, this parameter takes the form aster_preserve_column_case.

Parameters - Aster to Teradata

See Parameter Order.

aster_table_params

Table to be copied from Aster to Teradata

Parameter	Description
source_schema_name	[Optional] If the table uses a special schema, you can specify that schema name. The default is public.

size

If the target table does not exist, ensure the sum of all CHAR/VARCHAR columns is not greater than 64000; otherwise, the job will fail at runtime due to a JDBC limitation. For columns with the UNICODE session character set, consider the value double what is specified in size while calculating the sum of all columns' size. For example, if the column of the character set is UNICODE and the size is 2000, consider the size 4000.

skip_error_records

Specifies treatment of errors encountered when loading data to Aster Database. When using load_from_teradata, the connector can encounter an error parsing a row containing data that is valid in Teradata but is not supported in Aster Database.

• No: Default. Load is aborted upon encountering an error, and no rows are loaded to Aster Database.
• Yes: Keeps the existing case for column names when transferring to Aster Database.

table_columns

[Optional] Customizes the table columns before moving data to Teradata Database. If the target table does not exist, you can rename the columns on the target side. If a target table already exists, the column name and type should match the column that exists on target. The table column properties create the target table on Teradata Database if the target table does not exists, or validate the values of an existing table.

Option		Description
allowDuplicate		[Optional] Specifies whether duplicate values are allowed in the target table column (true of false). Must not be specified or must be set to false if isPrimaryIndex is true, or an error will occur.
allowNull		[Optional] Specifies if null values are allowed in the target table column (true or false). Must not be specified or must be set to false if isPrimaryIndex is true, or an error will occur.
isPrimaryIndex		[Optional] Specifies whether null values are allowed in the target table column (true or false). If isPrimaryIndex is false and allowNull is true, allowDuplicate must not be specified or must be false, or an error will occur.
name		Name of the column to be modified
size		[Optional] Limits the size of columns such as CHAR, VARCHAR, BYTE, and VARBYTE only.
target_name		[Optional] Renames the column. If no value is specified, the original column name is used as the target name.
target_type		[Optional] Changes the default column type. If no value is specified, the default mapping is used. The default and available mappings are described in the section below.
type		Table column type. Informational only; cannot be modified by the user.

Usage Notes

• Aster schema names, table names, and column names are case-sensitive.
• Aster identifiers can contain any characters except single quotes, a single asterisk with no other surrounding characters, double quotes, and backslashes.
• Aster to Teradata jobs are not allowed to move Aster tables with the same name but different case within the same database. This is a Teradata limitation.
• Aster to Teradata jobs are not allowed to move Aster tables with the same column names but different case. This is a Teradata limitation.
• For partial table copy, if the WHERE clause has case-sensitive column names, the case-sensitive column names must be surrounded by double quotes.
• Aster users should have CONNECT privileges on the source database and SELECT privileges on all tables specified in the job.
• The db_superuser tables are not visible to other users.
• Teradata allows a maximum of 2048 columns in a table; Aster allows a maximum of 1600 columns in a table.
• Teradata allows a maximum table and column name length of 30 characters. Aster allows a maximum table/column name length of 63 characters.
• Aster allows a maximum of 1600 columns in a table; however, if the columns contain multibyte UTF-8 characters, the maximum number of columns will be lower.
• Aster table names can contain any characters except a single asterisk, single quotes, double quotes, backslashes, and the character with code zero.
• Teradata column data types accept a range of values or a specific size of data. For more information, see Teradata® Database SQL Functions, Operators, Expressions, and Predicates, B035-1145.
• Aster column data types accept a range of values or a specific size of data.
• Staging database is not supported for Teradata to Aster jobs.
• Teradata to Aster jobs allow a maximum row data length of 64,260 bytes. This is a TPT API limitation.
• For Teradata to Aster jobs, Data Mover generates SQL to create the target table in Aster if the target table does not exist already.
• If the Aster table name has trailing spaces, the trailing spaces will be ignored. Teradata does not allow trailing spaces.

XML File Example

For the create command, type datamove create -f parameters.xml.

<?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/unity/datamover.xsd">
    <job_name>aster-1</job_name>
    <source_tdpid>dm-agent4</source_tdpid>
    <source_user>test</source_user>
    <source_password>test</source_password>
    
    <use_userid_pool>false</use_userid_pool>
    <!-- Aster system details.-->
    <target_aster_system>
        <system_name>153.64.28.178</system_name>
        <port>2406</port>
        <user_name>beehive</user_name>
        <password>beehive</password>
    </target_aster_system>
    
    <!-- Aster NUM_INSTANCES value -->
    <data_streams>1</data_streams>
    
    <!-- Aster MAX_SESSION value -->
    <source_sessions>1</source_sessions>
    <max_agents_per_task>1</max_agents_per_task>
    <overwrite_existing_objects>true</overwrite_existing_objects>
    <!-- Aster TRACE_LEVEL value -->
    <log_level>0</log_level>
    <online_archive>false</online_archive>
    <!-- Aster options -->
    <aster_options>
        <query_timeout>120</query_timeout>
        <preserve_column_case>no</preserve_column_case>
        <skip_error_records>no</skip_error_records>
    </aster_options>
    <database selection="unselected">
        <name>test</name>
         <target_database>
            <name>beehive</name>
        </target_database>
        <table selection="included">
            <name>bs1</name>
            <aster_table_params>
                <!-- Aster table params.-->
                    <target_schema_name>public</target_schema_name>
                    <target_table_type>FACT</target_table_type>
                    <target_table_distribution_type>DISTRIBUTE_BY_HASH</target_table_distribution_type>
                    <target_table_distribution_key_column>customer_id</target_table_distribution_key_column>
                </aster_table_params>
        </table>
    </database>
    <query_band>ApplicationName2=DM;Version=16.20.25.00;</query_band>
</dmCreate>