TeradataDBインストール後、最初に実施すること

# 目的
TeradataDBをインストールした後、最初に実施すること。
※環境構築（TeradataDBインストール）は以下を参照
[無償でTeradataDB環境構築（Teradata Express for VMware）](https://qiita.com/bickel/items/2ea469ff74c7c4378a55)

# コマンド
| 内容 | コマンド | 実行例・補足 |
|:-:|:-:|:-:|
|DBCの空き容量を確認  |  select Vproc, DatabaseName, MaxPerm, CurrentPerm from DBC.DiskSpace where DatabaseName = 'DBC'; |   |
| [データベース管理ユーザ作成](https://docs.teradata.com/r/0rYfghiYcBZf2Y_ffU5UtA/rYYTcQtUxUl5gGSFp3JQRg)  |  CREATE USER "dbadmin" FROM "dbc" AS PERM = 5e9 * (hashamp() + 1) PASSWORD =  "dbadmin" ; |   |
| ロギングの有効化  | begin query logging on [DB名];  ​| [DBQL](https://qiita.com/bickel/items/06c6a52d2f21db2cfc9f)参照  |
|   |   |   |
# 実行例
### DBCの空き容量を確認

```
select Vproc, DatabaseName, MaxPerm, CurrentPerm from DBC.DiskSpace where DatabaseName = 'DBC';

         Vproc  DatabaseName                                       MaxPerm                 CurrentPerm
--------------  ------------------------------  --------------------------  --------------------------
             0  DBC                                         12,893,175,514                  47,423,488
             1  DBC                                         12,893,175,514                  47,394,816
```

12GB x 2 の空き容量


### [データベース管理ユーザ作成](https://docs.teradata.com/r/0rYfghiYcBZf2Y_ffU5UtA/rYYTcQtUxUl5gGSFp3JQRg)

5GB x 2 の容量を付与

```
CREATE USER "dbadmin" FROM "dbc" AS PERM = 5e9 * (hashamp() + 1) PASSWORD =  "dbadmin" ;

grant execute, select on "dbc" to "dbadmin" with grant option;

select UserName(format 'X(20)'), PermSpace from dbc.UsersV;

UserName                               PermSpace
--------------------  --------------------------
DBC                               15,786,351,028
dbadmin                           10,000,000,000
tdwm                                 100,000,000
LockLogShredder                        4,800,000
External_AP                          160,000,000
TDPUSER                                        0
SysAdmin                             160,000,000
SystemFe                           6,400,000,000
TDMaps                             4,800,000,000
Sys_Calendar                          25,600,000
Crashdumps                                     0
viewpoint                             10,000,000
console                                   50,000


```

### サンプル表作成

```
create table t1 
(c1 integer , c2 char(10), c3 varchar(20))
unique primary index (c1);

delete t1 all;
insert into t1 values (1, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss');

insert into t1 select c1+1, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+2, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+4, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+8, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+16, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+32, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+64, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+128, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+256, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+512, to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;

insert into t1 select c1+power(2,10), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,11), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,12), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,13), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,14), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,15), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,16), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,17), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,18), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,19), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,20), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;

insert into t1 select c1+power(2,21), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,22), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
insert into t1 select c1+power(2,23), to_char(random(1,100)) || 'abcdefg', to_char(random(1,10000000)) || 'ssssssssss' from t1;
```

select Vproc, Databasename(format 'X(20)'), MaxPerm, CurrentPerm from dbc.Diskspace;

### クエリログの取得設定
詳細：[DBQL（データベース クエリー ログ）](https://qiita.com/bickel/items/06c6a52d2f21db2cfc9f)

##### 現在のクエリログ設定確認

```
SELECT * FROM DBC.DBQLRULESV;

 *** Query completed. No rows found. 
 *** Total elapsed time was 1 second.


SHOW QUERY LOGGING ON ALL;

 *** Text of DDL statement returned. 
 *** Total elapsed time was 1 second.

---------------------------------------------------------------------------

    Rule UserName   "ALL"
      Rule UserId   00000000
      Rule ZoneId   00000000
          Account   (Rule for any Account)

    NO RULE FOUND
```
→　設定なし

##### dbadminに対するクエリログ取得を有効化

```
begin query logging on dbadmin;

 *** Begin querylog accepted. 
 *** Total elapsed time was 1 second.


show query logging on dbadmin;

 *** Text of DDL statement returned. 
 *** Total elapsed time was 1 second.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Rule UserName   dbadmin
      Rule UserId   00001033
      Rule ZoneId   00000000
          Account   (Rule for any Account)

       DBQL RULE:
          Explain   F
           Object   F
              SQL   F
             Step   F
          XMLPlan   F
       StatsUsage   F
         UseCount   F
            Param   F
     FeatureUsage   F
          Verbose   F
     StatsDetails   F
      UtilityInfo   F
        NoColumns   F
          Summary   F
        Threshold   F
  Text Size Limit   200  

```



# 参考
[無償でTeradataDB環境構築（Teradata Express for VMware）](https://qiita.com/bickel/items/2ea469ff74c7c4378a55)
[]()
[]()