SCI - DBAdvisor Issue #2 - Technical Newsletter for Oracle Rdb and Oracle CODASYL DBMS

<body marginheight="0" marginwidth="0"> <p align="center"> <img src="../../../images/advisortit.gif" align="bottom" width="532" height="114"></p> <h1 align="center">Summer 1992Volume 1, Issue 2</h1> <ul> </ul> <p><a href="index.asp" target="_self"><font size="3">Go Back to DBAdvisor Main Page</font></a></p> <p><a name="Contents"><font color="#000000"><font size="4">In This Issue:</font></font> </a></p> <ul> <li><a href="#VAX DBMS Ported to Alpha Architecture"><font size="3">VAX DBMS Ported to Alpha Architecture</font></a><font size="3"> </font></li> <li><a href="#VAX DBMS Hints, Kinks"><font size="3">VAX DBMS Hints, Kinks and Work-Arounds</font></a><font size="3"> </font></li> <li><a href="#Quick"><font size="3">Product Development:<strong>QuickDisconnect </strong></font></a></li> <li><a href="#SPAMS"><font size="3">Implementing SPAMS for Performance </font></a></li> <li><a href="#Is Your Database Backup"><font size="3">Is Your Database Backup Strategy Really Effective</font></a><font size="3"> </font></li> <li><a href="#ReloadExpert"><font size="3">Product Announcement: <strong>ReloadExpert v1.1</strong> </font></a> <hr> </li> </ul> <p> </p> <h1 align="center"><a name="VAX DBMS Ported to Alpha Architecture">VAX DBMS Ported to Alpha Architecture</a></h1> <p align="center"><font size="2"><b>By Bryan Holland</b></font> </p> <p>Alpha is DEC's new 64-bit RISC based architecture that will run Open-VMS. Alpha is the future -- and VAX DBMS is there. Engineers within Digital's Database Systems Group have successfully ported DBMS to the ALPHA platform. Porting DBMS to the Alpha architecture shows Digital's commitment to its customers and to the future of DBMS. </p> <p>Digital took the opportunity to show off DBMS running on the Alpha platform during the recent DECUS and DECWorld events. </p> <p>The developers ported DBMS v4.3 to the Alpha platform. All features, program and user interfaces and on-disk structures remain unchanged from the VAX version of the product. DBMS <i>is</i> DBMS, regardless of what platform it is running on. </p> <p>Since the on-disk structures are consistent across platforms, it will be possible to access the same VAX DBMS database from either the VAX platform or the Alpha platform. </p> <h2><font size="4">The Porting Effort </font></h2> <p><font size="3">While "VMS is VMS", porting DBMS to the Alpha platform was by no means a "recompile and run" effort. Many portions of DBMS run in elevated processor modes and make use of several low-level VMS interfaces, thus requiring modification to run in the Alpha environment.</font><font size="4"> </font></p> <p align="center"><strong>All features, program and user interfaces and on-disk structures remain unchanged from the VAX version of the product. </strong></p> <p><font size="3">One of the primary goals of the porting effort was to maintain a common code-base (source code) across platforms. To do this, it was necessary to eliminate dependencies on certain VAX architectural features -- requiring a significant amount of code to be rewritten. According to Digital engineers, over 99 percent of the DBMS code is now common to both platforms.</font><font size="4"> </font></p> <h2><font size="4">Porting Your DBMS Applications to Alpha </font></h2> <p><font size="3">According to engineers in the Database System Group, porting customer DBMS applications from the VAX to Alpha will generally require only a recompile. Customer code generally does not make use of low-level VMS interfaces or operate in elevated processor modes. Thus, most customer ports will be very straight forward. However, to get the full benefits of the Alpha architecture, customers will have to naturally align their data structures.</font><font size="4"> </font></p> <h2><font size="4">Data Alignment Issues </font></h2> <p><font size="3">To get the best performance from the Alpha architecture, customers will want to naturally align the data structures in their programs. Naturally aligned data structures will yield performance improvements on both the VAX and Alpha architecture's. While naturally aligning data structures in the VAX environment will improve performance, doing so in the Alpha environment significantly improves performance.</font><font size="4"> </font></p> <h2><font size="4">Naturally Aligned Data </font></h2> <p align="center"><strong>The port of DBMS to the Alpha platform is proof of Digital's strong ongoing commitment to DBMS and their customers. </strong></p> <p><font size="3">Naturally aligned data, is data whose lowest addressed byte resides at an address that is an even multiple of the size of the item in bytes.</font><font size="4"> </font></p> <ul> <li><font size="3">a BYTE is naturally aligned at any address</font><font size="4"> </font></li> <li><font size="3">a WORD is naturally aligned at an even address</font><font size="4"> </font></li> <li><font size="3">a LONGWORD is naturally aligned at a multiple of 4</font><font size="4"> </font></li> <li><font size="3">a QUADWORD is naturally aligned at a multiple of 8</font><font size="4"> </font></li> </ul> <p><font size="3">Many of the Alpha compilers will handle data alignment issues, or issue warnings for unaligned data. Moving customer applications to the Alpha platform will generally be a straight forward path.</font><font size="4"> </font></p> <h2><font size="4">Translating VAX Images to Alpha with VEST</font> </h2> <p><font size="3">Digital is offering customers two migration paths to the Alpha platform from the VAX. The first, and usually the most straight forward, is the "recompile and run" port. However, for customers that do not have access to their application's source code, the VEST (<i>Vax Environment Software Translator</i>) may be able to translate their VAX images into Alpha images. Digital has been using the VEST translator for over a year internally. VEST will convert your VAX executables into Alpha executables. VEST is essentially a compiler that uses VAX/VMS images as its source code. There are two issues surrounding VEST translated images that customers must be aware of:</font><font size="4"> </font></p> <ol> <li><font size="3">Translated images will execute on the Alpha platform at roughly VAX speed -- They will not be able to take advantage of the Alpha architecture.</font><font size="4"> </font></li> <li><font size="3">The ability to call privileged sharable images (such as the DBCS) is questionable for VEST translated images. The jury is still out on whether VEST will provide a valid migration path for porting your VAX DBMS applications to Alpha.</font><font size="4"> </font></li> </ol> <p><font size="3">In general, customers will want to "recompile and run" their applications to gain the greatest benefit from the new Alpha architecture. However, naturally aligning data will yield significant performance improvements in the Alpha environment.</font><font size="4"> </font></p> <p><font size="3">The port of DBMS to the Alpha platform is proof of Digital's strong ongoing commitment to DBMS and their customers. Alpha is Digital's architecture to move them into the next century, and DBMS will be there to support your high-performance application requirements!</font></p> <p align="center"><a href="#Contents"><font size="3">Contents</font></a></p> <hr> <p><font size="2"><br> </font></p> <h1 align="center"><a name="VAX DBMS Hints, Kinks">VAX DBMS Hints, Kinks<br> and Work-Arounds </a></h1> <p align="center"><font size="2"><b>By SCI Technical Staff</b></font> </p> <p> </p> <p><font size="3">DBMS is a robust product with many features and parameters. Which features should be implemented, and how they should be implemented, varies from site to site. Determining the optimal configuration for <i>your environment</i> is not a trivial task!</font><font size="4"> </font></p> <p><font size="3"><b>This section will be reserved in future issues of DBAdvisor for </b><b><i>your</i></b><b> technical DBMS questions. SCI</b> staff will provide answers to your questions, and discuss alternative solutions.</font><font size="4"> </font></p> <h2><font size="4">SPAM Errors reported by DBO/VERIFY/ONLINE </font></h2> <p><font size="5"><b>Q:</b></font><font size="2"> </font><font size="3">When running DBO/VERIFY/ONLINE, we receive the following error messages:</font><font size="4"> </font></p> <p><font size="3">"DBO-W-PGSPAMENT, area <str>, page <int>; the <int>% fullness value for this data page does not fall within the <int>-<int>% range found on the space mgmt. page".</font><font size="4"> </font></p> <p><font size="3">However, if we run DBO/VERIFY standalone on the <i>same</i> database the error messages do not appear. What are we doing wrong?</font><font size="4"> </font></p> <p><font size="5"><b>A:</b></font><font size="3"> The problem you are experiencing, is a timing problem. The DBO/VERIFY/ONLINE operation uses batch retrieval (snapshot files) to ensure a consistent view of the database during the online verify. However, the snapshot files contain only storage-area line entries (records), <i>not</i> the SPAM pages or SPAM entries for each page.</font><font size="4"> </font></p> <p><font size="3">Since the SPAM entries are updated as records are being added, deleted, or modified during the online verification operation, the updated SPAM values are <i>not</i> being written to the snapshot files.</font><font size="4"> </font></p> <p><font size="3">Although there are no perfect solutions to this problem, there are a couple of ways that you can eliminate or minimize the impact:</font><font size="4"> </font></p> <ol> <li><font size="3">Run DBO/VERIFY on a <i>copy</i> of the database restored from the most recent backup. This solution has the advantage that it not only verifies the internal integrity of your database, but it also validates the integrity of your DBO/BACKUP and media. The obvious down-side to this solution is the time and space required to implement it.</font><font size="4"> </font></li> <li><font size="3">To <i>minimize</i> the impact of this problem in your environment, you should schedule your DBO/VERIFY/ONLINE to run during <i>low update</i> periods. This makes sense for all /ONLINE operations since it will reduce the overhead of the /ONLINE process.</font> </li> </ol> <h2><font size="4">File Access Errors</font> </h2> <p><font size="5"><b>Q:</b></font><font size="3"> When accessing the database for CONCURRENT RETRIEVAL, we sometimes receive the following fatal error:</font><font size="4"> </font></p> <p><font size="3">DBM-F-FILACCERR, error opening storage area file <str>; -SYSTEM-W-ACCONFLICT, file access conflict.</font><font size="4"> </font></p> <p><font size="3">There were <i>no</i> other processes accessing this database when the error occurs. This problem seems to occur more frequently during the evening hours.</font><font size="4"> </font></p> <p><font size="5"><b>A:</b></font><font size="3"> On the basis of our analysis, the problem you are experiencing is a conflict with the VMS system BACKUP. This would explain why you are seeing the problem more frequently during the evening (when your backups are scheduled).</font><font size="4"> </font></p> <p><font size="3">First, you should make <i>absolutely certain</i> that you are <i>not</i> using VMS backup to backup your DBMS database (see Backup article in this issue). Use the DBO/BACKUP utility to backup your DBMS database. Next, you should ask your system manager to exclude all database backups from the VMS system backup (/EXCLUDE= *.DBS,*.SNP,*.ROO). In addition, SCI recommends that customers use the DCL COMMAND SET FILE/NOBACKUP to ensure that DBMS files are not accidentally backed (and restored) by the system backup and restore procedures. If you attempt to use VMS to restore a file that was marked "/NOBACKUP", the error will be obvious -- <i>an empty file will be restored</i> (VMS will backup the header of files marked with /NOBACKUP). <b><i>Always </i></b><i>use the DBO/BACKUP and DBO/RESTORE routines to backup and restore your DBMS databases!</i></font><font size="4"> </font></p> <h2><font size="4">Extraneous AREABUSY exceptions</font> </h2> <p><font size="5"><b>Q:</b></font><font size="2"> </font><font size="3">Periodically, we receive the error message, "DBM-F-AREABUSY, usage of storage area <str> conflicts with a co-user", when we attempt to ready our database for CONCURRENT RETRIEVAL -- An extensive review of our code found <i>no</i> cases where any of the areas were readied in an incompatible mode.</font><font size="4"> </font></p> <p><font size="5"><b>A:</b></font><font size="2"> </font><font size="3">VAX DBMS can return the DBM-F-AREABUSY message on a READY statement even though compatible ready modes (CONCURRENT RETRIEVAL and CONCURRENT UPDATE) are being used. This situation can occur when an attempt is made to ready an area while database recovery is in progress. During database recovery, all database activity is frozen until the DBR recovery process has completed. When this situation arises, VAX DBMS will not wait for the freeze to complete if the lock request is a NOWAIT lock request. On READY statements, NOWAIT is the default for area locks requests.</font><font size="4"> </font></p> <p><font size="3">There are a couple of ways to handle this situation:</font><font size="4"> </font></p> <p><font size="3">1. Ready the database with WAIT (READY CONCURRENT RETRIEVAL WAIT). This will make the process issuing the ready wait for an area lock to be granted until after a database freeze has completed.</font><font size="4"> </font></p> <p><font size="3">2. Modify your programs to trap for this exception, wait a few seconds and then try to READY the area again.</font> </p> <p> </p> <ul> </ul> <p align="center"><font color="#000000"><font size="1"><strong>Send Your VAX DBMS related Questions to</strong></font></font><font color="#FFFFFF"><font size="1">:</font></font> </p> <p align="center"><font size="1"><b><i>DBAdvisor<br> c/o Software Concepts International<br> </i></b><br> Nashua, NH 03063, USA<br> Phone: (603) 879-9022<br> Internet Address:<br> </font>sciinc@sciinc.com</p> <p align="center"><font size="1">Answers will be published in<br> <i>VAX DBMS Hints, Kinks and Workarounds</i><b><i> </i></b>in the <i>next</i> issue of the DBAdvisor</font> </p> <p align="center"> <a href="#Contents"><font size="3">Contents</font></a></p> <hr> <p align="center"> </p> <p><a name="Quick"><font size="7"><b><i>Quick</i></b></font></a><font size="7"><b><i>Disconnect</i></b></font><font size="3"><b><i>™</i></b></font> </p> <p> </p> <p><font size="4"><b><i>QuickDisconnect</i></b><b> Enables You to Reorganize </b><b><i>Portions</i></b><b> of Your Database Easily and Efficiently by Removing Cross-Area-Pointers!</b></font> </p> <p><font size="3">Software Concepts International announced plans to develop <i>QuickDisconnect</i>. According to Bryan Holland, SCI owner, <i>QuickDisconnect</i> will offer significant performance improvements over traditional 3GL/DML (Data Manipulation Language) approaches, when disconnecting all records from a set.</font><font size="4"> </font></p> <p><font size="3">This is a perfect solution for any VAX DBMS site that needs to reorganize only a portion of their database. <i>QuickDisconnect</i> efficiently removes all cross-area-pointers broken by an initialize after you have unloaded the data in the "problem area".</font><font size="4"> </font></p> <h3>Cross-Area-Pointers</h3> <p><font size="3">Cross-Area-Pointers (CAPs) connect records in one database storage area with records in a different storage area. Most database designs include the use of CAPs, since they offer greater flexibility and significant performance benefits over single area database designs. However, if you initialize a storage area with CAPs, the set pointers in the remaining area no longer point to a valid DBMS record -- your database is corrupt.</font><font size="4"> </font></p> <h3>The Reload Dilemma</h3> <p><font size="3">If you ever tried to reorganize a single area in your database, you have no doubt been frustrated with having to deal with cross-area-pointers. If the schema definition permits, you can use the DML verb, DISCONNECT, to remove these set relationships before initializing the "problem area" . However, if the set is defined as FIXED or MANDATORY, you are forced to reload and erase additional records that are not stored within the "problem area" -- possibly <i>forcing</i> you to reload the <i>entire</i> database.</font><font size="4"> </font></p> <p>  </p> <table cellpadding="0" cellspacing="0" width="100%"> <tr> <td width="50%"> <h3>Traditional Methods Are Inefficient</h3> <p><font size="3">Using a traditional 3GL/DML approach to this problem is <i>grossly inefficient</i>. A DML DISCONNECT or ERASE verb must update at least three records for <i>every</i> disconnect performed...and every member occurrence must be disconnected before you can initialize the problem area! If you consider that each disconnect may result in multiple I/Os to the database, you can see why the DML DISCONNECT or ERASE option is very expensive.</font><font size="4"> </font></p> </td> <td width="50%"></td> </tr> </table> <h3>The QuickDisconnect Solution</h3> <p><font size="3"><i>QuickDisconnect</i> is a specialized program, designed to efficiently remove <i>all</i> record occurrences from a set. <i>QuickDisconnect</i> does this by directly removing the pointer clusters and index nodes of the set-type from all appropriate records in the database in a <i>single</i> pass through the area. Database pages are read only once and the affected records are updated only <i>once</i>.</font><font size="4"> </font></p> <p><font size="3"><i>QuickDisconnect</i> further improves performance by reading large portions of the database into its buffers with each I/O, thus reducing the number of I/Os to an absolute minimum.</font><font size="4"> </font></p> <h3>Disconnects Fixed and Mandatory Sets!</h3> <p><font size="3"><i>QuickDisconnect</i> disconnects sets by directly removing the pointer clusters of the appropriate set-types -- regardless of SCHEMA retention modes. Thus, even Fixed or Mandatory sets can be disconnected with <i>QuickDisconnect</i>! After unloading the data in the problem area and initializing the area the CAPS are efficiently removed by <i>QuickDisconnect</i>. The set-connections are reestablished using the DML CONNECT verb -- even for set-types defined with an AUTOMATIC insertion mode and a FIXED or MANDATORY retention mode.</font><font size="4"> </font></p> <h3>Improves Availability</h3> <p><font size="3"><i>QuickDisconnect</i> allows you to partition your database reorganizations into more manageable pieces. Being able to reorganize individual areas of your database means less time reorganizing your database...and that improves the availability of your database!</font><font size="4"> </font></p> <p><font size="3"><i>QuickDisconnect</i></font><font size="4"><i> </i></font><font size="3">is the ideal solution for any situation that requires <i>all</i> members of a set to be disconnected from their owners.</font></p> <h3>Licensing</h3> <p><font size="3"><i>QuickDisconnect</i> will be licensed on a per-node basis. Priced at only $9,995 for the distribution license, and $4,995 for the right-to-copy license regardless of node size.</font><font size="4"> </font></p> <h3>Limited time, Special Offer!</h3> <p><font size="3">SCI is offering a 25 percent discount for customers who sign predevelopment POs. This offer provides customers with significant savings with no risk.</font><font size="4"> </font></p> <p><font size="2">*<i>QuickDisconnect</i> is a trademark of<br> </font><font size="4">Software Concepts International </font></p> <p><font size="2"><i>*VAX DBMS is a trademark of<br> Digital Equipment Corporation<br> </i></font></p> <p><font size="3">Call Software Concepts International at (603) 879-9022 to order <i>QuickDisconnect</i> or <i>ReloadExpert</i>! </font></p> <p align="center"><font color="#000000"><font size="2"><b>FOR MORE INFORMATION</b></font></font> </p> <p align="center"><font size="2"><b><i>New England<br> Software Concepts<br> </i></b><br> Nashua, NH 03063<br> (603) 879-9022</font> </p> <p align="center"><a href="#Contents"><font size="3">Contents</font></a></p> <hr> <p align="center"> </p> <h1 align="center"><font size="7">Implementing </font><a name="SPAMS"><font size="7">SPAMS</font></a><font size="7"> for Performance </font></h1> <p align="center"> </p> <p align="center"><font size="2"><b>By SCI Technical Staff</b></font> </p> <p><font size="3">Space Area Management (SPAM) is a technique used by VAX DBMS to improve the storage performance of nearly full storage areas. The objective of SPAM technology is to allow the DBCS (Database Control System) to quickly locate a page with sufficient free space to store new records in your database. SPAM pages maintain an inventory of available space on database storage area pages. They enable databases with SPAM pages to spend less time searching for free in your database during <i>STORE</i> operations. Without SPAM technology, the DBCS would have to search each page in the storage area until it finds a page with sufficient free space to store the new record.</font><font size="4"> </font></p> <p><font size="3">This article examines the inner workings of SPAM technology. By understanding the purpose of SPAMs and how they work, you can make better decisions when implementing SPAMs in your database design. You will also learn why decreasing SPAM thresholds to a tiny number in versions prior to V4.3 didn't result in a decreased level of record fragmentation.</font><font size="4"> </font></p> <p><font size="3">The optimal SPAM values for your database storage areas depend on the size of the records stored in the area, the desired record placement strategy, the level of database contention in your database, as well as the "typical" order in which records are stored on a database page. For example, storing a large record followed by several smaller records requires different strategy than if the small records are stored first, followed by the large records.</font><font size="4"> </font></p> <p><font size="3">SPAM pages maintain a bit-mapped inventory of the free space available on the storage pages in your database. Two (2) bits are used to track the free space of each storage page in your database.</font><font size="4"> </font></p> <table border="1"> <tr> <td width="53"><font size="2">SPAM<br> Bits</font> </td> <td width="48"><font size="2">Decimal</font></td> <td width="54"><font size="2">Threshold </font></td> <td width="60"><font size="2">Default</font></td> </tr> <tr> <td width="53">0 0</td> <td width="48">0</td> <td width="54">0</td> <td width="60">0</td> </tr> <tr> <td width="53">0 1</td> <td width="48">1</td> <td width="54">1</td> <td width="60">70</td> </tr> <tr> <td width="53">1 0</td> <td width="48">2</td> <td width="54">2</td> <td width="60">85</td> </tr> <tr> <td width="53">1 1</td> <td width="48">3</td> <td width="54">3</td> <td width="60">95</td> </tr> </table> <p align="left"> </p> <p align="center"><strong>Without SPAM technology, the DBCS would have to search each page in the storage area until it finds a page with sufficient free space to store the new record.</strong> </p> <p>Thus, the free space on each database page is represented by a value between zero (0) and three (3), inclusive. These values correspond to the threshold values you set when you create the database (or when you restore the database using DBO/RESTORE/THRESHOLD). The SPAM entry for a page is updated whenever the amount of data on that page crosses a threshold boundary. </p> <p>It is important to note, that while the DBCS uses the SPAM pages to locate pages with sufficient <i>free space</i>, the threshold values of the SPAM pages are based on <i>used space</i>. </p> <p> </p> <h2><font size="4">The SPAM algorithm<br> (pre v4.3):</font> </h2> <p><font size="3">The DBCS first checks the <b>target page</b> for free space, <i>regardless of SPAM thresholds</i>. The target page is the page in the database that the DBCS <i>wants</i> to store the new record based on the <i>PLACEMENT</i> clause in the storage schema. If there is not enough space available on the target page for the new record, then the DBCS checks the other pages in the <i>current database buffer</i> for a page with sufficient free space to store the new record. The <i>current database buffer</i> is the buffer that contains the target page (as well as additional database pages based on the buffer length). The DBCS <u>only</u> checks the SPAM pages to look for free space when none of the pages in the current buffer contain sufficient free space to store the new record. Thus, with versions of DBMS prior to version 4.3, it is possible to exceed the third threshold (T3) when storing new records. That is, there was no way to define the maximum fullness of the database pages for storing new records. This could cause excessive fullness of the database pages, resulting in severe record fragmentation when the DBCS adds set pointer clusters to records during a CONNECT operation...<i>regardless of SPAM values!</i></font><font size="4"> </font></p> <p>  </p> <h2><font size="4">The SPAM algorithm<br> (v4.3):</font> </h2> <p><font size="3">The SPAM algorithm has been enhanced slightly for version 4.3 of DBMS. While earlier releases of DBMS would store a new record on any page in the buffer containing the target page <i>regardless of SPAM values, version 4.3 will not</i> store a record on a page that is <i>already </i>at the T3 threshold<i>.</i></font><font size="4"> </font></p> <p><font size="3">Sites that calculated SPAM thresholds based on the old (pre v4.3) algorithm should adjust their SPAM thresholds to reflect the new SPAM algorithm introduced with version 4.3.</font><font size="4"> </font></p> <p><font size="3">While this change offers some improvement over the prior SPAM algorithm, what database designers really need is the ability to specify how much <i>free</i> space to <i>leave</i> on a page. Thus, by knowing how many records will be stored on a page and how much each record is likely to grow (due to set insertions and dynamic data items), it would be possible to reserve this space on the page for future record expansion.</font><font size="4"> </font></p> <p>  </p> <h2>SPAM parameters</h2> <p><font size="3">There are two database parameters that affect Space Management; the <i>threshold</i> values, and the <i>interval</i>. Selecting optimal values for SPAM parameters can dramatically improve database performance -- Using the <i>default</i> values are <i>guaranteed</i> to be a <i>poor</i> choice.</font><font size="4"> </font></p> <p><font size="3">The threshold values determine the percent fullness at which the SPAM entries are updated, while the interval determines how many database storage pages are<i> </i>managed by each SPAM page.</font><font size="4"> </font></p> <p>  </p> <h2>SPAM Interval</h2> <p align="center"><b>Selecting optimal values for SPAM parameters can dramatically improve<br> database performance<br> Using the </b><b><i>default</i></b> <strong>values are </strong><strong><i>guaranteed</i></strong><strong> to be a </strong><strong><i>poor</i></strong><strong> choice. </strong></p> <p><font size="3">The /INTERVAL qualifier determines how many data pages are managed by each SPAM page in a database storage area.</font> </p> <table border="1" width="50%"> <tr> <td width="19"> </td> <td colspan="7" width="134"><font size="1"><b>Interval</b></font> </td> <td width="19"> </td> <td colspan="7" width="134"><font size="1"><b>Interval</b></font> </td> </tr> <tr> <td width="19"><font color="#FFFFFF"><font size="1"><b>S</b></font></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>...</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font color="#FFFFFF"><font size="1"><b>S</b></font></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>...</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td width="19"><font size="1"><b>d</b></font> </td> <td colspan="16" width="1459"><font size="1"><b>d</b></font> </td> </tr> <tr> <td width="19"><font color="#FFFFFF"><font size="1"><b>P</b></font></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>...</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font color="#FFFFFF"><font size="1"><b>P</b></font></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>...</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td colspan="16" width="1459"><font size="1"><b>a</b></font> </td> </tr> <tr> <td width="19"><font color="#FFFFFF"><font size="1"><b>A</b></font></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>...</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font color="#FFFFFF"><font size="1"><b>A</b></font></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>...</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td width="19"><font size="1"><b>t</b></font> </td> <td colspan="16" width="1459"><font size="1"><b>t</b></font> </td> </tr> <tr> <td width="19"><font color="#FFFFFF"><font size="1"><b>M</b></font></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="2"><b>a</b></font></td> <td width="19"><font size="1"><b>...</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font color="#FFFFFF"><font size="1"><b>M</b></font></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>...</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td width="19"><font size="1"><b>a</b></font> </td> <td colspan="16" width="1459"><font size="1"><b>a</b></font> </td> </tr> <tr> <td colspan="16" width="307"><font size="1"><b>SPAM Interval Diagram</b></font> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> <td width="96"> </td> </tr> </table> <p>Increasing the SPAM interval may reduce I/O, but may cause additional contention for SPAM pages in a high contention update environment. </p> <p>The default interval for SPAM pages is 256. This means that, by default, 256 database storage pages are managed by each SPAM page. This is also the minimum value that you can specify for the SPAM interval. The maximum SPAM interval value is determined by the following equation: </p> <p>MAXIMUM_SPAM_INTERVAL = ((blocks-per-page * 512) - 22) * 4 </p> <p>The value of 512 is the number of bytes per block. The value of 22 is the number of bytes in the SPAM page header, and the value of 4 is the number of SPAM entries (two bits) per byte (eight bits). </p> <p>In general, the larger your storage area, the larger the SPAM interval value should be. This methodology optimizes SPAM effectiveness by reducing the free space search time, while balancing the likelihood of SPAM page conflict. Low contention environments should use the maximum interval value, while high-contention <i>update</i> environments should lower their interval values. </p> <p align="center"><font size="3"><b>The</b><b><i> Optimal</i></b><b> SPAM Values can significantly improve database performance! Determining what the "optimal" values are is a </b><b><i>difficult</i></b><b> task!</b></font><font size="2"> </font></p> <p align="center"><font size="4"><b>Software Concepts International staff is the recognized authority in VAX DBMS technical issues.</b></font><font size="2"> </font></p> <p align="center"><font size="4"><b>Call (603) 879-9022 for <br> VAX DBMS Technical Consulting!</b></font><font size="2"> </font></p> <p><font size="3">The trade-off when setting SPAM intervals is data page I/O (searching for free space in which to store new records) versus SPAM page conflicts (which results in additional synchronization and I/O to the SPAM page). DBMS provides statistics to help you measure the performance impact of both sides of the SPAM interval equation.</font><font size="4"> </font></p> <h2>Interval Statistics</h2> <p><font size="3">The best measure of SPAM page contention can be seen on the PIO statistics page of DBO STATISTICS. The statistic, "unmark buffer -- SPAM page", measures how often SPAM pages are being written back to the database (SPAM page write I/Os). However, this statistic is not a direct measure of SPAM page contention, since SPAM pages are written back to the database due COMMIT/ROLLBACK, buffer overflows as well as contention.</font><font size="4"> </font></p> <h3>PIO Statistics</h3> <p><font size="4">To make sense of the "unmark buffer -- SPAM page" statistic, compare this statistic with the other statistics in the "unmark buffer" group. </font></p> <p><font size="3">If the "unmark buffer -- SPAM page" statistic is significantly higher than the "unmark buffer -- transaction", this is good indication of SPAM page conflicts.</font><font size="4"> </font></p> <p><font size="3">Another measure of SPAM page contention can be found by comparing the "unmark buffer -- SPAM page" with the "unmark buffer -- lock conflict" statistic. However, use caution when making this comparison -- the "unmark-buffer -- lock conflict" statistic includes <i>all</i> <i>page</i> buffers -- not just the SPAM pages.</font><font size="4"> </font></p> <p><font size="3">SPAM page contention also shows up in the page locking statistics. Increased SPAM contention would cause an increase in blocking ASTs for page locks. However, since the page locks also manage the entire <i>buffer</i> pool, it is difficult (if not impossible) to determine the severity or cost of SPAM page conflicts from this statistic.</font><font size="4"> </font></p> <h2>SPAM Thresholds</h2> <p><font size="3">You can specify up to three threshold values for the /THRESHOLD qualifier. It is possible to set two or more of the threshold values to the same number, thus effectively eliminating one of the threshold levels. If you have only one or two record-types of different lengths in your storage area, use only one or two threshold values to manage space in that storage area. Reducing the number of thresholds <i>may reduce the frequency in which the SPAM pages are updated, thus reducing SPAM page conflicts.</i> By default, DBMS sets three thresholds, with the values 70, 85, and 95 percent.</font><font size="4"> </font></p> <p><font size="3">SPAM threshold values are based on the percentage of <i>used</i> space on a database page. However, to effectively plan your threshold values, you must think of threshold values in terms of how much <i>free</i> space is <i>guaranteed to exist</i> on a database page for each threshold value. The following table helps to illustrate this concept:</font><font size="4"> </font></p> <table border="2"> <tr> <td width="79"><br> <font size="2">Threshold</font> </td> <td width="60">SPAM<br> Value</td> <td width="94">Guaranteed Percent Free</td> </tr> <tr> <td width="79">0</td> <td width="60">0</td> <td width="94">30%</td> </tr> <tr> <td width="79">1</td> <td width="60">70</td> <td width="94">15%</td> </tr> <tr> <td width="79">2</td> <td width="60">85</td> <td width="94">5%</td> </tr> <tr> <td width="79">3</td> <td width="60">95</td> <td width="94">0%</td> </tr> </table> <p>Thus, if you attempt to store a record that occupies 65% of the database page, the DBCS will check every page that is at the T0 threshold. The DBCS will not waste time checking those pages whose threshold values exceed the T0 level, since they do not <i>guarantee</i> sufficient <i>free space</i> for the new record. Note, that in this example, it is still possible that the DBCS will physically examine several database pages since the lowest threshold value (70) guarantees only 30% free space, while 65 percent is required to store this new record. Thus, if most or all of the pages in the storage area are above 35% fullness, the DBCS will yield little or no benefit from the SPAM thresholds. An approximation of the efficiency of this threshold value can be measured by using the following equation: </p> <p>SPAM_efficiency =</p> <p><u>SPAM_threshold - (100 - pct_needed)</u><br> SPAM_threshold - SPAM _ threshold (-1)</p> <p><u>= 70 - (100 - 65)</u> = 50%<br> 70 - 0</p> <p>In this example, only 50 percent of the pages at the T0 threshold will have sufficient space to store the new record! </p> <p>This equation is only approximate, since it assumes a normal distribution of page percentage fullness. In practice, the actual distribution page fullness tends <i>not</i> to follow a normal distribution. </p> <p align="center"><strong>The objective when selecting thresholds, is to choose values that will minimize the amount of physical I/O necessary to store new records in the database. </strong></p> <p>The objective when selecting thresholds, is to choose values that will minimize the amount of physical I/O necessary to store new records in the database. Since the record size, placement strategies, and update activity is likely to vary from area to area you must calculate and use different threshold values for each area in your database. </p> <h2>SPAM Algorithms</h2> <p><font size="3">To determine the optimal threshold values, we will need to understand more about the free space search algorithm used by VAX DBMS.</font><font size="4"> </font></p> <h3>MAX_FREE_LEN...</h3> <p>The actual amount of space that SPAMS are based on is called the "maximum free length". This value represents the amount of space available on a database page for storing user records (page size less page overhead). The calculation for the max_free_len is different for areas with snapshots and those areas without snapshots. </p> <p align="center"><b>Pages with snapshots:<br> <br> MAX_FREE_LEN =<br> </b>(blocks-per-page * 512)<br> - (24 + 4 + 4 + 18 + 10)<br> <font size="2"><b>or<br> </b>(blocks-per-page * 512) - 60</font> </p> <p align="center"><font size="2"><b>Pages without snapshots:<br> <br> MAX_FREE_LEN =<br> </b>(blocks-per-page * 512)<br> - (24 + 4 + 10)<br> <b>or<br> </b>= (blocks-per-page * 512) - 38</font> </p> <p><font size="3">The value of 512 is the number of bytes per block. The value of 24 is the number of bytes in the database page header. The first value of 4 is the number of bytes per line-index entry, the second value of 4 (pages with snapshots only) is the number of bytes per TSN (Transaction Sequence Numbers are used by snapshot transactions) index entry. The value of 18 (pages with snapshots only) is the number of bytes in the page footer. The value of 10 is the minimum number of bytes reserved for the SYSTEM record on each page.</font><font size="4"> </font></p> <p align="center"><font size="2"><b>Thus, any threshold value greater than 90% (such as the default T3 value of 95%) is useless!</b></font> </p> <p><font size="3">SPAM thresholds are percentages of MAX_FREE_LEN, and are always rounded down to the next even byte. Thus, the MAX_FREE_LEN of a storage area with snapshots and a page size of 2 blocks per page is (1024 - 60) or 964 bytes. Using the default thresholds of 70, 85, and 95, the actual threshold byte-values are 674, 818, and 914 respectively. For an existing database, you can confirm these values by issuing the following command:</font><font size="4"> </font></p> <p><font size="3">$ DBO/DUMP root-file-spec/OPT=DEBUG</font><font size="4"> </font></p> <p><font size="3">For each storage area, look for the following symbols:</font><font size="4"> </font></p> <p><font size="3">SPAM_T1 = 1st SPAM threshold in bytes</font><font size="4"> </font></p> <p><font size="3">SPAM_T2 = 2nd SPAM threshold in bytes</font><font size="4"> </font></p> <p><font size="3">SPAM_T3 = 3rd SPAM threshold in bytes</font><font size="4"> </font></p> <h3>Determining required space...</h3> <p>Next, we need to understand how much space the DBCS will look for when storing a new record. The DBCS uses the following equation to determine the required space needed to store the new record: </p> <p align="center"><b>REQUIRED_LEN =<br> </b>MAX(record-len + 5, 10)<br> + PAGE_PAD_LEN </p> <p>The value of <b><i>record-len</i></b> is the size of the <b><i>static data portion</i></b> of the record, as defined by the storage schema. It is easy to determine this value for each of the record-types in your database by using DBO/DUMP/STORAGE/OPTION = DEBUG and searching for the value of the "Static data portion length" of each record-type. </p> <h3>PAGE_PAD_LEN...</h3> <p>The DBCS actually reserves a portion of each database page (based on the PAGE_PAD_LEN) for future record growth on the page. The value of PAGE_PAD_LEN is currently calculated as 10 percent of the physical page size (blocks-per-page * 512) * 0.10 -- Thus, any threshold value greater than 90% (such as the default T3 value of 95%) is <i>useless</i>! </p> <p>The value of PAGE_PAD_LEN for each area can be verified by dumping the database root file as described above. Search for the value of PAGE_PAD_LEN for each storage area in your database. </p> <p><font size="3">As stated previously, the value used by the DBCS for the record length, is the length of the <b><i>static</i></b> data portion of the record. This value can be verified using DBO/DUMP/STORAGE/OPTION = DEBUG and looking for the value of the "Static data portion length" for each record-type in your database. This value does not include the length of known (automatic) set pointer clusters, or the length of <i>dynamic</i> storage items. In other words, the DBCS does not use the actual record-length of the storage segment -- the value used by the DBCS will <i>always </i>be<i> less</i> than the actual record length.</font><font size="4"> </font></p> <p><font size="3">When looking at the SPAM pages, the DBCS looks for the largest SPAM threshold (in terms of bytes) that is less than or equal to MAX_FREE_LEN - REQUIRED_LEN. </font></p> <p><font size="3">Pages at the T3 threshold are <i>never</i> searched, since they cannot guarantee <i>any</i> free space. Pages at the T0 level are <i>always</i> searched, since they guarantee the "most" free space.</font><font size="4"> </font></p> <p><font size="3">The following SPAM Selection Example Table illustrates what threshold value DBCS will search for using the default thresholds for a database area with snapshots and a page size of two blocks per page, and assuming a page pad length of ten percent (102 bytes):</font><font size="4"> </font></p> <h2><font size="4">Threshold Statistics </font></h2> <p><font size="3">The objective when setting threshold values, is to minimize the number of pages the DBCS searches when storing new data. DBMS provides several statistics that can help you measure the effectiveness of your SPAM thresholds.</font><font size="4"> </font></p> <h3>Record Statistics</h3> <p>The <i>Record Statistic, Pages Checked,</i> is the single<i> </i>most useful statistic for determining the overall effectiveness of your SPAM thresholds. This statistic shows the number of data pages that were checked before the DBCS found a page with sufficient space to store the record. By comparing the <i>Pages Checked</i> statistic, with the <i>Records Stored</i> statistic, you can get a very accurate picture of the effectiveness of your SPAM thresholds. In a well-tuned database, <i>Pages Checked </i>should be no more than 2 times <i>Records Stored</i>. </p> <h3>PIO Statistics</h3> <p>The PIO Statistic, "SPAM Page Request", provides one measure of the effectiveness of your SPAM strategy. A high number of SPAM page requests relative to the database activity may indicate that your SPAM interval is too low, and that the DBCS is having a difficult time locating free space for new records. This number will also increase when the storage area becomes over utilized. </p> <table border="1"> <tr> <td colspan="7" width="398"><b>SPAM Selection Example</b> </td> </tr> <tr> <td width="44"><font size="2">Threshold <br> Level</font> </td> <td width="42"><font size="2">Max Free Bytes</font> </td> <td width="36"><font size="2">Max Free Len</font> </td> <td width="42"><font size="2">Guar <br> Free<br> Bytes</font> </td> <td width="36"><font size="2">Page<br> Pad<br> Len</font> </td> <td width="48"><font size="2">Max Record Size</font> </td> <td width="150"><br> <br> <font size="2">When Threshold is used</font></td> </tr> <tr> <td width="44"><font size="2">T3</font> </td> <td width="42"><font size="2">914</font> </td> <td width="36"><font size="2">964</font> </td> <td width="42"><font size="2">0 </font></td> <td width="36"><font size="2">102</font> </td> <td width="48"><font size="2">= -107</font></td> <td width="150"><font size="2"><b><i>Never Searched!</i></b></font> </td> </tr> <tr> <td width="44"><font size="2">T2</font> </td> <td width="42"><font size="2">818</font> </td> <td width="36"><font size="2">964</font> </td> <td width="42"><font size="2">50</font> </td> <td width="36"><font size="2">102</font> </td> <td width="48"><font size="2">= -57</font></td> <td width="150"><font size="2"><b><i>Never Searched!</i></b></font> </td> </tr> <tr> <td width="44"><font size="2">T1</font> </td> <td width="42"><font size="2">674</font> </td> <td width="36"><font size="2">964</font> </td> <td width="42"><font size="2">146</font> </td> <td width="36"><font size="2">102</font> </td> <td width="48"><font size="2">= 39</font></td> <td width="150"><font size="2">Searched only if the record-len<b> <= 39</b></font> </td> </tr> <tr> <td width="44"><font size="2">T0</font> </td> <td width="42"><font size="2">0 </font></td> <td width="36"><font size="2">964</font> </td> <td width="42"><font size="2">290</font> </td> <td width="36"><font size="2">102</font> </td> <td width="48"><font size="2">= 183</font></td> <td width="150"><font size="2">Always searched</font> </td> </tr> <tr> <td colspan="7" width="398"><font size="1">GUARANTEED_FREE_BYTES = Max_Free_Len - Max_Free_Bytes(T-1) </font> <p><font size="1">PAGE_PAD_LEN = 10% of Page size in bytes</font> </p> <p><font size="1">MAX_RECORD_SIZE = Guaranteed_Free_Bytes - Page_Pad_Len - 5</font> </p> </td> </tr> </table> <p align="center"> </p> <p align="center"> </p> <p align="center"><font color="#000000"><font size="4"><b>Specialized VAX DBMS training Seminars<br> from the leading VAX DBMS support specialists!</b></font></font> </p> <ul> <li><b>VAX DBMS Database Administration (5 days)</b> </li> <li><b>VAX DBMS DBA Performance (3 days)</b> </li> <li><b>VAX DBMS Internals & Data Structures (4 days)</b> </li> <li><b>VAX DBMS Programming (5 days)</b> </li> <li><b>VAX DBMS Advanced Programming (3 days)</b> </li> </ul> <p align="center"><b>Call Software Concepts International today<br> for seminar registration and information!<br> (603) 879-9022</b> </p> <p align="center"><a href="#Contents"><font size="3">Contents</font></a></p> <hr> <p align="center"> </p> <p align="center"> </p> <h1 align="center"><a name="Is Your Database Backup">Is Your Database Backup<br> Strategy Really Effective?</a><br> <font size="4"><i>(Can You Recover Your Database When Needed?)</i></font> </h1> <p align="center"><font size="2"><b>By Digital Equipment Corporation's<br> Database Engineering and Documentation Group</b></font> </p> <p align="center"> </p> <p><font size="3">VAX DBMS provides backup and restore utilities that offer many different backup options. A well-constructed database backup and recovery strategy will include a combination of these functions selected to match the needs of your particular environment. There is no single backup strategy that works best for all configurations. The wrong strategy could even cost more than it is worth.</font><font size="4"> </font></p> <p align="center"><strong>There is no single backup strategy that works best for all configurations. The wrong strategy could even cost more than it is worth. </strong></p> <p><font size="3">A proper database backup strategy should strive to achieve a balance between minimizing operational overhead and maximizing database integrity. Over the life of a database, you will perform many more backups than restores. Since you cannot predict when a disk device will fail, or when a database will become corrupt, you must continually backup your data as it changes. This article contains suggestions and recommendations on making frequent database backup operations easier and infrequent restore operations more reliable.</font> </p> <h2><font size="4">Rules of a Strategy</font> </h2> <p><font size="3">A database backup strategy should be customized for your environment. It should take into consideration the system workload, usage schedule, importance of data, and hardware environment of the database. However, there are some guidelines that apply to all databases. Incorporating these guidelines into your strategy will ensure more reliable and more cost effective backups.</font><font size="4"> </font></p> <p><font size="3">Digital strongly recommends the following guidelines:</font><font size="4"> </font></p> <ul> <li><font size="3">Always use after-image journal (.AIJ) FILES.</font><font size="4"> </font></li> <li><font size="3">Always maintain a log of your backup schedule.</font><font size="4"> </font></li> <li><font size="3">Occasionally store backups in a separate location.</font><font size="4"> </font></li> <li><font size="3"><i>Do not</i> use the VMS Backup Utility on a database.</font><font size="4"> </font></li> <li><font size="3">Spread your database across several disks.</font><font size="4"> </font></li> <li><font size="3">Always consider your existing hardware configuration and system workload.</font><font size="4"> </font></li> <li><font size="3">Determine the volatility and value of your data.</font><font size="4"> </font></li> <li><font size="3">Verify the integrity of your database before each full backup.</font><font size="4"> </font></li> <li><font size="3">Verify the integrity of your backup media after each backup.</font><font size="4"> </font></li> </ul> <h2><font size="4">Always use after-image (AIJ) files </font></h2> <p><font size="3">Digital strongly recommends the use of after-image journaling. As data modifications are made to your database, an .AIJ file captures the operations each transaction makes. This provides exceptional recovery capabilities because you will be able to restore your database to the same state it was in before the corruption or hardware failure occurred. Considering the balance between cost effectiveness and data reliability, .AIJ files typically cost 5-10% (or less) in overhead but provide essentially a continuous backup of your transactions.</font><font size="4"> </font></p> <p><font size="3">You should <i>not</i> enable and disable after-image journaling at different times. To be effective, journaling must be used consistently. Leaving "holes" in your journal file defeats the purpose of this option. An .AIJ file should always be on a disk separate from all associated database files. If this disk is lost, you should restart the journaling and then immediately perform a full database backup. If the disk with the root file or storage area files is lost, you restore the lost files from backup and then use the .AIJ file to recover the most recent transactions. If either the root file and .AIJ files, or the storage area and .AIJ files were on the same disk, you will lose data if the disk failed.</font><font size="4"> </font></p> <p align="center"><font size="3"><b>NOTE:<br> After enabling after-image journaling, you should immediately perform a full database backup.<br> Failure to do so may result in a non-recoverable database.</b></font><font size="4"> </font></p> <p><font size="3">The database reference manuals contain additional information on backing up and recovering .AIJ files.</font><font size="4"> </font></p> <h2><font size="4">Always maintain a backup schedule and log </font></h2> <p><font size="3">A well-designed database backup and recovery strategy is useless if the strategy is not publicized and practiced. Operational personnel need to be aware of the importance of the backup methodology, and they need to be able to quickly access backup media in the event database recovery is necessary.</font><font size="4"> </font></p> <p><font size="3">Accurate record-keeping and media storage are essential to a proper backup and recovery strategy. The backup schedule should include dates and times, type of backup and tape labels.</font><font size="4"> </font></p> <h2><font size="4">Occasionally store backups in an offsite location </font></h2> <p><font size="3">Backups are performed to facilitate database restoration, if your primary media fails, once the problem is corrected. There are many possible sources of failure of your primary media, and some of these sources must be considered in your backup strategy. Also, always remember that the data in your database is much more valuable than the physical media it is stored on.</font><font size="4"> </font></p> <p><font size="3">If a site catastrophe destroys your disks, would your backup media still be safe? While it may be convenient, leaving your backup tapes on top of the tape drive is not a good idea. You should store you backup media in a separate location -- whether offsite or just another area of the building. Although offsite storage is the safest method, access to an offsite archive will take longer than one on-site, and there may be security issues about removing confidential data from your site. A compromise might be to send backups offsite only once a month. Traditionally, you send the previous month's or week's backup offsite, keeping the most recent backup in a safe location on-site.</font><font size="4"> </font></p> <p><font size="3">Magnetic tapes are an inexpensive, reliable, and reusable backup media. The environmental parameters for tapes are often broad and sometimes can lead to abuse. The audio tapes stored in your car can develop some hiss, and the video tapes in your home can drop out some colors, but if your backup tapes are damaged in this way, they are useless. You must follow the manufacturer's instructions on temperature, humidity, magnetic fields, and tape life span. A specially constructed tape library or archive is the safest storage strategy.</font> </p> <p align="center"><font size="2"><b>NOTE:</b></font> </p> <p><font size="2"><b>Tapes age and become less reliable over time. A good backup strategy precludes your ever needing to gamble on restoring a set of five-year-old tapes.</b></font> </p> <h2><font size="4">Do not use the VMS Backup Utility on a database</font> </h2> <p><font size="3">In the earliest versions of VAX DBMS, the VMS Backup Utility was a legitimate alternative to the database Backup and Restore Utilities. Today, there are many reasons <i>not</i> to use the VMS backup utility. For example:</font><font size="4"> </font></p> <ul> <li><font size="3">VMS backup does not consider database free space and wastes time and tape backing up unused space in the database.</font><font size="4"> </font></li> <li><font size="3">VMS backup does not understand the relationships between files. Databases spread across several devices would need special attention to ensure that the correct database root file, .AIJ, and storage area files are backed up safely and that they are synchronized.</font><font size="4"> </font></li> <li><font size="3">VMS backup does not provide the means for a database to be backed up safely when the database is in use. You would need to shut down your database during backups to ensure that no transactions occurred during the backup operation; this is clearly unacceptable for 7x24 applications.</font><font size="4"> </font></li> <li><font size="3">VMS backup may <i>lock</i> database files, thus preventing access to the database for large periods of time.</font><font size="4"> </font></li> </ul> <p align="center"><font size="3"><b>In the </b><b><i>earliest</i></b><b> versions of VAX DBMS, the VMS Backup Utility was a legitimate alternative to the database Backup and Restore Utilities.</b> </font></p> <p align="center"><font color="#000000"><font size="3"><b>Today, there are many reasons<br> not to use the VMS backup utility. </b></font></font></p> <p><font size="3">The multi-threaded database backup utility is designed specifically for VAX DBMS and Rdb/VMS databases. Digital recommends that you always use the multi-threaded backup capabilities of the product when backing up a database to tape because the utility:</font><font size="4"> </font></p> <ul> <li><font size="3">Operates faster than VMS Backup because it has been specifically optimized for databases.</font><font size="4"> </font></li> <li><font size="3">Uses less tape than VMS backup because it does not backup free space.</font><font size="4"> </font></li> <li><font size="3">Understands file dependencies and relationships.</font><font size="4"> </font></li> <li><font size="3">Can write to multiple tape devices simultaneously, reducing the total amount of backup time.</font><font size="4"> </font></li> <li><font size="3">Allows backups to be accomplished without shutting down the database.</font><font size="4"> </font></li> </ul> <p><font size="3">The ability of the database backup utility to write to multiple tape drives simultaneously depends on the degree of concurrency (i.e., the number of simultaneous writes) the number of drives, TCUs (controllers), HSC STI ports and how they have been cabled together.</font><font size="4"> </font></p> <p><font size="3">Simultaneous writing to multiple tape derives also depends on specific versions of VAX DBMS and Rdb/VMS. The following table identifies what capabilities exist in the various versions of the database products.</font><font size="4"> </font></p> <table border="2"> <tr> <td width="49"><font size="1">DBMS</font></td> <td width="36"><font size="1">Rdb</font> </td> <td width="136"><font size="1">Tape Drive Capability</font></td> </tr> <tr> <td width="49"><font size="1">V4.3</font></td> <td width="36"><font size="1">V4.1</font> </td> <td width="136"><font size="1">Will give you the option of overriding the auto-configuration done by the backup utility.</font> </td> </tr> <tr> <td width="49"><font size="1">V4.2</font></td> <td width="36"><font size="1">V4.0</font> </td> <td width="136"><font size="1">Will treat all drives that appear on the same HSC as <i>not</i> capable of simultaneous writes.</font> </td> </tr> <tr> <td width="49"><font size="1">V4.1</font></td> <td width="36"><font size="1">V3.1</font> </td> <td width="136"><font size="1">Does not explicitly support TA9x drives, although the drives may work to some degree</font> </td> </tr> </table> <p align="center"><b>Never use VMS file operations, such as COPY, RENAME and DELETE on database files.</b> </p> <p>Multi-threaded backup uses the same error recovery and group redundancy as the VMS Backup utility. Considering all factors in the database environment, it is significantly more reliable than VMS Backup. </p> <p>Never use VMS file operations such as COPY, RENAME and DELETE on database files. </p> <h2><font size="4">Always spread your database across several disks</font> </h2> <p><font size="3">A backup strategy should provide guidelines on backups and restores of your database. In addition, your overall database strategy should include safeguards for protecting your data from becoming corrupt and needing a restoration. While you cannot predict hardware failures, you can minimize their effects.</font><font size="4"> </font></p> <p><font size="3">By spreading your database across several disks, you can eliminate having a single point of failure. Even if your database is not large enough to require multiple disks, Digital strongly recommends putting the root file on a disk separate from the storage are disks, and you should <i>always</i> put the .AIJ files on a separate disk from the rest of the database. In addition, balancing busy storage areas between different disks is often a good tuning suggestion, but it also makes sense from a safety point of view.</font><font size="4"> </font></p> <p><font size="3">The VAX DBMS database reference manuals provide additional information on backing up individual storage areas of a database.</font><font size="4"> </font></p> <p><font size="3">Whether you backup you database to tapes or to disk, your backup schedule could overlap the routine VMS backups of your system (especially if your database files share their disks with other users.) Because VMS backups often run at a high priority, you should consider waiting until they are finished before starting a database backup. There will be less chance of mixing up tapes, less contention for the disks, and, if your system has multiple tape drives, you can allocate all of them to a multi-threaded database backup.</font><font size="4"> </font></p> <h2><font size="4">Always consider your existing hardware configuration and system workload </font></h2> <p><font size="3">The decision to backup your database either to tape or to disk will be determined primarily by your hardware environment and by personal preference.</font><font size="4"> </font></p> <p><font size="3">There are a few suggestions to consider:</font><font size="4"> </font></p> <ul> <li><font size="3">Rather than backup to disk and then to tape, it is often faster and more reliable to backup directly to tape.</font><font size="4"> </font></li> <li><font size="3">Multi-threaded backup to tape is usually faster than a single-threaded backup to disk. If you can allocate two tape drives, the backups complete in about half of the normal time. Using three drives completes the task in about a third of the time. In some configurations, the speed of multi-threaded backups is limited more by the VAX CI (Cluster Interconnect) port than by the speed of the tape drives.</font><font size="4"> </font></li> <li><font size="3">Historically, disk media is more reliable and faster than tapes. However, multi-threaded backup uses redundancy blocks which are more reliable than the single verify pass you get with a disk.</font><font size="4"> </font></li> <li><font size="3">Tapes are cheaper and easier to store than removable disk media. You can afford to keep more old backup versions around before you recycle the tapes. Try to maintain a minimum of three (3) versions of full database backups, since a single past version is not sufficient for a reliable backup strategy.</font><font size="4"> </font></li> <li><font size="3">If you backup to non-removable disk media, you can probably only keep one past set of backups, which is also insufficient for a reliable backup strategy.</font><font size="4"> </font></li> </ul> <h2><font size="4">Determine the volatility and value of your data </font></h2> <p><font size="3">The volatility of your database refers to how often data changes. If you can determine what data is changing, and how often, you can create a strategy based on backing up the most frequently changed storage areas more often. For example, the data in an inventory area could be updated several times an hour, an employee field could be updated at the end of every shift, and a list of suppliers might only change every few months. Determining and mapping this workload will help determine the needs of your site.</font><font size="4"> </font></p> <p><font size="3">The time required to complete incremental backups, and especially storage area incremental backups, is a good indication of changes to a database. Consider that a percentage of data in a database changes daily. Calculate this percentage and perform a full backup when 10% of a database has changed. While it is difficult to calculate this figure, it is much simpler to observe that when incremental backups take longer to complete each day, a greater percentage of a database has been changed since the last full backup. In fact, because of the extra calculations involved, an incremental backup could take longer than a full backup. A large incremental restore can take much longer than a full restore. Through observation, you should be able to determine the break-even point for performing a full backup rather than an incremental backup.</font><font size="4"> </font></p> <p align="center"><b>For backup to tape, the<br> multi-threaded backup is optimized to proceed at<br> whatever speed the hardware configuration will allow.<br> Allocating more tape drives<br> decreases backup times proportionally.</b> </p> <p><font size="3">In addition to the frequency of backups, a backup strategy should also consider the differing daily usage of a database. For example, a typical backup schedule might include incremental backups every night and a full backup on Friday night. However, if an examination of your usage shows that most changes are made on Wednesdays, they you should consider moving the full backup to Wednesday night. Similarly, if your database is accessed by users in multiple time zones, you might consider starting backups an hour or two later when the activity level has quieted down. Scheduling backups around user activities causes less impact on the users and results in more efficient backups.</font><font size="4"> </font></p> <h2><font size="4">Verify the integrity of your database before each full backup</font> </h2> <p><font size="3">A backup strategy is useless if the database being backed up is corrupt to begin with. VAX DBMS contains online utilities to verify the correctness of a database; these tools should be used prior to starting the database backup operation. No database backup operation should be allowed to proceed if the database has not been verified as consistent in all respects.</font><font size="4"> </font></p> <h2><font size="4">Verify the integrity of your backup media after each backup </font></h2> <p><font size="3">One of the most common mistakes made is not verifying the integrity of the database backup media; at least, not until the backup is needed to be restored and then it is too late.</font><font size="4"> </font></p> <p><font size="3">After a database is backed up, the backup media should be verified. Verification ensures:</font><font size="4"> </font></p> <ul> <li><font size="3">that the backup strategy works as intended.</font><font size="4"> </font></li> <li><font size="3">that the backup media is verifiable.</font><font size="4"> </font></li> <li><font size="3">that the backup media is restorable.</font><font size="4"> </font></li> </ul> <p><font size="3">There are several methods available to verify the integrity of the database backup media. However, only one method is guaranteed to accurately reflect the integrity of the backup media; The actual restoration or recovery of a database. Anything else is merely verifying the media, not the contents of the media.</font><font size="4"> </font></p> <p align="center"><font size="3"><b>NOTE</b></font><font size="4"> </font></p> <p><font size="3"><b>Never, ever, test the database restoration procedures by first deleting the source database. Restore or recover the database to a distinct file system, preferably on a separate machine.</b></font><font size="4"> </font></p> <h2>Speeding Up Backups</h2> <p><font size="3">For backup to tape, the multi-threaded backup is optimized to proceed at whatever speed the hardware configuration will allow. Allocating more tape drives decreases backup times proportionally. You can also save some operator time by formatting all of your tapes ahead of time.</font><font size="4"> </font></p> <p><font size="3">For backing up a database directly to disk, use the single-threaded backup utility, and set up RMS to have a large extend size. This reduces both fragmentation and the need to repeatedly expand the backup file. For example, RMS can allocate 50,000 blocks much faster than it can allocate 5000 blocks 100 times. This extend time becomes significant when compared to the amount of time for a simple write operation. You want to minimize the number of times the file extends while not setting it too large for your available disk space. If you can accurately calculate the final size of the resulting backup file, you can use the SET RMS_DEFAULT command in DCL to optimize the default extend size.</font><font size="4"> </font></p> <p><font size="3">For example, assume that a backup file is 1,005 blocks. You cannot directly specify the initial size of the file, but it is typically quite small (typically 3 blocks in most VMS systems). If you set the default extend to 1,000 blocks, the backup utility starts writing, gets to the end-of-file (after 3 blocks) and extends the file to 1,003 blocks. The backup utility again gets to the end-of-file and expands the 1,003 block file by 1,000 blocks, giving 2,003 blocks. When the database backup finishes, the file is truncated down to 1,005 blocks, but while the file is being written, 998 wasted blocks are allocated.</font><font size="4"> </font></p> <p><font size="3">It is sometimes possible to estimate the backup file size by analyzing your database to determine how full each storage area is. For example, use the space utilization summary pages generated by VAX DBMS DBO/ANALYZE/NOSET command.</font><font size="4"> </font></p> <p align="center"><font size="3"><b>NOTE</b></font><font size="4"> </font></p> <p><font size="3"><b>This method does not always provide a valid estimate. For example, consider what happens if you have a storage area with 100 pages, and there is one record on each page: Every page will be backed up. If you take that same area and put all 100 records on one page, only that one page will be backed up. The storage area is just as full in both cases.</b></font><font size="4"> </font></p> <p><font size="3">A simpler, and more reliable, method is to check the final size of the backup file the next time you perform a full database backup. If you set up your backups to run from a command procedure, be sure to consider future growth in your calculations.</font></p> <p align="center"><a href="#Contents"><font size="3">Contents</font></a></p> <hr> <p> </p> <p align="center"><font color="#000000"><font size="6"><b>Is Database Performance<br> Slowing You Down?</b></font></font> </p> <p align="center"><b>Software Concepts International technical staff can help you identify and eliminate database bottlenecks!</b> </p> <p align="center"><font size="5"><b>Haven't you </b><b><i>waited</i></b><b> long enough?<br> Call (603) 879-9022<br> and </b><b><i>stop</i></b><b> the </b><b><i>wait</i></b><b>!</b></font> </p> <hr> <p><a name="ReloadExpert"><font size="7"><b><i>ReloadExpert</i></b></font></a><b>™</b><font size="7"><b> v1.1</b></font> </p> <p><font size="4"><b>New England Software Concept's </b><b><i>ReloadExpert</i></b><b> version 1.1<br> offers many new features at a new lower price!</b></font> </p> <p><font size="3">SCI has enhanced its <i>ReloadExpert</i> optimizing reload script generator with the introduction of version 1.1. This new version offers enhanced functionality at a new lower price!</font><font size="4"> </font></p> <h3><font size="3">The Need for Reloads... </font></h3> <p><font size="3">Periodically reloading your VAX DBMS database is needed to maintain optimal database performance.</font><font size="4"> </font></p> <h3><font size="3">Productivity Tool </font></h3> <p><font size="3"><i>ReloadExpert</i></font><font size="4"> automates the reload process and ensures that your reload strategy is truly optimized and always up-to-date! </font><font size="3">Imagine creating a truly optimized VAX DBMS reload routine in a matter of minutes!</font><font size="4"> </font></p> <h3><font size="3">Minimize Time to Reload </font></h3> <p><font size="3">Reloading a production database can be a time-consuming process. <i>You cannot afford an inefficient reload strategy.</i></font><font size="4"> </font></p> <p><font size="3"><i>ReloadExpert</i> minimizes the time required to reload your database by producing truly optimized reload scripts and by making optimal use of system resources. <i>ReloadExpert</i> recommends optimal SYSGEN and UAF values for reloading your database based on <i>your</i> system's resources.</font> </p> <h3>Accurate Reload Scripts</h3> <p><font size="3">Your business depends on the data in your database. An erroneous reload script can seriously damage the integrity of your data. With <i>ReloadExpert</i> you can rest easy knowing that every set-type in your database has been carefully analyzed to ensure that the proper relationships will be maintained during the reload process.</font><font size="4"> </font></p> <h3>Automatic Restarts</h3> <p align="center"><font size="4"><b>Imagine creating a truly optimized VAX DBMS reload routine<br> in a matter of </b><b><i>minutes</i></b><b>!</b></font> </p> <p><font size="3">Should your system fail during an unload or load processes, <i>ReloadExpert</i> can <i>automatically restart</i> the unload or load at the point of failure, thus saving you from restarting the reload from the beginning!</font><font size="4"> </font></p> <h3><font size="3">Complete Set of Tools </font></h3> <p><font size="3"><i>ReloadExpert</i></font><font size="4"> generates everything you need to efficiently reload your database, including: </font></p> <p><font size="3">Load and Unload Format Language File (LFL/UFL); defines the record format of the unload files, and all set-significant information needed to maintain proper set relationships.</font><font size="4"> </font></p> <ul> <li><font size="3">Load Sequence Language files (LSL); defines the order in which record-types will be loaded into the database.</font><font size="4"> </font></li> <li><font size="3">Unload Sequence Language files (USL); defines the order and method by which your database will be unloaded. Moves the appropriate set-significant information to the output records to maintain proper set relationships during the reload.</font><font size="4"> </font></li> <li><font size="3">Reload Control file (CTL); defines the location and order in which the unload and load sequence files are executed.</font><font size="4"> </font></li> <li><font size="3">Optimized Reload Subschema (DDL); <i>ReloadExpert</i> even produces a subschema that is optimized for the reload process.</font><font size="4"> </font></li> <li><font size="3">Unload your source database with a single command!</font><font size="4"> </font></li> <li><font size="3">Load you target database with a single command!</font> </li> </ul> <h3>Tape Support</h3> <p><font size="3"><i>ReloadExpert</i></font><font size="4"> now supports the use of tape for reloading your database. You can unload portions of your database directly to tape, and then use the same tape during the load process.</font> </p> <h3>Parallel Unloads</h3> <p><font size="3">An optimized reload routine means less down-time. <i>ReloadExpert</i> allows you to unload portions of your database in parallel, thus reducing the overall reload time.</font><font size="4"> </font></p> <h3>Prolog and Epilog routines</h3> <p><font size="3">You can now write your own routines that <i>ReloadExpert</i> will execute immediately before and/or after each unload and load sequence to customize your reload. You can write routines to mount tapes, backup portions of your database, alter the placement of the unload RMS files, and many other uses!</font><font size="4"> </font></p> <h3>User Defined Keys</h3> <p><font size="3">Some database schema definitions do not guarantee unique keys for owner-record occurrences. <i>ReloadExpert</i> allows you to augment the schema definition by specifying User Defined Key (UDK) files that <i>ReloadExpert</i> uses to generate the reload scripts. In fact, <i>ReloadExpert</i> will even generate a template UDK file that can be easily edited to meet the unique needs of your database.</font> </p> <h3>DBO LOAD facility</h3> <p><font size="4"><i>ReloadExpert</i> generates scripts for the DBO/UNLOAD and DBO/LOAD facility.</font></p> <p><font size="3">This provides the following advantages:</font><font size="4"> </font></p> <ul> <li><font size="3">DIGITAL support of the underlying reload facility</font><font size="4"> </font></li> <li><font size="3">Enters the DBCS at a lower level than DML programs, thus is more efficient than traditional DML programs.</font><font size="4"> </font></li> </ul> <h3>Licensing</h3> <p><font size="3">The <i>ReloadExpert</i> distribution license is only $4,995. Additional nodes can be licensed with the right-to-copy license for only $2,495. These are one-time license fees, and are independent of node-size.</font><font size="4"> </font></p> <p><font size="2">*VAX DBMS is a trademark of <br> Digital Equipment Corp.</font><font size="4"> </font></p> <p><font size="2">*ReloadExpert is a trademark of<br> </font>Software Concepts International</p> <p> </p> <p align="center"><font color="#000000"><font size="2"><b>FOR MORE INFORMATION</b></font> </font></p> <p align="center"><font size="2"><b><i>New England<br> Software Concepts<br> </i></b></font><br> Nashua, NH 03063<br> (603) 879-9022 </p> <p align="center"><a href="#Contents"><font size="3">Contents</font></a></p> <hr> <p> </p> <div align="center"><center><table width="70%"> <tr> <td width="25%"><strong>Phone </strong></td> <td width="25%"><strong>USA Toll-free (Sales)</strong></td> <td width="25%"><strong>Fax</strong></td> <td width="25%"><strong>e-mail</strong></td> </tr> <tr> <td width="25%"><font size="2">(603) 879-9022</font></td> <td width="25%"><font size="2">1-888-CODASYL </font></td> <td width="25%"><font size="2">(603) 879-9023</font></td> </tr> </table> </center></div> <hr> <p align="center"><em>Copyright � 1996 Software Concepts International Inc. All rights reserved. </em></p> </body>