Archives of the TeradataForum

Message Posted: Wed, 13 Sep 2000 @ 16:18:10 GMT

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

Hakim,

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.

Yonina Schuchman

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