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:
0 Comments