16.20 - Comparisons Between PM/API and Teradata SQL Requests - 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

There are both similarities and differences between PM/API applications and Teradata SQL applications, such as Basic Teradata Query (BTEQ), special utilities (for example, FastLoad), and third party software.

Similarities

The similarities between a PM/API based application and a Teradata SQL based application include:
  • Data types supported for input and output data in PM/API applications are a subset of those data types supported by Teradata SQL.
  • Fixed length character data is blank padded on the right.
  • NULL returned for the PclRecordType response parcel is equal to NULL returned by Teradata SQL (see Response Groups).

Differences

The differences between a PM/API based application and a Teradata SQL based application are:
  • A PM/API application issues requests through a session partition named MONITOR.
  • The MONITOR partition does not support the capabilities found in a Teradata SQL partition. The following table shows how the MONITOR partition reacts to the Teradata SQL partition capabilities.
If you use the Teradata SQL partition capability... The MONITOR partition returns ...
Keep Response processing mode an error message.
Execute a rewind operation
Run the start-up operation
Field mode request
CleanUp request
P (Prepare) or S (Setup) request processing option of the DBCAREA
  • Keywords used by the MONITOR PM/API requests are different from keywords in Teradata SQL. However, both have the same rules for identifiers.
  • MONITOR query processing, unlike Teradata SQL, produces dynamic data.
  • When using CLIv2, a Using Data String, which defines the input data, is always required in a PM/API application. In Teradata SQL, it is optional.

Keywords/Reserved Words

The following keywords are Teradata Database reserved words:
  • ABORTSESSION
  • MONITOR
  • MONRESOURCE
  • MONSESSION
  • SETRESRATE
  • SETSESSRATE
The following keywords are Teradata Database non-reserved words that are permitted as object names, but discouraged because of the possible confusion that may result:
  • MONSQL
  • MONVERSION

For a complete list of words that are unavailable for use as object names, see Teradata Vantage™ SQL Fundamentals, B035-1141.

Using Teradata SQL GRANT and REVOKE Statements

If you are using a Teradata SQL based application, you can issue the GRANT and REVOKE statements. For more information on these SQL statements, see Teradata Vantage™ SQL Data Control Language, B035-1149.

In rare cases, a change in privileges could temporarily block the request of an active MONITOR session because the system must look up the new privileges before accepting any more requests from the affected user. If the system query is blocked by a lock on a Teradata Database dictionary table or if there is some other type of system processing bottleneck, you cannot issue any MONITOR requests until you acquire the new set of privileges. However, MONITOR requests submitted from the system console are not blocked by a change in privileges, because no privileges are necessary to issue MONITOR requests on the system console.

Using the ABORT SESSION Request

If you are using a PM/API based application, you can issue the CLIv2 or the Teradata JDBC Driver ABORT SESSION request.

With the proper privileges granted, you can abort and log off a Teradata Database user.

If the ABORT SESSION command... The Teradata Database user receives...
targets a current Teradata Database request or session an error message that the transaction is aborted.
targets a request or session after the user has logged off from Teradata Database an error message that the session is not currently logged on.

The error message is not returned to the user of the affected session until ABORT SESSION completes rolling back the current transaction or, if applicable, the session is logged off.

The status of an active session determines when the error message is received.

If, at the time you issue an ABORT REQUEST statement and... The...
a request is outstanding for the user session error message is sent as a response to the current request.
no request is outstanding, but a session is in progress user receives the error message when the next request is received.

For more information on this request, see ABORT SESSION.