Schema stored procedures for 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.

The following files are provided:

Text file     Package
ARIXU01A      ARIXU01M
ARIXU02A      ARIXU02M
ARIXU03A      ARIXU03M
ARIXU04A      ARIXU04M
ARIXU05A      ARIXU05M
ARIXU06A      ARIXU06M
ARIXU07A      ARIXU07M
ARIXU08A      ARIXU08M
ARIXU09A      ARIXU09M
ARIXU10A      ARIXU10M
AIXU11A       ARIXU11M
ARIXU12A      ARIXU12M
ARIXUPTA      ARIXUPTM


DSBU
ARISPDEF
ARISPRLD

EXEC
GENMODU



To obtain the files:

Transfer the packlib to  your VM id using format BINARY FIXED 1024.
Unpack the file using the copy or fcopy command with the unpack option.
Extract/copy the individual files from the packlib to your DASD.

Note:
1) If you downloaded the zip file from the web, you need to unzip it
first before transfering it to your VM id.
2) If you do not have the CMS FCOPY utility, you need to download
it from the following site and use it to obtain the files from the
packlib.
http://www.vm.ibm.com/download/packages/


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

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.

Run the supplied EXEC, GENMODU to create all the modules for schema
function support. The modules generated must be put on the 195 disk of
your database machine.

Run the supplied  DBSUs, ARISPDEF and ARISPRLD.  You need to run both
DBSUs when you are setting up the schema stored procedures for the
first time.  You only need to run ARISRLD after applying service to
the schema stored procedure(s) unless the instructions in the PTF
stating otherwise.

To run these DBSUs, you must:
1) ensure the connect ID used has DBA authority;
2) make appropriate changes to these DBSUs for your specific
environment;
3) for ARISPRLD, issue the FILEDEF statements in advance for the
reload package step to complete properly.

ARISPDEF 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.

ARISPRLD 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.