Archives of the TeradataForum

Message Posted: Thu, 27 Sep 2001 @ 15:30:34 GMT

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

<-- Anonymously Posted: Wednesday, September 19, 2001 11:23 ->

Hi all ,

How about this method of getting Active users based on incremental CPU/IO usage? It will also give incremental CPU usage and DISK IO usage. Don't know if this would miss anything....except for idle sessions to the Teradata.

Let me know what you think.

-------------------------

1) Create 3 tables as follows

     CREATE SET TABLE  NOW_AMPUSE  ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           ACCOUNTNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           USERNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           CPUTIME FLOAT FORMAT '---,---,---,--9.99' NOT NULL,
           DISKIO FLOAT FORMAT '--,---,---,--9' NOT NULL)
     UNIQUE PRIMARY INDEX ( ACCOUNTNAME ,USERNAME );

     CREATE SET TABLE  PAST_AMPUSE  ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           ACCOUNTNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           USERNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           CPUTIME FLOAT FORMAT '---,---,---,--9.99' NOT NULL,
           DISKIO FLOAT FORMAT '--,---,---,--9' NOT NULL)
     UNIQUE PRIMARY INDEX ( ACCOUNTNAME ,USERNAME );

     CREATE SET TABLE  AMPUSE ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           DDATE DATE FORMAT 'YYYY/MM/DD',
           TTIME INTEGER FORMAT '999999' NOT NULL,
           ACCOUNTNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           USERNAME CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
           CPUTIME FLOAT,
           DISKIO FLOAT,
           TAG CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX ( DDATE ,TTIME ,ACCOUNTNAME ,USERNAME );

2) Create a macro as follows

Replace macro GETAMP
As( locking table dbc.acctg for access
 ------------------------------------------------
  -- This table has current amp usage information
 ------------------------------------------------
  delete from  NOW_AMPUSE;
  insert into  NOW_AMPUSE ( ACCOUNTNAME , USERNAME, CPUTIME,  DISKIO )
      Select ACCOUNTNAME(char(16)),
     USERNAME,
     SUM(CPU),
     SUM(IO)
     from DBC.ACCTG
    group by 1,2 ;
 ---------------------------------------------------------------------------
-- This table compares current information to previous amp usage information
 ---------------------------------------------------------------------------
  Insert into  AMPUSE
        ( DDATE,TTIME,ACCOUNTNAME,     USERNAME, CPUTIME, DISKIO )
   select DATE,
     TIME,
     NOW.ACCOUNTNAME,
     NOW.USERNAME,
               NOW.CPUTIME - zeroifnull (BFR.CPUTIME) ,
     NOW.DISKIO - zeroifnull (BFR.DISKIO)
     from  NOW_AMPUSE NOW
     Left outer join   PAST_AMPUSE BFR
    On NOW.ACCOUNTNAME = BFR.ACCOUNTNAME
      and NOW.USERNAME    = BFR.USERNAME
      and NOW.CPUTIME NOT = BFR.CPUTIME
      and NOW.DISKIO NOT  = BFR.DISKIO
;

  ---------------------------------
  --- Copy from Now to past
  ---------------------------------
  delete from  PAST_AMPUSE ALL;
  insert into      PAST_AMPUSE select * from  NOW_AMPUSE;
);

3) Run the macro every x minutes

Exec GetInfo () ;

4)
---------------------------------------------------------------------------
-- Following SQL will give you active users with incremental cpu and diskio
-- in the last x minutes
---------------------------------------------------------------------------
Sel  username,
     sum(cputime) (named cputime) (format'---,---,--9'),
     sum(diskio) (named diskio) (format'---,---,--9')
from   ampuse
where ttime = (sel max(ttime) from  ampuse where ddate = date )
and   ddate = date
group by 1
having cputime > 0
or   diskio > 0
order by 2 desc

;

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