Getting Started with Derby

Apache Software FoundationGetting Started with DerbyApache 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.

Introduction to Derby

Welcome to Derby! Derby is a full-featured, open source relational database management system (RDBMS) that is based on Java(TM) and SQL.

Derby is written and implemented completely in the Java programming language. Derby provides users with a small-footprint standards-based database engine that can be tightly embedded into any Java based solution. Derby ensures data integrity and provides sophisticated transaction support. In the default configuration there is no separate database server to be installed or maintained by the end user. For more information on Derby, visit the Derby Web site at http://db.apache.org/derby.

Before you install Derby, you should understand the deployment options and system requirements.

Deployment options

The Derby software distribution provides two basic deployment options (also referred to as frameworks), the simple embedded option and the Derby Network Server option.

Embedded

Refers to Derby being started by a simple single-user Java application. With this option Derby runs in the same Java virtual machine (JVM) as the application. Derby can be almost invisible to the end user because it is started and stopped by the application and often requires no administration.

The Derby documentation often refers to this as the embedded configuration or embedded mode.

Server (or Server-based)

Refers to Derby being started by an application that provides multi-user connectivity to Derby databases across a network. With this option Derby runs in the Java virtual machine (JVM) that hosts the Server. Applications connect to the Server from different JVMs to access the database. The Derby Network Server is part of the Derby software distribution and provides this type of framework for Derby. Derby also works well with other, independently developed Server applications.

The Derby documentation often refers to this as the Network Server configuration or client/server configuration.

System requirements

Derby is a database engine written completely in Java. The database will run in any certified Java Virtual Machine (JVM).

You must have a JAVA version 1.4 or higher installed on your computer. The Java Development Kit (JDK) is required to perform the activities in the Self-study tutorial for users new to Derby.

To check that the correct version of Java is installed, issue the java -version command.

Product documentation for Derby

The Derby product documentation consists of the Derby manuals and the API reference.

Getting Started with Derby

Describes how to install and configure Derby. Includes a self-study tutorial for users new to Derby and a quick-start guide for experienced JDBC users. This guide introduces the dblook, ij, and sysinfo tools, and the libraries and scripts that are included with Derby.

Derby Developer's Guide

Describes the functionality and features of Derby common to all deployments, such as Derby's JDBC and SQL specifics, deploying Derby applications, security, and other advanced features.

Derby Reference Manual

Documents the implementation of the SQL language in Derby. This guide provides reference information about the JDBC and JTA implementations, keywords, system tables, properties, and SQLExceptions in Derby.

Tuning Derby

Explains how to configure and tune Derby through properties and provides reference information on properties. This guide also offers performance tips, an in-depth discussion of performance, and information about the Derby optimizer.

Derby Tools and Utilities Guide

Describes how to use the Derby tools such as dblook, ij, andsysinfo, and how to use the system procedures to import and export data, and how to store Java code in the database.

Derby Server and Administration Guide

Part One of this guide discusses configuring servers, how to program client applications, and database administration. In addition, some systems might require administrative tasks such as backing up databases. These tasks are independent of any server framework but are unique to multi-user or large systems.

Part Two of this guide discusses administrative issues such as backups and debugging deadlocks.

Derby API Reference

An API Reference that is automatically generated for all public Derby classes. No reference is provided for the JDBC API, which is part of the Java 2 Platform, Standard Edition. For more information about the classes in the API, see the Derby Reference Manual.

You can access the Version 10.3 manuals and API Reference from the Apache Derby: Documentation page on the Apache Derby Web site. The product documentation is also installed with Derby. The manuals are installed in the docs subdirectory and the API Reference is installed in the javadoc subdirectory.

Installing and configuring Derby

This section will help you install and configure Derby.

After you complete the installation:

•		Access the Self-study tutorial to get up and running with Derby. This tutorial demonstrates how to use Derby in the embedded and client/server configurations.
•		If you are an experienced JDBC programmer, you should also see the Quick start guide for experienced JDBC users.

Installing Derby

To install Derby you must download the distribution and extract the package.

There are several distributions of Derby, including binary and source distributions for the latest official release and for previous official releases.

The distributions are:

•		The bin distribution contains scripts, demonstration programs, documentation, and the optimized .jar files. These .ar files are the same set of .ar files that are in the lib distribution.
•		The lib distribution contains an optimized, small footprint set of the Derby .jar files for deployment.
•		The lib-debug distribution contains a larger footprint set of the Derby .jar files that are useful for debugging or reporting issues.
•		The src distribution contains the source files that are used to create the bin, lib, and lib-debug distributions.

> Important: The remainder of the instructions in this guide assume that you have downloaded the bin distribution.

To install Derby:

Navigate your Web browser to http://db.apache.org/derby/derby_downloads.html. The downloads page provides links to the distributions of Derby This page also contains a link to http://db.apache.org/derby/dev/derby_source.html, where you can find information on how to use the Subversion version control system to access the Derby source code for a specific branch or for the development trunk.

Download the ZIP or TAR archive for the distribution that you want to use.

Extract the downloaded package. The extracted installation contains several subdirectories:

•		The demo subdirectory contains the demonstration programs.
•		The bin subdirectory contains the scripts for executing utilities and setting up the environment.
•		The javadoc subdirectory contains the api documentation that was generated from source code comments.
•		The docs subdirectory contains the Derby documentation.
•		The lib subdirectory contains the Derby .jar files.
•		The test subdirectory contains regression tests for Derby.
•		The frameworks subdirectory contains older scripts for executing utilities and setting up the environment. These are provided in this release for backward compatibility. These scripts are deprecated in favor of the scripts in the bin directory, and will be removed in a future release.

Setting up your environment

Derby includes several scripts that run the Derby tools and utilities. How you run those scripts depends on the configuration that you want to use to access the Derby database and tools.

To set up your environment:

1.		Choose the method that you want to use to run the Derby tools and utilities.
2.		Set the environment variables.

Choosing a method to run the Derby tools and startup utilities

There are several ways that you can run the Derby tools and startup utilities.

The method that you choose to run the tools and utilities determines the environment variables that you set for Derby.

Choose the method that you want to use:

Method

When to Use

Requirements

Run the tools using the command shell scripts that are provided with Derby.

Use this method when you want to run the tools with the least amount of typing and have installed the full bin distribution of Derby.

•		Set the DERBY_HOME environment variable
•		Include the java.exe file in the PATH environment variable
•		Include the DERBY_HOME\bin directory in the PATH environment variable

Run the tools using the derbyrun.jar archive file.

Use this method when:

•		You have only the Derby jar files available (see Libraries provided by Derby), and do not have the full bin distribution of Derby
•		You do not want to use the scripts that are provided with Derby

•		Set the DERBY_HOME environment variable
•		Include the java.exe file in the PATH environment variable

The derbyrun.jar file must be in the same folder as the other Derby .jar files.

For more information see the syntax for the derbyrun.jar file.

Run the tools using the complete java syntax or use Derby in a Java program.

Use this method when:

•		You do not have the derbyrun.jar file or the Derby scripts installed.
•		You want to learn the full syntax of each command and understand the details of setting up the execution environment.
•		You are developing application programs with Derby

•		Set the DERBY_HOME environment variable
•		Include the java.exefile in the PATH environment variable
•		You must know the full package name for the Java class that supports the tool
•		The CLASSPATH environment variable must be set to include the required JAR files.

For details on setting the CLASSPATH, see Manually setting the CLASSPATH environment variable.

Based on the requirements of the method that you chose to run the tools, follow the instructions to set the environment variables.

Setting the environment variables

There are several environment variables that must be set depending on the method that you selected to run the Derby tools and startup utilities.

As mentioned in choosing a method to run the Derby tools and startup utilities, you must set the DERBY_HOME environment variable so that you can use the command examples that are presented in this manual. Adding the Derby scripts directory to your command execution PATH makes the scripts easier to use and enables you to use the script examples in this manual. The CLASSPATH environment variable must be set if you are using Derby in a Java program or executing the tools using the java command.

The steps below show you how to set the environment variables in a command window. The settings are only valid for that window. If you close the command window or open a new command window, you must set the environment variables again.

Tip: You can also set environment variables permanently. For example, on Windows you can use the Control Panel to permanently set the environment variables.

To set the environment variables:

Set the DERBY_HOME environment variable to the location where you extracted the Derby bin distribution.

For example, if you installed Derby in the /opt/Derby_10 directory on UNIX or in the c:\Derby_10 directory on Windows, use the following command to set the DERBY_HOME environment variable:

Operating System	Command
UNIX	export DERBY_HOME=/opt/Derby_10
Windows	set DERBY_HOME=c:\Derby_10.

Be certain that the java.exe file, version 1.4.2 or, higher is in your command execution PATH. Open a command window and run the java -version command.

Add the DERBY_HOME/bin directory to the PATH environment variable so that you can run the Derby scripts from any directory.

Operating System	Command
UNIX	export PATH="$DERBY_HOME/bin:$PATH"
Windows	set PATH=%DERBY_HOME%\bin;%PATH%. If you use the Control Panel to update your system PATH, add %DERBY_HOME%\bin to the end of the PATH environment variable

Tip: When the DERBY_HOME environment variable is set and the underlying /bin directory is included in the PATH environment variable, you can use shortened commands to start the Derby tools. Otherwise, either you must be in the directory where the script that starts the Derby tool is located, or you must specify the full path to the location of the script when you want to start the tool.

For more information on the scripts included in the bin distribution, see Scripts included with Derby.

Syntax for the derbyrun.jar file

The derbyrun.jar file is a special JAR file that simplifies how you invoke the Derby tools and the Network Server.

With the derbyrun.jar file, you can run the Derby tools and utilities using shortened names and you do not need to set the java CLASSAPATH environment variable. The derbyrun.jar file must be in the same folder as the other Derby JAR files.

Syntax

The syntax for using derbyrun.jar for each of the Derby tools is as follows:

Operating System

Command

UNIX (Korn Shell)

java -jar $DERBY_HOME/lib/derbyrun.jar ij [-p propertiesfile] [sql_script] 
java -jar $DERBY_HOME/lib/derbyrun.jar sysinfo [-cp ...] [-cp help]

java -jar $DERBY_HOME/lib/derbyrun.jar dblook [arg]* (or no arguments for usage) 
java -jar $DERBY_HOME/lib/derbyrun.jar server [arg]* (or no arguments for usage)

Windows

java -jar %DERBY_HOME%\lib\derbyrun.jar ij [-p propertiesfile] [sql_script] 
java -jar %DERBY_HOME%\lib\derbyrun.jar sysinfo [-cp ...] [-cp help] 
java -jar %DERBY_HOME%\lib\derbyrun.jar dblook [arg]* (or no arguments for usage) 
java -jar %DERBY_HOME%\lib\derbyrun.jar server [arg]* (or no arguments for usage)

To see this syntax reminder, run the command java -jar derbyrun.jar with no arguments.

Additional information

The server argument is a shortcut for running the NetworkServerControl tool. For details on using this tool, see the Derby Server and Administration Guide.

For more information on using derbyrun.jar to run the ij, sysinfo, and dblook tools, see the Derby Tools and Utilities Guide.

Manually setting the CLASSPATH environment variable

You can set the CLASSPATH environment variable in the operating system either temporarily, permanently, or at run time when you start your Java application and the JVM.

The classpath is a list of the class libraries that are needed by the JVM and other Java applications to run your program. There are scripts that are included with Derby that can set up the classpath to run the Derby tools.

If you want to call the Derby tools directly using Java and not using the scripts, you must manually set the CLASSPATH environment variable.

In most development environments, it is best to temporarily set the CLASSPATH environment variable in the command line shell where you are entering commands.

Derby provides several scripts in the DERBY_HOME/bin directory to help you set your classpath quickly. These scripts are:

setEmbeddedCP

Use the setEmbeddedCP script to set the classpath when the database engine is used in embedded mode. This script adds the derby.jar and derbytools.jar files to the classpath.

setNetworkServerCP

Use the setNetworkServerCP script to set the classpath when you want to start the network server. This script adds the derbynet.jar file to the classpath.

setNetworkClientCP

Use the setNetworkClientCP script to set the classpath when you want to access databases using the network client. This script adds the derbyclient.jar and derbytools.jar files to the classpath.

To set the classpath temporarily, run the script that is appropriate for your environment every time that you open a new command window.

To see the classpath that the script sets, issue the following command after you run the script:

•		On UNIX, use echo $CLASSPATH
•		On Windows, use echo %CLASSPATH%

For more information on running the ij and sysinfo utilities, see the Derby Tools and Utilities Guide

Using the Derby tools and startup utilities

There are several tools and utilities that you might want to use as you begin to work with Derby.

The tools that are included with Derby are dblook, ij, and sysinfo. To run these tools see:

•		Running sysinfo
•		Running ij
•		Running dblook

Derby includes a set of scripts that you can use to start the Derby tools. The scripts are located in the DERBY_HOME/bin directory. When you run these scripts, your CLASSPATH environment variable is set if you have not already set it, and remains set as long as you are running the tool.

Most of the examples in this guide that show how to use the Derby scripts to launch the Derby tools assume that you are using the embedded mode of the Derby database engine. Use the instructions below to run the scripts with the Network Server.

Running the scripts with the Network Server

To run the scripts with the Network Server, use the following commands:

•		For the dblook tool, call the script and specify the -d option and the full URL to the Network Server database. For example: dblook -d 'jdbc:derby://localhost/myDB;user=usr'
•		For the ij tool, issue the command set DERBY_OPTS=-Dij.protocol=jdbc:derby://localhost/ and then start ij by issuing the command ij.
•		For the sysinfo tool, issue the command NetworkServerControl sysinfo

Additional Derby utilities

In addition, there are Derby utilities that are system procedures that you can call by using the ij tool. For example, there are system procedures that you can use to import and export external files. Instructions on how to use these system procedures are included in the Derby Tools and Utilities Guide and the Derby Reference Manual.

Running sysinfo

The Derby sysinfo tool displays information about your Java environment and your version of Derby.

The sysinfo utility prints system information to a console.

Choose the method that you can use to run the sysinfo script:

Method	When to Use	Command
Run sysinfo as a standalone command.	Use this method if you are relatively new to the Java programming language and new to Derby.	You must set your environment variables before you can run the sysinfo utility using this method. To run the sysinfo script from the command line us: sysinfo The sysinfo script sets the appropriate environment variables, including the CLASSPATH, and runs the sysinfo utility.
Run sysinfo using the jar file that is located in the directory where sysinfo resides.	Use this method if you are new to Derby, but are familiar with the Java programming language.	You must set the DERBY_HOME environment variable before you can run the sysinfo utility using this method. On UNIX, the command is: java [options] -jar $DERBY_HOME/lib/derbyrun.jar sysinfo On Windows, the command is: java [options] -jar %DERBY_HOME%\lib\derbyrun.jar sysinfo
Run sysinfo using the java command.	Use this method if you are familiar with both the Java programming language and Derby, and you have already set the java.exe file in your command execution PATH.	You must set your CLASSPATH. Use the steps specified in Manually setting the CLASSPATH environment variable. Then specify the class name in the java command. For example: java org.apache.derby.tools.sysinfo

See "sysinfo" in the Derby Tools and Utilities Guide for more information about using the sysinfo utility.

Running ij

The Derby ij tool is a JDBC tool that you can use to run scripts or interactive queries against a Derby database.

•

Choose the method that you can use to run the ij script:

Method	When to Use	Command
Run ij as a standalone command.	Use this method if you are relatively new to the Java programming language and new to Derby.	You must set your environment variables before you can run the ij tool using this method. To run the ij script from the command line use: ij You must add the DERBY_HOME/bin directory in your PATH environment variable before you can run the ij tool. The ij script sets up the environment variables like CLASSPATH and starts the ij tool.
Run ij using the jar file that is located in the directory where ij resides.	Use this method if you are new to Derby, but are familiar with the Java programming language.	You must set the DERBY_HOME environment variable before you can run the ij tool using this method. On UNIX, the command is: java -jar $DERBY_HOME/lib/derbyrun.jar ij On Windows, the command is: java -jar %DERBY_HOME%\lib\derbyrun.jar ij
Run ij using the java command.	Use this method if you are familiar with both the Java programming language and Derby, and you have already set the java.exe file in your command execution PATH.	You must set your CLASSPATH. Use the steps specified in Manually setting the CLASSPATH environment variable. Then specify the class name in the java command. For example: java org.apache.derby.tools.ij

•

When you are ready to leave the ij tool, type:

ij> exit;

See the Derby Tools and Utilities Guide for more information about the ij tool.

Running dblook

The Derby dblook utility is a Data Definition Language (DDL) generation utility.

The dblook utility is a simple utility that dumps all or parts of the DDL of a user-specified database to either a console or a file. The generated DDL can then be used for such things as recreating all or parts of a database, viewing a subset of the objects in a database (for example, those objects that pertain to specific tables and schemas), or documenting the schema of a database.

Choose the method that you can use to run the dblook script:

Method	When to Use	Command
Run dblook as a standalone command.	Use this method if you are relatively new to the Java programming language and new to Derby.	You must set your environment variables before you can run the dblook utility using this method. To run the dblook script from the command line use: dblook -d databaseURL [options] The dblook script sets the appropriate environment variables, including the CLASSPATH, and runs the dblook utility.
Run dblook using the jar file that is located in the directory where dblook resides.	Use this method if you are new to Derby, but are familiar with the Java programming language.	You must set the DERBY_HOME environment variable before you can run the dblook utility using this method. On UNIX, the command is: java [options] -jar $DERBY_HOME/lib/derbyrun.jar dblook -d databaseURL [options] On Windows, the command is: java [options] -jar %DERBY_HOME%\lib\derbyrun.jar dblook -d databaseURL [options]
Run dblook using the java command.	Use this method if you are familiar with both the Java programming language and Derby, and you have already set the java.exe file in your command execution PATH.	You must set your CLASSPATH. Use the steps specified in Manually setting the CLASSPATH environment variable. Then specify the class name in the java command. For example: java org.apache.derby.tools.dblook -d databaseURL [options]

See "Using dblook" in the Derby Tools and Utilities Guide for more information about using the dblook utility.

Verifying the Derby system configuration

You should confirm that your computer has the correct software installed and that the environment variables are set before you start working with Derby.

To check the Derby system configuration:

Verify that the java.exe file, version 1.4.2 or, higher is in your command execution PATH by opening a command window and running the java -version command.

The output from the command looks something like this:

java version "1.4.2_04"  
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_04-b05)  
Java HotSpot(TM) Client VM (build 1.4.2_04-b05, mixed mode)

The output you see might be different from what is shown here because the java -version command outputs vendor-specific information. If the command produces an error or the version listed is not 1.4 or higher, install a JDK before continuing.

Verify that the DERBY_HOME environment variable is set and points to the root directory of the Derby 10.3 installation. Open a command window and run the appropriate command for your system.

If you installed Derby in the /opt/Derby_10 directory on UNIX or the c:\Derby_10 directory on Windows, the command that you use and the expected output are shown in the following table:

Operating System	Command	Output
UNIX (Korn Shell)	echo $DERBY_HOME	/opt/Derby_10
Windows	echo %DERBY_HOME%	C:\Derby_10

If you receive an error or do not see the expected output, ensure that you have set the DERBY_HOME environment variable, as described in Setting the environment variables.

Note: You can also use the sysinfo utility to verify that the environment variables are set correctly. If the environment variables are set correctly, the sysinfo command displays information about your Java virtual machine (JVM) and the version of Derby that you have installed.

Self-study tutorial for users new to Derby

To help you get up and running with Derby as quickly as possible, this self-study guide highlights some of the more important features of Derby.

This tutorial uses a series of activities designed to demonstrate the use of Derby in the embedded and client/server configurations. After performing these activities, you will find Derby to be an easy-to-use and fully functional relational database management system (RDBMS).

Tutorial skills and software requirements

To perform the tutorial activities, you do not need to have any prior knowledge of Java software, the Java Database Connectivity (JDBC) API, or SQL. Each activity provides the complete command syntax that you need to use for each operation. Instructions include the syntax for both a UNIX Korn shell and a Windows command prompt.

This tutorial demonstrates, but does not teach, the Java, JDBC and SQL code that is presented. If you want a deeper understanding of these topics, you will need to consult additional reference materials.

To perform the tutorial activities, you must have:

•

A Java Development Kit (JDK) version 1.4 or higher installed on your computer

•

The binary (bin) installation of Apache Derby version 10.3 installed on your computer

•

A basic knowledge of the computer command line interface:

•		How to start a command shell or window
•		How to navigate the file system hierarchy

System configuration

You should verify the system configuration before you perform the tutorial activities.

Problems and feedback on the tutorial activities

If you experience problems with any aspect of the tutorial activities, send an e-mail message to derby-user@db.apache.org. Someone from the Derby community will respond to your message.

The questions and feedback received will be used to make this tutorial more useful.

Tutorial overview

Each activity in this self-study tutorial highlights an important feature of Derby.

Here is what you can expect to learn in each activity:

Activity 1:

Use the Derby ij tool to load the Derby embedded driver and start the Derby database engine. Create the database firstdb and the table FIRSTTABLE. Use a few basic SQL statements to insert and select data. The Derby message log derby.log and the database directories and files are introduced.

Activity 2:

Use Derby within a client/server configuration. Start the Derby Network Server, which will embed the Derby engine. In a separate process, use the Derby ij tool to load the Derby client driver and connect to the Network Server. Create a database called seconddb and the table SECONDTABLE. Use a few basic SQL statements to insert and select data.

Activity 3:

Load the Derby database engine from a simple Java JDBC program. Use the embedded driver to create the database jdbcDemoDB and the WISH_LIST table. Populate the table with text entered from the keyboard, then view a list of the records in the table. Walk through the code to understand the basic structure of a JDBC program that accesses a Derby database. The CLASSPATH variable and connection URL attribute ;shutdown=true are introduced.

Activity 4:

Modify the Java JDBC program to load the client driver and connect to the Derby Network Server. Compile the altered program and test that the program operates as it did in the previous activity.

Activity 1: Run SQL using the embedded driver

In this activity, you will use the embedded driver to load the Derby database engine, create and connect to a database, and use some basic SQL statements.

To run SQL using the embedded driver:

1.		Create a directory and copy scripts into the directory
2.		Create a Derby database and run SQL statements

Creating a directory and copying scripts into the directory

To help you complete this activity, you must create a directory and copy several scripts into the new directory. You will use these scripts to quickly add tables and data to a new Derby database.

Tip: A command prompt appears after each command is run. If an error occurs, verify the syntax and retype the command.

Open a command window and change to a directory where you want to store the files that you create during the self-study tutorial activities.

Create the DERBYTUTOR directory. DERBYTUTOR will be your working directory for this activity.

Operating System	Command
UNIX (Korn Shell)	mkdir DERBYTUTOR
Windows	md DERBYTUTOR

Change to the DERBYTUTOR directory.

Operating System	Command
UNIX (Korn Shell)	cd DERBYTUTOR
Windows	cd DERBYTUTOR

Copy the SQL scripts from the Derby demo\programs\toursdb subdirectory into the DERBYTUTOR directory. You will use these scripts to create tables and add data to a new database, toursdb.

Operating System	Command
UNIX (Korn Shell)