Command Line Options | Restrictions Options| Basic Teradata Query - Restrictions Options - Basic Teradata Query
Basic Teradata® Query Reference
Product
Basic Teradata Query
Release Number
17.10
Published
February 2022
Language
English (United States)
Last Update
2022-02-03
dita:mapPath
nnw1608578382132.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities
Introduction to BTEQ
Welcome to Teradata Vantage
Overview of BTEQ
Prerequisites
Supported Releases
Environment
BTEQ Sessions
BTEQ Session Modes
Determining the Current Version of BTEQ
Windows
UNIX, Linux, and Apple macOS Systems
BTEQ Operation in the Client-Server Environment
BTEQ Communication
Mainframe-Attached Communication Diagram
Workstation-Attached Communication Diagram
BTEQ Process
Call-Level Interface
Teradata Director Program (TDP) (or MTDP)
Teradata Server
BTEQ Input
Command Set
Session Controls
SQL Controls
File Controls
Sequence Controls
Format Controls
Diagnostic Controls
Starting and Exiting BTEQ
Obtaining System Access
System Access Requirements
Obtaining Client System Access
Obtaining Vantage System Access
Additional Database Privileges
Starting a BTEQ Session
Command-Line Invocation
Encoding Controls for Unicode Sessions
Command Line Options
Diagnostic Options
Encoding Options
Restrictions Options
Automatic File Allocation
LOGON Command
Basic LOGON Command Elements
tdpid
userid
Password
acctid
Advanced LOGON Command Elements
Logging On Database Sessions
Logging On in Interactive Mode
Logging On in Batch Mode
Bypassing Logon Prompts
Successful Logon Message
Failed Logon Message
Retrying a Failed Logon Attempt
Basic and Advanced Logon Elements Diagram
Logging Off Database Sessions and Exiting BTEQ
LOGOFF Command
LOGOFF Example
EXIT and QUIT Commands
EXIT and QUIT Examples
Error Information Options
Changing Your Password
When Your Password Expires
Changing Your Expired Password
Changing Your Password at Any Time
Specifying a Different Teradata Server
System Default tdpid
Changing the tdpid
Changing the Server for a Single Logon
Changing the Server for the Current Session
Specifying Multiple Sessions
Maximum Number of Sessions in a Set
Setting the Number of Sessions
Getting Help
Syntax Conventions
Notes on Command Input
Using BTEQ
SQL Requests
Request Types
Single-Statement Example
Multi-Statement Example
Multi-Statement Processing
Terminating Semicolon Recognition
Continuing SQL
Example 1 – Continuing SQL
Example 2 – Continuing SQL
Negating a Request
Example – Negating Requests
Using Comments in a BTEQ Session
BTEQ Comment Elements
BTEQ Comment Example
Continuing BTEQ Comments
ANSI Comment Elements
ANSI Comment Example
Mixing Comments
Concatenating BTEQ Comments
Invalid Comments
Entering IPv4 and IPv6 Network Addresses
Example: Entering IPv4 and IPv6 Network Addresses
UNIX Signals
SIGTERM Handling
Example 1 – SIGTERM Handling
Example 2 – SIGTERM Handling
Example 3 – SIGKILL
Running BTEQ Batch Jobs
Running Under z/OS
Running Other Client Systems
Switching from Interactive to Batch Mode
Defining Batch Mode Files
File Types
Defining Input and Output Files for z/OS BTEQ
Defining Additional Files
Specifying a Standard Input File
Using a Here Document for Standard Input Files
Specifying a Standard Output File
Using the > or >> Characters to Specify Output Files
Specifying Output File Block Size
Specifying Output File Logical Record Length
Specifying an Import File
Specifying Files for User-Defined Functions
Workstation BTEQ
Mainframe BTEQ
Transferring Source or Include Files Using Mainframe BTEQ
Specifying an Export File
Specifying a Run File
Specifying a Standard Error Output File
Using Scripts, Run Files, Macros, and Stored Procedures
Line Numbers in Files
Command Execution Order
Creating and Using Scripts
Creating a Script
Identifying the Script as the Standard Input File
Displaying Script Results
Omitting the Userid and Password from the Script File
Creating and Using Run Files
Creating a Run File
Identifying a Run File as a Valid ddname
Executing a Run File
Terminating a Run File
Displaying Run File Results
Revising Scripts and Run Files
Accessing the System Editor
Creating and Using Macros
Additional Database Privileges for Macros
Command Execution in Macros
Creating a Macro
Available Commands for SQL Macros
Macro Example – deptdisplay
Executing Macros
Including a Macro in a Script
Creating Reports with Macros
Macro Example – Department Information Report
Defining the ddname of the Export File
Running the Macro
Report Example
Command Execution Messages
Creating and Executing a Macro from a Script
Creating and Using Stored Procedures
Types of Stored Procedures
External Stored Procedures
SQL Stored Procedures
Command Execution
Creating or Replacing an SQL Stored Procedure
Executing Stored Procedures
Including a Stored Procedure in a Script
Command Execution Messages
Using the Script File
Status Value Keywords
Testing Status Values
Status Examples
Testing and Branching
Using the REPEAT Command
Updating Rows Using a Single Request
Updating Rows Using Multiple Requests
Updating Rows by Importing a File
Using the REPEAT Command when Importing a File
Using the = Command When Importing a File
Using REPEAT with Multiple Sessions
Discarding Input and Abort Requests
Using the Break Key on Workstations
Single-Break
Double-Break
Triple-Break
Using the Break Key on z/OS
Single-Break
Interrupting User Input
Aborting Requests with the ABORT Command
Timing the ABORT Request
Restoring a Database After a Failed ABORT Request
Aborting a File Redirection
Entering Operating System Commands from a BTEQ Session
Entering UNIX Commands
Entering Windows Commands
Error Handling
Database Error Codes
Possible Data Loss Conditions
BTEQ Return Codes
Error Severity Levels
Maximum Error Level
Stored Procedure Compilation
SPL Structure
SPL Response Mode
BTEQ Error Output Files
I/O Errors and Abends
Mainframe-Attached Clients
BTEQ Large File (LF) Support
OS Limitations
BTEQ Output
Formatting BTEQ Output
Inheriting Defaults
Viewing the Current Format Specifications
Specifying the Default Format Specifications
Changing Screen Width
Changing the Null Specification
Disabling the Echo Required Function
Disabling the Retry Function
When an SQL Statement Fails in Teradata Mode
Specifying Record or Indicator Mode
Numeric Overflow in Reports
Limiting the Amount of Display Data
Specifying the Report Format
Vertical Presentation
Horizontal Presentation
Deleting Duplicated Data
Inserting Blank Lines
Inserting a Line of Underline Characters
Specifying Page Breaks
Specifying Column Separations
Splitting Report Lines
Horizontal Presentation
Vertical Presentation
Specifying Side Titles
Specifying Headers and Footers
BTEQ Commands for Headers and Footers
Specifying Printer Formats
Specifying the Page Size
Specifying the Form Feed Character
WITH Clause Output
Sequence of Fields
Summary Title Accommodation
Title Phrases
Converting Screen Displays to Reports
Producing a Report
Changing the Command Sequence
Identifying the Input File
Creating an Output Log File
Printing the Report File
Resubmitting Output as Input
Submitting Output as Input
Specifying the Export File
Specifying REPORT Format
Retrieving Data
Retrieving Data in REPORT Format
Closing the Export File
Specifying the Import File
Resubmitting the Output Data
Field Mode Translation of Control Characters
BTEQ Commands
BTEQ Command Syntax
General Rules
Windows Only
Example – BTEQ Command Syntax
BTEQ Command Elements
Continuing a BTEQ Command
Syntax Variable Definitions
Default BTEQ Values
Specifying Default Values
Checking Default Values
Command Syntax Errors and Command Settings
BTEQ Command Descriptions
=
AUTOKEYRETRIEVE
BRANCHMSG
COMPILE
CONNECTSTRING
DECIMALDIGITS
DEFAULTS
ECHOREQ
ENCRYPTION
ERRORLEVEL
ERRORONDBSWARNING
ERROROUT
EXIT or QUIT
EXITONDELAY
EXPORT
EXPORTEJECT
FOLDLINE
FOOTING
FORMAT
FORMCHAR
FULLYEAR
GOTO
HANG
HEADING and RTITLE
HELP BTEQ
IF, ELSEIF, ELSE, and ENDIF
IMPORT
INDICATORMODE
LABEL
LARGEDATAMODE
LOGMECH and LOGDATA
LOGOFF
LOGON
LOGONPROMPT
MAXERROR
MESSAGEOUT
NOTIFY
NULL
OMIT
OS
PACK
PAGEBREAK
PAGELENGTH
QUIET
RECORDLENGTH
RECORDMODE
REMARK
REPEAT
REPEATSTOP
REPORTALIGN
RETCANCEL
RETLIMIT
RETRY
RUN
SEPARATOR
SESSION CHARSET
SESSION RESPBUFLEN
SESSION SQLFLAG
SESSION TRANSACTION
SESSION TWORESPBUFS
SESSIONS
SHOW
SHOW CONTROLS
SHOW ERRORMAP
SHOW VERSIONS
SIDETITLES
SKIPDOUBLE
SKIPLINE
STATEMENTINDEPENDENCE
SUPPRESS
TDP
TIMEMSG
TIMEOUT
TITLEDASHES
TMSMMSG
TRAILINGLINES
TRANSLATECTRLSTOSPACES
TRIMTRAILINGSPACES
TSO
UNDERLINE
WIDTH
How to Read Syntax Diagrams
Syntax Diagram Conventions
Notation Conventions
Paths
Continuation Links
Required Entries
Optional Entries
Strings
Abbreviations
Loops
Excerpts
Multiple Legitimate Phrases
Sample Syntax Diagram
Diagram Identifier
Sample Workforce Database Tables
Sample Workforce Database Tables
Table DDLs
DDL for the Employee Table
DDL for the Department Table
DDL for the Project Table
DDL for the Charges Table
Table Contents
Kanji, Chinese, Korean, Unicode Character Set Support
Kanji, Chinese, Korean, Unicode Character Set Support
Kanji Character Sets
Establishing the Kanji Character Set
Description of the Kanji Character Set
SESSION CHARSET Command
GRAPHIC, VARGRAPHIC and LONG VARGRAPHIC
Chinese and Korean Character Sets
Rules for Chinese and Korean Character Sets
Unicode Character Sets
Rules for Unicode Character Sets
Using BTEQWIN
What is BTEQWIN?
BTEQWIN Main Window
BTEQWIN Menus
Sessions Menu
Edit Menu
View Menu
Window Menu
Help Menu
Displaying Multibyte Characters
Additional Information
Audience
Changes and Additions
Teradata Links
Related Documentation
-df
Indicates BTEQ commands that require file IO are to be disabled so that they can not be used during the BTEQ process session.
-do
Indicates BTEQ's OS command is to be disabled so that it can not be used during the BTEQ process session.
-dr
Indicates BTEQ commands that redirect stdout and stderr streams are to be disabled so that they can not be used during the BTEQ process session.
-t [n]
Enables specification of a maximum number of minutes an interactive user is allowed to let all their currently established database sessions collectively be idle, beyond which point BTEQ will automatically log them off. When no value is given, the default of 10 minutes is assumed. Refer to the TIMEOUT command for more details.