Archives of the TeradataForum

Message Posted: Thu, 09 Dec 2004 @ 14:42:20 GMT

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

We create a macro to reset the peak of the user and a macro to give you the minimum and maximum spool space by VAMP, the sum of the spool space for all VAMP et finally the spool space needed (the higher VAMP spool space * by the number of VAMP).

Hope that going to help you.

You use this macro in BTEQ, after execution, re-execute the result to reset the user's peak.

     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';)
     ;

You use this macro to see what I explain in introduction :

     REPLACE MACRO databasename.PEAKSPUSER (P_USER VARCHAR(30))
     AS
     (SELECT MIN(PeakPermSpace)             (TITLE 'MIN_PEAK' , FORMAT 'ZZ,ZZZ,ZZZ,ZZ9')
            ,MAX(PeakPermSpace)             (TITLE 'MAX_PEAK' , FORMAT 'ZZ,ZZZ,ZZZ,ZZ9')
            ,SUM(PeakPermSpace)             (TITLE 'TOT_PEAKS', FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9')
            ,MAX(PeakPermSpace) * (HASHAMP()+1) (TITLE 'TOT_PEAKS//needed', FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9')
            ,MAX(CurrentPermSpace) * (HASHAMP()+1)  (TITLE 'Current Perm//use', FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9')
            ,SUM(MaxPermSpace)              (TITLE 'PERM SPACE//Total', FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9')

      FROM   dbc.DataBaseSpace
            ,Dbc.dbase
      WHERE  databasename = :P_USER
        AND  DataBaseSpace.TableID = '000000000000'XB
        AND  dbase.databaseid = DataBaseSpace.databaseid
     ;);

Rudel Simard

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