| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.harness.procedure |
| |
| Licensed to the Apache Software Foundation (ASF) under one or more |
| contributor license agreements. See the NOTICE file distributed with |
| this work for additional information regarding copyright ownership. |
| The ASF licenses this file to You under the Apache License, Version 2.0 |
| (the "License"); you may not use this file except in compliance with |
| the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| |
| */ |
| |
| package org.apache.derbyTesting.functionTests.tests.store; |
| |
| import org.apache.derby.shared.common.sanity.SanityManager; |
| |
| import java.sql.CallableStatement; |
| import java.sql.Connection; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| |
| import org.apache.derby.tools.ij; |
| |
| |
| public class OnlineCompressTest extends BaseTest |
| { |
| boolean verbose = false; |
| |
| public OnlineCompressTest() |
| { |
| } |
| |
| /** |
| * call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE() system procedure. |
| * <p> |
| * Utility test function to call the system procedure. |
| * |
| **/ |
| protected void callCompress( |
| Connection conn, |
| String schemaName, |
| String tableName, |
| boolean purgeRows, |
| boolean defragmentRows, |
| boolean truncateEnd, |
| boolean commit_operation) |
| throws SQLException |
| { |
| CallableStatement cstmt = |
| conn.prepareCall( |
| "call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?, ?, ?, ?, ?)"); |
| cstmt.setString(1, schemaName); |
| cstmt.setString(2, tableName); |
| cstmt.setInt (3, purgeRows ? 1 : 0); |
| cstmt.setInt (4, defragmentRows ? 1 : 0); |
| cstmt.setInt (5, truncateEnd ? 1 : 0); |
| |
| cstmt.execute(); |
| |
| if (commit_operation) |
| conn.commit(); |
| } |
| |
| /** |
| * Create and load a table. |
| * <p> |
| * If create_table is set creates a test data table with indexes. |
| * Loads num_rows into the table. This table defaults to 32k page size. |
| * This schema fits 25 rows per page |
| * <p> |
| * |
| * |
| * @param conn Connection to use for sql execution. |
| * @param create_table If true, create new table - otherwise load into |
| * existing table. |
| * @param tblname table to use. |
| * @param num_rows number of rows to add to the table. |
| * |
| * @exception StandardException Standard exception policy. |
| **/ |
| protected void createAndLoadTable( |
| Connection conn, |
| boolean create_table, |
| String tblname, |
| int num_rows, |
| int start_value) |
| throws SQLException |
| { |
| if (create_table) |
| { |
| Statement s = conn.createStatement(); |
| |
| s.execute( |
| "create table " + tblname + |
| "(keycol int, indcol1 int, indcol2 int, indcol3 int, data1 varchar(2000), data2 varchar(2000))"); |
| s.close(); |
| } |
| |
| PreparedStatement insert_stmt = |
| conn.prepareStatement( |
| "insert into " + tblname + " values(?, ?, ?, ?, ?, ?)"); |
| |
| char[] data1_data = new char[500]; |
| char[] data2_data = new char[500]; |
| |
| for (int i = 0; i < data1_data.length; i++) |
| { |
| data1_data[i] = 'a'; |
| data2_data[i] = 'b'; |
| } |
| |
| String data1_str = new String(data1_data); |
| String data2_str = new String(data2_data); |
| |
| int row_count = 0; |
| try |
| { |
| for (int i = start_value; row_count < num_rows; row_count++, i++) |
| { |
| insert_stmt.setInt(1, i); // keycol |
| insert_stmt.setInt(2, i * 10); // indcol1 |
| insert_stmt.setInt(3, i * 100); // indcol2 |
| insert_stmt.setInt(4, -i); // indcol3 |
| insert_stmt.setString(5, data1_str); // data1_data |
| insert_stmt.setString(6, data2_str); // data2_data |
| |
| insert_stmt.execute(); |
| } |
| } |
| catch (SQLException sqle) |
| { |
| System.out.println( |
| "Exception while trying to insert row number: " + row_count); |
| throw sqle; |
| } |
| |
| if (create_table) |
| { |
| Statement s = conn.createStatement(); |
| |
| s.execute( |
| "create index " + tblname + "_idx_keycol on " + tblname + |
| "(keycol)"); |
| s.execute( |
| "create index " + tblname + "_idx_indcol1 on " + tblname + |
| "(indcol1)"); |
| s.execute( |
| "create index " + tblname + "_idx_indcol2 on " + tblname + |
| "(indcol2)"); |
| s.execute( |
| "create unique index " + tblname + "_idx_indcol3 on " + tblname + |
| "(indcol3)"); |
| s.close(); |
| } |
| |
| conn.commit(); |
| } |
| |
| /** |
| * Create and load a table with large columns. |
| * <p> |
| * If create_table is set creates a test data table with indexes. |
| * Loads num_rows into the table. This table defaults to 32k page size. |
| * <p> |
| * |
| * |
| * @param conn Connection to use for sql execution. |
| * @param create_table If true, create new table - otherwise load into |
| * existing table. |
| * @param tblname table to use. |
| * @param num_rows number of rows to add to the table. |
| * @param start_value Starting number from which num_rows are inserted |
| * @exception StandardException Standard exception policy. |
| **/ |
| protected void createAndLoadLargeTable( |
| Connection conn, |
| boolean create_table, |
| String tblname, |
| int num_rows, |
| int start_value) |
| throws SQLException |
| { |
| if (create_table) |
| { |
| Statement s = conn.createStatement(); |
| |
| // Derby-606. Note that this table is currently only used by Test6. |
| // Test6 needs data be to spread over 2 AllocExtents |
| // and this table schema is chosen so that the required scenario |
| // is exposed in minimum test execution time. |
| s.execute( |
| "create table " + tblname + |
| "(keycol int, indcol1 int, indcol2 int, data1 char(24), data2 char(24), data3 char(24)," + |
| "data4 char(24), data5 char(24), data6 char(24), data7 char(24), data8 char(24)," + |
| "data9 char(24), data10 char(24), inddec1 decimal(8), indcol3 int, indcol4 int, data11 varchar(50))"); |
| s.close(); |
| } |
| |
| PreparedStatement insert_stmt = |
| conn.prepareStatement( |
| "insert into " + tblname + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); |
| |
| char[] data1_data = new char[24]; |
| char[] data2_data = new char[24]; |
| char[] data3_data = new char[24]; |
| char[] data4_data = new char[24]; |
| char[] data5_data = new char[24]; |
| char[] data6_data = new char[24]; |
| char[] data7_data = new char[24]; |
| char[] data8_data = new char[24]; |
| char[] data9_data = new char[24]; |
| char[] data10_data = new char[24]; |
| char[] data11_data = new char[50]; |
| |
| for (int i = 0; i < data1_data.length; i++) |
| { |
| data1_data[i] = 'a'; |
| data2_data[i] = 'b'; |
| data3_data[i] = 'c'; |
| data4_data[i] = 'd'; |
| data5_data[i] = 'e'; |
| data6_data[i] = 'f'; |
| data7_data[i] = 'g'; |
| data8_data[i] = 'h'; |
| data9_data[i] = 'i'; |
| data10_data[i] = 'j'; |
| } |
| for( int i=0; i < data11_data.length; i++) |
| { |
| data11_data[i] = 'z'; |
| } |
| |
| String data1_str = new String(data1_data); |
| String data2_str = new String(data2_data); |
| String data3_str = new String(data3_data); |
| String data4_str = new String(data4_data); |
| String data5_str = new String(data5_data); |
| String data6_str = new String(data6_data); |
| String data7_str = new String(data7_data); |
| String data8_str = new String(data8_data); |
| String data9_str = new String(data9_data); |
| String data10_str = new String(data10_data); |
| String data11_str = new String(data11_data); |
| |
| int row_count = 0; |
| try |
| { |
| for (int i = start_value; row_count < num_rows; row_count++, i++) |
| { |
| insert_stmt.setInt(1, i); // keycol |
| insert_stmt.setInt(2, i * 10); // indcol1 |
| insert_stmt.setInt(3, i * 100); // indcol2 |
| insert_stmt.setString(4, data1_str); // data1_data |
| insert_stmt.setString(5, data2_str); // data2_data |
| insert_stmt.setString(6, data3_str); // data3_data |
| insert_stmt.setString(7, data4_str); // data4_data |
| insert_stmt.setString(8, data5_str); // data5_data |
| insert_stmt.setString(9, data6_str); // data6_data |
| insert_stmt.setString(10, data7_str); // data7_data |
| insert_stmt.setString(11, data8_str); // data8_data |
| insert_stmt.setString(12, data9_str); // data9_data |
| insert_stmt.setString(13, data10_str); // data10_data |
| insert_stmt.setInt(14, i * 20); // indcol3 |
| insert_stmt.setInt(15, i * 200); // indcol4 |
| insert_stmt.setInt(16, i * 50); |
| insert_stmt.setString(17, data11_str); // data11_data |
| |
| insert_stmt.execute(); |
| } |
| } |
| catch (SQLException sqle) |
| { |
| System.out.println( |
| "Exception while trying to insert row number: " + row_count); |
| throw sqle; |
| } |
| |
| if (create_table) |
| { |
| Statement s = conn.createStatement(); |
| |
| s.execute( |
| "create index " + tblname + "_idx_keycol on " + tblname + |
| "(keycol)"); |
| s.execute( |
| "create index " + tblname + "_idx_indcol1 on " + tblname + |
| "(indcol1)"); |
| s.execute( |
| "create index " + tblname + "_idx_indcol2 on " + tblname + |
| "(indcol2)"); |
| s.execute( |
| "create unique index " + tblname + "_idx_indcol3 on " + tblname + |
| "(indcol3)"); |
| s.close(); |
| } |
| |
| conn.commit(); |
| } |
| |
| /** |
| * Create and load a table with long columns and long rows. |
| * <p> |
| * If create_table is set creates a test data table with indexes. |
| * Loads num_rows into the table. This table defaults to 32k page size. |
| * <p> |
| * schema of table: |
| * keycol int, |
| * longcol1 clob(200k), |
| * longrow1 varchar(10000), |
| * longrow2 varchar(10000), |
| * longrow3 varchar(10000), |
| * longrow4 varchar(10000), |
| * indcol1 int, |
| * indcol2 int, |
| * indcol3 int, |
| * data1 varchar(2000), |
| * data2 varchar(2000) |
| * longrow5 varchar(10000), |
| * longrow6 varchar(10000), |
| * longrow7 varchar(10000), |
| * longrow8 varchar(10000), |
| * longcol2 clob(200k), |
| * |
| * |
| * @param conn Connection to use for sql execution. |
| * @param create_table If true, create new table - otherwise load into |
| * existing table. |
| * @param tblname table to use. |
| * @param num_rows number of rows to add to the table. |
| * |
| * @exception StandardException Standard exception policy. |
| **/ |
| private void createAndLoadLongTable( |
| Connection conn, |
| boolean create_table, |
| String tblname, |
| int num_rows) |
| throws SQLException |
| { |
| if (create_table) |
| { |
| Statement s = conn.createStatement(); |
| |
| s.execute( |
| "create table " + tblname + |
| " (keycol int, longcol1 clob(200k), longrow1 varchar(10000), longrow2 varchar(10000), longrow3 varchar(10000), longrow4 varchar(10000), indcol1 int, indcol2 int, indcol3 int, data1 varchar(2000), data2 varchar(2000), longrow5 varchar(10000), longrow6 varchar(10000), longrow7 varchar(10000), longrow8 varchar(10000), longcol2 clob(200k))"); |
| s.close(); |
| } |
| |
| PreparedStatement insert_stmt = |
| conn.prepareStatement( |
| "insert into " + tblname + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); |
| |
| char[] data1_data = new char[500]; |
| char[] data2_data = new char[500]; |
| |
| for (int i = 0; i < data1_data.length; i++) |
| { |
| data1_data[i] = 'a'; |
| data2_data[i] = 'b'; |
| } |
| String data1_str = new String(data1_data); |
| String data2_str = new String(data2_data); |
| |
| // some data to force row to be bigger than a page, ie. long row |
| char[] data3_data = new char[10000]; |
| char[] data4_data = new char[10000]; |
| |
| for (int i = 0; i < data3_data.length; i++) |
| { |
| data3_data[i] = 'c'; |
| data4_data[i] = 'd'; |
| } |
| String data3_str = new String(data3_data); |
| String data4_str = new String(data4_data); |
| |
| // some data for the long columns |
| char[] data5_data = new char[200000]; |
| char[] data6_data = new char[200000]; |
| |
| for (int i = 0; i < data5_data.length; i++) |
| { |
| data5_data[i] = 'e'; |
| data6_data[i] = 'f'; |
| } |
| |
| String data5_str = new String(data5_data); |
| String data6_str = new String(data6_data); |
| |
| for (int i = 0; i < num_rows; i++) |
| { |
| insert_stmt.setInt(1, i); // keycol |
| insert_stmt.setString(2, data5_str); // longcol1 |
| insert_stmt.setString(3, data3_str); // longrow1 |
| insert_stmt.setString(4, data3_str); // longrow2 |
| insert_stmt.setString(5, data3_str); // longrow3 |
| insert_stmt.setString(6, data3_str); // longrow4 |
| insert_stmt.setInt(7, i * 10); // indcol1 |
| insert_stmt.setInt(8, i * 100); // indcol2 |
| insert_stmt.setInt(9, -i); // indcol3 |
| insert_stmt.setString(10, data1_str); // data1_data |
| insert_stmt.setString(11, data2_str); // data2_data |
| insert_stmt.setString(12, data4_str); // longrow5 |
| insert_stmt.setString(13, data4_str); // longrow6 |
| insert_stmt.setString(14, data4_str); // longrow7 |
| insert_stmt.setString(15, data4_str); // longrow8 |
| insert_stmt.setString(16, data5_str); // longcol2 |
| |
| insert_stmt.execute(); |
| } |
| |
| if (create_table) |
| { |
| Statement s = conn.createStatement(); |
| |
| s.execute( |
| "create index " + tblname + "_idx_keycol on " + tblname + |
| "(keycol)"); |
| s.execute( |
| "create index " + tblname + "_idx_indcol1 on " + tblname + |
| "(indcol1)"); |
| s.execute( |
| "create index " + tblname + "_idx_indcol2 on " + tblname + |
| "(indcol2)"); |
| s.execute( |
| "create unique index " + tblname + "_idx_indcol3 on " + tblname + |
| "(indcol3)"); |
| s.close(); |
| } |
| |
| conn.commit(); |
| } |
| |
| private void log_wrong_count( |
| String error_msg, |
| String table_name, |
| int num_rows, |
| int expected_val, |
| int actual_val, |
| int[] before_info, |
| int[] after_info) |
| { |
| System.out.println(error_msg); |
| System.out.println("ERROR: for " + num_rows + " row test. Expected " + expected_val + ", but got " + actual_val ); |
| System.out.println("before_info:"); |
| System.out.println( |
| " IS_INDEX =" + before_info[SPACE_INFO_IS_INDEX] + |
| "\n NUM_ALLOC =" + before_info[SPACE_INFO_NUM_ALLOC] + |
| "\n NUM_FREE =" + before_info[SPACE_INFO_NUM_FREE] + |
| "\n NUM_UNFILLED =" + before_info[SPACE_INFO_NUM_UNFILLED] + |
| "\n PAGE_SIZE =" + before_info[SPACE_INFO_PAGE_SIZE] + |
| "\n ESTIMSPACESAVING =" + before_info[SPACE_INFO_ESTIMSPACESAVING]); |
| System.out.println("after_info:"); |
| System.out.println( |
| " IS_INDEX =" + after_info[SPACE_INFO_IS_INDEX] + |
| "\n NUM_ALLOC =" + after_info[SPACE_INFO_NUM_ALLOC] + |
| "\n NUM_FREE =" + after_info[SPACE_INFO_NUM_FREE] + |
| "\n NUM_UNFILLED =" + after_info[SPACE_INFO_NUM_UNFILLED] + |
| "\n PAGE_SIZE =" + after_info[SPACE_INFO_PAGE_SIZE] + |
| "\n ESTIMSPACESAVING =" + after_info[SPACE_INFO_ESTIMSPACESAVING]); |
| } |
| |
| |
| private void deleteAllRows( |
| Connection conn, |
| boolean create_table, |
| boolean long_table, |
| String schemaName, |
| String table_name, |
| int num_rows) |
| throws SQLException |
| { |
| testProgress( |
| "begin deleteAllRows," + num_rows + " row test, create = " + |
| create_table + "."); |
| |
| |
| if (long_table) |
| createAndLoadLongTable(conn, create_table, table_name, num_rows); |
| else |
| createAndLoadTable(conn, create_table, table_name, num_rows, 0); |
| |
| if (verbose) |
| testProgress("Calling compress."); |
| |
| // compress with no deletes should not affect size |
| int[] ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| int[] ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) |
| { |
| log_wrong_count( |
| "Expected no alloc page change.", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_ALLOC], |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (verbose) |
| testProgress("calling consistency checker."); |
| |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| testProgress("no delete case complete."); |
| |
| // delete all the rows. |
| ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| executeQuery(conn, "delete from " + table_name, true); |
| |
| callWaitForPostCommit(conn); |
| |
| if (verbose) |
| testProgress("deleted all rows, now calling compress."); |
| |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| // An empty table has 2 pages, one allocation page and the 1st page |
| // which will have a system row in it. The space vti only reports |
| // a count of the user pages so the count is 1. |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != 1) |
| { |
| log_wrong_count( |
| "Expected all pages to be truncated.", |
| table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (verbose) |
| testProgress("calling consistency checker."); |
| |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| testProgress("delete all rows case succeeded."); |
| |
| conn.commit(); |
| |
| testProgress("end deleteAllRows," + num_rows + " row test."); |
| } |
| |
| private void simpleDeleteAllRows( |
| Connection conn, |
| boolean create_table, |
| boolean long_table, |
| String schemaName, |
| String table_name, |
| int num_rows) |
| throws SQLException |
| { |
| testProgress( |
| "begin simpleDeleteAllRows," + num_rows + " row test, create = " + |
| create_table + "."); |
| |
| |
| if (long_table) |
| createAndLoadLongTable(conn, create_table, table_name, num_rows); |
| else |
| createAndLoadTable(conn, create_table, table_name, num_rows, 0); |
| |
| if (verbose) |
| testProgress("Calling compress."); |
| |
| // compress with no deletes should not affect size |
| int[] ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| int[] ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) |
| { |
| log_wrong_count( |
| "Expected no alloc page change.", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_ALLOC], |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| testProgress("no delete case complete."); |
| |
| // delete all the rows. |
| ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| executeQuery(conn, "delete from " + table_name, true); |
| |
| callWaitForPostCommit(conn); |
| |
| if (verbose) |
| testProgress("deleted all rows, now calling compress."); |
| |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| // An empty table has 2 pages, one allocation page and the 1st page |
| // which will have a system row in it. The space vti only reports |
| // a count of the user pages so the count is 1. |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != 1) |
| { |
| log_wrong_count( |
| "Expected all pages to be truncated.", |
| table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| testProgress("delete all rows case succeeded."); |
| |
| conn.commit(); |
| |
| testProgress("end simple deleteAllRows," + num_rows + " row test."); |
| } |
| |
| /** |
| * Check/exercise purge pass phase. |
| * <p> |
| * Assumes that either test creates the table, or called on an empty |
| * table with no committed deleted rows or free pages in the middle of |
| * the table in it. |
| * <p> |
| * |
| * @exception StandardException Standard exception policy. |
| **/ |
| private void checkPurgePhase( |
| Connection conn, |
| boolean create_table, |
| boolean long_table, |
| String schemaName, |
| String table_name, |
| int num_rows) |
| throws SQLException |
| { |
| testProgress( |
| "begin checkPurgePhase" + num_rows + " row test, create = " + |
| create_table + "."); |
| |
| if (long_table) |
| createAndLoadLongTable(conn, create_table, table_name, num_rows); |
| else |
| createAndLoadTable(conn, create_table, table_name, num_rows, 0); |
| |
| // dump_table(conn, schemaName, table_name, false); |
| |
| // delete all the rows, but don't commit the delete |
| int[] ret_before = getSpaceInfo(conn, "APP", table_name, false); |
| executeQuery(conn, "delete from " + table_name, false); |
| |
| |
| // dump_table(conn, schemaName, table_name, false); |
| |
| // Purge pass on non-committed deleted rows should do nothing. |
| |
| // System.out.println("lock info before compress call:\n " + get_lock_info(conn, true)); |
| |
| // Calling compress with just the "purge" pass option, no commit called. |
| callCompress(conn, "APP", table_name, true, false, false, false); |
| |
| int[] ret_after = getSpaceInfo(conn, "APP", table_name, false); |
| |
| // expect no change in the number of allocated pages! |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) |
| { |
| log_wrong_count( |
| "Expected no alloc page change(1).", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_ALLOC], |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| // expect no change in the number of free pages, if there are there |
| // is a problem with purge locking recognizing committed deleted rows. |
| if (ret_after[SPACE_INFO_NUM_FREE] != ret_before[SPACE_INFO_NUM_FREE]) |
| { |
| log_wrong_count( |
| "Expected no free page change(1).", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_FREE], |
| ret_after[SPACE_INFO_NUM_FREE], |
| ret_before, ret_after); |
| } |
| |
| // Test that it is ok to call multiple purge passes in single xact. |
| |
| // Calling compress with just the "purge" pass option, no commit called. |
| callCompress(conn, "APP", table_name, true, false, false, false); |
| ret_after = getSpaceInfo(conn, "APP", table_name, false); |
| |
| // expect no change in the number of allocated pages! |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) |
| { |
| log_wrong_count( |
| "Expected no alloc page change(2).", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_ALLOC], |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| // expect no change in the number of free pages, if there are there |
| // is a problem with purge locking recognizing committed deleted rows. |
| if (ret_after[SPACE_INFO_NUM_FREE] != ret_before[SPACE_INFO_NUM_FREE]) |
| { |
| log_wrong_count( |
| "Expected no free page change(2).", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_FREE], |
| ret_after[SPACE_INFO_NUM_FREE], |
| ret_before, ret_after); |
| } |
| |
| // since table was just loaded a defragment pass also should |
| // not find anything to do. |
| |
| // Calling compress with just the "defragment" option, no commit called. |
| |
| // currently the defragment option requires a table level lock in |
| // the nested user transaction, which will conflict and cause a |
| // lock timeout. |
| |
| try |
| { |
| callCompress(conn, "APP", table_name, false, true, false, false); |
| |
| logError("Defragment pass did not get a lock timeout."); |
| } |
| catch (SQLException sqle) |
| { |
| // ignore exception. |
| } |
| |
| ret_after = getSpaceInfo(conn, "APP", table_name, false); |
| |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) |
| { |
| log_wrong_count( |
| "Expected no alloc page change(3).", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_ALLOC], |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| if (ret_after[SPACE_INFO_NUM_FREE] != ret_before[SPACE_INFO_NUM_FREE]) |
| { |
| log_wrong_count( |
| "Expected no free page change(3).", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_FREE], |
| ret_after[SPACE_INFO_NUM_FREE], |
| ret_before, ret_after); |
| } |
| |
| |
| // make sure table is back to all deleted row state. lock timeout |
| // will abort transaction. |
| |
| // delete all rows and commit. |
| executeQuery(conn, "delete from " + table_name, true); |
| |
| callWaitForPostCommit(conn); |
| |
| // compress all space and commit. |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| |
| // add back all rows and commit. |
| if (long_table) |
| createAndLoadLongTable(conn, create_table, table_name, num_rows); |
| else |
| createAndLoadTable(conn, create_table, table_name, num_rows, 0); |
| conn.commit(); |
| |
| // delete all rows, and NO commit. |
| executeQuery(conn, "delete from " + table_name, false); |
| |
| |
| // Calling compress with just the truncate option, may change allocated |
| // and free page count as they system may have preallocated pages to |
| // the end of the file as part of the load. The file can't shrink |
| // any more than the free page count before the compress. |
| |
| // running the truncate pass only. If it compresses anything it is |
| // just the preallocated pages at end of the file. |
| |
| // currently the defragment option requires a table level lock in |
| // the nested user transaction, which will conflict and cause a |
| // lock timeout. |
| |
| |
| ret_before = getSpaceInfo(conn, "APP", table_name, false); |
| callCompress(conn, "APP", table_name, false, false, true, false); |
| ret_after = getSpaceInfo(conn, "APP", table_name, false); |
| |
| // expect no change in the number of allocated pages! |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) |
| { |
| log_wrong_count( |
| "Expected no alloc page change(4).", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_ALLOC], |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| // The only space that truncate only pass can free are free pages |
| // located at end of file, so after free space can be anywhere from |
| // what it was before to 0 pages. |
| if (ret_after[SPACE_INFO_NUM_FREE] > ret_before[SPACE_INFO_NUM_FREE]) |
| { |
| log_wrong_count( |
| "Expected no increase in free pages(4).", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_FREE], |
| ret_after[SPACE_INFO_NUM_FREE], |
| ret_before, ret_after); |
| } |
| |
| // now commit the deletes, run all phases and make sure empty table |
| // results. |
| conn.commit(); |
| |
| callWaitForPostCommit(conn); |
| |
| // check the table. Note that this will accumulate locks and |
| // will commit the transaction. |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| // test running each phase in order. |
| callCompress(conn, "APP", table_name, true, false, false, false); |
| callCompress(conn, "APP", table_name, false, true, false, false); |
| callCompress(conn, "APP", table_name, false, false, true , false); |
| ret_after = getSpaceInfo(conn, "APP", table_name, false); |
| |
| // An empty table has 2 pages, one allocation page and the 1st page |
| // which will have a system row in it. The space vti only reports |
| // a count of the user pages so the count is 1. |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != 1) |
| { |
| log_wrong_count( |
| "Expected all pages to be truncated.", |
| table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| if (ret_after[SPACE_INFO_NUM_FREE] != 0) |
| { |
| log_wrong_count( |
| "Expected no free page after all pages truncated.", |
| table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (verbose) |
| testProgress("calling consistency checker."); |
| |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| testProgress("end checkPurgePhase" + num_rows + " row test."); |
| } |
| |
| /** |
| * Test 1 - various # page tests, regular row/columns |
| * <p> |
| * perform a number of insert/delete/compress operations on a variety |
| * of sized tables, use space allocation information to verify that |
| * compression is happening and use consistency checker to verify that |
| * tables and indexes are all valid following the operations. |
| * <p> |
| * loop through testing interesting row count cases. The cases are |
| * 0 rows - basic edge case, 2 page table: 1 alloc, 1 user page |
| * 1 row - another edge case, 2 page table: 1 alloc, 1 user page |
| * 50 rows - 3 page table case: 1 alloc, 1 user page, 1 user page freed |
| * 4000 rows - reasonable number of pages to test out, still 1 alloc page |
| * |
| * note that row numbers greater than 4000 may lead to lock escalation |
| * issues, if queries like "delete from x" are used to delete all the |
| * rows. |
| * |
| * <p> |
| * |
| **/ |
| private void test1( |
| Connection conn, |
| String test_name, |
| String table_name) |
| throws SQLException |
| { |
| beginTest(conn, test_name); |
| |
| int[] test_cases = {0, 1, 50, 4000}; |
| |
| for (int i = 0; i < test_cases.length; i++) |
| { |
| // first create new table and run the tests. |
| deleteAllRows( |
| conn, true, false, "APP", table_name, test_cases[i]); |
| |
| // now rerun tests on existing table, which had all rows deleted |
| // and truncated. |
| deleteAllRows( |
| conn, false, false, "APP", table_name, test_cases[i]); |
| |
| checkPurgePhase( |
| conn, false, false, "APP", table_name, test_cases[i]); |
| |
| executeQuery(conn, "drop table " + table_name, true); |
| } |
| |
| endTest(conn, test_name); |
| } |
| |
| /** |
| * Test 2 - check repeated delete tests. |
| * <p> |
| * There was a timing error where test1 would usually pass, but |
| * repeated execution of this test found a timing problem with |
| * allocation using an "unallocated" page and getting an I/O error. |
| * |
| **/ |
| private void test2( |
| Connection conn, |
| String test_name, |
| String table_name) |
| throws SQLException |
| { |
| beginTest(conn, test_name); |
| |
| int[] test_cases = {4000}; |
| |
| for (int i = 0; i < test_cases.length; i++) |
| { |
| // first create new table and run the tests. |
| simpleDeleteAllRows( |
| conn, true, false, "APP", table_name, test_cases[i]); |
| |
| for (int j = 0; j < 100; j++) |
| { |
| |
| // now rerun tests on existing table, which had all rows deleted |
| // and truncated. |
| deleteAllRows( |
| conn, false, false, "APP", table_name, test_cases[i]); |
| } |
| |
| executeQuery(conn, "drop table " + table_name, true); |
| } |
| |
| endTest(conn, test_name); |
| } |
| |
| |
| |
| /** |
| * Test 3 - various # page tests, long row and long columns |
| * <p> |
| * perform a number of insert/delete/compress operations on a variety |
| * of sized tables, use space allocation information to verify that |
| * compression is happening and use consistency checker to verify that |
| * tables and indexes are all valid following the operations. |
| * <p> |
| * loop through testing interesting row count cases. The cases are |
| * 0 rows - basic edge case |
| * 1 row - another edge case |
| * 100 rows - ~50 meg table |
| * 4000 rows - ~2 gig table |
| * |
| * note that row numbers greater than 4000 may lead to lock escalation |
| * issues, if queries like "delete from x" are used to delete all the |
| * rows. |
| * |
| * <p> |
| * |
| **/ |
| private void test3( |
| Connection conn, |
| String test_name, |
| String table_name) |
| throws SQLException |
| { |
| beginTest(conn, test_name); |
| |
| // note that 500 rows took 30 minutes on a ~1.5 ghz laptop |
| int[] test_cases = {1, 2, 50}; |
| |
| for (int i = 0; i < test_cases.length; i++) |
| { |
| // first create new table and run the tests. |
| deleteAllRows( |
| conn, true, true, "APP", table_name, test_cases[i]); |
| |
| // now rerun tests on existing table, which had all rows deleted |
| // and truncated. |
| deleteAllRows( |
| conn, false, true, "APP", table_name, test_cases[i]); |
| |
| checkPurgePhase( |
| conn, false, true, "APP", table_name, test_cases[i]); |
| |
| executeQuery(conn, "drop table " + table_name, true); |
| } |
| |
| endTest(conn, test_name); |
| } |
| |
| /** |
| * Test 4 - check repeated delete tests. |
| * <p> |
| * There was a timing error where test1 would usually pass, but |
| * repeated execution of this test found a timing problem with |
| * allocation using an "unallocated" page and getting an I/O error. |
| * |
| **/ |
| private void test4( |
| Connection conn, |
| String test_name, |
| String table_name) |
| throws SQLException |
| { |
| beginTest(conn, test_name); |
| |
| int[] test_cases = {4000}; |
| |
| for (int i = 0; i < test_cases.length; i++) |
| { |
| |
| for (int j = 0; j < 100; j++) |
| { |
| // first create new table and run the tests. |
| simpleDeleteAllRows( |
| conn, true, false, "APP", table_name, test_cases[i]); |
| |
| // now rerun tests on existing table, which had all rows deleted |
| // and truncated. |
| deleteAllRows( |
| conn, false, false, "APP", table_name, test_cases[i]); |
| |
| executeQuery(conn, "drop table " + table_name, true); |
| } |
| |
| } |
| |
| endTest(conn, test_name); |
| } |
| |
| /** |
| * Create and load table for test5. |
| * <p> |
| * schema of table: |
| * keycol int, |
| * onehalf int, |
| * onethird int, |
| * c varchar(300) |
| * |
| * @param conn Connection to use for sql execution. |
| * @param schemaName the schema to use. |
| * @param table_name the table to use. |
| * @param num_rows number of rows to add to the table. |
| * |
| * @exception StandardException Standard exception policy. |
| **/ |
| private void test5_load( |
| Connection conn, |
| String schemaName, |
| String table_name, |
| int num_rows) |
| throws SQLException |
| { |
| Statement s = conn.createStatement(); |
| |
| s.execute( |
| "create table " + table_name + |
| " (keycol integer primary key, onehalf integer, onethird integer, c varchar(300))"); |
| s.close(); |
| |
| PreparedStatement insert_stmt = |
| conn.prepareStatement( |
| "insert into " + table_name + " values(?, ?, ?, ?)"); |
| |
| char[] data1_data = new char[200]; |
| |
| for (int i = 0; i < data1_data.length; i++) |
| { |
| data1_data[i] = 'b'; |
| } |
| String data1_str = new String(data1_data); |
| |
| for (int i = 0; i < num_rows; i++) |
| { |
| insert_stmt.setInt(1, i); // keycol |
| insert_stmt.setInt(2, i % 2); // onehalf: 0 or 1 |
| insert_stmt.setInt(3, i % 3); // onethird: 0, 1, or 3 |
| insert_stmt.setString(4, data1_str); // c |
| insert_stmt.execute(); |
| } |
| |
| conn.commit(); |
| } |
| |
| /** |
| * Execute test5, simple defragement test. |
| * <p> |
| * o delete every other row, defragment |
| * o delete every third row, defragment |
| * o delete last 1000 rows, defragment |
| * o delete first 512 rows, defragment. |
| * <p> |
| * run test with at least 2000 rows. |
| **/ |
| private void test5_run( |
| Connection conn, |
| String schemaName, |
| String table_name, |
| int num_rows) |
| throws SQLException |
| { |
| testProgress("begin test5: " + num_rows + " row test."); |
| |
| if (verbose) |
| testProgress("Calling compress."); |
| |
| // compress with no deletes should not affect size |
| int[] ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| int[] ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) |
| { |
| log_wrong_count( |
| "Expected no alloc page change.", |
| table_name, num_rows, |
| ret_before[SPACE_INFO_NUM_ALLOC], |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (verbose) |
| testProgress("calling consistency checker."); |
| |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| // DELETE EVERY OTHER ROW, COMPRESS, CHECK |
| // |
| // |
| |
| // delete all the rows every other row. |
| ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| executeQuery( |
| conn, "delete from " + table_name + " where onehalf = 0", true); |
| |
| callWaitForPostCommit(conn); |
| |
| if (verbose) |
| testProgress("deleted every other row, now calling compress."); |
| |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| if (total_pages(ret_after) != total_pages(ret_before)) |
| { |
| // currently deleting every other row does not add free or unfilled |
| // pages to the container so defragment has nowhere to put the rows. |
| |
| log_wrong_count( |
| "Expected no truncation.", |
| table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (verbose) |
| testProgress("calling consistency checker."); |
| |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| // DELETE EVERY THIRD ROW in original dataset, COMPRESS, CHECK |
| // |
| // |
| |
| // delete every third row |
| ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| executeQuery( |
| conn, "delete from " + table_name + " where onethird = 0", true); |
| |
| callWaitForPostCommit(conn); |
| |
| if (verbose) |
| testProgress("deleted every third row, now calling compress."); |
| |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| if (total_pages(ret_after) != total_pages(ret_before)) |
| { |
| // currently deleting every third row does not create any free |
| // or unfilled pages so defragment has no place to move rows. |
| log_wrong_count( |
| "Expected no truncation.", |
| table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (verbose) |
| testProgress("calling consistency checker."); |
| |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| // DELETE top "half" of rows in original dataset, COMPRESS, CHECK |
| // |
| // |
| |
| // delete top "half" of the rows in the original dataset. |
| ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| executeQuery( |
| conn, "delete from " + table_name + " where keycol > " + |
| (num_rows / 2), true); |
| |
| callWaitForPostCommit(conn); |
| |
| if (verbose) |
| testProgress("deleted top half of the rows, now calling compress."); |
| |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| // compress should be able to clean up about 1/2 of the pages. |
| if (verbose) |
| { |
| log_wrong_count( |
| "deleted top half keys, spaceinfo:", |
| table_name, num_rows, |
| ((total_pages(ret_before) / 2) + 2), |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (total_pages(ret_after) > ((total_pages(ret_before) / 2) + 2)) |
| { |
| log_wrong_count( |
| "Expected at least " + |
| (ret_before[SPACE_INFO_NUM_ALLOC] / 2 + 2) + |
| " pages to be truncated.", |
| table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (verbose) |
| testProgress("calling consistency checker."); |
| |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| // DELETE 1st 500 rows in original dataset, COMPRESS, CHECK |
| // |
| // |
| |
| // delete keys less than 500 |
| ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| executeQuery( |
| conn, "delete from " + table_name + " where keycol < 500 ", true); |
| |
| callWaitForPostCommit(conn); |
| |
| if (verbose) |
| testProgress("deleted keys < 500, now calling compress."); |
| |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| if (verbose) |
| { |
| log_wrong_count( |
| "deleted bottom 500 keys, spaceinfo:", |
| table_name, num_rows, |
| (total_pages(ret_before) - 33), |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| // The bottom 500 keys, assuming 4k pages, takes about 33 pages |
| if (total_pages(ret_after) > (total_pages(ret_before) - 33)) |
| { |
| log_wrong_count( |
| "Expected at least 33 pages reclaimed.", |
| table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| if (verbose) |
| testProgress("calling consistency checker."); |
| |
| if (!checkConsistency(conn, schemaName, table_name)) |
| { |
| logError("conistency check failed."); |
| } |
| |
| |
| conn.commit(); |
| |
| testProgress("end test5: " + num_rows + " row test."); |
| } |
| |
| /** |
| * Cleanup after test5_run |
| **/ |
| private void test5_cleanup( |
| Connection conn, |
| String schemaName, |
| String table_name, |
| int num_rows) |
| throws SQLException |
| { |
| executeQuery(conn, "drop table " + table_name, true); |
| } |
| |
| /** |
| * Test 5 - simple defragment test. |
| * <p> |
| * Create dataset and then: |
| * o delete every other row, defragment |
| * o delete every third row, defragment |
| * o delete last 1000 rows, defragment |
| * o delete first 512 rows, defragment. |
| * <p> |
| * run test with at least 2000 rows. |
| * |
| **/ |
| private void test5( |
| Connection conn, |
| String test_name, |
| String table_name) |
| throws SQLException |
| { |
| beginTest(conn, test_name); |
| |
| int[] test_cases = {2000, 10000}; |
| |
| for (int i = 0; i < test_cases.length; i++) |
| { |
| test5_load(conn, "APP", table_name, test_cases[i]); |
| test5_run(conn, "APP", table_name, test_cases[i]); |
| test5_cleanup(conn, "APP", table_name, test_cases[i]); |
| } |
| |
| endTest(conn, test_name); |
| } |
| |
| /** |
| * Test 6 - Online compress test for table that spans more than 1 AllocExtent. |
| * <p> |
| * Create dataset with Data spread over more than 1 AllcExtent and then: |
| * delete enough rows so that the last AllocExtent is empty. |
| * Try OnlineCompress with Purge, Defragment and Truncate |
| * <p> |
| * run test with at least 103000 rows. |
| * |
| **/ |
| private void test6( |
| Connection conn, |
| String test_name, |
| String table_name) |
| throws SQLException |
| { |
| beginTest(conn, test_name); |
| |
| int[] noRows = {104000}; |
| |
| for (int i = 0; i < noRows.length; i++) |
| { |
| // first create new table and run the tests. |
| createAndLoadLargeTable(conn, true, table_name, noRows[i], 0); |
| |
| if (verbose) |
| testProgress("Calling compress."); |
| |
| // compress with no deletes should not affect size |
| int[] ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| int[] ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) |
| { |
| log_wrong_count( |
| "Expected no alloc page change.", |
| table_name, noRows[i], |
| ret_before[SPACE_INFO_NUM_ALLOC], |
| ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| testProgress("no delete case complete."); |
| |
| // delete all the rows. |
| ret_before = getSpaceInfo(conn, "APP", table_name, true); |
| executeQuery(conn, "delete from " + table_name, true); |
| |
| callWaitForPostCommit(conn); |
| |
| conn.commit(); |
| |
| if (verbose) |
| testProgress("deleted all rows, now calling compress."); |
| |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| ret_after = getSpaceInfo(conn, "APP", table_name, true); |
| |
| // An empty table has 2 pages, one allocation page and the 1st page |
| // which will have a system row in it. The space vti only reports |
| // a count of the user pages so the count is 1. |
| if (ret_after[SPACE_INFO_NUM_ALLOC] != 1) |
| { |
| log_wrong_count( |
| "Expected all pages to be truncated.", |
| table_name, noRows[i], 1, ret_after[SPACE_INFO_NUM_ALLOC], |
| ret_before, ret_after); |
| } |
| |
| testProgress("delete all rows case succeeded."); |
| |
| testProgress("end simple deleteAllRows," + noRows[i] + " row test."); |
| |
| executeQuery(conn, "drop table " + table_name, true); |
| } |
| |
| endTest(conn, test_name); |
| } |
| |
| /** |
| * Test 7 - Online compress test for fetching more rows than buffer limit. |
| * <p> |
| * For smaller row size, if number of rows per page is more than max buffer |
| * size, then check if the remaining rows are also fetched for Compress |
| * Operation |
| * <p> |
| **/ |
| private void test7( |
| Connection conn, |
| String test_name, |
| String table_name) |
| throws SQLException |
| { |
| beginTest(conn, test_name); |
| |
| Statement s = conn.createStatement(); |
| |
| s.execute("create table " + table_name + "(keycol int)"); |
| s.close(); |
| PreparedStatement insert_stmt = |
| conn.prepareStatement("insert into " + table_name + " values(?)"); |
| try |
| { |
| for (int i = 0; i < 1200; i++) |
| { |
| insert_stmt.setInt(1, i); |
| |
| insert_stmt.execute(); |
| } |
| } |
| catch (SQLException sqle) |
| { |
| System.out.println( |
| "Exception while trying to insert a row"); |
| throw sqle; |
| } |
| conn.commit(); |
| |
| // delete the front rows leaving the last 200. Post commit may reclaim |
| // space on pages where all rows are deleted. |
| executeQuery( |
| conn, "delete from " + table_name + " where keycol < 1000", true); |
| |
| callWaitForPostCommit(conn); |
| |
| conn.commit(); |
| |
| if (verbose) |
| testProgress("deleted first 1000 rows, now calling compress."); |
| |
| callCompress(conn, "APP", table_name, true, true, true, true); |
| |
| testProgress("delete rows case succeeded."); |
| |
| executeQuery(conn, "drop table " + table_name, true); |
| |
| endTest(conn, test_name); |
| } |
| |
| public void testList(Connection conn) |
| throws SQLException |
| { |
| test1(conn, "test1", "TEST1"); |
| // test2(conn, "test2", "TEST2"); |
| test3(conn, "test3", "TEST3"); |
| // test4(conn, "test4", "TEST4"); |
| test5(conn, "test5", "TEST5"); |
| test6(conn, "test6", "TEST6"); |
| test7(conn, "test7", "TEST7"); |
| } |
| |
| public static void callWaitForPostCommit(Connection conn) |
| throws SQLException { |
| CallableStatement cstmt = |
| conn.prepareCall( |
| "call wait_for_post_commit()"); |
| cstmt.execute(); |
| cstmt.close(); |
| } |
| |
| public static void main(String[] argv) |
| throws Throwable |
| { |
| OnlineCompressTest test = new OnlineCompressTest(); |
| |
| ij.getPropertyArg(argv); |
| Connection conn = ij.startJBMS(); |
| |
| Statement stmt = conn.createStatement(); |
| // Create a procedure to be called before checking on contents |
| // to ensure that the background worker thread has completed |
| // all the post-commit work. |
| stmt.execute( |
| "CREATE PROCEDURE WAIT_FOR_POST_COMMIT() " + |
| "LANGUAGE JAVA EXTERNAL NAME " + |
| "'org.apache.derbyTesting.functionTests.util." + |
| "T_Access.waitForPostCommitToFinish' " + |
| "PARAMETER STYLE JAVA"); |
| conn.setAutoCommit(false); |
| stmt.close(); |
| |
| try |
| { |
| test.testList(conn); |
| } |
| catch (SQLException sqle) |
| { |
| org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException( |
| System.out, sqle); |
| sqle.printStackTrace(System.out); |
| } |
| } |
| } |
