Starting teradata database service in linux(vm)

For Teradata database express edition  installed on Suse linux, we can check the database service status by
command "pdestate".

# pdestate -a
PDE state is START/TVSASTART.  --which indicates database service is up and running.
or
PDE state is DOWN/HARDSTOP.   --- indicates database service is down.

we can start or stop the database service from /etc/init.d as

s10-1310:/etc/init.d # service tpa start/stop.

Note: once we start the database service (tpa), this may take few minute to start and enable logons etc.

Troubleshooting

If you are running into problems getting Teradata started, the first place to check for clues is in the log file:

#tail /var/log/messages

And finally, to check your storage, use the verify_pdisks command:

# verify_pdisks All pdisks on this node verified.

You may see some warning messages with this, but what we're looking for is the final 'verified' message.

Release Hut-Lock in Teradata database

Hut-Lock is placed on table or database by Teradata where there is archive/restore operation is being performed on table/database, ideally the archive/resotre/copy script should include “release lock” command at the end of script to release lock once the operation is complete. But in case if backup script did not mention “release lock” clause or backup job failed with abnormal termination the lock is not release automatically.

In this case we need to release lock manually; we can release Hut- lock in two ways 1. By using arcmain  or by using SQL Assistant/bteq.

Using arcmain to release lock.

Using command prompt or Linux prompt, enter below command.

      > arcmain

         Logon serverip/arcuser1,arcuserpassword;

         Release lock (databasename.tablename);       à to release table level lock.

  Or

         Release lock (databasename);         à to release database level lock.

         Logoff;

Note: In both the cases Teradata expect to use same user (arcuser1) which has put Hut-Lock, if not you can use super                    user with “override” option.

             

              i.e. Release lock (databasename.tablename), override;

Using Sql Assistant/bteq to release lock.

        1. Logon to SQL Assistant using same arcuser1, which accuire lock.

        2.   release lock(databasename.tablename);

Hut-Lock

Host Utility (HUT) Lock is a lock that Teradata ARC utility places when ARC command like ARCHIVE/RESTORE etc. are executed on database or objects with below condition apply.

     1. HUT locks are associated with the currently logged-on user who entered the statement,

         not with a job or transaction.

     2. HUT locks are placed only on the AMPs that are participating in a Teradata ARC

         operation.

     3. A HUT lock that is placed for a user on an object at one level never conflicts with another

         level of lock on the same object for the same user.

We can check the Hut-lock in Teradata database using the ShowLocks Console utility.

Teradata constraints type

Teradata has various types of explicit or implicit constraints to support object relationship and Teradata specific data validation constraints.

The type of table-level check constraint or partitioning constraint (formerly referred to as indexes).

constraints  types:

 C = Explicit table-level constraint check

 P = Non-partitioned Primary Index

 Q = Partitioning constraint

 S = Hash-Ordered Secondary Index without ALL

 K = Primary Key

 U = Unique constraint

 R = References constraint

 V = Value-Ordered Secondary Index without ALL

 H = Hash-Ordered Secondary Index with ALL

 O = Value-Ordered Secondary with ALL

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.

TERADATA Mode	ANSI Mode
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 teradata.net provider to connect , 

BTEQ Help

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.




DEFINITION

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]



OVERVIEW

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

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
      data;
    * 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 -
      (Rankings);
    * QUANTILE
      - (Quantiles);
    * CSUM -
      (Cumulation);
    * MAVG -
      (Moving Averages);
    * MSUM -
      (Moving Sums);
    * MDIFF
      - (Moving Differences);
    * MLINREG
      - (Moving Linear Regression);
    * ROLLUP
      - (One Dimension of Group);
    * CUBE -
      (All Dimensions of Group);
    * GROUPING
      SETS - (Restrict Group);
    * GROUPING
      - (Distinguish NULL rows).


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



OPERATING FEATURES

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.

Datatype short forms in Teradata

When we help table in Teradata, datatype is represented in short forms. below is list of datatype and it respective short forms.

• AT = TIME

• BF = BYTE

• BO = BLOB

• BV = VARBYTE

• CF = CHAR

• CO = CLOB

• CV = VARCHAR

• D = DECIMAL

• DA = DATE

• DH = INTERVAL DAY TO HOUR

• DM = INTERVAL DAY TO MINUTE

• DS = INTERVAL DAY TO SECOND

• DY = INTERVAL DAY

• F = FLOAT

• HM = INTERVAL HOUR TO MINUTE

• HR = INTERVAL HOUR

• HS = INTERVAL HOUR TO SECOND

• I1 = BYTEINT

• I2 = SMALLINT

• I8 = BIGINT

• I = INTEGER

• MI = INTERVAL MINUTE

• MO = INTERVAL MONTH

• MS = INTERVAL

• MINUTE TO SECOND

• PD = PERIOD(DATE)

• PM = PERIOD

(TIMESTAMP(n) WITH TIME

ZONE)

• PS = PERIOD

(TIMESTAMP(n))

• PT = PERIOD(TIME(n))

• PZ = PERIOD(TIME(n) WITH

TIME ZONE)

• SC = INTERVAL SECOND

• SZ = TIMESTAMP WITH

TIME ZONE

• TS = TIMESTAMP

• TZ = TIME WITH TIME

ZONE

• YM = INTERVAL YEAR TO

MONTH

• YR = INTERVAL YEAR

• UT = UDT Type