Lou DeGenaro | 5 Jan 16:12 2014

slow query? (from a practical newbie)

My table: Job

 primaryKey int primary key generated always as identity,
 id int not null,
 startDate bigint not null,
 endDate bigint not null,
 duration int not null,
 userId varchar( 64 ) not null,
 submitter varchar( 64 ) not null,
 schedulingClass varchar( 64 ) not null,
 debugPortDriver int not null,
 debugPortProcess int not null,
 state varchar( 64 ) not null,
 stateIndex int not null,
 completionType varchar( 64 ) not null,
 completionRationale varchar( 1024 ) not null,
 services int not null,
 processes int not null,
 initFails int not null,
 runFails int not null,
 pgin int not null,
 swapGbMax double not null,
 memorySizeGb int not null,
 workItemsTotal int not null,
 workItemsDone int not null,
 workItemsError int not null,
 workItemsRetry int not null,
 workItemsPreempt int not null,
 workItemsStatMax int not null,
 workItemsStatMin int not null,
 workItemsStatMean int not null,
 workItemsStatStdDev int not null,
 description varchar( 4096 ) not null,
 schedulingSharesMax int not null,
 logDirectory varchar( 4096 ) not null,
 jsonServiceDependencies clob not null,
 jsonJobDeployment clob not null,
 projection bigint not null,
 swapGbCurrent double not null,
 workItemsDispatch int not null,
 constraint uniqueColumns unique ( id )


My indexes:

CREATE INDEX id_desc ON Job (id desc)
CREATE INDEX stateIndex_asc_id_desc ON Job (stateIndex asc, id desc)


Query #1 - fast! meaning sub-second using prepared statement

     select * from Job order by stateIndex asc, id desc offset ? rows fetch next ? rows only


Query #2 - slow :-( meaning on the order of 30 seconds on a table with 55,000 records

     select * from Job where id < ? order by stateIndex asc, id desc offset ? rows fetch next ? rows only


Ostensibly the where clause really slow does the query.  How come and how do I remedy?



Tim Dudgeon | 3 Jan 13:49 2014

Unnecessary increment of sequence

I'm trying to use a sequence to generate a value where one is not 
supplied, but I'm not able to only increment the sequence when its 
needed. This is actually going on in a trigger, but to simplify matters 
here is an example that illustrates the problem:


In the coalesce function the first argument is always non null, so the 
second argument should never be needed, but you will see that the 
sequence is incremented anyway.
Are there any alternative approaches that can avoid this?
I was thinking of trying in a CASE statement instead, but sequences 
can't be used there :-(


Martijn Blankestijn | 2 Jan 18:05 2014

JDK 8 and setObject


I am using DERBY: "", packaged with Java 8 JDK ("java.runtime.version=1.8.0-ea-b120").

I created a table in-memory: create table Persoon (id int not null GENERATED ALWAYS AS IDENTITY , datum date not null, tijd time not null, tijdstip timestamp not null)

When I use the following code:

    try (PreparedStatement preparedStatement = connection.prepareStatement("insert into Persoon  (datum, tijd, tijdstip) values (?,?,?)")) {
      preparedStatement.setObject(1, LocalDate.of(2014, 3, 20), JDBCType.DATE);

I get the following exception:

java.sql.SQLDataException: An attempt was made to get a data value of type 'DATE' from a data value of type 'java.time.LocalDate'.
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.ConnectionChild.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.dataTypeConversion(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setObject(Unknown Source)
at nl.ordina.jdk8.TimePersistenceTest.insertParameterSetObject(TimePersistenceTest.java:48)

If I skip the JDBCType.Date, I get the same error.

In the "JDBC Maintenance Release 4.2" proposed changes the following is written

"21 Add the following mapping to table B-4, Mapping from Java Objects to JDBC Types
• Map the Java Object Type javax.time.LocalDate to the JDBC Type DATE

22 Add the following mapping to table B-5, Mapping, conversions Performed by setObject and setNull between
Java Object Types
• Allow the conversion of javax.time.LocalDate to CHAR, VARCHAR, LONGVARCHAR, and DATE"

Am I missing something?


Martijn Blankestijn
SolGT | 31 Dec 12:01 2013

Derby running on local computer ans Data Base located on server


In derbydev.pdf ( page 26, I read  :
Conventions for specifying the database path
You can specify only databases that are local to the machine on which the JVM
is running. ...
I want to use Derby (embedded mode) in my application on a networked computer and place the databases on the server.
Is it possible ?


Tim Dudgeon | 27 Dec 13:05 2013

ArrayIndexOutOfBoundsException when calling function from trigger

I'm having problems will calling functions from within a trigger.
The problem seems to be with the outer function call (FLOOR() in this 
case, but it also happens with other functions). It works fine in the 
SELECT statement, but when used in the trigger it throws a 
Remove the FLOOR() part from the trigger and it works fine.

-- create source table and some data
CREATE TABLE foo (name VARCHAR(20), val DOUBLE);
INSERT INTO foo (name, val) VALUES ('A', 10), ('A', 20), ('B', 30), 
('C', 40);

-- calling the function works fine here
SELECT name, FLOOR(AVG(LOG10(val))), COUNT(*)
FROM foo
GROUP BY name;

-- create target table for trigger
CREATE TABLE summary (name VARCHAR(20), aver DOUBLE, size INT);

-- create the trigger
INSERT INTO summary (name, aver, size)
SELECT name, FLOOR(AVG(LOG10(val))), COUNT(*)
FROM changed
GROUP BY name;

-- insert rows to cause trigger to fire
INSERT INTO foo (name, val) VALUES ('A', 10), ('A', 20), ('B', 30), 
('C', 40);

SELECT * FROM summary;

Any ideas?

Tim Dudgeon | 20 Dec 20:54 2013

Trigger questions

I'd like to check that my understanding of how triggers work is correct.

1. There is no procedural language like PL/SQL. The only solution here 
is to use Java stored procedures.

2. Triggers can only execute a single statement. If you need to do 
multiple things then you need to create multiple triggers (and in the 
right order or execution).

3. Relating to this I'm finding something I didn't expect.
I create 2 triggers on a table:
create trigger trg1 after insert on tab ... for each row ...
create trigger trg2 after insert on tab ... for each row ...

Then I do an insert like this:
insert into tab ( ... ) values ( ... )

and the triggers seem to fire like this:
trg1, trg2
So far so good.

Now I do in insert like this:
insert into tab ( ... ) values ( ... ), ( ... ), ( ... )
and it seems the triggers fire like this:
trg1, trg1, trg1, trg2, trg2, trg2
I was expecting:
trg1, trg2, trg1, trg2, trg1, trg2

But what I really want is:
trg1, trg2

4. There is no way to have a single trigger that handles 
insert/update/delete. You need multiple (potentially identical) triggers

p.s. What I'm wanting to achieve is to have trigger(s) on a table that 
when an insert/update/delete operation occurs results in updating a 
different table with a summary of the information for the modified row 
and related rows (e.g averaging that row and all other rows with some 
common attribute).

Many thanks


Tim Dudgeon | 19 Dec 12:29 2013

varargs with functions

I'm struggling with getting a function working that uses a Java static 
method that uses varargs.
I understand this should work with

I create function like this:

EXTERNAL NAME 'java.lang.String.format'

and use it like this:

values(FORMAT_CPD_CODE('XYZ%08d', 123))

But I get error:

java.sql.SQLSyntaxErrorException: No method was found that matched the 
method call java.lang.String.format(java.lang.String, int...), tried all 
combinations of object and primitive types and any possible type 
conversion for any  parameters the method call may have. The method 
might exist but it is not public and/or static, or the parameter types 
are not method invocation convertible.

Any suggestions on how to get this working?



irajon | 16 Dec 23:46 2013

Database Table Null Pointer Exception

I use Derby as an embedded database.  My program Is sent to clients with the
database.  I have a client that installed an update to my software and
somehow, a new table was generated incorrectly.  The program did a create
table, but when I look at this client's database, I don't see the new table. 
When I try to create the table in the client's database, it says that the
database already exists, and when I try to drop the table, it gives me a
null pointer exception.  
I ran the following sql query select * from SYS.SYSCONGLOMERATES left outer
join sys.systables on SYS.SYSCONGLOMERATES.tableid = sys.systables.tableid. 
this produced a listing of all of my tables and conglomerates etc... 
There is an extra line in the result that has a conglomerate, but no table
data.  I'm assuming, this is the corruption.  
Please let me know if this is correct, and how I can fix the issue.  I have
been working on trying to export the data, and then bring the data back to a
new uncorrupted database, but there is a lot of work involved because of
foreign keys and such.  


View this message in context: http://apache-database.10148.n7.nabble.com/Database-Table-Null-Pointer-Exception-tp136115.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Lasantha Siriwardana | 13 Dec 02:36 2013

Derby In-Memory DB is not using any available indexes

Hi All,

I've setup an derby in-memory db using derby distribution Below are the configurations;
Max Heap Size - 3GB
Page Cache Size - 10000
I stored nearly one million records in a table with around 50 columns and trying to retrieve the data. But the query takes ~10sec. When I check the explain plan, It's not used any index and the optimizer cost is very higher. Also the scan type was heap. Please help to resolve this issue and tuneup the query. Why doesn't derby in-memory use indexes?

nglasantha | 11 Dec 14:27 2013

Derby logs are not getting printed

Hi all,

I have configured derby logs, but logs are not getting printed. please help
to resolve this issue.

I tried to search the answer. Everybody is saying just add following entries
to property file. Still am not able to see log entries.


Derby version : derby- 

Am i missing something ?


View this message in context: http://apache-database.10148.n7.nabble.com/Derby-logs-are-not-getting-printed-tp136011.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Frans Verster | 7 Dec 11:08 2013

Java DB / Apache Derby Table Functions,current user?

Hi Guys and Girls,

Sorry to bother.. I am making some Java DB / Apache Derby Table Functions, working very nice. I need to know the user querying the function to return only his/her items. How can I get the session/login/current user?

Thanks in advance