Archives of the TeradataForum

Message Posted: Wed, 06 Dec 2006 @ 19:56:06 GMT

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

Here is a query that accounts for Profile spool allocations. It shows Current, Peak and Allocated spool. Allocated spool is the amount of spool a user can consume at any one time (all queries pull from the same allocation). I also believe the accounting of spool space to table DBC.DataBaseSpace does not record final values when there is a spool error. Someone can verify as I do not have time to look it up. I have never seen it less than 50% variance; it is usually really close to 85-99%.

     SELECT a.databasename (CHAR(10))
           ,a.vproc
           ,sum(a.currentspool)        (format'zzz,zzz,zzz,zzz') (char(15)) as Current_Spool
           ,sum(a.peakspool)         (format'zzz,zzz,zzz,zzz') (char(15) ) as PeaK_Spool
           ,sum(coalesce(a.MaxProfileSpool,a.maxspool) )
                     (format'zzz,zzz,zzz,zzz') (char(15))     as Allocated_Spool
      from      dbc.diskspace a
      where
            a.databasename =  'username'
      group by 1,2
      order by 3 desc,2
     ;

You can get a lot fancier with this query by adding Skew % with OLAP window functions, but I find that the results are sufficient for visual inspection as you normally are only looking at one account at a time.

NOTE: No one commented on this part of the original statement

Parent databases do not have any impact on Users spool usage or consumption. Only rule would be that a User cannot be given more spool than the Parents allocation at the time when assigned. A child can have more spool allocation than a parent if the Parents spool space was lowered after the child's spool was set. Another way is if a child was GIVEN to another database (Give username to databasename;)

Thanks,

Bob Diehl
Travelocity.com

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