I just got back from speaking at the San Francisco Teradata User Group meeting and my topic included techniques for controlling concurrency of load utilities. When I polled the audience, I was surprised and disappointed that so few in the audience were using utility throttles.

I’m a huge fan of utility throttles in Teradata, and I’d like to tell you why.

You can control MultiLoad jobs, FastLoad, and FastExport jobs differently with utility throttles. If you’re relying on the MaxLoadTask setting (that “other” option), all load utilities, including FastExport jobs, are managed collectively with a single limit for all.

When using utility throttles, limits can be automatically altered for you by Period or State. So, daytime limits can be set differently from nighttime limits. MaxLoadTask, a DBS Control setting, does not lend itself to frequent changes, and you’d be hard-pressed to make such changes happen automatically.

Utility throttles in Teradata 12 offer the ability to cleanly delay a job if it would cause the throttle limit to be exceeded. The delay activity is done before any of the AMP sessions log on, using a first-in-first-out delay queue. If you’re relying on MaxLoadTask, rejection is your only choice once the limit is reached. Yes, I know there’s a get-around for that, using SLEEP and TENACITY to automatically resubmit a rejected job, and you probably have those parameters coded into all your load scripts. But think about how your users will like the first-in-first-out fairness associated with utility throttle delays, when they contrast that to the luck of the draw approach they get with post-rejection re-submission today.

Utility throttles are not new. They have been available since Teradata V2R6.1, and can be used with or without TASM.

I’d like to nudge you along even more by confiding that future enhancements to managing load utility concurrency will be focused on utility throttles, not that “other” option. Utility throttles are the way of the future. In fact in Teradata 12, if you have the throttles category (category 2) active, as you would have to if you are using system throttles of any kind, then your MaxLoadTask setting will not be honored. So the only way you will be able to establish control with more than 15 (or less than 15) load jobs running in those circumstances is by making the leap to utility throttles.

But you won’t regret it. They are easy to use and much more flexible. Plus you’ll have another delay queue to play with.

So what’s stopping you now?
 

Discussion
richhalp 1 comment Joined 02/10
16 Feb 2010

Hi Carrie. this is Rich from Medco. Thanks for this blog entry. Do you have any generic recommendations on what utility limits (above or below 15) make sense with TD12?

monisiqbal 17 comments Joined 07/09
29 Apr 2010

You mentioned that utility throttles can be used with or without TASM. Can you please provide a pointer to using these throttles without TASM?

carrie 595 comments Joined 04/08
13 Dec 2010

Rich,

Sorry for the delayed response. I am just now seeing this comment of your.

I don't think I can make a general recommendations about utility concurrency, because what is right for you is based on the criticalness of the load work, how much resource is takes when it run, what else is running on the platform at the time and its priority.

I can say that it is not uncommon to see more than 15 concurrent load utilities (particularly during the off hours), but like everything, "it depends".

Thanks, -Carrie

carrie 595 comments Joined 04/08
13 Dec 2010

Monisiqbal,

You can use TDWM, with the throttles category enabled, and then define utility throttles. There is some information on this in the TDWM manual. The screens in TDWM are pretty easy to use and self-explanatory.

Thanks, -Carrie

vasudev 35 comments Joined 12/12
20 Mar 2013

Hi Carrie,
I am using TD12 and queries are taking longer to run than normal. Which teradata tool can be used to identify the no of active throttles? 
From some blog i came to know using TDWM we can identify number of active throttles. Is there any other tools available to calculate the number of active throttles? Please advise. Thanks in advance.
trustngs

carrie 595 comments Joined 04/08
25 Mar 2013

For utility throttles you can go to the Viewpoint  Query Monitor portlet.  Under Select Report, choose By Utility and you get a list of active utility throttles and their limits.
 
For workload throttles you can go to Viewpoint Workload Monitor portlet and drill down on the workloads and see which ones currently have throttles active.  You can see the limits and counts.
 
For system throttles, there are a couple of things you can try.  In Viewpoint from the main Workload Monitor screen you will see two icons 1: Active 2: Delayed.  I believe if you are delayed on a system throttle you will see the system throttle name in one of the reports, but you'd have to try that out for yourself.
 
You can also use one of the TDWM Open APIs, that are described in the API manual.   One of the choices there is TDWMThrottleStatistics.
 
Thanks, -Carrie

MarkVYoung 20 comments Joined 03/12
29 Jul 2013

Hi Carrie,
We are running Teradata Viewpoint V14.00.00.07-b55 and we are running with TD 13.10 and TTU 13.10, though we do still have some users using mainframe load utilities at version 13.00.
Due to one of the Tech Alerts that mentioned possible restarts with the use of Utility Throttles and running TTU 13.00 Load Utilities, we have not used these types of throttles.
I am wondering how the query throttle would impact when applied to the workload used for TPT batch loads. Since we have a default setting of 36 sessions for Fast / Multi Load jobs and 12 sessions for FastExport jobs, can we still run 5 concurrent TPT update or load jobs? This would result in up to 180 sessions being run, so does the query throttle see each session as an active query and then cause blocking and delays that potentially stop the jobs in their tracks or would it know there only 5 load jobs running?

30 Jul 2013

Hello.
Do you know if its possible have a utility throttle wich only applies to some users? 
What I´d like is for load users to be limited on the number of utilities they can use.
But there is a special user called dwh_sas, that should not be afected by the utility throttle. 
In other words if the utility limit is 1 and a load user is using a fastload a second fastload should be delayed, but if the user dwh_sas needs to start a fastload he should be able to do it.
Thanks

carrie 595 comments Joined 04/08
31 Jul 2013

Hi Mark,
 
If your client software and DBS software is both on 13.10, then you should be able to use load utility throttles just fine.  You do not want to try to use system throttles as a replacement, because when system throttles manage load utilities you can experience some odd side-effects.     
 
You can, however, use workload throttles that include classification by utility as a second level of concurrency control for load utilities. See my blog posting for information on how workload throttles and utility throttles can be used together (but only on 13.10).
 
Two Levels of Concurrency Control for Load Utilities in Teradata 13.10
 
I don't want to advise you on whether or not you are at risk from the restart described in the Tech Alert.  It would be better to call the support center and seek their advice if you have a concern in that area.
 
The session management rules are not related to load utility throttles.  They are separate, unrelated features.  The only consideration that might bring them together for you is if you are at risk for running out of sessions when you allow too high a concurrency on load utilities AND you specify a high number of sessions for each.   That is not likely to happen if you use the default settings for session management rules, but could if you change those default settings to very high numbers of sessions per load job.
 
Each session is not seen as a query (a unit of work).  The load job is seen as a single unit of work.  The utility throttle is managing the concurrency of these single load job units of work, and is blind to the number of sessions involved.   A load utility job will not begin execution unless all sessions it requires are available to it.  A load job will be placed in the delay queue without having logged on any sessions (except the control session) if it cannot get all database sessions it needs to proceed. 
 
Thanks, -Carrie
 

carrie 595 comments Joined 04/08
01 Aug 2013

Mauricio,
 
The solution is to use two levels of throttles:  Utility throttles for all users, then workload throttles on workloads that only specific users classify to. 
 
This approach is explained in another of my blogs titled: 
 
Two Levels of Concurrency Control for Load Utilities in Teradata 13.10
 
However, you must be on 13.10 or above for this to work.   For this to work you must add classification to a workload specifying which users you want to manage there and also "utility type", then only those users and only utility jobs of the type you specify will classify to that workload. 
Thanks, -Carrie

Arif_M 1 comment Joined 03/14
21 Mar 2014

Hi Carrie,
We are using utility throttles at workload level. We have recently observed that TPTEXP jobs in responding state are holding on to the utility slots in TASM and causing other jobs to wait in the delay queue. Is this an expected behaviour?
Does using NOSPOOL option for export jobs have an effect on TASM utiltiy limits like it has on AWT consumption?

Thanks, Arif

carrie 595 comments Joined 04/08
24 Mar 2014

Arif,
 
FastExport holds onto the utility slot until the end of the job, just as is the case with load utilities.  So what you are experiencing is expected behavior.  This is true whether you are using the standard FastExport or the NOSPOOL option.
 
Thanks, -Carrie

You must sign in to leave a comment.