Teradata Objects

Below are the list of objects contains in Teradata database or User:

• Tables–rows and columns of data
• Views–predefined subsets of existing tables
• Macros–predefined, stored SQL statements
• Triggers–SQL statements associated with a table
• Stored Procedures–program stored within Teradata
• User-Defined Function–function (C program) to provide additional SQL functionality
• Join and Hash Indexes –separate index structures stored as objects within a database
• Permanent Journals–table used to store before and/or after images for recovery

Important Note:

• Objects which require permanent spaces are:- Tables , Triggers, Stored Procedures, User-Defined Function, Join and Hash Indexes & Permanent Journals
• Objects which not require permanent spaces are:- Macros & Views

• Join and Hash Indexes and Permanent Journals – These aren’t directly accessed by users (these will be explained in details in another blog)
• These objects are created, maintained, and deleted using SQL.
• Object definitions are stored in the DD/D.

What is The Data Dictionary Directory (DD/D)

(It will be explained in details in another topic)

All Objects definitions are stored in the DD/D.

The DD/D

• is an integrated set of system tables
• contains definitions of and information about all objects in the system
• is entirely maintained by the Teradata Database
• is “data about the data” or “metadata”
• is distributed across all AMPs like all tables
• is normally accessed via Teradata supplied views

Examples of DD/D views:

DBC.TablesV- information about objects (e.g., tables) in a database/user
DBC.UsersV- information about all users
DBC.AllRightsV- information about access rights
DBC.AllSpaceV- information about space utilization

Teradata Objects in Details:

Below are the four important Teradata objects explained in details:

1. Tables

It contains rows and columns

To create and store the table structure definition in the DD/D, you can execute the CREATE TABLE DDL statement

examples:

Above definition is stored in DD/D

2. Views

Views are pre-defined filters of existing tables consisting of specified columns and/or rows from the table(s).

A single table view:

• is a subset of an underlying table
• allows users to read and update a subset of the underlying table
• has no data of its own

A multi-table:

A multi-table view allows users to access data from multiple tables as if it were in a single table.
Multi-table views (i.e., join views) are used for reading only, not updating.

3. Macros

A MACRO is a predefined set of SQL statements which is logically stored in a database.

Macros have many features and benefits:

• Simplify end-user access
• Control which operations may be performed by users
• May accept user-provided parameter values
• Are stored in the Teradata Database, thus available to all clients
• Reduces query size
• Are optimized at execution time
• May contain multiple SQL statements

To create a macro:

CREATE MACRO Customer_ListAS (SELECT CustNameFROM Customer;);

To execute a macro:

EXEC Customer_List;

To replace a macro:

REPLACE MACRO Customer_ListAS(SELECT CustNumber, CustNameFROM Customer;);

4. Stored Procedures

A stored procedure contains a set of SQL statements and procedural statements.

They consist of a set of control and condition handling comments that make SQL a computationally complete programming language.

The definition of the stored procedure is stored in the database, and the parameters are stored in data dictionary tables.

Teradata procedure support most of the operations, for example

• Insert / Update / Merge / Delete
• Drop / Truncate / Create Table/View
• Collect Statistics
• Procedure Calls
• Iteration[For/While loop]
• Cursors
• Variable manipulation
• Conditions [If/ELSE]

Teradata procedures can be of two types:

• General Procedure: Procedure that performs some action in the background
• Dynamic procedure: Procedures that returns result set/query result

Advantages of using Stored Procedures:

• Stored procedures reduce the network load between the client and the server.
• It provides better security since the data is accessed through stored procedures instead of accessing them directly.
• It gives better maintenance since the business logic is tested and stored in the server.

Syntax

To Create a PROCEDURE .

CREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] )   

BEGIN   

   <SQL or SPL statements>;   

END;  

Examples:

Consider the following Salary Table of the employees.

CompanyID	Income	Taxpay	Totalpay
202001	40,000	2,000	38,000
202002	80,000	3,000	77,000
202003	85,000	5,000	80,000
202004	90,000	2,000	88,000

The following example creates a stored procedure named Insert Salary to accept the values and insert them into the Salary Table.

CREATE PROCEDURE InsertSalary   

(   

   IN in_CompanyID INTEGER,   

   IN in_Income INTEGER,   

   IN in_Taxpay INTEGER,   

   IN in_Totalpay INTEGER   

)   

BEGIN   

   INSERT INTO Salary   

(   

      CompanyID,   

      Income,   

      Taxpay,   

      Totalpay   

 )   

   VALUES   

(   

      :in_CompanyID,   

      :in_Income,   ,   

      :in_Taxpay,   

      :in_Totalpay   

  );  

   

END;  

To Execute Stored Procedures:

CALL <procedure name> [(parameter values)];  

To Compiling Procedure:

.COMPILE FILE [procedure_file];

To Get Procedure Code:

SHOW PROCEDURE [database_name.procedurename];

To Drop Procedure:

DROP PROCEDURE [database_name.procedurename];