db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sai Pullabhotla <Sai.Pullabho...@jMethods.com>
Subject Issue with SYSCS_UTIL.SYSCS_COMPRESS_ TABLE
Date Fri, 05 Jun 2009 18:01:36 GMT

Hello,

Our application using Derby 10.4 (in embedded mode) has a background process
that runs the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure periodically. We
have been reported the following error while this procedure is being
executed and a SELECT statement came in at the same time:

	com.linoma.dpa.dao.DAOException: The conglomerate (71,409) requested does
not exist.
	com.linoma.dpa.runtime.JobFailedException: com.linoma.dpa.dao.DAOException:
The conglomerate (71,409) requested does not exist.
	    at com.linoma.dpa.runtime.Job.checkLogin(Unknown Source)
	    at com.linoma.dpa.runtime.Job.run(Unknown Source)
	    at com.linoma.dpa.runtime.Runtime.executeProject(Unknown Source)
	    at com.linoma.dpa.runtime.Runtime.executeProject(Unknown Source)
	    at com.linoma.dpa.j2ee.RunProjectCommandServlet.doPost(Unknown Source)
	    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	    at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	    at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	    at com.linoma.dpa.security.SecurityFilter.doFilter(Unknown Source)
	    at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	    at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	    at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	    at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	    at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	    at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	    at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	    at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
	    at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
	    at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
	    at
org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	    at java.lang.Thread.run(Thread.java:810)
	Caused by: com.linoma.dpa.security.LoginException:
com.linoma.dpa.dao.DAOException: The conglomerate (71,409) requested does
not exist.
	    at com.linoma.dpa.security.LoginUtilities.getUserDetails(Unknown
Source)
	    ... 22 more
	Caused by: com.linoma.dpa.dao.DAOException: The conglomerate (71,409)
requested does not exist.
	    at com.linoma.dpa.dao.rdbms.UserDAOImpl.getConsolidatedRoles(Unknown
Source)
	    at com.linoma.dpa.dao.rdbms.UserDAOImpl.findUser(Unknown Source)
	    ... 23 more
	Caused by: java.sql.SQLException: The conglomerate (71,409) requested does
not exist.
	    at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
	    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
	    at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
	    at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
	    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
	    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
	    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
Source)
	    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
Source)
	    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown
Source)
	    at
org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	    ... 25 more
	Caused by: ERROR XSAI2: The conglomerate (71,409) requested does not exist.
	    at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
	    at
org.apache.derby.impl.store.access.btree.index.B2IFactory.readConglomerate(Unknown
Source)
	    at
org.apache.derby.impl.store.access.RAMAccessManager.conglomCacheFind(Unknown
Source)
	    at
org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(Unknown
Source)
	    at org.apache.derby.impl.store.access.RAMTransaction.openScan(Unknown
Source)
	    at
org.apache.derby.impl.store.access.BackingStoreHashTableFromScan.<init>(Unknown
Source)
	    at
org.apache.derby.impl.store.access.RAMTransaction.createBackingStoreHashtableFromScan(Unknown
Source)
	    at org.apache.derby.impl.sql.execute.HashScanResultSet.openCore(Unknown
Source)
	    at org.apache.derby.impl.sql.execute.JoinResultSet.openRight(Unknown
Source)
	    at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown
Source)
	    at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown
Source)
	    at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
Source)
	    at
org.apache.derby.impl.sql.execute.UnionResultSet.getNextRowCore(Unknown
Source)
	    at
org.apache.derby.impl.sql.execute.SortResultSet.getRowFromResultSet(Unknown
Source)
	    at
org.apache.derby.impl.sql.execute.SortResultSet.getNextRowFromRS(Unknown
Source)
	    at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(Unknown
Source)
	    at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown
Source)
	    at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown
Source)
	    at
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown
Source)
	    at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
Source)
	    ... 29 more

I'm not sure if this is expected or not. I would like to know if it is (or
is not) recommended to run the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure
during the normal operation of an application. What kind of locks are
acquired during the execution of this procedure? The documentation says -

This procedure acquires an exclusive table lock on the table being
compressed.
All statement plans dependent on the table or its indexes are invalidated.
(I'm not sure what exactly this means).

The important thing to note is - all other subsequent SELECT queries after
the compression was done have also failed with the same exact error
referring to same conglomerate. We had to restart the database/application
to fix the issue. Is this something expected too? Or is this something to do
with the state of my connection in the connection pool.

We perform the following procedure to compress all the tables:

        String sql = "select schemaname, tablename from sys.sysschemas s, "
            + "sys.systables t where s.schemaid=t.schemaid and
t.tabletype='T'";
        Statement stmt = conn.createStatement();
        ResultSet rs = null;
        CallableStatement cs = null;
        try {
            rs = stmt.executeQuery(sql);
            cs = conn.prepareCall("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?,
?, ?)");
            while (rs.next()) {
                String schema = rs.getString(1).trim();
                String table = rs.getString(2).trim();
                cs.setString(1, schema);
                cs.setString(2, table);
                cs.setShort(3, (short) 1);
                cs.execute();
            }
        }

Your help/feedback is greatly appreciated.

Thanks. 
-- 
View this message in context: http://www.nabble.com/Issue-with-SYSCS_UTIL.SYSCS_COMPRESS_-TABLE-tp23892893p23892893.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message