Question

In this chapter, we will mention Derby tools. The Derby tools and utilities are a set of scripts supplied with Derby. They are typically used to create, inspect, and update a Derby database.

In this page, we will mention the sysinfo , the dblook , the ij , the startNetworkServer , and the stopNetworkServer tools. Note that these tools have .bat extension on Windows.

Launching Derby tools

Derby tools can be run in two ways. We use the script names located in the bin directory of the Derby installation directory. Or we can use the derbyrun.jar file to launch them.

$ ij
$ java -jar $DERBY_HOME/lib/derbyrun.jar ij

Assuming that the Derby bin directory is added to the PATH environment variable, we can launch the ij tool by specifying the name of the script in the terminal. The second line runs the ij using the derbyrun.jar file.

sysinfo

The sysinfo tool provides information about the Operating system, Java and Derby. It will print among others Java version, Java home directory, OS version, Java runtime version, Derby version, current and supported locales. The tool can be useful to track down some installation or configuration issues with Derby.

$ sysinfo
------------------ Java Information ------------------
Java Version:  1.6.0_30
Java Vendor:   Sun Microsystems Inc.
Java home:     /home/janbodnar/bin/jdk1.6.0_30/jre
Java classpath:  /home/janbodnar/bin/derby/lib/derby.jar:/home/janbodnar/bin/derby/lib/
  derbynet.jar:/home/janbodnar/bin/derby/lib/derbytools.jar:/home/janbodnar/bin/
  derby/lib/derbyclient.jar
OS name:     Linux
OS architecture: i386
OS version:    3.0.0-15-generic
Java user name:  janbodnar
Java user home:  /home/janbodnar
...

This is an excerpt from the information provided on a particular system.

ij

The ij is an interactive scripting tool. It is used for running scripts or interactive queries against a Derby database.

$ cat cars.sql
CREATE SCHEMA USER12;
CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS VALUES(1, 'Audi', 52642);
INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
INSERT INTO CARS VALUES(3, 'Skoda', 9000);
INSERT INTO CARS VALUES(4, 'Volvo', 29000);
INSERT INTO CARS VALUES(5, 'Bentley', 350000);
INSERT INTO CARS VALUES(6, 'Citroen', 21000);
INSERT INTO CARS VALUES(7, 'Hummer', 41400);
INSERT INTO CARS VALUES(8, 'Volkswagen', 21600);

We have a cars.sql file which creates a database schema and a CARS table.

$ ij
ij version 10.8
ij> connect 'jdbc:derby:dbs/testdb;user=user12;create=true';

We start the ij tool. If we had not added the Derby's bin directory to the PATH variable, we would have to specify the whole path to the ij tool. We create a testdb database in the dbs directory and make a connection to it. The dbs directory is a subdirectory of our current directory, where the ij was launched.

ij> SHOW CONNECTIONS;
CONNECTION0* -  jdbc:derby:dbs/testdb
* = current connection

The SHOW CONNECTIONS statement displays opened connections to Derby databases.

ij> RUN 'cars.sql';
ij> CREATE SCHEMA USER12;
0 rows inserted/updated/deleted
ij> CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
0 rows inserted/updated/deleted
ij> INSERT INTO CARS VALUES(1, 'Audi', 52642);
1 row inserted/updated/deleted
ij> INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
1 row inserted/updated/deleted
ij> INSERT INTO CARS VALUES(3, 'Skoda', 9000);
...

We load and execute the cars.sql site. We are informed about the ongoing operations.

ij> SELECT * FROM CARS;
ID     |NAME              |PRICE    
------------------------------------------------------
1      |Audi              |52642    
2      |Mercedes            |57127    
3      |Skoda             |9000     
4      |Volvo             |29000    
5      |Bentley             |350000   
6      |Citroen             |21000    
7      |Hummer            |41400    
8      |Volkswagen          |21600    

8 rows selected

We select all rows from the CARS table.

ij> connect 'jdbc:derby:dbs/testdb;shutdown=true';
ERROR 08006: Database 'dbs/testdb' shutdown.

Shutting down a database in Derby results in an exception. The ERROR 08006 is expected.

ij> SHOW CONNECTIONS;
No current connection

The connection is closed.

ij> EXIT;

We quit the ij tool with the EXIT command. Note that each command is followed by semicolon.

dblook

The dblook tool is used to save the data definition language of database objects including tables, views, indexes, or triggers.

$ dblook -d jdbc:derby:dbs/testdb
-- Timestamp: 2012-02-12 13:33:53.677
-- Source database is: dbs/testdb
-- Connection URL is: jdbc:derby:dbs/testdb
-- appendLogs: false

-- ----------------------------------------------
-- DDL Statements for schemas
-- ----------------------------------------------

CREATE SCHEMA "USER12";

-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------

CREATE TABLE "USER12"."CARS" ("ID" INTEGER NOT NULL, 
  "NAME" VARCHAR(30), "PRICE" INTEGER);

-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------

-- primary/unique
ALTER TABLE "USER12"."CARS" ADD CONSTRAINT "SQL120212131535700" PRIMARY KEY ("ID");

In the above example, we have dumped the objects from the testdb database. With the -d option we have provided the connection URL to the database. In our case the dblook tool saved a database schema and one table. With the -o option the output can be redirected to a file.

startNetworkServer & stopNetworkServer

The two scripts start and stop the Derby Network server. In case of a networked server, multiple connections to a Derby database may be created.

$ startNetworkServer &
[1] 3742
$ Sun Feb 12 14:22:30 CET 2012 : Security manager installed using 
the Basic server security policy.
Sun Feb 12 14:22:30 CET 2012 : Apache Derby Network Server - 10.8.2.2 - 
(1181258) started and ready to accept connections on port 1527

Here we start the Derby Network Server with the startNetworkServer script.

ij> connect 'jdbc:derby://localhost:1527/dbs/testdb';

Here we connect to the testdb database via the Derby Network Server. The connection URL is different for networked connections.

ij> SELECT * FROM USER12.CARS;
ID     |NAME              |PRICE    
------------------------------------------------------
1      |Audi              |52642    
2      |Mercedes            |57127    
3      |Skoda             |9000     
4      |Volvo             |29000    
5      |Bentley             |350000   
6      |Citroen             |21000    
7      |Hummer            |41400    
8      |Volkswagen          |21600    

8 rows selected

We select all cars from the CARS table. Since we have not provided the database schema in the connection URL, we must specify it now. The database schema is the user name; in our case USER12 .

$ stopNetworkServer
Sun Feb 12 14:48:51 CET 2012 : Apache Derby Network Server 
- 10.8.2.2 - (1181258) shutdown
$ Sun Feb 12 14:48:51 CET 2012 : Apache Derby Network Server 
- 10.8.2.2 - (1181258) shutdown

We have stopped the server with the stopNetworkServer script.

In the chapter, we have written about Derby tools.