IBM Books

Call Level Interface Guide and Reference

Appendix H. Pseudo Catalog Table for Stored Procedure Registration

For versions of DB2 CLI before DB2 Universal Database, the DB2CLI.PROCEDURES table must be created and populated at the server before SQLProcedures() and SQLProcedureColumns() can be called to retrieve information about stored procedures and their attributes.

DB2 Universal Database now makes use of the SYSCAT.PROCEDURES and SYSCAT.PROCPARAMS catalog tables which contain information about stored procedures and therefore the DB2CLI.PROCEDURES table is no longer required.

If you are still using a version of DB2 before version 5, you can use the sample command line processor input file STORPROC.DDL to create the DB2CLI.PROCEDURES table. You may then modify the sample STORPROC.XMP file to insert rows into this PROCEDURES table. Both of these files are located in the misc subdirectory of the sqllib directory. To use the file to create the table, execute the following from a command line: 

   db2 -f STORPROC.DDL -z STORPROC.LOG -t

It is the database administrator's responsibility to ensure that information has been entered correctly into the table and to keep the table up to date. Initially, all users have SELECT privilege for this table and only users with DBADM authority can INSERT, DELETE or UPDATE rows in this table. As with other tables, a user with DBADM authority can grant privileges to other users.

Legend for the DB2CLI.PROCEDURES Table:

Column Name
Name of the column

Data Type
Data type of the column

Nullable

Yes -- Nulls are permitted
No -- Nulls are not permitted

Key

Key -- The column is part of a primary key
No -- The column is not part of a key

Description
Description of the column

Table 214. Columns of the PROCEDURES table in the DB2CLI schema
Column Name Data Type Nullable Key Description
PROCSCHEMA VARCHAR(18) No PK Schema name of the procedure.
PROCNAME VARCHAR(18) No PK Name of the stored procedure specified on the SQL CALL statement.
DEFINER VARCHAR(8) No No Definer of the stored procedure. (The database administrator who inserted this row into the table.)
PKGSCHEMA VARCHAR(18) No No Schema name of the package to be used when the stored procedure is executed.
PKGNAME VARCHAR(18) No No Name of the package to be loaded when the stored procedure is executed.
PROC_LOCATION VARCHAR(254) No No External (full path) name of the procedure.
PARM_STYLE CHAR(1) No No The convention used to pass parameters to the stored procedure:
  • D for the Database Application Remote Interface (DARI) convention used by DB2 for common server servers.
LANGUAGE CHAR(8) No No The programming language used to create the stored procedure. Possible values are COBOL, C, REXX and FORTRAN for for common servers of DB2. (The value C is used for both C and C++ programs.) Other products may enable other languages, for example: PL/I and BASIC.
STAYRESIDENT CHAR(1) No No Determines whether the stored procedure load module is deleted from memory when the stored procedure ends:

  • Y indicates that the load module remains resident in memory after the stored procedure ends.

    This includes the case when the stored procedure returns SQLZ_HOLD_PROC to stay resident for a number of calls, and then terminates by returning SQLZ_DISCONNECT_PROC.

  • A blank entry indicates that the load module is deleted from memory after the stored module terminates.
RUNOPTS VARCHAR(254) No No Reserved (empty string).
PARM_LIST VARCHAR(3000) No No Parameter list of the stored procedure. See the syntax diagram following this table for the format of this parameter list.
FENCED CHAR(1) No No An indication of whether or not the procedure runs "fenced":
  • Y indicates the stored procedure is fenced
  • N indicates the stored procedure is not fenced
REMARKS VARCHAR(254) Yes No Description of the stored procedure.
RESULT_SETS SMALLINT No No The number of result sets that can be returned.

The input format of the parameter list column, PARM_LIST, is defined in Figure 19. If there is a syntax error in the contents of this column, a call to SQLProcedureColumns() will result in an error.

Figure 19. PARMLIST String Syntax

   .-,------------------------------------------------------------------------.
   V                                                                          |
>>----+--------------------------------------------------------------------+--+->
      |                                                         .-IN----.  |
      '-parm-name--| data-type |---+------------------------+---+-------+--'
                                   '-FOR--+-SBCS--+---DATA--'   +-OUT---+
                                          +-MIXED-+             '-INOUT-'
                                          '-BIT---'
 
>--------------------------------------------------------------><
 
data-type
 
|---+-INT-------------------------------------------------+-----|
    +-INTEGER---------------------------------------------+
    +-SMALLINT--------------------------------------------+
    +-FLOAT--+------------------+-------------------------+
    |        '-(--integer *--)--'                         |
    +-REAL------------------------------------------------+
    +-DOUBLE----------------------------------------------+
    +-DOUBLE PRECISION------------------------------------+
    +--+-DEC-----+---+----------------------------------+-+
    |  +-DECIMAL-+   '-(--integer--+-------------+---)--' |
    |  +-NUM-----+                 '-,--integer--'        |
    |  '-NUMERIC-'                                        |
    +--+-CHAR------+---+----------------+-----------------+
    |  '-CHARACTER-'   '-(--integer--)--'                 |
    +-VARCHAR--(--integer--)------------------------------+
    +-LONG VARCHAR----------------------------------------+
    +-GRAPHIC--+----------------+-------------------------+
    |          '-(--integer--)--'                         |
    +-VARGRAPHIC--(--integer--)---------------------------+
    +-LONG VARGRAPHIC-------------------------------------+
    +--+-BLOB---+---(--integer--+-K-+---)-----------------+
    |  +-CLOB---+               +-M-+                     |
    |  '-DBCLOB-'               '-G-'                     |
    +-DATE------------------------------------------------+
    +-TIME------------------------------------------------+
    '-TIMESTAMP-------------------------------------------'
Note:* - not supported in common server versions of DB2

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]