データディクショナリビューを利用した確認

#目的
データディクショナリビューを利用した確認

#コマンド
| 内容 | ディクショナリビュー | 実行例 |
|:-:|:-:|:-:|
| DB・ユーザの一覧と割当領域を確認  | Databases |  select DatabaseName, PermSpace,DBKind from dbc.databases order by 3,1 ; |
| DBに含まれる表を確認  |  [TablesV](https://docs.teradata.com/reader/fMqp0D_uNdzh8~BHZ2S~9w/nOADEyvvxstfCGwzcX6tOQ) | select DatabaseName(char(20)), TableName(char(20)), TableKind, LastAlterTimeStamp, BlockCompression from dbc.TablesV where DatabaseName = 'SYSDBA' order by 2; |
| 各DBの容量と使用量を確認  | [DiskSpace](https://docs.teradata.com/reader/fMqp0D_uNdzh8~BHZ2S~9w/spI7Kn7~vrQEvi2K6z50lA)  | select DatabaseName, sum(MaxPerm), sum(CurrentPerm), case sum(MaxPerm) when 0 then 0 else sum(CurrentPerm) * 100.00 / sum(MaxPerm) end as usage from dbc.DiskSpace group by DatabaseName order by DatabaseName;  |
| DBに含まれるテーブル毎の容量を確認 | [TableSize](https://docs.teradata.com/reader/fMqp0D_uNdzh8~BHZ2S~9w/Lwxe8D1igPvHBdHRh7j~hA) | select TableName, sum(CurrentPerm) from dbc.TableSize where DatabaseName = 'SYSDBA' group by TableName order by TableName; |
| 統計情報の状態を確認  | [TableStatsV](https://docs.teradata.com/reader/fMqp0D_uNdzh8~BHZ2S~9w/OSyWI~ERlUsaXLjEjRlPAw) | select TableName(char(15)), ColumnName(char(15)), StatsSource, ValidStats, DBSVersion, IndexNumber, RowCount, CreateTimeStamp from dbc.TableStatsV where DatabaseName = 'SYSDBA'; |

#実行例
###DB・ユーザの一覧と割当領域を確認(Databases)
```
select databaseName, PermSpace,DBKind from dbc.databases order by 3,1 ;

DatabaseName                                     PermSpace  DBKind
------------------------------  --------------------------  ------
dbcmngr                                        100,000,000  D
SQLJ                                             6,400,000  D
SYSBAR                                         320,000,000  D
SYSJDBC                                         16,000,000  D
SYSLIB                                         320,000,000  D
SYSSPATIAL                                      48,000,000  D
SYSUDTLIB                                      256,000,000  D
SYSUIF                                           6,400,000  D
TDQCD                                           15,360,000  D
TDStats                                         16,000,000  D
TD_SERVER_DB                                 1,024,000,000  D
TD_SYSFNLIB                                     12,894,208  D
TD_SYSGPL                                        6,400,000  D
TD_SYSXML                                        6,400,000  D
All                                                      0  U
console                                             50,000  U
Crashdumps                                               0  U
DBC                                         15,786,351,028  U
Default                                                  0  U
External_AP                                    160,000,000  U
EXTUSER                                                  0  U
LockLogShredder                                  4,800,000  U
PUBLIC                                                   0  U
sora                                             2,500,000  U
SysAdmin                                       160,000,000  U
SYSDBA                                       9,996,000,000  U
SystemFe                                     6,400,000,000  U
Sys_Calendar                                    25,600,000  U
TDMaps                                       4,800,000,000  U
TDPUSER                                                  0  U
tdwm                                           100,000,000  U
viewpoint                                       10,000,000  U

```

###DBに含まれる表を確認
```
select DatabaseName(char(20)), TableName(char(20)), TableKind, LastAlterTimeStamp, BlockCompression from dbc.TablesV where DatabaseName = 'SYSDBA' order by 2;

DataBaseName          TableName             TableKind   LastAlterTimeStamp  BlockCompression
--------------------  --------------------  ---------  -------------------  ----------------
SYSDBA                emp                   T          2020-07-24 10:56:11  DEFAULT
SYSDBA                t1                    T          2020-08-22 10:08:19  DEFAULT
SYSDBA                t2                    T          2020-08-30 11:37:53  DEFAULT
SYSDBA                t3                    T          2020-08-30 10:48:11  DEFAULT
SYSDBA                t4                    T          2020-08-30 10:48:21  DEFAULT
SYSDBA                t5                    T          2020-08-30 12:28:32  DEFAULT
```
※[TableKind](https://docs.teradata.com/reader/fMqp0D_uNdzh8~BHZ2S~9w/pxSuemnYss0UjnadGEYhsA)

###各DBの容量と使用量を確認
dbc.DiskSpaceはAMP毎の容量であるため、DB名でグルーピングして合計を確認
>select 
 DatabaseName, sum(MaxPerm), sum(CurrentPerm), 
 case sum(MaxPerm) when 0 then 0 else sum(CurrentPerm) * 100.00 / sum(MaxPerm) end as usage 
from dbc.DiskSpace group by DatabaseName order by DatabaseName;

```
select 
 DatabaseName, sum(MaxPerm), sum(CurrentPerm), 
 case sum(MaxPerm) when 0 then 0 else sum(CurrentPerm) * 100.00 / sum(MaxPerm) end as usage 
from dbc.DiskSpace group by DatabaseName order by DatabaseName;

 *** Query completed. 32 rows found. 4 columns returned. 
 *** Total elapsed time was 1 second.

DatabaseName                                  Sum(MaxPerm)            Sum(CurrentPerm)              usage
------------------------------  --------------------------  --------------------------  -----------------
All                                                      0                           0                .00
console                                             50,000                           0                .00
Crashdumps                                               0                           0                .00
DBC                                         15,786,351,028                 113,971,200                .72
dbcmngr                                        100,000,000                      16,384                .02
Default                                                  0                           0                .00
External_AP                                    160,000,000                           0                .00
EXTUSER                                                  0                           0                .00
LockLogShredder                                  4,800,000                     319,488               6.66
PUBLIC                                                   0                           0                .00
sora                                             2,500,000                           0                .00
SQLJ                                             6,400,000                     172,032               2.69
SysAdmin                                       160,000,000                     548,864                .34
SYSBAR                                         320,000,000                      24,576                .01
SYSDBA                                       9,997,500,000                 963,018,752               9.63
SYSJDBC                                         16,000,000                           0                .00
SYSLIB                                         320,000,000                  10,526,720               3.29
SYSSPATIAL                                      48,000,000                   1,363,968               2.84
SystemFe                                     6,400,000,000                   2,629,632                .04
SYSUDTLIB                                      256,000,000                     974,848                .38
SYSUIF                                           6,400,000                      65,536               1.02
Sys_Calendar                                    25,600,000                   1,622,016               6.34
TDMaps                                       4,800,000,000                   7,733,248                .16
TDPUSER                                                  0                           0                .00
TDQCD                                           15,360,000                     253,952               1.65
TDStats                                         16,000,000                   1,097,728               6.86
tdwm                                           100,000,000                   1,122,304               1.12
TD_SERVER_DB                                 1,024,000,000                     286,720                .03
TD_SYSFNLIB                                     12,894,208                   4,562,944              35.39
TD_SYSGPL                                        6,400,000                           0                .00
TD_SYSXML                                        6,400,000                     491,520               7.68
viewpoint                                       10,000,000                           0                .00
```

###DBに含まれるテーブル毎の容量を確認
dbc.TableSizeはAMP毎の容量であるため、テーブル名でグルーピング。

```
select TableName, sum(CurrentPerm) from dbc.TableSize where DatabaseName = 'SYSDBA' group by TableName order by TableName;

TableName                                 Sum(CurrentPerm)
------------------------------  --------------------------
emp                                                  8,192
t1                                             100,294,656
t2                                              92,307,456
t3                                             469,843,968
t4                                              99,164,160
t5                                             201,400,320
```

###統計情報の状態を確認
```
select TableName(char(15)), ColumnName(char(15)), StatsSource, ValidStats, DBSVersion, IndexNumber, RowCount, CreateTimeStamp from dbc.TableStatsV where DatabaseName = 'SYSDBA';


TableName        ColumnName       StatsSource  ValidStats  DBSVersion                        IndexNumber              RowCount      CreateTimeStamp
---------------  ---------------  -----------  ----------  --------------------------------  -----------  --------------------  -------------------
t1               c6,c7            S            T           16.20.32.23                                 ?             4,194,309  2020-08-29 12:14:06
t1               ?                S            T           16.20.32.23                                 ?             4,194,309  2020-08-29 12:11:34
t1               c1               S            T           16.20.32.23                                 1             4,194,309  2020-08-29 12:11:34
t1               c3               S            T           16.20.32.23                                 ?             4,194,309  2020-08-29 12:12:23
t1               c4,c5            S            T           16.20.32.23                                 ?             4,194,309  2020-08-29 12:13:21
t1               c2               S            T           16.20.32.23                                 ?             4,194,309  2020-08-29 12:11:56
```

#参考
[データ ディクショナリの概要 • Teradata Vantage™ データ ディクショナリ • リーダー • Teradataドキュメント](https://docs.teradata.com/reader/fMqp0D_uNdzh8~BHZ2S~9w/NuqWvJe8I52AFm4kI5qbcA)
[ビューの参照 • Teradata Vantage™ データ ディクショナリ • リーダー • Teradataドキュメント](https://docs.teradata.com/reader/fMqp0D_uNdzh8~BHZ2S~9w/wlmiUEa_Z20KD~F7V4qdCQ)
[]()