Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Spool space for Teradata users
 
From:   Rudel Simard

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023