Create a Macro for Space Usage Reporting | Teradata Vantage - About Creating a Macro for Space Usage Reporting - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantageâ„¢

You can create a macro like the one shown here.

Example of Creating a Macro for Space Usage Reporting

The following macro shows space used.

CREATE MACRO superspace as (
SELECT databasename, SUM(maxperm), SUM(currentperm)
  FROM DBC.DiskSpaceV
  GROUP BY databasename
  ORDER BY databasename
  WITH SUM(maxperm), sum(currentperm);
);

When executed, the macro returns the following results.

execute superspace;
*** Query completed. 28 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
                                                                       
                                                                       
DatabaseName                  Sum(MaxPerm)    Sum(CurrentPerm)
---------------------       --------------  ------------------
macro                                                                  
All                                      0                   0
cliuser                         10,000,000               7,168
CONSOLE                            100,000                   0
Crashdumps                   1,024,000,000                   0
Custo                        5,120,000,000         100,082,688
CUSTOMER_SERVICE                10,000,000               5,632
dash                            20,000,000               9,216
DBC                         24,316,695,812          89,080,832
Default                                  0                   0
Test_User                       20,000,000               4,096
explainsample                    1,000,000              12,288
EXTUSER                                  0                   0
mkt_bsktTest                 1,280,000,000           1,108,992
PDTBASE                         10,000,000              33,792
PUBLIC                                   0                   0
qttest                         500,000,000              10,240
SQLJ                           600,000,000                   0
SysAdmin                        40,000,000           1,693,696
SYSLIB                          10,000,000              12,288
SYSSPATIAL                     110,000,000           2,931,712
SystemFe                        60,000,000             207,872
SYSUDTLIB                      100,000,000                   0
Sys_Calendar                    15,000,000           2,654,208
TDPUSER                                  0                   0
testdb                             800,000             133,120
testuser                        20,000,000               9,216
udtuser                         10,000,000                   0
                       -------------------  ------------------
Sum(MaxPerm)                33,277,595,812         197,997,056