Workload Management API | Application Programming Reference | Teradata Vantage - Workload Management API - Advanced SQL Engine

Workload management API consists of interfaces to PM/APIs and open APIs. You can use these interfaces to:

Monitor system and session-level activities.
Monitor Teradata Active System Management (ASM) activity.
Track system usage and manage task priorities.

API Categories

PM/APIs and open APIs, also called SQL interfaces, are divided into the following categories:

System PMPC
Teradata Dynamic Workload Management
Query Band

Embedded services system is another category that applies only to some of the SQL interfaces (for example, the GetPSFVersion and TD_get_COD_limits functions).

The following diagram shows how:

The System PMPC, Teradata Dynamic Workload Management, and Query Band PM/API requests (such as Call-Level Interface Version 2 [CLIv2] and the Teradata JDBC Driver) interface to the PMPC subsystem through the MONITOR partition.
These PM/APIs are supported by both Call-Level Interface Version 2 (CLIv2) and the Teradata JDBC Driver. For more information on how the Teradata JDBC Driver requests are executed, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html .
The System PMPC and Teradata Dynamic Workload Management SQL interfaces (such as, user-defined functions and external stored procedures) connect to the PMPC subsystem through the SQL partition, except TDWMApply, TDWMRuleControl, and TDWMSetLimits. These APIs connect to the database system through the SQL partition and are not shown in the diagram below.
The embedded services system and most Query Band SQL interfaces connect to the SQL partition, except MonitorQueryband. This SQL interface is the only Query Band API that connects to the PMPC subsystem through the SQL partition and is not shown in the diagram below.

For more information on these APIs, see:

PM/APIs

PM/APIs provide access to PMPC routines resident in Teradata Database. The PMPC subsystem is available through a logon partition called MONITOR, using a specialized PM/ API subset of CLIv2 or Teradata JDBC Driver.

PM/APIs have the following features:

CLIv2 or Teradata JDBC Driver data is acquired in near real time, with less overhead and minimal possibility of being blocked. These capabilities allow frequent in-process performance analysis.
CLIv2 request saves the raw data in an in-memory buffer where a client application program can easily retrieve the data for real-time analysis or importing into custom reports. The Teradata JDBC Driver returns the data as a JDBC ResultSet where a client application program can easily retrieve the data.
CLIv2 or Teradata JDBC Driver request provides access to data that the resource usage does not. For example, session-level resource usage data, and data on application locks and which application is being blocked.

Using PM/APIs may not be the right choice for all performance monitoring requirements. Standard performance monitoring tools and reports, such as resource usage reports, may be sufficient.

Open APIs

The workload management open API provides an SQL interface to the PMPC subsystem and Teradata Database system through user-defined functions, embedded services functions, and external stored procedures. Most of the SQL interfaces available to the PMPC subsystem provide similar functionality to the CLIv2 or Teradata JDBC Driver requests.

Most open APIs do not follow transaction rules. If a transaction calls a UDF or external stored procedure and the transaction rolls back, the action of the UDF or external stored procedure is not rolled back. However, the external stored procedures that update the TDWM database must follow the transaction rules. If a transaction calls one of these external stored procedures and the transaction is aborted, the update will be rolled back.

Differences Between Open APIs and PM/APIs

The following table describes the differences between open APIs (that is, SQL interfaces consisting of UDFs or external stored procedures) and PM/APIs.

Open APIs...	PM/APIs...
are issued in the current SQL partition	require logging on to the MONITOR partition. To view a diagram of the process, see API Categories.
require EXECUTE privilege on the function or external stored procedure	require MONITOR privileges.
use SQL parsing, dispatching steps, and UDF processing	require use of a custom application in C, Java, or some other programming language.
run in priority of account string or by the Teradata dynamic workload management software classification	run in the system priority.
can be placed on a Teradata dynamic workload management software delay queue and can block system resources	do not block.
use AMP Worker Tasks (AWTs)	are not subject to running out of resources (AWTs).

PM/APIs Requirements for Using the API

If you are using CLIv2, you must install CLIv2 on a client machine where the PM/API application is running.

If you are using the Teradata JDBC Driver, you must import the SQL Interface package and load the Teradata JDBC Driver. For complete instructions, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html .

Open APIs (SQL Interfaces) Requirements for Using the API

The SQL interfaces to workload management already exist if the following scripts have been run as part of Teradata installation:

For System PMPC and Teradata dynamic workload management SQL interfaces, you must run Database Initialization Program (DIP) utility and execute the DIPDEM and DIPTDWM scripts.
For the embedded services system functions, you must run the DIP utility and execute the DIPALL or DIPSYSFNC script. These scripts create the TD_SYSFNLIB database which should only be used by the system to support the embedded services functions. For more information about activating the embedded services system functions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

For information about these DIP scripts, see Teradata Vantage™ - Database Utilities , B035-1102 .

Required Privileges

Each API described in this document, except for the MONITOR VERSION request and the embedded services system functions, has its own required privileges.

Below are some examples of the required privileges.

To connect with the Teradata JDBC Driver, it is necessary that you are granted privileges for executing PM/API requests. For example, you must issue GRANT MONITOR TO guest before connecting as a guest user and executing any PM/API request.
To issue the ABORT SESSION and MONITOR SESSION requests, you must have the ABORTSESSION and MONSESSION privileges respectively as part of your default role or these privileges must be granted directly to you.
To issue the MONITOR AWT RESOURCE, MONITOR VIRTUAL RESOURCE, and MONITOR PHYSICAL RESOURCE requests, you must have the MONRESOURCE privilege as part of your default role or this privilege must be granted directly to you.
To access the UDFs and external stored procedures, the DBA must grant EXECUTE FUNCTION and EXECUTE PROCEDURE privileges to you. These privileges are not granted by default.

For more information on roles and privileges, see:

Teradata Vantage™ - Database Administration, B035-1093
Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100
Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html

For more information on ...	See ...
the different System PMPC PM/APIs and open APIs	System PMPC APIs.
the different Teradata Dynamic Workload Management PM/APIs and open APIs	Teradata Dynamic Workload Management APIs: PM/APIs.
the different Query band PM/APIs and open APIs	Workload Management: Query Band APIs.
the different embedded services system functions	Workload Management: Embedded Services System APIs. For more information about embedded services system functions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
how to code an application that uses the CLIv2 requests in this document	Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417. Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418.
using the Teradata JDBC Driver to access the database	Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html .

Workload Management API | Application Programming Reference | Teradata Vantage - Workload Management API - Advanced SQL Engine - Teradata Database

Application Programming Reference