Is MLOAD executed in a single transaction?

Question

I have an MLOAD job that inserts data from an Oracle database into a Teradata database. One of the things it does it drop the destination table and recreate it. Our production website populates a dropdown list based on what's in the destination table.

If the MLOAD script is not on a single transaction then it's possible that the dropdown list could fail to populate properly if the binding occurs during the MLOAD job. If it is transactional, however, it would be a seamless process because the changes would not show until the transaction is committed.

I checked the dbc.DBQLogTbl and dbc.DBQLQryLogsql views after running the MLOAD job and it appears there are several transactions occurring within the job, so it would seem that the entire job is not done in a single transaction. However, I wanted to verify that this is indeed the case before I make assumptions.

Answer 1

A transaction in Teradata cannot include multiple DDL statements, each DDL must be commited seperatly.

A MLoad is treated logically as a single transaction even if you see multiple transactions in DBQL, these are steps to prepare and cleanup.

When your application tries to select from the target table everything will be ok (unless it's doing a dirty read using LOCKING ROW FOR ACCESS).

Btw, there might be another error message "table doesn't exist" when the application tries to select. Why do you drop/recreate the table instead of a simple DELETE?

Another solution would be a loading a copy of the table and use view switching:

mload tab2;
replace view v as select * from tab2;
delete from tab1;

The next load will do:

mload tab1;
replace view v as select * from tab1;
delete from tab2;

And so on. Of course your load job needs to implement the switching logic.