Data warehousing with Teradata: 2012

Monday, December 31, 2012

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

Sunday, December 23, 2012

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.

The FIFO push operation is defined as a SQL INSERT operation to store rows into a queue table.
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.
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

) ;

Saturday, December 1, 2012

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

Monday, November 12, 2012

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

Sunday, November 4, 2012

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.

Saturday, November 3, 2012

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.

Wednesday, October 31, 2012

How to find join indexes in database?

for newbie's there are various question that he may struck with, one of common question may be of these.

Join index can be found for a database by querying dbc.table.

select * from dbc.tables where databasename='mydatabasename' and tablekind='i';

OR

SELECT  * FROM dbc.indices 
WHERE indextype='J'
     AND databasename='mydatabase'
     AND tablename='mytablename'
ORDER BY indexname,columnposition;

Saturday, October 27, 2012

3055 Logons are only enabled for user DBC

In had an incident when we were unable to log-in to Teradata system from any user. the error was reported was "3055:Logons are only enabled for user DBC". this normally happens after some maintenance work or after up-gradation/migration of database or may be database administrator intentionally put restriction.

To sort this issue out, below steps can be followed:

1. Login to the node
2. type command "cnsterm 6" and press enter
3. in supervisor command prompt, type "enable all logons" and press enter.

Input Supervisor Command:
> enable all logons
enable all logons

12/10/26 03:17:47 Logons enabled.
Enable Logons of screen debug in ctl have been updated to All.

2616: Numeric overflow occurred during computation.

In Teradata normally we come across this error while selecting column value which is too large to be accommodated in requested datatype.

In below example we tried to count a table which has more than 6 billion records, so certainly the count would return result in integer and Standard max value for integer data type is 2147483647. so whenever selected value exceeds this range it will throw this error.

select count(*) from bm_tb.cust_priscription;

SELECT Failed. 2616: Numeric overflow occurred during computation.

In this case to get the proper result we need to cast the returned integer value to float or decimal.

select count(*) (float) from bm_tb.cust_priscription;

6,806,668,046.00

select count(*) (DECIMAL(12,0)) from bm_tb.cust_priscription;

6,806,668,046

You can try this demo to understand integer limit in SQLA:

SELECT CAST( 2147483647 AS INT)

Result: 2147483647

Then try to select integer value greater than the integer limit.

SELECT CAST( 2147483648 AS INT)

Result: "SELECT Failed. 2616: Numeric overflow occurred during computation."

Thursday, October 25, 2012

When should the statistics be collected?

We should collect statistics on following cases..

When statistics are stale, you can help stats on table to find last stats collection date.
On all Non-Unique indices·
On Non-index join columns·
On Primary Index of small tables·
On Primary Index of a Join Index·
On Secondary Indices defined on any join index·
When Join index columns that frequently appear on any additional join index columns that frequently appear in WHERE search conditions·
On Columns that frequently appear in WHERE search conditions or in the ON clause of joins

Wednesday, October 24, 2012

change password prompt at first login in teradata

In teradata, table for default user configurations is "dbc.syssecdefaults" and if user is assigned profile then configuration table is "DBC.PROFILES". the password change at first login means making users password temporary or set .EXPIREPASSWORD attribute to 0.

Through Teradata administrator we can modify the user and tick the temporary flag, but this seems not working sometime.

so we have a command to server this purpose,

MODIFY USER myusername AS PASSWORD = myteradata FOR USER ;

this command will set .EXPIREPASSWORD to 0 and will make the password temporary in effect.

Note : please note that above command will work only when if Expire field for profile is not already set to zero, as shown. there should be non zero value in this field and no user session should not be connected.

How to find the Release and Version information in Teradata

To find Release and Version information you can query this Data Dictionary DBC.DBCINFO table.

SELECT * FROM DBC.DBCINFO;

Tuesday, October 23, 2012

What is High confidence, Low confidence and No confidence in EXPLAIN plan?

Explain gives the execution strategy based on dictionary information (stats) it has about object in questions.

HIGH CONFIDENCE: Statistics are collected.
LOW CONFIDENCE: Statistics are not collected.But the where condition is having the condition on indexedcolumn.Then estimations can be based on sampling.
NO CONFIDENCE: Statistics are not collected and the condition is on non indexed column

What are Global temporary tables and Volatile temporary tables?

Global Temporary tables (GTT):

1. When they are created, its definition goes into Data Dictionary.

2. When materialized data goes in temp space.

3. That's why, data is active till the session persist,

and definition will remain there up-to its not dropped using Drop table statement.

If dropped from some other session then its should be Drop table all;

4. you can collect stats on GTT.

Volatile Temporary tables (VTT):

1. Table Definition is stored in System cache

2. Data is stored in spool space.

3. That's why, data and table definition both are active till session persist.

4. No collect stats for VTT.

5. If you are using volatile table, you can not put the default values on column level while creating table.

Monday, October 22, 2012

3668: A table listed in the Dictionary is not present

This error shows up when restore of table is failed in middle, and user tries to select from this table.

LOCKING Failed. 3668: A table listed in the Dictionary is not present.

solution would be to drop the table and recreate/restore/copy with latest backup.

Saturday, October 20, 2012

How to query databases space of whole system.

We can query dbc.Diskspace to know all about space in system. here is query through which we can get idea about Maximum permanent space allocated, how much space is currently consumed and how much is free, with details about % of use and % of space usage skew.

SELECT Databasename AS "Database Name",
Max_perm_gb (DECIMAL (10,1)) AS "Max Perm (GB)",
current_perm_gb (DECIMAL (10,1)) AS "Current Perm (GB)",
EffectiveUsedSpace_Gb (DECIMAL (10,1)) AS "Effective Used Space (GB)",
Current_Perm_Percent AS "Current Perm %",
(Max_perm_gb (DECIMAL (10,1))) -( current_perm_gb (DECIMAL (10,1))) as "unused space in GB" ,
(100) - (Current_Perm_Percent) as "unused %",
Peak_Perm_Gb (DECIMAL (10,1)) AS "Peak Perm (GB)",
Peak_Perm_Percent AS "Peak Perm %",
D_SKEW AS "Skew %"
FROM
(
SELECT
databasename, SUM(maxperm)/1024/1024/1024 AS Max_Perm_Gb,
SUM(currentperm)/1024/1024/1024 AS Current_Perm_Gb,
MAX(currentperm)*COUNT(*) /1024/1024/1024 AS EffectiveUsedSpace_Gb,
Current_Perm_Gb/Max_Perm_Gb * 100 AS Current_Perm_Percent,
SUM(peakperm)/1024/1024/1024 AS Peak_Perm_Gb,
Peak_Perm_Gb/Max_Perm_Gb * 100 AS Peak_Perm_Percent,
ZEROIFNULL((MAX(currentperm) - AVG(currentperm))/MAX(NULLIFZERO(currentperm)) * 100) AS D_SKEW
FROM dbc.diskspace
GROUP BY 1
) a
ORDER BY 2 DESC;

Friday, October 19, 2012

prepare script for enabling and disabling trigger in teradata

This kind of scripts are required when the system is going for maintenance.

SELECT 'Alter trigger '|| TRIM(databasename) || '.' || TRIM(triggername) || ' Disabled' ||';'
FROM dbc.triggers
WHERE Enabledflag = 'Y'
ORDER BY databasename, tablename, triggername;

Thursday, October 18, 2012

List of Teradata Database Access Rights

Here is list of access right and their full-form.

AE = ALTER EXTERNAL PROCEDURE
AF = ALTER FUNCTION
AP = ALTER PROCEDURE
AS = ABORT SESSION
CA = CREATE AUTHORIZATION
CD = CREATE DATABASE
CE = CREATE EXTERNAL PROCEDURE
CF = CREATE FUNCTION
CG = CREATE TRIGGER
CM = CREATE MACRO
CO = CREATE PROFILE
CP = CHECKPOINT
CR = CREATE ROLE
CT = CREATE TABLE
CU = CREATE USER
CV = CREATE VIEW
D = DELETE
DA = DROP AUTHORIZATION
DD = DROP DATABASE
DF = DROP FUNCTION
DG = DROP TRIGGER
DM = DROP MACRO
DO = DROP PROFILE
DP = DUMP
DR = DROP ROLE
DT = DROP TABLE
DU = DROP USER
DV = DROP VIEW
E = EXECUTE
EF = EXECUTE FUNCTION
GC = CREATE GLOP
GD = DROP GLOP
GM = GLOP MEMBER
I = INSERT
IX = INDEX
MR = MONITOR RESOURCE
MS = MONITOR SESSION
NT = NONTEMPORAL
OD = OVERRIDE DELETE POLICY (Reserved for future use)
OI = OVERRIDE INSERT POLICY (Reserved for future use)
OP = CREATE OWNER PROCEDURE
OS = OVERRIDE SELECT POLICY (Reserved for future use)
OU = OVERRIDE UPDATE POLICY (Reserved for future use)
PC = CREATE PROCEDURE
PD = DROP PROCEDURE
PE = EXECUTE PROCEDURE
R = RETRIEVE/SELECT
RF = REFERENCE
RO = REPLCONTROL
RS = RESTORE
SA = SECURITY CONSTRAINT ASSIGNMENT
SD = SECURITY CONSTRAINT DEFINITION
SH = SHOW
SR = SET RESOURCE RATE
SS = SET SESSION RATE
ST = STATISTICS
TH = CTCONTROL
U = UPDATE
UM = UDT Method
UT = UDT Type
UU = UDT Usage

Wednesday, October 17, 2012

Teradata Show lock utility

When A table or database is being backed up, it put a HUT lock on the object, which is released when it finishes backup successfully. But sometime when backup fails or aborted HUT lock be there and query on those objects goes into blocking. One way of find and release lock on object is to login into one of the node and use show lock utility.

Here is the steps we can follow...

1. start CNSTERM 6 by typing "cnsterm 6"

UAT2_1-9:/var/opt/teradata/DEV_108 # cnsterm 6

Attempting to connect to CNS...Completed.

Hello

Started 'dip' in window 1

at Tue Oct 16 04:03:29 2012

Input Supervisor Command:

start updatedbc

Started 'updatedbc' in window 1

at Tue Oct 16 04:18:18 2012

Input Supervisor Command:

enable all logons

12/10/16 05:11:03 Logons enabled.

Enable Logons of screen debug in ctl have been updated to All.

Input Supervisor Command:

start dip

Started 'dip' in window 1

at Tue Oct 16 05:19:50 2012

2. Start "showlock utility" by typing "start showlocks" on CNSTERM 6 terminal.

Input Supervisor Command:

> start showlocks

start showlocks

Started 'showlocks' in window 1

at Wed Oct 17 09:22:22 2012

Input Supervisor Command: