Access Jedox H2 Console

If you use Jedox’ drill through capabilities to a H2 database you might want to look inside the database file. Think of renamed cubes: They lose drill through capability until the the table in the drill through database is renamed accordingly. Unfortunately Jedox does not support and thus does not document this at all.

Given the cube load advanced settings “Drill through mode” = “Persisted” the database can be found here:

$ ll /opt/jedox/ps/tomcat/webapps/etlserver/data/db/etl_drillthrough/
total 34,816,159,744
drwxrwxr-x 2 jedoxweb jedoxweb          4,096 Mar 21 12:05 .
drwxr-xr-x 7 jedoxweb jedoxweb          4,096 Mar 21 11:57 ..
-rw-r--r-- 1 jedoxweb jedoxweb 34,782,119,936 Mar 21 12:05 drill.h2.db
-rw-r--r-- 1 jedoxweb jedoxweb         18,869 Mar 21 11:27 drill.trace.db

Locate and start the H2 console on the Jedox server:

$ sudo find /opt/jedox/ps -type f -name h2*jar
/opt/jedox/ps/tomcat/webapps/etlserver/WEB-INF/lib/h2-1.3.176.jar

$ java -jar /opt/jedox/ps/tomcat/webapps/etlserver/WEB-INF/lib/h2-1.3.176.jar
Failed to start a browser to open the URL http://123.123.123.123:8082: Browser detection failed and system property h2.browser not set

Of course we have no browser installed on the system. Instead, from our local machine we create a SSH tunnel to the Jedox server with ssh -L 8082:127.0.0.1:8082 username@servername and open the H2 Console with the URL http://127.0.0.1:8082/ in our local machine’s web browser. The JDBC URL to connect to the database is jdbc:h2:/opt/jedox/ps/tomcat/webapps/etlserver/data/db/etl_drillthrough/drill.

The database belongs to jedoxweb:jedoxweb and has permissions 644 (rw-r--r--) set but we come as a different user. Therefore the database is read only in the H2 Console. In order to get write permission we change the database’s permissions to 666 (rw-rw-rw-). Undo this after altering the database.