Solved

Change IBM DB2 to MySQL or PostgreSQL

Posted on 2014-04-14
9
1,770 Views
Last Modified: 2014-05-30
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.
0
Comment
Question by:FSOFT-SA
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 11

Accepted Solution

by:
MajorBigDeal earned 100 total points
ID: 40000764
I much prefer postgresql over mysql.  I use it with django, nginx, and python and it works really well.   I made this considered decision years ago but I think the overwhelming consideration now is that Oracle bought mysql and is in the process of killing the open source version.   That alone is a really good reason not to go with mysql.
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 100 total points
ID: 40000815
+1 for Postgresql. MySQL is more popular and you'll find lots of code/help for it, but in my experience postgres is more stable and faster in large databases.

If you use ERP-like SELECTS (with huge JOINs) a popular choice is Firebird. I have it running at 2 of my clients with no problems so far.

HTH,
Dan
0
 
LVL 27

Expert Comment

by:tliotta
ID: 40001059
In order to give really useful advice, we'd need to know the current DB2 version/release and platform.

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
0
 

Author Comment

by:FSOFT-SA
ID: 40001286
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 100 total points
ID: 40001393
Hi FSOFT-SA,

With that small a database, a better question might be why do you need to migrate away from DB2?  The free version of DB2 has some limitations compared to the paid-for licensed version, but a database your size shouldn't be affected by them.

Before undertaking a major conversion, take a look at DB2 Express-C.  It will cost the same as the open source DBMS (free) and shares a code-base with IBM's regular DB2 product.


Kent
0
 
LVL 61

Assisted Solution

by:gheist
gheist earned 100 total points
ID: 40003932
In postgresql you can define noop for encrypt/decrypt functions. Tables should go well, views may need some tweaking. Btw ibm has good references to ansi sql, so just get programmers working
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 100 total points
ID: 40006297
Given the limited size and complexity of the database, I'd say it comes down to what the developers feel most comfortable with.

I generally prefer DB2 primarily because there seems to be more conformance with SQL standards. But that can be a problem for developers who are less familiar with SQL and who need numerous extensions.

I wouldn't expect big problems going any of the directions as long as the developers are willing.

Tom
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40006357
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.
0
 

Author Closing Comment

by:FSOFT-SA
ID: 40100527
Thanks all for your advice.
From all of your comments, my customer chose PostgreSQL.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Insert Query Help, part2 6 56
MySQL - Limit or Top Records 15 42
MySQL Select latest stock count records 11 55
MySql hide the stored procedures 2 37
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Creating and Managing Databases with phpMyAdmin in cPanel.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now