SriniTera

Teradata Data Mover Moving data from one system to another is a key part of your analytical ecosystem operation. Well-planed, monitored, and managed data movement is essential to effective operation of your overall ecosystem. Whether your goal is to complete a onetime copy of data from your enterprise data warehouse (EDW) to your test system or to regularly and continuously synchronize a dual system environment, you need confidence that the data will be in the right place at the right time while causing the smallest possible impact on the data warehouse’s mission of serving your business. The decision about the best way to copy data between Teradata systems depends on many factors, including the data model and indexes present, other workloads in your system, and additional load jobs underway. What you need is a solution that automates the data movement process, a solution that also accommodates the many situations found within your comprehensive analytical ecosystem. And th...

Usage-1 It returns a rows randomly from Teradata database. Syntax for SAMPLE function: SAMPLE [WITH REPLACEMENT]    [RANDOMIZED ALLOCATION]                  [WHEN <condition>  THEN]                  {<number-of-rows> | <percentage>}    […,<number-of-rows> | <percentage>]                  [ELSE {<number-of-rows> |    <percentage }   END] Usage 2 It allows the user to get absolute number of rows or percentage of rows. Usage 3 It also allows the rows to get from multiple samples How to get random rows from a table: SELECT * from sample_table SAMPLE 5; How to get certain percentage of rows: SELECT * from sample_table SAMPLE .30; How to get multiple SAMPLE data from a single tables with out duplicates: SELECT * from sample_...

Simple SQL query to create a view in Teradata: CREATE View Employee_V AS SELECT      Employee_No            ,First_Name            ,Last_Name            ,Dept_No FROM Employee_Table ; - A view we create to restrict access to certain columns -To restrict access to certain derived columns -To restrict access to join tables -To restrict access to certain rows View is basically in a semantic layer. It supports to Presentation layer. Some time a view can change column names, a view can derive new columns , also aggregate columns. Simple rules for views: - A view should not contain Order By -All aggregate columns must have ALIAS -All derived column must have ALIAS

Indexing is one of the most important features of the Teradata RDBMS. In the Teradata RDBMS, an index is used to define row uniqueness and retrieve data rows, it also can be used to enforce the primary key and unique constraints for a table. The Teradata RDBMS support five types of indexes Unique Primary Index (UPI)  Unique Secondary Index (USI)  Non-Unique Primary Index (NUPI)  Non-Unique Secondary Index (NUPI)  Join Index  The typical index contains two fields: a value and a pointer to instances of that value in a data table. Because the Teradata RDBMS uses hashing to distribute rows across the AMPs, the value is condensed into an entity called a row hash, which is used as the pointer. The row hash is not the value, but a mathematically transformed address. The Teradata RDBMS uses this transformed address as a retrieval index. The following rules apply to the indexes used in the Teradata Relation database: An index is a scheme used to...

Teradata has a date function and a time function built into the database and the ability to request this data from the system. DATE was a valid data type for storing the combination of year, month and day, but TIME was not. Now, TIME and TIMESTAMP are both valid data types that can be defined and stored within a table. The Teradata RDBMS stores the date in YYYMMDD format on disk. The YYY is an offset value from the base year of 1900. The MM is the month value from 1 to 12 and the DD is the day of the month. Using this format, the database can currently work with dates beyond the year 3000. So, it appears that Teradata is Y3K compliant. Teradata always stores a date as a numeric INTEGER value. The following calculation demonstrates how Teradata converts a date to the YYYMMDD date format, for storage of January 1, 1999: The stored data for the date January 1, 1999 is converted to: YEAR: (1999-1900) * 10000 = 0990000 ->YEAR MONTH = 01*100= +0100 -> MONTH DATE...

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 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.

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.

One interesting thing I have noticed in Teradata. I have an existing table, I want to ADD one new column to it. I have given below query. I am getting an error. Resolution for Varchar Error Query: ALTER TABLE SAMPLES.LOAN_ACCOUNT ADD MY_NEW VARCHAR2(1); Error: 3706: Syntax error VARCHAR2 does not match defined type. Correct Query: ALTER TABLE SAMPLES.LOAN_ACCOUNT ADD MY_NEW1 VARCHAR(1); Comparison Oracle Vs DB2 Teradata ODBC Driver Type  Native type for Oracle 9i or 10g staging data source Data type for Oracle 9i or 10g staging data source Native type for DB2 staging data source Data type for DB2 staging data source BYTEINT integer NUMBER(38) integer VARCHAR(3) SMALLINT integer INTEGER integer INTEGER INTEGER Integer INTEGER Integer INTEGER DECIMAL Decimal NUMBER(18) Decimal DECIMAL(18,0) FLOAT Float FLOAT(126) Float DOUBLE CHAR Varchar VARCHAR2(n BYTE) Varchar VARCHAR(n BYTE) VARCHAR Varchar VARCHAR2(n BYTE) Varchar VA...

#Teradata SQL Assitant Setup: Open Teradata SQL Assistant: Start > All Programs > Teradata v1??> Teradata SQL Assistant Go to Tools > Define ODBC Data Source In the User DSN tab, click the Add button. In the Create New Data Source window, select “Teradata” from the list and click the Finish button. Fill out the form that appears with the following values: Name: Teradata Description: Teradata Name or IP Address: {insert the name of your database} Under Optional, click Options. Ensure that “No HELP DATABASE” is checked, then click OK. Click OK to finish.

SQL development just got a whole lot easier with Teradata SQL Assistant (SQLA) Java Edition (JE) 13.10—a query management tool that enables users to retrieve information from a Teradata Database, manipulate it, then store it on a desktop. This latest release runs on multiple platforms, including Windows, Linux and Mac OS, allowing organizations to use their choice of operating system (OS) to connect to the database. More: Teradata Videos SQLA JE is more user-friendly than previous versions. It lets panels be moved to create an individual look and feel for a customized display. Since SQLA JE is built on top of the Eclipse Rich Client Platform (RCP), it takes advantage of the RCP framework for building native graphical user interface (GUI) applica­tions and deploying them to a variety of desktop OSs. More: Teradata Quick refresher for most popular Utilities NULL functions in Teradata

The largest systems in the world have used Teradata for market dominance for the past 20 years. Its Massively Parallel Processing (MPP) technology analyzes on such a large scale that companies can run queries they have never been able to run before. Recognize that you now have something very powerful and that has the ability to analyze every aspect of your business. So do what you've never done, and get something that you've never got. More:  Teradata SQL quick reference.

Student_Table Student_ID Last_Name First_Name Class_Code Grade_Pt 423400 Larkins Michael FR 0.00 125634 Hanson Henry FR 2.88 280023 McRoberts Richard JR 1.90 260000 Johnson Stanley ? ? 231222 Wilson Susie SO 3.80 234121 Thomas Wendy FR 4.00 324652 Delaney Danny SR 3.35 123250 Phillips Martin SR 3.00 322133 Bond Jimmy JR 3.95 333450 Smith Andy SO 2.00 SQL Query SELECT First_Name ,Last_Name ,Class_Code ,Grade_Pt FROM Student_Table ; Related Posts 32 Complex SQL Queries