Introduction to Teradata BTEQ
A CLI is a command-line interface that is used for interaction with the underlying system. For Teradata, BTEQ is the CLI interface that allows performing communication with more than one database system. The BTEQ interface allows sending SQL queries and the interface formats the queries in the backend and prints the output to the same console. BTEQ is a very powerful CLI interface utility and it has the capability to work in Batch and Interactive mode. These utilities are useful in sending DLL-based statements, DML-based statements, and even stored procedures and macros to the database systems working beneath it. The output of the BTEQ will be most probably printed onto the screen, whereas it can also be directed to a printer to pick prints or even written or captured onto a specific file.
How BTEQ works?
BTEQ can be operated in both Batch and Interactive mode.
The BTEQ cli is not mostly installed on the server systems, Instead, they are installed to the client systems or workstations involved. Mostly the two major environments through which BTEQ are connected are the Mainframe and the workstation environments. Here the Mainframe environments can be connected to the BTEQ session through LAN or I/O channel, whereas the workstations are connected through LAN only.
For the systems which is connected to a Mainframe terminal, the BTEQ uses a TDP(Teradata director program) and for a workstation connected system, it uses the MTDP(Micro Teradata Director Program). BTEQ session allows to submission of both SQL queries and BTEQ commands in its interface.
A Teradata server will be operating between the BTEQ and the backend database systems, This server is responsible for interpreting the requests, Setting them in a hierarchy, and passing the requests to the database operating on the backend.
The TDP and MTDP are responsible to establish a connection between the server and BTEQ CLI.
The request to the systems can be of two different types, First, the single statement request was only one specific request will be sent. Next the multiple statement Teradata request where more than one specific SQL statement are sent for processing.
BTEQ sends the best single appeal at a time to anyone to be connected to a Teradata Database session. If a gaffe happens that advises the entreaty failed nevertheless may be rehashed, BTEQ resubmits the request. BTEQ pledges no likewise action. For the reason that BTEQ permits SQL to the database deprived of decoding it, it isn’t transaction alignment aware. This method can’t work out accomplishment over a transaction’s status. As such, the status ought to be definite totally via way of means of the technique used to hire BEGIN TRANSACTION (BT), ABORT, and END TRANSACTION (ET). Therefore, patrons ought to reminisce BTEQ’s “unawareness” in accumulation to the dissimilarities among ANSI and Teradata transaction semantics modes whilst it’s far vibrant for a transaction associated to unity or greater acquiesced requests to be totally retriable, or whilst a transaction ought to span requests.
When the semantics mode is brought up in Teradata, irrespective of the approach used to discover its transaction, a request or declaration’s catastrophe effects in push back of that transaction. It is the user’s duty to determine how to operate on those rollbacks.
Several BTEQ Operations
|Logon||This helps to get the user logged on to the Teradata system|
|Export||This option is used for exporting a file out of the Teradata database system, The path of the file needs to be specified along with the command.|
|Import||When a specific file needs to be brought into the database system or the server the import command is used, the path of the file needs to be specified along with the command.|
|Errorcode||When a Query fails or aborts due to a specific error then the query needs to be fixed. For fixing the status code of the executed query can be pulled using this command.|
|Logoff||This command is used to logoff from the database and also this command closes all the associated sessions connected to the database.|
|Runfile||This command is used for executing the file. Mostly the file will be filled with SQL queries and stored procedures.|
|Activitycount||Prints the number of rows impacted in the previously executed query.|
|Show versions||the versions of the various Teradatabase items are expected to be displayed. This is achieved by means of the show version command.|
|Database||This command is used for setting up the database used in the system.|
BTEQ Login snap: In the below example logon command is used to login into the BTEQ system.
BTEQ Show Version: In the below example the versions of the various tera database items are expected to be displayed. This is achieved by means of the show version command.
BTEQ help: The BTEQ help command allows to display of all the major commands being used in the BTEQ terminal. It also expresses the associated syntax of the commands.
BTEQ help: The show error map function helps in printing the error level and the hierarchy associated to each of the levels. We can see from the printed snap each of the error codes and the corresponding error levels.
As like any other command-line interface in the market, the BTEQ has a very steady capability for user support. These BTEQ cli’s come up with classified set of commands which provide options to flexibly and capably run commands and SQL queries. This makes the BTEQ command-line interface the most preferred setup for executing the commands in Teradata database enterprise systems.
This is a guide to Teradata BTEQ. Here we discuss the Introduction, syntax, and parameters, How BTEQ works? examples with code implementation. You may also have a look at the following articles to learn more –