Archives of the TeradataForum

Message Posted: Tue, 03 Sep 2002 @ 12:54:13 GMT

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

To reset the spool space fro Teradata user we created a macro who generated the reset code :

REPLACE MACRO databasename.ClearPeakUser (p_user  CHAR(30))
AS  ( ECHO '.SET SEPARATOR 0';
      ECHO '.SET FOLDLINE ON 1,2';
      UPDATE DBC.Acctg SET CPU = 0, IO = 0 WHERE UserName
:p_user;
      SELECT ' UPDATE DBC.DataBaseSpace ' (TITLE ''),
             'SET PeakPermSpace = 0, PeakSpoolSpace = 0,
PeakTempSpace = 0
' (TITLE ''),
             'WHERE DataBaseSpace.databaseid = '''(TITLE ''),
              Dbase.databaseid (TITLE ''),
              '''XB;' (TITLE '')
      FROM    DBC.Dbase
      WHERE   DBase.databasename = :p_user;
      ECHO '.SET SEPARATOR 1';
      ECHO '.SET FOLDLINE OFF';)
;

After execution of the macro 'ClearPeakUser', we analyse the spool with this macro. The macro gives you the information for the utilization of the CPUs, you can see if your request is well balance.

REPLACE MACRO databasename.PEAKSUSER (P_USER VARCHAR(30))
AS
(SELECT 'Peak   '                       (TITLE '' )
       ,MIN(PeakSpoolSpace)             (TITLE 'MIN' , FORMAT 'ZZ,ZZZ,ZZZ,ZZ9')
       ,MAX(PeakSpoolSpace)             (TITLE 'MAX' , FORMAT 'ZZ,ZZZ,ZZZ,ZZ9')
       ,SUM(PeakSpoolSpace)             (TITLE 'TOT', FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9')
       ,MAX(PeakSpoolSpace) * (HASHAMP()+1) (TITLE 'NEEDS', FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9')
       ,SUM(MaxSpoolSpace)              (TITLE 'SPOOL AUTH', FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9')
 FROM   dbc.DataBaseSpace
       ,Dbc.dbase
 WHERE  databasename = :P_USER
   AND  DataBaseSpace.TableID = '000000000000'XB
   AND  dbase.databaseid = DataBaseSpace.databaseid
 UNION
 SELECT 'Current'
       ,MIN(CurrentSpoolSpace)
       ,MAX(CurrentSpoolSpace)
       ,SUM(CurrentSpoolSpace)
       ,MAX(CurrentSpoolSpace) * (HASHAMP()+1)
       ,NULL
 FROM   dbc.DataBaseSpace
       ,Dbc.dbase
 WHERE  databasename = :P_USER
   AND  DataBaseSpace.TableID = '000000000000'XB
   AND  dbase.databaseid = DataBaseSpace.databaseid
 ORDER BY 1
;SELECT  :P_USER      (TITLE 'USER', FORMAT 'X(8)')
        ,ACCOUNTNAME  (TITLE 'ACCOUNT'FORMAT 'X(7)')
        ,SUM(CPUTIME) (TITLE 'TOT_CPU',FORMAT 'Z,ZZZ,ZZ9')
        ,SUM(DISKIO)  (TITLE 'TOT_I/O',FORMAT 'Z,ZZZ,ZZZ,ZZ9')
        ,MIN(CPUTIME) (TITLE 'MIN_CPU',FORMAT 'ZZZ,ZZ9')
        ,MAX(CPUTIME) (TITLE 'MAX_CPU',FORMAT 'ZZZ,ZZ9')
        ,MIN(DISKIO)  (TITLE 'MIN_I/O',FORMAT 'ZZZ,ZZZ,ZZ9')
        ,MAX(DISKIO)  (TITLE 'MAX_I/O',FORMAT 'ZZZ,ZZZ,ZZ9')
 FROM  (SELECT ACCOUNTNAME,SUM(CPUTIME),SUM(DISKIO)
        FROM   DBC.AMPUSAGE
        WHERE  USERNAME = :P_USER
        GROUP BY VPROC,1)
        AS
        TD_SOMM(ACCOUNTNAME,CPUTIME,DISKIO)
 GROUP  BY 2
 ORDER  BY 1,2;)
;

Rudel Simard
R�gie de l'assurance-maladie du Qu�bec
DBA de l'environnement informationnel

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