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:

move space in teradata

In teradata permanent space can be moved from one database to another, below is commands in sequence to perform this task.

1. CREATE DATABASE TDAdmin20131211041448 FROM "DBC" AS perm = 322122547200
2. GIVE TDAdmin20131211041448 TO "dba_dat"

3. DROP DATABASE TDAdmin20131211041448

Starting teradata database service in linux(vm)

For Teradata database express edition  installed on Suse linux, we can check the database service status by
command "pdestate".

# pdestate -a
PDE state is START/TVSASTART.  --which indicates database service is up and running.
or
PDE state is DOWN/HARDSTOP.   --- indicates database service is down.

we can start or stop the database service from /etc/init.d as

s10-1310:/etc/init.d # service tpa start/stop.

Note: once we start the database service (tpa), this may take few minute to start and enable logons etc.

Troubleshooting

If you are running into problems getting Teradata started, the first place to check for clues is in the log file:

#tail /var/log/messages

And finally, to check your storage, use the verify_pdisks command:

# verify_pdisks All pdisks on this node verified.

You may see some warning messages with this, but what we're looking for is the final 'verified' message.

Release Hut-Lock in Teradata database

Hut-Lock is placed on table or database by Teradata where there is archive/restore operation is being performed on table/database, ideally the archive/resotre/copy script should include “release lock” command at the end of script to release lock once the operation is complete. But in case if backup script did not mention “release lock” clause or backup job failed with abnormal termination the lock is not release automatically.

In this case we need to release lock manually; we can release Hut- lock in two ways 1. By using arcmain  or by using SQL Assistant/bteq.

Using arcmain to release lock.

Using command prompt or Linux prompt, enter below command.

      > arcmain

         Logon serverip/arcuser1,arcuserpassword;

         Release lock (databasename.tablename);       à to release table level lock.

  Or

         Release lock (databasename);         à to release database level lock.

         Logoff;

Note: In both the cases Teradata expect to use same user (arcuser1) which has put Hut-Lock, if not you can use super                    user with “override” option.

             

              i.e. Release lock (databasename.tablename), override;

Using Sql Assistant/bteq to release lock.

        1. Logon to SQL Assistant using same arcuser1, which accuire lock.

        2.   release lock(databasename.tablename);

Hut-Lock

Host Utility (HUT) Lock is a lock that Teradata ARC utility places when ARC command like ARCHIVE/RESTORE etc. are executed on database or objects with below condition apply.

     1. HUT locks are associated with the currently logged-on user who entered the statement,

         not with a job or transaction.

     2. HUT locks are placed only on the AMPs that are participating in a Teradata ARC

         operation.

     3. A HUT lock that is placed for a user on an object at one level never conflicts with another

         level of lock on the same object for the same user.

We can check the Hut-lock in Teradata database using the ShowLocks Console utility.