Archives of the TeradataForum

Message Posted: Fri, 14 Sep 2001 @ 13:47:41 GMT

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

What I prefer is to use Account String Expansion (ASE) - refer to the Database Administration manual, Chapter 5. ASE permits you to have control over how you log the system activity that is placed in the DBC.ACCTG table (or the view DBC.AMPUSAGE). The contents of DBC.ACCTG reflects the actual work that has been performed on the system (CPU and I/O).

The problem of depending on the DBC.SESSIONTBL table for system activity is that it only reflects that a session exists, but contains no information about the actual work performed. And unless you're constantly sampling the DBC.SESSIONTBL, you'll miss short duration sessions (where a logon occurs, a query executed and then a logoff). The same would be true of periodic sampling of the DBC.ACCTG table or spool space.

What I like to do is to give everybody a default account string of &L&D&T. When this is expanded to the DBC.ACCTG table, it gives the logon time of the user and then the date/time of each query submitted and the amount of work that was performed. This has some, but relatively minimal performance impact on the system.

For users, like production jobs, where I don't care (or already know) how many queries they submit, I give them a default account string of &L. You definitely don't want to use &L&D&T with TPump.

Using &L&D&T can create a lot of entries in the DBC.ACCTG table which I summarize into a history table and then delete from DBC.ACCTG. I think that there is a good trade-off between the system impact of &L&D&T and the value it can bring.

For example (since this is too broad of a subject to cover in a single note), using &L&D&T, you can tell things like:

- The &L portion of the ACCOUNTNAME from DBC.ACCTG and the USERNAME permits you to join with the session information contained in the DBC.SESSIONTBL, specifically column LOGONSOURCE. Using the information in DBC.SESSIONTBL and column LOGONSOURCE tells you where a query came from (ie- IP Address or Host). If MVS initiated, then the host utility which was used, etc.

- The &D&T portion of the ACCOUNTNAME from DBC.ACCTG permits you to extract the number of queries executed by a session and the date/time when they were executed.

- Since the individual queries are identifiable, you can isolate those queries which present workloads that are unevenly distributed across the AMPs.

Just a quick comment about the contents of DBC.ACCTG: It reflects actual work performed by the AMPs. So if somebody logged-on, went to lunch and then finished off their day by logging-off when they got back, DBC.ACCTG will have no rows. If you do something like 'SEL DATE,TIME;', then DBC.ACCTG will contain no rows because all the work for that query was performed by the PEs. Anything else, whether spool is generated or not, will be reflected in DBC.ACCTG.

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