Project

General

Profile

Wiki » Documentation » Installation Configuration and Administration Guide » Directory Configuration Reference »

Database Configuration

Older Versions

This section describes the runtime database configuration for FWD v4 and later, which changed substantially from older versions of FWD. For information on configuring the database for older versions, please refer to Database Configuration v3.

Search Algorithm

The database configuration can be located in different locations to differentiate the option as global for all servers or specific to a server. The search algorithm used for these options is the Server Search Algorithm.

The database nodes must be located in the global server location /server/default/database/<database_node>/ or in the per-server location:

/server/<server_id>/database/<database_node>/

where <server_id> is the name of the server and <database_node> is the JDBC database name.

Port Services Mappings

The port-services section maps a list of names to their TCP port numbers.

<node class="container" name="port_services">
  <node class="integer" name="my_first_service">
     <node-attribute name="value" value="3333"/>
  </node>
  <node class="integer" name="my_second_service">
     <node-attribute name="value" value="3343"/>
  </node>
</node>

This is used only by database connection setup.

Where the 4GL uses the services file to process services names to port mappings, FWD uses this data to create a logical connection to a database using the specified parameters. The 4GL statement which defines this is the CONNECT statement, and it will always result in a call to the ConnectionManager.connect(...)

The CONNECT statement uses a series of parameters among which is :

-S { port-number | service-name }

This specifies the port number or service name used to connect to a FWD server running on the specified host. If the host is specified, then it is required to specify either the port or service name. In the case the argument is not a number the directory will be searched to find the port_services node that has the specified name.

Persistence

The persistence node of the directory controls whether persistence (i.e., database) support is active, and whether foreign keys are in use. By default, persistence support is active and foreign key support is disabled. Since it is unusual to change either of these defaults, this node is not present in the directory at all by default.

The active parameter controls whether runtime persistence support is turned on. It would be an uncommon ABL application which does not use a database at all, but persistence support can be disabled. This may be useful, for instance, when running simple test cases which do not use a database. The following setting under the standard/runtime/ directory path disables it:

<node class="container" name="persistence">
  <node class="boolean" name="active">
     <node-attribute name="value" value="FALSE"/>
  </node>
</node>

Note that when persistence is disabled, the FWD server will log a warning at startup.

The foreign-keys parameter controls whether the conversion will calculate and enforce referential integrity using “natural” joins. A natural join is one where two tables are linked by an exactly matching index specification which is defined as unique in at least one of the tables. The default value of foreign-keys parameter is FALSE.

This parameter is deprecated and should not be used. Traditionally, Progress code has not had any concept of referential integrity. This means that normal relational database management system (RDBMS) rules may be and probably are broken in most applications. As one example, in Progress the order of deletes between two tables is not important (it can be in any order), because Progress traditionally has not known about nor enforced any relationship between tables. When adding referential integrity where there was none in the past, such problems can cause a serious deadlocking bug. There are known issues with the current implementation of foreign keys such that many converted Progress applications would not work properly if this is enabled. Some of these problems can likely be resolved in the FWD runtime, but they do still exist at the time of this writing.

Example:

<node class="container" name="persistence">
   <node class="boolean" name="foreign-keys">
      <node-attribute name="value" value="FALSE"/>
   </node>
</node>

Dirty-Share

Dirty-Share is a complex feature in FWD to replicate these two aspects in 4GL: intra-session and cross-session visibility of new/changed records described below. To be on par with 4GL behavior, FWD should technically have both "intra-session dirty-share" and "cross-session dirty-share" features enabled.

To implement this feature, FWD manages an in-memory database named "dirty-share database" that stores new/changes records that were not yet flushed or committed, but can be found by queries of the same session (intra-session) or another session (cross-session). In general, this feature kicks in for FIND statements, FOR EACH blocks, queries or other mechanisms that query the database. When such database access is triggered, FWD has the algorithms to resolute the correct records, either from dirty database or physical database.

Due to the design of the "dirty-share database", there are obvious limitations of the support for dirty-share: nested CAN-FIND is not honoring dirty-share, performance is usually affected by the need to constantly check dirty-share database for updates.

Cross-session dirty-share

The support for this feature in FWD is partial and is going to be dropped, don't rely on it.

The OpenEdge database has a transaction isolation problem caused by its core design being tightly coupled to index processing. When one session creates a new record and fully updates an index, this updated index is immediately visible to other sessions, even if the record is not fully initialized or it has not yet been committed (the transaction is still open). This means that other sessions can see these new records before they are committed. This is a bad practice. It doesn't fit the modern database transaction isolation policy. It has serious negative security implications. It should not be used. Yet, some 4GL applications intentionally or unintentionally rely upon this behavior to coordinate behavior across sessions. We call this cross-session dirty sharing. We have seen this behavior used in multiple applications to calculate a unique id at the time a record is created. This usage was implemented before OE supported sequences.

  • dirty-cross-share flag is used to enable/disable this feature. It is FALSE by default.
  • force-dirty-cross-share flag it used to force all tables to be subject to cross-session dirty-share mechanism. By default, only tables hinted as "dirty-read" as subject to "cross-session dirty-share" feature if enabled overall. This is FALSE by default.
    • this flag is ignored if dirty-cross-share is FALSE
  • dirty-share-global-notifications flag is used to allow one session to signal another that the "cross-session dirty-share" exposed a new record. With this enabled, FOR EACH queries of other sessions will start merging results from the physical database with the results in the dirty-share database in order to pick up these exposed new records. This leads to a severe performance downgrade as FOR EACH queries will pick records from the database one-by-one in order to be preemptive with the functionality of "cross-session dirty-share". This is FALSE by default.
    • this flag is ignored if dirty-cross-share is FALSE
Intra-session dirty-share

Most customers tend to rely on this feature and disabling it usually resulted in very early and obvious issues.

The 4GL exhibits some behavior that allows changes within a session to be visible to separate code (and buffers) elsewhere in the session. When a piece of code inserts or edits database fields that are indexed, those changes are immediately visible to upcoming queries on that fully updated index within the same session, even though some fields were not even set yet. This doesn't break transaction isolation like cross-session dirty sharing, but it allows manipulating partially updated records in-memory before flushing time and eventually WRITE trigger execution. We call this behavior intra-session dirty sharing.

  • dirty-intra-share flag is used to enable/disable this feature. It is TRUE by default.
  • force-dirty-intra-share flag it used to force all tables to be subject to intra-session dirty-share mechanism. By default, only tables hinted as "dirty-read" as subject to "intra-session dirty-share" feature if enabled overall. This is TRUE by default.
    • this flag is ignored if dirty-intra-share is FALSE
Dirty-share per table

In order to mark a table as ready for "cross-session dirty-share", use a <db-name>.schema.hints file to hint that a certain table is "dirty-read":

<hints>
   <schema>
      <table name="my-table" dirty-read="true">
      </table>
   </schema>
</hints>

This hint will be used by the conversion to emit:

@Table(name = "myTable", legacy = "my-table", dirtyRead = true)
How to configure dirty-share

Dirty-share is configurable from directory.xml file:

<node class="container" name="persistence">
   <node class="boolean" name="dirty-cross-share">
      <node-attribute name="value" value="FALSE"/>
   </node>
   <node class="boolean" name="dirty-intra-share">
      <node-attribute name="value" value="TRUE"/>
   </node>
   <node class="boolean" name="force-dirty-cross-share">
      <node-attribute name="value" value="FALSE"/>
   </node>
   <node class="boolean" name="force-dirty-intra-share">
      <node-attribute name="value" value="TRUE"/>
   </node>
   <node class="boolean" name="dirty-share-global-notifications">
      <node-attribute name="value" value="FALSE"/>
   </node>
</node>

Each option is described in the sections above.

Quick configurations:

  • Not having the options at all in directory.xml will lead to a good enough set-up (just like the one in the example):
    • the intra-session feature is enabled for all tables.
    • the cross-session feature is completely disabled: this may generate mismatches with 4GL behavior
  • Having all disabled will lead to better performance, yet it will completely stray away from 4GL functionality.
    • disabling all these will require a thorough code investigation to remove reliance on dirty-share
  • Having all enabled will lead to a full dirty-share feature overhead that is functionally the same as 4GL.
    • mind that "cross-session dirty-share" is partially implemented and is going to be unsupported, so this configuration is not recommended.

It is recommended to refactor the legacy code in order to avoid relying on cross-session dirty-share functionality as it is partially supported by FWD run-time and will be dropped.

Examples of dirty-share usage
  • Record ids that are (uniquely) assigned similarly how a sequence will do. Usually this happens in WRITE triggers and relies on cross-session dirty-share (identifying the absolute last id across all sessions, even if not committed).
    • These should be refactored to use sequences.
  • Mutex implementations that use a database table that is updated inside a transaction, but not committed. This relies on cross-session dirty-share to properly create / find / release mutexes without actually committing.
    • These should be refactored in Direct Java Code or find another way to make the mutexes visible cross-session without relying on dirty-share.
    • Such mutex work can be deferred to an appserver that has its own transaction and DB connection. This way, it can actually commit without spoiling the full transaction of the original business logic.

Batch Results size

Since FWD trunk revision 16488, a new feature was added: fetching the cached records in batches when the result set gets invalidated.

Example how to configure:

<node class="container" name="server">
    <node class="container" name="default">
        <node class="container" name="runtime">
        ...
            <node class="integer" name="batchResultsSize">
              <node-attribute name="value" value="100"/>
            </node>
        ...
        <node>
    </node>
</node>

This configuration is optional, with default value set to 100.

How to adjust this parameter:
  • if you encounter high database latency (iterating through a table with high number of records), try using a higher batch size.
  • if your batchResultsSize value is too high, your memory and Session cache will fill up, which will slow down your query performance.
  • if you want the performance prior revision 16488, use batchResultsSize 1.

Session Lifespan

This is an option that can be configured in directory.xml, it allows for any session to be reclaimed in a configurable time period after not being used.
It can be defined in the persistence container in the following way:

<node class="container" name="persistence">
    <node class="integer" name="session-lifespan">
        <node-attribute name="value" value="1000"/>
    </node>
</node>

The attribute value is measured in milliseconds. In the example above, a session can be reclaimed only if it didn't exceed 1 second of inactivity.

Each persistence context will have a SessionFactory instance associated which will manage the create/mark/reclaim/expire process of a session:
  • When a session is created, it will check for any session that can be reclaimed. It will create one if none can be reclaimed or return the existent reclaimable session.
  • When a session is not used anymore and needs to be closed, it is marked as reclaimable instead.
  • A session expires when the time period in which it can be claimed is exceeded. In this case, the session is closed.

All SessionFactory instances are registered to SessionFactory.SessionCloseThread which manages the closing of the sessions that exceed the time period in which they can be reclaimed.

Lifespan options:
  • If no option is set, the lifespan will be set to a default value (1 second).
  • If 0 is used as an option, the session will go through the reclaim process indefinitely. The managing thread will not start, but the SessionFactory instances will still be registered and deregistered. In this way, when the context is closed, the SessionFactory will be deregistered and any reclaimable session will be closed.
  • If a negative value is provided, session reclaiming will be disabled.
  • If a positive value is provided, it will represent the maximum time in which the session can be reclaimed.

Database Instances

Each database instance which the application can use must be configured via a database container, which at a high level looks like this:

<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="p2j">
      <!-- general settings -->
      ...
    </node>
    <node class="container" name="orm">
      <!-- ORM (Object-to-Relational-Mapping) settings -->
      ...
    </node>
  </node>

The name attribute in the second level container above with the value my_database corresponds with the physical database name in the legacy environment. This can be (but is not necessarily) different from the schema name and database instance name described in the sections below.

Each of the XML comments above are placeholders for nested containers with specific settings, possibly in more deeply nested containers. These settings are described in the various sections below.

P2J Section

The p2j container holds general purpose FWD database runtime settings:

  • schema (String) the name of the database's schema; typically, this is the name of the schema that was exported from the 4GL Data Dictionary. This can be, but it does not need to be, the same name as the name configured in the database container.
  • load_at_startup (boolean) determines whether the database is auto-connected (in the 4GL sense, not the JDBC sense) at server startup. If not auto-connected, it is assumed the original 4GL code used a CONNECT statement when this database needed to be connected. [TODO: this setting is obsolete; document new database-connections configuration]
  • embedded-collation (String) this is used to set collation rules. The default value is en_US@iso88591_fwd_basic. See the static initializer block of FwdCollatorProvider for other collations provided in FWD. This is used to set collation rules for H2 dialect. The other dialects are not affected.

Example:

<node class="container" name="database">
  <node class="container" name="my_database">
    ...
    <node class="container" name="p2j">
      <node class="string" name="schema">
        <node-attribute name="value" value="my_schema"/>
      </node>
      <node class="boolean" name="load_at_startup">
        <node-attribute name="value" value="TRUE"/>
      </node>
      <node class="string" name="embedded-collation">
        <node-attribute name="value" value="nl_NL@cp1252_fwd_basic"/>
      </node>
   </node>
   ...

ORM Section

The ORM section contains settings which allow FWD to map its persistence framework Java objects, which represent relational database constructs (e.g., tables, records, etc.), to the corresponding constructs in the backing database.

Dialect

The dialect container is used to specify the database dialect in use.

The levels of support for SQL standards among different database vendors vary, and each vendor may define extensions and features not specified by standards. To account for these differences in syntax, FWD implements an abstraction layer which requires the type of database dialect in use to be specified.

As of FWD version 4, the following dialects are provided:

Database FWD Dialect Name Notes
PostgreSQL com.goldencode.p2j.persist.dialect.P2JPostgreSQLDialect Recommended for production use
MariaDB com.goldencode.p2j.persist.dialect.MariaDbLenientDialect Recommended for production use
SQL Server com.goldencode.p2j.persist.dialect.SQLServerDialect Development was suspended a few years so the support is incomplete. We started working on this and expect to get it on par with the other, soon.
H2 com.goldencode.p2j.persist.dialect.H2Dialect Used heavily for testing and for FWD's temporary table support; not recommended for external database production use 
<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="string" name="dialect">
        <node-attribute name="value" value="com.goldencode.p2j.persist.dialect.P2JPostgreSQLDialect"/>
      </node>
      ...
Connection

The connection container is used to set up a JDBC connection to the permanent database as configured in the specified directory. The common path the connection directory nodes have in common is:

databaseDirNodePath + my_database + "/orm/connection/"

where databaseDirNodePath is the full database container path from the directory file and the my_database variable represents the name of the database.

The attributes of the connection container are:

  • driver_class : (String) the Java Driver class used to create the JDBC Connection
  • username : (String) the user name used to connect to the database.
  • password : (String) the password used to authenticate the user when connecting to the data base
  • url : (String) the jdbc url address of the database to which the connection is made. Note that the database name used in this URL does not need to match the physical database name configured in the database container; it is the name of the relational database instance to which JDBC connections will be made. For MariaDB, it is highly recommended to add useServerPrepStmts=true option in the URL in order to gain some performance improvements.
  • prepareThreshold (optional/Integer) : The threshold number of times a statement is called for which the query will be used as a named query, saving the execution plan at the database server (PostgreSQL only).

Example:

<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="container" name="connection">
        <node class="string" name="driver_class">
          <node-attribute name="value" value="org.postgresql.Driver"/>
        </node>
        <node class="string" name="username">
          <node-attribute name="value" value="my_db_username"/>
        </node>
        <node class="string" name="password">
          <node-attribute name="value" value="my_db_users_password_in_clear_text"/>
        </node>
        <node class="string" name="url">
          <node-attribute name="value" value="jdbc:postgresql://localhost/my_database_instance"/>
        </node>
        <node class="integer" name="prepareThreshold">
          <node-attribute name="value" value="1"/>
        </node>
     </node>
     ...
C3P0

The c3p0 section can be used to provide initialization data for the c3p0 database connection pool.

c3p0 is an easy-to-use library for making traditional JDBC drivers "enterprise-ready" by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2. In particular, c3p0 provides several useful services:

  • Classes which adapt traditional DriverManager -based JDBC drivers to the new javax.sql.DataSource scheme for acquiring database Connections.
  • Transparent pooling of Connection and PreparedStatements behind DataSources which can "wrap" around traditional drivers or arbitrary unpooled DataSources.

The attributes of the c3p0 container are:

  • minPoolSize (Integer) the minimum number of connections a pool will maintain at any given time.
  • maxPoolSize (Integer) the maximum number of connections the pool will maintain at any given time. Be sure your database configuration allows at least this many connections (it is a good idea to reserve a few extra connections which the pool will not use, for database administration).
  • aquireIncrement (Integer) the number of connections at a time c3p0 will try to acquire when the pool is exhausted.
  • maxIdleTime (Integer) the number of seconds a pooled connection will be available before it is released. Set to 0 for connections that do not expire.
  • maxStatementsPerConnection (Integer) the maximum number PreparedStatements a DataSource will cache per JDBC connection. The pool will destroy the least-recently-used PreparedStatement when it hits this limit. Statement pooling is optional and may help or hurt performance, depending on the particular application. Remove this parameter or set it to 0 to disable statement pooling. This option SHOULD be disabled if MariaDB driver and useServerPrepStmts option are used.

Please see also https://www.mchange.com/projects/c3p0/#configuration for more details on the above configuration options, which are passed through to c3p0.

Example (actual values should be determined by the needs of a particular application; those which affect performance should be determined through testing/profiling):

<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
    ...
      <node class="container" name="c3p0">
        <node class="integer" name="minPoolSize">
          <node-attribute name="value" value="20"/>
        </node>
        <node class="integer" name="maxPoolSize">
          <node-attribute name="value" value="400"/>
        </node>
        <node class="integer" name="acquireIncrement">
          <node-attribute name="value" value="20"/>
        </node>
        <node class="integer" name="maxIdleTime">
          <node-attribute name="value" value="600"/>
        </node>
        <node class="integer" name="maxStatementsPerConnection">
          <node-attribute name="value" value="100"/>
        </node>
      </node>
      ...
C3P0 and PGsql configuration for PASOE

Definitions & Formulas:

  • maxConnectionsPerAgent = number of worker threads serving requests
  • batchProcesses = number of batch processes per tenant

Max pool size per tenant:

  • maxPoolSize = maxConnectionsPerAgent + batchProcesses
    Max pool sizes for the 3 databases:
    • Landlord DB: fixed maxPoolSize = 15 (fixed)
    • Tenant DB: maxPoolSize as above
    • Tenant_shared DB: maxPoolSize as above
PostgreSQL max_connections formula: 
  • max_connections=<landlord maxPoolSize> + N × <tenant maxPoolSize> + <tenant_shared maxPoolSize> + <superuser connections>
    Where:
    • N = number of tenants
    • superuser connections = 10

With the current directory configuration:

maxConnectionsPerAgent = 16
batchProcesses = 4 → maxPoolSize = 16 + 4 = 20

landlord maxPoolSize = 15

tenant_shared maxPoolSize = 20

superuser connections = 10

One tenant:
max_connections = 15 + 20 + 20 + 10 = 65

Tenants (N) Landlord maxPoolSize Tenant maxPoolSize Tenant_shared maxPoolSize Superuser conns PostgreSQL max_connections
1 15 20 20 10 15 + 1*20 + 20 + 10 = 65
10 15 20 20 10 15 + 10*20 + 20 + 10 = 245
20 15 20 20 10 15 + 20*20 + 20 + 10 = 445
30 15 20 20 10 15 + 30*20 + 20 + 10 = 645

Tenants can be added dynamically, but max_connections per pgsql cluster cannot be changed on the fly, max_connections needs to be scaled accordingly to the anticipated number of tenants.

Strategies for Allocating JDBC Connections

We saw earlier that the values specified in directory.xml at database/<db-name>/orm represent the values for the shared database and, at the same time, the default values for all tenants of that database if not explicitly configured in landlord database via REST requests.

Probably the most important parameter for the connection pool is the maxPoolSize which sets the upper limit for the number of connection to a specific physical database.

In previous section a table with simple arithmetic is presented for a single cluster setup. However, as shown below, the setup can be extended to multiple clusters, as well.

  1. From known necessities
    Let's assume the customer has a good experience with the application and is aware of the number of connection needed. Let's consider maxPoolSize = 20 (as in cited document). We compute the minimal value for the value of max_connections stored in cluster's postgresql.conf file as follows:
    max_connections >= 10 (superuser) + 15 (landlord) + 20 x (N + 1) (shared and tenant physical databases)
    Pros and cons of this strategy:
    • + easy to configure when the application requirements are known and the number of tenants is also known from beginning;
    • - adding a tenant requires max_connections to be recomputed and PSQL to be restarted;
    • * alternatively, when adding a new tenant, it can be stored in a new cluster.
  2. Hard limits
    In case max_connections is fixed and cannot be changed, the maximum number of connections (maxPoolSize) will have to be computed using the following formula:
    maxPoolSize = floor((max_connections - 10 (superuser) - 15 (landlord)) / (N + 1) (shared and tenant physical databases))
    Pros and cons of this strategy:
    • + useful if setting up the server in strict conditions;
    • - if N is large, maxPoolSize may become too small to be useful
    • * in that case, some of the tenant physical databases can be moved to another cluster
  3. Dynamic allocation of tenants on multiple clusters
    From the bullets above marked with *, we can encounter cases when using a single cluster is not feasible. Using additional clusters (each with its own max_connections possible connections) allow the system to be naturally expanded. Physical databases can be stored to other clusters than the one which hosts the shared database. If not specified otherwise, the data pools for these databases will inherit the maxPoolSize of the shared database. When computing the max_connections for a new cluster which will hold an additional M tenants we use:
    max_connections2 >= 10 (superuser) + maxPoolSize x M (tenant physical databases)
    Notice that, even if the superuser connections are mandatory, the reservation of 15 connections for landlord and maxPoolSize for shared database are not accounted. At this moment, the FWD already has N + M tenants. This operation can be extended to any number of clusters, not mandatory on same machine.
  4. Fine-tuning connections
    As noted initially, all the above examples use the maxPoolSize set in database/<db-name>/orm. None of the tenants were configured with a different maxPoolSize when they were created / modified using tenant REST admin (or directly in landlord database, before re-starting the FWD server).
    The maxPoolSize (but also the other C3P0 parameters) can be individually configured for each tenant database. Let's assume we want to create a new private database which is expected to require a larger number of connections than declared in directory.xml for the shared database. We use the following request:
    POST https://localhost:7443/admin/tenants/fwd_ten404/db
{
    "physicalName": "fwd_ten404",
    "logicalName": "fwd",
    "url": "jdbc:postgresql://localhost:5430/fwd_ten404",
    "username": "cool-name",
    "password": "*****",
    "minPoolSize": 2, 
    "maxPoolSize": 42
}
    When running, the data source for fwd_ten404 database will allocate a minimum of 2 connections but will be able to grow up to 42, even if the directory settings for fwd database specified 10 and 20 respectively. Also, this seems to be a different cluster (using port 4530) than the default used by shared/logical database.
    In this general case, computing max_connections becomes a bit more complex. Formally:
    max_connections >= 10 (superuser) + 15 (landlord) + maxPoolSize (shared) + Σ(i in 1 .. N of maxPoolSize_i)
    for the primary cluster (the one storing the shared and landlord database), and 
    max_connections >= 10 (superuser) + Σ(i in 1 .. M of maxPoolSize_i)
    for secondary clusters. We have:
                      / maxPoolSize of shared database, if the value was not specified individually 
    maxPoolSize_i =  <
                      \ specific maxPoolSize, if specified in REST request

Particular case: if each tenant has its own database cluster, then the formulae above get simplified (N = 0 (assuming no tenant is stored with the shared database), M = 1, etc), as follows:

max_connections >= 10 (superuser) + 15 (landlord) + maxPoolSize (shared)
for main cluster, and
max_connections_k >= 10 (superuser) + maxPoolSize_k
for each secondary cluster holding the private database of tenant k. If the maxPoolSize is not specified at creation of later set, the default size from the shared database is inherited. Otherwise, maxPoolSize for tenant_k can be set in the REST request as:
maxPoolSize_k = max_connections_k - 10 (superuser)
assuming there are no other active databases on respective cluster. If there are, the formula will have to be adjusted proportionally.

JDBC

This section is used for JDBC additional settings:

  • fetch_size (Integer) represents the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query's scrollable result set. [TODO: this is a global parameter; move this documentation accordingly]
    Example:
    <node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="container" name="jdbc">
        <node class="integer" name="fetch_size">
          <node-attribute name="value" value="1024"/>
        </node>
      </node>
      ...
SQL Logging

It can be useful in limited situations to log all SQL statements which are executed by FWD for a particular database. The verbose nature of this logging can result in a very noise server log, and performance of an application may be adversely affected with SQL logging enabled. Therefore, this setting is only intended for diagnostic/debug use and it is recommended that it should be disabled for normal production use.

This setting is configured directly under the orm container.

  • show_sql ( boolean) used to set enable the logging of all the generated SQL statements. The default value is FALSE.
    Example:
    <node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="boolean" name="show_sql">
        <node-attribute name="value" value="TRUE"/>
      </node>
      ...
Session hook

A hook that can be executed each time a FWD session is established (in Session() constructor) and also when the connection is closed (in Session.close()).

The custom hook must implement com.goldencode.p2j.persist.orm.SessionHook and override onSessionClaim() and onSessionClose().
To be noted that onSessionClaim was initially named onSessionOpen, so renaming the method in the implementations of the SessionHook is necessary.

This setting is configured directly under the orm container.

  • session_hook ( String) The name of the custom implementation of SessionHook interface.
    Example:
    <node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="string" name="session_hook">
        <node-attribute name="value" value="_path_to_the_implementation_"/>
      </node>
      ...
Configuring session parameters

FWD allows to config database session parameters across all supported dialects, which sets the parameters when acquiring the connection.

The parameters are read from the startup-config node of a database in the directory and applied by the dialect's connection customizer as a session-scoped SET command every time a pooled connection is acquired.
Because the parameters are applied at session scope, each entry must name a variable/option that the database accepts as a per-session SET; server-global or persistent settings are out of scope for this mechanism.

Examples:
  • PostgreSQL
    <node class="container" name="database">
  <node class="container" name="my_database">
    ...
    <node class="container" name="p2j">
      ...
      <node class="strings" name="startup-config">
         <node-attribute name="values" value="synchronous_commit = off"/>
      </node>
  • MariaDB
    <node class="container" name="database">
  <node class="container" name="my_database">
    ...
    <node class="container" name="p2j">
      ...
      <node class="strings" name="startup-config">
         <node-attribute name="values" value="sort_buffer_size = 1048576"/>
         <node-attribute name="values" value="time_zone = '+00:00'"/>
         <node-attribute name="values" value="foreign_key_checks = 0"/>
      </node>
  • SQLServer
    <node class="container" name="database">
  <node class="container" name="my_database">
    ...
    <node class="container" name="p2j">
      ...
      <node class="strings" name="startup-config">
         <node-attribute name="values" value="STATISTICS IO ON"/>
      </node>
  • H2
    <node class="container" name="database">
  <node class="container" name="my_database">
    ...
    <node class="container" name="p2j">
      ...
      <node class="strings" name="startup-config">
         <node-attribute name="values" value="WRITE_DELAY 0"/>
      </node>
Implications of commit synchronization (durability vs. performance)

The most impactful session parameter is the one that controls commit durability - whether the database waits for the transaction log to be physically flushed to stable storage before reporting a COMMIT as successful. Each dialect exposes a different knob for this, but the trade-off is the same:

  • Synchronous commit (durable) - COMMIT returns only after the log record reaches disk. A committed transaction is guaranteed to survive an operating-system or hardware crash. This is slower because every commit incurs a disk fsync.
  • Asynchronous / delayed commit (relaxed) - COMMIT returns as soon as the log record is buffered, before it is flushed. Throughput increases and commit latency drops sharply, but a crash can lose the most recent committed transactions (those still sitting in the buffer).

Important: relaxing commit synchronization never corrupts the database and never produces partial/torn transactions. The database always recovers to a consistent state; the only risk is losing a small window of the latest committed transactions after an abrupt crash. Normal shutdowns lose nothing.

Default values per dialect

The following table lists the parameter that governs commit durability in each dialect and its out-of-the-box default.

Dialect Parameter Default Meaning of the default
PostgreSQL synchronous_commit on Fully durable - COMMIT waits for the WAL record to be flushed to disk.
MariaDB innodb_flush_log_at_trx_commit 1 Fully durable - the redo log is written and fsync-ed at every COMMIT.
SQL Server DELAYED_DURABILITY DISABLED Fully durable - COMMIT waits for the transaction-log flush. (Database-level setting; see the note below.)
H2 WRITE_DELAY 500 (ms) Not fully durable - changes are flushed at most every 500 ms, so a crash can lose up to ~0.5 s of committed work.
Notes:
  • On PostgreSQL, synchronous_commit = off can lose transactions committed within roughly the last 3 x wal_writer_delay (~ 0.6 s with the default wal_writer_delay = 200ms). Values local, remote_write and remote_apply tune behaviour for replication. Note that FWD overrides this default to off - see "FWD default for PostgreSQL" below.
  • On MariaDB, innodb_flush_log_at_trx_commit = 0 or 2 relaxes durability to roughly one flush per second (up to ~1 s of loss). This variable is session-settable, so it works with startup-config.
  • On SQL Server, delayed durability is a database-level option (ALTER DATABASE ... SET DELAYED_DURABILITY) or a per-transaction COMMIT clause; there is no session-scoped SET equivalent, so it cannot be relaxed through startup-config. It stays fully durable unless changed at the database level.
  • On H2, WRITE_DELAY 0 forces an immediate flush on every commit (fully durable, slower); the default of 500 ms favours throughput over durability.
OpenEdge (Progress) default

OpenEdge's commit durability is governed by the Delayed BI File Write (-Mf) startup parameter, which controls how long the engine may delay flushing the last before-image (BI) record - the write that makes a committed transaction durable. Its default depends on the database mode:

OpenEdge mode -Mf default Durability of the default
Single-user -Mf 0 Fully durable - the last BI record is flushed immediately at commit (equivalent to synchronous_commit = on / WRITE_DELAY 0).
Multi-user -Mf 3 Relaxed - the last BI record may be held up to ~3 seconds (a group-commit optimization), so an abrupt crash can lose the most-recent committed transactions within that window.

(Full crash protection can also be disabled entirely as an explicit opt-in, e.g. the -i "no integrity / no crash protection" mode used for bulk loads.)

Consequently:
  • To match an OpenEdge single-user source (fully durable), keep the durable defaults - synchronous_commit = on (PostgreSQL), innodb_flush_log_at_trx_commit = 1 (MariaDB) - and set WRITE_DELAY 0 on H2, whose 500 ms default is otherwise non-durable.
  • To match an OpenEdge multi-user source, note that the database defaults on PostgreSQL, MariaDB and SQL Server are actually stricter (fully durable) than OpenEdge's ~ 3 s delayed-BI window. Applications that want the original throughput characteristics may deliberately relax durability to approximate -Mf 3 - e.g. synchronous_commit = off on PostgreSQL (~ 0.6 s window) - accepting the same "lose the last moments of committed work on a crash" behaviour that OpenEdge multi-user already had by default. (H2's 500 ms WRITE_DELAY default already sits in this relaxed range.)
FWD default for PostgreSQL

For PostgreSQL, FWD's connection customizer applies synchronous_commit = off on every acquired connection by default - you do not need to configure it. The default is suppressed only when the database's startup-config sets synchronous_commit explicitly (to any value), in which case the configured value wins.

This deliberately makes FWD's PostgreSQL default less durable than stock PostgreSQL (on): it favours throughput and more closely matches the delayed-BI-write behaviour of a default multi-user OpenEdge database (-Mf 3). To restore full durability, add an explicit override to startup-config:

<node class="strings" name="startup-config">
   <node-attribute name="values" value="synchronous_commit = on"/>
</node>

The other dialects (MariaDB, SQL Server, H2) keep their own native defaults; FWD applies no implicit durability override for them.

Multi-tenant databases

In a multi-tenant deployment each tenant's physical database has its own connection pool, so the session-scoped SET statements (and the PostgreSQL synchronous_commit default) are applied per tenant connection and never leak between tenants.

However, startup-config is defined on the primary (default-tenant) database and is inherited uniformly by every tenant of that logical database - there is no per-tenant startup-config override. Consequences:
  • All tenants of a logical database receive the same startup-config statements.
  • The PostgreSQL synchronous_commit default is therefore all-or-nothing across tenants: either every tenant runs with off, or - if the primary's startup-config sets synchronous_commit - every tenant uses that configured value. A single tenant cannot be given a durability setting different from the others.

Example Configuration

Here is a full example of a permanent database configuration:

        <node class="container" name="database">
          <node class="container" name="my_database">
            <node class="container" name="p2j">
              <node class="string" name="schema">
                <node-attribute name="value" value="my_schema"/>
              </node>
              <node class="boolean" name="load_at_startup">
                <node-attribute name="value" value="TRUE"/>
              </node>
              <node class="string" name="embedded-collation">
                <node-attribute name="value" value="en_US_P2J"/>
              </node>
              <node class="strings" name="startup-config">
                 <node-attribute name="values" value="synchronous_commit = off"/>
              </node>
            </node>
            <node class="container" name="orm">
              <node class="string" name="dialect">
                <node-attribute name="value" value="com.goldencode.p2j.persist.dialect.P2JPostgreSQLDialect"/>
              </node>
              <node class="container" name="connection">
                <node class="string" name="driver_class">
                  <node-attribute name="value" value="org.postgresql.Driver"/>
                </node>
                <node class="string" name="url">
                  <node-attribute name="value" value="jdbc:postgresql://localhost:5433/my_database_instance"/>
                </node>
                <node class="string" name="username">
                  <node-attribute name="value" value="fwd_user"/>
                </node>
                <node class="string" name="password">
                  <node-attribute name="value" value="user"/>
                </node>
                <node class="integer" name="prepareThreshold">
                  <node-attribute name="value" value="1"/>
                </node>
              </node>
              <node class="boolean" name="show_sql">
                <node-attribute name="value" value="FALSE"/>
              </node>
              <node class="container" name="c3p0">
                <node class="integer" name="minPoolSize">
                  <node-attribute name="value" value="10"/>
                </node>
                <node class="integer" name="maxPoolSize">
                  <node-attribute name="value" value="50"/>
                </node>
                <node class="integer" name="acquireIncrement">
                  <node-attribute name="value" value="5"/>
                </node>
                <node class="integer" name="maxIdleTime">
                  <node-attribute name="value" value="900"/>
                </node>
                <node class="integer" name="maxStatementsPerConnection">
                  <node-attribute name="value" value="100"/>
                </node>
              </node>
              <node class="container" name="jdbc">
                <node class="integer" name="fetch_size">
                  <node-attribute name="value" value="1024"/>
                </node>
              </node>
            </node>
          </node>
        </node>

To reiterate, the legacy physical database name (in this case, my_database) does not need to match the legacy schema name (in this case my_schema), and neither needs to match the relational database instance name (in this case, my_database_instance).

The legacy physical database name and the legacy schema name are used by the FWD server to logically identify a relational database configuration, mapping it to legacy database references in an application. The relational database can have any arbitrary name; the JDBC URL specified in the directory's database configuration for a particular legacy database tells FWD how to connect to that resource.

Reference

With the exception of the port_services section, all of the configuration values below must be located in a <db_path>/database/<db_name>/ container. The port_services section must be placed directly under /server/default/ or in server/<server_id>/.

Here is the list of the database options and their path which can be configured within the directory:

Option ID Data Type Default Value Required Details
port_services/<service_name> Integer N/A No FWD uses this data to create a logical connection (in the 4GL sense) to a database using the specified parameters. It can also be specified as parameters to server startup if not given inside directory.
p2j/embedded-collation String en_US@iso88591_fwd_basic No This is used to set collation rules for H2 dialect. The other dialects are not affected.
p2j/load_at_startup Boolean false No This is used to determine auto-connect status, in the 4GL connection sense (not the JDBC connection sense).
p2j/mutable Boolean false No Determine whether a database's schema is programmatically mutable, which is set by configuration.
p2j/schema String <database-name> No The name of the schema associated with this database, as exported from the 4GL Data Dictionary.
p2j/use_java_udfs Boolean false No Decide whether to use Java or SQL UDFs. Java UDFs are now deprecated and the recommended value to use is false.
orm/connection/driver_class String N/A Yes The Java Driver class used to create the JDBC connection.
orm/connection/username String N/A Yes The username used to connect to the database.
orm/connection/password String N/A Yes The password used to connect to the database.
orm/connection/url String N/A Yes The JDBC URL (formatted according to the database vendor's requirements) used by JDBC to connect to the database instance.
orm/connection/prepareThreshold Integer 3* No The threshold number of times a statement is called for which the query will be used as a named query saving the execution plan at the database.
* Only used by PostreSQL (defaults to 3).
orm/c3p0/minPoolSize Integer 3 No The minimum number of connections the pool will maintain at any given time.
orm/c3p0/maxPoolSize Integer 15 No The maximum number of connections the pool will maintain at any given time.
orm/c3p0/maxIdleTime Integer 0 No The number of seconds a pooled connection will be available before it is released. Set to 0 or omit for connections that do not expire.
orm/c3p0/maxStatementsPerConnection Integer 0 No The total number PreparedStatements a DataSource will cache per pooled connection.
The pool will destroy the least-recently-used PreparedStatement when it hits this limit. Set to 0 or omit to disable prepared statement caching.
orm/c3p0/acquireIncrement Integer 3 No This parameter is used to determine how many connections at a time c3p0 will try to acquire when the pool is exhausted.
orm/jdbc/fetch_size Integer 256 / 1024 No Represents the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query ResultSet with next().
orm/show_sql Boolean false No Enable/disable the logging of all the generated SQL statements to the server log.
orm/dialect String N/A Yes The Java class that which defines database dialect-specific behavior and syntax.

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