//*************************************************************************** // (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: LargeRid.java // // SAMPLE: How to enable Large RIDs support on both new tables/tablespaces // and existing tables/tablespaces. // // SQL Statements USED: // ALTER TABLESPACE // CREATE TABLE // CREATE TABLESPACE // DROP // INSERT // REORG // SELECT // // JAVA 2 CLASSES USED: // Statement // // Classes used from Util.java are: // Db // Data // JdbcException // // //*************************************************************************** // // 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.lang.*; import java.sql.*; class LargeRid { public static void main(String argv[]) { try { Db db = new Db(argv); System.out.println(); System.out.println( "THIS SAMPLE SHOWS HOW TO ENABLE LARGE RID SUPPORT ON TABLES AND\n" + " TABLESPACES\n"); // connect to the 'sample' database db.connect(); dmstspaceaceCreate(db.con); System.out.println ("\n************************************************************\n"); System.out.println ("THE FOLLOWING SCENARIO SHOWS HOW TO ENABLE LARGE RID SUPPORT"); System.out.println(" FOR A NON-PARTITIONED TABLE\n"); System.out.println ("************************************************************"); tbCreate(db.con); createIndex(db.con); tbAlterSpace(db.con); reorgIndex(db.con); indexDrop(db.con); System.out.println ("\n************************************************************\n"); System.out.println ("THE FOLLOWING SCENARIO SHOWS HOW TO ENABLE LARGE RID SUPPORT"); System.out.println (" FOR A PARTITIONED TABLE\n"); System.out.println ("************************************************************"); partitionedTbCreate(db.con); insertData(db.con); tbDetachPartition(db.con); convertTbSpace(db.con); tbReorganize(db.con); tbAttachPartition(db.con); tbDrop(db.con); tablespacesDrop(db.con); // disconnect from the 'sample' database db.disconnect(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e); jdbcExc.handle(); } } // main // Creates regular DMS tablespaces static void dmstspaceaceCreate(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " CREATE REGULAR TABLESPACE \n" + "TO CREATE A REGULAR TABLESPACE \n" + "\n Execute the statement:\n" + " CREATE REGULAR TABLESPACE dms_tspace\n" + " MANAGED BY DATABASE \n" + " USING (FILE 'dms_cont.dat' 1000)"); // create regular DMS table space dms_tspace Statement stmt = con.createStatement(); String str = ""; str = "CREATE REGULAR TABLESPACE dms_tspace"; str = str + " MANAGED BY DATABASE USING (FILE 'dms_cont.dat' 10000)"; stmt.executeUpdate(str); System.out.println( "\nUSE THE SQL STATEMENT:\n" + " CREATE REGULAR TABLESPACE \n" + "TO CREATE A REGULAR TABLESPACE \n" + "\n Execute the statement:\n" + " CREATE REGULAR TABLESPACE dms_tspace1\n" + " MANAGED BY DATABASE \n" + " USING (FILE 'dms_cont1.dat' 1000)"); // create regular DMS table space dms_tspace1 str = "CREATE REGULAR TABLESPACE dms_tspace1" + " MANAGED BY DATABASE USING (FILE 'dms_cont1.dat' 10000)"; stmt.executeUpdate(str); System.out.println( "\nUSE THE SQL STATEMENT:\n" + " CREATE REGULAR TABLESPACE \n" + "TO CREATE A REGULAR TABLESPACE \n" + "\n Execute the statement:\n" + " CREATE REGULAR TABLESPACE dms_tspace2\n" + " MANAGED BY DATABASE \n" + " USING (FILE 'dms_cont2.dat' 1000)"); // create regular DMS table space dms_tspace2 str = "CREATE REGULAR TABLESPACE dms_tspace2" + " MANAGED BY DATABASE USING (FILE 'dms_cont2.dat' 10000)"; stmt.executeUpdate(str); System.out.println( "\nUSE THE SQL STATEMENT:\n" + " CREATE REGULAR TABLESPACE \n" + "TO CREATE A REGULAR TABLESPACE \n" + "\n Execute the statement:\n" + " CREATE REGULAR TABLESPACE dms_tspace3\n" + " MANAGED BY DATABASE \n" + " USING (FILE 'dms_cont3.dat' 1000)"); // create regular DMS table space dms_tspace3 str = "CREATE REGULAR TABLESPACE dms_tspace3" + " MANAGED BY DATABASE USING (FILE 'dms_cont3.dat' 10000)"; stmt.executeUpdate(str); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // dmstspaceaceCreate // Creates a non-partitioned table. static void tbCreate(Connection con) throws SQLException { try { System.out.println( "\nUSE THE SQL STATEMENT:\n" + " CREATE TABLE \n" + "TO CREATE A TABLE \n" + "\n Execute the statement:\n" + " CREATE TABLE large (max INT, min INT) IN dms_tspace"); // create table in 'dms_tspace' regular DMS tablespace Statement stmt = con.createStatement(); String str = ""; str = "CREATE TABLE large (max INT, min INT)" + " IN dms_tspace"; stmt.executeUpdate(str); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // tbCreate // Creates index on a table. static void createIndex(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " CREATE INDEX \n" + "TO CREATE AN INDEX \n" + "\n Execute the statement:\n" + " CREATE INDEX large_ind ON large (max)"); // create index Statement stmt = con.createStatement(); stmt.executeUpdate("CREATE INDEX large_ind ON large (max)"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // createIndex // Changes table space from regular to large. static void tbAlterSpace(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " ALTER TABLESPACE \n" + "TO ALTER A TABLESPACE \n" + "\n Execute the statement:\n" + " ALTER TABLESPACE dms_tspace CONVERT TO LARGE"); // convert regular DMS tablespace 'dms_tspace' to large DMS tablespace Statement stmt = con.createStatement(); stmt.executeUpdate("ALTER TABLESPACE dms_tspace CONVERT TO LARGE"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // tbAlterSpace // Reorganize indexes defined on a table. static void reorgIndex(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " REORG INDEXES \n" + "TO REORG INDEXES FOR A TABLE \n" + "\n Execute the statement:\n" + " REORG INDEXES ALL FOR TABLE large"); String sql = "CALL SYSPROC.ADMIN_CMD(?)"; CallableStatement callStmt1 = con.prepareCall(sql); String param = "REORG INDEXES ALL FOR TABLE large" ; // set the input parameter callStmt1.setString(1, param); // execute import by calling ADMIN_CMD callStmt1.execute(); con.commit(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // reorgIndex // Drop indexes defined on a table. static void indexDrop(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " DROP INDEX \n" + "TO DROP AN INDEX \n" + "\n Execute the statement:\n" + " DROP INDEX large_ind"); // drop the index Statement stmt = con.createStatement(); stmt.executeUpdate("DROP INDEX large_ind"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // indexDrop // Creates a partitioned table with 'part1' in 'dms_tspace1', 'part2' // in 'dms_tspace2', and 'part3' in 'dms_tspace3'. static void partitionedTbCreate(Connection con) throws SQLException { try { System.out.println( "\nUSE THE SQL STATEMENT:\n" + " CREATE TABLE \n" + "TO CREATE A TABLE \n" + "\n Execute the statement:\n" + " CREATE TABLE large_ptab (max SMALLINT NOT NULL,\n" + " CONSTRAINT CC CHECK (max>0))\n" + " PARTITION BY RANGE (max)\n "+ " (PART part1 STARTING FROM (1) ENDING (3) IN dms_tspace1,\n" + " PART part2 STARTING FROM (4) ENDING (6) IN dms_tspace2,\n" + " PART part3 STARTING FROM (7) ENDING (9) IN dms_tspace3)"); // create a partitioned table in regular DMS tablespaces i.e; part1 is // placed at dms_tspace1, part2 is placed at dms_tspace2 and // part3 at dms_tspace3. Statement stmt = con.createStatement(); String str = ""; str = str + "CREATE TABLE large_ptab " + "(max SMALLINT NOT NULL, CONSTRAINT CC CHECK (max>0))" + " PARTITION BY RANGE (max) " + "(PART part1 STARTING FROM (1) ENDING (3) " + "IN dms_tspace1, PART part2 STARTING FROM (4) ENDING (6) " + "IN dms_tspace2, PART part3 STARTING FROM (7) ENDING (9) " + "IN dms_tspace3)"; stmt.executeUpdate(str); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // partitionedTbCreate // Insert data into the table. static void insertData(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " INSERT INTO \n" + "TO INSERT DATA IN A TABLE \n" + "\n Execute the statement:\n" + " INSERT INTO large_ptab VALUES (1), (2), (3),\n" + " (4), (5), (6),\n" + " (7), (8), (9)"); // insert data into the table Statement stmt = con.createStatement(); String str = ""; str = "INSERT INTO large_ptab VALUES (1), (2), (3), (4)," + " (5), (6), (7), (8), (9)"; stmt.executeUpdate(str); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // insertData // If a partitioned table has data partitions in different regular DMS // tablespaces, then the tablespaces cannot be converted to large // with the current definition. // To do this, first detach all the partitions of the table, later // convert all the tablespaces to large, reorg all the detached // partitions to support large RID. Finally, reattach the partitions. // Now the entire table supports large RIDs. // Remove partition from a partitioned table. static void tbDetachPartition(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " ALTER TABLE \n" + "TO DETACH THE PARTITIONS \n" + "\n Execute the statements:\n" + " ALTER TABLE large_ptab\n" + " DETACH PARTITION PART3\n" + " INTO TABLE detach_part3\n\n" + " ALTER TABLE large_ptab\n" + " DETACH PARTITION PART3\n" + " INTO TABLE detach_part2"); // detach partitions from base table into some temporary tables Statement stmt = con.createStatement(); String str =""; str = "ALTER TABLE large_ptab DETACH PARTITION part3 " + "INTO TABLE detach_part3"; stmt.executeUpdate(str); str = "ALTER TABLE large_ptab DETACH PARTITION part2 " + "INTO TABLE detach_part2"; stmt.executeUpdate(str); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // tbDetachPartition // Changes table space from regular to large. static void convertTbSpace(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " ALTER TABLE \n" + "TO DETACH THE PARTITIONS \n" + "\n Execute the statements:\n" + " ALTER TABLESPACE dms_tspace1 CONVERT TO LARGE\n" + " ALTER TABLESPACE dms_tspace2 CONVERT TO LARGE\n" + " ALTER TABLESPACE dms_tspace3 CONVERT TO LARGE"); // convert regular DMS tablespaces to large DMS tablespaces Statement stmt = con.createStatement(); stmt.executeUpdate("ALTER TABLESPACE dms_tspace1 CONVERT TO LARGE"); stmt.executeUpdate("ALTER TABLESPACE dms_tspace2 CONVERT TO LARGE"); stmt.executeUpdate("ALTER TABLESPACE dms_tspace3 CONVERT TO LARGE"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // convertTbSpace // Reorganize table. static void tbReorganize(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " REORG TABLE \n" + "TO REORG THE DETACHED PARTITIONS \n" + "\n Execute the statements:\n" + " REORG TABLE large_ptab ALLOW NO ACCESS\n" + " REORG TABLE detach_part2 ALLOW NO ACCESS\n" + " REORG TABLE detach_part3 ALLOW NO ACCESS\n"); String sql = "CALL SYSPROC.ADMIN_CMD(?)"; CallableStatement callStmt1 = con.prepareCall(sql); String param1 = "REORG TABLE large_ptab ALLOW NO ACCESS"; String param2 = "REORG TABLE detach_part2 ALLOW NO ACCESS"; String param3 = "REORG TABLE detach_part3 ALLOW NO ACCESS"; // set the input parameter callStmt1.setString(1, param1); // execute reorg by calling ADMIN_CMD callStmt1.execute(); // set the input parameter callStmt1.setString(1, param2); // execute reorg by calling ADMIN_CMD callStmt1.execute(); // set the input parameter callStmt1.setString(1, param3); // execute reorg by calling ADMIN_CMD callStmt1.execute(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // tbReorganize // Add partition to a partitioned table. static void tbAttachPartition(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " REORG TABLE \n" + "TO REORG THE DETACHED PARTITIONS \n" + "\n Execute the statements:\n" + " ALTER TABLE large_ptab\n" + " ATTACH PARTITION part2\n" + " STARTING FROM (4) ENDING (6)\n" + " FROM TABLE detach_part2\n\n" + " ALTER TABLE large_ptab\n" + " ATTACH PARTITION part2\n" + " STARTING FROM (7) ENDING (9)\n" + " FROM TABLE detach_part3"); // reattach the reorganized detached partitions for table to support // large RIDs. Statement stmt = con.createStatement(); String str = ""; str = "ALTER TABLE large_ptab ATTACH PARTITION part2" + " STARTING FROM (4) ENDING (6)" + " FROM TABLE detach_part2"; stmt.executeUpdate(str); str = "ALTER TABLE large_ptab ATTACH PARTITION part3"; str = str + " STARTING FROM (7) ENDING (9)"; str = str + " FROM TABLE detach_part3"; stmt.executeUpdate(str); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // tbAttachPartition // Drop tables. static void tbDrop(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " DROP \n" + "TO DROP THE TABLES \n" + "\n Execute the statements:\n" + " DROP TABLE large\n" + " DROP TABLE large_ptab"); // drop the tables Statement stmt = con.createStatement(); stmt.executeUpdate("DROP TABLE large"); stmt.executeUpdate("DROP TABLE large_ptab"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // tbDrop // Drop tablespaces. static void tablespacesDrop(Connection con) throws SQLException { try { System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT:\n" + " DROP \n" + "TO DROP THE TABLESPACES \n" + "\n Execute the statements:\n" + " DROP TABLESPACE dms_tspace\n" + " DROP TABLESPACE dms_tspace1\n" + " DROP TABLESPACE dms_tspace2\n" + " DROP TABLESPACE dms_tspace3"); // drop the tablespaces Statement stmt = con.createStatement(); stmt.executeUpdate("DROP TABLESPACE dms_tspace"); stmt.executeUpdate("DROP TABLESPACE dms_tspace1"); stmt.executeUpdate("DROP TABLESPACE dms_tspace2"); stmt.executeUpdate("DROP TABLESPACE dms_tspace3"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } } // tablespacesDrop } // LargeRid