0

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?

  • Is Golden Gate out of the question? – Michael Kutz Sep 22 '19 at 23:22
  • @MichaelKutz I didn't know about Oracle's Golden Gate platform. Thanks for the suggestion. It's likely out of the question due to budget constraints. But I will certainly look into it. – User1973 Sep 22 '19 at 23:47
2

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.

|improve this 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.

|improve this answer|||||
1
  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?

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.