Archives of the TeradataForum

Message Posted: Wed, 30 Jul 2003 @ 13:08:49 GMT

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

You can look at it in WinDDI (Right click on the parent database and select Child Space) or you could capture the information from DBC.Tables (in the code below specify the database name in addition to TableName = 'All'.

              /* Create a table of Todays database space Totals */
              CREATE VOLATILE TABLE DatabaseSpaceVTemp,
                 NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
                 (
                 DatabaseName     VARCHAR(40),
                 MaxPermSpace     DECIMAL(17,2),
                 CurrentPermSpace DECIMAL(17,2),
                 PeakPermSpace    DECIMAL(17,2),
                 MaxSpoolSpace    DECIMAL(17,2),
                 PeakSpoolSpace   DECIMAL(17,2)
                 )
              UNIQUE PRIMARY INDEX (DatabaseName) ON COMMIT PRESERVE ROWS;

              INSERT INTO DatabaseSpaceVTemp
              SELECT DatabaseName,
                     sum(maxperm) MAXPERM,   sum(currentperm) CURPERM,
                     sum(peakperm) PEAKPERM,
                     sum(maxspool) MAXSPOOL, sum(peakspool) PEAKSPOOL
               FROM dbc.allspace
               WHERE tablename = 'All'
               GROUP BY databasename;

              /* Create a table of Total Database Space */
              CREATE VOLATILE TABLE DatabaseSpaceTotalVTemp,
                 NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
                 (
                 MaxPermSpace     DECIMAL(17,2),
                 CurrentPermSpace DECIMAL(17,2),
                 PeakPermSpace    DECIMAL(17,2),
                 MaxSpoolSpace    DECIMAL(17,2),
                 PeakSpoolSpace   DECIMAL(17,2)
                 )
              PRIMARY INDEX (MaxPermSpace) ON COMMIT PRESERVE ROWS;

              INSERT INTO DatabaseSpaceTotalVTemp
              SELECT sum(maxpermspace) MAXPERM,   sum(currentpermspace) CURPERM,
                     sum(peakpermspace) PEAKPERM,
                     sum(maxspoolspace) MAXSPOOL, sum(peakspoolspace) PEAKSPOOL
               FROM DatabaseSpaceVTemp;

/***************************************************************************
               * Set Reporting Defaults and Print Heading
***************************************************************************/
              .set width 132
              .set heading 'Database Sizes by percent Full' || date

/***************************************************************************
               * Report by Percent Full Peak Descending
***************************************************************************/
              select databasename       (title 'Database Name'),
                     maxpermspace       (title 'Max Perm'),
                     (CASE WHEN (maxpermspace > 0)
                           THEN (maxpermspace/1073741824)
                           ELSE 0
                           END)    (title 'Max Gig')  (FORMAT 'ZZZ,ZZ9.9999'),
                     Currentpermspace   (title 'CurrentPerm'),
                     (CASE WHEN (currentpermspace > 0)
                           THEN (currentpermspace/1073741824)
                           ELSE 0
                           END)    (title 'Cur Gig')  (FORMAT 'ZZZ,ZZ9.9999'),
                     (CASE WHEN ((Currentpermspace > 0) and (maxpermspace > 0))
                           THEN (Currentpermspace/maxpermspace)*100
                           ELSE -0
                           END)    (title 'Percent')  (FORMAT 'ZZ9.99%')
              from databasespacevtemp
              WHERE currentpermspace > 0
              ORDER BY 6 desc, 4,1;

Thanks,

Dara

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