All Forums Database
barani_sachin 141 posts Joined 01/12
18 Dec 2012
Can someone please explain the below query in detail?

Please expalin in detail how exactly we are getting total size of the DB and the free space in the DB.
Query used to find the free space.

SELECT DATABASENAME,SUM(MAXPERM)/1073741824 AS TOTAL_SPACE, 

SUM(CURRENTPERM)/1073741824 AS USED_SPACE, (TOTAL_SPACE - USED_SPACE) AS FREE_SPACE 

FROM DBC.DISKSPACE WHERE DATABASENAME ='XXXX'

GROUP BY 1

What is MAXPERM, CURRENTPERM ?
 
Please expalin in detail how exactly we are getting the skew factor of the table in the below query.
Query used to find the skew factor of a table.
 

SELECT TSIZE.DATABASENAME

,TSIZE.TABLENAME

,SUM(TSIZE.CURRENTPERM) AS CURRENTPERM

,SUM(TSIZE.PEAKPERM) AS PEAKPERM,

(100 - (AVG(TSIZE.CURRENTPERM)/MAX(TSIZE.CURRENTPERM)*100)) AS SKEWFACTOR

FROM DBC.TABLESIZE TSIZE

JOIN DBC.TABLES TDEF

ON TSIZE.DATABASENAME = TDEF.DATABASENAME

AND TSIZE.TABLENAME = TDEF.TABLENAME

AND TSIZE.DATABASENAME='YYYY'

GROUP BY 1,2,3,4

ORDER BY SKEWFACTOR DESC

 What is PEAKPERM?
 
Thanks in advance :)

Harpreet Singh 101 posts Joined 10/11
19 Dec 2012

You will get lot of interesting information in Teradata Administration manual chapter 4 Space. here is extract from chapter which tells about peek, current,max
CURRENTPERM The total number of bytes (including table headers) currently allocated
to existing data tables, index tables and subtables, stored procedures,
triggers, and permanent journals residing in a particular database/user.
This value is maintained on each AMP.
 
MAXPERM The maximum number of bytes available for storage of all (current and
future) data tables, index tables and subtables, stored procedures,
triggers, and permanent journals owned by a particular database/user.
Note: For each database or user reported, the number of permanent
bytes is divided by the number of AMPs in the configuration. The result
is recorded on each AMP and may not be exceeded on that AMP. A user
may own several objects, as long as the combined storage requirements
are within the MaxPerm limit set for the user on each AMP.
 
PEAKPERM The largest number of bytes ever used to store data in a user or database
since the last reset of this value to zero.
This value is maintained on each AMP.
To reset the PeakPerm value to zero, use the DBC.ClearPeakDisk macro
HTH
Harpreet

barani_sachin 141 posts Joined 01/12
19 Dec 2012

Thanks for the info :)

You must sign in to leave a comment.