Project

General

Profile

Wiki » Documentation » Installation Configuration and Administration Guide »

Database Server Setup for H2

At this time, the FWD project can use PostgreSQL, Microsoft SQL Server and H2 as its backing Relational Database Management System (RDBMS). H2 can be configured as a primary database for application data (also referred to as a "permanent database"). Since H2 is the easiest database to set up and use with FWD, this can be convenient for testing and development. However, currently H2 is not recommended for production use as a primary application data store.

H2 is the database engine for FWD's implementation of temporary tables (i.e., private, local tables converted from the ABL's TEMP-TABLE construct). In addition, H2 is used internally within the FWD application server to support most legacy metadata access, and for other implementation details.

Building the H2 Database from Source

At the time of this writing, the H2 database in use in FWD has been patched with fixes required by FWD. In the future, these fixes may be integrated back into the H2 Database open source main code base (if accepted by the H2 project team), so that patching of a future version is no longer necessary.

For now, the FWD build process automatically downloads a previously built, patched jar file from the FWD project's binary artifact repository. So, most users will not need to patch and build the H2 database from source. If you do have this need, please refer to Patching and Building Customized Third Party Libraries.

Configuration

There are three areas that must be configured for FWD's use of the H2 database, even if one does not use it as a primary data store for application data:
  • how H2 collates (sorts) its strings;
  • how H2 sorts unknown/null values;
  • the list of built-in functions.

H2 Collation Support Services

To match sorting order of ABL, H2 database engine uses a custom instance of J2SE's Collator abstract class. FWD uses a custom implementation of java.text.spi.CollatorProvider to enable H2 to sort text data the same way Progress would. This resource is shipped with FWD and is built into the p2jspi.jar file. It must be installed and made available to the FWD runtime using the Java Extension Mechanism. This provides J2SE's Locale and related classes with access to the custom collation services that are needed by FWD.

To enable this support, the p2jspi.jar file must be copied from the $P2J_HOME/build/lib directory to the Java Extension directory for the target JVM. For a Sun JVM, this directory is located at $JAVA_HOME/jre/lib/ext, regardless of the operating system. Note that administrator rights may be required, depending upon where the JVM is installed. This step is needed only one time. The content of p2jspi.jar file seldom changes, but when this happens, the jar must be updated. Please see String Collation Service Provider Installation for more information.

The FWD project implicitly uses H2 in embedded mode for the _temp (temp-table) database. There is also an embedded "dirty database" instance in H2 for each permanent database (no matter what the database type) which exists for this FWD server. The dirty database is an implementation detail in FWD which emulates ABL transaction behavior; it is not exposed directly to application code and is only mentioned here because it has an H2 dependency. It is also possible to use H2 explicitly (in memory or as a standalone Java database server).

The _temp and dirty database cases default to use the en_US_P2J collation that comes from p2jspi.jar. This can be overridden in the directory. For permanent databases, this must be explicitly specified in the directory.

To setup a collation configuration entry in the FWD directory. The directory path is /server/standard/database/{database_name}/p2j/embedded-collation. The default setting should be en_US_P2J, as in:

<node class="container" name="p2j">
  ...
  <node class="string" name="embedded-collation">
    <node-attribute name="value" value="en_US_P2J"/>
  </node>
</node>

Please refer to Application Server Installation for additional details.

Sorting unknown (null) values in H2

By default, for an ascending order, H2 sorts the null s low (i.e. it places the rows with null s in the sorted column at the beginning of the result set). This contradicts the Progress behavior, which requires to sort the unknown values high when an ascending order is used (i.e. place them at the end of the result set). To solve this, H2 provides a system property - h2.sortNullsHigh - which, if set to true, will determine H2 to sort the null value high.

The recommended way of setting this property is by passing it as a parameter to the command used to start the FWD server (if the database is used in embedded mode) or H2 server (if multiple connections are required to the database): -Dh2.sortNullsHigh=true:

java -Dh2.sortNullsHigh=true ...

User Defined Function (UDF) Support

Since H2 is implemented in pure Java, the Java support for user defined functions is available by default. During the M0 part of conversion, FWD will automatically generate the data definition language (DDL) SQL statements necessary to create the aliases for the user-defined functions, when the H2 dialect is configured in the p2j.cfg.xml project configuration file. You can find more information on this file in Global Configuration section.

After a successful conversion, the file which contains the needed UDF DDL is the same file which contains the table DDL. It can be found at $P2J_HOME/ddl/schema_table_<custom_db>_h2.sql. The UDF DDL statements can be found at the beginning, between the SET COLLATION statement and the first CREATE TABLE statement.

The H2 database operates in several modes, including an embedded mode. The mode is determined by the database connection URL (/server/<custom_server>/database/<custom_db>/hibernate/url) used in FWD's runtime configuration. In embedded mode, the database resides in the same JVM process as the FWD application server. Since H2 is not recommended for production use, this tends to be the only mode used (e.g., for testing, development, proofs of concept, etc.).

For a permanent database used in embedded mode, no special setup is required. The UDF classes already are accessible from the FWD server's classpath. However, if a standalone H2 server is used, the p2jpl.jar must be available to H2 server JVM's process, so the UDF used in queries to be executed. Since p2jpl.jar cannot be installed using the Java Extension mechanism, the solution is to add the jar directly to the classpath of H2 standalone server process. One way to do this is to add the jar to $H2DRIVERS environment variable before starting the H2 database engine:

$ export H2DRIVER=$P2J_HOME/build/libs/p2jpl.jar
$ /usr/bin/h2/bin/h2.sh

Alternatively, one can use the command-line described in "Using the H2 server" task from H2 specific installation TODO: please pull out the specific details and put them here, the document being referenced is obsolete.

Creating a Database

For permanent databases, the database is created when the data is migrated. The process is described in detail in Importing to H2 TODO: this link doesn't exist.

There is no need to create databases for any of the uses of H2 noted above, which are internal to the FWD application server implementation, including temporary tables and metadata access. These databases are created as necessary by the FWD server.

© 2004-2017 Golden Code Development Corporation. ALL RIGHTS RESERVED.