17.00 - Variables - Parallel Data Pump

Teradata® Parallel Data Pump Reference

Parallel Data Pump
June 2020
Programming Reference

Predefined System Variables

Avoid use of the prefix &SYS in user-defined symbols because the names of predefined utility variables begin with the prefix. The following table contains a list of predefined system variables.

Predefined System Variables 
System Variable Description
&SYSDATE 8-character date format yy/mm/dd
&SYSDATE4 10-character date format yyyy/mm/dd
&SYSDAY 3-character day of week: MON TUE WED THU FRI SAT SUN
&SYSDELCNT[n} Number of rows deleted from all the target tables of Import n. If n is not specified, it gives the count of deletes done to all the target tables for all imports. The maximum value of n is 4.
&SYSETCNT[n} Number of records inserted into the error table for import n. If n is not specified, it gives the total count of all the records inserted into the error table for all imports. The maximum value of n is 4.
&SYSINSCNT[n} Number of rows inserted into all the target tables for import n. If n is not specified, this variable gives the total inserts done to all the target tables for all imports. The maximum value of n is 4.
&SYSJOBNAME Up to 16 characters (ASCII or EBCDIC) in length, in whichever character set is appropriate.

If &SYSJOBNAME is not set using the NAME command, it defaults to MYYYYMMDD_hhmmss_lllll, where the following is true:

M = macro

YYYY = year

MM = month

DD = day

hh = hour

mm = minute

ss = second

lllll = is the low-order 5 digits of the logon sequence number returned by the database from the .LOGON command.

&SYSOS Client operating system:
  • “UNIX” for IBM AIX, Linux, Oracle Solaris, and Linux on s390x operating systems
  • “Win32” for all Windows operating systems
  • “MVS” for all Mainframe operating systems
&SYSRC Completion code from last response by the database.
&SYSRCDCNT[n] Total number of records read for import n. If n is not specified, it gives the total records read for all imports.
&SYSTIME 8-character time format hh:mm:ss
&SYSUPDCNT[n] Gives total updates to all target tables for import n. If n is not given, it gives the total updates done to all the target tables for all the imports. The maximum value of n is 4.
&SYSUSER Client system dependent: CMS user ID, z/OS Batch user ID. (z/OS-batch returns user ID only when a security package such as RACF, ACF2, or Top Secret has been installed).
&SYSAPLYCNT[n] Number of records applied for import n. If n is not given, it gives the total number of records applied for all imports.
&SYSNOAPLYCNT[n] Number of records not applied for import n. If n is not given, it gives the total number of records not applied for all imports.
&SYSRJCTCNT[n] Number of records rejected for import n. If n is not given, it gives the total number of rejected records of all the imports. The maximum value of n is 4.

Date and Time Variables

&SYSDATE, &SYSDATE4, &SYSTIME, and &SYSDAY reflect the time when Teradata TPump begins execution. The original values are restored at restart. These values are character data types and should not be used in numeric operations. System variables cannot be modified, only referenced.

The values returned by &SYSDAY are all in upper case. Monday, for example, is returned as 'MON':

0003 .IF '&SYSDAY' NOT = 'MON' THEN;
14:10:28 - FRI JUL 30, 1993
UTY2402 Previous statement modified to:
0004 .IF 'FRI' NOT = 'MON' THEN;
0006 .ENDIF;

This example causes the RUN FILE command to be executed every day but Monday. It can be seen from this example that any of the system variables can be used as the subject condition within an IF/ELSE/ENDIF command construct. This allows creation of a script forcing certain events to occur or tasks to operate in a predetermined sequence, based on the current setting of the variable.

As another example, if we create the following table:


Create table &TABLE (
     Account_Number INTEGER NOT NULL,
     Last_Name  VARCHAR(25),
     First_Name VARCHAR(25),
     Street_Address VARCHAR(30),
     City VARCHAR(20),
     State CHAR(2),
     Zip_Code CHAR(5)
     Balance DECIMAL(9,2) FORMAT '-$,$$$,$$$.99' )
   Unique primary Index (Account_Number);

and then check the system variable &SYSRC for a return code to verify if the table already exists, a file containing options to continue or quit is logged at the console. Any other error return codes terminate the job with a database error, as follows:

.IF CREATERC = 3803 /* Table &TABLE already exists */
.BEGIN LOAD ----------; /* No errors returned. We can start the job.*/
/*   TPump statements go here.....    */

File RUN01, which operates when the 3803 error causes the RUN FILE command to execute, contains the following options:

.DISPLAY '&SYSUSER: Table FOO already exists....'
 to FILE console;
.DISPLAY '&SYSUSER: Reply <C> Continue anyway...'
 to FILE console;
.DISPLAY '&SYSUSER: Reply <A> Abort this JOB....'
 to FILE console;
.DISPLAY '&SYSUSER: Reply <C> or <A>.Default <A>'
 to FILE console;
.LOGOFF CREATERC; /* Quit before we cause trouble */

Row Count Variables

The row count variables, which are updated for each Teradata TPump task, allow the insert, update, and delete row counts and the error table counts for each import to be queried:
  • &SYSETCNT[n]

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

When EXECUTE <macroname> INSERT|UPDATE|DELETE is used, Teradata TPump must rely on the user to have correctly identified the action (INSERT, UPDATE, or DELETE) which the macro performs. Teradata TPump cannot always determine the number of target tables, and therefore can only provide a single combined value for all target tables. Using the existing facility of variable substitution, each new system variable can be referenced as soon as the variable is defined. The new variables are defined during the import phase and should be referenced after the END LOAD command and before any subsequent BEGIN LOAD command in the Teradata TPump job script.

The values of the new system variables must be stored in the Teradata TPump log table and be restored after a restart.

Utility Variables

Teradata TPump supports utility variables. These variables are set via either the SET command or the ACCEPT command. Teradata TPump Commands describes them in greater detail.

Additionally, Teradata TPump predefines some utility variables that provide information about the Teradata TPump environment at execution time. The name of these variables must begin with an ampersand (&) when variable substitution is desired. The rest of the name must obey the rules for standard Teradata SQL column names.

Teradata TPump supports an environmental variable for each DML statement executed. At the end of an import, a variable is established for each statement executed. The variable is named using the number of the import (1 through 100), the label of the clause “containing” the DML statement, and the number of the statement within the IMPORT’s apply clause.

Variable Substitution

Variable substitution, to allow for dynamic statement modification, is allowed on any statement by preceding the variable name with an ampersand. Each occurrence of a variable name, preceded by an ampersand, is replaced by its current value. Numeric values are permitted, but their values are converted to character for the replacement. This replacement occurs before the statement is analyzed. The replacement operation for a given statement occurs only once (one scan). This means that replacements generating ampersand variable names are not replaced.

Even when it appears in a quoted string, an ampersand is always interpreted as the first character of a utility variable unless it is immediately followed by another ampersand. Such a double ampersand is converted to a single textual ampersand.

If a reference to a utility variable is followed by a nonblank character that could appear in a variable name, there must be a period between the variable and the nonblank characters. Teradata TPump 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, the sequence of variable and characters must appear as &x..ab to produce xy.ab as the result. Such a double period is converted to a single textual period and concatenated with the value of the utility variable.