Eliminating Unplanned Delays Within Load Utility Jobs

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

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.

19 Comments
Enthusiast
Thanks Carrie. At our client site, the same has been achieved by creating separate workload, with no throttle limit, with an 'include' criteria for utilities based on application name.

The mainframe utilities remain a problem though.
N/A
Hello Carrie, thanks for the info, can we set the program to throttle the load for certain applications run by certain users.
Teradata Employee
Alvie,

What you describe above will work, as an alternative approach to using system throttles with exclusion. I am still investigating if there are ways to manage the auxiliary session statements when the load job comes from the mainframe.

Thanks, -Carrie
Teradata Employee
Parsim,

You can create workloads that have load utility type as part of their classification, and that also classify by user and/or by application. That way the loads will run under the control of those workloads, and by adding a workload throttle, you can have a second level of concurrency control over load utilities. The load utility is the system-wide control, while individual WDs can enforce lower limits (by user, by account, for example) as a second level of control.

Make sure you put such workloads high in the evaluation order so load jobs will classify to them before any other workoads they may qualify for. No other type of work (such as regular SQL) will classify to these workloads due to the presence of the load utility type classification.
Enthusiast
Hi Carrie,
Yes, that is another but probably the only approach possible for the situation we were facing. The auxiliary session statements were getting delayed not on system throttles but due to workload throttles. As there is no option to exclude utilities based on Application Name for workload throttles, the only option for us was to create another workload without any sort of throttling on it.
Thanks.
Teradata Employee
There is another option to prevent auxiliary session SQL from mainframe utilities being controlled by utility throttles. That is to use query band exclusion on the system throttle.

You must be on 13.10.

A query band pair is inserted automatically into the load script by the 13.10 protocols on the client side. The query band "UtilityName" is a reserved name which all 13.10 client utilities already set immediately after logging on SQL sessions.

So instead of excluding by application name on the system throttle, which you can do with load jobs coming from the network, you can exclude by query band (works for both network and mainframe loads):

DIS-QUALIFY IF:
QryBand = 'UTILITYNAME = FASTEXP'
OR QryBand = 'UTILITYNAME = FASTLOAD'
OR QryBand = 'UTILITYNAME = MULTLOAD'

Thanks, -Carrie
Teradata Employee
Alvie,

On your comment left one day ago...

Yes, creating another workload without a throttle for that purpose is a reasonable thing to do in those cases. Good idea.

Thanks, -Carrie
Enthusiast
Hi Carrie,

Combining the Query Band solution with the fact that due to the workload throttle (as opposed to system throttle), we have to have another workload without a throttle on it, the definition/classification criteria of that workload should be based on Query Band rather than Application Names. Is that correct?

Thanks
Teradata Employee
Alvie,

First, query band exclusion only works starting in 13.10.

You want to disqualify by query band on any workload that has a workload throttle where the auxiliary SQL within a utility might classify, AND the utility comes from the mainframe. You use exclusion on the query band so that auxiliary SQL from a load job will NOT classify to a workload that has a throttle and potentially cause unwanted delays to the load utility.

However, the auxiliary SQL coming from the load job will have to classify to some workload. It will not classify to a workload that includes "utility type" as classification, but only to a workload where regular SQL classifies to. If the workload where the auxiliary session SQL was previously qualifying to had a throttle, and you added exclusion by query band to that workload, then you may want to add a new workload for the auxiliary SQL to classify to that does not have a throttle on it. You do NOT want to use query band exclusion on that new workload. On the other hand, you may not need to add a new workload if there is another already-existing workload that the auxiliary SQL will naturally classify into that makes sense, and that does not include a workload throttle.

The query band information that is attached to the load job will be associated to the auxiliary SQL, just as the LSN number will be associated to the auxiliary SQL. So leave off the query band exclusion criteria on the workload where you want the auxiliary SQL to run.

Thanks, -Carrie
Enthusiast
Thanks a lot Carrie! Makes perfect sense.

Regards...
Fan
I get the same error, I've found a solution through a document.
Thanks, GS
Enthusiast
Hi Carrie,

I am using teradata 12. Specifications are like this:
Utility throttle for number of combined FastLoad and MultiLoad utilities to a maximum of 2 and delays utilities that exceed the throttle limit.
Fastload parameters: Tenacity:2 Sleep 6.
A fastload is in delay queue for 2 hours what will happen to that fastload?

Thanks in advance.
Teradata Employee

For tenacity to be able to get involved, the load job has to have been rejected by the database and an error message returned to the client indicating that. 

Since you are not rejecting load jobs but rather delaying them, as far as the tenacity code goes, it thinks the job is running.  It doesn't see the delay because it's happening at the database level.

The FastLoad job will stay in the delay queue until it is allowed to run.  It doesn't matter how tenacity is set.

 Thanks, -Carrie

Hello Carrie,

1. I am a little confused with the picture showing that the 'write to restart log' sqls getting classified into the wd-short-qry and what was explained above "Subsequent statements of the unit are automatically assigned to the same utility workload". Isnt the 'write to restart log' sqls within the UOW and arent they supposed to be assigned to the same utility workload?

2. Just trying to clarify if my understanding is right about the auxiliary sessions outside of the UOW. Since the Aux Session carries the same LSN, thereby the system throttle knows its a SQL belonging to an utility. So it will apply the throttle if we dont exclude the utilities. hence we hae to exlcude to make sure not to qualify for that throttle. 

Thanks!

Jana

Teradata Employee

Jana,

An auxiliary session is not required as part of a load utility job, although most Teradata utilities do support that option.  But if you use an auxiliary session it will not be part of the utility unit of work because it is not included in the UOW protocols.  The database cannot control what might end up being executed within an auxiliary session.   So the decision was made by engineering to only allow the actual necessary non-optional load pieces to be part of that UOW.  That is why they appear to the database to be just another SQL request and will not classify to the same workload as the one running the utility.

For that reason your statement below is correct.  The auxiliary session SQL DOES carry the same LSN and does have the same application name as the load utility.  So by using system throttle classification (where that SQL might classify) to exclude by application name will effectively prevent auxiliary (that's hard for me to spell!) session work from being delayed there.

Thanks, -Carrie

Teradata Employee

Hi Carrie,

I am going through the charpter 10 of the Orange book that you mentioned. However there are 2 points that I am not quite sure:

1) The auxiliary session will not be classified/considered in the "Utility Throttle" ?

2) Both the auxiliary session (SQL) and the control session will fall into the same workload if the workload classification criterion specifies the utility name (fastload, multiload, ...) ?

Thx,

Cam

Teradata Employee

Hi Cam,

Response to #1:  A utility throttle will not impact or have any control over an auxiliary session SQL.  The auxiliary session, if there is one, is not included in the utility unit of work.  Utility throttles counters only get checked on  CHECK WORKLOAD END statements.  When TDWM sees a CHECK WORKLOAD END statement it looks at any and all utility throttles, to see if one applies and if it does apply, it checks to see if its counter is below the throttle limit.   The CHECK WORKLOAD END is the only point at which a utility throttle can cause a delay. Once the CHECK WORKLOAD END statement runs or is released from the delay queue and completes, the utility throttle plays no role.    

Response to #2:  The answer is no, the auxiliary session (SQL) and the control session will NOT fall into the same workload if workload classification includes  "utility" type.  Once you select "utility" for workload classification, you can then name the uilities you want the workload to support.  If you apply utility classification, which is recommended, anything that is not part of a utility unit of work will not be allowed to run there.  The auxiliary session SQL, since it looks like just another SQL statement, will not run there.

Thanks, -Carrie

Teradata Employee

Thanks Carrie for the detailed explanation.

In other words, we need to have a workload that will not throttle to include the auxiliary session by using other classification criteria liked user, application name, ... etc. rather than utility type.

Teradata Employee

Yes,  make sure whichever workload the auxiliary SQL goes to does NOT have a workload throttle.   And in order for the SQL to classify there the workload must NOT have utility classification. 

You also want to make sure that if there are any system throttles that could delay auxiliary SQL, that you exclude applications = FastLoad, MultiLoad, etc. from being controlled by that system throttle.  However, if your load utilities are coming from the mainframe you won't get an AppID, so in that case you can add classification criteria to a system throttle to exclude by queryband of UTILITYNAME=(FASTLOAD,MULTILOAD).  All the auxiliary SQL that is part of a MultiLoad or FastLoad or FastExport jobs will use the same queryband as all the other utility statements carry (you can see this in DBQLogTbl).

Thanks, -Carrie