| /* |
| Derby - Class org.apache.derbyTesting.functionTests.tests.store.TableLockBasicTest |
| |
| 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 java.sql.Connection; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.util.Locale; |
| |
| import junit.framework.Test; |
| |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.LocaleTestSetup; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Very basic single user testing of table locking, verify that the right locks |
| * are obtained for simple operations. This test only looks at table and |
| * row logical locks, it does not verify physical latches or lock ordering. |
| -- |
| * The basic methodology is: |
| * start transaction |
| * simple operation |
| * print lock table which should match the master |
| * end transation |
| * |
| */ |
| public class TableLockBasicTest extends BaseJDBCTestCase { |
| public TableLockBasicTest(String name) { |
| super(name); |
| } |
| |
| public static Test suite() { |
| Test test = TestConfiguration.defaultSuite(TableLockBasicTest.class); |
| test = DatabasePropertyTestSetup.singleProperty(test, |
| "derby.storage.rowLocking", "false", true); |
| test = new LocaleTestSetup(test, Locale.ENGLISH); |
| return TestConfiguration.singleUseDatabaseDecorator(test); |
| } |
| |
| protected void setUp() throws SQLException { |
| Statement s = createStatement(); |
| s.execute("CREATE FUNCTION PADSTRING (DATA VARCHAR(32000), " |
| + "LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME " |
| + "'org.apache.derbyTesting.functionTests.util.Formatters" |
| + ".padString' LANGUAGE JAVA PARAMETER STYLE JAVA"); |
| createLockTableQueryEntries(s); |
| s.close(); |
| |
| getConnection().setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); |
| getConnection().setAutoCommit(false); |
| } |
| |
| private void createLockTableQueryEntries(Statement s) throws SQLException { |
| s.execute("create view lock_table as " |
| + "select " |
| + "cast(username as char(8)) as username," |
| + "cast(t.type as char(8)) as trantype," |
| + "cast(l.type as char(8)) as type," |
| + "cast(lockcount as char(3)) as cnt," |
| + "mode," |
| + "cast(tablename as char(12)) as tabname," |
| + "cast(lockname as char(10)) as lockname," |
| + "state," |
| + "status " |
| + "from " |
| + "syscs_diag.lock_table l right outer join syscs_diag.transaction_table t " |
| + "on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction'"); |
| |
| //lock table with system catalog locks included. |
| s.execute("create view full_lock_table as " |
| + "select " |
| + "cast(username as char(8)) as username, " |
| + "cast(t.type as char(8)) as trantype," |
| + "cast(l.type as char(8)) as type," |
| + "cast(lockcount as char(3)) as cnt," |
| + "mode, " |
| + "cast(tablename as char(12)) as tabname," |
| + "cast(lockname as char(10)) as lockname," |
| + "state," |
| + "status " |
| + "from " |
| + "syscs_diag.lock_table l right outer join syscs_diag.transaction_table t " |
| + "on l.xid = t.xid where l.tableType <> 'S' "); |
| |
| //lock table with no join. |
| s.execute("create view lock_table2 as " |
| + "select " |
| + "cast(l.xid as char(8)) as xid," |
| + "cast(l.type as char(8)) as type," |
| + "cast(lockcount as char(3)) as cnt," |
| + "mode," |
| + "cast(tablename as char(12)) as tabname," |
| + "cast(lockname as char(10)) as lockname," |
| + "state " |
| + "from " |
| + "syscs_diag.lock_table l " |
| + "where l.tableType <> 'S' "); |
| |
| //-- transaction table with no join. |
| s.execute("create view tran_table as " |
| + "select " |
| + "* " |
| + "from " |
| + "syscs_diag.transaction_table"); |
| } |
| |
| public void tearDown() throws Exception { |
| Statement st = createStatement(); |
| st.executeUpdate("DROP FUNCTION PADSTRING"); |
| |
| st.execute("drop view lock_table"); |
| st.execute("drop view full_lock_table"); |
| st.execute("drop view lock_table2"); |
| st.execute("drop view tran_table"); |
| |
| st.close(); |
| commit(); |
| |
| super.tearDown(); |
| } |
| |
| public void testInsertIntoHeap() throws SQLException { |
| Statement st = createStatement(); |
| st.execute("create table heap_only (a int)"); |
| commit(); |
| |
| //Test insert into empty heap, should just get table lock |
| st.execute("insert into heap_only values (1)"); |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "1", "X", "HEAP_ONLY", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| |
| //Test insert into heap with one row, just get table lock |
| st.execute("insert into heap_only values (2)"); |
| rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "1", "X", "HEAP_ONLY", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| |
| st.close(); |
| dropTable("heap_only"); |
| } |
| |
| /** |
| * Test select from a heap, should get shared table lock. |
| */ |
| public void testSelectFromHeap () throws SQLException { |
| Statement st = createStatement(); |
| constructHeap(st); |
| |
| ResultSet rs = st.executeQuery("select a from heap_only where a = 1"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, |
| }); |
| rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "1", "S", "HEAP_ONLY", "Tablelock", "GRANT", "ACTIVE"} |
| }); |
| commit(); |
| |
| st.close(); |
| dropTable("heap_only"); |
| } |
| |
| private void constructHeap(Statement st) throws SQLException { |
| st.execute("create table heap_only (a int)"); |
| commit(); |
| |
| st.execute("insert into heap_only values (1)"); |
| commit(); |
| st.execute("insert into heap_only values (2)"); |
| commit(); |
| } |
| |
| /** |
| * Test delete from a heap, should get exclusive table lock. |
| */ |
| public void testDelectFromHeap () throws SQLException { |
| Statement st = createStatement(); |
| constructHeap(st); |
| |
| st.execute("delete from heap_only where a = 1"); |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "2", "X", "HEAP_ONLY", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| st.close(); |
| dropTable("heap_only"); |
| } |
| |
| /** |
| * Test update to heap, should get exclusive table lock. |
| */ |
| public void testUpdateToHeap () throws SQLException { |
| Statement st = createStatement(); |
| constructHeap(st); |
| |
| st.execute("update heap_only set a = 1000 where a = 2"); |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "2", "X", "HEAP_ONLY", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| st.close(); |
| dropTable("heap_only"); |
| } |
| |
| /** |
| * Test drop of heap, should get exclusive table lock. |
| */ |
| public void testDropHeap () throws SQLException { |
| Statement st = createStatement(); |
| constructHeap(st); |
| |
| st.execute("drop table heap_only"); |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "3", "X", "*** TRANSIEN", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| st.close(); |
| dropTable("heap_only"); |
| } |
| |
| public void testCreateIndex() throws SQLException { |
| Statement st = createStatement(); |
| |
| st.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')"); |
| st.execute("create table indexed_heap (a int, b varchar(1000))"); |
| st.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)"); |
| |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "1", "X", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| st.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096')"); |
| st.execute("create index a_idx on indexed_heap (a, b)"); |
| st.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL)"); |
| rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "4", "S", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"}, |
| {"APP", "UserTran", "TABLE", "1", "X", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"}, |
| {"APP", "UserTran", "TABLE", "1", "X", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"}, }); |
| commit(); |
| |
| st.close(); |
| |
| dropTable("indexed_heap"); |
| } |
| |
| public void testInsertIntoEmtpyIndexedHeaP() throws SQLException { |
| Statement st = createStatement(); |
| |
| st.execute("create table indexed_heap (a int, b varchar(1000))"); |
| commit(); |
| st.execute("create index a_idx on indexed_heap (a, b)"); |
| commit(); |
| |
| // Test insert into indexed heap, should just get table lock |
| st.execute("insert into indexed_heap (a) values (1)"); |
| ResultSet rs = st.executeQuery(" select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][] { |
| {"APP", "UserTran", "TABLE", "2", "X", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| // Test insert into indexed heap with one row, just get table lock |
| st.execute("insert into indexed_heap (a) values (2)"); |
| rs = st.executeQuery(" select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][] { |
| {"APP", "UserTran", "TABLE", "2", "X", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"} |
| }); |
| commit(); |
| |
| st.close(); |
| |
| dropTable("indexed_heap"); |
| } |
| |
| /** |
| * Test select from a indexed heap, should get shared table lock. |
| */ |
| public void testSelectFromIndexedHeap () throws SQLException { |
| Statement st = createStatement(); |
| constructIndexedHeap(st); |
| |
| ResultSet rs = st.executeQuery("select a from indexed_heap where a = 1"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"}, |
| }); |
| |
| rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "1", "S", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| st.close(); |
| |
| dropTable("indexed_heap"); |
| } |
| |
| private void constructIndexedHeap(Statement st) throws SQLException { |
| st.execute("create table indexed_heap (a int, b varchar(1000))"); |
| st.execute("create index a_idx on indexed_heap (a, b)"); |
| |
| st.execute("insert into indexed_heap (a) values (1)"); |
| st.execute("insert into indexed_heap (a) values (2)"); |
| |
| commit(); |
| } |
| |
| /** |
| * Test delete from a indexed heap, should get exclusive table lock. |
| */ |
| public void testDeleteFromIndexedHeap () throws SQLException { |
| Statement st = createStatement(); |
| constructIndexedHeap(st); |
| |
| st.execute("delete from indexed_heap where a = 1"); |
| |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "3", "X", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| st.close(); |
| |
| dropTable("indexed_heap"); |
| } |
| |
| /** |
| * Test update to indexed heap, should get exclusive table lock. |
| */ |
| public void testUpdateToIndexedHeap () throws SQLException { |
| Statement st = createStatement(); |
| constructIndexedHeap(st); |
| |
| st.execute("update indexed_heap set a = 1000 where a = 2"); |
| |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "4", "X", "INDEXED_HEAP", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| st.close(); |
| |
| dropTable("indexed_heap"); |
| } |
| |
| /** |
| * Test drop of indexed heap, should get exclusive table lock. |
| */ |
| public void testDropIndexedHeap () throws SQLException { |
| Statement st = createStatement(); |
| constructIndexedHeap(st); |
| |
| st.execute("drop table indexed_heap"); |
| |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "1", "X", "*** TRANSIEN", "Tablelock", "GRANT", "ACTIVE"}, |
| {"APP", "UserTran", "TABLE", "4", "X", "*** TRANSIEN", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| commit(); |
| |
| st.close(); |
| } |
| |
| /** |
| * Test LOCK TABLE statement |
| */ |
| public void testLockTableStatement() throws SQLException { |
| Statement st = createStatement(); |
| |
| st.execute("create table t1(c1 int)"); |
| commit(); |
| |
| PreparedStatement pst = prepareStatement("lock table t1 in exclusive mode"); |
| pst.execute(); |
| |
| ResultSet rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "1", "X", "T1", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| |
| //verify that statement gets recompiled correctly |
| st.execute("drop table t1"); |
| st.execute("create table t1(c1 int)"); |
| pst.execute(); |
| commit(); |
| |
| st.execute("lock table t1 in share mode"); |
| rs = st.executeQuery( |
| " select * from lock_table order by tabname, type " |
| + "desc, mode, cnt, lockname"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"APP", "UserTran", "TABLE", "1", "S", "T1", "Tablelock", "GRANT", "ACTIVE"}, |
| }); |
| |
| st.execute("drop table t1"); |
| commit(); |
| |
| st.close(); |
| } |
| |
| /** |
| * verify that lock table not allowed in sys schema |
| */ |
| public void testLockTableInSysSchema() throws SQLException { |
| Statement st = createStatement(); |
| |
| assertStatementError("42X62", st, |
| "lock table sys.systables in exclusive mode"); |
| |
| st.close(); |
| } |
| |
| /** |
| * Test RTS output when table locking configured |
| */ |
| public void testRTSOutput() throws SQLException { |
| Statement st = createStatement(); |
| |
| st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); |
| st.execute("create table rts(c1 int)"); |
| st.execute("insert into rts values 1"); |
| commit(); |
| |
| ResultSet rs = st.executeQuery("select * from rts with cs"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"1"} |
| }); |
| |
| rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()"); |
| |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Statement Name: \n" |
| + " " + (getTestConfiguration().getJDBCClient().isEmbedded() ? "null\n" : "SQL_CURLH000C3\n") |
| + "Statement Text: \n" |
| + " select * from rts with cs\n" |
| + "Parse Time: 0\n" |
| + "Bind Time: 0\n" |
| + "Optimize Time: 0\n" |
| + "Generate Time: 0\n" |
| + "Compile Time: 0\n" |
| + "Execute Time: 0\n" |
| + "Begin Compilation Timestamp : null\n" |
| + "End Compilation Timestamp : null\n" |
| + "Begin Execution Timestamp : null\n" |
| + "End Execution Timestamp : null\n" |
| + "Statement Execution Plan Text: \n" |
| + "Table Scan ResultSet for RTS at read committed isolation level using instantaneous share row locking chosen by the optimizer (Actual locking used: table level locking.)\n" |
| + "Number of opens = 1\n" |
| + "Rows seen = 1\n" |
| + "Rows filtered = 0\n" |
| + "Fetch Size = 16\n" |
| + " constructor time (milliseconds) = 0\n" |
| + " open time (milliseconds) = 0\n" |
| + " next time (milliseconds) = 0\n" |
| + " close time (milliseconds) = 0\n" |
| + " next time in milliseconds/row = 0\n\n" |
| + "scan information:\n" |
| + " Bit set of columns fetched=All\n" |
| + " Number of columns fetched=1\n" |
| + " Number of pages visited=1\n" |
| + " Number of rows qualified=1\n" |
| + " Number of rows visited=1\n" |
| + " Scan type=heap\n" |
| + " start position:\n" |
| + " null\n" |
| + " stop position:\n" |
| + " null\n" |
| + " qualifiers:\n" |
| + " None\n" |
| + " optimizer estimated row count: 6.00\n" |
| + " optimizer estimated cost: 100.40" |
| }, |
| }); |
| |
| st.execute("drop table rts"); |
| commit(); |
| |
| st.close(); |
| } |
| |
| public void testDDLTableLockMode() throws SQLException { |
| Statement st = createStatement(); |
| st.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); |
| |
| st.execute("create table default_granularity(c1 int)"); |
| st.execute("create table row_granularity(c1 int)"); |
| st.execute("alter table row_granularity locksize row"); |
| st.execute("create table table_granularity(c1 int)"); |
| st.execute("alter table table_granularity locksize table"); |
| |
| ResultSet rs = st.executeQuery("select * from default_granularity with cs"); |
| JDBC.assertEmpty(rs); |
| |
| rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Statement Name: \n" |
| + " " + (getTestConfiguration().getJDBCClient().isEmbedded() ? "null\n" : "SQL_CURLH000C2\n") |
| + "Statement Text: \n" |
| + " select * from default_granularity with cs\n" |
| + "Parse Time: 0\n" |
| + "Bind Time: 0\n" |
| + "Optimize Time: 0\n" |
| + "Generate Time: 0\n" |
| + "Compile Time: 0\n" |
| + "Execute Time: 0\n" |
| + "Begin Compilation Timestamp : null\n" |
| + "End Compilation Timestamp : null\n" |
| + "Begin Execution Timestamp : null\n" |
| + "End Execution Timestamp : null\n" |
| + "Statement Execution Plan Text: \n" |
| + "Table Scan ResultSet for DEFAULT_GRANULARITY at read committed isolation level using instantaneous share row locking chosen by the optimizer (Actual locking used: table level locking.)\n" |
| + "Number of opens = 1\n" |
| + "Rows seen = 0\n" |
| + "Rows filtered = 0\n" |
| + "Fetch Size = 16\n" |
| + " constructor time (milliseconds) = 0\n" |
| + " open time (milliseconds) = 0\n" |
| + " next time (milliseconds) = 0\n" |
| + " close time (milliseconds) = 0\n\n" |
| + "scan information:\n" |
| + " Bit set of columns fetched=All\n" |
| + " Number of columns fetched=1\n" |
| + " Number of pages visited=1\n" |
| + " Number of rows qualified=0\n" |
| + " Number of rows visited=0\n" |
| + " Scan type=heap\n" |
| + " start position:\n" |
| + " null\n" |
| + " stop position:\n" |
| + " null\n" |
| + " qualifiers:\n" |
| + " None\n" |
| + " optimizer estimated row count: 6.00\n" |
| + " optimizer estimated cost: 100.40" |
| } |
| } |
| ); |
| |
| |
| rs = st.executeQuery("select * from default_granularity with rr"); |
| JDBC.assertEmpty(rs); |
| |
| rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Statement Name: \n" |
| + " " + (getTestConfiguration().getJDBCClient().isEmbedded() ? "null\n" : "SQL_CURLH000C2\n") |
| + "Statement Text: \n" |
| + " select * from default_granularity with rr\n" |
| + "Parse Time: 0\n" |
| + "Bind Time: 0\n" |
| + "Optimize Time: 0\n" |
| + "Generate Time: 0\n" |
| + "Compile Time: 0\n" |
| + "Execute Time: 0\n" |
| + "Begin Compilation Timestamp : null\n" |
| + "End Compilation Timestamp : null\n" |
| + "Begin Execution Timestamp : null\n" |
| + "End Execution Timestamp : null\n" |
| + "Statement Execution Plan Text: \n" |
| + "Table Scan ResultSet for DEFAULT_GRANULARITY at serializable isolation level using share table locking chosen by the optimizer\n" |
| + "Number of opens = 1\n" |
| + "Rows seen = 0\n" |
| + "Rows filtered = 0\n" |
| + "Fetch Size = 16\n" |
| + " constructor time (milliseconds) = 0\n" |
| + " open time (milliseconds) = 0\n" |
| + " next time (milliseconds) = 0\n" |
| + " close time (milliseconds) = 0\n\n" |
| + "scan information:\n" |
| + " Bit set of columns fetched=All\n" |
| + " Number of columns fetched=1\n" |
| + " Number of pages visited=1\n" |
| + " Number of rows qualified=0\n" |
| + " Number of rows visited=0\n" |
| + " Scan type=heap\n" |
| + " start position:\n" |
| + " null\n" |
| + " stop position:\n" |
| + " null\n" |
| + " qualifiers:\n" |
| + " None\n" |
| + " optimizer estimated row count: 6.00\n" |
| + " optimizer estimated cost: 100.40" |
| } |
| } |
| ); |
| |
| rs = st.executeQuery("select * from default_granularity with cs"); |
| JDBC.assertEmpty(rs); |
| |
| rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Statement Name: \n" |
| + " " + (getTestConfiguration().getJDBCClient().isEmbedded() ? "null\n" : "SQL_CURLH000C2\n") |
| + "Statement Text: \n" |
| + " select * from default_granularity with cs\n" |
| + "Parse Time: 0\n" |
| + "Bind Time: 0\n" |
| + "Optimize Time: 0\n" |
| + "Generate Time: 0\n" |
| + "Compile Time: 0\n" |
| + "Execute Time: 0\n" |
| + "Begin Compilation Timestamp : null\n" |
| + "End Compilation Timestamp : null\n" |
| + "Begin Execution Timestamp : null\n" |
| + "End Execution Timestamp : null\n" |
| + "Statement Execution Plan Text: \n" |
| + "Table Scan ResultSet for DEFAULT_GRANULARITY at read committed isolation level using instantaneous share row locking chosen by the optimizer (Actual locking used: table level locking.)\n" |
| + "Number of opens = 1\n" |
| + "Rows seen = 0\n" |
| + "Rows filtered = 0\n" |
| + "Fetch Size = 16\n" |
| + " constructor time (milliseconds) = 0\n" |
| + " open time (milliseconds) = 0\n" |
| + " next time (milliseconds) = 0\n" |
| + " close time (milliseconds) = 0\n\n" |
| + "scan information:\n" |
| + " Bit set of columns fetched=All\n" |
| + " Number of columns fetched=1\n" |
| + " Number of pages visited=1\n" |
| + " Number of rows qualified=0\n" |
| + " Number of rows visited=0\n" |
| + " Scan type=heap\n" |
| + " start position:\n" |
| + " null\n" |
| + " stop position:\n" |
| + " null\n" |
| + " qualifiers:\n" |
| + " None\n" |
| + " optimizer estimated row count: 6.00\n" |
| + " optimizer estimated cost: 100.40" |
| } |
| } |
| ); |
| |
| |
| rs = st.executeQuery("select * from row_granularity with rr"); |
| JDBC.assertEmpty(rs); |
| |
| rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Statement Name: \n" |
| + " " + (getTestConfiguration().getJDBCClient().isEmbedded() ? "null\n" : "SQL_CURLH000C2\n") |
| + "Statement Text: \n" |
| + " select * from row_granularity with rr\n" |
| + "Parse Time: 0\n" |
| + "Bind Time: 0\n" |
| + "Optimize Time: 0\n" |
| + "Generate Time: 0\n" |
| + "Compile Time: 0\n" |
| + "Execute Time: 0\n" |
| + "Begin Compilation Timestamp : null\n" |
| + "End Compilation Timestamp : null\n" |
| + "Begin Execution Timestamp : null\n" |
| + "End Execution Timestamp : null\n" |
| + "Statement Execution Plan Text: \n" |
| + "Table Scan ResultSet for ROW_GRANULARITY at serializable isolation level using share table locking chosen by the optimizer\n" |
| + "Number of opens = 1\n" |
| + "Rows seen = 0\n" |
| + "Rows filtered = 0\n" |
| + "Fetch Size = 16\n" |
| + " constructor time (milliseconds) = 0\n" |
| + " open time (milliseconds) = 0\n" |
| + " next time (milliseconds) = 0\n" |
| + " close time (milliseconds) = 0\n\n" |
| + "scan information:\n" |
| + " Bit set of columns fetched=All\n" |
| + " Number of columns fetched=1\n" |
| + " Number of pages visited=1\n" |
| + " Number of rows qualified=0\n" |
| + " Number of rows visited=0\n" |
| + " Scan type=heap\n" |
| + " start position:\n" |
| + " null\n" |
| + " stop position:\n" |
| + " null\n" |
| + " qualifiers:\n" |
| + " None\n" |
| + " optimizer estimated row count: 6.00\n" |
| + " optimizer estimated cost: 100.40" |
| } |
| } |
| ); |
| |
| |
| rs = st.executeQuery("select * from table_granularity with cs"); |
| JDBC.assertEmpty(rs); |
| |
| rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Statement Name: \n" |
| + " " + (getTestConfiguration().getJDBCClient().isEmbedded() ? "null\n" : "SQL_CURLH000C2\n") |
| + "Statement Text: \n" |
| + " select * from table_granularity with cs\n" |
| + "Parse Time: 0\n" |
| + "Bind Time: 0\n" |
| + "Optimize Time: 0\n" |
| + "Generate Time: 0\n" |
| + "Compile Time: 0\n" |
| + "Execute Time: 0\n" |
| + "Begin Compilation Timestamp : null\n" |
| + "End Compilation Timestamp : null\n" |
| + "Begin Execution Timestamp : null\n" |
| + "End Execution Timestamp : null\n" |
| + "Statement Execution Plan Text: \n" |
| + "Table Scan ResultSet for TABLE_GRANULARITY at read committed isolation level using instantaneous share table locking chosen by the optimizer\n" |
| + "Number of opens = 1\n" |
| + "Rows seen = 0\n" |
| + "Rows filtered = 0\n" |
| + "Fetch Size = 16\n" |
| + " constructor time (milliseconds) = 0\n" |
| + " open time (milliseconds) = 0\n" |
| + " next time (milliseconds) = 0\n" |
| + " close time (milliseconds) = 0\n\n" |
| + "scan information:\n" |
| + " Bit set of columns fetched=All\n" |
| + " Number of columns fetched=1\n" |
| + " Number of pages visited=1\n" |
| + " Number of rows qualified=0\n" |
| + " Number of rows visited=0\n" |
| + " Scan type=heap\n" |
| + " start position:\n" |
| + " null\n" |
| + " stop position:\n" |
| + " null\n" |
| + " qualifiers:\n" |
| + " None\n" |
| + " optimizer estimated row count: 6.00\n" |
| + " optimizer estimated cost: 100.40" |
| } |
| } |
| ); |
| |
| |
| rs = st.executeQuery("select * from table_granularity with rr"); |
| JDBC.assertEmpty(rs); |
| |
| rs = st.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()"); |
| JDBC.assertFullResultSet(rs, new String[][]{ |
| {"Statement Name: \n" |
| + " " + (getTestConfiguration().getJDBCClient().isEmbedded() ? "null\n" : "SQL_CURLH000C2\n") |
| + "Statement Text: \n" |
| + " select * from table_granularity with rr\n" |
| + "Parse Time: 0\n" |
| + "Bind Time: 0\n" |
| + "Optimize Time: 0\n" |
| + "Generate Time: 0\n" |
| + "Compile Time: 0\n" |
| + "Execute Time: 0\n" |
| + "Begin Compilation Timestamp : null\n" |
| + "End Compilation Timestamp : null\n" |
| + "Begin Execution Timestamp : null\n" |
| + "End Execution Timestamp : null\n" |
| + "Statement Execution Plan Text: \n" |
| + "Table Scan ResultSet for TABLE_GRANULARITY at serializable isolation level using share table locking chosen by the optimizer\n" |
| + "Number of opens = 1\n" |
| + "Rows seen = 0\n" |
| + "Rows filtered = 0\n" |
| + "Fetch Size = 16\n" |
| + " constructor time (milliseconds) = 0\n" |
| + " open time (milliseconds) = 0\n" |
| + " next time (milliseconds) = 0\n" |
| + " close time (milliseconds) = 0\n\n" |
| + "scan information:\n" |
| + " Bit set of columns fetched=All\n" |
| + " Number of columns fetched=1\n" |
| + " Number of pages visited=1\n" |
| + " Number of rows qualified=0\n" |
| + " Number of rows visited=0\n" |
| + " Scan type=heap\n" |
| + " start position:\n" |
| + " null\n" |
| + " stop position:\n" |
| + " null\n" |
| + " qualifiers:\n" |
| + " None\n" |
| + " optimizer estimated row count: 6.00\n" |
| + " optimizer estimated cost: 100.40" |
| } |
| } |
| ); |
| |
| |
| st.close(); |
| rollback(); |
| } |
| |
| } |
