Archives of the TeradataForum

Message Posted: Tue, 27 Nov 2007 @ 23:02:55 GMT

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

We use AcctG to gather this information. You can also use DBQL data and that's a little more elaborate but gives you more flexibility for analysis. But if you don't have DBQL logging enabled for sometime, the analysis can be skewed. For capacity I think acctg will give you the requisite information.

I wrote a routine to extract data out from dbc and move it to a new database location where I can slice 'n dice the data and performance is better. This is on V2R6.2. You can choose to do the same or go directly to dbc.acctg.

We did do a few standardizations before we could start our analysis. The most important was to setup the Account String for all users on the system. We used this format: $PP$DEPT&S&D&H.

PP - Priority like H1, M1, M2 - similar to what you would define if you're using priority scheduler.

DEPT - 4 digit department or process name. For example (MKTU for Marketing User, BILB - Billing Batch). This allows us to delve deeper to gather usage statistics for that user / department / process.

S - Session (SSSSSSSS) - use this in conjunction with dbql to narrow down to performance issues. Just an extension)

D - Date (YYMMDD)

H - Hour (HH) -- Gives you hourly granularity

Of course you can go to more granularity like time, logon time, request.

PS: If you have a way to find out the date and time from the AccountName field then you can use that so the above step is up to you to implement.

I've attached the code that was used. Perhaps you can use it or extract information from it based on your requirements.

1. Create new database (perfstat) and tables and procedure

2. Nightly refresh of dbc.acctg data into perfstat.acctgDetail.

3. Archival / Purging / Summarization of dbc.acctg or perfstat.acctgDetail (Optional but recommended)

4. Compression on perfstat.acctgDetail (This will save you a lot of space)

5. Custom Queries -- you'll find them in the attachment

Hope it helps and good luck.

-Tej

Admin Comment:
--------------

The attachment can be found on the TeradataForum website:

www.teradataforum.com/attach.htm

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