Memory Management in TeraData
Memory Management
a) Permanent Memory
All tables, procedures, generals, indexes, sub tables etc Required permanent memory
Views, Macro, Trigger doesn’t required any permanent memory
A database can be created without permanent memory]
b) Spool Memory
i) It stores intermediates result and calculation(Select statement result, join operation data etc)
ii) View, macros, derived tables etc, store under spool memory
C)Temporary Memory
i) It holds intermediate result and calculation[generally session level information]
ii)global temporary tables store under temporary memory
Note- The un-used permanent can be taken as spool(or) temper are memory
There is a database with 100GB of memory, 60 GB Assign to spool, 40GB Assign to permanent, 20 GB OF Permanent data stored, How much spool memory available for business operations.
Spools = Assigned spool+ un- used permanent
=60+20
=80GB
Interested in mastering Teradata Training? Enroll now for FREE demo on
Current memory
Current occupied memory is current memory
Peak memory
The maximum occupied of memory earlier is peak memory
Max memory
Max memory assigned(earlier maximum memory) to the object.
Creating a database
1)A database is passively repository where it stores all database objects
2) Until you create an object, it is empty
3)Database doesn’t contains any passwords
Example
create database<database name>FROM
<Parent database>
As
Permanent=<Memory space>,
Spool=< Memory space >,
Temporary=< Memory space >,
[No]FALL BACK
Before/After journal,
Default journal table=<DATABASE.TABLE NAME>;//FOR
Permanent journal.
Dropping database/user
Syn: DROP database<database/USER>;
EG:DROP database MEERA;
Modifying database/user
Modifying database/user<database/USER;>
PERMANENT/Spool/TEMPORARY=<MEMORY SPACE>
Example
Create database db-DEVELOPMENT
From DBC
AS
PERMANENT=2000000,
Spool=2000000,
TEMPORARY=2000000,
SELECT*FROM DBC.databaseS
CREATE TABLE DB- DEVELOPMENT. TEST[PARTY ID INTEGER, PARTY NAME VARCHAR(30)]
INSERT INTO DB- DEVELOPMENT.TEST(1,’Meera’)
Select *FROM DB- DEVELOPMENT.TEST
Creating a user
A user in activate repository (data dictionary), where its store all database objects.
Until you create an object, user is an empty
User contains password where as database doesn’t contain any Password
Create USER- DEVELOPMENT
FROM DBC
AS
PERMANENT=2000000,
Spool =2000000,
TEMPORARY=2000000,
PASSWORD= VINAY;
Select * FROM DBC.USERS
Create table user- DEVELOPMENT. Test(Party ID Integer, party name varchar(30))
Insert INTO USR – DEVELOPMENT.TEST(‘VINAY’)
SEL* FROM USE- DEVELOPMENT. Test
DIFFERENCE BETWEEN database AND USER
| database | USER |
| 1.Passive repository[NO Change in request resource] | 1.Active repository[changes in number of request and responses] |
| 2.Doesn’t contain password | 2.Contain password |
| 3.To work with database we require user support | 3.By using this we can do any tool in tera data. |
Note:-
In real time for individual number(or) group administrator create
user id and password.
Creating a ODBC DRIVER with the above user
Open data sources
Check out the top Teradata Interview Questions now!
Connecting to query man by using this driver
Now we are in the above user space
Connecting other database(or)use They are 2 ways
By prefixing database name(or)USER name before the object
Syn:-<database/user name><object name>
Example:- SQL*FROM VINAYAKA. PARTY
database Table
By takes database name(or)User name as a current database name[Multiple query we can execute without prefixing database(or)user name]
Syn:-<database/ database /USER name>
Example:- Database VINAYAKA;
Select *From party;
Select *From party;
Select *From VIEW-party;
For Indepth knowledge on Teradata click on: