http://www.apache.org/licenses/LICENSE-2.0
• | 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");
• | derbynet.jar This jar file contains the Network Server
code. It is only necessary for the process that starts the Network Server
in addition to the standard Derby .jar
files. | |
• | derby.jar This file must be in your classpath to
use any of the Derby Network
Server functions. | |
• | derbyclient.jar This jar file must be in your class
path to use the Network Client driver. The jar file is necessary for client-side
communication with the Network Server using the Derby Network
Client driver. It needs to be in the client-side classpath to use the Network
Client driver to access Derby. |
• | setNetworkClientCP.bat (Windows) | |
• | setNetworkClientCP.ksh (UNIX) | |
• | setNetworkServerCP.bat (Windows) | |
• | setNetworkServerCP.ksh (UNIX) |
The default system directory is the directory in which Derby was started. (See the Java DB Developer's Guide for more information about the default system directory.)$DERBY_HOME\frameworks\NetworkServer\bin\startNetworkserver.bat
• | Specify a port number other than the default (1527) by using the -p <portnumber> option
as shown in the following example: where 1368 is the new port number. | |
• | 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: Remember: Before
using this option, you should run under the Java security manager and enable
user authentication. where myhost is the host name or IP address. On all interfaces,
you can specify a host name, IP address or 0.0.0.0 to listen. |
• | 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/frameworks/NetworkServer/bin
directory. |
• | Run the following sysinfo script to obtain information about
the Network Server on a Windows system:
| |
• | Run the following sysinfo script to obtain information about
the Network Server on a UNIX system:
|
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. |
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. |
• | 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) |
Encrypted user id and encrypted password | ClientDataSource.ENCRYPTED_USER_AND_PASSWORD_SECURITY
(0x09) | Encryption requires a JCE implementation that supports
the Diffie-Helman algorithm with a prime of 32 bytes. |
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);
public static javax.sql.DataSource getDS(String database, String user, String password) throws SQLException { org.apache.derby.jdbc.ClientDataSource ds = new org.apache.derby.jdbc.ClientDataSource(); // DatabaseName can include Derby URL Attributes ds.setDatabaseName(database); if (user != null) ds.setUser(user); if (password != null) ds.setPassword(password); // The host on which Network Server is running ds.setServerName("localhost"); // port on which Network Server is listening ds.setPortNumber(1527); return ds; }
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.
| Startup ij in
one of the following ways:
| |||||||
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. | |
• | 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. | |
• | 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. |
1.
| Copy the following IBM JCE jar files to the jre/lib/ext directory
of the IBM SDK's installation home:
| |||||||||||||
2.
| Modify the java.security file in the jre/lib/security directory.
In the section that lists providers (and preference order), replace the text
with:
Note: If
you are installing the IBM JCE on a Sun Java Development Kit, you must specify
both of these lines in the order shown. | |||||||||||||
3.
| To use the encrypted user id and password security mechanism during
JDBC connection using the network client, specify the securityMechanism in
the connection property. If an encrypted database is booted in
the Network Server, users can connect to the database without giving the bootPassword.
The first connection to the database must provide the bootPassword,
but all subsequent connections do not need to supply it. To remove access
from the encrypted database, use the shutdown=true option
to shut down the database. |
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
//Recommended set of permissions to start and use the Network Server, //assuming the 'd:/derby/lib' directory has been secured. //Fine tune based on your environment settings grant codeBase "file:d:/derby/lib/-" { permission java.io.FilePermission "${derby.system.home}${/}-", "read, write, delete"; permission java.io.FilePermission "${derby.system.home}","read"; permission java.io.FilePermission "${user.dir}${/}-", "read, write, delete"; permission java.util.PropertyPermission "derby.*", "read"; permission java.util.PropertyPermission "user.dir", "read"; permission java.lang.RuntimePermission "createClassLoader"; permission java.net.SocketPermission "myclientmachine", "accept"; }; //Required set of permissions to stop the Network Server, assuming you have // secured the 'd:/derby/lib' directory //Remember to fine tune this as per your environment. grant codeBase "file:d:/derby/lib/-" { //Following is required when server is started with "-h localhost" //or without the -h option permission java.net.SocketPermission "localhost", "accept, connect, resolve"; permission java.net.SocketPermission "127.0.0.1", "accept, connect, resolve"; //The following is only required if the server is started with the -h <host> //option (else shutdown access will be denied). permission java.net.SocketPermission "<host>:*", "accept, connect, resolve"; };
• | The following example shows how to start the Network Server in the default
security manager (listening to clients from localhost only,
which is the default behavior if the -h option is not used
to start the server). This example assumes that the policy file exists in d:/nwsvr.policy.
| |
• | You can also achieve the same behavior by using the -h option
when starting the server as shown in the following example:
| |
• | The following example shows how to start the Network Server (assuming
that you start the server on the host machine myserver) in the default
security manager (listening to client requests originating from other machines
only). This example assumes that the policy file exists in d:/nwsvr.policy.
|
• | 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);