Handling informatica failure with an MLoad job

Some of you may have experienced a failure in an MLoad job.  Due to the way the Informatica session was set up, when the job was re-run, a new error, similar to the following was given:

[ERROR] Type:(Teradata DBS Error), Error: (Worktable is missing for xxx during MLoad restart.)

This is happening because the "Drop Log/Error/Work Tables" attribute on the target in the session is checked.  This attribute tells Informatica to drop those tables before it starts the MLoad regardless if an MLoad was in progress in the prior run.  Unfortunately, by dropping the work and log tables, an MLoad that needed to be restarted cannot.  This is when the DBA gets called to drop and recreate the target table.  Well, we shouldn't need to do that anymore!!!

Here are the steps that should be followed when a failure occurs:

1)      Do a "select *" from the target table with a "sample 1" to see if a lock exist on the table.  Note: a "select count(*)" will not work for this purpose.

a.       If the query returns no error, then the job can be restarted.  Proceed to #2.

b.      If the query returns an error that a lock exists on the table, then a DBA needs to be engaged to drop that lock.  While waiting for that, you can do #2, but do not do #3 until the lock has been released.

Ex: Release mload on <tablename> ; --> if failed in aquisition phase.

release mload <tabname> in apply; -- if failed in appln phase.

2)      Ready the job - Copy the job to the support folder to make sure the "Drop Log/Error/Work Tables" attribute is unchecked.  The standard will change for development to have this box unchecked.  If the session already has this box unchecked, then there will not be a need to copy it to the support folder first.

3)      Re-run the job - Remember, Informatica has no idea that the MLoad was in progress and that an MLoad restart will commence.  Informatica is simply submitting the MLoad to Teradata.  Teradata, on the other hand, due to the data in the log table and work table, knows to do an MLoad restart.  Rerun the job.

4)      If there is another failure after this, start at #1 again.