1) LOGON:
The first command to perform the database operations is .LOGON.
.LOGON generally has four parameters,
1. TDPID
2. User ID
3. Password
4. Acct ID
Syntax:
Enter your logon or BTEQ command:
.LOGON generally has four parameters,
1. TDPID
2. User ID
3. Password
4. Acct ID
Syntax:
Enter your logon or BTEQ command:
.Logon 127.0.0.1/Tduser
Password:
****
Message From BTEQ:
***Logon Successfully Completed.
***Teradata Database Release is 12.00.00.10
***Teradata Database Version is 12.00.00.10
***Transaction Semantics Are BTET.
***Character set Name is ‘UTF16’
***Total Elapsed time was 1 Second.
Bteq Logon Elements:
Element | Requirement | Description |
Tdpid | Optional | The Teradata Director Program |
userid | Required | The User Identifier |
Password | Required | The Pwd for your userid |
Acctid | Optional | The account identifier for your userid |
Note: Omit Acctid if you have not been issued one.
2) Logon With TDPID:
Sometimes BTEQ expects users to enter TDPID.
You can see the TDPID in Teradata service controller by clicking the Edit hosts option.
The name you can see before cop1 or cop2 will be the TDPID of your system.
Normally, for local systems TDPID will be "localtd".
Syntax: .Logon [TDPID]/Userid
IP Address can be used as TDPID.
TDPID- May be Optional for Some Versions
Example:
.Logon Localtd/tduser
Bteq will ask for Password then.
3) ByPassing Logon prompts:
You can log onto the RDBMS without the system prompting you for your DBS username and password by setting the LOGONPROMPT command to OFF.
Enter the LOGONPROMPT command:
.Set LogonPrompt OFF
You can proceed when logon successful.
Output: ***Logon Prompts disabled. Type .LOGONPROMPT ON; to Re-Enable
4) LOGOFF:
LOGOFF will end the current RDBMS sessions without exiting BTEQ.
If you execute a LOGOFF command while a transaction is in progress, BTEQ aborts the transaction and backs out any changes that had been made to the database. (Significant feature)
Syntax:
.LOGOFF
Bteq displays the logoff message and prompts you for another logon or BTEQ command.
Output message:
*** you are now logged off from the DBC.
Teradata BTEQ for(System). Enter your Logon or BTEQ Command
5) BTEQ-Exit or Quit:
The BTEQ EXIT and QUIT commands are identical.
The EXIT command ends the current RDBMS sessions and exits BTEQ.
LOGOFF command is not required when you want to use EXIT or QUIT.
You may find it convenient to use the EXIT command to signify a normal ending and use the QUIT command to signify an abnormal ending.
Syntax:
The EXIT command ends the current RDBMS sessions and exits BTEQ.
LOGOFF command is not required when you want to use EXIT or QUIT.
You may find it convenient to use the EXIT command to signify a normal ending and use the QUIT command to signify an abnormal ending.
Syntax:
.EXIT Or. QUIT
BTEQ displays the exit message and returns you to your client system prompt.
*** Exiting BTEQ...
*** Exiting BTEQ...
6) . Bteq-Change Your Password:
Depending on the parameters set by Teradata administrator, your RDBMS password may expire periodically.
BTEQ requires that you change your expired password before logging onto the RDBMS.
When your RDBMS password expires, BTEQ displays the following message and prompts you to enter a new password:
***Warning 3032 User Pwd has Expired
Enter New Pwd:
Step 1:
Enter your password
Step 2:
Re Enter your password
If both entries are identical, and the logon is successful, BTEQ displays:
*** Logon Successfully Completed.
***Total Elapsed time was 17 Seconds
If both entries are not identical, BTEQ displays the following message and repeats the prompt for a new password:
If both entries are not identical, BTEQ displays the following message and repeats the prompt for a new password:
Password not matched, Please try again!
Enter New Password
7) Changing the TDPID:
You can change the TDPID for the following types.
Type 1: change the server for a single logon only
This can be achieved by LOGON with TDPID.
Example:
Type 1: change the server for a single logon only
This can be achieved by LOGON with TDPID.
Example:
.LOGON TDPID/tduser
Type 2:
change the server for all logons within the current session.
.SET TDP tdpid
Entering the TDP command specifies the Teradata RDBMS that BTEQ accesses for all the logons during the current BTEQ session.
8) How to submit a BTEQ Script in Batch mode:
Consider a BTEQ script contains the following code in it.
.Logon 127.0.0.1/user,pwd ; /* Login Credentials*/
Sel top 10 *from dbc.tables; /*Query */
.Quit
Way 1: You can submit the Bteq script in Batch Mode by .Run File Command.
.Run file will execute the commands provided in the file by forward only fashion.
Example: .Run File =BTEQfile.btq
Way 2: You can directly submit the script in the BTEQ prompt like below
Way 1 is being used generally in shell scripts since it is a procedural way.
Way 2 is used to see the immediate execution of the supplied.
Example: .BTEQ <BTEQfile.btq
9) Default Date Setting in BTEQ:
There are two default date settings in BTEQ.
After logging on to the session, you can render like below
After logging on to the session, you can render like below
1) Set session dateform= ANSIDATE; /*format is yyyy-mm-dd*/
2) Set session dateform=integerdate; /*fomat is yy/mm/dd –TERADATA date format*/
10) Checking Teradata versions:
You can check the version via BTEQ like below.
Example: .Logon 127.0.0.1/tduser,pwd;
.Show Version;
.Logoff
.Quit
Output: shows list of versions
11) QUIET:
When we use multiple sessions in our BTEQ, then QUIET option will be useful.
It is useful for reporting with the statistics of start and end times for the particular running cycle.
Oftenly, QUIET command will be used with REPEAT or = command for better reporting purpose.
Syntax: .Set Quiet ON;
It is useful for reporting with the statistics of start and end times for the particular running cycle.
Oftenly, QUIET command will be used with REPEAT or = command for better reporting purpose.
Syntax: .Set Quiet ON;
QUIET will suppress the outcome of SELECT statements, Activity messages.
Usage example: .Logon 127.0.0.1/tduser,pwd;
.Export file = C:\tx\abc.txt;
.Set Queit On;
Select *from sri.tbl_emp;
.Logoff
.Quit
In the above example, the output of select statement will not be printed in the display because of QUIET. However, the output will be exported to the file.
12) To skip first record while importing:
skip=1 will skip the first record.
Syntax:
Syntax:
.Import infile =<filename>, Skip=1;
Example:
Example:
.Logon Localtd/dbc,dbc;
.Import data file =sample.txt, skip =1;
.Repeat 3
Using empno (char(4))
Update tbl_emp SET salary=2*salary where emp_no= :empno
13) Specify Multiple Sessions:
You can set the number of sessions for BTEQ to process the queries in parallel manner.
Users come to know the main advantage of BTEQ while specifying the sessions more than one.
If you have multiple set/batch of queries which are independent to each other, then you are absolutely correct to use SET SESSIONS in BTEQ.
Users come to know the main advantage of BTEQ while specifying the sessions more than one.
If you have multiple set/batch of queries which are independent to each other, then you are absolutely correct to use SET SESSIONS in BTEQ.
For example, if you want to insert 1 lakh records into a database which approximately takes 100 minutes, then SET the sessions to 10.
The time will be (1/10) now compared to of previous insert.
Syntax:
.SET SESSIONS n
à where n is the number of sessions.
à n may vary with the workstation.
à n may vary with the workstation.
14) Single statement processing:
BTEQ will execute the commands to the Teradata RDBMS by watching where the semicolons used in the requested query.
Example:
Example:
1. INSERT tbl_employee('rrrrrrrrrrrrrrrrr',123,111);
2. INSERT tbl_employee('rrrrrrrrrrrrrrrrr',123,111);
3. INSERT tbl_employee('rrrrrrrrrrrrrrrrr',123,111);
The above example illustrates the single statement processing.
Since the semicolon is put at the end of each statement, BTEQ will execute each statement before executing the next one.
BTEQ submits the above statements to the Teradata RDBMS as three single statement requests.
Since the semicolon is put at the end of each statement, BTEQ will execute each statement before executing the next one.
BTEQ submits the above statements to the Teradata RDBMS as three single statement requests.
15) Error Code –Checking for Errors:
ERRORCODE indicates the actual completion code associated with the request.
It will return the code for most recent Teradata SQL request.
If ERRORCODE is Zero, then the request ran fine without any errors.
Syntax:
It will return the code for most recent Teradata SQL request.
If ERRORCODE is Zero, then the request ran fine without any errors.
Syntax:
.Logon localtd/dbc,dbc;
<Query>
.IF ERRORCODE = 0 THEN
<Next BTEQ Operation>
Example:
.Run File C:\TX\Sample.txt
Consider the below content is in the above path.
.Logon localtd/dbc,dbc
Database Sri;
Delete from tbl_emp;
.If Errorcode =0 Then .GoTo ins
Create table tbl_emp( id Int, name varchar(30), salary decimal(30,4));
.Label ins
Insert into tbl_emp (1,’Ruby’,15000);
Insert into tbl_emp (2,’Rose’,20000);
.Logoff
.Exit
Explanation:
1. First we login with user id and password.
2. We choose Database.
3. We are deleting the records from the employee table.
4. If delete operation is successful, then the error code will be zero and move to ins LABEL part.
5. It will insert the records and quit BTEQ.
Explanation:
1. First we login with user id and password.
2. We choose Database.
3. We are deleting the records from the employee table.
4. If delete operation is successful, then the error code will be zero and move to ins LABEL part.
5. It will insert the records and quit BTEQ.
16) GOTO – Redirecting the Control:
GOTO redirect the control to the label associated.
Sample Code:
Sample Code:
.Logon localtd/dbc,dbc
Database Sri;
Delete from tbl_emp;
.If Errorcode =0 Then .GOTO Ins
.Label Ins
Insert in tbl_emp(1,’Rose’);
.Logoff
.Exit
Explanation:
1. We login to BTEQ.
2. Deleting the records from tbl_employee table
3. If Delete operation is successful, then it will GO TO ins label.
4. ins will insert a record.
5. If Delete operation was not successful then logging off from bteq.
1. We login to BTEQ.
2. Deleting the records from tbl_employee table
3. If Delete operation is successful, then it will GO TO ins label.
4. ins will insert a record.
5. If Delete operation was not successful then logging off from bteq.
17) BTEQ - HANG - Put a pause:
You can put a pause while the BTEQ operation.
Syntax:
.Hang n
n - Seconds
Default value for n is 360 seconds.
Example:
.Logon localtd/dbc,dbc
Database Sri;
Delete from tbl_emp;
.Hang 180
Insert into tbl_emp( 1,’Rose’);
After deleting, BTEQ will wait for 3 minutes then it will insert.
18) BTEQ - SUPPRESS - Deleting Duplicated Data:
The BTEQ SUPPRESS command deletes duplicated data by replacing all consecutively repeating values with blank characters in your output reports.
Syntax:
.Set Suppress On column_no
Syntax:
.Set Suppress On column_no
Example: Suppose, tbl_employee contains following data
ID Name
100 Rafael
100 Green
100 Moore
100 Petar
300 Russel
300 Morkel
Script: .Logon localtd/dbc,dbc
.Set Suppress On 1;
Select *from tbl_emp;
.Logoff
.Exit
Output:
Output:
ID Name
100 Rafael
- Green
- Moore
- Petar
300 Russel
- Morkel
19) BTEQ - Inserting blank lines:
SKIPLINE will add blankline on your columns.
Syntax:
Syntax:
.SET SKIPLINE ON Column_no;
Example: Suppose, tbl_employee contains following data
ID Name
100 Rafael
100 Green
100 Moore
100 Petar
300 Russel
300 Morkel
Script:
.Logon localtd/dbc,dbc
.Logon localtd/dbc,dbc
.Set Skipline On 1;
Select *from tbl_emp;
.Logoff
.Exit
Output:
ID Name
100 Rafael
100 Green
100 Moore
100 Petar
- -
300 Russel
300 Morkel
.Set Skipline on 2
Then the output will be,
Then the output will be,
ID Name
100 Rafael
- -
100 Green
- -
100 Moore
- -
100 Petar
- -
300 Russel
- -
300 Morkel
20) BTEQ - Inserting a Line of Underline Characters:
Syntax:
.Set Underline On Column_no
.Set Underline On Column_no
Example:
Suppose, tbl_employee contains following data
ID Name
100 Rafael
100 Green
100 Moore
100 Petar
300 Russel
300 Morkel
Script:
.Logon Localtd/dbc,dbc
.Set Underline On 1;
Select *from tbl_emp;
.LogOff
.Exit
Output:
ID Name
100 Rafael
100 Green
100 Moore
100 Petar
----- -----
300 Russel
300 Morkel
Ex:2
.Set Underline on 2
Then the output will be,
ID Name
100 Rafael
---- ------
100 Green
----- -------
100 Moore
----- ------
100 Petar
----- -----
300 Russel
------ ------
300 Morkel
21) Entering DOS Commands:
Under MS-DOS, use the DOS command COMMAND to exit from BTEQ and the EXIT command to return to BTEQ.
Example:
The following example is a series of DOS commands entered from within a BTEQ session:
Example:
The following example is a series of DOS commands entered from within a BTEQ session:
.OS command
<in new cmd window>
C:\Docume~1\Locals~1\temp> Date
The current date is : Thu 11/15/2012
Enter the new date : (mm-dd-yy)
22) Changing the screen width:
Width specifications is from 20 to 254 characters.
The default screen width is 75 characters.
Example:
.SET WIDTH 100
If width exceeds 254 characters then the following error message will be encountered.
Note: ***Error width out of legal range of 20 to 254
23) Representing Null Values
In BTEQ, you can represent NULL values with a suitable string.
Syntax:
.SET NULL As ‘String’;
Example: To Represent Null as ‘___’ then
.Set Null As ‘___’;
24) ECHOREQ-Echo Required function:
By default, the echo required function (ECHOREQ) is enabled in BTEQ.
This returns an exact copy every BTEQ command and Teradata SQL statement to the standard output stream.
If you want to disable this option then,
.Set ECHOREQ OFF
Note: The BTEQ ECHOREQ command is not related to the Teradata SQL ECHO statement.
25) PageLength:
It determines how many lines must be there in the printed page of output.
FORMAT must be on to get the effect of PAGELENGTH.
FORMAT must be on to get the effect of PAGELENGTH.
Syntax: .SET PAGELENGTH N (Default value is 55)
Example: .Logon 127.0.0.1/tduser,pwd;
.Export file = C:\tx\lines.txt;
.Set Format On;
.Heading ‘Top’;
.Footing ‘Bottom’;
.Set PageLength 20;
Sel *from sri.tbl_emp;
.Format off;
.Logoff;
.Exit;
Please remember that PAGELENGTH will even suppress the output according to the length specified.
If the above select retrieves 30 rows, then in output you can see less than 30 rows.
If the above select retrieves 30 rows, then in output you can see less than 30 rows.
26) FULLYEAR-Setting year in YYYY Format:
To enable a four-digit year format to comply with the year 2000 format, enter the FULLYEAR command as follows:
.SET FULLYEAR ON;
Example:
.Logon 127.0.0.1/tduser,pwd;
.Set FullYear On;
Sel *ftom tbl_emp;
.Logoff;
FULLYEAR will affect only the titles, headings and footings.
It won't affect the outcome of SQL statements.
It won't affect the outcome of SQL statements.
27) Compile:
It defines a stored procedure object.
The SPL (Stored procedure language) file only can be supplied as input to the COMPILE command.
If no syntax errors were encountered, then the particular structure will be stored in the database.
In an open transaction, you can not render directly the CREATE and REPLACE procedure commands, only the SPL file is allowed for the operation.
Example:
The SPL (Stored procedure language) file only can be supplied as input to the COMPILE command.
If no syntax errors were encountered, then the particular structure will be stored in the database.
In an open transaction, you can not render directly the CREATE and REPLACE procedure commands, only the SPL file is allowed for the operation.
Example:
Usage: .Set session trans BTET
.Logon 127.0.0.1/tduser,pwd
BT;
Select Date;
.Compile File spSample1
ET;
In a Non transaction mode, COMMIT must be supplied.
.Set session trans ANSI
.Logon 127.0.0.1/tduser,pwd
Select Date;
.Compile File spSample1
Commit;
Wrong usage (Compile must be the last statement in a transaction)
.Set session trans BTET
.Logon 127.0.0.1/tduser,pwd
BT;
.Compile File spSample1
Select Date;
ET;
28) Default:
It will reset BTEQ command options to the values that were set when BTEQ was first invoked.
To reset the BTEQ format commands to their default values, enter the DEFAULTS command as follows:
Syntax:
.SET Defaults
.SET Defaults
Exmaple:
.Logon 127.0.0.1/Tduser,pwd
.SET Defaults;
Once you execute the Default command, the following parameters will be reset.
Please refer the table below.
Please refer the table below.
Command | Value |
ECHOREQ | ON |
EXPORT | RESET |
FOLDLINE | OFF ALL |
FOOTING | NULL |
FORMAT | OFF ON |
FORMCHAR | OFF |
HEAGING | NULL |
INDICDATA | OFF |
LOGONPROMPT | ON |
OMIT | OFF ALL |
PAGEBREAK | OFF ALL |
PAGELENGTH | 55 |
QUIET | OFF |
RECORDMODE | OFF |
RETCANCEL | OFF |
RETLIMIT | NO LIMIT |
RETRY | ON |
SKIPDOUBLE | OFF ALL |
SKIPLINE | OFF ALL |
SUPPRESS | OFF ALL |
TITLEDASHES | ON |
UNDERLINE | OFF ALL |
WIDTH | 75 CHARACTERS |
29) Remark:
REMARK places a string in the screen to intimate information.
Syntax:
.Remark string
Example:
.Logon 127.0.0.1/tduser,pwd;
.Remark ‘This is a line of commentary//
And another line too’
.Remark ‘This is a line //of commentary//
And another line//too’
.Logoff
Output:
This is a line of commentary
And another line too
This is a line
Of commentary
And another line
30) Break a line to have sections:
You can break a line to have two sections using some control characters of BTEQ.
solid vertical bars (||) will break a line into two sections.
solid vertical bars (||) will break a line into two sections.
Example:
.Logon 127.0.0.1/tduser,pwd
.Remark ‘I am coming|| from teradatablog’;
.Logoff;
Output:
I am coming from teradatablog
31) TIMEMSG Command:
It will enable you to print the total time of a specified request.
Syntax:
.Set TIMEMSG Query
In a Teradata Sql macro appears as:
ECHO ‘.Set TIMEMSG Query’;
Example:
.Logon 127.0.0.1/tduser,pwd
Select *from sri.tbl_emp;
.Logoff
Output: ***Total Query time was 1 second.
Please note that total query time is the output of TIMEMSG command and it is different from Total elapsed time.
32) Continuing BTEQ Commands:
You can continue a BTEQ command from one line to the next by including a dash character (-) as the last character of the line.
Example:
.SET HEADING ‘Actully –
I am coming from –
Narasarao peta’
Result:
Actully I am coming from Narasarao peta
33) ERROROUT:
BTEQ routes the standard error stream and the standard output stream to two files.
Syntax:
.Set Errorout Stdout;
.Set Errorout Stderr;
STDOUT: Channel attached systems
STDERR: Network attached systems
Example:
BTEQ- Enter your Teradata Sql request or BTEQ Command:
.Set Errorout Stderr
.Set Errorout Stderr
*** Error Messages now directed to STDERR.
34) INDICDATA:
INDICDATA specifies the mode of information returned from the Teradata RDBMS in response to SQL SELECT statements.
For example, if a user wants the data from Teradata RDBMS as in native format then INDICDATA can be used.
Syntax:
For example, if a user wants the data from Teradata RDBMS as in native format then INDICDATA can be used.
Syntax:
.SET INDICDATA ON|OFF;
Example: .Database sri;
.Set Defaults
.Set Indicdata on
Select empno,proj_id from tbl_emp
Where empno=101
Order by proj_id;
.Indicdata off
=1
.Defaults
.Format off
.Logoff
.Exit
Response:
Betq Returns the following response:
***New default database accepted.
***Success,Stmt# 1 ActivityCount=2
***Query completed. 2 rows found. 2 Columns Returned.
***Dump of parcel DATAINFO:0000 0002 01F4 0002
01C4 0008*…4…D…*
***Record#1. Dump of data: 0000 0027 14C5 D5C7
60F0 F0F0 F2* …Eng-0002*
***Record#2. Dump of data: 0000 0027 14C5 D5C7
60F0 F0F0 F3* …Eng-0003*
***Query Complted. 2 rows found. 2 columns returned.
Employee Project:
Id Id
101 Eng-0002
101 Eng-0003
35) FOLDLINE:
Splits each line of a report into two or more lines after the specified columns. This compresses the report into a smaller width.
Syntax:
Syntax:
.SET FOLDLINE ON 1,2,3(Columns Position);
Example:
.Set Width 40;
.Set Suppress On 1,2;
.Set Foldline on 2;
Sel E.empno ,E.ename,C.wkend(Format ‘Mmm-dd’),C.hours,P.description,
From employee E,Project P,Charges C
Where E.empno=C.empno And C.Proj_id=P.Proj_id
And E.Deptno=500 Order by 1,3;
Output:
The Ouput will be folded after the second column.
36) Specifying SideTitles
In BTEQ, you can specify the side title by triggering on the SIDETITLES.
Syntax:
.Set SideTitles ON
Example:
.Set Foldline On All
.Set SideTitles ON
Select id,name,sex from employee;
Output:
Id 1001
Name Russel S
Sex M
Id 1002
Name Mark
Sex M
37)Exporting Under VM:
Under VM on a channel-attached system, export command in BTEQ can be used like this.
Example:
Example:
.Logon 127.0.0.1/Userid,Pwd
.cms filedef reportout disk reportout
.export report ddname=reportout
.set rtitle ‘Department Information’
.Set Format on
Execute Deptdisplay;
.Set Format off
.Export reset
.Logoff
38) Giving Comments:
BTEQ comments can be given between *( asterisk).
Syntax:
Syntax:
*Comment_txt* |<cr> |<lf>
CR- Carriage Return
LF- Line Feed
Example:
*coming from forget code*
To Enter Multiple line BTEQ Comments,
*Comment Line 1 <Cr>
*Comment Line 2 <Cr>
*Comment Line 3 <Cr>
39) Identifying input and output file undes MS-DOS in Teradata:
Specifying input:
If your workstation is running MS-DOS/PC-DOS or UNIX, invoke BTEQ and specify sampfile as the input file, as follows:
BTEQ<Sample_file
Specifying output:
To also create a new output file named log.out, enter:
BTEQ<Sample_file> Log.Out
40) FORMCHAR:
The command governs format control for printing reports.
The command has four options: ON, OFF, DEFAULT or any HEX.
Use the FORMCHAR command when you need to specify form feed characters for printing reports.
For the FORMCHAR command to be effective, you must first set the FORMAT command option to ON.
Syntax:
.Set Format ON
.Set Formchar ON
You can enter the command for HEX as follows:
.Set Format ON
.Set Formchar “0C”xb
41) OMIT:
It will exclude specified columns returned from SQL SELECT statements.
Syntax:
.SET OMIT ON n
n- no of columns to be omitted ordered from left to right, and separated by a comma or one space.
Omitting all columns:
.set format on
.set defaults
.set omit off
.set omit on all
Omitting second column from left:
.set defaults
.set format on
.set omit off
.set omit on 2
42) OS in Teradata:
It will submit an operating system command to the network-attached system.
Syntax:
.OS<Command>
The Command must be system specific.
The command will very for Operating Systems.
Example:
.OS ls
In Unix Systems, It will list the directory.
43) RETRY:
It will resubmits requests that fail under certain operational error conditions.
Syntax: .Set Retry ON|OFF
When Set to ON, the RETRY Command Option takes effect when BTEQ detects any of the following failure codes:
Details:
.2631 –Transaction aborted due to %VSTR.
.2639 – Too many simultaneous transactions.
.2641 -- %DBID. %TVMID was restructured. Resubmit.
.2825 - No Record of the last request was found after RDBMS restart
.2827 – Request was aborted by user or due to statement error.
.2828 – Request was rolled back during system recovery.
.2835 -A unique index has been Invalidated; resubmit request.
.3111 – The Dispatcher has timed out the transaction.
.3120 – The request is aborted because of a RDBMS Restart.
.3598 – Concurrent change Conflict in Database-Try again
.3603 – Concurrent Change Conflict in table –Try again
.3897-- Request aborted due to RDBMS restart. Resubmit.
44) SESSION TRANSACTION:
It specifies whether transaction boundaries are determined by Teradata SQL semantics or ANSI standards.
Syntax: .SET SESSION TRANSACTION BTET;
.SET SESSION TRANSACTION ANSI;
BTET – Teradata Mode
ANSI – ANSI Mode
BTEQ Response:
Unless Bteq encounters an error condition, there is no display response to the SESSION TRANSACTION Command
45) SKIP LINE AND SKIP DOUBLE:
During displaying the result table, if the value in column 1 changes, skip one blank line to display the next row. If the value in column 3 changes, skip two blank lines to display the next row.
Example:
.Logon 127.0.0.1/Sri
Password:*****
.Set SkipLine On 1
.Set Skipdouble on 3
Select *from employee;
46) Show Controles:
.Logon 127.0.0.1/Sri
.Password:*****
.show controls
.show control
The above command will show the current settings of BTEQ.
Displays all of the BTEQ control command options currently configured.
47) SYSIN And SYSOUT:
SYSIN and SYSOUT are standard input and output streams of BTEQ. You can redirect them as the following example:
Start à Programsà Accessoriesà Command Prompt
C:\> cd c:\program files\ncr\teradata client\bin
C:\ program files\ncr\teradata client\bin> betq> result.txt
.logon teradata/forgetcode
******
Select *from students;
.exit
C:\program files\ncr\teradata client\bin> bteq>
In the above example, all output will be written into result.txt file but not to the terminal screen. If runfile.txt file is placed in the root directory C:\, we can redirect the standard input stream of BTEQ as the following example:
C:\>cd C:\program files\ncr\teradata client\bin
C:\program files\ncr\teradata client\bin> bteq<C:\runfile.txt
48) MACRO:
You can use the SQL statements to create a macro and execute this macro at any time. See the following example:
Create macro MyMacro1 as(
ECHO ‘.set separator “#”’
; sel *from students;
);
This macro executes one BTEQ command and one SQL request.
Execute MyMacro1;
This Sql statement executes the Macro
49) IF – ELSE Command:
In BTEQ, If else condition is being used to check the error code and activity counts.
Examples:
Checking Error code::
IF ERRORCODE > 0 Then .GOTO Label1
Else
.EXPORT DATA FILE= File_name
Checking for Activity Count:
Sql Statement
.IF ActivityCount = 0 Then .GOTO Label1
Else
.Export DATAFILE= File_Name
50) BTEQ- OS Under MS-DOS:
For MS-DOS/PC-DOS, use the DOS command COMMAND to exit from BTEQ and the EXIT command to return to BTEQ.
Sample: .OS Command
C>type b:myfile.one
C>edlin b:myfile.one
C> dir a:
C> exit
51) RETCANCEL:
The RETCANCEL command, used in conjunction with the RETLIMIT command, cancels a request when the value specified by the RETLIMIT command is exceeded.
Syntax: .Set RETCANCEL ON|OFF
The following example shows the RETCANCEL Command is used in conjuction with several RETLIMIT Commands:
.set Retcancel
.set Retlimit 1000
.set Retlimit 100000000
***Warning: Number too large
***Warning: Extra text Found after command.
The text ‘00’ not Processed(ignored)
52) Multiple statement Processing:
BTEQ will execute the commands to the Teradata RDBMS by watching where the semicolons used in the requested query
Example:
Insert tbl_emp(‘sssssssssss’,123,111)
; Insert tbl_emp(‘sssssssssss’,123,111)
; Insert tbl_emp(‘sssssssssss’,123,111)
The above example illustrates the multiple statement processing.
Since the semicolon is put at the beginning of each statement, BTEQ will execute all the statements at once.
BTEQ submits the above statements to the Teradata RDBMS as single statement request since it starts to submit the SQL only after seeing the semicolon at the third statement.
53) Giving Comments On In ANSI Mode:
In ANSI mode, The comments will be given like below
--ANSI Comment example text
For Multiple line Comments,
--ANSI Comment Example Text <CR>
--ANSI Comment Example Text <CR>
CRÃ Carriage Return
54)To Execute the given SQL Statement Repeatedly
Syntax:
Select 1* from database.Table1;
=n
Here “=” is to run the previous sql statement, “n” number of times.
Example:
.Logon Localtd/dbc,dbc
Select top 1* from emp.employee;
=4
The above query will run for four times.
55) REPEAT in ANSI Mode:
In ANSI mode, a session opens its transaction by its first SQL statement. The transaction is closed completely by sending either a COMMIT or ROLLBACK statement on the session. If the .SESSION and .REPEAT commands are used in ANSI mode, the COMMIT statement has to be sent along with the repeated SQL statement as one request.
Example:
.SESSION trans Ansi
.SESSIONS 10
.Logon TPID/Userid,Pwd
.Import datafile = <data file name>
.Repeat *
Using i(integer),J(integer)
Insert into <table name> (col1 ,col2)
Values(:I,:j); Commit Work;
.Quit
If the repeated request is sent without the COMMIT statement, sooner or later, one of the requests will be blocked by other sessions, and gradually the job will hang due to a deadlock.
56) Repeat Command:
When multiple statements have to be rendered against a database, the REPEAT command will be useful.
For example, if you want to update the salary for multiple employees like below in a BTEQ script,
For example, if you want to update the salary for multiple employees like below in a BTEQ script,
Update tbl_emp SET Salary=2*salary where emp_no=1111;
Update tbl_emp SET Salary=2*salary where emp_no=1121;
Update tbl_emp SET Salary=2*salary where emp_no=1411;
It will be tedious when multiple statements are present in a script.
BTEQ offers a solution for this problem by giving IMPORT, REPEAT (=) options.
Instead of specifying each statement let us make a single statement, and we will store the employee numbers in a file.
For each employee number in a file the update will happen.
Syntax: .Import data file =filename;
BTEQ offers a solution for this problem by giving IMPORT, REPEAT (=) options.
Instead of specifying each statement let us make a single statement, and we will store the employee numbers in a file.
For each employee number in a file the update will happen.
Syntax: .Import data file =filename;
.Repeat n;
Using Variable (datatype)
Sql Statement which uses variable from filename;
Repeat specifies the total number of requests to be submitted.
In our example we want to update the salary for the following employees:
In our example we want to update the salary for the following employees:
Sample.txt 1111
1121
1411
Example:
.Logon Localtd/dbc,dbc;
.Import Data file= Sample.txt;
.Repeat 3
Using empno(char(4))
Upadte tbl_emp SET sal=2*salary where emp_no= :empno;
The above code will open the sample.txt file and update the salary for the employee numbers present in the file.
57) REPEAT With =(Equivalent):
We can use the = Command When Importing a File instead of REPEAT.
The following codes are absolutely same.
1. With REPEAT
Example: .Logon Localtd/dbc,dbc;
.Import data file= sample.txt;
.Repeat 3
Using empno (char(4))
Update tbl_emp Set salary=2*salary where emp_no=:empno
2) With ‘=’
.Logon Localtd/dbc,dbc;
.Import data file= sample.txt;
Using empno (char(4))
Update tbl_emp Set salary=2*salary where emp_no=:empno;
=2
Please note that, the number you want to supply with '=' must be n-1 while in REPEAT n.
.Repeat 7
Or
=6
This is because, '=' symbol already start its operation once it is encountered in the script.
So, it will continue the next repeat operations after performing a repeat operation always.
58) Repeat Command with Multiple Sessions:
The following code illustrates the usage of REPEAT with multiple sessions
.Set Sessions 2
.Repeat 2
Database Sri;
.Import data file= Sample.txt
.Repeat 2
Using eid(char(2))
Update tbl_emp set salary = salary*1.07 where empno=:eid;
The first repeat command will assign the database forgetcode for the both sessions.
The second repeat command will do the update operation by reading the inputs from the sample.txt file.
The interesting feature here is BTEQ automatically assign the input values to the sessions accordingly.
In our example, the second repeat statement REPEAT 2 will assign a update statement for each session (We used 2 here).
59) RepeatStop:
REPEATSTOP (when ON) causes an entire REPEAT operation to be terminated if a non-retryable error occurs. If REPEATSTOP is OFF, REPEAT operations are not aborted.
Syntax: .Set Repeatstop ON|OFF;
ON - BTEQ terminates the entire REPEAT operation if a nonretryable error occurs
OFF - If a non-retryable error occurs, BTEQ rolls back to the previous BEGIN statement, and continues inserting records from the point where the error occurred.
60) How To Break a Line:
You can break a line by using some control characters of BTEQ.
double slashes (//) will break a line and add a newline)
Example:
.Logon tdid/user,pwd;
.Remark ‘I am coming//from forget code’;
.Logoff;
Output:
I am coming
From forget code
61) Exporting Under MS-Dos,Unix:
Under MS-DOS/PC-DOS or UNIX on a network-attached system, the export command will work like this:
Example: .Logon Tdpid/userid,pwd
.Export Report file = Report.out
.Set rtitle ‘Department Information’
.Set Format On
Execute DeptDisplay;
.Set Format Off
.Export Reset
.Logoff
.Exit
62) Listing Example On Various Platforms:
Using BTEQ OS command, you can render the OS specific commands.
Use the following OS commands to display the files in your directory:
• For UNIX : .OS ls
• For MS-DOS/PC-DOS: .OS DIR
• For VMS: .OS DIRECTORY Or .OS DIR
63) RETLIMIT:
