datastage job having high start up time

Lavanya B · Post by **Lavanya B** » Mon Apr 27, 2015 4:20 am

Hi,
There is a datastage job with Teradata Enterprise stage and Teradata API stages as the source. Data is being loaded to Oracle. All the stages in this job are running in sequential mode.
The start up time of this job is low most of the times. Sometimes the start up time is very high(approx 15 min, once it was approx 45min

).
What could be the reason for such high start up time. Why is the job having inconsistent start up times? Is there any environment variable/setting in the DB stage to control the start up time?
Could anyone please help on this?

qt_ky · Post by **qt_ky** » Mon Apr 27, 2015 6:23 am

Check in the stage properties for any before-SQL statements. The last time I checked, those SQL executions were counted as startup time.

Lavanya B · Post by **Lavanya B** » Tue Apr 28, 2015 12:20 am

There are no before SQL statements that are executed in the source Teradata stages.

Mike · Post by **Mike** » Tue Apr 28, 2015 6:43 am

It's not just before SQL that appears to get captured as Startup time.

From what I've observed, it appears to be a catch-all bucket for any time not specifically measured elsewhere.

Overhead would probably be a better name for it.

Before SQL, after SQL, time spent computing database statistics are all things I've seen captured as "Startup" time.

Mike

Mike · Post by **Mike** » Tue Apr 28, 2015 6:55 am

And, of course, it includes the "real" startup time of waiting for computing resources to become available.

Since you're using Teradata, any chance that you may be waiting for a utility slot to free up?

Mike

Mike · Post by **Mike** » Tue Apr 28, 2015 7:08 am

One last thought...

Since the long startup time is not consistent from run to run, it is probably not directly related to something that your job is doing; but rather, to something that is happening in your environment.

Mike

weiyi_will · Post by **weiyi_will** » Fri Jun 26, 2015 1:26 am

I ever also meet the case that it include execution time of after SQL as start up time.

PaulVL · Post by **PaulVL** » Fri Jun 26, 2015 8:57 am

My money is on the waiting for Teradata sessions.

Try requesting less sessions and see if your time reduces.

If you have a small result set, request less sessions.

Since all of your stages are in sequential mode (for a parallel job), I am guessing you have a very small amount of data.

ozgurgul · Post by **ozgurgul** » Fri Jun 26, 2015 9:06 am

Hi there - have a look at this APAR..

JR49593: INFORMATION SERVER 9.1 PARALLEL JOBS HAVE LONG STARTUP TIMES DURING SCHEMA SETUP

Hope it helps..