//*************************************************************************** // (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. //*************************************************************************** // // SOURCE FILE NAME: DbSeq.java // // SAMPLE: How to create, alter and drop a sequence in a database // // This sample demonstrates how to create, alter and drop a // sequence object. It also demonstrates how to use 'next value' // and 'previous value' with a sequence object. // // SQL STATEMENTS USED: // CREATE SEQUENCE // ALTER SEQUENCE // DROP SEQUENCE // INSERT // SELECT // COMMIT // ROLLBACK // // Classes used from Util.java are: // Db // JdbcException // // // Output will vary depending on the JDBC driver connectivity used. //*************************************************************************** // // For more information on the sample programs, see the README file. // // For information on developing Java applications see the Developing Java Applications book. // // For information on using SQL statements, see the SQL Reference. // // For the latest information on programming, compiling, and running DB2 // applications, visit the DB2 Information Center at // http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp //**************************************************************************/ import java.sql.*; import java.lang.*; class DbSeq { public static void main(String argv[]) { try { Db db = new Db(argv); System.out.println(); System.out.println( "THIS SAMPLE SHOWS HOW TO USE A SEQUENCE IN A DATABASE."); // connect to the 'sample' database db.connect(); createSequence(db.con); // The following code demonstrates how to GRANT the usage permission // on the sequence 'id_seq' to a user, Tom, from Bob. Comment out the // following and replace 'Tom' with the user you want to grant usage // permission to. // Statement grantstmt = con.createStatement(); // grantstmt.executeUpdate("GRANT USAGE ON SEQUENCE id_seq TO Tom"); // grantstmt.close(); // The following code demonstrates how to REVOKE the usage permission // on the sequence 'id_seq' from Tom by Bob. Comment out the // following, replace 'Bob' with your user name and 'Tom' with the // user you want to revoke usage permission from. // Statement revostmt = con.createStatement(); // revostmt.executeUpdate( // "REVOKE USAGE ON SEQUENCE id_seq FROM Tom BY Bob"); // revostmt.close(); nextValSeq(db.con); prevValSeq(db.con); dropSequence(db.con); // disconnect from the 'sample' database db.disconnect(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e); jdbcExc.handle(); } } //main // Helping function: This function is used to display the contents of // the tables created in this sample program. static void tbContentDisplay(Connection con, String tableName) { try { int empNo; String info = null; String column = "Name"; System.out.println("\n SELECT * FROM " + tableName); if (tableName.equalsIgnoreCase("emp_location")) { column="Location"; } System.out.println(" EmpNo " + column); System.out.println(" ----- ----------"); PreparedStatement pstmt = con.prepareStatement( "SELECT * FROM " + tableName); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { info = rs.getString(2); empNo = rs.getInt(1); System.out.println(" "+empNo + " " + info); } rs.close(); pstmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // tbContentDisplay // This function shows how to create a table and a sequence in a database. static void createSequence(Connection con) { try { System.out.println( "\n---------------------------------------------------\n"+ "USE THE SQL STATEMENT:\n" + " CREATE SEQUENCE\n" + "TO CREATE A SEQUENCE."); // Create a sequence object called 'id_seq' that generates the // employee's ID number. System.out.println(); System.out.println( " CREATE SEQUENCE id_seq\n" + " AS INTEGER\n" + " START WITH 400\n" + " INCREMENT BY 10\n" + " NO MINVALUE\n" + " MAXVALUE 430\n" + " NO CYCLE\n" + " NO CACHE"); Statement stmt = con.createStatement(); stmt.executeUpdate( "CREATE SEQUENCE id_seq AS INTEGER START WITH 400 " + "INCREMENT BY 10 NO MINVALUE MAXVALUE 430 NO CYCLE " + "NO CACHE"); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // createTbAndSeq // This function shows how to drop a table and a sequence object in a // database. static void dropSequence(Connection con) { try { System.out.println(); System.out.println( "------------------------------------------------\n"+ "USE THE SQL STATEMENT:\n" + " DROP SEQUENCE\n" + "TO DROP A SEQUENCE."); // drop a sequence object called 'id_seq' System.out.println(); System.out.println(" DROP SEQUENCE id_seq RESTRICT"); Statement drop = con.createStatement(); drop.executeUpdate("DROP SEQUENCE id_seq RESTRICT"); drop.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // dropTbAndSeq // This function shows how to alter a sequence object. static void alterSeq(Connection con) { try { System.out.println( "USE THE SQL STATEMENTS:\n" + " ALTER\n" + "TO ALTER A SEQUENCE"); // Alter the sequence to restart from 430 with a range of 400 to 430 // inclusively while incrementing by -10 (decrementing by 10) with // no maximum value. System.out.println(); System.out.println( " ALTER SEQUENCE id_seq\n" + " RESTART WITH 430\n" + " INCREMENT BY -10\n" + " MINVALUE 400\n" + " NO MAXVALUE\n" + " CYCLE\n" + " CACHE 10"); Statement altstmt = con.createStatement(); altstmt.executeUpdate( "ALTER SEQUENCE id_seq RESTART WITH 430 INCREMENT BY -10" + " MINVALUE 400 NO MAXVALUE CYCLE CACHE 10"); altstmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // alterSeq // This function shows how to use a sequence with 'NEXT VALUE' to insert // table data and generate a gap. static void nextValSeq(Connection con) { try { System.out.println(); System.out.println( "---------------------------------------------------\n"+ "USE THE SQL STATEMENTS:\n" + " INSERT\n" + "TO INSERT TABLE DATA USING A SEQUENCE WITH 'NEXT VALUE'"); // create a table called 'contract_emp' System.out.println(); System.out.println( " CREATE TABLE contract_emp(empNo INTEGER, name CHAR(10))"); Statement stmt = con.createStatement(); stmt.executeUpdate( "CREATE TABLE contract_emp(empNo INTEGER, name CHAR(10))"); stmt.close(); // insert table data using 'NEXT VALUE' System.out.println(); System.out.println( " INSERT INTO contract_emp\n" + " VALUES(NEXT VALUE FOR id_seq, 'shameem')\n"); Statement stmt1 = con.createStatement(); stmt1.executeUpdate( "INSERT INTO contract_emp VALUES(NEXT VALUE FOR id_seq, 'shameem')"); stmt1.close(); System.out.println(" COMMIT\n"); con.commit(); // display the content of the 'contract_emp' table tbContentDisplay(con, "contract_emp"); // insert table data using 'NEXT VALUE' System.out.println( " INSERT INTO contract_emp\n" + " VALUES(NEXT VALUE FOR id_seq, 'mohammed')\n"); Statement stmt2 = con.createStatement(); stmt2.executeUpdate( "INSERT INTO contract_emp VALUES(NEXT VALUE FOR id_seq, 'mohammed')"); stmt2.close(); // display the content of the 'contract_emp' table tbContentDisplay(con, "contract_emp"); System.out.println(" ROLLBACK\n"); con.rollback(); // display the content of the 'contract_emp' table tbContentDisplay(con, "contract_emp"); // insert table data using 'NEXT VALUE' System.out.println(); System.out.println( " INSERT INTO contract_emp\n" + " VALUES(NEXT VALUE FOR id_seq, 'sunny')\n"); Statement stmt3 = con.createStatement(); stmt3.executeUpdate( "INSERT INTO contract_emp VALUES(NEXT VALUE FOR id_seq,'sunny')"); stmt3.close(); // display the content of the 'contract_emp' table tbContentDisplay(con, "contract_emp"); System.out.println(); System.out.println( " Note:\n"+ " The new insertion has EmpNo 420. Note the gap in the\n"+ " EmpNo. This shows numbers generated by SEQUENCE are\n"+ " independent of the status of the transaction that\n"+ " generated the previous value in the sequence.\n"); System.out.println( " Altering the sequence to show overlap can be generated by\n"+ " a sequence object.\n"); alterSeq(con); // insert table data using 'NEXT VALUE' after altering the sequence System.out.println(); System.out.println( " INSERT INTO contract_emp\n" + " VALUES(NEXT VALUE FOR id_seq, 'saba')\n"+ " (NEXT VALUE FOR id_seq, 'repeat')"); Statement stmt4 = con.createStatement(); stmt4.executeUpdate( "INSERT INTO contract_emp VALUES(NEXT VALUE FOR " + "id_seq,'saba'), (NEXT VALUE FOR id_seq, 'repeat')"); stmt4.close(); // display the content of the 'contract_emp' table after altering the // sequence tbContentDisplay(con, "contract_emp"); System.out.println(); System.out.println( " Note:\n"+ " One of the new insertions has EmpNo 420 which\n" + " already exists. This happened because the new altered range\n" + " of sequence overlaps the old one. It is the responsibility\n" + " of the programmer to ensure that these overlaps do not\n" + " occur if they are not wanted.\n"); // drop the table 'emp_location' System.out.println(" DROP TABLE contract_emp"); Statement drop = con.createStatement(); drop.executeUpdate("DROP TABLE contract_emp"); drop.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // nextValSeq // This function shows how to use a sequence with 'PREVIOUS VALUE' to insert // the last generated sequence value into a table. static void prevValSeq(Connection con) { try { System.out.println(); System.out.println( "---------------------------------------------------\n"+ "USE THE SQL STATEMENTS:\n" + " INSERT INTO PREVIOUS VALUE\n" + "TO INSERT DATA INTO A TABLE USING A SEQUENCE WITH 'PREVIOUS VALUE'"); // create a table called 'emp_location' System.out.println(); System.out.println( " CREATE TABLE emp_location(empNo INTEGER, city CHAR(10))\n"); Statement stmt = con.createStatement(); stmt.executeUpdate( "CREATE TABLE emp_location(empNo INTEGER, city CHAR(10))"); stmt.close(); // insert table data using 'PREVIOUS VALUE' System.out.println( " INSERT INTO emp_location\n" + " VALUES(PREVIOUS VALUE FOR id_seq, 'repeat')"); Statement stmt1 = con.createStatement(); stmt1.executeUpdate( "INSERT INTO emp_location VALUES(PREVIOUS VALUE FOR id_seq, 'repeat')"); stmt1.close(); // display the content of the 'emp_location' table tbContentDisplay(con, "emp_location"); System.out.println(); System.out.println( " Note:\n"+ " By using SEQUENCE with 'PREVIOUS VALUE', you can insert\n"+ " into a different table with the last generated\n"+ " EmpNo.\n"); // drop the 'emp_location' table System.out.println(" DROP TABLE emp_location"); Statement drop = con.createStatement(); drop.executeUpdate("DROP TABLE emp_location"); drop.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // prevValSeq } // DbSeq