BTEQ利用方法

#目的
Teradata DatabaseへSQLクエリーを送信するCUIプログラムであるBasic Teradata Query(BTEQ)の操作まとめ

#コマンド
| 内容 | コマンド | 実行例・補足 |
|:-:|:-:|:-:|
| BTEQ起動  | bteq  | 文字セットとI/OエンコードはASCII  |
| BTEQ起動（+ログイン）  | bteq .LOGON [tdpid]/[userid], [password], '[acctid]'  | bteq .logon 127.0.0.1/sysdba,sysdba  |
| ユーザを確認  | select user;  | [ビルトイン関数](https://docs.teradata.com/reader/1DcoER_KpnGTfgPinRAFUw/oXnBzpwPwqND9DAJRWofWA)  |
|直前のコマンドを再実行| = ||
|現在の設定を確認| show controls [設定]; | 設定が指定されない場合はALL|
|書式設定をデフォルトに戻す| .set defaults; ||
|分割して表示| .foldline; ||
|列名を左に表示| .sidetitles ||
|表示幅を変更| .width [文字数] | .width 200|
|列の表示文字数を指定| カラム名 (format 'X(文字数)') | カラム名(format 'X(20)')|

#実行例

###BTEQ起動

```
TDExpress1620_Sles11:~ # bteq

 Teradata BTEQ 16.20.00.08 (32-bit) for LINUX. PID: 3711
 Copyright 1984-2018 Teradata. All rights reserved.
 Enter your logon or BTEQ command:
```
###BTEQ起動+ログイン

>bteq .LOGON [tdpid]/[userid], [password], '[acctid]' 

tdpid(Teradata Director Program ID)を省略した場合、デフォルトは「dbc」
hostsファイルの「dbccop1」で指定されているIPアドレスに接続
IPアドレスを直接指定しても可

```
TDExpress1620_Sles11:~ # bteq .logon 127.0.0.1/sysdba,sysdba;

 Teradata BTEQ 16.20.00.08 (32-bit) for LINUX. PID: 4420
 Copyright 1984-2018 Teradata. All rights reserved.
 Enter your logon or BTEQ command:

.logon 127.0.0.1/sysdba,

 *** Logon successfully completed.
 *** Teradata Database Release is 16.20.32.23                   
 *** Teradata Database Version is 16.20.32.23                     
 *** Transaction Semantics are BTET.
 *** Session Character Set Name is 'ASCII'.
 
```

###ユーザを確認
```
select user;

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

User
---------------------------------------------------------------------------
SYSDBA
```
###設定を確認
```
show controls;
 Maximum Instruction Bytes           = 1048500 
 Maximum Record Bytes                = 1024000
 Maximum Request Buffer Bytes        = 7340032
 Maximum Response Buffer Bytes       = 16775168
 Maximum Packed Records              = 64000
 Activity Count Bytes                = 8
 Maximum Sessions                    = 200
 Maximum Repeat Factor               = 2147483647
 Maximum String Length               = 254
 Maximum Report Columns              = 65535
 Maximum WITH Clauses for a Report   = 10
 Maximum Report Title Lines          = 10
 Maximum Report Page Lines           = 2147483647
 Minimum Report Width                = 20
 Maximum Report Width                = 1048575

 Maximum Notify MSG Text Bytes       = 254
 Maximum Bytes Saved for SUPPRESS, 
 PAGEBREAK, SKIPLINE, UNDERLINE,   
 and SKIPDOUBLE Commands, and for  
 &n Substitutions                    = 256

 Client Platform Byte Order          = LITTLE ENDIAN

 EXPORT  RESET 
 IMPORT  RESET
 LOGMECH                 = default
 LOGON   127.0.0.1/sysdba
 MESSAGEOUT RESET
 REPEAT                  = 1
 RUN     
 [SET] AUTOKEYRETRIEVE   = OFF
 [SET] BRANCHMSG         = VERBOSE
 [SET] DECIMALDIGITS     = 0 (SPB DEFAULT)
 [SET] ECHOREQ           = ON  
 [SET] ENCRYPTION        = OFF
 [SET] ERRORLEVEL        = ON  
 [SET] ERRORONDBSWARNING = OFF
 [SET] ERROROUT          = STDERR
 [SET] EXITONDELAY       = OFF
 [SET] FOLDLINE          = ON 1-100
 [SET] FOOTING           = NULL
 [SET] FORMAT            = OFF
 [SET] FORMCHAR          = OFF
 [SET] FULLYEAR          = OFF
 [SET] HEADING           = NULL
 [SET] INDICATORMODE (alias INDICDATA) = OFF
 [SET] LARGEDATAMODE     = OFF
 [SET] LOGONPROMPT       = ON
 [SET] MAXERROR          = OFF
 [SET] NOTIFY            = OFF
 [SET] NULL              = ?
 [SET] OMIT              = OFF ALL
 [SET] PACK              = 0
 [SET] PAGEBREAK         = OFF ALL
 [SET] PAGELENGTH        = 55   
 [SET] PREPAREMODE       = OFF
 [SET] QUIET             = OFF 
 [SET] RECORDLENGTH      = Import/Export: MAX64  Stdout: MAX1MB
 [SET] RECORDMODE        = OFF
 [SET] REPEATSTOP        = OFF  
 [SET] REPORTALIGN       = COMPATIBLE
 [SET] RETCANCEL         = OFF  
 [SET] RETLIMIT          = Rows: No Limit  Columns: 100 
 [SET] RETRY             = ON
 [SET] RTITLE            = NULL
 [SET] SEPARATOR         = two spaces
 [SET] SESSION CHARSET        = ASCII                         
       import/export encoding = ASCII                         
       stdin/stdout encoding  = ASCII                         
 [SET] SESSION RESPBUFLEN  = 8192 
 [SET] SESSION SQLFLAG   = NONE
 [SET] SESSION TRANSACTION = BTET
 [SET] SESSION TWORESPBUFS = ON
 [SET] SESSIONS          = 1
 [SET] SIDETITLES        = OFF for the normal report.
       And, it is   ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
 [SET] SKIPDOUBLE        = OFF ALL
 [SET] SKIPLINE          = OFF ALL
 [SET] STATEMENTINDEPENDENCE = OFF
 [SET] SUPPRESS          = OFF ALL
 [SET] TDP               = 127.0.0.1
 [SET] TIMEMSG           = DEFAULT
 [SET] TITLEDASHES       = ON for the normal report.
       And, it is   ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
 [SET] TMSMMSG           = OFF
 [SET] TRAILINGLINES     = DEFAULT
 [SET] TRANSLATECTRLSTOSPACES = ON
 [SET] TRIMTRAILINGSPACES = ON
 [SET] UNDERLINE         = OFF ALL
 [SET] WIDTH             = 75
```

###分割して表示
```
.foldline;

show controls foldline;

 [SET] FOLDLINE = ON 1-100
```

```
select c1, c2, c3 from t1 where c1 < 20;

 *** Query completed. 10 rows found. 3 columns returned. 
 *** Total elapsed time was 2 seconds.


         c1
-----------
   c2
   --
   c3
   ----
         19
   a
   abcd
         17
   a
   abcd
          7
   a
   abcd
         15
   a
   abcd
          5
   a
   abcd
         13
   a
   abcd
          3
   a
   abcd
         11
   a
   abcd
          1
   a
   abcd
          9
   a
   abcd
```

##列名を左に表示
```
.sidetitles;
 BTEQ -- Enter your SQL request or BTEQ command: 
.show controls sidetitles;

.show controls sidetitles;

 [SET] SIDETITLES = ON for the normal report.
       And, it is   ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
```

```
select c1, c2, c3 from t1 where c1 < 20;

 *** Query completed. 10 rows found. 3 columns returned. 
 *** Total elapsed time was 2 seconds.

c1           19
c2  a
c3  abcd
c1           17
c2  a
c3  abcd
c1            7
c2  a
c3  abcd
c1           15
c2  a
c3  abcd
c1            5
c2  a
c3  abcd
c1           13
c2  a
c3  abcd
c1            3
c2  a
c3  abcd
c1           11
c2  a
c3  abcd
c1            1
c2  a
c3  abcd
c1            9
c2  a
c3  abcd

```

###列の表示文字数を指定

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


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

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

```


#参考
[BTEQの紹介 • Basic Teradata Query リファレンス • リーダー • Teradataドキュメント](https://docs.teradata.com/reader/Ueo4joQI46W923PHsPTOzA/fBZaSF2hQMLRNVh~JXEY4Q)
[]()
[]()