FWD » Core Development » Database

Users with very large databases might have import times that are longer than the outage window which can be arranged. For example, if the cutover window is 24 hours and it takes 48 hours to import, then some other approach is needed.

A simple solution is to implement a 2 stage import process:

1. The first stage operates the same way as the current import works. The difference is that it is run ahead of time. The user dumps the database in OpenEdge but does not shut down the 4GL system. The system is allowed to continue working.
2. The second stage just applies diffs since the first stage was executed. This is designed to be very fast and it could easily handle a few weeks or more of diffs without taking very long at all.

The key here is to implement some kind of diffs capture and then to write a 2nd stage process to apply those changes. Of course, it must handle deletes as well as inserts and edits.

With OpenEdge 11.7 there is a Change Data Capture (CDC) facility that is designed exactly for this same purpose (probably for efficient replication of changes). That should be leveraged rather than writing a trigger-based implementation. The 4GL docs describe how to configure CDC this but I don't see anything obvious about how to dump or use the captured results. On the other hand it describes storing this data in "change tables". I guess one uses 4GL to read/dump the data in these change tables.

Unless there is a standard for storing the CDC results, we will have to implement the dump to some format and the "apply changes" 2nd phase would be written to read in that format. If there is a standard, we should use that standard if at all practical.

A customer has posed this question:

Many customers are on OpenEdge 11.6 while CDC requires 11.7 and it requires an extra license.

Is there another way to implement the two stage import without CDC?

Without CDC, there are multiple possible approaches:

• One could use schema triggers to log the changes. This change log would then be "replayed" in the migrated database.
• An alternative is to compare a full export of the modified database with the full export snapshot that was used in the stage 1 import. Java tools can be written to calculate the diffs by comparing the .d files.

I'm sure there are other approaches too.

• a replication triggers to collect the affected records for each table of the database during the interval between the two stages;
• an intermediary database (one table primarily) for storing the above information. That would be composed of following triplet:
  • table (the affected table),
  • pk (the recid of the record altered)
  • op (the operation, one of: create / change / deleted).

• if crt op is create and a new change is recorded, the create remains (it is irrelevant how many intermediary steps, from the PoV of the target database, this is just a new record which didn't exist before);
• if crt op is create and a new delete is recorded, the pk is removed from transfer database (the record was temporary, it will not be 'resurrected' since the PKs are not reused/reallocated in permanent databases);
• etc (a full 3x3 matrix will have to be implemented).

At the moment of the 2nd stage (even after weeks) after the process started, it will look directly at the remaining records in the changeset and process the data accordingly. In an average application, should only be only a fraction of the records affected. If a record is processed multiple times (like a counter), there will be a single operation to make it up-to-date.

Ovidiu Maxiniuc wrote:

[...]
A record in this table will be indexed by <table> x <pk> (actually, <pk> should be theoretically enough since the PK are supposed to be unique, but you never know).

I understand rowid uniqueness across the database is no longer enforced in later versions of OE, so we would need the table as well.

If two events of same record, a bit of logic will be applied. For example:
[...]

The introduction of a replication trigger is intrusive, but I agree it may be necessary, especially if CDC is not the approach we want. If so, we want the logic in that trigger to be minimal/fast. If there is logic (e.g., to coalesce events), I think it is better to put this in the stage 2 process which prepares the import content.

What will that import content look like? Standard SQL? Dialect-optimized SQL? Something else?

How does multitenancy affect the design? Consider that we may only want/need to track changes for a subset of tenants at a time.

We need to consider that the first data dump created must include an unique identifier for each record, which is available in 4GL; and rowid/recid can not be used - we need to add a special field, set via either a sequence, UUID or something else, to have an unique value for all records in a table, and also to be unique for the lifetime of that table (thus is non-repeating), and immutable once set.

• the table name
• the tenant name
• the unique identifier for the record
• boolean flag if a row was deleted or this is for a new/updated record

We can 'always append' to this table or add an index for (table, tenant, identifier, flag) so we don't record 'touched' rows more than once - we can rely on the 4GL validation to fail the insert if this new record state has been recorded.

• create .d files for all records which are new/updated, for each table and tenant
• separate files with unique identifier for all deleted records, for each table and tenant

The point of the above is: I don't think we need to follow all the create/update/delete operations to recreate the changes; we know that the 4GL state of the database is valid at the time this tool will run, to dump the changes.

How to solve possible conflicts on existing unique indexes in the actual 4GL table: this can happen if we keep in the PostgreSQL database the affected records, when the new dump is being processed. To solve this, we can, before doing the new import, delete all records (resolved via this new unique identifier) which are in the .d files and the 'deleted' files. Thus, we end up with two sets of records, one 'unchanged' and the other 'changed' - and when we process the 'changed' set, we know that these records will not conflict, as the dump from 4GL can be consider valid. And the union of the two sets is the actual 4GL table.

PS: if this new field to act as an unique identifier for all existing records can not be used, then rowid/recid recycling will pose problems. We will still need to export this value in the .d files and import it in PostgreSQL.

• we still delete from PostgreSQL all records in the second set
• the second set is still built from 'all tracked rowids', regardless if a rowid has been reused or not; if rowid A was existing, deleted, and a new record created for it (thus reused), we still need to delete it from the first set, and the second set will just create it.

PPS: rowids can be changed via other means, from claude:

Other ways ROWIDs change:

• A dump and load, or a binary dump/load, assigns new ROWIDs to essentially everything.
• Moving a table to another area with proutil tablemove changes its ROWIDs.
• Partitioning or tenant moves in multi-tenant or table-partitioned databases also change them.

So this needs to be considered that the rowids must remain stable (not changed via other tools) once the initial dump has been performed.

And the last PPPS: if the rowid/recid are immutable for the lifetime of a record (recycling is OK), then I think we are OK using it. If these are not immutable (i.e. there are maintenance processes which move tables/partitions/tenants/etc around and these get re-calculated), then rowid/recid can not be used.

Eric Faulhaber wrote:

A record in this table will be indexed by <table> x <pk> (actually, <pk> should be theoretically enough since the PK are supposed to be unique, but you never know).

I understand rowid uniqueness across the database is no longer enforced in later versions of OE, so we would need the table as well.

Actually, the table will not be enough if the rowid is reused, possibly in same table (as we encountered for temp-tables). But there is a bigger flaw in my design: the .d dumps don't contain the PKs. Constantin is right in note #3872-7. So, even if we have every other aspect resolved, I see no mean for identifying the identifying the records after they were dumped. If there is at least a unique index, we could use that, but this means that the Δ-database to contain the keys for that. I need to think more on this matter, but what I think is a solution is to use a custom dump application which would save the rowid, as well. However, this is not enough, the mapping should be kept during import, and later, as well, in a custom .d-format.

The introduction of a replication trigger is intrusive, but I agree it may be necessary, especially if CDC is not the approach we want. If so, we want the logic in that trigger to be minimal/fast. If there is logic (e.g., to coalesce events), I think it is better to put this in the stage 2 process which prepares the import content.

My estimation is this would be a fast operation: locate the Δ-record and just update it. If not - create it. No complex queries. But if that is too costly, we can save just raw and go with a secondary preprocessing pass just before applying the changes to FWD records - assuming the records are correctly identified.

What will that import content look like? Standard SQL? Dialect-optimized SQL? Something else?

Yes, this is what I thought of. Preferably standard SQL, assuming the replication triggers can save the changes in SQL in a db format compatible with FWD (thinking of secondary columns for datetime-tz and computed columns for character fields).

How does multitenancy affect the design? Consider that we may only want/need to track changes for a subset of tenants at a time.

To my knowledge, the triggers will receive events from all tenants. The tenant-id/name should also be saved in Δ-records (my mistake). When these are applied, the records will be automatically filtered because the matching rowid s (the operation would be applied on each tenant database).

Also, to respond to Constantin's concerns on #3872-8 and #3872-9: indeed, these events could happen, but these are executed manually, by an admin. If the customer knows a migration to FWD is in progress, these operations must be avoided. Or, if mandatory, the migration of database will move back to square 1 with the first stage.