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'  

Number of load jobs running on the system

To get a count of load jobs that are currently executing, you can use the following SQL.

SELECT COUNT(DISTINCT LogonSequenceNo) AS Utility_Cnt
FROM DBC.SessionInfo
WHERE Partition IN ('Fastload', 'Export', 'MLoad');

DBS Control parameters (MaxLoadTasks and MaxLoadAWT) can be used to control the number of concurrent load utilities running in a system.

Note: If TASM utility throttles are used, then the utility throttles override the MaxLoadTasks and MaxLoadAWT values and uses 60% (of AWTs) as the setting for the AWTs.

RELEASE MLOAD Statement

Once MLOAD job or TPT Update operator execution has begun, table headers are updated in the target tables indicating that a MLOAD is in progress. if the MLOAD fails, target tables are still considered under the control of the MLOAD and access to them will be restricted accordingly.

The RELEASE MLOAD statement provides a way to return tables to general availability where there is no desire to restart the MLOAD. If the specified table is in the Preliminary, DDL or the early part of the Acquisition phase, the RELEASE MLOAD statement makes the table completely accessible and prevents any attempt to restart the MLOAD.

 If the MLOAD had proceeded into the Application phase, the RELEASE MLOAD statement is rejected and the job must be restarted or until the transaction with the lock completes or if the point of no return has occurred(i.e. DELETE statement is sent to the DBC)

To successfully complete a RELEASE MLOAD, the following procedure must be followed:

1. Make sure MLOAD is not running; abort it if it is. (Note: MLOAD is still in a re-startable state if aborted. If it is past the point of no return, go to step 4.)

2. Enter RELEASE MLOAD (try IN APPLY if in application phase with caustion)

3. If successful, drop the work and error tables.

  4. If not successful, determine if past point of no return. If so, either restart MLOAD and let it complete, or drop target, work, and error tables.

Example:

release MLOAD Inventory;

release MLOAD Order IN APPLY;

Types of spools in Teradata

Teradata Database draws spool space dynamically from unused system perm space, there are mainly three types of spool usage in the system.

Volatile Spool:
Volatile spool is used for volatile table creation and  is retained until the Transaction completes (unless the table was created with ON COMMIT PRESERVE ROW)
or Table is dropped manually during the session
or Session ends
or Teradata Database resets

Intermediate Spool:
Intermediate spool is used during query processing, the spool are retained until they are no longer needed by the query for which they were created. You can determine when intermediate spool is flushed by examining the output of an EXPLAIN.
Note: The first step performed after intermediate spool has been flushed is designated “Last Use.”
or Teradata Database resets

Output Spool:
Output spool is used mainly for returning the result rows while responding, these are either Response rows returned in the answer set for a query or during Rows updated within, inserted into, or deleted from a base table

Find table skew in teradata

To find skew factor for table in Teradata, we can use below query.

 SELECT 

TABLENAME,

SUM(CURRENTPERM) /(1024*1024) AS CURRENTPERM, 

(100 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)*100)) AS SKEWFACTOR 

FROM 

DBC.TABLESIZE 

WHERE DATABASENAME= 'Mydatabase_name'

AND TABLENAME ='myTable_name'

GROUP BY 1;

Simply replace the "Mydatabse_name" and "myTable_name" and run the query.