Can we use Apache Derby (aka. Java DB) for event subsystem DB?

In light of the gloriously failing out of the box event subsystem db...

Has anyone tried setting up Apache Derby (a.k.a. Java DB, IBM Cloudscape) for the event subsystem db? Granted, I know nothing about Derby, but I was recently alerted of its (possibly promising) existance by this "The Register" article on Apache Derby

I thought I might check it out when I get a quiet day at the office ;-) That's today... But I would like to benefit from your experience. I promise to return the favour... Smiley Wink

cheers!

Find more posts tagged with

Comments

Migrateduser

Hi Frederik,
The openJMS latest release version(

0.7.6.1

) does not officially certify derby, however the newer still alpha version OpenJMS 0.7.7 has the embedded derby DB instead of the flat file. There are few tweeks and tools you will need to generate the DDL for the derbyDB to use it with openJMS. I shall attach the the DDL scripts and the JDBC configuartions TAGS for derby. Though NOT officially certified but you can test the working/performace if you must need to plugin derby.

Migrateduser

Detailed Steps:
1.) Download the latest derby 10.2.1.6 release.
2.) Use ij tool to Set the DB security settings
-- adding the user usr with password 'test'
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.usr','test');
-- adding the derby users and not the LDAP or external
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider','BUILTIN');
-- adding the authentication required property
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication','true');
3.) Create the eventDB using the ij tool
ij> connect 'jdbc:derby:eventDB;create=true;user=usr;password=test';
4.) Run the attached sql scripts to create the DB tables for the eventSubsystem.
5.) Replace the followingXML tag in the JMSConfigNEw.xml

6.) Add the derby JDBC driver jar (derbyclient.jar) in the eventSubsystem classpath. You will have to modify the registry settings in Windows. See how to plugin the new DB in eventSusbsystem from support site fro help.
7.) You are done Smiley Happy

Migrateduser

Here is the derby DDL.

Frederik

Thx HemantGaur!

I actually got it running myself on Friday, but I got stuck in the TS configurations, which we had previously adapted to NOT use the event subsystem at all.

Finally figured out that we had #ew_enable=false in iw.cfg. Dumb me did the uncomment to ew_enable=false which I finally fixed to ew_enable=true

But I'm happy to receive your input, because yours is cleaner with the added Authentication settings. I hadn't figured that out yet...

I was using non-authenticated connection, and I was using the DB2 ddl script (iw-home/eventsubsystem/conf/ddl/create_db2.sql). Apparently DB2 shares the same SQL syntax (largely) with Derby.

Checking the diff between your derby.sql and the db2.sql, I spot
- use of schema "usr" vs. no explicit schema (default in derby = 'app')
- Destination.ISQUEUE : type char(1) vs int
- System_Data.ID : type int vs bigint
- Messages.MESSAGEBLOB : LONG VARCHAR FOR BIT DATA vs BLOB(100K)
- Consumers.NAME : varchar(252) vs varchar(255)
- use of "not null" in db2.sql whenever possible.

Not sure why these changes exist... are they meant to keep the eventdb smaller (disk space)? Are you working for IWOV, btw?

Thanks again!

EDIT: corrections to your instructions
- step 2 must be performed after step 3.
- step 5 has been mangled by the forum (fix: surround by {HTML} tags), and seems to miss password="test" as attribute
[HTML]
driver="org.apache.derby.jdbc.ClientDriver"
url="jdbc:derby://localhost/eventDB"
user="usr"
password="test"
maxActive="10"
maxIdle="5"
minIdleTime="1800"
evictionInterval="3600" />[/HTML]

Migrateduser

Thanks Frederik Smiley Happy

I did the configurations after some trial and error and hence the step ordering might have been swapped but happy to know you also got that working.

I strongly suggest to use the SQL schema(ddl) provided by me because it has been generated using the openJMS tool (dbtool reading DB details from the OpenJMSconfig and creating the desired tables) and is more explicit in the constraints.This should be better performance wise, however your DB2 schema might also work.These tools are not shipped with the OpenJMS with TeamSite as Interwoven provides for the complete customized solution.

And yes I work for Interwoven :cool:.

Cheers,
Hemant Gaur

Frederik

The openJMS latest release version([/size]
0.7.6.1
[size=2]) does not officially certify derby, however the newer still alpha version OpenJMS 0.7.7 has the embedded derby DB instead of the flat file.

This was a year ago... Any news whether any future Teamsite version will include a derby DB to replace the flat file? If I understand it right, Interwoven is waiting for a newer version of OpenJMS that would include this directly?

Migrateduser

>>This was a year ago... Any news whether any future Teamsite version will include a derby DB to replace the flat file? If I understand it right, Interwoven is waiting for a newer version of OpenJMS that would include this directly?

The OpenJms has not yet released newer version and there were some evaluations done for the newer openJMS alpha and beta. The 6.7.1 SP1 is planned to have derby packaged with the TS installer.