目的
TeradataDBをインストールした後、最初に実施すること。
※環境構築（TeradataDBインストール）は以下を参照
無償でTeradataDB環境構築（Teradata Express for VMware）
コマンド
| 内容 | コマンド | 実行例・補足 |
|---|---|---|
| DBCの空き容量を確認 | select Vproc, DatabaseName, MaxPerm, CurrentPerm from DBC.DiskSpace where DatabaseName = 'DBC'; | |
| データベース管理ユーザ作成 | CREATE USER "dbadmin" FROM "dbc" AS PERM = 5e9 * (hashamp() + 1) PASSWORD = "dbadmin" ; | |
| ロギングの有効化 | begin query logging on [DB名]; | DBQL参照 |
実行例
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 の空き容量
データベース管理ユーザ作成
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;
クエリログの取得設定
現在のクエリログ設定確認
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