Teradata Utility Error Handling - Fastload

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.

Teradata Utility Error Handling - Fastload

In the first article in this series on Teradata Error Handling, we introduced the larger architecture and load utility environment. in this article the focus will be on the Fastload Utility and some of the techniques applicable to handling errors, exceptions and failures that can occur within a High Availability system.

Fastload

The Teradata Fastload utility loads data into empty tables. It has the ability to be restarted after a database reset or after a client process or platform failure. A Fastload job step should provide the capability to either restart at the point of failure or to completely rerun the step.

Restarts

Fastload will automatically restart a job after a database failure after the cause of the failure has been fixed and the database has reset. The database configuration must be exactly the same as when the Fastload job was initiated and the Fastload error tables must not have been de-leted. When these conditions are not true the Fastload job must be re-executed from the beginning.

A checkpoint interval can be established as part of the BEGIN LOADING command. This allows a restart to continue reading an input file at the point after the last checkpoint taken before a failure or inter-ruption occurs. The checkpoint interval is set as number of records read for each checkpoint.

When a database reset occurs the Fastload utility will continue to try to re-establish a database connection. When a connection is established the utility will continue the interrupted job automatically. If a check-point has been set it will continue at the point in the input file where the last checkpoint was taken. Specifying a checkpoint interval will de-crease the time needed for a restart but there are tradeoffs. Using checkpoints can increase the execution time of a Fastload job. The following factors should be considered when specifying a checkpoint in-terval:

  • Each checkpoint temporarily halts the multiple session data transfer feature of Teradata Fastload, thereby decreasing the speed of the Teradata Fastload job.
  • For each checkpoint, Teradata Fastload waits for each session to complete sending its current request, which interrupts the data transfer operation.
  • The record size and the size of the Teradata Database influence how often you should specify checkpoints. On a smaller Teradata Database, specify checkpoints:
    • Every 50,000 records if each record is more then 4 KB
    • Every 100,000 records if each record is less than 4 KB
    • On a larger Teradata Database, specify higher (less frequent) checkpoint values.

The procedure used and the Teradata Fastload response to restarting a paused Teradata Fastload job depends on the phase that the Teradata Fastload job was in when it was paused.

A Fastload job that was paused during loading can be can be restarted, either from the beginning or from the most recent checkpoint if the BEGIN LOADING command specified the checkpoint option. A job that was paused during end loading phase will restart from wherever it was interrupted. This is because the Teradata Database uses internal checkpointing during this phase.

A Fastload job that terminates because of a Fastload script error may be restarted after the error is corrected. Commands that have exe-cuted successfully must not be changed in the script. This will cause the restart to fail. If commands that have successfully executed have to be changed the entire Fastload job must be re-executed.

Restart After Database Reset

The following is an example of an automatic Fastload restart after a database reset has been initiated to fix a database error condition.

The Fastload script for this example follows:

LOGON test/testuser,test;
SET RECORD FORMATTED;
DEFINE FILE=C:\DataIntegration\Data\tran1.dat;
SHOW;
DROP TABLE TEST.TRAN_ERR;
DROP TABLE TEST.TRAN;
DROP TABLE TEST.TRANFLERR1;
DROP TABLE TEST.TRANFLERR2;
CREATE SET TABLE TEST.TRAN ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
STORE INTEGER,
TRAN_NO CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
TRAN_DATE DATE FORMAT 'MM/DD/YYYY',
TRAN_TIME TIME(6),
TRAN_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
TRAN_TERMINAL CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
CLERK VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CUST_NO CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
TOTAL_SALE DECIMAL(10,2),
SALES_TAX DECIMAL(10,2),
DISCOUNT DECIMAL(10,2),
DISC_PCT DECIMAL(5,3),
DISC_TAKEN_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
CASH_TENDERED DECIMAL(10,2),
CHANGE_GIVEN DECIMAL(10,2),
CHARGE_TENDERED DECIMAL(10,2),
CHECK_NUMBER VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
CHECK_TENDERED DECIMAL(10,2),
BANKCARD_TENDERED DECIMAL(10,2),
PURCHASE_ORDER_NUM VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( TRAN_NO );
CREATE ERROR TABLE TEST.TRAN_ERR FOR TEST.TRAN;
BEGIN LOADING TEST.TRAN
ERRORFILES TEST.TRANFLERR1, TEST.TRANFLERR2
CHECKPOINT 50000
INDICATORS;
INSERT TEST.TRAN.*;
END LOADING;
LOGOFF;

This script drops the target table and the error tables if they exist. It creates the target table and executes the load for the target table. A checkpoint will be taken every 50000 input records.

The following is the output from a Fastload job that was restarted automatically after a database reset occurred during the execution of the job.

     ===================================================================
= =
= FASTLOAD UTILITY VERSION 12.00.00.004 =
= PLATFORM WIN32 =
= =
===================================================================

===================================================================
= =
= Copyright 1984-2007, Teradata Corporation. =
= ALL RIGHTS RESERVED. =
= =
===================================================================

**** 16:14:34 Processing starting at: Sun Aug 09 16:14:34 2009

===================================================================
= =
= Logon/Connection =
= =
===================================================================

0001 LOGON test/testuser,

**** 16:14:37 Teradata Database Release: 12.00.01.24
**** 16:14:37 Teradata Database Version: 12.00.01.24
**** 16:14:37 Current CLI or RDBMS allows maximum row size: 64K
**** 16:14:37 Character set for this job: ASCII
**** 16:14:38 Number of FastLoad sessions connected = 2
**** 16:14:38 FDL4808 LOGON successful

0002 SET RECORD FORMATTED;

**** 16:14:38 Now set to read 'FORMATTED' records
**** 16:14:38 Command completed successfully

0003 DEFINE FILE=C:\DataIntegration\Data\tran1.dat;

**** 16:14:38 FDL4803 DEFINE statement processed

0004 SHOW;

FILE = C:\DataIntegration\Data\tran1.dat
TOTAL RECORD LENGTH = 0

0005 DROP TABLE TEST.TRAN_ERR;

**** 16:14:38 Command completed successfully

0006 DROP TABLE TEST.TRAN;

**** 16:14:38 Command completed successfully

0007 DROP TABLE TEST.TRANFLERR1;

**** 16:14:38 RDBMS error 3807: Object 'TEST.TRANFLERR1' does not exist.

0008 DROP TABLE TEST.TRANFLERR2;

**** 16:14:38 RDBMS error 3807: Object 'TEST.TRANFLERR2' does not exist.

0009 CREATE SET TABLE TEST.TRAN ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
STORE INTEGER,
TRAN_NO CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
TRAN_DATE DATE FORMAT 'MM/DD/YYYY',
TRAN_TIME TIME(6),
TRAN_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
TRAN_TERMINAL CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
CLERK VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CUST_NO CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
TOTAL_SALE DECIMAL(10,2),
SALES_TAX DECIMAL(10,2),
DISCOUNT DECIMAL(10,2),
DISC_PCT DECIMAL(5,3),
DISC_TAKEN_FLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
CASH_TENDERED DECIMAL(10,2),
CHANGE_GIVEN DECIMAL(10,2),
CHARGE_TENDERED DECIMAL(10,2),
CHECK_NUMBER VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
CHECK_TENDERED DECIMAL(10,2),
BANKCARD_TENDERED DECIMAL(10,2),
PURCHASE_ORDER_NUM VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFI

UNIQUE PRIMARY INDEX ( TRAN_NO );

**** 16:14:38 Command completed successfully

0010 CREATE ERROR TABLE TEST.TRAN_ERR FOR TEST.TRAN;

**** 16:14:38 Command completed successfully

0011 BEGIN LOADING TEST.TRAN
ERRORFILES TEST.TRANFLERR1, TEST.TRANFLERR2
CHECKPOINT 50000
INDICATORS;

**** 16:14:38 Indicator mode is set to ON
**** 16:14:38 Number of AMPs available: 2
**** 16:14:38 BEGIN LOADING COMPLETE

===================================================================
= =
= Insert Phase =
= =
===================================================================

0012 INSERT TEST.TRAN.*;

**** 16:14:39 Number of recs/msg: 316
**** 16:14:39 Starting to send to RDBMS with record 1
**** 16:14:39 Sending row 50000
**** 16:14:40 Sending row 100000
**** 16:14:41 Sending row 150000
**** 16:14:42 Sending row 200000
**** 16:14:42 Sending row 250000
**** 16:15:08 RDBMS error 2825: NO RECORD OF THE LAST REQUEST WAS FOUND
AFTER DBC RESTART
**** 16:15:08 FastLoad will attempt to retry this request 5 time(s)
**** 16:15:08 Teradata Database Release: 12.00.01.24
**** 16:15:08 Teradata Database Version: 12.00.01.24
**** 16:15:08 Current CLI or RDBMS allows maximum row size: 64K
**** 16:15:08 Character set for this job: ASCII
**** 16:15:10 Number of FastLoad sessions connected = 2
**** 16:15:10 FDL4808 LOGON successful
**** 16:15:10 The last checkpoint was taken at row: 250000
**** 16:15:10 FastLoad will now restart at row: 250001
**** 16:15:11 Sending row 300000
**** 16:15:12 Sending row 350000
**** 16:15:13 Sending row 400000
**** 16:15:14 Sending row 450000
**** 16:15:15 Sending row 500000
**** 16:15:16 Sending row 550000
**** 16:15:17 Sending row 600000
**** 16:15:18 Sending row 649472
**** 16:15:18 Finished sending rows to the RDBMS

===================================================================
= =
= End Loading Phase =
= =
===================================================================

0013 END LOADING;

**** 16:15:42 END LOADING COMPLETE

Total Records Read = 649472
Total Error Table 1 = 0 ---- Table has been dropped
Total Error Table 2 = 0 ---- Table has been dropped
Total Inserts Applied = 649472
Total Duplicate Rows = 0

Start: Sun Aug 09 16:15:19 2009
End : Sun Aug 09 16:15:42 2009

0014 LOGOFF;

===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================

**** 16:15:43 Logging off all sessions
**** 16:15:44 Total processor time used = '2.64063 Seconds'
. Start : Sun Aug 09 16:14:34 2009
. End : Sun Aug 09 16:15:44 2009
. Highest return code encountered = '0'.
**** 16:15:44 FDL4818 FastLoad Terminated

Restart after client failure

A Fastload job can be restarted after a client process failure. In this case the error tables and target tables must be in the state they were in when the failure occurred, therefore, a restart script must not in-clude the actions to drop error tables, drop target table and create target table. The following is an example of the restart script.

LOGON test/testuser,test;

SET RECORD FORMATTED;
DEFINE FILE=C:\DataIntegration\Data\tran1.dat;
SHOW;
BEGIN LOADING TEST.TRAN
ERRORFILES TEST.TRANFLERR1, TEST.TRANFLERR2
CHECKPOINT 50000
INDICATORS;
INSERT TEST.TRAN.*;
END LOADING;

LOGOFF;
In this example a client failure occurs while a Fastload job is execut-ing.
===================================================================
= =
= Insert Phase =
= =
===================================================================

0012 INSERT TEST.TRAN.*;

**** 16:33:43 Number of recs/msg: 316
**** 16:33:43 Starting to send to RDBMS with record 1
**** 16:33:44 Sending row 50000
**** 16:33:45 Sending row 100000
**** 16:33:46 Sending row 150000
**** 16:33:47 Sending row 200000
**** 16:33:47 Sending row 250000
**** 16:33:48 Sending row 300000
**** 16:33:49 Sending row 350000
**** 16:33:50 Sending row 400000

When the client failure occurs the Teradata database will log off the Fastload sessions, however, on a network attached client a gateway session will exist until a timeout has expired and the gateway session is cancelled. The default timeout period is 20 minutes. During this time the Fastload job can not be restarted and will return the following error.

C:\DataIntegration\Scripts>fastload <tranrestart.fl ===================================================================
= =
= FASTLOAD UTILITY VERSION 12.00.00.004 =
= PLATFORM WIN32 =
= =
===================================================================
===================================================================
= =
= Copyright 1984-2007, Teradata Corporation. =
= ALL RIGHTS RESERVED. =
= =
===================================================================
**** 16:34:26 Processing starting at: Sun Aug 09 16:34:26 2009
===================================================================
= =
= Logon/Connection =
= =
===================================================================
0001 LOGON test/testuser,
**** 16:34:29 Teradata Database Release: 12.00.01.24
**** 16:34:29 Teradata Database Version: 12.00.01.24
**** 16:34:29 Current CLI or RDBMS allows maximum row size: 64K
**** 16:34:29 Character set for this job: ASCII
**** 16:34:29 Number of FastLoad sessions connected = 2
**** 16:34:29 FDL4808 LOGON successful
0002 SET RECORD FORMATTED;
**** 16:34:29 Now set to read 'FORMATTED' records
**** 16:34:29 Command completed successfully
0003 DEFINE FILE=C:\DataIntegration\Data\tran1.dat;
**** 16:34:29 FDL4803 DEFINE statement processed
0004 SHOW;
FILE = C:\DataIntegration\Data\tran1.dat
TOTAL RECORD LENGTH = 0
0005 BEGIN LOADING TEST.TRAN
ERRORFILES TEST.TRANFLERR1, TEST.TRANFLERR2
CHECKPOINT 50000
INDICATORS;
**** 16:34:29 Indicator mode is set to ON
**** 16:34:29 RDBMS error 2738: TRAN already has Fastload running
===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================
**** 16:34:29 Logging off all sessions
**** 16:34:29 Total processor time used = '0.703125 Seconds'
. Start : Sun Aug 09 16:34:26 2009
. End : Sun Aug 09 16:34:29 2009
. Highest return code encountered = '12'.
**** 16:34:29 FDL4818 Fastload Terminated

The Fastload job can be restarted before the Gateway session timeout if the Gateway sessions for the failed Fastload job are killed. This can be done using the gtwglobal utility in the following way.

C:\Documents and Settings\john>gtwglobal
_______
| | |
| ___ __ ____ | ____ __|__ ____
| / |/ \ ____| ____| ____| | ____|
| --- | / | / | / | | / |
| \___ | \____| \____| \____| |__ \____|

Enter gateway command or enter h for Help:
select host 1

Host 1 has been selected.

Enter gateway command or enter h for Help:
1>kill user testuser
User TESTUSER has 4 sessions killed
1059 1060 1061 1062

Once the Fastload sessions have been killed the Fastload job can be restarted. The following is an example of the output from the restarted Fastload job after a client failure.

C:\DataIntegration\Scripts>fastload <tranrestart.fl
===================================================================
= =
= FASTLOAD UTILITY VERSION 12.00.00.004 =
= PLATFORM WIN32 =
= =
===================================================================

===================================================================
= =
= Copyright 1984-2007, Teradata Corporation. =
= ALL RIGHTS RESERVED. =
= =
===================================================================

**** 16:53:54 Processing starting at: Sun Aug 09 16:53:54 2009

===================================================================
= =
= Logon/Connection =
= =
===================================================================

0001 LOGON test/testuser,

**** 16:53:57 Teradata Database Release: 12.00.01.24
**** 16:53:57 Teradata Database Version: 12.00.01.24
**** 16:53:57 Current CLI or RDBMS allows maximum row size: 64K
**** 16:53:57 Character set for this job: ASCII
**** 16:53:57 Number of FastLoad sessions connected = 2
**** 16:53:57 FDL4808 LOGON successful

0002 SET RECORD FORMATTED;

**** 16:53:57 Now set to read 'FORMATTED' records
**** 16:53:57 Command completed successfully

0003 DEFINE FILE=C:\DataIntegration\Data\tran1.dat;

**** 16:53:57 FDL4803 DEFINE statement processed

0004 SHOW;

FILE = C:\DataIntegration\Data\tran1.dat
TOTAL RECORD LENGTH = 0

0005 BEGIN LOADING TEST.TRAN
ERRORFILES TEST.TRANFLERR1, TEST.TRANFLERR2
CHECKPOINT 50000
INDICATORS;

**** 16:53:57 Indicator mode is set to ON
**** 16:53:57 FastLoad is restarting
**** 16:53:57 Number of AMPs available: 2
**** 16:53:57 BEGIN LOADING COMPLETE

===================================================================
= =
= Insert Phase =
= =
===================================================================

0006 INSERT TEST.TRAN.*;

**** 16:53:57 Number of recs/msg: 316
**** 16:53:57 The last checkpoint was taken at row: 400000
**** 16:53:57 FastLoad will now restart at row: 400001
**** 16:53:58 Sending row 450000
**** 16:53:59 Sending row 500000
**** 16:54:00 Sending row 550000
**** 16:54:01 Sending row 600000
**** 16:54:01 Sending row 649472
**** 16:54:01 Finished sending rows to the RDBMS

===================================================================
= =
= End Loading Phase =
= =
===================================================================

0007 END LOADING;

**** 16:54:21 END LOADING COMPLETE

Total Records Read = 649472
Total Error Table 1 = 0 ---- Table has been dropped
Total Error Table 2 = 0 ---- Table has been dropped
Total Inserts Applied = 649472
Total Duplicate Rows = 0

Start: Sun Aug 09 16:54:02 2009
End : Sun Aug 09 16:54:21 2009

0008 LOGOFF;

===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================

**** 16:54:21 Logging off all sessions
**** 16:54:22 Total processor time used = '1.375 Seconds'
. Start : Sun Aug 09 16:53:54 2009
. End : Sun Aug 09 16:54:22 2009
. Highest return code encountered = '0'.
**** 16:54:22 FDL4818 FastLoad Terminated

Rerun

In some cases a Fastload job may need to be re-executed when the error tables or target tables are not available or the database configuration has changed. In the previous example the original Fastload job can be rerun because it drops the error and target tables and recreates the target table before loading the data.

In the next article in this series we will look at the Multiload Utility. 

4 REPLIES
Fan

Re: Teradata Utility Error Handling - Fastload

Are all of these just portions of different scripts? How can the gtwglobal part be scripted?
Enthusiast

Re: Teradata Utility Error Handling - Fastload

Just a clarification - You mentioned- Restart after client failure- is it same as restartablity caused by database/script issues such as script failing due to data formatting,invalid TIMESTAMP and any other data issues ?

Re: Teradata Utility Error Handling - Fastload

If the gtwglobal part can't be scripted there is another approach to killing sessions from sql. It is referenced in the Workload managemen API : PM/API. The functions are all in a user called syslib. The dba will have to grant you permission to execute the function.

REPLACE FUNCTION AbortSessions
(HostIdIn SMALLINT,
UserNameIn VARCHAR(30),
SessionNoIn INTEGER,
LogoffSessions VARCHAR(1),
UserOverride VARCHAR(1)
)

SELECT AbortSessions (1, 'User14', 0, 'Y', 'Y'); -- Logoff/Kill all sessions where the user User14 is logged on.
Enthusiast

Re: Teradata Utility Error Handling - Fastload

We are getting the following error given below while inserting rows. Does anyone have any idea how to troubleshoot this. 

**** 20:56:21 Sending row 3200000

**** 20:56:45 Sending row 3300000

**** 20:57:09 Sending row 3400000

**** 20:57:11 CLI Error 211: MTDP: EM_NODATA(211): No data received

              from dbc.

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

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

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

**** 20:57:11 Logging off all sessions

**** 20:57:18 Total processor time used = '60.24 Seconds'

     .        Start : Wed Jul 10 20:42:38 2013

     .        End   : Wed Jul 10 20:57:18 2013

     .        Highest return code encountered = '12'.

**** 20:57:18 FastLoad Paused