September 14


Optimal Use Of Teradata Utility Workload

By Roland Wenzlofsky

September 14, 2015

TASM, TIWM, workload, workload classification, workload management, workload throttles

Teradata Utility Workload – Overview

In our previous articles about Teradata workload management, we covered the basics about how SQL requests are classified into workloads. We talked about throttles, dynamic workload management and the priority scheduler.

This article is covering utility workload. Utility jobs are user requests which are not implementing the SQL protocol. Currently, this is the following ones:

  • Loads (Fastload, Multiload)
  • Unloads (FastExport)
  • Backup & Restore (ARCMAIN, BAR)

Above mentioned tasks are implementing their protocols. The difference between SQL workload and utility jobs is that utility workload is highly specialized and optimized. Therefore, the more advanced workload classification criteria's, such as “query characteristics” (product joins, ALL-AMP, etc.), are not available (and not needed). Still, the workload management (TASM, TIWM) offers additional features unique to this kind of workload.

Teradata implements below protocols to cover utility workload:

  • The FastLoad Protocol
  • The MultiLoad Protocol
  • The FastExport Protocol
  • The Backup & Restore Protocol

For each utility protocol, several implementations exist (for example, the FastLoad protocol is implemented by the Fastload Utility, the TPT Load operator, and the JDBC Fastload).

Non-Teradata utilities (3rd party) are allowed to apply these protocols (via TPT), but individual workload management features may be missing.

During execution, all utilities are logging on at least one SQL session and several utility sessions (with the exception of BAR, which is not making use of any utility session at all). The mandatory SQL session, called the control session, is used to execute all SQL statements belonging to the utility.

Load and unload utilities may use a second SQL session (the auxiliary session) for the maintenance of a restart log.

Utility resource usage can be managed by:

  • Concurrency Levels: Defines the maximum number of utilities which can be executed at one point in time.
    If the limit is exceeded, arriving utility jobs will be delayed or rejected
  • Utility Session Limit: Defines the minimum and a maximum number of sessions being used by each utility job.

Teradata Utility Workload – Concurrency Management

Concurrency limits are implemented by the usage of throttles. They can be defined on system level and workload level.

Additionally, Teradata workload management implements hard limits which never can be exceeded by the user-defined utility or workload throttles:

Limit for Fastloads + Multiloads + FastExports: 60
Limit for Fastloads + Multiloads: 30
Limit for Fastloads: 30
Limit for Multiloads: 30
Limit for Fastexports: 60
Limit for Backup & Restore: 350

Although the overall hard limit for load & unload utilities together is 60, there exist even more strict limits; For example, it’s not possible to execute more than 30 Fastloads at one point in time, even if the total limit of 60 loads & unload utilities is not reached.

The hard limits always lead to a delay, if there is no user-defined utility or workload throttle with the reject option defined.

The hard limits for concurrency are accompanied by a limit for AMP worker tasks. Only 60% of the available AWT’s can be used by utilities at one point in time.

Even if concurrency limits are not reached, the AWT limits may cause that a utility job is delayed or rejected (important: AWT limits are only checked for FastLoad, Multiload, and FastExport without Spool).

User-defined utility throttles are used to manage resources on a system level. You can define limits such as “Allow 15 Fastloads at one point in time”.  These throttles always have to be set in the range of the hard limits we mentioned above.

If you need to arrange your utility workload on a more detailed level, utility workloads which are using their throttles can be defined.

This is achieved by adding at least “utility” as classification criteria to your workload. Additional criteria’s can be used to further detail the workload, such as “request source,” “target” and “query band.”

Like mentioned earlier, SQL protocol specific criteria’s, such as “query characteristics,” are not available for classification.

Teradata Utility Workload – Session Management

Limiting the number of utility sessions is another way of managing system resources.

In the same way, as default concurrency limits are defined for utilities, the Teradata workload management is taking care of sessions automatically, if user-defined session limits do not overwrite the limits.

The workload management is distinguishing between small, medium and large amounts of data. By default, medium sized data is expected.

If you are loading small or large amounts of data, the default limits can be adjusted by using the query band below:


These are the default rules for utility session limits:

Fastload and Multiload:

Medium Sized Data:

IF  (“Number of AMPs” <= 20) THEN “Number of AMPs.”
ELSE MIN(20 + “Number of AMPs” / 20), 100)
Small Sized Data:

“Medium Sized Data” * 0.5
Large Sized Data:

MIN(“Medium Sized Data” * 1.5, “Number of AMPs”)


Medium Sized Data:

IF (“Number of AMPs” <= 4 THEN “Number of AMPs” ELSE 4
Small Sized Data:

“Medium Sized Data” * 0.5
Large Sized Data:

MIN(“Medium Sized Data” * 1.5, 2 * “Number of AMPs”)

If the default session limits are not sufficient, user workload can be defined.
Classification criteria’s for user-defined workload are:

  • Utility Name
  • Request Source
  • Query Band
  • Data Size (via the “UtilityDataSize” query band)

You should always at first design your utility rules on the system level to ensure a healthy behavior of your utility jobs. Specific workloads should only be considered to cover unique situations and requirements.

Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!