Characteristics

DB2 tables

MyISAM 

InnoDB 

BDB tables

tables

tables

Commitment 

Yes

No Yes

Yes

Control

Isolation level

UR, CS, RS, 

No

RU, RC, RR, 

RR

Serializable

Rollback on DDL

Yes

No

No

No

Consider using table level locking for:

Applications that use mostly reads such as Data Warehouse and Business 

Intelligence applications

Applications reading and updating through key positioning such as 

UPDATE... WHERE Custno = ?

Applications using INSERTs with subselects and only a small number of 

UPDATE and DELETEs

Consider to use for row level locking for:

Applications requiring a high level of concurrency and OLTP capabilities

Many SELECTs with only small result sets

Applications with high UPDATE/INSERT/DELETE frequency

However, let us attempt to summarize the concurrency issues that may arise 

when migrating a MySQL application to DB2 based on the two MySQL table 

types, which we consider significant:

MyISAM tables provide a high level of concurrency since SQL processing 

occurs in autocommit mode and no row level locks are maintained. When 

migrating to DB2 ensure your application operates in autocommit mode, 

which is by default not the case. Verify the lowest isolation level required for 

your application and MyISAM tables.

InnoDB tables provide concurrency control very similar to DB2. Please be 

aware that default transaction isolation for InnoDB is 

repeatable read 

while 

DB2 operates by default with 

cursor stability

.

7.3.5  Specifying the isolation level in DB2

Because the isolation level determines how data is locked and isolated from 

other processes while the data is being accessed, you should select an isolation 

level that balances the requirements of concurrency and data integrity. The 

isolation level that you specify is in effect for the duration of the 

unit of work.

230 

MySQL to DB2 UDB Conversion Guide