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

Or

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:
> 

 3. Start CNSTERM 1 in different putty session to see the hut lock..



UAT2_1-9:/var/opt/teradata/DEV_108 # cnsterm 1
Attempting to connect to CNS...Completed.
Hello
    USER arcuser4   MODE Excl     AMP All Amps

GL_TB.TRUNK_GS_SUMMARY
    USER arcuser4   MODE Excl     AMP All Amps

GL_TB.VERTICAL
    USER arcuser4   MODE Excl     AMP All Amps

GL_TB.ZZZ_GL_PARAMETER
    USER arcuser4   MODE Excl     AMP All Amps

GL_TB.ZZZ_TB_BI_DIM_STORE
    USER arcuser4   MODE Excl     AMP All Amps

GL_TB.ZZZ_TB_BI_DIM_STORE_OPER_HRS
    USER arcuser4   MODE Excl     AMP All Amps

GL_TB.base64
    USER arcuser4   MODE Excl     AMP All Amps

              --ShowLocks Processing Complete--

Now, once we get the list of object having HUT lock, we can login into the "arcmain" session and put release lock command on those object.

4. type "arcmain" and enter

5. type  “Logon 10.12.88.120/arcuser4,password;”

6. type “Release lock (GL_TB.TRUNK_GS_SUMMARY);”

7. Logoff;

Tuesday, October 16, 2012

Get current session information in Teradata


You can find current user of a session by simply querying ..

select user;

the whole session information can be queried with statement.

help session;

Sunday, October 14, 2012

Different Types of Joins in Teradata



Teradata joins

A join is an action that projects 1 columns from two or more tables into a new virtual table.
The Teradata Database supports joins of as many as 64 tables per query.
Less formally, a join is an action that retrieves column values from more than one table

Joins are need to extract some useful information from related tables in RDBMS. a common column or set of columns are joined between two tables of same info. group to get meaningful information

In Teradata, some of common join types are used to write Query are

- Inner join (can also be "self join" in some cases)
- Outer Join (Left, Right, Full)
- Cross join (Cartesian product join)

In Teradata, we have Optimizer (a parallel ware optimizer), which determines type of join strategy to be used based on user query taking performance factor in mind.


When User provides join query, optimizer will come up with join plans to perform joins. These Join strategies include

- Merge Join
- Nested Join
- Hash Join
- Product join
- Exclusion Join


 Merge Join
--------------------

Merge join is a concept in which rows to be joined must be present in same AMP. If the rows to be joined are not on the same AMP, Teradata will either redistribute the data or duplicate the data in spool to make that happen based on row hash of the columns involved in the joins WHERE Clause.
            If two tables to be joined have same primary Index, then the records will be present in Same AMP and Re-Distribution of records is not required.

There are four scenarios in which redistribution can happen for Merge Join
Case 1: If joining columns are on UPI UPI, the records to be joined are present in Same AMP and redistribution is not required. This is most efficient and fastest join strategy
Case 2: If joining columns are on UPI = Non Index column, the records in 2nd table has to be redistributed on AMP's based on data corresponding to first table.
Case 3: If joining columns are on Non Index column = Non Index column , the both the tables are to be redistributed so that matching data lies on same amp , so the join can happen on redistributed data.  This strategy is time consuming since complete redistribution of both the tables takes across all the amps
Case 4: For join happening on Primary Index, If the Referenced table (second table in the join) is very small, then this table is duplicated /copied on to every AMP.

 Nested Join
-------------------
Nested Join is one of the most precise join plans   suggested by Optimizer .Nested Join works on UPI/USI used in Join statement and is used to retrieve the single row from first table . It then checks for one more matching rows in second table based on being used in the join using an index (primary or secondary) and returns the matching results.

Example:
Select EMP.Ename , DEP.DeptnoEMP.salary
from
EMPLOYEE EMP ,
DEPARTMENT DEP
Where EMP.Enum = DEP.Enum
and EMp.Enum2345;    -- this results in nested join

 Hash join
----------------
Hash join is one of the plans suggested by Optimizer based on joining conditions. We can say Hash Join to be close relative of Merge based on its functionality. In case of merge join, joining would happen in same amp.   In Hash Join, one or both tables which are on same amp are fit completely inside the AMP's Memory   . Amp chooses to hold small tables in its memory for joins happening on ROW hash.

Advantages of Hash joins are
1. They are faster than Merge joins since the large table doesn’t need to be sorted.
2. Since the join happening b/w table in AMP memory and table in unsorted spool, it happens so quickly.

 Exclusion Join
-------------------------

These type of joins are suggested by optimizer when following are used in the queries
- NOT IN
- EXCEPT
- MINUS
- SET subtraction operations

Select EMP.Ename , DEP.DeptnoEMP.salary
from
EMPLOYEE EMP
WHERE EMP.Enum NOT IN
Select Enum from
DEPARTMENT DEP
where Enum is NOT NULL );

Please make sure to add an additional WHERE filter “with <column> IS NOT NULL” since usage of NULL in a NOT IN <column> list will return no results.

Exclusion join for following NOT In query has 3 scenarios

Case 1: matched data in "NOT IN" sub Query will disqualify that row
Case 2: Non-matched data in "NOT IN" sub Query will qualify that row
Case 3: Any Unknown result in "NOT IN" will disqualify that row - ('NULL' is a typical example of this scenario). 

Saturday, October 13, 2012

7535: Operation not allowed: table header has invalid partitioning.


This can occur due to a restore/copy of the table to another system with different hardware or operating system. In this case, the partitioning expressions need to be regenerated and updated in the table headers of the table. 

Example:

SELECT Failed. 7535: Operation not allowed: MTD_DB.MTD_MSTR table header has invalid partitioning. 

solution to this is validating table header with primary index of table.
For that we can ALTER the TABLE with the REVALIDATE PRIMARY INDEX option.

here we go... command is ..


alter table Mtd_tb.mtd_mstr revalidate primary index;




Script to replicate privileges.

sometime we may need to replicate exact access grant of an user to another user. here is the script to prepare "Grant" statements of a particular user..
Here we go..

SELECT
'GRANT '
|| CASE
WHEN ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE'
WHEN ACCESSRIGHT = 'AS' THEN 'ABORT SESSION'
WHEN ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE'
WHEN ACCESSRIGHT = 'CG' THEN 'CREATE TRIGGER'
WHEN ACCESSRIGHT = 'CM' THEN 'CREATE MACRO'
WHEN ACCESSRIGHT = 'CO' THEN 'CREATE PROFILE'
WHEN ACCESSRIGHT = 'CP' THEN 'CHECKPOINT'
WHEN ACCESSRIGHT = 'CR' THEN 'CREATE ROLE'
WHEN ACCESSRIGHT = 'CT' THEN 'CREATE TABLE'
WHEN ACCESSRIGHT = 'CU' THEN 'CREATE USER'
WHEN ACCESSRIGHT = 'CV' THEN 'CREATE VIEW'
WHEN ACCESSRIGHT = 'D' THEN 'DELETE'
WHEN ACCESSRIGHT = 'DD' THEN 'DROP DATABASE'
WHEN ACCESSRIGHT = 'DG' THEN 'DROP TRIGGER'
WHEN ACCESSRIGHT = 'DM' THEN 'DROP MACRO'
WHEN ACCESSRIGHT = 'DO' THEN 'DROP PROFILE'
WHEN ACCESSRIGHT = 'DP' THEN 'DUMP'
WHEN ACCESSRIGHT = 'DR' THEN 'DROP ROLE'
WHEN ACCESSRIGHT = 'DT' THEN 'DROP TABLE'
WHEN ACCESSRIGHT = 'DU' THEN 'DROP USER'
WHEN ACCESSRIGHT = 'DV' THEN 'DROP VIEW'
WHEN ACCESSRIGHT = 'E' THEN 'EXECUTE'
WHEN ACCESSRIGHT = 'I' THEN 'INSERT'
WHEN ACCESSRIGHT = 'IX' THEN 'INDEX'
WHEN ACCESSRIGHT = 'MR' THEN 'MONITOR RESOURCE'
WHEN ACCESSRIGHT = 'MS' THEN 'MONITOR SESSION'
WHEN ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE'
WHEN ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE'
WHEN ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE'
WHEN ACCESSRIGHT = 'RO' THEN 'REPLICATION OVERRIDE'
WHEN ACCESSRIGHT = 'R'THEN 'SELECT'
WHEN ACCESSRIGHT = 'RF' THEN 'REFERENCE'
WHEN ACCESSRIGHT = 'RS' THEN 'RESTORE'
WHEN ACCESSRIGHT = 'SS' THEN 'SET SESSION RATE'
WHEN ACCESSRIGHT = 'SR' THEN 'SET RESOURCE RATE'
WHEN ACCESSRIGHT = 'U' THEN 'UPDATE'
END
|| ' ON '
|| TRIM(DATABASENAME) || CASE WHEN TABLENAME<>'ALL' THEN '.'||TABLENAME END
|| ' TO TargetUserName '
|| CASE WHEN GRANTAUTHORITY = 'Y' THEN ' WITH GRANT OPTION;' ELSE ' ;' END
FROM DBC.ALLRIGHTS
WHERE USERNAME = 'SourceUserName'  --- and Databasename='Targetdatabasename'
GROUP BY 1
;


Note: this would generate privileges assigned directly to the user, not through role

Friday, October 12, 2012

Kill session in teradata


We can kill/abort the session from SQL command as below.

1. Find the session info as:

select * from dbc.SessionInfo;

2. Execute abortsession function:                

SELECT SYSLIB.AbortSessions(1,'MyUser',0,'Y','Y');

Param1: Hostno
Param2:UserName
Param3: SessionNo
Param4: LogoffSessions
Param5: UserOverride

Where 0 in Param3 means all session of user will be aborted,you can specify sessionno to kill a specific session.
Param4=Y means logoff the session first.


Tuesday, October 9, 2012

Difference between timestamp(0) and timestamp(6)


TIMESTAMP types are imported and exported in record and indicator modes as
CHARACTER data using the ANSI format string and the site-defined client character set.

The basic form of TIMESTAMP is TIMESTAMP (0) which is represented as CHAR (19) in ANSI string with format (YYYY-MM-DD HH:MI:SS)

Other more precise time stamps can be represented as  TIMESTAMP(n), Where n= 1 to 6 is represented in CHAR (20+n). this can be used to store fraction of seconds data (i.e. miliseconds of data can be stored).


Hence TIMESTAMP (6) is represented as CHAR (26)
and TIMESTAMP(6) can be formated in ANSI Character string as YYYY-MM-DDbHH:MI:SS.ssssss.