Upsert Example
Each record in the following upsert example contains the value of the primary index column (EmpNo) of a row of the Employee table whose PhoneNo column is to be assigned a new phone number from field Fone.
The example assumes that the current default database is Personnel, the database containing Employee.
.BEGIN IMPORT MLOAD TABLES Employee
.LAYOUT Layoutname;
.FIELD EmpNum 1 INTEGER;
.FIELD Fone * (CHAR (10));
.DML LABEL DMLlabelname
DO INSERT FOR MISSING UPDATE ROWS;
UPDATE Employee SET PhoneNo = :Fone WHERE EmpNo = :EmpNum;
INSERT Employee (EmpNo, PhoneNo) VALUES (:EmpNum, :Fone);
When the update operation fails, the INSERT statement executes, per the upsert feature. In this case, each record contains the primary key value (EmpNum) of a row that is to be inserted successively into the Employee table whose columns are EmpNo and PhoneNo.
Purpose
The END MLOAD command, which must be the last command of a Teradata MultiLoad task, signifies the end of the task script and initiates task processing by Teradata Database.
Syntax
Purpose
The EOC (End Of Console) command, which is primarily for developer use, indicates “end of console” when MultiLoad script is input from the console. Using the EOC command in MultiLoad batch mode will cause MultiLoad job to terminate.
Syntax
Purpose
The FIELD command specifies a field of the input record to be sent to Teradata Database. It can also specify a NULLIF expression.
Syntax
where:
Syntax Element |
Description |
datadesc |
Type and length of data in the field Teradata MultiLoad generates the USING phrase accordingly, with the user-assigned field name, for subsequent insert, update, and delete operations. For details on data types and data conversions, see SQL Data Definition Language for details. |
DROP… |
Character positions to be dropped from the specified fieldname1 These must be of a character data type. Teradata MultiLoad drops the specified characters and presents the field to Teradata Database as VARCHAR data type. |
fieldexpr |
Concatenation of two or more items, either: or a combination of these, as in: fieldname2||fieldname2||fieldname2...
The field names within a layout must be unique. Nested concatenations are not supported. Each fieldname2 that is actually a field name must be specified in its own FIELD or FILLER command with a startpos parameter and a datadesc parameter that specifies the character data type. The fieldname1 parameter in other FIELD commands can be referenced in fieldexpr expressions. When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the character and the string constants specified in the expression from the script character encoding to the client character encoding before concatenating the constants with the specified fields. For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems. Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters required in the job script. See International Character Set Support for details |
fieldname1 |
Name of an input record field The fieldname1 specification can be referenced by: A fieldname1 specification must obey the same construction rules as Teradata SQL column names. |
nullexpr |
Condition used for selectively inserting a null value into the affected column The condition is specified as a conditional expression involving any number of fields, each represented by its fieldname, and constants. Each fieldname appearing in the conditional expression must be defined by either: The fieldname specifications in a nullexpr condition cannot be defined by a fieldexpr of a FIELD command. Note: The fieldname1 parameter in other FIELD commands can be referenced in nullexpr conditions. A FastLoad-type value specification (NULLIF=value) cannot be used as the nullexpr specification. The nullexpr specification must be a complete conditional expression that includes a logical operator. When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the string constants and the import data referenced in the expression to the same character set before evaluating the expression. For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8when using the UTF-16 client character set on network-attached systems. Network Example: If the client character set is UTF-16 and the script character set is UTF-8, and the following commands are given, MLOAD translates the data in the C1 field to the UTF-8 form and compares it with the UTF-8 form of 'DELETED' to obtain the evaluation result. .field C1 * varchar(20);
.field C2 * varchar(40) nullif c1 = 'DELETED';
Mainframe Example: If the client character set is UTF-8 and the script character set is Teradata EBCDIC, and the following commands are given, Teradata MultiLoad translates the data in the C1 field from the UTF-8 form to the Teradata EBCDIC form and compares it to the Teradata EBCDIC form of 'removed' to obtain the valuation result. .field C1 * char(20);
.field C2 * char(40) nullif c1 = 'removed';
Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters required in the job script. See International Character Set Support for details. |
startpos |
Starting position of the specified field in the data records of an external data source The startpos can be specified as: Note: The field positions of input records can be redefined by specifying the same positions in multiple FIELD commands. |
Usage Notes
The following table describes the things to consider when using the FIELD command.
Topic |
Usage Notes |
Changing the Data Type is Not Allowed |
The datadesc parameter cannot be used to change the data type from character to decimal or period when redefining an input record field in fieldname1. Doing so causes the Teradata MultiLoad job to abort with an error message. |
Intermixing Commands |
Intermixing one or more FIELD commands with TABLE and FILLER commands is allowed. All of these commands must follow a LAYOUT command. |
NULLIF Performance |
Use the following: ...
And, in the more complex situation use: ...
.FIELD fs * CHAR(1) ;
VALUES(...,:fs,:fc,:fi,...);
|
|
or, use: ...
|
Parameter Evaluation Sequence |
If both NULLIF and DROP LEADING/TRAILING BLANKS/NULLS are specified in the same FIELD command, Teradata MultiLoad evaluates the DROP clause after the NULLIF clause. For example, if the input for field1 is 'x' in the following FIELD command, the NULLIF expression would evaluate to false because the leading blanks are not dropped before the NULLIF evaluation: .FIELD FIELD1 * CHAR (5) NULLIF
|
Specifying Decimal Data Types |
The following is the input length and field description for the decimal data type specifications which can be made in the datadesc parameter: DECIMAL(x) and DECIMAL(x,y) For more information on the DECIMAL data type, see SQL Data Types and Literals. |
Geospatial Types |
Teradata MultiLoad does not support geospatial data represented by LOBs. maximum 64000 |
Specifying ARRAY Data Types |
A column that is defined as an ARRAY data type in a Teradata table must be specified as a VARCHAR data type in the FIELD command. The external representation for an ARRAY data type is VARCHAR. The following is a sample Teradata table definition that includes a one-dimensional ARRAY data type for the COL003 column: CREATE SET TABLE SOURCE_TABLE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
EMP_ID INTEGER,
EMP_NO BYTEINT,
COL003 SYSUDTLIB.PHONENUMBERS_ARY,
COL004 SYSUDTLIB.DECIMAL_ARY,
COL005 SYSUDTLIB.INTEGER_ARY)
UNIQUE PRIMARY INDEX ( EMP_ID );
The following is a sample definition for the PHONENUMBERS_ARY data type: CREATE TYPE PHONENUMBERS_ARY AS CHAR(10) CHARACTER SET LATIN ARRAY [2];
The following is a sample definition for the DECIMAL_ARY data type: CREATE TYPE DECIMAL_ARY AS DECIMAL(5,2) ARRAY[2];
The following is a sample definition for the INTEGER_ARY data type: CREATE TYPE INTEGER_ARY AS INTEGER ARRAY[2];
The following is a sample Teradata MultiLoad FIELD definition for the sample SOURCE_TABLE: .FIELD EMP_ID * INTEGER;
.FIELD EMP_NO * BYTEINT;
.FIELD COL003 * VARCHAR(47);
.FIELD COL004 * VARCHAR(17);
.FIELD COL005 * VARCHAR(25);
In the above example, the COL003 column is defined as VARCHAR(47), because it's the maximum representation for the COL003 column in the table. The following is the calculation for the maximum representation for the COL003 column: 1 byte for the left parenthesis +1 byte for the single quote +10 to 20 bytes for the first element +1 byte for the single quote +1 byte for the comma +1 byte for the single quote +10 to 20 bytes for the second element +1 byte for the single quote +1 byte for the right parenthesis ---- 47 bytes |
|
The following are two sample data sets for the COL003 column: Sample data 1: ('3105551234','3105551234') Sample data 2: ('''''''''''''''''''''','''''''''''''''''''''') Sample data 1 contains 2 elements of phone numbers. Sample data 2 contains 2 elements of all single quote characters. In the above example, the COL004 column is defined as VARCHAR(17), because it's the maximum representation for the COL004 column in the table. The following is the calculation for the maximum representation for the COL004 column: 1 byte for the left parenthesis +1 to 7 bytes for the first element +1 byte for the comma +1 to 7 bytes for the second element +1 byte for the right parenthesis ---- 17 bytes The following is two samples of data for the COL004 column: Sample data 1: (-123.45,888.10) Sample data 2: (+123.45,-888.10) In the above example, the COL005 column is defined as VARCHAR(25), because it's the maximum representation for the COL005 column in the table. The following is the calculation for the maximum representation for the COL005 column: 1 byte for the left parenthesis +1 to 11 bytes for the first element + 1 byte for the comma + 1 to 11 bytes for the first element + 1 byte for the right parenthesis ---- 25 bytes The following is two samples of data for the COL005 column: Sample data 1: (-2147483648,+2147483647) Sample data 2: (0,0) Use the Teradata SQL “HELP TYPE” command to find out the maximum length for the ARRAY data type. For example, the information for the sample PHONENUMBERS_ARY, DECIMAL_ARY, and INTEGER_ARY ARRAY data types can be as follows: |
|
help type PHONENUMBERS_ARY;
*** Help information returned. One row.
*** Total elapsed time was 1 second.
Name PHONENUMBERS_ARY
Internal Type A1
External Type CV
Max Length 47
Array(Y/N) Y
Dimensions 1
Element Type CF
UDT Name ?
Array Scope [1:2]
Total Digits ?
Fractional Digits ?
Contains Lob N
Ordering F
Ordering Category M
Ordering Routine LOCAL
Cast N
Transform Y
Method Y
Char Type 1
HELP TYPE DECIMAL_ARY;
*** Help information returned. One row.
*** Total elapsed time was 1 second.
Name DECIMAL_ARY
Internal Type A1
External Type CV
Max Length 17
Decimal Total Digits ?
Decimal Fractional Digits ?
Contains Lob N
Ordering F
Ordering Category M
Ordering Routine LOCAL
Cast N
Transform Y
Method Y
Char Type 1
Array(Y/N) Y
Dimensions 1
Element Type D
UDT Name ?
Array Scope [1:2]
HELP TYPE INTEGER_ARY;
*** Help information returned. One row.
*** Total elapsed time was 1 second.
|
|
Name INTEGER_ARY
Internal Type A1
External Type CV
Max Length 25
Decimal Total Digits ?
Decimal Fractional Digits ?
Contains Lob N
Ordering F
Ordering Category M
Ordering Routine LOCAL
Cast N
Transform Y
Method Y
Char Type 1
Array(Y/N) Y
Dimensions 1
Element Type I
UDT Name ?
Array Scope [1:2]
As indicated in the returned information from the HELP TYPE command, the maximum length for the sample PHONENUMBERS_ARY ARRAY data type is 47 bytes. The maximum length for the sample DECIMAL_ARY ARRAY data type is 17 bytes. The maximum length for the sample INTEGER_ARY ARRAY data type is 25 bytes. For more information about the external representations for the ARRAY data type, see SQL Data Types and Literals. |
Specifying Graphic Data Types |
Following is the input length and field description for the graphic data type specifications can be made in the datadesc parameter: GRAPHIC(n) VARGRAPHIC(n) LONG VARGRAPHIC Note: LONG VARGRAPHIC also implies VARGRAPHIC (16000). Range is 0 to 16000 in a 32,000-byte field. For both VARGRAPHIC and LONG VARGRAPHIC, m, a value occupying the first 2 bytes of the input data, is the length of the input in bytes, not characters. Each multibyte character set character is 2 bytes. |
Specifying Period Data Types |
A period is an anchored duration. It represents a set of contiguous time granules within that duration. A period is implemented using a Period data type. Each period consists of two elements: The element type is one of the following DateTime data types. They are DATE, TIME, and TIMESTAMP. The five PERIOD data types include: For more information on the PERIOD data type, see SQL Data Types and Literals. |
Specifying Relevant Fields |
Because all fields specified by FIELD commands are sent to Teradata Database, only specify those fields that are relevant to the Teradata MultiLoad task. |
startpos Specification |
The specified starting position: Therefore, the first data position of the input record is position 1. For more information about the CONTINUEIF and INDICATORS specifications, see the “LAYOUT” command description. |
Using ANSI/SQL DateTime Data Types |
When the DATEFORM command is used to specify ANSIDATE as the DATE data type, Teradata MultiLoad internally converts each DATE field to a CHAR(10) field. All ANSI/SQL DateTime TIME, TIMESTAMP, and INTERVAL data types must be converted to fixed-length CHAR data types to specify column/field names in a Teradata MultiLoad FIELD command. Table 41 provides the conversion specifications and format examples for each ANSI/SQL DateTime specification. |
Table 41 describes Date/Time information to consider when using the FIELD command.
For the latest conversion specifications and format examples for each ANSI/SQL DateTime specification, see the INTERVAL type description in SQL Data Types and Literals.
Data Type |
Variable Definition |
Conversion Example |
INTERVAL YEAR INTERVAL YEAR (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format (n = 2): yy Format: (n = 4): yyyy |
INTERVAL YEAR TO MONTH INTERVAL YEAR (n) TO MONTH |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n + 4) Format (n = 2): yy-mm Format: (n = 4): yyyy-mm |
INTERVAL MONTH INTERVAL MONTH (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format (n = 2): mm Format: (n = 4): mmmm |
INTERVAL DAY INTERVAL DAY (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format (n = 2): dd Format: (n = 4): dddd |
INTERVAL DAY TO HOUR INTERVAL DAY (n) TO HOUR |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n + 4) Format (n = 2): dd hh Format: (n = 4): dddd hh |
INTERVAL DAY TO MINUTE INTERVAL DAY (n) TO MINUTE |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n + 7) Format (n = 2): dd hh:mm Format: (n = 4): dddd hh:mm |
INTERVAL DAY TO SECOND INTERVAL DAY (n) TO SECOND INTERVAL DAY TO SECOND (m) INTERVAL DAY (n) TO SECOND (m) |
n = number of digits Valid values: 1-4 Default = 2 m = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(n + 10+ m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): Format: (n = 4, m = 4): hhhh:mm:ss.ssss |
INTERVAL HOUR INTERVAL HOUR (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format: (n = 2): hh Format: (n = 4): hhhh |
INTERVAL HOUR TO MINUTE INTERVAL HOUR (n) TO MINUTE |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n + 4) Format: (n = 2): hh:mm Format: (n = 4): hhhh:mm |
INTERVAL HOUR TO SECOND INTERVAL HOUR (n TO SECOND INTERVAL HOUR TO SECOND (m) INTERVAL HOUR (n) TO SECOND (m) |
n = number of digits Valid values: 1-4 Default = 2 m = number of digits after the decimal point Valid values: 0-6 Default = 6 |
CHAR(n + 7+ m + (1 if m > 0, otherwise 0)) Format: (n = 2, m = 0): Format: (n = 4, m = 4): hhhh:mm:ss.ssss |
INTERVAL MINUTE INTERVAL MINUTE (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format (n = 2): mm Format: (n = 4): mmmm |
INTERVAL MINUTE TO SECOND INTERVAL MINUTE (n) TO SECOND INTERVAL MINUTE TO SECOND (m) INTERVAL MINUTE (n) TO SECOND (m) |
n = number of digits Valid values: 1-4 Default = 2 m = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(n + 4 + m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): mm:ss Format: (n = 4, m = 4): mmmm:ss.ssss |
INTERVAL SECOND INTERVAL SECOND (n) INTERVAL SECOND (n,m) |
n = number of digits Valid values: 1-4 Default = 2 m = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(n + 1 + m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): ss Format: (n = 4, m = 4): ssss.ssss
|
TIME TIME (n) |
n = number of digits after decimal point Valid values: 0–6 Default = 6 |
CHAR(8 + n + (1 if n > 0, otherwise 0)) Format (n = 0): hh:mm:ss Format: (n = 4): hh:mm:ss.ssss |
TIMESTAMP TIMESTAMP (n) |
n = number of digits after decimal point Valid values: 0–6 Default = 6 |
CHAR(19 + n + (1 if n > 0, otherwise 0)) Format (n = 0): Format (n = 4): |
TIME WITH TIME ZONE TIME (n) WITH TIME ZONE |
n = number of digits after decimal point Valid values: 0–6 Default = 6 |
CHAR(14 + n + (1 if n > 0, otherwise 0)) Format (n = 0): Format (n = 4): |
TIMESTAMP WITH TIME ZONE TIMESTAMP (n) WITH TIME ZONE |
n = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(25 + n + (1 if n > 0, otherwise 0)) Format (n = 0): Format (n = 4): |
Example
Concatenating Special Characters
Use the following command structure to concatenate a character string such as 'A'||'%'||'@':
.LAYOUT LAY1A;
.FIELD FIELD1 * INTEGER;
.FIELD FIELD2 * INTEGER;
.FIELD FIELD3 * CHAR (1);
.FIELD FIELD4 * CHAR (2);
.FIELD FIELD5 FIELD4||'%'||'@';
Example
Multiple References to the Same Field
Consider a set of input records that contains a 25-character address field in positions 15 through 39, with the street number in the first seven positions and the street name in the last 18 positions.
The following example shows how to refer both to the address field as a whole and separately to the street name:
.FIELD ADDRESS 15 CHAR(25);
.FIELD STREET 22 CHAR(18);
Example
Invalid Concatenation
A field that has been defined by a concatenation of fields cannot be concatenated.
The following example shows the error message generated for this condition:
0009 .LAYOUT LAY3S;
0010 .FIELD FIELD1 * INTEGER;
0011 .FIELD FIELD2 * INTEGER;
0012 .FIELD FIELD3 * CHAR (1);
0013 .FIELD FIELD4 * CHAR (2);
0014 .FIELD FIELD5 FIELD3||FIELD4;
0015 .FIELD FIELD6 FIELD3||FIELD4||FIELD5;
0016 .FIELD FIELD7 FIELD3||FIELD4||FIELD5||FIELD6;
0017 .DML LABEL LABELA;
0018 INSERT TBL3S
(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7) VALUES
(:FIELD1,:FIELD2,:FIELD3,:FIELD4,:FIELD5,:FIELD6,:FIELD7);
0019 .IMPORT INFILE INPUT FREE
LAYOUT LAY3S
APPLY LABELA;
0020 .END MLOAD;
15:03:06 ‑ MON OCT 22, 1990
UTY0215 It is invalid to concatenate a field that
has been defined by a concatenation of fields.
15:03:06 ‑ MON OCT 22, 1990
UTY1414 The previous error occurred on statement
number '15'.
Example
Using the GRAPHIC Data Types
The following example shows how to use the GRAPHIC data types in support of kanji or multibyte character data. The FIELD description of the data set or file can contain GRAPHIC data types.
.LAYOUT KANJIDATA;
.FIELD EMPNO * SMALLINT;
.FIELD LASTNAME* GRAPHIC(30);
.FILLER FIRSTNAME * GRAPHIC(30);
.FIELD JOBTITLE* VARGRAPHIC(30);
Example
Converting Data
The following valid example converts numeric data to different types of numeric data:
Assuming you have a target table:
CREATE TABLE MLTBL,FALLBACK
F1 INTEGER,
ABYTEINT BYTEINT,
ASMALLINT SMALLINT,
AINTEGER INTEGER,
ABIGINT BIGINT,
AFLOAT FLOAT)
PRIMARY INDEX (F1);
You can specify FIELD command for the input records:
FIELD FF1 * INTEGER;
FIELD FF2 * decimal(2,1);
FIELD FF3 * decimal(4,1);
FIELD FF4 * decimal(9,2);
FIELD FF5 * decimal(18,2);
FIELD FF6 * decimal(18,2);
The decimal data type will be converted to BYTEINT, SMALLINT, etc.
Purpose
The FILLER command describes a named or unnamed field as filler, which is not sent to Teradata Database.
Syntax
where:
Syntax Element |
Description |
datadesc |
type and length of data in the field |
fieldname |
optional name of an input record field The fieldname specification can be referenced by: A fieldname specification must obey the same construction rules as Teradata SQL column names. |
startpos |
starting position of the specified field in the data records of an external data source The startpos can be defined as: |
Usage Notes
Table 42 describes the things to consider when using the FILLER command.
Topic |
Usage Notes |
Intermixing Commands |
One or more FILLER commands can be intermixed with TABLE and FIELD commands. All of these commands must follow a LAYOUT command. |
Specifying Graphic Data Types |
The following lists the input length and field description for the graphic data type specifications that can be made in the datadesc parameter: GRAPHIC(n) LONG VARGRAPHIC Note: LONG VARGRAPHIC also implies VARGRAPHIC (16000). Range is 0 to 16000 in a 32,000-byte field. |
startpos Specification |
The specified starting position: Therefore, the first data position of the input record is position 1. For more information about the CONTINUEIF and INDICATORS specifications, see the LAYOUT command description. |
Example
Concatenating a Field with a Filler
Use the following command structure to concatenate a field with a filler:
.LAYOUT LAY1A;
.FIELD F1 * CHAR(2);
.FILLER F2 * CHAR(1);
.FIELD F3 F1||F2
Example
Using the GRAPHIC Data Types
The following example shows how to use the GRAPHIC data types in support of kanji or multibyte character data. The FILLER statement describing the input data set or file can contain GRAPHIC data types.
.LAYOUT KANJIDATA;
.FIELD EMPNO * SMALLINT;
.FIELD LASTNAME * GRAPHIC(30);
.FILLER FIRSTNAME * GRAPHIC(30);
.FIELD JOBTITLE * VARGRAPHIC(30);
Purpose
The IF, ELSE, and ENDIF commands provide conditional control of execution processes.
Syntax
Usage Notes
Table 43 describes the things to consider when using the IF, ELSE, and ENDIF commands.
Topic |
Usage Notes |
ELSE Clause |
Use the optional ELSE clause to execute commands when the condition is evaluated as false. |
Nesting IF Commands |
Teradata MultiLoad supports up to 100 nested IF commands. |
Numeric Results of the Conditional Expression |
The conditional expression is an expression that can be evaluated as either true or false. When evaluation of the expression returns a numeric result: |
Variable Substitutions |
Any ELSE or ENDIF commands must be present in their entirety, and cannot be composed simply of variables in need of substitution. |
Commands and statements following an IF, ELSE, or ENDIF structure that are not executed are not parsed and do not have their variables substituted. |
|
Variables in the IF Expression |
The conditional expression can be either user-defined variables or predefined system variables. |
Example
Teradata MultiLoad is case sensitive when comparing &SYS system variables. In this example, the RUN FILE command does not execute because the substituted values returned are all capitals:
0003 .IF '&SYSDAY' = 'Fri' THEN;
14:10:28 - FRI MAY 09, 1993
UTY2402 Previous statement modified to:
0004 .IF 'FRI' = 'Fri' THEN;
0005 .RUN FILE UTNTS38;
0006 .ENDIF;
Always consider this factor when creating a script to force the execution of a predetermined sequence of events. If 'FRI' is substituted in line 0003, the compare would work and the RUN FILE command would execute.
Example
In the following example, the user has created the table named &TABLE and a variable named CREATERC, into which is set the system return code resulting from the execution of the CREATE TABLE statement:
.SET CREATERC TO &SYSRC;
.IF &CREATERC = 3803 /* Table &TABLE exists */ THEN;
.RUN FILE RUN01;
.ELSE;
.IF &CREATERC <> 0 THEN;
.LOGOFF &CREATRC;
.ENDIF;
.ENDIF;
If the table name has not already been used, and the return code is not zero, the return code evaluates to an error condition and the job logs off with the error code displayed.
Purpose
The IMPORT command specifies a source for data input.
Syntax
The IMPORT command syntax depends on whether Teradata MultiLoad is running on a mainframe-attached or network-attached client system. Several of the syntax elements are common to both configurations, while others are specific.
For Mainframe-Attached Client Systems
For Network-Attached Client Systems
where:
Syntax Element |
Description |
APPLY label |
Error treatment options specified by a previous DML LABEL command for subsequent INSERT, UPDATE, or DELETE statements |
AXSMOD name |
Name of the access module file to be used to import data. These access modules include: For more information, see Teradata Tools and Utilities Access Module Reference (B035‑2425). |
|
A personal shared library file name can be used if custom access module is used. The AXSMOD option is not required for importing disk files on either network-attached or mainframe-attached client systems, or magnetic tape files on mainframe-attached client systems. It is required for importing magnetic tape and other types of files on network-attached client systems. To specify the OLE DB Access Module, Named Pipes Access Module, or the WebSphere MQ Access Module for specific platforms, see Teradata Tools and Utilities Access Module Reference. |
'c' |
Optional specification of the delimiter character that separates fields in the variable-length text records of the input data source. If a 'c' specification is not used, the default is the pipe character (|). When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the effective delimiter from the script character encoding to the client character encoding before separating fields with it. For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems. Network Example: If the client character set is UTF-16, the script character set is UTF-8 and the following command is given: …FORMAT VARTEXT '-'…
Teradata MultiLoad translates '-' from UTF-8 to UTF-16, and then separates the fields in the record according to the UTF-16 form of '-'. Mainframe Example: If the client character set is UTF-8, the script character set is Teradata EBCDIC, and the following command is given: …FORMAT VARTEXT '6A'xc…
Teradata MultiLoad interprets x’6A’ according to Teradata EBCDIC, translates it to the corresponding Unicode code point (U+007C “VERTICAL LINE”), and uses the UTF-8 encoding scheme of U+007C, 0x7C (which is '|' in 7-bit ASCII) as the delimiter character for the record. Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters which might be required in the job script. See International Character Set Support for details. For example, the code point of '|' is x'4F' in most IBM EBCDIC code pages. If '|' is specified as the delimiter in the script or leave the delimiter to default in a system environment using that type of IBM EBCDIC code page and UTF-8 data uses x'7C' (which is '|' in Unicode) as the delimiter, the job will run into errors because: |
DISPLAY ERRORS |
Optional keyword specification that writes input data records that produce errors to the standard error file. |
DISPLAY ERRORS EFILE efilename |
Optional keyword specification that writes input data records that produce errors to the user specified error file. If the user doesn't specify the error file name, the default error destination is the standard error file. |
FOR n |
Number of records, as an integer, starting at recordm, to be processed. If a FOR n or a THRU k specification is not used, Teradata MultiLoad continues processing through the last record obtained from the data source. Note: When “FOR 0” is used, Teradata MultiLoad defaults FROM as 2 and THRU as 1. A warning is issued. This makes Teradata Multi load only the 2nd record of the data file to the target table. |
FORMAT… |
Format of the input record, where: For client session character sets other than UTF16, the end-of-record marker byte sequence is: For the UTF16 client session character set (in which each character is encoded in two bytes), the end-of-record marker byte sequence is: Note: TEXT format should only be specified for character data like CHAR or ANSIDATE. Do not specify TEXT format for binary data, such as, INTEGER, BYTEINT, PERIOD, and other binary data. Depending on the actual byte values of the binary data, unexpected results may occur. Note: INDICATORS mode is not recommended when using TEXT record format. Please use UNFORMATTED record format instead. Note: When using UNFORMAT formatting in z/OS, ensure that the data stream and data source are consistent with the layout defined in the utility script. Discrepancies in the length of the data stream could result in data corruption. If a FORMAT option is not specified, the default format is FASTLOAD. |
FREE |
Deallocation of the tape input device specified by ddname when the import operation completes on mainframe-attached client systems. When de-allocated, any attempt to open the input device, either in the same Teradata MultiLoad task or in another task within the same script, produces an undefined ddname error. The default is to not deallocate the device. |
FROM m |
Logical record number, as an integer, of the record in the identified data source where processing is to begin. If a FROM m specification is not specified, Teradata MultiLoad begins processing with the first record received from the data source. |
HOLD |
Default condition to not deallocate the input tape device specified by ddname when the import operation completes on mainframe-attached client systems. Instead, the HOLD specification deallocates the device when the entire Teradata MultiLoad operation completes. |
INFILE ddname |
External data source that contains the input records on mainframe-attached client z/OS client systems. In z/OS, this is a DDNAME. If ddname is specified, Teradata MultiLoad reads data records from the specified source. If modulename is also specified, Teradata MultiLoad passes the records it reads to the specified module. The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set. A DDNAME must obey the same construction rules as Teradata SQL column names except that: If the DDNAME represents a data source on magnetic tape, the tape may be either labeled or non-labeled, as supported by the operating system. |
INFILE filename |
Fully qualified UNIX or Windows path name for an input file on network-attached client systems If the path name has embedded white space characters, enclose the entire path name in single or double quotes. If INFILE filename is specified, Teradata MultiLoad reads data from the specified source. If INMOD modulename is also specified, Teradata MultiLoad passes the data it reads to the specified module. |
init-string |
Optional initialization string for the access module The initialization string can contain double quotes, but not single quotes. |
INMOD modulename |
The modulename specification must obey the same construction rules as Teradata SQL column names except that on mainframe-attached client systems: The modulename specification must obey the applicable rules of the external system. Note: On some versions of UNIX operating systems, a ./ prefix character may have to be added to the modulename specification if the module is in the current directory. Note: On Windows platforms, if the INMOD module output messages are to stdout, the character set that INMOD uses is independent of the character set that MultiLoad uses, the display on stdout can be of mixed character sets. For example, IMMOD can output messages in ASCII and Multi load can output messages in UTF-16. |
LAYOUT layoutname |
Layout of the input record, as specified by a previous LAYOUT command. |
NOSTOP |
Optional keyword specification that inhibits the Teradata MultiLoad termination in response to an error condition associated with a variable-length text record. |
QUOTE |
The QUOTE option allows the user to specify whether input data values will never be quoted (QUOTE NO), optionally be quoted (QUOTE OPTIONAL), or always be quoted (QUOTE YES). If data values are to be optionally or always quoted, the user can specify the enclosing open and close quote, consisting of one or more characters. The default is the quotation mark (") for both open quote and close quote. The open quote ('q') and close quote ('r') can be different. If only 'q' is specified, it is assumed to be both the open quote and close quote. If open quote, close quote, or both include apostrophe(s), any apostrophes must be doubled in the QUOTE specification. |
THRU k |
Logical record number, as an integer, of the record in the identified data source where processing is to end. If a THRU k or a FOR n specification is not used, Teradata MultiLoad continues processing through the last record obtained from the data source. |
TRIM |
The TRIM option allows the user to request that no trimming is to be done, or that leading, trailing, or both leading and trailing pad characters are to be trimmed. The default pad character is blank (space). |
USING (parms) |
Character string containing whatever parameters are to be passed to the corresponding user exit routine: |
WHERE condition |
Condition that determines whether the indicated label options are applied to the records and sent to Teradata Database per subsequent INSERT, UPDATE, or DELETE statements where: The condition specification can reference: If a WHERE condition specification is not used, the default condition is true/yes. When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the string constants specified and the import data referenced in the condition to the same character set before evaluating the condition. For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems. Network Example: If the client character set is UTF-16, the script character set is UTF-8, and the following command is given: …APPLY lable1 WHERE C1 = 'INSERT';
Teradata MultiLoad translates the data in the C1 field to the UTF-8 form and compares it with the UTF-8 form of 'INSERT' to obtain the evaluation result. Mainframe Example: If the client character set is UTF-8, the script character set is Teradata EBCDIC, and the following command is given: …APPLY lable2 WHERE C2 = 'DELETE';
Teradata MultiLoad translates the data in the C2 field from the UTF-8 form to the Teradata EBCDIC form and compares it with the Teradata EBCDIC form of 'DELETE' to obtain the evaluation result. Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters which might be required in the job script. See International Character Set Support for details. |
Usage Notes
Table 44 describes the things to consider when using the IMPORT command.
Topic |
Usage Notes |
Data Type Specifications |
When using the VARTEXT specification, VARCHAR, VARBYTE and LONG VARCHAR are the only valid data type specifications which can be used in the Teradata MultiLoad layout FIELD and FILLER commands. |
Error Record Handling |
When Teradata MultiLoad encounters an error condition in an input record, it normally discards the record and terminates. When loading variable-length text records, either or both of these functions can be inhibited by specifying the error-handling options: By specifying both options and redirecting STDERR to a file location instead of the terminal screen, the Teradata MultiLoad job runs to completion and saves all the error records. Then they can be manually modified and loaded into the table. |
FREE/HOLD Option When Running Under z/OS |
The disposition of the output device specified in the JCL must be KEEP, not PASS, for the FREE/HOLD option to work in Teradata MultiLoad tasks running under z/OS. |
Import Task Command Restrictions |
The combined number of Teradata SQL statements under the DML commands cannot exceed 100 within a single Teradata MultiLoad import task. Sending an excessive number of statements to Teradata Database produces an error message indicating that there are too many DML steps for one Teradata MultiLoad import task. For an import task, a candidate statement or group of statements is applied if no condition is specified, or if the specified condition is true. Note, however, that for an import task, the only DML statements that are candidates for application by an IMPORT command are those within the scope of DML commands whose labels appear in one or more of the IMPORT command APPLY clauses. (The referenced DML commands and their following DML statements must appear between the BEGIN MLOAD command that defines the import task and the referencing IMPORT commands.) |
Input Record Requirements |
The total number of fields in each input record must be equal to or greater than the number of fields described in the Teradata MultiLoad layout FIELD and FILLER commands. If it is less, Teradata MultiLoad generates an error message. If it is more, Teradata Database ignores the extra fields. Note that a delimiter character in the last field of a record is optional. |
Null Fields |
Two consecutive delimiter characters direct Teradata MultiLoad to null the field corresponding to the one right after the first delimiter character. Also, if the last character in a record is a delimiter character, and yet there was at least one more field to be processed, then Teradata MultiLoad nulls the field corresponding to the next one to be processed, as defined in the layout FIELD and FILLER commands. |
Multiple APPLY Clauses |
In an import task, multiple APPLY clauses can be applied to the same data record in either of two ways. This features allows the same data record to be applied to different tables under the same or different conditions. First, if an APPLY clause refers to a label whose scope includes multiple DML statements, each of these statements is applied to the same data record under the same condition specified in the clause. Second, if multiple APPLY clauses are used, each can refer to the label specification of a different DML statement or group of statements. Each label specification is applied to the same data record under the condition specified in the respective clause. |
Primary Indexes and Partitioning Column Sets |
IMPORT tasks require that all values of the primary index column be set and all values of the partitioning column be set for deletes and updates. IMPORT tasks do not support updates of the partitioning column set. IMPORT tasks do not support primary index updates. |
Record Length Validation |
By default, Teradata MultiLoad does not compare the actual record length of the import data with the record length indicated by the layout specifications for the job. If they are not the same, the default behavior of Teradata MultiLoad depends on whether the actual import data record length is less than or greater than the record length indicated by the layout specifications: To change the default behavior and enforce a record-length validation check, use a MATCHLEN=on entry in the Teradata MultiLoad configuration file before invoking Teradata MultiLoad. In this case, Teradata MultiLoad terminates with an error message whenever the actual and specified record lengths are different. For information about using the Teradata MultiLoad configuration file, see “Teradata MultiLoad Configuration File” on page 46. |
VARTEXT Records |
When VARTEXT is specified, Teradata MultiLoad assumes that the input data is variable-length text fields separated by up to 10 field delimiter characters. The utility parses each input data record on a field-by-field basis, and creates a VARCHAR field for each input text field. |
Purpose
INSERT is a Teradata SQL statement that adds new rows to a table or view.
Syntax
where:
Syntax Element |
Description |
.* |
Default VALUES clause for the insert operation When the .* characters is used as a suffix with the tname specification, Teradata MultiLoad replaces these characters with a default VALUES clause before executing the command. Note: When using the .* default, tname must specify a table name and not a view name. The default option is most useful when used with the TABLE command, which builds the layout corresponding to the same table. |
cname |
Column of the specified table that is to receive the value from a field of matching input records If cname specifications are not entered, Teradata MultiLoad uses the column identifiers as they were defined by the CREATE TABLE statement. The value is identified by the corresponding entry in the fieldname list. |
fieldname |
Field of an input record, whose value is given to a column of the tname table that is identified by the corresponding cname specification of this command Alternatively, each fieldname clause may instead be an expression that includes one or more actual fieldname terms. |
tname |
Table or view that is to receive rows from Teradata MultiLoad input data records The tname specification must have been previously identified as tname1 in the BEGIN MLOAD command. |
VALUES |
The VALUES clause is of the form: VALUES (:column1, :column2,...)
where: :column1, :column2,… are the names of the columns from tname in the order in which they were defined by the CREATE TABLE statement. |
Usage Notes
Table 45 describes the things to consider when using the INSERT statement.
Topic |
Usage Notes |
ANSI/SQL DateTime Specifications |
The ANSI/SQL DATE, TIME, TIMESTAMP, and INTERVAL DateTime data types in Teradata SQL CREATE TABLE statements can be as column/field modifiers in INSERT statements. You must convert them to fixed-length CHAR data types when specifying the column/field names in the FIELD command. |
Object Restriction |
Only one tname object for an INSERT statement can be specified, and it must have been previously identified as a target object in a BEGIN MLOAD command. If the tname object is a view, it must not specify a join. Teradata MultiLoad operates only on single-table commands, so your INSERT statements must not contain joins. |
Required Privilege |
To use the INSERT statement, the INSERT privilege must be on the tname table or view. |
Specifying the Applicable DML Statements |
One way of specifying the applicable DML statements is to relate each field name to the name of the column to which the field’s data is applied. Another way tells Teradata MultiLoad to apply the first nonfiller field of a record that is sent to Teradata Database to the first defined column of the affected table, the second nonfiller field to the second column, and so on. In either case, a value for every column must be specified, either explicitly or by default. |
Using Unicode Data |
Do not use the tname.* version of an INSERT statement when using Unicode data from: These functions return the field names from the referenced tables and return byte/character counts that Teradata MultiLoad uses internally to construct the USING clause for the subsequent load operation. The internally generated USING clauses do not properly reflect the structure of the input data stream because of the byte-count and character-count conversions that occur when importing and exporting CHAR and VARCHAR data between the client system and Teradata Database. |
Example
The following examples show three ways to specify the relationship between the fields of input data records and the columns of the target table, using targetable as the target object name.
.LAYOUT lname;
.TABLE targetable;
.DML LABEL label;
INSERT INTO targetable .*;
.LAYOUT lname;
.FIELD first 1 somedatatype;
.FIELD f2nd * anydatatype;
.
.
.
.FIELD flast * datatype;
.DML LABEL label;
INSERT INTO targetable (col1, col2, ... colast)
VALUES (:f2nd, :first, ... :flast);
.LAYOUT lname;
.FIELD first 1 somedatatype;
.FIELD f2nd * anydatatype;
.
.
.
.FIELD flast * datatype;
.DML LABEL label;
INSERT INTO targetable VALUES (:first, :f2nd, ... :flast);
Example
The following example supposes an input data source that contains a series of 10- to 40-byte records. Each record contains the primary key value (EmpNum) of a row that is to be inserted successively into the Employee table whose columns are EmpNo, Name, and Salary. The example assumes that the current default database is Personnel, the database containing the Employee table.
.LAYOUT Layoutname;
.FIELD EmpNum 1 INTEGER;
.FIELD Name * (VARCHAR (30));
.FIELD Sal * (DECIMAL (7,2));
.DML LABEL DMLlabelname;
INSERT Employee (EmpNo, Name, Salary) VALUES (:EmpNum, :Name, :Sal);
Purpose
The LAYOUT command, used with an immediately following sequence of FIELD, FILLER, and TABLE commands, specifies the layout of the input data records.
Syntax
where:
Syntax Element |
Description |
||
CONTINUEIF condition |
Conditional phrase in which condition is of the form: position = value where The condition specified as position = value is case sensitive. Always specify the correct character case for this parameter. If the condition phrase is true, then Teradata MultiLoad forms a single record to be sent to Teradata Database by concatenating the next input record at the end of the current record. (The current record is the one most recently obtained from the external data source.) If the condition is false, then Teradata MultiLoad sends the current input record to Teradata Database either by itself or as the last of a sequence of concatenated records. Regardless of whether the condition evaluates to true or false, Teradata MultiLoad removes the tested string (the continuation indicator field) from each record. All CONTINUEIF processing necessary to construct a complete record is done before any other processing of the record. |
||
CONTINUEIF condition |
When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the specified value, which is either a character constant or a string constant, from the script character encoding to the client character encoding before evaluating the condition. Teradata MultiLoad uses the length of the constant in the client character encoding as the length of the continuation indicator field. For example, the job script must be in Teradata EBCDIC when using the UTF-8client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems. Note: Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters which might be required in the job script. See International Character Set Support for details. |
||
INDICATORS |
Condition that the data is in the indicator mode When the INDICATORS specification is used, Teradata MultiLoad sends all of the FIELD commands, including redefines, to Teradata Database.
Conversely, if INDICATORS is not specified and the data file contains indicator bytes in each record, the target table also is corrupted. Always make sure that INDICATORS specifications match the mode of the data being sent to Teradata Database. Note: INDICATORS processing is done only after any CONTINUEIF processing is completed for a record. Note: If IS NULL or IS NOT NULL is specified in the APPLY clause, INDICATORS specification is required for all the data formats except VARTEXT format. |
||
layoutname |
Name assigned to the layout for reference by one or more subsequent IMPORT commands A layoutname must obey the same construction rules as Teradata SQL column names. |
Usage Notes
Table 46 describes the things to consider when using the LAYOUT command.
Topic |
Usage Notes |
CONTINUEIF and INDICATORS Processing |
When both CONTINUEIF and INDICATORS are specified: |
Required Following Commands |
A LAYOUT command must be immediately followed by a combination of FIELD, FILLER, or TABLE commands. This sequence of commands, referenced by the layoutname, may describe one or more record formats contained in one or more client data sources. (See the redefinition options for FIELD, FILLER, and TABLE commands). The LAYOUT command sequence is terminated by the first subsequent command that is not a FIELD, FILLER, or TABLE command. |
Using the Same LAYOUT Command in More Than One Teradata MultiLoad Task |
Reference the same layoutname specification in more than one Teradata MultiLoad task, provided that: |
Purpose
Supplies parameters to the LOGMECH command beyond those needed by the logon mechanism, such as user ID and password, to successfully authenticate the user. The LOGDATA command is optional. Whether or not parameters are supplied and the values and types of parameters depend on the selected logon method.
LOGDATA is only available on network-based platforms.
Note: To prevent the password from appearing in the script, use Teradata Wallet. Refer to Security Administration and the appropriate installation guide for more information.
Syntax
where:
Syntax Element |
Description |
logdata_string |
Parameters for the logon mechanism specified using “LOGMECH” on page 163. For information about the logon parameters for supported mechanisms, see Security Administration. The string is limited to 64 KB and must be in the session character set. To specify a string containing white space or other special characters, enclose the data string in single quotes. |
Usage Notes
For more information about logon security, see Security Administration.
Example
If used, the LOGDATA and LOGMECH commands must precede the LOGON command. The commands themselves may occur in any order.
The following example demonstrates using the LOGDATA, LOGMECH, and LOGON commands in combination to specify the Kerberos logon authentication method and associated parameters:
.logmech KRB5;
.logdata joe@domain1@@mypassword;
.logon cs4400s3;
Purpose
Identifies the appropriate logon mechanism by name. If the mechanism specified requires parameters other than user ID and password for authentication, the LOGDATA command provides these parameters. The LOGMECH command is optional and available only on network-attached systems.
Syntax
where:
Syntax Element |
Description |
logmech_name |
Logon mechanism For a discussion of supported mechanisms, see Security Administration. The name is limited to 8 bytes; it is not case-sensitive. |
Usage Notes
Every session to be connected requires a mechanism name. If none is supplied, a default mechanism can be used instead, as defined on either the server or client system in an XML‑based configuration file.
For more information about logon security, see Security Administration.
Example
If used, the LOGDATA and LOGMECH commands must precede the LOGON command. The commands themselves may occur in any order.
The following example demonstrates using the LOGDATA, LOGMECH, and LOGON commands in combination to specify the Windows logon authentication method and associated parameters:
.logmech NTLM;
.logdata joe@domain1@@mypassword;
.logon cs4400s3;
Purpose
The LOGOFF command disconnects all active sessions and terminates Teradata MultiLoad on the client system.
Syntax
where:
Syntax Element |
Description |
retcode |
Completion code returned to the client operating system If a value for retcode is not specified, Teradata MultiLoad returns the appropriate terminating return code. |
Usage Notes
Table 47 describes the things to consider when using the LOGOFF command.
Topic |
Usage Notes |
Optional Completion Code |
Specify the optional completion code value, retcode, as a conditional or an arithmetic expression, evaluated to a single integer. The LOGOFF command is processed when the highest return code reached prior to the LOGOFF command is no more than 4 (warning). Any higher return code would have already terminated the Teradata MultiLoad job. If the LOGOFF command is processed, Teradata MultiLoad returns the higher of: For example, if the retcode value was specified as 2, but a warning had occurred earlier in the run, Teradata MultiLoad would return 4, not 2. If a serious error terminates the program before the LOGOFF command is processed, the return code output is the value generated by the error condition rather than the retcode value specified as a LOGOFF command option. |
Termination Return Codes |
When a Teradata MultiLoad job terminates and an optional retcode value has not been specified, the utility returns a code indicating the way the job completed: |
When Permitted |
The LOGOFF command is permitted at any point in the input script. It logs users off immediately. |
Example
Assume that:
If BADRC is set to &SYSRC after the failed Teradata SQL statement, you can use the following command to terminate the Teradata MultiLoad utility and return the unacceptable code to the client system:
.LOGOFF &BADRC;
Executing this command also drops the restart log table. If execution is terminated before the LOGOFF command is encountered, the restart log table is not dropped, so as to support a restart at a later time.
Purpose
The LOGON command establishes a Teradata SQL session with Teradata Database. The ACCEPT and SET commands are valid commands preceding LOGON and LOGTABLE commands.
Note: To prevent the password from appearing in the script, use Teradata Wallet. Refer to Security Administration and the appropriate installation guide for more information.
Syntax
Standard LOGON Syntax
On z/OS, with the use of the User Logon Exit routine in TDP, the user name is not required. For more information, see Teradata Director Program Reference.
Single Sign-On LOGON Syntax
Note: When logon encryption is enabled on the gateway, single sign-on is disabled on the client and standard logon syntax is used instead.
Note: Use of the period preceding the LOGON command is optional.
where:
Syntax Element |
Description |
acctid |
Account identifier of up to 30 characters associated with the user name If an acctid is not specified, Teradata MultiLoad uses the default identifier defined when the user was created. |
password |
Password associated with the user name Note: Passwords that contain special characters must be enclosed in double quotes. The strings in double quotes will not be touched in the LOGON command. |
tdpid |
Optional character string that identifies the name of a TDP If the tdpid is not specified, Teradata MultiLoad uses the default TDP established by the system administrator. For mainframe-attached systems, the tdpid string must be in the form: TDPn where n is the TDP identifier. |
username |
User identifier |
Usage Notes
Table 48 describes the things to consider when using the LOGON command.
Topic |
Usage Notes |
Logon Parameters |
For standard logon, the parameters (tdpid, username, password, and acctid) are used in all sessions established with Teradata Database. The LOGON command may occur only once. For single sign-on, if the Gateway to Teradata Database is configured to use single sign-on (SSO), and are already logged on to a Teradata client machine, then the machine name, user name, and password are not required in the LOGON command. The user name and password combination which were specified logging onto a Teradata client machine are authenticated with network security for a single sign-on such that valid Teradata users will be permitted to log on to Teradata Database. The use of SSO is strictly optional, unless the Gateway has been configured to accept only SSO-style logons. Note: On z/OS, with the use of User Logon Exit routine in TDP, userid is not required. Otherwise, userid is required. See Teradata Director Program Reference for more information. To connect to a Teradata Database system other than the one currently logged onto, the tdpid must be included in the LOGON command. If the tdpid is not specified, the default contained in clispb.dat will be used. Refer to Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems for information about setting defaults. To be interpreted correctly, the tdpid must be followed by the slash separator (‘/’), to distinguish the tdpid from a Teradata Database user name. For example, to connect to serverxyz, enter one of the following: .LOGON serverxyz/; .LOGON serverxyz/,,'acctinfo'; If an account ID is to be used, the optional account ID must be specified in the LOGON command. |
Using LOGON with the LOGTABLE Command |
Both the LOGON and LOGTABLE commands are required. LOGON and LOGTABLE commands may appear in any order, but must precede other commands except RUN commands used to identify the file containing the LOGON command. If the LOGON command is entered first, Teradata MultiLoad warns that the LOGTABLE command also is required. Note: When Teradata MultiLoad attempts to connect the Main SQL session the first time and the Teradata Database is down, Teradata MultiLoad displays an error message and terminates. Note: When Teradata MultiLoad attempts to connect the Auxiliary SQL session, or the data sessions and the Teradata Database is down. Teradata MultiLoad tries to connect 16 times; if the Teradata Database is still down, Teradata MultiLoad displays an error message and terminates. |
Example
The following example presents both the LOGON command and LOGTABLE command as they typically occur:
.logtable logtable001;
.logon tdpx/me,paswd;
Purpose
The LOGTABLE command specifies a restart log table for the Teradata MultiLoad checkpoint information. Teradata MultiLoad uses the information in the restart log table to restart jobs that are halted because of a Teradata Database or client system failure.
The ACCEPT and SET commands are valid commands preceding LOGON and LOGTABLE commands.
Syntax
where:
Syntax Element |
Description |
dbname |
Name of the database under which the log table exists The default is the database name associated with the user name specified in the LOGON command. Teradata MultiLoad searches for the tname table in that database unless another database name is specified in this option. |
tname |
Name of the restart log table |
Usage Notes
Table 49 describes the things to consider when using the LOGTABLE command.
Topic |
Usage Notes |
Changing the dbname Specification |
The LOGTABLE dbname option must be used to change the dbname specification for a Teradata MultiLoad operation. A subsequent Teradata SQL DATABASE statement, which must appear after the LOGTABLE/LOGON command, to change the dbname specification cannot be used. |
Dropping the Restart Log Table |
In the case of a paused Teradata MultiLoad job, do not drop the restart log table until Teradata MultiLoad completes the import or delete task. For more information, see “Implications of Dropping Required Teradata MultiLoad-Created Tables” on page 41. |
Maintaining the Restart Log Table |
Teradata MultiLoad automatically maintains the restart log table. If the table is manipulated in any way, the restart capability is invalidated. The only valid user maintenance function is to drop the restart log table. Never delete rows from the table. |
The Restart Log Table |
The table specified as the Teradata MultiLoad restart log table does not have to be fully qualified. |
Sharing the Restart Log Table |
Do not share the restart log table between two or more Teradata MultiLoad jobs. Each Teradata MultiLoad job must have its own restart log table to ensure that the job runs correctly. If a distinct restart log table is not used for each Teradata MultiLoad job, the results are unexpected. One or more of the affected jobs may not be able to be restarted. |
Specifying a New or Existing Table |
If a table that does not exist is specified, Teradata MultiLoad creates the table and uses it as the restart log during this invocation of the utility. If a table that already exists is specified, then Teradata MultiLoad checks the table to determine whether the current invocation of the utility is a restart operation. |
Using LOGTABLE with the LOGON command |
Both the LOGTABLE and LOGON command are required. LOGTABLE and LOGON commands may appear in any order, but must precede other commands except RUN commands used to identify the file containing the LOGON command. If the LOGON command is entered first, Teradata MultiLoad warns that the LOGTABLE command is also required. |
The following example presents both the LOGTABLE and LOGON commands as they typically occur.
.logtable Mine.Logtable001;
.logon tdpx/me,paswd;
Purpose
The PAUSE ACQUISITION command pauses the Teradata MultiLoad job during the acquisition phase of an import task. (The PAUSE ACQUISITION command cannot be used in a Teradata MultiLoad delete task.)
Syntax
Usage Notes
Table 50 describes the things to consider when using the PAUSE ACQUISITION command.
Topic |
Usage Notes |
Command Frequency and Placement |
The Teradata MultiLoad job can use more than one PAUSE ACQUISITION command, as long as each one is associated with a different import task. Only one PAUSE ACQUISITION command can be used in each import task. In a Teradata MultiLoad job script, PAUSE ACQUISITION commands must appear: If a PAUSE ACQUISITION command appears before the first IMPORT command, then Teradata MultiLoad pauses the job before starting the acquisition phase. If a PAUSE ACQUISITION command appears after the last IMPORT command, then Teradata MultiLoad pauses the job before starting the application phase. If a PAUSE ACQUISITION command appears between two IMPORT commands, then Teradata MultiLoad begins the acquisition phase, processes the first IMPORT command, and pauses the job before processing the second IMPORT command. |
Operational Requirements |
PAUSE ACQUISITION commands can be used to separate a Teradata MultiLoad job into two or more distinct operations provided that it has either: or |
Pause Conditions |
When a Teradata MultiLoad job pauses, the utility: |
Restarting the Job |
To restart a paused Teradata MultiLoad job, remove the associated PAUSE ACQUISITION statement from the Teradata MultiLoad job script and resubmit the job. Teradata MultiLoad then resumes processing acquisition phase IMPORT commands, and either: or |
Purpose
When a Teradata MultiLoad task has been suspended or aborted before the end of the application phase, the RELEASE MLOAD statement removes the access locks from the target tables in Teradata Database and inhibits any attempts to restart the Teradata MultiLoad utility.
Note: RELEASE MLOAD is a Teradata SQL statement that is recognized by BTEQ and Teradata MultiLoad. RELEASE MLOAD may be used in a Teradata MultiLoad job script or in an interactive Teradata MultiLoad session. It may also be used in BTEQ. Log on to BTEQ to enter the RELEASE MLOAD statement. Do not use a leading period (.) when using the RELEASE MLOAD statement in BTEQ.
Syntax
where:
Syntax Element |
Description |
dbname |
Name of the database where the target table to release exists. The default is the database name associated with the user name specified in the BTEQ LOGON command. BTEQ searches for the tablename table in that database unless this option specifies another database name. |
IN APPLY |
Teradata MultiLoad task that was in the application phase when it was suspended Note: The IN APPLY option cannot be used to release the locks that were placed on the Teradata MultiLoad target tables during the acquisition phase. In this case, use the RELEASE MLOAD statement without the IN APPLY option. |
tablename |
Name of the Teradata MultiLoad target table |
Note: The RELEASE MLOAD statement frees the target tables, but does not delete the error tables, the work tables, or the restart log table. They remain in the database, and must be dropped manually to free up the space and avoid conflicts when resubmitting the Teradata MultiLoad job.
Usage Notes
Table 51 describes the things to consider when using the RELEASE MLOAD statement.
Topic |
Usage Notes |
Locks |
To release the target tables, the Teradata MultiLoad release function must first obtain an exclusive lock on each specified table. This is not possible, and the RELEASE MLOAD statement will fail if either a database lock or a Teradata MultiLoad write lock has been placed on any of the specified tables. Teradata MultiLoad normally places write locks on the target tables: If the RELEASE MLOAD statement is executed before the Teradata MultiLoad task reaches these critical points, the release function completes before the utility can place write locks on the target tables. |
Messages Returned |
A release completed message indicates that all of the specified tables were releasable and the release function completed without error. If the release function encounters a table that cannot be released, it terminates with an error message identifying the table and the reason it could not be released. Acquisition phase error conditions include: Application phase error conditions include all of the acquisition phase error conditions plus: In this case, either: |
Releasing Application Locks |
The IN APPLY option of the RELEASE MLOAD statement releases the application locks on the target tables that satisfy one of the following conditions: If none of these conditions is true, and the table has no permanent journals, then Teradata MultiLoad changes the application lock to a restoration lock that prevents Teradata MultiLoad from restarting and allows the following table accesses: If permanent journals are defined on the table, the only option is to drop the target table. If the target table belongs to a replication group and change data capture is active (for example, the replication group status is not Defined nor Terminated), then the following steps must be completed to drop the table: 1 Put the replication group in Suspended status. 2 Use the ALTER REPLICATION GROUP statement to remove the table from the replication group. 3 Drop the table. |
Required Privilege |
Only the owner of the database or a user with one of the following privileges on the specified tables can use the RELEASE MLOAD statement: |
Table Requirements |
All of the tables that specified must have been involved in a Teradata MultiLoad task. If a specified table is not involved in a Teradata MultiLoad task, Teradata Database rejects the RELEASE MLOAD statement. |
Purpose
The ROUTE MESSAGES command specifies alternate destinations for Teradata MultiLoad utility output messages.
Syntax
where:
Syntax Element |
Description |
ECHO |
Additional destination, with a fileid specification For example, use the ECHO keyword to specify that messages be captured in a file (fileid2) while still being written to the terminal. Note: The ECHO OFF specification cancels the additional file specification of a previously established ECHO destination. |
fileid1 and fileid2 |
Alternate message destinations in the external system If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks. If the same destination with both fileid1 and fileid2 parameters is specified, Teradata MultiLoad duplicates the messages at each destination. For more information, see the “ACCEPT” or “RUN FILE” command descriptions. |
Usage Notes
Table 52 describes the things to consider when using the ROUTE MESSAGES command.
Topic |
Usage Notes |
Default Message Destinations |
If the ROUTE MESSAGES command is not used, Teradata MultiLoad writes output messages to: |
z/OS fileid Usage Rules |
If a DDNAME is specified, Teradata MultiLoad writes messages to the specified source. A DDNAME must obey the same construction rules as Teradata SQL column names, except that: The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set. The DDNAME represents a data source on magnetic tape, the tape may be either labeled or non-labeled, as supported by the operating system. |
Specifying the System Console/Standard Output Device |
Use the asterisk character (*) as the fileid1 or fileid2 specifications to route messages to the system console/standard output (stdout) device. The system console is the: For more information about the display screen and standard output devices, see “File Requirements” on page 31. |
Example
.ROUTE MESSAGES FILE OUTPUT;
The messages are written to the file designated by OUTPUT from this point unless redirected by another ROUTE MESSAGES command.
Note: On network-attached systems, if outfilename is used both to redirect stdout and as the fileid in a ROUTE MESSAGES WITH ECHO command, the results written to outfilename may be incomplete due to conflicting writes to the same file.
Purpose
The RUN FILE command invokes the specified external source as the current source of commands and statements.
Syntax
where:
Syntax Element |
Description |
fileid |
Data source of the external system The external system DD (or similar) statement specifies a file. If the path name contains single or double quotation marks, it should be enclosed in either single or double quotation marks. |
IGNORE charpos1 and charpos2 |
Start and end character positions of a field in each input record that contains extraneous information. For example: |
Usage Notes
Table 53 describes the things to consider when using the RUN FILE command.
Topic |
Usage Notes |
Executing the RUN FILE Command |
After Teradata MultiLoad executes the RUN FILE command, it reads additional commands from the specified source until a LOGOFF command or end of file condition is encountered, whichever occurs first. An end of file condition automatically causes Teradata MultiLoad to resume reading its commands and DML statements from the previously active source: Note: SYSIN/stdin remains the active input source after Teradata MultiLoad processes any user-provided invocation parameters. |
Nested RUN Commands |
The source specified by a RUN FILE command can have up to 16 levels of nested RUN commands. |
-i scriptencoding parameter |
When the -i scriptencoding parameter is used, the specified encoding form and byte order apply to all the command files specified by the .RUN FILE command and any nested RUN FILE commands. |
Specifying the System Console/Standard Input Device |
Use the asterisk character (*) as the fileid specification for the system console/standard input (stdin) device. The system console is the: For more information about the keyboard and standard input devices, see “File Requirements” on page 31. |
z/OS fileid Usage Rules |
If a DDNAME is specified, Teradata MultiLoad reads data records from the specified source. A DDNAME must obey the same construction rules as Teradata SQL column names except that: The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set. If the DDNAME represents a data source on magnetic tape, the tape may be either labeled or non-labeled, as supported by the operating system. |
Purpose
The SET command assigns a data type and a value to a Teradata MultiLoad variable.
Syntax
where:
Syntax Element |
Description |
expression |
New value for the utility variable var |
var |
Name of the Teradata MultiLoad variable to be set to the evaluated expression |
Usage Notes
Table 54 describes the things to consider when using the SET command.
Topic |
Usage Notes |
Changing the Data Type |
The SET command also dynamically changes the data type to that of the assigned value if it had already been defined. If the expression evaluates to a numeric value, the symbol is assigned an integer value, as in: .SET FOONUM TO ‑151 ;
If the expression is a quoted string, the symbol is assigned a string value, as in: .SET FOOCHAR TO '‑151' ;
The minimum and maximum limits for floating point data types are: 4.0E-75 <=abs(float variable)<7.0E75 |
Declaring Variables |
Variables need not be declared in advance to be the object of the SET command. If a variable does not already exist, Teradata MultiLoad creates it. Variables used to the right of TO in the expression must be declared in advance. |
Variable Substitution |
Teradata MultiLoad variable can be substituted wherever substitution is allowed. |
Examples
Teradata MultiLoad supports concatenation of variables, using the SET command, such as:
.SET C TO 1;
.SET D TO 2;
.SET X TO &C.&D;
In this example, X evaluates to 12.
If a decimal point is added to the concatenated variables, then X evaluates to 1.2, as in:
.SET C TO 1;
.SET D TO 2;
.SET X TO &C..&D;
Purpose
The SYSTEM command submits an operating system command to the client environment during a Teradata MultiLoad operation.
Syntax
where:
Syntax Element |
Description |
oscommand |
Any legal command in the client operating system |
Usage Notes
The SYSTEM command suspends the current Teradata MultiLoad operation to execute the client operating system command.
When the client operating system command completes, Teradata MultiLoad displays the return code from the invoked command and updates the &SYSRC variable.
Purpose
The TABLE command identifies a table whose column names and data descriptions are used as the names and data descriptions of fields of the input records. These are assigned in the order defined.
Syntax
where:
Syntax Element |
Description |
tableref |
Existing table whose column names and data descriptions are assigned, in the order defined, to fields of the input data records |
Usage Notes
Table 55 describes the things to consider when using the TABLE command.
Topic |
Usage Notes |
Column Names in the Referenced Table |
The column names of the specified table must be Teradata SQL column names that need not be enclosed in quotation marks. Tables cannot be created with invalid column names, and any nonstandard column name produces an error condition, depending on the nature of the divergence from the standard. The errors are: |
Intermixing TABLE Commands with FIELD or FILLER commands |
One or more TABLE commands with the FIELD or FILLER following a LAYOUT command can be intermixed. This method of specifying record layout fields assumes each field, as defined by the data description of the corresponding column of tableref, is contiguous with the previous one, beginning at the next-available character position beyond any previous field specifications for the input records. The fields must appear in the order defined for the columns of the table. The object identified by the tableref parameter must be a table. It need not appear as a parameter of the BEGIN MLOAD or BEGIN DELETE MLOAD command, but must either be the owner of the object or have at least one privilege on it. If specified as an unqualified table name, the current default database qualifies it. |
TABLE command and UDT type |
When the TABLE command is used and the table contains a structured UDT type, Teradata MultiLoad returns an external representation of the UDT and that requires the user to transform. The term “external type” means the data type of the external opaque container for a structured UDT and is the type returned by the from-sql transform method. |
Purpose
The UPDATE command is a version of the Teradata SQL UPDATE statement that changes field values in existing rows of a table.
Syntax
where:
Syntax Element |
Description |
cname |
Column whose value is to be replaced by the value of expr The column named must not be a column of the primary index. |
expr |
Expression whose resulting value is to replace the current value of the identified column The expression can contain any combination of: |
tname |
Table or view to be updated This table was previously identified as tname1 in the BEGIN MLOAD command. If tname is not explicitly qualified by database name, the current default database qualifies it. |
WHERE condition |
Conditional clause that specifies the row or rows to be updated The conditional clause can use values from fields of input data records by referring to their field names. |
Usage Notes
Table 56 describes the things to consider when using the UPDATE statement.
Topic |
Usage Notes |
OR Construct |
The OR construct cannot be used in an UPDATE statement. To accomplish the result normally achieved with the OR construct, use two separate UPDATE statements and use the APPLY clause of the IMPORT command to apply them conditionally. |
Referencing Field Names |
Make references to fields of the input data records for the expr and WHERE conditional as follows: :fieldname
where fieldname is defined by a FIELD or TABLE command of the layout referenced by an IMPORT command using this UPDATE statement. For the WHERE condition clause, the equality values for all the columns of the primary index must be explicitly specified. |
Required Privileges |
To use the UPDATE statement, the UPDATE privilege on the tname table or view is required. |
Specifying Multiple UPI Columns in an Import Task |
If multiple UPI columns in an import task are specified, specify them all in the WHERE clause of the UPDATE statement. In this case, the WHERE clause is fully qualified, thereby allowing Teradata MultiLoad to optimize the processing. |
Update Object Restrictions |
If the object of the UPDATE statement is a view, it must not specify a join. Teradata MultiLoad operates only on single tables, so UPDATE statements must not contain any joins. Only one object may be identified and that must be a target object as specified in the BEGIN MLOAD command. |
Example
The following example depicts an input data source that contains a series of 14-byte records. Each record contains the value of the primary index column (EmpNo) of a row of the Employee table whose PhoneNo column is to be assigned a new phone number from field Fone. The example assumes that the current default database is Personnel, the database containing the Employee table.
.LAYOUT Layoutname;
.FIELD EmpNum 1 INTEGER;
.FIELD Fone * (CHAR (10));
.DML LABEL DMLlabelname;
UPDATE Employee SET PhoneNo = :Fone WHERE EmpNo = :EmpNum;
Purpose
The VERSION command, which is primarily for developer use, displays version information for each utility component, including MultiLoad, Teradata ICU, Teradata CLI and Teradata Data Connector.
Syntax
Example
0001 .version;
MLDVER = MLDCLI 15.10.00.01 2014/05/20
MLDVER = MLDCNTL 14.10.00.00 2012/10/16
MLDVER = MLDEXEC 15.00.00.03 2013/12/03
MLDVER = MLDLANG 15.10.00.00 2014/03/20
MLDVER = MLDMAIN 14.10.00.01 2012/02/15
MLDVER = MLDMISC 15.00.00.00 2013/12/17
MLDVER = MLDNOTFY 15.00.00.01 2013/03/21
MLDVER = MLDPREP 15.00.00.05 2013/12/17
MLDVER = MLDSTMTS 15.10.00.02 2014/04/02
UT$VER = UMBCNTLR 15.00.00.04 2013/12/02
UT$VER = UMBDLOC 15.00.00.07 2014/01/08
UT$VER = UMBEXEC 15.00.00.00 2013/06/27
UT$VER = UMBOSDEP 14.10.00.03 2012/03/12
UT$VER = UMBSTFM 15.00.00.02 2013/10/05
UT$VER = UMBSTIN 15.00.00.04 2013/11/11
UT$VER = UMBSTLG 15.00.00.00 2013/06/27
UT$VER = UMBSTOP 15.00.00.01 2013/09/03
UT$VER = UMBSTQ 14.00.00.01 2011/05/26
UT$VER = UMBSTX 13.01.00.00 2008/09/10
UT$VER = UMUCNTIN 15.00.00.01 2013/06/02
UT$VER = UMUOUT 14.00.00.03 2011/11/04
UT$VER = UTCLI 15.10.00.02 2014/05/06
UT$VER = UTYCRMGR 14.10.00.02 2012/03/12
UT$VER = UTYCVTR 15.00.00.00 2013/10/05
UT$VER = UTYEXEVL 15.00.00.06 2014/03/11
UT$VER = UTYEXPRS 15.00.00.03 2013/12/03
UT$VER = UTYIO 15.10.00.00 2014/05/20
UT$VER = UTYKUNX 14.10.00.00 2012/04/25
UT$VER = UTYLOADM 14.10.00.03 2012/03/12
UT$VER = UTYLOGW 13.01.00.00 2008/09/16
UT$VER = UTYMBCS 14.10.00.03 2012/03/21
UT$VER = UTYMISC 15.00.00.02 2013/11/08
UT$VER = UTYMSG 14.10.00.01 2012/02/15
UT$VER = UTYMVSQ 13.01.00.00 2008/09/10
UT$VER = UTYNFY 14.10.00.00 2012/10/31
UT$VER = UTYPARSR 15.00.00.10 2014/03/20
UT$VER = UTYSMPR 15.00.00.02 2013/11/12
UT$VER = UTYVSUB 15.00.00.04 2014/01/16
CLIV2 : 15.10.00.00
MTDP : 15.00.00.09
MOSIos : 15.00.00.00
MOSIDEP : 15.00.00.00
OSENCRYPT : N/A
OSERR : 14.00.00.00
ICUVER = TDICU, 15.10.00.00
PMVER = Teradata Data Connector, 15.10.00.00
PMVER = PMPROCS, 15.00.00.10
PMVER = PMRWFMT, 15.00.00.00
PMVER = PMTRCE, 13.10.00.02
PMVER = PMMM, 13.00.00.01
PMVER = PMUDDI, 15.00.00.04
PMVER = DCUDDI, 15.00.00.11
PMVER = PMHEXDMP, 14.10.00.00
PMVER = PMUNXDSK, 15.10.00.02