Xref_PDF

BTEQ:               Batch TEradata Query or  Basic TEradata Query

** BTEQ vs. SQL Assistant

BTEQ is similar to Teradata SQL Assistant in submitting queries to the Teradata Database and generating reports. Yet, they are different in the following aspects:

•BTEQ connects to the database by means of CLIv2 (Call-Level Interface Version 2), whereas SQL Assistant does so by means of ODBC;

•BTEQ works on Unix and Windows as well, whereas SQL Assistant is designed for Windows only;

•BTEQ can run in batch mode for data-manipulating and reporting routines, whereas SQL Assistant cannot;

•BTEQ is a Teradata specific application, and can not communicate with any other type of databases, whereas SQL Assistant can communicate with any ODBC-compliant databases;

•BTEQ supports conditional logic (i.e., "IF..THEN..."), whereas SQL Assistant does not;

•BTEQ's result sets can be more easily formatted to refined effects than SQL Assitant, because the latter applies its own formatting to the result sets.

•BTEQ's import / export functions are much more versatile, flexible and programmable than those of SQL Assistant;

•BTEQ is a command driven utility, whereas SQL Assistant is a GUI;

•BTEQ can do Import/Export across all platforms whereas SQL Assistant is only on PC;

•BTEQ is more oriented to IT professionals like DBA's and programmers, whereas SQL Assistant is more oriented to business users and casual data consumers.

==========================================

** BTEQ vs. FastExport and MultiLoad

BTEQ is similar to Teradata FastExport and Teradata MultiLoad in exporting and importing.

- Yet, BTEQ is not a utility designed for bulk data movement.

- Firstly, Teradata export and load utilities are fully parallel. BTEQ can import and export small volumes of data to and from the Teradata Database. 

- Secondly, FastExport and MultiLoad have full restart capability. This feature means that if a FastExport or MultiLoad job should be interrupted for some reason, it can be restarted again from the last checkpoint,  without having to start the job from the beginning. BTEQ does not support such features.

- Thirdly, MultiLoad can load multiple input files concurrently and work on up to five tables at a time, using multiple sessions. MultiLoad places a lock on the destination table(s) to prevent user queries from getting  inconsistent results before the data load or update is complete.

     In conclusion, for complex bulk-mode routines, FastExport and MultiLoad are preferred utilities; otherwise, BTEQ is a suitable choice.

==========================================

** Types of EXTRACT:

                a.Report* -          Sends results to a host file in field mode.Data set contains column headings and formatted data. Data is truncated if exceeds 254 (REPORT).     

                b.Data   -              Sends results to a host file in record mode.

                c. Indicdata -     Sends query results that contain indicator variables to a host file. Allows Host programs to deal with nulls. Returns the results of a query to a file using indicator variables to identify null values.

                d. DIF    -              Output converted to Data Interchange Format, used to transport data to various PC programs, such as Lotus 1-2-3.

               

Syntax:

                .EXPORT            a/b/c/d             FILE= filename (,LIMIT=n)

                                                                                LIMIT is same as RETLIMIT.

                                                n1: Is the maximum number of rows that Teradata Database can return to the specified export file. Because the BTEQ RETLIMIT command also sets this specification, the most recent LIMIT=n1 option or RETLIMIT command overrides the previous specification. The default value for n1=0, which represents no limit.

                .EXPORT            RESET

Sample Extract reports :

.logon 127.0.0.1/tduser,tduser

database samples;

.export data file=C:\TD\basic_BTEQ_res1a.txt

.set separator '|'                                              

                select * from emp9;

.export reset

.export report file=C:\TD\basic_BTEQ_res1b.txt

.set separator '~'

                select * from emp9;

.export reset

.logoff

ResB  :=> same result which is shown under TSA

Notes:

** Result is automatically appended,if the report file is already exists else newly created.

** We can metion the file either within the Quotes or without the Quotes.

** Should be no.of export report statements equals to no.of export reports,else it will create only one file.

** Extracted data in DATA/INDICDATA mode file can use at FastLoad,MLoad.

=============================================

** Types of IMPORT:

                a. Report -           Reads a host file in report mode.

                b. Data  -              Reads a host file in record mode.

                c. Indicdata

                d. Vartext           

Syntax:

                .IMPORT            a/b/c/d  ( '|' only for D)            FILE= filename (,SKIP=n)

                                                n: number of lines skipped from the beginning of the file before reading data.The value is zero (0) by default.

                .IMPORT            RESET

Sample Import reports :

.logon 127.0.0.1/tduser,tduser

drop table samples.empv;

create table samples.empv

(

eno                        varchar(5)

,ename                 varchar(5)

,deptno                varchar(5)

);

.import vartext '|' file=C:\TD\Import_empv_data.txt

.repeat *

using ( Inp_eno               varchar(5)

                ,Inp_ename        varchar(5)

                ,Inp_deptno       varchar(5) )

Insert into samples.empv

(

                eno

                ,ename

                ,deptno

)

values

(

                :Inp_eno

                ,:Inp_ename

                ,:Inp_deptno

);

=============================================

**Return codes:

RC          Description

00           Job completed with no errors.

02           User alert to log on to Teradata Database.

04           Warning error.

08           User error.

12           Severe internal error.

The default return code assignments for Teradata Database error messages also determine the internal BTEQ ERRORLEVEL value that you can use in subsequent testing and branching commands,

such as:

                SELECT * FROM SOMEWHERE;

                .IF ERRORLEVEL >= 14 THEN .QUIT 17;

You can also use the BTEQ ERRORLEVEL command to change the severity level associated with one or more error codes, as in the following example:

                .SET ERRORLEVEL 2168 SEVERITY 4,

                                (2173, 3342, 5262) SEVERITY 8

                .SET ERRORLEVEL UNKNOWN SEVERITY 16

Note: As shown, you can also use UNKNOWN as a wildcard error number to accommodate any error codes that are not otherwise defined.

Notes:

                - Error code is a value returned by BTEQ at the end of each query.

                - Return code is a value returned by Script after execution.

                - Error level is a BTEQ command used to assign severity to some error codes at the time of error handling     .

** MAXERROR

If a BTEQ script contains a Teradata SQL statement that produces an error severity level that is greater than the designated MAXERROR value, BTEQ immediately aborts that job.

Use the following MAXERROR command, for example, to terminate BTEQ whenever a

Teradata Database error that is assigned an error level of 08 or higher occurs:

                .SET MAXERROR 8

                                If you do not specify a MAXERROR value, BTEQ jobs execute until one of the following conditions occurs:

                • End-of-file for the primary command input file is encountered.

                • A QUIT command is processed.

** Horizontal display

.set width 800

.export report file=c:\TD\reports_horizn_res.txt

                select * from emp9;

.export reset

   Res:

        eno  ename                           deptno

-----------  -------------------------  -----------

         19  Vicky                                101

** Vertical display

                .set foldline on all

Res:

        eno

-----------

   ename

   -------------------------

        deptno

   -----------

         19

   Vicky

             101

** Vertical display Vth subtitles

.set sidetitles on

Res:

   eno       19

 ename  Vicky

deptno   101

** Stop the extraction process

                .set retlimit 3                    :=> Extracts only 3 records

** Rename the NULLs

                .set null as 'value'            :=> Displays value what u mentioned here at extraction report

** Repeat Query

                =n                                           :=> Execute the recent query based on n value.

                                                n=3        => will execute thrice

** Report Header

.set rtitle 'Department Information'

.set format on

                select * from emp9;

.set format off

               

Notes:

                ** Header will be displayed when u mentioned both RTITLE & FORMAT options. If any one is not present then report header is blank.

** Title Dashes

                .set titledashes ON;         :=> Dashes will display under the cols

                                                                                                eno                        ename                  dept      

                                                                                                ---------                 --------------         ----------

                .set titledashes OFF;       :=>                         eno                        ename                  dept      

Notes:

                ** Default is ON

                ** If you want to remove the titles then

                                .set titledashes OFF

                                                select eno (title '')

                                                                ,ename (title '')

                                                                ,deptno (title '')

                                                from emp9;

** Display the statistics after result of the query

                 select eno,salary_amount

                 from emp_tbl

                 with count(eno),sum(salary_amount);

                                                                                eno        salary_amount

                                                                                --------   --------------

                                                                                101        5000                    

                                                                                102        2000

                                                                                -------    ---------------

                                                                                2             7000

                - If you want to display some headers

                                                with sum(salary_amount) title 'Sum of Salary'

                                                                                                                                Sum of Salary   7000

SQL

** Union & Union All

                UNION                 :=>         Remove the duplicate rows

                UNION ALL        :=>         same as X-Join

** Case:

- CASE allows for conditional processing of returned rows.

- CASE returns a single result for each row processed.

- Each row is evaluated against each WHEN clause until a match is discovered.

- First match returns a result for that row.

- If no match, ELSE result is produced for that row.

                Syntax:

CASE value-expr

WHEN expr1 THEN result1

WHEN expr2 THEN result2

:

ELSE resultn

END

                Or

CASE

WHEN value-expr1 THEN result1

WHEN value-expr2 THEN result2

:

ELSE resultn

END

                Eg:

                                select * from samples.mrtg_tbl;

                accnt_no            seller_id

1,234,567,003  C2CE

                1,234,567,001  CERL

                1,234,567,002  RLC2

                1,234,567,000  C2CE

insert into mrtg_tmp_tbl

                                    select m.accnt_no

                                                , case m.seller_id

                                                            when 'C2CE' then 'S'

                                                            when 'RLC2' then 'R'

                                                             else 'O'

                                                 end

                                    from mrtg_tbl m;

                                   

select * from samples.mrtg_tmp_tbl;

accnt                    mark

1,234,567,003  S

1,234,567,001  O

1,234,567,002  R

1,234,567,000  S

** Transpose Rows to Cols

select * from stdnt;

sno        sname  sub       marks

3             c              T             24

3             c              M            24

1             a              T             20

1             a              M            22

2             b             T             22

2             b             M            22

****

select

                sno

                ,sname

                ,MAX(coalesce

                                ((Case sub

                                 when 'T'            then marks end),'')) as T

                ,MAX(coalesce

                                ((Case sub

                                 when 'M'           then marks end),'')) as M

from stdnt

group by 1,2

order by 1;                      

sno        sname          T              M        

1             a                       20           22

2             b                      22           22

3             c                       24           24

** Remove duplicate records

**** General Q:

create table d_msnu as tbl_msnu with data;

select * from d_msnu;

delete from d_msnu where col1 in

                ( select b.col1 from d_msnu b group by 1 having count(*) >1);

select * from d_msnu;

Result:

                Duplicate rows are completely deleted. Eg: 2 rows are having 111 as key then both 2 are deleted.

**** Terdata :

create SET table td_msnu as tbl_msnu with data;

select * from td_msnu;

Result:

                Duplicate rows are not allowed while copy.Hence result would be single row.

 Eg: 2 rows are having 111 as key then only 1 row is deleted and rest is displayed.

** Rank

-- displays top 5 salaries

select empno

                ,salary

                ,rank(salary) as "Rank"

from samples.emps

qualify rank(salary) <= 5

order by 2 desc;

-- Same as above, displays top 5 salaries

select empno

                ,salary

                ,rank(salary desc) as "Rank"

from samples.emps

qualify rank(salary) <= 5;

-- Same as above, displays top 5 salaries

select empno

                ,salary

                ,rank() Over (order by salary desc) as "Rank"

from samples.emps

qualify rank(salary) <= 5;

Notes:

                -The QUALIFY clause allows restriction of rows to be output in the final result.