Archives of the TeradataForum
Message Posted: Wed, 13 Sep 2000 @ 16:18:10 GMT
I created a process a few years ago that captures daily CPU and IO by user. It could be updated to use the V2R3 calendaring feature.
1. CREATE SET TABLE DBADMIN.AMPUSAGEHIST,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( STATDATE DATE FORMAT 'YYYYMMDD' NOT NULL, STATDAY CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, USERNAME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, ACCOUNTNAME CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC, CPUTIME DECIMAL(11,2), DISKIO FLOAT) UNIQUE PRIMARY INDEX ( STATDATE ,USERNAME ,ACCOUNTNAME ); 2. CREATE SET TABLE DTXXX.SALES_DAY ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL ( DA_CALENDAR DATE FORMAT 'YYYY-MM-DD', ID_SLS_WK SMALLINT NOT NULL, ID_PLAN_YR SMALLINT NOT NULL, NA_DAY CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL ) UNIQUE PRIMARY INDEX ( DA_CALENDAR );
3. ELoad the sales day table with your corporate calendar.
4. replace MACRO dbadmin.GATHERSTATS AS (INSERT DBADMIN.AMPUSAGEHIST (statdate,statday,username,accountname,cputime,diskio) SELECT DATE (FORMAT 'YYYYMMDD'), NA_DAY, USERNAME,max(ACCOUNTNAME), SUM(CPUTIME),SUM(DISKIO) FROM DBC.AMPUSAGE,dtxxx.sales_day where da_calendar=date and username not in ('DBC'.....) GROUP BY NA_DAY,USERNAME; UPDATE DBC.AMPUSAGE SET CPUTIME=0,DISKIO=0 ALL; delete dbadmin.ampusagehist where statdate < add_months(date, -3); ); /*change this to reflect how many months data you wish to keep*/
5. Execute the macro at the end of the day, every day.
6. Here's a sample query I run in MSAccess that gives me CPU and IO usage for Monday's, our heaviest day.
select max(statdate) as STATDATE,id_sls_wk,a.username as USERID,b.commentstring as Teradata_User, sum(CPUTIME) as CPUTIME,sum(DISKIO) as DISKIO from ampusagehist a, dbc.users b,dtmmip01.tmmi020_sales_day c where a.username=b.username and accountname in ('list of accounts you wish to report on') and a.username not like '%TST' and a.username not like '%999' and a.username not like 'APPL%' and statdate > add_months (date,-2) and statday = 'MONDAY' and statdate (format 'YYYY-MM-DD') =da_calendar order by a.username,b.commentstring, id_sls_wk desc group by a.username,b.commentstring,id_sls_wk having sum(CPUTIME) > 0
Hope this is helpful.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|