DBC Tables or System tables on Teradata


System tables uses to track the database objects. In essence, access is a set of relational database tables (the system tables) about a set of relational database tables (the user project). System tables should not be altered directly by any user. For example, do not attempt to modify system tables with DELETE, UPDATE, or INSERT statements, or user-defined triggers.

System tables are much like the tables you create in a database – with a few exceptions. First, they are identified internally as system objects so that you can differentiate your regular tables from system tables. Secondly, some system tables are inherently read-only. System table are documented here solely for the purpose of giving us a better understanding of how Teradata RDBMS works internally. The following table describes each of the system tables.

DBC.TVM
Describes each table, view, or macro on the Teradata RDBMS. Identify table.


DBC.TVFIELDS
Describes each column on the Teradata RDBMS.


DBC.User
Identify user.

DBC.ACCESSRIGHTS
Describes each database and userid on the Teradata RDBMS.



DBC.DBASE
Describes each database and userid on the Teradata RDBMS. Identify database.


DBC.INDEXES
Describes columns contained in indexes in the Teradata RDBMS.


DBC.ResUsage
Log resource usage data.

Example

SELECT ACCOUNTNAME, USERNAME, SUM(CPUTIME), SUM(DISKIO) FROM
DBC.AMPUSAGE
WHERE SUBSTR(ACCOUNTNAME, 1, 2) = ‘CB’
GROUP BY USERNAME, ACCOUNTNAME
ORDER BY USERNAME, ACCOUNTNAME;
 *** Query completed. 9 rows found. 4 columns returned.
 *** Total elapsed time was 2 seconds.
AccountName     UserName             Sum(CpuTime)      Sum(DiskIO)
————–  ————-  ——————  —————
CB990902        JANETJONES               1,498.64        3,444,236
CB990903        JANETJONES                 934.23        1,588,764
CB990904        JANETJONES                 883.74          924,262
CB990905        JANETJONES                 214.99          200,657
CB990902        JOHNSMITH                  440.05          396,338
CB990903        JOHNSMITH                  380.12          229,730
CB990904        JOHNSMITH                  112.17          184,922
CB990905        JOHNSMITH                   56.88           99,677
CB990902        SAMOREILLY                 340.34          410,178

Performance – Teradata Manager

You can configure the Teradata Manager data collection service to collect performance data
with respect to:
• AMPUsage
• DBQL
• Te r a d a t a DWM

• Heartbeat queries
• Priority Scheduler
• ResUsage
• Spool space
• Table s pa ce

Teradata Manager data collection resources help in:
• Analyzing workload trends
• Analyzing historical resource utilization

Collecting historical data for performance

ResUsage History
Teradata Manager can summarize key ResUsage data up to 1 row per system per time
period specified. Teradata recommends retaining 3 to 6 months of detail to accommodate
various analysis tools, such as Teradata Manager itself, Visual Edge, and so on.

AMPUsage History
Teradata Manager can summarize to 1 row per system per account per time period
specified. Moreover, it can retain 1 day of detail. Teradata recommends deleting excess
detail to keep ongoing summary collection efficient.

DBQL History
Teradata Manager summaries key DBQL data to 1 row per user / account / application ID
/ client ID per time period. Teradata recommends retaining 13 months of copied detail.
That is, Teradata recommends copying detail to another table daily. You should delete the
source of copied detail daily to keep online summary collection efficient.

DQBL tables – Performance data collection

You can use DBQL tables to collect and evaluate:
• System throughput
• Response time
• Query details, such a query step level detail, request source, SQL, answer size, rejected
queries, resource consumption
• Objects accessed by the query

In addition to being able to capture the entire SQL statement, regardless of the length of the
SQL, DBQL also provides key insights into other aspects of a query such as whether it was
aborted, delayed by Teradata DWM, the start and end time, and so on.

DBQL operates asynchronously. As a result, the logging activity has a much lower impact on
the overall response time of given transactions.

PMON – Performance Monitor

From a product standpoint, Performance Monitor (PMON) is a tool bundled with the Teradata Manager product. Teradata Manager and PMON functionality has been replaced by Teradata Management Portlets for Viewpoint. As part of that transition, Teradata Manager and PMON 13.0 releases are the last releases of those products. For Teradata DB 13.10, you will need to use Teradata Management Portlets.


Performance Monitor (PMON) provides a real-time system status with functional areas for monitoring system activity


Functional areas:
Configuration summary
Performance summary and resource usage, both physical and virtual
Session and lock information
Session history
Control functions
Graphic displays of resource dataGraphic displays of session data


Features of PMON:
PMON uses charting facilities to present the data to identify abnormalities. Color is used to indicate warning conditions.
You can configure the Alert thresholds, color settings, and automatic data refresh rate values using the PMON Alert tab.

Teradata – Orange books

what are orange books?

teradata reference books you can get from T@YS or teradata @ your service. each company which has a license to use teradata would have some employees with user ID an passwords

SQL to return the first record in a group

Lets say we have a log table containing a record of page views for an intranet:

DateStamp IpAddress Page
20/06/2011 192.168.0.10 home
21/06/2011 192.168.0.11 about
21/06/2011 192.168.0.10 home
22/06/2011 192.168.0.12 home
22/06/2011 192.168.0.10 home
23/06/2011 192.168.0.11 about

To query this table to show the earliest date when each page was viewed requires just a simple group-by query:

SELECT Page, min(DateStamp) as FirstViewed
FROM LogTable
GROUP BY Page

which will give:

Page FirstViewed
about 21/06/2011
home 20/06.2011

But if we wanted to include the IP address associated with each of those first page views, then a simple join will no longer suffice and our query becomes significantly more complex.

SELECT detail.Page, detail.DateStamp as FirstViewed, detail.IpAddress
FROM LogTable detail
INNER JOIN (
   SELECT Page, min(DateStamp) as FirstViewed
   FROM LogTable
   GROUP BY Page) inside
ON inside.Page = detail.Page
AND inside.FirstViewed = detail.DateStamp

This returns:

Page FirstViewed IpAddress
home 20/06/2011 192.168.0.10
about 21/06/2011 192.168.0.11

Delete Duplicate Records using SQL

By duplicate record I mean that every field in one record is identical to every field in a different record, i.e. a duplicate is where there is no way of telling two or more records apart. If you just need to remove records which are similar (i.e. one or more fields are identical but there are one or more fields which are different) then instead refer to how to delete similar records.

To check that you have duplicate records in your table do the following:

select count(*) from MyTable

and

select distinct * from MyTable

unfortunately SQL does not permit a select count(distinct). You have duplicate records if the number of records returned by the second query is less than the number of records returned by the first.
Unfortunately there is no way in SQL to delete one of these duplicates without deleting all of them. They are identical after all, so there is no SQL query that you could put together which could distinguish between them.
What you can do is to copy all the distinct records into a new table:

select distinct *

into NewTable

from MyTable

This query will create a new table (NewTable in my example) containing all the records in the original table but without any records being duplicated. It will therefore preserve a single copy of those records which were duplicated.
Because this query creates (and populates) a new table, it will fail if the table already exists.

SQL Self Join – All employees in same country

self-join is joining a table to itself

Example
A query to find all pairings of two employees in the same country is desired. If there were two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, a normal join operation could be used to find the answer table. However, all the employee information is contained within a single large table.

Consider Employee table such as the following:

Employee Table
EmployeeID LastName Country DepartmentID
123 Rafferty Australia 31
124 Jones Australia 33
145 Steinberg Australia 33
201 Robinson United States 34
305 Smith Germany 34
306 John Germany NULL

An example solution query could be as follows:

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country

FROM Employee F

INNER JOIN Employee S ON F.Country = S.Country

WHERE F.EmployeeID < S.EmployeeID

ORDER BY F.EmployeeID, S.EmployeeID;

Which results in the following table being generated.







Employee Table after Self-join by Country

EmployeeIDLastNameEmployeeIDLastNameCountry


123Rafferty124JonesAustralia


123Rafferty145SteinbergAustralia


124Jones145SteinbergAustralia


305Smith306JohnGermany