******************************************************************************
* (c) Copyright IBM Corp. 2007 All rights reserved.
*
* The following sample of source code ("Sample") is owned by International
* Business Machines Corporation or one of its subsidiaries ("IBM") and is
* copyrighted and licensed, not sold. You may use, copy, modify, and
* distribute the Sample in any form without payment to IBM, for the purpose of
* assisting you in the development of your applications.
*
* The Sample code is provided to you on an "AS IS" basis, without warranty of
* any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
* IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
* MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
* not allow for the exclusion or limitation of implied warranties, so the above
* limitations or exclusions may not apply to you. IBM shall not be liable for
* any damages you suffer as a result of using, copying, modifying or
* distributing the Sample, even if IBM has been advised of the possibility of
* such damages.
*
******************************************************************************
*
* README for SQLJ Samples on Windows
*
* The <install_path>\sqllib\samples\java\sqlj directory contains this README
* file. Where <install_path> is the location of DB2 9.7 on your hard drive.
* The default location for <install_path> is
* C:\Program Files\IBM
*
* This README describes how to build and run sqlj sample code for DB2 9.7.
* The DB2 9.7 sqlj samples are located in the following directory:
* <install_path>\sqllib\samples\java\sqlj
*
* Copy the files from this directory to your working directory prior to
* building the sample programs. The sample programs directory is
* typically read-only on most platforms and some samples produce output
* files that require write permissions on the directory.
*
* WARNING: Some of these samples may change your database or database
* manager configuration. Execute the samples against
* a test database only, such as the DB2 SAMPLE database.
*
******************************************************************************
*
* Prepare your DB2 sample development environment
*
* 1) Copy the files in <install_path>\sqllib\samples\java\sqlj\* to your
* working directory and ensure that directory has write permission.
*
* 2) Modify the CLASSPATH to include:
* <install_path>\sqllib\java\db2java.zip
* <install_path>\sqllib\java\db2jcc.jar
* <install_path>\sqllib\java\db2jcc_license_cu.jar
* <install_path>\sqllib\java\<jdkDirName>\lib
* <install_path>\sqllib\lib
* <install_path>\sqllib\function
* <install_path>\sqllib\java\sqlj.zip
* where <jdkDirName> is the name of the
* jdk directory under <install_path>\sqllib\java.
*
* Modify the PATH to include <install_path>\sqllib\java\<jdkDirName>\bin,
* <install_path>\sqllib\lib.
*
* All samples should be run and built in a DB2 Command Window.
* The DB2 Command Window is needed to execute db2 specific commands.
* You can follow the step below to open DB2 Command window.
* From the Start Menu click Start --> Programs --> IBM DB2 -->
* <DB2 copy name> --> Command Line Tools --> Command Window.
*
* Please make sure that JDK_PATH( db2
* database manager configuration parameter) is
* pointing to the <install_path>\sqllib\java\<jdkDirName>.
*
* To see the dbm cfg parameter value, run the following from db2
* command window and look for the value of JDK_PATH.
* db2 get dbm cfg
*
* 3) To build and run SQLj samples, you need to:
*
* 1. Enable tcpip:
* db2set DB2COMM=tcpip
* db2stop
* db2start
*
* 2. By default, your database configuration parameter SVCENAME is
* set to an available port_number. If it is not, update the database
* configuration with an available port number using the following
* command:
* db2 update dbm cfg using SVCENAME <port_number>
* where <port_number> is an available port_number.
*
* After you have updated the database configuration manually, you have
* to restart DB2 using the following commands:
* db2 terminate
* db2stop
* db2start
*
* 4) Start the Database Manager with the following command:
* db2start
*
* 5) Create the sample database with the following command:
* db2sampl
*
* 6) Connect to the database with the following command:
* db2 connect to sample
*
* 7) To build Stored Procedures and User Defined Functions, ensure
* that you have write permission on the
* <install_path>\sqllib\function directory.
*
* 8) cd to the directory containing the files copied in step 1.
*
******************************************************************************
*
* Building DB2 Samples
*
* There are two ways to build DB2 samples: using a nmake utility or
* using the build files that are included with the DB2 sample
* programs.
*
* o To build samples using the nmake utility see
* 'BUILDING SAMPLES USING nmake UTILITY'.
* o To build samples using the build files or when you do not
* have a compatible nmake utility see 'BUILDING
* SAMPLES USING BUILD FILES'.
*
******************************************************************************
*
* *** BUILDING SAMPLES USING nmake UTILITY ***
*
* If you have a compatible nmake utility on your system, you
* can use the makefile provided. Such a nmake utility may be
* provided by another language compiler.Modify the PATH
* variable to include the directory containing the nmake
* utility.
*
* Depending on your environment, the makefile has to be
* modified. To run SQLj samples with the makefile provided, you
* need to hardcode the password in the makefile. Here is a
* list of variables you can set in the SQLj makefile:
* o set DB to the database you want to work with.
* o set UID to a valid user ID if needed (By default, UID is
* set to the current user id).
* o set PWD to the password of the user ID.
* o set SERVER_NAME to the server's name.
* o set PORT_NUMBER to an available port number (By default,
* PORT_NUMBER is set to 50000).
* For more details refer to the 'VARIABLES' section
* in the makefile.
*
* Execute the appropriate 'nmake' command in your working
* directory:
*
* o nmake <program name> - builds the sample identified by
* <program name>
* Do not include the file extension for the
* program name. E.g. nmake DbAuth
* For any dependencies refer to the individual sample.
*
* o nmake srv - builds only samples that can be run on the
* server,including routines (stored procedures and User
* Defined Functions).
*
* o nmake rtn - builds only routines.
*
* o nmake call_rtn - builds only client programs that call
* routines.
*
* o nmake client_run - builds only programs that run
* completely on the client (not ones that call routines).
*
* o nmake all_client - builds all client samples (all
* programs in the 'call_rtn' and 'client_run' categories).
*
* o nmake all - builds all supplied sample programs including
* routines, stored procedures and UDFs.
*
* After compiling the sample, run it using normal java
* invocation,
* java <program_name>
*
* Note:
* The makefile provided will only work if a compatible nmake
* executable program is resident on your system in a directory
* included in your PATH variable. Such a nmake utility may be
* provided by another language compiler.
*
*******************************************************************************
*
* *** BUILDING SAMPLES USING BUILD FILES ***
*
* As an alternative to the makefile, the build files provided
* can be used to build the sqlj samples.
*
* If you do not have a compatible nmake utility you can use
* the SQLj build files to build SQLj programs.
*
* Building and Executing Standalone Samples
* -----------------------------------------
*
* a) To build an SQLj program without a compatible nmake
* utility:
*
* bldsqlj <program name>
*
* Note:
* When you build SQLj programs with the build files, do not
* include the file extension for the program name.
*
* Like the SQLj makefile, the SQLj build files need to be
* updated with a valid user ID, password and an available
* port number.
*
* bldsqlj - Builds an SQLj program. Here is the usage of
* this build file:
*
* bldsqlj <prog_name> (requires hardcoding user ID and
* password in the bldsqlj file)
* bldsqlj <prog_name> <userid> <password>
* bldsqlj <prog_name> <userid> <password> <server_name>
* bldsqlj <prog_name> <userid> <password> <server_name>
* <port_number>
* bldsqlj <prog_name> <userid> <password> <server_name>
* <port_number> <db_name>
*
* Where,
* o <prog_name> - the name of the sample program without the
* .sqlj extension.
* o <userid> - user ID needed connect to the database.
* o <password> - password needed to connect to the
* database.
* o <server_name> - server's name that you work with.
* o <port_number> - an available port_number on the server.
* o <db_name> - the name of the database where the SQL
* package will be created.
* The default is "sample".
*
* NOTE: The following sqlj sample programs containing DDL statements
* need to be pre-compiled using sqlj <program name>.sqlj
* instead of the build file:
*
* TbConstr.sqlj
* TbTemp.sqlj
* TbUMQT.sqlj
*
* o After compiling the sample, run it using normal java invocation
* java <program_name>
*
* Building and Executing Stored Procedures
* ----------------------------------------
*
o Build the server file using:
* bldsqljs <prog_name> (requires hardcoding user ID and
* password in the bldsqlj file.)
* bldsqljs <prog_name> <userid> <password>
* bldsqljs <prog_name> <userid> <password> <server_name>
* bldsqljs <prog_name> <userid> <password> <server_name>
* <port_number>
* bldsqljs <prog_name> <userid> <password> <server_name>
* <port_number> <db_name>
*
* Where,
* o <prog_name> - the name of the sample program without
* the .sqlj extension.
* o <userid> - user ID needed connect to the database.
* o <password> - password needed to connect to the
* database.
* o <server_name> - server's name that you work with.
* o <port_number> - an available port_number on the server.
* o <db_name> - the name of the database where the SQL
* package will be created. The default is
* "sample".
*
* o Build stored procedure using the build file:
* spcat
*
* o Build the corresponding client file sample using
* bldsqljs as shown above.
*
* o Run the sample using normal java invocation:
* java <Client_prog_name>
*
* Building and Executing User Defined Functions:
* ----------------------------------------------
*
* o Build server file using:
* bldsqlj <prog_name> (requires hardcoding user ID and
* password in the bldsqlj file.)
* bldsqlj <prog_name> <userid> <password>
* bldsqlj <prog_name> <userid> <password> <server_name>
* bldsqlj <prog_name> <userid> <password> <server_name>
* <port_number>
* bldsqlj <prog_name> <userid> <password> <server_name>
* <port_number> <db_name>
* Where,
* o <prog_name> - the name of the sample program without
* the .sqlj extension.
* o <userid> - user ID needed connect to the database.
* o <password> - password needed to connect to the
* database.
* o <server_name> - server's name that you work with.
* o <port_number> - an available port_number on the server.
* o <db_name> - the name of the database where the SQL
* package will be created. The default is
* "sample".
*
* o Build the User Defined Functions using build files:
* udfcat or udfjcat.
*
* o Build the corresponding client file sample using
* bldsqlj as shown above.
*
* o Run the sample using normal java invocation:
* java <Client_prog_name>
*
* Building and Running the Data Source Samples
* --------------------------------------------
*
* 1. Obtain and set up non DB2 packages
*
* The following packages must be installed in order to build and run
* the Data Source Samples.
*
* Package Location of Package
* ====================================================================
* JNDI 1.2.1 class http://java.sun.com/products/jndi/
* #downloadLibraries
*
* File System Service http://java.sun.com/products/jndi/
* #downloadProvider 1.2
*
* JDBC 2.0 Optional http://java.sun.com/products/jdbc/download.html
* #spec Package
*
* To build this set of SQLJ samples, update your CLASSPATH to
* include the following files:
* jndi.jar
* fscontext.jar
* providerutil.jar
* jdbc20_stdext.jar
*
* For instance, if you have these jar files located at C:\JAR, your
* CLASSPATH should looks like:
* CLASSPATH=...;C:\JAR\jndi.jar;C:\JAR\fscontext.jar;
* C:\JAR\providerutil.jar;C:\JAR\jdbc2_0-stdext.jar
*
* You also have to update your CLASSPATH to include the path to the
* directory where jndi.properties is located. For instance, if
* jndi.properties is located under C:\JAR, your CLASSPATH should
* looks like:
* CLASSPATH=...;C:\JAR\jndi.jar;C:\JAR\fscontext.jar;
* C:\JAR\providerutil.jar;C:\JAR\jdbc2_0-stdext.jar;C:\JAR\
*
* NOTE:
* The SPI (Service Provider Interface) for storing and lookup of
* DataSources in the JNDI is used as the File System. You can
* modify jndi.properties if you want to use any other SPI for the
* JNDI, for instance, LDAP, DNS, etc.
*
* Please note that the appropriate jar files have to be presented
* in the CLASSPATH. No changes are required to run the SQLJ sample
* programs.
*
* 2. Modify Property Files
*
* Modify the property files DS1.prop, DS2.prop, DS3.prop to reflect
* your environment.
* Here is a list of variables you have to modify in these property
* files:
*
* o serverName - set to the server name.
* o portNumber - set to an available port number (by default set
* to 50000)
* o databaseName - set to the database (by default set to
* 'sample')
* o userName - set to a valid user ID.
* o password - set to the user ID's password.
*
* 3. Create the DataSources used in this set of SQLJ programs
*
* a) Compile the program createRegisterDS.java with the following
* command:
*
* javac createRegisterDS.java
*
* b) Run the createRegisterDS.java program with the following
* command:
*
* java createRegisterDS <property_file>
*
* There are three sample property files. They are as follows:
*
* 1. DS1.prop: jdbc\defaultDataSource
* - used in DataSource1.sqlj
*
* 2. DS2.prop: jdbc\DB2SimpleDataSource_ds1
* - used in Batch1Demo.sqlj, Batch2Demo.sqlj, Batch3Demo.sqlj,
* DataSource2.sqlj, ScrollIterDemo.sqlj
*
* 3. DS3.prop: jdbc\DB2SimpleDataSource_ds2
* - used in DataSource2.sqlj
*
* The DataSources are then created and registered in the JNDI.
*
* 4. Create a few sample tables in the SAMPLE database
*
* a) Compile the CreateDemoSchema.sqlj sample program:
*
* sqlj CreateDemoSchema.sqlj
*
* b) Run the CreateDemoSchema.sqlj sample program:
*
* java CreateDemoSchema
*
* This will create a schema and four tables in the SAMPLE
* database.
* The four tables are as follows:
*
* Batch_Test1 // used in Batch1Demo, Batch2Demo
* Batch_Test2 // used in Batch2Demo
* Scroll_Test
* BlobClob_Tab
*
* 5. Compile and Customize
*
* Compile and Customize the demo sample programs using the provided
* build file
* bldsqljds:
*
* bldsqljds <program_file>
*
* For instance, if you want to compile and customize
* DbConnDataSource.sqlj, do the following:
*
* bldsqljds DbConnDataSource
*
* Run demo sample programs by the following command:
*
* java <program_filename>
*
* For instance, if you want to run Batch1Demo.sqlj, do the following
* command:
*
* java Batch1Demo
*
******************************************************************************
*
* Common file Descriptions
*
* The following are the common files for SQLj samples. For more
* information on these files, refer to the program source files.
*
******************************************************************************
*
* Batch files
*
* bldsqlj.bat - build file for application programs
*
******************************************************************************
*
* Common Utility Function files
*
* makefile - Makefile for sample programs
* Util.sqlj - utilities used by most programs.
*
******************************************************************************
*
* SQLj Samples Design
*
* The Java SQLj sample programs form an object-based design reflecting
* the component nature of DB2. Related samples demonstrate a specific
* level of database programming. Each level is identified by the first
* two characters of the sample name. Here are the database levels
* represented by the samples:
*
* Identifier DB2 Level
*
* Ap Applet Level.
* Db Database Level.
* Tb Table Level.
* Dt Data Type Level.
* Ud UDF Level.
* Sp Stored Procedure Level.
*
******************************************************************************
*
* SQLj Sample File Descriptions
*
* The following are the SQLj sample files included with DB2. For more
* information on the sample programs, refer to the program source
* files.
*
******************************************************************************
*
* Applet Level
*
* Applt.html - HTML file for Applt.sqlj
* Applt.sqlj - How to create applets
*
******************************************************************************
*
* Database Level
*
* DbAuth.sqlj - How to grant\display\revoke authorities at database
* level.
* DbConn.sqlj - How to connect and disconnect from a database.
* DbMCon.sqlj - How to connect and disconnect from multiple
* databases.
* DbUse.sqlj - How to use database objects.
*
******************************************************************************
*
* Table Level
*
* LargeRid.sqlj - How to enable Large RIDs support on both new
* tables\tablespaces and existing tables\tablespaces.
*
* PREREQUISITE: Run the script LargeRid_setup.db2
* before running this sample. Run the LargeRid_cleanup.db2
* script to cleanup the database objects after running the
* sample.
*
* TbAST.sqlj - How to use staging table for updating deferred
* AST.
*
* PREREQUISITE: Run the script TbAST_setup.db2
* before running this sample. Run the TbAST_cleanup.db2
* script to cleanup the database objects after running
* the sample.
*
* TbCompress.sqlj - How to create tables with null and default
* value compression option.
* TbConstr.sqlj - How to work with table constraints.
* TbCreate.sqlj - How to create, alter, and drop tables.
* TbIdent.sqlj - How to use Identity Columns.
* TbInfo.sqlj - How to get and set information at a table
* level.
* TbMod.sqlj - How to modify information in a table.
* TbOnlineInx.sqlj - How to create and reorg indexes on a table.
* TbPriv.sqlj - How to grant\display\revoke privileges at a
* table level.
* TbRead.sqlj - How to read information in a table.
* TbRowcompress.sqlj - How to perform row compression on a table.
*
* PREREQUISITE: Run the script TbRowcompress_setup.db2
* before running this sample. Run the
* TbRowcompress_cleanup.db2 script to cleanup the
* database objects after running the sample.
*
* TbRunstats.sqlj - How to perform runstats on a table.
* TbSel.sqlj - How to select from each of: insert, update,
* delete.
* TbTemp.sqlj - How to use Declared Temporary Table.
* TbTrig.sqlj - How to use a trigger on a table.
* TbUMQT.sqlj - How to use user materialzed query tables
* (summary tables).
* SetIntegrity.sqlj - How to perform online SET INTEGRITY on a table.
*
* PREREQUISITE: Run the script SetIntegrity_setup.db2
* before running this sample. Run the
* SetIntegrity_cleanup.db2 script to cleanup the
* database objects after running the sample.
*
******************************************************************************
*
* Data Type Level
*
* DtUdt.sqlj - How to create\use\drop user defined distinct types.
*
******************************************************************************
*
* UDF Level
*
* UDFcli.sqlj - Call the UDFs in UDFsrv.java.
* UDFCreate.db2 - CLP script to catalog the Java UDFs contained in
* UDFsrv.java.
* UDFDrop.db2 - CLP script to uncatalog the Java UDFs contained in
* UDFsrv.java.
* UDFjcli.sqlj - Call the UDFs in UDFjsrv.java.
* UDFjCreate.db2 - CLP script to catalog the Java UDFs contained in
* UDFjsrv.java.
* UDFjDrop.db2 - CLP script to uncatalog the Java UDFs contained in
* UDFjsrv.java.
* UDFjsrv.java - Provide UDFs to be called by UDFjcli.sqlj.
* UDFsrv.java - Provide UDFs to be called by UDFcli.sqlj.
*
******************************************************************************
*
* Stored Procedure Level
*
* SpCreate.db2 - CLP script to issue CREATE PROCEDURE statements
* SpDrop.db2 - CLP script to drop stored procedures from the catalog
* SpClient.sqlj - Client application that calls the stored procedures.
* SpServer.sqlj - Stored procedure functions built and run on the
* server
* SpIterat.sqlj - Iterator class file for SpServer.sqlj.
*
******************************************************************************
*
* Java Beans Samples
*
* CreateEmployee.sqlj - How to create an employee record.
* GeneratePayroll.sqlj - How to generate payroll reports by
* department.
*
******************************************************************************
*
* Data Source Sample Files
*
* Batch1Demo.sqlj: - setBatchLimit(2) creates two Execution Contexts
* ex1\ex2.Inserts two rows using ex1 and perform
* executeBatch()Does a JDBC select to verify 2 rows
* in table.Runs two update statements for the two
* rows with ex2(DOES NOT do an executeBatch())
* Performs an SQLJ select to verify that the two
* rows were updated - The SQLJ select will trigger
* an implicit Batch Execute
*
* Batch2Demo.sqlj: - setBatching(true) BatchLimit(4) Uses two tables
* Batch_Test1\Batch_Test2. Loops for three times
* (insert statement on Batch_Test2).
* Batch_Test2 should have no rows
* Verifies through JDBC select (as implicit Execute
* OR executeBatch() has not been called)
* setBatchLimit(1)
* Inserts one row into Batch_Test1. This should
* create new Batch and previous batch should be
* implicitly executed.Batch_Test2 should have three
* rows. Batch_Test1 should have
* one row (after SQLJ select has been executed)
*
* Batch3Demo.sqlj - setBatching(true) setBatchLimit(UNLIMITED_BATCH)
* inserts fifty rows into Batch_Test1
* executeBatch().Verifies the number of rows
* through SELECT count(*) from Batch_Test1
*
* DbConnDataSource.sqlj - This is a sample program using the
* defaultContext.Because there is no context
* specified in the SELECT statement, the
* underlying context should be obtained from the
* DataSource jdbc\defaultDataSource which is
* registered using createRegisterDS.java and DS1.prop.
*
* DbConMDataSources.sqlj - This sample program is using the contexts
* TesCtx1 & TestCtx2.The underlying connection
* from ctx1 is obtained from the DataSource
* jdbc\DB2SimpleDataSource_ds1.Another
* connection from ctx2 is obtained from
* jdbc\DB2SimpleDataSource_ds2
*
* ScrollIterDemo.sqlj - This program illustrates usage of Named and
* Positional Scrollable Iterators in SQLJ. Also, it
* shows how a Scrollable Iterator can be
* used for positioned update by implementing
* ForUpdate Clause. However,
* updates will work only when the program is
* customized.
*
* BlobClobDemo.sqlj - This sample program shows the way to access
* Blob\Clob fields in DB2 tables. For accessing
* LOBs in S\390 Db2 additional auxiliary tables
* and indexes need to be created on server side.
*
* createRegisterDS.java - Creates and registers dataSources as specified
* by the property files.The DataSources will be
* created in the temp directory. The file uses
* File System SPI to create a .bindings file in
* the temp directory C:\temp (as defined in
* jndi.properties).
*
* CreateDemoSchema.sqlj - This program creates the schema for the
* DataSource Demo programs.
*
******************************************************************************