FSOFT-SA
asked on
Change IBM DB2 to MySQL or PostgreSQL
Dear Experts,
We need to convert database from IBM DB2 to MySQL or PostgreSQL,
Please give me some adivces:
- Which opensource DB should we choose: MySQL or PostgreSQL?
- What is the advantage and disadvantage of these two DB?
- What is the risks/issues when convert from IBM DB2 to MySQL/PostgreSQL?
Regards.
We need to convert database from IBM DB2 to MySQL or PostgreSQL,
Please give me some adivces:
- Which opensource DB should we choose: MySQL or PostgreSQL?
- What is the advantage and disadvantage of these two DB?
- What is the risks/issues when convert from IBM DB2 to MySQL/PostgreSQL?
Regards.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all.
@tliotta: current system uses:
- only tables & views: over 200 tables and over 100 views
- some queries for some views are a little complex with many select and deep join
- data volume is about over 5GB and may be increased
- there're CLOBs and BLOBs
- there're ENCRYPT_CHAR() , DESCRYPT_CHAR() (of DB2) when creating views
Please help.
@tliotta: current system uses:
- only tables & views: over 200 tables and over 100 views
- some queries for some views are a little complex with many select and deep join
- data volume is about over 5GB and may be increased
- there're CLOBs and BLOBs
- there're ENCRYPT_CHAR() , DESCRYPT_CHAR() (of DB2) when creating views
Please help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From an SQL development viewpoint, I prefer DB2 SQL simply for its date handling. Oracle is a bit clunky, and SQL Server is just plain awful. The ANSI parts are, of course, common.
On gotcha is that the default SQL style in MySQL is their proprietary version. It does some nice things, but the non-ANSI portions move it even further from "standard SQL" than do the extensions used by Oracle, DB2, and Microsoft. Unless you need those extensions, set the SQL mode to ANSI from the start.
But that kind of brings you full circle. There's no point in changing from DB2 to a different free engine if you're going to run in ANSI compatible mode.
MySQL and DB2 are the easiest to administer. (I assume the PostgreSQL is, too.) I can usually talk someone through an issue on the phone. That's usually not the case with Oracle and SQL Server.
On gotcha is that the default SQL style in MySQL is their proprietary version. It does some nice things, but the non-ANSI portions move it even further from "standard SQL" than do the extensions used by Oracle, DB2, and Microsoft. Unless you need those extensions, set the SQL mode to ANSI from the start.
But that kind of brings you full circle. There's no point in changing from DB2 to a different free engine if you're going to run in ANSI compatible mode.
MySQL and DB2 are the easiest to administer. (I assume the PostgreSQL is, too.) I can usually talk someone through an issue on the phone. That's usually not the case with Oracle and SQL Server.
ASKER
Thanks all for your advice.
From all of your comments, my customer chose PostgreSQL.
From all of your comments, my customer chose PostgreSQL.
Then we'd need to know something about how it's currently being used. E.g., have there been numerous stored procs, triggers, etc., added to make the database enforce significant business rules? Are there lots of complex queries against the database? What are the data volumes? Are features such as BLOBs used in significant parts of the database? Is any encryption important?
A general description of the database and the application environment could really tip advice towards postgresql or keep it more with MySQL.
Tom