Home

	Expand Search

Access Service User's Guide DirectConnect for AS/400

Chapter 14 Using AS/400 Stored Procedures

Creating an AS/400 Stored Procedure

Creating an AS/400 Stored Procedure

To create an AS/400 stored procedure, perform the following tasks:

• Be sure the NULLID collection exists.

• Build and execute the create procedure statement.

• Grant authorization on the stored procedure package.

All tables that the AS/400 stored procedure refers to should be qualified with the collection name. If a table is not qualified with the collection name, AS/400 SQL uses the user profile name of the AS/400 job (connection).

The access service library requires a naming convention to distinguish between SQL stored procedures that return a result set, those that do not, and external stored procedures:

• The first letter of a SQL stored procedure that returns a result set must be "s". For example:

      create procedure sauthors as select * from authors

• If the SQL stored procedure does not return a result set, the first letter of the stored procedure name must be something other than "s" or "x_".

• External stored procedure names must have an "x_" prefix. For example:

      create procedure x_excrule as call rulelib.rulepgm

Variables and syntax in Stored Procedures

An AS/400 stored procedure can contain variables, so that arguments can be passed to it when it runs. A argument can be used for any data value in the SQL statement but cannot be used for any table or column name.

The syntax for AS/400 stored procedures is as follows:

create procedure 

where:

• procname is the name of the stored procedure

• arglist is a set of arguments or argument specifications separated by commas

• optionlist is a set of options that affect the bind-time processing of the stored procedure package at the database

• sqlstatement is the AS/400 stored procedure SQL statement

Arguments in the arglist must be in the following format:

@parmname argtype, ...

or

&parmname argtype, ...

where:

• parmname is an arbitrary name for the argument. It can contain a maximum of 31 characters.

• argtype is any of the following datatypes: CHAR, VARCHAR, LONG VARCHAR, BINARY, VARBINARY, TEXT, INT, SMALLINT, FLOAT, REAL, DECIMAL, GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC.

  Formats for argtype are as follows:

  CHAR(length) VARCHAR(length) LONG VARCHAR(length)DECIMAL(precision,scale)

• A maximum of 50 arguments can be in arglist.

• There must be a one-to-one correspondence between the number of arguments used in the SQL statement of the AS/400 stored procedure and the number of arguments in the arglist. The arguments must occur in the same order.

• If an argument is required twice in the AS/400 stored procedure, it must appear twice in arglist.

• An argument must be declared as a datatype consistent with the datatype of the relevant column as follows:

  • Any argument declared as numeric can be used with any numeric column.

  • Any argument declared as character can be used with any character column. (TEXT is handled by the access service as CHAR or VARCHAR.)

  • Any argument declared as a character type can be used with a DATE, TIME, or TIMESTAMP column.

It is best to describe the arguments exactly as they appear in the corresponding column. In some cases, this can allow the AS/400 DBMS to use the SQL statement more efficiently.

The optionlist allows a client to specify information about the stored procedure. The client can specify options only when SQL transformation is in Passthrough mode. Sybase mode does not allow options.

A client can specify the following options:

• verify={req|opt} instructs the access service to verify the following:

  • verify=req instructs the access service to return an error message if a referenced object does not exist or the proper authorization is lacking. The create procedure statement fails when verify=req.

  • verify=opt instructs the access service to return a warning message if a referenced object is missing or if proper authorization is lacking. However, the create procedure statement succeeds if the objects exist and the proper authorizations are in place before the procedure executes. verify=opt is the default.

• owner=userid instructs the access service to use the specified userid as the package owner. Including a user ID does not affect creation or execution of the AS/400 stored procedure package unless the security setting affects it. The current user's login is the default.

• isolv={cs|ur|no} specifies the isolation level for binding the AS/400 stored procedure package (which is created when a SQL stored procedure is created).

  • isolv=cs specifies an isolation level of cursor stability. This means that all tables referenced by the SQL statement must be journaled. isolv=cs is the default.

  • isolv=ur specifies an isolation level of uncommitted read. ur specifies that the package is bound with the CHG isolation level. This means that any referenced tables in a select statement do not need to be journaled.

  • isolv=no specifies an isolation level of NONE, which means that journaling is not required for any objects in this SQL statement.

To include options in a create procedure statement, use a format as follows:

verify=req, isolv=ur, owner=userid

Consider the following guidelines for a create procedure statement:

• Separate each option and its value with a comma.

• Enclose the value for owner in single or double quotes:

  • If the value is enclosed in quotes, the access service passes the value exactly as it is written to the AS/400.

  • If the value is not enclosed in quotes, the access service converts the value to uppercase.

If the name you supply for procname already exists, the create procedure statement fails. Either drop the conflicting procedure or use a different name for the new procedure.

Interaction between AS/400 Stored Procedures and CSPs

This section describes the interaction between AS/400 stored procedures and catalog stored procedures (CSPs). It also contains information about creating an AS/400 stored procedure and CSP tables.

When the create procedure statement executes, the following events occur:

• A package for the AS/400 stored procedure is created in the NULLID collection of the AS/400. The package has the same name as the stored procedure.

• AS/400 verifies that the datatype declared for each argument is consistent with the datatype of the relevant column.

• One of the following happens, depending on whether or not the CSP tables NULLID.SYSPROCS and NULLID.SYSPROCCOL were created:

  • If the CSP tables were created, the access service updates the tables with information about the new stored procedure. One row is inserted into NULLID.SYSPROCS for the stored procedure, and one row is inserted into NULLID.SYSPROCCOL for each argument declared in the stored procedure. These tables are created by the CSP.SQL script that comes with the access service library.

  • If the tables were not created, the insert fails because rows cannot be inserted into tables that do not exist. However, because the create procedure request precedes the insert, the request is not rolled back when a failure occurs.

  To query these tables about existing AS/400 stored procedures, use the following CSPs:

  • sp_stored_procedures

  • sp_sproc_columns procname

    where procname is the name of the stored procedure.

  For more information about sp_stored_procedures, see sp_stored_procedures .

  For more information about sp_sproc_columns, see sp_sproc_columns .