Java DB Server and Administration Guide
Version 10.3
Derby Document build:
July 26, 2007, 9:17:01 AM (EDT)
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.
• | Derby in a multi-user environment Describes the different options for embedding Derby in a server framework and
explains the Network Server option. | |
• | Using the Network Server with preexisting Derby applications Describes how to change existing Derby applications to work with the
Network Server. | |
• | Managing the Derby Network Server Describes how to use shell scripts, the command
line, and the Network Server API to manage the Network Server. | |
• | Managing the Derby Network Server remotely by using the servlet interface Describes how to use the servlet interface to manage the Network Server. | |
• | Derby Network Server advanced topics Describes
advanced topics for Derby Network Server users. |
• | Checking database consistency Describes how to check the consistency of Derby databases. | |
• | Backing up and restoring databases Describes how to back up a database when it is online. | |
• | Logging on a separate device Describes how to put a database's log on a separate device, which can improve the performance of large databases. | |
• | Obtaining locking information Describes how to get detailed information about locking status. | |
• | Reclaiming unused space Describes how to identify and reclaim unused space in tables and related indexes. |
• | On the server side, install derby.jar and derbynet.jar. | |
• | On the client side, install derbyclient.jar. |
• | Through the command line | |
• | By using .bat and .ksh scripts | |
• | Through the servlet interface | |
• | With your own Java program (written using the Network Server API) | |
• | By setting Network Server properties |
/* If you are running on JDK 1.6 or higher, then you do not need to invoke Class.forName(). In that environment, the EmbeddedDriver loads automatically. */ Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance(); Connection conn = DriverManager.getConnection( "jdbc:derby:sample");
import org.apache.derby.drda.NetworkServerControl; import java.net.InetAddress; NetworkServerControl server = new NetworkServerControl (InetAddress.getByName("localhost"),1527); server.start(null);
String nsURL="jdbc:derby://localhost:1527/sample"; java.util.Properties props = new java.util.Properties(); props.put("user","usr"); props.put("password","pwd"); /* If you are running on JDK 1.6 or higher, then you do not need to invoke Class.forName(). In that environment, the EmbeddedDriver loads automatically. */ Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance(); Connection conn = DriverManager.getConnection(nsURL, props); /*interact with Derby*/ Statement s = conn.createStatement(); ResultSet rs = s.executeQuery( "SELECT * FROM HotelBookings");
• | derbyrun.jar |
• | derbynet.jar This jar file contains the Network Server
code. It must be in your classpath to start the Network Server. | |
• | derby.jar This jar file contains the
Derby database engine code.
It must be in the classpath in order for the Network Server to access
Derby databases.
derby.jar is included in the Class-Path attribute of
derbynet.jar's manifest file. If you have derbynet.jar
in the classpath and derby.jar is in the same directory as
derbynet.jar, it is not necessary to include derby.jar
explicitly. | |
• | derbyclient.jar This jar file contains
the Derby Network Client
JDBC driver that is necessary for communication with the Network Server. It must
be in the classpath of the application on the client side in order to access
Derby databases over a network.
|
• | setNetworkClientCP.bat (Windows) | |
• | setNetworkClientCP (UNIX) | |
• | setNetworkServerCP.bat (Windows) | |
• | setNetworkServerCP (UNIX) |
• | If you are relatively new to the Java programming language, follow the
instructions in "Setting up your environment" in
Getting Started with Java DB to set the
DERBY_HOME
and JAVA_HOME environment variables and to add
DERBY_HOME/bin
to your path. Then use the startNetworkServer.bat
script to start the Network Server on Windows machines and the startNetworkServer
script to start the Network Server on UNIX systems. These scripts are located
in $DERBY_HOME/bin,
where $DERBY_HOME is the
directory where you installed Derby. You
can run NetworkServerControl commands only from the host that started the
Network Server.
| |||||||
• | If you are a regular Java user but are new to Derby, set the
DERBY_HOME
environment variable, then use a java command to invoke the
derbyrun.jar or derbynet.jar file:
To see the command syntax, invoke derbyrun.jar server or
derbynet.jar with no arguments. | |||||||
• | If you are familiar with both the Java programming language and Derby,
you have already set
DERBY_HOME.
Set your classpath to include the
Derby
jar files. Then use a java command to invoke the
NetworkServerControl class directly.
|
• | Specify a port number other than the default (1527) by using the
-p portnumber option, as shown in the
following example:
| |
• | Specify a specific interface (host name or IP address) to listen on other
than the default (localhost) by using the -h
option, as shown in the following example:
where myhost is the host name or IP address. Remember: Before using the -h option, you should
run under the Java security manager with a customized security policy
and you should enable user authentication. |
• | You can include the following line in the derby.properties file: This starts the server on the default port, 1527, listening on localhost (all interfaces). To specify a different port or a specific
interface in the derby.properties file, include the following
lines, respectively:
You can also specify
the startNetworkServer and portNumber properties
by using a Java command:
| |
• | You can use the NetworkServerControl API to start the Network Server
from a separate thread within a Java application:
|
• | To shut down the Network Server by using the scripts that are provided
for Windows systems, use:
| |
• | To shut down the Network Server by using the scripts that are provided
for UNIX systems, use:
These scripts are located in the $DERBY_HOME/bin
directory. |
• | Run the following script to obtain information about
the Network Server on a Windows system:
| |
• | Run the following script to obtain information about
the Network Server on a UNIX system:
|
java -jar $DERBY_HOME/lib/derbyrun.jar server sysinfo [-h hostname][-p portnumber]
java org.apache.derby.drda.NetworkServerControl sysinfo [-h hostname][-p portnumber]
• | To run runtimeinfo from the command line:
| |
• | The getRuntimeInfo method returns the same information as
the runtimeinfo command. The signature for the getRuntimeInfo method
is String getRuntimeInfo(). For example:
|
Properties getCurrentProperties();
NetworkServerControl server = new NetworkServerControl(); Properties p = server.getCurrentProperties(); p.list(System.out); System.out.println(p.getProperty("derby.drda.host"));
org.apache.derby.jdbc.ClientDriver
where the <URL attribute> is either a Derby embedded or network client attribute.jdbc:derby://<server>[:<port>]/ <databaseName>[;<URL attribute>=<value> [;...]]
Property | Type | Description | URL attribute | Notes |
databaseName | String | The name of the database. This property is required. | ' | This property is also available using EmbeddedDataSource. |
dataSourceName | String | The data source name. | ' | This property is also available using EmbeddedDataSource. |
description | String | A description of the data source. | ' | This property is also available using EmbeddedDataSource. |
user | String | The user's account name. | user | Default is APP. This property is also available using
EmbeddedDataSource. |
password | String | The user's database password. | password | This property is also available using EmbeddedDataSource. |
serverName | String | The host name or TCP/IP address where the server is
listening for requests. | ' | Default is "localhost". |
portNumber | Integer | The port number where the server is listening for requests. | ' | Default is "1527". |
Property | Type | Description | URL attribute | Notes |
traceFile | String | The filename for tracing output. Setting this property
turns on tracing. See Network client tracing. | traceFile | ' |
traceDirectory | String | The directory for the tracing output. Each connection
will send output to a separate file. Setting this property turns on tracing.
See Network client tracing. | traceDirectory | ' |
traceLevel | Integer | The level of client tracing if traceFile or traceDirectory are
set. | traceLevel | The default is TRACE_ALL. |
traceFileAppend | Boolean | Value is true if tracing output should append to the
existing trace file. | traceFileAppend | The default is false. |
securityMechanism | Integer | The security mechanism. See Network client security. | securityMechanism | The default is USER_ONLY _SECURITY. |
retrieveMessageText | Boolean | Retrieve message text from the server. A stored procedure
is called to retrieve the message text with each SQLException and might
start a new unit of work. Set this property to false if you do not want the
performance impact or when starting new units of work. | retrieveMessageText | The default is true. |
ssl | String | The SSL mode for the client connection. See Network encryption and authentication with SSL/TLS | ssl | The default is off. |
Property | Type | Description | URL attributes | Notes |
connectionAttributes | String | Set to the list of Derby embedded
connection attributes separated by semicolons. | Various | This property is also available using EmbeddedDataSource.
See the Java DB Reference Manual for more information
about the various connection attributes. |
createDatabase | String | If set to "create", create the database specified with databaseName property. | create | This property is also available using EmbeddedDataSource.
See the Java DB Reference Manual for more information.
Similar to setting connectionAttribute to "create=true". Only "create" is allowed, other values equate to null. The result of conflicting settings of createDatabase, shutdownDatabase and connectionAttributes is undefined. |
shutdownDatabase | String | If set to "shutdown", shutdown the database specified with databaseName property. | shutdown | This property is also available using EmbeddedDataSource.
See the Java DB Reference Manual for more information.
Similar to setting connectionAttribute to "shutdown=true". Only "shutdown" is allowed, other values equate to null. The result of conflicting settings of createDatabase, shutdownDatabase and connectionAttributes is undefined.
If authentication and sqlAuthorization are both enabled, database shutdown is restricted to the database owner.
|
• | When you are using the DriverManager interface, set securityMechanism in
a java.util.Properties object before you invoke the form
of the getConnection method, which includes the java.util.Properties parameter. | |
• | When you are using the DataSource interface to create
and deploy your own DataSource objects, invoke the DataSource.setSecurityMechanism method
after you create a DataSource object. |
Security mechanism | securityMechanism property value | Comments |
User id and password | ClientDataSource.CLEAR_TEXT_PASSWORD_SECURITY (0x03) | Default if password is set |
User id only | ClientDataSource.USER_ONLY_SECURITY (0x04) | Default if password is not set |
Strong password substitution | ClientDataSource.STRONG_PASSWORD_SUBSTITUTE_SECURITY
(0x08) | Strong password substitution cannot be used with external
Derby authentication schemes (for example, LDAP). Also, this security mechanism
uses the SHA1PRNG algorithm to generate a random number that gets exchanged
between client and server. If you need to use this security mechanism, make
sure that support for the SHA1PRNG algorithm is available in the JCE provider
available with your JVM. For example, it is available with JVM version 1.4.1
and higher from Sun and with JVM version 1.4.2 and higher from IBM. |
Encrypted user id and encrypted password | ClientDataSource.ENCRYPTED_USER_AND_PASSWORD_SECURITY
(0x09) | Encryption requires a JCE implementation that supports
the Diffie-Hellman algorithm with a public prime of 256 bits. |
ij>connect 'jdbc:derby://localhost:1527/mydb; create=true;traceFile=trace.out;user=user1;password=secret4me';
• | Use the setLogWriter(java.io.PrintWriter) method of ClientDataSource
and set the PrintWriter to a non-null value. | |
• | Use the setTraceFile(String filename) method of ClientDataSource. | |
• | Use the setTraceDirectory(String dirname) method of ClientDataSource
to trace each connection flow in its own file for programs that have multiple
connections. |
• | Use the setLogWriter(java.io.PrintWriter) method of DriverManager
and set the PrintWriter to a non null-value. | |
• | Use the traceFile or traceDirectory URL
attributes to set these properties prior to creating the connection with the DriverManager.getConnection() method. |
String url = "jdbc:derby://samplehost.sampledomain.com:1528/mydb" + ";traceFile=/u/user1/trace.out" + ";traceLevel=" + org.apache.derby.jdbc.ClientDataSource.TRACE_PROTOCOL_FLOWS; DriverManager.getConnection(url,"user1","secret4me");
Trace level | Value |
org.apache.derby.jdbc.ClientDataSource.TRACE_NONE | 0x0 |
org.apache.derby.jdbc.ClientDataSource.TRACE_CONNECTION_CALLS | 0x1 |
org.apache.derby.jdbc.ClientDataSource.TRACE_STATEMENT_CALLS | 0x2 |
org.apache.derby.jdbc.ClientDataSource.TRACE_RESULT_SET_CALLS | 0x4 |
org.apache.derby.jdbc.ClientDataSource.TRACE _DRIVER_CONFIGURATION | 0x10 |
org.apache.derby.jdbc.ClientDataSource.TRACE_CONNECTS | 0x20 |
org.apache.derby.jdbc.ClientDataSource.TRACE_PROTOCOL_FLOWS | 0x40 |
org.apache.derby.jdbc.ClientDataSource.TRACE _RESULT_SET_META_DATA | 0x80 |
org.apache.derby.jdbc.ClientDataSource.TRACE _PARAMETER_META_DATA | 0x100 |
org.apache.derby.jdbc.ClientDataSource.TRACE_DIAGNOSTICS | 0x200 |
org.apache.derby.jdbc.ClientDataSource.TRACE_XA_CALLS | 0x800 |
org.apache.derby.jdbc.ClientDataSource.TRACE_ALL | 0xFFFFFFFF; |
• | Use bitwise OR operators ( | ) with two or more trace values. For example,
to trace PROTOCOL flows and connection calls, specify this value for traceLevel:
| |
• | Use a bitwise complement operator ( ~ ) with a trace value to specify
all except a certain trace. For example, to trace everything except PROTOCOL
flows, specify this value for traceLevel:
|
derby.connection.requireAuthentication=true derby.authentication.provider=BUILTIN derby.user.judy=no12see
jdbc:derby://localhost:1527/sample;user=judy;password=no12see
jdbc:derby://localhost:1527/sample;create=true;user=judy; password=no12see
jdbc:derby://localhost:1527/c:/my-db-dir/my-db-name;user=judy; password=no12see
String databaseURL = "jdbc:derby://localhost:1527/sample"; // // Load Derby Network Client driver class // If you are running on JDK 1.6 or higher, then you do not // need to invoke Class.forName(). In that environment, the // network client driver loads automatically. // Class.forName("org.apache.derby.jdbc.ClientDriver"); // Set user and password properties Properties properties = new Properties(); properties.put("user", "judy"); properties.put("password", "no12see"); // Get a connection Connection conn = DriverManager.getConnection(databaseURL, properties);
You must have the following two jar files present in your classpath in order to use the DB2(R) Driver for JDBC:com.ibm.db2.jcc.DB2Driver
• | db2jcc.jar | |
• | db2jcc_license_c.jar |
jdbc:derby:net://<server>[:<port>]/ <databaseName>[;<Derby URL attribute>=<value> [;...]] [:<Universal Driver attribute>=<value>; [...;]]
jdbc:derby:net://localhost:1527/sample:user=judy;password=no12see; retrieveMessagesFromServerOnGetMessage=true;
jdbc:derby:net://localhost:1527/sample;create=true:user=judy; password=no12see;retrieveMessagesFromServerOnGetMessage=true;
jdbc:derby:net://localhost:1527/"c:/my-db-dir/my-db-name":user=judy; password=no12see;retrieveMessagesFromServerOnGetMessage=true;
String databaseURL = "jdbc:derby:net://localhost:1527/sample"; // Load DB2 Driver for JDBC class Class.forName("com.ibm.db2.jcc.DB2Driver"); // Set user and password properties Properties properties = new Properties(); properties.put("user", "APP"); properties.put("password", "APP"); properties.put("retreiveMessagesFromServerOnGetMessage", "true"); // Get a connection Connection conn = DriverManager.getConnection(databaseURL, properties);
org.apache.derby.jdbc.ClientDataSource ds = new org.apache.derby.jdbc.ClientDataSource(); ds.setDatabaseName("mydb); ds.setCreateDatabase("create"); ds.setUser("user"); ds.setPassword("mypass"); // The host on which Network Server is running ds.setServerName("localhost"); // The port on which Network Server is listening ds.setPortNumber(1527); Connection conn = ds.getConnection();
import org.apache.derby.jdbc.ClientXADataSource; import javax.sql.XAConnection; ... XAConnection xaConnection = null; Connection conn = null; String driver = "org.apache.derby.jdbc.ClientDataSource"; ClientXADataSource ds = new ClientXADataSource(); ds.setDatabaseName ("sample;create=true"); ds.setServerName("localhost"); ds.setPortNumber(1527); Class.forName(driver); xaConnection = ds.getXAConnection("auser", "shhhh"); conn = xaConnection.getConnection();
1.
| Start ij in
one of the following ways. For details, see "Starting ij" in the
Java DB Tools and Utilities Guide.
| ||||||||||
2.
| Connect by specifying the URL: See Network client driver examples for additional URL examples. |
• | Error messages and SQLStates can differ between the network client and
embedded driver. Some SQLStates may be null when using the network client,
particularly for data conversion errors. | |
• | Multiple SQL exceptions and warnings will only return the SQLState of
the first exception when using the network client. The text of the additional
exceptions will be appended to the text of the first exception. See Error message differences. | |
• | Treatment of error situations encountered during batch processing with java.sql.Statement, java.sql.PreparedStatement and java.sql.CallableStatement is different. With the embedded driver processing stops when an error is encountered; with the network client driver processing continues, but an appropriate value as defined in the java.sql.Statement api is returned in the resulting update count array. | |
• | To use an encrypted user id and password, you need to have the IBM's Java
Cryptography Extension (JCE) Version 1.2.1 or later. |
• | The Network Client requires that there be at least one column in the select
list from the target table. For example, the following statement will fail
in a server environment: The Network Client driver looks at both of the columns in the select list and cannot determine the target table for update/delete by looking at the column metadata. This requirement is not necessary in an embedded environment. | |
• | The embedded driver allows for statement name changes when there is an
open result set on the statement object. This is not supported in a server
environment. |
Embedded environment | Server environment |
updateBytes on CHAR, VARCHAR, LONG VARCHAR datatypes
supported. | Not supported |
updateTime on TIMESTAMP datatypes supported. | Not supported |
updateClob and updateBlob supported. | Not supported |
ij> create table ai (x int, y int generated always as identity (increment by 200000000)); ij> insert into ai (x) values (1),(2),(3),(4),(5),(6),(7), (8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19);
ERROR 42Z24: Overflow occurred in identity for column 'Y' in table 'AI': SQLSTATE: 22003: The resulting value is outside the range for the data type INTEGER.
• | UserID (org.apache.derby.jdbc.ClientDataSource.USER_ONLY_SECURITY)
When using this mechanism, you must specify only the user property.
All other mechanisms require you to specify both the user name and the password. | |
• | Encrypted UserID and encrypted password (org.apache.derby.jdbc.ClientDataSource.ENCRYPTED_USER_AND_PASSWORD_SECURITY) When using this mechanism, both password and
user id are encrypted. | |
• | Strong password substitution (org.apache.derby.jdbc.ClientDataSource.STRONG_PASSWORD_SUBSTITUTE_SECURITY)
When using this mechanism, a strong password substitute is generated and
used to authenticate the user with the network server. The original password is
never sent in any form across the network. |
• | Clear text user name and password security, the default | |
• | Strong password substitute security | |
• | Encrypted user name and password security |
Connection.prepareStatement(String sql, String[] columnNames) Connection.prepareStatement(String sql, int[] columnIndexes) Statement.execute(String sql, String[] columNames) Statement.execute(String sql, int[] columIndexes) Statement.executeUpdate(String sql, String[] columnNames) Statement.executeUpdate(String sql, int[] columnIndexes)
java org.apache.derby.drda.NetworkServerControl start -p 1088
1.
| However, it is better to specify the port numbers by using any
of the following methods
|
• | NetworkServerControl() This constructor method
creates an instance that listens either on the default port (1527) or the
port that is set by the derby.drda.portNumber property. It
will also listen on the host set by the derby.drda.host property
or the loopback address if the property is not set. This is the default constructor;
it does not allow remote connections. It is equivalent to calling NetworkServerControl(InetAddress.getByName("localhost"),1527)
if no properties are set. | |
• | NetworkServerControl (InetAddress address, int portNumber) This constructor method creates an instance that listens on
the specified portNumber on the specified address. The InetAddress will
be passed to ServerSocket. NULL is an invalid address value.
The following examples show how you might allow Network Server to accept
connections from other hosts:
|
• | On the command line | |
• | In the .bat or .ksh files (loading the
properties by executing java -D) | |
• | In the derby.properties file. |
derby.drda.securityMechanism = [ USER_ONLY_SECURITY | CLEAR_TEXT_PASSWORD_SECURITY | ENCRYPTED_USER_AND_PASSWORD_SECURITY | STRONG_PASSWORD_SUBSTITUTE_SECURITY ]
• | You can use the scripts NetworkServerControl.bat for Windows systems
or NetworkServerControl.ksh for UNIX systems with the ping command.
For example:
| |
• | You can use the NetworkServerControl command:
| |
• | You can use the NetworkServerControl API to verify startup from
within a Java application:
|
private static boolean isServerStarted(NetworkServerControl server, int ntries) { for (int i = 1; i <= ntries; i ++) { try { Thread.sleep(500); server.ping(); return true; } catch (Exception e) { if (i == ntries) return false; } } return false; }
http://<server>[:port]/derby/derbynet
• | A servlet engine is not part of the Network Server. | |
• | When the Network Server is started by the servlet interface, shutting down
the Application Server also shuts the Network Server down, since both run in the
same JVM. |
• | Start or stop logging. | |
• | Start or stop tracing all sessions. | |
• | Specify session to trace. (If you choose this option, the Trace session
page is displayed.) | |
• | Change tracing directory (If you choose this option, the Trace directory
page is displayed.) | |
• | Specify threading parameters for Network Server. (If you choose this option,
the Thread parameters page is displayed.) | |
• | Stop the Network Server. |
java org.apache.derby.drda.NetworkServerControl start
java org.apache.derby.drda.NetworkServerControl start -h sampleserver.sampledomain.com
java org.apache.derby.drda.NetworkServerControl start -h 0.0.0.0
java org.apache.derby.drda.NetworkServerControl start ...
grant codeBase "${derby.install.url}derby.jar" { // // These permissions are needed for everyday, embedded Derby usage. // permission java.lang.RuntimePermission "createClassLoader"; permission java.util.PropertyPermission "derby.*", "read"; permission java.util.PropertyPermission "user.dir", "read"; permission java.util.PropertyPermission "derby.storage.jvmInstanceId", "write"; permission java.io.FilePermission "${derby.system.home}","read"; permission java.io.FilePermission "${derby.system.home}${/}-", "read,write,delete"; // // This permission lets you backup and restore databases // to and from arbitrary locations in your file system. // // This permission also lets you import/export data to and from // arbitrary locations in your file system. // // You may want to restrict this access to specific directories. // permission java.io.FilePermission "<<ALL FILES>>", "read,write,delete"; }; grant codeBase "${derby.install.url}derbynet.jar" { // // This permission lets the Network Server manage connections from clients. // // Accept connections from any host. Derby is listening to the host // interface specified via the -h option to "NetworkServerControl // start" on the command line, via the address parameter to the // org.apache.derby.drda.NetworkServerControl constructor in the API // or via the property derby.drda.host; the default is localhost. // You may want to restrict allowed hosts, e.g. to hosts in a specific // subdomain, e.g. "*.acme.com". permission java.net.SocketPermission "*", "accept"; };
• | A template policy lives in the Derby distribution at demo/templates/server.policy.
Copy the from this location to your own file, say myCustomized.policy.
All of the following edits take place in your custom file. | |
• | Replace the ${derby.install.url} variable with the location of
the Derby jars in your local file system. | |
• | Replace the ${derby.system.home} variable with the location of
your Derby system directory. Alternatively, rather than replacing this variable,
you can simply set the value of the derby.system.home system property
when you boot the server. | |
• | Replace the ${derby.security.host} variable with the address of
the network interface on which the server is listening. Note that the special
wildcard address "0.0.0.0" is not understood by SocketPermission, even though
Derby accepts this wildcard as a valid value for the -h option of the Network
Server startup command. If you must grant blanket permission to this wildcard
address, specify "*" in the policy file. | |
• | Refine the file permissions needed by backup/restore, import/export, and
the loading of application jars. |
grant codeBase "file:/usr/local/share/sw/derby/lib/derby.jar" { // // These permissions are needed for everyday, embedded Derby usage. // permission java.lang.RuntimePermission "createClassLoader"; permission java.util.PropertyPermission "derby.*", "read"; permission java.util.PropertyPermission "user.dir", "read"; permission java.io.FilePermission "/usr/local/shoppingCartApp/databases","read"; permission java.io.FilePermission "/usr/local/shoppingCartApp/databases/-", "read,write,delete"; permission java.util.PropertyPermission "derby.storage.jvmInstanceId", "write"; // // This permission lets a DBA reload the policy file while the server // is still running. The policy file is reloaded by invoking the // SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY() system procedure. // permission java.security.SecurityPermission "getPolicy"; // // This permission lets you backup and restore databases // to and from a selected branch of the local file system: // permission java.io.FilePermission "/usr/local/shoppingCartApp/backups/-", "read,write,delete"; // // This permission lets you import data from // a selected branch of the local file system: // permission java.io.FilePermission "/usr/local/shoppingCartApp/imports/-", "read"; // // This permission lets you export data to // a selected branch of the local file system: // permission java.io.FilePermission "/usr/local/shoppingCartApp/exports/-", "write"; // // This permission lets you load your databases with jar files of // application code // permission java.io.FilePermission "/usr/local/shoppingCartApp/lib/*", "read"; }; grant codeBase "file:/usr/local/share/sw/derby/lib/derbynet.jar" { // // This permission lets the Network Server manage connections from clients. // permission java.net.SocketPermission "localhost:0-", "accept"; };
java -Djava.security.manager -Djava.security.policy=/usr/local/shoppingCartApp/lib/myCustomized.policy org.apache.derby.drda.NetworkServerControl start -h localhost
java org.apache.derby.drda.NetworkServerControl start -h localhost -noSecurityManager
ketool will prompt for needed information like identity details and passwords.keytool -genkey <alias> -keystore <keystore>
The certificate file may then be distributed to the relevant parties.keytool -export -alias <alias> -keystore <keystore> \ -rfc -file <certificate file>
keytool -import -alias <alias> -file <certificate file> \ -keystore <trust store>
Generate a server certificate:keytool -genkey -alias myDerbyServer -keystore serverKeyStore.key
Generate a client key pair:keytool -export -alias myDerbyServer -keystore serverKeyStore.key \ -rfc -file myServer.cert
Generate a client certficate:keytool -genkey -alias aDerbyClient -keystore clientKeyStore.key
Install a client certificate in the server's trust store:keytool -export -alias aDerbyClient -keystore clientKeyStore.key \ -rfc -file aClient.cert
Install the server certificate in a client's trust store:keytool -import -alias aDerbyClient -file aClient.cert -keystore serverTrustStore.key
keytool -import -alias myDerbyServer -file myServer.cert -keystore clientTrustStore.key
java -Djavax.net.ssl.keyStore=serverKeyStore.key \ -Djavax.net.ssl.keyStorePassword=qwerty \ -jar derbyrun.jar server start -ssl basic
System.setProperty("javax.net.ssl.trustStore","clientTrustStore.key"); System.setProperty("javax.net.ssl.trustStorePassword","qwerty"); Connection c = getConnection("jdbc:derby://myhost:1527/db;ssl=peerAuthentication");
System.setProperty("javax.net.ssl.keyStore","clientKeyStore.key"); System.setProperty("javax.net.ssl.keyStorePassword","qwerty"); Connection c = getConnection("jdbc:derby://myhost:1527/db;ssl=basic");
System.setProperty("javax.net.ssl.keyStore","clientKeyStore.key"); System.setProperty("javax.net.ssl.keyStorePassword","qwerty"); System.setProperty("javax.net.ssl.trustStore","clientTrustStore.key"); System.setProperty("javax.net.ssl.trustStorePassword","qwerty"); Connection c = getConnection("jdbc:derby://myhost:1527/db;ssl=peerAuthentication");
java -Djavax.net.ssl.keyStore=clientKeyStore.key \ -Djavax.net.ssl.keyStorePassword=qwerty \ -Djavax.net.ssl.trustStore=clientTrustStore.key \ -Djavax.net.ssl.trustStorePassword=qwerty \ -jar derbyrun.jar server shutdown -ssl peerAuthentication
• | You can change the maximum number of threads by using the following
command:
You can also use
the derby.drda.maxThreads property to assign the maximum
value. A <max> value of 0 means that there is no maximum and
a new thread will be generated for a connection if there are no current threads
available. This is the default. The <max> and <min>
values are stored as integers, so the theoretical maximum is 2147483647 (the
maximum size of an integer). But the practical maximum is determined by the
machine configuration. | |
• | To change the time that a thread should work on one session's request
and check if there are waiting sessions, use the following command:
You can also use the derby.drda.timeSlice property
to set this value. A value of 0 milliseconds indicates that the thread will
not give up working on the session until the session ends. A value of -1 milliseconds
indicates to use the default. The default value is 0. The maximum number of
milliseconds that can be specified is 2147483647 (the maximum size of an integer). |
• | To turn on connection logging, you can use the servlet interface
or you can issue the following command:
| |
• | To turn connection logging off you can use the servlet interface
or you can issue the following command:
|
1.
| Turn on tracing for all sessions by specifying the following property: Alternatively, while the Network Server is running, you can use the following command to turn on the trace facility:
If you specify a <connection number>, tracing will be turned
on only for that connection. | |
2.
| Set the location of the tracing files by specifying the following
property:
You need to specify only the directory where the tracing files will
reside. The names of the tracing files are determined by the system. If you
do not set a trace directory, the tracing files will be placed in derby.system.home. | |
3.
| While the Network Server is running, enter the following command
to set the trace directory:
|
• | Starts the Network Server. | |
• | Checks that the Network Server is running. | |
• | Loads the Network Client driver. (Note that this step is not
necessary if you are running the client on JDK 1.6 or higher. In that
environment, the network client driver loads automatically.) | |
• | Creates the NsSampledb database if not already created. | |
• | Checks to see if the schema is already created, and if not, creates the
schema which includes the SAMPLETBL table and corresponding indexes. | |
• | Connects to the database. | |
• | Loads the schema by inserting data. | |
• | Starts client threads to perform database related operations. | |
• | Has each of the clients perform DML operations (select, insert, delete,
update) using JDBC calls. For example, one client thread establishes an embedded
connection to perform database operations, while another client thread establishes
a client connection to the Network Server to perform database operations. | |
• | Waits for the client threads to finish the tasks. | |
• | Shuts down the Network Server at the end of the demonstration. |
• | NsSample.java This is the entry point into the
sample program. The program starts up two client threads. The first client
establishes an embedded connection to perform database operations, and the
second client establishes a client connection to the Network Server to perform
database operations. You can change the following constants to modify
the sample program: NUM_ROWS The number of rows that must be initially loaded into the schema. ITERATIONS The number of iterations for which each client thread does database related
work. NUM_CLIENT_THREADS The number of clients that you want to run the program against. NETWORKSERVER_PORT The port on which the Network Server is running. | |||||||
• | NsSampleClientThread.java This file contains two
Java classes:
| |||||||
• | NetworkServerUtil.java This file contains helper
methods to start the Network Server and to shutdown the server. |
• | NsSample.class | |
• | NsSampleClientThread.class | |
• | NsSampleWork.class | |
• | NetworkServerUtil.class |
1.
| Open a command prompt and change directories to the %DERBY_HOME%\demo\
directory, where %DERBY_HOME%
is the directory where you installed Derby. | |
2.
| Set the CLASSPATH to the current directory (".") and also include
the following jar files in order to use the Network Server and the network
client driver: derbynet.jar The Network Server jar file. It must be in your CLASSPATH to use any of
the Network Server functions. derbyclient.jar This jar file must be in your CLASSPATH to use the Network Client driver. derby.jar The Derby database
engine jar file. derbytools.jar The Derby tools jar
file. | |
3.
| Test the CLASSPATH settings by running the following Java command:
This command shows the Derby jar
files that are in the classpath as well as their respective versions. | |
4.
| After you set up your environment correctly, run the NsSample program
from the same directory:
If the program runs successfully, you will receive output similar
to that shown in the following table:
|
• | Starts the Derby Network Server by using a property and also loads the embedded driver | |
• | Determines if the Network Server is running | |
• | Creates the NSSimpleDB database if it is not already
created | |
• | Obtains an embedded database connection | |
• | Tests the database connection by executing a sample query | |
• | Allows client connections to connect to the server until you decide to
stop the server and exit the program | |
• | Closes the connection | |
• | Shuts down the Network Server before exiting the program |
• | The source file: SimpleNetworkServerSample.java | |
• | The compiled class file: SimpleNetworkServerSample.class |
1.
| Open a command prompt and change directories to the %DERBY_HOME%\demo\nserverdemo
directory, where %DERBY_HOME%
is the directory where you installed Derby. | |
2.
| Set the classpath to include the current directory ("."), and the
following jar files: derbynet.jar The Network Server jar file. It must be in your CLASSPATH because you
start the Network Server in this program. derby.jar The database engine jar file. derbytools.jar The Derby tools jar
file. | |
3.
| Test the CLASSPATH settings by running the following Java command:
This command displays the Derby jar
files that are in the classpath. | |
4.
| After you set up your environment correctly, run the SimpleNetworkServerSample
program from the same directory:
If the program runs successfully, you will receive output that
is similar to that shown in the following exampleS:
|
• | Loads the network client driver. (Note that this step is not
necessary if you are running the client on JDK 1.6 or higher. In that
environment, the network client driver loads automatically.) | |
• | Obtains a client connection by using the DriverManager. | |
• | Obtains a client connection by using a DataSource. | |
• | Tests the database connections by running a sample query. | |
• | Closes the connections and then exits the program. |
• | The source file: SimpleNetworkClientSample.java. | |
• | The compiled class file: SimpleNetworkClientSample.class. |
1.
| Open a command prompt and change directories to the%DERBY_HOME%\demo\nserverdemo
directory, where %DERBY_HOME%
is the directory where you installed Derby. | |||||||
2.
| Set the classpath to include the following jar files:
| |||||||
3.
| After you set up your environment correctly, run the SimpleNetworkClientSample
program from the same directory:
If the program runs successfully, you will receive output similar
to that shown in the following example:
|
• | Base tables are internally consistent | |
• | Base tables and all associated indexes contain the same number of rows | |
• | The values and row locations in each index match those of the base table | |
• | All BTREE indexes are internally consistent |
where SchemaName and TableName are expressions that evaluate to a string data type. If you created a schema or table name as a non-delimited identifier, you must present their names in all upper case. For example:VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE( SchemaName, TableName)
VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'CITIES')
ERROR X0Y55: The number of rows in the base table does not match the number of rows in at least 1 of the indexes on the table. Index 'T1_I' on table 'APP.T1' has 4 rows, but the base table has 5 rows. The suggested corrective action is to recreate the index.
ERROR X0X62: Inconsistency found between table 'APP.T1' and index 'T1_I'. Error when trying to retrieve row location '(1,6)' from the table. The full index key,including the row location, is '{ 1, (1,6) }'. The suggested corrective action is to recreate the index.
ERROR X0X61: The values for column 'C10' in index 'T1_C10' and table 'APP.T1' do not match for row location (1,7). The value in the index is '2 2 ', while the value in the base table is 'NULL'. The full index key, including the row location, is '{ 2 2 , (1,7) }'. The suggested corrective action is to recreate the index.
SELECT tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE( 'SAMP', tablename) FROM sys.sysschemas s, sys.systables t WHERE s.schemaname = 'SAMP' AND s.schemaid = t.schemaid
xcopy d:\mydatabases\sample c:\mybackups\2005-06-01\sample /s /i
CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('c:/mybackups/2005-06-01')
public static void backUpDatabase(Connection conn)throws SQLException { // Get today's date as a string: java.text.SimpleDateFormat todaysDate = new java.text.SimpleDateFormat("yyyy-MM-dd"); String backupdirectory = "c:/mybacksup/" + todaysDate.format((java.util.Calendar.getInstance()).getTime()); CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)"); cs.setString(1, backupdirectory); cs.execute(); cs.close(); System.out.println("backed up database to "+backupdirectory); }
public static void backUpDatabaseWithFreeze(Connection conn) throws SQLException { Statement s = conn.createStatement(); s.executeUpdate( "CALL SYSCS_UTIL.SYSCS_FREEZE_DATABASE()"); //copy the database directory during this interval s.executeUpdate( "CALL SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE()"); s.close(); }
• | If you are using an operating system command to back up the database,
you must explicitly copy the log file as well, as shown in the following example:
|
• | Edit the logDevice entry in service.properties of the database
backup so that it points to the correct location for the log. In the previous
example, the log was moved to the default location for a log, so you can remove
the logDevice entry entirely, or leave the logDevice entry as is and wait
until the database is restored to edit the entry. |
• | The backup copy of the database | |
• | The set of archived logs | |
• | The current online active log |
SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE (IN BACKUPDIR VARCHAR(32672), IN SMALLINT DELETE_ARCHIVED_LOG_FILES)
SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(IN SMALLINT DELETE_ARCHIVED_LOG_FILES)
connect 'jdbc:derby:wombat;create=true'; create table t1(a int not null primary key); ------------------DML/DDL Operations CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE ('d:/backup', 0); insert into t1 values(19); create table t2(a int); -----------------DML/DDL Operations -----------------Database Crashed (Media Corruption on data disks)
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=d:/backup/wombat'; select * from t1; ---------------DML/DDL Operations
• | Specify the non-default location by using the logDevice attribute on the database connection URL when you create the database. | |
• | If the database is already created, move the log manually and update the service.properties file. |
--SQLException Caught-- SQLState: 40001 = Error Code: 30000 Message: A lock could not be obtained due to a deadlock, cycle of locks and waiters is: Lock : ROW, DEPARTMENT, (1,14) Waiting XID : {752, X} , APP, update department set location='Boise' where deptno='E21' Granted XID : {758, X} Lock : ROW, EMPLOYEE, (2,8) Waiting XID : {758, U} , APP, update employee set bonus=150 where salary=23840 Granted XID : {752, X} The selected victim is XID : 752
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'FLIGHTAVAILABILITY', 0);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'FLIGHTAVAILABILITY', 1);