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

.EXIT                    Or.       QUIT

BTEQ displays the exit message and returns you to your client system prompt. 
*** 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: 

      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:

.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

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;
      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:
      .Import infile =<filename>, Skip=1;

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.
 

             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. 
14) Single statement processing:

BTEQ will execute the commands to the Teradata RDBMS by watching where the semicolons used in the requested query.

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.
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:
.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.



16) GOTO – Redirecting the Control:

GOTO redirect the control to the label associated.

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.

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

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:
ID  Name
                  100      Rafael
                    -         Green
                    -         Moore
                    -         Petar
                  300      Russel
                    -         Morkel

19) BTEQ - Inserting blank lines:
SKIPLINE will add blankline on your columns.

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

                              .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,
                  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

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:
                  .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
.

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
.

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
. 
 
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: 
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
      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
.

                        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
.
      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: 
                        .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:
      .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: 
.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: 
        *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,
 
      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;
     .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
: 
    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:

      It specifies the maximum number of rows displayed on the terminal or written to a file in response to a Teradata SQL statement. 

Syntax:      .Set RetLimit    ON|OFF

      The RETLIMIT command applies to each Teradata SQL statement, not to Teradata SQL requests. Therefore, if a Teradata SQL request contains several Teradata SQL statements, BTEQ processes the specified number of rows for each statement. 

Example:

      .LogOn  Tdpid/Username,pwd;

      .Set  RetLimit  5;

      .Select *from Tbl_emp;

The above command will show only 5 records although all records are specified in SELECT.