Installing IJ Derby command line database client to assist with ESM troubleshooting

Document ID : KB000045619
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction: 

When attempting to troubleshoot an Enterprise Service Mananger (ESM) problem it can be useful to have access to the internal Apache Derby database.  The following instructions can be used to deploy IJ command line client.

Background:  

The ESM uses an Apache Derby single user database instance to store data regarding previous migrations and other configurations.  

Instructions: 

1) SSH onto the ESM appliance as root.

2) As the derby DB is single user only we need to stop the ESM 'service ssem stop

3) Install derby command line tool ij, see the following URL for location of download:- 

cd ~
mkdir derby 
cd derby 
tar -zxvf db-derby-10.12.1.1-lib.tar.gz
export DERBY_INSTALL=~/derby/db-derby-10.12.1.1-lib
export CLASSPATH=$DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL/lib/derbytools.jar:. 

4) Verify the derby IJ tool '/opt/SecureSpan/JDK/bin/java org.apache.derby.tools.sysinfo

5) Backup the original ESM DB:- 

cd /opt/SecureSpan/EnterpriseManager/var/ 
tar -cvf ~/esmdbbackup.tar db 

6) Execute ij '/opt/SecureSpan/JDK/bin/java org.apache.derby.tools.ij

7) At IJ command prompt run:- 

connect 'jdbc:derby:/opt/SecureSpan/EnterpriseManager/var/db/emsdb'; 
 
8) You can now perform standard SQL against the database as directed by CA Support. 
 
9) When finished you can restart ESM 'service ssem start' wait for it to start.

If required you can restore the esmdb from the back via:-

service ssem stop 
mv /opt/SecureSpan/EnterpriseManager/var/db /opt/SecureSpan/EnterpriseManager/var/dbmodified 
cd /opt/SecureSpan/EnterpriseManager/var/ 
tar -xvf ~/esmdbbackup.tar 
service ssem start 

Additional Information:

https://db.apache.org/derby/docs/10.3/tools/

http://db.apache.org/derby/releases/release-10.12.1.1.cgi