db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hans Müller" <hansmueller...@gmx.de>
Subject Re: Problems with SQL-Queries if the database is stored in a jar
Date Tue, 12 Apr 2011 06:54:38 GMT
Hi Knut,

thank you for your answer.

Your trick with the "--DERBY-PROPERTIES joinStrategy = NESTEDLOOP" seems to be work well.

Should I wrote a bug-report?
Or is this problem well-known? (Because of DERBY-2354)

Yours sincerely
hansmueller



-------- Original-Nachricht --------
> Datum: Mon, 11 Apr 2011 11:33:27 +0200
> Von: Knut Anders Hatlen <knut.hatlen@oracle.com>
> An: derby-user@db.apache.org
> Betreff: Re: Problems with SQL-Queries if the database is stored in a jar

> "Hans Müller" <hansmueller461@gmx.de> writes:
> 
> > Hello,
> >
> > i have a problem with a database which is stored a jar.
> >
> > The SQL-Queries work well, when the database in not inside a jar.
> > But if i store the database in a jar, then some SQL-Queries do not work
> and throw an exception.
> >
> > I have set the properties for the derby.storage.tempDirectory, befor i
> am loading the EmbeddedDriver of Derby.
> >
> > The database is large. It contains some lists with more than 1500, 3400,
> 3900, 5500 and 8000 lines.
> >
> > This are the Exceptions:
> >
> > java.sql.SQLException: Ein Feature ist nicht implementiert.
> > at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.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.EmbedStatement.execute(Unknown Source)
> > at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown
> Source)
> > at
> Datenbankanbindung.mAbfrageElementSonstiges(Datenbankanbindung.java:8758)
> > ...
> >
> > and
> >
> > Caused by: java.sql.SQLException: Ein Feature ist nicht implementiert.
> > at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> > at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
> > ... 14 more
> > Caused by: ERROR XSAI3: Ein Feature ist nicht implementiert.
> > at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> > at org.apache.derby.impl.store.raw.log.ReadOnly.checkVersion(Unknown
> Source)
> > at org.apache.derby.impl.store.raw.RawStore.checkVersion(Unknown Source)
> > at
> org.apache.derby.impl.store.access.RAMTransaction.checkVersion(Unknown Source)
> > at
> org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(Unknown
Source)
> > at
> org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(Unknown Source)
> > at org.apache.derby.iapi.store.access.DiskHashtable.<init>(Unknown
> Source)
> > at
> org.apache.derby.iapi.store.access.BackingStoreHashtable.spillToDisk(Unknown Source)
> > at
> org.apache.derby.iapi.store.access.BackingStoreHashtable.add_row_to_hash_table(Unknown
Source)
> > at
> org.apache.derby.iapi.store.access.BackingStoreHashtable.putRow(Unknown Source)
> > at
> org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown
Source)
> > at
> org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchSet(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.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.executeStmt(Unknown Source)
> > at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
> Source)
> > ... 8 more
> >
> >
> > This is an SQL-Querie, which works well with a database which is not
> stored in a jar and which throw an exception, if you store the database in a
> jar:
> >
> > SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment 
> > FROM ArtikelTab INNER JOIN SpracheTab ON ArtikelTab.ArtikelNr =
> SpracheTab.IdentifikationsNr INNER JOIN FassadenartenGruppeTab ON
> ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr 
> > WHERE SpracheTab.Sprache = 'de' 
> > AND ArtikelTab.Baugruppe = 'asdf' 
> > AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL) 
> > AND ArtikelTab.Lage LIKE '%Vertikal%' 
> > AND FassadenartenGruppeTab.Fassadenart = 'xyz' 
> > AND ArtikelTab.Sortiment = 's' 
> > ORDER BY ArtikelNr
> >
> > If you put the "ORDER BY"-line away or if you use "ORDER BY
> IdentifikationsNr" then it works.
> > But it is not only a problem with the "ORDER BY". I have some other
> SQL-Queries which do not work too and who have no "ORDER BY".
> >
> > The following SQL-Querie works well:
> >
> > SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment 
> > FROM ArtikelTab INNER JOIN SpracheTab ON ArtikelTab.ArtikelNr =
> SpracheTab.IdentifikationsNr INNER JOIN FassadenartenGruppeTab ON
> ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr 
> > WHERE SpracheTab.Sprache = 'de' 
> > AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL) 
> > AND FassadenartenGruppeTab.Fassadenart = 'xyz' 
> > ORDER BY ArtikelNr
> >
> > It seems, that this problem is similar to the following:
> >
> http://old.nabble.com/ResultSet.next%28%29-throws-ERROR-XSAI3%3A-Feature-not-implemented.-td20957328.html
> > and
> > https://issues.apache.org/jira/browse/DERBY-2354
> >
> >
> > Could it be, that Derby can only handle little databases, if you put
> these in a jar (read-only mode)?
> > Is this a Bug?
> > How can i fix the problem?
> 
> Hi Hans,
> 
> It looks like the root cause of this problem is the same as DERBY-2354,
> that Derby isn't prepared for handling hash tables that spill to disk in
> read-only databases.
> 
> You may be able to work around this problem by using optimizer overrides
> to force Derby's optimizer to pick nested loop joins instead of hash
> joins. That should eliminate the use of hash scans and prevent this bug
> from getting triggered, I think. In the failing query above, you would
> need to do something like this:
> 
> SELECT ArtikelTab.ArtikelNr, SpracheTab.Text1, ArtikelTab.Sortiment 
> FROM ArtikelTab --DERBY-PROPERTIES joinStrategy = NESTEDLOOP
>   INNER JOIN SpracheTab --DERBY-PROPERTIES joinStrategy = NESTEDLOOP
>     ON ArtikelTab.ArtikelNr = SpracheTab.IdentifikationsNr
>   INNER JOIN FassadenartenGruppeTab --DERBY-PROPERTIES joinStrategy =
> NESTEDLOOP
>     ON ArtikelTab.ArtikelNr = FassadenartenGruppeTab.ArtikelNr 
> WHERE SpracheTab.Sprache = 'de' 
> AND ArtikelTab.Baugruppe = 'asdf' 
> AND (ArtikelTab.Systembreite = ? OR ArtikelTab.Systembreite IS NULL) 
> AND ArtikelTab.Lage LIKE '%Vertikal%' 
> AND FassadenartenGruppeTab.Fassadenart = 'xyz' 
> AND ArtikelTab.Sortiment = 's' 
> ORDER BY ArtikelNr
> 
> More on optimizer overrides can be found here:
> http://db.apache.org/derby/docs/10.7/tuning/ctunoptimzoverride.html
> 
> Hope this helps,
> 
> -- 
> Knut Anders

-- 
Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de

Mime
View raw message