USEFULFREETIPS.COM|Blog
Currently viewing the category:
"Teradata"
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
- Share this:
- Share
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. Continue reading
- Share this:
- Share
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:
SELECT
DATABASENAME as Database
,TABLENAME as Table
,SUM(CURRENTPERM)/(1024*1024*1024) (DECIMAL(15,6)) as Current_Size
FROM DBC.TABLESIZE
WHERE DATABASENAME = ‘YOUR_DATABASE_NAME‘
and TABLENAME=’YOUR_TABLE_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. Continue reading
- Share this:
- Share
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
- Share this:
- Share
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 Continue reading
- Share this:
- Share
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
- Share this:
- Share
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. Continue reading
- Share this:
- Share
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 as Continue reading
- Share this:
- Share
Connect With Us
Like Us on Facebook
My Favorite
Archives
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010
- October 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- February 2010
Recent Comments