Profiles

A set of common parameters that can be applied to a group of users.

Now you have a question in your mind, what set of common parameters?

  • These parameters include: Account id's, default database, spool space allocation, temporary space allocation and password attributes (expiration, minchar, etc.)
  • Using profiles simplify user management, a change of a common parameter requires an update of a profile rather than updating each user
  • Profiles are managed by
    • CREATE PROFILE, MODIFY PROFILE, DROP PROFILE, and SELECT PROFILE

Example: You have a group of 1000 users that are assigned same temporary space, same spool space, same account ID and same default database, if you have to change a single parameter for 1000 users is time consuming. This can be over come by creating a profile with the assigned parameters and assigning that profile to the users. This eases the user management

Implementing profiles:

  • CREATE PROFILE: needed to create profiles
  • DROP PROFILE: needed to modify and drop profiles

Initially only DBC has CREATE PROFILE and DROP PROFILE access rights. These can given to DBA's by:

GRANT CREATE PROFILE, DROP PROFILE TO DBA WITH GRANT OPTION;

In the following section we will see, how to create a PROFILE and assigning a user to a profile:

Create PROFILE statement:

CREATE PROFILE [profile_name] AS ACCONT = [account_id/null], DEFAULT DATABASE = [db_name/null], SPOOL = [bytes/null], TEMPORARY = [bytes/null], PASSWORD = [password];

CREATE PROFILE Emp AS ACCOUNT = '$H2$acct01&H', DEFAULT DATABASE = 'ABC', SPOOL = '20000000', TEMPORARY = '100000000'...;

Assigning PROFILE to the user: It can be assigned using CREATE USER and MODIFY USER

CREATE USER Emp01 AS..., PROFILE = Emp;

MODIFY USER Emp02 AS PROFILE = Emp;

To remove profile for a USER:

MODIFY USER Emp02 AS PROFILE=NULL;

Profile definitions are applied to all users, overriding specifications at system or user level. All members inherit changed profile parameters. The impact on current users is as follows:

  • SPOOL and TEMP space are imposed immediately
  • Password attributes take effect upon next logon
  • Database and Account ID's are considered at next logon unless the user submits a SET SESSION ACCOUNT statement

Order of precedence of parameters:

  • Specify database or account id's at session level
  • Specified parameters in a Profile
  • CREATE USER or MODIFY USER statements

Source: Teradata Manuals

Read more ...
0 comments

Databases and Users

  • A user or database in Teradata is uniquely named permanent space that can store objects like tables, indexes, procedures, triggers, functions and other databases and users.
  • Views, macros, roles and profiles do not consume space, but their definitions are stored in the data dictionary
  • A database is repository for database objects, privilege definitions and space limits.
  • A user is same as a database except it can logon to Teradata Database with password, establish session and submit requests.

Users vs. Databases

User Database
A user performs action Database is passive
Users have passwords and startup strings Databases do not
Users can log on to the Teradata Database, establish sessions and submit SQL statements Databases cannot

The maximum number of databases and users allowed on Teradata Database is 4.2x109

Read more ...
0 comments

Spaces

Space Terminology

  Perm Spool Temp
Max The maximum number of bytes available for table, index and permanent journal storage in a database or user A value used to limit the number of bytes the system will consume to create spool files for a user A value used to limit the number of bytes the system will use to store data for global temporary tables for a user
Current The total number of bytes in use to store the tables, subtables, and permanent journals contained in the database or user The number of bytes currently in use for running transactions The number of bytes in use for global temporary tables
Peak The largest number of bytes actually used to store data in this user since the value was last reset The maximum number of bytes used by a transaction run for this user since the value was last reset The maximum number of bytes used by global temporary tables for a user since the value was last reset

Space Limits:

  • Perm and spool space limits are assigned at the database or user level and not at table level
  • When creating users or databases, perm space limits are deducted from the available space of the immediate owner
  • The spool and temp space limits should not exceed that of the immediate owner at the time of you create the object
  • If you do not specify the spool or temp limit, the new object inherits the limit from its immediate owner

Example of space terminology:

Lets say we have a user HR with MaxPerm space 25 MB, MaxSpool space 50 MB and MaxTemp space 30 MB. Now HR creates two users HR01 and HR02 with 6 MB, 4 MB MaxPerm space respectively and assigning 35 MB MaxSpool space to HR01, as there is spool assigned to HR02, it spool space defaults to its immediate parent MaxSpool space i.e. 50 MB. Now HR is left with 15 MB of MaxPerm space. Later HR drops HR01, HR MaxPerm space increases to 21 MB, since it regains Perm space fro HR01. As there is no MaxTemp space assigned to HR01 and HR02 it is defaulted to their immediate parent to 30 MB.

td

Giving one user to another:

  • When you give one object to another object in the hierarchy, all the space allocated to the object goes with it. If you drop the object the space goes to the immediate owner
  • When you give database or user, all descendants (child) of the given object remain descendants (child) of the given object
  • When you give an object to a new parent, the ownership of space is transferred  and limits remain the same
Read more ...
0 comments

Concatenation and Correlated Subqueries - Performance Tuning

Concatenation allows to retrieve data correlated to the MIN/MAX function in a single pass

Example:

1. Employees with highest salary in each department

This can be written as using correlated subquery

SELECT D_No, Sal, L_Na, F_Na
FROM test.employee ee
WHERE Sal IN
(SELECT MAX (Sal)
FROM test.employee em
WHERE ee.D_No=em.D_No
GROUP BY D_No)
ORDER BY D_No;

Answer Set:

Dept_No Salary_Amount Last_Name First_Name
10 70000 pitt           arun     
20 75000 lucas          frank    
30 60000 jones          indiana  
40 65000 phips          carla    
50 60000 stifler        tom      
50 60000 young          nikie    
60 60000 penn           arun     
70 70000 paul           ak       
80 70000 nelson         julie    
90 75000 white          sam      
100 45000 pitt           mark     

It can also be written as

SELECT D_No,
MAX(Sal || ' ' || L_Na || ',' ||F_Na)
FROM test.employee
GROUP BY D_No
ORDER BY D_No ;

Answer Set:

Dept_No Maximum(((((Salary_Amount||' ')||Last_Name)||',')||First_Nam
10       70000 pitt           ,arun     
20       75000 lucas          ,frank    
30       60000 jones          ,indiana  
40       65000 phips          ,carla    
50       60000 young          ,nikie    
60       60000 penn           ,arun     
70       70000 paul           ,ak       
80       70000 nelson         ,julie    
90       75000 white          ,sam      
100       45000 pitt           ,mark     

Did you observe the two answer sets? Can you tell the difference and how to overcome it? Please leave your answers in comments.

Read more ...
0 comments
Labels:

Correlated Subquery - Performance Tuning

A correlated subquery (CS) is a subquery whose outer query results are processed a row at a time, against the subquery result. The subquery result is computed for each row processed.

  • a CS eliminates the need for intermediate or temporary tables
  • a CS is fully integrated with global join planning to minimize costs
  • CS is significantly faster than the query using temporary tables

Example:

1. Employee with highest salary in each department

SELECT L_Na,Sal, D_No
FROM test.employee ee
WHERE Sal = (SELECT MAX (Sal)
FROM test.employee em
WHERE ee.D_No=em.D_No);

Answer Set:

Last_Name Salary_Amount Dept_No
stifler        60000 50
pitt           70000 10
young          60000 50
jones          60000 30
paul           70000 70
penn           60000 60
white          75000 90
nelson         70000 80
pitt           45000 100
lucas          75000 20
phips          65000 40

2. Employees whose salary is greater than the department average salary

SELECT L_Na, Sal, D_No
FROM test.employee ee
WHERE Sal > (SELECT AVG (Sal)
FROM test.employee em
WHERE ee.D_No= em.D_No);

Answer Set:

Last_Name Salary_Amount Dept_No
paul           70000 70
phips          65000 40
shook          65000 10
pitt           70000 10
jones          60000 30
lucas          75000 20
wall           65000 10
Read more ...
0 comments
Labels:

Heartbeat Query - Performance Tuning

A heartbeat query can be any SQL statement run at specific intervals whose response time is
being monitored.

  • Measure response time as an indicator of system demand or system/database hangs
  • Initiate an alert system if response time degrades so that you can take appropriate action

Heartbeat queries are classified as

  • System
  • Production

System Heartbeat Queries:

  • used to check overall system/database hangs, to react when response time reaches a certain thresholds, or when stalled, such as send alert and/or capture system level information
  • it should execute diagnostics that capture the state of the system if performance stalls
  • they are intended to focus on Teradata core system. They should be short running , low impact queries on tables that are normally not write locked
  • they are more useful when run frequently (for example 5-10 mins)
  • they should be run on system nodes (which eliminates other factors like middle tiers, network connections)

Example of system heartbeat query:

select * from dbc.dbcinfo;

As the query runs, Teradata Manager can monitor the query, logging start and end times.

Production Heartbeat Queries:

  • response times play a major role in the production heartbeat queries, these need to be monitored and stored for tracking purposes
  • response time are an indicator of system demand. When system demand is high, heartbeat response is high
  • they can be run on production user tables
  • monitor overall response
  • monitor specific area of the job mix
  • can be more complex and similar in nature to a particular type of production query, running in the same Priority Scheduler performance group
  • they are run less frequently (for example 20-60 mins)
Read more ...
0 comments
Labels:

Primary Key vs. Foreign Key

Primary Key:-

In the relational model, a Primary Key (PK) is used to designate a unique identifier for each row when you design a database. A Primary Key can be composed of one or more columns. In the example below, the Primary Key is the employee number.

Primary Key Rules:-

Rules governing how Primary Keys must be defined and how they function are:

Rule 1: Only one Primary Key per table.
Rule 2: A Primary Key value must be unique.
Rule 3: The Primary Key value cannot be NULL.
Rule 4: The Primary Key value should not be changed.
Rule 5: The Primary Key column should not be changed.
Rule 6: A Primary Key may be any number of columns.

Rule 1: One Primary Key:-

Each table must have one, and only one, Primary Key. In any given row, the value of the Primary Key uniquely identifies the row. The Primary Key may span more than one column, but even then, there is only one Primary Key.

Rule 2: Unique PK:-

Within the column(s) designated as the Primary Key, the values in each row must be unique. No duplicate values are allowed. The Primary Key's purpose is to uniquely identify a row. In a multi-column Primary Key, the combined value of the columns must be unique, even if an individual column in the Primary Key has duplicate values.

Rule 3: PK Cannot Be NULL:-

Within the Primary Key column, each row must have a Primary Key value and cannot be NULL (without a value). Because NULL is indeterminate, it cannot "identify" anything.

Rule 4: PK Value Should Not Change:-

Primary Key values should not be changed. If you changed a Primary Key, you would lose all historical tracking of that row.

Rule 5: PK Column Should Not Change:-

Additionally, the column(s) designated as the Primary Key should not be changed. If you changed a Primary Key, you would lose all the information relating that table to other tables.

Rule 6: No Column Limit:-

In the relational model, there is no limit to the number of columns that can be designated as the Primary Key, so it may consist of one or more columns. In the example below, the Primary Key consists of three columns: EMPLOYEE NUMBER, LAST NAME, and FIRST NAME.

Foreign Key:-

A Foreign Key (FK) is an identifier that links related tables. A Foreign Key defines how two tables are related to each other. Each Foreign Key references a matching Primary Key in another table in the database. For example, in the table below, the Department Number column that is a Foreign Key actually exists in another table as a Primary Key.

Foreign Key Rules:-

Rules governing how Foreign Keys must be defined and how they operate are:

Rule 1: Foreign Keys are optional.
Rule 2: A Foreign Key value may be non-unique.
Rule 3: The Foreign Key value may be NULL.
Rule 4: The Foreign Key value may be changed.
Rule 5: The Foreign Key column may be changed.
Rule 6: A Foreign Key may be any number of columns.
Rule 7: Each Foreign Key must exist as a Primary Key in the related table.

Rule 1: Optional FKs:-

Foreign Keys are optional; not all tables have them. Tables that do have them can have multiple Foreign Keys because a table can relate to multiple other tables. In the example table below:

  • The Department Number Foreign Key relates to the Department Number Primary Key in the Department Table elsewhere in the database.
  • The Job Code FK relates to the Job Code PK in the Job Code Table, elsewhere in the database.

Rule 2: Unique or Non-Unique FKs:-

Duplicate Foreign Key values are allowed. More than one employee could be assigned to the same department.

Rule 3: FKs Can Be NULL:-

NULL (missing) Foreign Key values are allowed. For example, under special circumstances, an employee might not be assigned to a department.

Rule 4: FK Value Can Change:-

Foreign Key values may be changed. For example, if Arnando Villegas moves from Department 403 to Department 587, the Foreign Key value in his row would change.

Rule 5: FK Column Can Change (in Certain Circumstances):-

You may add additional Foreign Key columns as needed. If you change an existing Foreign Key column, however, you may lose the relationship information between that table and the table containing the Primary Key.

Rule 6: FK Has No Column Limit:-

The Foreign Key may consist of one or more columns. A multi-column foreign key is used to relate to a multi-column Primary Key in the related table. In the relational model, there is no limit to the number of columns that can be designated as a Foreign Key.

Rule 7: FK Must Be PK in Related Table:-

Each Foreign Key must exist as a Primary Key in the related table. A department number that does not exist in the Department Table would be invalid as a Foreign Key value in the Employee Table.

This rule can apply even if the Foreign Key is NULL, or missing. Remember, a missing value is defined as a non-value; there is no value present. So the rule could be better stated: if a value exists in the Foreign Key column, it must match a Primary Key value in the related table.

Read more ...
1 comments