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