That’ll teach me

You know there are some questions for which the correct answer certainly isn’t the “right” answer.  Like when your 6-year old son comes over to you and says, “Dad, where do we keep the lawnmower?” — telling him: “It’s in the garage” probably won’t be the best thing.

You’d think I’d learn my lesson on this one.  True story — back in mid-95, 96 I was considered an expert on Oracle Replication.  And I get the following question: “Can I replicate the Oracle*CASE Repository?”.  Silly me, I step right into it by saying, “Well, it’s a regular Oracle schema, I don’t see why not…”.  2 weeks later I get an angry call from Oracle Support telling me that I shouldn’t go around saying Oracle supports replication of the CASE Repository unless I was willing to support it myself :-(

Anyway, today’s example is similar — about 2 months ago I was asked by an internal group for help on migrating a Postgres database into Oracle.  The complaint was “There aren’t any migration toolkits, what can we do?”.  So I do what every savvy tech person does nowadays, I google / sourceforge / freshmeat on “Oracle Postgres Migration” and I come with 4 or 5 tools which claim to do the job.  And I send them to the questioner with the suggestion — “go check these out”.  Silly me, again.

The group looks at them and picks the DTM Migration Kit – fair enough.  At first blush, looks fine.

Now at this point, I have to explain, I think there are 2 parts to any database migration — there’s the schema migration (structure conversion, often converting datatype definitions), and then there’s the data migration.  And it’s OK to use different tools for each one.  As a matter of fact, it’s probably a good idea.

Anyway, a couple of months go by, and things appear to be going swimmingly — they convert their schemas just fine.  And then, about 7 weeks prior to production cutover from Postgres to Oracle, I get a call — “It’s taking 40 hours to convert our production database”.  Mind you, the production database is only 10GB in size.  And they tell me — stop me if you’ve heard this one before — the Oracle database is the problem / bottleneck / source of all pain and misery :-)

So, I do more dumb stuff — I tell them: drop the indexes, disable the constraints, set to nologging — the whole shebang.  And gee — it still seems slow.  So I go take a look — and I just start shaking my head.  Imagine the slowest possible way to convert a set of data.  The migration toolkit connects to the datasource using ODBC, sucks out the data, and then connects to Oracle using ODBC and CONVERTS ONE ROW AT A TIME WITHOUT USING BIND VARIABLES.

Oh boy — every row its own special conversion routine — its own special, unique SQL statement, its own set of network roundtrips.  It was hilarious.  I asked the group to ask the DTM folks if there was an option to use bind variables (let alone do some bulk binds) — the response from the DTM people (their “architect”) — “hmm, that sounds interesting”.

Ah well — I think we’ll just use it for the schema migration.  My wife suggested we look at hetereogeneous services — I love it when she talks like that :-)

4 Responses to “That’ll teach me”

  1. Mihir Says:

    Nice Article. I read it as soon as you posted it today.

    Mihir Vakil

  2. sam Says:

    Good stuff,Happy blogging


  3. Aman Sharma Says:

    Hi sir
    you mentioned HS services.Would you like to share how did you do the job with it?
    Found your blog today only and read it whole.Its added in my favorites already.Happy blogging.
    best regards.
    Aman Sharma.

  4. ddelmoli Says:

    So far we haven’t used HS — I’m a bit wary of having the team try to configure ODBC on Unix for the first time. It’s on the list of things to try if we aren’t able to get the time down. Always a good idea to have your next idea ready.

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder