16.20 - Example: Identifying an AMP with a Specific Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

Use the SYSLIB.SparseMapAMPs function to find which AMP contains a specific table in a sparse map.

WITH dt1 (DatabaseName, ObjectName, ObjectKind, MapSlot, NumberOfPrimaryAMPs, ColocationName) AS
(SELECT DatabaseName, TableName, TableKind, MapSlot, NumberOfAMPs,
        ColocationName
 FROM DBC.TablesV t, DBC.Maps m
 WHERE ColocationName IS NOT NULL
   AND t.MapName = m.MapName)
SELECT AmpNo, PF, CAST(DbName AS CHAR(5)), CAST(ObjName AS CHAR(5))
FROM TABLE (SparseMapAMPs(dt1.DatabaseName, dt1.ObjectName, dt1.ObjectKind, dt1.MapSlot, dt1.NumberOfPrimaryAMPs, dt1.ColocationName)) dt
ORDER BY 1;

 AmpNo  PF  DbName  ObjName
------  --  ------  -------
     0  P   db1     tabs2
     7  P   db1     tabs1
    24  F   db1     tabs2
    31  F   db1     tabs1
    72  F   db1     tabs2
    96  P   db1     tabs2

WITH dt1 (DatabaseName, ObjectName, ObjectKind, MapSlot, NumberOfPrimaryAMPs, ColocationName) AS
(SELECT DatabaseName, TableName, TableKind, MapSlot, NumberOfAMPs,
        ColocationName
 FROM DBC.TablesV t, DBC.Maps m
 WHERE ColocationName IS NOT NULL
   AND t.MapName = m.MapName)
SELECT CAST(DbName AS CHAR(12)), CAST(ObjName AS CHAR(12)), AmpNo, PF
FROM TABLE (SparseMapAMPs(dt1.DatabaseName, dt1.ObjectName,      dt1.ObjectKind, dt1.MapSlot, dt1.NumberOfPrimaryAMPs,             dt1.ColocationName)) dt
ORDER BY 1,2;

DbName        ObjName        AmpNo  PF
------------  ------------  ------  --
db1           tabs1              7  P
db1           tabs1             31  F
db1           tabs2              0  P
db1           tabs2             24  F
db1           tabs2             72  F
db1           tabs2             96  P