RE: Out-of-memory errors

[Peter Ondruška]

OutOfMemoryError: GC overhead limit exceeded happens when JVM spends way much 
garbage collecting. Could it be your heap is too small? One way to check if 
DateTimeFormat may be leaking memory is to run query without this function.

-Original Message-
From: John English  
Sent: Monday, February 8, 2021 10:28 AM
To: Derby Discussion 
Subject: Out-of-memory errors

In the last few days I've suddenly had a bunch of OOM exceptions. I'm using 
Derby 10.9.1.0, Oracle Java 1.8.0 on Ubuntu 64-bit, and haven't upgraded for a 
while (probably years, looking at those numbers).

The place where they happen is in a call to executeQuery() in a method which 
displays a view as a table. Analysing the heap dump for the latest one with the 
Eclipse memory analyser shows this:

One instance of "org.apache.derby.impl.store.access.sort.MergeInserter" 
loaded by "org.eclipse.jetty.webapp.WebAppClassLoader @ 0xf04231b0" 
occupies 134,841,800 (64.65%) bytes. The memory is accumulated in one instance 
of "org.apache.derby.impl.store.access.sort.SortBuffer", loaded by 
"org.eclipse.jetty.webapp.WebAppClassLoader @ 0xf04231b0", which occupies 
134,841,496 (64.65%) bytes.

One instance of "org.apache.derby.impl.services.cache.ConcurrentCache" 
loaded by "org.eclipse.jetty.webapp.WebAppClassLoader @ 0xf04231b0" 
occupies 43,766,832 (20.98%) bytes. The memory is accumulated in one instance 
of "org.apache.derby.impl.services.cache.ConcurrentCache",
loaded by "org.eclipse.jetty.webapp.WebAppClassLoader @ 0xf04231b0", which 
occupies 43,766,832 (20.98%) bytes.

The query itself was:

   SELECT DateTimeFormat(t_time,null) AS t_time,
  facility,event,details,name,username,sector,item
   FROM system_log_view
   ORDER BY time DESC
   NULLS LAST
   FETCH NEXT 20 ROWS ONLY

The view is nothing special except that t_time is a duplicate of the time 
column (the timestamp of the log entry) used to create a separate formatted 
copy for display purposes:

   CREATE VIEW system_log_view AS
 SELECT  time AS t_time,
 facility,
 event,
 details,
 name,
 username,
 sector,
 item,
 time
 FROMsystem_log;

The stack trace shows the error is occurring inside the call to DateTimeFormat, 
which is again nothing special:

   public static final String formatDateTime (Timestamp date, String
locale) {
 if (date == null) {
   return null;
 }
 else {
   String fmt = translate("d-MMM- 'at' HH:mm",locale);
   return translatePhrases(fmt.format(date),locale);
 }
   }

Here's the start of the stack trace:

java.sql.SQLException: The exception 'java.lang.OutOfMemoryError: GC overhead 
limit exceeded' was thrown while evaluating an expression.
   at java.text.DigitList.clone()Ljava/lang/Object; (DigitList.java:736)
   at java.text.DecimalFormat.clone()Ljava/lang/Object;
(DecimalFormat.java:2711)
   at java.text.SimpleDateFormat.initialize(Ljava/util/Locale;)V
(SimpleDateFormat.java:645)
   at
java.text.SimpleDateFormat.(Ljava/lang/String;Ljava/util/Locale;)V
(SimpleDateFormat.java:605)
   at java.text.SimpleDateFormat.(Ljava/lang/String;)V
(SimpleDateFormat.java:580)
   at
database.Functions.formatDateTime(Ljava/sql/Timestamp;Ljava/lang/String;)Ljava/lang/String;
(Functions.java:51)

Does anyone have any idea what might be happening, or what I can do to find out 
more?

TIA,
--
John English

RE: Slow mount times

[Peter Ondruška]

There is very easy way to check if you are going to run database recovery at 
boot time by looking into logs subfolder. If you gracefully shut down, there 
are only two log files. If there are more your database will perform roll 
forward recovery, in worst case applying all the log files.

From: Rick Hillegas 
Sent: Sunday, February 7, 2021 4:11 PM
To: Derby Discussion ; Alex O'Ree 

Subject: Re: Slow mount times

I don't know of any special trace flags for this. Maybe something will turn up 
in derby.log if you set the diagnostic logging level to its most verbose level 
by running the application with the following system property:


  -Dderby.stream.error.logSeverityLevel=0

Hope this helps,
-Rick

On 2/6/21 6:53 PM, Alex O'Ree wrote:

Thanks i'll give it a shot.

Is there any logging in derby that i can enable into regarding this?



On Sat, Feb 6, 2021 at 7:08 PM Rick Hillegas 


wrote:



The usual cause for this behavior is that the application was brought

down ungracefully, say via a control-c or by killing the window where it

was running. The engine then needs to reconstruct the state of the

database by replaying many recovery logs. To gracefully exit Derby, you

need to explicitly shutdown Derby as described here:

https://db.apache.org/derby/docs/10.15/devguide/tdevdvlp20349.html



On 2/6/21 3:39 PM, Alex O'Ree wrote:

Sometimes when my app starts, it can take several minutes to initialize

the

database. Is there a way to find out whats going on? There isn't much log

output. I have overridden derby.stream.error.method but other than the

the

startup message, I don't have much to go on.



Is there perhaps a startup database file check or something?

Re: I don't find the JDBC driver class in derbyclient.jar

[Peter Ondruška]

Hi, in default setup you do not need to use authentication at all. However
if you do with APP user its default password is APP. This was for 10.14 and
I guess the same applies to 10.15. Peter

On Thu, 5 Mar 2020, 10:25 Richard Grin, 
wrote:

> Thanks a lot Rick. I put these 3 files in the lib directory of Payara and
> everything is working now.
>
> In the meanwhile I found the page
> http://db.apache.org/derby/docs/10.15/getstart/twwdactivity4.html but it
> does not mention the file derbytools.jar; is it a mistake?
>
> I am not used to Derby and I had a problem to solve with the default name
> and password to give (APP) in order to connect my Web application to Derby.
> I solved it by guessing the "APP" values to put. Where can I find this
> information in the documentation?
>
> Richard
> Le 05/03/2020 à 00:53, Rick Hillegas a écrit :
>
> Hey Richard,
>
> The drivers moved into derbytools.jar as part of the JPMS modularization
> work introduced by the previous feature release (10.15.1.3). In addition to
> derbyclient.jar, you will need to put derbyshared.jar and derbytools.jar on
> your client-side classpath or modulepath. Please see the corresponding
> release notes at
> http://db.apache.org/derby/releases/release-10.15.1.3.html
>
> Hope this helps,
> -Rick
>
> On 3/4/20 11:10 AM, Richard Grin wrote:
>
> Hi,
>
> I have just started using Derby (version 10.15.2.0) today.
>
> I would like to use it with Payara server so I have to put the JDBC
> driver in Payara. Payara can't find the class
> org.apache.derby.jdbc.ClientDriver. I looked for this class in the
> derbyclient.jar file but I couldn't find it.
>
> What's my mistake? Has the driver class changed? Is the class in another
> file?
>
> I have read articles about JDBC driver for old versions of Derby Network
> server but I can't find the information for the 10.15.2.0 version.
>
> Richard
>
>
>

Re: Avoid locking on DELETE

[Peter Ondruška]

You would need to test various scenarios. First I would propose larger
batch size (N thousands of rows). Are you sure you execute deletes in
batches? You should have autocommit off, execute N times delete statement,
commit, repeat. Pseudo code (I am on mobile phone):

1. Acquire connection
2. Set connection autocommit to false
3. Create prepared statement with delete, DELETE FROM WHERE primary key = ?
4. Create set of primary keys to be deleted
5. Iterate set (4.) with adding those keys to delete statement (3.) as batch
6. When you reach batch size or end of key set execute batch and commit,
continue (5.)

In my case with slow disk this really performs better and should avoid your
issue as well.

Peter

On Mon, 7 Oct 2019, 22:11 Peter,  wrote:

> Hi Peter,
>
> Thanks! I have implemented this and indeed the maximum delays are lower
> but the time for a delete batch to complete takes now longer (roughly 3-4
> times; for batchSize=500, total deleted items around ~1). The problem
> is likely that I have VARCHAR for the ID column.
>
> If I increase the frequency of issuing the original DELETE statement:
>
> DELETE FROM mytable WHERE created_at < ?
>
> Won't it have a similar effect due to smaller batches?
>
> Regards
> Peter
>
> On 07.10.19 16:31, Peter Ondruška wrote:
>
> In my case I have two separate steps. First SELECT primary keys of those
> records to be deleted (in your case SELECT id FROM mytable WHERE created_at
> < some_fixed_millis). And then I issue DELETE for those primary keys in
> batches of N statements (N being configuration parameter). You could create
> stored procedure for this with two parameters (some_fixed_millis,
> batch_size).
> Your idea DELETE WHERE SELECT and limiting rows needs to be run for every
> DELETE step making unnecessary read I/O.
>
>
> On Mon, 7 Oct 2019 at 14:10, Peter  wrote:
>
>> Hi Peter,
>>
>> Thanks a lot for the suggestion.This would be nice if it performs better.
>>
>> Is the idea to split one request into smaller parts or will
>> "Select+Delete IDs" just perform better?
>>
>> And regarding the latter option - is this possible in one SQL request? So
>> something like
>>
>> DELETE FROM mytable WHERE id IN
>>
>> ( SELECT id FROM mytable WHERE created_at < some_fixed_millis OFFSET 0 ROWS 
>> FETCH NEXT 1000 ROWS ONLY )
>>
>>
>> And then loop through the results via changing OFFSET and ROWS? (Btw: the
>> column created_at is indexed)
>>
>> Or would you recommend doing this as 2 separate statements in Java/JDBC?
>> Or via maybe even just issuing the original DELETE request more frequent?
>>
>> Regards
>> Peter
>>
>> On 06.10.19 03:50, Peter Ondruška wrote:
>>
>> Peter, try this if it makes a difference:
>>
>> 1. Select entries to be deleted, note their primary keys.
>> 2. Issue delete using keys to be deleted (1.) and use short transaction
>> batches.
>>
>> On Sun, 6 Oct 2019, 01:33 Peter,  wrote:
>>
>>> Hi,
>>>
>>> I have a table "mytable" with columns "id", "created_at" and "json"
>>> (VARCHAR, BIGINT, LONG VARCHAR), where data is coming in like new 200k
>>> entries every hour and I would like to keep only entries of the last 1
>>> or 2 hours. It is expected behaviour for the user if too old entries
>>> gets lost as it is some kind of a LRU cache.
>>>
>>> The current solution is to delete entries older than 4 hours every 30
>>> minutes:
>>>
>>> DELETE FROM mytable WHERE created_at < ?
>>>
>>> I'm using this in a prepared statement where ? is "4 hours ago" in
>>> milliseconds (new DateTime().getMillis()).
>>>
>>> This works, but some (not all) INSERT statement get a bigger delay in
>>> the same order (2-5 seconds) that this DELETE takes, which is ugly.
>>> These INSERT statements are executed independently (using different
>>> threads) of the DELETE.
>>>
>>> Is there a better way? Can I somehow avoid locking the unrelated INSERT
>>> operations?
>>>
>>> What helps a bit is when I make those deletes more frequently than the
>>> delays will get smaller, but then the number of those delayed requests
>>> will increase.
>>>
>>> What also helps a bit (currently have not seen a negative impact) is
>>> increasing the page size for the Derby Network Server:
>>> -Dderby.storage.pageSize=32768
>>>
>>> Regards
>>> Peter
>>>
>>>
>>
>

Re: Avoid locking on DELETE

[Peter Ondruška]

In my case I have two separate steps. First SELECT primary keys of those
records to be deleted (in your case SELECT id FROM mytable WHERE created_at
< some_fixed_millis). And then I issue DELETE for those primary keys in
batches of N statements (N being configuration parameter). You could create
stored procedure for this with two parameters (some_fixed_millis,
batch_size).

Your idea DELETE WHERE SELECT and limiting rows needs to be run for every
DELETE step making unnecessary read I/O.


On Mon, 7 Oct 2019 at 14:10, Peter  wrote:

> Hi Peter,
>
> Thanks a lot for the suggestion.This would be nice if it performs better.
>
> Is the idea to split one request into smaller parts or will "Select+Delete
> IDs" just perform better?
>
> And regarding the latter option - is this possible in one SQL request? So
> something like
>
> DELETE FROM mytable WHERE id IN
>
> ( SELECT id FROM mytable WHERE created_at < some_fixed_millis OFFSET 0 ROWS 
> FETCH NEXT 1000 ROWS ONLY )
>
>
> And then loop through the results via changing OFFSET and ROWS? (Btw: the
> column created_at is indexed)
>
> Or would you recommend doing this as 2 separate statements in Java/JDBC?
> Or via maybe even just issuing the original DELETE request more frequent?
>
> Regards
> Peter
>
> On 06.10.19 03:50, Peter Ondruška wrote:
>
> Peter, try this if it makes a difference:
>
> 1. Select entries to be deleted, note their primary keys.
> 2. Issue delete using keys to be deleted (1.) and use short transaction
> batches.
>
> On Sun, 6 Oct 2019, 01:33 Peter,  wrote:
>
>> Hi,
>>
>> I have a table "mytable" with columns "id", "created_at" and "json"
>> (VARCHAR, BIGINT, LONG VARCHAR), where data is coming in like new 200k
>> entries every hour and I would like to keep only entries of the last 1
>> or 2 hours. It is expected behaviour for the user if too old entries
>> gets lost as it is some kind of a LRU cache.
>>
>> The current solution is to delete entries older than 4 hours every 30
>> minutes:
>>
>> DELETE FROM mytable WHERE created_at < ?
>>
>> I'm using this in a prepared statement where ? is "4 hours ago" in
>> milliseconds (new DateTime().getMillis()).
>>
>> This works, but some (not all) INSERT statement get a bigger delay in
>> the same order (2-5 seconds) that this DELETE takes, which is ugly.
>> These INSERT statements are executed independently (using different
>> threads) of the DELETE.
>>
>> Is there a better way? Can I somehow avoid locking the unrelated INSERT
>> operations?
>>
>> What helps a bit is when I make those deletes more frequently than the
>> delays will get smaller, but then the number of those delayed requests
>> will increase.
>>
>> What also helps a bit (currently have not seen a negative impact) is
>> increasing the page size for the Derby Network Server:
>> -Dderby.storage.pageSize=32768
>>
>> Regards
>> Peter
>>
>>
>

Re: Avoid locking on DELETE

[Peter Ondruška]

Peter, try this if it makes a difference:

1. Select entries to be deleted, note their primary keys.
2. Issue delete using keys to be deleted (1.) and use short transaction
batches.

On Sun, 6 Oct 2019, 01:33 Peter,  wrote:

> Hi,
>
> I have a table "mytable" with columns "id", "created_at" and "json"
> (VARCHAR, BIGINT, LONG VARCHAR), where data is coming in like new 200k
> entries every hour and I would like to keep only entries of the last 1
> or 2 hours. It is expected behaviour for the user if too old entries
> gets lost as it is some kind of a LRU cache.
>
> The current solution is to delete entries older than 4 hours every 30
> minutes:
>
> DELETE FROM mytable WHERE created_at < ?
>
> I'm using this in a prepared statement where ? is "4 hours ago" in
> milliseconds (new DateTime().getMillis()).
>
> This works, but some (not all) INSERT statement get a bigger delay in
> the same order (2-5 seconds) that this DELETE takes, which is ugly.
> These INSERT statements are executed independently (using different
> threads) of the DELETE.
>
> Is there a better way? Can I somehow avoid locking the unrelated INSERT
> operations?
>
> What helps a bit is when I make those deletes more frequently than the
> delays will get smaller, but then the number of those delayed requests
> will increase.
>
> What also helps a bit (currently have not seen a negative impact) is
> increasing the page size for the Derby Network Server:
> -Dderby.storage.pageSize=32768
>
> Regards
> Peter
>
>

Re: [derby] searching within a blob

[Peter Ondruška]

Alex, I think this may be a solution:
https://db.apache.org/derby/docs/10.14/tools/rtoolsoptlucene.html

On Mon, 30 Sep 2019 at 18:18, Alex O'Ree  wrote:

> I have a use case where i have string data stored in a blob and i want to
> perform a query similar to
>
> select * from table where column1 like '%hello world%'
>
> It doesn't look like this is possible with derby out of the box. Is there
> a way to create a function that calls a java function or something that can
> be used to make this work?
>

Re: Derby DB Encryption

[Peter Ondruška]

https://db.apache.org/derby/docs/10.14/security/cseccsecure88690.html

The default encryption algorithm is DES.

You can specify an encryption provider and/or encryption algorithm other
than the defaults by using the encryptionProvider=*providerName* and
encryptionAlgorithm=*algorithm* attributes.

On Thu, 25 Jul 2019, 01:13 Oskar Z,  wrote:

> Does anyone know what is the default encryption algorithm for the Derby DB
> encryption?
>
> Thanks,
> Oskar
>
> On Jul 24, 2019, at 6:14 PM, Oskar Z  wrote:
>
> Looks like the database when being encrypted, must be the FIRST connection
> to DB. If DB has existing connections before encryption, then it will not
> work, and thus the passwords don’t matter.
>
> If encryption is done as a first connection to DB, then the DB must be
> shutdown, and then it seems to work, and the first call should have
> bootPassword. That’s what I found.
>
> Thanks for the help and pointers! It got me thinking :-)
>
> Regards,
> Oskar
>
> On Jul 24, 2019, at 2:08 PM, Peter Ondruška 
> wrote:
>
> Well, you "boot" with bootPassword only once. After your database is
> opened you do not need to specify bootPassword anymore. Maybe even
> specifying incorrect bootPassword after database is already opened does not
> trigger any error and may seem misleading.
>
> On Wed, 24 Jul 2019 at 19:47, Oskar Zinger  wrote:
>
>> I already have authentication working fine. I would like to also have
>> data encryption.
>>
>> Can I have both authentication and data encryption in Derby?
>>
>> Sent from my iPhone
>>
>> On Jul 24, 2019, at 11:37 AM, Peter Ondruška 
>> wrote:
>>
>> Oskar, you mixed two distinct topics, encryption and authentication. You
>> should also follow
>> https://db.apache.org/derby/docs/10.14/security/cseccsecure42374.html.
>> Peter
>>
>> On Wed, 24 Jul 2019 at 16:27, Oskar Z  wrote:
>>
>>> Hello - hope that someone has experience with Derby encryption.
>>>
>>> I’ve been using this documentation:
>>> https://db.apache.org/derby/docs/10.14/security/cseccsecure97760.html
>>>
>>> I’m not sure what’s happening, I’ve encrypted the DB using
>>> dataEncryption=true and provided bootPassword, at first I used the same
>>> password as the Owner of DB, but then I used a different password. But
>>> regardless, what ever password I specify or not specify as bootPassword,
>>> the connection is made OK.
>>>
>>> So to me, it looks as if the DB has not been really encrypted, even
>>> though I’m not see any exceptions/errors in derby.log.
>>>
>>> How can I tell for sure that DB encryption really happened?
>>>
>>> Thanks for you help!
>>>
>>> Kind regards,
>>> Oskar
>>>
>>
>> kaibo, s.r.o., ID 28435036, registered with the commercial register
>> administered by the Municipal Court in Prague, section C, file 141269.
>> Registered office: Kališnická 379/10, Prague 3, 130 00, Czech Republic.
>> https://kaibo.eu
>>
>>
> kaibo, s.r.o., ID 28435036, registered with the commercial register
> administered by the Municipal Court in Prague, section C, file 141269.
> Registered office: Kališnická 379/10, Prague 3, 130 00, Czech Republic.
> https://kaibo.eu
>
>
>
>

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, file 141269.

Registered office: Kališnická 379/10, Prague 3, 130 00, Czech Republic.

https://kaibo.eu <https://kaibo.eu>

Re: Derby DB Encryption

[Peter Ondruška]

Well, you "boot" with bootPassword only once. After your database is opened
you do not need to specify bootPassword anymore. Maybe even specifying
incorrect bootPassword after database is already opened does not trigger
any error and may seem misleading.

On Wed, 24 Jul 2019 at 19:47, Oskar Zinger  wrote:

> I already have authentication working fine. I would like to also have data
> encryption.
>
> Can I have both authentication and data encryption in Derby?
>
> Sent from my iPhone
>
> On Jul 24, 2019, at 11:37 AM, Peter Ondruška 
> wrote:
>
> Oskar, you mixed two distinct topics, encryption and authentication. You
> should also follow
> https://db.apache.org/derby/docs/10.14/security/cseccsecure42374.html.
> Peter
>
> On Wed, 24 Jul 2019 at 16:27, Oskar Z  wrote:
>
>> Hello - hope that someone has experience with Derby encryption.
>>
>> I’ve been using this documentation:
>> https://db.apache.org/derby/docs/10.14/security/cseccsecure97760.html
>>
>> I’m not sure what’s happening, I’ve encrypted the DB using
>> dataEncryption=true and provided bootPassword, at first I used the same
>> password as the Owner of DB, but then I used a different password. But
>> regardless, what ever password I specify or not specify as bootPassword,
>> the connection is made OK.
>>
>> So to me, it looks as if the DB has not been really encrypted, even
>> though I’m not see any exceptions/errors in derby.log.
>>
>> How can I tell for sure that DB encryption really happened?
>>
>> Thanks for you help!
>>
>> Kind regards,
>> Oskar
>>
>
> kaibo, s.r.o., ID 28435036, registered with the commercial register
> administered by the Municipal Court in Prague, section C, file 141269.
> Registered office: Kališnická 379/10, Prague 3, 130 00, Czech Republic.
> https://kaibo.eu
>
>

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, file 141269.

Registered office: Kališnická 379/10, Prague 3, 130 00, Czech Republic.

https://kaibo.eu <https://kaibo.eu>

Re: Derby DB Encryption

[Peter Ondruška]

Oskar, you mixed two distinct topics, encryption and authentication. You
should also follow
https://db.apache.org/derby/docs/10.14/security/cseccsecure42374.html. Peter

On Wed, 24 Jul 2019 at 16:27, Oskar Z  wrote:

> Hello - hope that someone has experience with Derby encryption.
>
> I’ve been using this documentation:
> https://db.apache.org/derby/docs/10.14/security/cseccsecure97760.html
>
> I’m not sure what’s happening, I’ve encrypted the DB using
> dataEncryption=true and provided bootPassword, at first I used the same
> password as the Owner of DB, but then I used a different password. But
> regardless, what ever password I specify or not specify as bootPassword,
> the connection is made OK.
>
> So to me, it looks as if the DB has not been really encrypted, even though
> I’m not see any exceptions/errors in derby.log.
>
> How can I tell for sure that DB encryption really happened?
>
> Thanks for you help!
>
> Kind regards,
> Oskar
>

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, file 141269.

Registered office: Kališnická 379/10, Prague 3, 130 00, Czech Republic.

https://kaibo.eu 

Re: cannot make indexes on long varchar?

[Peter Ondruška]

Not true, you can index such columns but it does not make sense using
standard as Mikael explained. For such follow Using the luceneSupport
optional tool
<https://db.apache.org/derby/docs/10.14/tools/rtoolsoptlucene.html>.


On Sat, 1 Dec 2018 at 14:48, Alex O'Ree  wrote:

> Is there a particular reason we can't do indexes on long varchar columns?
>


-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, file 141269.

Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu 
<https://www.kaibo.eu>

Re: Installing Derby

[Peter Ondruška]

You have started derby from C:\ directory as non-privileged user and derby
engine is trying to create log file in this location which is not writable.

On Wed, 28 Nov 2018 at 04:07, Bob M  wrote:

> Hi
> Am following the instructions
> All going well until I attempt to create my first database
>
> I receive the following message
> ***
> C:\>java org.apache.derby.tools.ij
> ij version 10.14
> ij> connect 'jdbc:derby:myFirstDBase;create=true';
> Wed Nov 28 15:55:44 NZDT 2018 Thread[main,5,main]
> java.io.FileNotFoundException: derby.log (Access is denied)
> ***
> what have I forgotten to do ?
> Bob M
>
>
>
> --
> Sent from:
> http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
>
-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, file 141269.

Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu 
<https://www.kaibo.eu>

Re: Incremental Online Backup

[Peter Ondruška]

Depends what you mean by incremental. Should you want to minimize data
created by backup (and increase time to recover) enable log arching and
protect full database backup and log files created after that. There is no
such thing as backing up "incrementally" i.e. only those blocks that
changed since last backup.

I would recommend running with log archiving anyway and doing online backup
(SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE) frequently
enough (daily) avoiding applying many log files for recovery.

See https://db.apache.org/derby/docs/10.0/manuals/admin/hubprnt46.html and
documentation on the procedures used in that page.

p.

On 16 September 2017 at 14:32, Shreyans Jain <shreyans2...@gmail.com> wrote:

> Is it possible in any way (may be a workaround or hack) to do online
> incremental backup of anything (logs, data files etc) by which database can
> be restored using that backup.
>
> Regards,
> Shreyans Jain
>



-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Re: SYSCS_DIAG.TRANSACTION_TABLE stale records

[Peter Ondruška]

Dear Brett,

I did not mention but I use latest stable Derby (10.13). I have checked
with https://bitbucket.org/ondruska/xadbreco and no XA transactions are
reported.

p.

On 11 August 2017 at 14:47, Bergquist, Brett <bbergqu...@canoga.com> wrote:

> Sorry for the late response to this but I did want to comment.  We are
> using ClientXADataSource extensively with Glassfish.   Our transactions are
> correctly reported in the SYSCS_DIAG.TRANSACTION_TABLE.   The only time
> that they have stuck around is when the connection between Glassfish and
> the Derby Network Server has been severed before the XA “prepare” or
> “commit” phase has been reached or due to a XA transaction timeout bug in
> Derby which I fixed and supplied and is in the latest builds (10.10.2.0 is
> what I am using).
>
>
>
> Having the transaction stay around is of course the correct thing since XA
> is the distributed protocol and until prepare/commit/rollback has been
> performed, Derby (the XA resource) has no idea the state of the transaction.
>
>
>
> I think I would write a little program to lists the XA transactions that
> are still open and see if those reported by the
> SYSCS_DIAG.TRANSACTION_TABLE are not in fact real XA transactions that have
> not been finalized.
>
>
>
> *From:* Rick Hillegas [mailto:rick.hille...@gmail.com]
> *Sent:* Tuesday, July 11, 2017 8:56 PM
> *To:* derby-user@db.apache.org
> *Subject:* Re: SYSCS_DIAG.TRANSACTION_TABLE stale records
>
>
>
> Hi Peter,
>
> How are you disconnecting the sessions? I would expect to see 1
> transaction for every active session, as the following script demonstrates:
>
> -- 1 active session = 1 open transaction
> connect 'jdbc:derby:memory:db;create=true' as conn1;
> select count(*) from syscs_diag.transaction_table;
>
> -- 2 active sessions = 2 open transactions
> connect 'jdbc:derby:memory:db' as conn2;
> select count(*) from syscs_diag.transaction_table;
>
> -- 3 active sessions = 3 open transactions
> connect 'jdbc:derby:memory:db' as conn3;
> select count(*) from syscs_diag.transaction_table;
>
> -- 2 active sessions = 2 open transactions
> disconnect;
> set connection conn1;
> select count(*) from syscs_diag.transaction_table;
>
> -- 1 active session = 1 open transaction
> set connection conn2;
> disconnect;
> set connection conn1;
> select count(*) from syscs_diag.transaction_table;
>
> Thanks,
> -Rick
>
> On 7/11/17 10:10 AM, Peter Ondruška wrote:
>
> Dear all,
>
> the documentation mentions "The SYSCS_DIAG.TRANSACTION_TABLE diagnostic
> table shows *all of the transactions that are currently *in the
> database." Is it really correct? In my case I have an application server
> (Payara) connected to database with ClientXADataSource. Over time the
> record count in this table grows. When I stop application server and all
> database sessions are disconnected, record count stays with no change and I
> would expect that it drops as transactions are definitely closed. The only
> way to "clean" the diagnostic table is to restart database.
>
> All the records are same (different XID of course):
>
> XIDGLOBAL_XIDUSERNAMETYPESTATUSFIRST_INSTANT
> SQL_TEXT
> 79512765NULLAPPUserTransactionIDLENULLNULL
>
> except one SystemTransaction:
> XIDGLOBAL_XIDUSERNAMETYPESTATUSFIRST_INSTANT
> SQL_TEXT
> 79241843NULLNULLSystemTransactionIDLENULLNULL
>
> and one UserTransaction (as expected):
> XIDGLOBAL_XIDUSERNAMETYPESTATUSFIRST_INSTANT
> SQL_TEXT
> 79604720NULLAPPUserTransactionIDLENULLSELECT *
> FROM syscs_diag.transaction_table
>
> Regards,
>
>
> --
>
> Peter Ondruška
>
>
> kaibo, s.r.o., ID 28435036, registered with the commercial register
> administered by the Municipal Court in Prague, section C, insert 141269.
> Registered office and postal address: kaibo, s.r.o., Kališnická 379/10,
> Prague 3, 130 00, Czech Republic.
> https://www.kaibo.eu
>
>
>
> --
> Canoga Perkins
> 20600 Prairie Street
> Chatsworth, CA 91311
> (818) 718-6300
>
> This e-mail and any attached document(s) is confidential and is intended
> only for the review of the party to whom it is addressed. If you have
> received this transmission in error, please notify the sender immediately
> and discard the original message and any attachment(s).
>



-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Re: Derby Database Corruption Issues

[Peter Ondruška]

Hi,

This is still valid (and in general for all systems utilising logging).

Regards,
p.

On 12 July 2017 at 20:04, Shreyans Jain <shreyans2...@gmail.com> wrote:

> I was reading https://wiki.apache.org/db-derby/DatabaseCorruption and i
> found prevention of corruption which states
> "Switch off the machine's write caching" . now the article is written in
> 2013. Is it still application to derby database corruption or now it has no
> effect.
>
> Regards,
> Shreyans Jain
>



-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

SYSCS_DIAG.TRANSACTION_TABLE stale records

[Peter Ondruška]

Dear all,

the documentation mentions "The SYSCS_DIAG.TRANSACTION_TABLE diagnostic
table shows *all of the transactions that are currently *in the database."
Is it really correct? In my case I have an application server (Payara)
connected to database with ClientXADataSource. Over time the record count
in this table grows. When I stop application server and all database
sessions are disconnected, record count stays with no change and I would
expect that it drops as transactions are definitely closed. The only way to
"clean" the diagnostic table is to restart database.

All the records are same (different XID of course):

XIDGLOBAL_XIDUSERNAMETYPESTATUSFIRST_INSTANTSQL_TEXT
79512765NULLAPPUserTransactionIDLENULLNULL

except one SystemTransaction:
XIDGLOBAL_XIDUSERNAMETYPESTATUSFIRST_INSTANTSQL_TEXT
79241843NULLNULLSystemTransactionIDLENULLNULL

and one UserTransaction (as expected):
XIDGLOBAL_XIDUSERNAMETYPESTATUSFIRST_INSTANTSQL_TEXT
79604720NULLAPPUserTransactionIDLENULLSELECT * FROM
syscs_diag.transaction_table

Regards,

-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Re: Page cache sizing

[Peter Ondruška]

at
org.apache.derby.impl.store.raw.data.CachedPage.setPageArray(Unknown Source)
at org.apache.derby.impl.store.raw.data.CachedPage.readPage(Unknown
Source)
at
org.apache.derby.impl.store.raw.data.CachedPage.setIdentity(Unknown Source)
at
org.apache.derby.impl.services.cache.ConcurrentCache.find(Unknown Source)
at
org.apache.derby.impl.store.raw.data.FileContainer.getLatchedPage(Unknown
Source)
at
org.apache.derby.impl.store.raw.data.RAFContainer.backupContainer(Unknown
Source)
at
org.apache.derby.impl.store.raw.data.BaseContainerHandle.backupContainer(Unknown
Source)
at
org.apache.derby.impl.store.raw.data.BaseDataFileFactory.backupDataFiles(Unknown
Source)
at org.apache.derby.impl.store.raw.RawStore.backup(Unknown Source)
at org.apache.derby.impl.store.raw.RawStore.backup(Unknown Source)

Regards,
p.




On 7 July 2017 at 04:59, Bryan Pendleton <bpendleton.de...@gmail.com> wrote:

>
>
> how does derby.storage.pageCacheSize parameter (
>> https://db.apache.org/derby/docs/10.13/ref/rrefproper81359.html) work
>> with database that has multiple page sizes--tables with default 4096 bytes
>> and tables with long/blob of 32768 byte pages?
>>
>>
> Hi Peter,
>
> I'm not 100% sure how this works; I think you should run some experiments.
>
> Here's what I *think* the behavior is:
> 1) The page cache is sized in 4K pages, so if you set pageCacheSize=1024,
> you get 4 meg of page cache memory
> 2) Tables with 4K pages simply use the cache as you expect.
> 3) Tables with 32K pages chew up 8 cache "pages" at a time, each 32K chunk
> of page cache holding 1 32K page from that large-page table.
>
> Some stuff I found while searching around, which might give you some ideas
> for experiments you could run:
>
> http://apache-database.10148.n7.nabble.com/out-of-memory-
> when-writing-blobs-td100948.html
>
> and
>
> https://issues.apache.org/jira/browse/DERBY-4537
>
> Sorry I'm not of much more help here.
>
> bryan
>
>


-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Page cache sizing

[Peter Ondruška]

Dear all,

how does derby.storage.pageCacheSize parameter (
https://db.apache.org/derby/docs/10.13/ref/rrefproper81359.html) work with
database that has multiple page sizes--tables with default 4096 bytes and
tables with long/blob of 32768 byte pages?

Thanks,

-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

SYSCS_DIAG.ERROR_LOG_READER

[Peter Ondruška]

Dear all,

in 10.13.1.1 (not tested with older) SYSCS_DIAG.ERROR_LOG_READER does not
work with database booted in server with
-Dderby.stream.error.style=rollingFile.

There are derby.log and derby-0.log in home directory and derby-0.log has
error messages which I expect to be always the current log file when
rollingFile is used.

SELECT * FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER('derby-0.log')) AS T1;
DISCONNECT;
ij version 10.13
ij> ij> ij> ERROR 38000: The exception 'java.sql.SQLException: derby-0.log
(No such file or directory)' was thrown while evaluating an expression.
ERROR null: derby-0.log (No such file or directory)
ij> ij> Finished: SUCCESS

SELECT * FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER('derby.log')) AS T1;
DISCONNECT;
ij version 10.13
ij> ij> ij> ERROR 38000: The exception 'java.sql.SQLException: derby.log
(No such file or directory)' was thrown while evaluating an expression.
ERROR null: derby.log (No such file or directory)
ij> ij> Finished: SUCCESS

SELECT * FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER()) AS T1;
DISCONNECT;
ij version 10.13
ij> ij> ij> TS
|THREADID|XID|LCCID
|DATABASE
|DRDAID
|LOGTEXT

---

0 rows selected
ij> ij> Finished: SUCCESS

Under same environment without rollingFile everything works fine and
content of derby.log is read.

-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Re: sunsetting support for Java 8

[Peter Ondruška]

Dear Rick,

well, nice to know and I do not see a reason to object but does anybody
have ideas what Java 9 adoption will look like(?).

p.

On 22 June 2017 at 04:15, Rick Hillegas <rick.hille...@gmail.com> wrote:

> The current schedule for Java 9 calls for a GA date some time this autumn:
> http://mail.openjdk.java.net/pipermail/jdk9-dev/2017-June/005867.html.
> Shortly after that, I would expect that the Derby community would publish a
> new 10.14.1 feature release: https://wiki.apache.org/db-der
> by/DerbyTenFourteenOneRelease
>
> I would like to propose that 10.14 be the last release family which runs
> on Java 8. The 10.15 family would only run on Java 9 and higher. I expect
> that we would produce a 10.15.1 release some time after the first
> maintenance release of Java 9 goes GA, probably late in 2018.
>
> Please us know if this platform policy would be a great hardship for you.
>
> Thanks,
> -Rick
>
>


-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Re: Connection authentication failure occurred. Reason: Invalid authentication..

[Peter Ondruška]

Dear Rick,

well, that is why it is strange because I am very certain I use correct
username and password to connect and the data from database can be
accessed. Yes, NATIVE credentials are stored in the database itself. There
are no other errors or warnings besides this one.

Peter

On 29 May 2017 at 19:26, Rick Hillegas <rick.hille...@gmail.com> wrote:

> Hi Peter,
>
> This is the error which Derby raises when the user presents invalid
> credentials at connection time. Are you confident that correct credentials
> were given? Are the NATIVE credentials stored in the database being
> connected to? Or are they stored in a system-wide credentials database?
> What other errors appear in the diagnostic log prior to this error?
>
> Thanks,
> -Rick
>
>
> On 5/29/17 12:12 AM, Peter Ondruška wrote:
>
> Hello,
>
> I am facing strange situation with 10.13.1.1. This error is logged when
> load against Derby is higher than usuall:
>
> ***
> Mon May 29 08:31:10 CEST 2017 Thread[DRDAConnThread_27,5,main] (XID =
> 74907526), (SESSIONID = 22748), (DATABASE = /*removed*/), (DRDAID =
> .-43515881796
> 1723857{1069}), Cleanup action starting
> java.sql.SQLNonTransientConnectionException: Connection authentication
> failure occurred.  Reason: Invalid authentication..
> at 
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
> 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.Util.generateCsSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown
> Source)
> at 
> org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.(Unknown
> Source)
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
> at java.security.AccessController.doPrivileged(
> AccessController.java:650)
> at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown
> Source)
> at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
> at 
> org.apache.derby.jdbc.BasicEmbeddedDataSource40.getConnection(Unknown
> Source)
> at 
> org.apache.derby.jdbc.EmbedPooledConnection.openRealConnection(Unknown
> Source)
> at org.apache.derby.jdbc.EmbedXAConnection.getRealConnection(Unknown
> Source)
> at 
> org.apache.derby.iapi.jdbc.BrokeredConnection.getRealConnection(Unknown
> Source)
> at org.apache.derby.iapi.jdbc.BrokeredConnection.isClosed(Unknown
> Source)
> at 
> org.apache.derby.impl.drda.PiggyBackedSessionData.getInstance(Unknown
> Source)
> at 
> org.apache.derby.impl.drda.Database.getPiggyBackedSessionData(Unknown
> Source)
> at org.apache.derby.impl.drda.DRDAConnThread.writePBSD(Unknown
> Source)
> at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
> Source)
> at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
> Caused by: ERROR 08004: Connection authentication failure occurred.
> Reason: Invalid authentication..
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.
> wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 22 more
> = begin nested exception, level (1) ===
> ERROR 08004: Connection authentication failure occurred.  Reason: Invalid
> authentication..
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.
> wrapArgsForTransportAcrossDRDA(Unknown Source)
> at 
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
> 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.Util.generateCsSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown
> Source)
> at 
> org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.(Unknown
> Source)
> at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Sourc

Connection authentication failure occurred. Reason: Invalid authentication..

[Peter Ondruška]

 with
-Dderby.authentication.native.passwordLifetimeMillis=0 just to be sure but
it has no influence. All the connections are pool from Payara/Glassfish
with ClientXADataSource and therefore it is strange that I see mentions of
Embedded in the trace. And there are no messages from Payara about failed
authentications.

Any ideas? :)

-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Re: BACKUP.HISTORY file

[Peter Ondruška]

Looking at source code
https://svn.apache.org/repos/asf/db/derby/code/tags/10.12.1.1/java/engine/org/apache/derby/impl/store/raw/RawStore.java#backup
it appears there is no way without modifying source.

On 20 January 2017 at 21:17, Lazur, Eric J. <eric.la...@jhuapl.edu> wrote:

> Hello,
>
>
>
> How can I turn off the creation of the BACKUP.HISTORY file that is created
> by the Derby database?  I am looking for a specific setting in the Derby
> configuration  that I can adjust that will allow me to either turn off the
> generation of the BACKUP.HISTORY file altogether or at least disable any
> writing of information to this file.  We are maintaining a system that does
> frequent backups and we are finding that these BACKUP.HISTORY files are
> growing very large and consuming to much disk space.
>
>
>
> I have searched through the archives at the Derby site but I have been
> unable to locate any information that specifically addresses this issue.
>
>
>
> Thank you,
>
>
>
> -Eric
>
>
>
> JHU/APL
>
>
>



-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Re: Use Apache Derby Network Server with encrypted database

[Peter Ondruška]

Just a note on "If a person is already on the machine and would be able to
sniff the local IP traffic, that person may also have access on the files
of Derby.". To prevent this you encrypt the database and carefully manage
encryption key. But if somebody interepts unencrypted network traffic (be
it local or remote) he can also intercept the encryption key used to boot
database and all encryption is useless, besides attacher will have username
and password to log in to started database and can export data over network.

On 25 August 2016 at 08:47, Hohl, Gerrit <g.h...@aurenz.de> wrote:

> Hello Peter,
>
> hello George,
>
>
>
> thank you for your 2 mails. And sorry I didn't reply earlier.
>
> Yes, I also realized that there is no difference between the embedded and
> the standalone version.
>
> The only exception is that you have to put the encryption library in the
> CLASSPATH of the network server if you want to use one.
>
> Everything else can be passed to the database via the connection.
>
>
>
> About SSL: As I use it as a local database, but in a different process
> than then application (this way I can do some maintenance even independent
> from the application) I guess I don't need it.
>
> Or let's put it this way: If a person is already on the machine and would
> be able to sniff the local IP traffic, that person may also have access on
> the files of Derby.
>
> And somewhere there is also the password for the keystore as well as the
> path to the keystore which contains the certificate(s). I guess from there
> to the boot password it is only a short way.
>
> Or I'm wrong?
>
>
>
> For a database installed on a different machine I definitely would
> recommend using SSL, of course.
>
>
>
> Thanks for your mails again. :-)
>
>
>
> Gruß
>
> Gerrit
>
>
>
> *Von:* toma.georg...@yahoo.com [mailto:toma.georg...@yahoo.com]
> *Gesendet:* Mittwoch, 24. August 2016 22:13
> *An:* derby-user@db.apache.org
> *Betreff:* Re: Use Apache Derby Network Server with encrypted database
>
>
>
> Hi Gerrit,
>
>
>
> Based on Apache Derby page, between the embedded mode and network mode
> there is no difference, https://db.apache.org/derby/
> docs/10.0/manuals/admin/hubprnt19.html#Differences+
> between+running+Derby+in+embedded+mode+and+using+the+Network+Server
>
>
>
> Have you tried to create an encrypted database via the network mode using
> the steps mentioned in your link ? It should work, otherwise can you please
> post your errors/stacktrace/exceptions.
>
>
>
> What I did on my side to try your scenario :
>
>1. Start Apache Derby in network mode
>
>
>1. Create a dummy encrypted database and connect to it via *ij*:
>
>
>1.  connect 'jdbc:derby://*localhost:1527*/MyDbTest;create=true;
>   dataEncryption=true';
>
>
>1. After that I’ve started to create tables and to execute SQL queries
>(select), just to play with the database.
>
>
>1. It worked, no difference between embedded vs network mode.
>
>
>1. Remember to append to the URL the host and the port where Apache
>   Derby server is started ( in my case it was localhost : 1527).
>
>
>
> What I’ve followed in order to achieve the above:
>
>1. http://db.apache.org/derby/papers/DerbyTut/ns_intro.html#
>ij_ns_client
>
>
>1. http://db.apache.org/derby/papers/DerbyTut/ij_intro.html#ij_connect
>
>
>1. https://db.apache.org/derby/docs/10.0/manuals/develop/develop15.html
>
>
>1. If you want a more custom example regarding the algorithm that can
>be used to encrypt the database, please have a look into this page :
>
>
>1. https://db.apache.org/derby/docs/10.2/ref/rrefattribencryptkey.html
>
>
>
> If you need more information, please let me know.
>
>
>
> Regards,
>
> George
>
>
>
>
>
> Sent from Windows Mail
>
>
>
> *Von:* Peter Ondruška [mailto:peter.ondru...@kaibo.eu]
> *Gesendet:* Mittwoch, 24. August 2016 12:32
> *An:* Derby Discussion
> *Betreff:* Re: Use Apache Derby Network Server with encrypted database
>
>
>
> Dear Gerrit,
>
> from my understanding the only difference with Derby network server and
> embedded is relevant part of connection string. The rest where you put
> parameters after semicolon and where you would specify encryption
> properties is the same. Just start network server and then connect using
> network url with decryption parameters, subsequent connections should also
> use those parameters because you do not know if database has already booted
> or not. I strongly recommend using SSL to connect to encrypted database ;)
>
>
&

Re: Use Apache Derby Network Server with encrypted database

[Peter Ondruška]

Dear Gerrit,

from my understanding the only difference with Derby network server and
embedded is relevant part of connection string. The rest where you put
parameters after semicolon and where you would specify encryption
properties is the same. Just start network server and then connect using
network url with decryption parameters, subsequent connections should also
use those parameters because you do not know if database has already booted
or not. I strongly recommend using SSL to connect to encrypted database ;)

On 24 August 2016 at 09:15, Hohl, Gerrit <g.h...@aurenz.de> wrote:

> Hello everyone,
>
>
>
> I've used Apache Derby for years now as an embedded RDBMS.
>
> BTW: Thanks to all developer doing a great job developing this database
> system. :-D
>
>
>
> But now I want to use it as a separate service running on Ubuntu Linux.
>
> This is no problem.
>
>
>
> But I haven't found any explanation or example how to create and use
> encrypted database if I'm running Derby as a service.
>
>
>
> I found only this page:
>
> https://db.apache.org/derby/docs/10.0/manuals/develop/develop115.html
>
> But it seems it only deals with an embedded Derby version.
>
>
>
> Regards,
>
> Gerrit
>
>
>



-- 
Peter Ondruška

-- 
kaibo, s.r.o., ID 28435036, registered with the commercial register 
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická 379/10, 
Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu

Re: Derby ERROR XSDB6

[Peter Ondruška]

I've had exactly the same problem which has to do with Eclipse not being
aware of embedded Derby specific way to shutdown. Eclipse only closes
connection and database stays open. I solved this by running standalone
Derby network server and connecting from Eclipse and application as network
client.

On Friday, 30 October 2015, Alessandro Manzoni <
manzoni.alessand...@gmail.com> wrote:

> For some reasons I'm using derby embedded driver.
> The application performs as expected unless, for debug purpose, I open the
> database from eclipse DatabaseDevelopment perspective.
> Even if i close the connection from DatabaseDevelopment and closing
> DatabaseDevelopment perspective too, when I connect the db from the
> application I get the SQLExceplion:
> "ERROR XSDB6: Another instance of Derby may have already booted the
> database.
> The only way I found to reset this figure, is restarting eclipse. I
> imagine that's because DatabaseDevelopment is using a different JVM.
> Is there a way to force closing the db?
>
> Thank you.
>
>

-- 
Peter Ondruška

Re: XQuery or XSLT support in Derby

[Peter Ondruška]

ntDispatchThread.run(EventDispatchThread.java:91)
> Caused by: ERROR 1: Encountered error while evaluating XML query
> expression for xmlquery operator:
> com.sun.org.apache.xpath.internal.domapi.XPathStylesheetDOM3Exception:
> Prefix must resolve to a namespace: xsl
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ... 45 more
> Caused by: javax.xml.xpath.XPathExpressionException:
> com.sun.org.apache.xpath.internal.domapi.XPathStylesheetDOM3Exception:
> Prefix must resolve to a namespace: xsl
> at
> com.sun.org.apache.xpath.internal.jaxp.XPathImpl.compile(XPathImpl.java:400)
> at org.apache.derby.iapi.types.SqlXmlUtil.compileXQExpr(Unknown Source)
> at
> org.apache.derby.exe.ac185e801cx0150xb293xf5c3x06bfe7b80.postConstructor(Unknown
> Source)
> at
> org.apache.derby.impl.services.reflect.LoadedGeneratedClass.newInstance(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericActivationHolder.(Unknown Source)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.getActivation(Unknown
> Source)
> ... 38 more
> Caused by:
> com.sun.org.apache.xpath.internal.domapi.XPathStylesheetDOM3Exception:
> Prefix must resolve to a namespace: xsl
> at
> com.sun.org.apache.xpath.internal.compiler.XPathParser.errorForDOM3(XPathParser.java:657)
> at
> com.sun.org.apache.xpath.internal.compiler.Lexer.mapNSTokens(Lexer.java:642)
> at
> com.sun.org.apache.xpath.internal.compiler.Lexer.tokenize(Lexer.java:219)
> at
> com.sun.org.apache.xpath.internal.compiler.Lexer.tokenize(Lexer.java:100)
> at
> com.sun.org.apache.xpath.internal.compiler.XPathParser.initXPath(XPathParser.java:114)
> at com.sun.org.apache.xpath.internal.XPath.(XPath.java:180)
> at com.sun.org.apache.xpath.internal.XPath.(XPath.java:268)
> at
> com.sun.org.apache.xpath.internal.jaxp.XPathImpl.compile(XPathImpl.java:392)
> ... 43 more
>
>
> How can I avoid that?
>
>
>
> Thank you,
> Greg
>



-- 
Peter Ondruška

Re: EmbeddedDriver and db.lck file

[Peter Ondruška]

This is what I di in context listener:

@Override  public void contextDestroyed(final ServletContextEvent sce)
{final ServletContext context = sce.getServletContext();
final String PERSIST_ROOT =
context.getInitParameter(PERSISTENT_ROOT);final String DB_URL =
DB_PREFIX.concat(PERSIST_ROOT).concat(db);try {
DriverManager.getConnection(DB_URL + SHUTDOWN, USERNAME, PASSWORD);
} catch (final SQLException e) {  // ignore}  }



On Wednesday, 17 June 2015, Thomas Meyer tho...@m3y3r.de wrote:

 Hi,

 I have a Servlet running under Jetty 9.2.11 which uses EclipseLink 2.6as
 JPA tool. In the JPA tool I did configure the usage of the Derby Embedded
 10.11.1 driver. For a fresh start of the jetty server everything works as
 expected.
 But when I now redeploy the context config XML file after I did update the
 referenced war file, derby begins to tell me that another instance did
 already boot the database.
 Somehow the db.lck file is not released when I close the
 EntityManagerFactory.

 Any idea what's going on here?

 How can I force the release of the db.lck file in a
 ServletListener.contextDestroyed() method?

 With kind regards
 Thomas



-- 
Peter Ondruška

Re: Copying encypted DB?

[Peter Ondruška]

Unless I missed something why not just create backup and then open the
backup copy and change encryption key.

On Wednesday, 15 April 2015, John English john.fore...@gmail.com wrote:

 I have a DB which is encrypted with one password, and I want to generate
 an identical copy of it which will be encrypted using a different password
 (so that I can provide copies of the same DB for two different customers
 without exposing one customer's password to the other).

 I thought at first I could create the tables from a script and then run
 lots of insert into foo (select * from bar) queries, but this won't work
 unless the auto-generated columns are allocated with the same sequence
 numbers so that the foreign key references will match up. And in some case
 the keys are not sequential, due to deletions.

 Is there an easy way to do this?

 TIA,
 --
 John English



-- 
Peter Ondruška

Re: How to Combine Apache Derby without installing with Jar Directly

[Peter Ondruška]

You can use Eclipse to create runnable jar that packs other jars inside.

On Wednesday, 7 January 2015, Varun Sawaji sawaji.va...@gmail.com wrote:

 Hi,

 I have application which was developed using Java and Apache Derby. I want
 to create executable JAR and run on any system which doesnot have derby
 DB.When I click on Jar file the derby also install on the system. Is it
 possible.

 Varun



-- 
Peter Ondruška

Re: Urgent question about JIra issue DERBY-526

[Peter Ondruška]

Try:

jdbc:derby://[2001:db8:0:f101:0:0:0:9]:1527/xxx;create=true;user=xxx;password=xxx

On 3 December 2014 at 09:09, Lin Ren lin@oracle.com wrote:

 Hi Guys,



 Sorry for the broadcast… I have a quick question about issue DERBY-526,
 I’m currently using Derby version 10.10.1.3, and still meet the same
 problem:



 When I using IPv6 JDBC URL like:
 “jdbc:derby://2001:db8:0:f101:0:0:0:9:1527/xxx;create=true;user=xxx;password=xxx”



 I got the exception: java.lang.NumberFormatException: For input string:
 db8:0:f101:0:0:0:9:1527



 My searched Jira and found out the issue 526, but seems it is still in
 open state, can anyone tell me whether the issue is fixed now? And in which
 version if yes?



 Thanks so much!



 Lin




-- 
Peter Ondruška

Re: Blob column behaviour, when we dont have data when having less data

[Peter Ondruška]

Derby uses only as much space as the size of your BLOB. The DDL for size of
BLOB column is the maximum BLOB you can store.

On 5 December 2014 at 06:57, kosurusekhar kosurusek...@gmail.com wrote:

 Thanks Mike for quick reply.

  2) whether derby will occupy complete 6MB space if I am trying to insert
  small size files like 512KB or 1MB?
 *space used will be that of the size of the inserted column plus some
 metadata overhead/page overhead. *

 This means that in a 6MB column if I save 512KB content file then it will
 occupy 6MB + metadata size. Right?

 Is there any provision to space in this kind of scenario ?


 Thanks
 Sekhar.



 --
 View this message in context:
 http://apache-database.10148.n7.nabble.com/Blob-column-behaviour-when-we-dont-have-data-when-having-less-data-tp143363p143376.html
 Sent from the Apache Derby Users mailing list archive at Nabble.com.




-- 
Peter Ondruška

Re: Locks on crashed database

[Peter Ondruška]

Dear Knut,

many thanks for the tip. For others who need something similar here is the
complete code:

package xarecovery;

import java.sql.SQLException;
import java.util.logging.Level;

import javax.sql.XAConnection;
import javax.sql.XADataSource;
import javax.transaction.xa.XAException;
import javax.transaction.xa.XAResource;
import javax.transaction.xa.Xid;

import org.apache.derby.jdbc.EmbeddedDataSource;
import org.apache.derby.jdbc.EmbeddedXADataSource;

/**
 * Remove obsolete lock records caused by not gracefully removing database
that was under transaction manager control.br /
 * This can be observed by having records in TRANSACTION_TABLE (and related
in LOCK_TABLE) with state PREPARED:br /
 * SELECT * FROM SYSCS_DIAG.LOCK_TABLE;br /
 * SELECT * FROM SYSCS_DIAG.TRANSACTION_TABLE;
 *
 * @author Knut Anders Hatlen, Peter Ondruška (just slightly modified)
 *
 */
public class Recover {

  private static final java.util.logging.Logger LOGGER =
java.util.logging.Logger.getLogger(Recover.class.getName());

  public static void main(final String[] args) {

final EmbeddedDataSource eds = new EmbeddedXADataSource();
eds.setDatabaseName(pathtodatabase);

final XADataSource ds = (EmbeddedXADataSource) eds;

try {
  final XAConnection xac = ds.getXAConnection();
  final XAResource xar = xac.getXAResource();
  for (final Xid xid : xar.recover(XAResource.TMSTARTRSCAN)) {
LOGGER.log(Level.INFO, Recover using rollback Xid {0},
xid.toString());
xar.rollback(xid);
  }
  xac.close();
} catch (final SQLException | XAException e) {
  LOGGER.log(Level.WARNING, null, e);
}

try {
  eds.setShutdownDatabase(shutdown);
  eds.getConnection();
} catch (final SQLException e) {
  LOGGER.log(Level.INFO, This exception is OK, e);
}

  }

}


On 25 November 2014 at 12:49, Knut Anders Hatlen knut.hat...@oracle.com
wrote:

 Peter Ondruška peter.ondru...@kaibo.eu writes:

  Dear all,
 
  I have a database that has locks in SYSCS_DIAG.LOCK_TABLE. How do I
  remove those locks? I restarted the database but the locks are still
  there. SYSCS_DIAG.TRANSACTION_TABLE also has related record with
  status PREPARED. This database was used with XA on application server
  but it was removed for troubleshooting.

 Hi Peter,

 You probably need to run XA recovery and commit or roll back the
 prepared transactions. Something like this:

 XADataSource ds = ;
 XAConnection xac = ds.getXAConnection();
 XAResource xar = xac.getXAResource();
 for (Xid xid : xar.recover(XAResource.TMSTARTRSCAN)) {
 xar.rollback(xid);
 // or, if you prefer, xar.commit(xid, false);
 }

 Hope this helps,

 --
 Knut Anders




-- 
Peter Ondruška

Locks on crashed database

[Peter Ondruška]

Dear all,

I have a database that has locks in SYSCS_DIAG.LOCK_TABLE. How do I remove
those locks? I restarted the database but the locks are still there.
SYSCS_DIAG.TRANSACTION_TABLE also has related record with status PREPARED.
This database was used with XA on application server but it was removed for
troubleshooting.

Thanks

-- 
Peter Ondruška

Re: No Powered By Derby Logo

[Peter Ondruška]

Great, very nice, thanks!

On 11 November 2014 03:33, Rick Hillegas rick.hille...@gmail.com wrote:

  Hi John,

 I have combined the poweredBy and Derby logos and put them here:
 http://db.apache.org/derby/logo.html#logo_with_nimbus

 Hope this helps,
 -Rick

 On 11/8/14 4:23 AM, John I. Moore, Jr. wrote:

  There are a lot of “Powered By” logos on the Apache web site (e.g., see
 http://apache.org/foundation/press/kit/ and
 http://apache.org/foundation/press/kit/poweredBy/), and there is an
 official Derby logo (http://db.apache.org/derby/logo.html), but I can’t
 seem to find a “Powered By Derby” logo combination.  Does one exist?  Are
 there plans to create on in the near future?



 _



 John I. Moore, Jr.

 SoftMoore Consulting







-- 
Peter Ondruška

Re: Derby online backup with huge database

[Peter Ondruška]

How long does it take to copy database to backup destination using
operating system copy command?

On 4 November 2014 06:54, kosurusekhar kosurusek...@gmail.com wrote:

 Hi All,

 We implemented to take the derby online backup when ever our application is
 launching. It is working fine. But in production the database grows more
 than 2GB. It is taking  more than 7 to 10 minutes to take the backup.

 Is this behaviour is normal with Derby database?

 Is there any thing need to configure/implement to speedup the backup
 process?

 Please advice me in that.

 Thanks in advance.

 Regards
 Sekhar.



 --
 View this message in context:
 http://apache-database.10148.n7.nabble.com/Derby-online-backup-with-huge-database-tp143121.html
 Sent from the Apache Derby Users mailing list archive at Nabble.com.




-- 
Peter Ondruška

Re: Another error to be explained

[Peter Ondruška]

To me it is pretty self-explaining. One of the required files of your
database directory is missing. c510.dat.

On 22 September 2014 20:50, Bob M rgmatth...@orcon.net.nz wrote:

 Hi

 Error Message:-

 SQLException:
 SQL State: XSDG3
 Error Code: 45000
 Message: Meta-data for unknown could not be accessed to read:
 C:\\us_copiosus\seg0\c510.dat
 SQLException:
 SQL State: XJ001
 Error Code: 0
 Message: Java exception: 'C:\...us_copiosus\seg0\c510.dat (Access is
 denied):java.io.FileNotFoundException'.

 What does this error mean?

 Bob M
 Dunedin
 New Zealand



 --
 View this message in context:
 http://apache-database.10148.n7.nabble.com/Another-error-to-be-explained-tp142334.html
 Sent from the Apache Derby Users mailing list archive at Nabble.com.




-- 
Peter Ondruška

Re: Another error to be explained

[Peter Ondruška]

Access denied, could be antivirus or permission or attributes rendering
file inaccessible.

On Monday, 22 September 2014, Bob M rgmatth...@orcon.net.nz wrote:

 Sorry, but it is NOT missing!

 Bob M



 --
 View this message in context:
 http://apache-database.10148.n7.nabble.com/Another-error-to-be-explained-tp142334p142338.html
 Sent from the Apache Derby Users mailing list archive at Nabble.com.



-- 
Peter Ondruška

Re: Problem with Select statement

[Peter Ondruška]

Hello,

can you describe your table testtable please?

On 9 Sep 2014, at 10:20, Kessler, Joerg joerg.kess...@sap.com wrote:

 Hi,
 I want to execute select statement on a table using a Java program and JDBC. 
 The statement is actually not very difficult:
 SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE  
 WHERE SEQ_ID = ? AND (MSGSTATE IS NULL OR MSGSTATE = 'A')
 When this statement is executed by a test I receive errors like
  
 Column 'A' is either not in any table in the FROM list or appears within a 
 join specification and is outside the scope of the join specification or 
 appears in a HAVING clause and is not in the GROUP BY list. If this is a 
 CREATE or ALTER TABLE  statement then 'A' is not a column in the target table.
  
 When I change the statement to
 SELECT MSG_NO, SEND_TO, CREATED_TIME, CONTENT, ENCRYPTION_KEY FROM TESTTABLE  
 WHERE SEQ_ID = ? AND MSGSTATE IS NULL
 there is no problem. Also when I execute the above statement via Eclipse 
 Database Development/SQL Scrapbook using a fix SEQ_ID the statement is 
 executed without error.
  
 What am I doing wrong?
  
 Best Regards,
  
 Jörg

Re: Difference

[Peter Ondruška]

:-) Thanks. I considered 10.10.2.0 stable, actually for me an my use it is very 
stable.
 
Peter


On Thursday, 31 July 2014, 19:31, Myrna van Lunteren m.v.lunte...@gmail.com 
wrote:
 


Hi,

10.10.2.0 has all the *new* functionality of 10.9.1.0 and 10.10.1.0. Plus it 
has  more bug fixes than 10.8.3.0, both because the 10.10 branch was pulled 
from trunk at a later time and because 10.10.2.0 was released later and thus 
even more fixes were back-ported. It therefore also has more possible 
incompatibilities to older versions.

10.8.3.0 only has the most important fixes available at the time of release 
back-ported, and has very few incompatibilities compared to e.g. 10.8.2.


There were some plans to make a 10.9.2 at one time but that fell by the 
wayside. It would have replaced the 10.8.3.0.

Myrna




On Thu, Jul 31, 2014 at 5:41 AM, Rick Hillegas rick.hille...@oracle.com wrote:

On 7/31/14 4:07 AM, Peter Ondruška wrote:

Dear all,

what is the difference between version 10.10.2.0 and 10.8.3.0? Or why is 
there 10.8.3.0 along with 10.10.2.0? Thanks
Peter

The Latest Official Releases tend to be the latest releases produced on the 2 
most active release branches. Once we publish 10.11.1, I expect that we'll 
remove 10.8.3.0 from that list. Right after we produce a feature release, the 
list has this meaning:

i) The top release is the most feature-rich distribution.

ii) The second release is the most stable distribution.

Hope this helps,
-Rick

Difference

[Peter Ondruška]

Dear all,

what is the difference between version 10.10.2.0 and 10.8.3.0? Or why is there 
10.8.3.0 along with 10.10.2.0? Thanks
 
Peter

Re: When to shut down a database

[Peter Ondruška]

Where did you read that?

If you declare your column to be CLOB(64K) than you have restricted its size. 
CLOB data type
 
 CLOB data type
A CLOB (character large object) value can be up to 2,147,483,647
characters long. A CLOB is used to store unicode character-based data, such
as large documents in any character set.   
View on db.apache.org Preview by Yahoo  
 
 
 String limitations
The following table lists limitations on string values in
Derby. Table 1. String limitations Value Maximum Limit Length of CHAR 254 
characters Length of VARCHAR 32,672 characters   
View on db.apache.org Preview by Yahoo  
 and String limitations



 
Peter
On Friday, 11 April 2014, 6:09, Chux chu...@gmail.com wrote:
 
Awesome insights guys, thanks for all your help.

BTW, I could not access the online documentation for some reason. Although I 
read somewhere that 64k is the maximum size you can allocate a clob on embedded 
mode. Is this correct? I would like to know what the limit is.



variable clob(64 K)

Thanks,
Chux



On Thu, Apr 10, 2014 at 5:32 AM, Dag H. Wanvik dag.wan...@oracle.com wrote:


On 09. april 2014 17:51, Rick Hillegas wrote:

On 4/8/14 2:00 AM, Chux wrote:

Hey Dag,

Thanks for your insight.

I'm using this as an embedded DB in a Java FX desktop application. This is a 
dumb question but would you recommend shutting down the database ever after 
a transaction? Like after you create a record then you shut it down after 
commit.

Depends on the application. If the database holds some kind of infrequently 
referenced metadata, so that say, it is only queried or updated once a day, 
then you could consider an on demand model where the database is booted for 
each query/update, then the query results are returned, then the database is 
shut down so that it doesn't consume any resources. The big extra cost of an 
on demand database would be this:  query/update time would be substantially 
longer since every query/update involves booting the database, compiling the 
query/update, and gracefully closing the database; that cost is on top of the 
steady-state cost of running a pre-compiled query/update.


In such a scenario one might want to shut down the engine, too, not just the 
database.
Note that shutting down the database will resources, but if the engine is 
still running, one can further release resources by shutting that down as well.

Cf. http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp20349.html (engine 
shutdown)
and http://db.apache.org/derby/docs/10.10/devguide/tdevdvlp40464.html 
(shutdown database)

Thanks,
Dag






Hope this helps,
-Rick


Best,
Chux



On Tue, Apr 8, 2014 at 12:32 AM, Dag H. Wanvik dag.wan...@oracle.com 
mailto:dag.wan...@oracle.com wrote:


    On 06. april 2014 21:02, George Toma wrote:

    Hi Chux,

    In my opinion  the example from  app.  referred at commit the
    transaction OR close the connection ( a connection could be
    transacted too ), and not to shutdown the db. If the business
    rule specifies that the db. needs to be shutdown when the app. is
    shutdown, then so be it.

    Normally the db is not shutdown, not even when the app is down.

    This is true for a client/server application. For use with
    embedded Derby, one would normally close down the database (and
    the database engine) before exiting the application. If one
    neglects to do so,
    one would see longer start-up times as Dyre indicated.

    Thanks,
    Dag




    Cheers,
    George
    On Sunday, April 6, 2014 7:14 PM, Chux chu...@gmail.com
    mailto:chu...@gmail.com wrote:
    Hello guys,

    I read in a sample app that you've got to shutdown a database. I
    was just confused if you need to shut it down on every connection
    transaction or just shut it down on application close, in my case
    a desktop applicaiton.

    Best,
    Chux

Re: Corrupt database: ArrayIndexOutOfBoundsException on connect

[Peter Ondruška]

Just a reminder to do backups and run the database with log archiving. 
Everything is built in Derby. I know for you it is too late for you :(

Peter

 On 10 Mar 2014, at 17:20, Myrna van Lunteren m.v.lunte...@gmail.com wrote:
 
 Although Derby has transaction control and a recovery mechanism, if a JVM 
 crashes or gets interrupted, the normal transaction steps might be 
 interrupted in unfortunate places, especially during compress. Was the 
 database shutdown before compress? Do you have a backup?
 
 But perhaps there is something of use to you on this page:
 https://wiki.apache.org/db-derby/DatabaseCorruption
 
 HTH
 Myrna
 
 
 
 On Mon, Mar 10, 2014 at 4:49 AM, Phil Bradley ph...@tower.ie wrote:
 
 Hi,
 
 I have a derby database that I am unable to connect to; when I try I get
 java.lang.ArrayIndexOutOfBoundsException. The full stack trace is
 shown below.
 
 Firstly, some background:
 
 - I'm using derby 10.8.2.2 with a Java Webstart application that
 connects in embedded mode. The clients are using Java 7u45 on Windows 7,
 32 bit
 
 - The application is configured to run SYSCS_UTIL.SYSCS_COMPRESS_TABLE()
 every 5 days on each table on startup
 
 - The client was running SYSCS_UTIL.SYSCS_COMPRESS_TABLE() on a
 particular table and based on the application logs, it looks like either
 the JVM crashed or the application was ended via task manager.
 
 - On subsequent attempts to start the application, the client was unable
 to connect to the database as per the stack trace below. I have made a
 copy of the database and I get this error reliably on accessing the
 copy.
 
 I have two questions:
 
 1. Is there anything that I can do to recover from this kind of scenario
 automatically?
 2. Is there any debugging or other investigation that I can do that will
 help reduce the severity of this kind of problem?
 
 Thanks,
 Phil
 
 
 
 
 
 java.sql.SQLException: Failed to start database
 'C:\Users\Administrator\.myapp\myapp_db' with class loader
 com.sun.jnlp.JNLPClassLoader@1bef5e8, see the next exception for
 details.
 at
 
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
 Source)
 at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
 Source)
 at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedConnection.bootDatabase(Unknown
 Source)
 at org.apache.derby.impl.jdbc.EmbedConnection.init(Unknown
 Source)
 at org.apache.derby.impl.jdbc.EmbedConnection30.init(Unknown
 Source)
 at org.apache.derby.impl.jdbc.EmbedConnection40.init(Unknown
 Source)
 at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown
 Source)
 at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
 at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown
 Source)
 at java.sql.DriverManager.getConnection(Unknown Source)
 at java.sql.DriverManager.getConnection(Unknown Source)
 at
 
 com.mycompany.database.DbInitializer.runScript(DbInitializer.java:143)
 at
 
 com.mycompany.myapp.ApplicationRunner.initialiseDb(ApplicationRunner.java:817)
 at
 
 com.mycompany.myapp.ApplicationRunner.startApplication(ApplicationRunner.java:945)
 at
 com.mycompany.myapp.ApplicationRunner.run(ApplicationRunner.java:581)
 at
 
 com.mycompany.myapp.ApplicationRunner.main(ApplicationRunner.java:552)
 at
 com.mycompany.myapp.ApplicationLoader.main(ApplicationLoader.java:90)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown
 Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at com.sun.javaws.Launcher.executeApplication(Unknown Source)
 at com.sun.javaws.Launcher.executeMainClass(Unknown Source)
 at com.sun.javaws.Launcher.doLaunchApp(Unknown Source)
 at com.sun.javaws.Launcher.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)
 Caused by: java.sql.SQLException: Failed to start database
 'C:\Users\Administrator\.myapp\myapp_db' with class loader
 com.sun.jnlp.JNLPClassLoader@1bef5e8, see the next exception for
 details.
 at
 
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
 Source)
 at
 
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
 ... 27 more
 Caused by: java.sql.SQLException: Java exception: ':
 java.lang.ArrayIndexOutOfBoundsException'.
 at
 
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
 Source)
 at
 
 

Re: DERBY_OPTS/DERBY_CMD_LINE_ARGS is there anywhere a list

[Peter Ondruška]

On my Windows I start Derby network server like this:

set DROPBOX=%USERPROFILE%\Dropbox
set PATH=%DROPBOX%\derby\bin;%PATH%
set DERBY_OPTS=-Xms256m -Xmx256m
set DERBY_OPTS=-Dderby.storage.pageCacheSize=4096 %DERBY_OPTS%

set DERBY_OPTS=-Djava.security.manager %DERBY_OPTS%
set DERBY_OPTS=-Djava.security.policy=%DROPBOX%\config\derby.policy %DERBY_OPTS%
set DERBY_OPTS=-Dderby.system.home=C:\TEMP %DERBY_OPTS%
start Derby server startNetworkServer

Peter

 On 26 Feb 2014, at 11:23, Kempff, Malte malte.kem...@de.equens.com wrote:
 
 Hi to all,
 I used to use Derby as embedded data base. Right now I‘d like to use it as 
 Server.
 When I start the server (on my own mashine right now) by using 
 startNetworkServer.bat,
 I got a warning that there is no acess on c:\derby.log.
 After researching on that I understood, that it would be great to give the 
 derby.system.home. I also read about derby.proerties file, where obviously 
 properties are stored. Of course you can give derby.system.home right on the 
 start using –Dprops-name, but if I just like to use the 
 startNetworkServer.bat how I do that there? In that File I saw something like 
 DERBY_OPTS and DERBY_CMD_LINE_ARGS as variables But where not really able to 
 see where the OPTS are supposed to be set (assuming/guessing) that here 
 java-Properties are to be set) A Listing of DERBY_CMD_LINE_ARGS I could not 
 find either.
  
 So using that startNetworkServer.bat what is the best/correct way to provide 
 derby.system.home property?
  
 Thank for Help in advance
  
 Malte
 
  Disclaimer: The contents of this electronic mail message are only binding 
 upon Equens or its affiliates, if the contents of the message are accompanied 
 by a lawfully recognised type of signature. The contents of this electronic 
 mail message are privileged and confidential and are intended only for use by 
 the addressee. If you have received this electronic mail message by error, 
 please notify the sender and delete the message without taking notices of its 
 content, reproducing it and using it in any way. 
 

Re: Apache Derby Command Line?

[Peter Ondruška]

C:\Java Server JRE\jdk1.7.0_51\bin\java.exe should be:
C:\Java Server JRE\jdk1.7.0_51\bin\java.exe

Peter

 On 22 Feb 2014, at 02:48, Turtles 3turt...@videotron.ca wrote:
 
 C:\Java Server JRE\jdk1.7.0_51\bin\java.exe

Re: How know databases connected with Derby networkServer

[Peter Ondruška]

Emory is cheap nowadays. Just run each database in separate JVM. If you are on 
Linux or AIX I would recommend IBM Java with class sharing..

Peter

 On 24 Jan 2014, at 11:32, AirDT cont...@solgt.fr wrote:
 
 Hello everyone, 
 
 I run a NetworkServer that allows multiple users to connect to multiple
 Derby database. 
 How do I know databases connected at a given moment ?
 In order to :
 - Have knowledge of potential users connected to each connected dataBase
 - Shutdown these databases in order to remove them without shutDown derby. 
 -...
 
 Any help would be appreciated.
 Thanks in advance
 
 AirDT
 
 
 
 --
 View this message in context: 
 http://apache-database.10148.n7.nabble.com/How-know-databases-connected-with-Derby-networkServer-tp136724.html
 Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Able to reconnect previously shutdown in-memory derby database

[Peter Ondruška]

My guess is that similar to filesystem if you only shutdown Derby without JVM 
exit database is still there. Similar to filesystem where you need to remove 
database from filesystem.

Peter

 On 9 Oct 2013, at 14:26, pelle.ullberg pelle.ullb...@gmail.com wrote:
 
 Hi, 
 
 Could someone please explain whats wrong with this little unit test that I
 mocked up? I'm using derby version 10.10.1.1
 
 Basically I create an in-memory derby database, shut it down and then
 expected it to not exist anymore. But if the unit test is right, I can
 actually reconnect to it.
 
 Best regards
 /Pelle
 
 Code is below!
 
 
 package com.klarna.derby;
 
 import org.apache.derby.jdbc.EmbeddedDriver;
 import org.junit.Assert;
 import org.junit.Test;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 import java.util.UUID;
 
 public class DerbyUtilsTest {
 
private static final Logger LOGGER =
 LoggerFactory.getLogger(DerbyUtilsTest.class);
 
@Test(expected = SQLException.class)
public void verifyDerbyShutdown() throws SQLException {
String url = jdbc:derby:memory: + UUID.randomUUID().toString();
 
Connection connection = DriverManager.getConnection(url +
 ;create=true);
 
// Ping derby just to make sure we got it up and running
connection.prepareCall(select * from
 SYS.SYSTABLES).executeQuery();
 
try {
DriverManager.getConnection(jdbc:derby:;shutdown=true);
} catch (SQLException e) {
// This exception is expected:
 http://db.apache.org/derby/docs/10.3/devguide/tdevdvlp20349.html
Assert.assertEquals(Derby system shutdown., e.getMessage());
} finally {
// Make sure old driver is collected
System.gc();
try {
// Re-rgeister driver so that new derby jdbc instances may
 be spawned.
DriverManager.registerDriver(new EmbeddedDriver());
} catch (SQLException e) {
LOGGER.error(Failed to re-register Derby embedded driver: 
 + e.getMessage(), e);
}
}
 
// Expected this to throw something like 'java.sql.SQLException:
 Database 'memory:d77d6863-7624-4990-86fb-2e40a5a1e04d' not found'
DriverManager.getConnection(url);
 
}
 
 }
 
 
 
 
 --
 View this message in context: 
 http://apache-database.10148.n7.nabble.com/Able-to-reconnect-previously-shutdown-in-memory-derby-database-tp134573.html
 Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Proper configuration for a very busy DB?

[Peter Ondruška]

I mean transaction log, by default they are in log subdirectory of database, 
next to seg0 directory. If you can do batch insertions.

Peter

 On 1 Oct 2013, at 17:53, Jerry Lampi j...@sdsusa.com wrote:
 
 Peter:
 Each client has one connection.  It is used for the entire session (which can 
 be days).
 The Derby log file are configured to have one log file per day.  Format names 
 like: productName-stderr.2013-10-01.log and productName-
 stdout.2013-10-01.log.
 
 Brett:
 - A flurry of data has been as great as 4000 records per second.  That is the 
 number cached by the client(s) and each record is dumped to the DB one at a 
 time.  Not all 30 clients see 4000 per second, likely only 2 or three of 
 them.  The DB has over 10 million records in it at any given time and it is 
 purged daily of older records.
 - We use prepared statements (PS).
 - Each client has one dedicated connection.
 
 All:
 I appreciate your responses.  I will benchmark using JMeter and then follow 
 the tuning tips for derby 10.8 ( 
 http://db.apache.org/derby/docs/10.8/tuning/index.html ).  I will start by 
 tweaking the derby.statementCache.size up from the 100 default.
 
 Any other advice greatly appreciated.
 
 Thanks,
 
 Jerry
 
 On 9/30/2013 2:55 PM, Peter wrote:
 Do you open new connection every time or do you have a pool? How often does 
 Derby checkpoint/switch log file?
 
 
 Peter
 
 
 On 9/30/2013 2:47 PM, Bergquist, Brett wrote:
 Jerry, can you provide a bit more background which might be helpful:
 
 - what is your definition of a flurry of data?   What sort of transaction 
 rate do you estimate this is?
 - are you using prepared statements for your inserts, updates, etc? If not, 
 then do so and also change the derby.statementCache.size to something quite 
 a bit larger.  This will allow the statements to be compiled once and cached 
 instead of being prepared each time you execute them.
 - are you using a connection pool or are you opening/closing connections 
 frequently?
 
 I have a system with a busy database and it took some tuning to get to this 
 point.  Right now it is doing about 100 inserts/second continuous 24x7 and 
 it has peaked up to 200 inserts/second.  Granted my application is different 
 than what you are doing but it is possible to get derby to run when busy.
 
 
 -Original Message-
 From: Jerry Lampi [mailto:j...@sdsusa.com]
 Sent: Monday, September 30, 2013 3:29 PM
 To: Derby User Group
 Subject: Proper configuration for a very busy DB?
 
 We have about 30 clients that connect to our version 10.8.2.2 Derby DB.
 
 The clients are programs that gather data from the operating system of their 
 host and then store that data in the DB, including FTP activity.
 Sometimes, the clients get huge flurries of data all at once and Derby is 
 unable to handle the influx of requests; inserts, updates, etc.  In 
 addition, the clients are written so that if they are unable to talk to the 
 DB, they queue up as much data as possible and then write it to the DB when 
 the DB becomes available.
 
 This client queuing is a poor design, and places greater stress on the DB, 
 as when the 30 clients finally do talk to the DB, they all dump data at 
 once.  The clients do not know about one another and therefore do not 
 attempt any throttling or cooperation when dumping on the DB.
 
 The net effect of all this is that the DB is too slow to keep up with the 
 clients.  As clients try to feed data to the DB, it cannot accept it as fast 
 as desired and this results in the clients queueing more data, exacerbating 
 the issue.
 
 So the DB is very busy.  The only significant thing we have done thus far is 
 change the derby.storage.pageCacheSize=5000 and increase Java heap to 1536m.
 
 Is there a configuration considered optimal for a VERY busy Derby DB?
 
 Thanks,
 
 Jerry
 
 
 ---
 avast! Antivirus: Outbound message clean.
 Virus Database (VPS): 130930-0, 09/30/2013 Tested on: 9/30/2013 2:28:40 PM 
 avast! - copyright (c) 1988-2013 AVAST Software.
 http://www.avast.com
 
 
 
 ---
 avast! Antivirus: Outbound message clean.
 Virus Database (VPS): 131001-0, 10/01/2013
 Tested on: 10/1/2013 10:53:12 AM
 avast! - copyright (c) 1988-2013 AVAST Software.
 http://www.avast.com
 
 
 

Re: Proper configuration for a very busy DB?

[Peter Ondruška]

Do you open new connection every time or do you have a pool? How often does 
Derby checkpoint/switch log file?


 
Peter


- Original Message -
From: Jerry Lampi j...@sdsusa.com
To: Derby User Group derby-user@db.apache.org
Cc: 
Sent: Monday, 30 September 2013, 21:28
Subject: Proper configuration for a very busy DB?

We have about 30 clients that connect to our version 10.8.2.2 Derby DB.

The clients are programs that gather data from the operating system of their 
host and then store that data in the DB, including FTP activity.  Sometimes, 
the clients get huge flurries of data all at once and Derby is unable to handle 
the influx of requests; inserts, updates, etc.  In addition, the clients are 
written so that if they are unable to talk to the DB, they queue up as much 
data as possible and then write it to the DB when the DB becomes available.

This client queuing is a poor design, and places greater stress on the DB, as 
when the 30 clients finally do talk to the DB, they all dump data at once.  The 
clients do not know about one another and therefore do not attempt any 
throttling or cooperation when dumping on the DB.

The net effect of all this is that the DB is too slow to keep up with the 
clients.  As clients try to feed data to the DB, it cannot accept it as fast as 
desired and this results in the clients queueing more data, exacerbating the 
issue.

So the DB is very busy.  The only significant thing we have done thus far is 
change the derby.storage.pageCacheSize=5000 and increase Java heap to 1536m.

Is there a configuration considered optimal for a VERY busy Derby DB?

Thanks,

Jerry


---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 130930-0, 09/30/2013
Tested on: 9/30/2013 2:28:40 PM
avast! - copyright (c) 1988-2013 AVAST Software.
http://www.avast.com

Re: Is there some way to shut down a Derby database faster?

[Peter Ondruška]

So if users need data after working with database give them consistent copy 
using backup 
http://db.apache.org/derby/docs/10.0/manuals/admin/hubprnt43.html#HDRSII-BUBBKUP-63476

Peter

On 4. 7. 2013, at 23:17, Trejkaz trej...@trypticon.org wrote:

 On Fri, Jul 5, 2013 at 12:44 AM, Bryan Pendleton
 bpendleton.de...@gmail.com wrote:
 Have you considered using a connection pool in between your
 application layer and the database, so that the connections
 are retained and re-used, rather than being fully reclaimed
 and fully reopened?
 
 From the original post:
 
 | and when the user closes the database they expect to be able to move
 | the files immediately after.
 
 TX

Re: Problems with Online Backup SYSCS_BACKUP_DATABASE

[Peter Ondruška]

hi, there should be in the backup destination as much disk space available
as your database size without logs.

Peter

On 17. 8. 2012, at 13:06, Stefan R. elstefan...@gmail.com wrote:

Hi,

We're using Derby DB (Version 10.8.2.2) in a larger project. Our database
size is now around 12GB. It is running as network service and is used as
the data backend for several Java web projects, that are connecting to the
database using the network jdbc client. One of the projects is triggering
nightly backups using the command:



CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)



Randomly (every 2 or 3 days) the backup cannot complete because of the
following exception:



Aug 13 00:03:14 srv- test-001 jsvc.exec[5953]: a:662)#012Caused by:
org.apache.derby.client.am.SqlException: Cannot backup the database, got an
I/O Exception while writing to the backup container file
/mnt/backup/2012-08-13-00-00-00/bd/seg0/c9b1.dat.#012#011at
org.apache.derby.client.am.Statement.completeExecute(Unknown
Source)#012#011at
org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown
Source)#012#011at
org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown
Source)#012#011at
org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown
Source)#012#011at
org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown
Source)#012#011at
org.apache.derby.client.am.Statement.readExecuteCall(Unknown
Source)#012#011at
org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown
Source)#012#011at
org.apache.derby.client.am.PreparedStatement.executeX(Unknown
Source)#012#011... 21 more#012Caused by:
org.apache.derby.client.am.SqlException: Java exception: 'No space left on
device: java.io.IOException'.#012#011... 29 more



The available space on the target device is more than sufficient. We
already started the derby process with the Java Option -Djava.io.tmpdir
to point to a directory residing on a partition with more space. This did
not help.



Do you have any suggestions? Which directories are used for running the
backup and how much space needs to be availabe?



Thank you,

Stefan

Re: Speed up single INSERT INTO statement?

[Peter Ondruška]

Consider batching inserts and use larger log file.
On 9 May 2012 19:01, TXVanguard brett.den...@lmco.com wrote:


 I need to speed up a single INSERT statement in Derby.

 The statement has the form:

 INSERT INTO table (col1, col2) SELECT a, b FROM 

 In my application, it take about 10 seconds to insert 3000 records.

 I have experimented with turning off autocommit, adjusting
 derby.storage.pageCacheSize and derby.storage.pageSize, turning off indexes
 for the table, and a few other things, but nothing seems to affect the
 speed
 of the INSERT.

 Are there any other techniques I can try?  Would it be helpful to
 temporarily turn off constraints for the table?
 --
 View this message in context:
 http://old.nabble.com/Speed-up-single-INSERT-INTO-statement--tp33763645p33763645.html
 Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Random DRDA Error on IBM J9 JVM

[Peter Ondruška]

), (DRDAID = {1}), Java exception: ':
 java.lang.NullPointerException'.
 Wed Apr 04 14:46:24 EDT 2012 : Connection number: 2.
 
 Wed Apr 04 14:46:24 EDT 2012: Shutting down Derby engine
 Wed Apr 04 14:46:24 EDT 2012 : Unexpected exception:
  {0}
 Wed Apr 04 14:46:24 EDT 2012 : null
 java.lang.NullPointerException
 at
 org.apache.derby.impl.services.monitor.TopService.getService(TopService.java:128)
 at
 org.apache.derby.impl.services.monitor.BaseMonitor.shutdown(BaseMonitor.java:199)
 at org.apache.derby.jdbc.InternalDriver.connect(InternalDriver.java:241)
 at org.apache.derby.jdbc.EmbeddedDriver.connect(EmbeddedDriver.java:126)
 at
 org.apache.derby.impl.drda.NetworkServerControlImpl.blockingStart(NetworkServerControlImpl.java:913)
 at
 org.apache.derby.impl.drda.NetworkServerControlImpl.executeWork(NetworkServerControlImpl.java:2243)
 at
 org.apache.derby.drda.NetworkServerControl.main(NetworkServerControl.java:320)
 Wed Apr 04 14:46:24 EDT 2012 : Apache Derby Network Server - 10.8.2.2 -
 (1181258) shutdown


 On Tue, Mar 20, 2012 at 8:22 AM, Brandon L. Duncan
 brandonl.dun...@gmail.com wrote:

 Thanks Peter and Myrna for the feedback. Myrna, those Wiki links were
 helpful; I did not even know they existed. I'm going to get a J9 environment
 setup with 10.8.2.2 in the next day or two and see how that goes.

 Thank you both again,
 Brandon

 On Tue, Mar 20, 2012 at 3:47 AM, Peter Ondruška
 peter.ondruska+de...@kaibo.eu wrote:

 Brandon, I run 10.8.2.2 with J9:

 $ java -version
 java version 1.6.0
 Java(TM) SE Runtime Environment (build pap3260sr9fp2-20110627_03(SR9
 FP2))
 IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 AIX ppc-32
 jvmap3260sr9-20110624_85526 (JIT enabled, AOT enabled)
 J9VM - 20110624_085526
 JIT  - r9_20101028_17488ifx17
 GC   - 20101027_AA)
 JCL  - 20110530_01

 at that works, also SR9 FP3 work fine (this is on AIX).

 On Mon, Mar 19, 2012 at 6:18 PM, Brandon L. Duncan
 brandonl.dun...@gmail.com wrote:
  Thanks Peter.
 
  Do you remember if you upgraded to 10.8.2.2 or 10.8.1.2? We do have a
  test
  environment with 10.8.1.2, and are seeing similar results. I do agree
  that
  it seems to be a strange coexistence with Derby and J9. The IBM Classic
  JVM
  doesn't seem to have this issue, although IBM seems to have eliminated
  it in
  V7R1M0.
 
  Mon Mar 19 12:46:40 EDT 2012 : Apache Derby Network Server - 10.8.1.2 -
  (1095077) started and ready to accept connections on port 11527
  Mon Mar 19 12:46:44 EDT 2012 : Connection number: 1.
  
  Mon Mar 19 12:46:45 EDT 2012:
  Shutting down instance a816c00e-0136-2bda-791f-cab24f1a on database
  directory /database with class loader
  sun.misc.Launcher$AppClassLoader@376a376a
  Mon Mar 19 12:46:45 EDT 2012 Thread[DRDAConnThread_11,10,main] Cleanup
  action starting
  java.lang.NullPointerException
  at
  org.apache.derby.impl.store.raw.data.BaseDataFileFactory.stop(Unknown
  Source)
  at org.apache.derby.impl.services.monitor.TopService.stop(Unknown
  Source)
  at org.apache.derby.impl.services.monitor.TopService.shutdown(Unknown
  Source)
  at
  org.apache.derby.impl.services.monitor.BaseMonitor.bootService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.startProviderService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.findProviderAndStartService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.startPersistentService(Unknown
  Source)
  at
 
  org.apache.derby.iapi.services.monitor.Monitor.startPersistentService(Unknown
  Source)
  at org.apache.derby.impl.jdbc.EmbedConnection.bootDatabase(Unknown
  Source)
  at org.apache.derby.impl.jdbc.EmbedConnection.init(Unknown Source)
  at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown Source)
  at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
  at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
  at org.apache.derby.impl.drda.Database.makeConnection(Unknown Source)
  at
  org.apache.derby.impl.drda.DRDAConnThread.getConnFromDatabaseName(Unknown
  Source)
  at
  org.apache.derby.impl.drda.DRDAConnThread.verifyUserIdPassword(Unknown
  Source)
  at org.apache.derby.impl.drda.DRDAConnThread.parseSECCHK(Unknown
  Source)
  at
  org.apache.derby.impl.drda.DRDAConnThread.parseDRDAConnection(Unknown
  Source)
  at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
  Source)
  at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
  Cleanup action completed
  Mon Mar 19 12:46:45 EDT 2012 Thread[DRDAConnThread_11,10,main] Cleanup
  action starting
  java.lang.NullPointerException
  at
  org.apache.derby.impl.store.raw.data.BaseDataFileFactory.stop(Unknown
  Source)
  at org.apache.derby.impl.services.monitor.TopService.stop(Unknown
  Source)
  at org.apache.derby.impl.services.monitor.TopService.shutdown(Unknown
  Source

Re: Random DRDA Error on IBM J9 JVM

[Peter Ondruška]

.
 
 Mon Mar 19 12:46:47 EDT 2012: Shutting down Derby engine
 Mon Mar 19 12:46:47 EDT 2012 : Unexpected exception:
  {0}
 Mon Mar 19 12:46:47 EDT 2012 : null
 java.lang.NullPointerException
 at org.apache.derby.impl.services.monitor.TopService.getService(Unknown
 Source)
 at org.apache.derby.impl.services.monitor.BaseMonitor.shutdown(Unknown
 Source)
 at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
 at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
 at org.apache.derby.impl.drda.NetworkServerControlImpl.blockingStart(Unknown
 Source)
 at org.apache.derby.impl.drda.NetworkServerControlImpl.executeWork(Unknown
 Source)
 at org.apache.derby.drda.NetworkServerControl.main(Unknown Source)
 Mon Mar 19 12:46:47 EDT 2012 : Apache Derby Network Server - 10.8.1.2 -
 (1095077) shutdown


 On Mon, Mar 19, 2012 at 12:12 PM, Peter Ondruška
 peter.ondruska+de...@kaibo.eu wrote:

 I have seen the same problem and resolved by upgrading Derby to 10.8.
 There must be something strange in J9 and Derby coexistence. This
 happened very random.

 On Mon, Mar 19, 2012 at 4:55 PM, Brandon L. Duncan
 brandonl.dun...@gmail.com wrote:
  I was wondering if anyone came across this error before while attempting
  to
  establish a connection to Derby? The database seems to boot fine, but
  when a
  connection is attempted it just bombs out. It also is not
  always reproducible, as at times it will be fine, other times it errors
  with
  the exception below. The JVM is IBM's J9 implementation. I know Derby
  10.4.2.0 is a wee bit old, but upgrading this instance would not be easy
  at
  this point.
 
  java version 1.6.0
 
  Java(TM) SE Runtime Environment (build
  pap3260sr9ifix-20110211_02(SR9+IZ94423))
 
  IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 OS/400 ppc-32
  jvmap3260sr9-20101130 (JIT enabled, AOT enabled)
 
  J9VM - 20101124_069295
 
  JIT  - r9_20101028_17488ifx2
 
  GC   - 20101027_AA)
 
  JCL  - 20101119_01
 
 
  Derby Log:
 
  Apache Derby Network Server - 10.4.2.0 - (689064) started and ready to
  accept connections on port 1555 at 2012-03-15 13:27:29.860 GMT
  Connection number: 1.
 
  2012-03-15 13:27:34.382 GMT:
  Shutting down instance a816c00e-0136-168a-b0de-d934f54c
  
  2012-03-15 13:27:34.384 GMT Thread[DRDAConnThread_11,5,main] Cleanup
  action
  starting
  java.lang.NullPointerException
  at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.stop(Unknown
  Source)
  at org.apache.derby.impl.services.monitor.TopService.stop(Unknown
  Source)
  at org.apache.derby.impl.services.monitor.TopService.shutdown(Unknown
  Source)
  at
  org.apache.derby.impl.services.monitor.BaseMonitor.bootService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.startProviderService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.findProviderAndStartService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.startPersistentService(Unknown
  Source)
  at
 
  org.apache.derby.iapi.services.monitor.Monitor.startPersistentService(Unknown
  Source)
  at org.apache.derby.impl.jdbc.EmbedConnection.bootDatabase(Unknown
  Source)
  at org.apache.derby.impl.jdbc.EmbedConnection.init(Unknown Source)
  at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown Source)
  at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
  at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
  at org.apache.derby.impl.drda.Database.makeConnection(Unknown Source)
  at
  org.apache.derby.impl.drda.DRDAConnThread.getConnFromDatabaseName(Unknown
  Source)
  at
  org.apache.derby.impl.drda.DRDAConnThread.verifyUserIdPassword(Unknown
  Source)
  at org.apache.derby.impl.drda.DRDAConnThread.parseSECCHK(Unknown Source)
  at org.apache.derby.impl.drda.DRDAConnThread.parseDRDAConnection(Unknown
  Source)
  at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
  Source)
  at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
  Cleanup action completed
  2012-03-15 13:27:34.385 GMT Thread[DRDAConnThread_11,5,main] Cleanup
  action
  starting
  java.lang.NullPointerException
  at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.stop(Unknown
  Source)
  at org.apache.derby.impl.services.monitor.TopService.stop(Unknown
  Source)
  at org.apache.derby.impl.services.monitor.TopService.shutdown(Unknown
  Source)
  at
  org.apache.derby.impl.services.monitor.BaseMonitor.bootService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.startProviderService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.findProviderAndStartService(Unknown
  Source)
  at
 
  org.apache.derby.impl.services.monitor.BaseMonitor.startPersistentService(Unknown
  Source)
  at
 
  org.apache.derby.iapi.services.monitor.Monitor.startPersistentService(Unknown
  Source

Re: Random DRDA Error on IBM J9 JVM

[Peter Ondruška]

I have seen the same problem and resolved by upgrading Derby to 10.8.
There must be something strange in J9 and Derby coexistence. This
happened very random.

On Mon, Mar 19, 2012 at 4:55 PM, Brandon L. Duncan
brandonl.dun...@gmail.com wrote:
 I was wondering if anyone came across this error before while attempting to
 establish a connection to Derby? The database seems to boot fine, but when a
 connection is attempted it just bombs out. It also is not
 always reproducible, as at times it will be fine, other times it errors with
 the exception below. The JVM is IBM's J9 implementation. I know Derby
 10.4.2.0 is a wee bit old, but upgrading this instance would not be easy at
 this point.

 java version 1.6.0

 Java(TM) SE Runtime Environment (build
 pap3260sr9ifix-20110211_02(SR9+IZ94423))

 IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 OS/400 ppc-32
 jvmap3260sr9-20101130 (JIT enabled, AOT enabled)

 J9VM - 20101124_069295

 JIT  - r9_20101028_17488ifx2

 GC   - 20101027_AA)

 JCL  - 20101119_01


 Derby Log:

 Apache Derby Network Server - 10.4.2.0 - (689064) started and ready to
 accept connections on port 1555 at 2012-03-15 13:27:29.860 GMT
 Connection number: 1.

 2012-03-15 13:27:34.382 GMT:
 Shutting down instance a816c00e-0136-168a-b0de-d934f54c
 
 2012-03-15 13:27:34.384 GMT Thread[DRDAConnThread_11,5,main] Cleanup action
 starting
 java.lang.NullPointerException
 at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.stop(Unknown
 Source)
 at org.apache.derby.impl.services.monitor.TopService.stop(Unknown Source)
 at org.apache.derby.impl.services.monitor.TopService.shutdown(Unknown
 Source)
 at org.apache.derby.impl.services.monitor.BaseMonitor.bootService(Unknown
 Source)
 at
 org.apache.derby.impl.services.monitor.BaseMonitor.startProviderService(Unknown
 Source)
 at
 org.apache.derby.impl.services.monitor.BaseMonitor.findProviderAndStartService(Unknown
 Source)
 at
 org.apache.derby.impl.services.monitor.BaseMonitor.startPersistentService(Unknown
 Source)
 at
 org.apache.derby.iapi.services.monitor.Monitor.startPersistentService(Unknown
 Source)
 at org.apache.derby.impl.jdbc.EmbedConnection.bootDatabase(Unknown Source)
 at org.apache.derby.impl.jdbc.EmbedConnection.init(Unknown Source)
 at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown Source)
 at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
 at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
 at org.apache.derby.impl.drda.Database.makeConnection(Unknown Source)
 at org.apache.derby.impl.drda.DRDAConnThread.getConnFromDatabaseName(Unknown
 Source)
 at org.apache.derby.impl.drda.DRDAConnThread.verifyUserIdPassword(Unknown
 Source)
 at org.apache.derby.impl.drda.DRDAConnThread.parseSECCHK(Unknown Source)
 at org.apache.derby.impl.drda.DRDAConnThread.parseDRDAConnection(Unknown
 Source)
 at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
 at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
 Cleanup action completed
 2012-03-15 13:27:34.385 GMT Thread[DRDAConnThread_11,5,main] Cleanup action
 starting
 java.lang.NullPointerException
 at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.stop(Unknown
 Source)
 at org.apache.derby.impl.services.monitor.TopService.stop(Unknown Source)
 at org.apache.derby.impl.services.monitor.TopService.shutdown(Unknown
 Source)
 at org.apache.derby.impl.services.monitor.BaseMonitor.bootService(Unknown
 Source)
 at
 org.apache.derby.impl.services.monitor.BaseMonitor.startProviderService(Unknown
 Source)
 at
 org.apache.derby.impl.services.monitor.BaseMonitor.findProviderAndStartService(Unknown
 Source)
 at
 org.apache.derby.impl.services.monitor.BaseMonitor.startPersistentService(Unknown
 Source)
 at
 org.apache.derby.iapi.services.monitor.Monitor.startPersistentService(Unknown
 Source)
 at org.apache.derby.impl.jdbc.EmbedConnection.bootDatabase(Unknown Source)
 at org.apache.derby.impl.jdbc.EmbedConnection.init(Unknown Source)
 at org.apache.derby.jdbc.Driver40.getNewEmbedConnection(Unknown Source)
 at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
 at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
 at org.apache.derby.impl.drda.Database.makeConnection(Unknown Source)
 at org.apache.derby.impl.drda.DRDAConnThread.getConnFromDatabaseName(Unknown
 Source)
 at org.apache.derby.impl.drda.DRDAConnThread.verifyUserIdPassword(Unknown
 Source)
 at org.apache.derby.impl.drda.DRDAConnThread.parseSECCHK(Unknown Source)
 at org.apache.derby.impl.drda.DRDAConnThread.parseDRDAConnection(Unknown
 Source)
 at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
 at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
 Cleanup action completed
 2012-03-15 13:27:34.388 GMT Thread[DRDAConnThread_11,5,main] (DATABASE =
 ../../database), (DRDAID = {1}), Java exception: ':
 java.lang.NullPointerException'.
 Connection 

Re: AW: Does derby ned allways a rollback or commt?

[Peter Ondruška]

I would extend your question: is there any difference in commit or rollback
after single select statement?
Dne 22.11.2011 12:45 malte.kem...@de.equens.com napsal(a):

 **

 So what would be the best practice using a rollback or a commit when just
 reading a database?

 ** **

 Malte
  --

 *Von:* florin.herin...@sungard.com [mailto:florin.herin...@sungard.com]
 *Gesendet:* Dienstag, 22. November 2011 12:14
 *An:* derby-user@db.apache.org
 *Betreff:* AW: Does derby ned allways a rollback or commt?

 ** **

 That is not derby specific. In any db selects are part of transactions
 too. So either you enable autocommit or you explicitly commit your
 transaction(s) before releasing the connection. Commit will release the
 locks you acquired on the db (read locks if you haven’t modified anything).
 

 ** **

 Cheers,

 ** **

 **Florin**

 ** **

 *Von:* malte.kem...@de.equens.com [mailto:malte.kem...@de.equens.com]
 *Gesendet:* Dienstag, 22. November 2011 12:09
 *An:* derby-user@db.apache.org
 *Betreff:* Does derby ned allways a rollback or commt?

 ** **

 Hi,

 I have an application using embedded derby (10.8.1.2). In the program I
 use a little routine that ends with rollback, commit or nothing at all, by
 using a parameter.

 In a certain mode I just do a select, so As far I concern I don't need to
 do neither rollback nor commit, since it is just a select.

 But I get then always an Exception:

 ** **

 Eine Verbindung kann nicht beendet werden, solange noch eine Transaktion
 aktiv ist.
 

   at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
 Source)
 

   at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
 Source)
 

   at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
 Source)
 

   at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
 Source)
 

   at
 org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
 
 

   at
 org.apache.derby.impl.jdbc.EmbedConnection.checkForTransactionInProgress(Unknown
 Source)
 

   at org.apache.derby.impl.jdbc.EmbedConnection.close(Unknown Source)*
 ***
 

 ** **

 That obviously is the code 25001

 ** **

 Even though I initialize all my prepared statements that also are inserts
 and updates, in this mode only a select statement is used.

 How come I get such a Exception. It seems that I have to commit or
 rollback always I quit a derby program even though no change has occurred
 on particular database

 ** **

 Or is there something I should care about?

 Equens SE

 Malte Kempff
 

 Core Applications/ Change Bulk Payments

 Tel:  +49(0)69/58 99 93 - 60417

 Fax: +49(0)69/58 99 93 - 60290

 mail to:malte.kem...@de.equens.com 

 ** **

 Equens SE
 Mainzer Landstraße 201
 60326 Frankfurt
 Germany
 Tel: +49(0)69-589993-09
 Fax: +49(0)69-589993-60300
 Amtsgericht Frankfurt HRB 84 429
 http://www.equens.com 
 

 i...@de.equens.com
 


 Vorstand: Michael Steinbach (Vorstandsvorsitzender)
 Alessandro Baroni
 Dr. Götz Möller
 Jan Sonneveld
 Aufsichtsratsvorsitzender: Erik Dralans
 Sitz: 3526 LB Utrecht, Niederlande, Eendrachtlaan 315, Handelskammer
 3022.0519

 ** **

Re: OutOfMemoryException when executing 180,000 batches

[Peter Ondruška]

Of course you get OOME if you use memory only database and your data size
plus overheads exceeds heap.
Dne 8.11.2011 23:44 Pavel Bortnovskiy pbortnovs...@jefferies.com
napsal(a):

 Is it unusual that Derby (used in-memory only) seems to throw an out of
 memory exception when executing 180,000 batched insert statements?
 (The JVM was started with -Xms1024m -Xmx2048m):

 Caused by: java.sql.SQLException: Java exception: 'GC overhead limit
 exceeded: java.lang.OutOfMemoryError'.
at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
 Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at
 org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
 Source)
at
 org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown
 Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setDate(Unknown
 Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setDate(Unknown
 Source)
at org.apache.derby.iapi.types.SQLDate.setInto(Unknown Source)
at
 org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeBatchElement(Unknown
 Source)

 Are there memory limitations for Derby running in the in-memory only mode?
 Is there anything that can be done to avoid getting such errors?

 Thanks,
 Pavel.


 Jefferies archives and monitors outgoing and incoming e-mail. The contents
 of this email, including any attachments, are confidential to the ordinary
 user of  the email address to which it was addressed. If you are not the
 addressee of this email you may not copy, forward, disclose or otherwise
 use it or any part of it in any form whatsoever. This email may be produced
 at the request of regulators or in connection with civil litigation.
 Jefferies accepts no liability for any errors or omissions arising as a
 result of transmission. Use by other than intended recipients is
 prohibited. In the United Kingdom, Jefferies operates as Jefferies
 International Limited; registered in England: no. 1978621; registered
 office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies
 International Limited is authorised and regulated by the Financial Services
 Authority.

Re: Setting Derby NetworkServer JVM size using NetworkServerControl

[Peter Ondruška]

append:
DERBY_OPTS=-Xms1g -Xmx1g
to bin\derby_common.bat

On Wed, Nov 9, 2011 at 5:39 PM, Hawley, Dan dan.haw...@lmco.com wrote:
 Hi,

 I have an urgent problem that I have not been able to solve by myself.  I am 
 populating many derby databases with data in preparation for going live with 
 a new application.  After opening about 10 databases, the Derby Network 
 Server crashes with an java.lang.OutOfMemoryError.  See derby log below.  We 
 start the Derby Network Server with a batch file that I also have below.  I 
 think I need to allocate more memory to the Derby Network Server's JVM but I 
 can't see how to do that using the NetworkServerControl to start the server.  
 Does anyone know how to do this?  What is a reasonable memory allocation for 
 the JVM?  Another question I have is: Am I doing something wrong having by 
 having Derby load this many databases?  I am using Hibernate JPA with Derby 
 for my application.  I am closing the EntityManagerFactory and EntityManager 
 objects when I close the application.

 Your assistance and advice is greatly appreciated.  Thanks, Dan

 =start derby server.bat file=
 f:
 cd f:\epns\database\EditorDatabase
 %DERBY_HOME%\bin\NetworkServerControl start -h secapp -p 1527


 ===derby server crashed = ran out of memory ==


 2011-11-08 21:53:28.187 GMT : Apache Derby Network Server - 10.6.1.0 - 
 (938214) started and ready to accept connections on port 1527
 
 2011-11-08 22:12:56.443 GMT:
  Booting Derby version The Apache Software Foundation - Apache Derby - 
 10.6.1.0 - (938214): instance a816c00e-0133-852b-e9db-003a53a0
 on database directory F:\EPNS\database\EditorDatabase\DBEDIT-B   with class 
 loader sun.misc.Launcher$AppClassLoader@fabe9

 Database Class Loader started - derby.database.classpath=''
 
 2011-11-08 22:26:30.306 GMT:
  Booting Derby version The Apache Software Foundation - Apache Derby - 
 10.6.1.0 - (938214): instance 274b9034-0133-852b-e9db-003a53a0
 on database directory F:\EPNS\database\EditorDatabase\011103AD   with class 
 loader sun.misc.Launcher$AppClassLoader@fabe9

 Database Class Loader started - derby.database.classpath=''
 
 2011-11-08 22:28:27.370 GMT:
  Booting Derby version The Apache Software Foundation - Apache Derby - 
 10.6.1.0 - (938214): instance 636e50d2-0133-852b-e9db-003a53a0
 on database directory F:\EPNS\database\EditorDatabase\011103AC   with class 
 loader sun.misc.Launcher$AppClassLoader@fabe9

 Database Class Loader started - derby.database.classpath=''
 
 2011-11-08 22:31:06.678 GMT:
  Booting Derby version The Apache Software Foundation - Apache Derby - 
 10.6.1.0 - (938214): instance f7f21170-0133-852b-e9db-003a53a0
 on database directory F:\EPNS\database\EditorDatabase\011100BM   with class 
 loader sun.misc.Launcher$AppClassLoader@fabe9

 Database Class Loader started - derby.database.classpath=''
 
 2011-11-08 22:33:08.717 GMT:
  Booting Derby version The Apache Software Foundation - Apache Derby - 
 10.6.1.0 - (938214): instance 64d6d20e-0133-852b-e9db-003a53a0
 on database directory F:\EPNS\database\EditorDatabase\011102-A   with class 
 loader sun.misc.Launcher$AppClassLoader@fabe9

 Database Class Loader started - derby.database.classpath=''
 
 2011-11-08 22:37:12.133 GMT:
  Booting Derby version The Apache Software Foundation - Apache Derby - 
 10.6.1.0 - (938214): instance 2a1c92ac-0133-852b-e9db-003a53a0
 on database directory F:\EPNS\database\EditorDatabase\011200-Y   with class 
 loader sun.misc.Launcher$AppClassLoader@fabe9

 Database Class Loader started - derby.database.classpath=''
 
 2011-11-08 22:41:16.888 GMT:
  Booting Derby version The Apache Software Foundation - Apache Derby - 
 10.6.1.0 - (938214): instance c7c3534a-0133-852b-e9db-003a53a0
 on database directory F:\EPNS\database\EditorDatabase\011201-F   with class 
 loader sun.misc.Launcher$AppClassLoader@fabe9

 Database Class Loader started - derby.database.classpath=''
 
 2011-11-08 22:43:54.771 GMT:
  Booting Derby version The Apache Software Foundation - Apache Derby - 
 10.6.1.0 - (938214): instance bdcb13e8-0133-852b-e9db-003a53a0
 on database directory F:\EPNS\database\EditorDatabase\DBITST-A   with class 
 loader sun.misc.Launcher$AppClassLoader@fabe9

 Database Class Loader started - derby.database.classpath=''
 
 2011-11-08 22:48:35.977 GMT:
  Booting Derby version The Apache 

Re: Derby secure by default

[Peter Ondruška]

Rick, I’d vote for secure by default in v.11. Thanks

On Mon, Sep 19, 2011 at 6:39 PM, Rick Hillegas rick.hille...@oracle.com wrote:
 The Derby developers are considering introducing a single master security
 property. Turning this property on will enable most Derby security
 mechanisms:

 1) Authentication - Will be on, requiring username/password credentials at
 connection time. Derby will supply a default authentication mechanism.

 2) SQL authorization - Will be on, hiding a user's data from other people.
 In addition, Derby will support more SQL Standard protections for Java
 routines.

 3) File permissions - Will be tightened as described by DERBY-5363.

 4) PUBLIC -This keyword will not be allowed as a user name.

 5) SSL/TLS encryption - Will shield client/server traffic.

 6) Server administration -  Will require credentials.

 When the property is off, Derby will behave as it does today:
 Authentication, authorization, and network encryption will be off, file
 permissions will inherit defaults from the account which runs the VM, PUBLIC
 will be a legal user name, and server administration won't need credentials.

 This new master property will make it easier to configure a more secure
 application. We want to introduce the property in an upcoming 10.x release,
 where it will default to being off. That means that it won't cause
 compatibility problems.

 Later on, we might change the default for this property so that it would
 normally be turned on. This would make Derby more secure out of the box at
 the cost of breaking existing applications. Many applications would need to
 explicitly turn the property off in order to run as they did previously.
 Release notes would document this behavioral change and we would bump the
 major release id from 10 to 11 in order to call attention to the change.

 We would like your feedback on this trade-off between security out of the
 box versus disruption. Should this extra security be enabled by default?

 Thanks,
 -Rick

Re: Opinions on new security feature requested

[Peter Ondruška]

+1 for more restrictive permissions. Actually when I run Derby on Unix
it runs under own user+group and database files are not accessible by
others.

On Fri, Sep 2, 2011 at 7:31 PM, Dag H. Wanvik dag.wan...@oracle.com wrote:
 Hi folks,

 we are always working to make Derby more secure; in this day and age,
 security is ever more on people's minds for obvious reasons;
 IT systems are everywhere and the bad guys never tire of finding new holes
 to break them. Up till now, Derby creates database files and logs using the
 default operating system permission in effect. On Unix/linux this is
 controlled by the umask of the process starting the Derby engine, be in
 embedded or a standalone Derby network server. Similarly on Windows, NTFS
 has a security model can be configured to give various default permissions.

 Now, often the defaults will allow other (than the one starting the VM) OS
 users the permissions to read and possibly write to the database files. This
 can be intention to allow several users to boot the same (shared) database),
 or it can be accidental. In DERBY-5363 we have been discussion use cases and
 scenarios for when it would be desirable to let Derby be more secure than
 the default permissions. Other database also do this, e.g. PostGreSQL, MS
 SQL server.

 Typically, only the OS user creating the database would have access (default
 behavior) unless one told the database to be lax and not worry about
 tightening up the default OS permissions.
 Obviously, one can achieve the same restrictive permissions, by setting the
 umask to 0077 on Unix, or tweak the NTFS settings similarly (Windows), but
 this requires some care and presumes that the users remembers to do so (many
 people don't grok the NTFS security model..)

 To be clear, one would be able to enable/disable this extra security by
 providing Derby with a property setting, so the question is really what is
 the msot appropriate default: use lax permissions (rely on the user having
 tightened up be fore starting the VM), or use the new proactive secure
 settings proposed in DERBY-5363.

 Secure default pros:
 - users will get better security by default. If one needs to share the
 database files, one can use a property to get old, lax behavior.
 - no need to change startup scripts to get better security

 Secure default cons:
 - upwards compatibility: if an installation relies on sharing database
 files, on would have to start using a property after upgrading.
 - requires at least Java 6 (on Unix), Java 7 on Windows/NTFS to work (an
 incentive to upgrade, though :-)

 In the discussion it as been suggested that many deployments, especially of
 embedded Derby, rely on several OS users having permissions, so changing the
 default Derby behavior would cause upgrade issues. Probably for most
 client/server deployments, where the server is started from the command
 line, it would be the same OS user starting the Derby server every time. In
 mixed deployments (embedded, but the server is sometimes started via the
 API), the latter may not hold true.

 A possible trade-off between the concerns would thus be to start embedded
 with the exisiting, lax permissions by default, but start the server from
 the command line with a secure (restrictive) default. In both cases, one
 would get the opposite behavior by providing a system property on VM
 startup.

 Before we settle the discussion on this, it would be good to hear what you
 think! Thanks!

 Dag

Re: nulls in prepared statement

[Peter Ondruška]

You would only subclass PreparedStatement as public
MyPreparedStatement extends PreparedStatement and override setString
method. And in your code use replace PrepareStatement with
MyPreparedStatement.

On Thu, Jul 21, 2011 at 5:31 PM, Tomcat Programmer
tcprogram...@yahoo.com wrote:


 Well may be you won't have less ugly code, but at least it will be hidden
 ;-)

 I think the easiest way it to use you own PreparedStatement class. So you
 can do
 any special treatment or workaround in a centralized and unique place.

 Hi JYL this is a very insightful and interesting solution, which I would not
 have thought of.  Is this as simple as creating my own class with the derby
 version as its superclass and then just overriding the method?  That seems
 too easy .. is there any catches or pitfalls you can give me a heads up on?
  Thanks again for your help!
 Adding one specific thought:  if I extend the class, how do I get my version
 to be instantiated? (this is a web application and so it will be picking up
 driver registration and so forth from JNDI.)

Re: nulls in prepared statement

[Peter Ondruška]

Eclipselink or Hibernate might help if you want less ugly code.
Dne 20.7.2011 23:38 Tomcat Programmer tcprogram...@yahoo.com napsal(a):



 You must explicitly set value to null:

 if (cobj.getPartNo()==null) pstmt.setNull(1, java.sql.Types.VARCHAR);
 else pstmt.setString(1,cobj.getPartNo());


 Hi Peter, thanks for responding but I am aware of this as I indicated in
my post. You realize how tedious this will be with any significant number of
fields?  What I am asking about is if there is an alternate solution.

 Thanks in advance,
 TC

Re: nulls in prepared statement

[Peter Ondruška]

You must explicitly set value to null:

if (cobj.getPartNo()==null) pstmt.setNull(1, java.sql.Types.VARCHAR);
else pstmt.setString(1,cobj.getPartNo());

On Wed, Jul 20, 2011 at 4:42 AM, Tomcat Programmer
tcprogram...@yahoo.com wrote:
 Hi Everyone,

 I've tried doing research on this on the web but can't seem to find a clear 
 answer, I hope someone can help.  I'm currently working on an application 
 using Derby where the data being entered is optional for a lot of the fields. 
 The columns in the tables are defined using VARCHAR data type and accept 
 nulls.  If, in the code to do the insert or update,  someone leaves a field 
 empty in the application it will result in a null value being passed through 
 setString method like this:

 pstmt.setString(1,cobj.getPartNo());

 where the getPartNo() method returns null.  When this happens I get a null 
 pointer exception.  Is there a configuration parameter for Derby to accept 
 Java nulls and treat them as SQL nulls?   If not, the code is going to get 
 incredibly ugly fast if each value has to be checked for null and then issue 
 a separate setNull() method call.

 Thanks in advance,
 TC

Re: How to unlock a table in derby

[Peter Ondruška]

Rollback might work as well :)
Dne 18.7.2011 15:57 Lahiru Gunathilake glah...@gmail.com napsal(a):
 I will try it, thank you Byan !

 Lahiru

 On Fri, Jul 15, 2011 at 8:47 PM, Bryan Pendleton 
bpendleton.de...@gmail.com
 wrote:

 I execute query lock table table name
 in share mode but I cannot see any documentation on how to unlock a
derby
 table.


 Commit.

 thanks,

 bryan

Re: Does anyone want to run Derby 10.9 on JVM 1.4 or on CDC/FP 1.1?

[Peter Ondruška]

Please drop 1.4
Dne 27.6.2011 16:05 Rick Hillegas rick.hille...@oracle.com napsal(a):
 The 1.4 JVM has not been supported as a free platform for some time
 (although I believe you can buy a support contract for 1.4 if you need
 to). Does anyone plan to run Derby 10.9 on this platform? Does anyone
 plan to run Derby 10.9 on the related small device CDC/FP 1.1 platform?
 For the next feature release, we are considering dropping support for
 one or both of these platforms. Your responses will help us make a
decision.

 Thanks,
 -Rick

Re: error executing multiple insert statements

[Peter Ondruška]

Run this in ij. That is for running SQL scripts. Or execute each
statement (without ;) separately.

On Thu, Jun 16, 2011 at 8:03 PM, Lothar Krenzien lkrenz...@web.de wrote:
 Hello,

 I'd like to execute multiple insert statements over JDBC at once, but can't 
 get it work ;(

 Here a small demo:

 ...

 insert into POSITIONTEMPLATE
 (ID,VERSION,NAME,PRINT)
 values
 (NEXT VALUE FOR unique_id,0,'abc',1);

 insert into POSITIONTEMPLATE
 (ID,VERSION,NAME,PRINT)
 values
 (NEXT VALUE FOR unique_id,0,'efg',0);


 If I execute every statement at once it works fine but if I execute all 
 statements at once I always get the following error:

 Encountered ; at line 4 column 9.

 I know that it means that there is a problem with the ; sign - but that 
 can't be right or ?
 Derby 10.8.1.2

 Thank you

Re: performance issue on 64 bit JVM

[Peter Ondruška]

Unless you need to address heap over 32bit JVM limits use 32bit JVM.
Just my EUR .02 :-)

2011/6/3 Arnaud Masson amas...@gmail.com:
 64-bit uses more memory,
 so if your Xmx is too small,
 the 64-bit version may have more GC overhead.

 You can increase Xmx
 or activate compressed-pointers to have a better memory usage.

 (Also the max heap size must fit in physical memory, otherwise the swap
 on disk could kill perfs.)


 Le 03/06/2011 04:09, QA Wang Yang a écrit :
 Dear Derby

 Could you tell me how tuning the performance issue?

 This is running sample query to fetch data from Derby database

 On my dual-core XP x-32 laptop the Corporate Overview takes 406(ms).
 But we saw on the dual quad core it took 22973 (ms).

 I don't know why the performance became bad on my powerful machine.

 This is my test detail

 Query:
 select * from Customer;
 select * from product;
 select * from order;
 select * from Customer1;

 Dual-core XP SP3 32 bit,JDK1.6.0_25 32 bit,
 Dual quad core Windows 2008 64 bit, JDK1.6.0_25 64 bit,


 *32 bit Dual core
 Fetched record size: 1413
 Fetch record time: 250 (ms)
 Fetched record size 8
 Fetch record time: 15 (ms)
 Fetched record size: 6
 Fetch record time: 0 (ms)
 Fetched record size: 1413 records
 Fetch record time: 141 (ms)
 total 406 ms

 **64 bit Dual quad core
 Fetched record size: 1413
 Fetch record time: 11483 (ms)
 Fetched record size 8
 Fetch record time: 3 (ms)
 Fetched record size: 6
 Fetch record time: 4 (ms)
 Fetched record size: 1413
 Fetch record time: 11484 (ms)
 total 22973 ms

 Thanks a lot



 Wangyang
 QA Group
 Jinfonet Software, Inc.

 www.jinfonet.com
 2nd floor on the east of BEC Theatre
 135 Xi Zhi Men Wai Street, Xicheng District Beijing, China 100044
 yang.w...@support.jinfonet.com
 86-10-68316633

 JReport
 Embedded Reporting for Java Applications

Re: Inserting control characters in SQL

[Peter Ondruška]

Have you tried \b ?

Peter
On Mar 11, 2011 4:41 PM, John English john.fore...@gmail.com wrote:
 I have a DDL schema which I am processing using IJ. I want to insert a
 row into a table containg a backspace character:

 CREATE TABLE foo (name VARCHAR(20), value VARCHAR(200));
 INSERT INTO foo VALUES('first',bs);

 where bs is an actual backspace character. I foolishly tried CHAR(8)
 but of course this doesn't do it. Obviously I could write a Java program
 that does this, but this means reinventing the IJ wheel. Is there any
 existing way to do this?

 TIA,

 
 John English | My old University of Brighton home page is still here:
 | http://www.it.brighton.ac.uk/staff/je
 

Re: inserting missing values

[Peter Ondruška]

You need to check using wasNull method whether the value is null. See jdbc
javadocs for ResultSet class.
On Dec 14, 2010 8:46 PM, Patrick Meyer meyer...@gmail.com wrote:
 What is the best way to handle missing values. For example, suppose I have
 an array that I want to insert into a table, like double[] row = {1.0,
2.0,
 MISSING, 4.0} where MISSING indicates a value that is missing. (I realize
 this is not a valid double value). I have been using a prepared statement
to
 set a null value anytime I have missing data like, pstmt.setNull(i+1,
 Types.DOUBLE); However, the problem is that using rs.getDouble(3) returns
a
 value of zero instead of null. The problem is that zero is a legitimate
 double value, not a missing or null value. Is it better to inset data
using
 Double.NAN like pstmt.setDouble(i+1, Double.NaN);? What is the best way to
 handle missing data?

 Thanks

Re: Hot backups

[Peter Ondruška]

Yes,see docs.
On Dec 3, 2010 4:23 PM, Clemens Wyss clemens...@mysign.ch wrote:
 Does Derby support hot backup(s)?

 Regards
 Clemens

Re: Hot backups

[Peter Ondruška]

No prob. Was on mobile while replying to your msg.

On Fri, Dec 3, 2010 at 6:03 PM, Clemens Wyss clemens...@mysign.ch wrote:
 RTFM
 - http://db.apache.org/derby/docs/dev/adminguide/derbyadmin.pdf
 sorry  thx

Von: Peter Ondruška [mailto:peter.ondru...@gmail.com]
Gesendet: Freitag, 3. Dezember 2010 16:38
An: Derby Discussion
Betreff: Re: Hot backups

Yes,see docs.
On Dec 3, 2010 4:23 PM, Clemens Wyss clemens...@mysign.ch wrote:
 Does Derby support hot backup(s)?

 Regards
 Clemens




-- 
Peter

Re: Language of error message

[Peter Ondruška]

It is determined by JRE's system locale. You probably use German
locale in your system's settings. If you are on Unix, set
LC_ALL=en_US, if you are on Windows go to regional settings in control
panel. If you cannot any of those try using your application with
additional JRE parameter -Dlanguage=en.

On Sat, Nov 27, 2010 at 3:50 PM, Thomas thomas.k.h...@t-online.de wrote:
 Hi,

 can someone please advise what is determining the language which will be seen 
 in
 error messages? Is this depend on the locale of the machine? I am currently
 seeing messages in german, but would like to switch to english, but don't know
 how to do that.

 Thanks in advance
 Thomas





-- 
Peter

Re: Language of error message

[Peter Ondruška]

Yeah,that's what I ment... just forgot user.
On Nov 27, 2010 4:23 PM, Marco Ferretti marco.ferre...@gmail.com wrote:
 set locale from vm options?

 -Duser.language=language -Duser.region=region

 -- Marco (from iPhone)

 On Nov 27, 2010, at 3:50 PM, Thomas thomas.k.h...@t-online.de wrote:

 Hi,

 can someone please advise what is determining the language which will be
seen in
 error messages? Is this depend on the locale of the machine? I am
currently
 seeing messages in german, but would like to switch to english, but don't
know
 how to do that.

 Thanks in advance
 Thomas

Re: NFS and Derby

[Peter Ondruška]

You could use NFS mounted read only databases as you can do so with
CD/DVD based media.

The risks with read-write databases on NFS devices is (was) that in
the old days of UDP protocol based NFS client/servers your connection
may easily break. It is not the case anymore with decent operating
systems (Solaris for example) and good NFS servers (again mostly
Solaris based or those from famous vendor) and good highly available
network infrastructure. Nowadays your servers disks are likely network
connected anyway (FC SAN, iSCSI).

On Thu, Nov 11, 2010 at 5:18 PM, Donald McLean dmclea...@gmail.com wrote:
 A local database on an NFS mounted disk? I would never consider such a 
 thing.

 My experience with NFS mounted resources is that network congestion
 can cause all sorts of nasty side effects. Even something as simple as
 an unexpectedly slow read or write can cause unanticipated cascading
 failure conditions. And no matter what value is used for a timeout,
 you can pretty much guarantee that it will be exceeded eventually.

 I realize that this doesn't address Derby specific concerns such as
 database corruption. Fortunately, I have no experience with that.

 Donald

 On Thu, Nov 11, 2010 at 10:56 AM, Kathey Marsden
 kmarsdende...@sbcglobal.net wrote:
 I have always told users they have to have their databases on a local disk
 to ensure data integrity and that  a system crash for an NFS mounted
 database could cause fatal corruption, but had a user this morning take me
 to task on this and ask me to explain exactly why.  I gave my general
 response about not being able to guarantee a sync to disk over the network,
 but want to have a more authoritative reference for why  you cannot count on
 an NFS mounted disk although I did find several places where the sync option
 favors data integrity which certainly doesn't sound like a guarantee.
  Does anyone know a good general reference I can use on this topic to
 support my you gotta use a local disk mantra.


 Also I think our documentation on this topic should be a bit stronger.
  Currently we just say it may not work and probably should be clearer that
 data corruption could occur.  I will file an issue to beef up the language
 based on the conversation in this thread.

 http://db.apache.org/derby/docs/10.5/devguide/cdevdvlp40350.html




-- 
Peter

Re: performance: Derby vs H2

[Peter Ondruška]

Rayson, some/most of us are looking for best performance AND best
stability/scalability/tools/etc.

When I look for performance I usually go with Berkeley DB JE ;-)

On Thu, Apr 22, 2010 at 7:14 PM, Rayson Ho raysonlo...@gmail.com wrote:
 On Thu, Apr 22, 2010 at 12:06 PM, bruehlicke bruehli...@gmail.com wrote:
 It is a unconstructive question to ask. It depends on many
 requirements and situations.

 I guess I just got an unconstructive answer from you :-D

 I am trying to find out whether the benchmarks they used are fair, and
 whether the Derby developers know that H2 is faster.

 Even with unfair benchmarks that make H2 really shine, can't Derby
 learn from H2??

 And no, I don't have an application that I need to choose between Derby or H2.

 Rayson




 Is a car faster than an airplane ? Well
 if I have to go to the next corner shopping mall to pick up some milk
 your forgot, this is most likely the case.

 So try it out - and if you think one is faster than the other for your
 particular situation - go for it. For me the maturity, support, proven
 deployments and scalability together with features available are the
 key driving forces.

 B-)

 On Thu, Apr 22, 2010 at 11:47 AM, Rayson Ho raysonlo...@gmail.com wrote:
 Is it really true that H2 is faster than Derby??

 http://en.wikipedia.org/wiki/Apache_Derby

 A year ago, I tried to remove the section that says that H2 is faster,
 but someone always added it back into the article. And besides me,
 seems like no one really care about the Comparison to other embedded
 SQL Java databases section.

 http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks

 Is it a well-known fact that H2 is always faster??

 And there is also H2's benchmark page:

 http://www.h2database.com/html/performance.html

 Is it a fair comparsion??

 Rayson

Re: performance: Derby vs H2

[Peter Ondruška]

Not really. By stability I mean being happy Derby user since its
Cloudscape ages..

On Thu, Apr 22, 2010 at 7:45 PM, Rayson Ho raysonlo...@gmail.com wrote:
 On Thu, Apr 22, 2010 at 12:24 PM, Peter Ondruška
 peter.ondru...@gmail.com wrote:
 Rayson, some/most of us are looking for best performance AND best 
 stability/scalability/tools/etc.

 Thanks for the reply, Peter.

 By stability, you mean ACID kind of stability??

 Rayson




 When I look for performance I usually go with Berkeley DB JE ;-)

 On Thu, Apr 22, 2010 at 7:14 PM, Rayson Ho raysonlo...@gmail.com wrote:
 On Thu, Apr 22, 2010 at 12:06 PM, bruehlicke bruehli...@gmail.com wrote:
 It is a unconstructive question to ask. It depends on many
 requirements and situations.

 I guess I just got an unconstructive answer from you :-D

 I am trying to find out whether the benchmarks they used are fair, and
 whether the Derby developers know that H2 is faster.

 Even with unfair benchmarks that make H2 really shine, can't Derby
 learn from H2??

 And no, I don't have an application that I need to choose between Derby or 
 H2.

 Rayson




 Is a car faster than an airplane ? Well
 if I have to go to the next corner shopping mall to pick up some milk
 your forgot, this is most likely the case.

 So try it out - and if you think one is faster than the other for your
 particular situation - go for it. For me the maturity, support, proven
 deployments and scalability together with features available are the
 key driving forces.

 B-)

 On Thu, Apr 22, 2010 at 11:47 AM, Rayson Ho raysonlo...@gmail.com wrote:
 Is it really true that H2 is faster than Derby??

 http://en.wikipedia.org/wiki/Apache_Derby

 A year ago, I tried to remove the section that says that H2 is faster,
 but someone always added it back into the article. And besides me,
 seems like no one really care about the Comparison to other embedded
 SQL Java databases section.

 http://en.wikipedia.org/wiki/Talk:Apache_Derby#Benchmarks

 Is it a well-known fact that H2 is always faster??

 And there is also H2's benchmark page:

 http://www.h2database.com/html/performance.html

 Is it a fair comparsion??

 Rayson

Re: Derby Char Column Type Versus VarChar

[Peter Ondruška]

I think Oracle Database and Derby behavior will be same. CHAR(size)
will preallocate size*characters in database page/block whereas
VARCHAR(size) will not. Maybe if you give us any hint what are you
trying to do we could help better. Peter

On Mon, Apr 5, 2010 at 3:41 PM, Mamatha Kodigehalli Venkatesh
mamatha.venkat...@ness.com wrote:
 Hello,



 Please let us know what will be the performance impact when I use VarChar
 (4000) instead of Char.

 I have found that the inserts to the table which contains this change are
 very slow…



 But on oracle, there is not much of impact on this.



 Based on the business requirement I need to use VarChar.



 Any suggestions will be of great help.



 Thanks

 Mamatha

Re: Case Sensitivity

[Peter Ondruška]

select
Id as id,
Code as code,
TypeStr as typeStr
  from MyTable

On Wed, Mar 24, 2010 at 10:30 PM, Pavel Bortnovskiy
pbortnovs...@jefferies.com wrote:

 Hello, all:

 when executing a statement, such as:

                         select
                     Id as id,
                     Code as code,
                     TypeStr as typeStr
                   from MyTable

 against a Derby in-memory table, the ResultSetMetaData present column names
 all in upper case and the original case is not preserved.

 Is there a setting which would allow to preserve the case specified in the
 SQL statement, i.e. with the statement above, the first column would be
 id, second - code, third - typeStr, etc...

 Thank you,

 Pavel.



 Jefferies archives and monitors outgoing and incoming e-mail. The contents
 of this email, including any attachments, are confidential to the ordinary
 user of the email address to which it was addressed. If you are not the
 addressee of this email you may not copy, forward, disclose or otherwise use
 it or any part of it in any form whatsoever. This email may be produced at
 the request of regulators or in connection with civil litigation. Jefferies
 accepts no liability for any errors or omissions arising as a result of
 transmission. Use by other than intended recipients is prohibited.  In the
 United Kingdom, Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office: Vintners Place, 68
 Upper Thames Street, London EC4V 3BJ.  Jefferies International Limited is
 authorised and regulated by the Financial Services Authority.

Re: Case Sensitivity

[Peter Ondruška]

Not that I know, this is by SQL standard.

On Wed, Mar 24, 2010 at 10:43 PM, Pavel Bortnovskiy 
pbortnovs...@jefferies.com wrote:


 Thank you, Peter, for your prompt response. Is enclosing them in quotes the
 only way to do so? Is there any setting which can be applied to Derby (or a
 -Define), so that the quotes could be omitted?





 *Peter Ondruška peter.ondru...@gmail.com*

 03/24/2010 05:33 PM
 Please respond to
 Derby Discussion derby-user@db.apache.org

   To
 Derby Discussion derby-user@db.apache.org
 cc
   Subject
 Re: Case Sensitivity




 select
Id as id,
Code as code,
TypeStr as typeStr
  from MyTable

 On Wed, Mar 24, 2010 at 10:30 PM, Pavel Bortnovskiy
 pbortnovs...@jefferies.com wrote:
 
  Hello, all:
 
  when executing a statement, such as:
 
  select
  Id as id,
  Code as code,
  TypeStr as typeStr
from MyTable
 
  against a Derby in-memory table, the ResultSetMetaData present column
 names
  all in upper case and the original case is not preserved.
 
  Is there a setting which would allow to preserve the case specified in
 the
  SQL statement, i.e. with the statement above, the first column would be
  id, second - code, third - typeStr, etc...
 
  Thank you,
 
  Pavel.
 
 
 
  Jefferies archives and monitors outgoing and incoming e-mail. The
 contents
  of this email, including any attachments, are confidential to the
 ordinary
  user of the email address to which it was addressed. If you are not the
  addressee of this email you may not copy, forward, disclose or otherwise
 use
  it or any part of it in any form whatsoever. This email may be produced
 at
  the request of regulators or in connection with civil litigation.
 Jefferies
  accepts no liability for any errors or omissions arising as a result of
  transmission. Use by other than intended recipients is prohibited.  In
 the
  United Kingdom, Jefferies operates as Jefferies International Limited;
  registered in England: no. 1978621; registered office: Vintners Place, 68
  Upper Thames Street, London EC4V 3BJ.  Jefferies International Limited is
  authorised and regulated by the Financial Services Authority.





 Jefferies archives and monitors outgoing and incoming e-mail. The contents
 of this email, including any attachments, are confidential to the ordinary
 user of the email address to which it was addressed. If you are not the
 addressee of this email you may not copy, forward, disclose or otherwise use
 it or any part of it in any form whatsoever. This email may be produced at
 the request of regulators or in connection with civil litigation. Jefferies
 accepts no liability for any errors or omissions arising as a result of
 transmission. Use by other than intended recipients is prohibited.  In the
 United Kingdom, Jefferies operates as Jefferies International Limited;
 registered in England: no. 1978621; registered office: Vintners Place, 68
 Upper Thames Street, London EC4V 3BJ.  Jefferies International Limited is
 authorised and regulated by the Financial Services Authority.

Re: Embedded Connection fail when disconnected from Network ? Solaris

[Peter Ondruška]

Dear B, actually this is Derby mailing list but I can tell you that
you should fix your nsswitch.conf not to use dns resolver (cp
/etc/nsswitch.files /etc/nsswitch.conf). Peter

On Tue, Feb 16, 2010 at 5:44 PM, bruehlicke bruehli...@gmail.com wrote:
 Wow - actually it is totally impossible to work with the Solaris
 system if not hooked up to a network. Each query takes a hundred
 years. - what's special about Solaris causing this ?

 B-)

 On Tue, Feb 16, 2010 at 10:34 AM, bruehlicke bruehli...@gmail.com wrote:
 Igonore !!!   It turns out that you just have to WAIT much (much)
 longer on Solaris to finally get the connection.

 B-)

 On Tue, Feb 16, 2010 at 10:17 AM, bruehlicke bruehli...@gmail.com wrote:
 Just want to check if some of you had the same experience.

 I have my NetBeans based app with Embedded Derby. Now, I for fun
 pulled out the network cable of my Solaris development box and it
 fails to connect to the database. Doing the same on Windows works
 (?read?Really strange)

 Could still be a red herring - but if anyone had similar experience
 (aka no connection to embedded Derby when disconnected from Network) -
 let me know - in particular if you see the same on Solaris.

 B-)

Re: refresh JSP webpage when Derby DB is changed

[Peter Ondruška]

Well, websockets will not work if your clients come from
corporationsas they usually deploy proxies with disallowed HTTP
CONNECT.  But as many said this is not really Derby topic. Just my EUR
0.02.

On Fri, Feb 12, 2010 at 10:21 PM, Ronald Rudy ronchal...@gmail.com wrote:
 As I understand it WebSocket is no longer part of the HTML5 spec, but its own 
 separate spec.. And only Chromium currently supports it (I think)..

 WebSockets would, however, be much more preferable if you have strong control 
 over the client browser(s).. but the polling - which is absolutely very brute 
 force and inelegant - will work in any browser from the last 4-5 years..

 On Feb 12, 2010, at 4:01:54 PM, Erin Drummond wrote:

 There IS a way to push data to the client, and I am using it
 successfully right now.

 It's called WebSocket and its part of HTML5, but only a few servers
 and browsers support it at the moment. Jetty 7 is one such server, and
 apparently later versions of WebKit and Gecko support it on the
 browser side (im not sure since my WebSocket client is a Java
 application which I wrote myself).

 Otherwise you can use Bayeux/Cometd to do long polling which simulates
 server push (its horridly complex compared to WebSockets depending on
 what you want to do with it, but it is much more widely supported)

 Erin

 On Sat, Feb 13, 2010 at 8:49 AM, Kristian Waagan
 kristian.waa...@sun.com wrote:
 Ronald Rudy wrote:

 This isn't really a Derby question, it's more of a web/Javascript
 question.

 There is no way to push data to a client.  The request always originates
 on the client.

 That said, IF you are so inclined you can use Ajax type calls to poll a
 servlet on the server on a regular interval to check for updates.  It would
 still be polling, though.  Not event-based.


 Hi,

 While the original request has to originate from the client, you don't have
 to have the client poll on regular intervals - you can use something like
 long poll or streaming to basically get an event-based system. Note that
 this requires some software on the server side, and as most things these
 techniques have their challenges.
 Besides from the link already mentioned, you can have a look at Atmosphere (
 https://atmosphere.dev.java.net/ ) and maybe post a question there or on a
 similer mailing list / forum.


 Hope this helps,
 --
 Kristian

 I would look at prototype if you want to start leveraging Ajax.
  http://www.prototypejs.org/   It's a good place to start.

 I would be very careful about having JS continuously polling your server
 though.  If you have lots of clients you will end up with lots of requests
 to handle.  That code has to be very efficient.


 On Feb 12, 2010, at 11:17:22 AM, albertkao wrote:



 The goal is to refresh a JSP web page on a web browser when the Derby DB
 on
 the server is changed.
 The rate of database change is 1-10 times per hour.
 The record size is about 1000 bytes.
 The user use the browser to modify the data and write back to the Derby
 DB.
 The web server is tomcat.
 I am thinking of comet programming - pushing data from the server to
 client
 when the Derby DB is changed.
 Is that a good idea?
 How to do that with javascript or java?
 Any sample code or tutorial?
 --
 View this message in context:
 http://old.nabble.com/refresh-JSP-webpage-when-Derby-DB-is-changed-tp27565891p27565891.html
 Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: OutOfMemoryErrors on group by select

[Peter Ondruška]

What Derby version is this? Do you have an index on groupCol?

On Mon, Feb 8, 2010 at 3:11 PM, Ronald Rudy ronchal...@gmail.com wrote:
 I've gotten to the point where I seem to be able to frequently induce 
 OutOfMemoryErrors when executing a statement like this one:

 SELECT groupCol, count(*) FROM myTable GROUP BY groupCol;

 The total number of records doesn't seem terribly large (about 400,000 
 records) though while I'm executing the above statement there are likely some 
 inserts and deletes happening concurrently.  I don't need anything 
 transactional here, I'm really just monitoring a table from a separate 
 network connection.

 In our app the database runs in its own JSVC daemon as a NetworkServer with 
 512MB allocated to it.  Page cache size is set to 2000.    Page size should 
 be Derby default (4096 I think).

 I cannot be 100% sure that the above is specifically what is causing the 
 error, but I can say that I haven't seen memory errors until I started 
 monitoring this table frequently and even then I really only saw it when the 
 table size started getting a bit bigger (not big by any stretch, but  300k 
 rows).

 The table itself is not really big - there are 7 varchar columns along with 
 an ID column.

 In this production-level situation stability is more important than 
 performance.  I'd like Derby configured so that no matter what is requested 
 SQL wise the daemon itself is not impacted.

 I'd also like to be able to build in some handling code perhaps that if/when 
 an OutOfMemoryError is encountered, it will automatically restart - for this 
 I might need to add a script with -XX:OnOutOfMemoryError as a JVM option 
 unless there's some way to handle this internally?

 -Ron

Re: OutOfMemoryErrors on group by select

[Peter Ondruška]

Try creating a non-unique index on the column you group by. Have you
tried more recent Derby version if you can reproduce that there?

On Mon, Feb 8, 2010 at 3:25 PM, Ronald Rudy ronchal...@gmail.com wrote:
 Version is 10.4.2.0, and no I don't - I am actually trying the group by/etc. 
 on another column that is indexed to see if there's any memory benefits..  
 The table isn't optimized for the below statement because in production it 
 will never be executed like that, I'm just trying to monitor some counts 
 while the app is running..


 On Feb 8, 2010, at 9:21:29 AM, Peter Ondruška wrote:

 What Derby version is this? Do you have an index on groupCol?

 On Mon, Feb 8, 2010 at 3:11 PM, Ronald Rudy ronchal...@gmail.com wrote:
 I've gotten to the point where I seem to be able to frequently induce 
 OutOfMemoryErrors when executing a statement like this one:

 SELECT groupCol, count(*) FROM myTable GROUP BY groupCol;

 The total number of records doesn't seem terribly large (about 400,000 
 records) though while I'm executing the above statement there are likely 
 some inserts and deletes happening concurrently.  I don't need anything 
 transactional here, I'm really just monitoring a table from a separate 
 network connection.

 In our app the database runs in its own JSVC daemon as a NetworkServer with 
 512MB allocated to it.  Page cache size is set to 2000.    Page size should 
 be Derby default (4096 I think).

 I cannot be 100% sure that the above is specifically what is causing the 
 error, but I can say that I haven't seen memory errors until I started 
 monitoring this table frequently and even then I really only saw it when 
 the table size started getting a bit bigger (not big by any stretch, but  
 300k rows).

 The table itself is not really big - there are 7 varchar columns along with 
 an ID column.

 In this production-level situation stability is more important than 
 performance.  I'd like Derby configured so that no matter what is requested 
 SQL wise the daemon itself is not impacted.

 I'd also like to be able to build in some handling code perhaps that 
 if/when an OutOfMemoryError is encountered, it will automatically restart - 
 for this I might need to add a script with -XX:OnOutOfMemoryError as a JVM 
 option unless there's some way to handle this internally?

 -Ron

Re: Java DB open for business under new management

[Peter Ondruška]

Nice history: Cloudscape, then acquired by Informix, then IBM,
opensourced, Sun, now Oracle :-))

On Thu, Feb 4, 2010 at 6:06 PM, Rick Hillegas richard.hille...@sun.com wrote:
 Hi everyone,

 There seem to be a lot of rumors flying around now that Sun is a wholly
 owned subsidiary of Oracle. I want you to know that the Java DB team will
 continue to contribute to Derby just as we've been doing for the last five
 years.

 Cheers,
 -Rick

Re: Improving data insert performance

[Peter Ondruška]

Also try using larger log files (10+MB) and if you rarely change data
you load (which I guess is your case) you may want to use
pageReservedSpace=0 and large pageSize (32kb?)

On Wed, Jan 13, 2010 at 5:01 PM, Nathan Boy nathan@gmail.com wrote:
 Hello,
   I have an embedded database application that generally involves
 inserting somewhere between 50k and 1000k rows of data into a
 database, and then analyzing and querying that data afterwards.  The
 data goes into about ten tables, but the bulk of the data is in just a
 few of them.  I run my database with test durability, and I add all
 of the primary key and foreign key constraints after the data is
 inserted into the database.  Currently all of the data is inserted
 using prepared statements executed in batches, and this gives me
 between 10 and 20 row inserts per millisecond on average.  I have
 spent quite a bit of time optimizing the insert step, and while I was
 doing research I came across this discussion from last year:

 http://www.mail-archive.com/derby-user@db.apache.org/msg10194.html

 The discussion suggests using bulk import as a way to speed up this
 initial insert step.  Unfortunately, I cannot use the built in import
 functions, as my data includes Timestamps with nanosecond granularity.
  As far as I can tell, there is no way to convince derby to parse a
 time specified down to the nanosecond.  In one of the emails, someone
 suggested that you can get bulk import performance by using a table
 function, and then running a query like INSERT INTO MYTABLE (...)
 SELECT S.* FROM TABLE (MYFUNC ()) S.  In my tests, however, this
 doesn't seem to perform the insert any faster than simply inserting
 the rows one at a time with a prepared statement.  I think this may be
 because I don't have a way to set the 'insertMode=bulkImport'
 property, which the bulk import system procedure is allowed to do.
 Does anyone know of a way to work around this, or of a better way to
 get my data into the database as quickly as possible?  Thanks in
 advance for your time.

 Cheers,
 Nathan Boy

Re: Derby as an in-memory JEE datasource

[Peter Ondruška]

I tested connection pool with these properties:

DatabaseName=memory:Test;create=true
User=APP
SecurityMechanism=4
RetrieveMessageText=true
Password=APP
TraceFileAppend=false
TraceLevel=-1
ServerName=localhost
PortNumber=1527
Ssl=off
LoginTimeout=0

Do not have any applications using database, but try yourself and let us know.

On Fri, Jan 8, 2010 at 9:44 AM, Kristian Waagan kristian.waa...@sun.com wrote:
 Marcin Kwapisz wrote:

 I think that sub-protocol memory is ignored. When I start Derby as a
 normal db server, connection can be established (with that connection pool)
 and everything works fine. Maybe I do something wrong. I do not know, so I
 asked if it is possible to configure in-memory database as a datasource.

 This is my connection pool configuration:

 jdbc-connection-pool allow-non-component-callers=false
 associate-with-thread=false connection-creation-retry-attempts=0
 connection-creation-retry-interval-in-seconds=10
 connection-leak-reclaim=false connection-leak-timeout-in-seconds=0
 connection-validation-method=auto-commit
 datasource-classname=org.apache.derby.jdbc.ClientDataSource
 fail-all-connections=false idle-timeout-in-seconds=300
 is-connection-validation-required=false
 is-isolation-level-guaranteed=true lazy-connection-association=false
 lazy-connection-enlistment=false match-connections=false
 max-connection-usage-count=0 max-pool-size=32
 max-wait-time-in-millis=6 name=TestDBPoolMem
 non-transactional-connections=false pool-resize-quantity=2
 res-type=javax.sql.DataSource statement-timeout-in-seconds=-1
 steady-pool-size=8 validate-atmost-once-period-in-seconds=0
 wrap-jdbc-objects=false
        property name=serverName value=localhost/
        property name=PortNumber value=1527/
        property name=networkProtocol value=jdbc:derby:memory/
        property name=DatabaseName value=EntityTestDB/
        property name=User value=testdb/
        property name=Password value=testdb/
    /jdbc-connection-pool


 Hi Marcin,

 The network driver doesn't really support the memory subsubprotocol in a
 proper way, but you can try to either remove networkProtocol or set it to
 jdbc:derby if it is required  (not sure if it should end with a colon or
 not), and then specify memory:EntityTestDB as DatabaseName.

 There is some other information at
 http://wiki.apache.org/db-derby/InMemoryBackEndPrimer , but note that the
 proper way to drop an in-memory database in the next feauture-release will
 be to use the JDBC connection attribute ;drop=true.

 The error I got:
 INFO: EclipseLink, version: Eclipse Persistence Services -
 2.0.0.v20091127-r5931
 2010-01-08 09:16:45
 com.sun.enterprise.resource.allocator.LocalTxConnectorAllocator
 createResource
 WARNING: poolmgr.create_resource_error
 2010-01-08 09:16:45 com.sun.enterprise.connectors.ConnectionManagerImpl
 internalGetConnection
 WARNING: poolmgr.get_connection_failure
 2010-01-08 09:16:45 com.sun.gjc.spi.base.DataSource getConnection
 WARNING: jdbc.exc_get_conn
 2010-01-08 09:16:45 org.eclipse.persistence.session.file: _InitPU.ejb
 SEVERE: Local Exception Stack: Exception [EclipseLink-4002] (Eclipse
 Persistence Services - 2.0.0.v20091127-r5931):
 org.eclipse.persistence.exceptions.DatabaseException
 Internal Exception: java.sql.SQLException: Error in allocating a
 connection. Cause: Connection could not be allocated because:
 java.net.ConnectException: Błąd podczas łączenia z serwerem localhost na
 porcie 1527 z komunikatem Connection refused: connect.


 I'm not quite sure why you get this error, maybe it's because of the
 specified network protocol. Otherwise, make sure your Derby network server
 is up and running.


 Hope this helps,
 --
 Kristian

 Regards
 Marcin Kwapisz

Re: Derby as an in-memory JEE datasource

[Peter Ondruška]

Any errors?

On Thu, Jan 7, 2010 at 5:59 PM, Marcin Kwapisz mkwap...@zsk.p.lodz.pl wrote:
 Hi,
 is it possible to use Derby as an in-memory datasource on Glassfish v3 
 application server? I tried but with no luck.

 Regards
 --
 Marcin  Kwapisz

Re: AW: AW: AW: OOM with millions of weakly-referenced Derby objects

[Peter Ondruška]

I develop on Windows and deploy to AIX 5.3 using Java 6 (_17 on Windows) and
SR4
http://www.ibm.com/developerworks/java/jdk/aix/j664/Java6_64.fixinfo.html and
never encountered anything unusual.

On Fri, Nov 27, 2009 at 4:30 PM, malte.kem...@de.equens.com wrote:

 Hi Dag,
 Thanks for your effort. The problem is as far I understand a special
 behavoiour on AIX Java Mashine. Does anybody of the developing or testing
 people got access on something like that?


 -Ursprüngliche Nachricht-
 Von: dag.wan...@sun.com [mailto:dag.wan...@sun.com]
 Gesendet: Freitag, 27. November 2009 16:15
 An: Derby Discussion
 Betreff: Re: AW: AW: AW: OOM with millions of weakly-referenced Derby
 objects


 Hi Malte,

 malte.kem...@de.equens.com writes:


  Hi Dag, The problem in my case is, that I don't have access to any
  AIX-Computer, and that is really a pity, because I cannot
  reconstruct the scenario on my own and watch it with my own eyes.
  That all happened on a production system and the provider denies
  trying it out again.  But attached you can find the SQL-script
  without being UTF8.  So that Problem should occur in the same way
  used on AIX (5.3) BTW: is there a way to be kind of independent of
  the input format?

 I converted the script you enclosed to UTF-8 and ran it through ij
 without any problems.

 I also tried to run it with the enclosed program, and it gave me no
 errors when the inout file was encoded with UTF-8 (Comments had
 non-ASCII German character), and it printed no errors on my
 OpenSolaris system (I don't have access to AIX).

 In any case, should you see this problem again, please feel free to
 file a bug report(*), preferably with a repro and/or logs.

 As for independence of the input format, since runScript requires that
 you specify the encoding, you could do some encoding auto-detect logic
 on the script, I guess..

 (*) http://db.apache.org/derby/DerbyBugGuidelines.html

 Hope this helps,
 Dag


 import org.apache.derby.tools.ij;
 import java.sql.*;
 import java.io.*;

 public class Foo {

public Foo() {};

static public void main(String[] args)
throws SQLException,
   UnsupportedEncodingException,
   FileNotFoundException,
   ClassNotFoundException {
Class.forName(org.apache.derby.jdbc.EmbeddedDriver);
Connection c =
 DriverManager.getConnection(jdbc:derby:wombat;create=true);

FileInputStream fs = new FileInputStream(args[0]);

int errs = ij.runScript(c,
fs,
UTF-8,
System.out,
UTF-8);

System.out.println(\n\nerrs= + errs);
c.close();
}
 }




 
  Malte
 
  -Ursprüngliche Nachricht-
  Von: dag.wan...@sun.com [mailto:dag.wan...@sun.com]
  Gesendet: Freitag, 27. November 2009 01:19
  An: Derby Discussion
  Betreff: Re: AW: AW: OOM with millions of weakly-referenced Derby objects
 
  malte.kem...@de.equens.com writes:
 
  Yes this is the API I am reffering to.  Well actually it says that,
  but in my case it did not do it.  It needed a long time, several
  minutes. Reason as it turned later out, after getting the output in
  a file a lot of IOExcceptions. I never got those on windows nor on
  Solaris. Thr original logic of the routine calling the runScript
  Method was asking for the return code but either it never hit this
  if statement and crashed somewhere within ij without giving the
  Exception to the caller or ij gave something like 0 so the output
  never was given to the logger.  So there is something phony with
  ij.runScript using it with AIX 5.3 and a SQL-Script not coded UTF8
  but declaring in the parameters as UTF8. Of course that is a fault
  by my own, but it should tel me about it.
 
  I looked at the implementation for runScript in
  org.apache.derby.impl.tools.ij.utilMain#runScriptGuts and it does seem
  to catch exceptions, count them and return them. Are you able to
  provide a repro script showing that 0 or -1 is returned from runScript
  for the failing scenario?
 
  Thanks,
  Dag
 
 
 
 
  -Ursprüngliche Nachricht-
  Von: dag.wan...@sun.com [mailto:dag.wan...@sun.com]
  Gesendet: Freitag, 20. November 2009 19:54
  An: Derby Discussion
  Betreff: Re: AW: OOM with millions of weakly-referenced Derby objects
 
  malte.kem...@de.equens.com writes:
 
  In other words I would count that situation as not nice behaviour of
 ij, since the return code should have been given as I would suppose it
 looking at the method signature.
 
  Is this the API signature you are referring to?
 
 
 http://db.apache.org/derby/docs/10.5/publishedapi/jdbc4/org/apache/derby/tools/ij.html#runScript(java.sql.Connection,%20java.io.InputStream,%20java.lang.String,%20java.io.OutputStream,%20java.lang.String)
 
  It says:
 
  Returns:h
  Number of SQLExceptions thrown during the execution, -1 if not
  known.
 
  If so, 

Re: unsubscribe

[Peter Ondruška]

Dear Stavros,
you seem to be using Gmail, so be so kind and click on show details link
in any message received from this mailing list and click again for
unsubscribe.

Thanks!

Peter

PS: OMG is it that hard to unsubscribe?! I though it is more difficult to
subscribe... Do not respond, just my Friday afternoon thoughts.

On Fri, Oct 9, 2009 at 1:53 PM, Stavros Kounis skou...@gmail.com wrote:

 unsubscribe

Re: Abandoning Derby - thank you all for your support

[Peter Ondruška]

TK, did you work on the issues with Sun Support? Or did you just rely on
free resources on mailing list? Thanks. Peter

On Wed, Sep 30, 2009 at 3:51 PM, T K sanokist...@yahoo.com wrote:

 Unfortunately, because of the unexpected growth of some tables with dead
 rows - due in part to unresolved bugs 691, 4054, 4055 (and even resolved
 ones like 4050 which we do not benefit from, i.e. we are still seeing
 unexpected table growth with concurrent updates) - which are hitting us very
 hard (e.g. estimated query costs growing into the hundreds of thousands and
 some in the 20+ million within days with just a dozen live rows in those
 tables, even when after creating otherwise-unnecessary indices for direct
 access), plus other issues where the compress table stored proc isn't
 reclaiming the dead space unless we really bring the application server
 down, we have to abandon Derby, and I just wanted to thank you all for your
 support.

 Regards

 TK

Re: Pooling

[Peter Ondruška]

Yes, Derby does not provide connection pool, pick your favourite (I can
confirm c3p0 works very well for me). Peter

On Sun, Sep 27, 2009 at 11:56 PM, Christopher Giblin c...@zurich.ibm.comwrote:


 Hi,
 Just to be sure - Derby does not directly support connection pooling,
 correct? Rather, One must use a third party pool such as Apache DBCP

 Could you please confirm my understanding?

 Thanks, chris

Re: How would you implement a record-level versioning system with Derby and Java?

[Peter Ondruška]

Actually I would store the history values outside the main table for
performance and storage overhead reasons (history table may have
additional columns, e.g. When-who-etc for auditing information). Peter

2009/7/9, Donald McLean dmclea...@gmail.com:
 On Thu, Jul 9, 2009 at 9:03 AM, Alessandro Bottonialexbott...@yahoo.it
 wrote:
 Hi Guys,
 I'm completely new to Derby and I evaluating it as a possibile embedded
 RDBMS for a Java/Swing application that I'm goig to develop. I'm
 currently following the tutorials at IBM DeveloperWorks
 (http://tinyurl.com/6mlwpj) and I have ordered the IBM book at Amazon's
 already (http://tinyurl.com/ln3cas).

 I have a stimulating question for you all (I apologize if this topic has
 been discussed already):

 how would you implement a system to keep track of any old version of a
 record (row) of your database?

 I'm asking this because I need a system that will allow the user to see
 any old version of a record and to undo/redo any change with just a
 couple of mouse clicks.

 Any idea? Any suggestion?

 Actually, this is a question that I have previously considered for a
 project that I am starting.

 My thought was to have an identity column (value assigned by the DBMS)
 and a separate ID column. From here, you can do several different
 things:

 1. Have a version column. Use the max function to find the current
 version, or make the current version -1 or something else easy to
 find.
 2. Use the max function on the identity column to find the latest version.
 3. Have previous and next columns with the identity values of the
 appropriate objects in the chain. The current version would have a
 null next.

 There are probably other approaches that I haven't thought of.

 PS: No, I'm not planning to use any kind of ORM (Hibernate or similar).
 Should I?

 I have used and recommend Hibernate. It simplifies things immensely
 IMO, though it does have its quirks (some of which are not properly
 explained by the documentation). It also works well with a number of
 other DBMS (if changing is something that might happen later).
 -
 Family photographs are a critical legacy for
 ourselves and our descendants. Protect that
 legacy with a digital backup and recovery plan.

Re: How would you implement a record-level versioning system with Derby and Java?

[Peter Ondruška]

I would recommend also looking at some data warehousing articles as
versioning is related to that. Though I do not have any concrete to
recommend :-(. Peter

2009/7/9, Alessandro Bottoni alexbott...@yahoo.it:
 Donald McLean ha scritto:
 Actually, this is a question that I have previously considered for a
 project that I am starting.

 My thought was to have an identity column (value assigned by the DBMS)
 and a separate ID column. From here, you can do several different
 things:

 1. Have a version column. Use the max function to find the current
 version, or make the current version -1 or something else easy to
 find.
 2. Use the max function on the identity column to find the latest version.

 I thought about this mechanism but I'm not completely convinced it would
 be the right one. I mean, there are two different user views to take
 into account:

 1) The user view onto the whole database while browsing/searching for a
 specific record.
 2) The single-record view used to examine/edit a specific record.

 While it is easy to use a SELECT with MAX to get the latest version of a
 specific record, it is not clear how I could get only the most recent
 version of every single record to build up the browse view. It looks
 like I should use a nested SELECT:  first, select all matching records
 and, second, select the latest version of  each of them. A little bit
 messy...

 3. Have previous and next columns with the identity values of the
 appropriate objects in the chain. The current version would have a
 null next.

 Nice approach! Actually, I did not think about this possibility. Many
 thanks for this suggestion.

 This would simplify a lot the SELECT operation used to generate the
 browse view (just select all of the records that have a NULL
 next-version field) while maintaining the possibility to go back and
 forth between versions (and build up a list of them, if needed).

 I think I will experiment with this one.

 There are probably other approaches that I haven't thought of.

 I thought about triggers/procedures, as well, but I had the feeling they
 would have the same problem as the SELECT + MAX approach.

 I have used and recommend Hibernate. It simplifies things immensely
 IMO, though it does have its quirks (some of which are not properly
 explained by the documentation). It also works well with a number of
 other DBMS (if changing is something that might happen later).

 Thanks. I'm new to Hibernate, as well, so I'm going to ask you (all)
 which book is best for learning Hibernate. I browsed a little bit on
 Amazon's but I did not find anything to be enthusiastic about. What
 would you suggest me as a good starting point/introductory book?

 Any good web article/tutorial to signal, as well?

 Thanks again.
 --

 Alessandro Bottoni
 Website: http://www.alessandrobottoni.it/

 In mathematics you don't understand things. You just get used to them.
  -- John von Neumann

Re: Error 42Y55

[Peter Ondruška]

Perhaps you want to use:
drop table tableName

On Sat, Jul 4, 2009 at 8:10 AM, sridhar devatha
devatha.srid...@gmail.comwrote:

 Hi,

 I am getting the
 ij drop table tableName;
 ERROR 42Y55: 'DROP TABLE' cannot be performed on 'tableName' because it
 does not exist.
 ij exit

 But, the table exists.
 I verified using sys.systables.
 what could be the reason.
 I am using netbeans to generate hibernate mappings.
 I also open command windows for server and another for connecting through
 ij  apart from netbeans.
 I closed netbeans. I restarted the server. Tried to drop the table. Still I
 am getting the same error.
 any body please help.
 --

 Yours Sincerely,
 Sridhar

Re: Hibernate + Derby - Unable to determine stream header for hibernate type 'text'

[Peter Ondruška]

Dear Kristian,

Regarding your remark about effectiveness of very small CLOB vs
VARCHAR should I just use VARCHAR to store 100 to 2000 character data
instead of CLOB? I do not want to index this column and storage size
and access speed (read only) are important.

Thanks,

Peter

2009/6/19, Kristian Waagan kristian.waa...@sun.com:
 kashyup wrote:
 Hello all,
 We have just started to look into Derby as our embedded DB for a desktop
 app
 version.

 Env: Hibernate 3.2 and Derby 10.5.1

 I have defined a java string field of type 'text' in Hibernate Mapping
 (works great on Postgres 8.1 and Oracle 10g)

 I use Hibernate SessionFactory to generate Table schema, which is done
 without any error.

 But, when I try to save records in tables via Hibernate Session, I get the
 following exception:
 Note: If I change hibernate mapping to type 'string', then I dont get this
 error:

 java.lang.IllegalStateException: No context, unable to determine which
 stream header format to generate
  at
 org.apache.derby.iapi.types.ClobStreamHeaderGenerator.determineMode(Unknown
 Source)
  at
 org.apache.derby.iapi.types.ClobStreamHeaderGenerator.expectsCharCount(Unknown
 Source)
  at org.apache.derby.iapi.types.ReaderToUTF8Stream.fillBuffer(Unknown
 Source)
  at org.apache.derby.iapi.types.ReaderToUTF8Stream.read(Unknown Source)
  at java.io.DataInputStream.read(Unknown Source)
  at org.apache.derby.iapi.types.SQLClob.readExternal(Unknown Source)
  at org.apache.derby.iapi.types.SQLChar.getString(Unknown Source)
  at org.apache.derby.iapi.types.SQLClob.getClone(Unknown Source)
  at org.apache.derby.impl.sql.GenericParameter.getClone(Unknown Source)
  at org.apache.derby.impl.sql.GenericParameterValueSet.init(Unknown
 Source)
  at org.apache.derby.impl.sql.GenericParameterValueSet.getClone(Unknown
 Source)
  at org.apache.derby.impl.jdbc.EmbedPreparedStatement.addBatch(Unknown
 Source)
  at 
 org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:31)
  at
 org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
  at
 org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2665)

 Also, I need to save it as type 'text' even though it gets saved as
 CLOB(255), as the CLOB column size can later be altered.

 What could be the cause? or changes needed?

 Hello,

 You have discovered / triggered a bug in the new Clob handling code
 introduced in 10.5.1.
 I logged DERBY-4278 [1], and have started working on a fix (the
 regression tests are running, patch needs review).

 Regarding workarounds, I'm afraid all I can think of is using the client
 driver instead of the embedded driver, avoid batching for inserts with
 Clobs, change the data type, or downgrade to 10.4. Another option is to
 build Derby with the patch and test it out :)
 I observe that you are using very small Clobs (255 characters). Clobs
 are in general less effective than using for instance VARCHAR.
 Is your concern about altering the column size related to Derby,
 Hibernate or something else?


 Regards,
 --
 Kristian

 [1] https://issues.apache.org/jira/browse/DERBY-4278
 thanks all

Re: Multiple instances of an application using same embedded database?

[Peter Ondruška]

Have a look at the archives of this list. It has been discussed already that
you can use server mode similarly to embedded.
Your application may try first use existing database as client and if fails
to connect it may start server.

Actually your second application instance would benefit from accessing as
client because of derby server cache.

On Fri, Jun 5, 2009 at 10:51 PM, Ron Olson tachokni...@gmail.com wrote:

 Hi all-

 I am writing an application that uses embedded Derby for holding data
 that the application collects/creates. It works great, I really like
 Derby.

 But, there's a problem: multiple application instances. The
 application can be launched multiple times and each instance can
 potentially create and retrieve data that would be stored/queried from
 Derby. The problem is that the second instance fails to run correctly
 because the database is locked by the first instance of the
 application.

 I can't create a Derby server, the application must be zero-setup and
 zero-config; the application handles initial database creation, making
 the tables, etc.

 I've looked and there doesn't seem to be any info on this. Is this
 even possible? If not, might anyone know what I might be able to use?
 The original version of the program used SQLite, which keeps the
 database in the single file. This was nice, but there isn't a pure
 Java way to get at a SQLite db, and besides, I like the additional
 functionality Derby gives me.

 Thanks for any info,

 Tacho

Re: Bug in Derby 10.5.1.1 or misuse ?

[Peter Ondruška]

Would you please try if there is any difference if you remove name IN (?).

2009/5/25, Gurvan Le Guernic glegu...@gmail.com:
 The NullPointer exception is thrown by the statement:
 ps = dbConnection.prepareStatement(sqlStr);
 ,with ps a PreparedStatement ans sqlStr a string equal to

 SELECT method, COUNT(method) FROM methodParameters  INNER JOIN types
 ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method)
  = ?

 I use similar code to prepare other statements and they work well. So I
 guess that if there is an error in my code, it lies in the SQL select query.

 Bryan Pendelton suggested filling a bug query. If I have no clue what
 happens this evening, I' ll do it.

 Peter Ondruška wrote:
 Can we see how you set parameters for this prepared statement?

 2009/5/25, Gurvan Le Guernic glegu...@gmail.com:

Hi,
  I have a java.lang.NullPointerException when preparing the following
 statement:

 SELECT method, COUNT(method) FROM methodParameters  INNER JOIN types
 ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method)
  = ?

 with the command:   dbConnection.prepareStatement(sqlStr);

 The tables involved are:
 CREATE TABLE types ( +
   id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, +
   name VARCHAR(128) NOT NULL UNIQUE, +
   shortName VARCHAR(64) NOT NULL +
 )
 and
 CREATE TABLE methodParameters ( +
   method INT REFERENCES methods (id), +
   position INT, +
   parameter INT REFERENCES types (id) +
 )

 SQL information for the exception are:
  SQL state: XJ001
  Error code: 0

 And the stack trace is:
  Message: Exception Java : ': java.lang.NullPointerException'.
 java.sql.SQLException: Exception Java : ':
 java.lang.NullPointerException'.
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
 Source)
 at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
 Source)
 at org.apache.derby.impl.jdbc.Util.javaException(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.EmbedPreparedStatement.init(Unknown Source)
 at
 org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown
 Source)
 at
 org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
 at
 org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
 Source)
 at org.thinkcollabs.jmbrowser.db.DAO.initSelect(DAO.java:370)
 at
 org.thinkcollabs.jmbrowser.db.DB_Derby$MethodDataLoader.run(DB_Derby.java:254)
 Caused by: java.sql.SQLException: Exception Java : ':
 java.lang.NullPointerException'.
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
 ... 16 more
 Caused by: java.lang.NullPointerException
 at
 org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown
 Source)
 at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown
 Source)
 at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown
 Source)
 at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
 Source)
 at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown
 Source)
 at
 org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
 Source)
 ... 9 more

 I can't see what I am doing wrong, but I am not an SQL expert. So, I am
 doing something wrong or is there a problem

Re: Bug in Derby 10.5.1.1 or misuse ?

[Peter Ondruška]

Well that is because you cannot bind parameter like you do: name IN
(?). Therefore you get an exception. Try name = ? and I bet it will
work.

2009/5/25, Gurvan Le Guernic glegu...@gmail.com:
 If I remove the WHERE part, it works fine (i.e. no exception generated
 when preparing the statement).

 SELECT method  FROM methodParameters AS mp   INNER JOIN types ON
 mp.parameter = types.id  GROUP BY method  HAVING COUNT(method) = ?

 Peter Ondruška wrote:
 Would you please try if there is any difference if you remove name IN
 (?).

 2009/5/25, Gurvan Le Guernic glegu...@gmail.com:

 The NullPointer exception is thrown by the statement:
 ps = dbConnection.prepareStatement(sqlStr);
 ,with ps a PreparedStatement ans sqlStr a string equal to

 SELECT method, COUNT(method) FROM methodParameters  INNER JOIN types
 ON parameter = id WHERE name IN (?) GROUP BY method HAVING COUNT(method)
  = ?

 I use similar code to prepare other statements and they work well. So I
 guess that if there is an error in my code, it lies in the SQL select
 query.

 Bryan Pendelton suggested filling a bug query. If I have no clue what
 happens this evening, I' ll do it.

 Peter Ondruška wrote:

 Can we see how you set parameters for this prepared statement?

 2009/5/25, Gurvan Le Guernic glegu...@gmail.com:


Hi,
  I have a java.lang.NullPointerException when preparing the following
 statement:

 SELECT method, COUNT(method) FROM methodParameters  INNER JOIN
 types
 ON parameter = id WHERE name IN (?) GROUP BY method HAVING
 COUNT(method)
  = ?

 with the command:   dbConnection.prepareStatement(sqlStr);

 The tables involved are:
 CREATE TABLE types ( +
   id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, +
   name VARCHAR(128) NOT NULL UNIQUE, +
   shortName VARCHAR(64) NOT NULL +
 )
 and
 CREATE TABLE methodParameters ( +
   method INT REFERENCES methods (id), +
   position INT, +
   parameter INT REFERENCES types (id) +
 )

 SQL information for the exception are:
  SQL state: XJ001
  Error code: 0

 And the stack trace is:
  Message: Exception Java : ': java.lang.NullPointerException'.
 java.sql.SQLException: Exception Java : ':
 java.lang.NullPointerException'.
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
 Source)
 at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown
 Source)
 at org.apache.derby.impl.jdbc.Util.javaException(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.EmbedPreparedStatement.init(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedPreparedStatement20.init(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedPreparedStatement30.init(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedPreparedStatement40.init(Unknown
 Source)
 at
 org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
 Source)
 at org.thinkcollabs.jmbrowser.db.DAO.initSelect(DAO.java:370)
 at
 org.thinkcollabs.jmbrowser.db.DB_Derby$MethodDataLoader.run(DB_Derby.java:254)
 Caused by: java.sql.SQLException: Exception Java : ':
 java.lang.NullPointerException'.
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
 Source)
 at
 org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
 ... 16 more
 Caused by: java.lang.NullPointerException
 at
 org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown
 Source)
 at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown
 Source)
 at
 org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown
 Source)
 at
 org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown
 Source

Re: How to read blobs?

[Peter Ondruška]

Hi, just add -Duser.language=en to java startup options.

2009/3/5, epdv peter.nabbef...@gmx.de:
 Sorry, only German language - Win, no lang=C available :(

 Kind regards

 Peter


 org.apache.derby.client.am.SqlException: Der für diesen CLOB/BLOB
 angegebene Locator ist ungültig
 Caused: org.apache.derby.client.am.SqlException: Bei der Auswertung
 eines Ausdrucks wurde die Ausnahme 'java.sql.SQLException: Der für
 diesen CLOB/BLOB angegebene Locator ist ungültig' ausgelöst.
  at org.apache.derby.client.am.Statement.completeExecute(Unknown
 Source)
  at
 org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown
 Source)
  at
 org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown
 Source)
  at
 org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown Source)
  at
 org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown Source)
  at org.apache.derby.client.am.Statement.readExecuteCall(Unknown
 Source)
  at
 org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
  at
 org.apache.derby.client.am.PreparedStatement.executeX(Unknown Source)
 Caused: org.apache.derby.client.am.SqlException: Es können keine
 weiteren java.sql.Clob/java.sql.Blob-Methoden aufgerufen werden, nachdem
 die free()-Methode aufgerufen oder nachdem die Blob/Clob-Transaktion
 ausgeführt oder wiederholt (Rollback) wurde.
  at
 org.apache.derby.client.am.CallableLocatorProcedures.handleInvalidLocator(Unknown
 Source)
  at
 org.apache.derby.client.am.CallableLocatorProcedures.blobGetLength(Unknown
 Source)
  at org.apache.derby.client.am.Blob.getLocatorLength(Unknown Source)
  at org.apache.derby.client.am.Lob.sqlLength(Unknown Source)
 Caused: java.sql.SQLException: Es können keine weiteren
 java.sql.Clob/java.sql.Blob-Methoden aufgerufen werden, nachdem die
 free()-Methode aufgerufen oder nachdem die Blob/Clob-Transaktion
 ausgeführt oder wiederholt (Rollback) wurde.
  at
 org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
 Source)
  at
 org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
  at org.apache.derby.client.am.Blob.length(Unknown Source)

Re: inserts slowing down after 2.5m rows

[Peter Ondruška]

Could be checkpoint.. BTW to speed up bulk load you may want to use
large log files located separately from data disks.

2009/2/27, Brian Peterson dianeay...@verizon.net:
 I have a big table that gets a lot of inserts. Rows are inserted 10k at a
 time with a table function. At around 2.5 million rows, inserts slow down
 from 2-7s to around 15-20s. The table's dat file is around 800-900M.



 I have durability set to test, table-level locks, a primary key index and
 another 2-column index on the table. Page size is at the max and page cache
 set to 4500 pages. The table gets compressed (inplace) every 500,000 rows.
 I'm using Derby 10.4 with JDK 1.6.0_07, running on Windows XP. I've ruled
 out anything from the rest of the application, including GC (memory usage
 follows a consistent pattern during the whole load). It is a local file
 system. The database has a fixed number of tables (so there's a fixed number
 of dat files in the database directory the whole time). The logs are getting
 cleaned up, so there's only a few dat files in the log directory as well.



 Any ideas what might be causing the big slowdown after so many loads?



 Brian