Archives of the TeradataForum

Message Posted: Wed, 30 Jun 2004 @ 12:43:09 GMT

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

Sorry about that...the below e-mail response was supposed to go to someone else who wanted to track spool usage for a user during a query to see if they were "hot amping". This is what I get for trying to respond to more that 1 e-mail at a time. Don't worry though this response went to him and I got a pretty strange reply back from him!!!

So, for your tracking of spool usage, we set up a table in our DBA database area as:

     CREATE SET TABLE databasename.tablename ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           DateCollected DATE FORMAT 'yyyy-mm-dd' NOT NULL,
           UserName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           OwnerName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           PeakSpoolSpace FLOAT FORMAT '----,---,---,---,--9' NOT NULL)
     PRIMARY INDEX ( UserName )
     UNIQUE INDEX ( DateCollected ,UserName );

And use the following on a daily basis to insert into this table:

     insert into databasename.tablename
     select  date, dsk.databasename, db.ownername, sum(peakspool)
        from
                dbc.diskspace dsk,
                dbc.dbase db
          where  dsk.databasename = db.databasename
              group by 1,2,3
              having sum(peakspool) > 0;

We then run an update on databasespace for peakspoolspace only: ( we do other things on a weekly basis for the other peak values)

     UPDATE DBC.DataBaseSpace
       SET  PeakSpoolSpace = 0 all;

You can then report on user peakspool and watch for growth abnormalities.

Hope this was better help than the other e-mail!!!!

Again..Sorry about that!

Kevin R Tewksbury
Database Administrator
Limited Technology Services

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