******************************************************************************
* (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 SQL Procedures Samples
*
* For windows, the <install_path>\sqllib\samples\sqlpl directory contains
* this README file.
*
* For unix, the <install_path>/sqllib/samples/sqlpl directory contains
* this README file.
*
* This README describes how to build and run stored procedure sample code
* for DB2 9.7. The DB2 9.7 stored procedure samples are located in the
* <install_path>\sqllib\samples\sqlpl directory for windows platform
* and <install_path>/sqllib/samples/sqlpl for unix based platforms.
* 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 for windows
* and $HOME for unix based platform.
*
* The following table lists sample programs that demonstrate SQL procedures.
*
* Files with a ".db2" file extension are DB2 Command Line Processer (CLP)
* scripts. These scripts issue CREATE PROCEDURE statements that create
* stored procedure on the database server.
*
* The stored procedures created by "spserver.db2" can be called by client
* applications created from sample files in other sample directories (see
* the "spserver.db2" source file for details). All other CLP scripts have
* a corresponding client application source file in the sqlpl directory.
*
* The client application for nested stored procedure "nestedsp.db2" is
* "NestedSP.java", which is a client application written using JDBC in
* the JAVA programming language.
*
* The client application for "rsultset.db2" is "rsultset.c", which is a
* client application written using CLI in the C programming language.
*
* The other CLP scripts have a corresponding client application file with
* a ".sqc" file extension, indicating that they use embedded SQL in the
* C programming language.
*
* NOTE: The JDBC driver used in "NestedSP.java" is the legacy JDBC Type 2
* driver. You may modify it to use different DB2 JDBC drivers, but
* be advised that a 64-bit instance of DB2 for Linux on AMD64 does
* not support the DB2 Universal JDBC driver's Type 2 connectivity.
* All other DB2 JDBC drivers are supported. The same is true for the
* "SpClient.java" file mentioned in the comments in the "spserver.db2"
* file.
*
* WARNING: Some of these samples will change your database or database
* manager's configuration. Execute the samples against a test
* database only, such as the DB2 SAMPLE database.
*
******************************************************************************
*
* Prepare your DB2 sample development environment
*
* On Windows steps 2 to 4 should be run in a DB2 Command Window.
* The DB2 Command Window is needed to execute the db2 specific commands.
* Listed below is how to opening the DB2 Command Window:
*
* o From the Start Menu click Start --> Programs --> IBM DB2 -->
* <DB2 copy name> --> Command Line Tools --> Command Window
*
* This Opens the CLP-enabled DB2 window, and initializes the DB2 command line
* environment. Issuing this command is equivalent to clicking the DB2
* Command Window as above.
*
* 1) Copy the files in <install_path>\sqllib\samples\sqlpl\* (for
* windows platform) or <install_path>/sqllib/samples/sqlpl/*
* (for UNIX based platform) to your working directory and ensure that
* directory has write permission.
*
* 2) Start the Database Manager with the following command:
* db2start
*
* 3) Create the sample database with the following command:
* db2sampl
*
* 4) Connect to the database with the following command:
* db2 "connect to sample"
*
* 5) cd to the directory containing the files copied in step 1.
*
******************************************************************************/
*
* Building DB2 Stored Procedure Samples
*
* Building Stored Procedure
*
* To run the SQL procedure CLP scripts, perform the following steps:
* 1. Connect to the database
* 2. Issue the following command at the CLP:
*
* db2 -td@ -vf <script-name>
*
* For example, to issue the CREATE PROCEDURE statement contained in the
* "nestif.db2" CLP script, issue the following command:
*
* db2 -td@ -vf nestif.db2
*
* Building Stored Procedure Client applications.
*
* There are two ways to build DB2 stored procedure client : using a nmake
* utility for windows(make utility for unix based platform) or using
* build files.
*
* o To build client application using the nmake utility for windows see
* 'BUILDING CLIENT APPLICATION USING nmake UTILITY on WINDOWS'.
* o To build client application using the make utility for unix see
* 'BUILDING CLIENT APPLICATION USING make UTILITY on UNIX'.
* o To build client application using the build files or when you do not
* have a compatible nmake utility see 'BUILDING CLIENT
* APPLICATION USING BUILD FILES'.
*
******************************************************************************
*
* *** BUILDING CLIENT APPLICATION USING nmake UTILITY on WINDOWS ***
*
*
* 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 might have to be
* modified.For more details refer to the 'VARIABLES' section
* in the makefile.
*
* Execute the appropriate 'nmake' command in your working
* directory:
*
* nmake <app_name> - Builds the program designated by <app_name>
*
* nmake all - Builds the all the supplied sample programs
*
* nmake clean - Erases intermediate files
* nmake cleanall - Erases all files produced in the build process,
* except the original source files
*
******************************************************************************
*
* *** BUILDING CLIENT APPLICATION USING make UTILITY on UNIX ***
*
* If you have a compatible make utility on your system, you
* can use the makefile provided. Modify the PATH
* variable to include the directory containing the make
* utility.
*
* Depending on your environment, the makefile might have to be
* modified.For more details refer to the 'VARIABLES' section
* in the makefile.
*
* Execute the appropriate 'make' command in your working
* directory:
*
* make <app_name> - Builds the program designated by <app_name>
*
* make all - Builds the all the supplied sample programs
*
* make clean - Erases intermediate files
* make cleanall - Erases all files produced in the build process,
* except the original source files
*
******************************************************************************
*
* *** BUILDING CLIENT APPLICATION USING BUILD FILES ***
*
* For building CLI and C client application, use the build file provided.
* Run the following command to make the client application
* bldapp <sample name>
* where <sample name> is the name of
* the client application without extension.
*
* For building JAVA client application, use the java bytecode compiler to
* compile the application.
* Util.java has utility classes which are required to compile the java
* client application. Compile the Util.java to generate required classes.
* javac Util.java
*
* Run the following command to make the client application
* javac <sample name>
* where <sample name> is the name of
* the client application.
*
******************************************************************************
*
* Common file Descriptions
*
* The following are the common files for SQL Procedures samples. For more
* information on these files, refer to the program source files.
*
******************************************************************************
*
* Common files
*
* makefile - Builds the supplied sample programs in the "sqlpl"
* samples directory.
* README - Lists and describes, at a high-level, all files in the
* "sqlpl" samples directory. (This file).
*
******************************************************************************
*
* BUILD Files for Windows
*
* bldapp.bat - Batch file for compiling embedded C applications
* with the Microsoft Visual C compiler. Use this script to
* compile source files in this directory that have a ".sqc"
* file extension.
* bldcli.bat - Batch file for compiling applications with
* the Microsoft Visual C compiler. Use this batch file to
* compile source files in this directory that have a
* ".c" file extension.
* embprep.bat - Batch file to precompile and bind C sample programs
* that contain embedded SQL.
*
*****************************************************************************
*
* BUILD Files for UNIX
*
* bldapp - Script file for compiling embedded C applications.
* Use this script to compile source files in this directory
* that have a ".sqc" file extension.
* bldcli - Script file for compiling applications. Use
* this script to compile source files in this directory that
* have a ".c" file extension.
* embprep - Script file to precompile and bind C sample
* programs that contain embedded SQL.
*
*****************************************************************************
*
* OTHER
*
* utilapi.c - Utility functions used by DB2 API samples.
* utilapi.h - Header file for utilapi.h.
* utilcli.c - Utility functions used by samples.
* utilcli.h - Header file for utilcli.c.
* utilemb.sqc - Utility functions used by embedded SQL samples.
* utilemb.h - Header file for utilemb.sqc.
*
*****************************************************************************
*
* SQL Procedures sample descriptions
*
* The following are the SQL Procedures sample files included with DB2.
* For more information on these files, refer to program source files.
*
* (CLI) - A client application, which calls a stored procedure or
* declares and calls a user-defined function on the server.
*
******************************************************************************
*
* COMMAND LINE PROCESSOR SQL Procedures Samples
*
* basecase.db2 - The UPDATE_SALARY procedure raises the salary of an
* employee identified by the "empno" IN parameter in the
* "staff" table of the "sample" database. The procedure
* determines the raise according to a CASE statement
* that uses the "rating" IN parameter.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL update_salary ('000100', 1)"
*
* basecase.sqc - Calls the UPDATE_SALARY procedure. (CLI)
*
* baseif.db2 - The UPDATE_SALARY_IF procedure raises the salary of an
* employee identified by the "empno" IN parameter in the
* "staff" table of the "sample" database. The procedure
* determines the raise according to an IF statement that
* uses the "rating" IN parameter.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL update_salary_if ('000100', 1)"
*
* baseif.sqc - Calls the UPDATE_SALARY_IF procedure. (CLI)
*
* dynamic.db2 - The CREATE_DEPT_TABLE procedure uses dynamic DDL to
* create a new table. The name of the table is based on
* the value of the IN parameter to the procedure.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL create_dept_table ('D11', ?)"
*
* dynamic.sqc - Calls the CREATE_DEPT_TABLE procedure. (CLI)
*
* iterate.db2 - The ITERATOR procedure uses a FETCH loop to retrieve
* data from the "department" table. If the value of the
* "deptno" column is not 'D11', modified data is inserted
* into the "department" table. If the value of the
* "deptno" column is 'D11', an ITERATE statement passes
* the flow of control back to the beginning of the LOOP
* statement.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL iterator ()"
*
* iterate.sqc - Calls the ITERATOR procedure. (CLI)
*
* leave.db2 - The LEAVE_LOOP procedure counts the number of FETCH
* operations performed in a LOOP statement before the
* "not_found" condition handler invokes a LEAVE statement.
* The LEAVE statement causes the flow of control to exit
* the loop and complete the stored procedure.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL leave_loop (?)"
*
* leave.sqc - Calls the LEAVE_LOOP procedure. (CLI)
*
* loop.db2 - The LOOP_UNTIL_SPACE procedure counts the number of
* FETCH operations performed in a LOOP statement until
* the cursor retrieves a row with a space (' ') value
* for column "midinit". The loop statement causes the
* flow of control to exit the loop and complete the
* stored procedure.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL loop_until_space (?)"
*
* loop.sqc - Calls the LOOP_UNTIL_SPACE procedure. (CLI)
*
* nestcase.db2 - The BUMP_SALARY procedure uses nested CASE statements
* to raise the salaries of employees in a department
* identified by the dept IN parameter from the "staff"
* table of the "sample" database.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL bump_salary (51)"
*
* nestcase.sqc - Calls the BUMP_SALARY procedure. (CLI)
*
* nestedsp.db2 - This CLP script contains three stored procedures.
* They are OUT_AVERAGE, OUT_MEDIAN and MAX_SALARY.
* They are nested, where OUT_AVERAGE calls OUT_MEDIAN
* and OUT_MEDIAN calls MAX_SALARY.
* To call these SQL procedures from the CLP,
* issue the following statement:
* db2 "CALL out_average (?,?,?,?,?)"
*
* nestedspdrop.db2- This CLP script drops the three stored procedures
* created by nestedsp.db2.
*
* NestedSP.java - Calls the OUT_AVERAGE procedure. (CLI)
*
* nestif.db2 - The BUMP_SALARY_IF procedure uses nested IF statements
* to raise the salaries of employees in a department
* identified by the dept IN parameter from the "staff"
* table of the "sample" database.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL bump_salary_if (20)"
*
* nestif.sqc - Calls the BUMP_SALARY_IF procedure. (CLI)
*
* repeat.db2 - The REPEAT_STMT procedure counts the number of FETCH
* operations performed in a repeat statement until the
* cursor can retrieve no more rows. The condition handler
* causes the flow of control to exit the repeat loop and
* complete the stored procedure.
* To call this SQL procedure from the CLP,
* issue the following statement:
* db2 "CALL repeat_stmt (?)"
*
* repeat.sqc - Calls the REPEAT_STMT procedure. (CLI)
*
* rsultset.db2 - The MEDIAN_RESULT_SET procedure calculates the median
* salary of employees from the "staff" table of
* the "sample" database. The median value is assigned
* to the salary OUT parameter and returned to the
* "rsultset" client. The procedure opens two
* WITH RETURN cursors to return result sets of the
* employees with a salary greater than the median and
* employees with a salary less than the median salary.
* The procedure returns the result sets to the client.
* To call this SQL procedure from the CLP, issue the
* following statement:
* db2 "CALL median_result_set (?)"
*
* rsultset.c - Calls the MEDIAN_RESULT_SET procedure, (CLI)
* displays the median salary, then displays
* the result set generated by the SQL
* procedure. This client is written using
* the API, which can accept result sets.
*
* spserver.db2 - The SQL procedures in this CLP script demonstrate basic
* error-handling, nested stored procedure calls, and
* returning result sets to the client application or
* the calling application. This script contains the
* following SQL procedures:
* o OUT_LANGUAGE
* o OUT_PARAM
* o IN_PARAMS
* o INOUT_PARAM
* o ONE_RESULT_SET
* o RESULT_SET_CALLER
* o TWO_RESULT_SETS
* o ALL_DATA_TYPES To call these SQL procedures,
* you can use the "spclient" application in the C,
* CLI, and CPP samples directories, or the "SpClient"
* application in the Java/JDBC and Java/SQLj
* samples directories.
*
* tbfn.db2 - The tables and SQL functions in this CLP script are used
* to illustrate various ways of invoking a table function
* that MODIFIES SQL DATA from within a SELECT statement.
*
* tbfnuse.db2 - This script invokes three table functions, updateInv,
* sal_by_dept, and update_salary. It displays the data
* in the tables related to this sample before and after
* the invocations to the table functions.
*
* tbselcreate.db2 - The tables and SQL procedure BUY_COMPANY in this CLP
* script are used to illustrate various ways of using a
* SELECT statement with a data change statement as the
* table-reference in the FROM clause. Data change
* statements include INSERT, UPDATE, DELETE, MERGE.
* Referencing one of these in a SELECT statement
* (also called SELECT FROM a data change statement)
* is useful for retrieving column values of just inserted
* rows (ie. when a column is a generated column), or for
* retrieving the old and new values of an updated column
* without having to use two statements, and more...
* The procedure BUY_COMPANY encapsulates some examples of
* useful applications of this statement.
* To call this SQL procedures, you can use the
* "tbsel" application in this directory.
*
* tbseldrop.db2 - The SQL statements in this script drop the tables and the
* procedure created script tbselcreate.db2
*
* tbsel.sqc - Client application that CALLs the (CLI)
* BUY_COMPANY SQL procedure, displays the
* data in the tables related to this sample
* before and after the CALL to the SQL procedure.
*
* whiles.db2 - The DEPT_MEDIAN procedure obtains the median salary of
* employees in a department identified by the "dept" IN
* parameter from the "staff" table of the "sample" database.
* The median value is assigned to the salary OUT parameter
* and returned to the "whiles" client. The whiles client
* then prints the median salary.
* To call this SQL procedure from the CLP, issue
* the following statement:
* db2 "CALL dept_median (51, ?)"
*
* whiles.sqc - Calls the DEPT_MEDIAN procedure. (CLI)
*
* arrays_sqlpl.db2- How to use ARRAY data type in SQL stored procedure.
* For java client for this sample please refer to
* sqllib/samples/java/jdbc directory
*
* array_stack.db2 - How to use ARRAY data type in SQL stored procedure
* to implement a stack operations.
* For java client for this sample please refer to
* sqllib/samples/java/jdbc directory
*
* modules.db2 - This sample demonstrates:
* 1. Creation of modules
* 2. Creation and usage of row data types
* 3. Creation and usage of strongly-typed and weakly-typed
* cursors
* 4. Full SQL PL support for functions and anonymous blocks
*
*defaultparam.db2 - How to use DEFAULT values in procedure.
********************************************************************************