2

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.

oscilatingcretin
  • 10,457
  • 39
  • 119
  • 206

1 Answers1

4

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.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Having thought about it, you're right, I should just delete instead. I originally did this and it works fine, but the logic behind dropping/recreating the table was to catch any changes in the source table's schema (varchar2 length changes, datatype changes, etc). However, it occurred to me that the MLOAD files would have to be modified anyway, so it's a moot point. So then you're saying that the insert into the destination table is transactional? If I add `delete mydb.mytable;` just before the `insert into mydb.mytable`, the delete and insert will occur in the same transaction? – oscilatingcretin Mar 04 '14 at 12:37
  • The **DELETE mytable;** is submitted outside of the BEGIN/END MLOAD (i.e. a Support Environment command), thus it's a seperate transaction. But it's exactly the same for a CREATE TABLE, there's a very short period after the table is created/deleted and before the MLoad locks are applied where a session could read zero rows. View switching will not show that behaviour. – dnoeth Mar 04 '14 at 14:31
  • You're right. Putting the delete within the begin/end breaks the script. What I will do instead is create a second duplicate table and use a BTEQ script to delete those records and then insert from the MLOADed table in a transaction. Then I won't have to use view switching. – oscilatingcretin Mar 04 '14 at 17:40