Archives of the TeradataForum

Message Posted: Tue, 22 Nov 2005 @ 23:20:40 GMT

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

By way of a recursive query in V2R6, we were able to calculate the total space used by a list of parents and all of their children, grand children, etc. You could probably tweak this to just get the names if you desired:

     WITH RECURSIVE owner (Owner_Name, DatabaseName, PermSpace, CURRENTPERM, VProc, Level) AS (

         SELECT  Databases.DatabaseName AS Owner_Name, Databases.DatabaseName
              , CASE WHEN DISKSPACE.VProc = 0 THEN Databases.PermSpace ELSE 0 END AS PermSpace
              , DiskSpace.CURRENTPERM, DiskSpace.VProc,1
         FROM DBC.DISKSPACE
         INNER JOIN DBC.Databases ON DiskSpace.DatabaseName = Databases.DatabaseName
         WHERE Databases.DatabaseName IN ('parent database here')

     UNION ALL

         SELECT CASE WHEN Level > 1 THEN owner.Owner_Name ELSE Databases.OwnerName END
              , Databases.DatabaseName
              , CASE WHEN DISKSPACE.VProc = 0 THEN Databases.PermSpace ELSE 0 END AS PermSpace
              , DiskSpace.CURRENTPERM, DiskSpace.VProc, Level + 1
         FROM DBC.DISKSPACE
         INNER JOIN DBC.Databases ON DISKSPACE.DatabaseName = Databases.DatabaseName
         INNER JOIN owner ON Databases.OwnerName = owner.DatabaseName AND owner.VProc = 0
         WHERE Level < 5
     )

     SELECT Owner_Name, SUM(PermSpace)/(1024*1024*1024)(INT) AS PermSpaceGb
          , SUM(CURRENTPERM)/(1024*1024*1024)(INT) AS CurrentPermGb
          , ((SUM(CurrentPerm))/NULLIFZERO(Sum(PermSpace))*100) (FORMAT 'zz9.99%') (CHAR(7)) AS PercentUsed
          , Max(Level) MaxDepth
     FROM owner
     GROUP BY 1
     ;

-Adam

Adam Stout
A Teradata Certified Master, V2R5
Certified CFMX Adv. Developer

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