16.20 - PM/API Processing - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K
You can use the following Teradata proprietary APIs and libraries to write end-user application interfaces to PM/API routines.
  • CLIv2, an interface between the end-user application written in C and the Teradata Director Program. Teradata Director Program is the interface between CLIv2 and the Teradata Database. For an example of CLIv2, see Sample PM/API Application.
  • The Teradata JDBC Driver, an interface between the end-user application written in Java and the Teradata Database.

Whether the client application interface you develop is a simple utility or a complex system, the process is the same. Because the PM/API is accessed by either CLIv2 or the Teradata JDBC Driver, requests and responses are processed in much the same way as for a Teradata SQL application. No changes are required in CLIv2 or the Teradata JDBC Driver to support PM/API. As a result, PM/API capabilities on Teradata Database are available on any client platform that supports CLIv2 or the Teradata JDBC Driver.

Related Information

For details on the uses of ... See ...
CLIv2
  • 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
the Teradata JDBC Driver

Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver

Creating a Request with CLIv2

Your client application program tells CLIv2 what to do by creating a request string consisting of:
  • A request parcel (when the response is desired in record mode) or an IndicReq parcel (when the response is desired in indicator mode).

    When the response mode option is set in CLI or CLIv2, it automatically uses the correct request parcel format.

  • A USING Data String, which contains the input data.

    Unlike Teradata SQL, PM/API does not use a USING Phrase to name the variables and reserve space in the request parcel. Instead, each MONITOR request has a USING Data String of a particular fixed format that determines the order of items, their data types, and lengths.

Because a USING Data String is required, either a data parcel must follow a request parcel or an IndicData parcel must follow an IndicReq parcel.

An IndicData parcel is recommended, because several of the fields in the USING Data String can be NULL.

Generating an IndicReq parcel results in a response that contains a PclDataInfo parcel, which describes the number of response columns. Each Record parcel returned begins with a number of presence bits, that supply the NULL indicators for the result columns.

To pass PM/API requests to Teradata Database as the text portion (body field) of the request parcel, the application program calls the CLIv2 DBCHCL routine with the DBCAREA function code (4) set to the Initiate Request operation.

Code your application program to do the following before calling CLIv2 for the Initiate Request operation:
  • Set the request pointer to the address of a character string containing the request name.
    For the IDENTIFY request, set the request pointer to the address of a character string containing one of the following:
    • IDENTIFY SESSION
    • IDENTIFY DATABASE
    • IDENTIFY USER
    • IDENTIFY TABLE
  • Set the request length to the length in bytes of the character string.
  • Set the USING Data pointer to the address of the USING Data String.
  • Set the USING Data Length to the length in bytes of the USING Data String.

Setting Indicator or Record Mode

You can send the USING Data String in either indicator mode or record mode. CLI must inform CLIv2 which mode is used by setting the appropriate use presence bits option in the DBCAREA.

The use presence bits option is usually set to a default value for the site.
If you want... Do the following ...
to change the default value
  1. Set change options to Y in the application program.
  2. Set the use presence bits option in the DBCAREA to Y.
    This also allows the application program to send NULL data to the Teradata Database system.
the USING Data String sent in record mode
  1. Set change options to Y in the application program.
  2. Set the use presence bits option to N in the DBCAREA in the application program.

Executing a Request with the Teradata JDBC Driver

The client application uses a PreparedStatement, an object that represents a prepared SQL statement, for the PM/API requests. The client application:
  • Specifies the PM/API request text as the Connection PreparedStatement method’s SQL argument. The Connection interface represents a session with a specific database.
  • Is not permitted to specify multiple requests separated by semicolons.
  • Can only specify a single PM/API request.
  • Specifies the input data of the PM/API request with the PreparedStatement setter methods (for example, setString or setObject).
  • Must use the PreparedStatement execute() method to execute the PM/API request because each PM/API request may return multiple results. The execute() method handles these complex results.

For more information on the PreparedStatement and Connection methods, see Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .

Response Processing with CLIv2

PM/API requests are processed similarly to Teradata SQL requests because CLIv2 is used for both. Response processing is similar in both parcel ordering and buffer allocation. For an example CLI program, see Sample PM/API Application.

Just as input data can be sent to the Teradata Database in indicator or record mode, the response can be returned from the Teradata Database in either indicator or record mode, depending on how the response mode option is set in CLIv2. You can set response mode to be independent of input data mode. For example, it may be more convenient to send the input data in record mode, but the response may require indicator mode if NULL is expected.

Response mode is usually set to a default value for the site. If you want to change the default value set, change options to Y in the application program.

If you want to return data in… Set the response mode option in DBCAREA to…
indicator mode I for Indicator. CLIv2 automatically uses the correct request parcel format.
record mode R for Record. CLIv2 automatically uses the correct request parcel format.
Field mode, represented by F, is not supported in a PM/API request.

Retrieving Results with the Teradata JDBC Driver

Each PM/API request may return multiple results. The client application must use the PreparedStatement execute() method to execute the PM/API request.

The client application calls the PreparedStatement getResultSet() method to retrieve the ResultSet from the PM/API request. A ResultSet provides access to a table of data generated by executing a PM/API request. The table rows are retrieved in sequence. Within a row its column values can be accessed in any order.

If the PM/API request returned multiple results, the client application calls the PreparedStatement getMoreResults() method to advance to the next result. Then, the client application calls the PreparedStatement getResultSet() method to retrieve the result set.

For more information on the PreparedStatement methods, see Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .

Parcel Ordering

The response returned from a MONITOR request is a series of parcels constructed by Teradata Database and sent to the client.

The basic parcel layout for a successful response is the same for most MONITOR requests, except the following:
  • MONITOR SQL
  • MONITOR VERSION
Some MONITOR requests such as:
  • MONITOR SESSION
  • MONITOR VIRTUAL CONFIG
  • MONITOR PHYSICAL CONFIG
  • MONITOR VIRTUAL RESOURCE
  • MONITOR PHYSICAL RESOURCE

behave like multistatement requests, for which multiple record parcels are returned. For request-specific parcel layout, see System PMPC APIs.

The following table shows the parcel layout for an unsuccessful response is the same for all MONITOR requests -- either a Failure or an Error parcel is returned.

Parcel Name Parcel Flavor Field Length Comments/Key Parcel Body Fields
Failure 9 15 to 267 Message code and message text: description of cause of failure; request could not be processed. For example, the request is aborted.
Error 49 15 to 267 Message code and message text: description of cause of error. Application can fix the problem and resubmit the request. For example, response buffer is too small to hold entire response row.

Buffer Allocation with CLIv2

A PM/API application, like a Teradata SQL application, requires space for a:
  • Response buffer, containing the parcels transmitted back from the Teradata Database.
  • Request buffer, containing the parcels sent to the Teradata Database.

Space for these two buffers is allocated for the application by CLIv2 based on the setting of DBCAREA arguments at the time a session is established. Default buffer sizes are controlled by the HSHSPB (control block containing site specific information), which is created during installation when CLI defaults are specified. The minimum size of a response buffer is 32,000 bytes.

If your response buffer is not large enough, your application program may receive an error message. For details on error messages, see Teradata Vantage™ - Database Messages, B035-1096.

Logging On

A PM/API application logs on similar to a Teradata SQL application. However, there are some differences.

The partition name is MONITOR:
  • In a Java application, specify the Teradata JDBC Driver PARTITION=MONITOR connection parameter. For details, see Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .
  • In a CLIv2 application, set the following:
    • Run pointer to the address of the character string containing the word MONITOR.
    • Run length to the length in bytes of the character string containing the word MONITOR.
Your logon fails if:
  • You do not have the appropriate MONITOR privilege.
  • The PE or client that the MONITOR session is logged on to is already supporting its maximum number of four MONITOR sessions.
    Currently on the PE, the load balancing mechanism does not support MONITOR partition sessions. Session Control allocates MONITOR session requests to the PE on which a MONITOR session is logged until the four sessions per PE limit is reached.
  • The system-wide limitation of 128 concurrent MONITOR sessions is reached.

Logging Off

A PM/API application logs off the same way a Teradata SQL application does with one exception. If you log off a session that has a local session monitoring rate (SesMonitorLoc) of nonzero, the rate is changed to zero. This change is added to the DBC.SW_Event_Log table (accessible from the DBC.Software_Event_LogV view), which is similar to what occurs if you had issued a SET SESSION RATE request prior to logging off.

A Java application calls the Connection close() method of the Teradata JDBC Driver to log off. The Connection close() method immediately releases a database and the JDBC resources instead of waiting for them to be automatically released. For more information on the Connection close() method, see Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver .

Warning and Error Messages

For a general discussion on common warning and error messages that may be returned in response to various requests, see Common Warning and Error Messages.

For detailed information on the meaning of individual error and warning messages and the response required, see Teradata Vantage™ - Database Messages, B035-1096.