Concepts in Bteq
Return Codes:
BTEQ return codes are two digit values that BTEQ will return to the client operating system as a result of any error code that occured in BTEQ session.
Some BTEQ return codes are as given below,
Return Codes & Description:
00 Ã Job completed with no errors.
02 Ã User alert to log on to the Teradata Database.
04 Ã Warining Error. 08 Ã User Error.
12 Ã Server Internal Error.
The return code is decided based on the error messages that BTEQ gets from the Teradata database.
Different Teradata database error codes will be assigned a specific return code value i
An Example for Script Structure:
lets say the user wants to update the address of an employee in Employee table and wishes to increase the salary twice.
Employee table resides in the database named Company.BTEQ script:
.Set Session Transaction Ansi
.Logon TDUSER/PWD
/*updating details of emp_no 101*/
Update Company.Employee SET Address=’Rome’ Where emp_no=101;
Update Company.Employee SET Salary=2*Salary Where emp_no=101;
.Quit
Exporting results to a file:
Beow is the syntax to export or write the output of Teradata SQL command to a local file by using EXPORT Bteq command.
Syntax:
.EXPORT FILE = <Local path>;
Example: .LOGON LocalTD/DBC,DBC;
.Export file = C:\TX\Out.txt;
.SET SEPERATOR ‘|’
Database DB_NAME;
Select *from Tbl_Emp;
.Logoff
.Exit
Result: The Select Stmt output is written into the file C:\TX\Out.txt
Each column content will be separated by ‘|’
Run File (Batch Mode):
To run batch of commands in BTEQ using the batch mode utility.
All required commands (both BTEQ and SQL) will be put in a file and the file will be supplied as input to BTEQ using RUN FILE command.
Syntax:
.RUN FILE = <Path of the Fire>
Example: Consider the content below present in a file C:\tx\sample.txt
1. .LOGON localtd/dbc,dbc
2. .EXPORT report file=C:\tx\s1.xls;
3. DATABASE forgetcode;
4. SELECT * FROM tbl_emp;
5. .EXPORT reset
6. .LOGOFF
7. .EXIT
In BTEQ, we can simply give the command as
.RUN FILE = C:\tx\sample.txt
Each Stmt of the file is executed and the output of select will be returned to the C:\tx\s1.xls
Creating and calling MACRO:
We can create or call a macro directly in a script.
After entering the login credentials EXECUTE,
now macro will be supplied.
We can call the macro from BTEQ, which was already created or can be dynamically create it in BTEQ script itself.
Syntax:
.Logon localTD/Tduser,Pwd
Execute Macro;
.Logoff
Example 1: Calling Macro which was already created,
.Logon localtd/tduser,pwd
Execute mc_employee;
.Logoff
Example 2) :In order to Create a macro dyanamically in the BTEQ script and calling it.
.Logon localtd/tduser,pwd;
Create Macro mc_emp as
(Echo ‘.set Separator “|” ‘
;Select *from tbl_employee;
);
Execute mc_emp;
.Logoff
Executing Stored Procedure:
call a stored procedure in a BTEQ script directly by rendering CALL command.
Syntax:
CALL Stored Procedure ([parameters]);
Example:
Calling the stored procedure sp_emp by passing the name and employee number.
.Logon Localtd/tduser/pwd;
CALL Sp_emp(‘Rufus’,12345);
.Logoff
.Exit
Script Structure:
To create and execute the BTEQ script in Teradata we have to follow the below steps -
Step 1: Create the BTEQ Script
To create and edit a BTEQ script we could use an text editor or client workstation.
.SET SESSION TRANSACTION ANSI
.LOGON TDUSER/TDPWD
<Query>
.QUIT
Step 2: Executing the script
Start BTEQ, now enter the below BTEQ command to submit a BTEQ script:
.RUN FILE = <Bteqscriptname>
Note: Comments can also be included in a BTEQ
/*Comments*/
ANSI style (--) can also be be used in any SQL command script
Creating Run File:
In BTEQ, commands could be put in a file and the file name will be given with RUN FILE command.
Syntax: .Run file =Filename Or
.Run DDNAME Filename;
Suppose, the below content is in a file C:\Sample.txt
.Logon Tdpid/ C:\Sample.txt Or
.Run DDNAME C:\Sample.txt
‘=’ is Optional
Return Codes:
BTEQ return codes are two digit values that BTEQ will return to the client operating system as a result of any error code that occured in BTEQ session.
Some BTEQ return codes are as given below,
Return Codes & Description:
00 Ã Job completed with no errors.
02 Ã User alert to log on to the Teradata Database.
04 Ã Warining Error. 08 Ã User Error.
12 Ã Server Internal Error.
The return code is decided based on the error messages that BTEQ gets from the Teradata database.
Different Teradata database error codes will be assigned a specific return code value i
An Example for Script Structure:
lets say the user wants to update the address of an employee in Employee table and wishes to increase the salary twice.
Employee table resides in the database named Company.BTEQ script:
.Set Session Transaction Ansi
.Logon TDUSER/PWD
/*updating details of emp_no 101*/
Update Company.Employee SET Address=’Rome’ Where emp_no=101;
Update Company.Employee SET Salary=2*Salary Where emp_no=101;
.Quit
Exporting results to a file:
Beow is the syntax to export or write the output of Teradata SQL command to a local file by using EXPORT Bteq command.
Syntax:
.EXPORT FILE = <Local path>;
Example: .LOGON LocalTD/DBC,DBC;
.Export file = C:\TX\Out.txt;
.SET SEPERATOR ‘|’
Database DB_NAME;
Select *from Tbl_Emp;
.Logoff
.Exit
Result: The Select Stmt output is written into the file C:\TX\Out.txt
Each column content will be separated by ‘|’
Run File (Batch Mode):
To run batch of commands in BTEQ using the batch mode utility.
All required commands (both BTEQ and SQL) will be put in a file and the file will be supplied as input to BTEQ using RUN FILE command.
Syntax:
.RUN FILE = <Path of the Fire>
Example: Consider the content below present in a file C:\tx\sample.txt
1. .LOGON localtd/dbc,dbc
2. .EXPORT report file=C:\tx\s1.xls;
3. DATABASE forgetcode;
4. SELECT * FROM tbl_emp;
5. .EXPORT reset
6. .LOGOFF
7. .EXIT
In BTEQ, we can simply give the command as
.RUN FILE = C:\tx\sample.txt
Each Stmt of the file is executed and the output of select will be returned to the C:\tx\s1.xls
Creating and calling MACRO:
We can create or call a macro directly in a script.
After entering the login credentials EXECUTE,
now macro will be supplied.
We can call the macro from BTEQ, which was already created or can be dynamically create it in BTEQ script itself.
Syntax:
.Logon localTD/Tduser,Pwd
Execute Macro;
.Logoff
Example 1: Calling Macro which was already created,
.Logon localtd/tduser,pwd
Execute mc_employee;
.Logoff
Example 2) :In order to Create a macro dyanamically in the BTEQ script and calling it.
.Logon localtd/tduser,pwd;
Create Macro mc_emp as
(Echo ‘.set Separator “|” ‘
;Select *from tbl_employee;
);
Execute mc_emp;
.Logoff
Executing Stored Procedure:
call a stored procedure in a BTEQ script directly by rendering CALL command.
Syntax:
CALL Stored Procedure ([parameters]);
Example:
Calling the stored procedure sp_emp by passing the name and employee number.
.Logon Localtd/tduser/pwd;
CALL Sp_emp(‘Rufus’,12345);
.Logoff
.Exit
Script Structure:
To create and execute the BTEQ script in Teradata we have to follow the below steps -
Step 1: Create the BTEQ Script
To create and edit a BTEQ script we could use an text editor or client workstation.
.SET SESSION TRANSACTION ANSI
.LOGON TDUSER/TDPWD
<Query>
.QUIT
Step 2: Executing the script
Start BTEQ, now enter the below BTEQ command to submit a BTEQ script:
.RUN FILE = <Bteqscriptname>
Note: Comments can also be included in a BTEQ
/*Comments*/
ANSI style (--) can also be be used in any SQL command script
Creating Run File:
In BTEQ, commands could be put in a file and the file name will be given with RUN FILE command.
Syntax: .Run file =Filename Or
.Run DDNAME Filename;
Suppose, the below content is in a file C:\Sample.txt
.Logon Tdpid/ C:\Sample.txt Or
.Run DDNAME C:\Sample.txt
‘=’ is Optional
0 Comments