Page History

...

• There's a primitive command-line utility called ij. Its interactive use can be made tolerable by giving it virtual files via process substitutionrunning it repeatedly from the shell via: ij -p <(echo "...connection properties...")  <(echo "...sql commands...")
• Once connected via ij:
  • SHOW TABLES; – shows tables
  • DESCRIBE <tablename>;  – describes a particular table. If the tablename is uppercase, don't quote it here.
  • Quote uppercase table names in SQL queries. E.g. SELECT "ID" from "SYNCS";
• If ij's output is too narrow (row values end with '&' indicating truncation), run the SQL: maximumdisplaywidth 10000;
• For BLOBs, this page of examples is your friend.
• The Derby docs are quite good, but use frames, so Google will take you to some random-looking page without any context. Start from here.

...

First get the Derby distribution, which includes the ij command-line utility.

root@usw1-l-jira01:~# wget 'http://archive.apache.org/dist/db/derby/db-derby-10.9.1.0/db-derby-10.9.1.0-bin.tar.gz'
root@usw1-l-jira01:~# tar zxvf db-derby-10.9.1.0-bin.tar.gz
root@usw1-l-jira01:~# export PATH+=:$HOME/db-derby-10.9.1.0-bin/bin
root@usw1-l-jira01:~# which ij    
# /home/redradish/db-derby-10.9.1.0-bin/bin/ij

...

So first we must make an offline copy of the Structure database;

root@usw1-l-jira01:~# cp -ra /var/atlassian/application-data/jira/current/structure ~/structure_offline

...

ij is as primitive and ugly as you'd expect. There is exactly one way to invoke it: passing in a properties file for connection parameters, and a SQL file to run:

ij> root@usw1-l-jira01:~# ij --help
Usage: java org.apache.derby.tools.ij [-p propertyfile] [inputfile]

To auto-connect to a particular database, you need to create a properties file, and tell ij to use it. For instance I created a ~/structure_offline.props file to connect to the ~/structure_offline/db Derby database, copied above:

echo "ij.database=jdbc:derby:/home/redradish${HOME}/structure_offline/db" > ~/structure_offline/db.props

and finally we connect:

root@usw1-l-jira01:~# ij -p structure_offline.props 
ij version 10.9
CONNECTION0* -     jdbc:derby:/home/redradish/structure_offline/db
* = current connection
ij> 

...