Utility Session Management - It's Inside the Database in Teradata 13.10! (UPDATED)

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

How do you pick the number of sessions to assign to your utility jobs?  Chances are you guess.  In Teradata 13.10 the task of deciding the number of sessions has been moved inside the database, meaning one less thing for you to worry about.  Read on for a quick intoduction to how this feature works.

Once you get on Teradata 13.10, the database is dictating the number of sessions each load job will get.  This is happening automatically, without your doing a thing.  Any parameters or config files you have set up will be ignored, and by default you will get a number of sessions between 20 and 100 for each utility you run. 

Session management applies to both EDW and Appliance platforms, and the only requirement is that one of the three workload management features in Viewpoint Workload Designer be enabled:  Filters, Throttles, or Workloads.  You can view the enabled features for your platform by going to the Activation tab within the General category.

Default Session Management Rules

There are default session management rules in place that will be in control when you first get on Teradata 13.10.  You can change the number of sessions that these default rules provide if you like.  There are 3 types of default rules:

  1. FastLoad and Muiltiload (including TPT and JDBC variants)
  2. FastExport
  3. Archive and Restore

The number of sessions specified by the session management default rules will be different based on the number of AMPs in your configuration, as illustrated by the following formulas.  First is the formula that the default session management rules use for the load type utilities:

 

FastExport always uses 1 session per AMP, up to an upper limit of 4 sessions per job.

Here is the formula that the default session management rules use to determine the number sessions for archive and restore jobs:

 

User-Defined Session Management Rules

In order to provide more granular control over the number of sessions used by utilities, you may create your own rules.   Which session management rules will be used by a given utility job is determined  by means of a classification process, similar to workload classification.   Classification to the default rules is based on utility name and optionally utility data size.  If you create your own customized session management rules you will have the opportunity to select TASM "who" criteria (user, account, query band, etc.) that will limit which utilities will use that rule.

For example, you may want to make sure that user loads from a sandbox user classify to a rule with a very low number of sessions.  Or you might want to ensure that your loads to your large, dynamic, sales tables have access to a higher number of sessions when they run.

Once you set up additional rules, you will need to specify an evaluation order.  The first session management rule that a utility classifies to will be the rule that determines the number of sessions that it will use.   At the bottom of the evaluation order is the default rule for that utility type, which is where all unmatched utilities will classify.

Is the Session Setting a Max or a Limit?

If you modify the number of sessions in one of the default session rules, or create a new customized rule, you will see that Viewpoint Utility Session screens refer to this number of sessions as a "Session Limit" or  a "Max Number of Sessions".

However, this number of sessions you specify is not an upper limit for the supported utilities (FastLoad, TPTLoad, etc.).  Rather it’s the exact number that must be available for the utility to begin execution, assuming you are on both Teradata 13.10 and TTU13.10 or later releases.  The session number specified in the TASM session rule will override both the minimum and maximum session specifications that have been placed in the script.

However, for non-conforming utilities that come from 3rd party tools or for utilities executing on pre-TTU13.10 software, the TASM session rules set a limit.  Only the maximum sesssion specification within the script will be overridden for those jobs, so it is possible that fewer sessions than specified in the TASM rule may be applied.

Viewpoint Utility Session terminology accounts for all of the situations described above, when it uses the term "limit".  However, if you are using the standard Teradata utilities from TTU13.10, this should be interpreted as "Number of sessions".

Utility Data Size 

If you know the size of the load job that is typically submitted by a specific user, then you can create a customized session management rule that uses the load type and the user name (or other criteria) to ensure those user’s jobs get the number of sessions that are appropriate.   Knowing the user and creating a rule for him is one way to manage the relationship between data size and number of sessions, and also keep the decision on the number of sessions inside the database.

Another approach is by using a new reserved query band called UtilityDataSize.  You have 2 values you can pass with that query band:  LARGE and SMALL.  If you do not pass a query band, the default number of sessions (equivalent to a MEDIUM size) is used.  Use of query bands puts control in the hands of the client, rather than the database, and assume that is 3rd party tool is being used, that the tool has the intelligence to support query bands.  

Below is some abbreviated FastLoad code that shows you where you can place the query band. 

sleep 1;
tenacity 1;

.SET SESSION CHARSET "ASCII";
.RUN NODEID.user1;
set record VARTEXT "|" DISPLAY_ERRORS NOSTOP;
errlimit 3;

SET QUERY_BAND = 'UtilityDataSize=SMALL;' UPDATE for session;

DELETE FROM fl_table_1;

define
     start_datetime (varchar(200),NULLIF='')
     ...
     ,file=data1;

begin loading    fl_table_1
      errorfiles et_fl_table_1,
                 uv_fl_table_1;

insert into fl_table_1 (
      start_datetime
     ...
     ) values (
     :start_datetime
     ...
     );

end loading;
.logoff;

The session management rule that the utility classifies to has a specific number of sessions.  There are three versions of each default rule, one for SMALL, one for medium data sizes called "Default", and one for LARGE data sizes.  The default version represents the standard number of sessions for that utility and will be used if no query band is passed.  If the query band says “LARGE” it will match to the LARGE version of the rule, whose number of sessions will have been increased by 50%.  If the query band says “SMALL” the rule it matches to will have a number of sessions that will be half of those in the default.  An example of those different size-specific rules whose default specifies 32 sessions follows:

DEFAULT:     = 32 sessions

LARGE:         32 * 1.50 = 48 sessions

SMALL:         32 * 0.5 = 16 sessions

Number of Sessions with Teradata Parallel Transporter (TPT)

When you’re using TPT you can initiate multiple instances of a load operator within a single load job.  All the instances will share a common logical sequence number (LSN).  One utility slot is required for each LSN.  In the database, the number of sessions specified by a session management rule will be applied to the LSN.  So if there are multiple load operators within a load job, they will share the number of sessions given to the load utility.

For example, if the session management rule for a TPT job specifies 10 sessions, and the TPT Load job has 2 load operators, then each operator will get 5 sessions.

How To Benefit from the Session Management Feature

You can do nothing, and once you are on Teradata 13.10, the default session management rules will be deciding how many sessions each of your utility jobs will use.

You can simply change the number of sessions that are specified in the default session management rules.    For your site, you may find 32 sessions for a configuration with 240 AMPs (for example) is too high, and you want most users to have a much lower number of sessions.

You can start using query bands to influence the number of sessions based on passing the value of LARGE or SMALL, if appropriate.  In that case, the number of sessions in the default rules or in the rules you have created will be selected based on the size value specified within the query band, as shown in the example above.

Or  you can define additional customized session management rules, based on your knowledge of who is submitting load utilities, and their needs.  Creating your own rules will give you more granular control over session assignments.  Don’t forget the evaluation order.

91 Comments
hi carrie,

may i know the how many amps are used for UPI, PI, SI
Teradata Employee

If the question you are asking is how many AMPs are used to access data using those indexes, the answer is: UPI and PI use one AMP, USI usually uses 2 AMPS, and NUSI uses all AMP.

Please see the Teradata Database Design manual, Chapter 10 and Chapter 11, for detailed information on using indexes in the Teradata database.

There is also a class offered by Teradata Education Network that discusses indexes:

http://developer.teradata.com/database/training/ teradata-indexes-how-the...

And there is an article by Alison on this topic at:

http://developer.teradata.com/database/articles/ indexes-too-much-of-a-go...

Also, if you do a search here in Developer Exchange for "indexes" you will get over 250 matches, so there should be lots of basics information available there as well.

Thanks, -Carrie
Teradata Employee
I think that Carrie's links above might be broken... here are working versions:

TEN indices class: http://developer.teradata.com/node/7813
Alison's article: http://developer.teradata.com/node/115
Teradata Employee
Thanks, Neil!
Enthusiast
very nice carrie
Teradata Employee
Carrie, do you know if there has been any thought put into making this TASM session setting overrideable at the job level. What I mean is if my job required X number of sessions, it would be nice to specifiy that using a parameter and have TASM allow the ad-hoc request if there are available resources. I ran into a situation with a third party ETL tool (DataStage) where the tool required me to provide it with a certain number of sessions. Given the default TASM settings I couldn't and so we had to work with the DBA's managing TASM on a case by case basis to set up custom utility data size rules. This back and forth effort with the DBA's can become a management hassle. If an ETL developer knows how many sessions his job requires then he/she should be able to specify it and if you don't know then let TASM decide.
Teradata Employee
Your point is a good one. Developement is considering refinements to this feature, and I will pass your comments along as input to that process. You make a good case for greater flexibility.

Thanks, -Carrie
Enthusiast
Hi carrie my question is:

question

I am working on TD13 trial version….

CREATE SET TABLE tduser.jn1_emp ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

emp_no INTEGER,

emp_loc varchar(12))

Unique PRIMARY INDEX ( emp_no );

Insert into tduser.jn1_emp(1,’hyd’);

Insert into tduser.jn1_emp(2,’bang’);

Insert into tduser.jn1_emp(3,’visak’);

Collect stats on tduser.jn1_emp index(emp_no);

CREATE SET TABLE tduser.jn2_emp ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

pme_no INTEGER,

emp_name varchar(12))

Unique PRIMARY INDEX ( pme_no );

Insert into tduser.jn2_emp(1,’raj’);

Insert into tduser.jn2_emp(2,’ravi’);

Insert into tduser.jn2_emp(4,’kishore’);

Collect stats on tduser.jn2_emp index(pme_no);

If I am trying to execute the following it is giving “low confidence” in the explain plan.can anybody suggest how to make it to “high confidence”

Explain sel * from tduser.jn1_emp, tduser.jn2_emp

Where emp_no = pme_no
Teradata Employee
Please see my article posted here in Developer Exchange that discusses confidence levels. There is an entire section on confidence levels in join steps.

Basically, most join steps will never be able to get a "high" confidence level no matter how many statistics you collect.

http://developer.teradata.com/database/articles/can-we-speak-confidentially-exposing-explain-confidence-levels
Enthusiast
Thank you carrie
Hi Carrie,

Thank you very much for giving valueable knowledge to us. This is my First post on this side.

My question is about Fastload

Fastload allow us to insert data on empty table. for EX.. I have a single input file, which contain 10k records and while inserting records using Fastload. On the occurrence of some error FastLoad terminated. will that possible to restart the FastLoad on populated table. if yes ten what would be process or step, we need to follow.
Teradata Employee
Teradata load utlilities have robust error handling and restart capabilities. In most cases a load utility job can be restarted after a database failure and reset, a load process error or failure, or a load utility client platform failure after the underlying problem has been corrected. Even if the table has been partly loaded before the error occurred. There are different failure cases that can require different approaches, for example removing bad records from the input. Please refer to Teradata manuals or training materials for more detail on the process itself.

Thanks, - Carrie
Teradata Employee
Carrie,

In indicate: "Once you get on Teradata 13.10, if TASM is enabled, then the database is dictating the number of sessions each load job will get." What if you are on an Appliance where there is no TASM?

Curious;-)
Teradata Employee
Hi JK,

Utility session management is available through Viewpoint for both full TASM and appliance sites. Appliance sites don’t get the “workload” part of TASM or the events or the state matrix, but they do get the session, throttle and filter parts.

For example, if you wanted to change the default number of sessions you could do that. In the Workload Designer chapter of the Viewpoint User Guide for 13.10, it tells you to click Sessions on the ruleset bar, then utility sessions. You should be able to edit from there.

Thanks, -Carrie

Enthusiast
carie,

what's the limit of available utility slots in 13.10. I see, TASM allows upto 30 and DBSCONTROL allows 15. we are also confused about utility limits and utility session limits in viewpoint. we have DBSCONTROl internal parameter (DisableTDWMSessionRules = TRUE) off, saying that TASM shouldn't control session rules. does it mean utility slots are now controlled by DBSCONTROL in our scenario, not TASM and thus limited to 15 slots?
Teradata Employee
There are two different features that need to be understood separately. 1.) Utility concurrency control; and 2.) Session Management.

Utility control:

Since Teradata 12.0, utility concurrency is able to be controlled within TASM or as part of the category 2 throttle capability of TDWM, by defining utility throttles. When you define utility throttles you can have from 0 to 30 utility slots available for FastLoad and MultiLoad jobs combined. If you are using utility throttles, any settings in DBS Control related to load utility concurrency will be ignored (such as MaxLoadTasks).

On the other hand, if you are relying on the DBS Control settings, and NOT using TASM/TDWM utility throttles, then your max number of load jobs will depend on how you have defined both MaxLoadTasks and MaxLoadAWT. If MaxLoadAWT is zero, then you will be limited to a max of 15 load jobs. If MaxLoadAWT is > 0, then whatever you set MaxLoadTask to will be honored up to 30, but only if the MaxLoadAWT setting can also be satisfied. In other words, you are allowed to use have more load jobs active, but only if does not use so many AMP worker tasks that you might prevent other work from running.

It sounds like you are using TDWM/TASM utility throttles, so you can completely ignore DBS Control settings when it comes to controlling load utility concurrency.

Session Management:

Session Management is is a new feature in Teradata 13.10 that is described in detail in this blog posting. If DisableTDWMSessionRules = TRUE, TASM will ignore session configuration rules and deafult session rules within TASM and everything will work just like it did in 13.0. That is, user-specified MINSESS and MAXSESS will be effective.

When you said in your posting that DisableTDWMSessionRules = TRUE if OFF, do you mean it is set to FALSE? If DisableTDWMSessionRules = FALSE, then the number of sessions per load job is being decided for you by TASM (as I described in the posting above) and rules related to sessions that have been defined. But this has nothing to do with load concurrency. This only has to do with when the load job runs, the number of sesssions it will use.

Thanks, -Carrie
Teradata Employee
Sorry, I misspoke when I said utility throttles were new in Teradata 12.0. They were actually introduced in Teradata V2R6.1. However, it was not until Teradata 12.0 that they were able to cleanly delay load jobs.
Enthusiast
Carrie, Thanks for the clarification. I meant TASM session management to be OFF, by setting DisableTDWMSessionRules = TRUE, which you already explained.
Enthusiast
Hi,
We run Datastage as our ETL tool, and find it hard to combine the best practices regarding number of session between our Datastage environment and our Teradata environment. The Datastage recommandation is: number of DS nodes * number of SessionsPerPlayer = MAX Sessions on Teradata.
The Teradata recommandation has so far been number of sessions as a multiple of your node number. (http://forums.teradata.com/forum/tools/how-to-select- sessionstenacitysle...).

This new functionallity overrides all previous tuning efforts made by the ETL and DBA team combined, but we rather would like to make the new functionallity work than to just go back to how things were in the old days (13.0)...
Enthusiast
now with a working link...

http://forums.teradata.com/forum/tools/how-to-select-sessionstenacitysleepcheckpoint-etc-for-fastloadmultiloadtpump-utilities
Teradata Employee
You can create multiple customized session management rules if you wish, to reflect the number of sessions you have determined prior to 13.10 to be optimal for different of your load jobs. While the new session management feature does indeed override previous script settings, you may be able to get similar results by creating customized rules. You can specify numbers of sessions in these session management rules as high as one sesssion per AMP, if you wish, although that is usually not recommended (as the information in the link you included points out).

Thanks, -Carrie
Enthusiast
Hello Carrie,

It appears that our system is consistently running over 62 Amp Worker Tasks. Currently, I am trying to get a handle on the data loads. I would like understand if the Mload sessions are consuming multiple AWTs or just 1 for each multi-threaded load. In Viewpoint, only the "master" session appears to have a workload assigned, but all have session IDs. We are on 13.10

Thanks for any insight into this issue.
Teradata Employee
Hello Carrie,

I was just trying to ask below.
1.) Utility concurrency control; and 2.) Session Management.
I had to test with FEXP configuration file & session number in TTU shell scripts.
However, the final adjustments were MWD.
They do not even disable MWD adjustment.
But the answer was found in your blog.
ANSWER is DisableTDWMSessionRules = TRUE

Thank you.
GS

GS
Teradata Employee
Elle,

The number of AMP worker tasks used by MultiiLoad depends on the phase of the load utility. MultiLoad (or the TPT load operator) uses 2 AWTs per AMP for the acquisition phase, and 1 AWT/AMP for the apply phase.

Thanks, -Carrie
Enthusiast
Carrie,

If we are using TASM, do we still need to use Teancity in our MLOAD jobs? They seem to get hung up in the SESDELAY until they reeach thier Tenacity limit and a load slot is open.
We were wondering if we could just remove Teancity so jobs would get released faster.
Teradata Employee
Hi Bob,

If you are using TASM, it is recommended that you use TASM utility throttle to manage concurrency of load jobs. TENACITY and SLEEP will be ignored if the load job is under the control of a utility throttles using the delay option, but they will be recognized and utilitized if the utility throttle reject option has been specified.

TENACITY and SLEEP are processed on the client side. Only when the database returns the error 2633 “Too many load tasks” do those parameters have a role to play. With the delay option, the database will never return the 2633 error and from the client’s perspective, the load job is running. With the delay option a job that would cause the utility throttle limit to be exceeded will be held in the delay queue until utility throttles are no longer exceeded.

Enhancements made to utility throttles in Teradata 13.10 will change this somewhat. When you get on this new release, TENACITY and SLEEP are no longer compatible with utility throttles that specify the reject option. This is due to changes in the database/client protocol needed to support enhancements in utility throttles in Teradata 13.10 (see the sidebar). So it is recommended that once you get onto Teradata 13. 10 you always use the delay option with utilities.

Thanks, -Carrie
Enthusiast
Hi Carrie. Great article but I'm confused about something. On the Viewpoint 14.0.0.5 Workload Designer Add Util Sess Limit screen it implies the number of sessions you enter there is a LIMIT, not a quantity to override the sessions requested by script. At the top of the screen it says "Please choose the Utilities to which this session limit applies". And the help for the Max Sessions field says "In Max Sessions, enter the maximum number of sessions that are allowed to be logged on to each of the selected utilities at one time."

But this article states that the Workload Designer rule OVERRIDEs what is in the script, not LIMITs it. Which is right? Or am I looking at the wrong page in Workload Designer?
Teradata Employee
Hi Keith,

I think you might be taking a wrong turn in Workload Designer.

When you first get into Workload Designer you will see the various category icons at the top, among them one titled "Sessions." When you click on "Sessions" you see several tabs. One of the tabs you can select will say "Utility Limits" and another tab will say "Utility Session". I think you may be clicking on the "Utility Limits" tab when you want to be clicking on the "Utility Session" tab. When you click on the "Utility Session" tab, you will see all of the default session management rules listed, and on the upper right you will see a button that says: "Create Utility Session" rule, which allows you to define a customized utility session rule. I am not seeing any text related to "limits" in this particular path.

Check again and make sure you are going down the correct series of screens, as both session management AND utility throttles appear under the "Sessions" category, which is a bit confusing.

Thanks, -Carrie
Teradata Employee
Hi Keith,

I now see that you were indeed on the correct navigation path, I just did not continue on down the path as far as you did.

Navigating deeper, I can now see you where the Viewpoint screens use the terminology that you are pointing out is confusing. Your opening an incident with the support center on that is a good idea, so changes can be considered and any enhancements tracked.

Thank you, and sorry about my confusion! -Carrie
Teradata Employee
Hi Carrie,

I was informed of a case where the Utility Session applies as a limit for network-attached clients. But for those channel-attached, i.e. Mainframe, this behaves such as what you describe above.

Does the "type" of client have something to do with how TASM would interpret the Utility Session?

Thanks,
Rommel
Teradata Employee
Rommel,

By design, load jobs from both mainframe and network attached clients should be treated the same in regards to session management in the database. This assumes that both client platforms are on TTU 13.10. Is it possible in the case that you describe that the network attached clients were on TTU 13.0 or earlier? While the mainframe client was on TTU 13.10?

Both client and database have to be on TTU 13.10 in order for the number of sessions to be treated as described in the posting above. A mis-match in software levels is the only thing I can think of that could explain the situation you describe, based on expected functionality.

Thanks, -Carrie
Teradata Employee
Hi Carrie,

Thanks for clarifying. I'll have to verify the installed TTU.

Rommel
Enthusiast
Hi carrie, i'm very big fan of you,

Could you plz suggest me how can i set query band for INSERT,DELETE for a session
Teradata Employee
You can use query band value-pairs at either the transaction or the session level to help direct queries or utilities to specific workloads. However, you cannot specify in a query band alone that you want to support any particular operation for a request or a utility job. Query bands are only tags, they don't by themselves instigate anay action. Only workload classification can control the types of operations that can be performed for the requests that classify to that workload.

In TASM there is a statement type classification you can specify for a workload. The choices of statement type that you can choose are DDL, DML and SELECT. Inserts and deletes would be covered under DML. If you do not specify a statement type classification, then that workload will support any of those three types of statements. Please read the TASM orange book for more details on classification choices and options. The Viewpoint User Guide also has detail about classification criteria (including query bands) and how to apply it in Workload Designer.

Thanks, -Carrie
Hi Carrie,

We have a situation where we have 10 Multiload jobs running from the mainframe, and we have the limits set to 16 max and 4 min in the load script. When we migrated from v12 with tasm 12 to v13.10 with tasm 13.10 we got burned. what happened is the new defaults took effect, and we went from running 10 jobs with 16 sessions conncurrently (160 sessions total) to trying to run 10 jobs with 28 sessions concurrently (280 sessions total) however we only had 220 sessions available on the system and jobs begain to fail becuase they couldn't get all 28 sessions logged on. So we turned off TASM to get around this problem. I have now adjusted my limits for the defaults to be 16, and all seem to work fine, however, we have several MLOAD jobs which run from the mainframe to update a single table with a single row, so they are using 1 session in the script. When these jobs run with 16 sessions now using TASM, they fail with a CLI0155 error - DBCHCL returned 155. The specified session does not exist. We disabled TASM again, and the process stopped failing. I have a ticket open to better understand, and they provided a link to this exchange for more information regarding the session limits.

I'm trying to understand how to simply disable Utility Session control from TASM so that I can turn TASM back on to manage my regular workload. I noticed information above on the DBS Control setting DisableTDWMSessionRules = TRUE, however I cannot see this setting in my DBS Control view. Can I get more specifics on this setting?
Teradata Employee
Hi James,

DisableTDWMSessionRules is an internal field in DBS Control. If you set it to TRUE then the pre-13.10 behavior will be preserved, and you can run TASM without the centralized session management. If you have an incident open, you could discuss that change with the support center.

DBS Control Record - Internal Fields:

198. DisableTDWMSessionRules = FALSE (Enabled if TDWM active)

Have you tried creating your own customized session rule for just those multiload jobs that only require 1 session? You can classify the new rule by multiload and then also by any WHO criteria, such as user, account, queryband, etc. That way only those jobs that need 1 (and don't wan't more than 1) session will match to that customized rule. Make sure you put it high in the evaluation order, as the utility will use whichever session rule it comes to first based on evaluation order.

You might want to play around with designing a few customized rules first and see how that works for you, before deciding to turn off the feature completely.

Thanks, -Carrie
Carrie,

Thanks for the response. Just out of curiosity, do you know whether changing this particular DBS Control requires a restart or not? I will also play around with creating different customized rules, and with the evaluation order as well.

Thanks.
Teradata Employee
No restart required.

Here's what the documentation I have on that parameter states:

198. DisableTDWMSessionRules - This field disables TDWM session configuration rules which determine the number of sessions to be used for a utility. When TDWM is disabled, this field is ignored. The behaviors remain the same as in TD13.0. Teradata server will allow up to one session per AMP. When TDWM is enabled and this field is set to TRUE, the behaviors also remain the same as in TD13.0 (same as TDWM disabled). When TDWM is enabled and this field is set to FALSE, Teradata server will select the number of utility sessions according to TDWM session rules.

The default value is FALSE.
Default: ==> FALSE
Recommended setting: ==> Default
Tpareset Required? ==> NO

Good luck! -Carrie
Carrie,

Thanks, exactly what I needed. The single MLOADS that are failling are using the same appliation user id and acctstring, so trying to create an alternative group may not work. The only way would be to change the actual code/script for the single MLOADS to use a queryband, but that will take some effort with the ETL team, and I'm trying to enable TASM and turn off the session limits pretty quickly.

Thanks again!!!
Enthusiast
Hi Carrie,
We have been running with TD13.10 for a few months now and everything seems to be going along smoothly with the default settings. Just recently we have had an increase in the number of FastExport jobs being run over the weekend and this appears to be impacting our Datastage ETL processing. The Datastage jobs keep failing with "Too many load/unload tasks running" and we are trying to determine a way of identifying how many load jobs were running around that time.
I have tried using DBQL tables, but they seem to log around 50 individual lines when only 10 sessions were used for one of the MLOAD jobs running from the mainframe. The session history table in which we capture a snapshot of dbc.sessioninfo every 10 minutes also does not seem that helpful in easily identifying the individual load jobs whith no reference to the number of sessions.
Is there an easy way to identify how many of our load slots are being used at any one time?
I am looking at setting up a workload that identifies all users, excluding our production users, and the use of Load Utility jobs and setting a query limit of 5 during our peak processing times - would this be the right method to achieve this?
Teradata Employee
Hi Mark,

The best way to determine the number of load utilities active at any point in time using the workload management APIs. Workload Management API consists of interfaces to PM/APIs and open APIs. See the manual titled:

Workload Management API: PM/API and Open API

The command you would want to issue is:

SELECT * FROM TABLE (TDWMLoadUtilSTATISTICS()) AS t1;

*** Query completed. 4 rows found. 3 columns returned.
*** Total elapsed time was 3 seconds.

UtilityType UtilityCount UtilityLimit
----------- ------------ ------------
MultiLoad 0 30
FastLoad 0 30
FastExport 0 60
ARC 0 350

Note that the DBA must grant EXECUTE FUNCTION and EXECUTE PROCEDURE privileges on
UDFs and external stored procedures in order for a user to access them. These privileges
are not granted by default.

Thanks, -Carrie
Enthusiast
Carrie,
Thanks for that, looks like what I am after. Is this function also likely to pickup idle sessions, as I have no load jobs actually running but get the following result:-

UtilityType UtilityCount UtilityLimit
1 ML/FL 1 30
2 MLoad 1 30
3 FLoad 0 30
4 FastExport 0 60
5 ARC 0 350
6 SA MLoad 1 30
7 SA FLoad 0 30
8 SA FExp 0 60
9 TPT Update 0 30
10 TPT Load 0 30
11 TPT Export 0 60
12 JDBC MLoad 0 30
13 JDBC FLoad 0 30
14 JDBC FExp 0 60
15 CSP FLoad 0 30

Also, can you advise what SA and CSP loads represents?
Teradata Employee
Mark,

If you are using MLoad 14.00.00.06, that erroneous utility count for MultiLoad could be due to a known TTU problem which has been fixed in MLoad14.00.00.07. Even without the fix, the count should eventually go back to zero after one timeout period (default to 5 minutes).

SA is for "stand alone" and CSP is for "saving crash dumps" which uses the FastLoad protocol.

Thanks, -Carrie
Enthusiast
Hi Carrie,

Great Article!! I have couple of questions here.

1) We are on 13.10 and viewpoint V 14.02. We have max sessions set to 10. So if there are say 7 fastload jobs running , we would have 70 active sessions. I would like to know the total number of sessions that we can get from the DB. My understanding is ,since we have 2 PEs ,we would have 240 sessions in total. Is my understanding correct? So at a time, we would not be able to run more than 24 jobs concurrently. In one of the thread above, it is mentioned as 30? Could you please clarify?

2) My second question is - Our development team ran the same set of fastload jobs around early morning and once at mid day. One of the jobs that ran early morning took 2.5 minutes , however the same job when executed during mid day ran for 15 minutes. Both the jobs took only 10 sessions each. I am not able to figure out the lag. Could you please help.

Thanks
Roopalini
Teradata Employee
Roopalini,

In terms of total number of sessions, you are correct that each PE is limited to 120, so you can multiply the number of PEs by 120 and that is the max number of sessions your system can support. If you only have 2 PEs, you are right that you would be limited to 240 sessions.

The "30" mentioned in the comments above had to do with the max number of utility jobs (MultiLoad and FastLoad) that are allowed to run concurrently. That is different from the number of sessions that each job is able to use.

To respond to your second question, there are many factors involved in the elapsed time of a load job. This includes details such as the priority that the job is assigned, what else is running on the platform at the same, contention for resources such as I/O or CPU, the power of the client at that point in time to send the data into Teradata, the capacity of the network. The number of sessions is really a minor part of that combination of factors. If the one that ran longer was running during a busy time of day, that is probably the explanation, but you could check all those other things as well.

Thanks, -Carrie
Enthusiast
Thanks for the response Carrie!!
Enthusiast
Hi Carrie

can you kindly confirm if Fast export shows as Responding Mode in viewpoint in 13.10 and running under Workload created for Utility will hold a utility slot defined for it or not ?

Thanks
Abhijit
Fan
Why CPU Skew very high during TPTUPD Loading, But Another Jobs not high, Why ?
Teradata Employee
Abhijit,

FastExport will hold a utility slot until the response mode is complete.

Thanks, -Carrie
Teradata Employee
GS Oh,

There are many reasons why skew might be high on one load job, but not another. It depends on the characteristics of the data being loaded and which AMPs the data is going to. More data could be going to a subset of AMPs, for example.

Another factor is how the table being loaded is spread across the AMPs. It may be that the table being loaded has a skewed primary index and that could contribute to the load job being skewed. There can be many reasons for skew. Those are the things to consider first.

Thanks, -Carrie