5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

BTEQ利用方法

Last updated at Posted at 2020-11-02

#目的
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; ビルトイン関数
直前のコマンドを再実行 =
現在の設定を確認 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ドキュメント

5
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?