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.

Find "Bad character in format or data" using OTranslate function

Bad character in format or data

======================

This error is reported when you try to translate a (Varchar or char) column with special characters in it to (Decimal or integer).When a query fails with such errors, the primary challenge is to find our where the problems lies and which column might have special character. the answer is where there is explicit or implicit conversion happening from character columns to integer.

More cumbersome job is to find what is that value in column which is causing the issue, here is a technique using which one can identify the value quickly.

example:

  sel roll from perf_app.$ab_temp   WHERE CHAR_LENGTH(roll) <> CHAR_LENGTH(oTranslate(roll, '/_#.'',-*)(:"', ''))

here the sys_fnlib.oTranslate function can identify the special character and provide the lenth of the value after replacing with ''(blank).

Dynamic Hash Join

Dynamic hash join provides the ability to do an equality join directly between a small table and a large table on non‑primary index columns without placing the large table into a spool file. important criteria for dynamic hash join to be used, the left table must be small enough to fit in a single hash join partition.

It process as follows:

1) Duplicate the smaller table.

2) Place the smaller table in a hash array

3) Read a row from the right table

4) Compute the row hash code

5) Match each right row with all rows in the hash array having the same row hash.

6) Join the matching rows.

Dynamic hash join is faster than redistributing the small table, putting it into the hash array, reading the large table, building the row hash code, writing the data out to spool, and then reading the table in again to do the hash join.

The dynamic hash join can be decided by optimizer in case of left, right, and full outer joins as well as inner joins, and can also take advantage of equality conditions for dynamic partition elimination. 

Create procedure in Teradata

A stored procedure is a database object containing combination of SQL statements and control and condition handling Statements stored in database in compiled form.

Basic Syntax:

    CREATE PROCEDURE ProcedureName (IN/OUT/INOUT fields datatypes)
    BEGIN
    SQL statements;
    END;

disable user in Teradata

To disable a user in Teradata we can revoke logon rights from user as below.

Revoke logon on all from myusername;

Tara process clean up

Follow below steps to check for leftover arcmain processes when we abort a job (best to do this when all jobs have finished running so you don't kill a job that you want to run) And the procedure to restart services properly.

Example below is for Tidwprod 2 node linux bar system Primary: RRI1TDBPL1P and Secondry: RRI1TDBPL2P

Proper steps for restarting services:

a.   Stop the TARA Server services. Once the TARA Server services have stopped, the TARA Client services should stop automatically.

TARA Server

Stop:   /etc/init.d/tdtaraserver stop

Stop:    /etc/init.d/tdtaraclient stop

b.   Verify the TARA Client services have stopped. If not, stop the TARA Client services for each configured machine.

TARA Client

Stop:    /etc/init.d/tdtaraclient stop

c.   Run the netstat command and make sure no connections are still set for the port 54323 (default). If there are still listings with status: ESTABLISHED, CLOSE_WAIT, TIMED_WAIT, etc. wait 5 to 10 minutes to make sure these entries go away.

RRI1TDBPL2P:~ # netstat -pant | grep 54323

d.   Once the tcp connections have been cleared for TARA, start the TARA Server services. The TARA Client services may start automatically (within 5 minutes). Otherwise, start the TARA Client services.

TARA Server

Start:   /etc/init.d/tdtaraserver start

Start:   /etc/init.d/tdtaraclient start

TARA Client

Start:   /etc/init.d/tdtaraclient start

e.   Verify the connections are established between the TARA Server and the TARA Clients by running the netstat command.

TARA SERVER

RRI1TDBPL1P:~ # netstat -pant | grep 54323

tcp 0 0 0.0.0.0:54323 0.0.0.0:* LISTEN 29146/taraserver

tcp 0 0 10.18.33.43:54323 10.18.33.43:40926 ESTABLISHED 29146/taraserver

tcp 0 0 10.18.33.43:54323 10.18.33.45:38522 ESTABLISHED 29146/taraserver

tcp 0 0 10.18.33.43:40926 10.18.33.43:54323 ESTABLISHED 29252/TaraD

tcp 0 0 10.18.33.43:54323 10.18.32.101:4874 ESTABLISHED 29146/taraserver

tcp 0 0 10.18.33.43:54323 10.18.32.101:4960 ESTABLISHED 29146/taraserver

RRI1TDBPL1P: