Derby Tools and Utilities Guide

Apache Software FoundationDerby Tools and Utilities GuideApache Derby

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

Related information

License

The Apache License, Version 2.0


                               Apache License
                           Version 2.0, January 2004
                        http://www.apache.org/licenses/

   TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION

   1. Definitions.

      "License" shall mean the terms and conditions for use,
      reproduction, and distribution as defined by Sections 1 through
      9 of this document.

      "Licensor" shall mean the copyright owner or entity authorized
      by the copyright owner that is granting the License.

      "Legal Entity" shall mean the union of the acting entity and all
      other entities that control, are controlled by, or are under
      common control with that entity. For the purposes of this
      definition, "control" means (i) the power, direct or indirect,
      to cause the direction or management of such entity, whether by
      contract or otherwise, or (ii) ownership of fifty percent (50%)
      or more of the outstanding shares, or (iii) beneficial ownership
      of such entity.

      "You" (or "Your") shall mean an individual or Legal Entity
      exercising permissions granted by this License.

      "Source" form shall mean the preferred form for making
      modifications, including but not limited to software source code,
      documentation source, and configuration files.

      "Object" form shall mean any form resulting from mechanical
      transformation or translation of a Source form, including but
      not limited to compiled object code, generated documentation,
      and conversions to other media types.

      "Work" shall mean the work of authorship, whether in Source or
      Object form, made available under the License, as indicated by a
      copyright notice that is included in or attached to the work
      (an example is provided in the Appendix below).

      "Derivative Works" shall mean any work, whether in Source or
      Object form, that is based on (or derived from) the Work and
      for which the editorial revisions, annotations, elaborations,
      or other modifications represent, as a whole, an original work
      of authorship.  For the purposes of this License, Derivative
      Works shall not include works that remain separable from, or
      merely link (or bind by name) to the interfaces of, the Work
      and Derivative Works thereof.

      "Contribution" shall mean any work of authorship, including
      the original version of the Work and any modifications or
      additions to that Work or Derivative Works thereof, that is
      intentionally submitted to Licensor for inclusion in the Work
      by the copyright owner or by an individual or Legal Entity
      authorized to submit on behalf of the copyright owner. For the
      purposes of this definition,
      "submitted" means any form of electronic, verbal, or written
      communication sent to the Licensor or its representatives,
      including but not limited to communication on electronic mailing
      lists, source code control systems, and issue tracking systems
      that are managed by, or on behalf of, the Licensor for the
      purpose of discussing and improving the Work, but excluding
      communication that is conspicuously marked or otherwise
      designated in writing by the copyright owner as "Not a
      Contribution."

      "Contributor" shall mean Licensor and any individual or Legal
      Entity on behalf of whom a Contribution has been received by
      Licensor and subsequently incorporated within the Work.

   2. Grant of Copyright License. Subject to the terms and conditions
      of this License, each Contributor hereby grants to You a
      perpetual, worldwide, non-exclusive, no-charge, royalty-free,
      irrevocable copyright license to reproduce, prepare Derivative
      Works of, publicly display, publicly perform, sublicense, and
      distribute the Work and such Derivative Works in Source or
      Object form.

   3. Grant of Patent License. Subject to the terms and conditions of
      this License, each Contributor hereby grants to You a perpetual,
      worldwide, non-exclusive, no-charge, royalty-free, irrevocable
      (except as stated in this section) patent license to make, have
      made, use, offer to sell, sell, import, and otherwise transfer
      the Work, where such license applies only to those patent claims
      licensable by such Contributor that are necessarily infringed by
      their Contribution(s) alone or by combination of their
      Contribution(s) with the Work to which such Contribution(s) was
      submitted. If You institute patent litigation against any entity
      (including a cross-claim or counterclaim in a lawsuit) alleging
      that the Work or a Contribution incorporated within the Work
      constitutes direct or contributory patent infringement, then any
      patent licenses granted to You under this License for that Work
      shall terminate as of the date such litigation is filed.

   4. Redistribution. You may reproduce and distribute copies of the
      Work or Derivative Works thereof in any medium, with or without
      modifications, and in Source or Object form, provided that You
      meet the following conditions:

      (a) You must give any other recipients of the Work or
          Derivative Works a copy of this License; and

      (b) You must cause any modified files to carry prominent notices
          stating that You changed the files; and

      (c) You must retain, in the Source form of any Derivative Works
          that You distribute, all copyright, patent, trademark, and
          attribution notices from the Source form of the Work,
          excluding those notices that do not pertain to any part of
          the Derivative Works; and

      (d) If the Work includes a "NOTICE" text file as part of its
          distribution, then any Derivative Works that You distribute
          must include a readable copy of the attribution notices
          contained within such NOTICE file, excluding those notices
          that do not pertain to any part of the Derivative Works, in
          at least one of the following places: within a NOTICE text
          file distributed as part of the Derivative Works; within the
          Source form or documentation, if provided along with the
          Derivative Works; or, within a display generated by the
          Derivative Works, if and wherever such third-party notices
          normally appear. The contents of the NOTICE file are for
          informational purposes only and do not modify the License.
          You may add Your own attribution notices within Derivative
          Works that You distribute, alongside or as an addendum to
          the NOTICE text from the Work, provided that such additional
          attribution notices cannot be construed as modifying the
          License.

      You may add Your own copyright statement to Your modifications
      and may provide additional or different license terms and
      conditions for use, reproduction, or distribution of Your
      modifications, or for any such Derivative Works as a whole,
      provided Your use, reproduction, and distribution of the Work
      otherwise complies with the conditions stated in this License.

   5. Submission of Contributions. Unless You explicitly state
      otherwise, any Contribution intentionally submitted for
      inclusion in the Work by You to the Licensor shall be under the
      terms and conditions of this License, without any additional
      terms or conditions.  Notwithstanding the above, nothing herein
      shall supersede or modify the terms of any separate license
      agreement you may have executed with Licensor regarding such
      Contributions.

   6. Trademarks. This License does not grant permission to use the
      trade names, trademarks, service marks, or product names of the
      Licensor, except as required for reasonable and customary use
      in describing the origin of the Work and reproducing the content
      of the NOTICE file.

   7. Disclaimer of Warranty. Unless required by applicable law or
      agreed to in writing, Licensor provides the Work (and each
      Contributor provides its Contributions) on an "AS IS" BASIS,
      WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
      implied, including, without limitation, any warranties or
      conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or
      FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for
      determining the appropriateness of using or redistributing the
      Work and assume any risks associated with Your exercise of
      permissions under this License.

   8. Limitation of Liability. In no event and under no legal theory,
      whether in tort (including negligence), contract, or otherwise,
      unless required by applicable law (such as deliberate and
      grossly negligent acts) or agreed to in writing, shall any
      Contributor be liable to You for damages, including any direct,
      indirect, special, incidental, or consequential damages of any
      character arising as a result of this License or out of the use
      or inability to use the Work (including but not limited to
      damages for loss of goodwill, work stoppage, computer failure or
      malfunction, or any and all other commercial damages or losses),
      even if such Contributor has been advised of the possibility of
      such damages.

   9. Accepting Warranty or Additional Liability. While redistributing
      the Work or Derivative Works thereof, You may choose to offer,
      and charge a fee for, acceptance of support, warranty, indemnity,
      or other liability obligations and/or rights consistent with this
      License. However, in accepting such obligations, You may act only
      on Your own behalf and on Your sole responsibility, not on behalf
      of any other Contributor, and only if You agree to indemnify,
      defend, and hold each Contributor harmless for any liability
      incurred by, or claims asserted against, such Contributor by
      reason of your accepting any such warranty or additional
      liability.

   END OF TERMS AND CONDITIONS

   APPENDIX: How to apply the Apache License to your work.

      To apply the Apache License to your work, attach the following
      boilerplate notice, with the fields enclosed by brackets "[]"
      replaced with your own identifying information. (Don't include
      the brackets!)  The text should be enclosed in the appropriate
      comment syntax for the file format. We also recommend that a
      file or class name and description of purpose be included on the
      same "printed page" as the copyright notice for easier
      identification within third-party archives.

   Copyright [yyyy] [name of copyright owner]

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
   implied.  See the License for the specific language governing
   permissions and limitations under the License.

About this guide

For general information about the Derby documentation, such as a complete list of books, conventions, and further reading, see Getting Started with Derby.

Purpose of this document

This book describes how to use the Derby tools and utilities. The tools and utilities covered in this book include:

•		ij
•		the import and export utilities
•		the database class loading utilities
•		sysinfo
•		dblook

Audience

This book is for:

•		developers, who might use the tools when developing applications
•		system administrators, who might use the tools to run backup scripts or to import large amounts of data
•		end-users, who might use one of the tools to run ad-hoc queries against a database

How this guide is organized

This guide includes the following sections:

•		What are the Derby tools and utilities? Overview of the tools and utilities, and Derby and JDBC basics for new or infrequent users.
•		Using ij How to get started with ij, a JDBC and SQL scripting tool.
•		ij properties reference Reference for ij properties.
•		ij commands and errors reference Reference for ij commands and errors.
•		Using the bulk import and export procedures Reference and how-to instructions for using bulk import and export.
•		ij commands and errors reference Syntax for executing the built-in procedures for storing jar files in the database.
•		sysinfo Reference information on the utility that provides information about your Derby environment.
•		dblook dblook is Derby's Data Definition Language (DDL) Generation Utility, which is more informally called a schema dump tool. It is a simple utility that dumps the DDL of a user-specified database to either a console or to a file. The generated DDL can then be used to recreate all or parts of a database, view a subset of a database's objects (for example, those which pertain to specific tables or schemas), or document a database's schema.

What are the Derby tools and utilities?

The Derby tools and utilities are a set of routines supplied with Derby that are typically used to setup and update a Derby database.

For more complete information on developing a system using Derby, see the Derby Developer's Guide.

Overview

Derby is a database management system (DBMS), accessed by applications through the JDBC API.

Included with the product are some standalone Java tools and utilities that make it easier to use and develop applications for Derby.

These tools and utilities include:

•		ij ij is Derby's interactive JDBC scripting tool. It is a simple utility for running scripts against a Derby database. You can also use it interactively to run ad hoc queries. ij provides several commands for ease in accessing a variety of JDBC features. ij can be used in an embedded or a client/server environment.
•		The import and export utilities These server-side utilities allow you to import data directly from files into tables and to export data from tables into files. Server-side utilities can be in a client/server environment but require that all files referenced be on the Server machine.
•		Database class loading utilities These utilities allow you to store application logic in a database.
•		sysinfo sysinfo provides information about your version of Derby and your environment.
•		dblook dblook is Derby's Data Definition Language (DDL) Generation Utility, also called a schema dump tool. It is a simple utility for the dumping the DDL of a user-specified database to either a console or to a file. The generated DDL can then be used for such things as recreating all or parts of a database, viewing a subset of a database's objects (for example, those which pertain to specific tables and schemas), or documenting a database's schema.

Environment setup and the Derby tools

ij, sysinfo, and dblook are tools that can be used in an embedded or a client/server environment. The import and export utilities and database class loading utilities are database-side utilities, which means that they run in the same JVM as Derby (e.g. on the Server). This means when used in a client/server environment all files imported, exported, or loaded must be local to the Server machine.

Java 2 Platform, Standard Edition, Version 1.4

All Derby tools require the Java 2 Platform, Standard Edition, Version 1.4 or later.

Classpath

To simplify the process of setting up the CLASSPATH environment variable to run Derby and the tools, a new jar file, derbyrun.jar, has been added to the Derby distribution. Adding this jar file to your classpath has the effect of putting all the Derby jar files in your classpath.

For details on using the Derby jar files for deploying applications, see the sections on deploying Derby applications in the Derby Developer's Guide.

About Derby databases

A Derby database consists of platform-independent files stored in a directory that has the same name as the database.

JDBC connection basics

Most of the Derby tools are JDBC applications. A JDBC application is one that uses the classes in the java.sql package to interact with a DBMS.

When you work with JDBC applications, you need to know about several concepts. The most basic is the connection. A JDBC connection is the object through which commands are sent to the Derby engine and responses are returned to the program. Establishing a connection to a specific database is done by specifying a appropriate database URL. The following sections provide background information to help in understanding the Derby database connection URL.

JDBC drivers overview

Before a JDBC application connects to a database, it must cause the proper JDBC driver to be loaded in the Java session. Derby provides the following JDBC drivers for use with the Derby database engine:

•		org.apache.derby.jdbc.EmbeddedDriver For embedded environments, when Derby runs in the same JVM as the application. This is commonly referred to as the embedded drvier.
•		org.apache.derby.jdbc.ClientDriver For client/server environments that use the Derby Network Server. This is commonly referred to as the Network Client driver.

You can use ij to connect to any database that supplies a JDBC driver. For those databases, you would need to load the supplied JDBC driver.

Database connection URLs

A JDBC URL provides a way of identifying a database so that the appropriate driver recognizes it and connects to it. In the Derby documents, a JDBC URL is referred to as a database connection URL.

After the driver is loaded, an application must specify the correct database connection URL to connect to a specific database. The Derby database connection URL allows you to accomplish tasks other than simply connecting. For more information about the Derby database connection URLs, see the Derby Developer's Guide.

A JDBC URL always starts with jdbc:. After that, the format for the database connection URL depends on the JDBC driver.

Here is the format for the database connection URL for connecting to an existing Derby database using the embedded driver:

•

jdbc:derby:databaseName;URLAttributes

The format for the database connection URL for connecting to an existing Derby database using the Network Client is:

•

jdbc:derby://host:port/databaseName;URLAttributes

The italicized items stand for something the user fills in:

•		databaseName The name of the database you want to connect to. This might also include the file system path to the database.
•		URLAttributes One or more of the supported attributes of the database connection URL, such as upgrade=true, create=true or territory=ll_CC. For more information, see "Setting attributes for the database connection URL" in the Derby Reference Manual.
•		host The name of the machine where the server is running. It can be the name of the machine or the address.
•		port The port number used by the server framework

About Protocols

Officially, the portion of the database connection URL called the protocol is jdbc:, just as http:// is a protocol in Web URLs. However, the second portion of the database connection URL (everything between jdbc: and databaseName), which is called the subprotocol, is informally considered part of the protocol. Later in this book you might see references to protocol. Consider protocol to be everything that comes before databaseName.

For complete information about the database connection URL, see the Derby Developer's Guide.

Tools and localization

The Derby tools provide support for common localization features such as localized message files and GUI, locale-appropriate formatting of data, codesets, unicode identifiers and data, and database territories.

For general information about international Derby systems, see the Derby Developer's Guide.

About locales

In the Derby documentation, we refer to three locales:

•		Java System locale This is the locale of your machine, which is automatically detected by your JVM. For Derby and Derby tools, the Java system locale determines the default locale.
•		Database territory This is the territory associated with your database when it is created. By default, this is the same as the java system locale. The database territory determines the language of database errors.
•		ij or dblook Session locale This locale is associated with your ij or dblook session. This locale determines the localized display format for numbers, dates, times, and timestamps.

Database territory

To specify a database territory, use the territory attribute on the URL connection when creating the database.

Note: You cannot modify a database's territory after the database has been created.

For information about database territories, see the Internationalization appendix in the Derby Developer's Guide.

Specifying an alternate codeset

You can specify an alternate codeset for your tool session.

Use the derby.ui.codeset property when starting ij or dblook. This property can be useful when working with scripts created on a different system.

Formatting display of locale-sensitive data

To display dates, timestamps, numbers, and times in the format of the ij Session locale, use the LocalizedDisplay command.

Note: These options do not change how Derbystores locale-sensitive data, simply how the tool displays the data.

The following example demonstrates using localizedDisplay in an en_US locale:

ij> VALUES CURRENT_DATE;
1
----------
2001-09-06
1 row selected
ij> localizeddisplay on;
ij> VALUES CURRENT_DATE;
1
------------------
September 6, 2001
1 row selected

Using ij

ij is Derby's interactive JDBC scripting tool. It is a simple utility for running scripts or interactive queries against a Derby database.

ij is a Java application, which you start from a command window such as an MS-DOS Command Window or the UNIX shell. ij provides several non-SQL commands for ease in accessing a variety of JDBC features for testing.

Starting ij

Derby provides batch and shell scripts for users in Windows and UNIX environments that can be used to start ij. By calling the appropriate script you will start ij and be able to connect with a simple command. The scripts are found in the bin directory of your Derby installation. You can also customize the ij scripts to suit your environment.

If you are using Derby as a client/server environment, start the Network Server before connecting to the Derby database. (See "Starting the Network Server" in the Derby Server and Administration Guide for details.) You can start ij by running the ij scripts for your environment. Follow the instructions in "Setting up your environment" in Getting Started with Derby to set the DERBY_HOME and JAVA_HOME environment variables and to add DERBY_HOME/bin to your path. Then use the following command:

ij [-p propertyFile] [inputFile]

Alternatively, set the DERBY_HOME environment variable, then use one of these commands:

(UNIX) java [options] -jar $DERBY_HOME/lib/derbyrun.jar ij
    [-p propertyFile] [inputFile]

(Windows) java [options] -jar %DERBY_HOME%\lib\derbyrun.jar ij
    [-p propertyFile] [inputFile]

java [options] org.apache.derby.tools.ij 
    [-p propertyFile] [inputFile]

If you use the last form of the command, be sure that derbyrun.jar is in your classpath (for pre-10.2 distributions derbytools.jar and usually derby.jar were required in the classpath).

The command line items are:

•		java Start the JVM.
•		options The options that the JVM uses. You can use the -D option to set ij properties (see Starting ij using properties) or system properties, such as Derby properties.
•		propertyFile A file you can use to set ij properties (instead of the -D option). The property file should be in the format created by the java.tools.Properties.save methods, which is the same format as the derby.properties file.
•		inputFile A file from which to read commands. The ij tool exits at the end of the file or an exit command. Using an input file causes ij to print out the commands as it runs them. If you reroute standard input, ij does not print out the commands. If you do not supply an input file, ij reads from the standard input.

For detailed information about ij commands, see ij commands and errors reference.

Creating a database using ij

You can create a Derby from within the ij tool.

To create a database with the ij tool, type the following command:

ij> connect 'jdbc:derby:testdb;create=true';

This command creates a database called testdb in the current directory, populates the system tables, and connects to the database. You can then run any SQL statements from the ij command line.

Starting ij using properties

You set ij properties in any of the following ways:

1.		by using the -D option on the command line
2.		by specifying a properties file using the -p propertyfile option on the command line

Remember: ij property names are case-sensitive, while commands are case-insensitive.

The following examples illustrate how to use ij properties:

To start ij by using a properties file called ij.properties, use a command like the following (with the addition of the file paths):

java -jar derbyrun.jar -p ij.properties

To start ij with a maximumDisplayWidth of 1000:

java -Dij.maximumDisplayWidth=1000 -jar derbyrun.jar

To start ij with an ij.protocol of jdbc:derby: and an ij.database of sample, use the following command:

java -Dij.protocol=jdbc:derby: -Dij.database=sample derbyrun.jar

To start ij with two named connections, using the ij.connection.connectionName property, use a command like the following (all on one line):

java -Dij.connection.sample=jdbc:derby:sample 
-Dij.connection.History=jdbc:derby:History 
-Dderby.system.home=c:\derby\demo\databases
-jar c:\derby\lib\derbyrun.jar

To see a list of connection names and the URLs used to connect to them, use the following command. (If there is a connection that is currently active, it will show up with an * after its name.)

ij version 10.4
ij(HISTORY)> show connections;
HISTORY* -    jdbc:derby:History
SAMPLE -     jdbc:derby:sample
* = current connection
ij(HISTORY)>

Getting started with ij

This section discusses the use of the ij tool.

Connecting to a Derby database

To connect to a Derby database, you need to perform the following steps:

1.		Start the JVM
2.		Load the appropriate driver.
3.		Create a connection by providing a valid database connection URL.

When using ij interactively to connect to a Derby database connection information is generally supplied on the full database connection URL. ij automatically loads the appropriate driver based on the syntax of the URL. The following example shows how to connect in this manner by using the Connect command and the embedded driver:

D:>java org.apache.derby.tools.ij
ij version 10.4
ij> connect 'jdbc:derby:sample';
ij>

If the URL entered contains Network Client information the Connect command loads the Network Client driver:

D:>java org.apache.derby.tools.ij
ij version 10.4
ij> connect 'jdbc:derby://localhost:1527/sample';
ij>

Note: In these and subsequent examples the databases were created in the derby.system.home directory. For more information on the System Directory see the Derby Developer's Guide.

ij provides alternate methods of specifying part or all of a connection URL (e.g. the ij.protocol, ij.database, or ij.connection.connectionName properties). These properties are often used when a script is being used and the path to the database or the driver name is not known until runtime. The properties can also to used to shorten the amount of information that must be provided with the connection URL. The following are some examples of different ways to supply the connection information:

•

Supplying full connection information on the command line

Specifying one of the following properties along with a valid connection URL on the ij command line starts ij with the connection already active. This is often used when running a SQL script so the database name or path can be specified at runtime.

•		ij.database - opens a connection using the URL provided
•		ij.connection.connectionName - Used to open one or more connections. The property can appear multiple times on the command line with different connectionNames and the same or different URLs.

This example shows how to create the database myTours and run the script ToursDB_schema.sql by specifying the database URL using the ij.database property.

C:\>java -Dij.database=jdbc:derby:myTours;create=true 
     org.apache.derby.tools.ij 
    %DERBY_HOME%\demo\programs\toursdb\ToursDB_schema.sql
ij version 10.4
CONNECTION0* -  jdbc:derby:myTours
* = current connection
ij> -- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements.  See the NOTICE file distributed with
     ...output removed...
ij> CREATE TRIGGER TRIG2 AFTER DELETE ON FLIGHTS FOR EACH STATEMENT MODE DB2SQL
INSERT INTO FLIGHTS_HISTORY (STATUS) VALUES ('INSERTED FROM TRIG2');
0 rows inserted/updated/deleted
ij>

•

Defining a Protocol and using a "short form" URL

A default URL protocol and subprotocol can be specified by setting the property ij.protocol or using the ij Protocol command. This allows a connection to be made by specifying only the database name. This "short form" of the database connection URL defaults the protocol (For more information, see About Protocols).

This example uses the ij Protocol command and a "short form" connection URL:

D:>java org.apache.derby.tools.ij
ij version 10.4
ij> protocol 'jdbc:derby:';
ij> connect 'sample';
ij>

•

Specifying an alternate Driver

If you are using the drivers supplied by Derby, you can specify the driver names listed in JDBC drivers overview. However, the Derby drivers are implicitly loaded when a supported protocol is used so specifying them is probably redundant. Specifying a driver is required when ij is used with other JDBC drivers to connect to non-Derby databases. To use drivers supplied by other vendors explicitly specify the driver one of three ways

•		with an ij property ij.Driver
•		using the JVM system property jdbc.drivers
•		using the ij Driver command

This example specifies the driver using the ij Driver command

D:>java org.apache.derby.tools.ij
ij version 10.4
ij> driver 'sun.jdbc.odbc.JdbcOdbcDriver';
ij> connect 'jdbc:odbc:myOdbcDataSource';
ij>

The ij Driver name and connection URL

Specifying the Driver Name and database connection URL, summarizes the different ways to specify the driver name and database connection URL.

Table 1. Specifying the Driver Name and database connection URL

Action	System Property	ij Property	ij Command
loading the driver implicitly	'	ij.connection.connectionName (plus full URL) ij.database (plus full URL) ij.protocolij.protocol.protocolName (plus protocol clause in Connect command)	ProtocolConnect (plus full URL)
loading the driver explicitly	jdbc.drivers	-Dij.Driver	Driver
specifying the database connection URL	'	ij.connection.connectionNameij.database	Connect

Using ij commands

The primary purpose of ij is to allow the execution of Derby SQL statements interactively or via scripts. Since SQL statements can be quite long ij uses the semicolon to mark the end of statement or command. All statements must be terminated with a semicolon. ij uses properties, listed later in this guide, to simplify its use. ij also recognizes specialized commands that extends its functionality such as the ability to create and test cursors and prepared statements, transaction control and more. For complete information about ij commands, see ij commands and errors reference later in the Tools Guide.

Other uses for ij

ij is a JDBC-neutral scripting tool with a small command set. It can be used to access any JDBC driver and database accessible through that driver.

The main benefit of a tool such as ij is that it is easy to run scripts for creating a database schema and automating other repetitive database tasks.

In addition, ij accepts and processes SQL commands interactively for ad hoc database access.

Running ij scripts

You can run scripts in ij in any of the following ways:

•		Name an input file as a command-line argument. For example: java org.apache.derby.tools.ij <myscript.sql>
•		Redirect standard input to come from a file. For example: java org.apache.derby.tools.ij < <myscript.sql>
•		Use the Run command from the ij command line. For example: ij> run 'myscript.sql';

Note: If you name an input file as a command-line argument or if you use the Run command, ij echoes input from a file. If you redirect standard input to come from a file, ij does not echo commands.

You can save output in any of the following ways:

•		By redirecting output to a file: java org.apache.derby.tools.ij <myscript.sql> > <myoutput.txt>
•		By setting the ij.outfile property: java -Dij.outfile=<myoutput.txt> org.apache.derby.tools.ij <myscript.sql>

ij exits when you enter the Exit command or, if executing a script, when the end of the command file is reached. When you use the Exit command, ij automatically shuts down an embedded Derby system by issuing a connect jdbc:derby:;shutdown=true request. It does not shut down Derby if it is running in a server framework.

ij properties reference

When starting upij, you can specify properties on the command line or in a properties file, as described in Starting ij using properties.

ij.connection.connectionName property

Function

Creates a named connection to the given database connection URL when ij starts up; it is equivalent to the Connect AS Identifier command. The database connection URL can be of the short form if an ij.protocol is specified. This property can be specified more than once per session, creating multiple connections. When ij starts, it displays the names of all the connections created in this way. It also displays the name of the current connection, if there is more than one, in the ij prompt.

Syntax

ij.connection.connectionName=databaseConnectionURL

When specified on the command line the databaseConnectionURL should not be enclosed in single quotations, however, if the database path contains special characters (e.g. a space) it must be enclosed in double quotes.

Example

This example connects to the existing database sample and creates then connects to the database anohterDB.

D:> java -Dij.connection.sample1=jdbc:derby:sample
-Dij.connection.anotherConn=jdbc:derby:anotherDB;create=true
    org.apache.derby.tools.ij
ij version 10.4
ANOTHERCONN* -  jdbc:derby:anotherDB;create=true
SAMPLE1 -        jdbc:derby:sample
* = current connection
ij(ANOTHERCONN)>