README File for Java Samples
DB2 Universal Database for UNIX platforms
=========================================
Last updated : March 2000
----------------------------------------------------------------------------
This file contains information for both JDBC and SQLJ sample programs.
To access, build, and run the DB2 Java sample programs, you must:
1. Install the DB2 Java Enablement component for JDBC and SQLJ
runtime support
2. Install the DB2 Application Development Client for JDBC and SQLJ
development support.
----------------------------------------------------------------------------
Operating System Specific Information
----------------------------------------------------------------------------
For more information on setting up your DB2 Java development environment and
running Java programs, refer to the "Building Java Applets and Applications"
section in the Application Building Guide.
For more information on creating JDBC and SQLJ applications for DB2, including
stored procedures and UDFs, refer to the "Programming in Java" section of the
Application Development Guide.
For the latest information on developing and building Java applications for
DB2, refer to the DB2 Java website at http:// www.software.ibm.com/data/db2/java.
----------------------------------------------------------------------------
QUICKSTART
----------------------------------------------------------------------------
1) Copy $DB2INST/sqllib/samples/java to a working directory
(where $DB2INST represents the location of the DB2 instance).
2) Create the sample database with the following command: db2sampl
3) To build stored procedure and UDF libraries, ensure that you have
write permission to the $DB2INST/sqllib/function directory
(where $DB2INST represents the location of the DB2 instance).
4) Modify the makefile to reflect your environment:
o set DB2PATH to the directory where DB2 is installed
o set UID to the user ID that will be used to connect to the database
o set PWD to the password of the user ID
5) Execute the 'make' command from the working directory
appropriate for your platform:
o make all - builds all samples, including stored procedures and
UDFs
o make srv - builds only stored procedures and UDFs, which must
be stored on the server in the $DB2INST/sqllib/function
directory (where $DB2INST represents the location of the DB2
instance).
o make allcli - builds only the samples that will run on a DB2
client, including samples that call stored procedures and UDFs
o make <program name> - builds the sample identified by <program
name>. Do not include the file extension for the program name.
7) To clean up your system after previous 'make' commands, execute the
following command:
o make cleanall - removes all .class and intermediate files from
the working directory and the $DB2INST/sqllib/function
directory (where $DB2INST represents the location of the DB2
instance)
Note: The makefile provided will only work if a compatible make executable
program is resident on your system in a directory included in your PATH
variable. Such a make utility may be provided by another language compiler.
If you do not have a compatible make utility you cannot use the makefile,
but you can still build JDBC programs with the javac bytecode compiler
using the following syntax:
javac <program name>.java
To build SQLJ programs in an environment without a make utility, you can
use the following build scripts:
----------------------------------------------------------------------------
Script Name Script description
----------------------------------------------------------------------------
bldsqlj Builds an SQLJ program. To build an SQLJ program with
'bldsqlj', use the following syntax:
bldsqlj <SAMPLE> [DATABASE [USERID PASSWORD]]
o SAMPLE - (mandatory) represents the name of the
sample program without the .sqlj extension
o DATABASE - (optional) represents the name of the
database in which an SQL package will be created;
if you do not specify DATABASE, you cannot
specify USERID or PASSWORD. The default database
is 'sample'.
o USERID - (optional) represents the user ID that
will be used to connect to the database specified
by DATABASE.
o PASSWORD - (optional) represents the password for
the user ID specified by USERID
bldsqljs Builds an SQLJ stored procedure or UDF library and
copies the resulting .class and .ser files to the
$DB2INST/sqllib/function directory (where $DB2INST
represents the location of the DB2 instance).
To build an SQLJ stored procedure or UDF library with
'bldsqljs', use the following syntax:
bldsqljs <SAMPLE> [DATABASE [USERID PASSWORD]]
See the previous description of 'bldsqlj' for an
explanation of these build script arguments.
----------------------------------------------------------------------------
SAMPLE PROGRAM DESCRIPTIONS
The following lists describe the functions performed by each sample program
and provide any special information that you may require to build or run
the sample program.
WARNING: Some of these samples will change your database or database manager
configuration. Execute the samples against a 'test' database only,
such as the SAMPLE database.
----------------------------------------------------------------------------
Java Database Connectivity (JDBC) Sample Programs
----------------------------------------------------------------------------
Program Name Program Description
----------------------------------------------------------------------------
DB2Appl.java Queries the sample database using the JDBC application
driver.
DB2Applt.java Queries the database using the JDBC applet driver. It
uses the user name, password, server, and port number
parameters specified in DB2Applt.html.
DB2Applt.html An HTML file that embeds the applet sample program,
DB2Applt. You must customize the server and user
parameters that are passed to the applet.
To view the applet, perform the following steps:
o Compile DB2Applt with the following command:
javac DB2Applt.java
o Start the JDBC server with the following command:
- db2jd <port_number>
where port_number represents the port that will
be used for TCP/IP communications by the applet.
The default port number is 6789.
o Copy the JDBC driver library, db2java.zip, from
$DB2INST/sqllib/java into the same directory as
DB2Applt.html (where $DB2INST represents the
location of the DB2 instance).
o View the applet by loading it in a web browser
that supports JDK 1.1, or view it locally with the
following command:
- appletviewer DB2Applt.html
DB2UdCli.java A client application that catalogs and calls the
user-defined functions contained in the server program
DB2Udf.
DB2Udf.java A user-defined function library containing functions
demonstrating:
1. Integer division
2. CLOB folding
3. Copying characters from one CLOB to another
4. Finding a vowel in a string
5. Using Java instance variables instead of a scratchpad
6. Using a Java UDF with DATALINK columns for a BLOB
7. Various table functions
Dynamic.java Demonstrates a cursor using dynamic SQL.
MRSPcli.java A client program that accepts multiple result sets from
the DB2GENERAL stored procedure MRSPsrv.
MRSPsrv.java The DB2GENERAL stored procedure that returns
multiple result sets to the client program, MRSPcli.
Outcli.java A client application that catalogs and calls the SQLJ
stored procedure, Outsrv. The client and stored
procedure conform to the SQLJ Routines, Part 1 standard
for SQLJ stored procedures.
PluginEx.java A Java program that demonstrates how to add new menu
items and toolbar buttons to the DB2 Control Center.
To compile this program, you must include the following
files and directories in your CLASSPATH:
. (this points to the current working directory)
$DB2INST/sqllib/java/db2java.zip
$DB2INST/sqllib/java/swingall.jar
$DB2INST/sqllib/cc/db2cc.jar
$DB2INST/sqllib/cc
(where $DB2INST represents the location of the DB2
instance).
If your attempt to compile PluginEx.java fails, your JDK
may be unable to handle JAR files and CLASSPATH entries
in the expected fashion. If you suspect this problem,
use the following steps to compile PluginEx.java:
1. Create a working directory (for example, /tmp/work)
2. Copy the $DB2INST/sqllib/cc/navigator directory into
your working directory with the following command:
cp -r $DB2INST/sqllib/cc/navigator /tmp/work
3. Make /tmp/work your working directory with the following
command:
cd /tmp/work
4. Copy PluginEx.java into the working directory with the
following command:
cp $DB2INST/sqllib/samples/java/PluginEx.java .
5. Extract all files from the JAR files using the following
commands:
jar xvf $DB2INST/sqllib/java/swingall.jar
jar xvf $DB2INST/sqllib/cc/db2cc.jar
6. Ensure that your CLASSPATH contains the following entries:
. (the present working directory)
$DB2INST/sqllib/java/db2java.zip
6. Compile PluginEx.java with the following command:
javac PluginEx.java
To use the sample extension to the Control Center, follow
the instructions contained in the PluginEx.java source.
For more information on creating your own extensions to
the Control Center, refer to the Administration Guide.
Spclient.java A client program that calls the following stored
procedures from the Spserver class:
(1) OUT_LANG: retrieves the language in which the stored
procedure library is implemented
(2) OUT_PARAM: retrieves the median salary of the EMPLOYEE
table
(3) IN_PARAMS: passes in 3 values to update salaries in
the EMPLOYEE table
(4) INOUT_PARAM: passes in a value and returns the median
salary of the EMPLOYEE table for employees who make more
than the input value
(5) RESULT_SET_CLIENT: return a result set from the
stored procedure
(6) TWO_RESULT_SETS: returns two result sets from the
stored procedure
(7) ALL_DATA_TYPES: manipulates all of the common data types
in a stored procecure
To successfully run the Spclient class, you must perform
the following steps:
1. javac Spserver.java
2. cp Spserver.class $DB2INST/sqllib/function
(where $DB2INST represents the location of the
DB2 instance)
3. db2 connect to sample
4. db2 -td@ -vf Spcreate.db2
5. javac Spclient.java
6. java Spclient
Spcreate.db2 A CLP script that contains the CREATE PROCEDURE statements
to register the methods contained in the Spserver class as
stored procedures. To run this script, perform the following
commands at the CLP:
1. db2 connect to sample
2. db2 -td@ -vf Spcreate.db2
Spdrop.db2 A CLP script that contains the DROP PROCEDURE statements
necessary for deregistering the stored procedures associated
in the Spserver class. To run this script, perform the following
commands at the CLP:
1. db2 connect to sample
2. db2 -td@ -vf Spdrop.db2
Spserver.java A class that contains the following PARAMETER STYLE JAVA
stored procedures:
(1) outLanguage: returns the language in which the stored
procedure library is implemented
(2) outParameter: returns the median salary of the EMPLOYEE
table
(3) inParams: accepts 3 values and update salaries in
the EMPLOYEE table accordingly
(4) inoutParam: accepts an input value and returns the median
salary of the EMPLOYEE table for employees who make more
than the input value
(5) resultSetToClient: returns a result set to the client
application
(6) twoResultSets: returns two result sets to the client
application
(7) allDataTypes: uses all of the common data types in a
stored procecure
For information on building and running this sample, refer
to the instructions in the Spclient.java description.
UDFcli.java A client application that catalogs and calls a scalar
function, a table function, and a column function from
the Java user-defined function (UDF) library, UDFsrv.
The client also catalogs a user-defined distinct type
(UDT) and creates a table using the UDT. To run this
program, you must first make UDFsrv and copy it to the
correct directory. To drop the UDFs, UDT, and table that
are defined in UDFcli, run this program with the
command-line parameter 'drop', as follows:
java UDFcli drop
UDFsrv.java UDF library that implements the scalar function, table
function, and column function that are cataloged and
called by the UDF client applications: UDFcli.java and
UDFclie.sqlj.
UseThrds.java Demonstrates the use of threads to run asynchronous functions.
It shows how to use a class to exchange information between a
thread and the main program.
Varinp.java Demonstrates variable input to dynamic SQL statement
calls using parameter markers.
V5SpCli.java A client application that calls the JDBC DB2GENERAL
stored procedure, V5Stp.
V5Stp.java A DB2GENERAL stored procedure that updates the
EMPLOYEE table on the server, and returns new salary
and payroll information to the client.
----------------------------------------------------------------------------
Embedded SQL for Java (SQLJ) Sample Programs
----------------------------------------------------------------------------
Program Name Program Description
----------------------------------------------------------------------------
App.sqlj Uses static SQL to retrieve and update data from the
EMPLOYEE table of the sample database.
Applt.sqlj An applet that queries the database using the JDBC
applet driver. It uses the user name, password, server,
and port number parameters specified in Applt.html.
Applt.html An HTML file that embeds the applet sample program,
Applt. You must customize the server and user
parameters that are passed to the applet.
To view the applet, perform the following steps:
o Compile Applt with one of the following commands:
- nmake Applt
or
- bldsqlj Applt
o Start the JDBC server with the following command:
- db2jd <port_number>
where port_number represents the port that will
be used for TCP/IP communications by the applet.
The default port number is 6789.
o Copy the JDBC driver library, db2java.zip, and the
SQLJ library, runtime.zip, from
$DB2INST/sqllib/java into the same directory as
Applt.html.
o View the applet by loading it in a web browser
that supports JDK 1.1, or view it locally with the
following command:
- appletviewer Applt.html
Cursor.sqlj Fetches data using an SQLJ iterator.
OpF_Curs.sqlj Declares the iterator used for updates int the Openftch
sample program.
Openftch.sqlj Fetches, updates, and deletes rows using an SQLJ
iterator declared in OpF_Curs.sqlj.
Outsrv.sqlj A stored procedure that conforms to the SQLJ Routines,
Part 1 specification. It determines the median salary
of the employees in the STAFF table of the sample
database, and returns that value to the JDBC client
application, Outcli, using the OUT parameter
medianSalary.
Static.sqlj Uses static SQL to retrieve information.
Stclient.sqlj A client program that calls the following stored
procedures from the Stserver class:
(1) OUT_LANG: retrieves the language in which the stored
procedure library is implemented
(2) OUT_PARAM: retrieves the median salary of the EMPLOYEE
table
(3) IN_PARAMS: passes in 3 values to update salaries in
the EMPLOYEE table
(4) INOUT_PARAM: passes in a value and returns the median
salary of the EMPLOYEE table for employees who make more
than the input value
(5) RESULT_SET_CLIENT: return a result set from the
stored procedure
(6) TWO_RESULT_SETS: returns two result sets from the
stored procedure
(7) ALL_DATA_TYPES: manipulates all of the common data types
in a stored procecure
To successfully run the Stclient class, you must perform
the following steps:
1. Compile Stserver with one of the following commands:
- make Stserver
or
- bldsqljs Stserver
2. db2 connect to sample
3. db2 -td@ -vf Stcreate.db2
4. Compile Stclient with one of the following commands:
- make Stclient
or
- bldsqlj Stclient
5. java Stclient
Stcreate.db2 A CLP script that contains the CREATE PROCEDURE statements
to register the methods contained in the Stserver class as
stored procedures. To run this script, perform the following
commands at the CLP:
1. db2 connect to sample
2. db2 -td@ -vf Stcreate.db2
Stdrop.db2 A CLP script that contains the DROP PROCEDURE statements
necessary for deregistering the stored procedures associated
in the Stserver class. To run this script, perform the following
commands at the CLP:
1. db2 connect to sample
2. db2 -td@ -vf Stdrop.db2
Stserver.sqlj A class that contains the following PARAMETER STYLE JAVA
stored procedures:
(1) outLanguage: returns the language in which the stored
procedure library is implemented
(2) outParameter: returns the median salary of the EMPLOYEE
table
(3) inParams: accepts 3 values and update salaries in
the EMPLOYEE table accordingly
(4) inoutParam: accepts an input value and returns the median
salary of the EMPLOYEE table for employees who make more
than the input value
(5) resultSetToClient: returns a result set to the client
application
(6) twoResultSets: returns two result sets to the client
application
(7) allDataTypes: uses all of the common data types in a
stored procecure
For information on building and running this sample, refer
to the instructions in the Stclient.sqlj description.
StserverIterator.sqlj Contains the StserverIterator class definition for the
Stserver stored procedure.
UDFclie.sqlj A client application that calls a scalar function, a
table function, and a column function from the Java
user-defined function (UDF) library, UDFsrv. To run
this program, you must first catalog the UDFs by making
and running the JDBC program UDFcli. To drop the UDFs,
user-defined distinct type (UDT), and table that were
defined in UDFcli, run this program with the
command-line parameter 'drop', as follows:
java UDFclie drop
Updat.sqlj Uses static SQL to update a database.
----------------------------------------------------------------------------