Integration: Keep two systems in sync

Question

I have a GIS system with 40 tables, ranging from 1,000 to 60,000 rows per table. The tables are the system of record for assets in a municipality.

The GIS assets in the tables get integrated to a Workorder Management System (WMS) on a weekly basis. The integration is based on web services that serve up the GIS tables to the WMS.

Constraint #1:

The integration to the Workorder Management System is multi-purpose.

1. There is a single asset table in the WMS that gets updated, via cron tasks, with any edits that have been made to the GIS assets (new assets, changed assets, and decommissioned assets). Only assets that have been edited are updated in the WMS.
2. The integration is also used to dynamically serve up the assets to a web map in the WMS (all of the GIS assets are used in the map--not just the assets that have been edited). The map in the WMS connects directly to the GIS web services--it does not use the records in the asset table or the cron tasks.

Constraint #2:

The WMS cron tasks are notoriously slow. Given my organization's infrastructure, my vendor says that the WMS cron tasks will only be able to sync 150 records per minute.

• Testing is ongoing, but we have been told to only sync the records that actually need to be synced (edits) due to the significant performance concerns. In other words, we can't just integrate or copy all records, all the time.
• To give you an idea, this is what the cron task process looks like: REST GIS web service >> JSON object >> Parse the JSON into individual records >> Generate XML for each record >> Process the XML records with Java classes >> Insert the records into the database.

Constraint #3:

GIS data is notoriously messy.

In constraint #2, I mentioned that the records get processed with Java classes. The Java classes check for errors (parent/child, field rules, etc.) and flag any records that fail.

• These records do not get integrated into the WMS.
• It is up to the GIS teams to correct the errors in GIS tables, then we'll try again in the next integration instance (next week) to sync the GIS records to the WMS.

---

Question:

Given the constraints above, I think I need to figure out a way to integrate all the GIS assets to the WMS (constraint 1.2), but also flag the records that need to be synced due to edits (constraint 1.1).

• For edited assets that failed to sync--I need to retry them in future syncs until they are successful.
• And I need to avoid syncing records unnecessarily--due to the performance concerns.

How can I do this?

Answer 1

Are you running flashback database? If you were you could run a process that would start just after the top of every hour. The process would look at each table as it was at the beginning of every hour and do a Merge command between the source and destination databases for a one way sync. You would then be able to replicate all of the tables every hour. If for some reason, this process takes more than an hour, then you would replicated every other hour using the top of every even or odd hours as the starting point. You would be replicating based on consistent data across all tables everytime.

Goldengate would probably work better if you were licensed for it. It would keep the data more current more of the time. Governments should get a higher discount than corporations. It's worth getting a price for Goldengate even if you find out it costs too much.

Answer 2

If the systems support / allow it, I would be inclined to build a queuing table and use triggers, in the app or in the db, as applicable, to insert sync requests into the queuing table. You could then have a job that reads the queuing table, does the requested syncs, maybe copies the sync request to a sync-audit table, then deletes the sync requests. If a sync fails, there should be a status flag that can be set to ERROR or RETRY.

Answer 3

Performance Improvements

REST GIS web service >> JSON object >> Parse the JSON into individual records >> Generate XML for each record >> Process the XML records with Java classes >> Insert the records into the database.

You have a GIS database which - I assume, its an Oracle database. On the assumption that you don't need the geometry in the WMS, then I think the above CRON job could be looked at for performance improvements. Involvement of the GIS web services - (again im making huge infrastructure assumptions here) is possibly the biggest problem. The vendors web services are historically known to be quite unsalable and that infrastructure is probably being used to do other things (generate maps, mnagage DB queries etc).

Im a big fan of Service oriented Architecture, but I also discourage solutions which require ten billion different copies of files and different formats etc.

Perhaps a different way of looking at this could be that you - as suggested by others - implement database triggers. These could create new tables (view tables maybe?) of the edited records in the Oracle database - which have no geometry (because we're assuming you don't need it in WMS solution). Again, being Oracle DB, your Java solution could be re-written to simply read the data out of the view tables, and insert into WMS. This would eliminate

• REST GIS Web service
• JSON object
• Parsing
• XML generation
• With the Java processing replaced by other java processing.

As per other solutions mentioned, if your record is inserted, remove from view table. If not, try again later or flag to separate area for manual fixing.

the other benefit, is that this should free up your GIS Web server which does your map rendering, and hopefully improve its performance.

Anyways just some ideas!

Answer 4

1. In the GIS tables, I can add a gis_last_edit_date field using out-of-the-box editor tracking functionality.
2. In the WMS, I'll figure out a way to populate a wms_last_sync_date field.
3. From there, I can create views on the GIS tables:
   • Using a database link and a join, the views would compare the gis_last_edit_date with the wms_last_sync_date.
   • If the gis_last_edit_date is greater than the wms_last_sync_date, then flag the record in a calculated field as "needs to be synced".

4. Then, I would serve up the GIS views to the WMS integration/web service.

   • All of the GIS records in the views would be used in the map (constraint 1.2).
   • Only the records that are flagged as "needs to be synced" would be synced via the cron tasks (constraint 1.1 and 2).

5. If any of the records failed to sync, they would be flagged, edited, and retried in the next sync (constraint 3).

Is there any reason why this wouldn't work?