//************************************************************************** // (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: TbCompress.java // // SAMPLE: How to create tables with null and default value compression // option. // // SQL STATEMENTS USED: // CREATE TABLE // ALTER TABLE // DROP TABLE // // JAVA 2 CLASSES USED: // Statement // // 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 application development website at // http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp //************************************************************************** import java.lang.*; import java.sql.*; class TbCompress { public static void main(String argv[]) { try { Db db = new Db(argv); System.out.println(); System.out.println( "THIS SAMPLE SHOWS HOW TO USE NULL AND DEFAULT VALUE\n" + "COMPRESSION OPTION AT TABLE LEVEL AND COLUMN LEVEL \n"); // connect to database db.connect(); // create a new table tbCreate(db.con); // activate null and default value compression tbCompress(db.con); // drop the table created tbDrop(db.con); // disconnect from 'sample' database db.disconnect(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e) ; jdbcExc.handle(); } } // main // create a new table static void tbCreate(Connection con) { try { Statement stmt = con.createStatement(); // create base table System.out.println( "\n-----------------------------------------------------------\n" + "USE THE SQL STATEMENT \n" + " CREATE TABLE \n" + "TO CREATE A TABLE \n\n" + " CREATE TABLE comp_tab(col1 INT NOT NULL WITH DEFAULT,\n" + " col2 CHAR(7),\n" + " col3 VARCHAR(7) NOT NULL,\n" + " col4 DOUBLE) \n"); stmt.executeUpdate( "CREATE TABLE comp_tab(col1 INT NOT NULL WITH DEFAULT," + " col2 CHAR(7)," + " col3 VARCHAR(7) NOT NULL," + " col4 DOUBLE)"); System.out.println(" COMMIT"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e); jdbcExc.handle(); } } // tbCreate // activate null and default value compression static void tbCompress(Connection con) { try { Statement stmt = con.createStatement(); System.out.println( "\n-----------------------------------------------------------\n" + "USE THE SQL STATEMENT \n" + " ALTER TABLE \n" + "TO ALTER COMPRESSION OPTIONS OF THE TABLE\n\n" + "To activate VALUE COMPRESSION at table level and COMPRESS \n" + "SYSTEM DEFAULT at column level \n\n" + " ALTER TABLE comp_tab ACTIVATE VALUE COMPRESSION \n\n" + "Rows will be formatted using the new row format on subsequent\n" + "insert, load and update operation, and NULL values will not be\n" + "taking up space if applicable.\n"); // if the table comp_tab does not have many NULL values, enabling // compression will result in using more disk space than using // the old row format stmt.executeUpdate("ALTER TABLE comp_tab ACTIVATE VALUE COMPRESSION"); con.commit(); System.out.println( "\nTo save more disk space on system default value for column\n" + "col1, enter\n" + "\n ALTER TABLE comp_tab ALTER col1 COMPRESS SYSTEM DEFAULT\n" + "\nOn subsequent insert, load, and update operations, numerical\n" + "0 value (occupying 4 bytes of storage) for column col1 will\n" + "not be saved on disk.\n"); stmt.executeUpdate("ALTER TABLE comp_tab "+ " ALTER col1 COMPRESS SYSTEM DEFAULT"); con.commit(); System.out.println( "\nTo switch the table to use the old format, enter\n\n" + " ALTER TABLE comp_tab DEACTIVATE VALUE COMPRESSION\n\n" + "Rows inserted, loaded or updated after the ALTER statement\n" + "will have old row format."); stmt.executeUpdate( "ALTER TABLE comp_tab " + " DEACTIVATE VALUE COMPRESSION"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e) ; jdbcExc.handle(); } } // tbCompress // drop the table created static void tbDrop(Connection con) { try { Statement stmt = con.createStatement(); // drop the table System.out.println( "\n-----------------------------------------------------------" + "\nUSE THE SQL STATEMENT\n" + " DROP TABLE\n" + "TO DROP THE TABLE\n\n" + " DROP TABLE comp_tab\n"); stmt.executeUpdate("DROP TABLE comp_tab"); System.out.println("\n COMMIT"); con.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e) ; jdbcExc.handle(); } } // tbDrop } // TbCompress