Schema stored procedures for CLI/ODBC/JDBC/OLE DB Client Applications


To obtain the files:

The CLIVSE CATL file must be transferred with format BINARY FIXED 80.
If you downloaded the zip file from the web, you need to unzip it
first before transferring it to your VSE id.


To set up the Schema Stored Procedures for the CLI/ODBC/JDBC/OLE DB
Client Applications:

The schema stored procedures are invoked internally by CLI/JDBC drivers.
You need to prepare the schema stored procedures before the database can
be accessed by the CLI, ODBC, JDBC and/or OLE DB client applications
using DB2 UDB v8 or later.

You  must have as least one stored procedure server  defined before
running this process. To set up a stored procedure server,  refer to
the section "Setting up a Stored Procedure Server"  in Chapter 11
"Stored Procedures" of this manual.  To define a stored procedure
server, refer to the CREATE PSERVER section in the DB2 Server for
VSE and VM SQL Reference manual.

The following files are provided as part of the JCL which catalogs all
necessary parts into your DB2 vSE library.
Obj file     Package
ARIXU01A     ARIXU01M
ARIXU02A     ARIXU02M
ARIXU03A     ARIXU03M
ARIXU04A     ARIXU04M
ARIXU05A     ARIXU05M
ARIXU06A     ARIXU06M
ARIXU07A     ARIXU07M
ARIXU08A     ARIXU08M
ARIXU09A     ARIXU09M
ARIXU10A     ARIXU10M
ArIXU11A     ARIXU11M
ARIXU12A     ARIXU12M
ARIXUPTA     ARIXUPTM


A-Type Member (DBSU INPUT COMMANDS)
ARISPDEF

Z-TYPE Member (Sample Job Control)
ARISPGPH
ARISPCTB
ARISPSET

Modify the JCL control file downloaded to catalog all parts into your
DB2VSE product library.

After cataloging the files, run the supplied JCLs, ARISPGPH, ARISPCTB
and ARISPSET.  You need to run all three jobs when you are setting up
the schema stored procedures for the first time. You only need to run
ARISPGPH and ARISPSET after applying service to the schema stored
procedure(s) unless the instructions in the PTF stating otherwise.

To run these jobs,  you must:
1) ensure the connect ID used has DBA authority;
2) make appropriate changes to these JCLs for your specific environment.

ARISPGPH creates phase for each of the schema stored procedures.

ARISPCTB completes the following tasks:
1. Define tables needed in addition to the system catalog tables  for
the schema stored procedures.
2. Define each of the schema stored procedures to the SYSTEM.SYSROUTINES
catalog table.

ARISPSET completes the following tasks:
1. Load associated packages of the schema stored procedures.
2. Grant RUN authorities on the packages  of  the schema stored
procedures to PUBLIC.


For both DB2 VM and VSE servers, if  you are  using a LOCAL date/time
format with a different length than ISO, USA, EUR, etc., you need to run
the IBM-supplied program ARIXUPTB before running the schema store
procedures for the first time.  You don't need to run this program again
as long as the length of the date/time format  remains unchanged.

Notes:
1. DB2 Server for VM and VSE does not provide casting between types,
i.e., there is no casting from integer to double, char to varchar,
smallint to integer, varchar(18) to varchar(128),  etc. and vice versa.

2. As the concepts of  catalogname to table and schemaname to stored
procedure do not apply to VM/VSE server,NULLs are always returned in
these columns in the result sets.  If catalogname (for all functions)
or schemaname (for SQLPROCEDURECOL and SQLProcedures) is passed from
the calling program with value which is not NULL, empty or "%" for JDBC
only, the stored procedure on VM/ VSE server will return an empty result
set.

3. For SQLProcedures, since the CLI/ODBC  recommends against using the
values returned for NUM_INPUT_PARM and NUM_OUTPUT_PARM and DB2 Server
for VSE and VM has great difficulty in returning the results for these
two columns in a single row in the result set due to the lack of support
of OUTER JOIN, zeros are always returned for these two columns.

4. SQLUDTs always returns an empty result set since user defined types
are not currently supported by DB2 Server for VSE and VM.

5. If the wildcard character % is used when specifying the input value
for schemaname, procname and paramname, it should be used at either the
beginning or the end of the string; otherwise, the result set returned
may not be as  expected because the system catalog columns that they
reference are not defined as variable length.  For explanation, refer
to the third paragraph (right before Simple Description) of the LIKE
Predicate section in the DB2 Server for VSE and VM SQL Reference manual.

6. The schema stored procedures for CLI/JDBC clients are written in C.
C Stored procedure on DB2/VSE&VM cannot handle input parameter  longer
than 254 characters. For explanation, refer to the Stored Procedure
Parameters section in Chapter 3 and Appendix B. Using SQL in C  of the
Application Programming manual.  The input will be truncated.  A message
is displayed on the stored procedure server to indicate the last keyword
which is accepted by the stored procedure.