Know your Teradata system configuration

It may come to your mind that how many Nodes, Amps, PEs are there in your development and production Teradata system in which you are working on daily basis. Know your Teradata system configuration as below.

• To check the Teradata system configuration you can use the below 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 Teradata system:    

SELECT HASHAMP( 0 ) +1;

AMP:-

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

PE:-

Parsing engines perform session control, query parsing, checking objects existence, security validation, optimization, preparing plan, query dispatch.

GTW:- 

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

RSG:- 

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

TVS:-

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

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