Leveraging MySQL skills to learn DB2 Express: DB2 versus MySQL administration and basic tasks

Skills transfer to DB2 Universal Database

Does your database environment require you to have cross-database skills? If you already know MySQL, you can use many of the skills you already have to learn DB2® Express. This article, the first in a series on leveraging your MySQL skills to learn DB2, starts you out with a comparison of administrative tasks, data types, SQL, locking, and more.

Allan Tham, Presales Specialist, ASEAN Techline, IBM

Allan ThamAllan Tham works as a DB2 Content Manager Technical Presales Support for Business Partners. He helps business partners solve a wide range of technical issues. Allan is certified for administration of DB2 Content Management. Prior to joining IBM, Allan worked in an end-user environment, where he was an Oracle DBA for three years.



23 February 2006

Also available in Chinese Russian

Introduction

Mananging diverse databases is a way of life for most database administrators. It's rarely the case for DBAs to simply manage a homogeneous set of databases. Often, in a corporate data center, you will have various back-end repositories such as data in the form of spreadsheets, images, or video/audio sitting in databases, including DB2 Universal Database™, Oracle, Microsoft® SQL Server, Informix®, Sybase, or open source databases such as MySQL. Most corporations require DBAs to have cross-database skills to manage these disparate databases, and individuals who acquire wide knowledge of various data sources will be indispensible to the corporate data center.

This article is the first of a series that will help you use your current MySQL DBA skills to begin learning about DB2 Express. This article will get you on the DB2 Express fast track as it highlights the features and functions and looks at the strengths of DB2 Express as compared with the open source database, MySQL.

This article compares general administrative tasks but does not delve into issues regarding manageability, stability, and scalability. Nor will it look at how the engines are written, or how the optimizer works for each. The goal is to begin the skills transfer process in an overview manner, or to describe the two products for those who are curious enough to make a comparison.

For cost-conscious starters, IBM provides a free version of DB2 Express called DB2 Express-C. DB2 Express-C has the same code base as DB2 Express, Workgroup Edition, and Enterprise Edition. As businesses grow, customers tend to move to either DB2 Workgroup or Enterprise where true enterprise high availability and performance can be achieved using the Data Partitioning Feature (DPF) and High Availability Disaster Recovery (HADR). DB2 Express-C is ideal for C/C++, Java™, .NET®, and PHP developers and can be downloaded and used for free. Note, however, that DB2 Express-C has a limitation of 2-WAY CPUs and 4GB RAM. Refer to the DB2 UDB distributed platform comparisons table for edition comparisons.


Topics

The article will look at the following topics:

Now let's proceed to look at each category in detail.


Installation

First look at the installation steps for both MySQL and DB2 Universal Database Express edition. Both are simple to install.

MySQL installation

There are a few more steps to install the MySQL server (including configuring it after installing the code) than there are for installing DB2 Express. However, both database servers are easy to install. Both MySQL and DB2 Express allow post-install database configuration using GUI administrative tools.

Notes on MySQL installers - what is included

For MySQL 5.0.18 Windows® installation, the server installation does not come with a GUI administrator or a query browser. After installing MySQL server, you get the following:

  • MySQL Command Line Client
  • MySQL Manual
  • MySQL Server Instance Configuration Wizard

See Figure 1 below:

Figure 1. What you get with MySQL
What you get

In order for to have GUI Administrator and Database Design & Modeling; SQL Development (replacing MySQL Query Browser), you must download the MySQL Workbench. Note that there are different downloads for various platforms. DB2 Express, on the other hand, installs all necessary administrative tools and utilities in one go; there is no need for a separate download.

DB2 Universal Database installation

DB2 logo DB2 Universal Database Express editon installation is very straightforward. Follow the steps below to install.

  1. Click Install Product from the welcome screen.
    Figure 2. Welcome Screen
    Welcome Screen
  2. Click Next.
    Figure 3. Select the product to install
    Select the product to install
  3. Click Next.
    Figure 4. Setup wizard
    Setup Wizard
  4. Accept the license agreement and click Next.
    Figure 5. Accept the license agreement
    Accept the license agreement
  5. For this example, I chose Typical. You can also choose Custom or Compact. Click Next.
    Figure 6. Install options - Typical, Compact, or Custom
    Install options - Typical, Compact or Custom
  6. Choose the drive and directory to install. You can leave it at the default.
    Figure 7. Install directory
    Install directory
  7. Enter a password for db2admin. The db2admin ID is the default user created for DB2 server administration.
    Figure 8. User information
    User information
  8. Click Next. Note that you can configure protocols and startup options here.
    Figure 9. Instance configuration
    Instance configuration
  9. Click Next to install.
    Figure 10. Click to install
    Click to install
  10. Click Next. Note that you can configure protocols and startup options here.
    Figure 11. Finishing up the installation
    Finishing up the installation

Once installed, DB2 Express has the following installation layout (overview). The default directory for DB2 Express install is C:\Program Files\IBM\SQLLIB. Note that the table below is the layout for the Windows platform only. Linux® and UNIX® have different path layouts.

Table 1. Installation layout for DB2 Express
Directory nameContent
/adsmcontains dsmapipw.exe for password encryption
/BINcontains all necessary binaries to start, stop, and administer databases
/bndcontains all the bind packages
/convcontains conversion tables for codepages
/DB2DB2 instance related files. For example, it contains the db2 log file, db2diag.log
/DB2DAS00contains DB2 Admin Server- (DAS) related files. For example, it contains db2dasdiag.log in its dump directory. If you have more than one DAS, it will continue the number such as DB2DAS01 and so forth. Typically, having one DAS is sufficient.
/javacontains JDBC™ drivers
/samplescontains lots of sample codes
/TOOLScontains mostly JAR files for DB2 tools
/tutorialscontains various tutorials

System structure

There are two system structures that will be discussed in this article:

  • Memory structure
  • Containers

First, let's look at a high level at the key memory layout for DB2 Express. For detailed memory management, refer to the developerWorks article "The DB2 UDB memory model: How DB2 uses memory" (listed in the resources section).

Memory structure

Figure 12. DB2 Express memory structure
DB2 Express memory structure

These components serve the following functions:

  • Package Cache - Memory allocated to store both static and dynamic SQL statements
  • Buffer Pool - Memory allocated to store data before flushing it to disks
  • Log Buffer - Memory used to store all changes to database before it is flushed to the logs on disk

Containers

Depending on the table type being used, MySQL stores data in either single files, multiple files, or tablespaces. At the end of this discussion, Table 2 summarizes containers being used for MySQL and DB2 Express.

Figure 13. MySQL containers
MySQL containers

DB2 logo

In contrast to MySQL, DB2 Express stores everything in tablespaces. Tablespaces are logical representations of physical containers in filesystems. Here are some facts about tablespaces:

  • A database must have at least one tablespace. By default, a standard DB2 Express installation will create three tablespaces:
    • Syscatspace - Stores system catalog information
    • Tempspace1 - Stores system temporary tables. A temporary tablespace can be either system- or user-defined. It is best to create a user temporary tablespace from the system one.
    • Userspace1 - Stores user data

    The layout of DB2 Express containers in the physical file system is seen as follows. C:\DB2\ is the default database path for your database manager. You can use the command list active databases to find out the database path, as well. DB2 database structure is laid out in this fashion, with each layer in the hierarchy to represent the following:

    Figure 14. DB2 Express container layout
    DB2 Express container layout
    • Drive/Directory - The drive or directory specified on the CREATE DATABASE command
    • DB2 Instance Name - The name of the DB2 Instance owner
    • NODE0000 - The partition number of the database. 0 for a non-partitioned database
    • SQL00001 - Database ID starting from 1
    • SQLOGDIR - The default log directory for the database
    • SQLT0000.0 - The catalog tablespace, SYSCATSPACE
    • SQLT0001.0 - The temporary tablespace, TEMPSPACE1
    • SQLT0002.0 - The user tablespace, USERSPACE1

    At any time, administrators can create more tablespaces using, for example, the commands shown in Listing 1 and Listing 2. (For complete create tablespace syntax, refer to the Information Center.)

    Listing 1. Creating a system tablespace in DB2 Express
    Create System Temporary Tablespace systemp1_space
     	managed by system      
    	using ('c:\systemp1_space','d:\systemp1_space')
    Listing 2. Creating a user tablespace in DB2 Express
    Create User Temporary Tablespace usertemp1_space
    	managed by database   
    	using (file 'c:\userdata1\usertemp1_space' 10000, 
                                file 'd:\userdata2\usertemp1_space' 20000)
  • A table can be split across multiple tablespaces.
  • Views, triggers, and stored procedures reside in tablespaces, as well.
  • Administrators can create as many tablespaces as they need with as many containers needed. The auto-extend feature is supported.
  • Tablespaces can be either system managed (SMS) or database managed (DMS).

Most often, DB2 database administrators will face the decision of either making a container system managed or database managed. The decision is purely a combination of a few factors, such as managebility, business requirements (such as database size and growth rate), and performance considerations. Typically the use of SMS is suitable for a smaller environment where the system can allocate more space when it's required. For heavier and bigger environments, DMS is preferred, as administrators can allocate space with the auto-extend feature. It's not too uncommon, however, to have a combination of both in a database, for example, having catalog and temporary tables in SMS while indexes and data sit in DMS.

Table 2 sums up the differences between MySQL and DB2 Express containers.

Table 2. Container differences
DatabaseTable typeComment
MySQLMyISAMStores as .MYI for index file and .MYD for data file. Only one file for index and data, respectively.
MySQLInnonDBStores data in tablespace identified by the path parameter, innodb_data_file_path. By default this value is set to ibdata1:10M:autoextend. Multiple data files can be used
MySQLMerge.MRG file contains the names of the tables that should be used as one and .FRM for table definition. Multiple data files are used.
DB2 ExpressAll typesStores as table spaces that can span across multiple disks. There are two types of containers:
  • System Managed (SMS) - Operating system file managed
  • Database Managed (DMS) - Database manager managed

To determine precisely which tablespace type to use, either SMS or DMS, read the SMS vs DMS comparison in the DB2 Information Center.


Executables

First look at the important executables available in MySQL and their equivalents in DB2 Express. Subsequently, the executables specific to the latter will be highlighted. Here, this article will focus on executables in the Windows operating system. The executables for both databases on Linux and UNIX may differ.

Table 3. Executables available in MySQL and DB2 Express
MySQL executable nameDB2 Express executable nameComment
MySQLInstanceConfig.exeDB2 Control CenterMySQLInstanceConfig is used for instance configuration. DB2 uses Control Center for instance configuration. Refer to the Database configuration section of this article
myisamchk.exeChecking to ensure MyISAM table integrityDB2 uses Control Center for the same checking purpose. For index, you can use Check Index on Index window or Check Index on Tablspace window. For table, use the CHECK constraint to ensure integrity. SET INTEGRITY is used for table integrity as well. For example, using Control Center operation on table, you can set the integrity for a particular table.

The db2dart tool can be used to check architectural correctness of databases. Sister to db2dart is inspect. To check the integrity for backup images, use db2ckbkp command For the entire database health systemwide, use the Health Center GUI.

myisampack.exeCompresses MyISAM tableUse VALUE COMPRESSION in create table statement to use the space saving row format at the column or table level to reduce space required for a table. Backup & Restore database API provide compression to reduce space is required.
mysql.exeMySQL client for WindowsDB2 Runtime Client - A separate product that can be installed to access remote DB2 databases
mysqladmin.exeMySQL Admin client - administrative tasks that can be carried using this commands are such as (amongst others) -
  • Create Database
  • Drop Database
  • Flush logs, tables, status
  • Process list
  • Shut down
  • Start slave
  • Stop slave
Basically, the DB2 command prompt can be used to do both client and administrative tasks. To access the DB2 comand prompt, go to All Programs -> IBM DB2 -> Command Line Tools.
mysqlbinlog.exeMysqlbinlog serves at least three purposes -
  • Logging all database transactions
  • Backup and restore
  • Replication
DB2 Express supports both online and archive loggings. All transactions will be logged for the same purpose as MySQL. DB2 Express supports infinite active log for a single transaction, so a long transaction will never fail (provided physical disk space is sufficient). Maximum log space supported is 256 GB.

Also, by default, DB2 logs errors to db2diag.log. You can analyze this log file using the utility, db2diag . The level of severity can be set at the following levels:

  • Info
  • Warning
  • Error
  • Severe
  • Critical
  • Event
mysqlcheck.exeChecks for database health, for example to check, repair, analyze, or optimize tables. For example, mysqlcheck -u root -p awtDB2 does not have a direct matching for table corruption check per se. Any table corruption will be written to the db2diag.log file with a timestamp pointing to dump trace. This dump files are not for the faint-hearted, as they are meant for a DB2 support representative. However, there are actions that can be taken against a table, for example:
  • Quiesce - For example, quiesce tablespaces for table <Schema Name>.<Table Name> Share|Intent to Update|Exclusive
  • Reorg - For example, reorg table <Schema Name>.<Table Name>
  • Reorg index - For example, reorg indexes all for table <Schema Name>.<Table Name> allow no access
  • Runstats - For example, runstats on table <Schema Name>.<Table Name> on all columns allow write access
  • Set Integriy - For example, set integrity for <Schema Name>.<Table Name> off no access cascade deferred
mysqld.exeRunning the MySQL server - many options available (for more info, type mysqld --help --verbose)You can start DB2 Express database instance using db2start. You can, however, start multiple instances by setting db2instance environment parameter to the instance you want to start. To stop the instance, use db2stop.

Note that you need SYSADM, SYSCTRL, or SYSMAINT authority in order to start and stop DB2 instances.

mysqldump.exeThis utility exports tables, certain rows within a table, an entire database, or a selection of databasesDB2 Export - DB2 Express supports export to four different formats of tables or rows of tables. The four supported file formats are: -
  • IXF - Integrated Exchange Format files
  • ASC - Non-delimited ASCII files
  • DEL - Delimited ASCII files
  • WSF - Worksheet Format files

To export is straightforward, for example export data from Employee table, export to employee.del of del messages emp.log select * from allanwtham.employee.

mysqlimport.exeThis utility import data from flat files DB2 Import - Supports same four formats as DB2 Export.
NALoadDB2 Load - High-speed loading data into databases
NAAuditdb2audit - audit facility for the detection of unknown or unanticipated access to data
NAExplaindb2expln - explains the access plan selection for static SQL statements
NAConfiguration Assistantdb2ca - configures remote access
NAdb2adminDB2 Admin Server- (DAS) related administrative tasks

Types of database tables

There are different types of tables for MySQL for different uses. You may mix and match these different table types within a database. Precisely which table type to use will depend on the need. The table type are as follows (the best known are MyISAM and InnoDB):

  • SAM

    SAM is one of the early table types. This type of table is for mainly for legacy purposes. It has been superceded by MyISAM table type, and the plan is to remove the support for this table type.

  • MyISAM

    MyISAM is the default table type. To create a table in MyISAM, you can either leave it as default or define explicitly using ENGINE keyword for example -

    Listing 3. Creating table using MyISAM
    Create table employee (
      empno int not null auto_increment primary key,
     firstnme varchar(30),
     lastname varchar(30),
     deptno int
      ) engine=MYISAM;

    MyISAM table type is a non-transactional safe table type. For multiple reads/writes with high concurrency, this may not be the table type preferred. MyISAM does not guarantee the Automicity, Consistency, Isolation, and Durability (ACID). However, MyISAM allows compression and full text search. For MyISAM type, the indexes are stored in .MYI (MyIndex) files and the data itself is stored in MYD (MyData) files. MyISAM table type does not have the notion of tablespace - all the data/indexes are stored in files. To check/repair MyISAM tables, use the utility myisamchk, and to compress the table, use myisampack. The only locking mechanism used in MyISAM is the table level locking, which translates that it is not suitable for high-usage environments.

  • InnoDB

    More and more businesses require the use of this table type. InnoDB is an ACID-compliant table type. InnoDB tables store data and indexes in a tablespace that allows numerous files to span across the filesystem. Developed originally by InnoBase Oy, this table type is suitable for a fast, high-performace, transaction-safe environment. InnoDB uses a finer grain of locking mechanism, row-level locking. Refer to the Locking mechanisms section for further details.

    To create a table type InnoDB, specify the keyword Innodb in the ENGINE option of a table create statement. See below for an example.

    Listing 4. Creating table using InnoDB
    Create table employee (
     empno int not null auto_increment primary key,
     firstnme varchar(30),
     lastname varchar(30),
     deptno int
     ) engine=InnoDB;
  • BerkeleyDB (BDB)

    The goals for this table type are to achieve the same objectives as InnoDB. Developed by Sleepycat, tables of this type are stored in B-tree with page-level locking. See The BDB (BerkeleyDB) Storage Engine.

  • Merge

    Merge type is a derivation of MyISAM by getting around the single huge file problem. It allows multiple MyISAM files to sit on different disks and queries can be executed based on the MERGE specification in the create table statement. You are required to specify the INSERT_METHOD option to either first or last in order to insert into a merge table. By default, only Select, Update and Delete are possible.

  • Heap

    Stores the entire table in memory. Speed is good; a crash will render data lost, however. It should be used as temporary, as its best.

DB2 logo DB2 Express stores data in containers - either system managed or database managed, as mentioned earlier. By default, tables created in DB2 Express are ACID compliant. There is no option for a non-ACID compliant table. The syntax for creating a table is very similar.

For example, to create a table employee with some columns:

Listing 5. Creating a table in DB2 Express
Create sequence sq1;
Create table employee1 
   (empno int not null default next_value of sq1,
    firstnme varchar(30), 
    lastname varchar(30), 
    deptno int, 
    primary key (empno))

ACID - Definition

See wiki definition of ACID.

  • Atomicity - Either all the tasks in a transaction must be done, or none of them. The transaction must be completed, or else it must be undone (rolled back).
  • Consistency - Every transaction must preserve the integrity constraints -- the declared consistency rules -- of the database. It cannot place the data in a contradictory state.
  • Isolation - Two simultaneous transactions cannot interfere with one another. Intermediate results within a transaction are not visible to other transactions.
  • Durability - Completed transactions cannot be aborted later or their results discarded. They must persist through (for instance) restarts of the DBMS after crashes.

There are four other table types supported by DB2 Express, as follows:

  • Temporary table -

    Also known as common table expression, this table is temporary table within the duration of a SQL statement. It can referenced as many times as needed without the need to re-compute. This table type can be used in place of a view.

  • Typed table -

    A table that is defined using structured type. A structured type is a data type containing a series of attributes.

  • Summary Table -

    A table that derives its definition from a query. Used mainly in warehouse environments.

  • Materialized Query Table -

    A table that derives its definition from a query of multiple tables. Used mainly in the warehouse environments.

Other than tables, there are other database objects such as indexes, functions, triggers, stored procedures, and so on, that constitute a working relational database. Table 4 compares these objects side-by-side:

Table 4. Similarities and differences in database objects
Object nameAvailability in MySQLAvailability in DB2 ExpressComment
User-defined table×Two types of user-defined tables
  • Temporary table (common table expression)
  • Typed table
The derived table in MySQL is something similar to DB2 Express temporary table
User defined functionsBoth scalar and column user-defined functions are supported
User-defined data types×There are three types of user-defined data type -
  • Distinct type
  • Structured type
  • Reference type
Stored procedureDB2 Express supports both SQL and Java stored procedures
ViewsStarting from version 5, MySQL supports view
Updateable ViewsDB2 Express supports typed view as well
TriggersStarting from version 5, MySQL supports triggers
Recursive SQL×Like recursive function, recursive SQL for DB2 Express can recursively use the result set to get to the final result
Sequences×MySQL doesn't support sequence natively, you can simulate a sequence, however.

Database configuration

In MySQL, you need to specify configuration parameters in the [mysqld] section of the configuration file `my.cnf'. On Windows systems, numerous configuration samples are provided, such as my-medium.ini. There are other my-xxx.ini files provided, as well. MySQL provides sample configuration file *.ini in Windows. A section of my-medium.ini is listed below. Note that you have to uncomment the relevant table types you choose.

Listing 6. Sample MySQL Configuration file
...
# The MySQL server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1 M
table_cache = 64
sort_buffer_size = 512K 
net_buffer_length = 8K
read_buffer_size = 256K 
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
...

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/var/ 
#innodb_log_arch_dir = /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

For DB2 Express, you can obtain and set the database or instance configuration values in either the Control Center or command line processor (CLP). To configure your database using the Control Center, right-click on the selected database and choose Configure Parameters.

Figure 15. Right-click to configure parameters
Right click to configure parameters

Note that you can change the values dynamically (some parameters will be reflected only when the database manager is stopped and restarted). Configuration parameters can be categorized as follows:

  • Application
  • Environment
  • Logs
  • Maintenance
  • Performance
  • Recovery

See a complete parameter list in the DB2 Information Center.

Figure 16. DB2 Express database configuration parameters
DB2 Express database configuration parameters

Alternatively, you can use the CLP to query and set configurations parameters. The syntax to update the database configuration is as follows:

Listing 7. Syntax for updating db configuration
	>>-UPDATE--+-DATABASE-+--+-CONFIGURATION-+---------------------->
           '-DB-------'  +-CONFIG--------+
                         '-CFG-----------'

                                   .----------------------.
                                   V                      |
        >--+---------------------+--USING----config-keyword value-+----->
        '-FOR--database-alias-'

        .-IMMEDIATE-.
        >--+-----------+-----------------------------------------------><
        '-DEFERRED--'

For example, to find out your administration server parameters, issue db2 get admin cfg. Or to get the db configuration for database SAMPLE, issue db2 get db cfg for SAMPLE.

As IBM strives for automation for DB2 UDB products, DB2 UDB Express comes with loads of automatic features through DB2 Control Center. Wizards such as Create Database with Automation, Design Advisor, Configuration Advisor, Configure Automatic Maintenance and much more are steps taken to automate common administrative tasks such as backup, configuration, and automatic maintenance of database objects and more. For example, administrators may choose to configure databases using Configuration Advisor. This feature saves administrators from the gruelling task of finding the right configuration parameters that best suit a database. Configuration Advisor is able to suggest the best configuration parameters to use. Administrators are encouraged to use this feature in order to achieve optimal database performance.


Graphic User Interface (GUI)

GUI administraton inevitably became a way of life for many administrators. Not only does the GUI serves as a fast and easy way to perform certain tasks, but also helps if you've forgotten the syntax for a command (or if you've never learned the commands at all). Most often, ease of use is the main factor that draws users to a GUI administration tool.

Two main GUIs provided by MySQL are MySQL Administration and Query Browser. Those who administer MySQL traditionally are not big fans of GUI administration. Often, the command line is still the preferred method. Currently, the latest available MySQL Administrator is version 1.1.6. In Figure 17 you see the login screen for MySQL:

Figure 17. MySQL Admin - Login
MySQL Admin - Login

Once logged in, tasks such as those listed below can be carried out in this GUI Administrator (amongst others):

  • Service Control - Start and stop service
  • Startup parameters - Parameters for both myISAM and InnoDB, buffer and cache, logs files, security and network settings
  • User Admin - Assigning priviliges to users and groups, and password management
  • Health - Memory and connection health check
  • Backup and restore
Figure 18. MySQL Admin - Tasks
MySQL Admin - Tasks

Note also that you can use phpMyAdmin tool that comes with WAMP (currently at version 1.6.0).

DB2 logo

There is much to say regarding the DB2 Express GUI. All administrative tasks can be carried out using this GUI, including general administration, health monitoring and setup, and more, as you see in Figure 19:

.

Figure 19. DB2 Express GUI administration - different aspects
DB2 Express GUI administration - different aspects

Among all the administrative GUI tools, you may find that DB2 Control Center is the one you visit most. You can start the DB2 Control Center using the shortcut by clicking on the green hard disk shaped icon at the right bottom tray on your Windows desktop:

.

Figure 20. DB2 Express GUI Administration - Control Center
DB2 Express GUI Administration - Control Centre

The DB2 Express Control Center is context sensitive. Right-clicking on an item will result in different drop-down task menus for you to act upon, depending on the context. For example, right-clicking on a particular database gives the following tasks options:

Figure 21. DB2 Express Control Center - list of database-related tasks
DB2 Express Control Centre - list of database related tasks

One point about DB2 Control Center is that you can use the SHOW COMMAND button to see the equivalent command for that task. On some occasions, you can opt to save the command to a script and schedule it to run at specified times. There are, in fact, many useful features including the Design Advisor and the Configuration Advisor, which I will not cover here. These notable features will be covered in an upcoming article.


Command line processor

MySQL provides a console for command line (mysql.exe). Administrative tasks can be carried out using this console.

Figure 22. MySQL command prompt
MySQL command prompt

DB2 logo

DB2 Express provides a console much the same as MySQL. One way to invoke this is to go to All Programs -> IBM DB2 -> Command line tools -> Command Windows.

All administrative tasks can be carried out in this console.

Figure 23. DB2 Express command prompt
DB2 Express command prompt

SQL comparisons

Admittedly, it's true that not all SQL statements are truly portable from one database to the other. This applies to migrating your SQL from MySQL to DB2 Express. Here are a few items this article will look at where SQL statement differences will be highlighted. This is only a small attempt to reconcile some differences between these two systems without getting into work-arounds. Note also that not all database features supported in DB2 Express are supported in MySQL currently. For example, foreign key constaint capability is only available to InnoDB with the slate support list for MySQL 5.1 as follows:

  • Foreign key for all table types besides InnoDB
  • Hot backup for MyISAM table
  • Rename database
  • Column level constraints

Let's look at the differences and similarities from the following angles:

  • Select statement

    The standard SQL statement supports keywords such as DISTINCT, GROUP BY, ORDER BY and HAVING. Both MySQL and DB2 Express support this well. However, to limit the resultset being returned, MySQL uses the keyword, LIMIT, while DB2 Express uses FETCH FIRST n ROWS to limit resultset being returned. A sample fetch is shown in Figure 24:

    Figure 24. Using Fetch n Rows in DB2 Express
    Using Fetch n Rows in DB2 Express

    Both MySQL and DB2 Express support CASE expression for conditional checking within SQL statement. For example, you can specify a desired output for queries that meets certain conditions. In the following case, you classify employees according to their earnings:

    Listing 7. Set Transaction syntax
    Select empno, firstnme, lastname, 
       case
         when integer (salary) > 40000 then 'High'
         when integer (salary) > 30000 and integer (salary) < 40000 then 'Medium'
         else 'Low'
        end
    from employee

    Apart from this, DB2 Express supports nested table expressions, as well. A nested table can be considered as a local temporary table that is defined and used within the scope of one SQL statement.

    MySQL has single value subquery that is equivalent in DB2 Express to a scalar full select.

  • Joins - The following table summarizes the joins available in both MySQL and DB2 Express -
    Table 5. Types of Joins
    Types of JoinsMySQLDB2 ExpressComment
    Inner joinInner join is a join that presents rows that exist in the joined tables. Traditonally, both use commas to get this join. It is the Cartesian product between the specified tables. In MySQL, however, it is also known as cross join. DB2 Express does not use the keyword cross.
    Cross joinSame as inner join.
    Left [Outer] joinLeft join or left outer join is a join that presents values for matched values and those that present only in the left table. In MySQL for example, if you need to select all rows from tableA that do not exist in tableB, the SQL statement looks like this - select tableA.* from tableA left join tableB on tableA.id=tableB.id where tableB.id is null.
    Right [Outer] JoinExactly opposite to left join. It presents matched values from joined tables and those that present only in the right table.
    Full [Outer] Join×Full Outer join is a join that presents matched values from joined tables and those that present only in other tables either left or right. This is uniquely available for
    Straight join×In MySQL, Straight join is identical to join, except that the left table is always read before the right table. Although there is no use of this keyword in DB2 Express, you can achieve the same goal in DB2 Express.
    Natural join×In MySQL, Natural join is equivalent to inner join. Although there is no use of this keyword in DB2 Express, you can achieve the same goal in DB2 Express.

    Note that you can assign the join type from GUI SQL Assist. A sample below shows how SQL Assist can be used with the available option of joins.

    Figure 25. DB2 join using SQL Assist
    DB2 join using SQL Assist

Authorization and privileges

In MySQL, there are two levels of privileges - administrative and user. All privileges can be granted or revoked using GRANT and REVOKE statements, respectively. A user can be granted user privileges such as create, select, update, delete, insert, execute, index, and so on, or system privileges including alter, drop, and shutdown. (Refer to privileges provided by MySQL.) The root user will have the following privileges by default:

Figure 26. Available privileges in MySQL
Available privileges in MySQL

DB2 logo

DB2 Express provides both authorization and privileges. Authorization is the higher set of predefined administrative rights and can be assigned to an individual user or groups of users to carry out general tasks, such as connecting to a database, create, drop and backup and restore databases while privileges -- both user and system -- are generally used for object manipulation. Fresh from install, DB2 Express provides the following authority levels:

Though SYSADM has the highest authority, most often, a user performing tasks such as backup and restore needs only SYSMAINT authority. Therefore, proper knowledge of authorities is essential for assigning users the appropriate authorities. In order to find out what authorities you have, issue the command get authorizations, which queries system catalog table SYSCAT.DBAUTH. In Figure 27, you see a sample of get authorizations output:

Figure 27. DB2 Express - Get authorization for current user
DB2 Express - Get authorization for current user

DB2 Express privileges are very similar to those of MySQL. Privileges (both user and system) can be granted or revoked using the GRANT or REVOKE commands. There are three types of privileges:

  • Control privilege - This privilege normally stays with the creator of an object. It's the ownership privilege. For example, if user A creates table B, user A will automatically be granted control privilege over table B.
  • Normal privilege - This privilege allows you to carry out a specific task. This privilege is granted explicitly or implicitly to carry out tasks on a database object such as SELECT, UPDATE, DELETE, and so on.
  • Implicit privilege - This privilege is granted when a higher privilege is granted to a user. For example, when a user excutes a package, implicit privilege is granted on-the-fly in order for the package execution to be successful without the explicit grant.

Figure 28 shows some of the privileges that can be granted to a user.

Figure 28. DB2 Express - Privileges administered using Control Center
DB2 Express - Privileges administered via Control Centre

Note that you can carry out the GRANT and REVOKE SQL statements from the CLP as well.


Locking mechanisms

MySQL InnoDB provides transaction-safe statements that supports four isolations described in the standard SQL-1992. In order to be transaction-safe, MySQL meets the ACID requirements. By default, MySQL uses the Repeatable Read isolation level for all transactions. However, you can change any incoming new sessions to have other isolation levels using the Set Transaction statement. You can either use SESSION or GLOBAL in the SQL statement. SESSION means the next connection will use the particular isolation set, while GLOBAL means all later connections will use the particular isolation set. Set Transaction syntax is shown in Listing 8:

Listing 8. Set Transaction syntax
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
    {READ UNCOMMITTED | READ COMMITTED
    | REPEATABLE READ | SERIALIZABLE}

For the four levels of isolation supported, Table 6 shows whether phantom, dirty, or non-repeatable read is possible (from strongest to weakest).

  • √ - Will happen
  • × - Will not happen
Table 6. Isolation and read characterisctics - Will or will not happen
Isolation levelPhantom ReadNon Repeatable ReadDirty Read
Serializable×××
Repeatable Read××
Read Committed×
Read Uncommitted

DB2 logo

DB2 Express provides similar levels of isolation. The four isolation levels supported by DB2 Express include, from strongest to weakest:

  • Repeatable read - RR
  • Read stability - RS
  • Cursor stability - CS
  • Uncommitted read - UR

With each isolation level providing certain transactional safety, the choice is a tradeoff, due to the performance implications of a stricter isolation level. For example, reading from a read-only look up table doesn't require Repeatable Read, and uncommitted read is sufficient . The default isolation level for embedded SQL statements is cursor stability (CS). There are a couple of ways you can change the isolation level (depending on context):

  • You can change this by using the keyword With in standard DML statements. For example, select * from employee with UR.
  • Use the change isolation level command, if you are doing it from command prompt.
  • Specify in the db2cli.ini file , if you are using DB2 call level interface.

Table 7 shows the isolation levels for DB2 and their read characteristics, from strongest to weakest:

  • √ - Will happen
  • × - Will not happen
Table 7. Isolation and read characterisctics - Will or will not happen
Isolation levelPhantom ReadNon Repeatable ReadDirty Read
Repeatable Read (RR)×××
Read Stability (RS)××
Cursor Stability (CS)×
Uncommitted Read (UR)

DB2 Express supports explicit locks for table spaces and tables. DB2 Express provides lock escalation when DB2 finds it cheaper to lock a table than to lock many rows within a table. Parameter such as Locklist and Maxlocks affect how lock escalation takes place.


PHP development support

With the increasing use of PHP as a Web development platform, developers often look for a quick install and strong development environment that saves hassle. Available with MySQL is a packaged-in quick deployment tool, WAMP (current version 1.6.1). IBM, together with Zend Core, provides a similar combinaiton for PHP development.

DB2 logo

Zend Core for IBM is a seamless, out-of-the-box, easy to install and support PHP development and production environment. The product includes tight integration with IBM DB2 Universal Database and IBM Cloudscape®, native support for XML and Web services, and support for increased adoption of Service Oriented Architectures (SOA). Zend Core for IBM delivers a rapid development and deployment foundation for database-driven applications. It offers an upgrade path from the easy-to-use, lightweight Cloudscape database to the mission-critical DB2 by providing a consistent API between the two. Refer to more information at Zend Core for IBM page.


LOB, video/audio management

While much of the buzz in data management today has to do with the Enterprise Content Management (ECM) space, MySQL is largely a structured data management product. Users with ECM needs must engage with existing ECM players that support MySQL as a back-end system. MySQL handles unstructured data using its series of BLOB data types.

DB2 logo

IBM, on the other hand, provides DB2 Content Manager as a content repository for both structured and unstructured data. In order to meet today's on demand business requirements,with 80% of data in unstructured formats, Enterprise Content Management (ECM) is seen to be a necessity in the corporate environment. Without the proper understanding of Enterprice Content Management (ECM), one can argue that it could be easy to build an enterprise content management system from scratch. Let's look at various digital content in a typical corporate, their pains, and how IBM DB2 Content Manager is built with feature-rich functions to meet this need.

Content may refer to:

  • Invoices, statements, reports
  • Fax and scanned papers
  • SCM, CRM & ERP data
  • Emails and desktop documents
  • Audio, video, and photo
  • Web content

Often customers face problems because their data is available in various formats and situated in various locations. Without a centralized repository, users may not be able to share information effectively, let alone collaborate to ensure smooth business continuity. Business process workflow is not enabled, and users find it hard to contribute to or search Web content. These are common pain points for most corporations.

Built with scalability in mind, DB2 Content Manager is a three-tier architecture that comprises a centralized index in Library Server and object storage in Resource Manager. The flexible Authorized user license enable deployment of as many Resource Managers as needed to cater to the business need. For example, two Resource Managers can be situated in the headquarters in New York, while Seattle, Atlanta, and Vancouver branches can each have a Resource Manager. It comes with lan cache to minimize the network traffic. IBM DB2 Content Manager supports hierchical storage management where objects can be migrated out in a proper and regulated period to external devices. For example, it automates the migration of objects after 6 months in DASD to external drives for 3 years and subsequently to be stored to tapes for 7 years.

IBM DB2 Content Manager also comes with authentication, privileges, and access control to ensure a tight and secure operation for users or groups of users. From document level actions, privileges with fine granularity such as create, read, update, delete, print, annotation modify, to Resource Manager collection access control, IBM DB2 Content Manager ensures proper access and use. Event logging can be turned on to further audit the content system.

From creation to management and dessemination of content, IBM life cycle managements banks on the rich features provided by IBM DB2 Content Manager. Some of the features of IBM DB2 Content Manager are listed as follows:

  • ad-hoc scanning
  • bulk load of content of various formats
  • native content viewer via Windows client, web client or portlet
  • check in/check out
  • process workflow (can be built with GUI workflow builder)
  • versioning
  • annotation
  • LDAP integration
  • event logging (both user and administrative activities)
  • integration with SAP & Siebel
  • integration with record management such as IBM DB2 Record Management to achieve regulatory compliance
  • integrate with customer existing line of business
  • contains Web services interface to use within applications or with other Web services
  • XML schema mapping

In the event of in-house content system development, IBM DB2 Express provides LOB/CLOB data type for this purpose.


Data types

Data types for MySQL and DB2 Express are similar. Note that DB2 Expess supports both built-in data types and user-defined data types (mentioned earlier). For an overview of how DB2 Express data types are organized, refer toFigure 29:

Figure 29. DB2 Express - Data type hierarchy
DB2 Express - Data Type hierarchy

With the above overview, let's map MySQL data types to the equivalent (or the closest match) in DB2 Express, comparing the following three main categories.

  • Numeric
  • String
  • Time & date

For information on similarities and differences in database objects, see Table 8.

For a complete description of DB2 SQL Limits, refer to the Information Center.


Backup and recovery

Backup and recovery are essential practices to ensure business continuity in the event of media failure. MySQL backup and recovery options depend greatly on the underlying table types. For example, both MyISAM and InnoDB table types allow cold backup of the database.MySQL Enterprise Backup does a hot backup of all tables that use the InnoDB storage engine. For tables using MyISAM or other non-InnoDB storage engines, it does a “warm” backup, where the database continues to run, but those tables cannot be modified while being backed up. See MySQL Hot Backups

DB2 logo

DB2 Express has offered both cold and hot backup from the very beginning. For cold backup, of course, there is a need for users to go offline with no access to the database being backed up. The implication for cold backup would be that data will be lost in case of media failure since the last backup. Hot backup, on the other hand, allows transactions to be continuously written to logs without the need to shut down the database being backed up. With proper planning, hot backup will guarantee no loss of data in the event of media failure. This backup method is required for the 24x7 environment.

There are two methods of logging available in DB2 Express, namely circular logging and archive logging. Choosing circular logging (written round robin and overwritten if full) enables you to carry out cold backup while choosing archive logging (archive logs that have been committed) enable you to do hot backup.

Whether you choose a cold or hot backup depends on the parameter you set in the db config such as those listed as follows:

  • LOGFILSIZ - Log file size. Total number of 4KB size to be allocated (default is 250)
  • LOGPRIMARY - Total of primary logs (default is 3)
  • LOGSECOND - Total number of secondary logs that can be allocated when the primary logs fill up
  • NEWLOGPATH - To change the location where the future log files are to be stored. Will only be effective once database is reactivated
  • MIRRORLOGPATH - A secondary path to write the logs to avoid single point of failure
  • OVERFLOWLOGPATH - Specify the location of the logs during a rollforward operation to allow access logs in multiple locations

To do cold backup is easy. You have to first shut down the database and issue the command, for example db2 backup database <db_name> to c:\backup.

To a hot backup is equally easy. First turn the log retain parameter on, and issue a command, for example db2 backup database online <db_name> to c:\backup.

Both cold and hot backup can be carried out in DB2 Express Control Center with few clicks. On top of that, DB2 Express provides incremental and delta backup.

  • Incremental - a backup of all changes since the most recent, successful, full backup
  • Delta - a backup of changes since the last successful, full, incremental or delta backup

A more complete backup and recovery issue will be discussed further in my upcoming article.


Conclusions

In this article, you looked at numerous aspects of both the MySQL and DB2 Express database servers. In an non-exhaustive manner, you went through comparisons in terms of installation, system structure such as memory, containers to backup, and recovery methodology. This article, as indicated earlier, is to give the existing MySQL database administrators a general overview of DB2 Express.


Disclaimer

This article is written to the best of our knowledge. Should you find any discrepancy, please feel free to contact the author.


Acknowledgement

Special thanks to Rahul Kitchlu and to Grant Hutchison for their reviews.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=104455
ArticleTitle=Leveraging MySQL skills to learn DB2 Express: DB2 versus MySQL administration and basic tasks
publish-date=02232006