Teradata

The job steps listed below allow a Teradata user to perform backup, restore and various other operations in concert with a Teradata database system.

 

To execute Teradata job steps you must install the Teradata Tools and Utilities (TTU) kit on both the Job Scheduler and on every Execution Machine you intend to execute Teradata jobs. BTEQ Tool is needed for SQL Query using BTEQ; .Net and/or ODBC (in that order) is needed for SQL Query using SQL; TDBuild is required for Start PT Job; TDLoad is required for Load Data; ARCmain is required for ARC.

 

Note: At the time this chapter was written, a known bug within TTU did not properly resolve host names. The workaround is to specify an IP address. This issue may have been resolved in a later version of the TTU (the bug was present in TTU version 10.13).

 

Note: Best Practice: When using existing Teradata scripts with the job steps below, we recommend that you remove the use of the .LOGON command from the script and allow ActiveBatch to add that entry, in a more secure manner, using the “Logon” properties.

 

 

This job step gives you access to the ARCmain program. The ARCmain program allows you to backup, restore and recover a Teradata database.

 

 

Job Step Properties

 

ScriptFile – This property, if specified, represents a valid file specification in which Teradata ARC commands have been specified. ScriptFile and Script are mutually exclusive. If ScriptFile and Script are specified, Script is used.

 

Script – This property, if specified, represents a series of valid Teradata ARC commands. ActiveBatch variable substitution is supported. ScriptFile and Script are mutually exclusive. If ScriptFile and Script are specified, Script is used.

 

Logon – This collection of properties represents the Teradata host name and security credentials necessary for accessing the Teradata system. While Teradata supports the specification of security credentials as part of the script, a Best Practice would be to use the Username Account object for security specification. This avoids setting username and password information in the clear.

 

FlagParameters – This optional property represents one or more options that may be specified and control the Teradata job step. Options are: Data Encryption, Halt, Hex, Log Skipped, Pause and Restart. Information on these parameters can be found in the latest “Teradata Tools and Utilities Command Summary” documentation.

 

CatalogParameters – This collection of properties allows you to specify which database objects are to be the subject of this job step. Documentation on CatalogParameters can be found in the latest “Teradata Tools and Utilities Command Summary” documentation.

 

ValueParameters – This collection of properties represents one or more runtime parameters that can be specified with the operation you intend to execute. For example, “WORKDIR” would represent a working directory. This runtime parameter would also contain the value of that parameter. Information on Runtime parameters can be found in the latest “Teradata Tools and Utilities Command Summary” documentation.

 

VerboseSettings – This optional property allows you to set verbose tracing of information when this job step executes. Level indicates the level of trace. The default value is 1. Acceptable values are 2 and 3 which provide additional tracing.

 

Return Step Value

 

Three (3) values are returned as part of this job step: ExitCode, Output and Error. ExitCode is the actual exit code provided when executing the Teradata ARC utility. Output represents a string that contains information written by the utility to “standard out” and Error represents a string that contains information written by the utility to “standard error”.

 

 

This job step allows you to load data from a flat file into a specified Teradata database table.

 

Note: This job step requires that the execution user have “write” access to the Teradata log folder (for example, c:\progam files Files (x86)\Teradata\Client\13.10\Teradata Parallel Transporter\logs).

 

 

Job Step Properties

 

File – This property represents a valid file specification in which the contents of the file will be loaded into the table specified by TargetTable.

 

Logon – This collection of properties represents the Teradata host name and security credentials necessary for accessing the Teradata system. 

 

TargetTable – This property represents the table you wish to load data into. The dropdown provides an enumeration of tables on the specified host (if “host” is specified).

 

JobName – This optional property represents the name of the Teradata job.

 

AdvancedSettings – This property consists of a number of additional and advanced settings that allow for further refinement in terms of loading data into the target table. See the related Teradata documentation for more information concerning these settings.

 

Return Step Value

 

Three (3) values are returned as part of this job step: ExitCode, Output and Error. ExitCode is the actual exit code provided when executing the Teradata ARC utility. Output represents a string that contains information written by the utility to “standard out” and Error represents a string that contains information written by the utility to “standard error”.

 

 

This job step allows you to submit a SQL query to a Teradata database (i.e. select and manage data, database objects, execute stored procedures, etc.). Two ways of submitting queries are supported: BteqScript and SqlBlock.  BteqScript invokes BTEQ (Basic Teradata Query) command based utility and runs BTEQ scripts.  SqlBlock uses the Teradata .Net provider (with a fallback to ODBC if the .Net provider is not available) to communicate with the database and submits Teradata Database SQL language statements.

 

SQL Script: BTEQ

 

 

Job Step Properties

 

ScriptFile – This property, if specified, represents a valid file specification in which Teradata BTEQ commands have been specified. ScriptFile or Script are required; both are mutually exclusive. If ScriptFile and Script are specified, Script is used.

 

Script – This property, if specified, represents a series of valid Teradata BTEQ commands. ActiveBatch variable substitution is supported. ScriptFile or Script are required; both are mutually exclusive. If ScriptFile and Script are specified, Script is used.

 

Logon – This collection of properties represents the Teradata host name and security credentials necessary for accessing the Teradata system. While Teradata supports the specification of security credentials as part of the script, a Best Practice would be to use the Username Account object for security specification. This avoids setting username and password information in the clear.

 

CharacterSet – This optional property controls the character set used when communicating with the target database and table.

 

Encoding – This optional property controls the encoding of the input script file (i.e. UTF8, UTF16,etc).

 

Return Step Value

 

Four (4) properties are returned as part of this job step: DataSet, ExitCode, Output and Error. DataSet is an object that consists of a number of sub-objects and collections that represent the information returned from the SQL Query job step. ExitCode is the actual exit code provided when executing the Teradata utility. Output represents a string that contains information written by the utility to “standard out” and Error represents a string that contains information written by the utility to “standard error”.

 

SQL Script: SQL

 

 

Job Step Properties

 

Logon – This collection of properties represents the Teradata host name and security credentials necessary for accessing the Teradata system. While Teradata supports the specification of security credentials as part of the script, a Best Practice would be to use the Username Account object for security specification. This avoids setting username and password information in the clear.

 

Database – This property represents a Teradata database that the SQL Query will target.

 

Content – This property represents one or more valid Teradata SQL statement(s).

 

Timeout – This optional property indicates how long to wait, in seconds, for the SQL statement to execute. The default is 30 seconds.

 

OutputResults – This optional property indicates what is to be done with the results of the SQL query. Three (3) choices are available: None (default), ReturnDataset and WriteToLog. ReturnDataset indicates that any results are to be returned as a job step return value. WriteToLog indicates that the results are to be written to the job’s log file.

 

Return Step Value

 

Four (4) properties are returned as part of this job step: DataSet, ExitCode, Output and Error. DataSet is an object that consists of a number of sub-objects and collections that represent the information returned from the SQL Query job step. ExitCode is the actual exit code provided when executing the Teradata utility. Output represents a string that contains information written by the utility to “standard out” and Error represents a string that contains information written by the utility to “standard error”.

 

 

This job step starts a PT job. “PT” represents the Parallel Transporter feature of the Teradata system.

 

 

Job Step Properties

 

ScriptFile – This property, if specified, represents a valid file specification in which Teradata PT commands have been specified. ScriptFile and Script are mutually exclusive. If ScriptFile and Script are specified, Script is used.

 

Script – This property, if specified, represents a series of valid Teradata PT commands. ActiveBatch variable substitution is supported. ScriptFile and Script are mutually exclusive. If ScriptFile and Script are specified, Script is used.

 

Logon – This collection of properties represents the Teradata host name and security credentials necessary for accessing the Teradata system. While Teradata supports the specification of security credentials as part of the script, a Best Practice would be to use the Username Account object for security specification. This avoids setting username and password information in the clear. In addition, three (3) ActiveBatch Execution Variables are created for you to use within your script: jobvar_TDusername (username), jobvar_TDpassword (password) and jobvar_TDpid (hostname). These variables: (1) avoid specifying security credentials in the clear and (2) avoid hard coding information that is best specified as a variable.

 

JobVariablesFile – This optional property represents a valid file specification in which variable/value pairs have been placed. These Teradata variables can then be specified within the script. 

 

JobName – This optional property allows you to name the job.

 

AdvancedSettings – This property consists of a number of additional and advanced settings that allow for further refinement in terms of running the PT job. See the related Teradata documentation for more information concerning these settings.

 

Return Step Value

 

Four (4) properties are returned as part of this job step: JobID, ExitCode, Output and Error. JobID represents the job id. ExitCode is the actual exit code provided when executing the Teradata ARC utility. Output represents a string that contains information written by the utility to “standard out” and Error represents a string that contains information written by the utility to “standard error”.