![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/02\/image-59.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Important Note:<\/strong> <\/h3>\n\n\n\n- Objects which require permanent spaces are:- Tables , Triggers, Stored Procedures, User-Defined Function, Join and Hash Indexes & Permanent Journals<\/strong><\/li>
- Objects which not require permanent spaces are:- Macros<\/strong> & Views<\/strong><\/li><\/ul>\n\n\n\n
- Join and Hash Indexes <\/strong> and Permanent Journals<\/strong> – These aren’t directly accessed by users (these will be explained in details in another blog)<\/li>
- These objects are created, maintained, and deleted using SQL<\/strong>.<\/li>
- Object definitions are stored in the DD\/D<\/strong>.<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
What is The Data Dictionary Directory (DD\/D)<\/h2>\n\n\n\n
(It will be explained in details in another topic)<\/p>\n\n\n\n
All Objects definitions are stored in the DD\/D<\/strong>. <\/p>\n\n\n\nThe DD\/D<\/h4>\n\n\n\n- is an integrated set of system tables<\/li>
- contains definitions of and information about all objects in the system<\/li>
- is entirely maintained by the Teradata Database<\/li>
- is \u201cdata about the data\u201d or \u201cmetadata\u201d<\/li>
- is distributed across all AMPs like all tables<\/li>
- is normally accessed via Teradata supplied views<\/li><\/ul>\n\n\n\n
Examples of DD\/D views:<\/h4>\n\n\n\n
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 <\/p>\n\n\n\n
<\/p>\n\n\n\n
Teradata Objects in Details:<\/h2>\n\n\n\n
Below are the four important Teradata objects explained in details:<\/p>\n\n\n\n
1. Tables <\/h2>\n\n\n\n
It contains rows and columns<\/p>\n\n\n\n
To create and store the table structure definition in the DD\/D, you can execute the CREATE TABLE DDL statement<\/p>\n\n\n\n
examples:<\/p>\n\n\n\n![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/02\/image-60.png\")
<\/figure>\n\n\n\nAbove definition is stored in DD\/D<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
2. Views<\/h2>\n\n\n\n
Views are pre-defined filters of existing tables consisting of specified columns and\/or rows from the table(s).<\/p>\n\n\n\n
A single table view:<\/p>\n\n\n\n
- is a subset of an underlying table<\/li>
- allows users to read and update a subset of the underlying table<\/li>
- has no data of its own<\/li><\/ul>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/02\/image-61.png\")
<\/figure>\n\n\n\nA multi-table:<\/p>\n\n\n\n
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.<\/p>\n\n\n\n![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/02\/image-62.png\")
<\/figure>\n\n\n\n3. Macros<\/h2>\n\n\n\n
A MACRO is a predefined set of SQL statements which is logically stored in a database.<\/p>\n\n\n\n
Macros have many features and benefits:<\/p>\n\n\n\n
- Simplify end-user access<\/li>
- Control which operations may be performed by users<\/li>
- May accept user-provided parameter values<\/li>
- Are stored in the Teradata Database, thus available to all clients<\/li>
- Reduces query size<\/li>
- Are optimized at execution time<\/li>
- May contain multiple SQL statements<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
To create a macro:<\/h4>\n\n\n\n
CREATE MACRO Customer_ListAS (SELECT CustNameFROM Customer;);<\/p>\n\n\n\n
To execute a macro:<\/h4>\n\n\n\n
EXEC Customer_List;<\/p>\n\n\n\n
To replace a macro:<\/h4>\n\n\n\n
REPLACE MACRO Customer_ListAS(SELECT CustNumber, CustNameFROM Customer;);<\/p>\n\n\n\n
<\/p>\n\n\n\n
4. Stored Procedures<\/strong><\/h2>\n\n\n\nA stored procedure contains a set of SQL statements and procedural statements. <\/p>\n\n\n\n
They consist of a set of control and condition handling comments that make SQL a computationally complete programming language. <\/p>\n\n\n\n
The definition of the stored procedure is stored in the database, and the parameters are stored in data dictionary tables.<\/p>\n\n\n\n
Teradata procedure support most of the operations, for example<\/p>\n\n\n\n
- Insert \/ Update \/ Merge \/ Delete<\/li>
- Drop \/ Truncate \/ Create Table\/View<\/li>
- Collect Statistics<\/li>
- Procedure Calls<\/li>
- Iteration[For\/While loop]<\/li>
- Cursors<\/li>
- Variable manipulation<\/li>
- Conditions [If\/ELSE]<\/li><\/ul>\n\n\n\n
Teradata procedures can be of two types:<\/p>\n\n\n\n
- General Procedure:<\/strong> Procedure that performs some action in the background<\/li>
- Dynamic procedure:<\/strong> Procedures that returns result set\/query result<\/li><\/ul>\n\n\n\n
Advantages of using Stored Procedures:<\/h4>\n\n\n\n- Stored procedures reduce the network load between the client and the server.<\/li>
- It provides better security since the data is accessed through stored procedures instead of accessing them directly.<\/li>
- It gives better maintenance since the business logic is tested and stored in the server.<\/li><\/ul>\n\n\n\n
Syntax<\/strong><\/p>\n\n\n\nTo Create a PROCEDURE .<\/strong><\/h4>\n\n\n\nCREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] ) <\/p>\n\n\n\n
BEGIN <\/p>\n\n\n\n
<SQL or SPL statements>; <\/p>\n\n\n\n
END; <\/p>\n\n\n\n
<\/p>\n\n\n\n
Examples:<\/strong><\/p>\n\n\n\nConsider the following Salary Table of the employees.<\/p>\n\n\n\nCompanyID<\/th> Income<\/th> Taxpay<\/th> Totalpay<\/th><\/tr> 202001<\/td> 40,000<\/td> 2,000<\/td> 38,000<\/td><\/tr> 202002<\/td> 80,000<\/td> 3,000<\/td> 77,000<\/td><\/tr> 202003<\/td> 85,000<\/td> 5,000<\/td> 80,000<\/td><\/tr> 202004<\/td> 90,000<\/td> 2,000<\/td> 88,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nThe following example creates a stored procedure named Insert Salary to accept the values and insert them into the Salary Table.<\/p>\n\n\n\n
CREATE PROCEDURE InsertSalary <\/p>\n\n\n\n
( <\/p>\n\n\n\n
IN in_CompanyID INTEGER, <\/p>\n\n\n\n
IN in_Income INTEGER, <\/p>\n\n\n\n
IN in_Taxpay INTEGER, <\/p>\n\n\n\n
IN in_Totalpay INTEGER <\/p>\n\n\n\n
) <\/p>\n\n\n\n
BEGIN <\/p>\n\n\n\n
INSERT INTO Salary <\/p>\n\n\n\n
( <\/p>\n\n\n\n
CompanyID, <\/p>\n\n\n\n
Income, <\/p>\n\n\n\n
Taxpay, <\/p>\n\n\n\n
Totalpay <\/p>\n\n\n\n
) <\/p>\n\n\n\n
VALUES <\/p>\n\n\n\n
( <\/p>\n\n\n\n
:in_CompanyID, <\/p>\n\n\n\n
:in_Income, , <\/p>\n\n\n\n
:in_Taxpay, <\/p>\n\n\n\n
:in_Totalpay <\/p>\n\n\n\n
); <\/p>\n\n\n\n
<\/p>\n\n\n\n
END; <\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
To Execute Stored Procedures:<\/h4>\n\n\n\n
CALL <procedure name> [(parameter values)]; <\/p>\n\n\n\n
<\/p>\n\n\n\n
To Compiling Procedure:<\/h4>\n\n\n\n.COMPILE FILE [procedure_file];\n<\/pre>\n\n\n\nTo Get Procedure Code:<\/h4>\n\n\n\nSHOW PROCEDURE [database_name.procedurename];\n<\/pre>\n\n\n\nTo Drop Procedure:<\/h4>\n\n\n\nDROP PROCEDURE [database_name.procedurename];\n\n\n<\/pre>\n\n\n\n<\/h2>\n","protected":false},"excerpt":{"rendered":"
Teradata Objects Below are the list of objects contains in Teradata database or User: Tables\u2013rows and columns of data Views\u2013predefined subsets of existing tables Macros\u2013predefined, stored SQL statements Triggers\u2013SQL statements… <\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[7338],"tags":[],"class_list":["post-27337","post","type-post","status-publish","format-standard","hentry","category-teradata"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=27337"}],"version-history":[{"count":3,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27337\/revisions"}],"predecessor-version":[{"id":27380,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27337\/revisions\/27380"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=27337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=27337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=27337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Join and Hash Indexes <\/strong> and Permanent Journals<\/strong> – These aren’t directly accessed by users (these will be explained in details in another blog)<\/li>
- These objects are created, maintained, and deleted using SQL<\/strong>.<\/li>
- Object definitions are stored in the DD\/D<\/strong>.<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
What is The Data Dictionary Directory (DD\/D)<\/h2>\n\n\n\n
(It will be explained in details in another topic)<\/p>\n\n\n\n
All Objects definitions are stored in the DD\/D<\/strong>. <\/p>\n\n\n\n
The DD\/D<\/h4>\n\n\n\n
- is an integrated set of system tables<\/li>
- contains definitions of and information about all objects in the system<\/li>
- is entirely maintained by the Teradata Database<\/li>
- is \u201cdata about the data\u201d or \u201cmetadata\u201d<\/li>
- is distributed across all AMPs like all tables<\/li>
- is normally accessed via Teradata supplied views<\/li><\/ul>\n\n\n\n
Examples of DD\/D views:<\/h4>\n\n\n\n
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 <\/p>\n\n\n\n<\/p>\n\n\n\n
Teradata Objects in Details:<\/h2>\n\n\n\n
Below are the four important Teradata objects explained in details:<\/p>\n\n\n\n
1. Tables <\/h2>\n\n\n\n
It contains rows and columns<\/p>\n\n\n\n
To create and store the table structure definition in the DD\/D, you can execute the CREATE TABLE DDL statement<\/p>\n\n\n\n
examples:<\/p>\n\n\n\n
<\/figure>\n\n\n\nAbove definition is stored in DD\/D<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
2. Views<\/h2>\n\n\n\n
Views are pre-defined filters of existing tables consisting of specified columns and\/or rows from the table(s).<\/p>\n\n\n\n
A single table view:<\/p>\n\n\n\n
- is a subset of an underlying table<\/li>
- allows users to read and update a subset of the underlying table<\/li>
- has no data of its own<\/li><\/ul>\n\n\n\n<\/figure>\n\n\n\n
A multi-table:<\/p>\n\n\n\n
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.<\/p>\n\n\n\n<\/figure>\n\n\n\n3. Macros<\/h2>\n\n\n\n
A MACRO is a predefined set of SQL statements which is logically stored in a database.<\/p>\n\n\n\n
Macros have many features and benefits:<\/p>\n\n\n\n
- Simplify end-user access<\/li>
- Control which operations may be performed by users<\/li>
- May accept user-provided parameter values<\/li>
- Are stored in the Teradata Database, thus available to all clients<\/li>
- Reduces query size<\/li>
- Are optimized at execution time<\/li>
- May contain multiple SQL statements<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
To create a macro:<\/h4>\n\n\n\n
CREATE MACRO Customer_ListAS (SELECT CustNameFROM Customer;);<\/p>\n\n\n\n
To execute a macro:<\/h4>\n\n\n\n
EXEC Customer_List;<\/p>\n\n\n\n
To replace a macro:<\/h4>\n\n\n\n
REPLACE MACRO Customer_ListAS(SELECT CustNumber, CustNameFROM Customer;);<\/p>\n\n\n\n
<\/p>\n\n\n\n
4. Stored Procedures<\/strong><\/h2>\n\n\n\n
A stored procedure contains a set of SQL statements and procedural statements. <\/p>\n\n\n\n
They consist of a set of control and condition handling comments that make SQL a computationally complete programming language. <\/p>\n\n\n\n
The definition of the stored procedure is stored in the database, and the parameters are stored in data dictionary tables.<\/p>\n\n\n\n
Teradata procedure support most of the operations, for example<\/p>\n\n\n\n
- Insert \/ Update \/ Merge \/ Delete<\/li>
- Drop \/ Truncate \/ Create Table\/View<\/li>
- Collect Statistics<\/li>
- Procedure Calls<\/li>
- Iteration[For\/While loop]<\/li>
- Cursors<\/li>
- Variable manipulation<\/li>
- Conditions [If\/ELSE]<\/li><\/ul>\n\n\n\n
Teradata procedures can be of two types:<\/p>\n\n\n\n
- General Procedure:<\/strong> Procedure that performs some action in the background<\/li>
- Dynamic procedure:<\/strong> Procedures that returns result set\/query result<\/li><\/ul>\n\n\n\n
Advantages of using Stored Procedures:<\/h4>\n\n\n\n
- Stored procedures reduce the network load between the client and the server.<\/li>
- It provides better security since the data is accessed through stored procedures instead of accessing them directly.<\/li>
- It gives better maintenance since the business logic is tested and stored in the server.<\/li><\/ul>\n\n\n\n
Syntax<\/strong><\/p>\n\n\n\n
To Create a PROCEDURE .<\/strong><\/h4>\n\n\n\n
CREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] ) <\/p>\n\n\n\n
BEGIN <\/p>\n\n\n\n
<SQL or SPL statements>; <\/p>\n\n\n\n
END; <\/p>\n\n\n\n
<\/p>\n\n\n\n
Examples:<\/strong><\/p>\n\n\n\n
Consider the following Salary Table of the employees.<\/p>\n\n\n\n
CompanyID<\/th> Income<\/th> Taxpay<\/th> Totalpay<\/th><\/tr> 202001<\/td> 40,000<\/td> 2,000<\/td> 38,000<\/td><\/tr> 202002<\/td> 80,000<\/td> 3,000<\/td> 77,000<\/td><\/tr> 202003<\/td> 85,000<\/td> 5,000<\/td> 80,000<\/td><\/tr> 202004<\/td> 90,000<\/td> 2,000<\/td> 88,000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n The following example creates a stored procedure named Insert Salary to accept the values and insert them into the Salary Table.<\/p>\n\n\n\n
CREATE PROCEDURE InsertSalary <\/p>\n\n\n\n
( <\/p>\n\n\n\n
IN in_CompanyID INTEGER, <\/p>\n\n\n\n
IN in_Income INTEGER, <\/p>\n\n\n\n
IN in_Taxpay INTEGER, <\/p>\n\n\n\n
IN in_Totalpay INTEGER <\/p>\n\n\n\n
) <\/p>\n\n\n\n
BEGIN <\/p>\n\n\n\n
INSERT INTO Salary <\/p>\n\n\n\n
( <\/p>\n\n\n\n
CompanyID, <\/p>\n\n\n\n
Income, <\/p>\n\n\n\n
Taxpay, <\/p>\n\n\n\n
Totalpay <\/p>\n\n\n\n
) <\/p>\n\n\n\n
VALUES <\/p>\n\n\n\n
( <\/p>\n\n\n\n
:in_CompanyID, <\/p>\n\n\n\n
:in_Income, , <\/p>\n\n\n\n
:in_Taxpay, <\/p>\n\n\n\n
:in_Totalpay <\/p>\n\n\n\n
); <\/p>\n\n\n\n
<\/p>\n\n\n\n
END; <\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
To Execute Stored Procedures:<\/h4>\n\n\n\n
CALL <procedure name> [(parameter values)]; <\/p>\n\n\n\n
<\/p>\n\n\n\n
To Compiling Procedure:<\/h4>\n\n\n\n
.COMPILE FILE [procedure_file];\n<\/pre>\n\n\n\n
To Get Procedure Code:<\/h4>\n\n\n\n
SHOW PROCEDURE [database_name.procedurename];\n<\/pre>\n\n\n\n
To Drop Procedure:<\/h4>\n\n\n\n
DROP PROCEDURE [database_name.procedurename];\n\n\n<\/pre>\n\n\n\n
<\/h2>\n","protected":false},"excerpt":{"rendered":"
Teradata Objects Below are the list of objects contains in Teradata database or User: Tables\u2013rows and columns of data Views\u2013predefined subsets of existing tables Macros\u2013predefined, stored SQL statements Triggers\u2013SQL statements… <\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[7338],"tags":[],"class_list":["post-27337","post","type-post","status-publish","format-standard","hentry","category-teradata"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=27337"}],"version-history":[{"count":3,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27337\/revisions"}],"predecessor-version":[{"id":27380,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27337\/revisions\/27380"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=27337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=27337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=27337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Dynamic procedure:<\/strong> Procedures that returns result set\/query result<\/li><\/ul>\n\n\n\n
- General Procedure:<\/strong> Procedure that performs some action in the background<\/li>
- These objects are created, maintained, and deleted using SQL<\/strong>.<\/li>