16.20 - Send Procedure to Database - Teradata Tools and Utilities

Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems

Teradata Tools and Utilities
September 2019
Programming Reference

A stored procedure is sent to Teradata Database in one or more segments using either the Initiate Request, or the Initiate With Protocol-function CLIv2 function.

The Maximum-Segment-Size CLIv2 query obtains the maximum size of each segment. Use of segments is indicated by the CLIv2 Segment-data option (with the Change-options option set to 'Y').

The application divides the text of the stored procedure into pieces no larger than the Maximum-Segment-Size, and sends each piece as a separate request (the text may be divided at any point). The first piece is identified using the Segment-data option as the first segment. The last (or only) piece is identified as the last segment; all other pieces are identified as intermediate segments.

When sending segments of a stored procedure, the maximum value for Request-length may vary slightly with the version of Teradata Database being used; the value may be obtained using the DBCHQE CLIv2-limits query (or deprecated Maximum-segment-size query).

Effects on Other Options

Use of Segment-data impacts the use of these other options:
  • the Keep-response option must be 'N'
  • the Request-mode option must be 'P' (parcel mode)
  • the Request-processing-option must be 'E' (execute request processing)
  • the Two-phase commit option must be 'N' (no two-phase commit)
  • if the Initiate with Protocol-function function is used, the Initiate Protocol-function option must be 'N' (because two-phase commit is not supported).
Other limitations also apply:
  • the character set for all segments is the character set of the first segment (changing the character set between segments may result unexpected translation of Segment-data)
  • only one sequence of segments may be in use within one session at a time.

To provide compilation options, an Initiate-Request extension to the DBCAREA must be used to provide a Stored Procedure Options request parcel (flavor 129). This parcel must be provided with the first segment.

For example, the following Assembler instructions (which assume use of the DBCAIRX macro) initialize an extension providing a Stored Procedure Options request parcel that would be addressed by the DBCAXP field in the DBCAREA when the first segment is sent:

MVC D8XIID(4),=A(D8XIIIRX) Set Eyecatcher
XC  D8XINEXT(4),D8XINEXT   No more extensions
MVC D8XILTYP(2),=H'129'    Parcel flavor
MVC D8XILLEN(2),=Y(D8XILMSZ+2) Parcel length
MVI D8XILDAT,C'P'         "PRINT" compile option
MVI D8XILDAT+1,C'Y'       "SPL" compile option

Advanced Applications

While there is no meaningful reason to do so, advanced applications can either ascertain the maximum size of a procedure, or build the request buffers to send a procedure to Teradata Database.

The only meaningful reason to ascertain the maximum procedure size would be to calculate the size of the procedure and not send it if it exceeds the maximum size. But since a huge procedure is unlikely, what this amounts to is optimizing an error path, and an unlikely error path. Teradata Database's maximum size of a procedure is the product of the maximum segment size and the maximum number of segments. The DBCHQE Build-info query may be used to obtain this maximum number of segments. As described earlier, the CLIv2-Limits query may be used to obtain the maximum segment size.

Though there is no known reason to do so, an advanced application may build request buffers to send the procedure, just as buffers may be built for any request. A Multi-TSR request parcel (flavor 128) is used to send the procedure to Teradata Database. The parcel also contains the segment number and last-segment indicator. The sequence number is an unsigned integer beginning with one and incremented by one for each subsequent segment. For the first segment, a Stored-procedure options parcel (flavor 129), discussed earlier, may also be specified.