Teradata Fastload Reference Manual

/ Comments off

UNIX and Windows Examples - FastLoad Teradata FastLoad Reference prodname FastLoad vrmrelease 15.10 category Programming Reference featnum B035-2411-035K.

The article contains comparison and main features of the data loading tools provided by Teradata. The tutorial illustrates main features of Teradata Multiload , FastLoad and TPump (Parallel Data Pump) and provides sample real-life uses of those tools.
Scroll down for the sample scripts which illustrate different ways to load a sample fixed-length extract into a Teradata database using FastLoad, MultiLoad and Parallel Data Pump (TPump).

Teradata Fast Load

  • Main use: to load empty tables at high speed.
    1. Teradata Online Documentation Quick access to technical manuals Find Teradata documentation—all online! Search Teradata's technical publications and explore our user guides, configuration guides, SQL manuals, and more. Sitemap Legal Support Policies Product Safety Careers Trademarks.
    2. So, I found some obsure information in the fastload reference manual that states fastload cannot support data files larger than 2 Gb on Windows systems becasue of old 32-bit architecture. The workaround was to use the 'LARGE FILE' AXSMOD. Unfortunantly, the AXSMOD source code is missing or no longer available that I can find.
  • The target tables must be empty in order to use FastLoad
  • Supports inserts only - it is not possible to perform updates or deletes in FastLoad
  • Although Fastload uses multiple sessions to load the data, only one target table can be processed at a time
  • Teradata Fastload does not support join indexes, foreign key references in target tables and tables with secondary index defined. It is necessary to drop any of the constraints listed before loading and recreate them afterwards.
  • The maximum number of concurrent Teradata Fastload tasks can be adjusted by a system administrator.
    • Teradata Fastload Reference Manual
  • Fastload runs in two operating modes: Interactive and Batch
  • Duplicate rows will not be loaded

    Teradata Multi Load

  • Main use: Load, update and delete large tables in Teradata in a bulk mode
  • Efficient in loading very large tables
  • Multiple tables can be loaded at a time.
  • Updates data in a database in a block mode (one physical write can update multiple rows)
  • Uses table-level locks
    • Teradata Fastload Reference Manual
  • Resource consumption: loading at the highest possible throughput
    • Duplicate rows allowed

    Teradata Parallel Data Pump (TPump)

  • Main use: to load or update a small amount of target table rows
  • Sends data to a database as a statement which is much slower than using bulk mode
  • TPump uses row-level hash locks
  • Resource consumption: loading speed can be adjusted using a built-in resource consumption management utility. The throughput can be turned down in peak periods.
    • TPump does not support MULTI-SET tables.
    The following script attached below will load a sample fixed-length columns extract into a Teradata database using FastLoad.
    Use the following command to run load the ggclients.fastload file using Teradata FastLoad script:
    Contents of a ggclients.fastload script:
    The following script attached below will load a sample fixed-length columns extract into a Teradata database using MultiLoad.

    Online Reference Manual

    Use the following command to run load the ggclients.mload file using Teradata FastLoad script:
    Contents of a ggclients.mload mload script:

    The sample script attached below loads a sample fixed-length columns extract into a Teradata database using Parallel Data Pump - Teradata TPump.
    Contents of a ggclients.tpump script:


    • Teradata Tutorial
    • Teradata Basics
    • Teradata Advanced
    • Teradata Useful Resources
    • Selected Reading

    FastLoad utility is used to load data into empty tables. Since it does not use transient journals, data can be loaded quickly. It doesn't load duplicate rows even if the target table is a MULTISET table.

    Teradata Fastload Download

    Limitation

    Mount your friends 3d: a hard man is good to climb download free. Target table should not have secondary index, join index and foreign key reference.

    How FastLoad Works

    FastLoad is executed in two phases.

    Sample Reference Manual

    Phase 1

    • The Parsing engines read the records from the input file and sends a block to each AMP.

    • Each AMP stores the blocks of records.

    • Then AMPs hash each record and redistribute them to the correct AMP.

    • At the end of Phase 1, each AMP has its rows but they are not in row hash sequence.

    Phase 2

    • Phase 2 starts when FastLoad receives the END LOADING statement.

    • Each AMP sorts the records on row hash and writes them to the disk.

    • Locks on the target table is released and the error tables are dropped.

    Example

    Create a text file with the following records and name the file as employee.txt.

    Following is a sample FastLoad script to load the above file into Employee_Stg table.

    Executing a FastLoad Script

    Teradata Fastload Reference Manual User

    Once the input file employee.txt is created and the FastLoad script is named as EmployeeLoad.fl, you can run the FastLoad script using the following command in UNIX and Windows.

    Once the above command is executed, the FastLoad script will run and produce the log. In the log, you can see the number of records processed by FastLoad and status code.

    FastLoad Terms

    Following is the list of common terms used in FastLoad script.

    Teradata Fastload Utility

    • LOGON − Logs into Teradata and initiates one or more sessions.

    • DATABASE − Sets the default database.

    • BEGIN LOADING − Identifies the table to be loaded.

    • ERRORFILES − Identifies the 2 error tables that needs to be created/updated.

    • CHECKPOINT − Defines when to take checkpoint.

    • SET RECORD − Specifies if the input file format is formatted, binary, text or unformatted.

    • DEFINE − Defines the input file layout.

    • FILE − Specifies the input file name and path.

    • INSERT − Inserts the records from the input file into the target table.

    • END LOADING − Initiates phase 2 of the FastLoad. Distributes the records into the target table.

    • LOGOFF − Ends all sessions and terminates FastLoad.