A client application can call a stored procedure on an Adaptive Server or an Open ServerConnect transaction running in a separate CICS or IMS region.
A client application can call a stored procedure or mainframe transaction one of two ways:
By executing an Adaptive language request (for example, “execute myproc”)
By making an RPC
RPCs have a few advantages over execute statements:
An RPC can be used to execute an Adaptive Server stored procedure or any Open ServerConnect transaction.
An Adaptive language request can only be used to execute an Adaptive Server stored procedure or a specially written Open ServerConnect language transaction.
When sending a request to an Adaptive Server, it is simpler and faster to accommodate stored procedure return parameters if the procedure is invoked with an RPC instead of a language request.
A server can execute a stored procedure or transaction residing on another server. This might occur when a stored procedure being executed on one Adaptive Server contains an execute statement for a stored procedure on another Adaptive Server. The execute command causes the first server to log in to the second server and execute the remote procedure. This is called a server-to-server RPC. It happens without any intervention from the application, although the application can specify the remote password that the first server uses to log in to the second.
A server-to-server RPC also occurs when an application sends a request to execute a stored procedure that does not reside on the server to which it is directly connected.
SQL commands contained in a stored procedure that is
executed as the result of a server-to-server remote procedure call
cannot be rolled back.
The following functions relate to RPCs:
CTBREMOTEPWD sets and clears the passwords that are used when logging in to a remote server (This feature is not available for calls to Open ServerConnect).
CTBCOMMAND initiates an RPC.
CTBPARAM defines parameters for an RPC.
CTBSEND sends an RPC.
CTBRESULTS, CTBBIND, and CTBFETCH process remote procedure results.
In addition to results generated by the SQL statements they contain, Adaptive Server stored procedures and Open ServerConnect transactions that are executed through an RPC:
Can generate a return parameter result set
Always generate a return status result set
All types of results—rows, status, and parameters—can be processed using CTBRESULTS, CTBBIND, and CTBFETCH.
Adaptive Server stored procedures and mainframe server transactions can return values for specified return parameters. Changes made to the value of a return parameter inside the stored procedure or transaction are then available to the program that called the procedure or transaction. This is analogous to the “pass by reference” facility available in some programming languages.
In order for a parameter to function as a return parameter, it must be declared as such within the stored procedure. Client-Library applications use the CTBPARAM routine to indicate return parameters.
Return parameter values are available to an application as a parameter result set only if the application invoked the stored procedure using an RPC.
CTBRESULTS returns CS_PARAM_RESULT if a parameter result set is available to be processed. Because stored procedure parameters are returned to an application as a single row, one call to CTBFETCH copies all of the return parameters for a stored procedure into the program variables designated through CTBBIND. However, an application should always call CTBFETCH in a loop until it returns CS_END_DATA.
When executing a stored procedure, the server returns any parameter values immediately after returning all row results. Therefore, an application can fetch return parameters only after processing the stored procedure row results. A stored procedure can generate several sets of row results, one for each select it contains. An application must call CTBRESULTS and CTBFETCH as many times as necessary to process these row results before calling CTBFETCH to fetch the stored procedure return parameters.
Adaptive Server, Open Server, and Open ServerConnect applications can all return a status.
All stored procedures that run on a Adaptive Server version 4.0 or later return a status. Stored procedures usually return 0 to indicate normal completion. Open ServerConnect status values are documented under TDSNDDON and TDSTATUS in the Mainframe Connect Server Option Programmer’s Reference for PL/1.
Because return status values are a feature of stored procedures, only an RPC or a language request containing an execute statement can generate a return status.
When executing a stored procedure, Adaptive Server returns the status immediately after returning all other results. Therefore, an application can fetch a return status only after processing the stored procedure row and parameter results, if any.
Open Server applications return the status after any row results, but either before or after return parameters.
CTBRESULTS returns CS_STATUS_RESULT if a return status result set is available to be processed. Because a return status result set contains only a single value, one call to CTBFETCH copies the status into the program variable designated via CTBBIND. However, an application should always call CTBFETCH in a loop until it returns CS_END_DATA.
