Updating TurboImage Databases, KSAM files and MPE files.

How MiniSoft’s ODBC and JDBC drivers update TurboImage Databases, KSAM files and MPE files.

The data on a HP e3000 is much different than typical relational databases, so concepts that apply to updating data in a relational database do not apply when updating HP e3000 data.

There is no row versioning in TurboImage databases or in KSAM and MPE files. This means that when the database engine or file system makes a change to a database or file, the change is immediate and therefore visible to every process accessing the database or file.

All locks needed in a transaction need to be applied at once. There is a method to allow locks to be incrementally applied, but it is very prone to deadlocks, so it is not used by ODBC. The locking mechanism does not lock individual records, but rather it sets up criteria describing the requested lock. Once a process has a lock, processes that try to set up locks with conflicting criteria are blocked (or optionally the lock request is rejected).

There is a dynamic rollback mechanism that allows changes to be undone. The changes made during a transaction are recorded in a log, and if rollback is required, they are applied in reverse.

The method used for updating (update/delete/insert) data most HP e3000 programmers use is to apply a lock describing all the data involved in the transaction, signal the dynamic rollback mechanism that a transaction is starting, make the changes to the data, signal the dynamic rollback mechanism that the transaction is done, and release the lock. ODBC uses this method.

The following example illustrates the process.

Assume that there are 3 statements, an INSERT statement, an UPDATE statement and a DELETE statement being executed in one transaction. All client software that use ODBC have some way to specify the start and end of a transaction.

When the first statement that will cause data to be changed is executed, ODBC will internally start its mechanism for keeping track of the transaction. As each statement is executed key things are done. The first thing is to set up a lock descriptor that describes the data being changed. For, example, if the WHERE clause on an UPDATE statement used a TurboImage key for the dataset being updated, the lock descriptor would record the item number of the key and the value. More information on how lock descriptors are constructed is given below. Second, a log would be kept of all the changes that this statement will cause. Also kept in this log is a copy of the original data. No actual changes are being made to the database or file at this time.

When the transaction is committed, the databases and files are changed using the following process. A lock is requested that will cover all the data being changed. The dynamic rollback mechanism is notified that a transaction is starting. The log of changes is read and each change is applied to the data. If a change is an update or delete of a row, the data is re-read and compared against the original data to make sure it has not been changed. After all the changes are made successfully, the dynamic rollback mechanism is notified that the transaction is over, and the lock is released. If any errors are encountered, or the data in a row is different than its original data, the dynamic rollback mechanism is instructed to rollback any changes, and then the lock is released.

The method for constructing the lock descriptors that cover all the data that will change can be controlled in various ways. When changes are being made to a KSAM or MPE file the whole file will be locked. When changes are made to a dataset in a TurboImage database, there are two types of locks; set level and item level. The following conditions will always apply to set level locks:

• The dataset is a Master and the operation is insert or delete.
• “Enable item-level locking” is unchecked in the DSN or, if using a ConnectionString, the ItemLocking property is set to DISABLED.
• ODBC cannot determine a valid item to use in the lock descriptor by examining the WHERE clause of UPDATE and DELETE statement or the item list in an INSERT statement and no lock item has been identified in the schema file if one is being used.

Otherwise, the following method is used to select an item and value for a lock descriptor used for item level locking:

• The lock item specified in the schema file if one is being used.
• The first TurboImage key item found in the WHERE clause of an UPDATE or DELETE statement.
• The first TurboImage item found in the item list of an INSERT statement.

The updating method used by ODBC is both efficient and reliable, but there are some restrictions it imposes. Transactions that have statements that attempt to update or delete the same row more than once will never successfully complete because the second check against the original data will always fail. If data in a row is to be updated using other data in the same row, then the expression to compute the new value should be included in the update statement. For example:

Do this:

UPDATE table SET item1 = item2 * 1.1 WHERE key = value

Instead of:

SELECT item2 FROM table WHERE key = value

UPDATE table SET item1 = ? WHERE key = value

Where the parameter (?) has the value retrieved from the item2 of the SELECT statement multiplied by 1.1.

Since there is no way to hold a lock for both the SELECT and INSERT statements, the value of item2 may have changed between the execution of the SELECT statement and the execution of the UPDATE statement.

The last consideration is that many errors that you might expect to come from the execution of an INSERT, UPDATE, or DELETE statement will not be returned upon their execution, but rather upon execution of the commit. This is because no data is actually changed until the commit. Always be sure to check for errors when the commit is done.

There are some settings in the DSN that can be used to fine-tuned the locking performance. Also, if ODBC processes are to be used concurrently with other programs updating TurboImage database, it is important to make sure that a compatible locking strategy is used. If the other programs are using set level locking, modify the DSN to disable item level locking. If the other programs are using item-level locking, set a lock item for each table using the Schema Editor.