Oracle to DB2 Migration Unlocked
Strategies for handling locking issues while moving from Oracle to DB2 for z/OS
Migration from Oracle Database to the IBM® DB2® on z/OS® database is not completely seamless and must be planned carefully. Administrators may face issues when migrating from Oracle to DB2 on z/OS due to locking differences between the two databases (see Table 1). However, these issues can be mitigated to a great extent.
One of the key locking behavior differences between Oracle and DB2 on z/OS is that Oracle does not hold any locks on a row while reading, and DB2 does. This difference can lead to a high probability of increased lock waits and issues such as deadlocks and timeouts in applications migrated from Oracle to DB2.
DB2 on z/OS
|Read queries do not hold any locks on the rows unless explicitly requested by a FOR UPDATE clause.||By default, the read queries hold share-level locks.|
|Only row-level locking is possible implicitly. The entire table can be locked explicitly if needed.||Default locking is at page level, but locking also possible at row, table, tablespace, and LOB levels.|
|There is no concept of lock escalation.||Lock escalation can happen if the number of locks increases. Escalation results in promotion of row-level lock to table-level lock and page-level lock to tablespace-level lock, so that the number of locks is reduced.|
|Dirty read is not possible since there is no concept of Uncommitted read.||Uncommitted read is possible and is one of the ways to read rows without holding share-level locks.|
To handle locking issues, mitigation strategies are required at the database, application, and operational levels.
Several types of database design changes can help mitigate locking issues:
- Row-level locks. Override the default DB2 page-level lock setting and reset so that the table uses row-level locking to increase concurrency. Row-level locks should be implemented carefully, since there could be increased overhead due to the growing number of locks, and the potential for lock escalation increases if not properly handled.
- Index and query tuning. Read queries, which might require a table scan, would not cause a problem in Oracle but would be an issue in DB2 on z/OS because they would lock the entire table. To mitigate this problem, ensure that all queries are optimized in terms of index and access path so there are no unnecessary table scans, especially for tables that are accessed in online transactions.
- Partitioning. Concurrency can be increased a great deal, especially for batch runs, by introducing partitioned table spaces in DB2 for z/OS. Data can be segregated into different partitions by identifying a partitioning key and having the data reside on different partitions based on the range of values of the key. When running a batch, multiple threads can be initiated based on the partitioning key value, so that the different threads access different partitions and provide higher concurrency.
Some of the key application design changes that can help mitigate locking issues include:
- Skip locked data. You may have a situation in which different transactions are going against the same table and you need to access only the rows that are not currently locked in any given table. In these cases, DB2 provides an option to query only the rows that are not locked by using the SKIP LOCKED DATA option in the SELECT, UPDATE, and DELETE clauses. This option applies only when the isolation levels of Cursor Stability (CS) and Read Stability (RS) are in place and also applies only to row-level and page-level locks.
- Uncommitted read. In cases where it is acceptable for the response from a read query to have uncommitted data, try using the WITH UR option in read queries in DB2, since this does not hold any shared locks. This option is very helpful for user queries run by application testers or business analysts in the user acceptance testing or production regions. These queries could contend with application queries, which can be avoided by running the user queries using the WITH UR clause.
- Table access ordering. Increases in locking contentions can also occur when migrating from Oracle to DB2 for z/OS due to improper ordering of access to tables in parallel transactions. Consistent access ordering can help avoid this problem. For example, if transaction 1 accesses table A first and then table B, subsequent transactions should use the same order when accessing the same tables.
Contention can occur due to different types of workloads going against the same table—for example, batch and online workloads accessing tables at the same time, or different batches accessing the tables at the same time. In these scenarios, one option is to make operational-level changes such as rescheduling the conflicting transactions. It may be possible to run a batch at off-peak hours when the online workload is not running. In case of two batches running parallel, try running one after the other, or put dependencies in place so that one cannot run when the other is running, and vice versa.
There are several key locking differences between Oracle and DB2 on z/OS that can lead to locking issues with applications migrated from Oracle to DB2. However, as this article demonstrates, options are available at the database, application, and operational levels to greatly mitigate any issues that might arise.
|[followbutton username='IBMdatamag' count='false' lang='en' theme='light']|