How to connect to Oozie's Derby database
Article ID: 294841
Updated On:
Products
Issue/Introduction
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.
Environment
Resolution
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 directory on the Oozie host.
$ 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:
a. In Ambari 1.7.1 / JDK 1.7.0, use the following command:
[root@amb171hawq ~]# /usr/jdk64/jdk1.7.0_67/db/bin/ij ij version 10.8 ij>
b. 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 ij>
5. In ij, connect to the database where /hadoop/oozie/data/oozie-db is the database directory found in step 2 above.
ij> connect 'jdbc:derby:/hadoop/oozie/data/oozie-db';
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 through Ambari.
8. If the restart of Oozie fails, it may be that ij was run as root. In that case, refer to the KB article, Oozie server showing as stopped in Ambari immediately after trying to start up the service.
Feedback
