The above diagram shows a very basic archiecture of TERADATA and is very useful and easy for learning purpose. The first component is PE (Parsing Engine)Continue Reading
TERADATA is a Database server which can handle multiple clients concurrently. Teradata is a RDBMS (Relational Database Management System) which is strongly recommended for handling huge data warehouse environment. The unique feature of Teradata isContinue Reading
In Teradata every Error is shown by an Error Code and corresponding description. Sometimes while using some utilities we may have some records in Error Tables. At that time we can see ErrorCodes in the table however Error Description is missing. So it may become little difficult to understand the issue.Continue Reading
To view recent queries in Teradata there are two simple ways :
First one is you can enable history if you are using some utility tool for running SQL’s like Teradata SQL Assistant. To enable history in SQL Assistant go to view –> Show History. It will list down all the SQL executed on the system . The disadvantage is it will display only those queries which were executed from that system and not all the queries which were executed on Teradata server.
Second way of viewing recent queries in Teradata is to use DBC.QRYLOG .
SEL * FROM DBC.QRYLOG;
This will display all the queries executed on the Teradata Server. Along with the queries it will also show which user executed the query and at what time . You can get much more information from this query. As the query will return huge number of rows if your Teradata Server is mature , you can add some filters in order to minimize the output rows.
We often come across the situation when we need to find out how much space a table in our database is actually occupying. This may help us in identifying the tables which are using large amount of space in Database or the tables which are not using much space in Database.
To find out how much space a table is using in a database use below query:
DATABASENAME as Database
,TABLENAME as Table
,SUM(CURRENTPERM)/(1024*1024*1024) (DECIMAL(15,6)) as Current_Size
WHERE DATABASENAME = ‘YOUR_DATABASE_NAME‘
GROUP BY 1,2;
Where ‘YOUR_DATABASE_NAME‘ is the name of the database for which u need to do the analysis and ‘YOUR_TABLE_NAME’ is the name of the table for which you want to do the analysis.
This Query will return 3 columns Database, Table and Current_Size in GB .Based on the analysis of these data we can easily see that how much space the table is occupying in the database. However if you want to see how much all the tables in a Database is occupying , just remove the filter on Tablename from the query to get all the tables in a Database with the Permanent Size for each tables.
In Teradata , OLAP window functions play an important role while handling complex SQL requirements. There are various WINDOW functions available in Teradata. However two of the ‘tricky’ OLAP Window functions available in Teradata are : RANK and ROW_NUMBER. To know more about ROW_NUMBER in Teradata read this post. So what is the difference between ROW_NUMBER and RANK in Teradata ? The function RANK in teradata resembles very much to rank we have in real life. We tend to give ranks to entities on the basis of values in some columns. However ROW_NUMBER in Teradata is used to generate SEQUENCE number etc. Let us take an example . In this we have a Table Student which has Student Name and Marks obtained by a Student in three Subjects.Continue Reading
In Teradata it is actually very important to know what is the Storage size required by the different datatypes.If you know the storage requirement of different datatypes used in your tables in Database, you can compute space requirement easily.So storage requirement for few of the most common datatypes used in Teradata are :
BYTEINT – 1 BYTE
SMALLINT – 2 BYTES
INTEGER – 4 BYTES
BIGINT – 8 BYTES
How to handle UTF8 Unicode Character set in Teradata.Teradata provides few of the best data loading utilities to its user namely Fastload and Multiload.Both of the utilities are extremely popular in Teradata Database world.User can import data from a data file into the RDBMS table very easily and tremendously quickly using these utilities.However , while loading data into the tables from Data files one must take precaution regarding the file format of data file.Generally, it is ASCII for Data files as well as Teradata Utilites.Continue Reading
Checking SQL history in Teradata can be very important.Few of the key points can be :
Check commands you executed in Teradata.
Check commands executed by other users in Teradata.
Who modified any object like table etc in Teradata.
When some SQL commands were executed in Teradata.
From which IP SQL commands were executed in Teradata.
To check SQL History in Teradata, we refer to dbc.QryLog. dbc.QryLog is a view which contains information about the SQL Queries executed in Teradata.We can use the below mentioned query for checking history of SQL queries you executed in Teradata.
We all know that Teradata by default is case insensitive.Even the column definitions while creating a table is NOT CASESPECIFIC by default.However if the requirement is to create a column as casespecific then what is the efficient way of using such columns in filtering(WHERE clause)? You can create a CASESPECIFIC column by explicitly mentioning that column as CASESPECIFIC in its DDL.However if you are using that column in WHERE claus it may result in undesirable results.You should be careful while using CASESPECIFIC columns.Say if in a table with two columns, NAME(CASESPECIFIC) and CITY I am having an entry asContinue Reading
We may come across a situation when one of our process may take more time than expected which may eventually result in affecting other processes in UNIX.Suppose we have a script which is performing some task.However due to some constraint we want this task to be completed within some specific amount of time.Say we have time slots for all our tasks and if any task is exceeding the time allowed for that task , it should be terminated.So we will see how to kill a process if it is taking more time than specified time for that process.
Following code may help in achieving exactly what we’ve been discussing here :