Welcome To Datawarehouse World

  Global Temporary Table Volatile Table It will use Temp Space It will use the spool space User Session based…Once the user session is ended then table will be dropped. The GTT definition will in data dictionary. User Session based…Once the user session is ended then table will be dropped. VT definition will not be in Data Dictionary. You can implement the alert mechanism in GTT You can’t use the alert mechanism in VT

  Primary Index Secondary Index It can be created while creating a table. It can able to create after table creation too. It can't be modified… If we need to change the PI then needs to drop the table and needs to recreate it along with new PI… It can be modified. Only one PI can able to create it in table We can create around 64 columns oriented SI.. It will perform the full table scan It will not perform the full table scan and it will improve the performance. If we didn’t define the PI then TD will automatically will assign the PI It’s an optional

 Secondary Index: 1. It can be created to improve the performance. 2. Its an optional. 3. It will not do the table full scan...     Note: primary index will do the table full scan. 4. Secondary index will be stored in Separate subtable and it will required some additional space in AMP... 5. Secondary index can able to create once the table got created.     Note: Primary index can able to create while creating a table. 6.Secondary index has a limit of 64 columns only. 7.Secondary index can be changed. 8. The below is the syntax for creating a Secondary index. Create Unique index (Movie_No) on Movie_List;

 Temp Space :  It's a unused space in Permanent Space and the Global Temp Table is used the Temp space.

 Spool Space is the unused permanent space which is used by the system to execute the sql query. Note:  Each and every users are having the Spool space limit...if the spool space limit got exceeded then will get a spool space error message. 

 Permanent Space:  They are just maximum amount of space ETL user or user or Database can use it. The amount of permanent space is divided by the number of Amps. Note:  If allocated space exceeds in AMP then we will get an out of space / memory issue. 

      SELECT e.emp_num, e.name, f.emp_num, f.name, e.country      FROM employee AS e, employee AS f      WHERE e.country = f.country      AND   e.emp_num < f.emp_num      ORDER BY e.emp_num, f.emp_num; Referred : Teradata Document.

Select A.MovieName, B.ActorName From Movie_List A  Right Outer Join Actor_List B On (A.Movie_No = B. Movie_No);

Select A.MovieName, B.ActorName From Movie_List A  Left Outer Join Actor_List B On (A.Movie_No = B. Movie_No);

Model 1: Select A.MovieName, B.ActorName From Movie_List A  Inner Join Actor_List B On (A.Movie_No = B. Movie_No); Model 2: Select A.MovieName, B.ActorName From Movie_List A  Join Actor_List B On (A.Movie_No = B. Movie_No); Model 3: Select A.MovieName, B.ActorName From Movie_List A, Actor_List B where A.Movie_No = B. Movie_No And A.Movie_No = 1;

Inner Join: It will return the matches records from multiple tables and we can able to use maximum 128 tables and views for per query block. Cross Join: The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join. (Referred : SqlShack)...Thanks SqlShack. Outer Join:     1. Left Outer Join: Return all the records from Left table and will return only matches records from right table.     2. Right Outer Join: Return all the records from right table and will return only matches records from left table.     3. Full Outer Join: it will combine left and right tables and will return matches and non matches records. Self join:  A single table considered as a two tables by using correlation names to rederive the records.

 Non Unique Primary Index?... It can accept duplicate records. Note: In Datawarehouse world, the Stage layer table used to create using NUPI only... Ex: Create SET table Movie_World ( Movie_Id Integer, Movie_Name Varchar (30) ) Primary index (Movie_Id);

 UPI : Unique Primary Index should not have any duplicate records. Create Set Table Movie_Warehouse ( Movie_Id Integer, Movie_Name Varchar (50), Movie_Release_Date Date Format 'YYYY-MM-DD', Movie_Language Varchar (30) ) Unique Primary Index (Movie_Id);

 Null IF : It will return a Null value if the arguments are equal. Syntax: Select Movie_Name, NullIF (Movie_No, 3) as Movie_Id From Movie_List;

Coalesce Statement:  If we received a Null value for movie id then it will reflect like Movie_Not_Found value. Select Movie_Name, Coalesce (Movie_Id,'Movie_Name_Not_Found') From Movie_List;

Select Movie_Name, Case Movie_No  when 1 then 'Comedy Movie' when 2 then 'Action Movie' Else 'Science Fiction Movie' End as Movie_Type From Movie_List;

 Select count (*) from Emp;

 Select Current_Date; Select Current_Time; Select Current_Timestamp; Select Database;

 Select Extract (Year From Current_Date); Select Extract (Month From Current_Date); Select Extract (Day From Current_Date); Select Extract (Minute From Current_Date);

 Select cast (Current_Date AS Integer);

Substring: Ex: Select Substring ('Datawarehouse' From 1 For 4) Result : Data Substr Ex: Select Substr('Datawarehouse',1,4)  Result : Data Concatenation symbol - || Select 'Data' || ' ' || 'Warehouse' Result : Datawarehouse Upper Case : Select Upper ('Data') Result : DATA Lower: Select Lower ('Data') Result : data

Minus : It will provide the result which one is not matching from both tables... Note: It will provide the not matching result from first select table only. Select Emp_No from Emp Minus Select Emp_no from Emp_Dep;

Intersect will provide the corresponding matches value from both tables. Select Emp_No from Emp intersect Select Emp_no from Emp_Dep;

 UNION all will allow the duplicates. Select EMP_No from EMP UNION ALL Select EMP_NO from  EMP_DEP;

 Select Emp_No From  EMP UNION Select Emp_No From  Emp_Dep; Union will combine the value from both tables..

Select EMP_NO, EMP_First_Name, EMP_Last_Name, count (*) from  EMP  Group by EMP_No, EMP_First_Name, EMP_Last_Name;

 Delete From EMP  Where Emp_No = '101';

Update table  SET First_Name ='Teradata' Where Emp_Number = 101;

 Insert into EMP_BKP ( Emp_No, Emp_First_Name, Emp_Last_Name, Date_Of_Birth ) Select Emp_No, Emp_First_Name, Emp_Last_Name, Date_Of_Birth From EMP;

 Insert into EMP ( EMP_NO, EMP_FIRST_NAME, EMP_LAST_NAME, DATE_OF_BIRTH ) VALUES ( 101, 'FirstName', 'LastName', '1947-08-15' );

 Drop table EMP;

 Alter table EMP ADD BIRTHDATE DATE FORMAT 'YYYY-MM-DD', DROP DOB;

Create SET Table EMP, Fallback  ( EMP_No Integer, Emp_First_Name Varchar (30), Emp_Last_Name Varchar (30), DOB Date format 'YYYY-MM-DD', Dept Varchar (10) ) Unique Primary Index (EMP_NO); SET : will not store duplicate records Multiset : will allow the duplicate records. Fallback: Fallback protects the table data by storing the second copy of rows of a table on another AMP called as Fallback AMP. If one AMP fails, then the fallback rows are accessed. With this, even if one AMP fails, data is still available through fallback AMP

  Volatile Table  − The data inserted into a volatile table is retained only during the user session. The table and data is dropped at the end of the session. These tables are mainly used to hold the intermediate data during data transformation. Global Temporary Table  − The definition of Global Temporary table is persistent but the data in the table is deleted at the end of user session.

Global Temporary table definition persist across session and Volatile table are session specific tables. Index can be created on Global Temporary table and not on Volatile table. Global temporary table uses Temporary space of login user and Volatile table uses spool space of login use. In a single session 2000 Global temporary table can be materialized and 1000 Volatile tables can be materialized.

It provides much better performance while dealing with execution of scripts and commands. It provides more programming features and is considered to be superior than Bash. It handles loop very well.

  Level 1 – Critical Impact/System Down. Complete system outage. Level 2 – Significant Impact/Severe downgrade of services. Level 3 –Minor impact/Most of the system is functioning properly. Level 4 – Low Impact/Informational.

1.Average First Response Time (AFRT) 2.Average Reply Time (ART) 3.Number of Support Tickets 4.Number of Ticket Backlog 5.First Contact Resolution Rate 6.Resolution Rate 7.Average Handle Time 8.Vertical / Project team feedback & comments

  Minimizing Deadlock by Specifying the LOCKING Request Modifier You can use the LOCKING request modifier to improve performance and reduce conflicts in the following ways. Using the NOWAIT option to abort a transaction if a lock cannot be granted immediately. Using the LOCKING ROW FOR WRITE syntax to eliminate the chance of a deadlock during an update when multiple transactions select and then update the same row. Applying a higher or lower severity of lock than that normally applied by the Lock Manager.

index ="main" | status ="404" | top limit = 50 url Naming concatenation:  index ="main" | eval new_field = 'Firstname' + 'LastName' | table "Firstname" + "Lastname", new_field . for integer concatenation. index ="main" | eval new_field = 'Firstname' + 'LastName'.100 | table "Firstname" + "Lastname", new_field override: index = "main"| eval "firstname" = 'Firstname'.100 | table "firstname" index ="main" | eval F1 = "Karthik", F2 = "Sid" | table "F1","F2"

1. Erex - do not know the regular expression : index = "main" | erex monthday examples ="7/01" 2. Abstract - The original text replaced by the summary : index ="main"| abstract maxlines = 50 3. Typer - to get to know event type : index ="main" | typer  4. Rename - rename the value as we want : index = "main" | rename Karthik as "Sharavan" 5. Anomalies - look for field values are unusual or unexpected.: index = "main" | anomalies by source 6. FillDown - replaces the null value using last non null values : index = "main" | filldown count 7. Acum - calculate running total | sourcetype=access_* status=200 categoryId=STRATEGY | chart count AS views by productId | accum views as TotalViews 8. AddTotals:arithmetic some of all numeric values : source="addtotalsData.csv" | chart sum(sales) BY products quarter

1. The Bourne Shell 2. The C Shell 3. THe Korn Shell - .Ksh (easy to use) 4. The GNU Bourne - Again Shell

 Parsing engine: Receive the query from client and will check the syntax error, user access, receive the result from client and send to client. Message parsing layer: it will receive the execution plan and send to AMP and will receive the result and will send to Engine. AMP : SOrting, filtering and data type conversion

  1. Exclusive lock : It will help to lock when undergoing some structural change.  If it’s applied then there is no other locks can able to apply in top of it. 2. Write Lock 3. Read Lock 4. Access lock : it will allow read and write locks but it will not allow exclusive lock.

 What is Splunk?... Splunk  is ‘Google’ for our machine-generated data. It’s a software/engine that can be used for searching, visualizing, monitoring, reporting, etc. of our enterprise data. Splunk takes valuable machine data and turns it into powerful operational intelligence by providing real-time insights into our data through charts, alerts, reports, etc. How to extract IP addresses from logs?... rex field=_raw  "(?<ip_address>\d+\.\d+\.\d+\.\d+)" How to troubleshoot splunk performance?... Check splunkd.log for errors Check server performance issues, i.e., CPU, memory usage, disk I/O, etc. Install the SOS (Splunk on Splunk) app and check for warnings and errors in its dashboard Check the number of saved searches currently running and their consumption of system resources Install and enable Firebug, a Firefox extension. Log into Splunk (using Firefox) and open Firebug’s panels. Then, switch to the ‘Net’ panel (we will have to enable it). The Net panel will show ...