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

<body marginheight="0" marginwidth="0"> <p align="center"><font color="#000000"><font size="4"><img src="../../../images/advisortit.gif" align="bottom" width="532" height="114"></font></font></p> <p align="center"><font color="#000000"><font size="4">Issue #4</font></font></p> <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="#SQL Access to DBMS!">SQL Access to DBMS! </a></li> <li><a href="#Partitioning">Partitioning DatabasesWith <i>Dbinitialize</i> </a></li> <li><a href="#Hints">DBMS Hints, Kinks and Work-arounds</a></li> <li><a href="#DEC DBMS User Conference">DBMS User Conference </a></li> <li><a href="#INTOUCH">INTOUCH 4GL as a Data Integration Tool</a></li> <li><a href="#What's New in">What's New in DEC DBMS v6.0!</a> </li> <li><a href="#Locking">DBMS Locking, Part II </a></li> <li><a href="#Computer Associates Acquires The ASK Group">Computer Associates Acquires ASK Group </a></li> </ul> <p><a name="SQL Access to DBMS!"><font size="7">SQL Access to DBMS!</font></a></p> <p><font size="3">If you feel as if your mission-critical production data is trapped inside a proprietary data manager, then you haven't heard about the new SQL interface to DEC DBMS! Digital's new DB Gateway for DEC DBMS provides industry-standard SQL access to your DEC DBMS data. </font></p> <p><font size="3">In the past, customers who may have been best served by a CODASYL-style database management system for their application were swayed to a relational database, typically because of the availability of tools and familiarity with the relational programming environment. With the availability of the SQL interface to DEC DBMS, customers may now feel confident in choosing the <i>right</i> underlying database management system for their business needs, and not sacrifice their choice of 4GLS, query tools, and programming styles. </font></p> <h2><font size="4">Tools, tools, tools... </font></h2> <p><font size="3">The DEC DB Gateway for DEC DBMS allows much more flexibility than "just the ability to write high level programs with imbedded SQL statements." The gateway allows virtually any product that accesses data using SQL to access DEC DBMS. This provides DBMS sites with a wide choice of 4GLS, client/server front-end tools, query and programming tools.</font><font size="4"> </font></p> <p><font size="3">Many of the SQL tools available under MS-Windows or the Mac environments will allow you to access your DBMS data. Imagine accessing your DBMS data using Microsoft ACCESS, Visual BASIC or SQLWindows!</font><font size="4"> </font></p> <h2><font size="4">Read & Modify with SQL! </font></h2> <p><font size="3">DEC DB Gateway for DEC DBMS provides more than read-only access to your data. With the gateway product, you can modify existing data. However, the current implementation does not allow inserts (STORE) or updates to set membership (CONNECT, DISCONNECT or RECONNECT).</font><font size="4"> </font></p> <h2><font size="4">Version Requirements </font></h2> <p><font size="3">The SQL interface to DEC DBMS will access any V5.x or V6.0 databases. Special SQL images are provided on the V6.0 media-although a separate license is required to activate the SQL interface.</font><font size="4"> </font></p> <h2>Product Components</h2> <p><font size="3">There are three components to DEC DB Integrator Gateway for DBMS.</font><font size="4"> </font></p> <ul> <li><font size="3">The Non-SQL Data Server works with the metadata and data drivers to emulate relational access for non relational data sources (such as DEC DBMS). </font></li> <li><font size="3">The Metadata Driver contains the code specific to accessing DEC DBMS metadata. </font></li> <li><font size="3">The data driver contains the code specific to accessing DEC DBMS data.</font><font size="4"> </font></li> </ul> <p align="left"><font size="3">The data driver reads DEC DBMS data and provides it to the Non-SQL Data Server, where it is associated with DEC DBMS metadata retrieved by the metadata driver.</font><font size="4"> </font></p> <p align="center"><b>The gateway allows virtually any product that accesses data using SQL to access DEC DBMS... This provides DBMS sites with a wide choice of 4GLS, client/server front-end tools, query and programming tools.</b> </p> <p> </p> <h2><font size="4">SQL Attach </font></h2> <h2><font size="3">Before you can access data, you must <i>attach</i> (BIND) your process to the database using the SQL ATTACH statement.</font><font size="6"><strong> </strong></font></h2> <p><font size="2"><b>Figure-1: SQL Attach statement format</b></font> </p> <p><font size="2"><tt>SQL> ATTACH 'FILENAME/TYPE=NSDS -<br> cont> /PATH=root_spec.ssname -<br> cont> DICTIONARY_DRIVER=<br> cont> DBMSQL$SHR60'</tt></font> </p> <p><font size="3">There are three qualifiers to the ATTACH statement required for attaching to a DBMS database. The format of the ATTACH statement is outlined in Figure-1.</font><font size="4"> </font></p> <p><font size="3">You can also define the logical name 'SQL$DATABASE' for default attachments to the database. When the first SQL SELECT statement is executed, the database defined in the logical name 'SQL$DATABASE' is automatically attached.</font><font size="4"> </font></p> <h2><font size="4">Mapping DBMS to SQL... </font></h2> <p><font size="3">During the attach process, DEC DBMS sets, records, and data items, are mapped in memory to provide an emulated relational format. Understanding how this mapping occurs is important for navigating a DBMS database with SQL.</font><font size="4"> </font></p> <p><font size="3">DBMS <i>record-types</i> and <i>set-types</i> are mapped to SQL <i>tables</i>. Each DBMS record type is mapped to a relational table. A DBMS set is mapped into a relational table as the combination of the owner record and at least one member record. Forked sets are mapped into multiple tables - one for each owner/member combination in the forked set. Because of this, implicit ordering information within forked sets is lost in the SQL environment.</font><font size="4"> </font></p> <p><font size="3">Records that participate in system-owned, automatic, non-optional (fixed or mandatory) sets are not mapped to tables (the appropriate sets are mapped, thus providing access to this data).</font><font size="4"> </font></p> <p><font size="3">The SQL statement SHOW TABLES lists all sets and records as they are mapped to the relational format.</font><font size="4"> </font><font size="3">The SQL statement, SHOW TABLE <i>tablename</i> displays detailed information on a specific table relation.</font><font size="4"> </font></p> <h2><font size="4">Mapping Data Items... </font></h2> <p><font size="3">In mapping data items, the DEC DBMS data types are mapped to relational data types. DBMS supports many data types that are not supported by the SQL standard. Data types, as defined in a DBMS subschema, that are not supported in the relational environment are not visible with the SQL interface. Thus, it may be necessary to create new subschemas that support the required data types. Repeating items are also not supported in the relational environment. The SQL interface represents repeating items as individual elements.</font><font size="4"> </font></p> <h2><font size="4">DBKEYs as Foreign Keys </font></h2> <p><font size="3">In the relational model, relationships between tables are made based on a common "foreign key" - the "dept_no" field in the employee table matches the "dept_no" field in the department table. In the CODASYL model, such relationships are often defined as set-types in the schema, and the foreign keys are unnecessary. The CODASYL model uses "pointers" to establish the relationship.</font><font size="4"> </font></p> <p align="center"><b>The SQL interface provides<br> full relational access to<br> pointer-based set relationships through emulated "join keys" <br> - otherwise known as DBKEYS!</b> </p> <p><font size="3">Without he foreign keys, it may seem impossible to perform certain "join" operations on a DBMS database. However, the SQL interface also defines a "join key" for each table. The "join key" contains the dbkey (physical key) of each record in the table. For tables that represent DBMS sets, the "join key" is the dbkey of the <i>member</i> records.</font><font size="4"> </font></p> <p><font size="3">The SQL interface provides full relational access to pointer-based set relationships through emulated "join keys" - otherwise known as DBKEYS!</font><font size="4"> </font></p> <h2><font size="4">Summary </font></h2> <p><font size="3">Finally, you can have you the high-performance benefits of the CODASYL model...and be able to access your data too! You no longer need to choose between the "right data model" and one with ease of use. Who says you can't have you cake and eat it too?</font><font size="4"> </font></p> <p><font size="3">DBMS continues to be an excellent data model for many complex applications - such as manufacturing and financial. After years of requests by the DBMS user community, DBMS finally has the SQL interface we all wanted!</font><font size="4"> </font></p> <p align="center"> </p> <h2 align="center">DEC DBMS Gateway for DBMS SQL Examples...</h2> <p align="center"><font size="3">The following examples illustrate various queries of the sample 'PARTS' database.</font> </p> <h3>Find all parts supplied by Vendor 44466000:</h3> <p><font size="2"><tt>sql> select vend_id, vend_name,- <br> cont> part_id, part_desc -<br> cont> from vendor_supply a,-<br> cont> part_info_supply -<br> cont> where vend_id = '4446600' -<br> cont> and a.join_key = b.join_key;</tt></font> </p> <h3>Find all vendors that supply part AZ177311:</h3> <p><font size="2"><tt>sql> select part_id, part_desc,- <br> cont> vend_id, vend_name -<br> cont> from part_info_supply a,-<br> cont> vendor_supply b-<br> cont> where part_id = 'AZ177311' -<br> cont> and a.join_key = b.join_key;</tt></font> </p> <h3>Reduce the cost of all parts in class 'AZ' by 10 %:</h3> <p><font size="2"><tt>seq> update class_part -<br> cont> set part_price = part_price * 0.90 -<br> cont> where class_code = 'AZ'</tt></font> </p> <h3>List the vendor name of all parts each employee is responsible for, sorted by emp_id (a 3-way join):</h3> <p><font size="2"><tt>sql> select r.emp_id, ps.part_desc, -<br> cont> vs.sup_type, vs.vend_name, from -<br> cont> responsible_for r, -<br> cont> part_info_suppy ps, -<br> cont> vendor_supply vs -<br> cont> where r.part_id = ps.part_id -<br> cont> and ps.join_key = vs.join_key -<br> cont> order by r.emp_id.</tt></font> </p> <h3>For each employe, list the subcomponents of all parts that they are responsible for:</h3> <p><font size="2"><tt>sql> select r.emp_id, r_emp_last_name, -<br> cont> p.part_desc, p.comp_sub_part from -<br> cont> responsible_for r, part_uses p -<br> cont> where r.part_id = p_part_id -<br> cont> and r_part_id <> '' -<br> cont> and p.comp_sub_part <> '';</tt></font> </p> <p align="center"><a href="#Contents"><font color="#000000">Contents</font></a></p> <hr> <p align="center"> </p> <p align="center"> </p> <p align="center"> </p> <h1 align="center"><a name="Partitioning">Partitioning</a> Databases With <i>DBInitialize Expert</i> </h1> <p> </p> <p align="center"><font size="4"><b><i>DBInitialize</i></b><b> allows you to partition your database into manageable pieces.</b></font> </p> <p><font size="3">Very Large Databases (VLDBs) require special care. Traditional tools just don't work when dealing with tight schedules and large databases. The solution is to partition your VLDB with Software Concepts International<i> DBInitialize Expert</i> tool!</font><font size="4"> </font></p> <p align="center"><font color="#000000"><b>FOR MORE INFORMATION</b></font> </p> <p align="center"><b><i>Software Concepts Inernational<br> </i></b><br> Nashua, NH 03063<br> (603) 879-9022 </p> <h2><font size="4">What is <i>DBInitialize</i>?</font> </h2> <p><font size="3">DBInitialize is a software tool that allows you to partition your DEC DBMS database into manageable pieces. Need to reorganize the data in only a <i>few</i> areas, but can't afford the down time required to reorganize the <i>entire</i> database-no problem! Want to move a record type from one area to another? Again, no problem! Whether initializing storage areas, or record types, DBInitialize automatically "disconnects" all sets needed to maintain database consistency.</font><font size="4"> </font></p> <p><font size="3">If only one area of your database is in need of reorganization, you can unload this area and then quickly initialize the area-leaving the rest of your data intact! Now, reload <i>only</i> that area and you are back in business.</font><font size="4"> </font></p> <p>  </p> <h2><font size="4"><i>Incredibly Fast!</i></font> </h2> <p><font size="3">A MANMAN site reported that the MANMAN procedure to erase all records in the WOHAREA, RTGAREA and EMPAREA ran for over 27 hours! An additional 20 minutes were required to initialize the areas using DBO.</font><font size="4"> </font></p> <p><font size="3">Using DBInitialize, the entire process, <i>including</i> the DBO/INITIALIZE, took only 20 minutes! Using DBInitialize saved over 27 hours!</font><font size="4"> </font></p> <p><font size="3">At another site, it took 8-1/2 minutes to perform 10000 disconnects using a traditional COBOL/DML program. DBInitialize completed the same task in only 1/2 minute -- seventeen times faster than the DML approach.</font><font size="4"> </font></p> <p align="center"><b>If you are looking for a way to reorganize </b><b><i>portions</i></b><b> of your database but can't afford the downtime to reorganize the entire database, then DBInitialize was written for you!</b> </p> <p><font size="3">In another benchmark we disconnected 563176 members from two different <i>index</i> sets and 487481 members (1,050,657 total members!) from two <i>chain</i> sets in only 21 minutes.</font><font size="4"> </font></p> <h2><font size="4">How does DBInitialize do this? </font></h2> <p><font size="3">When performing storage area initializations, DBInitialize performs pointer cleanups in <i>parallel</i> with the DBO/INITIALIZE operations. In the above example, DBO/INITIALIZE took longer than the pointer-cleanup and thus DBInitialize had to wait for DBO to complete.</font><font size="4"> </font></p> <p><font size="3">When you are ready to reload your data, you can structure your reload procedures to run in parallel, thus further reducing downtime!</font> </p> <h2><font size="4">Flexibility </font></h2> <p><font size="3">Besides storage area initializations, you can also initialize (erase) all occurrences of specified record-types or initialize (disconnect) individual set-types.</font><font size="4"> </font></p> <p><font size="3"><b><i>Storage Area</i></b><b> </b>initialization is most useful when you need to reorganize the data in only one area, and can't afford to reorganize the entire database. In some environments, storage area initialization may be used an efficient way to purge obsolete data.</font><font size="4"> </font></p> <p><font size="3"><b><i>Record Type</i></b> initialization is a great way to move data from one area to another. Just unload the records from their original area, initialize only that record type and then reload the records and reconnect their owner/member relationships.</font><font size="4"> </font></p> <p><font size="3"><b>Set Types</b> initialization has proven itself useful when dealing with set corruption. Even when you can't walk a corrupted set, you can usually walk the area to find all members of a set and then find their owners to save the owner/member relationships to an external file. Using DBInitialize you can quickly remove the corrupted set pointers and index nodes (if any). It is then a relatively simple operation to connect the members to their respective owners and have your database back on line!</font><font size="4"> </font></p> <h2><font size="4">Disconnects FIXED and MANDATORY sets </font></h2> <p><font size="3">DBInitialize disconnects <i>all</i> sets required to maintain database consistency <i>regardless</i> of the set retention modes. Thus, even set types with either FIXED or MANDATORY retention modes are disconnected <i>without any schema changes!</i></font><font size="4"> </font></p> <h2><font size="4">Summary </font></h2> <p><font size="3">If you are looking for a way to reorganize portions of your database but can't afford the downtime to reorganize the entire database, then DBInitialize was written for you!</font><font size="4"> </font></p> <h2><font size="4">FREE 30 Day Demo!</font> </h2> <p><font size="3"><b>For a free 30 day evaluation of </b><b><i>DBInitialize Expert</i></b><b>, call Software Concepts International at (603) 879-9022. </b></font></p> <p align="center"><font color="#000000"><font size="6"><b><i>DBInitialize Expert - Triple Offer!!!</i></b></font> </font></p> <p align="center"><font size="5"><b><i>FREE Demo...</i></b></font> </p> <p>Just how <i>fast</i> is <i>DBInitialize</i>? Perform your own benchmarks on your database. Call Software Concepts International for a <b>FREE</b> 30 day on-site evaluation of DBIitialize<i>. Call (603) 879-9022 to arrange your free demo!</i> </p> <p align="center"><font size="5"><b><i>FREE Software...</i></b></font> </p> <p>Purchase a multi-node <i>DBInitialize</i> license before <i>December 1, 1994</i>, and SCI will write a <i>custom utility</i> to reorganize any storage-area of your choice for <b>FREE</b>!. Source code to custom utility included! Call for details! </p> <p align="center"><font size="5"><b><i>FREE Weekends...</i></b></font> </p> <p>Imagine reorganizing pieces of your database <i>during weeknights!</i> This <i>frees</i> you weekends for non-database activities! Don't spend this Thanksgiving with your database! Get <i>DBInitialize</i> and <i>free</i> your weekends!</p> <p align="center"><a href="#Contents"><font color="#000000">Contents</font></a></p> <hr> <p align="center">  </p> <h1 align="center">DBMS <a name="Hints">Hints</a>, Kinks and <font size="6">Work-arounds</font> </h1> <p align="center"> </p> <h2><font size="4">DBMS Versions... </font></h2> <p><font size="3">Digital has announced and is shipping DBMS v6.0. While v6.0 represents the most advanced version of DBMS, Digital's Database Systems Engineering group supports the following prior releases of DBMS:</font><font size="4"> </font></p> <ul> <li><font size="3">v4.3c</font><font size="4"> </font></li> <li><font size="3">v5.0a (not recommended)</font><font size="4"> </font></li> <li><font size="3">v5.1b</font><font size="4"> </font></li> <li><font size="3">v6.0</font><font size="4"> </font></li> </ul> <p><font size="3">There is really no excuse for being on a release of DBMS prior to v4.3. Versions 4.3 to v5.1 offer similar functionality, and SCI recommends v5.1 over v4.3. Version 6.0 is relatively new, and contains significant changes over v5.1.</font><font size="4"> </font></p> <p><font size="3">If you are experiencing problems, it is important to verify that you are on the current revision level (letter identifier) of the product. Revision level updates are often Mandatory Updates (MUPs), that contain fixes to improve availability or eliminate conditions that may result in data loss.</font><font size="4"> </font></p> <p align="center"><b>If you are experiencing problems, it is important to verify that you are on the current revision level (letter identifier) of the product. Revision level updates are often Mandatory Updates (MUPs),<br> that contain fixes to improve availability or eliminate conditions that may result in data loss.</b> </p> <h2><font size="4">Long Recovery Times </font></h2> <p><font size="3">Periodically we encounter long delays waiting for "freeze locks". We have seen delays lasting from a few minutes to over an hour! What are "freeze locks", and why are they locking us up for so long?</font><font size="4"> </font></p> <p><font size="3">Freeze Locks are part of the recovery mechanism used by DBMS to detect and recover for a failed process (a STOP/ID, CTRL-Y STOP, or a node failure in a multi-node environment).</font><font size="4"> </font></p> <p><font size="3">When a database process fails the DBMS MONITOR process must start a recovery process (DBR) to roll-back any uncommitted updates performed by the failed process. However, when a process fails in a VMS environment, it also releases all of its locks, including database locks. Without some sort of a control mechanism, this could result in a "free-for-all" for access to these updated (but uncommited) records.</font><font size="4"> </font></p> <p><font size="3">The "control mechanism" used by DBMS to prevent this "free-for-all" is to have the DBMS monitor request the "freeze lock". All other database processes are notified of the monitor's need for the "freeze lock" and they stop all database activity until the "freeze lock" is released by the monitor process.</font><font size="4"> </font></p> <p><font size="3">The monitor process creates a Database Recovery process (DBR) and holds the freeze lock until the recovery processes completely recovers (rolls back) the uncommitted transaction of the failed process. The <i>freeze lock</i> is held until the recovery process complete.</font><font size="4"> </font></p> <p><font size="3">In your environment, some processes have access to a large number of database buffers. The number of buffers that a process uses is determined by the DBO/CREATE (/MODIFY) qualifer "/BUFFERS" or the value of the logical name, DBM$BIND_BUFFERS (if defined). When this process failed, the database recovery process used only 20 buffers (the default) available. The default number of buffers used by the DBR process was insufficient to manage the large number of updates made by the failed process-and thus caused excessive I/O during recovery.</font><font size="4"> </font></p> <p><font size="3">To solve this problem, we modified your database to significantly increase the number of buffers available to the recovery process. This was done using the DBO/MODIFY /DBR_BUFFERS = <i>n</i> command.</font><font size="4"> </font></p> <p><font size="3">In general, the DBR process should have <i>more</i> buffers than the user that is is recovering for.</font> </p> <h3>Recovery With Fast Commits...</h3> <p>SCI clients have also experienced longer "freeze lock" delays when FAST COMMITS are enabled. This is a result of the transaction "redo" recovery needed when FAST COMMITS are implemented. If you are using FAST COMMITS and are experiencing excessive "freeze lock" delays, try reducing the checkpoint interval. </p> <p>It is also a good practice to minimize the need for recovery processes by avoiding the use of "idle process killers" or STOP/ID. To terminate a database image, use the FORCEX system service rather than the DELPRC. </p> <h2><font size="4">RUJ bug </font></h2> <p><font size="3">Under certain conditions, DBMS will bugcheck with a fatal error (DBR$RESOLVE). This error requires a full database restore and recovery from an earlier DBO/BACKUP and the associated After Image Journal (AIJ) file. This occurs when the DBMS monitor believes it must recover for a failed process, and there is an inconsistency between the contents of the RUJ block in the root file and the corresponding (if any) Run Unit Journal file.</font><font size="4"> </font></p> <p><font size="3">This problem may occur when a process is stopped (STOP/ID or CTRL-Y/STOP or DBO/CLOSE/ABORT=DELPRC) <i>after</i> it has bound to the database and <i>before</i> it has performed any DML verbs on that database <i>and</i> the contents of the RUJ block in the root file are uninitialized.</font><font size="4"> </font></p> <p><font size="3">This problem may be caused by RUJ blocks in the root file being left unitialized after issuing a DBO/MODIFY/USERS command to <i>increase</i> the maximum number of users for the database or when the Process Identification in the RUJ block in the root file contains the PID of an earlier process.</font><font size="4"> </font></p> <p><font size="3">Under existing versions of DBMS, the RUJ file is not created, and the contents of the RUJ block in the root file are not initialized until a MODIFY occurs. Until the RUJ block in the root header is initialized it may contain an older RUJ entry. When a process is deleted, DBMS attempts to recover from an older or invalid RUJ entry but is unable to do so because the Run Unit Journal file does not exist or the PID in the RUJ block and the existing Run Unit Journal file do not match.</font><font size="4"> </font></p> <p><font size="3">This problem should be fixed in DBMS v4.3c, v5.1a and 6.0.</font><font size="4"> </font></p> <p><font size="3">If you believe you have experienced this bug, search the DBRBUGCHK.DMP file for the string "*****". You may see something like the following:</font><font size="4"> </font></p> <p><font size="2"><tt>***** Exception at 00B4A568 : DBR$RESLOVE + 00000117</tt></font> </p> <p><font size="3">The "DBR$RESOLVE + 117" indicates that this is the problem you are experiencing.</font><font size="4"> </font></p> <p><font size="3">To recover, you will need to restore your database from an earlier backup, and recover the database using the After Image Journal.</font><font size="4"> </font></p> <p><font size="3"><b>Recommendations:</b></font><font size="4"> </font></p> <p><font size="3">Avoid the use of auto-logoff utilities and implement After Image Journaling on all DEC DBMS databases. The very small amount of overhead is well worth the additional level of protection. In addition, since version 4.3, certain optimizations can be used only if after image journaling is enabled-possibly enhancing overall performance <i>if</i> AIJs are enabled.</font><font size="4"> </font></p> <p><font size="3">It is also wise to force the RUJ blocks in the root file to be initialized by <i>simultaniously</i> binding (attaching) to the database multiple times (one bind per number of users allowed). To force the RUJ block to be initialized, each attach to the database must READY the database, FETCH and MODIFY the first record then ROLLBACK the transaction. Note: Multiple binds can occur within one process by using streams -- each stream represents a separate attach to the database.</font><font size="4"> </font></p> <p align="center"><font size="3"><b>While there are some benefits to omitting prior pointers, <br> </b><b><i>extreme</i></b> caution should be used when choosing this option. </font></p> <h2><font size="4">Prior Pointers Omitted...</font> </h2> <p><font size="3">We discovered the down side of omitting prior pointers at a client site running MANMAN.</font><font size="4"> </font></p> <p><font size="3">One of the Data Definition Language (DDL) SET options of the storage schema design is to OMIT PRIOR pointers. This reduces the size of the pointer clusters by storing only the next and owner pointer, and <i>may</i> reduce locking during set inserts (CONNECTs).</font><font size="4"> </font></p> <p><font size="3">While there are some benefits to omitting prior pointers, <i>extreme</i> caution should be used when choosing this option. When prior pointers are omitted, it is still possible to "FIND PRIOR" within the set. However, to do this, the DBCS must find the owner of the set and walk the set in the <i>next</i> direction until the <i>"prior"</i> record is found. If the "prior" record is the first record in the set, then the search completes quickly...if the "prior" record is the last record in the set, then the length of the search depends on the number of records in the set.</font><font size="4"> </font></p> <p><font size="3">FIND/FETCH PRIOR is not the only verb which is negatively impacted when prior pointers are omitted. When <i>removing </i>records from a set (DISCONNECT), the DBCS must search for the <i>prior</i> record in the set to modify its <i>next</i> pointer within the set-type being disconnected. Again, to find the <i>prior</i> record, the DBCS must find the owner record and then sequentially walk the set in the <i>next</i> direction until the <i>prior</i> record is found.</font><font size="4"> </font></p> <p><font size="3">An even less obvious problem can occur when prior pointers are omitted from CALC sets. As it turns out, in versions of DBMS <i>before</i> v5.1, CALC sets were searched in the <i>prior</i> direction-with a bad twist-the actual search process worked as follows:</font><font size="4"> </font></p> <ol> <li><font size="3">Find the <i>prior</i> record.</font><font size="4"> <br> </font><font size="3">Find the owner</font><font size="4"> <br> </font><font size="3">Find next until the "prior" record is found.</font><font size="4"> </font></li> <li><font size="3">Check if the logical key matches-if not, go to step 1.</font><font size="4"> </font></li> </ol> <p><font size="3">Using this algorithm, if a CALC set contains 500 members, it would take 500 "searches" to find the 500th member, but 130300 to find the first!</font><font size="4"> </font></p> <p><font size="3">In a healthy CALC (hashed) set environment (one with many SYSTEM owners distributed throughout the area and few members per owner), the impact of this would be minimal.</font><font size="4"> </font></p> <p><font size="3">However, at one of our client sites running ASK MANMAN, their database creation routine created the database area with an initial allocation of 1 and then extended the area to its full size. This left the calc range for that area at 1, forcing all members of CALC sets within this area to share the same SYSTEM owner. Even though the area was small enough to cache all 5000 records into database buffers (memory), a job to perform only a few hundred updates ran for over 24 hours-using over 24 hours of CPU time! To solve their immediate problem, we performed a DBO/MODIFY/RECALC on this one area, and the job completed in only a few minutes. To solve their long-term problem, we fixed their creation routine and modified their storage schema so that this set would contain prior pointers and then reorganized their database.</font><font size="4"> </font></p> <p><font size="3">Again, this problem has been fixed in version 5.1.</font><font size="4"> </font></p> <h2><font size="4">SPAM Thresholds... </font></h2> <p><font size="3">We have a very heavily used MANMAN database, and would like to set the database Space Area Management (SPAM) thresholds optimally. Should thresholds be adjusted monthly or should we just stick to the default threshold values, and do they really make much difference?</font><font size="4"> </font></p> <p><font size="3">Default SPAM thresholds are <i>definately not</i> optimal. By understanding how DBMS searches for free space when storing new records, you can prove mathematically that any threshold over 89% does not provide any benefit. Since the default thresholds are 70, 85 and 95% there is definately room for improvement! Also, any utility or tool that recommends SPAM thresholds above 89% should be looked upon with suspicion.</font><font size="4"> </font></p> <p align="center"><b>Any utility or "tool kit" that recommends SPAM thresholds above 89% should be looked<br> upon with suspicion.</b> </p> <p><font size="3">The free space search algorithm used by DBMS takes into consideration <i>only static</i> data structures. Therefore, there is no need to calculate thresholds on a monthly basis. The actual or average record length, as reported in the DBO/ANALYZE report has no bearing on optimal threshold values, and should not be used for setting SPAM thresholds. The only data structures that DBMS considers when looking for free space are:</font><font size="4"> </font></p> <ul> <li><font size="3">MAX_FREE_LEN - based on page size and whether SNAPSHOTS are allowed.</font><font size="4"> </font></li> <li><font size="3">PAGE_PAD_LEN - based on page size.</font><font size="4"> </font></li> <li><font size="3">STATIC_RECORD_LEN-the sum of the lengths of the <i>static</i> data items as defined in the storage schema.</font><font size="4"> </font></li> </ul> <p><font size="3">Optimal threshold values should be set when the database is created, and modified only when there is a change to the storage schema or the page size of a storage area. Anyone that recommends setting SPAM thresholds more frequently lacks full understanding of SPAMs!</font><font size="4"> </font></p> <p><font size="3">Space Management (SPAM) technology was designed to improve the <i>storage</i> performance of <i>nearly full</i> storage areas and areas where large ranges of full pages. The overall impact of SPAM thresholds on database performance depends on the rate in which new records are stored into the database, and the fullness of the storage area in which records are being stored.</font><font size="4"> </font></p> <p><font size="3">An area which contains a lookup table (where records are seldom added once loaded) will probably not benefit much from thresholds.</font><font size="4"> </font></p> <p><font size="3">Likewise, an area with plenty of free space generally will not benefit much from thresholds. If you store a record in an empty storage area, it is bound to find a page with sufficient free space. Before even considering SPAM thresholds, DBMS will store a new record on <i>any</i> page in the target buffer that contains sufficient free space-as long is it is not at the T3 threshold. Assume that you page size is 2 blocks per page and your buffer length is 20. DBMS will scan 10 pages (all pages in the target buffer) before using the SPAMs!</font><font size="4"> </font></p> <p><font size="3">Optimal SPAM thresholds have a dramatic impact on heavily updated storage areas that have large ranges of full or nearly-full pages.</font> </p> <p align="center"> </p> <p align="center"><font color="#000000"><font size="4"><b>OPTIMAL SPAM THRESHOLDS</b></font></font> <br> <font size="4"><b>are just a phone call away!</b></font> </p> <p align="center"><font size="4"><b>(603) 879-9022</b></font> </p> <h2><font size="4">DB Access methods </font></h2> <p><font size="3">We converted our application from another network (CODASYL) database running on another platform. Our database is relatively small, yet we are experiencing some performance problems.</font><font size="4"> </font></p> <p><font size="3">After reviewing your environment, there are two primary causes of your performance bottlenecks:</font><font size="4"> </font></p> <p><font size="3">1. The database needs to be reorganized to achieve optimal placment of records which were stored in extended areas. This includes determining the optimal page sizes, allocations and thresholds for each storage area.</font><font size="4"> </font></p> <p><font size="3">2. It appears that the platform from which you converted either did not provide for keyed access to member of user-owned sets or the application did not take advantage of this feature. The following describes some of the enhancements you may want to consider.</font><font size="4"> </font></p> <p><font size="3">Except for CALC and SYSTEM owned indexed sets, your application always performs sequential walks of any user-owned set, performing your own test of the record selection criteria. That is, after each "find next <record> within <set>" your application performs its own test of the selection criteria.</font><font size="4"> </font></p> <p><font size="3">As a first step, you will want to modify your DML programs to include your record selection criteria within the DML verbs.</font><font size="4"> </font></p> <p><font size="3">Example:</font><font size="4"> </font></p> <p><font size="3"><tt>PERFORM UNTIL FOUND OR END<br> FIND NEXT <record><br> WITHIN <set><br> IF PART_ID = WS_PART_ID<br> SET FOUND TRUE<br> END IF<br> END PERFORM</tt></font><font size="4"> </font></p> <p><font size="3">becomes:</font><font size="4"> </font></p> <p><font size="3"><tt>FIND NEXT <record><br> WITHIN <set><br> </tt><b><tt>WHERE PART_ID<br> = WS_PART_ID</tt></b></font><font size="4"> </font></p> <p align="center"> </p> <p align="center"><b>The DBO/ANALYZE report does not report space used by line indices, so it is possible for ANALYZE to show 100% space available,<br> but for the area to completely full!</b> </p> <p><font size="3">This will simplify your code, as well as allow you to easily identify the primary access paths into your data. As part of a later exercise, you can modify your database design to provide keyed access to these primary access paths.</font><font size="4"> </font></p> <p><font size="3">Although data sharing is not a significant requirement at this point in your application, this would be a good opportunity to add RETAINING (currency indicator...) qualifiers to the DML statements in your programs. This will help avoid future locking problems, and will also provides a modest performance boost.</font><font size="4"> </font></p> <h2><font size="4">Global Buffer Memory Leaks </font></h2> <p><font size="3">Global buffers were introduced in version 4.3 to reduce I/O by allowing multiple processes to share data in-memory. Global buffers are allocated out of system global pages by the DBMS monitor process when a database is opened (either by the DBO/OPEN command or by the first process that attaches to the database). When the database is closed, the monitor process should unmap the global pages it had allocated during the open.</font><font size="4"> </font></p> <p><font size="3">Under some circumstances, the DBMS monitor process would fail to release all global pages it had previously allocated. This resulted in a "memory leak" which could ultimately cause the database monitor to consume all available global pages. Once this happens, it would be necessary to shut down the DBMS monitor in order to free the global pages and resume database operations.</font><font size="4"> </font></p> <p><font size="3">This problem has been fixed in DBMS 4.3a, 5.0, and 5.1a.</font><font size="4"> </font></p> <h2><font size="4">DBKEY Scoping problem </font></h2> <p><font size="3">A problem was introduced in v4.3 where DBKEYs (line numbers on database storage area pages) were not being reused as efficiently as possible. This resulted in the space allocated for the line index (not the space used by your data) to be lost. Ideally, the DBCS would allow a DBKEY to be reused after the process which deleted the line (record) has committed its transaction and <i>unbound</i> from the database. While in most environments, the optimal time to allow DBKEY reuse would be after the commit, there are some valid reasons for waiting until the deleting process unbinds from the database.</font><font size="4"> </font></p> <p><font size="3">The problem introduced in v4.3, caused DBKEYs to be reused only when a single process was attached to the database. Thus, at most sites, DBKEYs would never be reused.</font><font size="4"> </font></p> <p><font size="3">For <i>each</i> record that was deleted, eight (8) bytes would be lost if the storage area has snapshots, or four (4) bytes if snapshots were not allowed for that area. Unfortunately, the DBO/ANALYZE report does not report space used by line indices, so it is possible for ANALYZE to show 100% space available, but for the area to completely full!</font><font size="4"> </font></p> <p><font size="3">To determine if your database is suffering from excessive unused dbkeys, you must use DBO/DUMP to dump out the storage area pages. If the pages contain large numbers of "empty" lines (locked by) then this is probably a result of this problem. In severe cases, it is not uncommon to see relatively small pages contain a 80 or more "empty" lines-larger pages may contain even more empty lines.</font><font size="4"> </font></p> <p><font size="3">Recovering the space lost to these dbkeys may be difficult. The excess dbkeys at the end of the page can be freed by using DBO/RESTORE to restore the area, while recovering dbkeys in the "middle" of the page will require an unload/load of the affected storage areas.</font><font size="4"> </font></p> <p><font size="3">While this problem was fixed in v4.3b and v5.0a and all versions since, your database may still be suffering from the ill-effects of this problem. </font></p> <p align="center"><font color="#000000"><font size="2"><b>Send your DEC DBMS<br> related Questions to:</b></font></font> </p> <p align="center"><font size="2"><b>DBAdvisor<br> c/o Software Concepts International<br> </b></font><br> Nashua, NH 03063, USA<br> phone: (603) 879-9022 </p> <p align="center"><font size="1">Answers will be published in<br> <i>DEC DBMS Hints, Kinks and Workarounds</i> in the <i>next</i> issue of the DBAdvisor.</font> </p> <p align="center"><a href="#Contents"><font color="#000000">Contents</font></a></p> <hr> <p align="center">  </p> <h1 align="center"><a name="DEC DBMS User Conference">DEC DBMS User Conference</a> </h1> <p align="center"> </p> <p align="center"><font color="#000000"><b>FOR MORE INFORMATION</b> </font></p> <p align="center"><font size="2"><b>Talley Management Group, Inc.<br> </b></font>Suite 200<br> 875 Kings Hwy.<br> Woodbury, NJ 08096<br> (800) 842-7220<br> FAX 1 (609) 845-7258</p> <p><font size="3">Of special interest to DEC DBMS Users is the <i>DATABASE INFORMATION MANAGEMENT CONFERENCE,</i> October 10, 11, 12, 1994 at the Sheraton Washington Hotel, Washington, D.C. The event is "the Information Summit for Digital Equipment Corporation's Database Developers and Users."</font><font size="4"> </font></p> <p><font size="3">The event will feature sessions for DBMS users. Learn first-hand Digital's product strategy. Explore ideas with your fellow IT professionals, and meet industry experts who will give you support in the development and maintenance of your database strategies.</font><font size="4"> </font></p> <p><font size="3">In addition to DEC DBMS specific sessions, the <i>Database Information Management Conference</i> will also include tracks and seminars for Rdb Users, Digital's database systems Engineers, and StoreageWorks Technologies. Keynote speakers from within and outside of Digital will present key points of view.</font><font size="4"> </font></p> <p><font size="3">Strategic IT database management sessions and Technology demonstrations will be central to the conference information gathering processes.</font><font size="4"> </font></p> <p align="center"><b>The DATABASE INFORMATION MANAGEMENT CONFERENCE promises to be the most complete opportunity to evaluate, and strategically position your use of Digital's Database products.</b> </p> <p><font size="3">The <i>DATABASE INFORMATION MANAGEMENT CONFERENCE</i> promises to be the most complete opportunity to evaluate, and strategically position your use of Digital Database products. The event is one of the most timely "Call to Meetings" in many years.</font><font size="4"> </font></p> <p><font size="3"><b><i>Plan to attend "the Summit for Digital's Database Products."</i></b></font><font size="4"> </font></p> <p><font size="3">Those interested in submitting a sessions or acquiring more information please Fax to:</font><font size="4"> </font></p> <p><font size="3"><b>Database Information Management Conference<br> </b>c/o Talley Management Group Inc.<br> Suite 200<br> 875 Kings Hwy.<br> Woodbury, NJ 08096<br> [FAX 1-609-845-7258].</font><font size="4"> </font></p> <h2><font size="4">Submission Guidelines: </font></h2> <p><font size="2"><b>Deadline August 26, 1994</b></font> </p> <ul> <li><font size="2">Please submit a 150 word synopsis of your topic, a session title and bullet points describing your presentation</font> </li> <li><font size="2">Include a brief biography with your submission.</font> </li> <li><font size="2">List any prior speaking engagements and your topics.</font> </li> <li><font size="2">Case study formats are preferred</font> </li> </ul> <p align="center"><a href="#Contents"><font color="#000000">Contents</font></a></p> <hr> <p align="center">  </p> <h1 align="center"><a name="INTOUCH">INTOUCH</a> 4GL as a Data Integration Tool</h1> <p align="center"><font size="2"><b>by Dan Swain<br> Senior Software Engineer, Touch Technologies, Inc.</b></font></p> <p align="center"> </p> <p align="center"><font color="#000000"><font size="2"><b>FOR MORE INFORMATION</b></font><b> </b></font><br> <b>Touch Technologies, Inc. <br> </b>9988 Hibert Street<br> Suite 310<br> San Diego, CA 92131<br> 1 (800) 525-2527 or<br> 1 (619) 566-3603<br> FAX: 1 (619) 566-3663 </p> <p><font size="3">Imagine an environment where your manufacturing data is in an ASK MANMAN DEC DBMS database, your Decision Support System is in a DEC Rdb database, your sales department has developed a PC based sales quoting application using dBASE while your engineering department manages its data in both an Oracle database and several RMS files.</font><font size="4"> </font></p> <p><font size="3">Now, imagine that you've just been given the ominous task of integrating all of this data-while your training budget has been slashed! Sound impossible?</font><font size="4"> </font></p> <p><font size="3">INTOUCH, a fourth generation language noted for its high performance features provides <i>full</i> read and <i>write</i> access to the following data sources:</font><font size="4"> </font></p> <ul> <li><font size="3">DEC DBMS</font><font size="4"> </font></li> <li><font size="3">DEC Rdb</font><font size="4"> </font></li> <li><font size="3">RMS</font><font size="4"> </font></li> <li><font size="3">Userbase</font><font size="4"> </font></li> <li><font size="3">VMS Mailboxes</font><font size="4"> </font></li> <li><font size="3">Oracle</font><font size="4"> </font></li> <li><font size="3">dBASEIII+</font><font size="4"> </font></li> <li><font size="3">CDD (read only)</font></li> </ul> <p><font size="4"></font></p> <p align="center"><b>INTOUCH provides full<br> </b><b><i>multi-streamed</i></b> access to<br> DEC DBMS, thus allowing you to access multiple DEC DBMS databases simultaneously. </p> <p> </p> <h2><font size="4">Consistent Syntax </font></h2> <p><font size="3">INTOUCH accesses all data sources through a concept called <i>structures</i>. The syntax for accessing <i>structures</i> is very consistent across all database engines. INTOUCH extentions to the standard structure syntax allows complete access to the full set of features that each database engine supports.</font><font size="4"> </font></p> <h2><font size="4">Read/Write Access to DBMS</font> </h2> <p><font size="3">INTOUCH provides complete <i>read and write</i> access to all supported database engines-including DEC DBMS. This means that it is easy to write <i>complete</i> applications that access DEC DBMS databases in INTOUCH...or you can develop enhanced interfaces to the data in an existing application, such as MANMAN, COMETS or MACPAC using INTOUCH.</font><font size="4"> </font></p> <h2><font size="4">CDD not required </font></h2> <p><font size="3">Sites with complex schema definitions will appreciate the fact that INTOUCH <i>does not require the use of the CDD</i> for compiling database metadata. INTOUCH understands DBMS data structures, and automatically obtains definitions about records and sets directly from the database - INTOUCH does not require the use of the CDD. Thus, INTOUCH compilations are significantly faster than languages that require the use of the CDD.</font><font size="4"> </font></p> <p><font size="3">Since INTOUCH accesses the metadata definitions <i>directly from the database</i> there is never a chance of a sub-schema mismatch. In fact, INTOUCH even knows when the subschema changes, and will dynamically relink to the new subschema definition at run-time!</font><font size="4"> </font></p> <h2><font size="4">Multi-stream access</font> </h2> <p><font size="3">While integrating data from disparate data sources is a requirement at many sites, integrating data from a common database engine may also be a requirement. Complex applications such as ASK MANMAN store data in multiple DEC DBMS databases, requiring simultanious access to several DEC DBMS databases. To access multiple DEC DBMS databases simultaneously, each attach to a database occurs through a separate <i>stream</i>. INTOUCH provides full multi-streamed access to DEC DBMS, thus allowing you to access multiple DEC DBMS databases simultaneously.</font><font size="4"> </font></p> <p> </p> <h2><font size="4">Minimizing Locking (even during updates). </font></h2> <p align="center"><b>Non-technical users can easily create reports using GUIDE's menu driven process,<br> while more experienced users can quickly generate reports from GUIDE's command line.</b> </p> <p><font size="3">Developing applications that minimize locking in a report-only environment is simple-just ready your database for BATCH RETRIEVAL. However this approach requires that your database have SNAPSHOTS enabled, and <i>requires</i> updaters to write to the snapshot files as well as the live database storage area files. In addition, your report programs will now have to read both the live pages and the snapshot files to access records that have been updated by other users on your system. This <i>"simple"</i> approach to minimizing locking in a read-only DEC DBMS environment causes additional overhead that may degrade the overall performance of your entire application-and is <i>not</i> the approach used by INTOUCH.</font><font size="4"> </font></p> <p><font size="3">The optimal approach to minimizing record locking in a DEC DBMS environment (during <i>either</i> read or write transactions) is to carefully manage your programs <i>currency indicators</i>. While currency indicators allow efficient access to a DEC DBMS database, they also place <i>read-only</i> locks on the records they point to. To make application development easier, DEC DBMS automatically <i>updates all currency indicators</i> (whether they are needed or not) whenever a record is accessed (placing a read-only lock on the record). These currency indicators (locks) are maintained on this record until the end of the transaction, or until they happen to move to another record. By carefully managing currency indicators, you can significantly reduce record locking in both read and write transactions.</font><font size="4"> </font></p> <p><font size="3">If managing currency indicators properly sounds difficult, you are right-unless you are using INTOUCH. INTOUCH updates <i>only</i> those currency indicators <i>required for efficient database access</i>-and releases (unlocks) them as soon as they are no longer needed! This happens <i>automatically</i> in INTOUCH, without the programmer having to know anything about how, why, or when to use currency indicators. Applications written in INTOUCH incur significantly less locking than those written in other languages.</font><font size="4"> </font></p> <h2><font size="4">GUIDE to Report Writing </font></h2> <p><font size="3">For simple creation of complex reports, INTOUCH provides GUIDE (Guided Query Language). Non-technical users can easily create reports using GUIDE's menu driven process, while more experienced users can quickly generate reports from GUIDE's command line.</font><font size="4"> </font></p> <p><font size="3">GUIDE produces a complete INTOUCH program from the commands that you enter. This INTOUCH program can be saved and run separately from GUIDE. This unique feature gives you the flexibility to use a report writer to easily create reports, yet gives you the full performance of the INTOUCH language. Use GUIDE to mock up and test your report and then compile the resulting INTOUCH program for use in your production system.</font><font size="4"> </font></p> <p align="center"><a href="#Contents"><font color="#000000">Contents</font></a></p> <hr> <p> </p> <p align="center"> </p> <p align="center"> </p> <h1 align="center"><a name="What's New in">What's New in<br> DEC DBMS Version 6.0 </a></h1> <p><font size="3">DEC DBMS 6.0 offers significantly greater performance, easier management and more features. In this article, we will review many of the new features in 6.0 and discuss how you can benefit from these enhancements. DBMS 6.0 is more than just an incremental enhancement over v5.1.</font><font size="4"> </font></p> <h2><font size="4">I/O Enhancements </font></h2> <p><font size="3">In a database environment, I/O activity causes most bottlenecks. While disk subsystems are seeing performance improvements of approximately 10% per year, CPU performance has been doubling about every 1-1/2 years. Without significant enhancements to database I/O subsystems, performance of database applications is limited to the performance improvements of the disk system.</font><font size="4"> </font></p> <p align="center"><b>DEC DBMS 6.0 offers significantly greater performance, easier management and more features than any previous release.</b> </p> <p><font size="3">Digital's Database Engineering Group has made significant improvements to the I/O subsystem of DBMS 6.0 to minimize the stall time of database processes. In many cases, DBMS <i>continues</i> processing while asynchronous I/Os complete-minimizing I/O stall time.</font><font size="4"> </font></p> <h3>Asynchronous Batch Writes</h3> <p>Disk I/Os are <i>slow</i> and can cause database processes to stall while they wait for I/O operations to complete. One approach to minimizing I/O stall time is to reduce I/Os-this is accomplished in DBMS using local or global buffers, fast commits and the new AIJ Logger Server (ALS). Another approach to minimizing I/O stall time is to continue processing while previous I/Os complete asynchronously. </p> <p>Before version 4.3 of DBMS, <i>each</i> modified page was written to storage area files synchronously (one at a time). In version 4.3 of DBMS, <i>all</i> modified pages were sorted by page number and written back to the database synchronously. This allowed the batched writes to be optimized by I/O subsystems and for I/Os to occur in parallel on multiple devices. However, even with this implementation, database processes waited for <i>all</i> writes to complete. Thus, a database process would <i>stall</i> until the I/Os completed, regardless of whether it needed any of the pages for further activity. </p> <p>The new Asynchronous Batch Write (ABW) feature of DBMS 6.0 eliminates this bottleneck by allowing the database process to continue <i>unless</i> events require "catching up" with the write in progress for a page. Thus in many environments, the database server process will continue processing additional data <i>without</i> waiting for prior writes to complete. </p> <p>In version 6.0, ABW is enabled by default. It can be disabled by defining a logical name, but most sites will want to leave it enabled as they are likely to benefit from its use. It is also possible to tune the behavior of ABW with additional logical names. </p> <p>Most DBMS utilities that write to the database (such as recovery and load) will also see performance improvements as a result of this enhancement. </p> <h3>Asynchronous Pre-Fetch</h3> <p>While Asynchronous Batch Writes minimize the need to wait for database writes, Asynchronous Pre-Fetch (APF) attempts to predict what page(s) will be needed in the future and reads them into your buffer pool <i>before</i> you need them. If successful in this predictive behavior, then APF eliminates read stall time. </p> <p>APF is most likely to benefit applications that perform substantial sequential processing of database areas. Many batch jobs that process all of a given record type come to mind. </p> <h3>Optimized Page Transfers</h3> <p>Once a page has been read into memory, we are faced with how to avoid duplicate reads, and how to prevent forced writes to disk. A forced write occurs when a modified page in the buffer pool is "forced" to be written back to disk so that the current version of that page may be read by another process. </p> <p><font size="3">Global buffers answer the problem of duplicate reads-if one process reads a page into the global buffer pool, other processes on the same node can access the page without incurring an I/O.</font><font size="4"> </font></p> <p><font size="3">Before version 6.0, a "forced write" occurred when a modified page in the buffer pool was requested by another process. With Optimized Page Transfers (OPT), multiple processes on a single node can share <i>and update</i> pages <i>without</i> flushing the pages to disk. Thus, pages in the <i>global</i> buffer pool can contain committed updates from another process-without these updates being reflected on disk.</font> </p> <p align="center"> </p> <p align="center"><b>With Optimized Page Transfers (OPT), multiple processes on a single node can share </b><b><i>and update</i></b><b> pages </b><b><i>without</i></b><b> flushing the pages to disk.</b> </p> <p><font size="3">In version 6.0, Optimized Page Transfers are automatically enabled if <i>all</i> the following conditions are met:</font><font size="4"> </font></p> <ul> <li><font size="3">Global buffers are enabled</font><font size="4"> </font></li> <li><font size="3">AIJs are enabled</font><font size="4"> </font></li> <li><font size="3">Fast commit is enabled</font><font size="4"> </font></li> <li><font size="3">Max cluster nodes = one (1)</font><font size="4"> </font></li> </ul> <p><font size="3">Optimized Page Transfers can significantly enhance the performance of update intensive multi-user applications where multiple processes share the same data (or data that is on the same page).</font> </p> <h3>Main Memory DBs</h3> <p>"Main Memory Database" support is not itself a "feature" of version 6.0. However, the combination of other features such as Global Buffers, Optimized Page Transfers, Fast Commits, AIJ Log Server and hardware considerations (such as increases in memory support, reduction in memory prices and rapid growth in CPU power) make main-memory databases a possibility in some environments. With OPT, and sufficient memory, it is conceivable that your entire database could be cached in main memory with the only I/O being to the AIJ and periodic writes to the storage area files during checkpointing. </p> <h2><font size="4">Enhanced After-Image Journaling (AIJ) </font></h2> <p><font size="3">Many of the I/O enhancements to DBMS defer writes to the database storage area files-with the updates journaled to the After Image Journal (AIJ) file. This allows applications to achieve higher throughput (usually measured in Transactions Per Second (TPS)). The higher TPS rates now possible place a greater burden on AIJ processing, and in some sites this may become the bottleneck.</font><font size="4"> </font></p> <p><font size="3">Significant enhancements have been made in 6.0 to improve the performance and management of after image journals.</font><font size="4"> </font></p> <h3>Circular AIJ Journaling</h3> <p>In prior versions, a database could have at most one AIJ file. When the AIJ file became full, it would extend. If the AIJ device is full, the extend fails and database activity is halted. While DBMS 6.0 still supports single "extensible" AIJs (the default), most sites will benefit from the features of circular AIJs. </p> <p>With Circular AIJ Journaling, a database has multiple, fixed-length AIJ files. While DBMS writes to only one AIJ file at a time, it will automatically switch to the next accessible AIJ file when one AIJ file becomes full or inaccessible. By spreading the circular journal files across multiple disks you benefit from automatic fail over if the "current" AIJ disk becomes unavailable. </p> <p>To use circular AIJs, you must first reserve slots in the root file for AIJ journal information - an off-line activity. This can be done either during database creation or at a later point using DBO/MODIFY. Once you reserve the AIJ slots, you can add or remove AIJ journal files to the database on-line - without interrupting database activity. </p> <p>AIJ backups also are significantly faster with circular AIJs, since you backup AIJ files that are not currently in use. </p> <h3>AIJ Logging Server</h3> <p>While Digital's database engineers have significantly enhanced DBMS to reduce I/O to the storage areas, writes to the AIJ file are still required at commit time. In high transaction systems, with a large number of processes, the overhead of synchronizing AIJ access may become a bottleneck. </p> <p>The optional AIJ Log Server (ALS) process is a detached process that performs all writes to the After Image Journal (AIJ) file for a database on a node. By enabling ALS, you eliminate the lock bottlenecks that can occur when multiple database processes are each writing to the AIJ file. </p> <p><font size="3">When enabled, there is one ALS process per database per node. Database processes post their updates to a shared global section, and the ALS process writes the updates to disk. The ALS process</font><font size="2"> </font><font size="3">can be started manually through the new DBO command SERVER or the database can be configured to automatically start the ALS when the database is opened.</font><font size="2"><br> </font></p> <p><font size="4"><strong>Electronic AIJ Cache</strong></font></p> <p><font size="3">With ALS enabled, one magnetic disk can handle thousands of TPS (transactions per second). Those with extremely response-time-critical high-end applications can further reduce AIJ stall time by using solid state disks (SSDs) and the new AIJ Cache on Electronic disk (ACE) feature to further reduce commit stalls.<br> </font></p> <p><font size="3">With ACE enabled, a new file type (.ACE) is created on the SSD. This ACE file is used as a temporary cache for AIJ writes, and requires only 64 blocks per node. To use ACE, you must enable ALS for the database.<br> </font></p> <p><font size="3">With ACE enabled, commit stall time is reduced to only 1 millisecond! In fact, the actual writing to the AIJ file happens in parallel with database activity.<br> </font></p> <p><font size="4"><strong>AIJ Backup Server</strong></font><font size="2"><br> </font><font size="3">The optional AIJ Backup Server (ABS) is a process created by the DBMS monitor. The monitor creates the ABS process when DBMS switches to the next AIJ file. When enabled, the ABS automatically performs a backup of the prior AIJ file to an on-disk backup file. The ABS process automatically stops when the AIJ backup completes.</font></p> <p><font size="3"><br> By using the ABS, your AIJ files are automatically backed up to disk without interfering with database operations. This eliminates the need for your operations staff to perform special backup operations for the AIJ files.</font></p> <p><font size="3"><br> Unfortunately, the backup AIJ files created by the ABS cannot be optimized using DBO/OPTIMIZE (similar to no-quiet-point AIJ backups).</font></p> <p><font size="2"><br> </font><font size="4"><strong>Locking</strong></font><font size="2"><br> </font><font size="3"><strong>Two-Phase Page Locking</strong></font><font size="2"><br> </font><font size="3">Before version 6.0, page locking was used for mutual exclusion, and record locking was used for ensuring logical "correctness". In some environments, this results in excessive locking since both a page and a record lock is used to manage the same resource (a record).</font></p> <p align="center"><font size="2"><br> </font><font size="4"><strong>DATABASE ADMINISTRATION</strong></font><font size="3"><strong><br> Software Concepts International<br> can supplement your staff with knowledgable and experienced DEC DBMS DBAs.<br> (603) 879-9022</strong></font></p> <p align="left"><font size="3"><br> Page locking can be enabled on a per-area basis. When page locking is enabled, DBMS uses page locks for both mutual exclusion and logical correct-nesseliminating the need for the overhead of record-level locking. Transactions are serial-ized by page access, rather than record access. This may also reduce I/O since the pages will not be written to disk as the result of a request for a common page.</font></p> <p align="left"><font size="3">When enabled, page locks are held until the end of the transaction. Thus, this may result higher contention at the page level and more deadlocks.</font></p> <p align="left"><font size="3">Page level locking works best when transactions are kept short, and there are sufficient buffers to hold the pages updated during the transaction.</font></p> <p align="left"><font size="3">The DBO/SHOW statistics, Record Lock Statistics (Blocking ASTs and stall times) can provide some insight into whether your environment will benefit from page locking.</font></p> <p align="left"><font size="3"><strong>Partitioned Lock Trees</strong><br> Prior to version 6.0, a single lock tree was created for each database. In a clustered environment, this means that when the database locks were remastered across the cluster, all locks for the database were moved. This impacted the ability to scale database perform-ance with additional cluster nodes.</font></p> <p align="left"><font size="3">In version 6.0, the lock tree is partitioned by database root structures and areas. This provides better cluster scalability, better work-load balancing across a cluster and greater local mastering of locks. Single-node environments will see no benefit from this enhancement.</font></p> <p align="left"><font size="3"><strong>Recoverable Latches</strong></font></p> <p align="left"><font size="3">The VMS Distributed Lock Manager (DLM) provides a robust set of features, and provides for manage-ment of resources across the cluster. The DLM is a highly optimized routine, but the capabilities of the DLM come at a costthey are relatively slow.</font></p> <p align="left"><font size="3">Where cluster-wide resource management is required, the DLM is still the best tool. However, not all DBMS locks require cluster-wide servicing. Those locks that are node specific, are better served by using a more efficient locking protocolrecoverable latches.</font></p> <p align="left"><font size="3">One such node-specific resource that must be managed is the Global Buffer Page Table (GBPT) locks. The GBPT is accessed when a page is added to or removed from a process' allocate set and when a page is read from disk into the global buffer.</font></p> <p align="left"><font size="3">Global buffers offer reduced disk I/O and better memory utilization over local buffering. However, this came at the cost of significantly more synchronization overhead for managing the GBPT slot locks.</font></p> <p align="center"><font size="3"><strong>Recoverable latches drastically reduces the code path<br> required for global buffer synchronization. With version 6.0, you <br> achieve the benefits of global buffers<br> without the added overhead of<br> GBPT synchronization.</strong></font></p> <p align="left"><font size="2"><br> </font><font size="3">Using recoverable latches for managing the GBPT drastic-ally reduces the code path required for global buffer synchronization. With version 6.0, you achieve the benefits of global buffers without the added overhead of GBPT synchronization.</font></p> <p align="left"><font size="4"><strong>Backup/Recovery</strong></font></p> <p align="left"><font size="3">DBMS backup and recovery processes will benefit from many of the I/O improvements made to the version 6.0 I/O subsystem. Along with these benefits, new features have been added to the backup and recovery systems that improves database availability.</font></p> <p align="left"><font size="3"><strong>Fast Incremental Backup</strong></font></p> <p align="left"><font size="3">Incremental backups need to be fast. Before version 6.0, incremental backups scanned the entire database looking for pages modified since the last full backup. As database sizes increase, the percentage of pages modified decreases, while the time required to perform incremental backups increases.</font></p> <p align="left"><font size="3">One solution to this problem is not to scan every page, but to scan only those pages that are likely to have changed. To determine which pages are "likely" to have been changed, DBMS updates the SPAM pages with a time and date stamp and the transaction sequence number (TSN) of the first update that occurs within the SPAM interval after a full backup.</font></p> <p align="left"><font size="3">To implement the fast incremental backups, DBMS now scans the SPAM pages. Pages within each SPAM interval are only scanned for updated pages if the SPAM page indicates that pages within its interval have been modified.</font></p> <p align="left"><font size="3">No changes were necessary in the backup command syntax to implement this change. If you are not using incremental backups because they "take too long," now is a good time to try them again!</font></p> <p align="left"><font size="3"><strong>Recovery by Page</strong></font></p> <p align="left"><font size="3">DBMS is a very solid product, and corruption is rare. However, hardware, software or operational errors are possible and can result in data corruption. Traditional recovery procedures become unacceptable as databases become larger (restoring a 20 gigabyte database may have a significant impact on your operation).</font></p> <p align="left"><font size="3">If only a few pages in your database are corrupted, wouldn't it be great if you could recover only those pages, without having to recover the entire database Well, in version 6.0, you can!</font></p> <p align="left"><font size="3">DBMS tracks corrupted pages in a new root file structure, the Corrupt Page Table (CPT). DBMS automatically logs corrupted pages into the CPT when it detects a checksum error during reference. Pages in the CPT may be in one of three states:</font></p> <ul> <li> <p align="left"><font size="3">Corrupt (pages with checksum errors)</font> </p> </li> <li> <p align="left"><font size="3">Inconsistent (pages restored but need recovery from AIJ to make it consistent)</font> </p> </li> <li> <p align="left"><font size="3">Consistent (pages restored and recovered from the AIJ).</font> </p> </li> </ul> <p align="left"><font size="3">Corrupt pages are pages with checksum errors. Inconsistent pages are pages that have been restored but need to be recovered from the AIJ to make them consistent. Consistent pages are pages that have been restored and whose transaction level makes it consistent with its area.</font></p> <p align="left"><font size="3">To implement this functionality, a new qualifier, "/JUST_PAGES", has been added to both the RESTORE and RECOVER commands. When this qualifier is present, DBO will restore or recover only those pages listed as "corrupt" or "inconsistent" in the CPT.</font></p> <p align="left"><font size="4"><strong>DBO/SHOW STATISTICS</strong></font></p> <p align="left"><font size="3">The DBO/SHOW statistics utility has always been a model for what should be included in a database performance monitor. DBMS 6.0 improves on this, setting yet a higher standard.</font></p> <p align="left"><font size="3"><strong>New Page Header</strong></font></p> <p align="left"><font size="3">The 6.0 statistics utility displays a common header on all statistics screens. The new page headers include useful information such as the length of the collection sample (how long has the database been opened), the sample rate (set_rate), the current page number within the screen (e.g., page 1 of 5) and the input mode (on-line, record or replay).</font></p> <p align="center"> </p> <p align="left"><font size="3"><strong>Lock Deadlocks and Timeouts</strong></font></p> <p align="left"><font size="3">Two new screens have been added to the statistics utility to display "Lock Timeouts" and "Lock Deadlock" histories. These screens are similar to the "Stall Messages" screens of prior versions of DBMS, except that the last occurrence of a lock timeout or deadlock is displayed for each active process on the current node. Both screens display the process id of the process that encountered the lock conflict, the resource that was involved in the lock conflict, and the number of lock conflicts (deadlocks or timeouts) for that process. This information is useful for identifying resource bottle-necks in your application or database.</font></p> <p align="left"><font size="3">While displaying the number of lock conflicts per process is useful, a more useful statistic would be the number of times a resource was involved in a lock conflict.</font></p> <p align="left"><font size="3"><strong>"Stall Message" improvements</strong></font></p> <p align="left"><font size="3">Prior to version 6.0, the "Stall Messages" screen displayed all stalls, regardless of duration. In high-volume OLTP environments (those with a large number of I/Os on high speed devices), it may be difficult to differentiate "important" stall messages from "noise" stall messages caused by millisecond I/O stalls.</font></p> <p align="left"><font size="3">The "Stall Messages" screen has been enhanced to include an "Alarm" option. This allows the DBA to filter out unimportant stalls by removing stall messages less than a specified duration.</font></p> <p align="center"><font size="4"><strong>ON-SITE DBMS TRAINING</strong></font><font size="3"><strong><br> Did your last DEC DBMS<br> training experience provide:<br> * Knowledgable instructors?<br> * Customized instruction?<br> * Answers to site-specific issues?<br> * Would you recommend it to others? <br> Software Concepts International<br> offers all levels of<br> DEC DBMS training<br> from novice to guru,<br> in all aspects of DEC DBMS.<br> (603) 879-9022</strong></font></p> <p align="left"><font size="4"><strong>Reload Utility</strong></font></p> <p align="left"><font size="3">The Reload Utility, a new function of the Database Restructuring Utility (DRU), allows database users to shuffle records in an area to relocate records from non-target pages to their target pages and update the relevant data structures including the meta-data, if necessary.</font></p> <p align="left"><font size="4"><strong>Conversion issues</strong></font></p> <p align="left"><font size="3">Prior to upgrading to DBMS 6.0, you should perform a full DBO/BACKUP on all databases.</font></p> <p align="left"><font size="3">Convert all databases to version 6.0 using the DBO/CONVERT command. There is no need to convert your database backup files from prior versions, since databases are automatically converted to version 6.0 when restored from a database backup file.</font></p> <p align="left"><font size="3">The CONVERT command re-struct-ures the contents of the database root file to make them consistent with version 6.0. As a result, the space requirements of the root file are likely to increase (significantly if you are converting from v4.3 or earlier).</font></p> <p align="left"><font size="3">If you intend on using After Image Journaling (AIJs), you should perform a full DBO/BACKUP after converting your databases to 6.0. Without this post-conversion backup, you will not be able to recover transactions from your AIJ.</font></p> <p align="center"><a href="#Contents"><font color="#000000">Contents</font></a></p> <hr> <p align="center"> </p> <p align="center"> </p> <p align="center"> </p> <p align="center"> </p> <h1 align="center">DBMS <a name="Locking">Locking</a>, Part II</h1> <p><font size="3">Those of you with good memories, will remember that in the last issue of the DBAdvisor, we began a discussion of managing locking issues in DEC DBMS...and promised a follow-up article in this issue...and now, without further interruption, the sequel to DBMS Locking Part I.</font> </p> <h2><font size="4">Causes of Lock Conflicts </font></h2> <p><font size="3">Lock conflicts are normal and should be expected in any concurrent environment. <i>Excessive</i> lock conflicts are not expected, nor should they be tolerated. This section examines many of the common causes of excessive lock conflicts, and identifies ways to resolve them.</font><font size="4"> </font></p> <p><font size="3">There is no "magic" database parameter that will solve (or significantly reduce) lock contention. The responsibility for resolving lock conflict problems are <i>shared</i> between the application designer, the database administrator and the application programmer.</font><font size="4"> </font></p> <p><font size="3">To understand the cause of lock conflicts, one must understand when DBMS requests a record lock, promotes it to a higher level (exclusive), and when DBMS releases record locks. Armed with this information, you are able to minimize the number of records that DBMS locks, and reduce the time that it keeps record locked.</font> </p> <h3>Read-only record locks:</h3> <p>DBMS places a read-only lock on a record whenever a record is accessed. Assuming the database is not set for "hold retrieval locks", DBMS maintains this lock until no currency indicators point to the record, and the record is not pointed to by an entry in a keeplist. If the database is set for "hold retrieval locks", then the read-only lock is held until the next COMMIT or ROLLBACK. </p> <p>Read-only record locks do not block other run-units from <i>reading</i> this record - However, if another run-unit attempts to <i>update</i> this record, <i>either directly or indirectly</i>, this results in a lock conflict. </p> <h3>Exclusive record locks.</h3> <p>DBMS promotes a lock to exclusive mode whenever a record is updated. This update can occur <i>directly</i>, as the result of a MODIFY, STORE or ERASE DML verb, or <i>indirectly</i> when a record is inserted or removed from a set (DBMS updates the pointer clusters of the next and prior records within a set). An exclusive lock can also be requested explicitly by an application program by using the <i>'FOR UPDATE'</i> option of the FIND and FETCH verbs. <i>Once an exclusive lock has been placed on a record, that record remains locked until the next COMMIT or ROLLBACK</i>. If the transaction is ended with a 'COMMIT <i>RETAINING'</i>, then records pointed to by a keeplist entry or a currency indicator are demoted to read-only locks while all others are unlocked. </p> <p>Exclusive locks prevent all other run-units from <i>accessing or passing through</i> this record. This may cause serious locking problems when the locked <i>record</i> is an <i>index node</i> of a highly traversed indexed set or another highly-accessed record occurrence. </p> <p>Example: Run-unit blocked from passing through a locked record. </p> <p><font size="2"><b>Figure 1: Process blocked from reading through a locked record</b></font> </p> <p> </p> <p><font size="3">If run-unit <i>a</i> modifies rec-2, then run-unit <i>a</i> will hold an exclusive lock on rec-2. If run-unit b finds rec-1 and then attempts to find rec-3 within the set "my_set" then run-unit <i>b</i> will be blocked until run-unit<i> a</i> releases rec-2.</font><font size="4"> </font></p> <h3>Application Design Issues</h3> <h4><font size="3">Currency Indicators...</font><font size="4"> </font></h4> <p><font size="3">Currency indicators are place holders that mark a position in each logical structure in a subschema Currency indicators can be <i>active </i>(they point to a record), or <i>null</i> (they don't point to anything). Currency indicators provide a mechanism for efficient database navigation. However, <i>each </i><i><u>active</u></i><i> currency indicator maintains a read-only lock on the record to which it points</i>. This read-only lock is maintained until all currency indicators have been removed from the record <i>and</i> the record is not pointed to by a keeplist entry.</font><font size="4"> </font></p> <p><font size="3">Currency indicators can point to:</font><font size="4"> </font></p> <p><font size="3">1. The most recently retrieved or updated record (<b><i>run-unit</i></b>).</font><font size="4"> </font></p> <p><font size="3">2. A position within <i>each</i> realm (<b><i>realm currency)</i></b>.</font><font size="4"> </font></p> <p><font size="3">3. A position within each set-type (<b><i>set-type currency</i></b>).</font><font size="4"> </font></p> <p><font size="3">4. An occurrence of each record-type (<b><i>record type currency).</i></b></font> </p> <p><font size="3"><i>Each </i><i><u>active</u></i><i> currency indicator maintains a read-only lock on the record to which it points</i>.</font><font size="4"> </font></p> <p><font size="3"><i>By default, </i>DBMS<i> automatically</i> updates all currency indicators after executing each DML (Data Manipulation Language) statement. This allows you to use any of these currency indicators without pre-stating your intentions. Since relatively few currency indicators are generally used during a specific transaction, significantly more currency indicators are made active than are needed by an application. As a result, records are locked for longer than is necessary.</font><font size="4"> </font></p> <p align="center"> </p> <p align="center"><b>Mismanagement of currency indicators is the single biggest cause of excessive lock conflicts.</b> </p> <p align="center"> </p> <p><font size="3">Mismanagement of currency indicators is the single biggest cause of excessive lock conflicts. While currency indicators are required for efficient navigation of a DEC DBMS database, each <i>active</i> currency indicator maintains a read-only lock on the record it points to. <i>The longer you maintain an active currency indicator on a record, the longer the record is locked.</i></font><font size="4"> </font></p> <p><font size="3"><b>Run-unit</b> currency indicators point to the last record <i>successfully</i> accessed using a FIND, FETCH or STORE verb. There is <i>only one run-unit currency indicator</i> (per stream) and it points to the "current" position in the database. The run-unit currency indicator is the only currency indicator is <i>always</i> updated whenever you access a DEC DBMS record (it cannot be suppressed using the RETAINING clause).</font><font size="4"> </font></p> <p><font size="3"><b>Realm</b> currency indicators point to a position (a record) within a realm. DEC DBMS provides the ability to maintain separate realm currency indicators for <i>each realm</i> in your subschema. Multiple realm currency indicators can be active at a time. This allows you to <i>find next, prior or current</i> within a <i>REALM</i> - using a realm as the starting-point for database navigation. By default, realm currency indicators are automatically updated each time you access a record. To improve DML efficiency and to reduce locking, REALM currency indicators should be retained, <i>except</i> when your program accesses a record using the "WITHIN <<i>realm-name></i>" DML syntax.</font><font size="4"> </font></p> <p><font size="3"><b>Record-type</b> - Record type currency indicators point to an occurrence of a given record-type. Each record-type defined in the subschema can have an active currency indicator. The record-type currency indicator allows you to "find current <<i>record-name></i>". By default, record-type currency indicators are automatically updated whenever you access a record. <i>RETAIN </i>record-type currency indicators except when your programs re-access a record using the 'FIND (or FETCH) <i>CURRENT</i> <i>record-name</i>'.</font><font size="4"> </font></p> <p><font size="3"><b>Set-type</b> currency indicators mark a position within a given set-type. This allows you to navigate DBMS sets easily and efficiently. Each set-type defined in the subschema has a currency indicator, which may be current or null. By default, when you access a record, DBMS automatically updates set-type currency indicators for <i>all</i> set-types owned by the record just accessed, as well as all set-types in which the record participates as a member. Typical DML programs use only a few of the set-type currency indicators that are automatically updated by DBMS - thus adding overhead and holding locks longer than necessary.</font><font size="4"> </font></p> <p><font size="3">The key to efficient use of currency indicators, is to <i>RETAIN </i>all currency indicators <i>EXCEPT</i> those <i>needed</i> for efficient database navigation. When a currency indicator is no longer required for further navigation (i.e., at an end-of-collection), it should be freed. Several benefits are derived from proper management of currency indicators, including:</font><font size="4"> </font></p> <ul> <li><font size="3">Reduced overhead (DBMS doesn't have to update the currency indicators)</font><font size="4"> </font></li> <li><font size="3">Reduced locking (locks are released sooner)</font><font size="4"> </font></li> <li><font size="3">Reduced code-paths (programs generally perform fewer DML verbs since "re-finding" records to re-establish currency is minimized).</font><font size="4"> </font></li> </ul> <p> </p> <p><strong>Queued Locking</strong></p> <p>The key to efficient use of currency indicators, is to RETAIN all currency indicators EXCEPT those needed for efficient database navigation. When a run-unit attempts to access a DBMS record that is locked by another run-unit (the granted lock), the lock request is placed in a lock conversion queue. If another run-unit attempts to access that record, in an access mode that is incompatible with either of the other processes, its request will be blocked and the request will be placed in the conversion queue as well  even it its request is compatible with the granted lock.</p> <p><strong>Example:</strong><br> Run-unit 1 accesses PART '12345' for read (R)</p> <p>Run-unit 2 attempts to access PART '12345' for update (EX)</p> <p>Run-unit 3 attempts to access PART '12345' for read (R).</p> <p>Run-unit 2's exclusive request is blocked by run-unit 1's read-only lock.</p> <p>Run-unit 3's read request is blocked by run-unit 2's exclusive request.</p> <p>NOTE: This is NOT a deadlock -- run-units 2 and 3 will wait until run-unit 1 releases the 'read-only' lock on PART '12345'.</p> <p>The queued lock request mech-a-nism insures equitable access to database resources. How-ever, queuing lock requests can degrade database concurrency if processes are not releasing locks promptly. </p> <p><strong>Excessive transaction duration.</strong></p> <p>Long transactions degrade shared access to a database for two reasons. First, long transactions tend to lock more records due to the additional processing they are performing. Furthermore, many of these records are locked for extended periods of time, preventing other processes for accessing them.</p> <p>Concurrent read/write access to a DBMS database means shared access. Ideally, each run unit sharing this database will have the illusion that they have exclusive access to the database. However, to create this illusion, all run-units must be willing to share database resources. This requires that no process locks a resource for longer than is necessary to accomplish their task.<br> A common misconception is that only update transactions must be kept to a minimum. .All tran-sactions, except for EXCLUSIVE (where concurrency is not an issue) and snapshot (BATCH RETRIEVAL) transactions should issue a periodic commit [retaining] or rollback.</p> <p>The need for update transactions to commit or rollback is obvious  this releases locks on any modified records. While read-only record locks are released by using the FREE statement, a commit or rollback is necessary to release the QUIETPOINT and snap area cursor locks used by /ONLINE backups and deferred snapshot transactions.</p> <p>A record or entry-point into the database that is frequently updated can effectively single-thread access to your database.</p> <p><strong>Locking and Terminal I/O...</strong></p> <p>Sound principals of online system development, dictate that resource (database) locks should not be held while waiting for terminal I/O. Typical update transactions can be broken down into three (3) discrete steps:</p> <p>1. Inquiry phase: The operator views the current status of the data.<br> 2. Input phase: The terminal oper-ator enters new or changed data.<br> 3. Update phase: Updates are posted to the database.</p> <p>To improve database concurrency, always commit or rollback trans-actions at the end of the inquiry phase. This releases all record locks during the input (or terminal I/O) phase.</p> <p>Using this model, the update phase becomes a bit more complicated. First, records must be re-accessed (using either their logical or physical keys), and then their values must be compared with the values obtained during the inquiry phase  if the values are unchanged, then the record can be modified to reflect the desired update. This check is necessary to ensure that your trans-action does not overwrite an update made by another run-unit.</p> <p><strong>Improper Ready Modes.</strong></p> <p>Inappropriate READY modes are usually fairly obvious in a concur-rent environment. The run-unit will receive the AREABUSY except-ion condition or will stall if the WAIT qualifier was used in the READY statement. CONCUR-RENT RETRIEVAL and CON-CUR-RENT UPDATE are the most common ready modes used in a shared DBMS environment.</p> <p>It should be noted that there is no difference in record locking when accessing records when realms are readied for either CONCUR-RENT RETRIEVAL or CONCUR-RENT UPDATE. Readying a realm for CONCURRENT UPDATE enables you to update records in that realm, and prevents others from readying the area for PROTECTED or EXCLUSIVE access.</p> <p>BATCH RETRIEVAL is an effective ready mode for eliminating record lock conflicts in those environ-ments where snap-shots allowed and enabled (or deferred). BATCH RETRIEVAL (or snapshot) trans-actions incur no record locks  thus, they neither block nor are blocked by other transactions. Note that if snap-shots are enabled and non-deferred (the default), that all update trans-actions except EXCLUSIVE UPDATE will write before images of the updated records to the snapshot file. While DBMS carefully manages writes to the snapshot file to min-imize overhead, in some environ-ments this I/O overhead may still be prohibitive.</p> <p><strong>Inefficient DML statements</strong></p> <p>DBMS does much work to minimize the use of VMS locks, and to resolve lock conflicts for you. Much of this work is done using the Blocking AST (BLAST) mechanisms of the VMS lock manager. For instance, DBMS writes modified pages back to the database and then demote locks when it receives a BLAST for a page lock, thus allowing concurrent access to a data-base page. However, in current releases of DBMS, BLASTs are only processed between DML verbs. Thus, if you are processing an inefficient DML statement (such as a FETCH using a non-key value), DBMS does not to respond to BLASTS (does not release a locked resource) until the DML verb completes.</p> <p><strong>Database Design</strong></p> <p>While database design can sign-ificant-ly impact concurrency, many sites are faced with imple-menting a database design provided by a third-party vendor. Below are several common database design pitfalls that degrade database concurrency.<br> </p> <p><strong>Excessive Sets</strong></p> <p>Sets provide efficient access paths to their member records. However, insertion and removal from sets physically modifies the next and prior pointers of the adjacent members in the sets. Even though the modification to these adjacent members was indirectly caused by the insertion or removal from a set, the impact is no less severe from a locking standpoint. When inserting or removing a record from a set, adjacent members within that set are locked for exclusive update until the end of the transaction.</p> <p><strong>Excessive Indexed Sets</strong></p> <p>Indexed sets tend to cause more locking problems than CALC (hashed) or CHAIN sets. Insertions and removals from indexed sets cause not only the adjacent records in the set to be updated, but also the set's index nodes. This places an exclusive lock on an index node which is often a primary access path to its member records. Thus, when this one index node is locked, it can effectively block access to thousands of member records! SYSTEM owned index nodes are particularly problematic since they often provide a single entry-point to all members. Thus, a locked index node may effectively block access to all records in the set.</p> <p><strong>Index Node sizes...</strong></p> <p>The larger the size of an index node, the more member records it can potentially point to. Larger indices tend to lock more records than smaller index nodes. By decreasing the node size of your indexed sets, you can decrease the number of records controlled by each node. This reduces the level of contention for the index nodes themselves. The trade off is that smaller index nodes result in more I/O's to find the member records. You must also balance index node size with index usage  an indexed set that is built once and then used only as a lookup table can have large node sizes without negatively impacting concurrency, while an index that is frequently updated in a concurrent environ-ment will generally benefit from smaller node sizes.</p> <p><strong>Frequently updated record occurrence</strong></p> <p>A record or entry-point into the database that is frequently updated can effectively single-thread access to your database. For example, suppose your on-line order entry system uses sequential order numbers for tracking orders. Two common methods for obtaining the 'next' order number are:</p> <ol> <li>To place the orders in a sorted set then access the last member in the set, add 1 to its order number before storing the next order.</li> <li>To access a control-record via a CALC set, increment the 'last-number' field in the control-record and then store the new order record.</li> </ol> <p>Both of these methods suffer from the same problem  posting of order records are single threaded through either the indexed set or through the control record. .Note that all chronological indexed sets will suffer from this anomaly.</p> <p>If you application permits, you can assign order numbers by order-taker  using a unique ID to prefix the order numbers. Thus, each order-taker is then assigned their own 'thread' into the database. Locking can also be reduced by assigning a block of control numbers at a time, thus minimizing the need to access and update the control-record.</p> <p><strong>Single record deadlocks...</strong></p> <p>When inserting or removing a record from a set, adjacent members within that set are locked for exclusive update until the end of the transaction. Frequently accessed records are also the source of often misunderstood deadlocks. While the "deadly embrace" is often thought of as the only source of deadlocks, it is possible for two run-units to deadlock when accessing a single record. The following example illustrates this problem:</p> <p>RUN UNIT ACTION STATUS<br> 1 FETCH "A" OK<br> 2 FETCH "A" OK<br> 1 MODIFY "A" WAIT<br> 2 MODIFY "A" DEADLOCK</p> <p>To minimize the chance of single record deadlocks, records that are known to be updated during the transaction should be accessed for exclusive update using the FOR UPDATE qualifier of the FETCH or FIND statement. This will cause the second run-unit to wait until the first run-unit commits their transaction before being granted access to the record. </p> <p>Example:<br> RUNUNIT ACTION STATUS<br> 1 FETCH "A" FOR UPDATE OK<br> 2 FETCH "A" FOR UPDATE WAIT<br> 1 MODIFY "A"/COMMIT OK*<br> 2 MODIFY "A" OK<br> *run-unit 2s FETCH now completes.</p> <p><strong>Separate static from volatile data</strong></p> <p>Reads do not conflict with other reads. Record lock conflicts occur between writers and other run-units. By grouping records according to their update patterns, improved database concurrency results. For example, suppose your application has a large look-up table that is initially loaded and infrequently updated, but often searched. By placing the records that make up this table in an area that is infrequently updated, you can virtually eliminate lock conflicts when accessing these records.</p> <p><strong>Tools</strong></p> <p>Database locking can be observed at the system, database, or at the application level. Each tool is effective for identifying different types of lock resource problems.</p> <p><strong>DBO/SHOW STATS</strong></p> <p>Several of the DBO/SHOW STAT-ISTICS screens can be used isolate and identify locking problems.</p> <p>Summary Locking Statistics provide a good overview of lock utilization and contention. Key indicators to look at are "stall time x100". This provides some insight into how much time is being spent waiting for locked database resources.</p> <p>Once that you have identified that a significant amount of time is being spent waiting for locked database resource, the Locking (one lock type) screens can quickly identify which lock types are causing the greatest stall time.</p> <p>Record locks are the most frequent source of lock conflicts. The Locking (record locks) screen displays the record locking statistics. Again, "stall time x100" provides an indication of how much time is being spent waiting for record locks. If you have adjustable record locking granular-ity enabled for your database, compare the value of blocking ASTs to the number of locks requested. A number greater than 20 percent indicates that significant overhead is being incurred to manage the ALG tree. Disabling ALG or reducing the number of levels in its tree will reduce the Blocking ASTs and may improve overall database performance.</p> <p>VMS MONITOR LOCKS</p> <p>The VMS MONITOR LOCKS utility provides insight into total lock activity on your node and nodes in a VAXCluster. This utility is useful for identifying potential lock shortages as well as determining if total locking activity may be affecting system performance.</p> <p align="center"> </p> <p><strong>DBO/SHOW GETLKI (pre v4.3)</strong></p> <p>The DBO/SHOW GETLKI utility provides extensive database lock information in a dump-like format. While the format of this report does not lend itself to quick analysis, it contains information that can identify what processes are blocking other processes, which processes are being blocked and most of the data held in the database lock tree. Note that DBO-/SHOW GETLKI runs in EXEC mode, and cannot be terminated with a CTRL-Y. Since its output can be quite lengthy, you should direct its out-put to a file rather than SYS$OUTPUT.</p> <p><strong>DBO/SHOW locks (v4.3 and beyond)</strong></p> <p>Version 4.3 significantly enhanced the lock summary reports with the addition of the DBO/SHOW LOCKS command. This new command allows you to show processes that are blocked by another process, processes that are blocking other processes, and to select locks held by a specific process. This command is particularly useful for determining who is blocking another process, and to identify the resource in contention.</p> <p align="left"> </p> <p align="left"><strong>ShowLocks()</strong></p> <p align="left">At the application level, you can easily develop routines using callable DBQ to display or report on the records currently locked by a process. This information is extremely useful in identifying which records are locked at specific points within your application.</p> <p align="left">Example:<br> call 'dbq$interpret'<br> using by descriptor<br> 'set output <output-filespec>'<br> call 'dbq$interpret'<br> using by descriptor<br> 'show locks'<br> call 'dbq$interpret'<br> using by descriptor<br> 'set output'</p> <p align="left"><strong>ShowCurrent()</strong></p> <p align="left">As discussed previously in this article, mismanaged currency indicators are frequently the largest cause of excessive lock conflicts. It is easy to instrument your code to display the status of currency indicators in your application.</p> <p align="left">call 'dbq$interpret'<br> using by descriptor<br> 'set output <output-filespec>'<br> call 'dbq$interpret'<br> using by descriptor<br> 'show current'<br> call 'dbq$interpret'<br> using by descriptor<br> 'set output'<br> open <output-filespec><br> for each record<br> display if it does not contain "is null"<br> next record<br> close file</p> <p align="left">It is also possible to generate a routine for reporting currency indicators by using the DBCS function call 'DBM$-GET-_DBKEY'. This function allows you to return the DBKEY of records pointed to by each realm, record, and set currency indicator in the current subschema. Currently there is no mechanism to dir-ect-ly translate the currency-ind-icator id's to their realm, record and set namesalthough this informa-tion can be obtained from a subschema listing.</p> <p align="left"><font size="5"><strong>Summary</strong></font></p> <p align="left">By default, DBMS takes a very conservative approach to locking  generally, it locks much more than is actually needed by an applica-tion. DBMS also provides sign-ificant control over what it locks, and how long it retains locks. By carefully managing locking in a DBMS environment, it is possible to provide virtually transparent "shared" access to your DBMS database!</p> <p align="left"><font size="5"><strong>Identify & Resolve!</strong></font></p> <p align="left">Armed with information on causes and cures for excessive locking, you are now ready to identify and resolve those nasty contention problems in your environment.</p> <p align="center"><a href="#Contents"><font color="#000000">Contents</font></a></p> <hr> <p> </p> <p align="center"><a name="Computer Associates Acquires The ASK Group"><font size="6">Computer Associates Acquires The ASK Group</font></a></p> <p>By now, we all have heard that computer software giant, Computer Associates (CA), has acquired financial-ly troubled ASK Group.</p> <p align="center"><strong>CA brings to the ASK customers financial strength that will allow continued support and enhance-ments to the ASK products  including MANMAN/-Classic.</strong></p> <p>The ASK group has been an acknowledged leader in manu-factur-ing and distribution systems for many years. ASK based its systems on a sound technological foundation, and on a thorough understanding of real-world client requirements.</p> <p>CA brings to the ASK customers financial strength that will allow continued support and enhance-ments to the ASK products  including MANMAN/-Classic. CA-MANMAN/Classic will continue to be enhanced and supported, with input from clients and the User Advisory Board.</p> <p>A near-term release will provide significant functionality enhance-ments. A visual work-bench will provide convenient and flexible access to information. Integration with CA tools such as CA-Visual Express will add flexible end-user querry and reporting.</p> <p>Some of the major enhancements being brought to the product line include more flexible purchase order processing, streamlined order entry, greater visibility in formula management and new Data-port transac-tion sets.</p> <p>Over the long term, CA's develop-ment plans for MANMAN/Classic will be based on real-world require-ments, and will be determined after close cooperation with clients and the User Advisory Board.</p> <p>The acquisition of ASK by CA does not represent the death of MAN-MAN/Classic  rather it may re-pre-sents the rebirth. </p> <p align="center"><a href="#Contents"><font color="#000000">Contents</font></a></p> <hr> <p><font size="2">The DBAdvisor is published by Software Concepts International<b><i>.</i></b> We will consider for publication all submitted manuscripts and photographs. SCI welcomes your articles and suggestions but cannot be responsible for loss or damage. All information presented is believed to be accurate. however, we cannot be responsible for their accuracy or application.</font> </p> <hr> <p><font size="2">COPYRIGHT � 1994 by Software Concepts International<i>.</i> All rights reserved. No part of this publication may be reproduced in any form without written permission from the publisher, and the inclusion of this copyright notice.</font> </p> <hr> <p> </p> <p align="center"><strong> </strong></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> </table> </center></div> <hr> <p align="center"><em>Copyright � 1996 Software Concepts International Inc. All rights reserved. </em></p> </body>