Oracle_Rdb[TM]______________________________________ Release Notes Version 7.0 ECO 1 Mar 1997 This manual summarizes corrections to software problems. ________________________________________________________________ Oracle Rdb Release Notes Version 7.0 ECO 1 Copyright © Oracle Corporation, 1986, 1997 This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065. If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error- free. Notice of Product Name Changes The product DEC Rdb and a number of other related products were recently purchased from Digital Equipment Corporation by Oracle Corporation. DEC Rdb is now known as Oracle Rdb. Because the sale of these products was concluded recently, the software/documentation does not reflect the new names. Oracle is a registered trademark of Oracle Corporation. Oracle CODASYL DBMS, Oracle CDD/Repository, Oracle CDD/Administrator, Oracle DBA Workcenter, Oracle Expert, Oracle Graphical Schema Editor, Oracle InstantSQL, Oracle Module Language Oracle RALLY, Oracle Rdb, Oracle RMU, Oracle RMUwin, Oracle SQL/Services, Oracle TRACE, and Oracle TRACE Collector are trademarks of Oracle Corporation. All other product or company names mentioned are used for identification purposes only, and may be trademarks of their respective holders. This document was prepared using VAX DOCUMENT, Version 2.0. _________________________________________________________________ Contents Preface................................................... iv 1 Installing Oracle Rdb ECOs 1.1 Requirements..................................... 1-1 1.2 Invoking VMSINSTAL............................... 1-1 1.3 Stopping the Installation........................ 1-2 1.4 After Installing Oracle Rdb...................... 1-2 2 Software Errors Fixed 2.1 Software Errors Fixed in ECO 1................... 2-1 2.1.1 Bugchecks at PIOFETCH$WITHIN_DB + 2DC ......... 2-1 2.1.2 RMU/RESTORE Bugchecks at FILE_CLEANUP + 72 .... 2-2 2.1.3 Read Only Transactions Returned Incorrect Results........................................ 2-3 2.1.4 PGSPAMENT Errors from RMU VERIFY after an RMU RESTORE........................................ 2-4 2.1.5 INPCONERR from Parallel Load ................. 2-5 2.1.6 RDO global field matching fails when SCALE is negative....................................... 2-6 2.1.7 Bugcheck with exception at RDMS$$ALPHA$DYN_EXPAND + 00009C................ 2-7 2.1.8 RMU Errors are not Always Directed to SYS$ERROR ............................................... 2-7 2.1.9 RMU/SHOW STATISTIC "Stall Messages" could not be internationalized........................... 2-9 2.1.10 Hash index incurs extra I/Os. ................. 2-9 2.1.11 A race condition caused the corruption of indices and segmented strings.................. 2-10 2.1.12 Excessive I/O during an insert into a hashed index.......................................... 2-11 iii 2.1.13 Excessive SPAM fetches during a single update/insert & delete......................... 2-11 2.1.14 Random arithmetic trap errors during RMU/LOAD....................................... 2-11 2.1.15 Monitor Log File Fragmentation ................ 2-12 2.1.16 Rollback After Delete Could Bugcheck .......... 2-12 2.1.17 CONVERT_ERROR or ARITH_EXCEPT from SELECT with GROUP BY when no data in underlying table...... 2-13 2.1.18 Process deletion after several date expressions.................................... 2-14 2.1.19 Hot Standby Database Attributes Lost .......... 2-14 2.1.20 ALTER INDEX changes HASHED ORDERED to HASH SCATTERED...................................... 2-14 2.1.21 ALTER STORAGE MAP does not reorganize data .... 2-15 2.1.22 SQLCODE/SQLSTATE incorrectly propagated from SQL functions and procedures .................. 2-15 2.1.23 ALTER INDEX waits too long to validate storage area name ..................................... 2-16 2.1.24 RMU Extract generates incorrect keyword for PARTITIONING clause ........................... 2-17 2.1.25 Unexpected RDMS$_WISH_LIST generated by ALTER DATABASE ... DROP STORAGE AREA CASCADE ........ 2-17 2.1.26 Vertically partitioned tables allocate space for COMPUTED BY columns ....................... 2-18 2.1.27 VARCHAR data in vertically partitioned tables not saved correctly ........................... 2-18 2.1.28 ALTER TABLE not fully supported for Vertically Partitioned tables ............................ 2-19 2.1.29 COSI-F-EXQUOTA seen when creating storage map with many columns in STORE COLUMNS clause...... 2-19 2.1.30 Problems corrected for Sequential Scan ....... 2-20 2.1.31 Problems corrected for Strict Partitioning ... 2-20 2.1.32 When page transfer via memory is enabled read only transactions may fail with SYS-F-NOPRIV error.......................................... 2-24 2.1.33 Spam Pages Could be Incorrect after Recovery .. 2-24 2.1.34 RMU/Load/Place forces constraint evaluation ... 2-25 2.1.35 Loss of entries in ranked indexes with duplicates..................................... 2-25 2.1.36 WAIT FOR CLOSE clause SHOWS incorrect information and does not IMPORT correctly...... 2-26 2.1.37 Match keys with constant equality return wrong number of rows................................. 2-27 iv 2.1.38 RMU/SHOW STATISTIC bugcheck following cancelled sub-menu selection............................. 2-29 2.1.39 RMU/RECOVER of optimized AIJ journal does not preserve sequence#............................. 2-29 2.1.40 Subquery of IN clause with both DISTINCT and LIMIT TO returned too few rows ................ 2-30 2.1.41 Database Recovery Processes Bugcheck at DBR$RESOLVE.................................... 2-31 2.1.42 Minimum NODE SIZE for SORTED indices not calculated correctly .......................... 2-31 2.1.43 RMU/BACKUP/AFTER_JOURNAL "File Already Exists" Failure........................................ 2-32 2.1.44 RMU/SHOW STATISTIC "Alarm Bell" rings excessively for long stalls.................... 2-33 2.1.45 Floating divide by zero in query with constant expression..................................... 2-34 2.1.46 Memory leak with sort when cursor closed before end of stream.................................. 2-35 2.1.47 TRANSLATE() Function Returns Corrupted Data Instead of White Space......................... 2-36 2.1.48 ACCVIO During Remote Replication .............. 2-36 2.1.49 Row Cache screen shows extraneous characters where cache name exceeds 21 characters......... 2-37 2.2 Documentation Corrections........................ 2-37 2.2.1 Documentation Order Number Corrected .......... 2-37 2.2.2 Documentation Corrections for Rdb7 ............ 2-38 2.2.2.1 Incorrect syntax for SET FLAGS statement.... 2-38 2.2.2.2 Incorrect Syntax for CREATE STORAGE MAP..... 2-39 2.3 Known problems................................... 2-40 2.3.1 Restriction added for CREATE STORAGE MAP on table with data ............................... 2-41 2.3.2 The SET TRANSACTION MODE clause of a CREATE/ALTER DATABASE is not exported/imported.............................. 2-41 2.3.3 Processes stalling for page locks when ABW is enabled........................................ 2-42 2.3.4 RMU/EXTRACT problem when generating RDO scripts on ALPHA....................................... 2-43 2.3.5 Database Hang When Using Multiple Attaches to Same Database.................................. 2-44 2.3.6 OTHERWISE clause only valid with STORE USING clause of a Vertical Record Partition Storage Map............................................ 2-44 v 2.3.7 STORE COLUMNS clause required for each Vertical Record Partition............................... 2-45 2.3.8 DROP TABLE CASCADE will result in %RDB-E-NO_META_UPDATE.......................... 2-46 3 Enhancements 3.1 Enhancements Provided in ECO 1................... 3-1 3.1.1 RMU/SHOW STATISTIC "Stall Class" Informational Screens........................................ 3-1 3.1.2 RMU/SHOW STATISTIC "Stall Messages" Include Transaction State.............................. 3-4 3.1.3 RMU/SHOW STATISTIC "Logical Area" Screens ..... 3-4 3.1.4 RMU/REPAIR /INITIALIZE=LAREA_PARAMETERS /TYPE Qualifier...................................... 3-8 3.1.5 Replication Governor Status Configurable ..... 3-9 3.1.6 Stall Messages and RMU/SHOW STATISTICS ........ 3-10 3.1.7 RMU/SHOW USERS now identifies nodes where DB is open........................................... 3-11 3.1.8 Disk Erase Control Logical Name RDM$BIND_FORCED_DISK_ERASE..................... 3-12 3.1.9 RMU/SHOW STATISTIC Lock Timeout and Lock Deadlock Logging............................... 3-12 3.1.10 ALTER INDEX now performs less area scans ...... 3-15 3.1.11 RMU/DUMP/AFTER /START & /END Qualifiers are Difficult to Use............................... 3-16 vi _________________________________________________________________ Preface The Oracle Rdb Version 7.0 ECO 1 release notes summarize corrections to software. These release notes cover both Oracle Rdb for OpenVMS Alpha and Oracle Rdb for OpenVMS VAX, which are referred to by their abbreviated name, Oracle Rdb. Intended Audience This manual is intended for users responsible for: o System management o Database administration o Application programming Document Structure This manual consists of two chapters: Chapter 1 Describes how to install this ECO. Chapter 2 Describes software errors corrected in this ECO. iv 1 _________________________________________________________________ Installing Oracle Rdb ECOs This software update is installed using the standard VMS Install Utility. 1.1 Requirements The following conditions must be met in order to install ECO 1 to Oracle Rdb Version 7.0: o The installation requires approximately 90000 free blocks on your system disk for OpenVMS VAX systems; 150000 blocks for OpenVMS Alpha systems. o You must have Oracle Rdb Version 7.0 already installed on your system. o You must have a valid Oracle Rdb Version 7.0 license, registered through the VMS License Management Facility (LMF). You do not need a special license to install the ECO. o Oracle Rdb Version 7.0 must be shutdown before you install the ECO kit. That is, the command file SYS$STARTUP:RMONSTOP(70).COM should be executed before proceeding with this installation. If you have a VMS cluster you must shutdown all versions of Oracle Rdb on all nodes in the cluster before proceeding. 1.2 Invoking VMSINSTAL To start the installation procedure, invoke the VMSINSTAL command procedure: @SYS$UPDATE:VMSINSTAL variant-name device-name OPTIONS N The save-set names for the software update for Oracle Rdb Version 7.0 are: o RDBSE1070.A for Oracle Rdb for OpenVMS VAX standard Installing Oracle Rdb ECOs 1-1 o RDBASE1070.A for Oracle Rdb for OpenVMS Alpha standard o RDBMVE1070.A for Oracle Rdb for OpenVMS VAX multiversion o RDBAMVE1070.A for Oracle Rdb for OpenVMS Alpha multiversion Use the name of the device on which you plan to mount the media for the device-name. For example, MTA0: is the device name for a tape drive. It is not necessary to use the console drive for this installation. However, if you do use the console drive, you should replace any media you removed once the installation is complete. The optional OPTIONS N parameter prints this manual, which is the only documentation for the ECO. The following example shows how to start the installation procedure on device MTA0: and print the documentation. $ @SYS$UPDATE:VMSINSTAL RDBSE1070 MTA0: OPTIONS N 1.3 Stopping the Installation To stop the installation procedure at any time, press Ctrl/Y. When you press Ctrl/Y, the installation procedure deletes all files it has created up to that point and exits. You can then start the installation again. If VMSINSTAL detects any problems during the installation, it notifies you and a prompt asks if you want to continue. You might want to continue the installation to see if any additional problems exist. However, the copy of Oracle Rdb installed will probably not be usable. 1.4 After Installing Oracle Rdb This ECO provides a new Oracle Rdb Oracle TRACE facility definition. Any Oracle TRACE selections that reference Oracle Rdb will need to be redefined to reflect the new facility version number for the updated Oracle Rdb facility definition, "RDBVMSV7.0-1". 1-2 Installing Oracle Rdb ECOs If you have Oracle TRACE installed on your system and you would like to collect for Oracle Rdb, you must insert the new Oracle Rdb facility definition included with this ECO kit. The installation procedure inserts the Oracle Rdb facility definition into a library file called EPC$FACILITY.TLB. To be able to collect Oracle Rdb event-data using Oracle TRACE, you must move this facility definition into the Oracle TRACE administration database. Perform the following steps: 1. Extract the definition from the facility library to a file (in this case, RDBVMS.EPC$DEF). $ LIBRARY /TEXT /EXTRACT=RDBVMSV7.0-1 - _$ /OUT=RDBVMS.EPC$DEF SYS$SHARE:EPC$FACILITY.TLB 2. Insert the facility definition into the Oracle TRACE administration database. $ COLLECT INSERT DEFINITION RDBVMS.EPC$DEF /REPLACE Note that if you are installing the multiversion variant of Oracle Rdb, the process executing the INSERT DEFINITION command must use the version of Oracle Rdb that matches the version used to create the Oracle TRACE administration database or the INSERT DEFINITION command will fail. Installing Oracle Rdb ECOs 1-3 2 _________________________________________________________________ Software Errors Fixed This chapter describes software errors that are fixed by Oracle Rdb Version 7.0 ECO 1, known restrictions with this version and corrections to the documentation 2.1 Software Errors Fixed in ECO 1 2.1.1 Bugchecks at PIOFETCH$WITHIN_DB + 2DC Bug 343349 Oracle Rdb would bugcheck at PIOFETCH$WITHIN_DB + 2DC when the following conditions were true: 1. When the process attached to the database a storage area was greater than one giga-byte in size, 2. The storage area has since doubled in size, 3. The process that bugchecked attempted to extend the storage area to more than double its original size. or, similarly: 1. When the process attached to the database a storage area was less than one giga-byte in size, 2. The storage area has since grown to more than two giga- bytes in size, 3. The process that bugchecked attempted to extend the storage area to more than two giga-bytes in size. The bugcheck would contain the text similar to the following (this example is for V6.1 VAX): ***** Exception at 0020736C : PIOFETCH$WITHIN_DB + 000002DC %COSI-F-BUGCHECK, internal consistency failure Software Errors Fixed 2-1 There was a limitation in Oracle Rdb that prevented a process from extending a storage area when the extent would cause the storage area to extend to the greater of 1) 2 gigabytes, or 2) twice its original size at the time the process attached to the database. This restriction has been lifted for local buffer databases only. The restriction is still in place for databases that use global buffers. However, the limit for global buffer databases has been raised such that the storage area would have to more than double from an attach time size the greater of either 2 gigabytes, or twice its original size at the time the process attached to the database. For local buffer databases, restarting the application will prevent the bugchecks. For global buffer databases, it is necessary to close and re-open the database if a storage area has more than doubled in size since the database was opened and the storage area had a size of two giga-bytes when it was opened. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.2 RMU/RESTORE Bugchecks at FILE_CLEANUP + 72 Bug 373358 RMU/RESTORE or DBO/RESTORE would bugcheck after restoring After Image Journal (AIJ) information in the restored database. %DBO-I-AIJRECEND, after-image journal "state" recovery complete %SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=7FFFFFF4, PC=00083373, PSL=03C00002 %DBO-I-BUGCHKDMP, generating bugcheck dump file ... The bugcheck dump would contain an exception similar to the following: ***** Exception at 000850F3 : FILE_CLEANUP + 00000072 %SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=7FFFFFF4, PC=000850F3, PSL=03C00002 The problem was due to an error in the AIJ recovery code that would sometimes corrupt various in memory data structures. There is no workaround for this problem. 2-2 Software Errors Fixed This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.3 Read Only Transactions Returned Incorrect Results Bug 397364 If a transaction inserts a row in a table in the same physical database location (DBKEY) as a previously deleted row and then rolls back the insert, other read-only transactions executing at the time will not properly find the previous contents of the older row. The following example demonstrates the problem: -- Session 1: SQL> create table foo (f1 int); SQL> insert into foo values (1); 1 row inserted SQL> insert into foo values (2); 1 row inserted SQL> commit; SQL> set transaction read only; -- Session 2: SQL> delete from foo where f1 = 2; 1 row deleted SQL> commit; SQL> insert into foo values (3); 1 row inserted SQL> rollback; -- Session 1: -- The following query should have returned both rows -- initially stored in the table (1 and 2) but -- it only returned one row. SQL> select * from foo; F1 1 1 row selected This problem would lead to various problems such as: o Online verifies erroneously reporting corrupt databases o Online backups not properly backing up some rows in the database Software Errors Fixed 2-3 o Applications not always getting the correct results back from queries executed in a read-only transaction There is no workaround for this problem. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.4 PGSPAMENT Errors from RMU VERIFY after an RMU RESTORE Under particular circumstances, RMU RESTORE will create SPAM pages that have the incorrect threshold number for a data page. RMU VERIFY will detect these errors during an off-line verify and display the PGSPAMENT message for those entries. The incorrect value does not cause any data corruption or incorrect results to queries, but it can result in free space on data pages not being used. The circumstances required for this to occur are: o The page must be in a storage area with a uniform page format. o The logical area owning the page must have thresholds defined for it. o The correct value of the page fullness that should be stored in the SPAM is 2. Under these circumstances, RMU RESTORE used to store a SPAM threshold value of 3, instead of the correct value. The following example shows a script that creates a database that meets the criteria described above. The database is backed up, deleted, then restored. After the restore operation, VERIFY returns a PGSPAMENT error. $ sql create database filename 'TESTCASE_DB.RDB' create storage area RDB$SYSTEM filename 'TESTCASE_SYSTEM_AREA.RDA' create storage area TEST_AREA1 filename 'TEST_AREA1.RDA'; create table TABLE1 ( FLD1 CHAR (50)); 2-4 Software Errors Fixed create storage map TABLE1_MAP for TABLE1 store in TEST_AREA1 (thresholds are (30, 60, 80)); insert into table1 values ('12345678901234567890123456789012345678901234567890'); commit work; exit $ rmu/backup/nolog testcase_db testcase_db.rbf $ sql drop database filename testcase_db; exit $ $ rmu/restore/nocdd/nolog testcase_db.rbf %RMU-I-AIJRSTAVL, 0 after-image journals available for use %RMU-I-AIJISOFF, after-image journaling has been disabled %RMU-W-USERECCOM, Use the RMU Recover command. The journals are not available. $ $ rmu/verify/all/nolog testcase_db %RMU-W-PGSPAMENT, area TEST_AREA1, page 5 the fullness value for this data page does not match the threshold value in the space management page expected: 3, computed: 2 You can use the RMU REPAIR command with the SPAM qualifier to rebuild SPAM pages. REPAIR will rebuild SPAM pages with the correct value. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.5 INPCONERR from Parallel Load If you try to use parallel load to load data from a delimited text file into a partitioned table and the field used for partitioning the table is not the first field in the data file, then LOAD will use incorrect data when it attempts to determine the correct partition for each row. In most cases, LOAD will abort with an INPCONERR. Software Errors Fixed 2-5 The following example shows the results of an attempt to load a database from a delimited text file when the partitioning column is in the 12th field in the data file. ALPHA>RMU/LOAD/PLAN bench.par %RMU-I-EXECUTORMAP, Executor EXECUTOR_1 (pid: 532066CA) will load storage area BENCH01. %RMU-I-EXECUTORMAP, Executor EXECUTOR_2 (pid: 532052CB) will load storage area BENCH02. %RMU-I-EXECUTORMAP, Executor EXECUTOR_3 (pid: 532066CC) will load storage area BENCH03. %RMU-I-EXECUTORMAP, Executor EXECUTOR_4 (pid: 532066CD) will load storage area BENCH04. %COSI-F-INPCONERR, input conversion error %RMU-I-DATRECREAD, 1 data records read from input file. One workaround for this problem is to store the field used for partitioning as the first field in the data file. Another possibility is to use a fixed file format instead of a delimited text format. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.6 RDO global field matching fails when SCALE is negative It is legal in RDO to fully specify field (aka column) attributes within a DEFINE RELATION statement. If the field name and attributes match an existing global field (aka domain) then that global field is used, otherwise a new global field is created by the DEFINE RELATION statement. This behaviour was not working in the following releases of Rdb: V5.0, V5.1, V6.0, V6.1, and V7.0. If the scale of a numeric value was negative (which is typical) then an error was generated. If the scale was zero, or positive then no error is generated. RDO> define field global1 datatype is signed word scale -3. RDO> commit; RDO> define relation gbl2_tbl. cont> global1 datatype is signed word scale -3. cont> end gbl2_tbl. %RDO-F-FIELD_EXISTS, field GLOBAL1 already exists in this database 2-6 Software Errors Fixed In this case RDO should have detected that the new field matched exactly the existing global field. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.7 Bugcheck with exception at RDMS$$ALPHA$DYN_EXPAND + 00009C In some databases which use record compression, a bugcheck would occur during record expansion. The exception within the bugcheck was Exception at RDMS$$ALPHA$DYN_EXPAND + 009C Other offsets are also possible. No completely successful workaround is known at this time other than to disable record compression. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.8 RMU Errors are not Always Directed to SYS$ERROR RMU did not always direct error messages to the error stream. Instead, the error messages would show up in the output stream. Most users do not notice this, since the error stream and the output stream are usually the same. However, if you wanted to redirect the error stream to somplace different, there were some cases where error messages would not show up in the error stream. The following example shows an attempt to use the RMU DUMP command on an non-existent file. The output stream is redirected to file OUT.TXT and the error stream is redirected to file ERROR.TXT. Notice that the BADDBNAME error appears in the output file but not the error file. Software Errors Fixed 2-7 $ DEFINE SYS$OUTPUT OUT.TXT $ DEFINE SYS$ERROR ERROR.TXT $ $ RMU/DUMP/HEADER FRED $ $ DEASS SYS$OUTPUT $ DEASSIGN SYS$ERROR $ $ TYPE OUT.TXT %RMU-W-BADDBNAME, can't find database root DS1_USER1:[DBA]FRED.RDB; -RMS-E-FNF, file not found %RMU-F-CANTOPNROO, cannot open root file "FRED" $ $ TYPE ERROR.TXT %RMU-F-CANTOPNROO, cannot open root file "FRED" RMU will now display the error message in the error stream. The same commands now give the following results: $ DEFINE SYS$OUTPUT OUT.TXT $ DEFINE SYS$ERROR ERROR.TXT $ $ RMU/DUMP/HEADER FRED $ $ DEASS SYS$OUTPUT $ DEASSIGN SYS$ERROR $ $ TYPE OUT.TXT %RMU-W-BADDBNAME, can't find database root DS1_USER1:[DBA]FRED.RDB; -RMS-E-FNF, file not found %RMU-F-CANTOPNROO, cannot open root file "FRED" $ $ TYPE ERROR.TXT %RMU-W-BADDBNAME, can't find database root DS1_USER1:[DBA]FRED.RDB; -RMS-E-FNF, file not found %RMU-F-CANTOPNROO, cannot open root file "FRED" This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2-8 Software Errors Fixed 2.1.9 RMU/SHOW STATISTIC "Stall Messages" could not be internationalized Currently, all RMU/SHOW STATISTIC "Stall Messages" were hard-coded and could not be internationalized. This has been corrected. All stall messages are now stored as messages in the standard message file. 2.1.10 Hash index incurs extra I/Os. Hash indices created under Oracle Rdb could result in redundant I/Os when data was accessed by them. This behaviour was viewed by RMU/SHOW STATISTICS. Dumping the hash index area showed that if collisions were present, they would be linked linearly by overflow pointers when this was not necessary. The following example shows the problem: 0038 2005 02E2 line 2: bucket for hash index 56 .... total hash bucket size: 127 FFFF FFFFFFFF FFFF 02E6 bucket overflow -1:-1:-1 00 02EE flags 0 00000001 02EF duplicate count 1 0032 00000011 000D 02F3 pointer 50:17:13 65 02FB key len: 101 bytes 2020202020202020202020706C656800 02FC key: '.help ' 20202020202020202020202020202020 030C key: ' ' :::: (4 duplicate lines) 2020202020 035C key: ' ' 00 0361 padding '.' 0038 2005 0362 line 1: bucket for hash index 56 .... total hash bucket size: 127 0038 00000073 0002 0366 bucket overflow 56:115:2 ^^^^^^^^ 00 036E flags 0 00000001 036F duplicate count 1 0032 00000011 0005 0373 pointer 50:17:5 65 037B key len: 101 bytes 20202020202020202020202074616300 037C key: '.cat ' 20202020202020202020202020202020 038C key: ' ' :::: (4 duplicate lines) 2020202020 03DC key: ' ' 00 03E1 padding '.' Software Errors Fixed 2-9 Hash elements "cat" and "help" hash to the same hash value. They should have been in the same hash bucket instead they were in two different buckets linked by a bucket overflow pointer. The workaround to this problem is to make sure there are no collisions. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.11 A race condition caused the corruption of indices and segmented strings Several database corruption problems have been discovered and were related to a race condition within Oracle Rdb. Symptoms included: o Corrupted B-tree indices. This includes index keys out of sequence, index and table out of synchronization. For example an index points to a non-existent table row or a table row is not pointed to by an index entry. o Corrupted Hash index. For example an index points to a non-existent table row or a table row is not pointed to by an index entry. o Part of a segmented string structure being missing. This problem occurred in Rdb 6.1 and 7.0. When multiple users were doing select queries before inserting or deleting data there was a window when corruption of indices and segmented strings could occur. A full verify of the database would identify the corruption. If an index becomes corrupt then the only workaround is to delete and redefine the index. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2-10 Software Errors Fixed 2.1.12 Excessive I/O during an insert into a hashed index Bug 387602 There were some cases where a single insert into a hash index incurred excessive I/O. This could be seen through RMU/SHOW STATISTICS. This was the result of attempts at space reclamation within a hash bucket chain. Hash bucket fragments were marked in order to reclaim space and each hash bucket then had to be journalled. The longer the hash bucket chain the more I/Os that were incurred. There is no workaround to this problem. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.13 Excessive SPAM fetches during a single update/insert & delete Bug 359926 When using one big table to update/insert and delete from other tables, excessive spam fetches occurred. In a uniform area, when the logical area becomes full, Oracle Rdb would walk the SPAM intervals again and again only to find out that another clump needs to be allocated. This resulted in excessive spam fetches. The workaround is to partition the afflicted tables horizontally i.e. split one big logical area into multiple smaller logical areas. This will reduce the number of SPAM pages to be searched. Alternatively, place the tables in an area with a smaller page size. This will reduce the SPAM interval. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.14 Random arithmetic trap errors during RMU/LOAD Bug 418775 During an RMU/LOAD command when a sorted ranked index is being built ARITH_EXCEPT errors could occur and the RMU/LOAD operation would be terminated. Software Errors Fixed 2-11 $ RMU/LOAD /CORRESPONDING/COMMIT=1000/NOCONSTRAINTS/RECORD=(FILE=COT.RRD) - /TRANSACTION_TYPE=EXCLUSIVE/PLACE FX CONFO_OUT_TRADE COT.DAT %RMU-I-LOADERR, Error loading row 74. %RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime 1SYSTEM-F-HPARITH, high performance arithmetic trap, Imask=00000000, Fmask=0000 000, summary=02, PC=0121AA90, PS=0000000B -SYSTEM-F-FLTINV, floating invalid operation, PC=0121AA90, PS=0000000B The workaround is to use a non-ranked sorted index. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.15 Monitor Log File Fragmentation In the past the monitor log file has grown in small increments as the file was written. This growth behaviour contributed to disk and file fragmentation and also caused additional I/O overhead for the disk where the log file is located. The default monitor log allocation and extension quantity is now 512 blocks. Furthermore a logical name RDM$MON_ LOG_EXTEND_SIZE may now be used to increase the monitor log file allocation and extension quantity. Define the logical name RDM$MON_LOG_EXTEND_SIZE system-wide with an integer value to specify the monitor log file allocation and extension quantity. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.16 Rollback After Delete Could Bugcheck In certain combinations of locked and free space on a database page, a rollback of row deletions could result in bugchecks in the routine RUJUTL$ROLLBACK_LOOP and possibly cause process deletion. The following script shows how this problem could occur: $ @RDM$DEMO:PERSONNEL SQL M NOCDD "" SYS$DISK:[] $ MCR SQL$ SQL> ATTACH 'FILE MF_PERSONNEL'; SQL> DELETE FROM EMPLOYEES; 100 rows deleted SQL> ROLLBACK; %RDMS-I-BUGCHKDMP, generating bugcheck dump 2-12 Software Errors Fixed Additional information about the length of an erased row is now stored in the RUJ file in order to help the rollback operation be able to make better use of the locked space on a database page. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.17 CONVERT_ERROR or ARITH_EXCEPT from SELECT with GROUP BY when no data in underlying table Bug 418444 In prior releases of Rdb it was possible to receive an exception such as RDB-E-CONVERT_ERROR or RDB-E-ARITH_EXCEPT when processing a SELECT that (directly or indirectly via a view) contained a GROUP BY. For this to occur, the GROUP BY had to reference an expression involving DATE data types or certain string operations such as SUBSTRING, and there had to be no data in the table contributing to the row data that was to be partitioned by the GROUP BY. In effect, certain actions related to the partitioning were still occurring even if there was no data. The following example displays a view and the SELECT expression which results in a convert error. create table ttest (d integer,i integer); create view test_view as select sum(i) as sumi, cast(cast(cast(d as char(8)) as date vms) as date ansi) as d1 from ttest group by d; select sum(sumi) from test_view group by d1; %RDB-E-CONVERT_ERROR, invalid or unsupported data conversion -COSI-F-IVTIME, invalid date or time One workaround for this problem it to insert NULL values into the columns, although this may be not be feasible for some applications. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. If there is no data to be partitioned, none of the GROUP BY actions will occur, and the SELECT will return the expected result of '0 rows selected'. Software Errors Fixed 2-13 2.1.18 Process deletion after several date expressions In some databases certain SQL statements which used several date expressions could cause process deletion. An example of such an expression is: select * from dem_trt_delai where cast(maj_date as date ansi) + cast (annee_numero as interval year) + cast(mois_numero as interval month) + cast(jour_numero as interval day) between cast(maj_date as date ansi) and cast(maj_date as date ansi); This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.19 Hot Standby Database Attributes Lost Preconfigured Hot Standby database attributes were overwritten with hard-coded default values when the /Master_Root or /Standby_Root qualifier was specified when restarting Database Replication. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.20 ALTER INDEX changes HASHED ORDERED to HASH SCATTERED When ALTER INDEX is used to change the partitioning of a HASHED ORDERED index, it incorrectly recreates the partitions as HASHED SCATTERED. This can be verified by using the RMU Dump Larea=RDB$AIP command in Rdb V6.1 and later. The SQL SHOW INDEX, or SHOW TABLE (INDEX) commands still report the index as HASHED ORDERED. The data is still intact, and only the access method is affected by this problem. The effect will be that the hash bucket placement will not be as expected by the database designer. A work around is to use DROP INDEX and CREATE INDEX instead of the ALTER INDEX command. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2-14 Software Errors Fixed 2.1.21 ALTER STORAGE MAP does not reorganize data If a storage map defines a table as PARTITIONING IS NOT UPDATABLE (also known as a strictly partitioned table) then the ALTER STORAGE MAP should reorganize the data to maintain the strictly partitioned nature of the data. This is not currently performed by Rdb7. This may lead (after the ALTER STORAGE MAP) to range retrieval queries returning too little data when a sequential scan of the partitions is performed. The workaround is to include the REORGANIZE AREAS option when performing changes to the partitioning. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. When ALTER STORAGE MAP is executed on a strictly partitioned storage map and storage areas are added, dropped or changed then the REORGANIZE AREAS clause is implicit. 2.1.22 SQLCODE/SQLSTATE incorrectly propagated from SQL functions and procedures When an SQL stored function or procedure is called it returns to the caller's environment warnings generated within the stored function. In Rdb7 SQLSTATE and SQLCODE were incorrectly being set to any END OF DATA conditions from within the called stored routine. This may cause queries executing under the 'SQL92', or 'ORACLE LEVEL1' dialects which use SQL functions to terminate prematurely. The END OF DATA state from the called stored routine was incorrectly being used to terminate the query which called the function. For example, the query shown below should always return one row, not zero rows. Software Errors Fixed 2-15 SQL> create module m_sqlcode cont> language SQL cont> cont> function f_end_of_data () returns integer; cont> begin cont> declare :x integer; cont> select 1 into :x from employees where employee_id = 'xxxxx'; cont> return -1; cont> end; cont> cont> end module; SQL> SQL> select 'value' from rdb$database; value 1 row selected SQL> SQL> select f_end_of_data () from rdb$database; 0 rows selected SQL> This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. SQL stored functions and procedures no longer return the END OF DATA codes (i.e. SQLCODE 100 and SQLSTATE '02000') to the caller. Other warnings (SQLSTATE values starting with '01') such as 'String truncation during assignment' and 'NULL eliminated in aggregate' are still propagated to the caller. 2.1.23 ALTER INDEX waits too long to validate storage area name When ALTER INDEX processed a new index storage map it did not validate the storage area names until it had processed some of the data. This caused wasted time during execution and during rollback of the failed map. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. Oracle Rdb now validates the storage areas during the initial processing phase for the index, and before it starts performing I/O to alter the index. 2-16 Software Errors Fixed 2.1.24 RMU Extract generates incorrect keyword for PARTITIONING clause Bug 430928 The PARTITIONING IS NOT UPDATABLE clause was extracted by RMU Extract with a spelling error. The workaround is to manually correct the spelling of the word from 'updateable' to the correct spelling 'updatable'. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.25 Unexpected RDMS$_WISH_LIST generated by ALTER DATABASE ... DROP STORAGE AREA CASCADE When the clause DROP STORAGE AREA CASCADE is issued as part of the ALTER DATABASE statement it may generate an unexpected RDMS$_WISH_LIST error with no explanation as to the cause of the problem. For example, SQL> alter data file db$:vrp_test drop storage area area cascade; %RDB-E-NO_META_UPDATE, metadata update failed -RDMS-E-WISH_LIST, feature not implemented yet This error is raised when an attempt is made to alter a vertically partitioned storage map (i.e. a STORAGE MAP which includes the STORE COLUMNS clause). This type of map can not currently be altered explicitly using ALTER STORAGE MAP (as documented in the Rdb7 documentation), nor can they be implicitly altered using the DROP STORAGE AREA CASCADE of the ALTER DATABASE statement. This last point was unfortunately not documented in the Rdb7 documentation. For this ECO to Rdb7 an alternate message is raised which names the storage map causing the problem. SQL> alter data file db$:vrp_test drop storage area area cascade; %RDB-E-NO_META_UPDATE, metadata update failed -RDMS-E-VRPINVALID, invalid operation for storage map "M6" The description for this error states that the map is a vertically partitioned map and may not be altered. Oracle recommends that vertically partitioned tables not be mapped to storage areas shared by other tables or indices as this will preclude the use of DROP STORAGE AREA CASCADE for those other tables. Software Errors Fixed 2-17 2.1.26 Vertically partitioned tables allocate space for COMPUTED BY columns When a table is vertically partitioned (i.e. columns from the same table are stored in different storage areas), then Rdb7 incorrectly allocated storage space which included space for COMPUTED BY columns. COMPUTED BY columns do not require disk space so this is wasted space. This problem does not exist for tables which are not vertically partitioned. This problem is corrected in this ECO for Rdb7. However, any existing tables which match these characteristics will need to be rebuilt. The old format of the data is incorrect and any partition which contains COMPUTED BY columns (usually the last) will not be read correctly after this ECO is installed. Any table which has COMPUTED BY columns and which is also vertically partitioned, i.e. has a storage map containing the STORE COLUMNS clause will need to be unloaded, recreated and then reloaded. Please use RMU Unload (or another application) to unload the data before this ECO is installed, then drop the table. After the ECO is installed, you can then recreate the storage map and reload the saved data. The new on-disk image will now contain only those columns which contain data and should occupy less disk space. 2.1.27 VARCHAR data in vertically partitioned tables not saved correctly When a table is vertically partitioned (i.e. columns from the same table are stored in different storage areas), Rdb was not copying data for VARCHAR columns correctly to the target partition. The last two bytes of the VARCHAR data may be corrupted when read by a query. This problem is corrected in this ECO for Rdb7. However, any existing tables which match these characteristics (vertically partitioned and containing VARCHAR columns) will need to be reloaded. Note that in the case where the VARCHAR data is smaller than the declared VARCHAR length no corruption occurs. 2-18 Software Errors Fixed This problem does not affect any other data type or COMPUTED BY columns of VARCHAR type. 2.1.28 ALTER TABLE not fully supported for Vertically Partitioned tables When a table is vertically partitioned (i.e. columns from the same table are stored in different storage areas) the ALTER TABLE statement can corrupt the on disk stored data. Specifically, if the ALTER TABLE changes a data type, adds or removes columns then the data from the vertical record partitions stored before the ALTER are not correctly processed. This problem is corrected in this ECO for Rdb7. However, any existing tables that have been altered should be reloaded. i.e. the table dropped, recreated and reloaded. 2.1.29 COSI-F-EXQUOTA seen when creating storage map with many columns in STORE COLUMNS clause Bug 429969 In Oracle Rdb7, when creating a storage map that vertically partitions a table and stores many columns in a particular partition, virtual memory could be exhausted. The following error may be seen COSI-F-EXQUOTA. The following example shows this error on a storage map with many columns (75) being stored in the first partition. create storage map TABLE_MAP for TABLE_A store columns (COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5...COLUMN75) in AREA1 store columns (COLUMN76, COLUMN77) in AREA2; %COSI-F-EXQUOTA, exceeded quota -SYSTEM-F-VASFULL, virtual address space is full This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. The create storage map command now executes successfully for any number of columns. Software Errors Fixed 2-19 2.1.30 Problems corrected for Sequential Scan In Rdb7 a sequential scan of a partitioned table (i.e. a table with a storage map across multiple areas) which is defined (or defaulted) as PARTITIONING IS UPDATABLE will sometimes not scan all partitions. This means that some data is not returned for the query. The first execution of the query will correctly scan all storage areas in the map. However, subsequent executions of the query will not perform a complete scan. Examples of multiple executions include: multiple calls to a stored procedure, re-opening a cursor, and executing the same SQL module language or precompiler query multiple times. There is no workaround for this problem. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.31 Problems corrected for Strict Partitioning Bug 429969 When a table's storage map has the attribute PARTITIONING IS NOT UPDATABLE then mapping of data to a storage area is strictly enforced. This is known as strict partitioning. This ECO to Rdb7 corrects several problems with the strict partitioning functionality. 1. Table cardinality incorrectly updated after a partial sequential scan. When a full table scan is performed, Rdb takes the opportunity to collect and update the table cardinality. However, when strict partitioning is used only a subset of the table data is scanned, and so an incorrect cardinality could be applied. This is corrected by this ECO. The cardinality is not updated for a partial sequential scan. However, RMU/COLLECT should be executed on the strictly partitioned tables to ensure that the cardinality is set correctly. 2. Incorrect partition selection when columns have type VARCHAR. 2-20 Software Errors Fixed If the query provided VARCHAR values (either literals, column values or host variables) the last two bytes of the value were not used correctly for partition selection. This was only a problem if the data value was equal or within one character of the VARCHAR declared length. This problem has been corrected in this ECO. The full VARCHAR selection value is used. No other data types were affected by this problem. If VARCHAR columns were used for strict partitioning then the table should be reloaded. 3. Strict Partitioning not used for LEFT, RIGHT or FULL OUTER joins. Bug 430931 When an outer join query was used, then strict partitioning was disabled for sequential scans. This meant that more I/O was required to solve these joins than was needed. This problem has been corrected in this ECO. Where possible, strict partitioning will be used when both an outer join and a sequential retrieval is required. However, adding a SORTED index may result in less I/O and faster response time for such queries. _____________________ Restriction _____________________ When an OUTER JOIN is executed with a USING clause SQL generates hidden CASE expressions to select the non-NULL values for the USING columns. For example, the reference to EMPLOYEE_ID in the WHERE clause references EMPLOYEE_ID from either the JOB_HISTORY or the EMPLOYEES table. select count(*) from employees full outer join job_history using (employee_id) where employee_id < '00200'; These hidden conditional expressions currently disable the strict partitioning selection. Therefore, if you are using strict partitioning you may need to qualify the column name fully and use the ON clause to Software Errors Fixed 2-21 enable strict partitioning. The example query could be recoded as shown: select count(*) from employees e full outer join job_history jh on e.employee_id = jh.employee_id where e.employee_id < '00200' or jh.employee_id < '00200'; This restriction will be documented in the Oracle Rdb documentation in a future release. ______________________________________________________ 4. Multiple table references interfere with partition selection. If a single query referenced a table multiple times, or multiple concurrent queries referenced the same table and sequential retrieval is used then an incorrect set of partitions may be scanned. This occurred because the various table references interfered with the correct partition selection. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 5. If a strictly partitioned table is defined with a storage map with no OTHERWISE clause then some queries would fail with an error, instead of executing correctly. This happened when a range query included an upper limit which was outside the maximum WITH LIMIT clause of the storage map. SQL> select name cont> from T cont> where badge between 8 and 10; Conjunct Get Retrieval sequentially of relation T %RDMS-E-EXCMAPLIMIT, exceeded limit on last partition in storage map for T 2-22 Software Errors Fixed This query now works as expected with this ECO to Rdb7. SQL> select name cont> from T cont> where badge between 8 and 10; Conjunct Get Retrieval sequentially of relation T (partitioned scan#1) NAME i j 2 rows selected SQL> 6. Changes to STRATEGY ("S") and EXECUTION ("E") debug flags output Either the SET FLAGS statement or the logical RDMS$DEBUG_FLAGS (RDB_DEBUG_FLAGS on Digital UNIX) can be used to enable the STRATEGY or EXECUTION tracing during query evaluation. If a table is strictly partitioned and the query allows the use of this feature then the STRATEGY dump now includes the text "(partitioned scan#nn)" after the table name. The #nn indicates the leaf number for this sequential scan (there may be several within a single query). For instance, select name from T where badge between 8 and 10; ~S#0045 Conjunct Get Retrieval sequentially of relation T (partitioned scan#1) ~E#0045.1: Strict Partitioning using 2 areas partition 9 (larea=57) partition 10 (larea=58) NAME i j 2 rows selected Software Errors Fixed 2-23 For this type of sequential retrieval the EXECUTION trace now includes a count and a list of the selected partitions each time the query is executed. Note that these examples use constant values for the partitioning columns, however, in an application these values are likely to vary and be supplied in host variables. Therefore, the selected partitions may change from one execution to the next. The ~E#0045 in the example indicates an EXECUTION trace for the query numbered 0045-this corresponds to the ~S#0045 value printed by the STRATEGY dump. The .1 indicates the leaf number and is used to associate the EXECUTION trace with a specific part of the query identified by the "(partitioned scan#1)" notation. 2.1.32 When page transfer via memory is enabled read only transactions may fail with SYS-F-NOPRIV error Bug 408761, Bug 432133 When page transfer via memory is enabled and read only transactions are running concurrently with read write transactions some read only transactions may fail with the following error message : %RDB-F-IO_ERROR, input or output error -RDMS-F-CANTWRITEDBS, error writing pages x:x-x -SYSTEM-F-NOPRIV, no privilege for attempted operation The only workaround is to disable page transfer via memory. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.33 Spam Pages Could be Incorrect after Recovery After an RMU/RECOVER operation or a rollback operation when the Fast Commit option is enabled, it was possible that the SPAM page was not correctly updated to reflect a change of free space on a page. 2-24 Software Errors Fixed The following example shows how this problem could be seen: $RMU/RESTORE/NORECOVER MYRBF $RMU/RECOVER/ROOT=MYDB.RDB MYAIJ.AIJ $RMU/VERIFY/ALL MYDB.RDB %RMU-W-PGSPMCLST, area MYAREA, page 2205 the 70% fullness value for this data page does not fall within the 96-100% range found on the space management page As a workaround, use RMU /REPAIR /SPAMS command to correct the SPAM information. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.34 RMU/Load/Place forces constraint evaluation When the Place qualifier is used on RMU Load it forces constraints to be loaded and executed during loading, even if the NoConstraints qualifier was used. The Place option does a first pass through the data to calculate the probable target DBKEY for each data row when using a PLACEMENT VIA INDEX mapping. The data is then sorted in this DBKEY order to ensure a sequential updating of pages in the target area. The goal is to reduce random I/O during the load operation and reduce the load time. The NoConstraints option was only disabling constraints during the second load pass, unfortunately the first pass had already loaded and enabled the constraints. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. The first pass to calculate the target DBKEY will no longer cause the constraints and triggers to be loaded and enabled. 2.1.35 Loss of entries in ranked indexes with duplicates Bug 440823 Rdb 7.0 sorted ranked index containing duplicates may lose duplicate entries when the index is built from existing records within a table. i.e. when CREATE INDEX is issued after there are records already in the table on which the index is being created. In addition incorrect results may be returned when the index is used for access. Software Errors Fixed 2-25 RMU/ANALYZE may be used to determine if any entries have been lost after a ranked index build. The following example shows how RMU/ANALYZE will show this error: RMU-I-BTRDUPCAR, Inconsistent duplicate cardinality (C1) of 2 for entry 1 at dbkey 7:125:0. Actual count of duplicates is 35 . %RMU-I-BTRERPATH, parent B-tree node of 7:125:0 is at 7:125:3 %RMU-I-BTRHEACAR, Sum of entry cardinalities given as 146 ; expected 12 %RMU-I-BTRNODDBK, Dbkey of B-tree node is 7:128:2 %RMU-I-BTRENTCAR, Inconsistent entry cardinality (C1) of 146 specified for entry 13 at dbkey 7:125:3 using precision of 33. Dbkey 7:128:2 at level 1 specified a cardinality of 12. %RMU-W-IDXDATMIS, Index I_D1 does not point to a row in table D1. Logical dbkey of the missing row is 95:2:3. %RMU-W-IDXDATMIS, Index I_D1 does not point to a row in table D1. Logical dbkey of the missing row is 95:3:7. ... There are two possible workarounds: Rebuild the index as a normal sorted index ( not ranked ) OR build the index on an empty table and then insert the records into the table. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.36 WAIT FOR CLOSE clause SHOWS incorrect information and does not IMPORT correctly. Bugs 428306 and 428308 In Rdb 7.0, the OPEN IS AUTOMATIC (WAIT n MINUTES FOR CLOSE) clause of a CREATE or ALTER DATABASE statement did not SHOW the correct information. Specifically, a SHOW DATABASE displayed the WAIT period in seconds rather than minutes. Additionally, the IMPORT operation did not correctly import the WAIT clause. 2-26 Software Errors Fixed The following example shows a database created with the OPEN IS AUTOMATIC (WAIT 5 MINUTES FOR CLOSE) clause. A SHOW DATABASE shows the WAIT clause in minutes not seconds. CREATE DATABASE FILENAME WAIT_CLAUSE OPEN IS AUTOMATIC (WAIT 5 MINUTES FOR CLOSE); SHOW DATABASE * Default alias: Oracle Rdb database in file WAIT_CLAUSE Multischema mode is disabled Number of users: 50 . . Mode is Open Automatic (Wait 300 minutes for close) The following example shows the IMPORT operation does not work correctly for the WAIT CLAUSE. A SHOW DATABASE shows the WAIT CLAUSE as 0 minutes rather than 5 minutes: ATT 'FILE WAIT_CLAUSE'; EXPORT DATA FILE WAIT_CLAUSE INTO WAIT_CLAUSE_TMP; DISCONNECT ALL; IMPORT DATA FROM WAIT_CLAUSE_TMP FILE WAIT_CLAUSE; Exported by Oracle Rdb X7.0-00 Import/Export utility A component of Oracle Rdb SQL X7.0-00 . . Open is Automatic, Wait period is 5 minutes . . Mode is Open Automatic (Wait 0 minutes for close) There is no workaround. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.37 Match keys with constant equality return wrong number of rows Bug 428374 Wrong number of rows would be returned by a select query where the match keys has constant equality predicates. The following query should return 2000 for both columns, but return NULL for the second column: Software Errors Fixed 2-27 select (qty * price), (select sum (t2.qty * t2.price) from t2 t2 where t2.f1 = t1.f1 and t2.f2 = t1.f2) from t1 t1 where t1.f1 = 'K1001' AND t1.f2 = 1; 2000 NULL 1 row selected The following example shows the SQL script to reproduce the problem: $SQL$ attach 'file personnel'; CREATE TABLE T1 (F1 CHAR(5), F2 INTEGER, QTY INTEGER, PRICE INTEGER); CREATE INDEX T1_NDX ON T1 (F1, F2); insert into t1 value ('K1000', 1, 100, 20); insert into t1 value ('K1001', 1, 100, 20); insert into t1 value ('K1002', 1, 100, 20); insert into t1 value ('K1003', 1, 100, 20); update rdb$relations set rdb$cardinality = 5000 where rdb$relation_name = 'T1'; COMMIT; CREATE TABLE T2 (F1 CHAR(5), F2 INTEGER, QTY INTEGER, PRICE INTEGER); insert into t2 value ('K1002', 1, 100, 20); insert into t2 value ('K1001', 1, 100, 20); insert into t2 value ('K1003', 1, 100, 20); ! this should select 2000 for both columns select (qty * price), (select sum (t2.qty * t2.price) from t2 t2 where t2.f1 = t1.f1 and t2.f2 = t1.f2) from t1 t1 where t1.f1 = 'K1001' AND t1.f2 = 1; 2-28 Software Errors Fixed WORKAROUND: None. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.38 RMU/SHOW STATISTIC bugcheck following cancelled sub-menu selection Bug 450888 It is possible for the RMU/SHOW STATISTIC utility to bugcheck when a sub-menu selection is cancelled. Here is an example of the exception from the bugcheck: Exception: Exception at 004290F0 : KUTDIS$SETUP_COMMON + 00000470 Reason : %SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=00000000, PC=0013D808, PSL=03C00000 The following example shows how to reproduce this problem: 1. select SUMMARY OBJECT STATISTICS 2. Select MENU 3. Select LOCKING ONE STAT FIELD 4. CTRL/Z from locking menu. 5. Press left arrow <- The only work-around is to not cancel a sub-menu selection. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. Cancelling a sub-menu selection now works correctly. 2.1.39 RMU/RECOVER of optimized AIJ journal does not preserve sequence# The RMU/RECOVER utility does not properly preserve the "next" AIJ sequence number following recovery of an optimized AIJ journal. This problem occurs when an AIJ file containing multiple sequence numbers is optimized. The only work-around is to use the original, unoptimized AIJ journal. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. The RMU/OPTIMIZE/AFTER_JOURNAL and RMU/RECOVER utilities have been corrected to properly initialize the next AIJ sequence number following roll-forward completion. Software Errors Fixed 2-29 2.1.40 Subquery of IN clause with both DISTINCT and LIMIT TO returned too few rows In some cases when the IN clause used a SELECT subquery the wrong number of rows were returned for the query. This occurred when both the DISTINCT and the LIMIT TO clause were applied in the subquery. This is shown in the following example: SQL> attach 'file MF_PERSONNEL'; SQL> SQL> -- first shown that the subquery should return 2 rows SQL> SQL> select distinct employee_id from job_history cont> order by 1 limit to 2 rows; EMPLOYEE_ID 00164 00165 2 rows selected SQL> SQL> -- expect to get two rows from this query (matching SQL> -- the two values reported above), but only get one SQL> SQL> select employee_id, last_name from employees where cont> employee_id in (select distinct employee_id from job_history cont> order by 1 limit to 2 rows); EMPLOYEE_ID LAST_NAME 00164 Toliver 1 row selected SQL> In general the IN clause doesn't need to perform a DISTINCT because existence in the set of returned values is sufficient for the query. Therefore, the Rdb optimizer removes the DISTINCT from these types of subqueries and so avoids a sorting step. However, when the source row set is restricted using the LIMIT TO clause then the DISTINCT is required to guarantee that the result is restricted correctly. This problem was corrected in Oracle Rdb7. This release note was missing from the Oracle Rdb Release notes. 2-30 Software Errors Fixed 2.1.41 Database Recovery Processes Bugcheck at DBR$RESOLVE In certain combinations of locked and free space on a database page, a database recovery (DBR) process rollback operation could result in bugchecks in the routine DBR$RESOLVE. Because the DBR process failure results in a forced database shutdown, the workaround is to restore and recover the database. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. Additional information about the length of an erased row is now stored in the RUJ file in order to help the rollback operation be able to make better use of the locked space on a database page. 2.1.42 Minimum NODE SIZE for SORTED indices not calculated correctly When creating a UNIQUE index with a type of SORTED RANKED an error could be generated if specifying the minimum node size. The minimum node size can be calculated using the formula provided in the Rdb documentation. SQL> create table T cont> (a integer, cont> b char(4)); SQL> SQL> create unique index I2 on T (a) cont> type is SORTED RANKED cont> node size 101; %RDB-E-NO_META_UPDATE, metadata update failed -RDB-E-IMP_EXC, facility-specific limit exceeded -RDMS-F-INDEX_S_MIN, user requested node size of 101 bytes for index needing 104 In this case the specified NODE SIZE was correct but was rejected by Rdb7. For non-UNIQUE indices with type of SORTED RANKED the minimum node size was not calculated correctly and so indices may be created with a node size smaller than the minimum. This may lead to poor index performance and possible INSERT and UPDATE failures. In addition the NODE SIZE specified on an ALTER INDEX statement was not validated by Rdb. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. Software Errors Fixed 2-31 2.1.43 RMU/BACKUP/AFTER_JOURNAL "File Already Exists" Failure When a single extensible AIJ journal is created on OpenVMS using an explicit file version number, it is possible for subsequent AIJ backup operations to fail with a "file already exists" error. This problem only occurs when using a single, extensible AIJ journal. The following example shows the creation of an AIJ journal with an explicit file version number. $ rmu/set after/enable/add=(name=tst, file=TST_AIJ.AIJ;1, alloc=512) tst The following example shows the AIJ backup failure that can result following the creation of an AIJ journal with an explicit file version number. $ rmu/back/after/log tst tst_aij.aij_bck ! backup KO %RMU-I-AIJBCKBEG, beginning after-image journal backup operation %RMU-I-OPERNOTIFY, system operator notification: Oracle Rdb Database RDB$ROOT2:[70]TST.RDB;1 Event Notification AIJ backup operation started %RMU-I-AIJBCKSEQ, backing up after-image journal sequence number 1 %RMU-I-LOGBCKAIJ, backing up after-image journal TST at 09:44:26.66 %RMU-I-LOGCREBCK, created backup file RDB$ROOT2:[70]TST_AIJ.AIJ_BCK;2 %RMU-W-AIJDEVDIR, AIJ filename "TST_AIJ.AIJ;1" does not include a device/directory %RMU-I-AIJBCKSTOP, backup of after-image journal TST did not complete %RMU-F-FILACCERR, error creating after-image journal file _CHSR36$DKA500:[JSUBRI.70]TST_AIJ.AIJ;1 -RMS-E-FEX, file already exists, not superseded %RMU-F-FTL_BCK, Fatal error for BACKUP operation at 19-FEB-1997 09:44:27.10 There are several work-arounds to avoid this problem. Do not specify an explicit file version number when creating an AIJ journal. Use multiple circular AIJ journals instead of a single extensible AIJ journal. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. The AIJ backup operation correctly creates new versions of AIJ journals that were originally created with explicit file version numbers. 2-32 Software Errors Fixed 2.1.44 RMU/SHOW STATISTIC "Alarm Bell" rings excessively for long stalls When a very long stall occurs, such as when an AIJ backup occurs, the RMU/SHOW STATISTIC utility alarm bell feature is not very useful, as it is constantly ringing. There is no work-around to this problem, other than disabling the alarm bell. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. When the RMU/SHOW STATISTIC utility alarm bell feature is enabled, the alarm bell can be configured to ring continuously or selectively. When in "continuous" mode, the default setting, the alarm bell rings whenever any stall exceeds the specified alarm threshold. This is equivalent to how the alarm bell previously worked. When in "selective" mode, the alarm bell only rings when a new stall becomes the oldest stall to exceed the specified alarm threshold. Therefore, in the case of the long-running AIJ backup stall, the alarm bell will only ring once until the stall terminates and a new stall exceeds the alarm threshold. The RMU/SHOW STATISTIC utility also now allows you to select one or more "classes" of stalls which control how the "Alarm Bell" facility and the "Alarm Notification" facility operate. Currently, there are ten stall classes from which to choose: 1. Record Stalls 2. Page Stalls 3. Table Stalls 4. Storage Area Stalls 5. Database File Stalls 6. AIJ/RUJ Journal Stalls 7. Transaction Stalls 8. Hot Standby Stalls Software Errors Fixed 2-33 9. Database Stalls 10. Miscellaneous Stalls By default, all stall classes are selected. Stall classes can be selected using the "Tools Menu", which is obtained using the "!" key. When either the "Alarm Bell" facility or the "Alarm Notification" facility is enabled, the "Tools Menu" will contain an "Alarm Classes" sub-menu option. Selecting the "Alarm Classes" option will display a sub- menu containing all of the available stall classes. Stall classes that have been selected are identified by a "->" designation. Stall classes that have been de-selected do not have this designation. To de-select a previously selected stall class, or to select a previously de-selected stall class, simply choose it from the sub-menu. The "Alarm Classes" sub-menu will continue to be displayed until you either type "CTRL-Z" to terminate it, or de-select all but the last stall class. ________________________ Note ________________________ You cannot de-select all stall classes. When you have de-selected all but one stall class, the "Alarm Classes" sub-menu will automatically cancel itself. ______________________________________________________ 2.1.45 Floating divide by zero in query with constant expression Bug 450934 2-34 Software Errors Fixed ! The following query should select one record of employee ! with id = '00169' but it generates ! a floating divide by zero error. SELECT * FROM employees WHERE ( EMPLOYEE_ID = '00169' AND ( ( 1 = 1 ) OR ( 1 <> 2 AND LAST_NAME = 'Toliver' ) ) ); The problem is caused by the optimizer code where it computes the duplicity factor of the table by dividing the table cardinality by the distinct cardinality. The distinct cardinality becomes zero when the selectivity of the constant expression defaults to zero at the query compilation time, and thus generates the divide by zero error. There is no workaround for this problem. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.46 Memory leak with sort when cursor closed before end of stream Bug 420945 Previously, if a cursor was closed before it had reached the end of stream and a sort occurred during its execution, sixteen byte chunks of virtual memory were not properly released. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. Software Errors Fixed 2-35 2.1.47 TRANSLATE() Function Returns Corrupted Data Instead of White Space Bug 423056 When using DEC_Kanji to DEC_MCS conversion, the Translate() function would return corrupted characters. This problem occurred if you passed an untranslatable character to the translate() function (ie:Japanese punctuation characters). This should return a single-octet of white space code instead. The following is an example of the "bad" output. SQL> select _dec_kanji'¡¦¡«¡¬', cont>translate(_dec_kanji'¡¦¡«¡¬' using rdb$dec_mcs) cont>from dual; ¡¦¡«¡¬ ¥Þß 1 row selected The following demonstrates what the code now returns. SQL> select _dec_kanji'¡¦¡«¡¬', cont>translate(_dec_kanji'¡¦¡«¡¬' using rdb$dec_mcs) cont>from dual; ¡¦¡«¡¬ 1 row selected This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.48 ACCVIO During Remote Replication Bug 423056 An ACCVIO would occur while performing a remote replication with the Replication Option for Oracle Rdb when the replicator detached from the database. The problem was only observed on ALPHA but could potentially happen on VAX as well. Here is an example of the problem: 2-36 Software Errors Fixed SQL> crea trans_decnet type is replication move table emp to EXISTING filename chsr36"user pass"::W2:[JSUBRI.70]DDAL70.RDB log 'W2:[JSUBRI.70]DDAL70.LOG_decnet'; SQL> start trans trans_decnet now; 15:10:59 %DDAL-I-LOATABDAT, loading data for table EMP 15:10:59 %DDAL-I-TAB_STATS, TABLE EMP %DDAL-I-ROWSFMSRC, 14 rows copied from RDB$ROOT2:[70]MF_PERSO %DDAL-I-ROWSTOTGT, 14 rows copied into chsr36"proxy"::W2:[JSU 15:10:59 %DDAL-I-CREATEIDX, creating index DDAL$DBKEY_INDEX1_2 15:10:59 %DDAL-I-LOATABDAT, loading data for table RDB$VINTAGE ----- 14-JAN-1997 15:11:00.20 ----- Error ------------------------- %SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=0000000000000000, PC=0000000000000000, PS=0000001B This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.1.49 Row Cache screen shows extraneous characters where cache name exceeds 21 characters Bug 444581 In the Row Cache (insert cache) RMU/SHOW STATISTICS screen (option U followed by G), where a cache name exceeds 21 characters, the last 3 characters of a cache name remain on the screen when the caches are paged through using the up & down arrow keys. The workaround is to reset the screen with R which removes the extraneous characters. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. 2.2 Documentation Corrections 2.2.1 Documentation Order Number Corrected The order number for the library set for Oracle Rdb7 for OpenVMS is listed incorrectly in the V7.0 Release Notes. The correct number is A45807-1. Software Errors Fixed 2-37 2.2.2 Documentation Corrections for Rdb7 The Oracle Rdb SQL Reference Manual for Rdb7 contained an error in the syntax diagram when describing the SET FLAGS and the CREATE STORAGE MAP statements. This release note corrects the syntax as well as provides more examples for these statements. 2.2.2.1 Incorrect syntax for SET FLAGS statement The SET FLAGS statement allows enabling and disabling of database system debug flags for the current session. The literal or host variable passed to this command can contain a list keywords, or negated keywords separated by commas. Spaces are ignored. The keywords may be abbreviated to an unambiguous length. ________________________ Note ________________________ Oracle Corporation reserves the right to add new keywords to any release or update to Oracle Rdb which may change this unambiguous length. Therefore, it is recommended that the full keyword be used in applications. ______________________________________________________ The syntax for this SET statement is show below. SET FLAGS --+--> literal --------+--> | | +--> host-variable --+ SET NOFLAGS ------------------------> The SET NOFLAGS statement disables all currently enabled flags. It is equivalent to SET FLAGS 'NONE'. The following examples demonstrate the use of host variables in interactive SQL, as well as literal strings with multiple options to enable and disable flags. The same SET FLAGS commands can also be used in dynamic SQL. 2-38 Software Errors Fixed SQL> show flags Alias RDB$DBHANDLE: Flags currently set for Oracle Rdb: PREFIX SQL> -- declare a host variable to be used with SET FLAGS SQL> declare :hv char(40); SQL> -- assign a value to the variable SQL> begin cont> set :hv = 'strategy, outline'; cont> end; SQL> -- use the host variable to enable or disable flags SQL> set flags :hv; SQL> show flags Alias RDB$DBHANDLE: Flags currently set for Oracle Rdb: STRATEGY,PREFIX,OUTLINE SQL> -- use a string literal directly with the SET FLAGS statement SQL> set flags 'noprefix,execution(10)'; SQL> show flags Alias RDB$DBHANDLE: Flags currently set for Oracle Rdb: STRATEGY,OUTLINE,EXECUTION(10) Please refer to the Oracle Rdb SQL Reference Manual for further examples. 2.2.2.2 Incorrect Syntax for CREATE STORAGE MAP The syntax diagram the partition-clause incorrectly indicated that the STORE keyword was not repeated. When creating a vertically partitioned table you must repeat the STORE keyword for each partition. partition-clause = -+-> STORE +------------------+-+---------------------+-> store-clause -+-> | | | | | | | +-> columns-clause-+ +-> store-attributes -+ | | | +----------------------------------------------------------------------+ Software Errors Fixed 2-39 The following storage map shows an alternate mapping for the EMPLOYEES table in the same MF_PERSONNEL database. SQL> create storage map EMPLOYEES_MAP cont> for EMPLOYEES cont> placement via index EMPLOYEES_HASH cont> -- store the primary information horizontally partitioned cont> -- across the areas EMPIDS_LOW, EMPIDS_MID and EMPIDS_OVER cont> -- disable compress because these columns are accessed often cont> store cont> columns (EMPLOYEE_ID, LAST_NAME, cont> FIRST_NAME, MIDDLE_INITIAL) cont> disable compression cont> using (EMPLOYEE_ID) cont> in EMPIDS_LOW cont> with limit of ('00200') cont> in EMPIDS_MID cont> with limit of ('00400') cont> otherwise in EMPIDS_OVER cont> cont> -- place all the address information in EMP_INFO cont> -- make sure these character columns are compressed cont> -- to remove the trailing spaces cont> store cont> columns (ADDRESS_DATA_1, ADDRESS_DATA_2, CITY, STATE, cont> POSTAL_CODE) cont> enable compression cont> in EMP_INFO cont> cont> -- the remaining columns get cont> -- written randomly over these areas cont> store cont> enable compression cont> randomly across (SALARY_HISTORY, JOBS); Please refer to the Oracle Rdb SQL Reference Manual for the full syntax of the CREATE STORAGE MAP statement. 2.3 Known problems 2-40 Software Errors Fixed 2.3.1 Restriction added for CREATE STORAGE MAP on table with data Oracle Rdb7 added support which allows a storage map to be added to an existing table which contains data. The restrictions listed for Rdb7 were: o The storage map must be a simple map which references only the default storage area and represents the current (default) mapping for the table. The default storage area is either RDB$SYSTEM or the area name provided by the CREATE DATABASE ... DEFAULT STORAGE AREA clause. o The new map may not change THRESHOLDS or COMPRESSION for the table, nor can it use the PLACEMENT VIA INDEX clause. It may only contain one area and may not be vertically partitioned. This new map simply describes the mapping as it exists by default for the table. This ECO to Rdb7 adds the additional restriction that the storage map may not include a WITH LIMIT clause for the storage area. The following example shows the reported error. SQL> create table MAP_TEST1 (a integer, b char(10)); SQL> create index MAP_TEST1_INDEX on MAP_TEST1 (a); SQL> insert into MAP_TEST1 (a, b) values (3, 'Third'); 1 row inserted SQL> create storage map MAP_TEST1_MAP for MAP_TEST1 cont> store using (a) in RDB$SYSTEM cont> with limit of (10); -- can't use WITH LIMIT clause %RDB-E-NO_META_UPDATE, metadata update failed -RDMS-F-RELNOTEMPTY, table "MAP_TEST1" has data in it -RDMS-E-NOCMPLXMAP, can not use complex map for non-empty table 2.3.2 The SET TRANSACTION MODE clause of a CREATE/ALTER DATABASE is not exported/imported. Bug 428310 In Rdb 7.0, the SET TRANSACTION MODE clause of a CREATE or ALTER DATABASE statement is not EXPORTed/IMPORTed. The same is true for an ALTER TRANSACTION MODE clause of a CREATE or ALTER DATABASE statement. Thus, in Rdb 7.0, the transaction mode clause cannot be exported/imported. This is a restriction. Software Errors Fixed 2-41 The following example shows a database created with the SET TRANSACTION MODE clause. If this database is exported or imported, the transaction mode information is lost. This is a restriction. CREATE DATABASE FILENAME TRANSMODE_TEST SET TRANSACTION MODE (NO BATCH UPDATE); There is a workaround. After the database is exported and imported, the database can be altered and the SET TRANSACTION MODE set. EXPORT DATA FILE TRANSMODE_TEST INTO TRANSMODE; DISCONNECT ALL; IMPORT DATA FROM TRANSMODE FILE TRANSMODE_TEST1; DISCONNECT ALL; ALTER DATA FILE TRANSMODE_TEST1 SET TRANSACTION MODE (NO BATCH UPDATE); This restriction will be lifted in a future version of Oracle Rdb. 2.3.3 Processes stalling for page locks when ABW is enabled Bugs 460533,460062,442725,449685 Processes can stall for extended periods, waiting for a page lock to be released by another process. The page lock will eventually be released when the blocking process voluntarily gives up that page: typically when finishing its transaction, or when reusing the buffer containing that page. However, if the database operates in "fast commit" mode, the blocking process may not be able to give up the page, since it may be itself waiting for user input or for other work to do. Since the problem only happens for pages that have been written by the "asynchronous batch write" mechanism, a temporary workaround is to disable this mechanism. This is achieved using the following syntax: SQL> alter database filename my_database cont> asynchronous batch writes are disabled; This problem was introduced in Rdb7 and will be corrected in a future ECO for Oracle Rdb Version 7.0. 2-42 Software Errors Fixed 2.3.4 RMU/EXTRACT problem when generating RDO scripts on ALPHA When generating an RDO script on VAX invalid syntax can be generated for storage maps for segmented strings. A semicolon is missed from the script. The workaround is to use and SQL script or to edit the RDO script and add the semicolon. Here is an example of the problem: RDO> ! RMU/EXTRACT for Oracle Rdb V7.0-01 RDO> ! RDO> ! Storage Map Definitions RDO> ! RDO> . . RDO> define storage map LARGE_OBJECT_STORAGE cont> description is cont> /* Keep segmented strings in separate areas */ cont> for SEGMENTED STRINGS cont> store within cont> MODEL_DB_1 for RELATION_1, EMPLOYEE.MANAGERS_COMMENTS cont> MODEL_DB_2 for EMPLOYEE.EMPLOYEE_PHOTO MODEL_DB_2 for EMPLOYEE.EMPLOYEE_PHOTO ^ %RDO-W-LOOK_FOR_STT, syntax error, looking for: %RDO-W-LOOK_FOR_CON, ,, THRESHOLDS, ;, NO, ENABLE, DEFAULT, %RDO-W-LOOK_FOR_CON, DISABLE, PLACEMENT, REORGANIZE, STORE, %RDO-W-LOOK_FOR_CON, END, %RDO-F-LOOK_FOR_FIN, found MODEL_DB_2 instead RDO> MODEL_DB_3; MODEL_DB_3; ^ %RDO-F-LOOK_FOR, syntax error, looking for a valid RDO statement, found MODEL_D RDO> end. end. ^ %RDO-F-LOOK_FOR, syntax error, looking for a valid RDO statement, found END ins The problem will be fixed in a future eco for Oracle Rdb Version 7.0. Software Errors Fixed 2-43 2.3.5 Database Hang When Using Multiple Attaches to Same Database When using a large number of attaches to a database by the same process, or attaching to a large number of databases at the same time, it is possible to cause the database(s) to hang. The problem is caused by various timing conditions, and only seems to occur when using the AIJ Log Server ("ALS") server or when using the "Record Cache" feature. The problem can occur when attaching multiple times to the same database, or attaching to multiple databases. The problem does NOT occur when using only a single attach to the database. Also, because the problem is timing related, the problem seems to occur when using three or more attaches, but the problem CAN occur when using at least two attaches. The work-around is to disable the "AIJ Log Server" and/or disable the record cache feature when attaching to multiple databases. This problem will be fixed in a future eco to Oracle Rdb Version 7.0. 2.3.6 OTHERWISE clause only valid with STORE USING clause of a Vertical Record Partition Storage Map. In Rdb 7.0, SQL did not enforce the correct use of the OTHERWISE clause in a Vertical Record Partition storage map. Specifically, in a CREATE STORAGE MAP statement, if a STORE columns IN clause was specified, SQL allowed the OTHERWISE clause. This is incorrect syntax. The following example shows incorrect SQL syntax. There is no STORE USING clause; therefore the OTHERWISE clause is invalid. CREATE STORAGE MAP TABLE_1_MAP FOR TABLE_1 STORE columns (PKEY,IKEy) across (area_1,area_2) STORE columns (SKEY,CKEY) IN AREA_4 OTHERWISE AREA_14; 2-44 Software Errors Fixed There is a workaround. The workaround is to use the correct SQL syntax when creating a Vertical Record Partition storage map. The following example shows the correct SQL syntax. The OTHERWISE clause is used with the STORE USING clause. CREATE STORAGE MAP TABLE_1_MAP FOR TABLE_1 STORE columns (PKEY,IKEy) across (area_1,area_2) STORE columns (SKEY,CKEY) USING (PKEY) IN AREA_4 WITH LIMIT OF (100) OTHERWISE AREA_14; This problem will be corrected in a future version of Oracle Rdb. SQL will then generate an error message if the syntax is not correct. 2.3.7 STORE COLUMNS clause required for each Vertical Record Partition Bug 420412 In Rdb 7.0, SQL did not enforce the STORE COLUMNS clause for each Vertical Record Partition defined in a CREATE STORAGE MAP statement. Omitting the STORE clause caused unpredictable results. The following example shows incorrect SQL syntax for the STORE COLUMNS clause of a Vertical Record Partition. Specifically, for each Vertical Record Partition defined, STORE is required. It is incorrect SQL syntax to use COLUMNS without STORE. create storage map test_map store columns (a) in storage_area_a columns (b) in storage_area_b; There is a workaround. The workaround is to use the correct SQL syntax when defining each Vertical Record Partition. The following example shows the correct SQL syntax for a Vertical Record Partition definition that is there is a STORE COLUMNS clause for each Vertical Record Partition. create storage map test_map store columns (a) in storage_area_a store columns (b) in storage_area_b; Software Errors Fixed 2-45 This problem will be corrected in a future version of Oracle Rdb. SQL will then generate an error message if the syntax is not correct. ________________________ Note ________________________ The syntax diagram in the Oracle Rdb7 SQL Reference Manual and in the V7.0 interactive SQL HELP shows the incorrect SQL syntax. This error will be corrected in a future release of Oracle Rdb. ______________________________________________________ 2.3.8 DROP TABLE CASCADE will result in %RDB-E-NO_META_UPDATE In Oracle Rdb 7.0, SQL passed incorrect MBLR to Oracle Rdb when a DROP TABLE CASCADE statement was issued. This occurred when the following conditions applied: 1. a table was created with an index defined on the table. 2. a storage map was created with a placement via index. 3. the storage map was a Vertical Record Parition Storage Map with 2 or more STORE COLUMNS clauses. Thus, if such conditions apply, and one issues a DROP TABLE CASCADE, the result will be the error message: %RDB-E-NO_META_UPDATE, metadata update failed. The following example shows a table, index, and storage map definition followed by a DROP TABLE CASCADE statement. The example also show the resultant error message. create table vrp_table ( id int, id2 int); commit; create unique index vrp_idx on vrp_table (id) store in empids_low; commit; create storage map vrp_map for vrp_table placement via index vrp_idx enable compression store columns (id) in empids_low store columns (id2) in empids_mid; commit; 2-46 Software Errors Fixed drop table vrp_table cascade; Index VRP_IDX is also being dropped. VIA clause on storage map VRP_MAP is also being dropped. %RDB-E-NO_META_UPDATE, metadata update failed -RDMS-E-WISH_LIST, feature not implemented yet -RDMS-E-VRPINVALID, invalid operation for storage map "VRP_MAP" There is a workaround. The workaround is to first drop the storage map, and then drop the table cascade. The following example shows the workaround. One must DROP the STORAGE MAP first, then one may issue a DROP TABLE CASCADE. The SHOW statement illustrates that the table, index, and storage map were dropped. drop storage map vrp_map; drop table vrp_table cascade; Index VRP_IDX is also being dropped. commit; show table vrp_table No tables found show index vrp_idx No indexes found show storage map vrp_map No Storage Maps Found This problem will be corrected in a future version of Oracle Rdb. SQL will then generate correct MBLR. Software Errors Fixed 2-47 3 _________________________________________________________________ Enhancements This chapter describes the enhancements that are introduced by Oracle Rdb Version 7.0 ECO 1. 3.1 Enhancements Provided in ECO 1 3.1.1 RMU/SHOW STATISTIC "Stall Class" Informational Screens Often, when attempting to diagnose performance problems, the existing RMU/SHOW STATISTIC "Stall Messages" and "Active User Stall Messages" screens are extremely useful for identifying the cause of present stalls. However, these screens show only the "moment in time" view of the cause of the stalls. Two new screens have been added to the RMU/SHOW STATISTIC utility; each of these screens displays information about the types or "classes" of stalls that are either occurring or have occurred since statistics collection began. There are currently 10 classes of stall messages. o records: These are record related stalls, such as waiting for record locks to be granted. o pages: These are page related stalls, such as waiting for storage area I/O to complete or page locks to be granted. o tables: These are table related stalls, such as waiting for logical area locks to be granted. o storage areas: These are storage area related stalls, such as waiting for storage areas to be created, deleted, truncated or opened. o database rootfile: These are database rootfile related stalls, such as waiting for rootfile I/O to complete or "object" locks to be granted. Enhancements 3-1 o recovery journals: These are journal related stalls, such as opening, initializing or extending journals, as well as waiting for journal locks to be granted. o transactions: These are transaction related stalls, such as waiting for 2PC transactions to commit, or waiting for checkpoints to complete. o hot standby: These are hot standby related stalls. o database: These are database related stalls, such as waiting for the database freeze to complete. o miscellaneous: These are generic stalls, such as waiting for a bugcheck dump to complete. The new "Stall Statistics" screen identifies the number of stalls and their corresponding durations for a particular stall class. The screen can be configured (using the "Config" onscreen-menu option) to display either the "aggregate count" information or the "aggregate duration" information. The following example shows the "Stall Statistics" screen displaying aggregate count information. Node: ALPHA3 Oracle Rdb X7.0-00 Performance Monitor 24-OCT-1996 15:55:30 Rate: 1.00 Second Stall Statistics (Aggregate counts) Elapsed: 01:44:29.27 Page: 1 of 1 KODA_TEST:[R_ANDERSON.OE_MASTER]OE_RDB.RDB;1 Mode: Online -------------------------------------------------------------------------- statistic....... rate.per.second............. total....... average...... name............ max..... cur..... avg....... count....... per.trans.... miscellaneous 0 0 0.0 0 0.0 records 15 0 0.0 160 0.3 pages 2706 0 48.1 301991 579.6 tables 0 0 0.0 0 0.0 storage areas 33 0 0.0 133 0.2 database rootfile 125 0 0.3 2162 4.1 recovery journals 66 0 0.5 3405 6.5 transactions 69 0 0.0 294 0.5 hot standby 0 0 0.0 0 0.0 database 9 0 0.0 19 0.0 -------------------------------------------------------------------------- Config Exit Graph Help Menu Options Pause Reset Set_rate Time_plot Unreset 3-2 Enhancements In this example, there were 301,991 total stalls for the "page" class. The following example shows the "Stall Statistics" screen displaying aggregate duration information. Node: ALPHA3 Oracle Rdb X7.0-00 Performance Monitor 24-OCT-1996 15:55:41 Rate: 1.00 Second Stall Statistics (Aggregate durations) Elapsed: 01:44:40 Page: 1 of 1 KODA_TEST:[R_ANDERSON.OE_MASTER]OE_RDB.RDB;1 Mode: Online -------------------------------------------------------------------------- statistic....... rate.per.second............. total....... average...... name............ max..... cur..... avg....... count....... per.trans.... miscellaneous 0 0 0.0 0 0.0 records 8720 0 4.8 30237 58.0 pages 78453 0 88.3 554787 1064.8 tables 0 0 0.0 0 0.0 storage areas 3400 0 0.7 4742 9.1 database rootfile 1876 0 1.5 9835 18.8 recovery journals 6652 0 9.9 62716 120.3 transactions 31192 0 10.0 63145 121.1 hot standby 0 0 0.0 0 0.0 database 2052 0 0.1 1226 2.3 -------------------------------------------------------------------------- Config Exit Graph Help Menu Options Pause Reset Set_rate Time_plot Unreset The stall durations are collected as hundredths of seconds. Therefore, the total "page" stall duration of 554787 is really 5547.87 seconds. ________________________ Note ________________________ Since certain types of stalls can be nested, the total stall durations may be larger than actually occurred. ______________________________________________________ The new "Active Stall Counts" screen identifies the actual number of processes currently stalled in a particular stall class. Ideally, the number of stalled processes for each class should be "0", which indicates that there are no stalled processes. Enhancements 3-3 The following example shows the "Active Stall Counts" screen: Node: ALPHA3 Oracle Rdb X7.0-00 Performance Monitor 24-OCT-1996 15:41:05 Rate: 0.10 Seconds Active Stall Counts Elapsed: 01:30:04.69 Page: 1 of 1 KODA_TEST:[R_ANDERSON.OE_MASTER]OE_RDB.RDB;1 Mode: Online -------------------------------------------------------------------------- Stall Category Stall.Cnt 10 20 30 40 50 60 70 80 90 100 +----+----+----+----+----+----+----+----+----+----+ miscellaneous 0 | | | | | | | | | | | records 0 | | | | | | | | | | | pages 5 +-* | | | | | | | | | | tables 0 | | | | | | | | | | | storage areas 0 | | | | | | | | | | | database rootfile 0 | | | | | | | | | | | recovery journals 0 | | | | | | | | | | | transactions 1 * | | | | | | | | | | hot standby 0 | | | | | | | | | | | database 0 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | -----------------------+----+----+----+----+----+----+----+----+----+----+ Exit Help Menu Set_rate Write ! 3.1.2 RMU/SHOW STATISTIC "Stall Messages" Include Transaction State RMU/SHOW STATISTIC "Stall Messages" screens now display the process current transaction state ("R" for Read-Only or "W" for Read-Write) along with the stall information. 3.1.3 RMU/SHOW STATISTIC "Logical Area" Screens The RMU/SHOW STATISTIC utility has been enhanced to display "Logical Area" statistics. A "logical area" is a table, btree index or hash index. This enhancement essentially provides the means to "drill down" to a specific table's or index's statistic information. 3-4 Enhancements The following screen is an example of the EMPLOYEES table in the MF_PERSONNEL database: Node: ALPHA3 Oracle Rdb X7.0-00 Performance Monitor 15-NOV-1996 07:28:18 Rate: 1.00 Second Logical Area Statistics Elapsed: 00:55:54.35 Page: 1 of 1 KODH$:[R_ANDERSON.WORK.STATS]MF_PERSONNEL.RDB;1 Mode: Online -------------------------------------------------------------------------- Table EMPLOYEES in EMPIDS_OVER statistic....... rate.per.second............. total....... average...... name............ max..... cur..... avg....... count....... per.trans.... record marked 14 0 0.0 18 0.0 record fetched 14 0 0.7 2645 0.9 fragmented 0 0 0.0 0 0.0 record stored 9 0 0.7 2621 0.9 fragmented 0 0 0.0 0 0.0 pages checked 9 0 0.7 2621 0.9 saved IO 2 0 0.0 162 0.0 discarded 0 0 0.0 0 0.0 record erased 5 0 0.0 6 0.0 fragmented 0 0 0.0 0 0.0 ------------------------------------------------------------------------- Exit Graph Help Menu Options Pause Reset Set_rate Time_plot Unreset Write In the above screen, the statistic information displayed pertains specifically to the EMPLOYEES table within the EMPIDS_OVER storage area. The following screen is an example of the EMPLOYEES_HASH hash index in the MF_PERSONNEL database: Node: ALPHA3 Oracle Rdb X7.0-00 Performance Monitor 15-NOV-1996 07:47:11 Rate: 1.00 Second Logical Area Statistics Elapsed: 01:14:47.77 Page: 1 of 1 KODH$:[R_ANDERSON.WORK.STATS]MF_PERSONNEL.RDB;1 Mode: Online ------------------------------------------------------------------------- Hash EMPLOYEES_HASH in EMPIDS_OVER statistic....... rate.per.second............. total....... average...... name............ max..... cur..... avg....... count....... per.trans.... hash insertions 0 0 0.0 0 0.0 duplicates 0 0 0.0 0 0.0 hash deletions 0 0 0.0 0 0.0 duplicates 0 0 0.0 0 0.0 Enhancements 3-5 hash scans 0 0 0.0 0 0.0 hash index fetches 0 0 0.0 0 0.0 bucket fragments 0 0 0.0 0 0.0 duplicate nodes 0 0 0.0 0 0.0 ------------------------------------------------------------------------- Exit Graph Help Menu Options Pause Reset Set_rate Time_plot Unreset Write The following screen is an example of the RDB$NDX_REL_NAME_ N btree index in the MF_PERSONNEL database: Node: ALPHA3 Oracle Rdb X7.0-00 Performance Monitor 15-NOV-1996 07:49:21 Rate: 1.00 Second Logical Area Statistics Elapsed: 01:16:57.55 Page: 1 of 1 KODH$:[R_ANDERSON.WORK.STATS]MF_PERSONNEL.RDB;1 Mode: Online ------------------------------------------------------------------------- Btree RDB$NDX_REL_NAME_N in RDB$SYSTEM statistic....... rate.per.second............. total....... average...... name............ max..... cur..... avg....... count....... per.trans.... node fetches 1 0 1.5 7135 2.4 leaf fetches 0 0 0.6 3039 1.0 dup. fetches 0 0 0.2 1189 0.4 index lookups 0 0 0.1 792 0.2 index scans 0 0 0.4 2115 0.7 primary entries 0 0 0.0 0 0.0 dup. entries 0 0 0.0 0 0.0 node insertions 0 0 0.0 0 0.0 root insertions 0 0 0.0 0 0.0 leaf insertions 0 0 0.0 0 0.0 dup. insertions 0 0 0.0 0 0.0 node creations 0 0 0.0 0 0.0 root splits 0 0 0.0 0 0.0 leaf creations 0 0 0.0 0 0.0 dup. creations 0 0 0.0 0 0.0 index creations 0 0 0.0 0 0.0 node removals 0 0 0.0 0 0.0 root removals 0 0 0.0 0 0.0 leaf removals 0 0 0.0 0 0.0 dup. removals 0 0 0.0 0 0.0 node deletions 0 0 0.0 0 0.0 leaf deletions 0 0 0.0 0 0.0 dup. deletions 0 0 0.0 0 0.0 index destructions 0 0 0.0 0 0.0 3-6 Enhancements ------------------------------------------------------------------------- Exit Graph Help Menu Options Pause Reset Set_rate Time_plot Unreset Write Note that the type of information displayed depends on the type of the logical area. Also, the "graph", "time plot" and "scatter plot" options are available for all fields in the display. The "Logical Area" screens are selected using the "Logical Area Information" option of the main menu. Currently, there is only 1 logical area screen, the "Logical Area Statistics" screen. Once the logical area screen has been selected, a menu of available logical areas will be displayed. The menu is sorted in alphabetical order. If the RMU/SHOW STATISTIC utility knows the type of a particular logical area, then the type will be displayed in the menu option, but does not affect the sort order. Note that all logical areas, including the system relations and indexes, are displayed in the menu. When a logical area has been selected, the RMU/SHOW STATISTIC utility will identify the "type" of the selected logical area. If the type cannot be determined, you will be prompted to enter the logical area type. The following restrictions and caveats apply: 1. The "Logical Area" statistics are not written to the binary output file. Conversely, the "Logical Area" statistics screens are not available during binary input file replay. 2. There currently is no mechanism to display aggregate information for a partitioned logical area. All statistics information displayed is on a per-storage- area basis. 3. A "summary information" screen of all logical areas is not currently available. 4. The number of lines of statistics information displayed depends on the size of the terminal or window. In particular, the btree index displays up to 24 lines of statistics information; on a standard 24x80 terminal, this means that only the first 15 lines of statistics would be displayed. Enhancements 3-7 5. Any table or index created prior to Rdb7 ECO 1 will require that the user enter the appropriate logical area type. WARNING: If you specify an incorrect logical area type (i.e. specify a table as being a hash index), inappropriate statistics fields will be displayed. 3.1.4 RMU/REPAIR /INITIALIZE=LAREA_PARAMETERS /TYPE Qualifier Starting with Oracle Rdb7 ECO 1, the RMU/SHOW STATISTIC utility can display information on a "per-logical area". A "logical area" is a table, btree index, hash index, or any partition of one of those. The RMU/SHOW STATISTIC utility use the on disk AIP pages to determine the appropriate "type" of each logical area, so that the appropriate statistics information can be displayed. The logical area "type" information in the AIP is "unknown" for logical areas created prior to Oracle Rdb7 ECO 1. If the RMU/SHOW STATISTIC utility cannot determine the logical area type from the AIP, it will prompt the user to manually enter the logical area type; however, this information is not updated in the database AIP pages. Therefore, in order to update the on disk logical area "type" in the AIP, the RMU/REPAIR utility has been enhanced. The /INITIALIZE=LAREA_PARAMETERS qualifier option file support has been enhanced. You can now specify a /TYPE=[TABLE|BTREE|HASH|SYSTEM|BLOB] in the options file. For example, to repair the EMPLOYEES table of the MF_PERSONNEL database, you would create an options file that contains the following line: EMPLOYEES /TYPE=TABLE For partitioned logical areas, the /AREA=name qualifier can be used to identify the specific storage areas that are to be updated. For example, to repair the EMPLOYEES table of the MF_PERSONNEL database for the EMPID_OVER storage area only, you would create an options file that contains the following line: EMPLOYEES /AREA=EMPID_OVER /TYPE=TABLE 3-8 Enhancements The /TYPE qualifier specifies the type of a logical area. The following keywords are allowed: o TABLE. Specifies that the logical area is a data table. This would be a table created using the SQL "CREATE TABLE" syntax. o BTREE. Specifies that the logical area is a btree index. This would be an index created using the SQL "CREATE INDEX TYPE IS SORTED" syntax. o HASH. Specifies that the logical area is a hash index. This would be an index created using the SQL "CREATE INDEX TYPE IS HASHED" syntax. o SYSTEM. Specifies that the logical area is a system record which is used to identify hash buckets. Users cannot explicitly create these types of logical areas. ________________________ Note ________________________ This type should NOT be used for the RDB$SYSTEM logical areas. This type does NOT identify system relations. ______________________________________________________ o BLOB. Specifies that the logical area is a blob repository. There is no error checking of the "type" specified for a logical area. The specified type does not affect the collection of statistics, nor does it affect the readying of the affected logical areas. However, an incorrect type will cause incorrect statistics to be reported by the RMU/SHOW STATISTIC utility. 3.1.5 Replication Governor Status Configurable The status of the Hot Standby Replication Governor is now a database attribute. The REPLICATE AFTER_JOURNAL CONFIGURE utility supports the enabling and disabling of the Replication Governor. The DUMP/HEADER command can be used to view the status of the Replication Governor for a given replication standby database. Enhancements 3-9 The following example shows the RMU CONFIGURE command. $ RMU/REPLICATE AFTER_JOURNAL CONFIGURE - USER_DISK1:[ORACLE_RDB.STANDBY]MF_PERSONNEL - /MASTER_ROOT=REMOTE1::USER_DISK2:[ORACLE_RDB.MASTER]MF_PERSONNEL.RDB - /GOVERNOR=ENABLED The following example shows the output of an RMU/DUMP/HEADER=HOT_STANDBY command. *------------------------------------------------------------------------- * Oracle Rdb V7.0-00 3-DEC-1996 10:48:02.68 * * Dump of Database header * Database: USER_DISK1:[ORACLE_RDB.STANDBY]MF_PERSONNEL.RDB;1 * *------------------------------------------------------------------------- Database Parameters: Root filename is "USER_DISK1:[ORACLE_RDB.STANDBY]MF_PERSONNEL.RDB;1" Hot Standby... - Database has been configured as "Replication Standby" Master database is "USER_DISK2:[ORACLE_RDB.MASTER]MF_PERSONNEL.RDB;1" Remote node name is "REMOTE1" Replication last commenced on 21-NOV-1996 09:03:36.92 Database replication is "online" Replication Governor is "enabled" Server checkpoint interval is 100 messages Server gap-timeout interval is 5 minutes Server buffer count is 256 Server 2PC transaction resolution is "commit" 3.1.6 Stall Messages and RMU/SHOW STATISTICS In previous releases of Oracle Rdb, stall messages (the messages that show on the "Stall Messages" and "Active User Stall Messages" screens) were written and formatted by each process attached to the database. The RMU/SHOW STATISTICS process simply displayed the messages on the screen. This behavior has been changed. Starting with Oracle Rdb Version 7.0 ECO 1 RMU/SHOW STATISTICS process creates and formats the stall message strings. Individual processes attached to the database quickly record information about the stall but perform no message formatting. 3-10 Enhancements Moving the work of formatting the stall messages results in a reduction of CPU usage for all processes attached to the database. Less CPU resources are consumed because stall message information is now never formatted by the processes attached to the database (whether RMU/SHOW STATISTICS is being run or not). However, a process running RMU/SHOW STATISTICS may use additional CPU resources than in previous versions when formatting and displaying the "Stall Messages" and "Active User Stall Messages" screens. But, moving the formatting work to the process running RMU/SHOW STATISTICS from the processes attached to the database should result in an overall reduction in the CPU resources used. 3.1.7 RMU/SHOW USERS now identifies nodes where DB is open The RMU/SHOW USERS utility has been enhanced to identify the various nodes in the VMScluster where the database is currently "open" and available for use. Note that "open" includes those nodes where the database is open for utility access only. The following example shows the RMU/SHOW USERS utility output for a database open on three nodes. ALL> rmu/show users Oracle Rdb X7.0-00 on node ALPHA3 3-JAN-1997 13:30:17.54 - monitor started 3-JAN-1997 09:38:25.73 - monitor log filename is "$111$DUA366:[RDMMON_LOGS]RDMMON701_ALPHA3.LOG;634" - 255 monitor buffers available (256 maximum) database _$111$DUA347:[R_ANDERSON.WORK.STATS]MF_PERSONNEL.RDB;1 - First opened 3-JAN-1997 09:39:17.94 * database is opened by an operator - current after-image journal file is _$111$DUA347:[R_ANDERSON.WORK.STATS]RICK1.AIJ;1 - Database also open on these nodes: ALPHA5 QUICKR ALL> Note that the "current" node (ALPHA3) is not displayed in the list output. Enhancements 3-11 3.1.8 Disk Erase Control Logical Name RDM$BIND_FORCED_DISK_ERASE In certain cases, problems with the operating system file erase function have caused problems with Oracle Rdb's ability to correctly initialize file contents with specific patterns. To work around these problems, a new logical name, RDM$BIND_FORCED_DISK_ERASE can be used to control how Oracle Rdb attempts to initialize files with specific patterns. If RDM$BIND_FORCED_DISK_ERASE is undefined or defined as zero, Oracle Rdb will use the operating system's erase functionality to initialize file contents. If RDM$BIND_FORCED_DISK_ERASE is defined as "1", then Oracle Rdb will initialize the file contents by writing a pattern into the file directly. Because writing to the file directly is slower than using the operating system's erase features, it is not the default. To be effective, the logical name RDM$BIND_FORCED_DISK_ ERASE must be defined /SYSTEM and must be defined before a database is opened or created. 3.1.9 RMU/SHOW STATISTIC Lock Timeout and Lock Deadlock Logging Tools like the RMU/SHOW STATISTIC utility can help solve a current problem within the database. But most problems are solved within a limited timeframe. Because most customers are running Rdb in a 7x24 hour environment it is not always possible to have an expert readily available. This means most customers cannot trace a problem until after it has occurred and been solved. This is especially the case when analyzing lock timeouts and deadlocks, because once the event has occurred all information regarding the lock event is gone. Frequently, a lock deadlock of "interest" is superceded by another lock deadlock. Currently, the RMU/SHOW STATISTIC utility provides the "Lock Timeout History" and "Lock Deadlock History" screens. However, these screens only record the last timeout or deadlock for the processes. There is no method to record each lock timeout or deadlock as it occurs. 3-12 Enhancements The RMU/SHOW STATISTIC utility has been enhanced to provide two new command qualifiers: /LOCK_TIMEOUT_LOG=logfile_spec and /DEADLOCK_LOG=logfile. The logfile is the name of the file to which all lock timeout and lock deadlock messages will be logged. The lock timeout and lock deadlock messages are written in human-readable format similar to the "Lock Timeout History" and "Lock Deadlock History" screens. The header region of the lock timeout and lock deadlock log contains three lines. The first line indicates that the RMU/SHOW STATISTIC utility created the log file. The second line identifies the database. The third line identifies the date and time the log was created. The main body of the stall log contains three columns. The first column contains the process ID and stream ID which experienced the lock timeout or deadlock. The second column contains the time the timeout or deadlock occurred; the date is NOT displayed. The third column contains the timeout or deadlock message describing the affected resource; this message is similar to the originating stall message. For example: 2EA00B52:34 14:25:46.14 - waiting for page 5:751 (PR) Also, if any lock timeouts or lock deadlocks are missed for a particular process, usually because the recording interval is too large, the number of missed lock timeouts or deadlocks is displayed after the message, in brackets. For example: 2EA00B52:34 14:25:46.14 - waiting for page 5:751 (PR) [1 missed] Only one message per occurring lock timeout or deadlock is logged. The lock timeout or deadlock messages are written at the specified screen refresh rate, determined using the command qualifier or online using the "Set_rate" onscreen menu option. Obviously, using a larger refresh rate will minimize the size of the file but result in a large number of missed lock timeout or deadlock messages. Using a smaller refresh rate will produce a large log file, but will contain a much finer granularity of lock timeout or deadlock messages. Enhancements 3-13 The affected LockID is not displayed, since this is meaningless information after the lock deadlock has completed. Note that you do not need to be displaying the "Lock Timeout History" or "Lock Deadlock History" screens in order to record the stall messages to the stall log. The logs are maintained regardless of which screen, if any, is displayed. Note that the /LOCK_TIMEOUT_LOG and /DEADLOCK_LOG qualifiers are separate and distinct from each other. They can be used together or separately, as necessary. Using the /LOCK_TIMEOUT_LOG or /DEADLOCK_LOG command qualifiers, a "Lock Event Logging" server can be easily constructed. The following OpenVMS DCL script shows how to create a server that logs both lock timeout and lock deadlock events on the MF_PERSONNEL database for the next 15 minutes: $ RMU/SHOW STATISTIC /NOHISTORY /TIME=1 /NOINTERACTIVE /LOCK_TIMEOUT_LOG=TIMEOUT.LOG /DEADLOCK_LOG=DEADLOCK.LOG - /NOBROADCAST /UNTIL="+15:00" - MF_PERSONNEL ________________________ Note ________________________ Using the /TIME=1 or /TIME=-50 qualifier appears to produce a reasonable log while minimizing the impact on the system. ______________________________________________________ The lock timeout and lock deadlock logging facility can also be enabled or disabled while the RMU/SHOW STATISTIC utility is running. Using the "Tools" menu (obtained by typing the "!" key at any screen). The lock timeout log and lock deadlock log are not available during binary file replay. The following sample "Lock Deadlock" log was produced using a refresh rate of "1.00" seconds: 3-14 Enhancements Oracle Rdb X7.0-00 Performance Monitor Lock Deadlock Log Database KODH$:[R_ANDERSON.WORK.STATS]MF_PERSONNEL.RDB;1 Lock Deadlock Log created 7-FEB-1997 14:20:40.57 2EA00B52:33 14:20:46.22 - waiting for page 5:924 (PW) 2EA04F3A:34 14:20:45.09 - waiting for page 5:792 (PR) 2EA00B52:33 14:20:46.98 - waiting for page 5:1228 (PW) 2EA04F3A:34 14:20:48.19 - waiting for page 5:1086 (PW) 2EA00B52:33 14:20:48.20 - waiting for page 5:1244 (PR) 2EA00B52:33 14:20:58.34 - waiting for page 5:638 (PR) 2EA04F3A:34 14:20:59.17 - waiting for page 5:638 (PW) 2EA04F3A:34 14:21:00.47 - waiting for page 5:482 (PR) 2EA00B52:33 14:21:02.23 - waiting for page 5:661 (PR) 2EA00B52:33 14:21:08.56 - waiting for page 5:523 (PW) 2EA00B52:33 14:21:09.33 - waiting for page 5:918 (PW) 2EA00B52:33 14:21:10.05 - waiting for page 1:976 (PW) 2EA04F3A:34 14:21:15.91 - waiting for page 5:623 (PW) 2EA04F3A:34 14:21:18.63 - waiting for page 5:794 (PW) 2EA00B52:34 14:25:50.45 - waiting for page 5:1086 (PW) . . . This enhancement is available in Oracle Rdb Version 7.0 ECO 1. 3.1.10 ALTER INDEX now performs less area scans When ALTER INDEX is used to change the partitioning of an index it scans each referenced storage area to initialize and create the index partitions. With this ECO of Rdb, the amount of I/O has been reduced (especially for MIXED format areas) by avoiding one of the area scans. Prior to this change Rdb, would scan each of the areas twice to remove the old index. With this ECO for Rdb7 only a single scan of each area is required to remove the old hash index. This change makes use of the same optimizations introduced in Rdb7 for commands such as TRUNCATE TABLE, DROP TABLE, DROP INDEX and ALTER DATABASE ... DROP STORAGE AREA CASCADE. Enhancements 3-15 3.1.11 RMU/DUMP/AFTER /START & /END Qualifiers are Difficult to Use The /START and /END qualifiers for the RMU/DUMP/AFTER_ JOURNAL utility are extremely difficult to use as users seldom know, nor can they determine, the AIJ record number in advance of using the utility. There is no workaround to this problem. This problem has been corrected in Oracle Rdb Version 7.0 ECO 1. The RMU/DUMP/AFTER_JOURNAL utility has been enhanced to provide more advanced selection criteria. Two new optional qualifiers, /FIRST=select_list and /LAST=select_ list have been added. The select_list of these qualifiers consists of a list of one or more of the following keywords: o RECORD=record#: Specifies the first or last record in the AIJ journal. This is the same as the existing /START and /END qualifiers, which are still supported, but obsolete. o BLOCK=block#: Specifies the first or last block in the AIJ journal. o TSN=tsn: Specifies the first or last TSN in the AIJ journal, using the standard "[n:]m" TSN format. o TID=tid: Specifies the first or last TID in the AIJ journal. o TIME=date_time: Specifies the first or last date/time in the AIJ journal, using the standard date/time format. Both of the /FIRST and /LAST qualifiers are optional. You may specify both, either of them, or neither of them. The keywords specified for the /FIRST qualifier can differ from the keywords specified for the /LAST qualifier. For example, to start the dump from the fifth block of the AIJ journal, you would use the following command: RMU/DUMP/AFTER_JOURNAL /FIRST=(BLOCK=5) MF_PERSONNEL.AIJ To start the dump from block 100 or TSN 52, whichever occurs first, you would use the following command: RMU/DUMP/AFTER_JOURNAL /FIRST=(BLOCK=100,TSN=0:52) MF_PERSONNEL.AIJ 3-16 Enhancements When multiple keywords are specified for a qualifier, the first condition being encountered activates the qualifier. In the above example, the dump will start when either block 100 or TSN 52 is encountered. Enhancements 3-17