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.

• there is no guarantee for a single, immutable, unique identifier for a certain record, thus we will need to record and replay all changes, in order, where each change identifies the record via the first unique index in that table, or via its full record fields if there is no unique index
• triggers are required to record these changes, in a separate table, as otherwise rollback can not be processed properly
• schema triggers will either need to be added to tables where there is none, or 4GL code (via a RUN statement) injected at end of a trigger, in a FINALLY block, if the table already has such a trigger
  • an alternative would be to add session triggers for all tables in the schema, in a generated .p file which runs at the agent's startup procedure - caveat - these don't chain, so only one can be active at a time; works only if there are no session triggers in the application code
• allow these triggers to record the changes only for certain explicitly specified tenants
• allow these triggers to be disabled by default, and only activated after the first stage completes

• what is the table structure where these changes get recorded - how do we record the record's fields - do we just dump them to JSON?
• does the first stage data dump execute while the production database is still in use? If yes, then I think the triggers recording the changes need to be activated while first stage data dump is running - so, in this case, what happens if a change is recorded, and this already exists in the first stage dump?
  • I think the above applies even if a replica database is used for first stage dump - lets assume first stage is for time T1, time T2 is when production activates the change triggers. So we have:
    • if triggers are activated after replica db is 'frozen', then between T1 and T2 we may lose changes
    • if triggers are activated before replica db is 'frozen', then we have changes recorded which already exist in the first stage dump

Another open question, talking about multi-tenant OE databases: what happens with the shared tables? When are these dumped and imported? What changes do we need to track after the first tenant has been processed (first and second stage has data has been dumped)?

via the first unique index in that table

Doesn't this also fall into the null case? Two records having (<value>, null) on an unique index, while value is not unique.

what is the table structure where these changes get recorded - how do we record the record's fields - do we just dump them to JSON?

I guess the .d format is more reasonable (values separated by a space, quote wrapped if needed, with right encoding, d/m/y, etc.). I think the export tool does that using actual EXPORT.

Big problemo is LOBs. These are exported in separate files by data exporter. Serializing them into a longchar might be problematic.
So we may need to export them on disk and associate the LOB with a serialized field. But if a CHANGE occurs on a serialized row that has two instances in the DB. How can we detect that the CHANGE occurred on the row with LOB1 or LOB2?

if triggers are activated before replica db is 'frozen', then we have changes recorded which already exist in the first stage dump

I think we can save something like a time when the change was done. We voluntarily replay since a certain time momentum.
Even if this task is about "two stage import", I think it would be suitable to have a "N stage import" instead. Get more and more data to FWD incrementally and decide when porting is done (not necessarily after 2nd import). That is why I think this problem you suggest needs more thinking time.

Alexandru Lungu wrote:

via the first unique index in that table

Doesn't this also fall into the null case? Two records having (<value>, null) on an unique index, while value is not unique.

You are completely right - unique indexes are not really unique to identify a record. Thus, what's left is storing the entire record state (the state as before the change, in the 'old' record - just mentioning as I didn't mention it in previous notes). This can pose also a storage problem, depending on the serialization mode we use and how large the record data is, and what we do with LOB fields; they will need to be used to identify the record.

Also, we need to consider that when replaying the changes, we will need to do first a SELECT to find the matching record (as we will not be able to use a BLOB in a WHERE clause, and we will need to explicitly match it), and after that an UPDATE (or DELETE) on it.

Greg: having a stable unique identifier field at each table will solve some of these discussions... maybe it's worth re-considering.

I cannot imagine a scenario in which the customer will accept a requirement to modify all existing OE tables to add a UUID column. It is not going to happen. I think it will already be a difficult request to add a table to store the changes. They may refuse that as well.

Constantin Asofiei wrote:

Greg: having a stable unique identifier field at each table will solve some of these discussions... maybe it's worth re-considering.

In my previous notes I tried to not think of this, just because few customer will accept to add one additional column for each table. It also needs to be populated for existing records and add a trigger to initialise it for newly created records. The 'logistic' for doing this for a 1000+ tables database is enormous.
But now it's the only clean solution I see for uniquely identifying the records. Probably the faster and simple, after the step above. With that unique (hidden from 4GL code) column, the 1st stage export will happen naturally.

There is a second problem with computing the Δ in stage two. If the (replication) triggers are unreliable in case of rollback, we are really blocked. But let's assume they are called on a change and even if the operation is rolled back the log they do remains. That will not be an actual correctness problem, but a performance: when performing the 2nd stage, the information is taken from original table, and the only downside is that it is actually unmodified. Extra work/space, but the right value in the export file.

• add a new sequence S of positive numbers;
• create a flag F which will mark the end of 1st stage. May be another sequence with initial value of 0;
• add additional hidden field U for each table to be exported and populate them with values from sequence;
• add triggers for said tables, which will do:
  • set values for hidden field U from the sequence S for new records;
  • if the flag F is set, the value for U are turn to negative, for both newly created and modified. The value U of deleted records is written to s special location D (text file is OK);
• start the 1st stage;
• the flag F is set (sequence incremented) at the moment the 1st stage ends;
• when the 2nd stage is due to start the server is disconnected;
• the database is scanned for negative U fields and those records are saved to Δ dump;

• do the initial import with the 1st stage data;
• when 2nd stage dump is available:
  • use the D list to remove the deleted records;
  • use the unique values U from 2nd data to delete updated record (avoid unique index collision). If the unique indexes are disabled, this step can be skipped
  • import the 2nd data;
• start the FWD server

Greg Shah wrote:

I cannot imagine a scenario in which the customer will accept a requirement to modify all existing OE tables to add a UUID column. It is not going to happen. I think it will already be a difficult request to add a table to store the changes. They may refuse that as well.

OK, understood. From what I can find, CDC has the same issues related to rowid (recycling or recalculated when i.e. moving table in a partition).

• the first stage database and the second stage database have the same rowids (if replica is used for first stage, my understanding is this will work only if OpenEdge Replication is used for it, so rowids match)
• there is no partitioning or other process which would recalculate the rowids, after the first stage dump is performed.

This is because I still don't like replaying changes when we can just delete and re-insert some records. If we move ahead implementing change replay, we will need to be very sure of the performance impact in 4GL, storage impact, and performance impact on FWD when replaying them.

As a side note, the needed table to capture the changes can be in a different database, is not mandatory to be in the same database; and even connected and accessed dynamically.

Ovidiu: please think what needs to be done for the shared tables in multi-tenant mode - see #3872-13; look into existing customer's code and see how they use them.