SriniTera

Teradata has four locks. Let us see details about these locks. Exclusive lock - No Compatibility Read Lock - Compatibility for Read Lock and Access Lock Access Lock - It has compatibility for Read lock, Access Lock and Write lock Write Lock - It has compatibility for Access lock The locks which are compatible can allow to share objects. Point to remember: On the following objects we can place these locks. Database Table Row-hash - A Row Hash lock always involves a 1-AMP operation where the Primary Index is utilized in the WHERE clause of the query. Instead of locking the entire table and possibly making other users wait Teradata will only lock the rows that have the same Row Hash as the value in the WHERE clause

What are OLAP functions? There are some difference between Aggregate functions and OLAP functions. When OLAP functions are combined with standard SQL within the data warehouse, they provide the ability to analyze large amounts of historical, business transactions from the past through the present. Plus, they provide the ability to project possible future values. The OLAP functions are the cousins of the aggregate functions, but are very different in their use.   Like traditional aggregates, OLAP functions operate on groups of rows and permit qualification and filtering of the group result.  Unlike aggregates, OLAP functions also return the individual row detail data and not just the final aggregated value. Command Description CSUM Cumulative sum of a referenced value, for a range or dimension. MSUM Computation of a moving sum of a referenced value, based on a specified window. MAVG Computation of a moving average of a refere

A Quantile is used to divide rows into a number of categories or grouping of roughly the same number of rows in each group. Quantile Function The percentile is the QUANTILE most commonly used in business. This means that the request is based on a value of 100 for the number of partitions SELECT Product_ID1, Sales_Date1, Daily_Sales1 ,QUANTILE(100, Daily_Sales1 ) AS "Quantile1" FROM Sales_Table WHERE Product_ID1 < 3000 AND Sales_Date1 > 1000930 ; The calculation is percentile for every row in the Sales table on Daily sales.

Day to day work we need to write SQL queries with DATE . Teradata has inbuilt utility, we can use it to know useful information about DATE. SYS_CALENDAR.CALENDAR CALENDER is a view, and we would like to why we need to use it. If we want to know DATE attributes between 1900 to 2100 , we can use this utility. Let me give different SQL Queries: SELECT DAY_OF_WEEK,CALENDAR_WEEK FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE = '1960-05-01'; Other way we can use it as: CREATE VIEW Today AS ( SELECT * FROM SYS_CALENDAR.Calendar WHERE SYS_CALENDAR.Calendar.calendar_date = DATE ); Just, we are creating another view as "today''. This can be used in Data warehousing projects. This is particularly useful in OLAP environments where it is common to request values aggregated by weeks, months, year-to-date, years, and so on.

Teradata Case Statement What is CASE statement? CASE will provide logical control of the SQL. In CASE statement we can give >,<,=,<> The CASE has structure it starts with CASE and end with END See an example of Advanced CASE: SELECT   COMPANY,   CASE     WHEN DEPT_NO = 1 THEN 'HR'     WHEN DEPT_NO = 2 THEN 'IT'     WHEN DEPT_NO = 3 THEN 'FINANCE'     ELSE 'I DON''T KNOW'   END AS DEPT_NAME FROM SAMPLES.COMP_DETL ORDER BY 1; The above query covered all like ELSE, THEN, WHEN, CASE and END. This is the best example how typically,you can write CASE in an SQL query for logical validation.

In Teradata there are many String functions. In simple terms, String functions are which deal with Characters. There are 2 modes are available. One is ANSI mode and another one is Teradata mode. ANSI mode is CASE Sensitive. Teradata mode is not CASE sensitive. Understanding and learning of STRING functions is very important in Data warehouse projects. The following functions are available: CHARACTERS SUBSTR SUBSTRING TRIM POSITION and INDEX In Teradata mode: SELECT CHARACTERS(PRODUCT_NAME) AS LENGTH_PRODUCT FROM SAMPLES.ACCOUNTS; In ANSI mode: SELECT CHARACTER_LENGTH(PRODUCT_NAME) AS LENGTH_PRODUCT FROM SAMPLES.ACCOUNTS; The output we will have number of Characters in Product_name; SELECT TRIM(PRODUCT_NAME) AS NEW_NAME FROM SAMPLES.ACCOUNTS; TRIM-functions deletes spaces on both sides of Product_name. Challenge is how to delete spaces in between name of the product. Here we need to use SUBSTRING concept.

Teradata Error I am executing a query INSERT INTO SAMPLES.CUST_NAMES; SELECT A.NAME FROM SAMPLES.CUSTOMERS INNER JOIN SAMPLES.ORDERS ON A.CUST_CODE = B.CUST_CODE WHERE B.ORDER_QTY BETWEEN 200 AND 300 ORDER BY A.NAME DESC; The error is ORDER BY is not allowed. Here, we are just inserting so ORDER BY is not required. It is useless.

A view is a part of base table or base tables. Many reasons we can use Views. 1. Views with aggregate data we can use 2. Reduce complexity, so that less SQL skill is required to use views 3. Like any other base table, we can not Alter views 4. We use REPLACE option to alter views View can be dropped: DROP VIEW view_name; Before going into indepth of Views, some rules are there to create View: Should NOT use ORDER BY either on a view or base table in CREATE VIEW query Indexes- we shoud not Always give Alias to Aggregtate/Derived column names Simple View: A view is created from a base table without any functions or Joins Complex View: A view is created with Aggregate functions, Joins, Derived data, DISTINCT, GROUP BY will be treated as Complex view.

There are 3 types of access rights in Teradata. How these access rights will obtain? Let us see full details. DBC is default user, under it SYSDBA will come, and then other users will come. DBC is super user, it has full powers, and credentials should be Confidential. Implicit rights : Belong to the owners of objects. Owners don't require rows in the DBC.AccessRights table to grant privileges on owned objects. Ownership rights can't be revoked. A parent or owner has the implied right to GRANT privileges over their children. DBC and SysDBA hold implicit roles on all the other databases above. Automatic rights: Happen whenever a CREATE statement is submitted, and new rows are automatically added to the DBC.AccessRights table. When the databases above were created, they automatically received all but four access rights on themselves. Automatic rights are removed with REVOKE or DROP statements. Explicit rights  : are completely controlled by when a user explicitly and

#Primary Index in Teradata Primary index is created while creating Table. We no need to worry about definition. If we forget to mention Primary index, Teradata will create it automatically. Create table samples.orders (Order_no INT, Amount DEC (5,2), Total INTEGER, Item_name VARCHAR(10) Unique Primary Index(Order_No); How Teradata selects UPI? It selects first column in the Table as UPI (Unique Primary Index). By default Teradata create UPI. NUPI - Is also called non-unique primary index. During the table definition we can give NUPI. Especially we are loading data into Staging-tables from mainframe or other server. The disadvantage of NUPI is all the duplicate rows, grouped together in same AMP. It causes for skewing. But this kind of skewing is acceptable. Where we need to give PI in SELECT statement. For efficient performance, we need to give PI in where clause. SELECT * from Samples.order where Order_No = 10005; Keep reading for more on Teradata BI.

Currently Teradata supports different data types. BYTEINT ==> Signed whole number ==> -128 to 127 CHAR ==> Character String  ==> 1to 64000 bytes. The disadvantage is it pads spaces, when you do not give that much string length VARCHAR ==> Character string ==> Only it occupies space, the string length you have given DATE ==>  DATE type  ==> YYYYMMDD format TIME ==> Time value will be stored ==> HH:MM:SS INTEGER ==>Signed whole number ==>   -2,147,483,648 to 2,147,483,647 SMALLINT ==> Signed small whole number ==> -32,768 to 32,767 DEC ==> Signed decimal number ==> 18 digits on either side ==> DEC(18,0)-Max and DEC(18,18)-small There are other data types. Keep reading in my next posts. Read more  

There are 3 types of Temporal Tables. 1. Valid time temporal tables 2. Transaction time tables 3. Bi-Temporal tables How to create Bi-temporal Tables: CREATE MULTISET TABLE Asset_Owners ( Cust_No        INTEGER  ,Asset_No        INTEGER  ,Asset_Val_Time   PERIOD (DATE) NOT NULL as VALIDTIME  ,Asset_Tran_Time PERIOD (TIMESTAMP(6) with TIME ZONE)                       NOT NULL as TRANSACTIONTIME )PRIMARY INDEX(Prop_No) ; Why we call this as Bi-temporal table  means,we aliased one column as VALIDTIME and another column as TIME ZONE. "PERIOD" is a new data-type, So it refers begin date and end date: 2015-01-01.2015-01-30 For time, 2015-01-01 08.09.000000-05.00.9999-12-31 08.09+05.30 So we understand that how data will be stored in PERIOD data type. Keep reading more on Temporal tables.

#What is columnar table in Teradata best example: What is columnar table in Teradata? Let me give an example. Create table samples.test_1(name varchar (10), id integer ) no primary index; This table we also call it as NOPI table. Two rules for columnar table. Table must be multiset table It should be NOPI table Additional Points on columnar tables: Columnar Tables allow Columns to be Partitioned. An AMP still holds the entire row, but partitions vertically. Columns are placed inside their own individual Container.