15.10 - Examples of Job Control Support Applications Using PM/APIs - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

This section explains two advanced examples of potential job control support applications that use PM/API requests:

  • Resource Supervisor
  • Idle Session Logoff
  • They are explained at a high level so that, by understanding the concepts, you can develop similar applications at a customer site for monitoring and controlling the use of Teradata Database resources.

    Resource Supervisor

    A Resource Supervisor prevents runaway queries. Runaway queries are sometimes a problem at a site where end users can access the Teradata Database to make ad hoc Teradata SQL requests. A badly formulated query (for example, one missing constraint on a WHERE clause) could inadvertently cause a product join, which consumes more resources than the user intended. Further, a user making an ill‑formed SQL statement might request a join on two big tables, which unintentionally results in a Cartesian product join. The Resource Supervisor aborts transactions that exceed a certain resource usage threshold.

    You can write a Resource Supervisor for Teradata Database to use features available in the request as shown in the example below.

    Program the SET SESSION RATE request to set a reasonable session‑level collection rate, for example, 10 minutes. Based on the session-level rate, program the client application to issue a MONITOR SESSION request for all sessions or for a subset of sessions (for example, if users from a specific client are the only ones to be governed). For each session returned to the client, program the client application to check some site‑specific criteria to see if the session is a candidate for the Resource Supervisor.

    For example, interactive users are required to have INTERACTIVE as the first word of their account string. If only interactive users are to be monitored by the Resource Supervisor, all sessions that do not include INTERACTIVE as the first word of the UserAccount value returned by a MONITOR SESSION request are ignored.

    For those sessions that are candidates for the Resource Supervisor, program the client application to look at the AMPCPUSec, PECPUSec, and AMPIO values to determine if some site‑specific maximum acceptable value has been exceeded.

    These session values are cumulative and may not be appropriate for use as a Governor limit because you are limiting the total resource usage of a session and not of a request.

    Program the client application to keep a history of all previous cumulative values of AMPCPUSec, PECPUSec, and AMPIO, plus current XactCount (transaction count) and ReqCount (request count) values for each session.

    The difference between the historical value and the current value tells you the resources used. The request count and transaction count values tell you if they are consumed as part of the current transaction or as part of the new request.

    If the Resource Supervisor determines that a particular session has exceeded site‑specific limits, program the client application to issue an ABORT SESSION request for those session that have exceeded the limits.

    The client application can specify the logoff option for the ABORT SESSION request, depending on how severely the offending session is controlled.

    Idle Session Logoff

    An Idle Session Logoff application automatically logs off users whose sessions have been idle for a certain length of time. This job control support feature prevents users from walking away from a terminal and allowing unauthorized users access to sensitive information.

    You can write an Idle Session Logoff application program using the requests described below.

    Program the SET SESSION RATE request to set a reasonable session‑level collection rate, for example, 10 minutes. Based on the session-level rate, program the client application to issue a MONITOR SESSION request for all sessions or for a subset of sessions (for example, if users from a specific client are the only ones to be monitored for idle sessions). For each session returned to the client, check some site‑specific criteria to see if the session is a candidate for Idle Session Logoff.

    For example, interactive users are required to have INTERACTIVE as the first word of their account string. If only interactive users are to be monitored by the Resource Supervisor, all sessions that did not have INTERACTIVE as the first word of the UserAccount value returned by a MONITOR SESSION request are ignored. Those sessions with the INTERACTIVE label proceed through the next step.

    For sessions that are candidates for Idle Session Logoff, program the client application to verify the following conditions to determine whether the session has been inactive for the duration of the collection period:
  • AMPState and PEState are idle.
  • The session was idle during the last MONITOR SESSION request.
  • XactCount and ReqCount values did not change during the last MONITOR SESSION request.
  • If all these conditions are met, the session has been inactive for the duration of the collection interval and is a candidate for automatic logoff.

    Program the client application to issue an ABORT SESSION request with the logoff option for the sessions that are candidates for automatic logoff.