2

In the ETL there are 2 parallel jobs and those 2 jobs run start and finish almost at the same time. After they are finished they will update few details to a same look up table. Both the parallel jobs are taking the same amount of time to finish. If both the jobs are started at the same time then the jobs are creating a mlock on the look up table and both the jobs are failing. Tried releasing mlock and running them again. But that is still going into mlock again. So added a wait time of 20 secs before the 1st parallel job. Then both the parallel jobs finished with any issue and updated the required details to the look up table.

Adding a wait time is not an ideal solution in this use case, because later on few parallel jobs needs to added (somewhere around 20-30). If wait steps were to be added before each parallel job, then the time increases drastically. Because for the 2nd job 20 sec, 3rd job 40 sec, so on.. This will be a large number by the time 20th job is reached.

So looking for an alternative solution rather than adding a wait step before each job to avoid the MLock issue?

Teja Goud Kandula
  • 1,462
  • 13
  • 26
  • Is it necessary that mload is used to update this table? A BTEQ or ODBC or JDBC insert may be a better solution (not sure how that's accomplished in informatica) as it will just go into Blocked status until the previous write is finished without any messy mload locks. – JNevill Mar 19 '21 at 15:24
  • 1
    If the update is only for "few details" then MLOAD is the wrong tool. That said, be sure each job has its own distinct log, work, error table names. If multiple jobs that potentially try to run in parallel use the same names (e.g. the default generated names), you are more likely to run into issues. – Fred Mar 19 '21 at 17:02
  • It helped @Fred. Again you have pointed in the right direction. – Teja Goud Kandula Mar 22 '21 at 19:33
  • @Fred, you can post your comment as answer so that I can accept it. – Teja Goud Kandula Mar 23 '21 at 20:41

1 Answers1

1

Be sure each job has its own distinct log, work, and error table names. If multiple jobs that potentially try to run in parallel use the same names (e.g. the default generated names), you are more likely to run into issues.

But if the update is only for a "few details" then MLOAD is the wrong tool.

Fred
  • 1,916
  • 1
  • 8
  • 16