5 tips for Teradata Fastload

teradata fastload

Teradata Fastload – use effectively

Teradata Fastload is a very fast and efficient tool to load huge amount of data into a Teradata database. It has good documentation to browse as a reference, however there are a few small things good to know to use it more comfortably – either in challenged situations.

Tip1: How to understand “Error1” table

When a record is failed to load because of any Teradata error (conversion, null problem, etc), the record get into “Error1” table, which is set in the “BEGIN LOADING” section of the control script.
The structure of the “Error1” table is fix:

  1. ErrorFieldName:
    Which column was problematic
  2. ErrorCode:
    The Teradata error code, lookup in DBC.ErrorMsgs if documentation is not at your hands
  3. DataParcel
    This filed contains the record transformed into hexa string format

Unfortunately DataParcel’s hexadecimal codes are unreadable for a human in Queryman or other GUIs.
With this short BTEQ script you can export the original record layout into a file:

.SET RECORDMODE ON
.EXPORT DATA FILE=badrecord.out
SELECT DataParcel FROM <Errortable1>;

Tip2: How to switch off deduplication?

There is no way to switch it off. Teradata Fastload will do a full-record-layout deduplication. No matter we use nonunique primary index (NUPI) and MULTISET table definition Teradata will deduplicate eagerly.

If you must load each records, use a preprocessor script (PERL, AWK, etc), which adds a running number column to your data. This way there will be no identical records at all, surely.

Tip3: Deduplication vs. “Error2” table

Why do we have “Error2” table, if Teradata Fastload always does a deduplication? This error table contains those records which passed the full-record-layout deduplication, but failed at the unique primary index (UPI) duplication check. So the data contained other record with the same PI value, but differ in one or more other columns. To load those records either, user NUPI or NO PI table.

Tip4: What is the good “Sessions” number?

SESSIONS is a parameter to be specified at the “BEGIN LOADING” section. Can fall between 1 and “Number-of-AMPs”. The bigger is the number, the better is the loading performance, but the longer is the logon time. So for a small file use 1, for large number of rows, use the maximum allowed. For medium files I generally use 10.

If a lot of small different files to different tables are to be loaded, mind using other technique, since Teradata Fastload will not have advantages in this league.

Tip5: Table is “Being loaded”…

When a Teradata Fastload session is stopped for reaching error limit, the target and the error tables cannot be accessed due to locking:
Error 2652: Operation not allowed: <db>.<table> is being Loaded.

Run this Fastload script to release the locks and get access to the tables:

BEGIN LOADING <db>.<table>;

END LOADING;

After this script finished the error tables are readable to identify the load problem root causes, see the “Error1” table tip above.

How to speed up Teradata Fastexport

About Teradata Fastexport

Fastexport1
Teradata Fastexport is a nice, powerful tool to export mass volume of data to file. Its default execution flow is the following:

  1. Apply locks on the affected tables
  2. Execute the SELECT statement
  3. Places the result in a SPOOL
  4. Releases locks
  5. Exports data from the SPOOL

It has an advantage: the release of locks will happen immediately after the result data is copied into the SPOOL. Terefore need not to wait until the data is written to the export file outside the database – it is typically a single process operation, can last for a long time.

The price to be payed for this advantage: all the exported data must be copied first to the SPOOL. When the SELECT is a simple operation (no join, sort or other extras), the copy phase can be saved, which can approach 50% of the total resource and time consumption.

The NOSPOOL trick

For these simple cases one can use the NOSPOOL/NOSPOOLONLY mode.
While NOSPOOL is a soft switch, which is ignored if not applicable, NOSPOOLONLY will make statement failed if the nospool option is not available for the given query.

In NOSPOOL mode the database will not make a copy of result data. Instead it directly passes the data to the client, which has another benefit: the execution of the SELECT runs dominantly parallel with the file write operation, instead of successively like in SPOOL (default) case.

Usage:
.BEGIN EXPORT
SESSIONS …
[SPOOLMODE SPOOL|NOSPOOL|NOSPOOLONLY];

The tradeoff

The NOSPOOL operation only supports the “simple” SELECT statements. The not supported operations:

  • SORT (ORDER BY, HAVING)
  • JOIN
  • Aggregations (GROUP BY, SUM())
  • OLAP functions
  • Non-table data (date, session, user)
  • Multiple statements

But allows:

  • Sampling
  • Column selection
  • Scalar functions (like col1 + col2, substr())
  • “Where” clause (incl. partition elimination)

The disadvatages of NOSPOOL mode:

  • No ActivityCount is available
  • Locks maintained till the end of the export session
  • Data conversion errors can happen during the exporting phase
  • (Only available from V13.10)

What is it good for indeed?
It can almost double the throughput in case of:

  • DataMart export (to another database)
  • Archive dumps
  • Copy data to test environment
  • Migration to another database systems

Measures

We’ve executed a Teradata to Netezza migration, where got to transport ~12TBytes of data through file interface. After we activated the NOSPOOL option, the average export speed grew up by 84%.

Quicktips – Rowcount

Quick rowcount

Sometimes we are interested in how many records are in a table.
It is easy, we issue the typical rowcount SQL statement:

select count(*) from <table>;

However Teradata can serve this request effectively by only scanning the cylinder index (not all data blocks), it still can last for a long time in case of big tables and extensively loaded systems.

One can have a quite good assumption for the record count from the statistics. This method is implemented in the PRISE Tuning Assistant software

V13.10 and before

help stat <table>;

Choose any existing statistics, the latest is the best (by “Date”;”Time”), and then issue this, replacing “xxx” with the chosen stat colmn(s):

help stat <table> column (xxx);

The “Number of rows” column shows the estimated record count. The same value appears for each buckets, take the value in the first row.

V14.00 and after

select databasename, tablename, RowCount,LastCollectTimestamp from dbc.statsv
where databasename='<db>' and tablename='<table>'
qualify sum(1) over (partition by databasename,tablename order by lastcollecttimestamp desc rows unbounded preceding) = 1
;

Please note that this data may be an estimation (sample statistics) or outdated (stale statistics).

Without statistics

In this case we can have deduct the order of magnitude from the allocated size of the table.
Naturally, the recordcount strongly depends on the average record size.

select databasename,tablename, sum(currentperm) /1024/1024 Table_MBytes
from dbc.allspaceV
where databasename='<db>' and tablename = '<table>'
group by 1,2;

QUICKTIPS – DELETE or CREATE+INSERT?

Delete or Insert? Which one is faster?

The question

If I got to delete rows from a table, practically I have two options:

  • Issue a DELETE command with the desired WHERE / JOIN condition
  • Create an empty table with the same structure and INSERT the remaining records using the negated WHERE condition and the rename tables
The first one is simple and straightforward, the second is complicated and will require more perm space. So it is a question?
Yes, it is. Because of the backgrounds of the execution:
  • DELETE
    It will modify each blocks that contain record(s) to be deleted. It has to write Transient Journal (transaction log) to enable transaction related functionality. In addition those data blocks which fill-up-rate falls below a threshold (eg. 50%) they will be merged. All these may cause really lot of I/O overhead. In some cases the deletion of the rows will happen in “random-like” order therefore the I/O access will be random, which is painfully slow.
  • INSERT
    In this case it is quite simple: we insert into an empty table, which causes almost zero Transient Journal data, and the system has to write only that amount of data blocks, what can store the records that will remain, once each block. An additional advantage: the I/O will always be “linear” instead of “random-like” therefore it can be multi-block operation, which is pretty fast.

Shall we forget about the delete?

The answer

No, absolutely not. DELETE is good enough if the amount of the records that must be deleted is not that much.
We have two factors that will help us in the decision:

  • How many records to be deleted?
  • How many records will remain after deletion?

I do not have well defined universal thresholds where it is worth to choose this or that, it depends.
I used to use the CREATE+INSERT+RENAME method if

    • The table is big enough (> > 0,1-0,5Gbytes or 10-50k rows / AMP)
      and
      I got to delete more than 20..40% of the records
    OR
  • The table has high hash collision (HC) rate on the PI
    and
    The table is MULTISET*
    and
    The delete is a JOIN-ed one

So it depends, got to measure. PRISE Tuning Assistant can help you to build up your own rule of thumb, applicable on your environment, by showing the resource consumption in pretty details.

* If the HC rate is high and the table is SET then the INSERT will be very slow because of the duplication check method

QUICKTIPS – DELETE+INSERT – together or separetely?

Shall I run DELETE and INSERT separately?

We often have permanent or temporary tables in our data warehouse which must be purged before they are populated again. We can do the operation within a transaction or separately. It is not the same…

Options – (example)

The examples assume Teradata (BTET)  mode, which is Auto-commit by default

  1. Together: both statements run in the same transaction
    1. Explicit transaction
      begin transaction;
      delete from TABLE1;
      insert into TABLE1 SELECT …..;
      end transaction;

    2. Multi-statement
      delete from TABLE1
      ;insert into TABLE1 SELECT …..;
  2. Separately: the statements run in separate transaction
    delete from TABLE1 [ALL];
    insert into TABLE1 SELECT …..;

     (Please note if you run it in Teradata Administrator aka. WinDDI, this will be run in one explicit transaction, since the application will place a BT; before the script and an ET; after.)

    In this case the ALL clause is only optics: the DELETE without a WHERE condition is handled internally as “ALL”.

What is the difference?

The result

If everything is OK, we will not experience any difference at the end – from result aspect. The same data will be found in TABLE1 after we finished each ways. The difference shows if the INSERT step fails eg. for running out of perm space:

  • In “A.” cases the all transaction will be rolled back, therefore we will see all the data in the table we had before the delete.
  • In “B.” case we will delete the data from the tab, independently the success of the INSERT phase. After a failed INSERT the table will be empty

We have another additional transient difference:

  • In A. case we will not “see” an empty table from another session in any point of time: until a point the table seems as a “before” version, and when the transaction finished it will switch to an “after” image for other sessions in no time.
  • In B. case when the DELETE finished, the table seems to be empty up to the moment of COMMITing the INSERT statement. So if the INSERT lasts 2 hours, others will face and empty TABLE1 for 2 hours.

So I do not recommend to use option B. if the “empty table” state is not valid.

The performance

The A. and B. options will be executed significantly different manner. The DELETE operation will know that all the data will be dropped, so internally it just administers the data blocks to be free instead of clearing their content record-by-record, BUT only if the statement is the only statement in the transaction! In this case only the data block administration must be logged (to be able to roll back for some reason) instead of every block changes. This results far less Transient Journal activity besides less block I/Os.

Let’s see some measurements.
I’ve put 10M records into a table, and then executed the same DELETE + INSERT statement on it. The results are the following:

  1. CPU:    9  sec I/O: 39K
  2. CPU  >0.1sec I/O:   4K

It is worth to mind the difference if you got to do it with billions of records.