Query to find out biggest table in teradata

Here is query you can find out biggest table in the system. top clause can be modified to display top 10,100 biggest tables in the system.

select top 1 databasename, tablename ,sum(currentperm) from dbc.tablesize  group by 1,2 order by 3 desc;                                                                                                                                          

How to set timezone in Teradata?

Timezone can be set in Teradata in System level, User level or in Session level. to set time zone in system level you need to modify dbs control parameter.

Timezone can be set at user level while creating user as

Create user user name TIME ZONE=LOCAL (or TIME ZONE='5:30',TIME ZONE=NULL).


At session level it can be set by set command. to set timezone to local (i.e. Current System's Timezone) you can issue in current session.

SET TIME ZONE LOCAL

to set time zone to a specific timezone issue below command.


SET TIME ZONE INTERVAL '05:00' HOUR TO MINUTE

What is queue table in Teradata?

Queue Tables feature simplifies the implementation of asynchronous, event-driven applications. Performance is improved by saving CPU and network resources.Queue tables possess all the properties
of regular Teradata Database tables:persistence, scalability, parallelism, ease of use, reliability, availability  recovery, and security. In addition, queue tables possess FIFO queue properties such as push, pop, and peek queue operations.

 These properties allow queue tables to provide flexibility,power, functionality, and leverage the natural performance characteristic of the Teradata Database. With queue tables, the Teradata Database
provides the capability to run real-time,event-driven active data warehouse activity while still having access to historical data providing the capability to make more effective business decisions in realtime
processing environments.


The queue table definition is different from a standard base table in that a queue table always contains a user-defined insertion timestamp (QITS) as the first column of the table. The QITS contains the time the row was inserted into the queue table as the means for approximate FIFO ordering.

Queue properties are implemented as following.

1. The FIFO push operation is defined as a SQL INSERT operation to store rows into a queue table.
2. The FIFO peek operation is defined as an SQL SELECT operation to retrieve rows from a queue table without deleting them. This is also referred to as browse mode.
3. The FIFO pop operation is defined as an SQL SELECT AND CONSUME operation to retrieve a row from a queue table and delete that selected row upon completion of the read. This is also referred to as consume mode.A consume mode request goes into a delayed state when a SELECT AND CONSUME finds no rows in the queue table. The request remains idle until an INSERT to that queue table awakens the request; that row is returned, and then it’s deleted. Consumed rows are returned in FIFO order.

create queue table syntax :

create table prod.myquetable, queue

(

create_timestamp timestamp(6) not null default current_timestamp(6),

uid int, 

uname int

) ; 

what is logon privilege in Teradata?

When a user is created in the database it automatically gets database logon privileges, and can
logon from any configured, connected client using Teradata authentication (TD2 mechanism).

By default, the database automatically grants permission to log on for all users defined in the
database, from all client system connections (hostids).


You can use the REVOKE LOGON statement to restrict:
• All logons to the database for a particular user
• Logons to the database by a user through one or more client connections (hostids).

Example :

GRANT/REVOKE LOGON ON hostid1,hostid2.. FROM username1,username2..

or


GRANT/REVOKE LOGON ON All FROM username1,username2..

where hostid corresponds to a host group (HostNo value).

Enabling and Disabling query logging in Teradata

Database query logging (DBQL) is an optional feature in Teradata through which we can track processing behavior of database by logging user activity. User activity can be logged in various level namely object, sql text, steps, explain etc. one can also set limit to the length of query text to be logged.

since this is resource consuming task that database has to perform on each activity, on should assess impact on database performance before enabling it in various level.

Query logging can be enabled on user using its account name. Every user created in database has its account name (account string) assigned while user creation, though account string can be assigned to a user or group of user manually.

Account string of a user can be verified by querying "dbc.accountinfo" view or better edit profile to check account string.

select * from dbc.accountinfo where username =’username’

The DBQL can be enable for specific user using their account string by below command

begin query logging with explain,objects, sql on all account=(' $M$BUSI$S$D$H')

in the above command query logging will be enabled for  objects, sql text and explain  on all user having account ‘$M$BUSI$S$D$H' .  There are few more detail option for query logging is available namely step logging, xml explain, summery logging.  The text size of sql text can be limit by limit clause as below. Here 0 means unlimited whereas any positive integer will limits to its value.

begin query logging with explain,sql limit sqltext=0 on all account=(' $M$BUSI$S$D$H')

Query logging can be disable with end query logging command as show below.

end query logging with explain,objects, sql on all account=(' $M$BUSI$S$D$H')

DBQL can be enabled for all users irrespective of their account string by below command

Begin query logging with objects, sql limit sqltext=0 on all

Finally DBQL rules can be verified by querying "dbc.dbqlrules" view.

select * from dbc.dbqlrules

Where does TD store transient journal?

Transient Journal (TJ) is an area of space in the DBC database which is used primarily for storing of roll-back/undo  information during inserts/deletes/updates on a tables. 

TJ require perm space and is stored in "dbc.transientjournal". This special type of table can grow beyond dbc's  perm space limit until the whole system runs out of perm space.

It is good practice to backup Transient Journal regularly and delete to enhance query and load performance.

Few differences between Global Temporary Table and Volatile Table

 Global Temporary tables (GTT) :

• Table Definition is stored  into Data Dictionary.
• Data is stored in temp space.
• GTT data is active upto the session ends, but table definition will remain there in Data dictionaly untill is is dropped  using Droptable statement.
• secondry Index can be created on Global Temporary table.
• Stats can be collected on GTT.
• CHECK or BETWEEN constraints, COMPRESS column and DEFAULT and TITLE clause are supported by Global Temporary table.
• In a single session 2000 Global temporary table can be materialized.

Volatile Temporary tables (VTT) :

• Table Definition is stored in System cache.
• Data is stored in spool space.
• VTT data and table definition both are active only upto session ends.
• secondry Index can not be created on Global Temporary table.
• stats cannot be collected on VTT.
• CHECK or BETWEEN constraints, COMPRESS column and DEFAULT and TITLE clause are not supported by GTT.
• In a single session 1000 Volatile temporary table can be materialized.
• VTT does not support default value for a column while creating a table.