15.00 - Temporal Semantics - Parallel Data Pump

Teradata Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
15.00
category
Programming Reference
featnum
B035-3021-034K

Temporal Semantics

DML validtime qualifier and NONTEMPORAL semantics are supported. For more information, see SQL Data Manipulation Language (B035‑1146).

Example  

.BEGIN LOAD SESSION number;
.LAYOUT Layoutname; 
.TABLE  Targetablename; 
.DML LABEL DMLlabelname; 
INSERT INTO Targetablename.*;
.IMPORT INFILE Infilename LAYOUT Layoutname  APPLY DMLlabelname;
.END LOAD; 

Example  

.LAYOUT lname; 
.FIELD first 1 somedatatype; 
.FIELD f2nd * anydatatype;
.
.
.
.FIELD flast * datatype; 
.DML LABEL label; 
INSERT INTO targetable VALUES (:first, :f2nd, ... :flast); 

Example  

.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); 

Example  

An input data source contains a series of 10‑ to 40‑byte records. Each record contains the primary index value (EmpNum) of a row that is to be inserted successively into the Employee table whose columns are EmpNo, Name, and Salary.

.BEGIN LOAD SESSION number ;
.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); 
.IMPORT INFILE Infilename LAYOUT Layoutname  APPLY DMLlabelname;
.END LOAD; 

LAYOUT

Purpose  

The LAYOUT command, in conjunction with the immediately following sequence of FIELD, FILLER, and TABLE commands, specifies the layout of the externally stored data records.

Syntax  

where

 

Syntax Element

Description

layoutname

Name assigned to the layout for reference by one or more subsequent IMPORT commands

A layoutname must obey the same rules for its construction as Teradata SQL column names.

The name specified also may be used in the LAYOUT clause of an IMPORT command.

MACROCHARSET

An optional clause on the layout command. If no fieldlevel MACROCHARSET is present for a field of character type, but a layoutlevel MACROCHARSET is present, TPump generates a CHARACTER SET clause in the macro creation DDL for the corresponding field using the specified layout character set.

servercharsetname

Server storage character set name. The following two documents are for the valid server storage character set names: International Character Set Support (B0351125) and SQL Data Definition Language (B0351144).

CONTINUEIF condition

following:

position = value

where position is an unsigned integer (never an asterisk) that specifies the starting character position of the field of every input record that contains the continuation indicator, and where value is the continuation indicator specified as a character constant or a string constant. Teradata TPump uses the length of the constant as the length of the continuation indicator field.

In the CONTINUEIF option, the condition specified as position = value is case‑sensitive; verify that the correct case has been specified for this parameter.

If the condition is true, Teradata TPump 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 parameter is false, Teradata TPump sends to Teradata Database, the current input record either by itself, or as the last of a sequence of concatenated records.

Note that the starting position of the continuation indicator field is specified as a character position of the input record. Character positions start with character position one. Teradata TPump removes the continuation indicator field from the input records so that they are not part of the final concatenated result.

For other fields whose startpos is specified as an unsigned integer, the startpos refers to the field position within the final concatenated result. Consequently, the continuation indicator field cannot be defined as all or part of a field defined with the FIELD, FILLER, or TABLE commands. Refer to the startpos parameter of the FIELD command.

 

When the character set of the job script is different from the client character set used for the job (for example, on z/OS the job script must be in Teradata EBCDIC when using the UTF8 client character set, or UTF‑16 client character set can be used with the job script in UTF8), Teradata TPump translates the specified value, which is either a character constant or a string constant, from the script character set form to the client character set form before evaluating the condition. Teradata TPump uses the length of the constant in the client character set form as the length of the continuation indicator field.

Notice:

When using the UTF‑8 client set on the mainframe, be sure to examine the definition in International Character Set Support (B035‑1125) to determine the code points of the special characters required. Different versions of EBCDIC do not always agree as to the placement of these characters.

The mappings between Teradata EBCDIC and Unicode can be referred to International Character Set Support (B035‑1125).

INDICATORS

Condition that the data is in the indicator mode

This means that the first n bytes of each record are indicator bytes, where n is the rounded‑up integer quotient of the number of fields defined by this LAYOUT command for transmission to Teradata Database, divided by 8.

Teradata TPump sends all the FIELD commands, including redefines, to Teradata Database. If a field has been defined and then redefined, indicator bits must be set for both. FILLER commands also need to have indicator bits set. Teradata TPump sends both the defined and the redefined fields to Teradata Database. This demonstrates the inefficiency of redefines which cause the transfer of an extraneous field.

If INDICATORS is specified on the LAYOUT command and the data file does not contain indicator bytes in each record, the target table will be loaded with spurious data. Conversely, if INDICATORS is not specified and the data file contains indicator bytes in each record, the target table will likewise be corrupted. Exercise caution to guard against either occurrence.

A LAYOUT command that includes the INDICATORS option must accurately describe all fields of the record to agree with the column descriptions and ordering of the table from which this indicator‑mode data was previously selected. If the INDICATORS option is specified, Teradata TPump sends the data to Teradata Database with indicators.

The NULLIF parameter of the FIELD command can be specified with or without the INDICATORS option. If NULLIF is specified, Teradata TPump sends the data to Teradata Database with indicators, whether or not the INDICATORS option is specified.

Usage Notes

Although there is no explicit limit to the number of LAYOUT commands allowed, there is a practical limit. The implied limit on usable LAYOUT commands per Teradata TPump load is 100, because Teradata TPump allows up to 100 IMPORT commands within a load, and each IMPORT can reference only one LAYOUT.

A LAYOUT command must be immediately followed by a combination of FIELD, FILLER, and/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 redefinition options for FIELD, FILLER, and TABLE). The LAYOUT command sequence is terminated by the first subsequent command that is not a FIELD, FILLER, or TABLE command.

A layoutname may be used by one or more Teradata TPump tasks (delimited by BEGIN LOAD and END LOAD) in a single job step and must be defined prior to any IMPORT commands that reference it. All IMPORT commands in a single Teradata TPump task must reference the same layoutname in the LAYOUT clause.

If a field‑level MACROCHARSSET is present for a field of character type, TPump generates a CHARACTER SET clause in the macro creation DDL for the corresponding field using the specified character set.

If no field‑level MACROCHARSET is present for a field of character type, but a layout/schema‑level MACROCHARSET is present, TPump generates a CHARACTER SET clause in the macro creation DDL for the corresponding field using the specified layout character set.

Absent user specification, the following clause will be generated for each character macro parameter CHARACTER SET LATIN, when the client session character set is:

  • Is ASCII
  • Is EBCDIC
  • Ends in _0A or _0E
  • Ends in _zx, where “z” is other than 0 (digit zero)
  • Ends in _zx, where “z” is other than 0 (digit zero)
  • Does not end in _zx or _zxx, except for KATAKANAEBCDIC, UTF‑8, or UTF‑16
  • TPump generates CHARACTER SET UNICODE in all other cases.

    LOGDATA

    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 available only on network‑based platforms.

    Syntax

    where

     

    Syntax Element

    Description

    logdata_string 'logdata_string'

    Parameters required for the logon mechanism specified using “LOGMECH” on page 169

    For information about the logon parameters for supported mechanisms, see Security Administration (B035‑1100).

    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.

    Note: The security feature this command supports is not supported with the UTF‑16 session character set.

    Usage Notes  

    For more information about logon security, see Security Administration (B035‑1100).

    Examples  

    If used, the LOGDATA command and the LOGMECH command must precede the LOGON command. The commands themselves may occur in any order.

    The 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;

    LOGMECH

    Purpose  

    Identifies the appropriate logon mechanism by name. If the specified mechanism 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

    Definition of the logon mechanism

    For a discussion of supported logon mechanisms, see Security Administration (B035‑1100).

    The name is limited to 8 bytes. It is not casesensitive.

    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 (B035‑1100).

    Examples  

    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;

    LOGOFF

    Purpose  

    The LOGOFF command disconnects all active sessions and terminates execution of Teradata TPump on the client. An optional return code value may be specified as a conditional or arithmetic expression, evaluated to a signed integer.

    Syntax  

    where

     

    Syntax Element

    Description

    retcode

    Completion code returned to the client operating system

    If retcode is not specified, Teradata TPump returns the value generated by the error condition.

    Usage Notes

    Teradata TPump tracks the internal error condition code throughout the job and returns either 0 for complete success, 4 for warnings, 12 for fatal errors, and 16 for no sysprint. These values are the “error conditions”.

    To avoid ambiguity or conflict with standard Teradata TPump completion codes, values greater than 20 should be used. Teradata TPump returns the higher value between the value generated by the error condition and the return code specified in LOGOFF.

    If the LOGOFF command processes, this indicates that the highest return code reached was no more than 4 (warning). Any return code other than 0 or 4 would have terminated the job.

    LOGOFF is permitted at any point in the input script and logs off immediately.

    Example  

    Suppose successful execution of a Teradata SQL statement (such as CREATE TABLE) is necessary to prepare for Teradata TPump. If it is determined that the statement has failed with an unacceptable completion code, and if BADRC is set to &SYSRC after the failed SQL statement, the execution of Teradata TPump can be terminated and the unacceptable code returned to the client by executing this command:

    .LOGOFF &BADRC;

    The restart table is dropped when this command is executed. If execution is terminated before the LOGOFF command is encountered, the restart table is not dropped, in order to support a restart at a later time.

    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 optional retcode specified as a LOGOFF command option.

    LOGON

    Purpose  

    The LOGON command establishes a Teradata SQL session between Teradata TPump and Teradata Database. Use it to specify the LOGON string for connecting sessions required by subsequent functions. 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. Security Administration (B035‑1100) and the appropriate installation guide for more information.

    Syntax  

    Standard LOGON

    Note: On z/OS, with the use of the User Logon Exit routine in TDP, the user name is not required. See the Teradata Director Program Reference (B035‑2416) for more information.

    Single Sign On LOGON

    Note: When logon encryption is enabled on the gateway, single sign on is disabled on the client and standard logon syntax should be used instead.

    where

     

    Syntax Element

    Description

    tdpid

    Optional identifier associated with a particular copy of Teradata Database

    If this field is not specified, the default tdpid, established by the system administrator, is used.

    For mainframeattached systems, the tdpid string must be in the form:

    TDPn 

    where n is the TDP identifier

    username

    User identifier of up to a 30character maximum

    password

    Optional password associated with the username, up to a 30character maximum

    Teradata Database must be configured to recognize the password specified.

    Note: Passwords that contain special characters must be enclosed in double quotes.

    'acctid'

    Optional account identifier associated with the username, up to a 30character maximum

    The string specification must be enclosed in single quotes.

    If this field is not specified, a default acctid” is used.

    Usage Notes

    Both the LOGON command and the LOGTABLE command are required to set up the Teradata TPump support environment. Use them in any order, but they must precede any other commands. However, a RUN FILE command can be used to identify a file containing the LOGON command before the LOGON and LOGTABLE commands.

    LOGON and LOGTABLE commands typically occur as:

    .logtable logtable001;
    .logon tdpx/me,paswd;

    When the LOGON command is executed, the initial Teradata TPump utility session is logged on. The logon information is saved and re‑used when processing the BEGIN LOAD command to connect the appropriate number of sessions.

    The parameters (tdpid, username, password, and “acctid”) are optional and are used in all sessions established with Teradata Database. The LOGON command may only occur once. The period (.) preceding LOGON is also optional.

    The tdpid identifier specifies a particular Teradata Database. See the system or site administrator for the identifier planned to use. If a tdpid is not specified and the site administrator has not updated the System Parameter Block, the default identifier is Teradata Database. The long form of this parameter, tdpx, should be used to avoid CLI errors that can occur when the short form is used.

    The tdpid parameter is optional if the site has only one TDP, if a TDP command was previously executed, or if the default TDP was selected. This parameter is not case‑sensitive.

    Teradata TPump does not prompt for a username or password. If either or both of these are required, Teradata TPump fails and reports the error. Both of these parameters may be optional if a logon exit is used.

    Where possible, do not use special characters in the acctid parameter string. Although acctid may contain special characters, the special characters might be interpreted differently by different output devices. Therefore, a script containing special characters might have to be modified if the output is routed to another device. If the acctid contains an apostrophe (single quote) character, use either the second form of the LOGON command, which is delimited by quotes, or double the apostrophe character as follows:

    .LOGON 0/fml,fml,"engineering’s account"

    or

    .LOGON 0/fml,fml,'engineering"s account"

    If the acctid does not contain an apostrophe, the two LOGON command forms are the same.

    If any parameter is entered incorrectly, the logon fails and Teradata TPump returns an error message. For security reasons, the error message does not state in which parameter the error occurred.

    If password security on a mainframe‑attached client is a concern, use the RUN FILE command to alter the script to accept the LOGON command from another dataset/file under the control of ACF2 or another client‑resident security system. For example:

    //stepname EXEC PGM=TPUMP,...
    //SECURE DD DSN = FOO
    //SYSIN DD *
    .LOGTABLE MYTABLE;
    .RUN SECURE;

    Then log on by simply entering the LOGON command with a valid user name and no password if the system administrator has granted this option. As an example, to log onto Teradata TPump as user ABC with ABC as the password (which is masked from view on the output listing), specify the LOGON command on one line as follows:

    .logon ABC,ABC

    When the command is entered, Teradata TPump displays something like:

    **** 15:12:47 UTY8400 Teradata Database Version: 14.00.00.00
    **** 15:12:47 UTY8400 Default character set: ASCII
    **** 15:12:47 UTY8400 Current RDBMS has UDT support
    **** 15:12:47 UTY8400 Maximum supported buffer size: 1M
    **** 15:12:47 UTY8400 Upsert supported by RDBMS server
    **** 15:12:47 UTY8400 Data Encryption supported by RDBMS server
    **** 15:12:47 UTY8400 Array Support supported by RDBMS server
    **** 15:12:48 UTY6211 A successful connect was made to the RDBMS.

    Logon exits are supported on both mainframe and clients in the UNIX system. The CLIv2 User Logon Exit routine can be used to make some or all logon string elements optional.

    LOGON is used with the LOGTABLE command, both of which are required. LOGON and LOGTABLE may appear in any order. If LOGON is entered first, a warning that LOGTABLE is required is returned.

    The parameters (tdpid, username, password, and “acctid”) are used in all sessions established with Teradata Database. The LOGON command may occur only once.

    Note: If the database is configured to use the Single Sign On (SSO) and the Teradata client machine is logged on, the machine name, user name, and password are not required in the LOGON command. The user name and password combination specified when the Teradata client machine was logged on are authenticated via network security for a SSO 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.

    To connect to a Teradata Database other than the default, the TDPid must be included in the LOGON command. If the TDPid is not specified, the default contained in clispb.dat will be used. 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 slugger, enter one of the following:

    .LOGON slugger/;
    .LOGON slugger/,,'acctinfo';

    If the LOGON command is entered first, Teradata TPump warns that the LOGTABLE command is also required.

    If an account ID is to be used, the optional account ID must be specified in the LOGON command.

    Teradata TPump terminates with a proper message when Teradata TPump attempts to connect the Main SQL session the first time and the Teradata Database is down.

    LOGTABLE

    Purpose  

    The LOGTABLE command identifies the table to be used for journaling checkpoint information required for safe, automatic restart of the Teradata TPump support environment in the event of a client or Teradata Database hardware platform failure.

    The LOGTABLE command is used in conjunction with the LOGON command, both of which are required. Both LOGON and LOGTABLE may appear in any order. The ACCEPT and SET commands are valid commands preceding LOGON and LOGTABLE commands. If LOGON is entered first, a warning is returned that the LOGTABLE is required.

    Notice:

    Do not share the restart log table between two or more Teradata TPump jobs. Each Teradata TPump job must have its own restart log table to ensure that it runs correctly. If a distinct restart log table for each Teradata TPump job is not used, the results are unexpected. In addition, one or more of the affected jobs may not be able to restart.

    Syntax  

    where

     

    Syntax Element

    Description

    dbname

    (Optional) Database name under which the log table exists

    The default is the database name associated with the username specified in the LOGON command. Teradata TPump searches for the table (tname) in that database unless another database name is specified in this option.

    tname

    Identifier for the restart log table

    Usage Notes  

    A LOGTABLE command is required for each invocation of Teradata TPump. Only a single LOGTABLE command is allowed for each execution. It must precede all environmental and application commands (other than RUN FILE and LOGON) in the input stream.

    The specified table is used as the Teradata TPump restart log. It does not have to be fully qualified. If the table exists, it is examined to determine if this is a restart. When this is the case, a restart is done automatically. If the table does not exist, it is created and used as a restart log during this invocation of Teradata TPump.

    The log table is maintained automatically by Teradata TPump. If this table is manipulated in any way, the restart capability is lost. The only action that should be taken is to DROP the log table; never attempt to delete rows from the table. The log table should not be dropped when the Teradata TPump job using that log table is running. If the log table is dropped during a job run, Teradata TPump will run into errors.

    The default for the database name cannot be overridden with a DATABASE statement because it must come after LOGTABLE/ LOGON. Instead, the LOGTABLE dbname option must be used.

    Teradata TPump allows a DELETE DATABASE statement because DELETE is a standard Teradata SQL function. This statement can delete the current restart log after it has been created, which terminates the job.

     

    Example  

    The following example presents both the LOGTABLE command and the LOGON command as they typically occur.

    .logtable Mine.Logtable001;
    .logon tdpx/me,paswd;