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.
- 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 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://developer.teradata.com/connectivity/reference/jdbc-driver .
- 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 Teradata 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.
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.
- 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.
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.
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.
|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://developer.teradata.com/connectivity/reference/jdbc-driver .
Open APIs (SQL Interfaces) Requirements for Using the API
- 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 .
Each API described in this document, except for the MONITOR VERSION request and the embedded services system functions, has its own 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.
- Teradata Vantage™ - Database Administration, B035-1093
- Teradata Vantage™ NewSQL Engine Security Administration, B035-1100
- Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver
|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||
|using the Teradata JDBC Driver to access the Teradata Database||
Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .