****************************************************************************** * (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. ********************************************************************************