Have you ever wanted two levels of control on load utilities? More specifically, have you ever wanted to limit how many load utilities a subset of users are able to run? This was not possible before, but it is something that is supported in the Teradata 13.10 release. Let me explain how this works.
First, multiple levels of throttles, whether to manage queries or to manage load utilities, always rely on the presence of TASM. If you don’t have TASM, you can’t set up workloads. If you don’t have workloads defined, you can’t create workload throttles. And you need workload throttles to make up your second level of control, the first level being system throttles, or for the sake of this discussion, utility throttles.
The graphic below illustrates two levels of concurrency control that exists for queries.
So let’s assume you are using TASM, and you have specific workloads that your load utilities run in. First consider the behavior prior to Teradata 13.10, then after.
Before 13.10, your only level of control over utility concurrency was at the system level with utility throttles (or at the system level with the DBS Control MaxLoadTask parameter). Once a utility is approved to run by the utility throttle it will classify to a workload. A common (and recommended) method of classifying a load utility to a workload is by setting up classification on the utility type.
Prior to 13.10 you could, for example, run all FastLoads in one workload, and all MultiLoads in another, if you wanted. Usually classification by utility type was accompanied by an additional “who” criteria, such as user name. So you could run all FastLoads submitted by ETLUser in a higher priority workload than FastLoads submitted by AppUser. However, before 13.10, FastLoad jobs from both ETLUser and AppUser shared the same system-level utility throttle. Since they shared the same throttle, AppUser could take an unfair number of those load slots at any point in time, and there was no mechanism to prevent that.
Prior to 13.10 it was recommended that you not use the workload throttle limits for workloads that support load utilities, because at that time workload throttles didn’t recognize utilities, or consider them as a unit of work. This lack of compatibility between workload throttles and utilities led to some odd behavior when the two were combined. For that reason, it was recommended to never put a workload throttle on a workload that was supporting load utilities.
There have been several enhancements for utility throttles In 13.10. First, classification criteria for utility throttles have been expanded to include “where” criteria such as table name or database name for the load utilities (FastLoad, MultiLoad, FastExport jobs). In addition, more specific utility types of each load/unload protocol are supported. In 13.10, utility type can distinguish between stand alone FastLoad utility, TPT Load operator, and JDBC FastLoad. This gives you the flexibility of having one workload for stand alone FastLoad, and a different workload for the TPT load operator, for example.
But the big change, the one to get really excited about, is the ability to limit the number of utilities at the workload level. Workload throttles have been enhanced to both recognize load utilities and delay them cleanly. This means you can now classify different users who submit load jobs to different workloads, and each workload could have a different throttle limit. At the same time you could use the standard utility throttle to manage concurrency levels at a higher level, for all the users submitting loads.
Enhancements have been made to the workload throttles, so that they can recognize and cleanly delay load jobs, just as they have always been able to cleanly delay queries. You might have some DDL in a load script, and those statements will be classified and processed as queries, not load utilities. The load utility classification will include everything between the BEGIN and the END LOADING statements. Even if a DDL is delayed, it will not be holding onto a utility slot, in 13.10.
So, you can now better manage those groups of users who like to load their own data. You can classify them to their own workload and then limit them to whatever level of load job concurrency you wish. When two levels of utility throttles are active, both levels must be satisfied for the load job to start running.
Just a reminder that as of Teradata 12.0 the DBS Control settings such as MaxLoadTask and MaxLoadAWT will no longer be honored if any system-level throttles for either queries or utilities are active.
Whether you are using TASM or not, you should no longer continue to rely on DBS Control settings to manage load concurrencies. Instead, I heartily invite you to get on board with utility throttles.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.