How to export data from Oozie Derby database?

[#1]

I'm looking to generate reports on workflow performance in Oozie: failures, durations, users, etc... What is the best way to do so if Oozie is currently using DerbyDB and not MySQL? (Prior to moving Oozie to MySQL.)

I stopped the Oozie service and used the Phoenix sqlline tool:

su - oozie -c "/usr/hdp/current/oozie-server/bin/oozie-stop.sh"
java -cp .:/usr/hdp/2.3.2.0-2950/oozie/libserver/derby-10.10.1.1.jar:/usr/hdp/2.3.2.0-2950/phoenix/bin/../phoenix-4.4.0.2.3.2.0-2950-thin-client.jar sqlline.SqlLine -d org.apache.derby.jdbc.EmbeddedDriver -u jdbc:derby:/hadoop/oozie/data/oozie-db -n none -p none --color=true --fastConnect=false --verbose=true --isolation=TRANSACTION_READ_COMMITTED

0: jdbc:derby:/hadoop/oozie/data/oozie-db> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('OOZIE','WF_ACTIONS','WF_ACTIONS.del',null,null,null);
0: jdbc:derby:/hadoop/oozie/data/oozie-db> !outputformat vertical
0: jdbc:derby:/hadoop/oozie/data/oozie-db> !tables
0: jdbc:derby:/hadoop/oozie/data/oozie-db> SELECT STATUS, WF_ID, TYPE, NAME, EXECUTION_PATH, ERROR_MESSAGE FROM OOZIE.WF_ACTIONS;

[#2 · Accepted solution]

@Vladimir Zlatkin here's one idea Link. Here are tools in their official page Link derby wiki has a full list Link

[#3]

Since Oozie is stopped you can use ij, Derby interactive sql tool.