Derby tools

In this chapter, we mention Derby tools. 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, dblook, ij, startNetworkServer, and stopNetworkServer tools.

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.

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

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.

$ $DERBY_HOME/bin/sysinfo
------------------ Java Information ------------------
Java Version:    1.8.0_111
Java Vendor:     Oracle Corporation
Java home:       /home/janbodnar/bin/jdk1.8.0_111/jre
Java classpath:  /home/janbodnar/bin/jdk1.8.0_111/db/lib/derby.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbynet.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbytools.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbyoptionaltools.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbyclient.jar
OS name:         Linux
OS architecture: amd64
OS version:      4.4.0-66-generic
...

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
SET SCHEMA USER12;
CREATE TABLE CARS(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
    (START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO CARS(Name, Price) VALUES('Skoda', 9000);
INSERT INTO CARS(Name, Price) VALUES('Volvo', 29000);
INSERT INTO CARS(Name, Price) VALUES('Bentley', 350000);
INSERT INTO CARS(Name, Price) VALUES('Citroen', 21000);
INSERT INTO CARS(Name, Price) VALUES('Hummer', 41400);
INSERT INTO CARS(Name, Price) VALUES('Volkswagen', 21600);

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

$ $DERBY_HOME/bin/ij
ij version 10.11
ij> CONNECT 'jdbc:derby:testdb;user=user12;create=true';

We start the ij tool. We create a testdb database and make a connection to it.

ij> SHOW CONNECTIONS;
CONNECTION0* - jdbc:derby: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:testdb;shutdown=true';
ERROR 08006: Database '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, and triggers.

$DERBY_HOME/bin/dblook -d jdbc:derby:testdb
-- Timestamp: 2017-03-13 20:05:43.281
-- Source database is: testdb
-- Connection URL is: jdbc:derby: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);

CREATE TABLE "APP"."CARS" ("ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(30), "PRICE" INTEGER);

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

-- PRIMARY/UNIQUE
ALTER TABLE "APP"."CARS" ADD CONSTRAINT "SQL170313140819740" PRIMARY KEY ("ID");

ALTER TABLE "USER12"."CARS" ADD CONSTRAINT "SQL170313200304680" 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 and stopNetworkServer

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

$ $DERBY_HOME/bin/startNetworkServer &
[1] 12421
$ Mon Mar 13 20:12:39 CET 2017 : Security manager installed using the Basic server security policy.
Mon Mar 13 20:12:40 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) started and ready to accept connections 

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

ij> CONNECT 'jdbc:derby://localhost:1527/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.

$ $DERBY_HOME/bin/stopNetworkServer
Mon Mar 13 20:15:42 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown
$ Mon Mar 13 20:15:42 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown

We have stopped the server with the stopNetworkServer script.

NetworkServerControl

NetworkServerControl is a system tool which enables to start and stop Derby network server and configure or retreive diagnostic information. With the exception of ping, all commands can only be performed from the machine on which the server is running.

$ $DERBY_HOME/bin/NetworkServerControl start &

With the start command, we start the Derby server.

$ $DERBY_HOME/bin/NetworkServerControl ping
Tue Mar 21 15:53:29 CET 2017 : Connection obtained for host: localhost, port number 1527.

The ping command tests whether the Derby server is up.

$ $DERBY_HOME/bin/NetworkServerControl sysinfo
--------- Derby Network Server Information --------
Version: CSS10110/10.11.1.2 - (1629631)  Build: 1629631  DRDA Product Id: CSS10110
-- listing properties --
derby.drda.traceDirectory=/home/janbodnar/.derby/
derby.drda.maxThreads=0
derby.drda.sslMode=off
derby.drda.keepAlive=true
...

The sysinfo command provides system information.

$ $DERBY_HOME/bin/NetworkServerControl runtimeinfo
--- Derby Network Server Runtime Information ---
---------- Session Information ---------------
Session # :3
-------------------------------------------------------------
# Connection Threads : 1
# Active Sessions : 1
# Waiting  Sessions : 0

Total Memory : 78643200	Free Memory : 75359512

The runtimeinfo command provides extensive debbugging information about sessions, threads, prepared statements, and memory usage for the running Network Server.

$ $DERBY_HOME/bin/NetworkServerControl shutdown
Tue Mar 21 15:56:43 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown
Tue Mar 21 15:56:44 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown

The shutdown command stops the Derby server.

In the chapter, we have written about Derby tools.