Database Administration GUI for Derby

bkonia

Although Aware IM is a fully self-contained system that manages your database for you, there may still be times when you need to access the database directly. If you're using MySQL, there are dozens of database administration tools available. However, if you're using Derby (Aware IM's built-in database), you'll have a much harder time finding any user-friendly tools to accomplish this.

The good news is, there is an excellent and extremely user-friendly GUI for Derby. The tool I'm referring to is called "Cloudscape Workbench" and it's published by IBM. You can get a free download from the following URL:

http://www-128.ibm.com/developerworks/db2/downloads/csworkbench/

You do have to register and create an account with IBM prior to downloading, but the registration procedure is relatively painless.

After installing Workbench on your Aware IM server machine, you'll need to create a connection to your Aware IM database. Before you create the connection, it's very important that you shut down the Aware IM configuration tool so that the Aware IM server, JBoss and Tomcat are all stopped. The connection will fail if you try to connect while the Aware IM server components are running.

To create the connection:

Select Derby 10.1 in the database manager tree.

Select "Derby Embedded JDBC Driver" from the JDBC Driver dropdown menu.

Under "Database Location", click on the Browse button and browse to the location in your file system where your Aware IM database is found. On a Windows system, this will normally be C:\AwareIM\DATA\BASDB.

Un-Check "Create the database if required".

Under "Class Location", browse to the location of your derby.jar file. Normally this will be C:\AwareIM\Tomcat\shared\lib\derby.jar

Under "User Information", set "User ID" = root and leave the password blank.

At that point, click on the "Test Connection" button and it should indicate that the connection was successful.

Please note: Most users should never need to access the database directly and you can seriously screw things up if you don't know what you're doing. Therefore, I would strongly discourage you from trying this unless you have a really good reason for needing to access the database.

tford

Thanks for pointing out this tool, Brad!

Sometimes I link to "look under the hood" to understand things.

Tom

matverp

The above link is not available anymore:

http://www-128.ibm.com/developerworks/db2/downloads/csworkbench/

Does anyone know where to find a database administration tool for Derby?

aware_support

Derby comes with a tool called "ij" which allows you to enter SQL commands (similar to the mysql utility in MySQL). While this is not a graphic tool it is quite adequate to do everything you need to do with Derby at the database level. You can find all the files you need here http://www.awareim.com/ij

matverp

Thanks, this is helpful.
Do you know of a tool with a GUI as well?

...

RocketRod

Thanks support. I downloaded the files but hey have no idea where to start. What do I do with these files and how do I use them?

Cheers Rod

BobK

While Derby is no longer the database I Use, in the past I did access a Derby database using Squirrel.

SQuirreL SQL Client is a graphical Java program that will allow you to view the structure of a JDBC
compliant database, browse the data in tables, issue SQL commands etc.

Squirrel can be found at http://www.squirrelsql.org/

Go to the 'Download and installation' page from the menu on the left.
Download the 'Plain Zip format..' version. The current file to download is squirrel-sql-3.1.2-standard.zip
After downloading, extract into the folder of your choice.

To start squirrel, run squirrel-sql.bat or squirrel-sql.sh, depending upon your system.

One time setup.
After starting Squirrel:
A) Select the Drivers tab on the far left.
B) Double click 'Apache Derby Embedded'
C) In the 'Example URL' replace <database>[;create=true] with the location of the Derby data
which should be something like C:/AwareIm/Data/BASDB. So the Example URL will be: jdbc:derby:C:/AwareIm/Data/BASDB
D) Select the 'Extra Class Path' tab and press the 'Add' button.
E) Find the derby.jar file, which should be in AwareIM\Tomcat\shared\lib and press 'Open'. Press 'OK' to close the driver form.
F) Select the 'Aliases' tab on the far left and press the plus sign button to create a new Aliases.
G) Enter a name and select the 'Apache Derby Embedded' driver.
H) Press the test button, enter User and Password if needed and press the connect button. Hopefully you get a 'Connection successful' response.
I) Press 'OK' to save the aliase.
J) The just created alias will connect to the AwareIM production database.
K) To connect to the test database, repeat steps F thru I. In step G, in the URL, replace BASDB with BASDBTEST

To use Squirrel
A) Double click on one of the aliases you just created, enter user and password if needed and press connect.
B) You should see 2 tabs. The 'Objects' tab will display various database objects and the 'SQL' tab where you can enter sql statements to access the database.

NOTE: Before useing Squirrel, you must shutdown AwareIM. I think this is because AwareIM uses Derby as an embedded database.

RocketRod

Thanks for that. All installed, set up and working fine including updating of tables. A must for anyone using Derby. Very good and clear instructions.

Cheers Rod

aware_support

And for those who wish to use the command line tool (ij), you need to do the following:

1) Download the file http://www.awareim.com/ij/derbytools.jar
2) Put this file into AwareIM/Tomcat/shared/lib directory
3) Download the file http://www.awareim.com/ij/ijBASDB.bat.bin
4) Rename it to ijBASDB.bat
5) Put it into AwareIM/bin directory and run it from there

If all goes well you should see this prompt:
ij>

You can enter SQL commands at this prompt. Each command must end with semi-colon.