Archives of the TeradataForum

Message Posted: Thu, 16 Dec 1999 @ 08:25:46 GMT

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

Here are 2 macros used by our users to check their spool space. The following macros has been created +- 2 years ago.... And I don't think it is out of date.

     REPLACE MACRO CISMON.UNSTABLE
            AS
            (
            LOCK DBC.DISKSPACE FOR ACCESS
            SEL
            DATABASENAME
             ( UC, TITLE 'WHO?', FORMAT 'X(30)'),
            COUNT(VPROC)
             (DECIMAL(3,0), TITLE 'AMPS', FORMAT 'ZZ9'),
            MAX(CURRENTSPOOL (FLOAT)) / 1048576.00
             (DECIMAL(8,2), TITLE 'MAX (MB)', FORMAT 'ZZBZZ9.99'),
            MIN(CURRENTSPOOL (FLOAT)) / 1048576.00
             (DECIMAL(8,2), TITLE 'MIN (MB)', FORMAT 'ZZBZZ9.99'),
            ZEROIFNULL(MAX(CURRENTSPOOL (FLOAT)) /
             NULLIFZERO(AVG(CURRENTSPOOL (FLOAT))) )
             (DECIMAL(6,2), TITLE 'RATIO', FORMAT 'ZZ9.99'),
            SUM(CURRENTSPOOL (FLOAT)) / 1048576.00
             (DECIMAL(9,2), TITLE 'CURRENT (MB)', FORMAT 'ZZZBZZ9.99'),
            ZEROIFNULL(MAX(CURRENTSPOOL (FLOAT)) * 100.0000 /
             NULLIFZERO(MIN(MAXSPOOL (FLOAT)) (FLOAT)) )
             (DECIMAL(6,2), TITLE 'MAX (%)', FORMAT 'ZZ9.99')
            FROM DBC.DISKSPACE
            HAVING MAX(CURRENTSPOOL (DECIMAL(15,2))) <> 0.00
            GROUP BY 1
            ORDER BY 5 DESC, 6 DESC, 1, 2;
        )

In case of a high 'RATIO' users can check for any hotAMP by executing the following macro.

     replace macro cismon.hot_spool(
          p_database  varchar(30) ) as
     (
     LOCK DBC.DISKSPACE FOR ACCESS
     select vproc ,
            currentspool (format 'ZZZ,ZZZ,ZZZ,ZZ9') (title 'CURRENT'),
            peakspool (format 'ZZZ,ZZZ,ZZZ,ZZ9') (title 'PEAK'),
            maxspool (format 'ZZZ,ZZZ,ZZZ,ZZ9') (title 'MAX')
       from dbc.diskspace
      where (databasename  = :p_database
         )
      order by 1,2 desc;
     );

Mackels Yves
DBA Teradata @ Belgacom

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