Creating a Remote Procedure

Define a local alias for a procedure: a means of accessing a procedure on a remote server, through a local resource. Before you can map procedures from another server to the local alias, you must define the remote server where the procedure is located.

Prerequisites

Database Version	Procedure Privileges
SAP Sybase IQ 15.3 and 15.4	Remote procedure to be owned by self or any user – Requires DBA authority.
SAP Sybase IQ 16.0	Remote procedure to be owned by self – Requires CREATE PROCEDURE system privilege. Remote procedure to be owned by any user – Requires one of: CREATE ANY PROCEDURE system privilege. CREATE ANY OBJECT system privilege.

Database Version

Procedure Privileges

SAP Sybase IQ 15.3 and 15.4

Remote procedure to be owned by self or any user –

Requires DBA authority.

SAP Sybase IQ 16.0

Remote procedure to be owned by self –

Requires CREATE PROCEDURE system privilege.

Remote procedure to be owned by any user –

Requires one of:
- CREATE ANY PROCEDURE system privilege.
- CREATE ANY OBJECT system privilege.

The SAP Sybase IQ resource is authenticated and running.
If the resource is a multiplex, the coordinator must be running.

Note: You must define a remote server before you can define a remote procedure on that remote server.

Task

In the Perspective Resources view, select the resource and select Resource > Administration Console.
In the left pane, select IQ Servers > Compiled Objects > Procedures.
Click the arrow next to Procedures and select New Remote Procedure.
The Create Remote Procedures Wizard appears.

On the Welcome page, specify:

Option	Description
Select a resource for which the remote procedure will be created	From the list, select the resource for which the remote procedure will be created.
Which user or group/role do you want to own the remote procedure?	From the list, select the user or role/group to own the remote procedure.
Which remote server contains the procedure?	From the list, select the remote server.
Which procedure do you want to use for this remote procedure?	Select the remote procedure from the list.
What do you want to name the remote procedure?	Enter a unique name for the new remote procedure definition; maximum of 128 characters. The combination of owner and procedure name must be unique in the database

Click Next.
On the SQL page, edit the code provided by the wizard to provide parameter definitions for the remote procedure.

Note: When a query is made to list procedures on a remote server, the query result does not display parameters. Therefore, if a remote procedure does have set parameters (IN, OUT, IN/OUT or RESULT), they must be typed in the editor.
(Optional) Enter a comment to further identify the remote procedure.
Click Finish.

Example

Use a case statement to classify the results of a query:

CREATE PROCEDURE ProductType (IN product_id INT, OUT type CHAR(10))
BEGIN
  DECLARE prod_name CHAR(20) ;
  SELECT name INTO prod_name FROM "GROUPO"."Products"
  WHERE ID = product_id;
  CASE prod_name
  WHEN 'Tee Shirt' THEN
    SET type = 'Shirt'			
  WHEN 'Sweatshirt' THEN
    SET type = 'Shirt'
  WHEN 'Baseball Cap' THEN
    SET type = 'Hat'
  WHEN 'Visor' THEN
    SET type = 'Hat'
  WHEN 'Shorts' THEN
    SET type = 'Shorts'
  ELSE
    SET type = 'UNKNOWN'
  END CASE ;
END