2

I work with a pretty large (O(10^1) TB) DB2 (LUW, v. 9.7) database. It's got data coming in on a continuous basis via Golden Gate replication from another DB2 database. It's used for business intelligence and analytics.

Now I'm working with a group in the parent company which is trying to build an enterprise data warehouse. They want to collect data from their own databases, as well as from their acquisitions (like my site). To this end they purchased an Oracle BDA appliance (Cloudera Hadoop), and sometime soon an Oracle Exadata box will be stood up.

Putting aside the fact that the target database is Hadoop, not a traditional RDMS, I'm having a hard time coming up with solutions that will faithfully copy the data out of the source database, given that rows are not only being continuously inserted, but also updated. (As far as I can tell, rows are never deleted.)

Question

I'm interested in what the landscape of possible approaches looks like, as well as which approaches will scale and not be too much of a performance burden on the source database?

Current Solution

Currently we copy data to Vertica in-house, using a home-built solution. Small tables are dumped on the target and then copied in their entirety to Vertica. Large tables have a trigger that updates a table with a single row. That datum records the oldest value of a timestamped and indexed column seen in any row that's inserted or updated. All the SELECTs are done as uncommitted reads. This appears to work, but we do transfer a large amount of data; the new project requires transmission over a much greater distance and with presumably less bandwidth. Moreover, this process is only run once per week. While I don't think a lag measured in minutes is required for this new project, the principals might not be very happy with a weekly refresh.

Possible Solutions

Here's what I've brainstormed so far:

  1. A vended replication solution like Golden Gate.

  2. Some in-house solution that ships transaction logs (probably beyond our dev capabilities).

  3. A trigger that exports any inserted or updated row. I assume this would be a horrendous performance hit on the source DB.

  4. A trigger that records the primary key of any inserted/updated row. Also would appear to be a big performance hit.

  5. Adding an indexed timestamp column for time of last modification, and an accompanying trigger to modify it on update. The DBAs I work with claim this would be a performance hit (I can't really tell if it would be superior to numbers 3 and 4 above). Moreover, it adds the complication that the upstream data source doesn't have this column, with possible implications for the current replication process between the two DB2 databases.

  • Since you already have GoldenGate in place, option 1 looks like a no-brainer. – mustaccio Jul 30 '15 at 17:27
  • @mistaccio: I would think so. It's been mentioned by our enterprise architect. But in the meantime we're doing things like shipping data by doing selects on the last 24 hours of inserts for the time being. Which confuses me, because it doesn't capture updates, and yet no one is openly pointing out that this is therefore pretty much useless as a dev milestone. – user1071847 Jul 30 '15 at 17:33
  • Why can't you use DB2 replication? – Hogan Apr 16 '18 at 20:39
  • @Hogan: It's been a long time since I thought about the problem, and I'm no longer at that company, so I don't recall why. Perhaps too heavy a solution at the time (licensing issues). – user1071847 Apr 18 '18 at 12:51

Your Answer

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

Browse other questions tagged or ask your own question.