Developing Performance Monitoring Applications - Session Information

Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Developing Performance Monitoring Applications - Session Information

This is the third in a series of articles.

       View the first article.

       View the series index.

To view detail information about the sessions logged on to the Teradata system you should use the GetSessionData method. This method returns a Sessions collection. The sessions in this collection are sorted by user name.

This makes it simple to count the number of sessions for each user, or to group resource usage by user.

You can limit the list of sessions returned by specifying a host id (a 16 bit integer). Specify a host id of –1 to indicate that you want to return all sessions from all hosts; This is the default.

For example, to retrieve all sessions use:

In VB:    Dim Sess As Sessions

              Set Sess = gMon.GetSessionData

In C#:    Sessions Sess;

              Sess = gMon.GetSessionData();

In C++:  _Sessions *pSess;

              HRESULT hr = pMon->GetSessionData(-1, &pSess);

You may then iterate through the Sessions collection to examine the performance values for each individual Session. You may also call the session methods which will be described in later articles.

Note that if you want to ensure that this method is not blocked by another request you must set the IdNames property to False before calling GetSessionData. This will however cause the monitor to sometimes return numeric Database/Table/User Ids instead of the usual Database/Table/User Names.

If you call a session method that is not supported by the version of the database you are connected to, the call will do nothing, and immediately return. The parameters will remain unchanged.

Blocked Sessions

Each Session object contains a Blocks collection. This collection will be empty unless the AMPState is 'BLOCKED'. In this case you can iterate through the list of blocking sessions by using the index values from 1 to Blocks.Count. Teradata reports a maximum of 3 sessions in the block chain. If there are more than 3 sessions blocking your session the MoreBlockers property will be set to True.

Each Block object provides information about the session that is blocking your session and the object on which the lock is held. The session that actually holds the lock will report Granted as True. Other sessions, which report Granted as False, are ahead of you in the lock chain but do not yet hold the lock.

The object provides enumerated values for both LockType (Row Hash, Table or Database) and Severity (Read, Write, Exclusive and Access).

The SQL and Explain Plan

If a session is logged on to the 'DBC/SQL' Partition and is currently executing a request, or is blocked (the AMPState is ACTIVE or BLOCKED), you will be able to fetch the SQL and Explain text by calling the GetSessionSQL method of the Monitor object, or the GetSQL method of an individual session.

The GetSessionSQL method requires input parameters to specify the HostId, SessionNum and RunVproc of the session you are interested in. If you do not know the RunVproc you can specify zero, but this will be less efficient.

Both these methods return the SQL in a BSTR string. They also return the currently executing step number(s) and a Steps collection.

Each Step in the Steps collection contains the parallel step number [StepNum], the EstimatedTime, ActualTime, EstimatedIOs, ActualIOs, Confidence level and StepText properties of that step. The ActualTime and ActualIO properties will only contain valid data after that step has completed.

To retrieve the SQL and Explain for a session:

In VB:   Dim Steps As Steps

             Dim Step1 As Integer

             Dim Step2 As Integer

             Dim SQL As String

             SQL = ses.GetSQL(Step1, Step2, Steps)

In C#:   Steps Steps;

              short Step1, Step2;

              String SQL;

              SQL = ses.GetSQL(Step1, Step2, Steps);

In C++:  _Steps *pSteps = NULL;

              short Step1, Step2;

              BSTR sSQL;

              HRESULT hr = pSes->GetSQL(&Step1, &Step2, &pSteps, &sSQL);

In C, when calling these methods, you must initialize the Steps parameter to NULL before calling the function. Failure to do so will cause your call to fail in Microsoft's setup code.

The values returned in Step1 and Step2 contain the index of the first, and last, step currently executing. If the current step is not a parallel step they will both contain the same value.

Query Bands

The GetQueryBands method is available in both the Monitor and Session classes.

The Monitor level method requires input parameters to specify the HostId, SessionNum and RunVproc of the session you are interested in. If you do not know the RunVproc you can specify zero, but this will be less efficient.

GetQuerybands returns a QueryBands collection that contains all the Querybands that have been set for the specified session and transaction. Note that the collection may be empty.

This feature is only available when the MonitorVersion is 6 or higher. (Teradata 12 or higher)

To retrieve the QueryBands for a session:

In VB:     Dim QBs As QueryBands

              QBs = gMon.GetQueryBands(HostId, SessionNum, RunVproc)

or           QBs = MySession.GetQueryBands()

In C#:    QueryBands QBs;

              QBs = gMon.GetQueryBands(HostId, SessionNum, RunVproc);

or           QBs = MySession.GetQueryBands()

In C++:  _QueryBands *QBs;

              HRESULT hr = pMon->GetSessionData(HostId, SessionNum, RunVproc, &QBs);

or           HRESULT hr = MySession.GetQueryBands(&QBs);

You may then iterate through the QueryBands collection to examine the Name/Value pairs of each individual QueryBand.

Please refer to the Reference Guide for detailed information on the Object Model.