MySQL Vs Db2 – Part 1

by nivas on March 9, 2015

MySQL is open-source RDBMS and is gaining popularity in the recent years.. I am starting a comparison series for mysql and db2..This series will help MySQL DBAs to catchup Db2 quickly and vice versa.. Please post your feedback

1. Knowing the software version :


mysql> select version() ;

 

+-----------+
| version() |
+-----------+
| 5.5.28 |
+-----------+
1 row in set (0.00 sec)

mysql> show global variables like 'version%' ;
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.5.28                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | i686                         |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
4 rows in set (0.01 sec)

db2:

[db2iown1@localhost ~]$ db2licm -l
Product name:                     "DB2 Express-C"
License type:                     "Unwarranted"
Expiry date:                      "Permanent"
Product identifier:               "db2expc"
Version information:              "10.5"
Max number of CPUs:               "2"
Max amount of memory (GB):        "16"
Enforcement policy:               "Soft Stop"

2.Listing the instances

Db2:

[db2iown1@localhost ~]$ db2ilist

db2iown1

MySQL:

mysqld_multi

mysqld_multi report 1 returns:

Reporting MySQL servers

MySQL server from group: mysqld1 is running

3. Schemas:

MySQL: In MySQL schema and database mean the same. You can not create schemas within a database

Db2: In db2 you can have multiple schemas(logical grouping of objects) within a database

4. Start/Stop MySQL Vs Start/Stop DB2:

DB2:

You stop db2 instance using the below command:

[db2iown1@localhost ~]$ db2stop force
SQL1064N  DB2STOP processing was successful.
[db2iown1@localhost ~]$

MySQL:

You stop mysql instance using the below two methods

1)/etc/init.d/mysql stop

2)mysqld_multi 4 stop

5.Reorg Vs Optimize

Db2 Reorg and MySQL optimize are functionally similar to some extent. They help in defragmenting a table .  Db2 reorg has wide variety of options and when compared to MySQL optimize , it is more advanced

6).Analyse Vs Runstats

Db2 Runstats and MySQL Analyze are functionally similar to some extent.

7.Reorgcheck in db2

Db2 reorgcheck utility helps in identifying the tables in pending-reorg state.. MySQL does not provide a similar utility

8.transaction logs :

Db2:

Db2 has circular logs (Active) and archive logs.. Active logs are moved to archive location(LOGARCHMETH1) once the transactions are committed and written to disk.

MySQL(Innodb):

Innodb has two transactional logs(redo logs) created by default. These logs are used for crash recovery. There are also binary logs which record all the transactions and can be used for point in time recovery

9.Storage engines :

Db2:

There is no concept called storage engines

MySQL:

MySQL has a concept of storage engine which allows flexibility .. Below are some of the storage engines :

1) Innodb : Most widely used storage engine.. It is transactional engine and is ideal for OLTP workloads

2)MyISAM: Works well for read-only workloads. Not a transactional engine

3)Memory: Memory engine has all the data in memory . Very fast but data is not retained after restarts

4)Archive: Useful for archiving

10.mysqld_safe Vs fault monitor

db2 fault monitor and mysqld_safe are funcionally similar to some extent. But db2 fault monitor can also be used for auto restarts after system reboot

Db2:

fault monitor restarts db2 if it crashes

MySQL:

mysqld_safe restarts mysqld if the process crashes

Comments on this entry are closed.

Previous post:

Next post: