CREATE PROCEDURE (external) statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

To create a fenced procedure, no additional authorities or privileges are required.

To replace an existing procedure, the authorization ID of the statement must be the owner of the existing procedure (SQLSTATE 42501).

Syntax

>>-CREATE--+------------+--PROCEDURE--procedure-name------------>
           '-OR REPLACE-'                              

>--+------------------------------------------------------------------------------------+-->
   '-(--+--------------------------------------------------------------------------+--)-'   
        | .-,--------------------------------------------------------------------. |        
        | V .-IN----.                                                            | |        
        '---+-------+--+----------------+--| data-type |--+--------------------+-+-'        
            +-OUT---+  '-parameter-name-'                 '-| default-clause |-'            
            '-INOUT-'                                                                       

>--| option-list |---------------------------------------------><

data-type

|--+-| built-in-type |-+----------------------------------------|
   '-array-type-name---'   

built-in-type

|--+-+-SMALLINT----+----------------------------------------------------------------------+--|
   | +-+-INTEGER-+-+                                                                      |   
   | | '-INT-----' |                                                                      |   
   | '-BIGINT------'                                                                      |   
   |                  .-(5,0)-------------------.                                         |   
   +-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+   
   | | '-DEC-----' |  |          .-,0-------.   |                                         |   
   | '-+-NUMERIC-+-'  '-(integer-+----------+-)-'                                         |   
   |   '-NUM-----'               '-,integer-'                                             |   
   |          .-(53)------.                                                               |   
   +-+-FLOAT--+-----------+--+------------------------------------------------------------+   
   | |        '-(integer)-'  |                                                            |   
   | +-REAL------------------+                                                            |   
   | |         .-PRECISION-. |                                                            |   
   | '-DOUBLE--+-----------+-'                                                            |   
   |                    .-(1)------------------------.                                    |   
   +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+   
   | | | '-CHAR------'  '-(integer-+-------------+-)-'          |  |              (1) | | |   
   | | |                           +-OCTETS------+              |  '-FOR BIT DATA-----' | |   
   | | |                           '-CODEUNITS32-'              |                       | |   
   | | '-+-VARCHAR----------------+--(integer-+-------------+-)-'                       | |   
   | |   '-+-CHARACTER-+--VARYING-'           +-OCTETS------+                           | |   
   | |     '-CHAR------'                      '-CODEUNITS32-'                           | |   
   | |                                  .-(1M)-----------------------------.            | |   
   | '-+-CLOB------------------------+--+----------------------------------+------------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-+-------------+-)-'              |   
   |     '-CHAR------'                             +-K-+ +-OCTETS------+                  |   
   |                                               +-M-+ '-CODEUNITS32-'                  |   
   |                                               '-G-'                                  |   
   |            .-(1)------------------------.                                            |   
   +-+-GRAPHIC--+----------------------------+------+-------------------------------------+   
   | |          '-(integer-+-------------+-)-'      |                                     |   
   | |                     +-CODEUNITS16-+          |                                     |   
   | |                     '-CODEUNITS32-'          |                                     |   
   | +-VARGRAPHIC--(integer-+-------------+-)-------+                                     |   
   | |                      +-CODEUNITS16-+         |                                     |   
   | |                      '-CODEUNITS32-'         |                                     |   
   | |         .-(1M)-----------------------------. |                                     |   
   | '-DBCLOB--+----------------------------------+-'                                     |   
   |           '-(integer-+---+-+-------------+-)-'                                       |   
   |                      +-K-+ +-CODEUNITS16-+                                           |   
   |                      +-M-+ '-CODEUNITS32-'                                           |   
   |                      '-G-'                                                           |   
   |                          .-(1M)-------------.                                        |   
   +-+-BLOB----------------+--+------------------+----------------------------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                                        |   
   |                                     +-K-+                                            |   
   |                                     +-M-+                                            |   
   |                                     '-G-'                                            |   
   +-+-DATE------+------------------------------------------------------------------------+   
   | +-TIME------+                                                                        |   
   | '-TIMESTAMP-'                                                                        |   
   +-XML----------------------------------------------------------------------------------+   
   | .-SYSPROC.-.                   (2) (3)                                               |   
   '-+----------+--DB2SECURITYLABEL-------------------------------------------------------'   

default-clause

|--DEFAULT--+-NULL-------------+--------------------------------|
            +-constant---------+   
            +-special-register-+   
            +-global-variable--+   
            '-(--expression--)-'   

option-list

|--●--LANGUAGE--+-C-----+--●--+-------------------------+--●---->
                +-JAVA--+     '-SPECIFIC--specific-name-'      
                +-COBOL-+                                      
                +-CLR---+                                      
                '-OLE---'                                      

   .-DYNAMIC RESULT SETS 0--------.     .-MODIFIES SQL DATA-.   
>--+------------------------------+--●--+-------------------+--->
   '-DYNAMIC RESULT SETS--integer-'     +-NO SQL------------+   
                                        +-CONTAINS SQL------+   
                                        '-READS SQL DATA----'   

      .-NOT DETERMINISTIC-.     .-CALLED ON NULL INPUT-.      
>--●--+-------------------+--●--+----------------------+--●----->
      '-DETERMINISTIC-----'                                   

   .-OLD SAVEPOINT LEVEL-.      
>--+---------------------+--●----------------------------------->
   '-NEW SAVEPOINT LEVEL-'      

>--EXTERNAL--+----------------------+--●------------------------>
             '-NAME--+-'string'---+-'      
                     '-identifier-'        

   .-FENCED------------------------.      
>--+-------------------------------+--●------------------------->
   +-FENCED--●--+-THREADSAFE-----+-+      
   |            '-NOT THREADSAFE-' |      
   |                .-THREADSAFE-. |      
   '-NOT FENCED--●--+------------+-'      

     .-COMMIT ON RETURN NO--.        
>--+-+----------------------+-+--●------------------------------>
   | '-COMMIT ON RETURN YES-' |      
   '-AUTONOMOUS---------------'      

   .-EXTERNAL ACTION----.  .-INHERIT SPECIAL REGISTERS-.      
>--+--------------------+--+---------------------------+--●----->
   '-NO EXTERNAL ACTION-'                                     

>--PARAMETER STYLE--+-DB2GENERAL---------+--●------------------->
                    +-DB2SQL-------------+      
                    +-GENERAL------------+      
                    +-GENERAL WITH NULLS-+      
                    +-JAVA---------------+      
                    '-SQL----------------'      

>--+------------------------------+--●-------------------------->
   '-PARAMETER CCSID--+-ASCII---+-'      
                      '-UNICODE-'        

                                  .-NO DBINFO-.      
>--+------------------------+--●--+-----------+--●--------------|
   '-PROGRAM TYPE--+-SUB--+-'     '-DBINFO----'      
                   '-MAIN-'                          

1. The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
2. DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
3. For a column of type DB2SECURITYLABEL, NOT NULL WITH DEFAULT is implicit and cannot be explicitly specified (SQLSTATE 42842). The default value for a column of type DB2SECURITYLABEL is the session authorization ID's security label for write access.

Description

OR REPLACE

Specifies to replace the definition for the procedure if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the procedure are not affected. This option can be specified only by the owner of the object. This option is ignored if a definition for the procedure does not exist at the current server. To replace an existing procedure, the specific name and procedure name of the new definition must be the same as the specific name and procedure name of the old definition, or the signature of the new definition must match the signature of the old definition. Otherwise, a new procedure is created.

procedure-name

Names the procedure being defined. It is a qualified or unqualified name that designates a procedure. The unqualified form of procedure-name is an SQL identifier. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.

The name, including the implicit or explicit qualifiers, together with the number of parameters must not identify a procedure described in the catalog (SQLSTATE 42723). The unqualified name, together with the number of the parameters, need not be unique across schemas.

If a two-part name is specified, the schema-name cannot begin with 'SYS' (SQLSTATE 42939).

(IN | OUT | INOUT parameter-name data-type default-clause,...)

Identifies the parameters of the procedure, and specifies the mode, optional parameter name, data type, and optional default value of each parameter. One entry in the list must be specified for each parameter that the procedure will expect.

No two identically-named procedures within a schema are permitted to have exactly the same number of parameters. A duplicate signature returns an SQL error (SQLSTATE 42723).

For example, given the statements:

   CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
   CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...

the second statement will fail, because the number of parameters in the procedure is the same, even if the data types are not.

If an error is returned by the procedure, OUT parameters are undefined and INOUT parameters are unchanged.

IN

Identifies the parameter as an input parameter to the procedure. Any changes made to the parameter within the procedure are not available to the calling SQL application when control is returned. The default is IN.

OUT

Identifies the parameter as an output parameter for the procedure.

INOUT

Identifies the parameter as both an input and output parameter for the procedure.

parameter-name

Optionally specifies the name of the parameter. The parameter name must be unique for the procedure (SQLSTATE 42734).

data-type

Specifies the data type of the parameter. A structured type cannot be specified (SQLSTATE 429BB).

built-in-type

Specifies a built-in data type. For a more complete description of each built-in data type, see "CREATE TABLE". Only built-in data types that have a correspondence in the language that is being used to write the procedure may be specified.

• A datetime type parameter is passed as a character data type, and the data is passed in the ISO format.
• XML is invalid with LANGUAGE OLE.
• Because the XML value that is seen inside a procedure is a serialized version of the XML value that is passed as a parameter in the procedure call, parameters of type XML must be declared using the syntax XML AS CLOB(n).
• CLR does not support DECIMAL scale greater than 28 (SQLSTATE 42613).
• Decimal floating-point is not supported with languages C, Java™ COBOL, CLR, and OLE (SQLSTATE 42613).

array-type-name

Specifies the name of a user-defined array type. If array-type-name is specified without a schema name, the array type is resolved by searching the schemas in the SQL path. The array must be an ordinary array and the procedure must be a Java procedure defined with the PARAMETER STYLE JAVA clause (SQLSTATE 428H2).

DEFAULT

Specifies a default value for the parameter. The default can be a constant, a special register, a global variable, an expression or the keyword NULL. The special registers that can be specified as the default are that same as those that can be specified for a column default (see "default-clause" in the "CREATE TABLE" statement). Other special registers can be specified as the default by using an expression.

The expression can be any expression of the type described in "Expressions". If a default value is not specified, the parameter has no default and the corresponding argument cannot be omitted on invocation of the procedure. The maximum size of the expression is 64K bytes.

The default expression must not modify SQL data (SQLSTATE 428FL or SQLSTATE 429BL). The expression must be assignment compatible to the parameter data type (SQLSTATE 42821).

A default cannot be specified in the following situations:

• For INOUT or OUT parameters (SQLSTATE 42601)
• For a parameter of type ARRAY, ROW, or CURSOR (SQLSTATE 429BB)

SPECIFIC specific-name

Provides a unique name for the instance of the procedure that is being defined. This specific name can be used when altering, dropping, or commenting on the procedure. It can never be used to invoke the procedure. The unqualified form of specific-name is an SQL identifier. The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another routine instance that exists at the application server; otherwise an error (SQLSTATE 42710) is raised.

The specific-name may be the same as an existing procedure-name.

If no qualifier is specified, the qualifier that was used for procedure-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of procedure-name or an error (SQLSTATE 42882) is raised.

If specific-name is not specified, a unique name is generated by the database manager. The unique name is 'SQL' followed by a character timestamp: 'SQLyymmddhhmmssxxx'.

DYNAMIC RESULT SETS integer

Indicates the estimated upper bound of returned result sets for the procedure.

MODIFIES SQL DATA, NO SQL, CONTAINS SQL, READS SQL DATA

Specifies the classification of SQL statements that can be run by this procedure, or any routine that is called by this procedure. The database manager verifies that the SQL statements issued by the procedure and all routines that are called by the procedure are consistent with this specification.

For the classification of each statement, see SQL statements that can be executed in routines and triggers.

The default is MODIFIES SQL DATA.

MODIFIES SQL DATA

Specifies that the procedure can run any SQL statement except statements that are not supported in procedures (SQLSTATE 38003).

NO SQL

Specifies that the procedure can run only SQL statements with a data access classification of NO SQL (SQLSTATE 38001).

CONTAINS SQL

Specifies that the procedure can run only statements with a data access classification of CONTAINS SQL or NO SQL (SQLSTATE 38003 or 38004).

READS SQL DATA

Specifies that the procedure can run statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL (SQLSTATE 38002 or 38003 or 42985).

DETERMINISTIC or NOT DETERMINISTIC

This clause specifies whether the procedure always returns the same results for given argument values (DETERMINISTIC) or whether the procedure depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC procedure must always return the same result from successive invocations with identical inputs.

This clause currently does not impact processing of the procedure.

CALLED ON NULL INPUT

CALLED ON NULL INPUT always applies to procedures. This means that the procedure is called regardless of whether any arguments are null. Any OUT or INOUT parameter can return a null value or a normal (non-null) value. Responsibility for testing for null argument values lies with the procedure.

OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL

Specifies whether or not this procedure establishes a new savepoint level for savepoint names and effects. OLD SAVEPOINT LEVEL is the default behavior. For more information about savepoint levels, see the "Rules" section in the description of the SAVEPOINT statement.

LANGUAGE

This mandatory clause is used to specify the language interface convention to which the procedure body is written.

C

This means the database manager will call the procedure as if it were a C procedure. The procedure must conform to the C language calling and linkage convention as defined by the standard ANSI C prototype.

JAVA

This means the database manager will call the procedure as a method in a Java class.

COBOL

This means the database manager will call the procedure as if it were a COBOL procedure.

CLR

This means the database manager will call the procedure as a method in a .NET class. At this time, LANGUAGE CLR is only supported for procedures running on Windows operating systems. NOT FENCED cannot be specified for a CLR routine (SQLSTATE 42601).

OLE

This means the database manager will call the procedure as if it were a method exposed by an OLE automation object. The stored-procedure must conform with the OLE automation data types and invocation mechanism. Also, the OLE automation object needs to be implemented as an in-process server (DLL). These restrictions are outlined in the OLE Automation Programmer's Reference.

LANGUAGE OLE is only supported for procedures stored in databases running on Windows operating systems. THREADSAFE may not be specified for procedures defined with LANGUAGE OLE (SQLSTATE 42613).

EXTERNAL

This clause indicates that the CREATE PROCEDURE statement is being used to register a new procedure based on code written in an external programming language and adhering to the documented linkage conventions and interface.

If the NAME clause is not specified, "NAME procedure-name" is assumed. If the NAME clause is not formatted correctly, an error is returned (SQLSTATE 42878).

NAME 'string'

This clause identifies the name of the user-written code which implements the procedure being defined.

The 'string' option is a string constant with a maximum of 254 bytes. The format used for the string is dependent on the LANGUAGE specified.

• For LANGUAGE C:

  The string specified is the library name and procedure within the library, which the database manager invokes to execute the procedure being CREATEd. The library (and the procedure within the library) do not need to exist when the CREATE PROCEDURE statement is performed. However, when the procedure is called, the library and procedure within the library must exist and be accessible from the database server machine.

  
  >>-'--+-library_id-------+--+------------+--'------------------><
        '-absolute_path_id-'  '-!--proc_id-'      
  
  

  The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.

  library_id

  Identifies the library name containing the procedure. The database manager will look for the library as follows:

  Operating system	Library name location
  Linux AIX® Solaris HP-UX	If 'myfunc' was given as the library_id, and the database manager is being run from /u/production, the database manager will look for the procedure in library /u/production/sqllib/function/myproc if FENCED is specified, or /u/production/sqllib/function/unfenced/myproc if NOT FENCED is specified.
  Windows	The database manager will look for the function in a directory path that is specified by the LIBPATH or PATH environment variable.

  Stored procedures located in any of these directories do not use any of the registered attributes.

  absolute_path_id

  Identifies the full path name of the procedure. The format depends on the operating system, as illustrated in the following table:

  Operating system	Full path name example
  Linux AIX Solaris HP-UX	A value of '/u/jchui/mylib/myproc' would cause the database manager to look in /u/jchui/mylib for the myproc procedure.
  Windows	A value of 'd:\mylib\myproc.dll' would cause the database manager to load the file myproc.dll from the d:\mylib directory. If an absolute path ID is being used to identify the routine body, be sure to append the .dll extension.

  ! proc_id

  Identifies the entry point name of the procedure to be invoked. The exclamation point (!) serves as a delimiter between the library ID and the procedure ID. '!proc8' would direct the database manager to look for the library in the location specified by absolute_path_id, and to use entry point proc8 within that library.

  If the string is not properly formed, an error is returned (SQLSTATE 42878).

  The body of every procedure should be in a directory that is mounted and available on every database partition.

• For LANGUAGE JAVA:

  The string specified contains the optional jar file identifier, class identifier and method identifier, which the database manager invokes to execute the procedure being created. The class identifier and method identifier do not need to exist when the CREATE PROCEDURE statement is performed. If a jar_id is specified, it must exist when the CREATE PROCEDURE statement is performed. However, when the procedure is called, the class identifier and the method identifier must exist and be accessible from the database server machine, otherwise an error is returned (SQLSTATE 42884).

  
  >>-'--+----------+--class_id--+-.-+--method_id--'--------------><
        '-jar_id :-'            '-!-'                 
  
  

  The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.

  jar_id

  Identifies the jar identifier given to the jar collection when it was installed in the database. It can be either a simple identifier or a schema qualified identifier. Examples are 'myJar' and 'mySchema.myJar'.

  class_id

  Identifies the class identifier of the Java object. If the class is part of a package, the class identifier part must include the complete package prefix, for example, 'myPacks.StoredProcs'. The directory the Java virtual machine will look in for the classes depends on the operating system, as illustrated in the following table:

  Operating system	Directory the Java virtual machine will look in for the classes
  Linux AIX Solaris HP-UX	'.../myPacks/UserProcs/'
  Windows	'...\myPacks\UserProcs\'

  method_id

  Identifies the method name with the Java class to be invoked.

• For LANGUAGE CLR:

  The string specified represents the .NET assembly (library or executable), the class within that assembly, and the method within the class that the database manager invokes to execute the procedure being created. The module, class, and method do not need to exist when the CREATE PROCEDURE statement is executed. However, when the procedure is called, the module, class, and method must exist and be accessible from the database server machine, otherwise an error is returned (SQLSTATE 42284).

  C++ routines that are compiled with the '/clr' compiler option to indicate that they include managed code extensions must be cataloged as 'LANGUAGE CLR' and not 'LANGUAGE C'. The database manager needs to know that the .NET infrastructure is being utilized in a procedure in order to make necessary runtime decisions. All procedures using the .NET infrastructure must be cataloged as 'LANGUAGE CLR'.

  
  >>-'--assembly--:--class_id--!--method_id--'-------------------><
  
  

  The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.

  assembly

  Identifies the DLL or other assembly file in which the class is located. Any file extensions (such as .dll) must be specified. If the full path name is not given, the file must be in the function directory of the database instance path (for example, C:\Program Data\IBM\DB2\Copy Name). If the file is in a subdirectory of the instance function directory, the subdirectory can be given before the file name rather than specifying the full path. For example, if your instance directory is C:\Program Data\IBM\DB2\Copy Name and your assembly file is C:\Program Data\IBM\DB2\Copy Name\function\myprocs\mydotnet.dll, it is only necessary to specify 'myprocs\mydotnet.dll' for the assembly. The case sensitivity of this parameter is the same as the case sensitivity of the file system.

  class_id

  Specifies the name of the class within the given assembly in which the method that is to be invoked resides. If the class resides within a namespace, the full namespace must be given in addition to the class. For example, if the class EmployeeClass is in namespace MyCompany.ProcedureClasses, then MyCompany.ProcedureClasses.EmployeeClass must be specified for the class. Note that the compilers for some .NET languages will add the project name as a namespace for the class, and the behavior may differ depending on whether the command line compiler or the GUI compiler is used. This parameter is case sensitive.

  method_id

  Specifies the method within the given class that is to be invoked. This parameter is case sensitive.

• For LANGUAGE OLE:

  The string specified is the OLE programmatic identifier (progid) or class identifier (clsid), and method identifier (method_id), which the database manager invokes to execute the procedure being created by the statement. The programmatic identifier or class identifier, and the method identifier do not need to exist when the CREATE PROCEDURE statement is executed. However, when the procedure is used in the CALL statement, the method identifier must exist and be accessible from the database server machine, otherwise an error results (SQLSTATE 42724).

  
  >>-'--+-progid-+--!--method_id--'------------------------------><
        '-clsid--'                    
  
  

  The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.

  progid

  Identifies the programmatic identifier of the OLE object.

  A progid is not interpreted by the database manager, but only forwarded to the OLE automation controller at run time. The specified OLE object must be creatable and support late binding (also known as IDispatch-based binding). By convention, progids have the following format:

     <program_name>.<component_name>.<version>

  Because this is only a convention, and not a rule, progids may in fact have a different format.

  clsid

  Identifies the class identifier of the OLE object to create. It can be used as an alternative for specifying a progid in the case that an OLE object is not registered with a progid. The clsid has the form:

     {nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}

  where 'n' is an alphanumeric character. A clsid is not interpreted by the database manager, but only forwarded to the OLE APIs at run time.

  method_id

  Identifies the method name of the OLE object to be invoked.

NAME identifier

This identifier specified is an SQL identifier. The SQL identifier is used as the library-id in the string. Unless it is a delimited identifier, the identifier is folded to upper case. If the identifier is qualified with a schema name, the schema name portion is ignored. This form of NAME can only be used with LANGUAGE C.

FENCED or NOT FENCED

This clause specifies whether the procedure is considered "safe" to run in the database manager operating environment's process or address space (NOT FENCED), or not (FENCED).

If a procedure is registered as FENCED, the database manager protects its internal resources (for example, data buffers) from access by the procedure. All procedures have the option of running as FENCED or NOT FENCED. In general, a procedure running as FENCED will not perform as well as a similar one running as NOT FENCED.

CAUTION:

Use of NOT FENCED for procedures that were not adequately coded, reviewed, and tested can compromise the integrity of a DB2® database. DB2 databases take some precautions against many of the common types of inadvertent failures that might occur, but cannot guarantee complete integrity when NOT FENCED stored procedures are used.

Either SYSADM authority, DBADM authority, or a special authority (CREATE_NOT_FENCED) is required to register a procedure as NOT FENCED. Only FENCED can be specified for a procedure with LANGUAGE OLE or NOT THREADSAFE.

LANGUAGE CLR procedures cannot be created when specifying the NOT FENCED clause (SQLSTATE 42601).

THREADSAFE or NOT THREADSAFE

Specifies whether the procedure is considered safe to run in the same process as other routines (THREADSAFE), or not (NOT THREADSAFE).

If the procedure is defined with LANGUAGE other than OLE:

• If the procedure is defined as THREADSAFE, the database manager can invoke the procedure in the same process as other routines. In general, to be threadsafe, a procedure should not use any global or static data areas. Most programming references include a discussion of writing threadsafe routines. Both FENCED and NOT FENCED procedures can be THREADSAFE.
• If the procedure is defined as NOT THREADSAFE, the database manager will never invoke the procedure in the same process as another routine.

For FENCED procedures, THREADSAFE is the default if the LANGUAGE is JAVA or CLR. For all other languages, NOT THREADSAFE is the default. If the procedure is defined with LANGUAGE OLE, THREADSAFE may not be specified (SQLSTATE 42613).

For NOT FENCED procedures, THREADSAFE is the default. NOT THREADSAFE cannot be specified (SQLSTATE 42613).

COMMIT ON RETURN

Indicates whether a commit is to be issued on return from the procedure. The default is NO.

NO

A commit is not issued when the procedure returns.

YES

A commit is issued when the procedure returns if a positive SQLCODE is returned by the CALL statement

The commit operation includes the work that is performed by the calling application process and the procedure.

If the procedure returns result sets, the cursors that are associated with the result sets must have been defined as WITH HOLD to be usable after the commit.

AUTONOMOUS

Indicates the procedure should execute in its own autonomous transaction scope.

EXTERNAL ACTION or NO EXTERNAL ACTION

Specifies whether the procedure takes some action that changes the state of an object not managed by the database manager (EXTERNAL ACTION), or not (NO EXTERNAL ACTION). The default is EXTERNAL ACTION. If NO EXTERNAL ACTION is specified, the system can use certain optimizations that assume the procedure has no external impact.

INHERIT SPECIAL REGISTERS

This optional clause specifies that updatable special registers in the procedure will inherit their initial values from the environment of the invoking statement.

No changes to the special registers are passed back to the caller of the procedure.

Non-updatable special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore set to their default values.

PARAMETER STYLE

This clause is used to specify the conventions used for passing parameters to and returning the value from procedures.

DB2GENERAL

This means that the procedure will use a parameter passing convention that is defined for use with Java methods. This can only be specified when LANGUAGE JAVA is used.

DB2SQL

In addition to the parameters on the CALL statement, the following arguments are passed to the procedure:

• A vector containing a null indicator for each parameter on the CALL statement
• The SQLSTATE to be returned to the database manager
• The qualified name of the procedure
• The specific name of the procedure
• The SQL diagnostic string to be returned to the database manager

This can only be specified when LANGUAGE C, COBOL, CLR, or OLE is used.

GENERAL

This means that the procedure will use a parameter passing mechanism by which the procedure receives the parameters specified on the CALL. The parameters are passed directly, as expected by the language; the SQLDA structure is not used. This can only be specified when LANGUAGE C, COBOL, or CLR is used.

Null indicators are not directly passed to the program.

GENERAL WITH NULLS

In addition to the parameters on the CALL statement specified under GENERAL, another argument is passed to the procedure. This additional argument is a vector of null indicators, one for each of the parameters on the CALL statement. In C, this would be an array of short integers. This can only be specified when LANGUAGE C, COBOL, or CLR is used.

JAVA

This means that the procedure will use a parameter passing convention that conforms to the Java language and SQLJ Routines specification. IN/OUT and OUT parameters will be passed as single entry arrays to facilitate returning values. This can only be specified when LANGUAGE JAVA is used.

PARAMETER STYLE JAVA procedures do not support the DBINFO or PROGRAM TYPE clauses.

SQL

In addition to the parameters on the CALL statement, the following arguments are passed to the procedure:

• A null indicator for each parameter on the CALL statement
• The SQLSTATE to be returned to the database manager
• The qualified name of the procedure
• The specific name of the procedure
• The SQL diagnostic string to be returned to the database manager

This can only be specified when LANGUAGE C, COBOL, CLR, or OLE is used.

PARAMETER CCSID

Specifies the encoding scheme to use for all string data passed into and out of the procedure. If the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID ASCII for all other databases.

ASCII

Specifies that string data is encoded in the database code page. If the database is a Unicode database, PARAMETER CCSID ASCII cannot be specified (SQLSTATE 56031). When the procedure is invoked, the application code page for the procedure is the database code page.

UNICODE

Specifies that string data is encoded in Unicode. If the database is a Unicode database, character data is in UTF-8, and graphic data is in UCS-2. If the database is not a Unicode database, character data is in UTF-8. In either case, when the procedure is invoked, the application code page for the procedure is 1208.

If the database is not a Unicode database, and a procedure with PARAMETER CCSID UNICODE is created, the procedure cannot have any graphic types, the XML type, or user-defined types (SQLSTATE 560C1).

If the database is not a Unicode database, and the alternate collating sequence has been specified in the database configuration, procedures can be created with either PARAMETER CCSID ASCII or PARAMETER CCSID UNICODE. All data passed into and out of the procedure will be converted to the appropriate code page.

This clause cannot be specified with LANGUAGE OLE, LANGUAGE JAVA, or LANGUAGE CLR (SQLSTATE 42613).

PROGRAM TYPE

Specifies whether the procedure expects parameters in the style of a main routine or a subroutine. The default is SUB.

SUB

The procedure expects the parameters to be passed as separate arguments.

MAIN

The procedure expects the parameters to be passed as an argument counter, and a vector of arguments (argc, argv). The name of the procedure to be invoked must also be "main". Stored procedures of this type must still be built in the same fashion as a shared library, rather than a stand-alone executable. PROGRAM TYPE MAIN is only valid when the LANGUAGE clause specifies one of: C, COBOL, or CLR.

DBINFO or NO DBINFO

Specifies whether specific information known by the database manager is passed to the procedure when it is invoked as an additional invocation-time argument (DBINFO) or not (NO DBINFO). NO DBINFO is the default. DBINFO is not supported for LANGUAGE OLE (SQLSTATE 42613). It is also not supported for PARAMETER STYLE JAVA or DB2GENERAL.

If DBINFO is specified, a structure containing the following information is passed to the procedure:

• Data base name - the name of the currently connected database.
• Application ID - unique application ID which is established for each connection to the database.
• Application Authorization ID - the authorization ID of the user that connected to the database (the SYSTEM_USER special register).
• Code page - identifies the database code page.
• Database version/release - identifies the version, release and modification level of the database server invoking the procedure.
• Platform - contains the server's platform type.

The DBINFO structure is common for all external routines and contains additional fields that are not relevant to procedures.

If you change session authorization ID (the SESSION_USER special register) using the SET SESSION AUTHORIZATION statement, the Application Authorization ID still returns the value of the SYSTEM_USER special register.

Rules

• Autonomous routine restrictions: Autonomous routines cannot return result sets and do not support the following parameter data types (SQLSTATE 428H2):
  • Cursor types
  • Structured types
  • XML
  Global variables of cursor types cannot be referenced within the autonomous scope.

Notes

• Creating a procedure with a schema name that does not already exist results in the implicit creation of that schema, provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
• A Java routine defined as NOT FENCED will be invoked as if it had been defined as FENCED THREADSAFE.
• A procedure that is called from within a compound SQL (inlined) statement will execute as if it were created specifying NEW SAVEPOINT LEVEL, even if OLD SAVEPOINT LEVEL was specified or defaulted to when the procedure was created.
• XML parameters are only supported in LANGUAGE JAVA external procedures when the PARAMETER STYLE DB2GENERAL clause is specified.
• Setting of the default value: Parameters of a procedure that are defined with a default value are set to their default value when the procedure is invoked, but only if a value is not supplied for the corresponding argument, or is specified as DEFAULT, when the procedure is invoked.
• Privileges: The definer of a procedure always receives the EXECUTE privilege WITH GRANT OPTION on the procedure, as well as the right to drop the procedure. When the procedure is used in an SQL statement, the procedure definer must have the EXECUTE privilege on any packages used by the procedure.
• Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of DB2 and with other database products. These alternatives are non-standard and should not be used.
  • RESULT SETS can be specified in place of DYNAMIC RESULT SETS.
  • NULL CALL can be specified in place of CALLED ON NULL INPUT.
  • DB2GENRL can be specified in place of DB2GENERAL.
  • SIMPLE CALL can be specified in place of GENERAL.
  • SIMPLE CALL WITH NULLS can be specified in place of GENERAL WITH NULLS.
  • PARAMETER STYLE DB2DARI is supported.
  The following syntax is accepted as the default behavior:

  • ASUTIME NO LIMIT
  • NO COLLID
  • STAY RESIDENT NO
  • CCSID UNICODE in a Unicode database
  • CCSID ASCII in a non-Unicode database if PARAMETER CCSID UNICODE is not specified

Examples

• Example 1: Create the procedure definition for a procedure, written in Java, that is passed a part number and that returns the cost of the part and the quantity that is currently available.

     CREATE PROCEDURE PARTS_ON_HAND (IN PARTNUM INTEGER,
         OUT COST DECIMAL(7,2),
         OUT QUANTITY INTEGER)
     EXTERNAL NAME 'parts.onhand'
     LANGUAGE JAVA PARAMETER STYLE JAVA

• Example 2: Create the procedure definition for a procedure, written in C, that is passed an assembly number and returns the number of parts that make up the assembly, total part cost, and a result set that lists the part numbers, quantity, and unit cost of each part.

     CREATE PROCEDURE ASSEMBLY_PARTS (IN ASSEMBLY_NUM INTEGER,
         OUT NUM_PARTS INTEGER,
         OUT COST DOUBLE)
       EXTERNAL NAME 'parts!assembly'
       DYNAMIC RESULT SETS 1 NOT FENCED
       LANGUAGE C PARAMETER STYLE GENERAL