Teradata Space Management Scripts
1) To find AMP usage and CPU utilization on TD server.
You could query DBC.Tablesize, DBC.ProfileInfo, DBC.AllRoleRights, and
DBC.RoleInfo and others. The query should return on the AMP Usage and CPU
Utilization The following query is dbc.ampusage
Select Account Name, UserName, sum(diskio),sum(cputime)
from dbc.ampusage
Group by 1,2;
2) To find the information at the user & account level. Space Usage. (Total Disk Space On DBC)
The following query is dbc.disksspace :
Select sum (currentperm) c1, sum (maxperm) c2, c2-c1 from dbc.diskspace;
(Or)
Select sum(maxperm) from DBC.DiskSpace; (Total disk space in the entire System (DBC))
SELECT SUM (MaxPerm) FROM DBC.DiskSpaceV;
(Or)
Select sum(MaxPerm) from DBC.DiskSpace where DatabaseName = 'DBC';
3) To find table size in particular DB on TD
Select database name, table name, SUM (current perm) FROM DBC.Tablesize
WHERE database name = <DATABASE name> GROUP BY 1,2;
4) To find DB size on TD
Select database name, SUM (maxperm), SUM (current perm) FROM dbc.diskspace
WHERE database name =<DATABASE name> GROUP BY 1;
5) To get all the same spaces for one or more Databases or Users on TD
Select DATABASENAME, SUM (MAXPERM), SUM (CURRENTPERM),
SUM (MAXPERM)-SUM (CURRENTPERM) ,SUM(MAXSPOOL)
FROM DBC.DISKSPACE
GROUP BY 1 ORDER BY 2;
6) To find Occupied and Empty Permanent Space and Assigned Spool Space. If you
Filter using WHERE Database name = 'xxxx'
Select DATABASENAME, SUM (MAXPERM),SUM(CURRENTPERM),SUM(MAXPERM)-
SUM (CURRENTPERM), SUM (MAXSPOOL
FROM DBC.DISKSPACE GROUP BY 1
ORDER BY 2;
(Or)
Select * from dbc.databases where database name='Your database name';
7) To find total disk space in GB on TD server
Select sum(maxperm)/ 1024/ 1024/ 1024 "MaxPerm in GB", sum(currentperm)/
1024/ 1024/ 1024 "CurrrentPerm in GB", sum(maxspool)/1024/1024/1024
"maxspool in GB", (sum(maxperm)/ 1024/ 1024/ 1024) - (sum(currentperm)/
1024/ 1024/ 1024 ) " SpaceLeft in GB",(sum(currentperm) / (sum(maxperm))
*100) "% Used"
from dbc.diskspace;
Select sum(maxperm)/ 1024/ 1024 "MaxPerm in MB", sum(currentperm)/ 1024/
1024 "CurrrentPerm in MB", sum(maxspool)/1024/1024 "maxspool in MB",
(sum(maxperm)/ 1024/ 1024) - (sum(currentperm)/ 1024/ 1024 ) "
SpaceLeft in MB",(sum(currentperm) / (sum(maxperm)) *100) "% Used"
from dbc.diskspace;
8) To find Percent of disk space free on TD server
SELECT (((SUM (MAXPERM) - (SUM(CURRENTPERM)) - SUM(CURRENTSPOOL)) /
NULLIFZERO (SUM (MAXPERM))) * 100) (TITLE'% FREE', FORMAT'ZZ9.99') FROM
DBC.DISKSPACE;
SELECT DatabaseName CAST (SUM (MaxPerm) AS FORMAT 'zzz,zzz,zz9')
,CAST (SUM (CurrentPerm) AS FORMAT 'zzz,zzz,zz9'),CAST (((SUM (CurrentPerm))/
NULLIFZERO (SUM(MaxPerm)) * 100) AS FORMAT 'zz9.99%') AS "% Used"
FROM DBC.DiskSpace GROUP BY 1 ORDER BY 4 DESC ;
9) To find Percent of disk available for spool on TD server
SELECT (((SUM (MAXPERM) - SUM (CURRENTPERM)) / NULLIFZERO
(SUM (MAXPERM))) * 100) (TITLE'% AVAIL FOR SPOOL', FORMAT'ZZ9.99')
FROM DBC.DiskSpaceV;
10) To find Disk space for a given database on TD server
SELECT SUM (MAXPERM) FROM DBC.DiskSpaceV WHERE DATABASENAME='XXXX';
11) To find Disk space for a given table on TD server
SELECT SUM (CURRENTPERM) FROM DBC.TablesizeV WHERE DATABASENAME='XXX' AND TABLENAME = 'XXXX';
12) To check database space you could run the following query
Select database name, sum (current perm), sum (maxperm) from dbc.diskspace group By 1;
13) To find out which child database have a free perm
Select database name, sum (current perm), sum (maxperm), sum (maxperm) -
Sum (current perm) from dbc.diskspace Group by 1;
14) To find MaxPerm, Current perm and Peak perm on dbc disks pace
Select sum (current Perm), sum (peak Perm), sum (MaxPerm) from dbc.DiskSpace;
15) Disk Space currently in use on TD Server
SELECT SUM (CurrentPerm), SUM (MaxPerm),((SUM(currentperm) /
NULLIFZERO (SUM (maxperm)) * 100))(TITLE '%MaxPerm', FORMAT 'zz9.99')
FROM DBC.DiskSpaceV;
16) Percent of space used by each database in the system on TD Server
SELECT Databasename (format ‘X (12)'), SUM (maxperm), SUM(currentperm),
((SUM (currentperm))/NULLIFZERO (SUM (maxperm)) * 100)(FORMAT 'zz9.99%',TITLE 'Percent // Used')
FROM DBC.DiskSpaceV GROUP BY 1 ORDER BY 4 DESC WITH SUM (currentperm), SUM (maxperm);
17) Users who are running out of PERM space
SELECT Databasename (format 'X(12)'),SUM(maxperm),SUM(currentperm),
((SUM (currentperm))/NULLIFZERO (SUM (maxperm)) * 100)(format 'zz9.99%',
TITLE 'Percent // Used')
FROM DBC.DiskSpaceV GROUP BY 1 HAVING
(SUM (currentPerm) / NULLIFZERO (SUM (maxperm))) > 0.9 ORDER BY 4 DESC;
18) Users using lot of spool on TD Server
SELECT databasename,SUM(peakspool) FROM DBC.DiskSpaceV
GROUP BY 1 HAVING SUM (peakspool) > 5000000000 ORDER BY 2 DESC;
You can change the value 500000000 to whatever value is appropriate for your site.
Some sites with more space may have higher tolerance for higher spool usage and
Spool limits.
19) To Shows macro space used
CREATE MACRO superspace as (SELECT databasename (char (30)), SUM(maxperm), SUM (currentperm)
FROM DBC.DiskSpaceV GROUP BY databasename ORDER BY Databasename WITH SUM (maxperm), sum (currentperm););
20) To shows TableSizeV View
The DBC.TableSizeV view provides AMP information about disk space usage at
the table level.Optionally use viewnameVX for information on only those tables that the
requesting user owns or has SELECT privileges on.
SELECT tablename (TITLE 'Table'),currentperm (TITLE 'CurPerm'),vproc (TITLE 'Amp')
FROM DBC.tablesizeV WHERE databasename='xxx' AND tablename = 'xxxx' ORDER BY 2 DESC;
21) To finding Skewed Tables on TD Server
SELECT vproc AS AMP, TableName (FORMAT ‘X (20)'), CurrentPerm FROM
DBC.TableSizeV WHERE DatabaseName = 'USER' ORDER BY Table Name, AMP ;
22) Warning about AllSpaceV View
SELECT DatabaseName,Sum(CurrentPerm) FROM DBC.AllSpaceV GROUP BY 1
Having sum (currentperm) > 0 ORDER BY 2 desc;
SELECT DatabaseName,Sum(CurrentPerm)
FROM DBC.AllSpaceV
where databasename='DBC' GROUP BY 1 having sum(currentperm) > 0 ORDER BY 2 desc;
SELECT DatabaseName,Sum(CurrentPerm)
FROM DBC.DiskSpaceV GROUP BY 1
Having sum (currentperm) > 0 ORDER BY 2 desc;
23) To find TableSizeV to report the PERM of tables in a particular database and DiskSpaceV to report the PERM of the database
TableSizeV reports MAX (CurrentPerm) and SUM (CurrentPerm) on all tables in the Specified database
SELECT MAX (CurrentPerm), SUM(CurrentPerm) FROM DBC.TableSizeV
WHERE DatabaseName = ‘xxxx’;
DiskSpaceV reports MAX (CurrentPerm) and SUM (CurrentPerm) at the database level:
SELECT MAX (CurrentPerm), SUM(CurrentPerm) FROM DBC.DiskSpaceV
WHERE DatabaseName = ‘xxxxx’ ;
24) DBC.LogOnOffV View
The following query returns any failed logon attempts during the last seven days
SELECT LogDate,LogTime,UserName (FORMAT 'X(10)'),Event FROM DBC.LogOnOffV
WHERE Event NOT LIKE ('%Logo%') AND LogDate GT DATE – 7 ORDER BY LogDate,
LogTime;
25) To find the Max PermSpace on TD Server by using DataBase id
Select sum (maxpermspace)
From DBC.DataBaseSpace
where Databaseid='00007408'XB;
( '00007408'XB is Database id for the database)
26) Select sum ( currentperm) from DBC.TableSize where databasename='xxx' ;
Select max (currentperm)
from DBC.TableSize group by vproc where databasename='xxx' ;
(In above query Max Of current permspace of each amp is less than the
Maxpermspace/80(80 is number of amps)
Amp is full only when it reaches =26528972=2122317760.00(Max Perm Space)/80
Amp number which is occupied highest space is 50 I.e. 10132992.00 Bytes
27) To find Max PermSpace and Current PermSpace on TD Server
Select max (currentperm), max (maxperm)
from dbc.diskspace where databasename='xxx';
28) To find Current PermSpace,Effective Space of TableSize and DataBaseName on TD
Server by using HASHMAP
SELECT SUM (CURRENTPERM) ACTUALSPACE, MAX (CURRENTPERM)*(HASHAMP()+1) EFFECTIVESPACE
FROM DBC.TABLESIZE
WHERE DATABASENAME = 'mydb' AND TABLENAME ='mytable';
SELECT SUM(CURRENTPERM)/(1024*1024) ACTUALSPACE, (MAX(CURRENTPERM)*(HASHAMP()+1))/(1024*1024) EFFECTIVESPACE
FROM DBC.TABLESIZE
WHERE DATABASENAME = 'DB_NAME' AND TABLENAME = 'TABLE_NAME';
29) To use the below query and you can modify according to your needs
Select databasename,cast(sum(maxperm)/1024/1024/1024 as decimal(7,2))
max_perm,cast(sum(currentperm)/1024/1024/1024 as decimal(7,2))
current_perm,cast(sum(maxspool)/1024/1024/1024 as decimal(7,2))
max_spool,cast(sum(currentspool)/1024/1024/1024 as decimal(7,2)) current_spool
from dbc.diskspace where databasename in (select databasename
from dbc.databases where dbkind = 'D' and databasename in ('STG_DEV', 'STG_TST_TMP',
'WRK_STG_DEV') ) group by databasename order by max_perm desc;
29) Find MAX(CurrentPerm) and SUM(CurrentPerm) in DBC
select max(CurrentPerm),sum(CurrentPerm) from DBC.DiskSpace
30) DBC.DiskSpace
It provides information about disk space usage (including spool) for any database or
Account.
SELECT DatabaseName CAST (SUM (MaxPerm) AS FORMAT 'zzz, zzz, zz9’)
, CAST (SUM (CurrentPerm) AS FORMAT 'zzz, zzz,zz9’),CAST (((SUM (CurrentPerm))/
NULLIFZERO (SUM (MaxPerm)) * 100) AS FORMAT ’zz9.99 %') AS "% Used"
FROM DBC.DiskSpace GROUP BY 1 ORDER BY 4 DESC;
31) DBC.TableSize
It provides information about disk space usage (excluding spool) for any database, table or account.
SELECT Vproc ,CAST (TableName AS FORMAT 'X(20)') ,CurrentPerm,PeakPerm
FROM DBC.TableSize WHERE DatabaseName = USER ORDER BY TableName, Vproc ;
32) DBC.AllSpace
It provides information about disk space usage (including spool) for any database,
table,or account.
SELECT Vproc,CAST (TableName AS FORMAT 'X(20)') ,MaxPerm,CurrentPerm
FROM DBC.AllSpace WHERE DatabaseName = USER
ORDER BY TableName, Vproc ;
33) You can play with SQL to SUM up the database to get total count of database or you
Can simply use the
view diskspace; (to get the totals across the whole database)
34) Data Skew Check
1) First check to see if the primary index is ok for tables system-wide:
SEL (MAX(CurrentPerm) - MIN(CurrentPerm)) * 100/(NULLIF(MIN(currentperm),0))
(NAMED variance)(FORMAT 'zzzzz9.99%'),MAX(CurrentPerm)(TITLE 'Max')
(FORMAT 'zzz,zzz,zzz,999'),MIN(currentperm)(TITLE 'Min')(FORMAT 'zzz,zzz,zzz,999'),TRIM(DatabaseName)||'.'||TableName (NAMED Tables)
FROM DBC.tablesizeGROUP BY DatabaseName, TableNameHAVING SUM(CurrentPerm) > 1000000 AND variance > 1000
WHERE DatabaseName NOT IN ('CrashDumps','DBC')
ORDER BY Tables;
2) If this is not the case, try to identify the job that might be responsible for the concentration of IO and cpu by checking spool usage by vproc:
//* DBC.DISKSPACE - Get AMP Number with skew for data & Peak Spool - Include % distribution efficiency */
.export file diskskew.out
.set defaults
.set separator 1
.set width 110
SELECT A.databasename (Format 'x(20)') (Title 'DiskSpace//DB Name')
, A.accountName (Format 'x(20)') (Title 'Acct Name'), A.vproc (Format 'zzzz9') (Title 'AMP //Number'), A.currentperm / (1024**2) (Format 'zz,zz9.999') (Title '//CurrPerm')
, DT.avgCurperm / (1024**2) (Format 'zz,zz9.999') (Title 'Avg//CurrPerm')
, A.currentperm / DT.avgCurperm (Format 'zzz9.99') (Title 'CurrPerm//Skew')
, A.peakspool / (1024**2) (Format 'zz,zz9.999') (Title '//PeakSpool'), DT.avgPeakspool / (1024**2) (Format 'zz,zz9.999') (Title 'Avg//PeakSpool'), A.peakspool / DT.avgPeakspool (Format 'zzz9.99') (Title 'PeakSpool//Skew')FROM DBC.Diskspace A,(SELECT databasename (Format 'x(12)') (Title 'DiskSpace//DB Name') , accountName (Format 'x(12)') (Title 'Acct Name'), AVG(currentperm) (Format 'z,zzz,zz9') (Title 'CurrPerm//MBytes')
, AVG(peakspool) (Format 'z,zzz,zz9') (Title 'PeakSpool//MBytes') FROM DBC.Diskspace
GROUP BY 1, 2 ) DT (databasename, accountName, avgCurperm, avgPeakspool)WHERE A.databasename = DT.databasename AND A.accountName = DT.accountName
AND (A.currentperm / DT.avgCurperm >= 1.30 OR A.peakspool / DT.avgPeakspool >= 1.30)
ORDER BY 3, 1, 2;
.set defaults
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by Users with more than 100,000 cpu seconds */
.export file ampusage.out
.set defaults
.set width 110
SELECT A.accountName (Format 'x(18)') (Title 'AMPUsage//Acct Name'), A.username (Format 'x(22)') (Title 'User Name'), SUM(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'CPUtime'), SUM(A.DiskIO) (Format 'zzz,zzz,zzz,zz9') (Title 'DiskIO'), AVG(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'AvgCPUtime'), MAX(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'MAXCPUtime')
, MAX(A.CPUTime)/nullifzero(AVG(A.CPUTime) )(Format 'zz9.99') (Title 'CPU//Skew//Effect')
FROM DBC.AMPUsage A GROUP BY 1, 2 HAVING SUM(CPUTime) > 1e5 ORDER BY 3 desc, 1, 2;
.set defaults
.set width 80
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by User with skewing greater than 30% over average*/
.export file ampusageskew.out
.set defaults
.set width 94
SELECT A.accountName (Format 'x(18)') (Title 'AMPusage//Acct Name'), A.username (Format 'x(12)') (Title 'User Name'), A.vproc (Format '99999') (Title 'Vproc'), A.CPUTime (Format 'zz,zzz,zz9') (Title 'CPUtime'), DT.AvgCPUTime (Format 'zz,zzz,zz9') (Title 'AvgCPUtime'), A.CPUTime/NULLIFZERO(DT.AvgCPUTime)(Format 'zz9.99')(Title 'Ratio//to Avg')(Named CpuRatio), A.DiskIO (Format 'zzz,zzz,zzz,zz9') (Title 'AvgDiskIO'), A.DiskIO/NULLIFZERO(DT.avgDiskIO) (Format 'z9.99') (Title 'Ratio//to Avg')FROM DBC.AMPUsage A,(SELECT accountName, username, AVG(CPUTime), SUM(CPUTime)
, AVG(DiskIO), SUM(DiskIO) FROM DBC.AMPUsageGROUP BY 1, 2HAVING SUM(CPUTime) > 1e4) DT (accountName, username, avgCPUtime, sumCPUtime, avgDiskIO, sumDiskIO)
WHERE A.accountname = DT.accountName AND A.username = DT.username
AND CpuRatio > 1.30 ORDER BY 5, 1, 2, 3;
3) Once you have identified the application, check the tables involved to see if secondary indexes are skewed or if the access to the tables is causing a concentration on the clique...
/* The following query will provide the distribution by amp for a given index or column. */
select hashamp (hashbucket(hashrow(index or column))),count(*) from database.table
group by 1 order by 2 desc;
/* The following query will provide the number of collisions for row hash. */
select hashrow(index or column), count(*) from database.table group by 1 order by 1 having count(*) > 10;
35) To find out I/O and CPU Usage from this data Dictionary Table DBC.AMPUSAGE
SELECT ACCOUNTNAME, USERNAME,SUM(CPUTIME) AS CPU,SUM (DISKIO)
AS DISKIO FROM DBC.AMPUSAGE GROUP BY 1,2 ORDER BY 3 DESC;
36) Find the total space in your system
Select sum (maxperm) from dbc.diskspace;
37) How to find CYLINDERS
Go to - >Teradata Manager ->Select MENU -> Investigate then “Space Usage”, then
“Cylinders by vproc “. it tells you the number of available cylinders. Now, divide the
Total space by the number of cylinders to get the size of a cylinder.
38) SELECT databasename ,SUM ((((MaxPerm) / 1024) /1024) / 1024) AS
Maximum_Space_GB ,SUM ((((CurrentPerm) / 1024) /1024) / 1024) AS
Current_Space_Used_GB,((100-Percent_Used)*Maximum_Space_GB)/100 AS
Free_Space_GB,(Current_Space_Used_GB / NULLIFZERO (Maximum_Space_GB) *
100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS Percent_Used,
(Free_Space_GB / NULLIFZERO (Maximum_Space_GB)) FROM DBC.DISKSPACE
Group By 1 ORDER BY 5 DESC;
Here is SQL to show you a user's max spool (amount allocated) and peak spool (highest amount used):
SELECT DATABASENAME (Title 'User'),sum(MaxSpool) (format '---,---,---,---,--9')(char(19))(title 'max Spool'),sum(PeakSpool) (format '---,---,---,---,--9')(char(19))(title 'Peak Spool'),sum(Maxtemp) (format '---,---,---,---,--9')(char(19))(title 'max temp'),sum(PeakTemp) (format '---,---,---,---,--9')(char(19))(title 'Peak Temp') FROM DBC.DISKSPACE
WHERE DatabaseName = 'DBNAME' ORDER BY 1 GROUP BY 1;
Here is SQL to give you information including spool and temporary space allocations for a user:
SELECT A.DatabaseName (CHAR(20)), A.OwnerName (CHAR(20)), A.AccountName (CHAR(20)), B.DefaultDatabase (CHAR(20)), (A.SpoolSpace / 1000000000) (NAMED Spool_In_gig), (A.TempSpace / 1000000000) (NAMED Temp_In_gig)
FROM DBC.DATABASES A, DBC.DBASE B WHERE A.DatabaseName = B.DatabaseNamei
AND A.DatabaseName = 'Joe_User' ORDER BY 1;
39) Deleting DBQL tables should not cause any problem.
Two steps for a cleanup job could be:
(1) Insert selects the rows to a private database if you want to keep some history. Consider using (empty) staging tables for optimized insert-select.
(2) Delete DBC.DBQL* tables (except for the RuleTbl and RuleCountTbl which anyways you can't). If you are deleting based on date then use collectiontimestamp on all but DBQLogTbl. We use Start Time to delete from DBQLogTbl as the collectiontimestamp is way off in this table (may be a bug)
43) DBC.DiskSpace (Useful)
It provides information about disk space usage (including spool) for any database or account.
SELECT DatabaseName,CAST (SUM (MaxPerm) AS FORMAT 'zzz,zzz,zz9'),CAST (SUM (CurrentPerm) AS FORMAT 'zzz,zzz,zz9'),CAST (((SUM (CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100) AS FORMAT 'zz9.99%') AS "% Used FROM DBC.DiskSpace GROUP BY 1
ORDER BY 4 DESC;
44) DBC.TableSize
It provides information about disk space usage (excluding spool) for any database, table or account
SELECT Vproc,CAST (TableName AS FORMAT 'X(20)') ,CurrentPerm,PeakPerm FROM DBC.TableSize WHERE DatabaseName = USER ORDER BY TableName, Vproc ;
45) DBC.AllSpace
It provides information about disk space usage (including spool) for any database, table, or account.
SELECT Vproc,CAST (TableName AS FORMAT 'X(20)') ,MaxPerm,CurrentPerm
FROM DBC.AllSpace WHERE DatabaseName = USER ORDER BY TableName, Vproc ;
You could query DBC.Tablesize, DBC.ProfileInfo, DBC.AllRoleRights, and
DBC.RoleInfo and others. The query should return on the AMP Usage and CPU
Utilization The following query is dbc.ampusage
Select Account Name, UserName, sum(diskio),sum(cputime)
from dbc.ampusage
Group by 1,2;
2) To find the information at the user & account level. Space Usage. (Total Disk Space On DBC)
The following query is dbc.disksspace :
Select sum (currentperm) c1, sum (maxperm) c2, c2-c1 from dbc.diskspace;
(Or)
Select sum(maxperm) from DBC.DiskSpace; (Total disk space in the entire System (DBC))
SELECT SUM (MaxPerm) FROM DBC.DiskSpaceV;
(Or)
Select sum(MaxPerm) from DBC.DiskSpace where DatabaseName = 'DBC';
3) To find table size in particular DB on TD
Select database name, table name, SUM (current perm) FROM DBC.Tablesize
WHERE database name = <DATABASE name> GROUP BY 1,2;
4) To find DB size on TD
Select database name, SUM (maxperm), SUM (current perm) FROM dbc.diskspace
WHERE database name =<DATABASE name> GROUP BY 1;
5) To get all the same spaces for one or more Databases or Users on TD
Select DATABASENAME, SUM (MAXPERM), SUM (CURRENTPERM),
SUM (MAXPERM)-SUM (CURRENTPERM) ,SUM(MAXSPOOL)
FROM DBC.DISKSPACE
GROUP BY 1 ORDER BY 2;
6) To find Occupied and Empty Permanent Space and Assigned Spool Space. If you
Filter using WHERE Database name = 'xxxx'
Select DATABASENAME, SUM (MAXPERM),SUM(CURRENTPERM),SUM(MAXPERM)-
SUM (CURRENTPERM), SUM (MAXSPOOL
FROM DBC.DISKSPACE GROUP BY 1
ORDER BY 2;
(Or)
Select * from dbc.databases where database name='Your database name';
7) To find total disk space in GB on TD server
Select sum(maxperm)/ 1024/ 1024/ 1024 "MaxPerm in GB", sum(currentperm)/
1024/ 1024/ 1024 "CurrrentPerm in GB", sum(maxspool)/1024/1024/1024
"maxspool in GB", (sum(maxperm)/ 1024/ 1024/ 1024) - (sum(currentperm)/
1024/ 1024/ 1024 ) " SpaceLeft in GB",(sum(currentperm) / (sum(maxperm))
*100) "% Used"
from dbc.diskspace;
Select sum(maxperm)/ 1024/ 1024 "MaxPerm in MB", sum(currentperm)/ 1024/
1024 "CurrrentPerm in MB", sum(maxspool)/1024/1024 "maxspool in MB",
(sum(maxperm)/ 1024/ 1024) - (sum(currentperm)/ 1024/ 1024 ) "
SpaceLeft in MB",(sum(currentperm) / (sum(maxperm)) *100) "% Used"
from dbc.diskspace;
8) To find Percent of disk space free on TD server
SELECT (((SUM (MAXPERM) - (SUM(CURRENTPERM)) - SUM(CURRENTSPOOL)) /
NULLIFZERO (SUM (MAXPERM))) * 100) (TITLE'% FREE', FORMAT'ZZ9.99') FROM
DBC.DISKSPACE;
SELECT DatabaseName CAST (SUM (MaxPerm) AS FORMAT 'zzz,zzz,zz9')
,CAST (SUM (CurrentPerm) AS FORMAT 'zzz,zzz,zz9'),CAST (((SUM (CurrentPerm))/
NULLIFZERO (SUM(MaxPerm)) * 100) AS FORMAT 'zz9.99%') AS "% Used"
FROM DBC.DiskSpace GROUP BY 1 ORDER BY 4 DESC ;
9) To find Percent of disk available for spool on TD server
SELECT (((SUM (MAXPERM) - SUM (CURRENTPERM)) / NULLIFZERO
(SUM (MAXPERM))) * 100) (TITLE'% AVAIL FOR SPOOL', FORMAT'ZZ9.99')
FROM DBC.DiskSpaceV;
10) To find Disk space for a given database on TD server
SELECT SUM (MAXPERM) FROM DBC.DiskSpaceV WHERE DATABASENAME='XXXX';
11) To find Disk space for a given table on TD server
SELECT SUM (CURRENTPERM) FROM DBC.TablesizeV WHERE DATABASENAME='XXX' AND TABLENAME = 'XXXX';
12) To check database space you could run the following query
Select database name, sum (current perm), sum (maxperm) from dbc.diskspace group By 1;
13) To find out which child database have a free perm
Select database name, sum (current perm), sum (maxperm), sum (maxperm) -
Sum (current perm) from dbc.diskspace Group by 1;
14) To find MaxPerm, Current perm and Peak perm on dbc disks pace
Select sum (current Perm), sum (peak Perm), sum (MaxPerm) from dbc.DiskSpace;
15) Disk Space currently in use on TD Server
SELECT SUM (CurrentPerm), SUM (MaxPerm),((SUM(currentperm) /
NULLIFZERO (SUM (maxperm)) * 100))(TITLE '%MaxPerm', FORMAT 'zz9.99')
FROM DBC.DiskSpaceV;
16) Percent of space used by each database in the system on TD Server
SELECT Databasename (format ‘X (12)'), SUM (maxperm), SUM(currentperm),
((SUM (currentperm))/NULLIFZERO (SUM (maxperm)) * 100)(FORMAT 'zz9.99%',TITLE 'Percent // Used')
FROM DBC.DiskSpaceV GROUP BY 1 ORDER BY 4 DESC WITH SUM (currentperm), SUM (maxperm);
17) Users who are running out of PERM space
SELECT Databasename (format 'X(12)'),SUM(maxperm),SUM(currentperm),
((SUM (currentperm))/NULLIFZERO (SUM (maxperm)) * 100)(format 'zz9.99%',
TITLE 'Percent // Used')
FROM DBC.DiskSpaceV GROUP BY 1 HAVING
(SUM (currentPerm) / NULLIFZERO (SUM (maxperm))) > 0.9 ORDER BY 4 DESC;
18) Users using lot of spool on TD Server
SELECT databasename,SUM(peakspool) FROM DBC.DiskSpaceV
GROUP BY 1 HAVING SUM (peakspool) > 5000000000 ORDER BY 2 DESC;
You can change the value 500000000 to whatever value is appropriate for your site.
Some sites with more space may have higher tolerance for higher spool usage and
Spool limits.
19) To Shows macro space used
CREATE MACRO superspace as (SELECT databasename (char (30)), SUM(maxperm), SUM (currentperm)
FROM DBC.DiskSpaceV GROUP BY databasename ORDER BY Databasename WITH SUM (maxperm), sum (currentperm););
20) To shows TableSizeV View
The DBC.TableSizeV view provides AMP information about disk space usage at
the table level.Optionally use viewnameVX for information on only those tables that the
requesting user owns or has SELECT privileges on.
SELECT tablename (TITLE 'Table'),currentperm (TITLE 'CurPerm'),vproc (TITLE 'Amp')
FROM DBC.tablesizeV WHERE databasename='xxx' AND tablename = 'xxxx' ORDER BY 2 DESC;
21) To finding Skewed Tables on TD Server
SELECT vproc AS AMP, TableName (FORMAT ‘X (20)'), CurrentPerm FROM
DBC.TableSizeV WHERE DatabaseName = 'USER' ORDER BY Table Name, AMP ;
22) Warning about AllSpaceV View
SELECT DatabaseName,Sum(CurrentPerm) FROM DBC.AllSpaceV GROUP BY 1
Having sum (currentperm) > 0 ORDER BY 2 desc;
SELECT DatabaseName,Sum(CurrentPerm)
FROM DBC.AllSpaceV
where databasename='DBC' GROUP BY 1 having sum(currentperm) > 0 ORDER BY 2 desc;
SELECT DatabaseName,Sum(CurrentPerm)
FROM DBC.DiskSpaceV GROUP BY 1
Having sum (currentperm) > 0 ORDER BY 2 desc;
23) To find TableSizeV to report the PERM of tables in a particular database and DiskSpaceV to report the PERM of the database
TableSizeV reports MAX (CurrentPerm) and SUM (CurrentPerm) on all tables in the Specified database
SELECT MAX (CurrentPerm), SUM(CurrentPerm) FROM DBC.TableSizeV
WHERE DatabaseName = ‘xxxx’;
DiskSpaceV reports MAX (CurrentPerm) and SUM (CurrentPerm) at the database level:
SELECT MAX (CurrentPerm), SUM(CurrentPerm) FROM DBC.DiskSpaceV
WHERE DatabaseName = ‘xxxxx’ ;
24) DBC.LogOnOffV View
The following query returns any failed logon attempts during the last seven days
SELECT LogDate,LogTime,UserName (FORMAT 'X(10)'),Event FROM DBC.LogOnOffV
WHERE Event NOT LIKE ('%Logo%') AND LogDate GT DATE – 7 ORDER BY LogDate,
LogTime;
25) To find the Max PermSpace on TD Server by using DataBase id
Select sum (maxpermspace)
From DBC.DataBaseSpace
where Databaseid='00007408'XB;
( '00007408'XB is Database id for the database)
26) Select sum ( currentperm) from DBC.TableSize where databasename='xxx' ;
Select max (currentperm)
from DBC.TableSize group by vproc where databasename='xxx' ;
(In above query Max Of current permspace of each amp is less than the
Maxpermspace/80(80 is number of amps)
Amp is full only when it reaches =26528972=2122317760.00(Max Perm Space)/80
Amp number which is occupied highest space is 50 I.e. 10132992.00 Bytes
27) To find Max PermSpace and Current PermSpace on TD Server
Select max (currentperm), max (maxperm)
from dbc.diskspace where databasename='xxx';
28) To find Current PermSpace,Effective Space of TableSize and DataBaseName on TD
Server by using HASHMAP
SELECT SUM (CURRENTPERM) ACTUALSPACE, MAX (CURRENTPERM)*(HASHAMP()+1) EFFECTIVESPACE
FROM DBC.TABLESIZE
WHERE DATABASENAME = 'mydb' AND TABLENAME ='mytable';
SELECT SUM(CURRENTPERM)/(1024*1024) ACTUALSPACE, (MAX(CURRENTPERM)*(HASHAMP()+1))/(1024*1024) EFFECTIVESPACE
FROM DBC.TABLESIZE
WHERE DATABASENAME = 'DB_NAME' AND TABLENAME = 'TABLE_NAME';
29) To use the below query and you can modify according to your needs
Select databasename,cast(sum(maxperm)/1024/1024/1024 as decimal(7,2))
max_perm,cast(sum(currentperm)/1024/1024/1024 as decimal(7,2))
current_perm,cast(sum(maxspool)/1024/1024/1024 as decimal(7,2))
max_spool,cast(sum(currentspool)/1024/1024/1024 as decimal(7,2)) current_spool
from dbc.diskspace where databasename in (select databasename
from dbc.databases where dbkind = 'D' and databasename in ('STG_DEV', 'STG_TST_TMP',
'WRK_STG_DEV') ) group by databasename order by max_perm desc;
29) Find MAX(CurrentPerm) and SUM(CurrentPerm) in DBC
select max(CurrentPerm),sum(CurrentPerm) from DBC.DiskSpace
30) DBC.DiskSpace
It provides information about disk space usage (including spool) for any database or
Account.
SELECT DatabaseName CAST (SUM (MaxPerm) AS FORMAT 'zzz, zzz, zz9’)
, CAST (SUM (CurrentPerm) AS FORMAT 'zzz, zzz,zz9’),CAST (((SUM (CurrentPerm))/
NULLIFZERO (SUM (MaxPerm)) * 100) AS FORMAT ’zz9.99 %') AS "% Used"
FROM DBC.DiskSpace GROUP BY 1 ORDER BY 4 DESC;
31) DBC.TableSize
It provides information about disk space usage (excluding spool) for any database, table or account.
SELECT Vproc ,CAST (TableName AS FORMAT 'X(20)') ,CurrentPerm,PeakPerm
FROM DBC.TableSize WHERE DatabaseName = USER ORDER BY TableName, Vproc ;
32) DBC.AllSpace
It provides information about disk space usage (including spool) for any database,
table,or account.
SELECT Vproc,CAST (TableName AS FORMAT 'X(20)') ,MaxPerm,CurrentPerm
FROM DBC.AllSpace WHERE DatabaseName = USER
ORDER BY TableName, Vproc ;
33) You can play with SQL to SUM up the database to get total count of database or you
Can simply use the
view diskspace; (to get the totals across the whole database)
34) Data Skew Check
1) First check to see if the primary index is ok for tables system-wide:
SEL (MAX(CurrentPerm) - MIN(CurrentPerm)) * 100/(NULLIF(MIN(currentperm),0))
(NAMED variance)(FORMAT 'zzzzz9.99%'),MAX(CurrentPerm)(TITLE 'Max')
(FORMAT 'zzz,zzz,zzz,999'),MIN(currentperm)(TITLE 'Min')(FORMAT 'zzz,zzz,zzz,999'),TRIM(DatabaseName)||'.'||TableName (NAMED Tables)
FROM DBC.tablesizeGROUP BY DatabaseName, TableNameHAVING SUM(CurrentPerm) > 1000000 AND variance > 1000
WHERE DatabaseName NOT IN ('CrashDumps','DBC')
ORDER BY Tables;
2) If this is not the case, try to identify the job that might be responsible for the concentration of IO and cpu by checking spool usage by vproc:
//* DBC.DISKSPACE - Get AMP Number with skew for data & Peak Spool - Include % distribution efficiency */
.export file diskskew.out
.set defaults
.set separator 1
.set width 110
SELECT A.databasename (Format 'x(20)') (Title 'DiskSpace//DB Name')
, A.accountName (Format 'x(20)') (Title 'Acct Name'), A.vproc (Format 'zzzz9') (Title 'AMP //Number'), A.currentperm / (1024**2) (Format 'zz,zz9.999') (Title '//CurrPerm')
, DT.avgCurperm / (1024**2) (Format 'zz,zz9.999') (Title 'Avg//CurrPerm')
, A.currentperm / DT.avgCurperm (Format 'zzz9.99') (Title 'CurrPerm//Skew')
, A.peakspool / (1024**2) (Format 'zz,zz9.999') (Title '//PeakSpool'), DT.avgPeakspool / (1024**2) (Format 'zz,zz9.999') (Title 'Avg//PeakSpool'), A.peakspool / DT.avgPeakspool (Format 'zzz9.99') (Title 'PeakSpool//Skew')FROM DBC.Diskspace A,(SELECT databasename (Format 'x(12)') (Title 'DiskSpace//DB Name') , accountName (Format 'x(12)') (Title 'Acct Name'), AVG(currentperm) (Format 'z,zzz,zz9') (Title 'CurrPerm//MBytes')
, AVG(peakspool) (Format 'z,zzz,zz9') (Title 'PeakSpool//MBytes') FROM DBC.Diskspace
GROUP BY 1, 2 ) DT (databasename, accountName, avgCurperm, avgPeakspool)WHERE A.databasename = DT.databasename AND A.accountName = DT.accountName
AND (A.currentperm / DT.avgCurperm >= 1.30 OR A.peakspool / DT.avgPeakspool >= 1.30)
ORDER BY 3, 1, 2;
.set defaults
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by Users with more than 100,000 cpu seconds */
.export file ampusage.out
.set defaults
.set width 110
SELECT A.accountName (Format 'x(18)') (Title 'AMPUsage//Acct Name'), A.username (Format 'x(22)') (Title 'User Name'), SUM(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'CPUtime'), SUM(A.DiskIO) (Format 'zzz,zzz,zzz,zz9') (Title 'DiskIO'), AVG(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'AvgCPUtime'), MAX(A.CPUTime) (Format 'zzz,zzz,zz9') (Title 'MAXCPUtime')
, MAX(A.CPUTime)/nullifzero(AVG(A.CPUTime) )(Format 'zz9.99') (Title 'CPU//Skew//Effect')
FROM DBC.AMPUsage A GROUP BY 1, 2 HAVING SUM(CPUTime) > 1e5 ORDER BY 3 desc, 1, 2;
.set defaults
.set width 80
/* DBC.AMPUSAGE - Get CPU Usage & Logical Disk I/Os by User with skewing greater than 30% over average*/
.export file ampusageskew.out
.set defaults
.set width 94
SELECT A.accountName (Format 'x(18)') (Title 'AMPusage//Acct Name'), A.username (Format 'x(12)') (Title 'User Name'), A.vproc (Format '99999') (Title 'Vproc'), A.CPUTime (Format 'zz,zzz,zz9') (Title 'CPUtime'), DT.AvgCPUTime (Format 'zz,zzz,zz9') (Title 'AvgCPUtime'), A.CPUTime/NULLIFZERO(DT.AvgCPUTime)(Format 'zz9.99')(Title 'Ratio//to Avg')(Named CpuRatio), A.DiskIO (Format 'zzz,zzz,zzz,zz9') (Title 'AvgDiskIO'), A.DiskIO/NULLIFZERO(DT.avgDiskIO) (Format 'z9.99') (Title 'Ratio//to Avg')FROM DBC.AMPUsage A,(SELECT accountName, username, AVG(CPUTime), SUM(CPUTime)
, AVG(DiskIO), SUM(DiskIO) FROM DBC.AMPUsageGROUP BY 1, 2HAVING SUM(CPUTime) > 1e4) DT (accountName, username, avgCPUtime, sumCPUtime, avgDiskIO, sumDiskIO)
WHERE A.accountname = DT.accountName AND A.username = DT.username
AND CpuRatio > 1.30 ORDER BY 5, 1, 2, 3;
3) Once you have identified the application, check the tables involved to see if secondary indexes are skewed or if the access to the tables is causing a concentration on the clique...
/* The following query will provide the distribution by amp for a given index or column. */
select hashamp (hashbucket(hashrow(index or column))),count(*) from database.table
group by 1 order by 2 desc;
/* The following query will provide the number of collisions for row hash. */
select hashrow(index or column), count(*) from database.table group by 1 order by 1 having count(*) > 10;
35) To find out I/O and CPU Usage from this data Dictionary Table DBC.AMPUSAGE
SELECT ACCOUNTNAME, USERNAME,SUM(CPUTIME) AS CPU,SUM (DISKIO)
AS DISKIO FROM DBC.AMPUSAGE GROUP BY 1,2 ORDER BY 3 DESC;
36) Find the total space in your system
Select sum (maxperm) from dbc.diskspace;
37) How to find CYLINDERS
Go to - >Teradata Manager ->Select MENU -> Investigate then “Space Usage”, then
“Cylinders by vproc “. it tells you the number of available cylinders. Now, divide the
Total space by the number of cylinders to get the size of a cylinder.
38) SELECT databasename ,SUM ((((MaxPerm) / 1024) /1024) / 1024) AS
Maximum_Space_GB ,SUM ((((CurrentPerm) / 1024) /1024) / 1024) AS
Current_Space_Used_GB,((100-Percent_Used)*Maximum_Space_GB)/100 AS
Free_Space_GB,(Current_Space_Used_GB / NULLIFZERO (Maximum_Space_GB) *
100 ) (FORMAT 'zz9.99%' , TITLE 'Percent // Used') AS Percent_Used,
(Free_Space_GB / NULLIFZERO (Maximum_Space_GB)) FROM DBC.DISKSPACE
Group By 1 ORDER BY 5 DESC;
Here is SQL to show you a user's max spool (amount allocated) and peak spool (highest amount used):
SELECT DATABASENAME (Title 'User'),sum(MaxSpool) (format '---,---,---,---,--9')(char(19))(title 'max Spool'),sum(PeakSpool) (format '---,---,---,---,--9')(char(19))(title 'Peak Spool'),sum(Maxtemp) (format '---,---,---,---,--9')(char(19))(title 'max temp'),sum(PeakTemp) (format '---,---,---,---,--9')(char(19))(title 'Peak Temp') FROM DBC.DISKSPACE
WHERE DatabaseName = 'DBNAME' ORDER BY 1 GROUP BY 1;
Here is SQL to give you information including spool and temporary space allocations for a user:
SELECT A.DatabaseName (CHAR(20)), A.OwnerName (CHAR(20)), A.AccountName (CHAR(20)), B.DefaultDatabase (CHAR(20)), (A.SpoolSpace / 1000000000) (NAMED Spool_In_gig), (A.TempSpace / 1000000000) (NAMED Temp_In_gig)
FROM DBC.DATABASES A, DBC.DBASE B WHERE A.DatabaseName = B.DatabaseNamei
AND A.DatabaseName = 'Joe_User' ORDER BY 1;
39) Deleting DBQL tables should not cause any problem.
Two steps for a cleanup job could be:
(1) Insert selects the rows to a private database if you want to keep some history. Consider using (empty) staging tables for optimized insert-select.
(2) Delete DBC.DBQL* tables (except for the RuleTbl and RuleCountTbl which anyways you can't). If you are deleting based on date then use collectiontimestamp on all but DBQLogTbl. We use Start Time to delete from DBQLogTbl as the collectiontimestamp is way off in this table (may be a bug)
43) DBC.DiskSpace (Useful)
It provides information about disk space usage (including spool) for any database or account.
SELECT DatabaseName,CAST (SUM (MaxPerm) AS FORMAT 'zzz,zzz,zz9'),CAST (SUM (CurrentPerm) AS FORMAT 'zzz,zzz,zz9'),CAST (((SUM (CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100) AS FORMAT 'zz9.99%') AS "% Used FROM DBC.DiskSpace GROUP BY 1
ORDER BY 4 DESC;
44) DBC.TableSize
It provides information about disk space usage (excluding spool) for any database, table or account
SELECT Vproc,CAST (TableName AS FORMAT 'X(20)') ,CurrentPerm,PeakPerm FROM DBC.TableSize WHERE DatabaseName = USER ORDER BY TableName, Vproc ;
45) DBC.AllSpace
It provides information about disk space usage (including spool) for any database, table, or account.
SELECT Vproc,CAST (TableName AS FORMAT 'X(20)') ,MaxPerm,CurrentPerm
FROM DBC.AllSpace WHERE DatabaseName = USER ORDER BY TableName, Vproc ;
Comments
Post a Comment