//*************************************************************************** // (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. //*************************************************************************** // SAMPLE FILE NAME: Arrays_Sqlpl.java // // PURPOSE: To demonstrate the new ARRAY type and functions UNNEST and // ARRAY_AGG. // // USAGE SCENARIO: Scenario is based on the employee data in sample database. // The management has selected best projects based on the projects performance // in the current year and decided to give the employees of these projects a // performance bonus. The bonus will be a specific percentage of employee // salary. // // An array of varchar is used to store the selected project names. // // A stored procedure is implemented to calculate the bonus. The stored // procedure takes this array and percentage value as input. // // PREREQUISITE: Run the script bonus_calculate.db2 using the following // command // db2 -td@ -vf bonus_calculate.db2 // This script do the following // // 1. Create the ARRAY types. // 2. Create the table "bonus_temp". // 3. Create a stored procedure to calculate the bonus. // 3.1 Select the ID and corresponding bonus values in // corresponding ARRAY type "employees" and "bonus" respectively // using aggregate function ARRAY_AGG. // 3.2 Use UNNEST function to select the ARRAY elements from ARRAY // variables and insert the same in "bonus_temp" table. // // EXECUTION: javac Arrays_Sqlpl.java // java Arrays_Sqlpl // // INPUTS: NONE // // OUTPUT: The employee IDs and the corresponding bonus will be calculated and // stored in a table. An employee can work for multiple projects so multiple // entries are possible for the same employee id in this table. // // // // SQL STATEMENTS USED: // CREATE TABLE // SELECT // DROP // CALL // //***************************************************************************** // For more information about the command line processor (CLP) scripts, // see the README file. // For information on using SQL statements, see the SQL Reference. // // For the latest information on programming, building, and running DB2 // applications, visit the DB2 Information Center: // http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp // //***************************************************************************** // // SAMPLE DESCRIPTION // //***************************************************************************** // 1. Call the stored procedure to calculate the bonus. Input to this // stored procedure will be the ARRAY of all the projects which are // applicable for the bonus. // 2. Select the data from the table "bonus_temp". //***************************************************************************** import java.sql.*; public class Arrays_Sqlpl { public static void main(String argv[]) { String[] projects=new String[10]; String url = "jdbc:db2:sample"; int percentage=10; com.ibm.db2.jcc.DB2Connection con = null; try { // connect to the db Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); // connect to the 'sample' database con = (com.ibm.db2.jcc.DB2Connection) DriverManager.getConnection( url ); Statement stmt=con.createStatement(); //***************************************************************************** // // 1. Call the stored procedure to calculate the bonus. The input to this // stored procedure will be the ARRAY of all the projects which are // applicable for bonus. //***************************************************************************** // Prepare the call statement String sql = "CALL bonus_calculate(?, ?)"; CallableStatement callStmt = con.prepareCall(sql); // Create an SQL Array projects[0] = "AD3111"; projects[1] = "IF1000"; projects[2] = "MA2111"; java.sql.Array projectArray=con.createArrayOf("VARCHAR",projects); // set IN parameters callStmt.setArray(1, projectArray); callStmt.setInt(2,percentage); // call the procedure callStmt.execute(); //***************************************************************************** // // 2. Select the data from the table "bonus_temp". // //***************************************************************************** String selectStmt = "SELECT * FROM bonus_temp"; ResultSet rs = stmt.executeQuery(selectStmt); while(rs.next()) { System.out.println("Employee ID :"+rs.getString(1)); System.out.println("Bonus :"+rs.getDouble(2)); } // cleanup callStmt.close(); con.close(); } catch (Exception e) { try { con.rollback(); con.close(); } catch (Exception x) { } e.printStackTrace(); } } // end main } // end Arrays_Sqlpl