»

Feb 21

Teradata Utilities : FASTLOAD – Part 1


Fastload is use to load vast amounts of data from flat files to empty tables of Teradata, with a lighting fast speed. Fast load is specially designed to load millions of rows in Teradata tables in a very less time as compared to other conventional methods.

Fastload can run from either mainframe or network host. In both the cases it has three requirement to execute successfully i.e. – log table, empty table and two error tables.

LOG TABLE – This table is used to record all the progress during execution of Fastload. This table exists in the SYSADMIN database under the name of FASTLOG. The structure of the FASTLOG table is as follows


EMPTY TABLE Fastload needs the table to be empty before inserting rows into that table. It does not care how this is implemented but it requires empty tables without any prior records. If the table is not empty then Fastload will pause and show the error.

We can empty the table in two ways;

Either delete all rows from the table

<delete from DATABASENAME.TABLENAME>

Or

First drop the table then recreate the whole structure once again.

TWO ERROR TABLES – Fastload require two error tables to catch any error which occurs during the execution of the Fastload. It create the error tables itself, all we have to do is to name these tables. Each error tables record error of specific type. The first error table record any translation or constraint violation error.

For e.g. if a row has data which does not match with the data type of its particular column then this row will be captured by error table 1. The second error table captured any error which is related to duplication of values for Unique Primary Indexes (UPI). Fastload will capture only one occurrence of the value and store the duplicate occurrence in the second error table. However if the entire row is duplicated then Fastload count it but does not store the row.

These tables are analyzed later for error handling.


22 comments

Skip to comment form

  1. Rohan

    Hi Team,

    Could you please let me know how can we explicitly specify where the log tables and error tables should get created by the TDCH script when we are using internal.fastload?

    In PROD DB the ID doesn’t have access to create and drop . When we use Internal.fastload in TDCH script it will create and drop the error tables automatically. So do we have any option to give the database name where these log table and error tables needs to be created by the script?

  2. amit jha

    Thanks you all Teradata team…..which providing very useful information.

    1. admin

      You are welcome 🙂

  3. samra

    While running this script , All the records are getting populated in Error table 1 . Can anyone help me whats the error , and why it is not getting loaded in Target Table ?

    .logon localtd/tduser,tduser

    .SET RECORD VARTEXT “|”;

    drop table retail.my_fast;
    drop table retail.my_fast_err1;
    drop table retail.my_fast_err2;

    create table retail.my_fast
    (
    employee_no integer,
    dept_no integer,
    last_name char(10),
    first_name char(10),
    salary integer)
    unique primary index(employee_no);
    define

    employee_no (VARCHAR(15)),
    dept_no (VARCHAR(15)),
    last_name (VARCHAR(15)),
    first_name (VARCHAR(15)),
    salary (VARCHAR(15))

    file=c:\sam\input1.dat;
    ERRLIMIT 15;

    BEGIN LOADING
    retail.my_fast

    ERRORFILES
    retail.my_fast_err1,
    retail.my_fast_err2

    CHECKPOINT 5;
    insert into retail.my_fast
    values
    (
    :employee_no,
    :dept_no,
    :last_name,
    :first_name,
    :salary
    );

    end loading;
    .logoff;

    —————————–flat file —————————–

    |10021 |200 |Sharma|Amit |63000|
    |10001 |100 |Thakur|Ankit|83000|
    |10002 |201 |Arora |Aman |73000|
    |10028 |301 |Samra |Sita |77000|
    |10029 |401 |Berg |Andy |67000|
    |10023 |501 |Ayer |John |52000|

    PLease help , i am new to TD

    1. Prakash

      Hi Samra,

      I am not aware of the bteq scripting but i guess in table you have defined emp no and dept no. as integer where as in define you have mentioned the same as varchar. as the datatype is not matching it will move to error table 1.

      Others pls correct me.

      thanks

  4. career technician

    Your means of describing the whole thing in this article is genuinely nice, every one be able to simply know it, Thanks a lot.

    Stop by my site – career technician

  5. balaji

    Hi,

    The information about Fastload is very useful.
    I have task to load mainframe data to Teradata. Database team given me only the main table name where am going to load. They have not mention about the Error tables.
    In your post it is mentioned Fastload requires two error tables to catch any error during execution. And the error tables will be created by FASTLOAD itself.
    is that meaning that we need to mention any name for error table, so that it will automatically creates the error table during excution ?
    error tables need not to be predefined ?

    Please guide me.

    1. admin

      Hi,
      It’s not mandatory to define the error tables with FASTLOAD.
      In case you are not defining it then FASTLOAD will automatically creates the error table in the same Database as that of target table.

      Hope it helps.
      Thanks

  6. Shari Vigil

    We use PGM=FASTLOAD,PARM=’NOSPIE/’ in JCL and I need to know what that PARM is or does. Job runs on mainframe and writes to Teradata table.

    Thanks.

  7. ashok kumar

    Hi !!!

    Greetings of the day !!!!

    While I am working with fast load script, most of the times a lock is applying on target tables and error tables..i.e., “No operation allowed, table is being loaded”

    what should i do in this situation

    1. Naga krishna

      If u get this error(“Table is being loaded”)

      at this time,

      Don’t drop error tables,locks.Because if u release locks and error tables script moves into inconsistent state.
      There are two ways to rectify this problem.
      1st way:

      Simply rectify errors in the script or file,
      according to our scriptjust comments if any dml statements are used before begin loading.
      Finally run,it starts from last point in the log table.
      2nd way:

      in case of new file to run
      just take a dummy script and run

  8. Vinod

    Nice post 🙂

  9. jyo

    What is the meaning of ‘CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL’ ?
    please explain it….

    1. admin

      CHARACTER SET LATIN –> it means the defulat character type for the particular column are LATIN, you can also give CHARACTER SET UNICODE, CHARACTER SET ASCII etc.
      NOT CASESPECIFIC –> it means the values are not case sensitive e.g ‘TERADATATECH = teradatatech’
      NOT NULL –> self explanatory 🙂

  10. Venkat Vujji

    Hi Admin,

    Thanks for the information on FastLoad. I am a Oracle Developer and I am now learning Teradata which is quite interesting. I understand that Fastloading prerequisite is empty table. But what if I want to add more rows to an existing table which is having some data. I want to append to that table. Is there any other method to append the rows to a table? Please advise.

    Thanks,
    Venkat.

    1. admin

      two things you can do –

      1) make a temporary copy of that table which is exact replica of the actual table, load it with fastload, then insert all the rows from the temp table to actual data.
      Again empty the temp table so that it should be ready for next set of rows coming by fastload.

      2) or use a MULTILOAD …. 😉

      1. Venkat Vujji

        That’s make sence. Thanks for your response.

  11. Raj

    If we load the data in to table by using fastload that should be empty..!!
    My question is if we are loading to non-Empty table ,What will happen?It load the data in to target or it will load in to error table ??And why?? please give reply!!

    1. admin

      hi Raj,

      The main prerequisite of FASTLOAD is that the target table must be empty.
      if you try to load the data through FASTLOAD in the table which is not empty, then it will terminate the fastload and no data will be loaded anywhere.
      it will throw an error that –
      “RDBMS error 2636 : must be empty for fast loading”

      and the return code for FASTLOAD will be = ’12’

      To know other limitations of FASTLOAD you can refer –
      http://www.teradatatech.com/?p=126

  12. Raj

    Eagerly waiting for Part 2 🙂

  13. Raj

    Very useful information about FastLoad.
    Thanks and keep sharing such information ?
    GUD WORK 🙂

    1. admin

      Thanks Raj … look forward for new posts 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>