Pivotal Knowledge Base


How to connect to Oozie's Derby database


Product Version
Pivotal HD 3.x
Oozie 4.1


While troubleshooting Oozie issues, it may be necessary to connect to Oozie's underlying database. By default, Oozie's database is run on Derby and this article explains how to connect to the Derby database and run SQL commands.

Note: Pivotal does not recommend using the Derby database as a backend to Oozie in production environments; MySQL, Oracle, or other options should be used. 


1. In Ambari, take note of the following two Oozie configurations:

  • Oozie Data Dir
  • Database Name

2. In Ambari, shutdown Oozie.

3. Log on to the Oozie host AS the OOZIE user and find the data directory for the database, by going into Oozie data dir on the oozie host, for example, here it is oozie-db:

[oozie@node2 ~]$ ls -ltr /hadoop/oozie/data/
total 8
drwxr-xr-x 5 oozie hadoop 4096 Jun 29 07:12 oozie-db
-rw-r--r-- 1 root  root    662 Jun 29 07:12 derby.log
[oozie@node2 ~]$ 

4. On the Oozie host, as user OOZIE, run ij:

In Ambari 1.7.1 / JDK 1.7.0:

[root@amb171hawq ~]# /usr/jdk64/jdk1.7.0_67/db/bin/ij
ij version 10.8

In Ambari 2.x / JDK 1.8.0 the following must be done:

[oozie@node2 ~]$ /var/lib/ambari-agent/data/tmp/jdk/jdk1.8.0_40/db/bin/ij
ij version 10.8

5. In IJ, connect to the database where /hadoop/oozie/data/oozie-db is the database directory dir found in step 2 above.

ij> connect 'jdbc:derby:/hadoop/oozie/data/oozie-db';

6. Most standard SQL commands will then work. To display the tables, "show tables" can be used. WF_JOBS and WF_ACTIONS will be the most relevant tables to work on:

ij> show tables;
TABLE_SCHEM         |TABLE_NAME                    |REMARKS             
SYS                 |SYSALIASES                    |                    
SYS                 |SYSCHECKS                     |                    
SYS                 |SYSCOLPERMS                   |                    
SYS                 |SYSCOLUMNS                    |                    
SYS                 |SYSCONGLOMERATES              |                    
SYS                 |SYSCONSTRAINTS                |                    
SYS                 |SYSDEPENDS                    |                    
SYS                 |SYSFILES                      |                    
SYS                 |SYSFOREIGNKEYS                |                    
SYS                 |SYSKEYS                       |                    
SYS                 |SYSPERMS                      |                    
SYS                 |SYSROLES                      |                    
SYS                 |SYSROUTINEPERMS               |                    
SYS                 |SYSSCHEMAS                    |                    
SYS                 |SYSSEQUENCES                  |                    
SYS                 |SYSSTATEMENTS                 |                    
SYS                 |SYSSTATISTICS                 |                    
SYS                 |SYSTABLEPERMS                 |                    
SYS                 |SYSTABLES                     |                    
SYS                 |SYSTRIGGERS                   |                    
SYS                 |SYSUSERS                      |                    
SYS                 |SYSVIEWS                      |                    
SYSIBM              |SYSDUMMY1                     |                    
OOZIE               |BUNDLE_ACTIONS                |                    
OOZIE               |BUNDLE_JOBS                   |                    
OOZIE               |COORD_ACTIONS                 |                    
OOZIE               |COORD_JOBS                    |                    
OOZIE               |OOZIE_SYS                     |                    
OOZIE               |OPENJPA_SEQUENCE_TABLE        |                    
OOZIE               |SLA_EVENTS                    |                    
OOZIE               |SLA_REGISTRATION              |                    
OOZIE               |SLA_SUMMARY                   |                    
OOZIE               |VALIDATE_CONN                 |                    
OOZIE               |WF_ACTIONS                    |                    
OOZIE               |WF_JOBS                       |                    

35 rows selected

7. Once finished, start Oozie again via Ambari.

8. If the restart of Oozie fails, it may be that ij was run as root. In that case, see the KB article, Oozie server showing as stopped in Ambari immediately after trying to start up the service.



Powered by Zendesk