Monday, November 10, 2014

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. 

Thursday, February 20, 2014

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)
    SQL statements;

Tuesday, February 11, 2014

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;

Sunday, February 2, 2014

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.
RRI1TDBPL1P:~ # netstat -pant | grep 54323
tcp 0 0* LISTEN 29146/taraserver
tcp 0 0 ESTABLISHED 29146/taraserver
tcp 0 0 ESTABLISHED 29146/taraserver
tcp 0 0 ESTABLISHED 29252/TaraD
tcp 0 0 ESTABLISHED 29146/taraserver
tcp 0 0 ESTABLISHED 29146/taraserver