This section provides instructions for using the Java DB database implementation bundled with Application Server 8.2.
Sun Java System Application Server 8.2 introduces two new asadmin commands for starting and stopping the Java DB Network Server.
The start-database command can be used to start an instance of the Java DB network server:
start-database [--dbhost 0.0.0.0] [--dbport 1527] [--dbhome path/derby] |
The default value for the host is 0.0.0.0, which allows for Java DB to listen on localhost as well as the IP/hostname interfaces. The value for the dbhome property is the location of the Java DB databases. The default path is <appserver_install_dir>/derby.
The asadmin stop-database command is used to shut down an instance of the Java DB network server that is running:
stop-database [--dbhost 0.0.0.0] [--dbport 1527] |
The Java DB configuration that ships with Application Server 8.2 also includes several useful scripts which can help you use Java DB. The following scripts are available for use in the <appserver_install_dir>/derby/frameworks/NetworkServer/bin directory:
startNetworkServer.ksh/bat — Script to start the network server
stopNetworkServer.ksh/bat — Script to stop the network server
ij.ksh/bat — interactive JDBC scripting tool
dblook.ksh/bat — Script to view all or part of the DDL for a database
sysinfo.ksh/bat — Script to display versioning info regarding the Java DB environment
NetworkServerControl.ksh/bat — Script which provides a means of executing commands on the NetworkServerControl API
Set the DERBY_INSTALL environment variable to point to the <appserver_install_dir>/derby directory.
Unset your CLASSPATH environment variable.
You can also optionally set the following properties:
For more information about these utilities, see the Derby Tools and Admin guides.
This example shows how to capture the DDL for a table in Pointbase and create the same table in Java DB using Netbeans 5.0. Another option for doing this is by using the commander tool and the unload database command:
./startcommander.sh Do you wish to create a new Database. (Yes (Y) or No (N))? [default: N]: Enter product to connect with: (Embedded (E) or Server (S))? [default: E]: e Enter driver to use? [default: [com.pointbase.jdbc.jdbcUniversalDriver]: Enter database URL? [default: [jdbc:pointbase:embedded:sample]: Enter Username? [default: PBPUBLIC]: Enter Password? [default: PBPUBLIC]: PointBase Commander 5.2 ECF build 294 size restricted version EMBEDDED Interactive SQL command language. SunOS/5.9 (C) Copyright 2004 DataMirror Mobile Solutions, Inc. All rights reserved. Licensed to: Sun_customer_demo_use For commercial version contact PointBase at: pointbase.com PHONE: 1-877-238-8798 (US & CANADA) 1-408-961-1100 (International) WEBSITE: www.pointbase.com SQL>unload database sampledb.sql; SQL> unload database sampledb.sql; SQL> 13 Row(s) Unloaded. (PBPUBLIC.CUSTOMER_TBL) SQL> 4 Row(s) Unloaded. (PBPUBLIC.DISCOUNT_CODE_TBL) SQL> 30 Row(s) Unloaded. (PBPUBLIC.MANUFACTURE_TBL) SQL> 11 Row(s) Unloaded. (PBPUBLIC.MICRO_MARKETS_TBL) SQL> 9 Row(s) Unloaded. (PBPUBLIC.OFFICE_TBL) SQL> 4 Row(s) Unloaded. (PBPUBLIC.OFFICE_TYPE_CODE_TBL) SQL> 15 Row(s) Unloaded. (PBPUBLIC.ORDER_TBL) SQL> 6 Row(s) Unloaded. (PBPUBLIC.PRODUCT_CODE_TBL) SQL> 30 Row(s) Unloaded. (PBPUBLIC.PRODUCT_TBL) SQL> 10 Row(s) Unloaded. (PBPUBLIC.SALES_REP_DATA_TBL) SQL> 10 Row(s) Unloaded. (PBPUBLIC.SALES_REP_TBL) SQL> 52 Row(s) Unloaded. (PBPUBLIC.SALES_TAX_CODE_TBL) SQL> 12 Table(s) Unloaded. SQL> quit;
The results from executing the unload database command is written in the above example to the file sampledb.sql. The sampledb.sql file contains all of the DDL required to create the necessary tables and indexes. It also contains the DML to insert the data back into the database. The commander command RUN is intended to be used import the data into another Pointbase database using the script that was generated. Here is an example of what the INSERT statements and associated data look like in the generated file:
INSERT INTO "ADVENTURE"."CATEGORY" ( "CATID", "LOCALE", "NAME", "DESCRIPTION", "IMAGEURI" ) VALUES( ?, ?, ?, ?, ? ); { 'ISLAND ','en_US','Island Adventures','Experience an island / paradise in a way fit for your needs.','Island_Adventures.gif' 'JUNGLE ','en_US','Jungle Adventures','Experience a jungle / paradise in a way fit for your needs.','Jungle_Adventures.gif' 'MOUNTAIN ','en_US','Mountain Adventures','Experience an / elevated paradise with a view.','Mountain_Adventures.gif' 'ORBITAL ','en_US','Orbital Adventures','Experience a vacuum / paradise with a beautiful view and where no one can hear you scream.', / 'Space_Adventures.gif' 'WESTERN ','en_US','Western Adventures','Enjoy the Wild West. / ','Western_Adventures.gif' 'SOUTH_POLE ','en_US','South Pole Adventures','Experience a / frozen paradise in a way fit for your needs.','SouthPole_Adventures.gif' };
You could easily edit the file generated from the commander unload database command so that it only consisted of the DDL (for example, it would not be hard to write a program which would process the insert statements). As a simple test, we use the unload database command against the Pointbase sample database, and then edit the generated script, making the following changes:
Removed the phrase Organization Heap from the end of all CREATE Table statements
Removed the COMMIT command
Changed the Boolean datatype to be smallint
Removed all of the INSERT statements and associated data
Next, a simple Ant script is used to execute the DDL using the sql target. Finally, the same experiment is repeated for the sun-appserv-samples database requiring the following additional changes to the generated SQL file:
Make all changes as described above for the sample database
Remove the create user commands
Remove the SET PATH commands
Change the Decimal precision from 38 to max of 31
Change the float precision from 64 to max of 52
The SPECIFIC keyword for CREATE PROCEDURE is not currently supported
Removed the GRANT commands
Converting Pointbase Java procedures to work with Java DB requires some changes to the Java code as well as to the CREATE PROCEDURE statements. Information on creating Java DB Java procedures can be found in the Derby Reference manual. Support for the Boolean datatype should be in the next release of Java DB.