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