• Generates optimized DBO unload and load scripts
• Automatic recovery from most systems failures
• Automatic buffer optimization
• Recommends optimal SYSGEN and UAF values
• Supports Parallel Unload operations
• Tape drive support
• Maintains order of significant sets
• Generates optimized subschema for unload and load operations
• Use full screen editors for augmenting unique record keys

A problem has been discovered in the online backup feature of VAX DBMS v4.3. In some situations, the use of DBO/BACKUP/ONLINE (without the /MULTITHREADED qualifier) may create an invalid backup file. If this file is restored, it is possible that the resulting database will contain some corruption. Note: this alert does not apply if you are using the /MULTITHREADED backup feature.

Digital began shipping a mandatory update to resolve this problem on February 5, 1993. If you are using on ONLINE backup features of version 4.3, then you must install this update immediately.

If you are running v4.3, and you are not able to immediately apply this mandatory update, then you should change your dbo/backup routines to use the /MULTITHREADED feature or remove the /ONLINE qualifier. Failure to do so may result in an unusable database backup.

Q: We have an application that uses several streams (attaches to several databases simultaneously). We noticed that when some of these processes run, they use an excessive amount of system resources, and tend to page-fault heavily. We’ve tried increasing their working set sizes, and although this has helped the page faulting somewhat, we wonder why this application is such a resource hog.

A: To understand where your system resources are going you have to understand a little about how DBMS handles multiple streams. Each stream is a separate connect to the database – each has its own UWA (User Work Area), and each maintains its own set of buffers. Thus, if your application contains five (5) streams, each of which holds 20 buffers of 10 pages each, each process is effectively using 512k bytes (5*20*10*512) of virtual memory for buffering. In addition, you can figure an additional 15% to handle other data structures needed to manage these buffers.

First, evaluate the need for so many steams – if each stream is accessing a different database, then you have no choice, and must continue to use a separate stream for each database. If they are accessing the same database, then you may want to combine some of the streams.

A second alternative is to use different buffering strategies for each stream. There are two ways to accomplish this.

1. Modify the default buffer parameters on each database to match the exact requirements of that database. For example, those databases that tend to be accessed randomly should have many small buffers – databases with significant sequential access should use fewer large buffers. In choosing the default parameters, your primary concern should be with the buffer length since the buffer length cannot be changed dynamically at run time.

2. The second approach is to make use of the logical name, DBM$BIND_BUFFERS, to dynamically adjust the buffer count (number of buffers) for each stream. To use this approach, you will have to write a routine that will define this logical name to be the appropriate value before the first bind on each stream. Using this technique, it is possible for each stream in your application to use a different number of buffers. You may want to consider designing this routine to use a logical to determine the appropriate number of buffers to use for this stream. NESC has implemented this design at various client sites, where the appropriate number of buffers is determined at run time based on the current image and stream name.

If you are running DBMS v4.3 or later, you can take advantage of global buffering. This allows multiple processes (or streams) share physical memory for database buffering.

Q: During a recent system upgrade, the system manager purged a disk that contained our Run Unit Journal (RUJ) files. Unfortunately, this resulted in some corruption in our database that was not immediately discovered. Worse yet, all of our backup files now contain corrupted images of the database. Is there any way to recover from this situation?

A: The problems that you have experienced indicate that there are several weaknesses in your environment:

1. The frequency with which DBO/VERIFY was being executed.

2. The backup and retention cycle for your database files.

3. Lack of adequate controls over access to and retention of your RUJ files.

However, all of that is outside of the scope of your immediate problem, which is fixing your database.

The DBO/VERIFY utility indicates that your database contains none corruption. While we can theoretically patch the database using the DBO/ALTER (DBMS low-level patching utility), the extent of the corruption will make this a slow and error-prone task.

To solve this problem, we used a combination of tools that allowed us to automate much of our work.

1. We wrote a special purpose program to obtain the pointer clusters from the current records, and store them in a RMS file.

2. Using a prototype version of NESC’s DBInitialize program, we were able to remove the pointer clusters and index nodes of the affected set types.

3. We wrote a special purpose program that read the pointer information contained in step 1 to reconnect the pointer clusters of the appropriate sets.

Q: We are experiencing wide fluctuations in the time required to store certain record types in our database. The problem does not seem to be related to locking problems or lack of CPU resources (no other processes were attached to the database, and the system has significant idle CPU available.)

A: The first thing that we looked for was excessively full storage areas. In your environment, this was not the cause of your problems.

By analyzing the transactions that were experiencing this problem, and running some test cases on your database, we were able to isolate the source of your problem. The problem you are experiencing has to do with the way that DBMS inserts records into sorted sets that have many duplicate key values. The current algorithm works as follows:

When DBMS attempts to insert a record into an indexed set, it searches the b-tree index for the largest key value that is less than or equal to the key value of the record being inserted. The index points to the first record that meets these criteria. If you are inserting a key value that is larger than the key value found, or the set order specifies duplicates are last, DBMS performs a sequential search through a chain of pointers linking records with duplicate key values until it finds a record with a key value larger than the one being inserted. Thus, the time to insert records into indexed sets with duplicate key values can fluctuate significantly depending on the number of duplicate records that must be traversed during the insert operation.

To solve the problem, we added an additional field onto the end of the sort key to significantly reduce the number of duplicates within these sorted sets. We were able to accomplish this using the DRU facility. Thus, we avoided the need to restructure your database.

Digital's Database Engineering group is currently evaluating alternative algorithms for inserting records into sets with duplicate key values – their objective is to eliminate this bottleneck.

Q: We are running DBMS version 4.2a on a 43xx system and are experiencing periodic bugchecks dumps when attempting to bind to the database. This happens infrequently, but we haven't been able to identify a reason.

A: The problem you are experiencing has to do with an algorithm that is used to generate the name of some data control blocks that are mapped into memory during the bind process. The current algorithm uses the VMS system service $GETTIM to generate "unique" identifiers. However, on some of the faster processors, this routine can be executed twice within the same VMS time interval (100 nanoseconds). Thus, this algorithm can experience some failures on the faster processors. The algorithm will be fixed in a future release of DBMS.

In the meantime, a patch is available through the CSC (Customer Care Center) for v4.2 of DBMS. You can also work around this problem by using DBO/OPEN to open the database on those nodes that will be accessing the database.

DEC DBMS is Digital's highest performance database management system. Yet, achieving the highest level (or even a reasonable level) of performance is not automatic – and how to do so is not always intuitive. In fact, many of the default values used for database design and creation are indeed very poor choices.

Database performance may be acceptable immediately after installing the application, and then may begin to deteriorate over time. Performance may degrade as additional data is added or as new functionality is utilized.

Determining when it is appropriate to tune your database or review your application is often difficult to determine. This article examines some of the tangible and intangible costs associated with poor system response time.

Measuring the Cost of Reduced Performance:

The cost of and to people is frequently the most significant costs associated with diminished database performance.

Idle Time – The amount of excess time spent waiting for the database to complete a task. This cost can be directly measured using the equation:

COST = TIME * RATE * FREQUENCY

Increased Stress – Stress is a natural result of being blocked from performing a job efficiently. Poor system response time feels like sitting in stop-and-go traffic on the expressway to any employee determined to remain productive. Their job is to get product out the door – not wait for the system to respond!

Reduced Moral – Confronted with ongoing poor response time, it is easy to lose hope for improvement. If your operations staff is pressured to produce more – and their support systems impede on their ability to be productive – low moral is a natural result. Unwillingness of management to invest in improving system response may ultimately lead users of your system to feel that their efforts to remain productive are going unnoticed.

While difficult to measure, the impact of increased stress and reduced moral on the operations staff can be very real, including:

• Increased sick leave
• Higher turnover rates (increased training costs)
• Increased errors (reduced product quality)

Businesses invest enormous sums of money in the buildings, equipment and technology of their production facilities. To obtain a reasonable return-on-investment, these facilities must remain productive. Database applications are frequently a critical component in the scheduling and operation of these production facilities.

Poor application performance can cause under-utilization of these resources. Job's that do not complete on time can result in:

• Overtime
• Idle Equipment
• Missed Deadlines

Buying new hardware may or may not be an appropriate solution. A Ferrari is no faster in stop-and-go traffic than a Chevy (or an Alpha AXP is no faster than a VAX 11/730 when waiting on a record-lock). Yet, investing in additional or faster hardware is a common knee-jerk reaction to poor system response time...and like the Ferrari in a traffic jam, it probably will not help you finish any sooner! Determining where resource bottlenecks are, and identifying their causes is often a more cost-effective solution than just "throwing more hardware" at the problem.

The following outlines some common database problems for which additional hardware may not be the appropriate solution:

Excessive I/Os – A transaction that currently takes 3000 I/Os to complete, but should complete in 30 I/Os may slightly improve with faster disks – fixing the real problem would result in substantial improvements.

Locking Problems – If a process is blocked from accessing a record because another run-unit has the record locked, a faster computer will not improve response time! Locking is a frequent bottleneck in concurrent database applications.

Management prefers to buy hardware (rather than solve the real problem) because:

• Money spent on hardware can usually be depreciated over time (thus deferring its real costs). Tuning an existing database/application generally must be expensed.
• Management believes it will eventually be able to use this ‘excess capacity’ anyway.
• Hardware is tangible (you can touch and feel it – you know just what you got), software design, analysis and tuning are intangible (it makes you feel better – but you are not sure why.)

The appropriate solution for a performance problem depends on the cause of the bottleneck. New England Software Concepts has successfully identified and implemented effective performance solutions at many DBMS sites.

Common Performance Problems

While performance problems have many different causes, New England Software Concepts has observed the following to be common bottlenecks at client sites:

• Excessive lock conflicts
• Excessive storage area utilization
• Poor database or application design
• Ineffective use of available hardware
• Inadequate hardware
• Unrealistic expectations

Acceptable or optimal database performance does not happen by accident (or default.) Knowledge of the application, sound principals of database design and an understanding of the tools available for monitoring and using database resources are essential for achieving and maintaining optimal database performance.

DBMS is often criticized for excessive locking – resulting in poor database performance when sharing data among multiple concurrent processes. Is this criticism justified, or is DBMS being unfairly blamed for application design and implementation shortfalls? To evaluate this question, we need to understand more about DBMS locking protocols. In this article, we examine how, why, what and when DBMS locks and unlocks database resources. Future articles will address how to minimize the impact of database locking.

In an ideal concurrent environment, many processes can simultaneously access data in a DBMS database, each having the appearance that they have exclusive access to the database. In practice, this environment is closely approximated by careful use of locking protocols.

Locking is necessary in a concurrent environment to assure that one process does not retrieve or update a record that is being updated by another process. Failure to use some controls (locking), would result in inconsistent and corrupt data.

In addition to record locking, DBMS implements several other locking mechanisms to ensure the integrity of other data structures that provide shared I/O, communication among different processes in a cluster and automatic recovery in the event of a process or cluster failure. While these other lock structures use additional VMS lock resources, they rarely hinder database concurrency, but can actually improve database performance.

DBMS makes extensive use of the VMS Distributed Lock Manager for controlling virtually every aspect of database access. Use of the Distributed Lock Manager ensures

cluster-wide control of database resources, thus allowing DBMS to take advantage of Digital's clustering technology.

VMS locks consume system resources. A typical process, running a DBMS application may lock hundreds or thousands of records and database pages at a time. Using a VMS lock for each of these resources in a busy database could easily exhaust these resources. The system parameters: LOCKIDTBL, LOCKIDTBL_MAX, and REHASHTBL parameters determine the number of locks that exist on the system at any one time.

To minimize the number of VMS locks required to maintain record and page integrity, DBMS implements a technique called adjustable locking granularity. This allows DBMS to manage a group of resources (pages or records) using a single VMS lock. When a conflicting request is made for the same resource group, the process that is holding the lock is notified that it is blocking another process and automatically reduces the locking-level of the larger group.

Adjustable page locking is mandatory and hidden from the database administrator, while adjustable record locking can be enabled and tuned or disabled for each database. When adjustable record locking is enabled, DBMS attempts to minimize the number of VMS locks required to maintain database integrity without impacting database concurrency.

DBMS employs many types of locks to ensure database integrity in a concurrent environment. By using various lock types for different functions DBMS can provide optimal performance in many different environments.

DBMS uses area locks to implement the DML (Data Manipulation Language) READY statement. If a realm is readied by another run unit, later READY usage modes by other run-units must be compatible with all existing READY usage modes.

Area locks can significantly affect database concurrency – however, their impact is only felt during a DML READY statement. Lock conflicts for area locks occur only when you attempt to READY a realm. Once you successfully READY a realm, concurrent locking protocols (if required) are handled at the page and record level. Table I displays compatible area ready modes.

 

	First Run Unit	Second Run Unit
		Concurrent Retrieval	Protected Retrieval	Concurrent Update	Protected Update	Exclusive
	Concurrent Retrieval	GO	GO	GO	GO	WAIT
	Protected Retrieval	GO	GO	WAIT	WAIT	WAIT
	Concurrent Update	GO	WAIT	GO	WAIT	WAIT
	Protected Update	GO	WAIT	WAIT	WAIT	WAIT
	Exclusive	WAIT	WAIT	WAIT	WAIT	WAIT

Page locks are used to manage the integrity of the page buffer pool. DBMS automatically resolves page lock conflicts by using the blocking AST features of the VMS lock manager. Thus, page locks are not typically a major impediment to database concurrency unless long-DML verbs are frequently executed in your environment. DBMS utilizes adjustable locking to minimize the number of VMS locks required to maintain consistency of the buffer pool. A high level of blocking ASTs is an indication that there is a lot of contention for database pages in the buffer pool. Reducing the buffer length may help to reduce the overhead of page level blocking ASTs.

Record locks are typically the largest source of lock conflicts in a DEC DBMS environment. Record locks are used to manage the integrity of your data, and to implement the "adjustable record locking granularity" feature of DEC DBMS. Adjustable locking is the default for record locks, but can be tuned or disabled by the DBA.

Quiet point locks are used to control online database and afterimage journal backup operations. Large quiet point lock stall times indicate that processes are waiting for online backups to begin, or for the primary after-image journal file to be written to secondary storage. To minimize the effects (duration) of quiet point locks, it is important that all concurrent database processes (except for batch retrieval transactions) periodically execute commits (or commit retaining). Even "concurrent retrieval" transactions should periodically "commit [retaining]" their transactions. This ensures that the online backups will achieve a "quiet point" quickly and allow new transactions to proceed.

Freeze locks are used to stop (freeze) database activity during database process recovery. When a process terminates abnormally (as a result of a process or node failure, STOP/ID, or a CTRL-Y/STOP), all locks held by that process are automatically released. If transactions were allowed to continue, database corruption would result. Thus, when a process terminates abnormally, DBMS uses the freeze lock to stop database activity until the failed process(es) can be recovered. Freeze locks typically are not a major source of contention in most environments. However, if you are subject to frequent system or process failures, or users are using CTRL-Y/STOP to exit from programs, freeze locks could hinder database concurrency.

Several of the DBMS creation and modification qualifiers have a direct impact on database locking characteristics. Establishing the appropriate mix of qualifiers in your environment can help minimize the impact of database locking.

The [no]hold_retrieval_locks qualifier, determines whether DBMS holds read-only record locks on all records read for the duration of the transaction (until the next COMMIT [without the RETAINING option] or ROLLBACK). Holding retrieval locks guarantees that any records previously read during a transaction will not have been changed by another run-unit during the same transaction. While this increases the consistency of your transaction, it can significantly degrade concurrency.

This option should only be used if your transactions read very few records and consistency of all records read must be guaranteed throughout the transaction. By default, DBMS uses /NOHOLD_RETRIEVAL_LOCKS.

The logical name, DBM$BIND_HOLD_RETRIEVAL_LOCKS may be used to override the default established in the root file. If DBM$BIND_HOLD_RETRIEVAL_LOCKS translates to "1" then all records read by the transaction are locked until the end of the transaction.

New England Software Concepts recommends against using hold retrieval locks in most environments.

The [no]wait_record_locks qualifier determines whether a run-unit waits when requesting a record that is locked in a conflicting mode by another run-unit or if it receives a "lock conflict" exception. This qualifier only determines if the requesting run-unit will receive a "lock conflict" exception – not a "deadlock" exception (deadlock exceptions are always returned when they occur).

When the default (WAIT_RECORD_LOCKS) is used, DBMS will not generate a "lock conflict" exception, and the blocked process will continue to wait until the record is unlocked. Thus, the process can continue to wait indefinitely until the record is unlocked by the other run-unit. The logical name, DBM$BIND_WAIT_RECORD_LOCKS may be used to override the default established in the root file. Again, a value of "1" enables wait on record lock conflicts, and a value of "0" causes the process to receive the "lock conflict" exception.

New England Software Concepts recommends clients to WAIT on record conflicts. This allows the application to trap for "deadlocks", and avoids "live-lock" situations that cannot be detected. In addition, the wait on record conflicts can be used with the /TIMEOUT to give the application control over records locked for an excessive duration.

/TIMEOUT=LOCK=seconds

The timeout qualifier allows you to specify the amount of time that a run-unit waits for a locked record before returning a "lock timeout" exception. This qualifier must be used with the "wait" on record locks (above).

The logical name, DBM$BIND_LOGK_TIMEOUT_INTERVAL may be used to override the default established in the root file. The value of the translation determines the number of seconds to wait for a locked record.

If your applications trap the ‘DBM$TIMEOUT’ exceptions, then New England Software Concepts recommends using lock timeouts with a time of at least 60 seconds. Using the /TIMEOUT qualifier only if your application is designed to handle "lock timeout" exceptions. COBOL shops that use declaratives, may want to handle "DBM$_DEADLOCK", "DBM$LCKCNFLCT", and "DBM$TIMEOUT" exceptions in the same "USE" section.

Enabling, disabling, or modifying the values of the adjustable locking features of DBMS will not significantly reduce record lock conflicts. However, adjustable locking can significantly affect the amount of lock resources your application uses, as well as the overall overhead associated with record locking.

The DBO/SHOW STATISTICS (record locking) screen provides useful insights into the potential benefits and costs of adjustable locking. If you observe a blocking AST rate that is more than 20-25% of the number of locks requested plus locks promoted, then this may indicate significant adjustable locking overhead. In this case, try disabling adjustable locking, or reducing the number of levels in its tree.

Lock optimization sounds so obvious. Who wouldn't want "lock optimization?" Lock optimization (the default), only controls whether area locks are held from one transaction to another. This avoids the overhead of acquiring and releasing locks for each transaction.

In environments where long DML verbs are frequently executed, lock optimization may actually degrade performance. This is because the process holding the lock does not release the NOWAIT lock until the end of its current DML verb. Thus, if the current DML verb takes a long time to complete, the process trying to ready the realm may experience a long delay.

Snapshots are included in this discussion of locking, because the use of snapshots (batch retrieval transactions) can significantly reduce the level of lock contention in your database. Although snapshot transactions are subject to page and other resource lock conflicts, they are never involved in record lock conflicts – thus providing significantly increased concurrency between read-only and update transactions.

Enabling snapshots are not however a panacea – All update processes (except EXCLUSIVE or BATCH) must write before-images of their updates to the snapshot files. The use of /DEFERRED qualifier minimizes this affect by allowing update processes to write to the snapshot file only when snapshot transactions are active.

Additional or excessive buffers require additional page level locking to manage the buffer pool. If you are using large buffer counts, you may need to increase the enque limits on your processes, as well as the SYSGEN parameters, LOCKIDTBL, LOCKIDTBL_MAX, and REHASHTBL.

DBMS signals one three types of exceptions when a process encounters a locked record – a deadlock, a lock conflict, or a lock timeout.

A deadlock exception, DBM$_DEADLOCK, is returned when two run-units attempt to access a resource in mutually exclusive modes, and each run-unit is waiting for a resource that the other run-unit holds. This indicates that neither run-unit can continue unless one of the run-units releases its locks. When a deadlock occurs, DBMS will choose a "victim," and signal that run-unit of the deadlock condition. This does not cause the "victim" to automatically release its locks. The victim process should immediately execute a 'rollback' to release its locks.

DBMS will only return the lock conflict exception, DBM$_LCKCNFLCT, when the run-unit is bound to a database with "/NOWAIT_RECORD_LOCKS" enabled and it attempts to access a record that is locked in a mutually exclusive mode by another run-unit. Note, that only the "blocked" run-unit receives the exception.

The third type of exception is the lock timeout exception, DBM$TIMEOUT. A lock timeout only occurs when the "/TIMEOUT=LOCK=nnn" and "/NOWAIT_RECORD_LOCKS" are enabled and a run-unit attempts to access a record that is locked in a mutually exclusive mode by another run-unit.

In the next issue of DBAdvisor, we will discuss options for handling and minimizing DBMS lock conflicts.

When Touch Technologies, Inc. of San Diego, CA needed DEC DBMS expertise to implement their INTOUCH 4GL programming interface to DEC DBMS, they called New England Software Concepts.

Dan Esbensen, president of Touch Technologies asked, "What is it that most DBMS sites do wrong – We want to make certain that we do it right." Currency indicators are generally no well understood, and are a major cause of locking and performance problems at many DEC DBMS sites.

Currency indicators are place holders that indicate the current position in each logical structure in a subschema. They provide a mechanism for very efficient database navigation. However, there is a downside to currency indicators – each active currency indicator maintains a read-only lock on the record to which it points. The read-only lock is maintained until all currency indicators have been removed from the record and it is not pointed to by an entry in a keeplist.

The logical structures pointed to by currency indicators are:

1. The most recently retrieved or updated record (run-unit).

2. Each realm in the subschema.

3. Each set type in the subschema.

4. Each record type in the subschema.

By default, DBMS automatically updates currency indicators after executing each DML (Data Manipulation Language) statement. Figure 1 show how currency indicators are automatically updated by DBMS:

Figure 2 shows how records remain locked by currency indicators even after a new record is FETCHed!

Accepting the default – automatic currency indicator updating – simplifies 3GL programming. 3GL programmers can navigate a database using any of the active currency indicators, usually without having to first specify which ones you intend to use. Allowing currency indicators to be updated automatically also means increased record locking and reduced database concurrency!

Figure 3 shows how automatic currency indicator updating can be suppressed by using the RETAINING clause on DML statements – significantly reducing database locking! By properly managing currency indicators, database navigation can be very efficient without excessive locking. Yes, you can have your cake and eat it too!

This is precisely the effect of INTOUCH’s automatic currency indicator management. INTOUCH uses currency indicators to efficiently navigate DBMS databases, while updating only those currency indicators needed for efficient navigation. INTOUCH automatically unlocks records by releasing (freeing) currency indicators when they are no longer needed.

Programming in INTOUCH allows programmers to concentrate on the job of building applications. The complex task of managing currency indicators and locking is handled automatically by the INTOUCH language.