Chapter 5 - How Teradata Tracks Objects

“A bird does not sing because it has the answers, it sings because it has a song.”

-Anonymous

Teradata Assigns each Object a Unique Numeric ID

Your SQL uses database names, table names and column names, but each of these is actually given a numeric ID and it is that Numeric ID that the Parsing Engine (PE) and the Access Module Processors (AMPs) use to track the objects.

Each Database, User, Profile and Role is assigned a globally unique numeric ID.

Each Table, View, Macro, Trigger, Stored Procedure, User-defined Function, Join Index, and Hash Index is assigned their own globally unique numeric ID.

Each Column is assigned a numeric ID that is unique within its Table ID.

Each Index is assigned a numeric ID unique within its Table ID.

The Data Dictionary keeps track of all the SQL names and their associated numeric IDs.

The PE uses the Data Dictionary to verify names and then the PE convert them to IDs.

The AMPs use the numeric IDs supplied by the Parsing Engine in its Data Dictionary RESOLVER logic. The PE Resolver resolves that the object names exist in the Data Dictionary.

People understand languages such as English, but Teradata really only understands numbers. So, Teradata converts each object into a number so it can easily track each object.

The Table ID

The Table ID is a 48-bit Unique Value for Tables, Views, Macros, Triggers, Stored Procedures, Join Indexes, etc. that comes from DBC.Next dictionary table.

DBC.Next has billions of numeric IDs and it assigns a 32-bit Unique ID to every object (such as a Table, View, etc.). A 16-bit will be added to this Unique ID to add more information.

There is a range of DBC.Next IDs for Spool files and a range of IDs for Global Temporary tables, a range for Permanent Journal tables, and a range of IDs for Normal data tables. This helps Teradata to define what type of table it is.

The Range of the Unique ID (32-bits from DBC.Next) tells Teradata if the object is a normal data table, permanent journal, global temporary table, or a spool file. For example, Spool Files have a range of Unique ID numbers that start with C000 0001 through FFFF FFFF.

Then, the last 16-bits tell Teradata what part of the table they are looking for. It could be the Table Header, the data table or the fallback data table, the secondary index, or it could be an actual Blob or Clob.

In total, the 48-bit TableID tells Teradata what type of table it is and what part of the table they are looking to retrieve.

The Table ID in Greater Detail

DBC.Next has billions of numeric IDs and it assigns a 32-bit Unique ID to every object (such as a Table, View, etc.). A 16-bit Sub-table ID will be added to this Unique ID to determine what part of the table they want to retrieve. So the entire TableID is 48-bits.

Unique Value 32-bits define the type of table. Below you can see the different types:

• Normal data table
• Permanent journal
• Global Temporary
• Spool file

Sub-table ID identifies the part of a table the system is looking at.

• (Table Header 0)
• (Data table 1024 and Fallback data table 2048)
• (1st Secondary index 1028 and the fallback 1st secondary index 2052)
• (2nd Secondary index 1032 and the fallback 2056)
• (1st Reference index 1536 and fallback 2560)
• (1st BLOB or CLOB 1792 and Fallback 2816)
• (2nd BLOB or CLOB 1794 and fallback 2818)
• Archive Online Subtable 18440 and there is no Fallback version.

Looking at the TableID inside the actual Cylinders

Both blocks in Cylinder 1 above are from the same table. The first 32-bits have the same value in 1234, but the second 16-bits identify Table Header vs. Data Block.

A More Detailed View of TableID inside the actual Cylinders

Everything above represents the Employee_Table. It has a Unique TableID value of 1234 (for each), but a different subtable ID portion to identify the Table Header, Data Block, Fallback Data Block, the Secondary Index subtable, and the Fallback Secondary Index subtable. The 48-bit TableID has a unique table portion and a subtable portion.

The Blocks Below are All Associated with the Same Table

The 32-bit Unique Value and the 16-bit Sub-Table ID combine to make up the 48-bit Table ID.
Each Block's TableID below begins with 1234, thus each block is associated with the same table.

Bits, Bytes and More

When describing disk storage capacity, a megabyte is 1,000,000 bytes in decimal notation. But when the term megabyte is used for real and virtual storage, it is 1,048,576 bytes is the appropriate notation. Some believe it is always 1,048,576 bytes on the argument that bytes should naturally be computed in powers of two. So, which definition do most people conform to? When referring to a megabyte for disk storage, the hard drive manufacturers use the standard that a megabyte is 1,000,000 bytes. The operating systems Windows, though, uses the 1,048,576 byte rule.

Cylinder Sizes

Teradata V13.10 introduced Large Cylinder Support

Prior to Teradata 13.10 and Large Cylinder Support, cylinders are 3872 sectors, which is just under 2 MBs. With Large Cylinder Support (Teradata 13.10 and beyond), cylinders can be 12 MBs in size. A sector is 512 bytes in size, which is the smallest amount of disk ever allocated.