Understanding Teradata Parsing Engine and Access Rights: Impact on System Performance

Understanding the relationship between Teradata’s parsing engine (PE) and system performance requires knowledge of the PE’s tasks within the Teradata architecture.

The Teradata parsing engine manages access rights, parsing the statement, optimizing the SQL, creating processing steps, and dispatching to the AMPs.

Knowledge of the specific dependencies between roles and access rights in a Teradata system is unnecessary to address our issue. Instead, it is important to note that individual object permissions are saved in the DBC.AccessRights table.

When creating a table, various permissions will be assigned to your user account (the one used for logging into the system).

CREATE TABLE <DATABASE>.< TABLE>(
PK INTEGER NOT NULL
) PRIMARY INDEX (PK);

To verify the statement above, we will utilize the view DBC.AllRights rather than DBC.AccessRights for ease of use. This view utilizes object names instead of the object identifiers internally utilized by Teradata, simplifying the process for us.

SELECT
UserName,DatabaseName,TableName,AccessRight,GrantorName,CreatorName
FROM
dbc.allrights
WHERE
DatabaseName = ” AND tablename = ”;

The result set above displays that the DBC.AccessRights table stores 12 permission records, including permissions for updating, retrieving, deleting, restoring, and dropping triggers, among others, following the creation of a table.

The number of permissions stored varies based on the Teradata Release and potentially other parameters.

Grants are typically retrievable from the cache, but when the cache cannot satisfy the necessary permits, the parsing engine must reference the DBC.AccessRights table (refer to the DDL provided):

CREATE SET TABLE DBC.AccessRights
(
UserId BYTE(4) NOT NULL,
DatabaseId BYTE(4) NOT NULL,
…
) PRIMARY INDEX ( UserId ,DatabaseId );

Hashing is utilized in DBC tables to distribute table records amongst all AMPs. However, skewing may pose an issue, as is the case with DBC.AccessRights where the primary index is {UserId, DatabaseId}.

Your daily batch process may result in a vast number of tables being generated in the staging area by a single user, causing significant skewing in the DBC.AccessRights table.

Verifying user permissions within the parsing engine will result in longer parsing times and, subsequently, a decrease in overall system performance.

How can this problem be reduced in scope?

By definition, the user who creates an object already possesses all access rights, rendering the records written to DBC.AccessRights superfluous.

We can delete these records using the following SQL method:

SELECT
‘REVOKE ALL ON ‘|| TRIM(a.databasename)|| ‘.’ || trim(a.tablename) || ‘ from ‘|| trim(a.username) || ‘;’ (TITLE ”)
FROM dbc.allrights a
INNER JOIN
DBC.TABLES b
ON
a.DatabaseName = b.DatabaseName
and a.tablename = b.tablename
where   a.grantorname = ‘<USER>’
and   a.DatabaseName
in     (
select d.databasenameI
from   dbc.dbase d
where   d.rowtype = ‘D’)
and   a.username   = a.grantorname
and a.accessright not in (‘IX’,’RF’)
and b.tablekind = ‘T’
GROUP BY 1;

If UserName matches GrantorName, then delete the record from DBC.AccessRights.

Cleaning the DBC.AccessRights table can yield faster parsing times, as confirmed by checking the DBC.DBQLOGTBL.

Note that performing a DROP & CREATE action on the table results in the permissions being added to DBC.AccessRights again. Removing the contents from your target tables is advisable instead of using this method.