README File for Java Samples
DB2 Universal Database for OS/2
===============================
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.
Java programs must be built on an HPFS drive. During installation, if the DB2
install program detects that the target drive is FAT, it places two files,
javasamp.exe and this README file, in the %DB2PATH%\samples\java directory
(where %DB2PATH% is the location where DB2 is installed). To use the Java
sample programs, move javasamp.exe to your HPFS working directory, and
run this executable program. This will extract the Java sample programs into
the directory. If the install progam detects that the target drive is HPFS,
it will extract the Java sample files into the %DB2PATH%\samples\java
directory during the install process. It is highly recommended that you copy
these files to a separate HPFS working directory before using them.
To run Java stored procedures or user-defined functions (UDFs) on an OS/2
server, you must install DB2 on an HPFS drive on the server in order to
allow the stored procedure or UDF ".class" files to be placed in the
%DB2PATH%\function directory without truncating the filename extension.
To build SQLJ programs, you must turn off the just-in-time compiler feature
of the Java Development Kit with the following command:
SET JAVA_COMPILER=NONE
Note that SQLJ source files may include an end-of-file character (EOF) that
causes the SQLJ translator 'sqlj' to fail with an exception similar to the
following:
Cursor.sqlj:122.2: Error: Java Parsing. Encountered: \u001a
Expected: <EOF> ; "abstract" ...; "public" ...; "interface" ...;
"#sql" ...; ";" ...;
Total 1 error.
To remove the offending EOF, open the SQLJ source file in a text editor
that does not automatically append an EOF, modify the file by adding a
blank line to the end of the file, and resave the file.
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 %DB2PATH%\samples\java files to an HPFS working directory (where
%DB2PATH% is the location where DB2 is installed, such as "C:\sqllib").
If %DB2PATH%\samples\java is on a FAT drive, copy and run the javasamp.exe
program to extract the Java files into your HPFS working directory.
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 C:\sqllib\function directory
(where C: represents the drive on which DB2 is installed).
4) Modify the makefile to reflect your environment:
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 'nmake' command from the working directory
appropriate for your platform:
o nmake all - builds all samples, including stored procedures and
UDFs
o nmake srv - builds only stored procedures and UDFs, which must
be stored on the server in the C:\sqllib\function directory
(where C: represents the drive on which DB2 is installed).
o nmake allcli - builds only the samples that will run on a DB2
client, including samples that call stored procedures and UDFs
o nmake <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 nmake cleanall - removes all .class and intermediate files from
the working directory and the C:\sqllib\function directory
(where C: represents the drive on which DB2 is installed).
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.cmd 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.cmd Builds an SQLJ stored procedure or UDF library and
copies the resulting .class and .ser files to the
C:\sqllib\function directory (where C: represents the
drive on which DB2 is installed).
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
C:\sqllib\java into the same directory as
DB2Applt.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 DB2Applt.html
DB2UdCli.java A client application that catalogs and calls the
user-defined functions contained in the UDF library,
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 stored procedure MRSPsrv.
MRSPsrv.java The 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)
C:\sqllib\java\db2java.zip
C:\sqllib\java\swingall.jar
C:\sqllib\cc\db2cc.jar
C:\sqllib\cc
(where DB2INST1 represents the instance under which DB2 is
installed).
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, C:\temp\work)
and a "navigator" subdirectory
2. Copy the C:\sqllib\cc\navigator directory into
your working directory with the following command:
copy C:\sqllib\cc\navigator C:\temp\work\navigator
3. Make C:\temp\work your working directory with the following
command:
cd C:\temp\work
4. Copy PluginEx.java into the working directory with the
following command:
copy C:\sqllib\samples\java\PluginEx.java .
5. Extract all files from the JAR files using the following
commands:
jar xvf C:\sqllib\java\swingall.jar
jar xvf C:\sqllib\cc\db2cc.jar
6. Ensure that your CLASSPATH contains the following entries:
. (the present working directory)
C:\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. copy Spserver.class C:\sqllib\function
(where C: represents the drive on which DB2 is
installed)
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.
V5SpCli.java A client application that calls the V5Stp.java stored procedure
to modify employee salaries and print out payroll information.
V5Stp.java Stored procedure that modifies employee salaries and returns
payroll information to the V5SpCli.java client program.
Varinp.java Demonstrates variable input to dynamic SQL statement
calls using parameter markers.
----------------------------------------------------------------------------
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 C:\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:
- nmake Stserver
or
- bldsqljs Stserver
2. db2 connect to sample
3. db2 -td@ -vf Stcreate.db2
4. Compile Stclient with one of the following commands:
- nmake 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.
----------------------------------------------------------------------------