Sunday, October 27, 2013

Teradata session mode

Teradata support both ANSI and Teradata session mode while connecting to database, the main difference between the two modes are as listed.

Comparisons are not case specific Comparisons are  case specific
ALLOWS Truncation of Display data No Truncation of Display data allowed
CREATE TABLE default to SET tables CREATE TABLE default to MULTISET tables
Each Transaction is IMPLICIT automatically Each Transaction is IMPLICIT automatically
Have to specify BT/ET explicitly Do not have to specify BT/ET explicitly

The current mode of session can be checked by "help session" query in the result set "Transaction Semantics" column represent the Session mode. or alternatively we can query dbc.SessionInfoV view as below .

SELECT transaction_mode FROM dbc.SessionInfoV WHERE SessionNo = SESSION;

we can also set the session mode as per requirement. for bteq we can simply execute below command before .logon

.set session transation BTET; ----> for teradata mode 
.set session transation ANSI;   ---> for ansi mode.

for "sql assistant" if we are connecting using odbc driver we have to set odbc connection as below.

if we are using provider to connect , 

Tuesday, October 15, 2013


Introduction to teradata utilities BTEQ

BTEQ is a Teradata native query tool for DBA and programmers. BTEQ (Basic
TEradata Query) is a command-driven utility used to 1) access and manipulate
data, and 2) format reports for both print and screen output.


BTEQ, short for Basic TEradata Query,
is a general-purpose command-driven utility used to access and manipulate data
on the Teradata Database, and format reports for both print and screen output. [1]


As part of the Teradata Tools and Utilities (TTU), BTEQ is a
Teradata native query tool for DBA and programmers — a real Teradata workhorse,
just like SQLPlus for the Oracle Database. It enables users on a workstation to
easily access one or more Teradata Database systems for ad hoc queries, report
generation, data movement (suitable for small volumes) and database

All database requests in BTEQ are expressed in Teradata
Structured Query Language (Teradata SQL). You can use Teradata SQL statements
in BTEQ to:

    * Define
      data — create and modify data structures;
    * Select
      data — query a database;
    * Manipulate
      data — insert, delete, and update data;
    * Control
      data — define databases and users, establish access rights, and secure
    * Create
      Teradata SQL macros — store and execute sequences of Teradata SQL
      statements as a single operation.

BTEQ supports Teradata-specific SQL functions for doing
complex analytical querying and data mining, such as:

    * RANK -
      - (Quantiles);
    * CSUM -
    * MAVG -
      (Moving Averages);
    * MSUM -
      (Moving Sums);
    * MDIFF
      - (Moving Differences);
      - (Moving Linear Regression);
    * ROLLUP
      - (One Dimension of Group);
    * CUBE -
      (All Dimensions of Group);
      SETS - (Restrict Group);
      - (Distinguish NULL rows).

Noticeably, BTEQ supports the conditional logic (i.e.,
"IF..THEN..."). It is useful for batch mode export / import


This section is based on Teradata documentation for the
current release.[1]

BTEQ Sessions

In a BTEQ session, you can access a Teradata Database easily
and do the following:

    * enter
      Teradata SQL statements to view, add, modify, and delete data;
    * enter
      BTEQ commands;
    * enter
      operating system commands;
    * create
      and use Teradata stored procedures.