Space check

Database
Highlighted
Enthusiast

Space check

Hi,

 

Could anyone please help on the below..

is there any script to check the total size of Teradata system that we are working on.

I was looking into DBC.Diskspace but not sure about the list of databases to be excluded while calculating the space.

 

I would like to see the total size of the TD system, used space, available space.(Perm, temp and spool)

 

Should I consider the production and devlopment boxes separately or how do I find these details.

 

Kindly help.

 

Thanks.


Accepted Solutions
Supporter

Re: Space check

Akhill,

 

For total space in your Teradata system, you shoud not exclude any databases.

Below SQL gives you system total (Perm Space), plus a lot of detail about databases in the system.

Keep details for each system seperately.

If you have PROD and DEV environment on the same system, you may need to summarize for each environment.

 

LOCKING ROW FOR ACCESS
SEL CASE WHEN DBname = 'DBC' THEN 1
WHEN DBname IN ('SYSDBA','SYSAdmin','DBadmin','Sec_admin') THEN 2
WHEN DBname IN ('console','Crashdumps','Default','EXTUSER','SPOOL_RESERVE'
,'PUBLIC','SystemFe','Sys_Calendar','TDPUSER','tdwm','viewpoint') THEN 8
WHEN Max_Perm > 900000 THEN 4 /** over 1TB **/
WHEN Max_Perm > 900 THEN 5 /** over 1GB **/
ELSE 6 END AS "S"
,TRIM(DBname) "Database/User" ,UorD "tp" ,TRIM(Owner) "Owner"
,Max_Perm "Perm MB:Max" ,Avail_Perm "Avail" ,Max_Perm - Avail_Perm "Used"
,ZEROIFNULL(B1.CtTbl) "Table" ,ZEROIFNULL(B1.CtVw) "View"
,ZEROIFNULL(B1.CtMac) "Macro" ,ZEROIFNULL(B1.CtSP) "SP"
,ZEROIFNULL(B1.CtJI) "JI" ,ZEROIFNULL(B1.CtJ) "Jrnl"
,ZEROIFNULL(B1.CtF) "func" ,ZEROIFNULL(B1.CtOth) "Oth"
,Spool_Max "S-Max" ,Spool_peak "S-peak"
,Temp_max "T-max" ,Temp_peak "T-peak"
FROM (SEL trim(DBs.DATABASENAME) (CHAR(32)) as DBname ,DBs.DatabaseID
,trim(DBs.OWNERNAME) (CHAR(32)) as Owner ,DBs.RowType as UorD
,SUM(MAXPERM/1E6) AS Max_Perm
,(SUM(MAXPERM)-COUNT(*)*MAX(CURRENTPERM))/1E6 AS Avail_Perm
,SUM(MAXSPOOL/1E6) AS Spool_Max ,SUM(PEAKSPOOL/1E6) AS Spool_peak
,SUM(MAXTemp/1E6) AS Temp_max ,SUM(PEAKTemp/1E6) AS Temp_peak
FROM DBC.DISKSPACE Dsp ,DBC.DBase DBs
WHERE Dsp.DATABASENAME=DBs.DATABASENAME
GROUP BY 1,2,3,4
) A1
LEFT OUTER JOIN
(SEL DatabaseID
,SUM(CASE WHEN tablekind in ('O','Q','T') THEN 1 ELSE 0 END) CtTbl
,SUM(CASE WHEN tablekind in ('V') THEN 1 ELSE 0 END) CtVw
,SUM(CASE WHEN tablekind in ('M') THEN 1 ELSE 0 END) CtMac
,SUM(CASE WHEN tablekind in ('E','P') THEN 1 ELSE 0 END) CtSP
,SUM(CASE WHEN tablekind in ('I','N') THEN 1 ELSE 0 END) CtJI
,SUM(CASE WHEN tablekind in ('A','F','R','S') THEN 1 ELSE 0 END) CtF
,SUM(CASE WHEN tablekind in ('J') THEN 1 ELSE 0 END) CtJ
,COUNT(*) -CtTbl -CtVw -Ctmac -CtSP -CtJI -CtF -CtJ AS CtOth
/********************************************************************************
A = Aggregate function
B = Combined aggregate and ordered analytical function
D = JAR
E = External Stored Procedure
F = Standard function
G = Trigger
H = Instance or Constructor Method
I = Join Index
J = Journal
M = Macro
N = Hash Index
O = Table with no primary index and no partitioning
P = Stored Procedure
Q = Queue Table
R = Table function
S = Ordered analytical function
T = Table with a primary index, partitioning, or both
U = User-defined data type
V = View
X = Authorization
Y = GLOP Set
/*******************************************************************************/
FROM DBC.tvm GROUP BY 1
) B1
ON A1.DatabaseID = B1.DatabaseID
UNION ALL
SEL 0 AS Seq ,'' as DBname ,'' as UorD ,' Totals' as Owner
,SUM(MxPm) AS MxPerm ,SUM(AvPm) AS AvPerm ,MxPerm - AvPerm AS UsePerm
,ZEROIFNULL(B2.CtTbl) "Tbl Cnt" ,ZEROIFNULL(B2.CtVw) "Vw Cnt"
,ZEROIFNULL(B2.CtMac) "Mac Cnt" ,ZEROIFNULL(B2.CtSP) "SP Cnt"
,ZEROIFNULL(B2.CtJI) "JI Cnt" ,ZEROIFNULL(B2.CtF) "F Cnt"
,ZEROIFNULL(B2.CtJ) "J Cnt" ,ZEROIFNULL(B2.CtOth) "Oth Cnt"
,MAX(SpMx) as SPMx ,MAX(SpPk) as SpPk ,MAX(TpMx) as TpMx ,MAX(TpPk) as TpPk
FROM (SEL DBs.DATABASENAME ,SUM(MAXPERM/1E6) AS MxPm
,(SUM(MAXPERM)-SUM(CURRENTPERM))/1E6 AS AvPm
,SUM(MAXSPOOL/1E6) as SPMx ,SUM(PEAKSPOOL/1E6) as SpPk
,SUM(MAXTemp/1E6) as TpMx ,SUM(PEAKTemp/1E6) as TpPk
FROM DBC.DISKSPACE Dsp ,DBC.DBase DBs
WHERE Dsp.DATABASENAME=DBs.DATABASENAME
GROUP BY 1
) A2
CROSS JOIN
(SEL SUM(CASE WHEN tablekind in ('O','Q','T') THEN 1 ELSE 0 END) CtTbl
,SUM(CASE WHEN tablekind in ('V') THEN 1 ELSE 0 END) CtVw
,SUM(CASE WHEN tablekind in ('M') THEN 1 ELSE 0 END) CtMac
,SUM(CASE WHEN tablekind in ('E','P') THEN 1 ELSE 0 END) CtSP
,SUM(CASE WHEN tablekind in ('I','N') THEN 1 ELSE 0 END) CtJI
,SUM(CASE WHEN tablekind in ('A','F','R','S') THEN 1 ELSE 0 END) CtF
,SUM(CASE WHEN tablekind in ('J') THEN 1 ELSE 0 END) CtJ
,COUNT(*) -CtTbl -CtVw -Ctmac -CtSP -CtJI -CtF -CtJ AS CtOth
FROM DBC.tvm
) B2
ORDER BY 1,3,2;

 

Cheers, Frank

Teradata Frank, Certified Master
1 ACCEPTED SOLUTION
2 REPLIES 2
Supporter

Re: Space check

Akhill,

 

For total space in your Teradata system, you shoud not exclude any databases.

Below SQL gives you system total (Perm Space), plus a lot of detail about databases in the system.

Keep details for each system seperately.

If you have PROD and DEV environment on the same system, you may need to summarize for each environment.

 

LOCKING ROW FOR ACCESS
SEL CASE WHEN DBname = 'DBC' THEN 1
WHEN DBname IN ('SYSDBA','SYSAdmin','DBadmin','Sec_admin') THEN 2
WHEN DBname IN ('console','Crashdumps','Default','EXTUSER','SPOOL_RESERVE'
,'PUBLIC','SystemFe','Sys_Calendar','TDPUSER','tdwm','viewpoint') THEN 8
WHEN Max_Perm > 900000 THEN 4 /** over 1TB **/
WHEN Max_Perm > 900 THEN 5 /** over 1GB **/
ELSE 6 END AS "S"
,TRIM(DBname) "Database/User" ,UorD "tp" ,TRIM(Owner) "Owner"
,Max_Perm "Perm MB:Max" ,Avail_Perm "Avail" ,Max_Perm - Avail_Perm "Used"
,ZEROIFNULL(B1.CtTbl) "Table" ,ZEROIFNULL(B1.CtVw) "View"
,ZEROIFNULL(B1.CtMac) "Macro" ,ZEROIFNULL(B1.CtSP) "SP"
,ZEROIFNULL(B1.CtJI) "JI" ,ZEROIFNULL(B1.CtJ) "Jrnl"
,ZEROIFNULL(B1.CtF) "func" ,ZEROIFNULL(B1.CtOth) "Oth"
,Spool_Max "S-Max" ,Spool_peak "S-peak"
,Temp_max "T-max" ,Temp_peak "T-peak"
FROM (SEL trim(DBs.DATABASENAME) (CHAR(32)) as DBname ,DBs.DatabaseID
,trim(DBs.OWNERNAME) (CHAR(32)) as Owner ,DBs.RowType as UorD
,SUM(MAXPERM/1E6) AS Max_Perm
,(SUM(MAXPERM)-COUNT(*)*MAX(CURRENTPERM))/1E6 AS Avail_Perm
,SUM(MAXSPOOL/1E6) AS Spool_Max ,SUM(PEAKSPOOL/1E6) AS Spool_peak
,SUM(MAXTemp/1E6) AS Temp_max ,SUM(PEAKTemp/1E6) AS Temp_peak
FROM DBC.DISKSPACE Dsp ,DBC.DBase DBs
WHERE Dsp.DATABASENAME=DBs.DATABASENAME
GROUP BY 1,2,3,4
) A1
LEFT OUTER JOIN
(SEL DatabaseID
,SUM(CASE WHEN tablekind in ('O','Q','T') THEN 1 ELSE 0 END) CtTbl
,SUM(CASE WHEN tablekind in ('V') THEN 1 ELSE 0 END) CtVw
,SUM(CASE WHEN tablekind in ('M') THEN 1 ELSE 0 END) CtMac
,SUM(CASE WHEN tablekind in ('E','P') THEN 1 ELSE 0 END) CtSP
,SUM(CASE WHEN tablekind in ('I','N') THEN 1 ELSE 0 END) CtJI
,SUM(CASE WHEN tablekind in ('A','F','R','S') THEN 1 ELSE 0 END) CtF
,SUM(CASE WHEN tablekind in ('J') THEN 1 ELSE 0 END) CtJ
,COUNT(*) -CtTbl -CtVw -Ctmac -CtSP -CtJI -CtF -CtJ AS CtOth
/********************************************************************************
A = Aggregate function
B = Combined aggregate and ordered analytical function
D = JAR
E = External Stored Procedure
F = Standard function
G = Trigger
H = Instance or Constructor Method
I = Join Index
J = Journal
M = Macro
N = Hash Index
O = Table with no primary index and no partitioning
P = Stored Procedure
Q = Queue Table
R = Table function
S = Ordered analytical function
T = Table with a primary index, partitioning, or both
U = User-defined data type
V = View
X = Authorization
Y = GLOP Set
/*******************************************************************************/
FROM DBC.tvm GROUP BY 1
) B1
ON A1.DatabaseID = B1.DatabaseID
UNION ALL
SEL 0 AS Seq ,'' as DBname ,'' as UorD ,' Totals' as Owner
,SUM(MxPm) AS MxPerm ,SUM(AvPm) AS AvPerm ,MxPerm - AvPerm AS UsePerm
,ZEROIFNULL(B2.CtTbl) "Tbl Cnt" ,ZEROIFNULL(B2.CtVw) "Vw Cnt"
,ZEROIFNULL(B2.CtMac) "Mac Cnt" ,ZEROIFNULL(B2.CtSP) "SP Cnt"
,ZEROIFNULL(B2.CtJI) "JI Cnt" ,ZEROIFNULL(B2.CtF) "F Cnt"
,ZEROIFNULL(B2.CtJ) "J Cnt" ,ZEROIFNULL(B2.CtOth) "Oth Cnt"
,MAX(SpMx) as SPMx ,MAX(SpPk) as SpPk ,MAX(TpMx) as TpMx ,MAX(TpPk) as TpPk
FROM (SEL DBs.DATABASENAME ,SUM(MAXPERM/1E6) AS MxPm
,(SUM(MAXPERM)-SUM(CURRENTPERM))/1E6 AS AvPm
,SUM(MAXSPOOL/1E6) as SPMx ,SUM(PEAKSPOOL/1E6) as SpPk
,SUM(MAXTemp/1E6) as TpMx ,SUM(PEAKTemp/1E6) as TpPk
FROM DBC.DISKSPACE Dsp ,DBC.DBase DBs
WHERE Dsp.DATABASENAME=DBs.DATABASENAME
GROUP BY 1
) A2
CROSS JOIN
(SEL SUM(CASE WHEN tablekind in ('O','Q','T') THEN 1 ELSE 0 END) CtTbl
,SUM(CASE WHEN tablekind in ('V') THEN 1 ELSE 0 END) CtVw
,SUM(CASE WHEN tablekind in ('M') THEN 1 ELSE 0 END) CtMac
,SUM(CASE WHEN tablekind in ('E','P') THEN 1 ELSE 0 END) CtSP
,SUM(CASE WHEN tablekind in ('I','N') THEN 1 ELSE 0 END) CtJI
,SUM(CASE WHEN tablekind in ('A','F','R','S') THEN 1 ELSE 0 END) CtF
,SUM(CASE WHEN tablekind in ('J') THEN 1 ELSE 0 END) CtJ
,COUNT(*) -CtTbl -CtVw -Ctmac -CtSP -CtJI -CtF -CtJ AS CtOth
FROM DBC.tvm
) B2
ORDER BY 1,3,2;

 

Cheers, Frank

Teradata Frank, Certified Master
Enthusiast

Re: Space check

Thank you so much Frank... This is very useful and helpful.