Archives of the TeradataForum

Message Posted: Fri, 21 Nov 2008 @ 14:57:37 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Getting a session's own resource info

From:		L�vai �kos

DWellman wrote:

CPU and IO is available for a session, the difficult bit in your requirements are to get it "within the session".

Working through the possible options:

1) DBC.AmpUsage: you can use Account String Expansion (ASE) to generate 1 row per amp per session. However, the AmpUsage rows are not written until the query has finished. If you don't mind missing out the resource figures for the query that retrieves the resources figures then this is probably the easiest mechanism. What you do need to do though is to set up ASE for the relevant usernames.

2) PM/API or PM&PC - whatever you want to call it: this information is updated every 'n' seconds but may be too old for your needs. On a lot of sites 'n' is 300-600 seconds (i.e. 5 to 10 minutes). That can be changed dynamically by a program issuing the commands. Unless you are on TD 12 this data is NOT available from SQL. You have to write a cliv2 program.

3) DBQL: the data is available, but after a time delay which is controlled by one of the DBSControl settings. I can't remember which one off the top of my head, read the manual for this.

Finding the correct queries is easy as your code would already know the session number, use that as part of the search criteria when reading the DBQL table for the data. The issue here may be that if you're reading the DBQL table in database DBC then your query is going to be a full table scan. This 'may take some time' (as the saying goes !).

What are you actually trying to achieve ?

Hi,

Thank you for the suggestions, option 1 seems to be the right way!

My goal is to measure the resource consumption of an SQL script (or at least a whole session), run from the SLJM environment.

The simplest way would be if the runner script (Bteq) would select the resource consumption from the database, and write into the log (out), by supplement each script with a predefined SQL.

In Oracle it is a quite simple method, the resource measuring tables (maybe virtual tables) are effectively accessible.

By the advice I planned to do the following:

- Set the users' account string to &D&H&S [date,hour,sessionID, time is maybe unneccessary]Causes negligible impact according to the Teradata Docs.

- Log the session's ID and the expanded account string (substituted date,time, and session ID parts)

- Assign the resource consumption to the taks with post processing

The resource info can be found in the AmpUsage view (Acctg table). The PI of the underlying table is (AccountName,Username), where Accountname is the expanded one (logically)

My current problem is that I can not find any information about how to get the expanded account string of a (current) session. Every dbc tables I've checked (eg.dbc.sessiontbl) contains the raw (unexpanded) account string.

Does anyone have any idea?

thanks,

�kos


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference