Handling locking policies during migration from Oracle to DB2forz/OS

Migrating from an Oracle database to IBMDB2onzOS is not completely seamless and must be carefully planned. Because of the lock differences between the two databases, the Administrator may face various problems when migrating from Oracle to DB2onzOS (see table 1 ). However, these problems can be mitigated to a large extent. Oracle and DB2onz

Migrating from an Oracle database to IBM DB2 on z/OS is not completely seamless and must be carefully planned. Because of the lock differences between the two databases, administrators may face various problems when migrating from Oracle to DB2 on z/OS (see table 1 ). However, these problems can be mitigated to a large extent. Oracle and DB2 on z

Migrating from Oracle to IBM DB2 on z/OS is not completely seamless and must be carefully planned. Because of the lock differences between the two types, administrators may face various problems when migrating from Oracle to DB2 on z/OS (see table 1 ). However, these problems can be mitigated to a large extent.

One of the major lock behavior differences between Oracle and DB2 on z/OS is that Oracle does not lock the row while reading, but DB2 does. This difference may increase the chance of lock waits and problems (such as deadlocks and timeouts in applications migrated from Oracle to DB2.

	Oracle	DB2 on z/OS
1	Unless otherwise specified in the for update clause, the read query does not lock the row.	By default, read queries hold shared locks.
2	Only row-level locks can be implicitly executed. You can explicitly lock the entire table if needed.	The default locks are page-level locks, but you can apply locks at the row, table, tablespace, and LOB levels.
3	The lock upgrade concept does not exist.	If the number of locks increases, you can perform a lock upgrade. The upgrade results will prompt the row-level locks to be upgraded to table-level locks and page-level locks, and then to tablespace-level locks to reduce the number of locks.
4	Because the concept of uncommitted read does not exist, dirty read cannot be performed.	You can execute uncommitted reads, and dirty reads are a way to read rows without a shared level lock.

Table 1. Key lock differences between Oracle and DB2 on z/OS

To solve the locking problem, you must implement the migration policy at the database, application, and operation level.

Database-level policy

The following types of databases and design changes help ease locking:

A row-Level Lock. Overwrite the default DB2 page-Level Lock settings and reorganize them so that the table can use row-level locks to improve concurrency. Row-level locks should be used with caution, because an increase in the number of locks may lead to an increase in overhead. If they are not properly handled, the lock upgrade will inevitably increase.

Optimize the indexing and query of shards. Reading a query (which may require table scanning) will not cause Oracle problems, but will cause problems with DB2 on z/OS, Because reading a query will lock the entire table. To alleviate this problem, we need to ensure that the indexes and access paths of all queries have been optimized to avoid unnecessary table scans, especially the table scans accessed during online transactions.

Shard partition. After the Partition Table space is introduced in DB2 for z/OS, the concurrency will be greatly improved, especially in batch operation. By determining the partition key and placing data in different partitions based on the key value range, you can divide the data into different partitions. During batch processing, multiple threads can be started based on the partition key value, so that different threads can access different partitions and provide higher concurrency.

Application-level policies

Some key application design changes may help mitigate locking issues, including:

The slave node skips the locked data. You may have encountered a situation where the same table executes different transactions and you only need to access the rows that are not currently locked in the given table. In these cases, DB2 provides an option to query only unlocked rows by using the kip locked data option in the SELECT, UPDATE, and DELETE clauses. This option is applicable only when the cursor stability (CS) and read stability (RS) Isolation levels are set, and only for Row-level locks and page-level locks.

Shard has not been submitted for read. In some cases, it is acceptable to read the query response containing uncommitted data, so use the with ur option to read the query in DB2, so it does not have any share lock. This option is useful for application testers and business analysts in the User Acceptance Test or production area when running user queries. These queries may compete WITH application queries. Therefore, user queries using the with ur clause may need to avoid this situation.

Sequence Table access sequence. Due to the improper access sequence of parallel transaction tables, lock contention may also occur during migration from Oracle to DB2 for z/OS. Consistent access order helps avoid this problem. For example, if transaction 1 accesses table A first and then table B, the subsequent transaction accesses the same table in the same order.

Operation-level policy

Because the same table executes different types of workloads (for example, batch processing and online workloads simultaneously access tables, or different batches simultaneously access tables), contention may occur. In these cases, one way is to execute Operation-level changes, such as rescheduling conflicting transactions. You can also run batch processing workloads during off-peak hours (when online workloads are not running. If two batches run in parallel, try to run one before the other, or set the dependency so that one workload cannot run when another workload runs, and vice versa.

Conclusion

There are some major lock differences between Oracle and DB2 on z/OS. Therefore, when an application is migrated from Oracle to DB2, locking may occur. However, as described in this article, we can execute some solutions at the database, application, and operation level to greatly alleviate any problems that may occur.