Variables - MultiLoad

Teradata® MultiLoad Reference

Product
MultiLoad
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-18
dita:mapPath
aim1544831946660.ditamap
dita:ditavalPath
gyk1507317446489.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

Teradata MultiLoad supports the types of variables described in the topics that follow, including predefined system variables, data and time variables, row count variables, and utility variables.

Predefined System Variables

The following table lists supported predefined system variables. Since the variables all begin with the &SYS prefix, avoid using this prefix in user-defined utility variables. System variables can only be referenced; they cannot be modified.

System Variables 
Variable Name Description
&SYSAPLYCNT [n] Count of apply conditions satisfied for import file n. If n is not specified, the default is cumulative count of apply conditions satisfied (all import).
&SYSDATE Eight-character date in yy/mm/dd format.
&SYSDATE4 Ten-character date in yyyy/mm/dd format.
&SYSDAY Three-character uppercase day-of-week specification: MON, TUE, WED, THU, FRI, SAT, or SUN.
&SYSDELCNT [n] Number of rows deleted from the nth target table. If n is not specified, the default is the first target table.

The maximum value of n is 5.

&SYSETCNT [n] Number of rows in the ET table for the nth target table. Teradata MultiLoad uses the ET table, also called the Acquisition Phase error table, to store data errors found during the acquisition phase of a Teradata MultiLoad import task.

If n is not specified, the default is the first target table. The maximum value of n is 5.

&SYSINSCNT [n] Number of rows inserted in the nth target table. If n is not specified, the default is the first target table.

The maximum value of n is 5.

&SYSNOAPLYCNT [n] Count of records not applied for import file n. If n is not specified, the default is cumulative count of records not applied (all import files).
&SYSOS Client operating systems:
  • For z/OS: z/OS, z/OS/SP, z/OS/ESA
  • UNIX
  • Win32
&SYSRC Completion code of the last response from the database.
&SYSRCDCNT [n] Count of records read from import file n. If n is not specified, the default is the cumulative count of records read (all import files).
&SYSRJCTCNT [n] Count of candidate records rejected from import file n. If n is not specified, the default is cumulative count of candidate records rejected (all import).
&SYSTIME Eight-character time in hh:mm:ss format.
&SYSUPDCNT [n] Number of rows updated in the nth target table.

If n is not specified, the default is the first target table.

The maximum value of n is 5.

&SYSUSER z/OS batch userid. (z/OS batch returns userid only when a security package such as RACF, ACF2, or Top Secret has been installed.)
&SYSUVCNT [n] Number of rows in the UV table for the nth target table. Teradata MultiLoad uses the UV table, also called the Application Phase error table, to store data errors found during the application phase of a Teradata MultiLoad import or delete task.

If n is not specified, the default is the first target table.

The maximum value of n is 5.

Date and Time Variables

The four date and time variables reflect the time when Teradata MultiLoad begins execution:
  • &SYSDAY
  • &SYSDATE
  • &SYSDATE4
  • &SYSTIME

The original values are not maintained after a Teradata MultiLoad restart operation.

Do not reference the values in numeric operations since the values are all character data types.

Row Count Variables

The five row count variables, which are updated for each Teradata MultiLoad task, allow queries of the insert, update, and delete row counts and the error table counts for each target table:
  • &SYSDELCNT[n]
  • &SYSETLCNT[n]
  • &SYSINSCNT[n]
  • &SYSUPDCNT[n]
  • &SYSUVCNT[n]

The values are stored in Teradata MultiLoad restart log table and are restored after a client system or database restart.

Reference these variables after the END MLOAD command and before any subsequent BEGIN MLOAD command in the Teradata MultiLoad job script.

Utility Variables

Teradata MultiLoad supports utility variables that are set with either the SET command or the ACCEPT command.

Teradata MultiLoad also predefines some utility variables that provide information about the Teradata MultiLoad support environment at execution time. Utility variable names must begin with the ampersand character (&) to use variable substitution.

Variable Substitution

Teradata MultiLoad performs variable substitution on any statement. When Teradata MultiLoad encounters a variable name with an ampersand character (&) prefix, it replaces the variable name with the current value of that variable.

The following table describes rules that apply to variable substitution.

Variable Substitution Rules 
Topic Rule
Numeric Variable Values Are permitted, but Teradata MultiLoad converts them to character data types for the replacement.
Value Substitution Takes place one time:
  • On the initial scan
  • Before the statement is analyzed

If replacement produces a variable name with an ampersand character, that variable name is not replaced.

An Ampersand Character Is always interpreted as the first character of a utility variable, unless it is immediately followed by another ampersand character. This is true, even when it appears in a quoted string.

(Teradata MultiLoad converts two consecutive ampersand characters to a single ampersand text character.)

References to a Utility Variable When followed by a non-blank character or character string that could appear in a variable, the reference must include a period between the variable and the non-blank characters. Teradata MultiLoad discards the period in this context.

For example, if a utility variable called &x has the value xy and is to be immediately followed by the characters .ab in some context, to produce xy.ab as the result, the sequence of variable and characters must appear as:

&x..ab

Teradata MultiLoad converts the two periods to a single period text character concatenated with the value of the utility variable.