Home

	Expand Search

Access Service User's Guide DirectConnect

Chapter 14 Remote Stored Procedures and Host-Resident requests

Chapter 14

Remote Stored Procedures and Host-Resident requests

This chapter describes how to create, execute, and delete a remote stored procedure (RSP) and host-resident request. The following topics are covered:

• Creating Remote Stored Procedures

• Executing Remote Stored Procedures

• Creating Host-Resident requests

• Executing Host-Resident requests

• Deleting Host-Resident requests

For more information about how to create and execute an RSP, see the Open ServerConnect Programmer's Guide for Remote Stored Procedures.

Creating Remote Stored Procedures

An RSP is a customer-written CICS program that resides on the mainframe. You can write an RSP in any one of the following languages:

• Assembler

• COBOL II

• PL/1

• C

An RSP can use any CICS resource or access any CICS data source.

In an RSP, the access service recognizes argument values that are enclosed in either single or double quotes. Quoted argument values must be in the proper case. The entire statement has a 32 kilobyte size limit.

Executing Remote Stored Procedures

A client application executes an RSP by issuing a command appropriate for the SQL transformation mode in effect.

The access service passes this request to the MainframeConnect for DB2/MVS-CICS access module, AMD2 , which performs a CICS link to the specified RSP. If it does not find a procedure by that name, it searches the host request library (see "Executing Host-Resident requests" for more information). The access service returns all results generated by the RSP to the client application.

In Passthrough mode

A client application executes an RSP by issuing a use procedure command as follows:

use procedure [with [binary] data] procedurename [arglist];
 [input
data]

where:

• procedurename is the name of the RSP.

• arglist is the list of arguments.

• input data is the binary data.

A client application can send input data only when the SQL transformation mode is Passthrough mode (or TSQL0 or TSQL1 backward compatible modes).

If the with binary data clause is specified, you must configure the access service DefaultClientCodeset and DefaultTargetCodeset properties to unequal values. For example:

{ACS Required}
 DefaultClientCodeset=850
 DefaultTargetCodeset=500

These unequal property value settings enable the access service to recognize that all data between the semicolon and the end of the buffer is binary.

If the property values are equal, the access service performs ASCII and EBCDIC translation with the data records.

If the with data clause is specified, the access service assumes that each line following the semicolon (;) is a data record. A single line with a semicolon follows the last record. The access service performs ASCII and EBCDIC translation with the data records.

The following examples show how to execute an RSP in Passthrough mode:

use procedure bob &ARG1=red &ARG2=blue

use procedure bob this is variable text

Do not confuse the use procedure syntax with a Transact-SQL use command, which specifies a SQL server database context.

In Sybase mode

A client application executes an RSP by issuing an execute command as follows:

exec procedurename [arglist];

execute procedurename [arglist];

Following are examples of executing an RSP in Sybase mode:

exec bob @arg1=red, @arg2=blue

execute bob this is variable text

The client application cannot send input pipes when in Sybase mode (or TSQL2 backward compatible mode).

IXF Records

RSPs do not support IXF records.

Creating Host-Resident requests

The access service supports host-resident requests, which are SQL statements stored in a special DB2 table called the host request library. A client application executes a host-resident request the same way it executes an RSP.

Rather than the client application issuing the SQL directly, you can define the statement and then have the client application execute the statement as a procedure. A host-resident request gives you more control over the SQL that a client application generates.

To add a request to the host request library, a client application executes a create request or a create procedure statement that contains the request name and the SQL text. The user who issues the statement must have all necessary privileges on the host request library table.

The following restrictions apply to a host-resident request:

• It must be a single SQL statement.

• Its name must be eight characters or less.

• Its maximum length is 32,000 characters.

In Passthrough mode

When in Passthrough mode (or TSQL0 or TSQL1 backward compatible mode), use the following syntax to create a request:

create
request requestname
 request command

For example:

create request selauth select * from authors

In Sybase mode

When in Sybase mode (or TSQL2 backward compatible mode), use the following syntax to create a request:

create
procedure requestname as
 request
command

For example:

create procedure selauth as select * from authors

The access service does not support the following

create procedure

features of Transact-SQL:

• Numbered procedures

• Default parameter argument values

• The with recompile option

The first two cause errors; the third is ignored.

Executing Host-Resident requests

A client application executes a host-resident request by issuing a SQL command appropriate for the SQL transformation mode in effect. The access service passes this request to the MainframeConnect for DB2/MVS_CICS access module, AMD2, which retrieves the SQL command and executes it against DB2. The access service returns all results to the client application.

In Passthrough mode

When in Passthrough mode (or TSQL0 or TSQL1 backward compatible mode), use the following syntax to execute a host-resident request:

use request requestname

where requestname is the name of the host-resident request.

Some use request statements can be nested. One request can point to a second use request statement, and so on.

Do not confuse the use request syntax with a Transact-SQL use command, which specifies a SQL server database context.

In Sybase mode

When in Sybase mode (or TSQL2 backward compatible mode), execute a host-resident request by using the execute command as follows:

execute requestname

Variables in Host-Resident requests

Host-resident requests can contain up to 50 variables with names of any length. In Passthrough mode, an ampersand (&) precedes each variable. In Sybase mode, an at symbol (@) precedes each variable. Variables are specified as follows:

Passthrough mode:

&var1=value1 &var2=value2 ...

or Sybase mode:

@var1=value1, @var2=value2

The following example shows how to create and execute a host-resident request with variables when in Passthrough mode.

create request seltitle select * from titles
 where type = &vartitle and total_sales > &varsales

use request seltitle &vartitle='psychology' &varsales=2000

The following example shows how to create and execute a host-resident request with variables when in Sybase mode.

create procedure seltitle as select * from titles
 where type = @vartitle and total_sales > @varsales

execute seltitle @vartitle='psychology', @varsales=2000

Deleting Host-Resident requests

To delete a request, use the appropriate command for the SQL transformation mode in effect.

In Passthrough mode

When in Passthrough mode (or TSQL0 or TSQL1 backward compatible mode), delete a request by using the drop request command as follows:

drop request requestname

where requestname is the name of the host-resident request.

In Sybase mode

When in Sybase mode (or TSQL2 backward compatible mode), delete a request by using the drop procedure command as follows:

drop procedure requestname

where requestname is the name of the host-resident request.