Large Transactions for IMAGE Users
B T Vikram Kumar, Shobha Pradeep
Hewlett-Packard India Software Operations Ltd.
# 29, Cunningham Road,
Bangalore, INDIA
Phone: 91-80-2251554 extn 1197
FAX: 91-80-2264107,
emails: vikram@india.hp.com, shobhap@india.hp.com
Background
Of late many of the users of TurboIMAGE and IMAGE/SQL have the requirement of transaction sizes larger than which is currently possible today, i.e. > 4 MB. This is true, especially for users who run applications involving bulk puts/ deletes /updates. The situation becomes more difficult with the limitation of the size of the Transaction Manager (XM) userlog ,if the transactions are on the same volume set. The Application Support Solution team of CSY has therefore identified a solution to address these problems. This paper presents the details of the solution.
Introduction
Version C.03.00 of TurboImage introduced the concept of dynamic transaction, meaning the application can rollback a transaction at runtime. Later when the Transaction Manager started handling the dynamic transactions, some customers used to see the stalled transaction message due to the limit on the size of the transaction.
Dynamic transactions are the ones which are bracketed by DBXBEGIN and DBXEND or DBXUNDO, which may span a single database or multiple databases. In the case of multiple database transactions the total number of databases which can take part in a transaction is restricted to 15. A dynamic transaction can be rolled back using DBXUNDO or automatically when the application aborts within a transaction. When an application does a bulk insert, delete or update, there is a chance that it hits the current limit of 4 MB on transaction size. Till recently, the process used to abort under such situation, after displaying an error message that the transaction size has exceeded. An intermediate solution has been provided to rollback the transaction before process abort, so as to leave the database in a consistent state, and user could re-run the application after manually prune the application. However, this was not a comfortable alternative, and users have been requesting that there be an increase in the transaction size.
The user log related limit arises due to the fact that XM in MPE is log-based ie. all the transactions mainly the ones which involve manipulation to the database are logged on to a circular log file. When there are many applications writing into the XM log file, it could also get filled up easily, again resulting in process abort.
Objective
The main objective of this solution therefore has been to increase the transaction size from the existing 4MB.
The second objective is to increase the size of the XM userlog (all references to userlog in this paper refers to XM userlog) from the current value of 64MB to support large transactions. The userlog comes into picture here because all the changes to a specific volume set is written to the same userlog. So if only the transaction size limit is increased, because of the increased number of open transactions, the 64MB limit is also very easily hit.
The third objective is to ensure compatibility with existing applications, so that they can run without any problems even with these enhancements.
Overview of the solution
In order to achieve the above mentioned objectives, the capabilities of the following products /subsystems of MPE/iX have been enhanced:
Transaction Manager
TurboImage/iX
Image/SQL
Allbase/SQL
Transaction Manager
A patch MPEKXG8 was earlier released this year (on MPE/iX 5.5) to cater to the increase in number of transactions by reducing the data stored in a transaction. This was made possible, by keeping only the essential information required for possible rollback in future. A large amount of internal data becomes irrelevant after completion of an extended transaction (we will refer any dbput/ dbdelete/ dbupdate as an extended transaction throughout this paper). This allows for more transactions even within the current 4 MB limit.
However, a long term solution calls for increasing the transaction limit to larger values. Based on current OS limits, the maximum transaction size has been increased to 32 MB. In addition to this, XM has been changed to send out an early warning message (soft limit warning), before the transaction size limit of 32 MB is reached. When, ultimately the transaction size reaches 32 MB, it aborts the process and rollback the transaction.
Another enhancement in XM is to increase the userlog. The current userlog size is 64MB per volume set. Currently, for each transaction, the information that can be contained in the userlog is limited to 4MB. Due to the increase in the transaction size for each process, the total userlog file should also be increased. The userlog normally resides on the master volume of a volume set. User can use VOLUTIL to increase the size of the userlog. The default value will be 64 MB. However, it has to be ensured that memory beyond 256 MB is available on the system, before increasing the userlog through volutil. It has to be noted that, large transactions may result in all active processes getting aborted, if appropriate userlog size is not available.
TurboImage
Image has been enhanced to support the new 32 MB transaction size. This means, one can have approximately more than 8 times the number of extended transactions in a dynamic transaction than possible today (including the XM optimization available with XM patch MPEKXG8). IMAGE enhancement is not limited to just the increased transaction size support. It really provides a user control to manage large transactions. If the user want to judiciously control his large transaction, IMAGE provides an answer to it. A new mode (mode-18) has been added to DBCONTROL intrinsic. If the application makes a call to DBCONTROL mode-18 before the start of a transaction, it sets a flag in IMAGE, to trap the softlimit warning message from XM, and inhibits all new transactions. Now the user application have two options a) to commit the transaction (through a call to DBXEND) or rollback the transaction (through a call to DBXUNDO). IMAGE will do the rest, and one can start a new dynamic transaction to continue his job. He can continue the new transaction till the next softlimit warning has reached, and respond as above. The softlimit has been chosen to be at 28 MB. At any stage, if it is desired not to use this feature, the new DBCONTROL mode-19 can be used to reset the flag in IMAGE, so that no early warning, or forced commit/rollback occurs. To have compatibility with existing applications, the flag is not set by default. i.e., by default there won’t be any user control on the transaction size, which can grow till 32 MB. Once 32 MB has been reached, XM will abort the process with ‘Stalled Transaction’ message, and IMAGE will automatically rollback the transaction and leaves the database in a consistent state.
Image/SQL & Allbase/SQL
For the benefit of users doing bulk deletes, Allbase has a ‘DELETE WITH AUTOCOMMIT’ feature for Allbase tables. Now this facility has been extended to IMAGE tables as well. IMAGE/SQL and Allbase/SQL have been enhanced to support this. However, this option has to be exercised with great care, as IMAGE/SQL will do periodic commits at internally maintained intervals, and there will not be any way to rollback the deletes. For bulk deletes, the autocommit feature is very useful in that, chances of hitting the 32MB limit is remote.
Pointers to more info
Additional information, and details of usage will be provided through a communicator article.
Conclusion
In summary, this solution
- Supports transaction sizes upto 32 MB
- Accommodates more number of extended transactions
- Provides user control, if desired
- Extends ‘DELETE WITH AUTCOMMIT’ feature to IMAGE tables
- Ensures compatibility with existing applications
- Options to expand userlog
Acknowledgements
Information and help provided by Tien-You-Chen, B S Jyoti and K Nagarajan of CSY lab has been gratefully acknowledged. Thanks are also due to our colleagues in database team, who helped in various stages of the solution, and to CSY management for encouragement and support. Comments/ suggestions from SIGIMAGE and HP3000-L community are also gratefully acknowledged.