Open Invitation to add a Question and Answer to Derby FAQs

• Key: DERBY-232
• Type: Task
• Status: In Progress
• Priority: Trivial
• Assignee: Jean T. Anderson
• Reporter: Jean T. Anderson
• Component/s: Web Site

Description

Add a comment to this Jira issue with the FAQ entry (both the question and the answer) you would like to be added to http://db.apache.org/derby/faq.html, and the assignee will add it. Setting the "Patch Available" flag is a great way to keep a new FAQ on the radar until committed, even if the text of the FAQ is in a comment instead of in an actual patch.

Indicate in your comment if the FAQ belongs in the "Developing Derby" or "Using Derby" section.

Comments

[Jean T. Anderson]

Here's a question for the "Using Derby" section:

When will Derby have its own client JDBC driver?

Answer:
A Derby client driver was contributed and the vote to accept it was passed on April 26; see http://mail-archives.apache.org/mod_mbox/db-derby-dev/200504.mbox/[e-mail]%3e . The Derby 10.2.2 snapshot jar posted on April 29 (see http://mail-archives.apache.org/mod_mbox/db-derby-user/200504.mbox/[e-mail]%3e,
) includes the Derby client and is available for download from http://incubator.apache.org/derby/derby_downloads.html .

[Stan Bradbury]

Proposed FAQ for addition to the website document. This was posted on the developerWorks forum May 23, 2005:

   ?? Question:
from what I've read (PDF docs), it seems Cloudscape is not suitable when a program wants to execute a transaction spanning two Cloudscape databases distributed accross (remotely located) JVMs. Did anybody try this before? If it is not possible, is there any workaround, possibly in conjunction with other tools?

The usual scenario would be to use the EmbeddedXADataSource (probably JNDI) and to register a XAResource obtained from the XADataSouce/XAConnection


   !! Answer:
XA support using Network Server will allow you to do this. This functionality has been added to the current Derby codeline. You can test drive the functionality using the Derby 10.1 alpha snapshot available at:
http://incubator.apache.org/derby/derby_downloads.html#Snapshot+Jars

There is an example of establishing an XA connection in the recent build of the Admin and Sever Guide at:
http://incubator.apache.org/derby/docs/adminguide/cadminapps811478.html

[Stan Bradbury]

From: developerWorks forum - May 13, 2005
PROPOSED FAQ for addition to the website document.

   ?? Question:
I understand that Cloudscape is not supported on JDK 1.5.

Is there any information about recognised problems or limitations?

So far I have found 2 issues specific to 1.5 on the Apache site concerning Decimal conversions (both resolved):
http://issues.apache.org/jira/browse/DERBY-83
http://issues.apache.org/jira/browse/DERBY-74

Does anyone know of any more or have any experience, particularly if you regretted trying it?


   !! Answer:
The two JIRA issues you list are fixed in the Derby 10.1 codeline. A full suite of tests run weekly on JDK 1.5 and have not uncovered problems.

Added Stan Bradbury's May 26 FAQ contributions -- committed revision 178719. Stan, I shortened these a bit for the Derby FAQ page; please let me know if I messed up the meaning of anything.

[Sunitha Kambhampati]

Q.Are there any tips to make derby go faster ?
1)Use PreparedStatement with dynamic markers('?') instead of Statement. This will be faster since the statement does not need to be recompiled every time
http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705
http://incubator.apache.org/derby/manuals/tuning/perf34.html#IDX438

2) Avoid inserts in autocommit mode if possible:
Inserts can be painfully slow in autocommit mode. The reason is that each commit involves a flush of the log to the disk for each insert statement. The commit will not return until a physical disk write has been executed.
So to speed things up, run in autocommit false mode and execute a number of inserts in one transaction and then explicitly issue a commit.

[Jean T. Anderson]

Added Sunitha Kambhampati's May 31 FAQ contribution on performance tips for coding applications, revision 179270 (thanks for the contribution!). I made one minor change to the URLs, inserting docs/10.0 for the full reference to the 10.0 docs. New FAQ now live at http://incubator.apache.org/derby/faq.html#derby_faster .

[Sunitha Kambhampati]

Q. I get a lock timeout error, how do I debug this ?
To debug lock timeout errors, set derby.locks.monitor=true and derby.locks.deadlockTrace=true in derby.properties , this will print the lock table information to the derby.log when the timeout happens. If you also set the derby.language.logStatementText=true , then all the statement executed on the system will be written out to the derby.log. Statement information also includes the transaction id on which the statement is being executed. Using the transaction id that is holding the lock in the lock table dump if you do a reverse search for the transaction id in derby.log, it is possible to narrow down which statements may be holding the locks.

----
This should probably go in section - using derby..

[Jean T. Anderson]

Added Sunitha Kambhampati's June 14 FAQ on how to debug lock timeout errors, revision 190670 -- thanks for the contribution! The new FAQ is live at http://incubator.apache.org/derby/faq.html#debug_lock_timeout

[John H. Embretsen]

New FAQ proposal, in the "Using Derby: Getting Started" section:

------

Q:
How do I find out which version of Derby I am using?

A:
You can use the sysinfo tool or the JDBC API to get version information from your Derby installation. See the VersionInfo wiki page (http://wiki.apache.org/db-derby/VersionInfo) for more details.

------

Note:
The wiki page also includes info on how to get hints about which JDBC version your VM supports. Did not include this in the FAQ, but feel free to do so if desired.
Also, I don't know an easy way of determining which version a specific database (format) is; only Derby software versions (drivers/network server). Please add information to the wiki or the FAQ if you know more.

[Jean T. Anderson]

Committed John's Sep 6 suggestion in rev 580757 (thanks, John!).

[Paulo]

Why Derby doesn't implements an offset ??
There's a workaround solution to paginate in the database??
thanks

[John H. Embretsen]

I'd like to update the FAQ answer for the question "Does Derby support a LIMIT command?" to include information about the new ROW_NUMBER function. Forrest/Website diff is attached (limit-rownumber.diff).

My proposed new text is included below. Let me know if there are any issues with it. I plan to commit and do the update within a day or two.

The patch includes links to the following web pages:
 1: http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/[e-mail]%3e
 2: http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html?page=1
 3: http://wiki.apache.org/db-derby/OLAPRowNumber
 4: manuals/index.html

I had to use some less than elegant workarounds in HTML to be able to present examples with the desired formatting on multiple lines with Forrest, so let me know if you have better solutions for this.

--- --- ---

Derby supports limiting the number of rows returned by a query through
JDBC [1]. For example, to fetch the first 5 rows of a large table:

Statement stmt = con.createStatement();
stmt.setMaxRows(5);
stmt.execute("SELECT * FROM myLargeTable");

Some related tuning tips are available in this external article [2].

Starting with the 10.4.1.3 release Derby also supports limiting the
number of rows using the ROW_NUMBER function.

For example, to fetch the first 5 rows of a large table:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
    FROM myLargeTable
) AS tmp
WHERE rownum <= 5;

The ROW_NUMBER function can also be used to select a limited number
of rows starting with an offset, for example:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
    FROM myLargeTable
) AS tmp
WHERE rownum > 200000 AND rownum <= 200005;

For more information, refer to the ROW_NUMBER built-in function in
the Derby Reference Manual (available from the Documentation page [4]).
Development notes are available on the OLAPRowNumber wiki page [3].

The LIMIT keyword is not defined in the SQL standard, and is currently
not supported.

--- --- ---

[John H. Embretsen]

Committed limit-rownumber.diff (revision 654105) with one minor modification: Changed
stmt.execute("SELECT * FROM myLargeTable");
to
ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable");

[John H. Embretsen]

Added a link to http://wiki.apache.org/db-derby/LockDebugging for FAQ 5.4 ("I get a lock timeout error. How do I debug this?") with revision 654112.

[hallenzzz]

Can I get the UNIQUE CONSTRAINT name by java.sql.DatabaseMetaData?
Just like I get PRIMARY KEY name by java.sql.DatabaseMetaData.getPrimaryKeys and get FOREIGN KEY name by java.sql.DatabaseMetaData.getImportedKeys.
And can I get the UNIQUE CONSTRAINT name by any JAVA API?