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 the 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:
Other limitations also apply:
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 D8XISIZE(4),=A(D8XIHDSZ+D8XILMSZ+2) Ext. length
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
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 the 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. The 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 the 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.