----------------------------------------------------------------------------
IBM DB2 Universal Database
Version 6.1
for Windows NT 4.0, Windows 95, and Windows 98
----------------------------------------------------------------------------
README file for DB2 Java samples
Last updated : April 1999
----------------------------------------------------------------------------
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 Software Developer's Kit (DB2 SDK) for JDBC and SQLJ
development support.
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 C:\sqllib\samples\java to a working directory
(where C: represents the drive on which DB2 is installed).
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.bat 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.bat Builds an SQLJ stored procedure or UDF library and
copies the resulting .class and .ser files to the
%DB2PATH%\function directory (where %DB2PATH% points to
where 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
----------------------------------------------------------------------------
BasicCon.java Performs a basic connection.
Browser.java Lists columns, foreign keys, index columns, or
statistics for a table.
ColPriv.java Lists column privileges.
Columns.java Lists all columns for every table with a name that
matches a given search string.
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
DB2SpCli.java A client application that calls the JDBC stored
procedure, DB2Stp.
DB2Stp.java A stored procedure that updates the EMPLOYEE table on
the server, and returns new salary and payroll
information to the client.
DB2UdCli.java A client application that catalogs and calls the
user-defined functions contained in the server program
DB2Udf.
Dynamic.java Demonstrates a cursor using dynamic SQL.
Embedded.java Creates, populates, lists data from, and drops a table.
GetAttrs.java Lists some common environment, connection, and
statement options and attributes.
GetData.java Queries and displays of data from the sample database.
Inpcli.java A client application that calls the JDBC stored
procedure, Inpsrv.
Inpsrv.java A stored procedure that uses host variables to accept
data from the client application, Inpcli. The stored
procedure creates a table named PRESIDENTS in the
sample database using data passed by host variables.
JavaSamp.java Creates a table, JAVA_SAMPLE, adds data, displays data,
and deletes the table.
JobUpdat.java Uses a statement and a prepared statement to query the
database and update information.
LookRes.java Extracts a string from a resume stored as a character
large object (CLOB) field by converting the entire CLOB
into a char array and searching for the desired
substring. Note that this sample program may not work
on systems that use a non-Latin-1 codepage.
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.
MultiCon.java Performs multiple connections.
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.
PicIn.java Loads a graphic binary large object (BLOB) into the
EMP_PHOTO field using the setBinaryStream() method.
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.
Prepare.java Creates a prepared statement which is repeatedly
executed using different values for the conditional
clause.
ProcCols.java Uses DatabaseMetaData.getProcedureColumns() method to
list the parameters of stored procedures that have been
cataloged with a CREATE PROCEDURE statement.
Procs.java Uses DatabaseMetaData.getProcedures() method to list
the stored procedures that have been cataloged with a
CREATE PROCEDURE statement.
ShowPic.java Extracts a BLOB picture into file using the
getBinaryStream() method. The file is read using the
InputStream.read() method.
Simple.java Performs a basic query and displays the result set.
StpCli.java A client application that calls the SQLJ stored
procedure, Stp.
Tables.java Lists all tables that match the schema search pattern
entered by the user.
Tools.java Contains static methods used by some of the JDBC samples
for performing functions like connecting to a database.
TypeInfo.java Display the data type information for all types used
by the data source to which the program connects.
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 Uses threads to run an SQL statement asynchronously.
Varinp.java Demonstrates variable input to dynamic SQL statement
calls using parameter markers.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
JDBC Samples that use ORDER tables created by Create.java
----------------------------------------------------------------------------
To use these samples, you must:
1. Make the CLI UDF sample order.c
2. Run the samples in the following order:
----------------------------------------------------------------------------
Program Name Program Description
----------------------------------------------------------------------------
Create.java Creates all tables for the order scenario.
CustIn.java Inserts customers into the customer table using an
array insert.
ProdIn.java Inserts products into the products table using an
array insert.
ProdPart.java Inserts parts into the prod_parts table using an
array insert.
OrdIn.java Inserts orders into the ord_line, ord_cust tables
using an array insert.
OrdRep.java Generates a customer order report and uses the user
defined function 'price' defined in the CLI UDF sample
order.c.
PartRep.java Generates exploding parts report using a recursive
SQL query.
DropJava.java Removes the new tables created for the ORDER scenario.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
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.
Stp.sqlj A stored procedure that updates the EMPLOYEE table on
the server and returns new salary and payroll
information to the JDBC client program, StpCli.
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.
----------------------------------------------------------------------------