DSXchange

Hi,

We are facing a problem with Teradata enterprise stage, which uses Mload and Fast Export resources of Teradata.

If all the resources are busy processing other jobs, then Data Stage Jobs fails after giving the following error:-

"Too many Load/Unload Jobs running."

Is any option available, so that jobs doesn't fails, it continue waiting in the Teradata queue till the MLoad or Fast Export resources are free to execute the job?

We have defined the following Additional Connection Options in Teradata enterprise stage:-

1. requestedsessions
2. sessionperplayer
3. synctimeout

Note:- We checked queue option at the Teradata end, it is working fine from Teradata end. If we execute lot of Bteq scripts using Fast Export or MLoad then bteq scripts waits till the resources are not available to them and automatically starts those scripts once resource are available.

Thanks in advance.

Anuj

Welcome Aboard
Teradata has a limit on how many load utility operation can be run concurrently. You are exceding the limit. Its exceding the timeout session as well as the tenacity(number of retries). Either increase the tenacity number or control the number of concurrent operations. Its not a datastage problem but a teradata limit. I suggest having a chat with your dba and coming up with a scheduling period that can accomodate your load operation.

Hi,

Thanks a lot for the suggestion.

We understood this while analysis and we found this problem arises if we put too much of load on the Teradata Server. Most of the times if too many Jobs are submitted manually not using the Scheduler.

yeah we can increase the limit in Teradata as well but DBA is not ready for that as he is having his own reasons for not increasing the same.

So, i wanted to have a solution at Data Stage end so that Job waits till the resource are not available.

So do you think if we remove synctimeout parameter from Additional connection option job would wait endlessly till the resources are available.

Cheers

This is a simple economics problem; supply and demand.

If your DBA won't increase the supply, you must reduce the demand.

Do fewer things simultaneously. And/or use smaller configurations (fewer nodes for communicating with Teradata).

anujk_dev wrote:So do you think if we remove synctimeout parameter from Additional connection option job would wait endlessly till the resources are available.

I dont think removing synctimeout will make it wait endlessly. It might fall to a default timeout. Plus letting a job wait endlessly is not advisable and certainly not a stable design.

u need to specify TENACY and SLEEP as well so that the load wont fail if the limit exceeds it will wait untill TENACY and SLEEP setting ..

TENACITY instructs Teradata to queue for retry if a utility slot is not available. Without out, the process simply fails if there is no utility slot available. By default, there are 15 utility slots.

Who is "u"? There's no-one of that name registered on DSXchange.