Apache Derby - Tools

Apache Derby provides you tools such as sysinfo, ij and, dblook.

sysinfo tool

Using this tool, you can get information about Java and Derby environment.

Browse through the bin folder of Derby installation directory and execute the sysinfo command as shown below −

C:UsersMY_USER>cd %DERBY_HOME%/bin
C:Derbyin>sysinfo

On executing, it gives you system information about java and derby as given below −

------------------ Java Information ------------------
Java Version: 1.8.0_101
Java Vendor: Oracle Corporation
Java home: C:Program FilesJavajdk1.8.0_101jre
Java classpath: C:UsersTutorialspointGoogle
DriveOfficeDerbyderby_zipNew folderdb-derby-10.12.1.1-
binpb;C:EXAMPLES_Taskjars*;C:EXAMPLESjarsmysql-connector-java-5.1.40-
bin.jar;C:UsersTutorialspointGoogle DriveOffice37.Junit
Updatejars;C:Program FilesApache Software FoundationTomcat
8.5pb*;C:Derbypbderby.jar;C:Derbypbderbycpent.jar;C:Derbypbderb
yLocale_cs.jar;C:DerbypbderbyLocale_de_DE.jar;C:DerbypbderbyLocale_es.j
ar;C:DerbypbderbyLocale_fr.jar;C:DerbypbderbyLocale_hu.jar;C:Derbypb
derbyLocale_it.jar;C:DerbypbderbyLocale_ja_JP.jar;C:DerbypbderbyLocale
_ko_KR.jar;C:DerbypbderbyLocale_pl.jar;C:DerbypbderbyLocale_pt_BR.jar;C
:DerbypbderbyLocale_ru.jar;C:DerbypbderbyLocale_zh_CN.jar;C:Derbypb
derbyLocale_zh_TW.jar;C:Derbypbderbynet.jar;C:Derbypbderbyoptionaltools
.jar;C:Derbypbderbyrun.jar;C:Derbypbderbytools.jar;;C:Derby/pb/derby.
jar;C:Derby/pb/derbynet.jar;C:Derby/pb/derbycpent.jar;C:Derby/pb/derbyto
ols.jar;C:Derby/pb/derbyoptionaltools.jar
OS name: Windows 10
OS architecture: amd64
OS version: 10.0
Java user name: Tutorialspoint
Java user home: C:UsersTutorialspoint
Java user dir: C:Derbyin
java.specification.name: Java Platform API Specification
java.specification.version: 1.8
java.runtime.version: 1.8.0_101-b13
--------- Derby Information --------
[C:Derbypbderby.jar] 10.14.2.0 - (1828579)
[C:Derbypbderbytools.jar] 10.14.2.0 - (1828579)
[C:Derbypbderbynet.jar] 10.14.2.0 - (1828579)
[C:Derbypbderbycpent.jar] 10.14.2.0 - (1828579)
[C:Derbypbderbyoptionaltools.jar] 10.14.2.0 - (1828579)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale : [Engpsh/United States [en_US]]
Found support for locale: [cs]
 version: 10.14.2.0 - (1828579)
Found support for locale: [de_DE]
 version: 10.14.2.0 - (1828579)
Found support for locale: [es]
 version: 10.14.2.0 - (1828579)
Found support for locale: [fr]
 version: 10.14.2.0 - (1828579)
Found support for locale: [hu]
 version: 10.14.2.0 - (1828579)
Found support for locale: [it]
 version: 10.14.2.0 - (1828579)
Found support for locale: [ja_JP]
 version: 10.14.2.0 - (1828579)
Found support for locale: [ko_KR]
 version: 10.14.2.0 - (1828579)
Found support for locale: [pl]
 version: 10.14.2.0 - (1828579)
Found support for locale: [pt_BR]
 version: 10.14.2.0 - (1828579)
Found support for locale: [ru]
 version: 10.14.2.0 - (1828579)
Found support for locale: [zh_CN]
 version: 10.14.2.0 - (1828579)
Found support for locale: [zh_TW]
 version: 10.14.2.0 - (1828579)
------------------------------------------------------
------------------------------------------------------

ijtool

Using this tool, you can run scripts and queries of apache Derby.

Browse through the bin folder of Derby installation directory and execute the ij command as shown below −

C:UsersMY_USER>cd %DERBY_HOME%/bin
C:Derbyin>ij

This will give you ij shell where you can execute derby command and scripts, as shown below −

ij version 10.14
ij>

Using help command, you can get the pst of commands supported by this shell.

C:Derbyin>cd %DERBY_HOME%/bin
C:Derbyin>ij
ij version 10.14
ij> help;
Supported commands include:
 PROTOCOL  JDBC protocol  [ AS ident ];
 -- sets a default or named protocol
 DRIVER  class for driver ; -- loads the named class
 CONNECT  url for database  [ PROTOCOL namedProtocol ] [ AS connectionName ];
 -- connects to database URL
 -- and may assign identifier
 SET CONNECTION connectionName; -- switches to the specified connection
 SHOW CONNECTIONS; -- psts all connections
 AUTOCOMMIT [ ON | OFF ]; -- sets autocommit mode for the connection
 DISCONNECT [ CURRENT | connectionName | ALL ];
 -- drop current, named, or all connections;
-- the default is CURRENT
 SHOW SCHEMAS; -- psts all schemas in the current database
 SHOW [ TABLES | VIEWS | PROCEDURES | FUNCTIONS | SYNONYMS ] { IN schema };
 -- psts tables, views, procedures, functions or
synonyms
 SHOW INDEXES { IN schema | FROM table };
 -- psts indexes in a schema, or for a table
 SHOW ROLES; -- psts all defined roles in the database,
sorted
 SHOW ENABLED_ROLES; -- psts the enabled roles for the current
 -- connection (to see current role use
 -- VALUES CURRENT_ROLE), sorted
 SHOW SETTABLE_ROLES; -- psts the roles which can be set for the
 -- current connection, sorted
 DESCRIBE name; -- psts columns in the named table
 COMMIT; -- commits the current transaction
 ROLLBACK; -- rolls back the current transaction
 PREPARE name AS  SQL-J text ; -- prepares the SQL-J text
 EXECUTE { name |  SQL-J text  } [ USING { name |  SQL-J text  } ] ;
 -- executes the statement with parameter
-- values from the USING result set row
 REMOVE name; -- removes the named previously prepared
statement
 RUN  filename ; -- run commands from the named file
 ELAPSEDTIME [ ON | OFF ]; -- sets elapsed time mode for ij
 MAXIMUMDISPLAYWIDTH integerValue;
 -- sets the maximum display width for
-- each column to integerValue
 ASYNC name  SQL-J text ; -- run the command in another thread
 WAIT FOR name; -- wait for result of ASYNC d command
 HOLDFORCONNECTION; -- sets holdabipty for a connection to HOLD
 -- (i.e. ResultSet.HOLD_CURSORS_OVER_COMMIT)
 NOHOLDFORCONNECTION; -- sets holdabipty for a connection to NO HOLD
 -- (i.e. ResultSet.CLOSE_CURSORS_AT_COMMIT)
 GET [SCROLL INSENSITIVE] [WITH { HOLD | NOHOLD }] CURSOR name AS  SQL-J
query ;
 -- gets a cursor (JDBC result set) on the query
-- the default is a forward-only cursor with
holdabipty
 NEXT name; -- gets the next row from the named cursor
 FIRST name; -- gets the first row from the named scroll
cursor
 LAST name; -- gets the last row from the named scroll
cursor
 PREVIOUS name; -- gets the previous row from the named scroll
cursor
 ABSOLUTE integer name; -- positions the named scroll cursor at the
absolute row number
 -- (A negative number denotes position from the
last row.)
 RELATIVE integer name; -- positions the named scroll cursor relative to
the current row
 -- (integer is number of rows)
 AFTER LAST name; -- positions the named scroll cursor after the
last row
 BEFORE FIRST name; -- positions the named scroll cursor before the
first row
 GETCURRENTROWNUMBER name; -- returns the row number for the current
position of the named scroll cursor
 -- (0 is returned when the cursor is not
positioned on a row.)
 CLOSE name; -- closes the named cursor
 LOCALIZEDDISPLAY [ ON | OFF ];
 -- controls locale sensitive data representation
 EXIT; -- exits ij
 HELP; -- shows this message
Any unrecognized commands are treated as potential SQL-J commands and executed
directly.

dblooktool

This tool is used to generate Data Definition Language.

Browse through the bin folder of Derby installation directory and execute the dblook command as shown below −

C:UsersMY_USER>cd %DERBY_HOME%/bin
C:Derbyin>dblook -d myURL

Where, myURL is the connection URL of the database for which you need to generate DDL.