The Teradata Multiload utility provides the capability to perform batch maintenance on tables (insert, update, delete and upsert). It loads data from external sources and provides the capability to restart jobs interrupted by errors, exceptions and failures.
A Multiload job executes in 5 phases:
1. Preliminary
2. DML Transaction
3. Acquisition
4. Application
5. Cleanup
Restarts
A Multiload job can not be restarted or rerun if these tables are not present for an interruption that occurs during the APPLICATION PHASE.
Interruptions that occur during the application phase will be restarted based on internal checkpoints created by the database.
The following is a simple Multiload script that will be use to illustrate the Multiload restart capabilities.
A Multiload job executes in 5 phases:
1. Preliminary
- Parses and validates all of the MultiLoad commands and Tera-data SQL statements in your MultiLoad job
- Establishes sessions and process control with the Teradata Database
- Submits special Teradata SQL requests to the Teradata Data-base
- Creates and protects temporary work tables and error tables in the Teradata Database
2. DML Transaction
- Submits the DML statements specifying the insert, update, and delete tasks to the Teradata Database
3. Acquisition
- Imports data from the specified input data source
- Evaluates each record according to specified application con-ditions
- Loads the selected records into the worktables in the Tera-data Database
- There is no acquisition phase activity for a MultiLoad delete task.
4. Application
- Acquires locks on the specified target tables and views in the Teradata Database
- For an import task, inserts the data from the temporary work tables into the target tables or views in the Teradata Data-base
- For a delete task, deletes the specified rows from the target table in the Teradata Database
- Updates the error tables associated with each MultiLoad task
5. Cleanup
- Forces an automatic restart/rebuild if an AMP went offline and came back online during the application phase
- Releases all locks on the target tables and views
- Drops the temporary work tables and all empty error tables from the Teradata Database
- Reports the transaction statistics associated with the import and delete tasks.
- Multiload returns the returns codes described previously (0, 4, 8, 12) to the operating system or calling program.
Restarts
It is possible to restart a Multiload job in any of the phases after an error or failure has interrupted an executing Multiload job. There are differences in the restart procedures in some of the phases that will be described. It is also possible to rerun an interrupted Multiload job in some of the phases which will also be discussed.
A Multiload job creates a restart log table that must be present for a job to be restarted and must be dropped before a job can be rerun. It creates a work table for each input source. These tables must be pre-sent for a job to restart and must be dropped to rerun a job. Multiload also creates two error tables for each target table that must be present for a restart and that must be dropped to rerun a Multiload job.
A Multiload job can not be restarted or rerun if these tables are not present for an interruption that occurs during the APPLICATION PHASE.
Checkpoint frequency can be set for checkpoint to occur after a number of records have been read from an input source. The checkpoints are taken during the acquisition phase. A restart after an interruption in the acquisition phase will start reading the input source at the point following the last checkpoint executed before the interruption.
The following is a simple Multiload script that will be use to illustrate the Multiload restart capabilities.
.logtable test.tranmllog;
.LOGON test/testuser,test;
.begin import
mload tables test.tran
checkpoint 20000;
.layout tranlayout;
.FIELD STORE * varchar(10);
.field TRAN_NO * varchar (10);
.field TRAN_DATE * varchar (10);
.field TRAN_TIME * varchar (10);
.field TRAN_TYPE * varchar (1);
.field TRAN_TERMINAL * varchar (3);
.field CLERK * varchar (10);
.field CUST_NO * varchar (10);
.field NAME * varchar (30);
.field TOTAL_SALE * varchar (14);
.field SALES_TAX * varchar (14);
.field DISCOUNT * varchar (14);
.field DISC_PCT * varchar (10);
.field DISC_TAKEN_FLAG * varchar (1);
.field CASH_TENDERED * varchar (14);
.field CHANGE_GIVEN * varchar (14);
.field CHARGE_TENDERED* varchar (14);
.field CHECK_NUMBER * varchar (20);
.field CHECK_TENDERED * varchar (14);
.field BANKCARD_TENDERED * varchar (14);
.field PURCHASE_ORDER_NUM * varchar (20);
.dml label insdml;
insert into test.tran.*;
.import infile C:\DataIntegration\Data\TRAN2.DAT
format vartext '|'
layout tranlayout
apply insdml;
.end mload;
.logoff;
This script inserts rows into one table, “TRAN”. It set a checkpoint every 20000 input records.
Restart after database reset
The following is an example of an automatic restart that occurs for an interruption and reset that occurred during the acquisition phase.
C:\DataIntegration\Scripts>mload -b < tran.ml
**** 22:11:09 UTY2414 BRIEF option is enabled.
========================================================================
= MultiLoad Utility Release MLOD.13.00.00.000 =
= Platform WIN32 =
========================================================================
= Copyright 1990-2009, Teradata Corporation. ALL RIGHTS RESERVED. =
========================================================================
**** 22:11:09 UTY2411 Processing start date: SUN AUG 09, 2009
========================================================================
= Logon/Connection =
========================================================================
0001 .logtable test.tranmllog;
0002 .LOGON test/testuser,;
**** 22:11:11 UTY8400 Teradata Database Release: 12.00.01.24
**** 22:11:11 UTY8400 Teradata Database Version: 12.00.01.24
**** 22:11:11 UTY8400 Default character set: ASCII
**** 22:11:11 UTY8400 Current RDBMS has interval support
**** 22:11:11 UTY8400 Current RDBMS has UDT support
**** 22:11:11 UTY8400 Maximum supported buffer size: 1M
**** 22:11:11 UTY8400 Data Encryption supported by RDBMS server
**** 22:11:12 UTY6211 A successful connect was made to the RDBMS.
**** 22:11:12 UTY6217 Logtable 'test.tranmllog' has been created.
0003 .begin import
mload tables test.tran
checkpoint 20000;
========================================================================
= Processing MultiLoad Statements =
========================================================================
0004 .layout tranlayout;
0005 .FIELD STORE * varchar(10);
0006 .field TRAN_NO * varchar (10);
0007 .field TRAN_DATE * varchar (10);
0008 .field TRAN_TIME * varchar (10);
0009 .field TRAN_TYPE * varchar (1);
0010 .field TRAN_TERMINAL * varchar (3);
0011 .field CLERK * varchar (10);
0012 .field CUST_NO * varchar (10);
0013 .field NAME * varchar (30);
0014 .field TOTAL_SALE * varchar (14);
0015 .field SALES_TAX * varchar (14);
0016 .field DISCOUNT * varchar (14);
0017 .field DISC_PCT * varchar (10);
0018 .field DISC_TAKEN_FLAG * varchar (1);
0019 .field CASH_TENDERED * varchar (14);
0020 .field CHANGE_GIVEN * varchar (14);
0021 .field CHARGE_TENDERED* varchar (14);
0022 .field CHECK_NUMBER * varchar (20);
0023 .field CHECK_TENDERED * varchar (14);
0024 .field BANKCARD_TENDERED * varchar (14);
0025 .field PURCHASE_ORDER_NUM * varchar (20);
0026 .dml label insdml;
0027 insert into test.tran.*;
0028 .import infile C:\DataIntegration\Data\TRAN2.DAT
format vartext '|'
layout tranlayout
apply insdml;
0029 .end mload;
========================================================================
= MultiLoad Initial Phase =
========================================================================
**** 22:11:12 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: 20000 records.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 22:11:13 UTY0815 MLOAD session(s) connected: 2.
========================================================================
= MultiLoad Acquisition Phase =
========================================================================
**** 22:11:16 UTY1812 A checkpoint is being initiated because 20000 output recor
ds have been
sent to the RDBMS.
**** 22:11:17 UTY0827 A checkpoint has been taken, recording that input record 2
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:11:17 UTY1812 A checkpoint is being initiated because 40000 output recor
ds have been
sent to the RDBMS.
**** 22:11:43 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 2;
**** 22:11:43 UTY0806 RDBMS error, 2825: NO RECORD OF THE LAST REQUEST WAS FOUND
AFTER DBC
RESTART
**** 22:11:43 UTY1804 Re-starting this MultiLoad task because The RDBMS crashed.
========================================================================
= MultiLoad Initial Phase =
========================================================================
**** 22:11:43 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: 20000 records.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 22:11:44 UTY0815 MLOAD session(s) connected: 2.
========================================================================
= MultiLoad Acquisition Phase =
========================================================================
**** 22:11:46 UTY1806 This MultiLoad Import task is re-starting from checkpoint
taken at
record 20000 of IMPORT 1.
**** 22:11:47 UTY1812 A checkpoint is being initiated because 40000 output recor
ds have been
sent to the RDBMS.
**** 22:11:48 UTY0827 A checkpoint has been taken, recording that input record 4
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:11:49 UTY1812 A checkpoint is being initiated because 60000 output recor
ds have been
sent to the RDBMS.
**** 22:11:50 UTY0827 A checkpoint has been taken, recording that input record 6
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:11:51 UTY0826 A checkpoint has been taken, recording that end of file ha
s been reached
for IMPORT 1 of this MultiLoad Import task.
**** 22:11:51 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 67817....... 67817
Apply conditions satisfied:.......... 67817....... 67817
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
========================================================================
= MultiLoad Application Phase =
========================================================================
**** 22:12:10 UTY0818 Statistics for table test.tran:
Inserts: 67817
Updates: 0
Deletes: 0
========================================================================
= MultiLoad Task Cleanup =
========================================================================
**** 22:12:10 UTY0821 Error table test.ET_tran is EMPTY, dropping table.
**** 22:12:11 UTY0821 Error table test.UV_tran is EMPTY, dropping table.
**** 22:12:11 UTY0825 Error table statistics for:
Target table 1: test.tran
Number of Rows Error Table Name
============== ========================================================
0 test.ET_tran
0 test.UV_tran
**** 22:12:12 UTY0822 MultiLoad processing complete for this MultiLoad import ta
sk.
========================================================================
= MultiLoad Task Complete =
========================================================================
0030 .logoff;
========================================================================
= Logoff/Disconnect =
========================================================================
**** 22:12:12 UTY6216 The restart log table has been dropped.
**** 22:12:12 UTY6212 A successful disconnect was made from the RDBMS.
**** 22:12:12 UTY2410 Total processor time used = '1.625 Seconds'
. Start : 22:11:09 - SUN AUG 09, 2009
. End : 22:12:12 - SUN AUG 09, 2009
. Highest return code encountered = '0'.
An automatic restart may also occur if an interruption occurs during the application phase. The following output illustrates this type of re-start.
C:\DataIntegration\Scripts>mload -b < tran.ml
**** 22:22:57 UTY2414 BRIEF option is enabled.
========================================================================
= MultiLoad Utility Release MLOD.13.00.00.000 =
= Platform WIN32 =
========================================================================
= Copyright 1990-2009, Teradata Corporation. ALL RIGHTS RESERVED. =
========================================================================
**** 22:22:57 UTY2411 Processing start date: SUN AUG 09, 2009
========================================================================
= Logon/Connection =
========================================================================
0001 .logtable test.tranmllog;
0002 .LOGON test/testuser,;
**** 22:23:00 UTY8400 Teradata Database Release: 12.00.01.24
**** 22:23:00 UTY8400 Teradata Database Version: 12.00.01.24
**** 22:23:00 UTY8400 Default character set: ASCII
**** 22:23:00 UTY8400 Current RDBMS has interval support
**** 22:23:00 UTY8400 Current RDBMS has UDT support
**** 22:23:00 UTY8400 Maximum supported buffer size: 1M
**** 22:23:00 UTY8400 Data Encryption supported by RDBMS server
**** 22:23:00 UTY6211 A successful connect was made to the RDBMS.
**** 22:23:00 UTY6217 Logtable 'test.tranmllog' has been created.
0003 .begin import
mload tables test.tran
checkpoint 20000;
========================================================================
= Processing MultiLoad Statements =
========================================================================
0004 .layout tranlayout;
0005 .FIELD STORE * varchar(10);
0006 .field TRAN_NO * varchar (10);
0007 .field TRAN_DATE * varchar (10);
0008 .field TRAN_TIME * varchar (10);
0009 .field TRAN_TYPE * varchar (1);
0010 .field TRAN_TERMINAL * varchar (3);
0011 .field CLERK * varchar (10);
0012 .field CUST_NO * varchar (10);
0013 .field NAME * varchar (30);
0014 .field TOTAL_SALE * varchar (14);
0015 .field SALES_TAX * varchar (14);
0016 .field DISCOUNT * varchar (14);
0017 .field DISC_PCT * varchar (10);
0018 .field DISC_TAKEN_FLAG * varchar (1);
0019 .field CASH_TENDERED * varchar (14);
0020 .field CHANGE_GIVEN * varchar (14);
0021 .field CHARGE_TENDERED* varchar (14);
0022 .field CHECK_NUMBER * varchar (20);
0023 .field CHECK_TENDERED * varchar (14);
0024 .field BANKCARD_TENDERED * varchar (14);
0025 .field PURCHASE_ORDER_NUM * varchar (20);
0026 .dml label insdml;
0027 insert into test.tran.*;
0028 .import infile C:\DataIntegration\Data\TRAN2.DAT
format vartext '|'
layout tranlayout
apply insdml;
0029 .end mload;
========================================================================
= MultiLoad Initial Phase =
========================================================================
**** 22:23:00 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: 20000 records.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 22:23:01 UTY0815 MLOAD session(s) connected: 2.
========================================================================
= MultiLoad Acquisition Phase =
========================================================================
**** 22:23:04 UTY1812 A checkpoint is being initiated because 20000 output recor
ds have been
sent to the RDBMS.
**** 22:23:05 UTY0827 A checkpoint has been taken, recording that input record 2
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:23:06 UTY1812 A checkpoint is being initiated because 40000 output recor
ds have been
sent to the RDBMS.
**** 22:23:07 UTY0827 A checkpoint has been taken, recording that input record 4
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:23:07 UTY1812 A checkpoint is being initiated because 60000 output recor
ds have been
sent to the RDBMS.
**** 22:23:08 UTY0827 A checkpoint has been taken, recording that input record 6
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:23:10 UTY0826 A checkpoint has been taken, recording that end of file ha
s been reached
for IMPORT 1 of this MultiLoad Import task.
**** 22:23:10 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 67817....... 67817
Apply conditions satisfied:.......... 67817....... 67817
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
========================================================================
= MultiLoad Application Phase =
========================================================================
**** 22:24:19 UTY0817 MultiLoad submitting the following request:
EXEC MLOAD test.tran;
**** 22:24:19 UTY0805 RDBMS failure, 2828: Request was rolled back during system
recovery.
**** 22:24:19 UTY1804 Re-starting this MultiLoad task because The RDBMS crashed.
========================================================================
= MultiLoad Initial Phase =
========================================================================
**** 22:24:19 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: 20000 records.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 22:24:20 UTY0815 MLOAD session(s) connected: 2.
========================================================================
= MultiLoad Application Phase =
========================================================================
**** 22:24:27 UTY0818 Statistics for table test.tran:
Inserts: 67817
Updates: 0
Deletes: 0
========================================================================
= MultiLoad Task Cleanup =
========================================================================
**** 22:24:27 UTY0821 Error table test.ET_tran is EMPTY, dropping table.
**** 22:24:27 UTY0821 Error table test.UV_tran is EMPTY, dropping table.
**** 22:24:27 UTY0825 Error table statistics for:
Target table 1: test.tran
Number of Rows Error Table Name
============== ========================================================
0 test.ET_tran
0 test.UV_tran
**** 22:24:28 UTY0822 MultiLoad processing complete for this MultiLoad import ta
sk.
========================================================================
= MultiLoad Task Complete =
========================================================================
0030 .logoff;
========================================================================
= Logoff/Disconnect =
========================================================================
**** 22:24:29 UTY6216 The restart log table has been dropped.
**** 22:24:29 UTY6212 A successful disconnect was made from the RDBMS.
**** 22:24:29 UTY2410 Total processor time used = '1.70313 Seconds'
. Start : 22:22:57 - SUN AUG 09, 2009
. End : 22:24:29 - SUN AUG 09, 2009
. Highest return code encountered = '0'.
Restarts after client failure
A Multiload job may be restarted after it is interrupted by a failure of the Multiload process or the client processing platform. This type of er-ror may be a system error or can be a programming error or data er-ror. The system can be restarted by correcting the problem and re-running the Multiload job. The following output is an example of a re-start for a failure of a Multiload process during the acquisition phase.
C:\DataIntegration\Scripts>mload -b < tran.ml
**** 22:35:51 UTY2414 BRIEF option is enabled.
========================================================================
= MultiLoad Utility Release MLOD.13.00.00.000 =
= Platform WIN32 =
========================================================================
= Copyright 1990-2009, Teradata Corporation. ALL RIGHTS RESERVED. =
========================================================================
**** 22:35:51 UTY2411 Processing start date: SUN AUG 09, 2009
========================================================================
= Logon/Connection =
========================================================================
0001 .logtable test.tranmllog;
0002 .LOGON test/testuser,;
**** 22:35:53 UTY8400 Teradata Database Release: 12.00.01.24
**** 22:35:53 UTY8400 Teradata Database Version: 12.00.01.24
**** 22:35:53 UTY8400 Default character set: ASCII
**** 22:35:53 UTY8400 Current RDBMS has interval support
**** 22:35:53 UTY8400 Current RDBMS has UDT support
**** 22:35:53 UTY8400 Maximum supported buffer size: 1M
**** 22:35:53 UTY8400 Data Encryption supported by RDBMS server
**** 22:35:54 UTY6211 A successful connect was made to the RDBMS.
**** 22:35:54 UTY6217 Logtable 'test.tranmllog' has been created.
0003 .begin import
mload tables test.tran
checkpoint 20000;
========================================================================
= Processing MultiLoad Statements =
========================================================================
0004 .layout tranlayout;
0005 .FIELD STORE * varchar(10);
0006 .field TRAN_NO * varchar (10);
0007 .field TRAN_DATE * varchar (10);
0008 .field TRAN_TIME * varchar (10);
0009 .field TRAN_TYPE * varchar (1);
0010 .field TRAN_TERMINAL * varchar (3);
0011 .field CLERK * varchar (10);
0012 .field CUST_NO * varchar (10);
0013 .field NAME * varchar (30);
0014 .field TOTAL_SALE * varchar (14);
0015 .field SALES_TAX * varchar (14);
0016 .field DISCOUNT * varchar (14);
0017 .field DISC_PCT * varchar (10);
0018 .field DISC_TAKEN_FLAG * varchar (1);
0019 .field CASH_TENDERED * varchar (14);
0020 .field CHANGE_GIVEN * varchar (14);
0021 .field CHARGE_TENDERED* varchar (14);
0022 .field CHECK_NUMBER * varchar (20);
0023 .field CHECK_TENDERED * varchar (14);
0024 .field BANKCARD_TENDERED * varchar (14);
0025 .field PURCHASE_ORDER_NUM * varchar (20);
0026 .dml label insdml;
0027 insert into test.tran.*;
0028 .import infile C:\DataIntegration\Data\TRAN2.DAT
format vartext '|'
layout tranlayout
apply insdml;
0029 .end mload;
========================================================================
= MultiLoad Initial Phase =
========================================================================
**** 22:35:54 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: 20000 records.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 22:35:54 UTY0815 MLOAD session(s) connected: 2.
========================================================================
= MultiLoad Acquisition Phase =
========================================================================
**** 22:35:57 UTY1812 A checkpoint is being initiated because 20000 output reco
ds have been
sent to the RDBMS.
**** 22:35:58 UTY0827 A checkpoint has been taken, recording that input record
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:35:59 UTY1812 A checkpoint is being initiated because 40000 output reco
ds have been
sent to the RDBMS.
**** 22:36:00 UTY0827 A checkpoint has been taken, recording that input record
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:36:01 UTY1812 A checkpoint is being initiated because 60000 output reco
ds have been
sent to the RDBMS.
The gateway sessions associated with the Multiload job will remain ac-tive for the gateway timeout time (20 minutes default). These sessions can be killed using the gtwglobal utility in the same manner that was described for the Fastload utility.
The interrupted Multiload job can be restarted by re-executing the job. The following output is from a re-execution of the example Multiload job.
C:\DataIntegration\Scripts>mload -b < tran.ml
**** 22:42:35 UTY2414 BRIEF option is enabled.
========================================================================
= MultiLoad Utility Release MLOD.13.00.00.000 =
= Platform WIN32 =
========================================================================
= Copyright 1990-2009, Teradata Corporation. ALL RIGHTS RESERVED. =
========================================================================
**** 22:42:35 UTY2411 Processing start date: SUN AUG 09, 2009
========================================================================
= Logon/Connection =
========================================================================
0001 .logtable test.tranmllog;
0002 .LOGON test/testuser,;
**** 22:42:38 UTY8400 Teradata Database Release: 12.00.01.24
**** 22:42:38 UTY8400 Teradata Database Version: 12.00.01.24
**** 22:42:38 UTY8400 Default character set: ASCII
**** 22:42:38 UTY8400 Current RDBMS has interval support
**** 22:42:38 UTY8400 Current RDBMS has UDT support
**** 22:42:38 UTY8400 Maximum supported buffer size: 1M
**** 22:42:38 UTY8400 Data Encryption supported by RDBMS server
**** 22:42:38 UTY6211 A successful connect was made to the RDBMS.
**** 22:42:38 UTY6210 Logtable 'test.tranmllog' indicates that a restart is in p
rogress.
0003 .begin import
mload tables test.tran
checkpoint 20000;
========================================================================
= Processing MultiLoad Statements =
========================================================================
0004 .layout tranlayout;
0005 .FIELD STORE * varchar(10);
0006 .field TRAN_NO * varchar (10);
0007 .field TRAN_DATE * varchar (10);
0008 .field TRAN_TIME * varchar (10);
0009 .field TRAN_TYPE * varchar (1);
0010 .field TRAN_TERMINAL * varchar (3);
0011 .field CLERK * varchar (10);
0012 .field CUST_NO * varchar (10);
0013 .field NAME * varchar (30);
0014 .field TOTAL_SALE * varchar (14);
0015 .field SALES_TAX * varchar (14);
0016 .field DISCOUNT * varchar (14);
0017 .field DISC_PCT * varchar (10);
0018 .field DISC_TAKEN_FLAG * varchar (1);
0019 .field CASH_TENDERED * varchar (14);
0020 .field CHANGE_GIVEN * varchar (14);
0021 .field CHARGE_TENDERED* varchar (14);
0022 .field CHECK_NUMBER * varchar (20);
0023 .field CHECK_TENDERED * varchar (14);
0024 .field BANKCARD_TENDERED * varchar (14);
0025 .field PURCHASE_ORDER_NUM * varchar (20);
0026 .dml label insdml;
0027 insert into test.tran.*;
0028 .import infile C:\DataIntegration\Data\TRAN2.DAT
format vartext '|'
layout tranlayout
apply insdml;
0029 .end mload;
========================================================================
= MultiLoad Initial Phase =
========================================================================
**** 22:42:38 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: 20000 records.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 22:42:39 UTY0815 MLOAD session(s) connected: 2.
========================================================================
= MultiLoad Acquisition Phase =
========================================================================
**** 22:42:40 UTY1806 This MultiLoad Import task is re-starting from checkpoint
taken at
record 40000 of IMPORT 1.
**** 22:42:40 UTY1812 A checkpoint is being initiated because 60000 output recor
ds have been
sent to the RDBMS.
**** 22:42:41 UTY0827 A checkpoint has been taken, recording that input record 6
0000 has been
processed for IMPORT 1 of this MultiLoad Import task.
**** 22:42:43 UTY0826 A checkpoint has been taken, recording that end of file ha
s been reached
for IMPORT 1 of this MultiLoad Import task.
**** 22:42:43 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 67817....... 67817
Apply conditions satisfied:.......... 67817....... 67817
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
========================================================================
= MultiLoad Application Phase =
========================================================================
**** 22:42:57 UTY0818 Statistics for table test.tran:
Inserts: 67817
Updates: 0
Deletes: 0
========================================================================
= MultiLoad Task Cleanup =
========================================================================
**** 22:42:57 UTY0821 Error table test.ET_tran is EMPTY, dropping table.
**** 22:42:57 UTY0821 Error table test.UV_tran is EMPTY, dropping table.
**** 22:42:57 UTY0825 Error table statistics for:
Target table 1: test.tran
Number of Rows Error Table Name
============== ========================================================
0 test.ET_tran
0 test.UV_tran
**** 22:42:58 UTY0822 MultiLoad processing complete for this MultiLoad import ta
sk.
========================================================================
= MultiLoad Task Complete =
========================================================================
0030 .logoff;
========================================================================
= Logoff/Disconnect =
========================================================================
**** 22:42:59 UTY6216 The restart log table has been dropped.
**** 22:42:59 UTY6212 A successful disconnect was made from the RDBMS.
**** 22:42:59 UTY2410 Total processor time used = '0.8125 Seconds'
. Start : 22:42:35 - SUN AUG 09, 2009
. End : 22:42:59 - SUN AUG 09, 2009
. Highest return code encountered = '0'.
A Multiload job may also be restarted if interrupted during the application phase.
It is important to note that the restart log and error tables must be available. If they are not the Multiload job can not be restarted or rerun. In this case the table must be reloaded from backup or source because they are unusable.
The following output is an example of a Multiload job restarted after an interruption during the application phase.
C:\DataIntegration\Scripts>mload -b < tran.ml
**** 22:53:17 UTY2414 BRIEF option is enabled.
========================================================================
= MultiLoad Utility Release MLOD.13.00.00.000 =
= Platform WIN32 =
========================================================================
= Copyright 1990-2009, Teradata Corporation. ALL RIGHTS RESERVED. =
========================================================================
**** 22:53:17 UTY2411 Processing start date: SUN AUG 09, 2009
========================================================================
= Logon/Connection =
========================================================================
0001 .logtable test.tranmllog;
0002 .LOGON test/testuser,;
**** 22:53:19 UTY8400 Teradata Database Release: 12.00.01.24
**** 22:53:19 UTY8400 Teradata Database Version: 12.00.01.24
**** 22:53:19 UTY8400 Default character set: ASCII
**** 22:53:19 UTY8400 Current RDBMS has interval support
**** 22:53:19 UTY8400 Current RDBMS has UDT support
**** 22:53:19 UTY8400 Maximum supported buffer size: 1M
**** 22:53:19 UTY8400 Data Encryption supported by RDBMS server
**** 22:53:19 UTY6211 A successful connect was made to the RDBMS.
**** 22:53:19 UTY6210 Logtable 'test.tranmllog' indicates that a restart is in p
rogress.
0003 .begin import
mload tables test.tran
checkpoint 20000;
========================================================================
= Processing MultiLoad Statements =
========================================================================
0004 .layout tranlayout;
0005 .FIELD STORE * varchar(10);
0006 .field TRAN_NO * varchar (10);
0007 .field TRAN_DATE * varchar (10);
0008 .field TRAN_TIME * varchar (10);
0009 .field TRAN_TYPE * varchar (1);
0010 .field TRAN_TERMINAL * varchar (3);
0011 .field CLERK * varchar (10);
0012 .field CUST_NO * varchar (10);
0013 .field NAME * varchar (30);
0014 .field TOTAL_SALE * varchar (14);
0015 .field SALES_TAX * varchar (14);
0016 .field DISCOUNT * varchar (14);
0017 .field DISC_PCT * varchar (10);
0018 .field DISC_TAKEN_FLAG * varchar (1);
0019 .field CASH_TENDERED * varchar (14);
0020 .field CHANGE_GIVEN * varchar (14);
0021 .field CHARGE_TENDERED* varchar (14);
0022 .field CHECK_NUMBER * varchar (20);
0023 .field CHECK_TENDERED * varchar (14);
0024 .field BANKCARD_TENDERED * varchar (14);
0025 .field PURCHASE_ORDER_NUM * varchar (20);
0026 .dml label insdml;
0027 insert into test.tran.*;
0028 .import infile C:\DataIntegration\Data\TRAN2.DAT
format vartext '|'
layout tranlayout
apply insdml;
0029 .end mload;
========================================================================
= MultiLoad Initial Phase =
========================================================================
**** 22:53:19 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: 20000 records.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 22:53:20 UTY0815 MLOAD session(s) connected: 2.
========================================================================
= MultiLoad Application Phase =
========================================================================
**** 22:53:25 UTY0818 Statistics for table test.tran:
Inserts: 67817
Updates: 0
Deletes: 0
========================================================================
= MultiLoad Task Cleanup =
========================================================================
**** 22:53:25 UTY0821 Error table test.ET_tran is EMPTY, dropping table.
**** 22:53:26 UTY0821 Error table test.UV_tran is EMPTY, dropping table.
**** 22:53:26 UTY0825 Error table statistics for:
Target table 1: test.tran
Number of Rows Error Table Name
============== ========================================================
0 test.ET_tran
0 test.UV_tran
**** 22:53:27 UTY0822 MultiLoad processing complete for this MultiLoad import ta
sk.
========================================================================
= MultiLoad Task Complete =
========================================================================
0030 .logoff;
========================================================================
= Logoff/Disconnect =
========================================================================
**** 22:53:27 UTY6216 The restart log table has been dropped.
**** 22:53:27 UTY6212 A successful disconnect was made from the RDBMS.
**** 22:53:27 UTY2410 Total processor time used = '0.671875 Seconds'
. Start : 22:53:17 - SUN AUG 09, 2009
. End : 22:53:27 - SUN AUG 09, 2009
. Highest return code encountered = '0'.
Rerun
Multiload jobs that are interrupted before the application phase starts or after the application phase ends may be rerun. Jobs that are inter-rupted during the application phase must be restarted.
To rerun an entire Multiload job the following actions must be taken before the job is rerun:
- Use BTEQ to execute RELEASE MLOAD command for each target table in Multiload script
- Drop work table for each target table
- Drop acquisition error table for each target table
- Drop application error table for each target table
- Drop restart log table
These actions can be executed by a BTEQ SQL script that is run as part of a Multiload script or run by an external job scheduling system.
The following is an example of a SQL script that will allow this example to be rerun.
.logon test/testuser,test database test; release mload tran; drop table uv_tran; drop table et_tran; drop table wt_tran; drop table log_tran; .logoff .quit
This script can be executed as part of a Multiload script that executes this script with BTEQ and then executes the Multiload job as illustrated in the following Multiload script.
.system 'bteq < c:\DataIntegration\scripts\tranreset.sql'; .run file c:\DataIntegration\scripts\tranload.ml; .logoff;
This script can always be run when the intent is to run the entire Multi-load job.
No comments:
Post a Comment