Slashdot is powered by your submissions, so send in your scoop

 


Forgot your password?
Close
typodupeerror
×
Technology

Converting From Oracle To DB2? 14

Posted by Cliff from the exporting-to-a-different-backend dept.
Peter asls: "We are currently running Oracle 8.1.5 on Sun SPARC Solaris 2.6 for our applications (written in C, C++, and JAVA. Because of the high costs of Oracle we are considering a full scale conversion to DB2. Does anyone know how involved this task would be and to what extent would this affect the code in such a conversion. Any information on this would be greatly appreciated."
This discussion has been archived. No new comments can be posted.

Converting from Oracle to DB2? More

Converting from Oracle to DB2?

Comments Filter:

  • The first problem would be to somehow import your Databases to DB2. Oracle offers quite some methods to export data and in the meanest case you must export to a txt file and than import everything into DB2. This should only take a few days.

    The part about the Code is up t the code. In jdbc you sould only need to adapt the Database driver. This should be only one line of Code in each Application. I think C and C++ should be the same. So if you are lucky this should also take only a few days.

    Considering possible mistakes and some evaluation time you should hopefully only need 1-2 weeks


    --

  • I dont see that slashdot is the right place to ask

    Agreed!

    To quote my flatmate (hp-ux sys-admin) when I just read the article to him - "fuck!"

    This really depends on how much code you have, how much data you have etc. etc. Has anyone done a keeping oracle cost vs a conversion cost? Don't forget that converting will run over time and budget (of course :)

  • Oracle and DB2 are managed very different but when it comes down to it the conversion is going to very greatly dependant on integrated into Oracles environment. A few things to consider about the Applications How much Oracle Pro*C do you use? Do you use Flex fields in Oracle? Are you reliant on PL/SQL? Cursors are managed and used significantly different!!!! You will have to re-write all of your cursor code. about the Database How reliant are you on sequial and nested triggers? Are you using Oracle ODS(High Avalabilty Software)? What kind of connections do you currently have? persisant or non-per? about the Data Date fields are handled differently Are you using Blobs? Do you manage multiple volumn underneth your database? All of these are signigicant issues when you are changing such a critcal system component. It is like have to change to filesystem on every computer that your company has from Unix to PC a lot has to change.
  • Seriously, this isn't a minor plug-in a new backend sort of thing. DB2 is different with administration and the dialect of SQL is a bit different.

    Overall the level of industry use/acceptance of DB2 is much lower than Oracle, particularly the Unix/NT (UDB) version of DB2. DB2 is big in the mainframe world, but good luck finding new hires with DB2 on Unix experience. Mainframe experience doesn't automatically port over to Unix experience.

    Cost is going to vary widely based on:
    1. Who's doing it? -- In house or hire consultants? Consultants will cost a lot more, but might be able to pull it off faster.
    2. Level of in-house expertise with Oracle and DB2 (doesn't sound like much since you're asking this)
    3. Timeframe, both for deploying the new database, and schedule for supporting both at the same time.
    4. Training you plan to offer to existing people and/or new people you'll be hiring.
    5. Level of buy-in from the technical and business users -- if the techs hate db2 the project will be an expensive failure. If the business users won't let up on change requests during the conversion then the project will likely be late and/or over budget.
    6. Complexity of what you're trying to port to DB2 and the level of reliance on proprietary Oracle features.

    Why are you asking Slashdot this? Seems like you should be asking us about a conversion to mySQL or PostgresSQL... :)
  • actually, the piece of sql you posted won't run on db2 with the 'TABLE' in there...

    i ran this (which worked fine, btw):

    select ac_type from hs.ac_acct order by ac_type

    this worked with every non-PK column in my table.

    this is on DB2 UDB 5.2 for AIX running on (not surprisingly) an IBM rs/6000

  • Seriously, this isn't a minor plug-in a new backend sort of thing. DB2 is different with administration and the dialect of SQL is a bit different.

    The fringes of the SQL may be different but the core SQL language is controlled by the SQL Language Council so any SQL which comes under the SQL '92 spec should move across without difficulty. It's the extras that cause the problems where the different vendors have different approaches to various problems.

    Overall the level of industry use/acceptance of DB2 is much lower than Oracle, particularly the Unix/NT (UDB) version of DB2. DB2 is big in the mainframe world, but good luck finding new hires with DB2 on Unix experience. Mainframe experience doesn't automatically port over to Unix experience.

    I'd like to know where you get your figures from. The level of industry use of DB2 is not 'much lower than Oracle', despite what Oracle's marketing department would have you believe. All those ads which say '96% of the Fortune 50 use Oracle' miss out the fact that most (all?) of the Fortune 50 use DB2. Most major companies use multiple database systems for various reasons. And an increasing proportion of those DB2 users are using UDB.

    Cheers,

    Toby Haynes

    DB2 UDB developer

  • yeah. i did it (and currently still am doing it). i can support mysql, postgresql and oracle currently...db/2 coming up shortly. its fairly simple (actually the hard part was loading the tables in the database) if you know what the hell youre doing....which most people dont. still, if you can afford a DBA you can afford to do the conversion.
  • The fringes of the SQL may be different but the core SQL language is controlled by the SQL Language Council so any SQL which comes under the SQL '92 spec should move across without difficulty. It's the extras that cause the problems where the different vendors have different approaches to various problems.

    Oracle is not SQL '92 compliant, as it lacks the JOIN operator in the WHERE clause of a SELECT statement. Yes, you can still join many, many tables -- just use the older syntax. It probably is SQL 92 incompatabile in other regards, too.

    For the author's question, this won't be a problem. For the conversion the other way, though, it might.

    I don't mean to be too blunt, but, let me state the obvious: If you are using the DBMS to it's full potential, you are hopefully using many stored procedures. You'll have to recode them all, and you might have to change your glue code in the business logic layer (in Perl, you put all the DBI stuff into modules, right?). If you are using stored procedures, it is going to be a LOT OF WORK. If not, it'll probably be simple, although you won't gain anything noticiable by users. If you aren't using the stored procedures, hints, and other Oracle-specific functions, I'd just convert over to Postgress or MySql (if you aren't using foriegn keys, either) and save yourself lots of $$$.

  • IBM wants you to migrate. They have a team that will even help. Check outthis [ibm.com] IBM migration site for more info.

  • Have you ever done this ?

    Quite obviously not.

  • Well, since I forwarded this article to my DBA, only to find out *he* posted it, let me clarify things:

    This is software being deployed to customer sites, so it's not just in-house work. What we would like to do is support both Oracle or DB2 as a database option for our product. The application uses a fairly generic set of tables, and the SQL used is also pretty vanilla (although we would like to tune it for better performance, so this is where we may have problems supporting multiple DBs).
    So, anyone out there ever tried this? It's not just a conversion, it is potentially a case where we have to support multiple database systems...

  • As a matter of fact i have. Not into DB2 but into several other Databases. I have never used DB2 but it's language should be similar to other databases. So the Conversion is possible.

    As for the code part. If there is a jdbc driver for DB2 i see no problem. Sure it's up to the Code but feasible.

    So please explain yourself. Did i miss something?

    Maybe the reason is that i've already done similar things and things which seem trivial to me seem difficult to you?


    --

  • I forwarded this article to my DBA, only to find out *he* posted it

    ROFL !

    How big is this thing ? The trouble with a DB2 port, compared to an Oracle -> SQL Server port, is that it really is quite different internally. A good Oracle data model looks a lot like a good SQL Server data model, and both subset quite trivially onto a MySQL data model (which isn't good, but it's the best you'll get).
    DB2, OTOH, will suck performance-wise, unless it's re-designed and tweaked by someone dressed in a white shirt and a dark suit. It's a whole different bucket of cod, and you need to grok IBM to get far with it.

    If it's a little thing, then you can live with the lumps. If it's a biggie, then you need help to do the tweaking. Last time I looked (early '99) IBM were pretty clued up on being helpful with this.

    The SQL dialects are about as equivalent as English and Welsh.

  • The poor excuse of a standard called SQL doesn't cover real world stuff. It's nice for textbooks but that's it.

    Virtually all the databases I've seen do things differently in such important/common things like:

    1) Dates, times and timestamps
    2) Autoincrementing counters/rowids/whattheycallit and how to get the rowid/whatever you just inserted.
    3) Transaction behaviour - do/which errors cause implicit rollback? What's isolated and how.
    4) Handling of large objects or large fields.

    ODBC and stuff like that does help somewhat, but you lose significantly in performance.

    Then there are also the gotchas like different rounding behaviours. e.g where somehow "27 Nov 2000" isn't "27 Nov 2000" because there's a little teensy time portion that's not displayed ;). Also max limits can often be annoying- like max field lengths. And better check case sensitivity and how it's handled.

    And if you actually used the special features you _paid_ pots of money for it's even worse - e.g. full text indexing, tree traversal, multimedia stuff, object orientation, parallel whatever or clustering or .

    If you didn't (and aren't going to ) use any of those special features then erm maybe you are spending too much on databases.

    In that case you may consider trying something free instead. If you're not a super large site and won't be in the next 5 years or so then I'd recommend Postgresql.

    Because if you have no difficulty porting from Oracle to DB2, then it's probably going to be even easier for you to port from Oracle to Postgresql. Because 1) you probably have a reasonably manageable App+DB situation and 2) AFAIK Postgresql and Oracle are more alike than DB2 to Oracle.

    And all this is just about _using_ the databases, not _administering_ the databases!

    Better be extra nice to your DBA if you really value your data. If not your data will just be slammed into the new database, and any important bits dangling out will just go poof - "Well you said it had to be done by Friday".

    Cheerio,
    Link.

Slashdot Top Deals

Without life, Biology itself would be impossible.

Close