Examples of IndexStatsUtil

org.apache.derbyTesting.junit.IndexStatsUtil
Helper class for obtaining index statistics and doing asserts on them.
This implementation assumes all tables/indexes belong to the current schema.
The timeout value is used to make the utility more resilient to differences in timing due to varying scheduling decisions, processor speeds, etc. If the system table contains the wrong number of statistics objects for the query, it will be queried repeatedly until the right number of statistics objects is obtained or the query times out.

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

        //  prior to 10.5 and hence we can't cause the hanging statistics to 
        //  appear in order to test the drop statistics after hard upgrade
        if (!oldAtLeast(10, 5)) return;


        // Helper object to obtain information about index statistics.
        IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
        Statement s = createStatement();
        
        switch (getPhase())
        {
        case PH_CREATE:
            s.executeUpdate("CREATE TABLE TEST_TAB_1 (c11 int not null,"+
                    "c12 int not null, c13 int)");
            s.executeUpdate("INSERT INTO TEST_TAB_1 VALUES(1,1,1),(2,2,2)");
            s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                    "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
                    "PRIMARY KEY (c11)");
            //The statistics for primary key constraint has been added
            stats.assertTableStats("TEST_TAB_1",1);
            
            s.executeUpdate("CREATE TABLE TEST_TAB_2 (c21 int not null)");
            s.executeUpdate("INSERT INTO TEST_TAB_2 VALUES(1),(2)");
            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
                    "ADD CONSTRAINT TEST_TAB_2_PK_1 "+
                    "PRIMARY KEY (c21)");
            stats.assertTableStats("TEST_TAB_2",1);
            //DERBY-5702 Add a foreign key constraint and now we should find 2 rows
            // of statistics for TEST_TAB_2 - 1 for primary key and other for
            // foreign key constraint
            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
                    "ADD CONSTRAINT TEST_TAB_2_FK_1 "+
                    "FOREIGN KEY(c21) REFERENCES TEST_TAB_1(c11)");
            //DERBY-5702 Like primary key earlier, adding foreign key constraint
            // didn't automatically add a statistics row for it. Have to run update
            // statistics manually to get a row added for it's stat
            stats.assertTableStats("TEST_TAB_2",1);
            //Need to do a compress table to create the statistics for foreign
            // key constraint. Update statisitcs procedure is only available
            // in 10.5 and upwards and hence can't use that procedure here
            // since we are testing older releases too.
            s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
            //s.execute("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','TEST_TAB_2',1)");
            stats.assertTableStats("TEST_TAB_2",2);
            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
                    "DROP CONSTRAINT TEST_TAB_2_FK_1");
            //Dropping the foreign key constraint does not remove it's 
            // statistics row because of DERBY-5681.
            stats.assertTableStats("TEST_TAB_2",2);
            assertStatementError("42Y03", s,
            "CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
            break;


        case PH_SOFT_UPGRADE:
        case PH_POST_SOFT_UPGRADE:
            assertStatementError("42Y03", s,
                       "CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
            break;


        case PH_HARD_UPGRADE:
            stats.assertTableStats("TEST_TAB_2",2);
            s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
            stats.assertNoStatsTable("TEST_TAB_2");
            s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
            stats.assertNoStatsTable("TEST_TAB_2");
            break;


        case PH_POST_HARD_UPGRADE:
            //Make sure that the new procedure is still available
            s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

                ")");


        // Insert data
        insertData(con);


        IndexStatsUtil stats = new IndexStatsUtil(con);
        // Add constraints
        stmt.executeUpdate("alter table " + pktbl + " add constraint " +
                "PK_TWOCOL_PKTAB primary key (id1, id2)");
        stats.getStatsTable(pktbl, 2);
        stmt.executeUpdate("alter table " + fktbl + " add constraint " +
                "PK_FKTAB primary key (id)");
        stats.getStatsTable(fktbl, 1);
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "PK_MAIN primary key (id)");
        stats.getStatsTable(tbl, 1);
        stmt.executeUpdate("create index DUPS_MAIN on " + tbl + "(nonunique)");
        stats.getStatsTable(tbl, 2);
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "FKS_MAIN foreign key (fk_self) references " + tbl + "(id)");
        stats.getStatsTable(tbl, 3);
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "FKSNN_MAIN foreign key (fk_self_notnull) references " +
                tbl + "(id)");
        stats.getStatsTable(tbl, 4);


        int preFkAddition = stats.getStatsTable(tbl).length;
        // This doesn't trigger DERBY-5681.
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "fk_to_be_dropped foreign key (fk_dropped) " +
                "references " + fktbl + "(id)");
        Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition +1);
        stmt.executeUpdate("alter table " + tbl + " drop constraint " +
                "fk_to_be_dropped");
        Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition);


        // Trigger DERBY-5681.
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "fk_on_pk foreign key (id) " +
                "references " + fktbl + "(id)");
        stmt.executeUpdate("call syscs_util.syscs_update_statistics(" +
                "'APP', '" + tbl + "', null)");
        Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition +1);
        stmt.executeUpdate("alter table " + tbl + " drop constraint " +
                "fk_on_pk");
        // Derby failed to drop the statistics when the constraint got dropped.
        Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition +1);


        // Do an assert, but since we may be run with both old and new
        // releases allow for two cases.
        Assert.assertEquals(
                getNumTotalPossibleStats(), getAllRelevantStats(null));

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

     */
    private int getAllRelevantStats(List list)
            throws SQLException {
        boolean oldAutoCommitValue = con.getAutoCommit();
        con.setAutoCommit(true);
        IndexStatsUtil stats = new IndexStatsUtil(con);
        String[] tables = getTableNames();
        int count = 0;
        for (int i=0; i < tables.length; i++) {
            IndexStatsUtil.IdxStats[] entries = stats.getStatsTable(tables[i]);
            if (list != null) {
                list.addAll(Arrays.asList(entries));
            }
            count += entries.length;
        }
        stats.release(false);
        con.setAutoCommit(oldAutoCommitValue);
        return count;
    }

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

            throws SQLException {
        // DERBY-5097: On machines with a single core/CPU the load generated
        // by the test threads may cause the index statistics daemon worker
        // thread to be "starved". Add a timeout to give it a chance to do
        // what it has been told to do.
        IndexStatsUtil stats = new IndexStatsUtil(getConnection(), 5000);
        IdxStats[] myStats = stats.getStatsTable(TAB, 2);
        for (int i=0; i < myStats.length; i++) {
            IdxStats s = myStats[i];
            assertEquals(_100K, s.rows);
            switch (s.lcols) {
                case 1:

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

    }


    /** Runs the real test case. */
    private void assertOnSCUI(boolean keepDisposable)
            throws SQLException {
        IndexStatsUtil stats = new IndexStatsUtil(
                openDefaultConnection(), 20*1000); // 20 seconds timeout
        // Create table.
        String TAB = "STAT_SCUI";
        dropTable(TAB);
        Statement stmt = createStatement();
        stmt.executeUpdate("create table " + TAB +
                " (id int not null, val int)");
        stats.assertNoStatsTable(TAB);
        PreparedStatement psIns = prepareStatement(
                "insert into " + TAB + " values (?,?)");
        setAutoCommit(false);
        for (int i=0; i < 20; i++) {
            psIns.setInt(1, i);
            psIns.setInt(2, i);
            psIns.executeUpdate();
        }
        commit();
        setAutoCommit(true);
        stmt.executeUpdate("alter table " + TAB + " add constraint PK_" + TAB +
                " primary key(id)");
        stats.assertTableStats(TAB, keepDisposable ? 1  : 0);
        stmt.executeUpdate(
                "create unique index UNIQ_IDX_" + TAB + " ON " + TAB + "(val)");
        stats.assertTableStats(TAB, keepDisposable ? 2  : 0);
        PreparedStatement ps = prepareStatement(
                "call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', ?, ?)");
        // Update stats for all indexes.
        ps.setString(1, TAB);
        ps.setNull(2, Types.VARCHAR);
        ps.execute();
        stats.assertTableStats(TAB, keepDisposable ? 2  : 0);


        // Update stats for one specific index.
        ps.setString(2, "UNIQ_IDX_" + TAB);
        ps.execute();
        stats.assertTableStats(TAB, keepDisposable ? 2  : 0);


        // Drop statistics.
        stmt.execute("call SYSCS_UTIL.SYSCS_DROP_STATISTICS(" +
                "'APP', '" + TAB + "', null)");
        stats.assertNoStatsTable(TAB);


        // Update and assert again, this time in the reverse order.
        ps.execute();
        stats.assertTableStats(TAB, keepDisposable ? 1  : 0);
        ps.setNull(2, Types.VARCHAR);
        ps.execute();
        stats.assertTableStats(TAB, keepDisposable ? 2  : 0);
        IndexStatsUtil.IdxStats[] oldStats = stats.getStatsTable(TAB);
        
        // Insert more data to trigger an automatic update
        setAutoCommit(false);
        for (int i=20; i < 2000; i++) {
            psIns.setInt(1, i);
            psIns.setInt(2, i);
            psIns.executeUpdate();
        }
        commit();
        setAutoCommit(true);
        // Trigger the scheduling logic to get the istat daemon going
        prepareStatement("select * from " + TAB + " where id = ?"); 
        IndexStatsUtil.IdxStats[] newStats =
                stats.getNewStatsTable(TAB, oldStats);
        assertEquals(oldStats.length, newStats.length);
        for (int i=0; i < oldStats.length; i++) {
            assertEquals(keepDisposable, newStats[i].after(oldStats[i]));
        }


        // Cleanup
        dropTable(TAB);
        stats.release();
    }

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

    public void setUp()
            throws SQLException {
        if (stats != null) {
            stats.release();
        }
        stats = new IndexStatsUtil(openDefaultConnection(), DEFAULT_TIMEOUT);
    }

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

        PreparedStatement ps = con.prepareStatement(
                "select * from " + TAB + " where id = ?");
        ps.close();


        // Get statistics for the non-unique index.
        IdxStats[] myStats = new IndexStatsUtil(
                ds.getConnection(), DEFAULT_TIMEOUT).getStatsTable(TAB, 1);
        assertEquals(1, myStats.length);
        assertTrue(myStats[0].rows == 300);


        // Shutdown database and try to delete it.

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

        Statement stmt = con.createStatement();
        stmt.executeUpdate("drop table " + TAB1);
        stmt.close();


        // Trigger stats update on secondary table.
        IndexStatsUtil myStats =
                new IndexStatsUtil(ds.getConnection(), DEFAULT_TIMEOUT);
        myStats.assertNoStatsTable(TAB2);
        con.prepareStatement("select * from " + TAB2 + " where id = ?");
        myStats.assertTableStats(TAB2, 1);
        myStats.release();


        // Shutdown, then delete database directory.
        JDBCDataSource.shutdownDatabase(ds);
        assertDirectoryDeleted(constructDbPath(db));
    }

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

        stmt.executeUpdate("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', '" +
                TAB1 + "', 0)");
        stmt.close();
        // There should still be a statistics object written during the
        // compress operation.
        IndexStatsUtil myStats =
                new IndexStatsUtil(ds.getConnection(), DEFAULT_TIMEOUT);
        myStats.assertTableStats(TAB1, 1);


        // Trigger stats update on secondary table, make sure the daemon can
        // still process work.
        myStats.assertNoStatsTable(TAB2);
        con.prepareStatement("select * from " + TAB2 + " where id = ?");
        myStats.assertTableStats(TAB2, 1);
        myStats.release();
    }

View Full Code Here

Examples of org.apache.derbyTesting.junit.IndexStatsUtil

     * @throws SQLException if creating/populating the table fails
     */
    private void createAndInsertSimple(Connection con, String table, int rows)
            throws SQLException {
        Statement s;
        IndexStatsUtil myStats;
        if (con == null) {
            con = getConnection();
            s = createStatement();
            myStats = stats;
        } else {
            s = con.createStatement();
            myStats = new IndexStatsUtil(con);
        }
        // See if the table exists, and if so, drop it.
        dropIfExists(con, table);
        // Create table.
        s.executeUpdate(
                "create table " + table + "(id int primary key, val int)");
        s.executeUpdate("create index NON_UNIQUE_INDEX_" + table + " on " +
                table + "(val)");


        myStats.assertNoStatsTable(table);


        // Insert data
        long start = System.currentTimeMillis();
        println("created " + table + ", inserting " + rows + " rows");
        insertSimple(con, table, rows, 0);
        println("completed in " + (System.currentTimeMillis() - start) + " ms");
        myStats.assertNoStatsTable(table);
    }

View Full Code Here

0 1 2 3 4 5

TOP

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.