2.4 Creating and Using Job variables in Teradata Parallel Transporter

Using and Setting up Job variables:

In the above example we can see many attribute values that start with a @. These are actually job variables.

Ex:

      VARCHAR TdpId             = @MyTdpId,

      VARCHAR UserName          = @MyUserName,

      VARCHAR UserPassword      = @MyPassword,

Actual values are passed at run time using multiple ways explained later.

Once variables have been defined, they can be reused in any job where the value of the variable is valid.

A common use of variables is for the values of operator attributes.

To be able to use job variables we need to complete 2 activities:

1. Assign values to these variables. There are many places where we can assign values to these variables. Following is the list in the order of priority.

• Command line (Highest Priority)

You can specify variables on the command line, as follows:

tbuild -f weekly_update.tbr -u "UsrID = 'user', Pwd =  'pass' "

• A local job variables file

We Can have a local file that contains all the variables.

We can specify a local job variables file, which contains the values for job variables, using the -v option on the command line as follows:

tbuild -f weekly_update.tbr -v local.jobvars

On z/OS, specify a local job variables file through the DDNAME of ATTRFILE.

• Global job variables file

This global job variables file is read by every Teradata PT job. So if we have any variable that we need all the jobs to use we can place it here.

Place common, system-wide job variables in this file, then specify the path of the global job variables in the Teradata PT configuration file by using the GlobalAttributeFile parameter.
 

• Job script itself (lowest)

2. Reference variables in the job script.

Job variable reference is composed of the @ symbol, followed by a unique identifier for the variable.

These variables can be referenced in two places:

• Specifying Variables for Attributes:

Ex:

VARCHAR UserName = @MyUserName,

VARCHAR UserPassword = @MyPassword

If the attribute is:

• A character data type, the job variable value must be a quoted string.

• An integer data type, the job variable value must be an integer.

• An array attribute, the variable value must be an array of values of the attribute data type

• Specifying Non-Attribute Job Variables

Job variables can be used for object names and other parameters.

Ex:

'INSERT INTO TABLE ' || @TargTable || ' (:col1, :col2, . . ., :coln);'

Here the name of the target table is passed as variable from either of the 4 ways explained earlier.

However the technique of using them is different. We need to use concatenation as shown above.