Creating a Procedure

Set up a new procedure on an SAP Sybase IQ server.

Prerequisites

Database Version	Procedure Privileges
SAP Sybase IQ 15.3 and 15.4	Watcom SQL or Transact-SQL procedure to be owned by self – you must have one of: DBA authority RESOURCE ADMIN authority Watcom SQL or Transact-SQL, to be owned by any user – you must have DBA authority External C/C++ or External Environment procedure to be owned by self or any user – you must have DBA authority.
SAP Sybase IQ 16.0	Watcom SQL or Transact-SQL procedure to be owned by self – you must have the CREATE PROCEDURE system privilege. Watcom SQL or Transact-SQL procedure to be owned by any user – you must have one of: CREATE ANY PROCEDURE system privilege CREATE ANY OBJECT system privilege External C/C++ or External Environment procedure to be owned by self – you must have all of: CREATE EXTERNAL REFERENCE system privilege CREATE PROCEDURE system privilege External C/C++ or External Environment procedure to be owned by any user – you must have the CREATE EXTERNAL REFERENCE system privilege. You must also have one of: CREATE ANY PROCEDURE system privilege CREATE ANY OBJECT system privilege

Database Version

Procedure Privileges

SAP Sybase IQ 15.3 and 15.4

Watcom SQL or Transact-SQL procedure to be owned by self – you must have one of:

DBA authority
RESOURCE ADMIN authority

Watcom SQL or Transact-SQL, to be owned by any user – you must have DBA authority

External C/C++ or External Environment procedure to be owned by self or any user – you must have DBA authority.

SAP Sybase IQ 16.0

Watcom SQL or Transact-SQL procedure to be owned by self – you must have the CREATE PROCEDURE system privilege.

Watcom SQL or Transact-SQL procedure to be owned by any user – you must have one of:

CREATE ANY PROCEDURE system privilege
CREATE ANY OBJECT system privilege

External C/C++ or External Environment procedure to be owned by self – you must have all of:

CREATE EXTERNAL REFERENCE system privilege
CREATE PROCEDURE system privilege

External C/C++ or External Environment procedure to be owned by any user – you must have the CREATE EXTERNAL REFERENCE system privilege. You must also have one of:

CREATE ANY PROCEDURE system privilege
CREATE ANY OBJECT system privilege

The SAP Sybase IQ resource is authenticated and running.
(Optional) If you are creating a Java procedure, install the required Java classes and JAR files in the database. (You can create the procedure if the classes and JAR files are not installed, but SAP Sybase IQ cannot execute the procedure.)
(Optional) If you are creating a procedure that relies on a non-Java external environment, copy the library that contains the external procedure to a location accessible to the SAP Sybase IQ server. (You can create the procedure if the library is not accessible to SAP Sybase IQ, but SAP Sybase IQ cannot execute the procedure.)

Task

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

On the Welcome page, specify:

Option	Description
Select a resource for which the procedure will be created	From the list, select the resource for which the procedure will be created.
Which user or group/role do you want to own the procedure?	From the list, select the user or role/group to own the procedure.
What do you want to name the procedure?	Enter a unique name for the new procedure; maximum of 128 characters.
Which SQL dialect or language do you want to use?	Select the language or SQL dialect for the procedure. If you choose External environment, click the drop-down menu to select an environment.
Use SQL template	(Optional) Select to insert a hardcoded SQL template. Leave the box unchecked to retrieve generated SQL from the back end.

Click Next.
On the SQL page, edit the code provided by the wizard.
1. For Watcom SQL and Transact-SQL, define input parameters in the format provided.
2. For Watcom SQL, define the result in the format provided.
3. For external languages, fill in the external name in the format provided.
4. Enter procedure statements.
5. Make any other changes needed to complete your procedure.
6. (Optional) Enter a comment describing the procedure.
  Comments can include both HTML and Javadoc tags, so you can incorporate them into your generated database documentation.
Click Finish.