Thursday, 15 September 2011

Teradata Utilities

Teradata loading utilities
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.

Fast Load

  • Main use=> To load empty tables at high speed.
  • The target tables must be empty in order to use FastLoad.
  • It supports inserts only(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.
  • Fastload runs in two operating modes: Interactive and Batch.
  • Duplicate rows will not be loaded. 

  • 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.
  • Resource consumption: loading at the highest possible throughput.
  • Duplicate rows allowed .

  • 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. 

  • FastLoad(script)

    ===========================


    The following script attached below will load a sample fixed-length columns extract into a Teradata database using FastLoad.
    
    
    SESSIONS 4;
    ERRLIMIT 25;
    logon tdpid/username,password;
    
    create table gg_cli (
    wh_cust_no integer not null,
    cust_name varchar(200),
    bal_amt decimal(15,3) format �ZZZ,ZZ9.999�
    )
    unique primary index( wh_cust_no ) ;
    
    SET RECORD UNFORMATTED;
    
    define
                    wh_cust_no(char(10)), delim1(char(1)),
                    cust_name(char(200)), delim2(char(1)),
                    bal_amt(char(18)), delim3(char(1))
                    newlinechar(char(1))
    file=insert.input;
    
    SHOW;
    
    BEGIN LOADING gg_cli errorfiles error_1, error_2;
    
    insert into gg_cli (
                    :wh_cust_no,
                    :cust_name,
                    :bal_amt
    );
    
    END LOADING;
    
    logoff;
    

    MultiLoad(script)

    ===========================

    The following script attached below will load a sample fixed-length columns extract into a Teradata database using MultiLoad. Use the following command to run load the ggclients.mload file using Teradata FastLoad script:

    .logtable inslogtable;
    .logon tdpid/username,password;
    
    create table gg_cli (
    wh_cust_no integer not null,
    cust_name varchar(200),
    bal_amt decimal(15,3) format �ZZZ,ZZ9.999�
    )
    unique primary index( wh_cust_no ) ;
    
    .BEGIN IMPORT MLOAD tables gg_cli;
    
    .layout ggclilayout;
                    .field wh_cust_no 1 char(10);
                    .field cust_name 12 char(200);
                    .field bal_amt 213 char(18);
    .dml label insertclidml;
    
    insert into gg_cli.*;
    
    .import infile insert.input
                    format text
                    layout ggclilayout
                    apply insertclidml;
    .END MLOAD;
    
    .logoff;
    
    

    TPump(script)

    ===========================

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

    .logtable tpumplogtable;
    .logon tdpid/username,password;
    
    .BEGIN LOAD SESSION 4;
    
    .layout ggclilayout;
                    .field wh_cust_no 1 char(10);
                    .field cust_name 12 char(200);
                    .field bal_amt 213 char(18);
    .dml label insertclidml;
    
    insert into gg_cli.*;
    
    .IMPORT INFILE insert.input
                    layout ggclilayout
                    apply insertclidml;
    .END LOAD;
    
    .logoff;




    No comments:

    Post a Comment

    Post a Comment