What is checktable in teradata

"Checktable" is diagnostic tool provided by Teradata to identify inconsistencies and corruption in internal data structure of database objects in the system, it checks inconsistencies at table header, row identifier, secondary index and fallback table validity.

check table has 3 levels of checking

level 1: compares row count of subtable of primary data with subtable of fall back table for each cluster and reports the difference and also does row count comparison for base table and secondary index subtable.

level 2:  compares row identifier in various sub tables and report any inconsistency, It checks fallback & secondary index subtables for duplicate row id's , out of order rowids and incorrectly distributed rows,

level 3: compares the actual rows bytes by byte at secondary index, table header, rowid and fallback level. it is most resource consuming level and should be run in idle time.


check table can be run from superviser window in primary not of system using cnsterm 6 console.

command: start checktable will start the utility.

example check table command:
check all tables at level one with no error limit;

What is Crashdump database in Teradata

Crashdump database is used to store the memory/swap dump of Teradata system when Teradata Database crashes (database restarts) or there is critical/Fatal error has occurred in Teradata which it cannot recover from.

This is Teradata support consultant team use crash dump to analyze the reason of database crash or any fatal error.

Crash dump types:
there are two type of dumps that is generated from Teradata TPA namely 1. Crash dump 2. snapshot dump.

Enabling System level resource logging in Teradata

This can be done from cnsterm terminal, follow the steps below.

CVS_1-8:~ # cnsterm 6
Attempting to connect to CNS...Completed.
Hello

Input Supervisor Command:
start qrysessn

Started 'qrysessn' in window 1
 at Sat May 11 06:53:04 2013


Input Supervisor Command:
start qrysessn

Started 'qrysessn' in window 2
 at Sat May 11 06:53:04 2013


Input Supervisor Command:
start showlocks

Started 'showlocks' in window 1
 at Sat May 11 06:53:08 2013

Input Supervisor Command:
> get logtable all
get logtable all

spma table's logging is disabled
ipma table's logging is enabled
svpr table's logging is enabled
ivpr table's logging is disabled
scpu table's logging is enabled
sldv table's logging is enabled
spdsk table's logging is enabled
svdsk table's logging is enabled
sawt table's logging is enabled
sps table's logging is enabled
shst table's logging is enabled

Input Supervisor Command:
> set logtable spma on
set logtable spma on


Input Supervisor Command:
> get logtable all
get logtable all

spma table's logging is enabled
ipma table's logging is enabled
svpr table's logging is enabled
ivpr table's logging is disabled
scpu table's logging is enabled
sldv table's logging is enabled
spdsk table's logging is enabled
svdsk table's logging is enabled
sawt table's logging is enabled
sps table's logging is enabled
shst table's logging is enabled

Input Supervisor Command:
> set logtable ivpr on
set logtable ivpr on


Input Supervisor Command:
> get logtable all
get logtable all

spma table's logging is enabled
ipma table's logging is enabled
svpr table's logging is enabled
ivpr table's logging is enabled
scpu table's logging is enabled
sldv table's logging is enabled
spdsk table's logging is enabled
svdsk table's logging is enabled
sawt table's logging is enabled
sps table's logging is enabled
shst table's logging is enabled

Input Supervisor Command:

What is impact CPU in teradata?

Impact cpu in Teradata is the cpu consumed on hottest AMP multiplied by number of AMP in the system. so, in other word we can say it as actual "impact" on system cpu during skewed processing.

formula to calculate impact cpu:
MaxAMPCPUTime * (HASHAMP()+1) AS impactCPU

Teradata HUT lock vs Transaction lock

If we consider these locks only from blocking perspective then behavior of HUT lock and Transaction Lock are same.

0. For example, 
1. 
   
2. 1) A read lock prevents another job from claiming a write lock or exclusive lock. 
3. 2)A write lock prevents a read lock, write lock, or exclusive lock. 
4. 3) An exclusive lock prevents any other type of lock. 
5. 
   
6. Conflicting HUT and transaction locks on an object block each other, just as if both were transaction locks or both were HUT locks. 

The two differences between transaction locks and HUT locks are: 

Permanence:
           A transaction lock exists only for the duration of a transaction; after the transaction completes or is                    aborted, the lock is released. A HUT lock is more permanent. It is only removed if an explicit                            RELEASE LOCK command is issued, or if the locking operation completes successfully 

Scope:
           Transaction lock has session scope
           i.e. it applies to any command submitted by the session running the locking transaction. Another session            running against the same object(s) must claim its own locks, even if it is running under the same user 

           HUT lock has users scope
           i.e. it applies to any Teradata ARC operation that the user is performing on the object. A user must have            only one HUT lock against an object at a time. The HUT lock allows any Teradata ARC operation from            that user to access the locked object(s), but blocks other users from acquiring a conflicting HUT lock on            the object(s). 

The difference between copy and restore in teradata

The difference between copy and restore is as below

• A restore operation moves data from archived files back to the same Teradata Database from which it was archived or to a different Teradata Database so long as the database DBC is already restored.

• A copy operation moves data from an archived file back to a Teradata Database, not necessarily to the same system, and creates a new table if one does not already exist on the target database. When a selected partition is copied, the table must exist and be a table that was previously copied as a full-table copy.

How to find PPI tables in Teradata.

List all PPI tables in Teradata:

The index and parition information in teradata can be found in below two tables.

dbc.indices
==========

all index information in Teradata is stored in dbc.indices and as table is being partitioned on primay index
the table is partition information can be found with indextype='Q'.

sel * from dbc.indices where  indextype='Q' and databasename=<yourdatabasename>

dbc.indexconstraints
===============

Teradata consider PPI as constraints and also store the partition expression in dbc.indexconstraints

sel constrainttext from dbc.indexconstraints where dbc.indexconstraints='Q'