Database Setup¶
This chapter is out of date and is no longer being maintained. It is only being retained for historical purposes. The replacement chapter can be found in Database Setup.
GES: I have copied this from the “Installation” chapter of the FWD Developer Guide. It is a good start, but it is not 100% done. Here is a list of things that definitely need some attention:
- H2 support (the current content is PostgreSQL only and it wasn't updated to mention H2) - these changes should be flowed back to the “Installation” chapter of the FWD Developer Guide
- PostgreSQL production configuration parameters (create a new section of this document and make a table in there with a row for every postgresql.conf setting listed in the Database Checklist chapter. Move those rows to this new table and just leave a single row in the checklist which highlights that the implementer must review the dbms configuration parameters and then select/implement appropriate values. Make sure there is a column in the new table that summarizes the purpose of the parameter. Add a column that summarizes the calculation for setting a reasonable value.
- there may be things to move here from the database checklist chapter
- make sure all chapter references are correct (since this was previously in the Dev Guide, the chapter references are all relative to that book, wherever possible we want the chapter references to be to this book, but if the content does not exist in this book and should exist in another book then we need to point there)
- carefully review the p2j_project_guide.html, Appendix E, section "Special Preparation on Linux - Collation Fix"; make sure that any unique/useful content there is integrated here
- discuss (with Eric) which version of PostgreSQL should be documented here; see this email for some useful info:
-------- Original Message --------
| Subject: | progress 8.2 problem |
|---|---|
| Date: | Mon, 06 Feb 2012 13:37:50 +0400 |
| From: | Stanislav Lomany <svl@goldencode.com> |
| To: | Eric Faulhaber <ecf@goldencode.com>, Greg Shah <ges@goldencode.com> |
Guys,
I've encountered a problem: I created a new operation in Majic, andtime to open that operation is about 2 minutes (on my computer). Finally
I've figured out that the query "select id from notes where notes.key is
null and notes.seq = 1" runs about 30 seconds. The problem is that,
according to forums, postgresql <=8.2 cannot search for null values
using indexes. We probably should recommend in the documentation to use 8.3.
On lightning this query runs significantly faster.Thanks,
Stas.
-------- Original Message --------
| Subject: | Re: progress 8.2 problem |
|---|---|
| Date: | Mon, 06 Feb 2012 12:58:06 -0500 |
| From: | Eric Faulhaber <ecf@goldencode.com> |
| Reply-To: | ecf@goldencode.com |
| Organization: | Golden Code Development |
| To: | Stanislav Lomany <svl@goldencode.com> |
| CC: | Greg Shah <ges@goldencode.com> |
Stas,
At this point, I would say there's no reason to recommend less than 9.1. We will soon be adding features (mixed direction indexes, for instance) that are not supported by 8.x.
Thanks,
Eric
- review the com.goldencode.p2j.persist.pl JavaDoc package summary and integrate any useful content here
- review the PL/Java documentation and the building PL/Java section in the Developer Guide; make sure that the configuration/setup information in this chapter is up to date, comprehensive and accurate;
- we need to add a section in this chapter that documents how to build a new version of PL/Java, this is especially important when one needs to match a specific 32-bit or 64-bit production JVM but there is no such build of the proper PL/Java version already available; the PL/Java build must match the JVM that will be used inside PostgreSQL to run the code!
- add a section discussing the setup of the proper user account in the database (that userid and password will be configured in the directory for the application server's connections to the database); the same user account is used for all connections; please ask Constantin and Eric about this topic and where you can find additional information about our approach, the requirements that must be met and the issues to consider
- add a section discussing the security setup for the user account; this text may be helpful but it is certainly incomplete:
Security Concern: if the database user has superuser rights in PostgreSQL, then it is critical that the port which PostgreSQL uses (5432 unless otherwise set) is only accessible to the FWD application server and to privileged systems. No other systems should be able to access this port. This is not a recommended approach, but if all testing has occurred only with this configuration, then changing this for production is ill advised without additional testing of the final production configuration.
- Eric mentioned some recommended setup of the pg_hba.conf file to configure the user account security/connection security; please find out where that information might be recorded and write an enhanced version here, including production system considerations; if there is a development/testing environment approach that differs from the production system approach, then please add a similar section to the developer guide to explain the non-production particulars
- add a connection security section to this document; here are some important points to consider: we don't yet support encrypted connections but PostgreSQL does support it; if encrypted connections are needed changes will have to be made to <ask_eric_for_a_high_level_list_of_changes>; we use 1 user/database account for all the shared hibernate connections; you should not use the “trust” method of connection security in production environments; make pg_hba.conf parameter recommendations here
- explain that the database configuration for the FWD application server is not handled in this chapter, to find information on that topic, see the “Persistence Configuration” section of the Database chapter of Part 2 of this book (not yet written)
At this time, the FWD project supports PostgreSQL and H2 as its backing Relational Database Management System (RDBMS). While PostgreSQL is successfully used in production, at this time H2 is intended to be used only in testing or development environments. Additional database types will be supported as customer demand requires.
PostgreSQL¶
At this time, the FWD project supports PostgreSQL as its backing Relational Database Management System (RDBMS). Additional database types will be supported as customer demand requires.
To ensure the behavior of the new system matches that of the old as closely as possible, certain legacy features must be supported by the database server in the converted application. These features require special attention when installing the database server.
For instance, a Progress database by default will sort string data differently than a standard PostgreSQL database for the same locale. To make PostgreSQL sort these data in a similar manner to the pre-conversion system, it is necessary to install a custom locale at the operating system level, which the PostgreSQL database cluster can then use to collate its string data.
Likewise, built-in functions used in the where clauses of 4GL data access language statements (e.g., FOR, FIND, etc.) require special consideration. These functions are converted to user-defined functions within the Hibernate Query Language (HQL) queries used by the converted application. This approach presumes support for user-defined functions exists in the backing database. To provide such support, FWD relies upon PL/Java, an open source project which enables Java as a procedure language within a PostgreSQL database.
Step 1: Install a Custom Locale¶
In order to ensure that text data sorts properly (compatibly with the Progress database sorting), a custom locale must be installed.
The PostgreSQL database cluster used for a converted application must be initialized using a custom locale which defines how FWD databases should sort textual data in indexes and query results. The FWD project provides multiple custom locale for this purpose. The default is en_US.iso88591_fwd_basic since 2021.07.16. One specific to the application must be chosen and installed into the Linux operating system on the database server machine, before the PostgreSQL FWD cluster is created. This locale is intended to mimic, as closely as possible, a Progress database's default collation behavior using the ISO-8859-1 character set. Only this default case is supported at this time, and dynamic collation changes made by individual 4GL language statements currently are not supported by FWD.
The following instructions will install this onto a Linux system (at this time, there is no analog to this procedure for the Windows operating system).
- Ensure the Linux package which provides the
libclocale database sources is installed on the target machine. - Run
localedef --helpand review the bottom of the output to determine which directories contain these source files (as well as the path which contains the system's binary locales). The source will often reside in some derivative ofi18n, such as/usr/share/i18n. The binaries will often reside in subdirectories of/usr/lib/locale. - Login as the system's superuser.
- Change to the directory containing the charmap files (e.g.,
/usr/share/i18n/charmaps). Locate the archive containing the ISO-8859-1 charmap definition. Decompress it if necessary (it may begzip'd or otherwise archived). - Copy the custom FWD locale source file from the
p2j/localesubdirectory into the system's locales source directory (e.g.,/usr/share/i18n/locales). - Run the locale compiler (the following lines should be emitted as a single command on one line):
Step 2: Install PostgreSQL Database Server Software¶
Install the PostgreSQL server and client software. The PostgreSQL database version 9.x ( 9.1 or higher recommended) will serve as the back end to your converted application. The simplest way - recommended for test and development environments at a minimum - is to use your platform's package manager to install a binary distribution of the PostgreSQL server. Be sure to install client software at the same time, which you will need for database administration, testing, ad-hoc queries, etc. This document assumes the psql client is installed, but other PostgreSQL client software can be used as well.
If you have a need to compile PostgreSQL with custom settings, you will need to consult the PostgreSQL documentation, as that requirement is beyond the scope of this document.
In either case, stop before you initdb a new cluster.
Step 3: Create a FWD-Specific Database Cluster¶
You will create a cluster which is specifically for use with your converted FWD application. It should be separate from any database cluster used for non-FWD applications, because it will be built atop of one of the custom locale, (like en_US.iso88591_fwd_basic described above). Thus, it will collate string data like a Progress database, which may not be appropriate for other applications.
If you installed the PostgreSQL server software from a binary package, the package probably created a default database cluster. We will not use this default cluster, since we have to create a cluster which uses the custom locale described above. A locale cannot be applied to a PostgreSQL cluster once it has been created, so we must create a new cluster. In this case, consult the packager's documentation (or the postgresql.conf file if a default cluster was installed) to determine the data subdirectory in which PostgreSQL stores its databases.
If you built PostgreSQL yourself, and installed it from scratch, take note of the data directory you specified. You will need it to initialize a cluster.
PostgreSQL provides the initdb utility to create a new database cluster. We need to specify an extra option to this utility, to override the default locale with the en_US.iso88591_fwd_basic locale we installed previously:
su - postgres
initdb --locale=en_US.iso88591_fwd_basic -D {data_dir}
where {data_dir} is your data subdirectory.
Step 4: Create a Database User Account¶
Create a database user account with which your application will access the database. The converted application uses a single database user for all database access required by application business logic. Thus, each user of the application will not have a corresponding database user account. User and group accounts and application security in general are managed at the application server, not at the database.
The database user you create here should have sufficient rights to log into the database and to read and write normal application data, but should not have superuser rights (i.e., should not be able to create/drop databases, grant/revoke privileges, etc.). The postgres user is not re-used for this purpose, because that account does have superuser rights to the database.
PostgreSQL provides the createuser and dropuser utilities to manage database roles and privileges. Please consult the PostgreSQL documentation for instructions on the use of these utilities and/or the DDL statements used to manage user accounts.
Security Concerns:¶
If the database user has superuser rights in PostgreSQL, then it is critical that the port which PostgreSQL uses (5432 unless otherwise set) is only accessible to the FWD application server and to privileged systems.
No other systems should be able to access this port. This is not a recommended approach, but if all testing has occurred only with this configuration, then changing this for production is ill advised without additional testing of the final production configuration.
Step 5: Install User Defined Function (UDF) Support¶
Install PL/Javafor Progress-compatible, built-in function support in database queries.
PL/Java enables the use of Java as a procedure language within a PostgreSQL database. This allows the definition of User Defined Functions (UDFs) implemented in Java, for use in SQL statements. This is necessary for FWD to execute queries containing the equivalent of Progress built-in functions. To ensure the converted application behaves identically to the original application, the FWD runtime environment provides compatible Java implementations of Progress built-in functions, such as LOOKUP, NUM-ENTRIES, and so forth. These functions can be used in where clauses for data access language statements like FIND and FOR, and the conversion process will generate Data Manipulation Language (DML) statements which expect these functions to be executed within the context of those queries at the database server. Accordingly, we need a way to execute the compatible Java implementations of these functions at the database server, which requires Java be available as a procedure language within PostgreSQL.
This architecture is much more efficient than executing the functions at the application server. The latter approach would require that we return a superset of the possible results from the database server to the application server, then execute the built-in functions at the application server to perform the final step of filtering. In most cases, this would involve pushing significant amounts of data between database server and application server - an expensive operation - only to throw away all of those results which do not match the criteria specified by the built-in function portions of the query. With PL/Java, this filtering is performed at the database server instead.
There are two phases of enabling FWD's built-in function support. The first, described in this section, is to install the PL/Java software on the database server. This provides the basic support of defining and enabling Java as a procedure language in PostgreSQL. The second phase is to install a FWD-specific jar file into each database which requires Progress-compatible, built-in function support. That step is described in the Data Migration chapter of this handbook.
Install PL/Java¶
PL/Java is a third party, open source project (http://pgfoundry.org/projects/pljava/), which is not shipped with the FWD distribution. It is comprised of a Java component, which is platform neutral, and a platform-specific component, which must be binary-compatible with the version of PostgreSQL you are using. The binary distribution of PL/Java version 1.4.3 which is available at the time of this writing is compiled for use with the 32-bit version of PostgreSQL 9.1. A more recent snapshot version is also available at this time (*snapshot.20120525).
If you are using a different version of PostgreSQL (including the 64-bit version), you will need to build PL/Java on the machine on which PostgreSQL is installed.
PL/Java build PrerequisitesIn order to build PL/Java from sources you will need the following software to be installed:
• PostgreSQL >=9.1 (no PostgreSQL source is needed).
• Java runtime 1.6.x. Also make sure that JAVA_HOME environment variable is set.
• Make sure that the pg_config utility is installed and is available in your search path. If you have installed
PostgreSQL using a software package manager (rpm, deb etc.), then pg_config most likely will have been installed as part of the PostgreSQL server development package.
Building PL/Java¶
It is recommended to build the latest PL/Java for your machine instead of using the binary-compatible version, this is necessary if you intend to use 64 bit JVM or PostgreSQL.
You can download the latest source code from the download area of the PL/Java project: http://pgfoundry.org/frs/?group_id=1000038.
At the moment this document was written the latest version of PL/Java is a snapshot pljava-src-snapshot.20120525.0.tar.gz and can be built using Java 1.6 without having to make a workaround and is also compatible with the newer versions of PostgreSQL 9.x .
In order to compile sources, just go to the root directory of the sources (where main Makefile resides) and run
make command.
*Building PL/Java for older versions*¶
Note: It is not recommended to use older versions of PL/Java (1.4.2 might not be compatible with PostgreSQL 9.1 but 1.4.3 is ).The following is reference material to see how older version were built.
Older versions of PL/Java 1.4.2 and 1.4.3 can not be compiled with Java 1.6. In order to solve this problem, the source code with the tag java6 should be extracted from the project CVS.
Use these commands to do this:
cvs -d :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/pljava login
(when prompted for a password, press Enter)
cvs -d :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/pljava checkout -r java6 org.postgresql.pljava
In order to compile sources, just go to the root directory of the sources (where main Makefile resides) and run
make command.
In order to compile sources, just go to the root directory of the sources (where main Makefile resides) and run make command.
After that you should be able to pick build/pljava.jar and build/objs/pljava.so.
Configure PL/Java for PostgreSQL¶
Once you have downloaded and unpacked the PL/Java binary distribution (or built PL/Java from source), you will need to configure PostgreSQL to use it. PL/Java provides a native library (pljava.so) which must be made accessible to the PostgreSQL server. You can either copy this file alongside PostgreSQL's other libraries in PostgreSQL's lib subdirectory, or you can tell PostgreSQL where to find it. The latter is accomplished by editing the dynamic_library_path setting in the postgresql.conf file. By default, this setting reads:
#dynamic_library_path = '$libdir'
Change it to:
dynamic_library_path = '$libdir:{pljava_home}'
where {pljava_home} is replaced with the location of the pljava.so file on your database server system. Note that this is a path name, not a file name; it is the directory containing the pljava.so file, not the file name itself. While you are editing postgresql.conf, add the following settings as well (customarily located in the CUSTOMIZED OPTIONS section):
custom_variable_classes = 'pljava'
pljava.classpath = '{pljava_home}/pljava.jar'
where {pljava_home} is replaced with the location of the pljava.jar file on your system.
Finally, you may want to limit the amount of heap each PL/Javalaunched JVM can use, since you will have one
JVM instance running per connection which uses Java UDFs. To do this, add a line to the same section in
postgresql.conf:
pljava.vmoptions = '-Xmx8m'
The option(s) specified here will be passed by PL/Java to the JVMs it launches. 8 megabytes should be more
than enough to support all of the standard UDFs that are part of FWD, though you should experiment with this
number, especially if you implement your own UDFs
Next, we need to ensure some environment variables are set for the postgres user (the user which launches the database server). This provides the PL/Java software running in the server's context with the information necessary to find and launch the Java Virtual Machine, in order to execute Java UDFs. These environment variables are most easily set by modifying the shell configuration (e.g., .bash_profile if using bash) for the postgres user, which ensures they are available to the postmaster (the main PostgreSQL server process). Add the following lines to the shell configuration file:
JAVA_HOME={location_of_JRE_installation}
CLASSPATH=
LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/i386:$JAVA_HOME/jre/lib/i386/server
{location_of_JRE_installation} should be set to the top-level directory of the Java 6.0 JRE installed in the previous step.
CLASSPATH is nulled out to ensure the JVM launched by the PostgreSQL back end process does not inherit a default CLASSPATH setting for the machine, but rather relies solely upon the classpath set when installing the p2jpl.jar (discussed in the Data Migration chapter).
LD_LIBRARY_PATH is set to allow PL/Java to find the libraries needed to launch the JVM. Note that we specify the HotSpot server compiler (.../jre/lib/i386/@server@) when setting this variable. This is intended to improve performance with the assumption that a JVM, once launched, is long-lived due to connection pooling. Note also the reference to i386, which presumes a 32-bit JVM. Adjust this setting accordingly if using a 64-bit JVM.
On some Linux distributions, there may be a better place to set environment variables used by PostgreSQL. For example, Ubuntu-based PostgreSQL packages create an environment file alongside the standard configuration files specifically for this purpose. Consult your platform-specific documentation for these particulars.
The changes made in this section require a restart of the database server to take effect
Bootstrap PL/Java for a Database¶
The following commands must be executed (once and only once) against each PostgreSQL database which will use Java UDFs:
cd /opt/pljava
psql -U {super_user} -f install.sql {database_name}
where:
{super_user} is a user with rights to modify the target database
{database_name} is the target database
Configure the Server¶
We make one deviation from the out-of-the-box server configuration, which is to disable local access and enable access only via TCP sockets. In the /usr/local/pgsql/data/pg_hba.conf file (assuming the default installation location), change the following section:
# "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust
to:
# "local" is for Unix domain socket connections only #local all all trust # IPv4 local connections: host all gc 127.0.0.1/32 md5 # IPv6 local connections: host all gc ::1/128 md5
To make these changes take effect, shut down the server (with Ctrl+C) and re-launch it.
PostgreSQL production configuration parameters¶
Preparing the PostgreSQL database for production requires to change values of the parameters from porgresql.conf file in order to improve performance.
Below is a table with parameters value changes that influence the most, or are vital into getting information for an optimal performance configuration.
The information from the table is according to PostgreSQL 9.1 User Manual.
| Parameter change | Purpose of parameter | Estimate value considerations |
|---|---|---|
increase max_connections |
Determines the maximum number of concurrent connections to the database server.This parameter can only be set at server start.Increasing this parameter might cause PostgreSQL to request more System V shared memory or semaphores than your operating system's default configuration allows. |
The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.Increasing this parameter might cause PostgreSQL to request more System V shared memory or semaphores than your operating system's default configuration allows. ensure PostgreSQL cluster supports enough connections for peak use |
increaseshared_buffers (increase SHMMAX if necessary) |
Sets the amount of memory the database server uses for shared memory buffers. The default is typically 32 megabytes (32MB).This parameter can only be set at server start.Increasing this within the correct limits will improve the response times. |
A reasonable value for shared_buffers is 25% of the memory in your system. There are some workloads where even large settings for shared_buffers are effective, but because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount. Larger settings for shared_buffers usually require a corresponding increase in checkpoint_segments, in order to spread out the process of writing large quantities of new or changed data over a longer period of time. |
increase work_mem |
Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to one megabyte (1MB).Increasing this up to a certain point will ncrease query response time |
Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem@; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries. Estimated production value: ~256 MB |
increase wal_buffers |
The amount of shared memory used for WAL data that has not yet been written to disk. This parameter can only be set at server start. Increasing this might improve write performance |
The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers but not less than 64kB nor more than the size of one WAL segment, typically 16MB. This value can be set manually if the automatic choice is too large or too small, but any positive value less than 32kB will be treated as 32kB.Setting this value to at least a few megabytes can improve write performance on a busy server where many clients are committing at once. The auto-tuning selected by the default setting of -1 should give reasonable results in most cases. |
increase maintenance_work_mem |
Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 16 megabytes (16MB).Larger settings might improve performance for vacuuming and for restoring database dumps. |
Since only one of the maintenance operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem (twice as much as work_mem is a good value) |
effective_cache_size |
Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be use |
When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. The default is 128 megabytes ( 128MB).Depending on how much you want to use index scans over sequential scans and the system' s memory value you can set the value even to more than 50% of the system's memory |
postgresql.conf - reduce random_page_cost |
Sets the planner's estimate of the cost of a non-sequentially-fetched disk page | Reducing this value relative to seq_page_cost will cause the system to prefer index scans; raising it will make index scans look relatively more expensive. You can raise or lower both values together to change the importance of disk I/O costs relative to CPU costs(which are described by cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost) |
postgresql.conf - increase checkpoint_warning |
Write a message to the server log if checkpoints caused by the filling of checkpoint segment files happen closer together than this many seconds (which suggests that checkpoint_segments ought to be raised). |
This parameter can only be set in the postgresql.conf file or on the server. The default is 30 seconds (30s). Zero disables the warning. This parameter can only be set in the postgresql.conf file or on the server |
postgresql.conf - increase checkpoint_segments |
Maximum number of log file segments between automatic WAL checkpoints (each segment is normally 16 megabytes). This parameter can only be set in the postgresql.conf file or on the server command line. |
The default is three segments. Increasing this parameter can increase the amount of time needed for crash recovery. Increasing the number of checkpoint segments is corelated with increasing shared_buffers and can improve performance while loading |
postgresql.conf - increase default_statistics_target |
Sets the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS. |
Larger values increase the time needed to do ANALYZE, but might improve the quality of the planner's estimates. The default is 100. |
Connection Security¶
We don't yet support encrypted connections but PostgreSQL does support it; if encrypted connections are needed changes will have to be made to <ask_eric_for_a_high_level_list_of_changes>; we use 1 user/database account for all the shared hibernate connections; you should not use the “trust” method of connection security in production environments; make pg_hba.conf parameter recommendations here
H2 server¶
Similar to the PostgreSQL server, using H2 as a backend for a permanent database requires some special attention to certain issues. These issues are related to how H2 sorts its strings, the unknown value and how built-in functions are registered.
Install FWD Collation Support ServicesIn order to ensure that text data sorts properly (compatibly with the Progress database sorting), the JVM in which
H2 is running must have access to custom collation rules. Follow the instructions in the Install FWD Collation Services for H2 Database section above. As you do this, keep in mind that if you are running H2 as a standalone database server, you must install the p2jspi.jar file in the Java extension directory for the JVM which will be used for the H2 server, which is not necessarily the same JVM running the FWD application server (it may be on a different machine altogether).
After the p2jspi.jar file is installed for the appropriate JVM, the H2 database must be initialized so that the en_US_FWD collator provider will be used. This is done by setting the actual collation used by the database, using the SET COLLATION command:
SET COLLATION en_US_FWD;
The default setting for the collation can be found in the p2j.cfg.xml file, under the cfg/schema/namespace/dialect-specific/collation node. This value is used during M0 to generate the set collation DDL for the H2 dialect.
<schema metadata="standard" >
<namespace name=”...”>
...
<dialect-specific name="h2">
<parameter name="collation" value="en_US_FWD" />
</dialect-specific>
</namespace>
</schema>
Currently, only en_US_FWD collation is supported.
This command must be executed before any table is created in the database. During DDL generation, the command is automatically emitted so that is executed after all tables were dropped and before any table is created. If you are not using the schema script generated by FWD during the M0 portion of the conversion process, then you must make sure that the collation is set before any table is created in the database.
Sorting Unknown Value¶
Progress' unknown value is mapped by FWD to the null value in an H2 database. By default, for an ascending order, H2 sorts the null value low (i.e., it places the rows with nulls in the sorted column at the beginning of the result set). This contradicts the Progress behavior, which requires unknown value to be sorted 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 cause 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 the H2 server (if multiple connections are required to the database):
java -Dh2.sortNullsHigh=true …
User Defined Function (UDF) Support
Being written in pure Java, the Java support for user defined functions is available by default in H2. During the M0 part of the conversion, FWD will automatically output the DDL necessary to create the aliases for the userde fined functions, when the H2 dialect is used. If the permanent database is used in embedded mode, no special setup is required; but, if a separate H2 server is used, the p2jpl.jar must be added to the H2 server JVM's classpath. See the Installation chapter of the FWD Conversion Handbook on how to start the H2 server.
Creating a Database¶
In H2, a database is created when the first connection is made, if it does not already exist. The connection which creates the database is also responsible for creating a user and setting its password; until changed, all subsequent connections will have to authenticate using that user's name and password.
See the Database Migration section of the Database creation and import chapter for details on how the H2 database can be created during schema installation.
© 2004-2017 Golden Code Development Corporation. ALL RIGHTS RESERVED.