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;
Let’s check about each of the components of the Teradata system.
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.
Parsing engines perform session control, query parsing, checking an object’s existence, security validation, optimization, preparing plan, query dispatch.
Gateway provides a socket interface between the user’s query and the parsing engine of the Teradata database.
Relay Services Gateway provides a socket interface for relaying dictionary changes to the Teradata MDS(Meta Data Service) utility.
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.
|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.