SCI - Technical Article - What's New With Oracle CODASYL DBMS 7.0 (DBMS only)

VERSION 7.0.4

RECOVERY BUFFERS

A new qualifier, /DBR_BUFFERS, has been added on the DBO/CREATE and /MODIFY. It can significantly improve recovery times particularly if the row caching feature is enabled. The larger the number of buffers; generally, the better the recovery time as long as the number is not so large that the DBR process has to page fault.

DEFERRED SNAPSHOT ENHANCEMENT
The deferred snapshot algorithm has been relaxed so that concurrent retrieval transactions will not block a batch retrieval transaction from starting. When deferred snapshots are enabled, update transactions to not have to write before-image records to the snapshot file unless there is an active batch retrieval transaction. This enhancement makes deferred snapshots a possibility in a large number of sites – resulting in improved performance.

VERSION 7.0.3

This release now supports the Alpha EV67 processor

DBO/SHOW STATISTICS

A new qualifier, /OPCOM_LOG, has been added for tracking database related opcom messages.

MEMORY MAPPING
As of this release (7.0.3), Oracle CODASYL DBMS can specify whether database global sections (including global buffer memory) are mapped in system space or in process space. This new feature is similar to a capability added in Oracle CODASYL DBMS release 7.0.2 with regard to row caching.

The syntax for this new feature is:

DBO/CREATE/MEMORY_MAPPING=({SYSTEM|PROCESS})
DBO/MODIFY/MEMORY_MAPPING=({SYSTEM|PROCESS})

The command qualifier is /MEMORY_MAPPING=SYSTEM, and the default qualifier is /MEMORY_MAPPING=PROCESS.

The parameter SYSTEM indicates that memory mapping should use the system space buffers. The system space global section is located in the OpenVMS Alpha system space, which means that a system space global section is fully resident, or pinned, in memory and does not affect the quotas of the working set of the process. As a result, a process referencing a system space global section can have up to 256MB of resident, working set space. The parameter PROCESS indicates that memory mapping should use process space. The default is /MEMORY_MAPPING=PROCESS. When you use global sections created in the process space, you and other users share physical memory, and the OpenVMS Alpha operating system maps a row cache to a private address space for each user.

VERSION 7.0.2

PAGE TRANSFER

In version 7.0.2, the PAGE_TRANSFER option has been added to the GLOBAL_BUFFERS qualifier on the DBO/CREATE and /MODIFY commands. When the PAGE_TRANSFER=MEMORY option is specified, it means that a process does not need to write a modified page to disk. An update intensive database with the necessary characteristics can have a significant performance gain with this feature. Sharing the pages among processes can save a number of I/O operations. To take advantage of this feature, the database must have global buffers, after image journaling and fast-commit enabled. It must also be accessed from a single node. (IE the NUMBER OF CLUSTER NODES value must be 1) The default is PAGE_TRANSFER=DISK .

DBO/DUMP/AFTER_JOURNAL

Several new qualifiers have been added so that subsets of the journal file can be dumped such as /ONLY. This can help when searching for particular transactions when used in conjunction with TSN or TID also /START/END/FIRST/LAST to limit the output that can be considerable when dumping the journal file.

VERSION 7.0

Version 7.0 is the largest single update to DBMS ever! Perhaps the most significant enhancement is the ability to restructure (unload/load) areas online with minimal downtime. There are also major enhancements to database availability, recoverability and performance.

Online Area Reload

This most awaited feature allows database restructuring to occur as a background task concurrently with other database activity. Yes, you can take orders or move inventory while the database is being restructured! Online area reload works by creating a new storage area into which new data will be stored and existing data will be moved. After all data has been moved, the new area becomes the permanent area and the previous area file is deleted.

While the actual unload/load (by far, the most time-consuming task) occurs concurrently, there are two brief periods of exclusive access required to initiate and to terminate the online restructuring process. Here are the 6 steps to restructure a database online.

Six steps to an online area reload:

VERIFY the internal consistency of your database (may be done online). Okay, this is not technically required, but is highly recommended... Besides, you’ve been doing this weekly, anyways.

RESERVE a temporary reload storage area (requires exclusive access to the database, but is done only once in the life of a database).

DEFINE the unload/load script

PREPARE the online reload. This exclusive process updates the metadata (schema and subschemas) definitions to describe the new temporary storage area to DBMS. During this step, DBMS:

Defines a target area using a reserved reload area.

Adds the target area to the subschema realm area list and to the records' WITHIN lists.

Creates a target area file using the area qualifiers specified in the DBO /MODIFY/RESTRUCTURE/ONLINE command (this is where the new area size is defined).

Moves system-owned chain and index set owner records to the target area.

Builds the system-owned index set B-tree in the target area (this may take some time if you have system-owned indexed sets with lots of members).

All changes made during the PREPARE phase are written to the AIJ if after-image journaling is enabled.

EXECUTE the online reload. This is when the actual data gets moved to the new area (sit back, this may take a while).

CLEANUP the database after all records have been reloaded. The CLEANUP step requires exclusive access to the database, but you can wait until a convenient time to close the database and run the CLEANUP command. This command performs the following tasks:

Checks that the RELOAD has completed.

Removes the old area name from the subschema realm and the schema WITHIN clause.

Changes the original area to a reserved reload area (ready for your next on-line reload).

Deletes the original area file.

All changes made during the CLEANUP phase are journaled to the .AIJ file if after-image journaling is enabled.

Too good to be true?

No, it is not “magic.” Although DRU can perform certain “tricks” that we can’t do with standard DML (through our own C or COBOL programs), the restructure job is really a massive concurrent update job, enjoying both the benefits and pitfalls of any concurrent update process. It can run concurrently with other users – but it also locks records and can be blocked by other processes. Fortunately, DBMS does a good job of handling deadlock conditions – automatically rolling back and starting from the last committed record. However, it may be unrealistic to expect that the online restructuring will go completely unnoticed.

Hot-Standby

With this release, Oracle announced an add-on product called “hot-standby.” Hot standby provides the ability to maintain a physical copy of the database on either a local or a geographically remote VMS node. Updates to the production (master) database are applied to the hot-standby database via special AIJ server processes. Note that the two databases (the master and the standby) are physically identical. Thus it is not possible to restructure or modify the standby database in any way.

Total Protection?

Hot-standby does NOT provide protection against all types of failures. For example, if an updated database page is not written back to disk (perhaps because of a hardware or software failure), then the page on disk will appear to be a valid page, but is inconsistent (missing updates). If a record on that page is then modified, that modification will be written to the AIJ. The modified (but inconsistent record) will then be written to the AIJ and propagated into the hot-standby database. Hot-standby is intended to provide for protection against catastrophic disk failures – perhaps as the result of a site disaster.

v7.0 Performance Enhancements

Locked Free Space released at COMMIT

In previous versions, DBMS did not release the space used by deleted records until the process that deleted these records unbound from the database. If this process stayed bound to the database for long periods (such as a database server process), the amount of “locked free space” reserved by this process became excessive. This would lead to erratic performance behavior that is difficult to diagnose – DBMS may perform excessive I/Os when storing new records even when there appeared to be sufficient free space. Starting with DBMS v7.0, lock free space is released when the process that deleted the record performs a COMMIT.

Record Cache

	A shared (global) cache of records (not pages).
	Secondary to page (buffer) cache (the default).
	Records remain cached even when page has been flushed.
	Defined by storage area.
	You, as the DBA, control what gets cached!

Benefits

	Reduced I/O
	Improved response time
	Lower overhead
	Efficient use of memory

Restrictions (major)

	Oracle recommends that record caches are to be used for read-only records – In SCI’s opinion, this significantly reduces the potential benefit of this feature.
	Single-node access.
	AIJs must be enabled (a really good idea anyway).
	Fast commit feature must be enabled.

Very Large Memory

It is now possible to use as much physical memory as is available on your system for DBMS data caches.

System Space Global Sections

System Space Global Sections (SSGC) are a special type of global sections that are available only on AXP systems (not on VAX). The SSGC resolve some performance issues related to standard global sections. Specifically, standard global sections:

	Consumed system overhead to map each section into referencing processes’ address space
	GS required matching page file quotas.
	GS address space is page-faulted through process’ working sets.
	Required matching virtual memory, artificially increasing GBLPAGFIL values.
	Allocating VM was very slow – resulting in poor BIND performance.

SSGS are located in system space. As such, SSGS:

	Fully resident in memory.
	Does not affect process quotas or working set.
	Does not require page file space.
	VM is not allocated – much faster binds.

Other Enhancements

DML DEBUG Flags

Have you ever wondered what your programs are really doing to the database? Finally, there is an easy way to find out how your program is really accessing the database. The new debug flags allow you to capture the DML statements of any program that runs against a DBMS database. These flags are set by defining the logical name, DBM$BIND DEBUG FLAGS. Currently, there are 3 possible values; V, D and Z. Value “V” displays each DML verb executed by the program, value “D” displays the run-unit currency dbkey after each successful DML verb and value “Z” displays a data and time stamp, the process id and stream id. You may define multiple flags in the logical. For example, to display the DML statement and the dbkey of the records accessed, define the following:

$ define DBM$BIND_DEBUG_FLAGS DV

By default, the output goes to the process’ sys$output. However, the DEBUG output can be redirected to a file by defining the logical name DBM$BIND_DEBUG_FLAGS_OUTPUT. For example, to capture the output to a file called DML_STATEMENTS.TXT, define the following:

$ define DBM$BIND_DEBUG_FLAGS_OUTPUT DML_STATEMENTS.TXT

New DBCS keeplist functions

Developers who make extensive user of DBMS keeplists (an internal “bookmark” of previously accessed records) will appreciate the 5 new keeplist functions:

	DBM$KID_COUNT – returns the number of streams active in the application.
	DBM$KID_GET_ENTRY – returns the keeplist id (an internal identifier that DBMS uses to locate a specific keeplist) based on the ordinal entry value.
	DBM$KID_GET_ALL – returns all keeplist id’s in a single call.
	DBM$KPL_GET_ENTRY – returns a specific keeplist entry (a dbkey) from the specified keeplist based on it’s ordinal position.
	DBM$KPL_GET_ALL – returns in a single call, all keeplist entries (dbkeys) contained with the specified keeplist.

Each of these functions has a corresponding function used if your application uses streams.

DBO/ANALYZE binary output file

DBAs will appreciate the new /BINARY_FILE qualifier on DBO/ANALYZE which generates a file containing the ANALYZE data in standard binary format. No longer is it necessary to parse the DBO/ANALYZE output to capture the data values in the analyze reports.

Show Statistics Enhancements

DBMS (and Rdb) have always led the pack in reporting run-time database environment statistics. The amount and quality of data available has increased significantly in recent releases (v6.0, v6.1)…but especially so in v7.0! A plethora of new statistics is available, and you can even collect database statistics cluster-wide. The v7.0 statistics utility also sports an “online analysis” feature that provides “expert” advice on changes that may improve database performance. A new “dashboard” facility allows dynamic, non-persistent changes to be made to various database bind parameters. In essence, the “Dashboard” allows you to “drive” the database performance by making dynamic adjustments to various database parameters. These adjustments affect the resources available to the process and the behavior of processes that access the database. By dynamically adjusting these values at run-time, you can better measure the effect that new values will have on the database environment.

Bug Fixes

While many bug fixes were minor, the v7.0 release notes referenced 98 such “enhancements.” Some of the more critical issues addressed are:

	The fast incremental backup, introduced in v6.0, might not check certain candidate data pages, thus causing the pages not to get backed up.
	Database backups could not be applied if a storage area extended during the backup.
	Various problems associated with the use of the FAST COMMIT feature.
	The DBMS Monitor process would bugcheck. This problem was worsened by the fact that the process would loop until the disk containing the monitor log file filled (typically the system disk).
	DBO/BACKUP/AFTER_JOURNAL/RENAME may not produce a usable AIJ backup file.
	DBO/OPTIMIZE of an AIJ backup file that contained multiple sequence numbers would not properly initialize the next sequence number. This prevented additional AIJs from being applied.
	DBO/RESTORE would sometimes bugcheck at FILE_CLEANUP + 72.
	Processes would bugcheck at PIOAPF$SYNC and PIOAPF$BUF_COMPLETE. These bugchecks were related to the asynchronous prefetch feature implemented in DBMS v6.0.
	It was possible for the AREA RELOAD to fail to move all records.
	Application processes may hang waiting for the DBK Scope lock.
	Applications no longer hang when EXEC mode ASTs are disabled. When a process was hung in this state, it was not possible to kill this process using the STOP DCL command. This condition would result in a hung database requiring a system reboot to clear.
	Failed AIJ backups may cause recovery problems.
	DBMS Monitor process would sometimes hang, requiring the process to be killed and restarted to make the database operational.

Summary
DBMS v7.0 is a major release with many new features and benefits. Should you upgrade to DBMS v7.0? In making this decision, note that Rdb v7.0, sharing much of its core code with DBMS, was released about a year earlier than DBMS v7.0. In this respect, DBMS has been available much longer and has had much more activity than its “release date” would indicate. If major updates or “.0” releases scare you off, consider upgrading to DBMS v6.1-B (based on v6.1 which has a long and stable history – with a few key update patches).