Differences between running Derby in embedded mode and using the Network Server

XA is not supported.

When you use the Derby Network Server, the java.sql.Connection.setTransactionIsolation() method behaves somewhat differently than when Derby runs in embedded mode:

1. setTransactionIsolation() does not commit the transaction.
2. The new isolation level is applied only to statements created after setTransactionIsolation() is called. Statements created with the previous isolation level will continue to be executed with the isolation level that was set when they were created.

The Network Server reports only the first error/warning if multiple errors or warnings occur for a given statement. For example:

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);

The Network Server generates the following error with the exception message appended to error:

ERROR 42Z24: Overflow occurred in identity for column 'Y' in table 'A I':
SQLSTATE: 22003: The resulting value is outside the range 
for the data type INTEGER.

Using the Derby embedded driver, you would see the following two SQL exceptions: :

ERROR 42Z24: Overflow occurred in identity for column 'Y' in table 'AI'. 
 

ERROR 22003: The resulting value is outside the range for the data type INTEGER.

This is because the Universal Driver reports only one SQLException or one SQLWarning per statement.

By default, the Universal Driver will not return message text. Use the retrieveMessagesFromServerOnGetMessage attribute to see message text. For example, if retrieveMessagesFromServerOnGetMessage=false:

ij> select * from akdfj;
ERROR 42X05: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42X05, SQLERRMC: AKDFJ 42X05

If retrieveMessagesFromServerOnGetMessage=true:

ij> select * from akdfj;
ERROR 42X05: Table 'AKDFJ' does not exist.

As when running Derby in embedded mode, you can enable or disable server-side user authentication when using the Derby Network Server. However, the default security mechanism (CLEAR_TEXT_PASSWORD) requires that you supply both the user name and password.

In addition to the default user name and password security mechanism, com.ibm.db2.jcc.DB2BaseDataSource.CLEAR_TEXT_PASSWORD_SECURITY, Derby Network Server supports the following security properties :

• UserID (com.ibm.db2.jcc.DB2BaseDataSource.USER_ONLY_SECURITY)

  When using this mechanism, only the user property needs to be specified.

• Encrypted UserID and encrypted password (com.ibm.db2.jcc.DB2BaseDataSource.ENCRYPTED_USER AND_PASSWORD_SECURITY)

  When using this mechanism, both password and user are encrypted.

The user's name specified upon connection is the default schema for the connection, if a schema with that name exists. See the "User Names and Schemas" section in the Derby Developer's Guide.

If you specify any other security mechanism, you will receive an exception.

To change the default, you can specify another security mechanism as a property or on the URL (using the securityMechanism attribute) when making the connection.

When user authentication is enabled in Derby, you have the option of using the default security mechanism, user name and password, or of specifying that the security mechanism be encrypted user and password.

When user authentication is turned off in Derby, you can use any of the security mechanism options.

A user and password need to be provided for all security mechanisms except USER_ONLY_SECURITY. However, because user authentication is disabled in the Derby server, the user name and password you supply does not have to be one recognized as valid by Derby.

To use encrypted user id and password, you need to have IBM JCE (Java Cryptography Extension) 1.2.1 or later. You can use it with any versions of IBM or Sun's Java^(TM) 2 Platform, Standard Edition, v 1.2 (J2SE).

IBM JDK 1.4 or later comes with IBM JCE, so there's no separate IBM JCE installation needed. For earlier versions of IBM JDK or other JDK's:

1. Copy the IBM JCE jar files to the jre/lib/ext directory of the IBM SDK's installation home. The jar files include: ibmjceprovider.jar, ibmjcefw.jar, ibmpkderby.jar, and ibmpkcs11.jar.
2. Modify the java.security file under jre/lib/security, in the list of providers (and preference order) section, change it to be:

   security.provider.1=sun.security.provider.Sun
   security.provider.2=com.ibm.crypto.provider.IBMJCE
   

   Note that you need both lines (if you are installing the IBM JCE on a Dun JDK) and that order is important.

3. During JDBC connection using JCC, specify 9 as securityMechanism in the connection property to use encrypted user id and password.

   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 the bootPassword. To remove access from the encrypted database, use the dbshutdown option to shutdown the database.

   The Network Server uses IBM JCE 1.2.1 to do the encryption, as does the IBM DB2 JDBC Universal Driver.