Teradata System Configuration

In this tutorial, you will learn how to check any Teradata system configuration with the help of the DBC table.

Teradata systems are made with different components like Nodes, Amps, PEs, etc. The configuration of each Teradata system may be different as per the customer requirements.

Suppose you are working as a Teradata developer, Teradata Architect, or a Teradata Administrator and there may be different systems for development, testing, and production. You can check the configuration of Teradata systems easily.

To check the Teradata system configuration you can use the following query:

SELECT DISTINCT
     NodeID
     ,NodeType
     ,VProcType1||’: ‘||TRIM(VProc1) AS AMPs
     ,VProcType2||’: ‘||TRIM(VProc2) AS PEs
     ,VProcType3||’: ‘||VProc3 AS GTW
     ,VProcType4||’: ‘||VProc4 AS RSG
     ,VProcType5||’: ‘||VProc5 AS TVS
FROM DBC.ResUsageSpma
ORDER BY NodeID

To find out the count of AMPs in the Teradata system:    

SELECT HASHAMP( 0 ) +1;

Output

amps in teradata
No. of AMPs in Teradata

Let’s check about each of the components of the Teradata system.

1) AMP

Access module processors are responsible for all database-related functions like executing database queries, locking tables/rows/databases, sorting, joining data rows, aggregation, disk space management.

2) PE

Parsing engines perform session control, query parsing, checking an object’s existence, security validation, optimization, preparing plan, query dispatch.

3) GTW

Gateway provides a socket interface between the user’s query and the parsing engine of the Teradata database.

4) RSG 

Relay Services Gateway provides a socket interface for relaying dictionary changes to the Teradata MDS(Meta Data Service) utility.

5) TVS

Teradata Virtual Storage manages disk storage. It provides a portion of disk storage to each AMP to work independently.

Below is the configuration of the Teradata system which I have installed in my laptop.

NodeID NodeType AMPs PEs GTW RSG TVS
101 XXXX AMP : 24 PE : 2 GTW : 1 RSG : 0 TVS : 1
102 XXXX AMP : 24 PE : 2 GTW : 1 RSG : 0 TVS : 1
103 XXXX AMP : 24 PE : 2 GTW : 1 RSG : 0 TVS : 1
104 XXXX AMP : 24 PE : 2 GTW : 1 RSG : 0 TVS : 1

From the above example, you can see that this is an MPP (Massively Parallel Processing) system with 4 nodes each having 24 AMPs, 2 PEs, 1 GTW, no RSG, and 1 TVS. 

Please get connected & share!

Advertisement