Create a Macro for Space Usage Reporting | Teradata Vantage - Creating a Macro for Space Usage Reporting - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
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;

Result:

*** 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