If you are using utility throttles in Teradata 13.10 to manage concurrency of your load jobs, any delay action against the utility job's unit of work will happen cleanly at the beginning of the load job. However, there may be times where the load activity can be unexpectedly held up after the utility has begun execution. This posting describes the particular conditions under which this may happen, and points to a simple adjustment you can make to keep it from happening again.
In Teradata 13.10 and TTU 13.10, TASM and Teradata utilities were enhanced by treating the whole load/unload process as one unit of work. A unit of work for a load or unload utility starts at the BEGIN LOADING/MLOAD/EXPORT statement on the control SQL session. It ends after the END LOADING/MLOAD/EXPORT statement. In other words, a utility unit of work spans multiple utility-related SQL statements. Only the first SQL statement of a unit on the control SQL session will satisfy the utility classification criterion and be assigned to a utility workload. Subsequent statements of the unit are automatically assigned to the same utility workload.
However, SQL statements outside the load/unload unit of work, as well as SQL statements from any auxiliary SQL sessions associated with the utility job (if they should be issued), will be assigned to a different workload. They will be classified and managed just as though they were stand-alone requests. An auxiliary session within a utility is optional and is not incorporated into the utility unit of work. For example, writing the result of a checkpoint to the restart log is done by an auxiliary session. Some utility protocols, such as FastLoad or MultiLoad, are designed to wait for these sessions when they have work to do, such as writing to a log.
These types of auxiliary statements may carry the same LSN (logon sequence number) as the load utility itself. However, since the SQL statements are not considered to be part of the TASM utility unit of work, they will not be under the control of the TASM utility throttle. By the time they execute, the unit of work for the utility may have already begun.
If the auxiliary statements or other SQL statements that happen to be defined within the utility job happen to classify to a system throttle that has been defined with other purposes in mind, then these auxiliary SQL statements could be delayed and could hold up progress in completing the utility job.
For that reason, if a system throttle exists that is not intended to include utility jobs, it is recommended that you define it in such a way as to exclude utilities, using utility application names. This will solve the problem of unexpected delays from auxiliary or other SQL sessions by ensuring that they will never be delayed. Note that this approach will work only for utilities that are executed from the network. It will not work for utilities coming from the mainframe. This is because mainframe clients are not able to take advantage of either classification or exclusion by application name.
In general, there is limited benefit in utility jobs being classified to system throttles, so in any cases where a system throttle might impact either the utility itself or any of its auxiliary sessions, use classification on the system throttle to exclude the utilities, by utility application name.
For more information on this topic, see the orange book Teradata Active System Management for Teradata 14.0 with SLES 11 Chapter 10 Utility Management, written by Hoa Tran. The information in that chapter applies equally to Teradata 13.10 with SLES 10.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.