15.10 - Variables - MultiLoad

Teradata MultiLoad Reference

prodname
MultiLoad
vrm_release
15.10
category
Programming Reference
featnum
B035-2409-035K

Variables

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

Table 10 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.

 

Table 10: 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 Teradata 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.

    Note: 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 Teradata 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.

    Table 11 describes rules that apply to variable substitution.

     

    Table 11: 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.