MySQL Vs Db2

by on April 25, 2014

MySQL is open-source RDBMS and is gaining popularity in the recent years.. I am starting a comparison series from this week.. 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



style="display:inline-block;width:250px;height:250px"
data-ad-client="ca-pub-3128777066323817"
data-ad-slot="8110217887">

{ 0 comments }

Thanks to every one who participated in the beta testing of the diag tool (http://db2tutorial.net/beta-testers-needed-db2-diaglog-monitor-tool/).. Special thanks to Paul, Manoj , Raf , Rajesh , Alex

Thanks to DB2 expert Serge Rielau for liking the tool

 

 

Below are the results (tested on multiple servers):

 

 

 


We are working on few improvements and more people are invited for the testing.. Please send the following details to db2tutorialnet@gmail.com :

 

Name:

Email:

Contact number:

 

 

{ 0 comments }

Beta testers needed – db2 diaglog monitor tool

July 15, 2013

  Earlier we discussed about diaglog and its importance. We also designed an excel utility(http://db2tutorial.net/excel-sheet-to-monitor-your-diaglog-try-it-today/) Now we are coming up with a GUI tool that monitors diaglog for all your servers. All the data is cached locally and it gives a monthly,weekly and daily overview of errors across all your servers. So you have diag [...]

Read the full article →

Backup validation – Are you doing it right ?

May 12, 2013

Backup and Recovery is the most important task for any DBA.  As a DBA we have to ensure that all our backups are running successfully and reaching netbackup/TSM . Below are some of the common scenarios we face : (adsbygoogle = window.adsbygoogle || []).push({}); Make sure that you validate all your critical databases so that [...]

Read the full article →

Split mirror and db2relocatedb – powerful db2 recovery options

April 12, 2013

All of us are familiar with backup/restore commands  and this is the most widely used method for db2 backup and recovery. But there are other ways you can use to backup and recover your data. 1)Advanced Copy Services(ACS) 2)Split mirror 3)db2relocatedb Let me explain my experience with these utilities. We had a 4TB database and [...]

Read the full article →

db2 wallpapers- db cfg parameter listing

December 28, 2012

DBM_cfg_print_URL DB_cfg_print_URL These charts will provide the following information :

Read the full article →

Virtualization basics – zlinux and P series

November 15, 2012

Virtualization is the process of creating virtual version of a device or a resource. Virtualization can occur at different levels: Server virtualization Storage virtualization OS level virtualization Network virtualization Application virtualization In this article we are going to discuss on zlinux and AIX P series. (adsbygoogle = window.adsbygoogle || []).push({}); Below are the common terms [...]

Read the full article →

Google searches for db2 terms & DBA job trends

May 14, 2012

Have you ever wondered about the popular db2 terms on Google.. How many people are searching for a word like ‘db2 compression’ ? How many people are interested in the ‘Purescale’ technology ? Below are some of the popular terms on Google search. To get the entire data please visit www.google.com/adwords and search for “db2″ [...]

Read the full article →

Interview with db2 9.7 advanced admin book author

February 1, 2012

Adrian Neagu and Robert Pelletier authored a db2 9.7 book which is scheduled to release in March 2012. Below is the Amazon link and you may preorder it now.. I am in the technical review board for this book and I found the content interesting , especially the code snippets for DBA tasks About the [...]

Read the full article →

db2 wallpapers- dbm cfg parameter listing

January 27, 2012

Below is the chart that you should pin to your cubes: It depicts: You can print the chart by going to the below URL DBM_cfg_print_URL

Read the full article →