DB Alias Tab - Teradata, Load Request Editor

Use the DB Alias tab to define the parameters needed to perform the Load Process for the Teradata loader. The Optim™ solution supports the Teradata Loader for both FastLoad and MultiLoad.

Load using Teradata Named Pipe

Load performance may be significantly improved using the Teradata Named Pipe Access Module. When this option is used, the load process runs in a single phase rather than a 2-stage process, resulting in reduced elapsed time for load processing. As the row data is immediately loaded after conversion to a loader format, the space requirements are also significantly reduced, when compared to the 2-stage load process.

The Optim solution writes the data to the named pipe, the Teradata Named Pipes Access Module reads and then copies the data, and the data is loaded. You have the option of writing the data to be loaded to a fallback file, in case you need to restart the load using native Teradata utilities outside of the Optim solution. The fallback file has an extension of .fbf and is created in the %TEMP% or %WINDIR%\temp directory. The Optim solution writes data to the fallback file, based on the save interval you set. After the load process completes, the fallback file is automatically deleted.

Use the Load tab in Personal Options to specify the Teradata server, user ID, password, and path to the Loader, along with other information. See Personal Options in the Common Elements Manual.

Example of the Load Request Editor DB Alias tab for Teradata

The DB Alias tab for Teradata has the following fields:

Mode

Insert
Select this option to insert rows from the Source File into empty destination tables. If destination tables contain data, the loader returns an error.
Replace
Select this option to clear and replace all of the existing rows in the destination tables with the rows from the Source File.
Delete
Select this option to delete rows from the destination table. To delete rows from the source table, the source table and destination table must have the same name, same creator ID, and same DBALIAS in the Table Map.

Delete mode is available only when you use MultiLoad. Other options also determine whether you can select this mode.

Utility

MultiLoad
Select this option to use MultiLoad for the Load Request.
FastLoad
Select this option to use FastLoad for the Load Request.

File Type

ASCII
Use ASCII file type to import data.
ASCII Delimited
Use ASCII Delimited file type to import data. If you select this file type, select a valid Delimiter.
Delimiter
You can exclude the delimiter from being scanned in character data columns by the Optim solution while generating the loader file. This can improve load performance. To exclude delimiters from scanning, select any of the following values from the Delimiter drop-down list:
X'01' (No Pre-scan)
X'02' (No Pre-scan)
X'03' (No Pre-scan)
X'04' (No Pre-scan)
X'05' (No Pre-scan) 
Additional delimiters are supported which are scanned in character data columns by the Optim solution while generating the loader file. Select any of the following values from the Delimiter drop-down list. The Optim solution scans these values while generating the loader file:
(  )  *  /  ;  ?  |  <  >  =
X'FA'
X'FB'
X'FC'
X'FD'
X'FE'
X'FF'

Options

Perform Load
Select this check box to run the loader automatically after file conversion processing is complete. If you clear this check box, the Load Process prepares the data in the appropriate format and creates the BAT file to run the loader, but does not initiate the loader. To run the loader, edit the BAT file to include the proper password information and then run the BAT file.
Use Named Pipe
Select this check box to use the Teradata Named Pipe Access Module to load the data. The data to be loaded is written to the named pipe and then submitted to the loader.
Delete files if Successful
Select this check box to delete the data files after the loader completes successfully.
  • If you select this check box, Fast Load Confirmation is unavailable. Fast Load Confirmation reduces processing time when you run a Load Request that has run before. See Process a Load Request for details.
  • If you select Use Named Pipe, this check box is unavailable.
Delete files if Failure
Select this check box to delete the data files if the loader does not complete successfully.
  • If you select this check box, Fast Load Confirmation is unavailable.
  • If you select Use Named Pipe, this check box is unavailable.
Load When Source Is Empty
Select this check box to perform the load if a table to be loaded contains no rows. If you do not select this check box, any table in the file that contains no rows will be skipped in the load phase. Using load with an empty source table deletes rows from the target table, resulting in a clean test environment.
Use In-line LOBs
Select this check box to include LOBs in a data file (inline with the table data).
Enable delimiter pre-scan
Select this check box to scan for delimiter characters while building the loader file.
Create Exception Tables
Select this check box to create exception tables for the tables you specified.

CheckPoint

MultiLoad/FastLoad supports the use of checkpoints. Checkpoints are entries posted to a restart log table at regular intervals during the MultiLoad data transfer operation. If processing stops while a MultiLoad job is running, restart the job at the most recent checkpoint. For example, assume 1,000,000 records are loading in a table and have specified checkpoints every 50,000 records. MultiLoad pauses and posts an entry to the restart log table whenever multiples of 50,000 records are successfully sent to the Teradata Database. If the job stops after record 60,000 has been loaded, restart the job at the record immediately following the last check point record, 50,001.

By default, MultiLoad takes a checkpoint every 15 minutes. To bypass the MultiLoad checkpoint function, specify a checkpoint rate of zero in the BEGIN MLOAD command. That way, the job completes without taking a checkpoint. When the checkpoint rate is 60 or higher, the specified number refers to the number of records or rows, not the number of minutes. Though this would nullify the MultiLoad restart/reload capability, it would allow use of an INMOD routine that does not support the checkpoint function.

Specify from 0 through 999999999. An entry of 1 through 59 represents minutes. An entry of 60 through 999999999 represents rows. The default is 0.

Error Limit

This entry identifies the error threshold that limits the number of rejected records that can be written to the error tables during the acquisition phase of a MultiLoad/FastLoad task. Specify from 0 through 999999999. The default is 0.

Max Sessions

This entry identifies the maximum number of MultiLoad/FastLoad sessions that can be logged on. Specify from 1 through 200. The default is 1.

Work path for data files

Type or select the path for storing temporary data files.

Additional Loader Parameters

You can augment the loader arguments created automatically by the Optim solution with additional loader parameters, if necessary. The additional parameters you create are appended to the list created by the solution, but are not validated prior to starting the loader. Refer to your Teradata documentation for valid operands. If additional loader parameters are forced from within Product Options, you cannot modify them. See the Product Options section in the Installation and Configuration Guide for further information.