Datatype short forms in Teradata

When we help table in Teradata, datatype is represented in short forms. below is list of datatype and it respective short forms.

• AT = TIME

• BF = BYTE

• BO = BLOB

• BV = VARBYTE

• CF = CHAR

• CO = CLOB

• CV = VARCHAR

• D = DECIMAL

• DA = DATE

• DH = INTERVAL DAY TO HOUR

• DM = INTERVAL DAY TO MINUTE

• DS = INTERVAL DAY TO SECOND

• DY = INTERVAL DAY

• F = FLOAT

• HM = INTERVAL HOUR TO MINUTE

• HR = INTERVAL HOUR

• HS = INTERVAL HOUR TO SECOND

• I1 = BYTEINT

• I2 = SMALLINT

• I8 = BIGINT

• I = INTEGER

• MI = INTERVAL MINUTE

• MO = INTERVAL MONTH

• MS = INTERVAL

• MINUTE TO SECOND

• PD = PERIOD(DATE)

• PM = PERIOD

(TIMESTAMP(n) WITH TIME

ZONE)

• PS = PERIOD

(TIMESTAMP(n))

• PT = PERIOD(TIME(n))

• PZ = PERIOD(TIME(n) WITH

TIME ZONE)

• SC = INTERVAL SECOND

• SZ = TIMESTAMP WITH

TIME ZONE

• TS = TIMESTAMP

• TZ = TIME WITH TIME

ZONE

• YM = INTERVAL YEAR TO

MONTH

• YR = INTERVAL YEAR

• UT = UDT Type

More on Volatile Tables

Volatile tables are useful for the following:

• Creating tables that the system accesses and modifies frequently

• For when you know you do not need the data for later use

• Automatically dropping the table at session end

Using volatile tables enables the Optimizer to do an index scan instead of a table scan.

The definition of a volatile table is stored in memory cache only for the duration of the

current session. Space usage is charged to the spool space of the login user. If your SPOOL

allocation is large enough, you can create up to 1000 volatile tables per session.

Note: Volatile tables do not survive a Teradata Database reset. Restart processing destroys

both the contents and the definition of a volatile table.

The following are not available for volatile tables:

• Referential integrity constraints

• Check constraints

• Permanent journaling

• Compressed column values

• DEFAULT clause

• TITLE clause

• Named indexes

• Privilege checking (because volatile tables are private to the session in which they are

created)

• Identity Column

More on Global Temporary Tables

Global temporary tables store a query result in a temporary table for use in subsequent

queries. This improves system performance as follows:

• The system needs to re-execute the query fewer times.

• The user needs to create and drop fewer tables.

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a global table. Global

temporary tables are stored in the Data Dictionary, but their contents do not persist across

sessions.

Also, their contents are not saved when a transaction completes, unless you explicitly specify

otherwise. To save table rows after a transaction ends, specify ON COMMIT PRESERVE

ROWS as the last keywords in the CREATE statement. However, if you are using a proxy

connection and the connection is lost or switched to another proxy user, all temporary tables

are also lost.

The following options are not available for global temporary tables:

• Referential constraints

• Permanent journaling

• Identity column

Space usage is charged to the temporary space of the login user.

Note: Global temporary tables require both permanent and temporary space. A minimum of

512 bytes of permanent space is required for the table header stored on each AMP for the base

global temporary table definition. Temporary space is required for storing the actual rows.

You can materialize up to 2000 instances of global temporary tables in a single session, as long

as your login user has adequate temporary space.

To obtain a list of all global temporary tables in the system, query the CommitOpt column of

the DBC.TablesV view. For example:

SELECT * FROM DBC.TablesV WHERE CommitOpt IN (‘D’, ‘P’);

The CommitOpt column shows the value D or P for a global temporary table. (P is for ON

COMMIT PRESERVE ROWS and D is ON COMMIT DELETE ROWS. N indicates the object

is not a temporary table.)

To obtain a list of all global temporary tables you own, query the restricted (DBC.TablesVX)

view.

You can also query the TransLog column to determine if there is transaction logging for a

global temporary table.

You can materialize a global temporary table locally by referencing it in an SQL data

manipulation language (DML) statement. To do this, you must have the appropriate privilege

on the base temporary table or on the containing database, as required by the statement that

materializes the table. Space usage is charged to the temporary space of the login user.

Any number of different sessions can materialize the same table definition, but the contents

change depending on the DML statements applied during the course of a session.

Privileges are not checked on the materialized instances of any global temporary tables

because those tables exist only for the duration of the session in which they are materialized.

How to find number of amp in the system?

Number of AMP depends on number of node in the system and  can vary from 8 to 40 depending on type of node used in system, latest node type 6690 can have 40 AMP on it.

Total AMP in the system can be found out by query

select hashamp() +1;

or 

select Vproc from dbc.Acctg where VprocType='AMP' group by Vproc;

It is recomended to have nodetype same accross the system to avoid any skew while processing request, but in case you have more than one node type in the system we can use below query to get nodewise number of AMPs in the system.

Select nodeid,count(distinct Vproc) from dbc.ResCpuUsageByAmpView group by 1

We can query to find what are the different node types are being used in the system

select distinct nodetype from  dbc.resusagespma;

Number of node in the system can be found out by

Select count(distinct nodeid) from dbc.resusagescpu;