VAX_Rdb/VMS_________________________________________
Release Notes
AA-GQ93J-TE
May 1992
This document contains the Release Notes for VAX
Rdb/VMS Version 4.1. It describes new and changed
features; software errors fixed; and problems,
restrictions, and other information relating to
Version 4.1.
Revision/Update Information This manual is a
revision and supersedes
previous versions.
Operating System: VMS
Software Version: VAX Rdb/VMS Version 4.1
Digital Equipment Corporation
Maynard, Massachusetts
________________________________________________________________
The information in this document is subject to change
without notice and should not be construed as a commitment
by Digital Equipment Corporation. Digital Equipment
Corporation assumes no responsibility for any errors that
may appear in this document.
The software described in this document is furnished under
a license and may be used or copied only in accordance with
the terms of such license.
No responsibility is assumed for the use or reliability
of software on equipment that is not supplied by Digital
Equipment Corporation or its affiliated companies.
Restricted Rights: Use, duplication, or disclosure by the
U.S. Government is subject to restrictions as set forth in
subparagraph (c)(1)(ii) of the Rights in Technical Data
and Computer Software clause at DFARS 252.227-7013.
© Digital Equipment Corporation 1984, 1985, 1986, 1987,
1988, 1989, 1990, 1992.
All Rights Reserved.
The postpaid Reader's Comments form at the end of this
document requests your critical evaluation to assist in
preparing future documentation.
The following are trademarks of Digital Equipment
Corporation: ACMS, ALL-IN-1, CDD/Plus, CDD/Repository, CI,
DEC, DECdecision, DECdtm, DECforms, DECintact, DECnet,
DECplan, DECtp, DECtrace, DECwindows, HSC, MASSBUS,
MicroVAX, PATHWORKS, RA, Rdb/VMS, ULTRIX, UNIBUS, VAX,
VAX Ada, VAX BASIC, VAX C, VAX CDD, VAX COBOL, VAX
DATATRIEVE, VAX DBMS, VAX DOCUMENT, VAX FMS, VAX FORTRAN,
VAX MACRO, VAX Pascal, VAX Performance Advisor, VAX RALLY,
VAX Rdb/ELN, VAX RMS, VAX SCAN, VAX 6000, VAX SPM,
VAX TEAMDATA, VAX Xway, VAXcluster, VAXELN, VAXset,
VAXstation, VIDA, VMS, VT, and the DIGITAL logo.
IBM and OS/2 are registered trademarks of International
Business Machines Corporation. AppleTalk, Macintosh,
and MacTerminal are registered trademarks and MPW is a
trademark of Apple Computer, Inc. MS-DOS is a registered
trademark and Windows is a trademark of Microsoft
Corporation. SunOS and SPARCstation are trademarks of Sun
Microsystems, Inc.
This document is available on CDROM.
This document was prepared using VAX DOCUMENT, Version 2.1.
_________________________________________________________________
Contents
Preface................................................... xxv
1 New and Changed Features
1.1 Rdb/VMS V4.1 Has Multiple Version Support for
Running Both V4.0 and V4.1 on the Same Node or in
the Same VAXcluster.............................. 1-1
1.2 Deinstall/Delete Command Procedure............... 1-1
1.3 New Update Rules in Rdb/VMS V4.1-Modify and Erase
Operations May Fail in RDO, RDBPRE, and RDML..... 1-2
1.4 Online Help...................................... 1-4
1.4.1 Accessing DCL-Level Help Topics Related to
Rdb/VMS Using the Standard Version of Rdb/VMS
V4.1........................................... 1-5
1.4.2 Accessing DCL-Level Help Topics Related to
Rdb/VMS Using the Varianted Version of Rdb/VMS
V4.1........................................... 1-5
1.5 Rdb/VMS Security Auditing........................ 1-6
1.5.1 Security Audit Log Format Is Changed .......... 1-6
1.5.2 RMU/SET AUDIT and RMU/SHOW AUDIT Commands Have
New and Changed Qualifiers and Keywords........ 1-6
1.6 Query Optimizer Enhancements..................... 1-7
1.6.1 Displaying Estimated Query Cost ............... 1-7
1.6.2 Limiting Query Execution with the Query
Governor....................................... 1-7
1.6.3 Setting a Preferred Query Optimization Mode ... 1-9
1.6.4 Quick Index Lookup in the Dynamic Optimizer ... 1-11
1.6.5 Improved OR Optimization ...................... 1-11
1.6.6 MIN/MAX Aggregate Optimization ................ 1-15
1.6.7 Improved View Optimization .................... 1-17
1.6.8 Preventing Anomalous Updates That Can Occur
with Subqueries................................ 1-19
1.7 Using Global Buffers............................. 1-21
iii
1.7.1 Enabling Global Buffers ....................... 1-22
1.7.2 Applications That Benefit from Using Global
Buffers........................................ 1-22
1.8 Journal Fast Commit Transaction Processing and
Commit to Journal Optimization................... 1-34
1.8.1 Commit to Journal Optimization ................ 1-38
1.9 Recovering from a Lost .AIJ File When Journal
Fast Commit Transaction Processing Is Enabled.... 1-39
1.10 Carry-Over Locks Optimization Can Be Disabled.... 1-42
1.11 After-Image Journal Optimization................. 1-43
1.12 Changes to the EXPORT/IMPORT Statements for
V4.1............................................. 1-45
1.12.1 SQL and RDO IMPORT and EXPORT Statements
Support All New Rdb/VMS V4.1 Features.......... 1-45
1.12.2 Changed Syntax for the SQL IMPORT Statement ... 1-47
1.12.3 Changed Syntax for the SQL EXPORT Statement ... 1-47
1.12.4 NO EXTENSIONS Error Checking .................. 1-48
1.13 Online DBA Activities............................ 1-48
1.14 Offline DBA Activities........................... 1-51
1.15 Rdb/VMS Now Supports Creating Storage Areas for
Segmented Strings and List Data on WORM Optical
Disks............................................ 1-52
1.16 Other New and Changed Features for V4.1 That
Affect All Interfaces............................ 1-53
1.16.1 Performance Improvement for Creating Tables and
Views.......................................... 1-53
1.16.2 New On-Disk Format for Segmented Strings (List
of Byte Varying)............................... 1-54
1.16.3 Storing Lists Randomly Across Storage Areas ... 1-57
1.16.4 Change in Utilization of SPAM Thresholds ...... 1-57
1.16.5 Nontext Fields with Collating Sequences Are No
Longer Allowed................................. 1-58
1.16.6 Default Lock Timeout Interval ................. 1-59
1.16.7 Batch Writes to Disk .......................... 1-59
1.16.8 Minimum Value for the SPAM Interval Is Changed
from 256 to 216................................ 1-61
1.16.9 Table Metadata Lock WAIT/NOWAIT Behavior Is
Changed and a Single User Request for a Freeze
Lock Is Now Queued............................. 1-61
1.16.10 Change in Operation of Index Deletion ......... 1-62
1.16.11 Buffers for Storage Areas Are No Longer
Invalidated When the Lock Mode Is Changed (from
CR <=> CU)..................................... 1-63
iv
1.16.12 Change in Getting Event Flags from the
RDB$RDBSHR_EVENT_FLAGS Logical Name............ 1-64
1.16.13 .RUJ Files Are No Longer Created with the UIC
of the Process That Created the File........... 1-64
1.16.14 Changes to RDB$FIELDS and RDB$FIELD_VERSIONS
System Relations............................... 1-65
1.16.15 .AIJ File Is Now Initialized Upon Creation .... 1-65
1.16.16 VAXcluster Nodes up to 96 Are Now Supported ... 1-66
1.16.17 Change to SYS REQUEST Error Called from 113 ... 1-66
1.16.18 Prototype Syntactic Forms of Vertical Record
Partitioning Are Not Possible in V4.1 Storage
Map Statements................................. 1-66
1.17 SQL: New and Changed Features and Statements for
V4.1............................................. 1-66
1.17.1 Multischema Databases ......................... 1-67
1.17.2 Sample Multischema Database ................... 1-68
1.17.3 Authorization Identifiers ..................... 1-68
1.17.4 Multischema Naming ............................ 1-69
1.17.5 Run-Time SQL Kit .............................. 1-69
1.17.6 Reserved Words as Identifiers ................. 1-70
1.17.7 Nested View Updates with CHECK OPTION ......... 1-71
1.17.8 RESTRICT Argument Now Implicit for DROP TABLE
Statement...................................... 1-71
1.17.9 SINGLE Predicate Replaces UNIQUE Predicate .... 1-71
1.17.10 SQLDA2 Structure .............................. 1-71
1.17.11 ORDER BY and LIMIT TO Clauses Are Allowed in
Subqueries..................................... 1-72
1.17.12 Constraint Keyword Changes .................... 1-72
1.17.13 Connections ................................... 1-72
1.17.14 Extended Dynamic Cursors ...................... 1-73
1.17.15 Optional Colons in Module Language
Parameters..................................... 1-73
1.17.16 Error Literals ................................ 1-73
1.17.17 Date-Time Data Types and Date Arithmetic ...... 1-74
1.17.18 Date-Time Functions ........................... 1-75
1.17.19 QUADWORD Is Changed to BIGINT ................. 1-76
1.17.20 C Language and CDD Support for CHAR Data
Types.......................................... 1-76
1.17.21 ALTER DOMAIN Now Allows DATE to Be Converted to
VARCHAR........................................ 1-76
1.17.22 Segmented String Storage on Write-Once
Read-Many (WORM) Devices....................... 1-76
1.17.23 Logical Area Thresholds for Uniform Areas ..... 1-76
v
1.17.24 Interactive SET EXECUTE and SHOW EXECUTION MODE
Statements..................................... 1-77
1.17.25 Interactive SET WARNING and SHOW WARNING MODE
Statements..................................... 1-77
1.17.26 Interactive SET FLAGGER and SHOW FLAGGER MODE
Statements..................................... 1-77
1.17.27 SET ALIAS Statement ........................... 1-77
1.17.28 SQLDA.SQLVAR Has a Structure Name in VAX C .... 1-79
1.17.29 SQL INSERT Statement Now Accepts Value
Expressions.................................... 1-80
1.17.30 Journal Fast Commit Transaction Processing and
Commit to Journal Optimization................. 1-80
1.17.31 Global Buffers ................................ 1-82
1.17.32 Changed Behavior for the ALTER TABLE
Statement...................................... 1-82
1.17.33 SQL Code Generation Improvements .............. 1-87
1.17.34 Commit-Time and Rollback Optimization for
Explicitly Closed Cursors in Embedded SQL and
SQL Module Language............................ 1-88
1.17.35 Update Locking for Cursors .................... 1-88
1.17.36 Sample Programs in Precompiled SQL and SQL
Module Language................................ 1-89
1.17.37 Deprecated Syntax ............................. 1-90
1.17.38 New SHOW DEFAULT Statement .................... 1-91
1.17.39 VAX Data Distributor Statements Are Dynamically
Executable..................................... 1-91
1.17.40 SQL Now Produces Bugcheck Dump Files When an
Internal Error Is Detected..................... 1-91
1.18 SQL/Services: New and Changed Features for V4.1.. 1-91
1.18.1 New SQL/Services APIs for THINK C, Windows 3.0,
and SunOS...................................... 1-92
1.18.2 A New Network Transport Supported by
SQL/Services: TCP/IP........................... 1-92
1.18.3 Detached Processes Have Replaced Batch Queues
for Processing Execution Servers............... 1-94
1.18.4 SQL/Services Server Report Utility for
Execution Server Process Reporting............. 1-94
1.18.5 SQL/Services Deprecates Support for Filter
Expressions.................................... 1-94
1.18.6 SQL/Services Multiple Version Support ......... 1-94
1.18.7 SQL/Services Support for New SQL Features ..... 1-95
1.18.8 SQL/Services VMS API Is Shipped with Rdb/VMS
Run-Time Kit................................... 1-95
vi
1.18.9 SQL/Services Provides MS-DOS Executable Forms
of the SQLSRV$DYNAMIC Sample Program........... 1-95
1.18.10 Changes to the SQL/Services Configuration File
............................................... 1-95
1.18.11 SQL/Services Database Class Servers Are
Supported in Rdb/VMS Version 4.1............... 1-96
1.18.12 Null Termination of ASCII Strings ............. 1-96
1.18.13 New API Routine SQLSRV_SQLDA_REF_DATA Is
Added.......................................... 1-97
1.18.14 OS/2 Support .................................. 1-97
1.19 RDO: New and Changed Features and Statements for
V4.1............................................. 1-98
1.19.1 SHOW FIELD Statement Displays the New SQL
Date-Time Data Types........................... 1-98
1.19.2 Additional RDO Enhancements for V4.1 .......... 1-98
1.19.3 RDO IMPORT Supports the Full Range of New V4.1
Features....................................... 1-99
1.19.4 Support for Carry-Over Locks .................. 1-99
1.19.5 Support for Default Lock Wait ................. 1-100
1.19.6 Support for Fast Commit Transaction Processing
and Commit to Journal Optimization............. 1-100
1.19.7 Support for Global Buffers .................... 1-101
1.19.8 Support for Query Governor .................... 1-102
1.19.9 Support for Modifiable Logical Area Thresholds
for Uniform Storage Areas...................... 1-102
1.19.10 SET LINE_LENGTH Statement Is Restricted to 512
Bytes.......................................... 1-103
1.19.11 Clarification of Behavior of F_FLOATING Data
Type........................................... 1-103
1.20 RDBPRE: New and Changed Features and Statements
for V4.1......................................... 1-104
1.20.1 Query Governor ................................ 1-104
1.21 RDML: New and Changed Features and Statements for
V4.1............................................. 1-104
1.21.1 Query Governor ................................ 1-104
1.22 RMU: New and Changed Features for V4.1........... 1-104
1.22.1 Indirect File References Can Be Used for the
Following RMU Command Qualifiers............... 1-104
1.22.2 RMU/DUMP and RMU/CLOSE Commands No Longer
Require VMS SYSPRV Privilege................... 1-106
1.22.3 New RMU Command Qualifiers Common to More Than
One RMU Command................................ 1-106
vii
1.22.4 RMU/ANALYZE, RMU/ANALYZE/INDEXES, and
RMU/ANALYZE/PLACEMENT Commands................. 1-109
1.22.5 RMU/BACKUP Command ............................ 1-109
1.22.6 RMU/BACKUP/AFTER_JOURNAL Command .............. 1-111
1.22.7 RMU/CONVERT Command ........................... 1-112
1.22.8 RMU/COPY_DATABASE Command ..................... 1-113
1.22.9 RMU/DUMP/AFTER_JOURNAL Command ................ 1-113
1.22.10 RMU/DUMP/BACKUP Command ....................... 1-114
1.22.11 RMU/DUMP/EXPORT Command ....................... 1-114
1.22.12 RMU/EXTRACT Command ........................... 1-114
1.22.13 RMU/LOAD Command .............................. 1-127
1.22.14 RMU/MOVE_AREA Command ......................... 1-128
1.22.15 RMU/OPEN Command .............................. 1-128
1.22.16 RMU/OPTIMIZE/AFTER_JOURNAL Command ............ 1-130
1.22.17 RMU/RECOVER Command ........................... 1-133
1.22.18 RMU/REPAIR Command ............................ 1-134
1.22.19 RMU/RESTORE Command ........................... 1-139
1.22.20 RMU/RESTORE/ONLY_ROOT Command ................. 1-140
1.22.21 RMU/SHOW LOCKS Command ........................ 1-143
1.22.22 RMU/SHOW STATISTICS Command ................... 1-144
1.22.23 RMU/SHOW USERS Command ........................ 1-149
1.22.24 RMU/UNLOAD Command ............................ 1-149
1.22.25 RMU/VERIFY Command ............................ 1-149
1.23 New and Changed Rdb/VMS Logical Names for V4.1... 1-151
1.23.1 The Logical Name RDMS$BIND_VM_SEGMENT
Changed........................................ 1-151
1.23.2 Setting the Query Optimizer Elapsed Time Limit
with the Logical Name RDMS$BIND_QG_TIMEOUT..... 1-152
1.23.3 Setting the Query Optimizer Row Number
Delivery Limit with the Logical Name
RDMS$BIND_QG_REC_LIMIT......................... 1-152
1.23.4 Specifying the RDM$BIND_CKPT_TRANS_INTERVAL
Logical Name to Define a Checkpoint Interval... 1-153
1.23.5 Using the RDMS$USE_OLD_UPDATE_RULES Logical
Name in RDO to Preserve Update Behavior........ 1-153
1.23.6 Using the RDMS$USE_OLD_SEGMENTED_STRING Logical
Name........................................... 1-154
1.23.7 Using the RDMS$BIND_SEGMENTED_STRING_COUNT
Logical Name................................... 1-154
1.23.8 Using the
RDMS$BIND_SEGMENTED_STRING_DBKEY_SCOPE
Logical Name................................... 1-155
1.23.9 Using the RDB$CHARACTER_SET Logical Name ...... 1-156
viii
1.23.10 RDMS$BIND_WORK_FILE Logical Name Is Changed ... 1-156
1.23.11 New Logical Name, RDM$BIND_STATS_DISABLED,
Disables the Writing of Database Statistics for
a Process...................................... 1-157
1.23.12 New Logical Name, RDM$MON_USERNAME, Allows
the Monitor Process to Inherit Quotas of the
Specified User................................. 1-158
1.24 Obsolete Statements and Features................. 1-159
1.24.1 SQL Deprecated Features and Incompatible
Changes for Rdb/VMS Version 4.1................ 1-159
1.24.2 Obsolete RDO Statements Removed Completely .... 1-165
1.24.3 RDO Obsolete Statements and Features .......... 1-165
1.25 Summary of Documentation Additions and Changes
for V4.1......................................... 1-166
2 Software Errors Fixed
2.1 General Information.............................. 2-1
2.1.1 VMS Sort Utility for VMS V5.1, V5.2, and V5.3
Caused Problems with Rdb/VMS Databases......... 2-1
2.2 Software Errors Fixed in V4.1 That Apply to All
Interfaces....................................... 2-2
2.2.1 Rdb/VMS Behavior Had Changed so That Buffers
Were Emptied on Rollback....................... 2-2
2.2.2 Lock-Related Looping Problem .................. 2-2
2.2.3 NOWAIT Transactions Started During a Recovery
Process Caused an RDMS-F-AREABUSY Fatal
Error.......................................... 2-3
2.2.4 Bugcheck Sometimes Resulted When a Sorted Index
Rebalanced Itself.............................. 2-4
2.2.5 Locking Protocol Problem Caused Bugchecks ..... 2-4
2.2.6 SPAM Pages Were Not Updated Correctly ......... 2-4
2.2.7 Global Section Was Corrupted When a User Had
Multiple Attaches.............................. 2-5
2.2.8 Range Query Returned Unexpected Results ....... 2-5
2.2.9 Some Update-Intensive Applications Experienced
a Performance Degradation in Rdb/VMS V4.0
Compared to V3.1B.............................. 2-5
2.2.10 Rdb/VMS Doing a Remote Fetch Went into a
Compute Bound Loop When the Network Link
Failed......................................... 2-6
2.2.11 Wrong RDBINTSHR.EXE Image Was Installed for
Interactive License Customers.................. 2-6
ix
2.2.12 Active Transactions in Application Programs
Could Not Recover from Network Failures........ 2-7
2.2.13 Using Event Flags Caused Conflicts with Other
Software Products.............................. 2-7
2.2.14 An Access Violation Resulted When DECdtm
Services and DECnet Services Were Not
Running........................................ 2-7
2.2.15 If a Commit Failed During a One-Phase
Commit Protocol When an Explicit Distributed
Transaction Was Run, It Caused a Premature
$FINISH_RMOP to DECdtm......................... 2-8
2.2.16 The Rdb/VMS DISTRIBTRAN Privilege Was Not
Available for Remote Database Access........... 2-9
2.2.17 Problem with the Use of Virtual Memory ....... 2-9
2.2.18 Virtual Memory Increased with Some
Fourth-Generation Languages.................... 2-9
2.2.19 Remote File Specifications Were Incorrectly
Formatted...................................... 2-10
2.2.20 SORTWORK Files Were Not Closed Properly During
Image Rundown.................................. 2-11
2.2.21 Monitor Produced a Bugcheck in the
MON$FIND_USER Routine.......................... 2-12
2.2.22 A Bugcheck Was Produced Within the Monitor Log
File During Heavy Monitor Activity............. 2-13
2.2.23 Rdb/VMS Monitor Failed When the Last User
Finished on a Particular Database.............. 2-14
2.2.24 Defining a Remote Logical Name Denied Access to
the Local Node................................. 2-14
2.2.25 Transfer of Large Blocks of Data over the
Network Resulted in an Error................... 2-15
2.2.26 Problem When Database Was Defined as Remote ... 2-15
2.2.27 RDB$REMOTE Account That Has SYSTEM as Owner
Creates Installation Problems.................. 2-16
2.2.28 Rdb/VMS Did Not Accept the Database File
Specification in a Logical Name................ 2-16
2.2.29 UPDATE Privilege Access for a Table with
DACCESS Audit Event Was Not Captured........... 2-17
2.2.30 DACCESS Audit Event Required a Minimal Set of
Privileges for Auditing to Occur............... 2-17
2.2.31 Audited Privileges for DACCESS Audit Event
for Views and View-Columns Were Not Being
Imported....................................... 2-18
x
2.2.32 Audited Privileges for DACCESS Audit Event for
Columns Were Not Being Copied to the New Column
Definition During an SQL ALTER TABLE Command... 2-18
2.2.33 Deleting and Then Creating a Logical Area and
Accessing the Schema Caused a Page Checksum
Bugcheck....................................... 2-18
2.2.34 Partitioned Sorted Index Stored the First
Record Incorrectly............................. 2-19
2.2.35 Partitioned Sorted Indexes Resulted in Various
Problems....................................... 2-19
2.2.36 You Could Not Define Views Based on System
Relations...................................... 2-20
2.2.37 Altering Domain and Column Attribute Caused
FLDINUSE Error................................. 2-21
2.2.38 Unnecessary Privileges Were Stored for the
Database or Tables............................. 2-22
2.2.39 BUGCHECK Occurred on Rollback/Redefinition of
CREATE STORAGE MAP . . . STORE LIST Statement... 2-23
2.2.40 Problem in PSII$DELETE_EMPTY_NODE Caused an
Error.......................................... 2-24
2.2.41 Defining a Partitioned Hashed Index Resulted in
Corruption..................................... 2-24
2.2.42 Unable to Delete a Collating Sequence That Was
No Longer in Use............................... 2-25
2.2.43 Nontext Fields with Collating Sequences
Produced Incorrect Results with Constraints.... 2-26
2.2.44 CHANGE DATABASE Statement Resulted in Bad
Parameter Error Message........................ 2-26
2.2.45 Change in Operation of Index Deletion ......... 2-27
2.2.46 Privilege Violation in Batch-Update Caused
Database Corruption............................ 2-28
2.2.47 Collating Sequence Problems ................... 2-28
2.2.48 Improper Error Displayed When Exporting
and Importing to Use a Different Collating
Sequence....................................... 2-29
2.2.49 ALTER STORAGE MAP Statement Caused a Bugcheck
When Combined with Disabling Compression....... 2-29
2.2.50 Synchronization Problem for an Empty Sorted
Index.......................................... 2-30
2.2.51 Defining a View Caused a Bugcheck When a Sorted
Index Was Previously Defined................... 2-31
2.2.52 Collating Sequences That Used Two-to-Two
Character Mapping Could Bugcheck............... 2-33
xi
2.2.53 Disabling Compression When Changing a Table's
Storage Map to Use a Different Storage Area
Caused a Bugcheck.............................. 2-33
2.2.54 REORGANIZE Clause Caused a Bugcheck ........... 2-34
2.2.55 REORGANIZE PAGES Had No Affect When Moving
Records from the RDB$SYSTEM Storage Area into
a New Storage Area and Placing Them Using an
Index.......................................... 2-34
2.2.56 Area Allocation Was Displayed Incorrectly ..... 2-35
2.2.57 Several Miscellaneous Problems Occurred with
Triggers....................................... 2-35
2.2.58 Interaction of a Trigger with the Modify of
a Segmented String May Cause a "NO_RECORD"
Failure on Commit.............................. 2-36
2.2.59 Problem with Improper Interactions Between
Trigger Actions and Triggering Statements...... 2-37
2.2.60 An Incorrect Value Was Stored or a Bugcheck
Resulted When Using BEFORE UPDATE or BEFORE
MODIFY Triggers................................ 2-37
2.2.61 Certain Queries Using Substrings Produced
Incorrect Results.............................. 2-38
2.2.62 Updating the Cardinality of a Relation Caused a
Bugcheck....................................... 2-39
2.2.63 CONTAIN Predicate Caused Uppercase Type in the
Second Byte of Some Two-Octet Characters....... 2-39
2.2.64 Negate Operator Incorrectly Propagated the NULL
Bit While Processing a Record Stream........... 2-39
2.2.65 UPDATE Operation Stored Incorrect Results ..... 2-41
2.2.66 UPDATE Operation Caused a Bugcheck ............ 2-42
2.2.67 Unacceptable VALID IF from CDO ................ 2-42
2.2.68 MODIFY Operation Caused Index Corruption on
Partitioned Hash Indexes....................... 2-43
2.2.69 RDB$DBKEY_LENGTH System Field Was Incorrect for
Certain Views.................................. 2-44
2.2.70 View Updates and the CHECK OPTION Clause Worked
Incorrectly.................................... 2-45
2.2.71 Problem Comparing Different Data Types ........ 2-46
2.2.72 COMPUTED BY Columns Allocated Space Within the
Stored Row..................................... 2-47
2.2.73 Unnecessary Evaluation of Certain Types of
Constraints Occurred on Record Insertion ...... 2-49
2.2.74 Unexpected Lock Conflict on Freeze Error
Messages Was Possible.......................... 2-51
xii
2.2.75 Arithmetic Overflow Happened Unnecessarily for
SUM Function................................... 2-52
2.2.76 Comparing Integer and Text Fields Caused
Problems....................................... 2-53
2.2.77 Arithmetic Exception Resulted When Joining
Integer Columns................................ 2-53
2.2.78 COBOL Lines Were Generated That Did Not Comply
with the ANSI Standards........................ 2-54
2.2.79 Optimization of Constraint Processing Was
Limited........................................ 2-54
2.2.80 Rdb/VMS Hung on a SELECT Statement When a
Column Data Type Was Changed from INTEGER to
CHARACTER to DATE.............................. 2-54
2.2.81 NOWAIT Transactions Had Their Buffers
Invalidated at Commit Time..................... 2-56
2.2.82 Bugcheck Occurred with an Exception at
RDMS$$RSS$ASN_FOR_RSS$NDX...................... 2-56
2.2.83 INSERT Command to Double the Contents of a
Table Resulted in an I/O Loop.................. 2-57
2.2.84 Query Using Descending Indexes Returned
Incorrect Results.............................. 2-57
2.2.85 Query with SQL LIKE Returned Incorrect
Results........................................ 2-58
2.2.86 Query with Compressed Indexes Returned
Incorrect Results.............................. 2-59
2.2.87 Query Returned Incorrect Results .............. 2-62
2.2.88 Poor Performance Was Observed with Queries
Using Dynamic OR Optimization Within the Leaf
Retrieval...................................... 2-62
2.2.89 Query Returned Records in Wrong Order if Sorted
in Descending Order with the SQL ORDER BY
<column-name> DESCENDING or the RDO SORTED BY
DESCENDING <field> Clauses..................... 2-63
2.2.90 Shared Write Queries Consumed More Memory Than
Expected....................................... 2-64
2.2.91 Query Optimizer Caused Various Bugchecks When
Queries Were Run............................... 2-64
2.2.92 Poor Performance Was Experienced While
Retrieving Views by Dbkey...................... 2-65
2.2.93 Wrong Results Were Returned from Queries That
Used Collating Sequences and the STARTING WITH
"" Relational Operator......................... 2-66
xiii
2.2.94 Queries with Computed Expressions and Indexes
Returned the Wrong Results..................... 2-67
2.2.95 Queries with Computed Expressions Returned the
Wrong Results.................................. 2-68
2.2.96 Key-Only Boolean Optimization Was Underutilized
by the Query Optimizer......................... 2-68
2.2.97 Total Relation Boolean Was Not Generated
Properly in a Leaf Strategy.................... 2-70
2.2.98 Using Placement via Sorted Index Clause to
Store Rows in a Uniform Area Could Cause the
Wrong Query Optimizer Strategy to Be Chosen.... 2-70
2.2.99 Incorrect Results Were Returned on Join
Operations Using Partitioned Indexes........... 2-71
2.2.100 Join Query Matched a Null Aggregate or
Expression to a Column with Zeros or Blanks and
Produced Incorrect Results..................... 2-72
2.2.101 Read/Write Query with Multiple Range Predicates
on an Index Column Performed Poorly............ 2-72
2.2.102 Query Optimizer Did Not Choose Index-Only
Retrieval When the Dbkey Was Selected.......... 2-73
2.2.103 Optimizer Did Not Recognize That an Index
Segment Was Compressed......................... 2-75
2.2.104 Multisegmented Index Was Not Selected When a
Not-Equal Predicate Was Specified.............. 2-75
2.2.105 Singleton Subselect Statement Returned
Incorrect Results.............................. 2-76
2.2.106 Query with a FOR Loop and MODIFY Statement
Followed by a PRINT Statement Returned
Incorrect Results.............................. 2-78
2.2.107 Query with a Computed-By Field and OR Logic
Returned Incorrect Results..................... 2-78
2.2.108 Query with Keys Scanned the Index Instead of
Using Direct Tree Lookup....................... 2-79
2.2.109 RDBPRE Query Using Dynamic OR Optimization
Returned Wrong Results......................... 2-79
2.2.110 SQLMOD Query Returned Empty Rows .............. 2-80
2.2.111 Under Certain Circumstances a Committed Update
Was Not Completely Written to the .AIJ File.... 2-81
2.2.112 Problem with SPAM Thresholds in a Recover
Operation...................................... 2-81
2.2.113 After-Image Journal File Create or Modify
Operation Allowed Nonsystem Concealed Logical
Names to Be Used............................... 2-81
xiv
2.2.114 Recovery Failed When It Tried to Undo a Create
Logical Area Operation......................... 2-82
2.2.115 Recovery-Unit Journal (.RUJ) Files Could Not Be
Created Using Angle Brackets (<>).............. 2-82
2.2.116 Bugcheck Occurred if the .AIJ File Could
Not Be Opened, Possibly Resulting in LAT
Termination.................................... 2-83
2.2.117 RMU/MONITOR/START Operation at System Startup
with Global Buffers Enabled Could Result in an
Exceeded Quota Error........................... 2-83
2.3 SQL Software Errors Fixed in V4.1................ 2-85
2.3.1 ALTER DOMAIN Statement Did Not Allow the DATE
Data Type to Be Converted to VARCHAR........... 2-85
2.3.2 Altering Column from One Data Type to Another
Could Cause Various Exceptions When Records
Containing NULL Values in That Column Were
Accessed....................................... 2-85
2.3.3 ALTER STORAGE MAP Command Resulted in the Loss
of Data........................................ 2-86
2.3.4 Importing a Database with Tables Containing
Lists (Segmented Strings) Failed............... 2-86
2.3.5 SQL IMPORT Printed Dates in the User's
Format......................................... 2-87
2.3.6 Source Attributes for Storage Maps Were Not
Saved in Pre-V3.1 SQL IMPORT Operations........ 2-87
2.3.7 INDEX Percent Fill and Node Size Information .. 2-88
2.3.8 Complete Export Consistency ................... 2-89
2.3.9 More Informative Error Message ................ 2-89
2.3.10 Earlier Information Not Previously Exported ... 2-90
2.3.11 Certain Trigger Definitions Caused a
Bugcheck....................................... 2-91
2.3.12 Triggers Created with Long Source Text Strings
Were Improperly Displayed...................... 2-92
2.3.13 Triggers Created from Programs Had Their Source
Text Truncated by a Word....................... 2-93
2.3.14 SQL_DYNAMIC.C Sample Program Error Is Fixed ... 2-94
2.3.15 Null Handling Problem Occurred When Using SQL
Module Language and Dynamic SQL Availability... 2-95
2.3.16 Incorrect Conversion of Numeric Data Types
Caused Erroneous Values for Scales............. 2-96
2.3.17 SQLTYPE Value Fixed in the SQLDA .............. 2-96
xv
2.3.18 Bugcheck Returned by the System When a User
with Incorrect Privileges Showed Protection on
a Schema....................................... 2-97
2.3.19 Record Parameters Could Not Be Used Where
Values Were Expected........................... 2-97
2.3.20 SQL Incompatibilities in Rdb/VMS V4.0 That Are
Fixed in Rdb/VMS V4.0B and V4.1................ 2-97
2.3.20.1 Incompatibilities Between Object Modules.... 2-97
2.3.20.2 Incompatibilities Between TABLE and LIST
Cursors..................................... 2-99
2.3.20.3 Incompatibilities Between Cursors and COMMIT
or ROLLBACK Statements...................... 2-99
2.3.21 Message Vector Contained Erroneous Information
About the Number of Longwords Used............. 2-99
2.3.22 Data Dictionary (CDD/Plus) Fields with Scales
Were Not Always Properly Translated............ 2-99
2.3.23 UNION Queries Returned Incorrect Data on
Numeric Data Types............................. 2-100
2.3.24 Modules That Used the DECLARE TRANSACTION
Statement Were Ignoring TXN Attributes......... 2-100
2.3.25 Embedded SQL Ada Programs Could Not Use LIST
Cursors........................................ 2-100
2.3.26 Views That Selected Dbkeys Caused a Bugcheck .. 2-100
2.3.27 Dynamic SQL Statements with Indicator Arrays
Were Not Correctly Handled Prior to Rdb/VMS
V4.0B.......................................... 2-101
2.3.28 SQL Allocated More Memory Than Necessary During
a Dynamic SET TRANSACTION Statement............ 2-101
2.3.29 Preparing a Statement with D-Float Parameters
Caused a Bugcheck.............................. 2-101
2.3.30 Memory Was Lost Between Database Attaches ..... 2-101
2.3.31 Using the IGNORE CASE Option of the LIKE Clause
Sometimes Resulted in a Query That Incorrectly
Returned No Rows............................... 2-102
2.3.32 SQL SELECT Statement Resulted in an Invalid BLR
Error.......................................... 2-103
2.3.33 Storage of Records Containing NULL Values Using
Dynamic SQL Caused an Arithmetic Exception..... 2-103
2.3.34 Input VARCHAR Parameter Actual Value Was Longer
Than Procedure Parameter....................... 2-103
2.3.35 SELECT Operation of an Altered Table Resulted
in an Infinite Loop............................ 2-104
xvi
2.3.36 Opening a Cursor That Was Already Opened Caused
the Cursor to Lose Its State................... 2-105
2.3.37 Executing the ROLLBACK Statement with OPEN
List Cursors Left List Cursors in an Unusable
State.......................................... 2-107
2.3.38 Executing the COMMIT Statement with OPEN List
Cursors Did Not Commit the Newly Created
Lists.......................................... 2-113
2.3.39 OPEN Statement of an INSERT TABLE CURSOR Did
Not Properly Return Error Status............... 2-116
2.3.40 Records Included from the Data Dictionary in
the C Preprocessor Did Not Null-Terminate
Character Strings.............................. 2-120
2.4 SQL/Services Errors Fixed in V4.1................ 2-121
2.4.1 Trailing Characters Appeared on SQL/Services
Sample Program Error Messages.................. 2-121
2.4.2 sqlsrv_fetch_many Routine Caused Problems on
the Macintosh Platform......................... 2-121
2.4.3 Unpredictable Results Occurred When Trying to
Store Segmented Strings from the Macintosh
Environment Using DECnet....................... 2-122
2.4.4 Certain Calls to the sqlsrv_fetch_many Routine
Caused Problems................................ 2-122
2.4.5 Authorization Failure Occurred When SYSUAF Flag
LOCKPWD Was Set................................ 2-122
2.4.6 Column Limit Raised to 500 .................... 2-123
2.4.7 SQL/Services Failure Did Not Produce a Bugcheck
File........................................... 2-123
2.4.8 SQL$STARTUP.COM Startup File Contained an Error
in the SQL/Services Startup Logical Name....... 2-123
2.4.9 Node Names Containing Numeric Characters Were
Improperly Made Uppercase in OS/2 API.......... 2-123
2.4.10 SQLSRV_ASCII_STRING Data Type and NULL
Termination.................................... 2-124
2.4.11 Compilation Error Fixed for SQL/Services ULTRIX
Sample Application............................. 2-124
2.4.12 SQL/Services MS-DOS IVP Failure Is Fixed ...... 2-124
2.4.13 SQL/Services Length Packet Split Problem Is
Fixed.......................................... 2-124
2.4.14 Problem Freeing Network Connections for
SQL/Services ULTRIX API Is Fixed............... 2-125
2.4.15 VMS Application Programming Interface (API)
Installation Failed Without Rdb/VMS............ 2-125
xvii
2.4.16 Failure of SQL/Services Communication Server to
Report Error Status Is Fixed................... 2-125
2.4.17 SQL/Services Shutdown Procedure Problem Is
Fixed.......................................... 2-125
2.4.18 SQL/Services Startup File Changes ............. 2-126
2.4.19 SQL/Services Macintosh API Code Fixes ......... 2-126
2.4.19.1 SQL/Services Now Allows Changing the Name of
the Macintosh System Folder................. 2-126
2.4.19.2 SQL/Services SQLSRV$Volume installation
Volume Can Now Be Accessed on the
Macintosh................................... 2-127
2.4.19.3 SQL/Services Now Looks for PATHWORKS DECnet
Tool Instead of DECtask Tool................ 2-127
2.4.19.4 SQL/Services Macintosh API Fix for Macintosh
Systems Based on the Motorola 68000 Chip.... 2-127
2.4.20 SQL/Services Proxy File Fixes ................. 2-127
2.4.21 SQL/Services Client Application Logging
Fixes.......................................... 2-128
2.4.22 SQL/Services API Fetch Routines Are Fixed ..... 2-128
2.4.23 More Pertinent Error Message Is Returned
When Passing an Invalid Class Name in the
sqlsrv_set_server_class Routine................ 2-128
2.5 RDO and RDBPRE Software Errors Fixed in V4.1..... 2-128
2.5.1 Bugcheck Was Returned Because the CHANGE FIELD
Had No VALID IF Clause......................... 2-129
2.5.2 Incorrect Value Was Stored During an RDO STORE
or MODIFY Operation............................ 2-130
2.5.3 Multiple RDO Statements in a FOR Loop Caused
Unpredictable Results.......................... 2-130
2.5.4 Query Using Static OR and a Common
Subexpression in Two or More OR Legs Produced
Incorrect Results.............................. 2-132
2.5.5 RDO IMPORT Operation Did Not Save All SQL
Defined Attributes............................. 2-133
2.5.6 RDO IMPORT Statement Default Is CDD_LINKS, as
Documented..................................... 2-133
2.5.7 RDO IMPORT Failed to Detect Illegal Zero Values
Specified on the IMPORT Command................ 2-133
2.5.8 Problem with Callable RDO and Varying String
Descriptors.................................... 2-135
2.5.9 RDB$INTERPRET Now Fully Supports VS (Varying
String) Descriptors............................ 2-136
xviii
2.5.10 RDBPRE Generated Incorrect Code for Request
Handles........................................ 2-136
2.5.11 Wrong Number of Records Was Returned by a Query
in an Inner FOR Loop........................... 2-138
2.6 RDML Software Errors Fixed in V4.1............... 2-138
2.6.1 Shared Fields in a Relation and a View Caused a
Bugcheck....................................... 2-139
2.6.2 RDML-E-READ_ONLY Error Was Returned When
Attempting to Update COMPUTED BY Fields........ 2-139
2.6.3 Precompiler Aborted When Processing RDB$MISSING
in an RSE...................................... 2-139
2.6.4 Code Was Incorrectly Generated for RDB$MISSING
in the GET Statement........................... 2-140
2.6.5 Error Resulted in Generating Double Precision
Constants for Pascal........................... 2-140
2.6.6 Changing Records in CDD/Plus Caused an Error .. 2-141
2.6.7 CDD/Plus Informational Message Caused RDML to
Abort Compilation.............................. 2-141
2.6.8 RDML Generated a Fatal Error Message When
It Encountered an Informational CDD/Plus
Message........................................ 2-141
2.6.9 Problem with RDML Read-Only Field ............. 2-142
2.7 RMU Software Errors Fixed in V4.1................ 2-142
2.7.1 TA90E and TA91 Tape Drives Were Not Recognized
in Rdb/VMS V4.0 and V4.0A...................... 2-142
2.7.2 Returned DCL $STATUS Was Inconsistent Between
RMU Commands................................... 2-143
2.7.3 Index Cardinality Was Not Maintained When an
Application Performed Only a Few Insertions or
Deletions...................................... 2-143
2.7.4 Behavior of RMU/BACKUP Was Confusing When Tape
Volume Protection Was Used..................... 2-143
2.7.5 RMU/BACKUP Command Was Incompatible with the
VMS MOUNT/NOUNLOAD Commands.................... 2-144
2.7.6 RMU/BACKUP Command Required Tapes on Each Drive
to Be Mounted.................................. 2-144
2.7.7 RMU/BACKUP Command Failed to Overlap the
Rewind-Dismount and Write of the Next Volume... 2-144
2.7.8 VMS BACKUP Was Incompatible with the RMU/BACKUP
and RMU/RESTORE Commands....................... 2-144
2.7.9 Problems with Tape Label Checking in the
RMU/BACKUP and RMU/RESTORE Commands............ 2-145
xix
2.7.10 RMU/RESTORE Stopped Searching a Tape for
the Named Backup File if It Encountered a
Zero-Length File............................... 2-146
2.7.11 AIJ No-Quiet-Point Backup Could Corrupt the
.AIJ File...................................... 2-146
2.7.12 /INTERVAL Qualifier of the
RMU/BACKUP/AFTER_JOURNAL Command Miscalculated
a Specified Interval Value..................... 2-147
2.7.13 RMU/CLOSE/CLUSTER/WAIT/ABORT=FORCEX Command
Hung the Database.............................. 2-147
2.7.14 Privileges Required to Execute the RMU/DUMP,
RMU/OPEN, and RMU/CLOSE Commands............... 2-147
2.7.15 RMU/CONVERT Command Failed with a Default
Collating Sequence Defined..................... 2-148
2.7.16 RMU/COPY or RMU/MOVE Command Incorrectly Copied
Area Inventory Pages (AIP)..................... 2-149
2.7.17 DISTRIBTRAN Privilege Was Not Audited Correctly
by Security Auditing Facility.................. 2-149
2.7.18 Problem with RMU/SHOW USERS and RMU/SHOW SYSTEM
Commands and VMS WORLD Privileges.............. 2-149
2.7.19 RMU/REPAIR Command Caused Database
Corruption-Problem I........................... 2-150
2.7.20 RMU/REPAIR Command Caused Database
Corruption-Problem II.......................... 2-150
2.7.21 SPAM Threshold Calculations Resulted in Errors
Reported by the RMU/VERIFY Command............. 2-150
2.7.22 RMU/VERIFY Reported False AIPENTMBZ Warning
Messages....................................... 2-150
2.7.23 Hashed Index Verification Caused Buffer
Flushing Problem............................... 2-151
2.7.24 RMU/VERIFY Did Not Properly Verify
Databases Converted to V4.1 Using the
RMU/CONVERT/NOCOMMIT Command................... 2-151
2.7.25 RMU/VERIFY Could Report False BADSEGDBK
Errors......................................... 2-152
2.7.26 RMU/VERIFY Did Not Do .AIJ File Verification if
the User Did Not Have Sufficient Privileges to
Open the .AIJ File............................. 2-152
2.7.27 RMU/VERIFY/INDEX or RMU/VERIFY/ALL Command
Caused a Bugcheck if You Had Hashed Indexes
Defined........................................ 2-153
2.7.28 RMU/VERIFY Command Returned Spurious Errors
Involving Fragmented Records................... 2-153
xx
2.7.29 RMU/VERIFY Command Reported False MINGTRSIZ
Error Messages ................................ 2-154
2.8 Software Errors Related to CDD/Plus Fixed in
V4.1............................................. 2-154
2.8.1 Using CDD/Plus to Specify Collating Sequences
Returned an Error.............................. 2-154
2.8.2 SQL or RDO Hung During Integrate Operation and
a Ctrl/Y Was Not Possible...................... 2-155
2.9 CDD/Plus Software Errors Fixed That Affected
Rdb/VMS.......................................... 2-155
2.10 Software Errors Fixed in Rdb/VMS That Affected
Other Products................................... 2-156
2.10.1 Invalid Data Was Sent to DECtrace for the
Transaction Event.............................. 2-156
3 Known Problems, Restrictions, and Other Notes
3.1 Known Problems and Restrictions for General
Knowledge in V4.1................................ 3-1
3.1.1 Error Message Files Are Altered by DECpresent
V1.0........................................... 3-1
3.1.2 VMS Lock Remastering Changed in VMS V5.4 ...... 3-2
3.1.3 Multiversion Problem Exists in V4.1 Process
Environment When You Run RMONSTOP and RMONSTART
Procedures for Either V4.0 or V4.0A............ 3-2
3.2 Known Problems and Restrictions for All
Interfaces in V4.1............................... 3-4
3.2.1 System Table Changes from V4.0 to V4.1 Cause
DEC RdbExpert for VMS to Fail with NONULLIND
Error.......................................... 3-4
3.2.2 Placement Using Sorted Indexes Can Result in
Performance Degradation over Time as Rows Are
Added and Index Key Values Updated............. 3-5
3.2.3 Deleted Space Is Not Reused Until the Next
Database Attach................................ 3-6
3.2.4 Digital Does Not Support Access to Rdb/VMS
Through an Asychronous System Trap (AST)
Service Routine in a User Application.......... 3-8
3.2.5 V4.1 Multiplex Feature Should Be Disabled if
Remotely Attaching to V4.1 and V3.1 or V4.0
Databases...................................... 3-9
xxi
3.2.6 Segmented String Records Should Match the
Page Size When Stored on WORM Optical Disk
Devices........................................ 3-9
3.2.7 VAX RALLY Application Fails When Snapshots Are
Enabled Deferred............................... 3-9
3.2.8 Using Quoted Threshold Values for Binary Data
Types for Partitioning Data or Indexes Results
in Data or Index Corruption.................... 3-11
3.2.9 Triggers That Affect Subject Table Rows Can
Cause Loops or Inconsistent Results............ 3-12
3.2.10 Collating Sequences Producing Too Many Nulls
May Result in a Bugcheck Dump.................. 3-13
3.2.11 Sequential Retrieval Causes Problems with
Dynamic Optimizer.............................. 3-14
3.2.12 You Cannot Correctly Import a Database That
Contains Computed-By Columns That Reference
Other Computed-By Columns...................... 3-14
3.2.13 Table Name Must Match Dictionary Record Name .. 3-15
3.2.14 Certain Reserved Words Cannot Be Used as
Database Handles for RDBPRE or as Aliases for
SQL$PRE and SQL$MOD............................ 3-15
3.2.15 Transaction Handle and Messages Vector Are Not
Always Available in Callable RDO............... 3-16
3.3 SQL Notes of General Interest for V4.1........... 3-16
3.3.1 Using the SMALLINT Data Type and VAX C ........ 3-17
3.4 SQL Known Problems and Restrictions in V4.1...... 3-18
3.4.1 SQL Users Using the Multiversion Kit Must Link
with the SQL$USER Logical Name................. 3-19
3.4.2 Using LIST STORAGE AREA Clause on CREATE
DATABASE Statement Causes Unexpected I/O to
WORM Device.................................... 3-19
3.4.3 You Cannot Import a Multischema Database to
Earlier Versions............................... 3-20
3.4.4 You Cannot Use EXPORT WITH NO EXTENSIONS if
INTERVAL Is Defined............................ 3-20
3.4.5 SQL Object Module Incompatibility ............. 3-20
3.4.6 Confusion Over the Use of ASCII and ASCIZ in
Dynamic SQL and C Programs..................... 3-21
3.5 SQL/Services Known Problems and Restrictions in
V4.1............................................. 3-22
3.5.1 Process Logical Names Are Not Supported in
SQL/Services................................... 3-22
xxii
3.5.2 SQL/Services Compatibility Issue with the Order
of Include Files............................... 3-22
3.5.3 New DATE, TIME, TIMESTAMP, and INTERVAL
Data Types Are Not Directly Supported by
SQL/Services................................... 3-23
3.5.4 Problem with SQL/Services CDev for the
Macintosh and the New Release of PATHWORKS for
Macintosh V1.1................................. 3-24
3.5.5 Invalid Length Is Returned by SQLSRV_VARBYTE
Data Type...................................... 3-25
3.5.6 Allocating Space for SQLSRV_VARCHAR and
SQLSRV_VARBYTE Data Types Can Cause a
Problem........................................ 3-25
3.5.7 SQL/Services Compatibility Issues ............. 3-25
3.5.7.1 SQL/Services (Version 4.0 or Higher)
Server Uses Proxy-Like and Default Access
to Authorize Version 3.0 or 3.1 Client
Applications................................ 3-26
3.5.7.2 SQL/Services V4.0 Server Error -2031
Returned to V3.1 Client APIs................ 3-26
3.5.7.3 SQL/Services IVP Failure Caused by -2003
Network Error............................... 3-26
3.6 RDO and RDBPRE Known Problems and Restrictions in
V4.1............................................. 3-27
3.6.1 RDO Restrictions in V4.1 ...................... 3-27
3.6.2 RDO CONVERT Operation on V3.0 Databases Causes
Database Corruption When the Database Is
Converted to V4.0, V4.0A, V4.0B, or V4.1....... 3-27
3.6.3 RDO Export Operation May Return SQL Error
Messages....................................... 3-28
3.6.4 RDO Import Operation Error Message Results from
Importing a Multischema Database Referencing
CDD/Plus....................................... 3-28
3.6.5 Aggregate Expressions in RDO Return an Error .. 3-29
3.6.6 Loss of Precision for Text to Quadword
Assignment in RDB$INTERPRET.................... 3-30
3.7 RMU Known Problems and Restrictions in V4.1...... 3-31
3.7.1 Problems Using RMU Commands with SYSMAN ....... 3-31
3.7.2 Problem Using VMS ALLOCATE Command with RMU
Commands....................................... 3-31
3.7.3 Concealed Logicals Are Supported but No Longer
Recommended for Use After V4.0................. 3-32
xxiii
3.7.4 Restrictions on Using RMU Commands with the
/WORM and /NOSPAMS Qualifiers.................. 3-32
3.7.5 Do Not Delete After-Image Journal (.AIJ)
Backup Files if the AIJ Backup Fails or Is
Terminated..................................... 3-33
3.7.6 RMU/EXTRACT Known Problems and Restrictions in
V4.1........................................... 3-34
3.7.6.1 VALID IF Clauses Are Converted to Domain
Level CHECK() Constraints .................. 3-34
3.7.6.2 Triggers in RDO Allow Users to Define a
Trigger Action Within a Join of Two or More
Tables...................................... 3-34
3.7.6.3 VMS Style Dates and CURRENT_TIMESTAMP....... 3-35
3.7.6.4 Blank Lines in Multiline Descriptions Are
Removed by RDO.............................. 3-36
3.7.6.5 RMU/EXTRACT Column Default Value
Restriction................................. 3-36
3.7.6.6 Partial Support for RdbAccess for VAX RMS Is
Provided by Rdb/VMS V4.1.................... 3-37
3.7.7 Attaches to an "OPEN IS MANUAL" Database That
Is Currently Closed Are Not Allowed............ 3-37
3.7.8 Clarification on the Meaning of "Granted"
and "Requested" in RMU/SHOW LOCKS Output
Displays....................................... 3-38
3.7.9 False BADFNMAIJ Errors Are Returned from
RMU/VERIFY During Root Verification............ 3-38
3.8 CDD/Plus (CDD/Repository) Known Problems,
Restrictions, and Notes for V4.1................. 3-40
3.8.1 Using CDD/Repository Global Fields with
Rdb/VMS........................................ 3-40
3.8.2 Compatibility of CDD/Repository (Previously
Called CDD/Plus) and Rdb/VMS................... 3-44
3.9 CDD/Plus V4.2A and V4.3 Known Problems and
Restrictions for V4.1............................ 3-47
3.9.1 Changing the Rdb/VMS Database File Name ....... 3-47
3.9.2 Using CDD/Plus in a Multiversion Environment .. 3-48
3.9.3 New DATE Data Types Are Not Supported by
CDD/Plus V4.3.................................. 3-49
3.9.4 SQL Interface Restrictions for CDD/Plus V4.3
and Earlier Versions........................... 3-49
3.9.5 Integrate Sometimes Fails with a NO_META_UPDATE
Error.......................................... 3-49
3.9.6 CDD/Plus V4.2 and V4.3 Restriction ............ 3-50
xxiv
3.10 Restrictions Lifted by CDD/Plus Version 4.3...... 3-50
3.11 Rdb/VMS Documentation Errors and Omissions for
V4.1............................................. 3-50
3.11.1 Buffer Management Changes for V4.0 ............ 3-50
3.11.2 New Logical Name RDM$MON_USERNAME Is Not
Documented in the Main V4.1 Documentation
Set............................................ 3-53
3.11.3 Maintenance Operations for and Characteristics
of WORM Media Not Previously Described in the
V4.1 Documentation............................. 3-53
3.11.3.1 Maintenance Operations on WORM Media........ 3-53
3.11.3.2 WORM Media Characteristics and Assumptions.. 3-55
3.11.4 Database Key Recommendation Is Clarified ...... 3-58
3.11.5 Specification of Correlation Name in Table
References Is Restricted....................... 3-59
3.11.6 Correction for Database Keys Example .......... 3-59
3.11.7 RMU/SHOW STATISTICS Formatted Binary Output
File Changes Are Not Documented................ 3-59
3.11.8 Description of the Space Used by the New
Segmented String Structures on WORM Disks in
the VAX Rdb/VMS Guide to Database Maintenance
Is Incorrect................................... 3-62
3.11.9 SQLDA2 Null Indicator Data Type Is Incorrect .. 3-63
3.11.10 SQL ALTER DOMAIN and RDO CHANGE FIELD Statement
Restriction Is Undocumented.................... 3-63
3.11.11 Examples of Specifying Preferred Optimization
Mode Show Incorrect SQL Syntax................. 3-64
3.11.12 Reference to Example of Initialized Parameter
Is Incorrect................................... 3-65
3.11.13 INSERT Statement VALUES Clause Is Missing an
Argument....................................... 3-65
3.11.14 Value Returned by AVG Function ................ 3-65
3.11.15 CREATE STORAGE AREA Is Missing List Storage
Area Syntax.................................... 3-65
3.11.16 CREATE STORAGE MAP STORE LISTS Clause Syntax Is
Incorrect...................................... 3-66
3.11.17 Embedding Quotation Marks in Literals Table Is
Incorrect...................................... 3-67
3.11.18 Not All CDO and DTR Edit Strings Are Accepted
by SQL......................................... 3-68
3.11.19 Corrections to Tables in the VAX Rdb/VMS Guide
to Using SQL/Services.......................... 3-69
xxv
3.11.20 The VAX Rdb/VMS RDO Reference Manual Appendix C
Omits VAX Data Distributor Commands............ 3-72
3.11.21 RDB$MISSING Is Evaluated at Compile Time, Not
Run Time....................................... 3-73
3.11.22 SORTWORKn Logical Name Description Is
Inaccurate..................................... 3-75
3.11.23 Clarification on Setting the Read-Only
and Read/Write Attributes for the
RDB$SYSTEM Storage Area and Using the
RMU/ANALYZE/CARDINALITY/UPDATE Command......... 3-76
3.11.24 Clarification on the Relationship Between
the Number of Users and the Number of Nodes
Supported on a Database........................ 3-76
3.11.25 Clarification on Why Snapshot Files Grow in
Size........................................... 3-77
3.11.26 Request Handle Syntax Is Treated Differently in
Statistical Expressions........................ 3-79
3.11.27 Documentation Error Regarding Microsoft
C Compatible Assembler Required for the
SQL/Services MS-DOS Application Programming
Interface (API) Installation................... 3-80
3.11.28 Description of RMU/SHOW STATISTICS Checkpoint
Display Needs Clarification.................... 3-80
3.11.29 Using RDML and the Two-Phase Commit Protocol
by Calling the DECdtm System Service Calls
Implicitly and Explicitly Is Not Fully
Documented..................................... 3-81
3.11.30 Explanation of the "Lock Conflict on Freeze"
Errors......................................... 3-82
3.11.31 Explanation of RMU/SHOW STATISTICS Blocking
AST............................................ 3-84
3.11.32 Clarification of Behavior of the RDO F_FLOATING
Data Type...................................... 3-85
3.11.33 Explanation of RMU/SHOW STATISTICS Stall
Messages....................................... 3-86
3.11.34 RDMS$BIND_VALIDATE_CHANGE_FIELD Logical Name
Was Not Documented in V4.0..................... 3-88
3.11.35 RDMS$BIND_VM_SEGMENT Logical Name Was
Misnamed in the V3.1 Documentation and Was Not
Documented in V4.0............................. 3-88
3.12 SQL/Services Troubleshooting Suggestions......... 3-88
3.12.1 Common SQL/Services Network Errors ............ 3-88
xxvi
3.12.2 Common SQL/Services Fatal Execution Server
Errors......................................... 3-90
3.12.3 Common SQL/Services API Installation
Failures....................................... 3-91
3.12.4 SQL/Services Compatibility Issues ............. 3-91
3.12.4.1 SQL/Services V4.0, V4.0A, V4.0B, and V4.1
Server Uses Proxy-Like and Default Access to
Authorize V3.0 or V3.1 Client Applications.. 3-92
3.12.4.2 SQL/Services V4.0, V4.0A, V4.0B, or V4.1
Server Error -2031 Returned to V3.1 Client
APIs........................................ 3-92
3.12.4.3 Queue Manager Must Be Started for the
SQL/Services IVP to Work.................... 3-92
3.13 Restrictions Retained from V4.0.................. 3-93
3.13.1 Known Problems and Restrictions for All
Interfaces for V4.0............................ 3-93
3.13.1.1 Improving the Performance of the Export
Operation Using the DCL SET Command to
Change the Default Extend Parameter Value... 3-93
3.13.1.2 SNAPSHOTS DEFERRED May Stall Transactions... 3-93
3.13.1.3 PLACEMENT VIA INDEX Clause Prohibits Shadow
Clustering.................................. 3-94
3.13.1.4 RDB$SYSTEM Storage Area Cannot Be Read-Only
When a Relation Is Readied in Exclusive or
Batch-Update Mode........................... 3-94
3.13.1.5 Joined Relations Do Not Allow "MODIFY" if
Using the WITH Clause....................... 3-95
3.13.1.6 Using RDML/C to Update a View Returns
Errors...................................... 3-96
3.13.1.7 DECLARE and START Stream Are No Longer
Allowed for Certain Views................... 3-98
3.13.1.8 A Clarification of the Storage Algorithm for
Mixed Pages................................. 3-98
3.13.1.9 Adjustment of Cardinalities in V4.0
Is Likely to Cause Poorer Optimizer
Performance................................. 3-99
3.13.1.10 SELECT on SCHEMA (in V4.1, SELECT on
DATABASE) (READ on DATABASE) ACE Is Now
Required.................................... 3-99
3.13.1.11 Rdb/VMS Network Link Failure Does Not Allow
FINISH (in V4.1, DISCONNECT) to Tidy Up
Transactions................................ 3-100
3.13.1.12 VAX Data Distributor Copy Processes Do
xxvii
Not Process Database Pages Ahead of an
Application................................. 3-100
3.13.1.13 Setting an Appropriate WSEXTENT Relative to
WSQUOTA for SORT/MERGE Operations........... 3-101
3.13.1.14 Attempting to Acquire a Lock Could Cause an
Infinite Loop............................... 3-102
3.13.2 SQL Known Problems and Restrictions for V4.0 .. 3-102
3.13.2.1 SQL Allows False Redefinition of the DATE
Data Type................................... 3-102
3.13.2.2 Problem Adding a New Field to CDO Defined
Record and Not to Last Position............. 3-103
3.13.2.3 Module Language Passes Extraneous Characters
with Inexact Dynamic Descriptors............ 3-104
3.13.2.4 Close List Cursor Before Fetching Rows from
Table Cursor................................ 3-105
3.13.2.5 SELECT Statement with GROUP BY Clause
Did Not Return Date Fields in EDIT STRING
Format...................................... 3-107
3.13.2.6 When Using the BETWEEN Operator, You Should
Specify the Lower Value First............... 3-107
3.13.2.7 Cautions on Using ANY, ALL, or IN Clauses in
Constraint Definitions...................... 3-108
3.13.2.8 SQL Does Not Recognize a Record During SQL
Precompile Time............................. 3-108
3.13.2.9 Release of Cursor Statements Referencing
Prepared Statements Causes Problems......... 3-109
3.13.2.10 SQL Does Not Translate Logical Names
Referencing Source Databases................ 3-109
3.13.2.11 Problem with Transferring a Table to an
Existing Database Containing the Same Table
Name........................................ 3-110
3.13.3 RDO Known Problems and Restrictions for V4.0 .. 3-111
3.13.3.1 RDO SHOW USERS and SHOW MONITOR Statements
Do Not Work for Remotely Accessed
Databases................................... 3-111
3.13.4 RDML Known Problems and Restrictions for
V4.0........................................... 3-111
3.13.4.1 RDML Generates an Error Message When
Attempting to Store or Modify Read-Only
(COMPUTED BY) Fields........................ 3-112
xxviii
3.13.5 RMU Known Problems and Restrictions for V4.0 .. 3-112
3.13.5.1 Non-Updatable Fields Cannot Be Unloaded
Using the RMU/UNLOAD Command................ 3-112
3.13.5.2 Single-File Databases Require the /ROOT
Qualifier When Using the RMU/MOVE_AREA
Command..................................... 3-113
3.13.5.3 Shortened Form of RMU/RESTORE Command Does
Not Work Properly........................... 3-113
3.13.5.4 Installing RMU with Privileges.............. 3-113
3.13.6 CDD/Plus Restrictions for V4.0 ................ 3-114
3.13.6.1 Minimum Supported Version of CDD/Plus....... 3-114
3.13.6.2 Using CDD/Plus with PERSONNEL.COM........... 3-114
3.13.6.3 Some Views Are Not Accepted by VAX CDD/Plus
V4.2........................................ 3-114
3.13.6.4 GRANT and REVOKE Statements Generate
MBLRSYNERR Message if Attached by Path
Name........................................ 3-115
3.13.7 Restrictions Lifted by CDD/Plus Version 4.2 ... 3-115
3.13.7.1 Incompatibilities Between Rdb/VMS V4.0
and CDD/Plus That Have Been Lifted by
VAX CDD/Plus Version 4.2.................... 3-115
3.14 Restrictions Retained from V3.1.................. 3-115
3.14.1 Known Problems and Restrictions for All
Interfaces for V3.1............................ 3-115
3.14.1.1 Object Modules Created with V3.1 and V4.0
Are Not Downward-Compatible................. 3-115
3.14.1.2 FIRST n Is Not Considered During
Optimization................................ 3-116
3.14.1.3 With the Exception of Views, Do Not Add
Fields to Relations, or Define Indexes,
Triggers, and Other Database Objects Based
on System Relation Fields................... 3-116
3.14.1.4 Performance Considerations for Using VARYING
STRING or COLLATING SEQUENCE Attribute for
Index Keys.................................. 3-116
3.14.1.5 Sorting or Implied Sorting for Projection on
a Dbkey Is Not Worthwhile................... 3-118
3.14.1.6 Many Attaches to and Detaches from the Same
or Multiple Databases While Using Search
xxix
Lists to Point to the Database Use Up I/O
Channel Quota............................... 3-119
3.14.1.7 Unexpected Setting of the NULL Attribute
After an IMPORT Operation................... 3-119
3.14.1.8 RDO IMPORT Statement Generates Bugcheck
Dumps if the Index Definition Fails......... 3-120
3.14.1.9 IMPORT Statement Fails to Complete Index
Definition with Users Attached to the
Database.................................... 3-122
3.14.1.10 Using LIB$DT_INPUT_FORMAT to Change Date
Input Format Sometimes Causes Access
Violation................................... 3-122
3.14.1.11 Operations on F-Floating Data Round to Whole
Numbers..................................... 3-123
3.14.1.12 Rdb/VMS Interaction with Data Distributor
V2.1 May Generate Bugcheck Dumps............ 3-123
3.14.1.13 Batch-Update Transactions Can Cause
a Bugcheck Dump to Occur if an Index
Definition Fails............................ 3-124
3.14.1.14 Rdb/VMS Logical Name, RDMS$BIND_WORK_VM, Has
an Upper Limit of 65,000 Bytes.............. 3-124
3.14.1.15 Reserving a Table in Exclusive Mode May
Prevent Operations from Being Performed on
Other Tables in the Same Storage Area....... 3-124
3.14.1.16 Problem Defining COLLATING SEQUENCE IS
NORWEGIAN NORWEGIAN......................... 3-125
3.14.1.17 Snapshot File Name, File Type, or Version
Number Cannot Be Changed for Single-File
Databases................................... 3-126
3.14.1.18 Rdb/VMS and VMS Debugger Interaction........ 3-127
3.14.1.19 Using Rdb/VMS from a VMS Detached Process... 3-129
3.14.2 SQL Known Problems and Restrictions for V3.1 .. 3-130
3.14.2.1 DDL Statements Cannot Refer to Objects
Before Their Creation....................... 3-130
3.14.2.2 Deleting Metadata in Rdb/VMS................ 3-131
3.14.2.3 SQL Schema Compilation Fails on the First
Fatal Error................................. 3-131
3.14.2.4 COMMENT ON Statement Cannot Be Used in
CREATE SCHEMA Statement..................... 3-132
3.14.2.5 Dynamic Cursors Cannot Access Views Created
with GROUP BY or UNION Clause............... 3-132
3.14.2.6 You Cannot Use INCLUDE Statement in Variable
Declaration................................. 3-132
xxx
3.14.2.7 SQL Ada Precompiler Does Not Support the
Correct Overloading of Subprograms.......... 3-132
3.14.2.8 SQL Precompiler Does Not Evaluate
Expressions in Variable Declarations or
Understand Literals......................... 3-133
3.14.2.9 SQL Ada Precompiler Does Not Support Named
Literals or Ranges.......................... 3-134
3.14.2.10 SQL Module Language Processor Fails on the
First Fatal Error........................... 3-134
3.14.3 SQL/Services Known Problems and Restrictions
for V3.1....................................... 3-135
3.14.3.1 Disable VAX SQL/Services V1.0 Startup
Procedure................................... 3-135
3.14.4 RDO and RDBPRE Known Problems and Restrictions
for V3.1....................................... 3-135
3.14.4.1 Database Handle Scope....................... 3-135
3.14.4.2 RDO CHANGE INDEX Restriction Is Now
Signaled.................................... 3-136
3.14.4.3 Problem of Different Optimizations of the
Same Query from Different Environments...... 3-136
3.14.4.4 Restrictions on Using Missing Value Fields
in Nested Queries........................... 3-137
3.14.4.5 STORE WITHIN and DISABLE/ENABLE COMPRESSION
Clauses Cannot Both Be Specified............ 3-138
3.14.5 RDML Known Problems and Restrictions for
V3.1........................................... 3-139
3.14.5.1 Variables Cannot Be Database Handles........ 3-139
3.14.5.2 RDML Run-Time Object Library No Longer
Requires You to Link with VAXCRTL or
VAXCRTLG Object Libraries or Shareable
Images...................................... 3-141
3.14.5.3 RDML/EPascal Ignores
/LINKAGE=PROGRAM_SECTION Qualifier.......... 3-141
3.14.5.4 RDML/Pascal Does Not Understand Some
Character String Value Expressions.......... 3-141
3.14.5.5 RDML/Pascal Does Not Accept All Possible
Valid Pascal Host Language Variables........ 3-142
3.14.5.6 RDML Does Not Allow Nested Comments......... 3-142
3.14.5.7 C Host Variables............................ 3-142
3.14.5.8 C String Continuation Character............. 3-143
3.14.5.9 Path Name and the DATABASE Statement........ 3-144
xxxi
3.14.6 RMU Known Problems and Restrictions for V3.1 .. 3-144
3.14.6.1 Using the /USERS_MAX and /NODES_MAX
Qualifiers with the RMU/RESTORE Command
Requires Both Qualifiers on the First Line
of DCL Input................................ 3-144
3.14.6.2 The RMU/BACKUP/AFTER_JOURNAL /CONTINUOUS
/UNTIL="TOMORROW:+00:50" Command Fails for
V3.1A and VMS V5.3 or V5.4.................. 3-145
3.14.6.3 There Is a 17-Character Limit for File Names
When You Back Up Databases to Tape.......... 3-145
3.14.6.4 RMU/DUMP/BACKUP Command Specifying a Value
of 1 or 2 for the /ACTIVE_IO Qualifier
Causes the AIJ Dump to Stall................ 3-146
3.14.6.5 RMU/SHOW STATISTICS Command Does Not Record
All Statistics in the Binary File........... 3-146
3.14.6.6 Dumping the .AIJ File Is Incompatible with
Normal Usage................................ 3-147
3.14.6.7 RMU/RESTORE Command May Initialize the SPAM
Thresholds in One or More Storage Areas..... 3-147
3.14.7 DSRI Notes and Restrictions Retained from
V3.1........................................... 3-148
3.14.7.1 RCI Instantiation Number Must Be Zero for
Remote Access............................... 3-148
3.14.7.2 Context Variables That Are Not Unique Within
a Request Cause Invalid BLR................. 3-149
3.14.8 CDD/Plus Restrictions Retained from Rdb/VMS
V3.1........................................... 3-149
3.14.8.1 CDD/Plus COMPUTED BY Fields Are Not
Currently Supported in Rdb/VMS Relations or
Views....................................... 3-150
3.14.8.2 EXPORT WITH NOEXTENSIONS Statement Can
Corrupt the CDD$DATABASE.................... 3-150
A Additional Release Notes for Rdb/VMS V4.1
A.1 Special SQL Images Provide a Bridge for Date-Time
Compatibility.................................... A-1
A.2 Additional Release Notes for Rdb/VMS V4.1........ A-6
A.2.1 Deprecated Default Semantics of the ORDER BY
Clause......................................... A-6
A.2.2 Creating a Storage Map on a View Caused a
Bugcheck Dump.................................. A-7
xxxii
A.2.3 Multiple Database Recovery (DBR) Instances
Attempted to Recover the Same .RUJ File and
Resulted in DBR Bugcheck....................... A-7
A.2.4 AIJ Recovery Failed Because TSNs Did Not Match
Database Root Information...................... A-8
A.2.5 AIJ File Modification on One Node Was Not
Reflected on Another Node...................... A-8
A.2.6 Memory Corruption Problem Related to the Number
of Users Caused a Bugcheck..................... A-8
A.2.7 Bugcheck Resulted When Attempting to Deassign
an Invalid File Channel During a Database
Detach......................................... A-9
A.2.8 Problem Running RDML on a System with No
Rdb/VMS License................................ A-9
A.2.9 Performance Problem Is Observed with Rdb/VMS
Distributed Update Transactions in a Mixed VMS
V5.4 and V5.5 Operating System Environment..... A-9
A.2.10 A Distributed Transaction Abort Error Will
Change from a Warning to an Error in a Future
Release of Rdb/VMS............................. A-10
A.2.11 Fractional Seconds Precision Is Not Handled
Correctly...................................... A-10
A.2.12 An RDB-E-UNRES_REL Error May Be Returned After
You Issue an ALTER STORAGE MAP Statement....... A-11
A.2.13 Mapping Values for an Index Cannot Be Used in
an RDO or SQL Import Operation................. A-11
A.2.14 A Detach from the Database Sometimes Results in
a Bugcheck..................................... A-13
A.2.15 RDO Concatenate Expression with Two Databases
Can Return Incorrect Results................... A-13
A.2.16 RDO Concatenate Expression in the Nested FOR
Loops Can Return Incorrect Results............. A-14
A.2.17 RDBPRE Upgrade from Rdb/VMS V3.0B to V4.0A
Results in Run-Time Error BAD_REQ_HANDLE Upon
Recompilation.................................. A-16
A.2.18 RMU/EXTRACT Does Not Recognize the Keyword ALL
in View Definitions............................ A-17
A.2.19 RMU/EXTRACT Does Not Correctly Extract an
RDO-Defined Trigger Containing More Than One
STORE Clause in a FOR Loop..................... A-18
A.2.20 Index Loses Attributes After an Integrate
Operation...................................... A-18
xxxiii
A.2.21 CDD/Repository, Rdb/VMS: Record and Field
Interaction.................................... A-19
A.2.22 Information Omitted from the VAX Rdb/VMS
Installation Guide Regarding the Installation
of the SQL$INT.EXE and SQL$SHRnn.EXE Shareable
Images......................................... A-20
A.2.23 Documentation Omitted Information Indicating
That an .OAIJ File Can Be Displayed Using the
RMU/DUMP/AFTER_JOURNAL Command................. A-20
A.2.24 CREATE STORAGE MAP Diagram Errors ............. A-21
A.2.25 CAST Documentation Errors ..................... A-24
A.2.26 The VAX Rdb/VMS RMU Reference Manual and RMU
Help Are Unclear on When You Can Optimize .AIJ
Files and Incorrectly State You Cannot Optimize
an .AIJ File on Tape........................... A-25
A.2.27 CREATE DATABASE Statement Must Be Lexically
Before Any DECLARE TABLE Statement in a
Precompiled Module or Module Language.......... A-26
A.2.28 Starting the Monitor from an Invalid Directory
with FAST COMMIT Enabled Causes a DBR
Problem........................................ A-27
A.2.29 Clarification on Lock Modes Shown by RMU/SHOW
LOCKS Utility.................................. A-28
A.2.30 PIOFETCH$WITHIN_DB+142 Bugcheck Resulted After
an Online RMU/COPY_DATABASE Operation.......... A-29
A.2.31 RMU/VERIFY Operation of a Storage Area with
No Root File Verification Could Result in a
Bugcheck....................................... A-29
A.2.32 Queries That Used Partitioned Indexes Could
Result in RDMS$$COMPILE_INDEX_MAPS Bugchecks... A-30
A.2.33 Problem with CDD/Repository and Views ......... A-30
A.2.34 Metadata Corruption Results After Committing
Failed Metadata Changes for a DROP TABLE
CASCADE Operation.............................. A-31
A.2.35 Virtual Memory Not Released by a Failing SET
TRANSACTION Statement with a Large Transaction
Parameter Block (TPB).......................... A-33
A.2.36 RMU/EXTRACT Output for a SQL Constraint Check
Clause in a CREATE TABLE Statement for a
Multischema Database Returns a Syntax Error.... A-34
A.2.37 Restriction on the Use of the /NOSPAMS
Qualifier...................................... A-35
xxxiv
A.2.38 Fully Qualified Multischema Naming Convention
Is Not Recognized for Lists in a Storage Map
Statement...................................... A-36
A.2.39 Node Failure Resulted in Database Corruption
Under Rare Conditions.......................... A-36
A.2.40 Bugcheck at RDMS$$INSERT_SYMBOL + 0BC ......... A-37
A.2.41 Comparing Integer and Text Fields Causes
Problems....................................... A-37
A.2.42 SPAM Pages Are Not Initialized by an
Incremental Restore Operation If the Restore
Operation is Adding a Storage Area............. A-38
A.2.43 Bugcheck at RDMS$$EXE_OPEN+78 on Query ........ A-38
A.2.44 Bugcheck at RDMS$$FIND_VALID_SEG_CRTV +
0000000F on Query.............................. A-40
A.2.45 Use of Colons with a Default of
NOANSI_PARAMETERS for a SQL Module
Language Source File Produces an -E- Error
When Compiled, an Object File, and the Link
Succeeds....................................... A-40
A.2.46 SQL/Services: IVP Fails With -2034 Error Under
Rdb/VMS V4.1................................... A-41
A.2.47 An Error on a Two-Phase Commit Protocol Start
Transaction Can Cause an Access Violation...... A-42
A.2.48 Bugcheck at ADD_STRG_TO_STBL_LISTS +
00000037....................................... A-42
B Summary of Rdb/VMS V4.1 Documentation
Examples
1-1 Using the RDMS$USE_OLD_UPDATE_RULES Logical
Name........................................... 1-154
1-2 Using the RDMS$BIND_WORK_FILE Logical Name .... 1-157
2-1 Triggers Properly Displayed ................... 2-92
2-2 Trigger Text Truncated ........................ 2-94
2-3 Cursor Losing Its State ....................... 2-105
2-4 Executing ROLLBACK with LIST CURSORS .......... 2-107
2-5 Executing COMMIT with OPEN LIST Cursors ....... 2-113
2-6 OPEN Not Returning Error Information .......... 2-117
2-7 Records from the Data Dictionary Not Terminated
with the NULL Character........................ 2-120
xxxv
Tables
1 Differences in Relational Terminology ......... xxvi
1-1 Sample Programs in Precompiled SQL and SQL
Module Language................................ 1-89
1-2 Available Transports for SQL/Services APIs .... 1-93
1-3 Output Selection Using Various Combinations of
RMU/EXTRACT Qualifiers......................... 1-126
1-4 Side-by-Side Comparison of the Two Stall
Messages Screens............................... 1-147
1-5 SQL Deprecated Features and Incompatible
Changes for Rdb/VMS Version 4.1................ 1-160
1-6 Obsolete Statements ........................... 1-165
3-1 Compatibility Between Storage Area Attributes
and Disk Drive Types........................... 3-58
3-2 Embedding Quotation Marks in Literals ......... 3-67
3-3 CDO Edit Strings Supported by SQL ............. 3-68
3-4 SQL Statements That Can Be Dynamically
Executed....................................... 3-70
3-5 SQL Statements That Cannot Be Dynamically
Executed....................................... 3-71
B-1 Rdb/VMS Documentation and Part Numbers ........ B-6
xxxvi
_________________________________________________________________
Preface
VAX Rdb/VMS software, Version 4.1, often referred to
as V4.1 in this manual, is a general-purpose database
management system based on the relational data model.
This manual describes new and changed features; problems
fixed in this release; and current problems, restrictions,
and other notes.
________________________ Note ________________________
The release notes are supplied in printed form,
Bookreader form, and in online form (in SYS$HELP). For
V4.1, the online and Bookreader forms are prepared for
production after the printed form; thus, information
in the online and Bookreader forms is more up-to-date.
The version previous to V4.1 is referred to throughout
this manual as Version 4.0 or V4.0. The term "Version
4.0" refers to Version 4.0 and any updates to Version
4.0; thus, for example, a reference to "the Version
4.0 behavior" of a statement refers to the behavior
under Version 4.0 and any of its updates.
______________________________________________________
Intended Audience
These Release Notes are intended for all users of Rdb/VMS,
and should be read to supplement information contained in
the Rdb/VMS documentation set.
To get the most out of this manual, you should be familiar
with Rdb/VMS, data processing procedures, and basic
database management concepts and terminology.
xxv
A Note on the Terminology
When the SQL, RDO, and RDML interfaces use different terms
to describe the same entity or concept, this manual uses
the SQL term, unless the discussion is specifically about
RDO or RDML. (This is also true of most of the other
manuals in the Rdb/VMS documentation set.) For example,
this manual normally uses table instead of relation,
column instead of field (of a relation), and row instead
of record.
Thus, the notes of problems fixed may use different
database terms to mean the same thing. Table 1 shows some
of the different terms used.
Table_1_Differences_in_Relational_Terminology______________
ANSI/ISO SQL
SQL______________Standard_________RDO,_RDML________________
Alias N/A[1] Database handle
Authorization Authorization VMS user name
identifier identifier
Cartesian Cartesian Cross product
product product
Catalog Catalog N/A[1]
(SQL2)[2]
Column Column Field
Column select Column select Record selection
expression expression expression
Connection Connection N/A[1]
(SQL2)[2]
Consistency Consistency Concurrency
level 2 level 2
(SQL2)[2]
[1]N/A_means_that_the_term_is_not_applicable_or_not_used___
with the listed product, standard, or system.
[2]SQL2 is the draft industry standard currently being
developed.
(continued on next page)
xxvi
Table_1_(Cont.)_Differences_in_Relational_Terminology______
ANSI/ISO SQL
SQL______________Standard_________RDO,_RDML________________
Consistency Consistency Consistency
level 3 level 3
(SQL2)[2]
Context files N/A[1] N/A[1]
Correlation Correlation Context variable
name name
Database Database[3] Database
Domain Domain Global field
(SQL2)[2]
Environment Environment N/A[1]
List N/A[1] Segmented string
Parameter Parameter Host language variable
Predicate Predicate Conditional expression
Result table Result table Record stream
Row Row Record
Schema Schema N/A[1]
Session Session N/A[1]
(SQL2)[2]
Storage area N/A[1] Storage area
Storage map N/A[1] Storage map
Table Table Relation
[1]N/A_means_that_the_term_is_not_applicable_or_not_used___
with the listed product, standard, or system.
[2]SQL2 is the draft industry standard currently being
developed.
[3]The current ANSI/ISO standard for SQL and the ANSI/ISO
SQL2 Draft Standard define this term in a slightly
different way than does the SQL interface of Rdb/VMS.
___________________________________________________________
xxvii
Operating System Information
Information about the versions of the operating system and
related software that are compatible with this version of
Rdb/VMS is included in the Rdb/VMS media kit and the VAX
Rdb/VMS Installation Guide.
For compatibility information about other software products
used with this version of Rdb/VMS, refer to the System
Support Addendum (SSA) that is included with the Software
Product Description (SPD). You can use the SPD/SSA to
verify which versions of your operating system are
compatible with this version of Rdb/VMS.
Structure
This manual contains three chapters and two appendixes:
Chapter 1 Summarizes the new and changed features of
Rdb/VMS V4.1.
Chapter 2 Describes known software errors in versions
prior to V4.1 that were fixed in V4.1.
Chapter 3 Describes problems, restrictions, and
workarounds known to exist in Rdb/VMS V4.1.
This chapter also includes restrictions
retained from previous versions of Rdb/VMS.
Appendix A Describes additional release notes for Rdb/VMS
V4.1 not described elsewhere in this book.
Appendix B Lists the Rdb/VMS documentation and part
numbers.
Related Manuals
For more information on VAX Rdb/VMS Version 4.1, see the
following manuals in the Rdb/VMS documentation set:
o Getting Started with VAX Rdb/VMS
Introduces VAX Rdb/VMS, the Digital relational database
management system for VMS software environments.
Explains relational database concepts. Introduces using
SQL to retrieve, store, and update data. Introduces
how to create, load, and maintain an Rdb/VMS database.
Includes a directory of Rdb/VMS documentation.
o VAX Rdb/VMS Glossary and Master Index
xxviii
Defines terms used in the documentation for Rdb/VMS and
related products. Provides a master index to the entire
Rdb/VMS documentation set.
o VAX Rdb/VMS Guide to Using SQL
Introduces the Rdb/VMS SQL (structured query language)
interface, and shows how to retrieve, store, and update
data interactively and through application programs. Can
be used as a tutorial for learning the major features of
SQL.
o VAX Rdb/VMS Guide to Using SQL/Services
Describes how to develop application programs that use
SQL/Services, a client/server software component of
Rdb/VMS. SQL/Services allows programs invoked on various
remote computers running the Macintosh, MS-DOS, OS/2,
SunOS, ULTRIX, ULTRIX for RISC, or VMS operating systems
to access Rdb/VMS as well as other databases supported
by SQL on a VMS server system.
o VAX Rdb/VMS Guide to Distributed Transactions
Describes the two-phase commit protocol and distributed
transactions, explains how to start and complete
distributed transactions using SQL, RDBPRE, and RDML,
and how to recover from unresolved transactions using
RMU commands.
o VAX Rdb/VMS Guide to Database Design and Definition
Explains how to design a logical database and how to
translate that design into a physical database using
Rdb/VMS data definition statements.
xxix
o VAX Rdb/VMS Guide to Database Maintenance
Explains how to use the database maintenance utilities
to perform such operations as database monitoring,
security auditing, opening and closing a database,
verifying and altering a database, backing up,
restoring, and recovering a database, journaling
database activity, modifying database characteristics,
and handling bugcheck dumps.
o VAX Rdb/VMS Guide to Database Performance and Tuning
Describes how to analyze the elements that affect
database performance and how to tune those elements
to optimize performance. Contains decision trees that
provide an organized approach to identifying and solving
common database performance problems.
o VAX Rdb/VMS SQL Reference Manual
Provides reference material and a complete description
of the statements, the interactive, dynamic, and
module language interfaces, and the syntax for SQL,
the structured query language interface for Rdb/VMS.
o VAX Rdb/VMS RMU Reference Manual
Provides reference material and a complete description
of the commands and syntax of the Rdb/VMS Management
Utility (RMU).
o VAX Rdb/VMS Installation Guide
Describes how to install Rdb/VMS and SQL/Services
Application Programming Interface (API) software.
Conventions
In examples, an implied carriage return occurs at the end
of each line, unless otherwise noted. You must press the
Return key at the end of a line of input.
Often in examples the prompts are not shown. Generally,
they are shown where it is important to depict an
interactive sequence exactly; otherwise, they are omitted
in order to focus full attention on the statements or
commands themselves.
xxx
This section explains the conventions used in this manual:
<Ctrl/x> This symbol in examples tells you to press
the Ctrl (control) key and hold it down while
pressing the specified letter key.
<Return> This symbol in examples indicates the Return key.
<Tab> This symbol in examples indicates the Tab key.
Vertical ellipsis points in an example mean that
. information not directly related to the example
. has been omitted.
.
. . . Horizontal ellipsis points in statements or
commands mean that parts of the statement or
command not directly related to the example have
been omitted.
< > Angle brackets enclose user-supplied names.
[ ] Brackets enclose optional clauses from which you
can choose one or none.
$ The dollar sign represents the DIGITAL Command
Language prompt. This symbol indicates that the
DCL interpreter is ready for input.
References to Products
The Rdb/VMS documentation to which this document belongs
often refers to products by their abbreviated names:
o DECdecision software is referred to as DECdecision.
o DEC RdbAccess for VAX RMS on VMS software is referred to
as RdbAccess for VAX RMS.
o DEC RdbExpert for VMS software is referred to as
RdbExpert.
o DEC SERdb for Security-Enhanced VMS software is referred
to as SERdb.
o DECtrace for VMS software is referred to as DECtrace.
o VAX ACMS software is referred to as ACMS.
o VAX Ada software is referred to as Ada.
o VAX BASIC software is referred to as BASIC.
o VAX C software is referred to as C.
xxxi
o VAX CDD/Plus software is referred to as CDD/Plus or the
data dictionary.
o VAX CDD/Repository V5.0 is referred to as
CDD/Repository.
o VAX COBOL software is referred to as COBOL.
o VAX Data Distributor software is referred to as Data
Distributor.
o VAX DATATRIEVE software is referred to as DATATRIEVE.
o VAX DBMS software is referred to as VAX DBMS.
o VAX FORTRAN software is referred to as FORTRAN.
o VAXELN Pascal and VAX Pascal are both referred to
as Pascal except when the use of a Relational Data
Manipulation Language (RDML) statement is not the same
in the VAXELN and VMS environments. In the latter case,
either VAXELN Pascal or VAX Pascal is specified.
o VAX PL/I software is referred to as PL/I.
o VAX RALLY software is referred to as RALLY.
o VAX Rdb/ELN software is referred to as Rdb/ELN.
o VAX Rdb/VMS software is referred to as Rdb/VMS. VAX
Rdb/VMS software Versions 3.1, 3.1A, 3.1B, and 3.1C
are often referred to as V3.1, V3.1A, V3.1B, and V3.1C,
respectively. VAX Rdb/VMS software Versions 4.0, 4.0A,
and 4.0B are often referred to as V4.0, V4.0A, and
V4.0B, respectively.
o VAX SQL software is referred to as VAX SQL whenever it
is correct to refer to Version 2.0 or earlier of SQL.
The use of SQL by itself indicates the SQL interface
included as part of VAX Rdb/VMS Version 3.1 and higher.
o VAX TEAMDATA software is referred to as TEAMDATA.
o VAX TDMS software is referred to as TDMS.
o VIDA software is referred to as VIDA.
xxxii
1
_________________________________________________________________
New and Changed Features
This chapter provides a summary of the new features and
technical changes in VAX Rdb/VMS Version 4.1.
1.1 Rdb/VMS V4.1 Has Multiple Version Support for Running Both
V4.0 and V4.1 on the Same Node or in the Same VAXcluster
Rdb/VMS supports running multiple versions of Rdb/VMS.
You can run V4.1 and V4.0 on the same node or in the same
VAXcluster. This will allow testing applications with the
most current release of Rdb/VMS software (V4.1) without
replacing the latest released production version of Rdb/VMS
V4.0.
The restrictions for running multiple versions include the
following:
o This feature requires V4.0 of Rdb/VMS (or no Rdb/VMS
installed).
o SQL/Services will allow only one version of SQL/Services
on a particular node on the cluster at a time.
See the chapter on multiple version support in the
VAX Rdb/VMS Installation Guide for more information on
installing and using multiple versions of Rdb/VMS.
1.2 Deinstall/Delete Command Procedure
For your convenience, Rdb/VMS provides a deinstall/delete
command procedure. This procedure lets you delete previous
versions of Rdb/VMS, including SQL and SQL/Services. For
instance, if you decide to convert your production and data
dictionary databases to V4.1, you could choose to delete
V4.0.
The deinstall command procedure automatically shuts down
the Rdb/VMS monitor of the version being deleted.
New and Changed Features 1-1
To delete Version 4.0, enter the following command:
$ @SYS$MANAGER:RDBVMS$DEINSTALL_DELETE 4.0
Only the parameters 4.0 or 4.1 are accepted by the
deinstall/delete command procedure.
The deinstall/delete command procedure asks the following
questions:
Do you want to delete the RDB$REMOTE account? [N]: Y
Do you want to delete the network object RDBSERVER? [N]: Y
If you have modified parameters in either the RDB$REMOTE
account or the network object RDBSERVER, you may wish to
keep them for future use. The installation of V4.1 will not
retain the modified parameters.
1.3 New Update Rules in Rdb/VMS V4.1-Modify and Erase Operations
May Fail in RDO, RDBPRE, and RDML
Starting in Rdb/VMS V4.1, new update rules will be enforced
by default. With new update rules it is no longer possible
to modify or delete rows from a table that is directly
joined with other tables. However, rows from a table can
still be modified or deleted if the table is joined with
other tables that are in a subquery. Because SQL does not
have syntax that allows rows from a table to be modified or
deleted and at the same time allow that table to be joined
with other tables, the new update rules have no affect
on SQL applications. However, those RDO applications that
contain join update queries, that is, the update queries
that modify or delete rows from a table that is joined with
other tables will be affected and will have to be fixed.
Rdb/VMS now gives an error diagnostic for the following
join update query:
FOR E IN EMPLOYEES CROSS D IN DEGREES OVER EMPLOYEE_ID
WITH D.DEGREE = 'MA'
ERASE E
END_FOR
%RDMS-E-JOIN_CTX_UPD, relation EMPLOYEES is part of a join, cannot be updated
1-2 New and Changed Features
In this previous update query if an employee has two MA
degrees, the same employee row will be joined to two
different degree rows. Therefore Rdb/VMS will try to
delete the same row twice. Or if instead of using the ERASE
verb, the previous update query used a MODIFY verb on the
EMPLOYEES table, then Rdb/VMS might modify the row more
than once.
In prior versions of Rdb/VMS, join update queries similar
to the previous query worked correctly or produced an error
diagnostic trying to delete the same row more than once,
or-even worse-produced a bugcheck.
The previous update query can be reworded into an
equivalent form to achieve the same result as follows:
FOR E IN EMPLOYEES WITH (ANY D IN DEGREES WITH D.EMPLOYEE_ID = E.EMPLOYEE_ID
AND D.DEGREE = 'MA')
ERASE E
END_FOR
The rows can now be erased because the EMPLOYEES table is
no longer directly joined to the DEGREES table. The use
of a modified update query guarantees that an employee row
will not be deleted more than once.
To ease the transition for applications that depend on the
old update rules, Rdb/VMS will support either new or old
update rules in V4.1 and in the next version after V4.1.
By default, Rdb/VMS will enforce new update rules. To make
Rdb/VMS continue to use the old update rules, define the
logical name RDMS$USE_OLD_UPDATE_RULES to "1". The dual
support of new as well as old update rules is provided to
give users time to change their applications (if necessary)
to conform to the new update rules.
The support for old update rules will be removed from
Rdb/VMS in the release following the release after V4.1.
The following join update query will no longer work with
the new update rules. Also this update query will modify
some salary history rows more than once and gives multiple
salary raises to some managers!
New and Changed Features 1-3
! Give a 10% salary raise to all managers who have an MA degree.
FOR S IN SALARY_HISTORY CROSS D IN DEGREES CROSS DP IN DEPARTMENTS
WITH S.EMPLOYEE_ID = D.EMPLOYEE_ID AND
S.EMPLOYEE_ID = DP.MANAGER_ID AND
S.SALARY_END MISSING AND
D.DEGREE = 'MA'
MODIFY S USING S.SALARY_AMOUNT = S.SALARY_AMOUNT * 1.1
END_FOR
This query can be reworded using a subquery as follows:
FOR S IN SALARY_HISTORY
WITH S.SALARY_END MISSING AND
(ANY D IN DEGREES CROSS DP IN DEPARTMENTS
WITH S.EMPLOYEE_ID = D.EMPLOYEE_ID AND
S.EMPLOYEE_ID = DP.MANAGER_ID AND
D.DEGREE = 'MA')
MODIFY S USING S.SALARY_AMOUNT = S.SALARY_AMOUNT * 1.1
END_FOR
This revised query will work with the new as well as the
old update rules and it will ensure that each qualified
manager gets a single salary raise.
________________________ Note ________________________
Some examples in Sections 6.7 and 9.2.6.3 of the VAX
Rdb/VMS Guide to Using RDO, RDBPRE, and RDML will no
longer work with the new update rules. To run these
examples, reword them using the ANY subquery mentioned
previously.
______________________________________________________
1.4 Online Help
Rdb/VMS provides the following DCL-level help topics
related to Rdb/VMS:
o RDBPRE
Information on the RDBPRE preprocessor for BASIC, COBOL,
and FORTRAN programs
o RDBVMS
General information on the VAX Rdb/VMS software
1-4 New and Changed Features
o RDML
Information specific to the RDML preprocessor
o RDO
Information on invoking the RDO utility
o RMU
Information specific to the RMU utility
o SQL
Information about the SQL interface to VAX Rdb/VMS
o SQL_SERVICES
Information on the SQL/Services component of VAX Rdb/VMS
How you access the DCL-level help topics related to
Rdb/VMS depends on whether your site has the standard or
multiversion (varianted) version of Rdb/VMS V4.1.
1.4.1 Accessing DCL-Level Help Topics Related to Rdb/VMS Using
the Standard Version of Rdb/VMS V4.1
If your site has the standard version of Rdb/VMS V4.1, you
access the V4.1 Help on any of the topics in the previous
list by typing HELP and the topic name. For example,
you access the V4.1 Help topic on RDBVMS by typing the
following:
$ HELP RDBVMS
With the standard version of Rdb/VMS V4.1, you can access
only the V4.1 Help for any of the topics in the previous
list.
1.4.2 Accessing DCL-Level Help Topics Related to Rdb/VMS Using
the Varianted Version of Rdb/VMS V4.1
If your site has the multiversion (varianted) version of
Rdb/VMS V4.1, you access the V4.1 Help on any of the topics
in the previous list by typing HELP and the topic name with
a "41" suffix. For example, you access the V4.1 Help topic
on RDBVMS by typing the following:
$ HELP RDBVMS41
New and Changed Features 1-5
With the multiversion (varianted) version of Rdb/VMS V4.1,
you can also access the V4.0 Help for any of the topics in
the previous list by typing HELP and the topic name without
any suffix. For example, you access the V4.0 Help topic on
RDBVMS by typing the following:
$ HELP RDBVMS
1.5 Rdb/VMS Security Auditing
The following sections describe changes to Rdb/VMS security
auditing for V4.1.
1.5.1 Security Audit Log Format Is Changed
In Rdb/VMS V4.1, the format of the security audit
information stored in the VMS security audit journal has
been changed to eliminate some unnecessary redundancy. Once
Rdb/VMS V4.1 is installed, security auditing information
from previous Rdb/VMS releases (V4.0) can no longer
be loaded from the VMS security audit journal via the
RMU/LOAD/AUDIT command. Only security auditing information
generated after the installation of Rdb/VMS V4.1 can be
retrieved.
1.5.2 RMU/SET AUDIT and RMU/SHOW AUDIT Commands Have New and
Changed Qualifiers and Keywords
In Rdb/VMS V4.1, a new keyword, DATABASE, has been added
to the RMU/SET AUDIT and RMU/SHOW AUDIT commands. The
keyword DATABASE can now be used as a synonym for the
keyword SCHEMA. The word DATABASE will now be displayed
in the output of the RMU/SET AUDIT command, replacing the
word SCHEMA as shown in the following example:
$ RMU/SHOW AUDIT/DACCESS=DATABASE MF_PERSONNEL
Security auditing STARTED for:
DACCESS (enabled)
DATABASE
(ALTER)
Security alarms STARTED for:
DACCESS (enabled)
DATABASE
(ALTER)
1-6 New and Changed Features
In addition, the RMU/SET AUDIT command qualifiers /IDENT
and /PRIV can now be specified as the full strings,
/IDENTIFIERS and /PRIVILEGES, respectively.
1.6 Query Optimizer Enhancements
Sections 1.6.1 through 1.6.8 describe enhancements to the
query optimizer.
1.6.1 Displaying Estimated Query Cost
Some queries can result in Rdb/VMS performing a large
number of I/Os, retrieving a large number of rows, or both.
The SQL SET QUERY CONFIRM statement lets you preview the
cost of a query before any rows are actually returned. If
the cost appears excessive, you can cancel the query by
answering No; to continue, answer Yes.
Estimated query cost is also written to the SQLCA. SQL
fills in the SQLCA fields SQLERRD[2] and SQLERRD[3] with
estimated result table cardinality and the estimated IOs,
respectively, when an OPEN statement has been executed for
a table cursor.
________________________ Note ________________________
You must recompile application modules so that the new
values in SQLERRD[2] and SQLERRD[3] can be returned.
______________________________________________________
1.6.2 Limiting Query Execution with the Query Governor
With Rdb/VMS Version 4.1, you can set limits to restrict
the output generated by a query, both within an application
and for interactive users. The mechanism used to set these
limits is called the query governor. There are two ways to
set limits using the query governor:
o You can restrict output by limiting the number of rows a
query can return. The optimizer counts each row returned
by the query and stops execution when the row limit is
reached.
New and Changed Features 1-7
o You can restrict the elapsed time that the optimizer
spends compiling a query. The time limit value specified
is an integer that indicates the number of elapsed
seconds to allow the query to compile. When the
specified time limit is reached, the query compilation
aborts.
If an established limit is exceeded, the query is canceled
and an error message displayed.
Application developers and 4GL tools can use this feature
to prevent users from overloading the system. The database
administrator can manage system performance and reduce
unnecessary resource usage by setting option limits.
You can set the row limit and time limit values by using
the any of the following methods:
o For interactive SQL, use the SET QUERY LIMIT statement.
SQL> SET QUERY LIMIT TIME n
SQL> SET QUERY LIMIT ROWS m
You can use the SHOW QUERY LIMIT statement to display
the values set for each limit.
o For the precompiler SQL$PRE, use the query limit
parameter to the /SQLOPTIONS qualifier.
/SQLOPTIONS = (QUERY_TIME_LIMIT = seconds, QUERY_MAX_ROWS = number_of_rows)
o For the module language compiler SQL$MOD, use the
options qualifiers.
/QUERY_TIME_LIMIT = seconds /QUERY_MAX_ROWS = number_of_rows.
o For dynamic SQL, options are inherited from compilation
qualifiers.
o Use the logical names RDMS$BIND_QG_TIMEOUT and
RDMS$BIND_QG_REC_LIMIT. If you are using RDO, defining
these logical names is the only way you can set query
limits.
With all of the query governor options, you can set both
the time limit and the row limit. Whichever value is
reached first stops the output.
1-8 New and Changed Features
1.6.3 Setting a Preferred Query Optimization Mode
SQL now enables you to influence the retrieval strategy
selected by the query optimizer by specifying either fast
first row retrieval or total time retrieval when using the
DECLARE CURSOR statement.
o With fast first row retrieval, data is returned as soon
as possible.
This fast first row retrieval strategy benefits
applications that need to establish the existence of,
or look at, one or a few rows that satisfy the query.
For example, interactive applications can allow a user
to abort a query after displaying several screens of
data without reading the entire set of rows.
Note that the query optimizer can attempt to optimize
certain portions of the query in one or two ways
depending on the internal grouping separators such as
EXISTS, LIMIT TO, ORDER BY, and aggregate expressions.
By explicitly specifying one or two options, the
user can influence only those portions of a query not
automatically covered by the query optimizer itself.
o With total time retrieval, the optimizer determines the
best strategy for satisfying the entire query.
This total time retrieval strategy benefits
applications, such as batch jobs, that are interested
in minimizing total retrieval time and can afford to
wait for the first row of data to be returned. Most
queries default to fast first optimization.
The following examples illustrate the DECLARE CURSOR syntax
for setting a preferred optimization mode.
New and Changed Features 1-9
SQL> DECLARE CEMP TABLE CURSOR
cont> FOR
cont> SELECT *
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID > '00405'
cont> OPTIMIZE FOR FAST FIRST;
SQL> DECLARE TEMP TABLE CURSOR
cont> FOR
cont> SELECT LAST_NAME, FIRST_NAME
cont> FROM EMPLOYEES
cont> OPTIMIZE FOR TOTAL TIME;
These new features, the OPTIMIZE FOR TOTAL TIME and
OPTIMIZE FOR FAST FIRST options, can significantly improve
query performance.
If you plan to retrieve all the requested records and do
not care about the speed of the first record appearance,
always specify the OPTIMIZE FOR TOTAL TIME option because
the default optimization is a "fast first" optimization.
In this case, some performance improvement over the default
always takes place, and occasionally the improvement can be
two to tenfold.
If you wish to see several first records as quickly as
possible and then terminate the query (even though in
other cases you will decide to run the query to the end),
use the OPTIMIZE FOR FAST FIRST option. With this option
the optimizer tries its best in providing the fast first
records delivery. In contrast, with the "total time"
option, you often must wait for all index scan completions
before you get the first record.
Digital recommends that users select a query optimization
mode when running queries. You should especially consider
selecting the nondefault "total time" option because the
desired style of record delivery is now returned.
1-10 New and Changed Features
1.6.4 Quick Index Lookup in the Dynamic Optimizer
In Rdb/VMS V4.1, the dynamic optimizer has been enhanced
to provide a quick and inexpensive lookup into a sorted
index. The quick index lookup feature determines the
right order to scan background indexes by using estimates
of dbkey cardinality for each index. These quick index
lookup estimates are done before the competition process
of dynamic optimization starts. Once the estimates are
available, the optimizer selects the index with the fewest
dbkeys to be scanned first.
During dynamic optimization, an estimate routine is called
if at least one background index is eligible and at least
two background indexes are available. Eligible indexes must
meet the following criteria:
o The indexes must be sorted.
o The indexes must be located in a single area or be part
of a single-file database.
o At least one index must be restricted by a single range
or equality.
Determining the correct scanning order for background
indexes improves dynamic optimization performance by
quickly establishing lists of dbkeys required to retrieve
the rows specified in the query and by compressing the
dbkey lists as fast as possible.
1.6.5 Improved OR Optimization
Sometimes expressing a predicate in different but logically
equivalent ways can result in the query optimizer selecting
different access strategies. For example, this situation
can occur when one of the Boolean "distributive" laws is
applied to a predicate.
Consider the following hypothetical example based on the
DEGREES table from the PERSONNEL database. Assume that the
only index defined on DEGREES has been defined as follows:
SQL> CREATE INDEX DEG_COLLEGE_CODE_YEAR_GIVEN_NDX
cont> DEGREES (COLLEGE_CODE, YEAR_GIVEN);
New and Changed Features 1-11
The following three queries return all rows from the
DEGREES table for employees who graduated from Stanford
in either 1981 or 1983:
(1) SELECT * FROM DEGREES
WHERE COLLEGE_CODE = 'STAN' AND YEAR_GIVEN IN (1981, 1983);
(2) SELECT * FROM DEGREES
WHERE COLLEGE_CODE = 'STAN'
AND
(YEAR_GIVEN = 1981 OR YEAR_GIVEN = 1983);
(3) SELECT * FROM DEGREES
WHERE COLLEGE_CODE = 'STAN' AND YEAR_GIVEN = 1981
OR
COLLEGE_CODE = 'STAN' AND YEAR_GIVEN = 1983
Queries 1 and 2 generate identical internal structures;
hence, both always produce identical strategies. In
general, you can consider terms of the first form shown
here to be merely a syntactic shorthand of the second form.
First Form: F IN (v1, v2, . . . vn)
Second Form: (F = v1 OR F = v2 . . . OR F = vn)
Query 3, however, is obtained from query 2 by applying
the distributive law (that is, "multiplying" out the
expression).
It is likely that the most efficient access strategy for
any of the three queries would be to use both segments of
the index to locate the two sets of records (one set for
each year) specified. However, prior to V4.1 the query
optimizer would have recognized the opportunity to use
this multisegment strategy only for query 3. For queries
1 and 2 it would have selected a strategy using only the
leading segment (COLLEGE_CODE) of the index. It would have
fetched all index entries for records with COLLEGE_CODE =
'STAN' and scan them (using the key-only Boolean feature)
to select the dbkeys for those with the proper values for
the YEAR_GIVEN field.
This non-optimal strategy was selected because the
optimizer lacked the ability to "distribute" the leading
index segment (COLLEGE_CODE) term into the two terms
connected by the OR Boolean on the second index segment
(YEAR_GIVEN).
1-12 New and Changed Features
Furthermore, had the index been hashed instead of sorted,
the query optimizer would not have used the index at
all for queries 1 and 2. This is because a hashed index
requires equality predicates on all segments (that is,
exact match). Lacking the ability to apply the distributive
law, the query optimizer would not have recognized that
these predicates specified exact matches on the hashed
fields, and hence would have been forced to choose a full
table scan strategy.
With the improved OR optimization feature new in V4.1, the
query optimizer produces the same strategy for all three of
the preceding queries (using both segments of the index if
that is the best choice).
The usefulness of this feature is that the query optimizer
now chooses an efficient access strategy using several
segments of a multisegment index in certain cases where
predicates on some of the segments are joined with AND
Booleans with combinations of predicates on other segments
joined with OR Booleans.
There are some restrictions. The query optimizer is
not capable of fully "multiplying out" multiple OR
expressions that are joined with an AND Boolean. Thus,
some opportunities for selecting a multisegment strategy
will still be overlooked. For example, to find all the rows
from the DEGREES table for employees that graduated from
Stanford, MIT, or Purdue in either 1981 or 1983, you might
formulate the query as follows:
(4) SELECT * FROM DEGREES
WHERE COLLEGE_CODE IN ('STAN', 'MIT', 'PRDU')
AND
YEAR_GIVEN IN (1981, 1983);
This is logically equivalent to the following "multiplied
out" form:
New and Changed Features 1-13
(5) SELECT * FROM DEGREES
WHERE COLLEGE_CODE = 'STAN' AND YEAR_GIVEN = 1981
OR
COLLEGE_CODE = 'STAN' AND YEAR_GIVEN = 1983
OR
COLLEGE_CODE = 'MIT' AND YEAR_GIVEN = 1981
OR
COLLEGE_CODE = 'MIT' AND YEAR_GIVEN = 1983
OR
COLLEGE_CODE = 'PRDU' AND YEAR_GIVEN = 1981
OR
COLLEGE_CODE = 'PRDU' AND YEAR_GIVEN = 1983;
Unfortunately, for query 4 the query optimizer is still not
able to recognize the possibility of using both segments
of the index. However, it does consider using the first
segment of the index combined with the key-only Boolean
feature (but if the index were hashed, it could not be used
at all for query 4).
This limitation is not as serious as it might seem, because
unless the cardinality of the table is extremely high,
the multisegment strategy (which requires six separate
index range lookups) may be inferior to the single-segment
strategy (which requires only three index range scans)
anyway.
Furthermore, if you want the multisegment strategy to be
considered, you can express the query either as in query 5
or in the partially multiplied out form as follows:
(6) SELECT * FROM DEGREES
WHERE YEAR_GIVEN = 1981 AND COLLEGE_CODE IN ('STAN', 'MIT', 'PRDU')
OR
YEAR_GIVEN = 1983 AND COLLEGE_CODE IN ('STAN', 'MIT', 'PRDU');
The query expression should not contain more than one OR or
IN factor joined with AND Boolean expressions. If it does,
the query optimizer may not recognize all multisegment
strategy possibilities. (It is acceptable to join several
terms with AND Boolean expressions that are joined with OR
Boolean expressions as in queries 5 and 6).
1-14 New and Changed Features
You need not worry about the order of terms or factors.
The query optimizer recognizes opportunities to apply the
commutative law. For example, the following two queries 1
and 7 produce the same strategy.
(1) SELECT * FROM DEGREES
WHERE COLLEGE_CODE = 'STAN' AND YEAR_GIVEN IN (1981, 1983);
(7) SELECT * FROM DEGREES
WHERE YEAR_GIVEN IN (1983, 1981) AND COLLEGE_CODE = 'STAN';
1.6.6 MIN/MAX Aggregate Optimization
Min/max aggregate optimization is used when an appropriate
sorted index is available and the aggregate value is
materialized from a column that is part of the index. The
other necessary conditions for this optimization are as
follows:
o The query must select a single minimum or maximum value.
o The aggregated column should be the first segment of
an index. If it is not the first segment, then all
leading segments (segments before it) must have equality
selection (that is, column = value or column is null).
o There cannot be any selections on columns outside of the
index being used.
o A sorted index with a segment that is a VARCHAR or
COLLATING SEQUENCE column cannot be used.
Min/max optimization can be used on ascending, descending,
partitioned and/or multisegmented sorted indexes. When this
optimization is used, an index B-tree descend is performed
to locate either the minimum or the maximum column value.
This approach is much faster than either scanning a range
of index keys or sequentially scanning all table rows to
find the min/max column value.
The following queries use min/max optimization. Their
strategies show the notation that indicates the use of
min/max optimization.
New and Changed Features 1-15
SELECT MIN(EMPLOYEE_ID) FROM EMPLOYEES;
Aggregate Index only retrieval of relation EMPLOYEES
Index name EMP_EMPLOYEE_ID [0:0] Min key lookup
00164
1 row selected
The notation "Min key lookup" at the end of the strategy
print indicates that min/max optimization is used to find
the minimum value for EMPLOYEE_ID column.
SELECT MAX(EMPLOYEE_ID) FROM EMPLOYEES;
Aggregate Index only retrieval of relation EMPLOYEES
Index name EMP_EMPLOYEE_ID [0:0] Max key lookup
00471
1 row selected
The notation "Max key lookup" at the end of the strategy
print indicates that min/max optimization is used to find
the maximum value for EMPLOYEE_ID column.
SELECT MAX(EMPLOYEE_ID) FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN '00200' AND '00300';
Aggregate Index only retrieval of relation EMPLOYEES
Index name EMP_EMPLOYEE_ID [1:1] Max key lookup
00287
1 row selected
As in the previous query, the notation "Max key lookup"
at the end of the strategy print indicates that min
/max optimization is used to find the maximum value for
EMPLOYEE_ID column. This maximum column value is found
within an index range (BETWEEN '00200' and '00300'), which
is indicated by the notation "EMP_EMPLOYEE_ID [1:1]" right
before "Max key lookup."
Another example query that uses min/max optimization on the
second segment of a two-segment index is as follows:
CREATE INDEX SH_SALEND_SALARY ON SALARY_HISTORY (SALARY_END,
SALARY_AMOUNT);
! Find the highest current salary.
SELECT MAX(SALARY_AMOUNT) FROM SALARY_HISTORY WHERE SALARY_END IS NULL;
1-16 New and Changed Features
Aggregate Index only retrieval of relation SALARY_HISTORY
Index name SH_SALEND_SALARY [1:1] Max key lookup
93340.00
1 row selected
The keys in the index SH_SALEND_SALARY are made up of two
columns: SALARY_END and SALARY_AMOUNT. Min/max optimization
is used to find the maximum SALARY_AMOUNT value within the
range of all index keys that have a null SALARY_END value.
1.6.7 Improved View Optimization
In V4.1, the optimizer uses predicates based on index
columns both from the query and also from the view
definitions for potential indexed retrievals. If a view
nests other views, then index predicates (if any) from all
nested views will also be used. This improvement should
help produce more efficient strategies, and therefore,
better performance of queries that use views.
The following query examples show that the optimizer now
produces better strategies than before.
CREATE VIEW EMP_GT_200 AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID >= '00201';
! Log of query run on Rdb V4.0.
SELECT * FROM EMP_GT_200
WHERE EMPLOYEE_ID <= '00204';
Conjunct
Leaf#01 FFirst EMPLOYEES Card=100
BgrNdx1 EMP_EMPLOYEE_ID [0:1]
EMPLOYEE_ID FIRST_NAME LAST_NAME
00201 Marjorie Clinton
00202 Margaret Harrington
00203 Johanna Gaudet
00204 Charles Myotte
4 rows selected
The notation "EMP_EMPLOYEE_ID [0:1]" in the strategy
indicates that the index EMP_EMPLOYEE_ID is scanned
starting with first key until key '00204'. That means 41
keys with 41 corresponding rows are fetched. Out of 41
rows, only 4 rows were returned by the query. Clearly,
New and Changed Features 1-17
fetching the other 37 keys as well as the corresponding
rows was unnecessary. The reason for fetching 41 keys and
rows instead of 4 is that the predicate "EMPLOYEE_ID >=
'00201'" in the view definition is not used in the indexed
retrieval.
! Log of query run on Rdb V4.1.
SELECT * FROM EMP_GT_200
WHERE EMPLOYEE_ID <= '00204';
Leaf#01 FFirst EMPLOYEES Card=100
BgrNdx1 EMP_EMPLOYEE_ID [1:1] Fan=17
EMPLOYEE_ID FIRST_NAME LAST_NAME
00201 Marjorie Clinton
00202 Margaret Harrington
00203 Johanna Gaudet
00204 Charles Myotte
4 rows selected
The notation "EMP_EMPLOYEE_ID [1:1]" in the strategy
indicates that the index EMP_EMPLOYEE_ID is scanned
starting with key '00201' until key '00204'. In other
words, exactly 4 keys and the corresponding 4 rows are
fetched, and all 4 rows are returned by the query. An
efficient indexed retrieval was possible because the
predicate "EMPLOYEE_ID >= '00201'" in the view definition
is also used.
The following query provides another example that uses a
nested view and a two-segment index.
CREATE INDEX DEG_COLL_CODE_DEGREE ON DEGREES (COLLEGE_CODE, DEGREE);
CREATE VIEW INNER_VIEW AS
SELECT EMPLOYEE_ID, COLLEGE_CODE, DEGREE, YEAR_GIVEN FROM DEGREES
WHERE DEGREE = 'PhD';
CREATE VIEW OUTER_VIEW AS
SELECT * FROM INNER_VIEW WHERE COLLEGE_CODE = 'STAN';
! Log of query run on Rdb V4.0.
SELECT * FROM OUTER_VIEW;
Conjunct
Leaf#01 FFirst DEGREES Card=165
BgrNdx1 DEG_COLL_CODE_DEGREE [1:1]
1-18 New and Changed Features
EMPLOYEE_ID COLLEGE_CODE DEGREE YEAR_GIVEN
00166 STAN PhD 1979
00186 STAN PhD 1977
00198 STAN PhD 1976
00359 STAN PhD 1978
00374 STAN PhD 1981
5 rows selected
The notation "DEG_COLL_CODE_DEGREE [1:1]" indicates that
only the predicate on first index segment is used for
indexed retrieval. That is, 23 rows that have COLLEGE_
CODE = 'STAN' are fetched, and only 5 rows are delivered.
The predicate in the INNER_VIEW definition is not used for
indexed retrieval.
! Log of query run on Rdb V4.1.
SELECT * FROM OUTER_VIEW;
Conjunct
Leaf#01 FFirst DEGREES Card=165
BgrNdx1 DEG_COLL_CODE_DEGREE [2:2] Fan=15
EMPLOYEE_ID COLLEGE_CODE DEGREE YEAR_GIVEN
00166 STAN PhD 1979
00186 STAN PhD 1977
00198 STAN PhD 1976
00359 STAN PhD 1978
00374 STAN PhD 1981
5 rows selected
The notation "DEG_COLL_CODE_DEGREE [2:2]" indicates that
predicates on both first and second index segments are used
for indexed retrieval. The 5 rows that have COLLEGE_CODE
= 'STAN' and DEGREE = 'PhD' are fetched and all 5 rows are
delivered. The indexed retrieval is more efficient because
it used predicates from both view definitions.
1.6.8 Preventing Anomalous Updates That Can Occur with Subqueries
Prior to V4.1, Rdb/VMS sometimes updated more or less
than the actual number of rows when the update query
also referenced the table being updated in a subquery.
Specifically, when the result of a subquery was determined
by reading data from the table being updated, and the
subquery result was used to decide whether a row should
be updated, sometimes this resulted in an anomalous update.
The anomalous update occurred because the determination
New and Changed Features 1-19
of subquery result and the decision to update each row was
interleaved. The solution to this problem was to separate
out these two tasks into two different steps.
The following example illustrates the anomalous update
(which occurs in V4.0) and the prevention of an anomalous
update (in V4.1).
! Log of query run using Rdb V4.0.
SELECT * FROM SALARY_HISTORY S WHERE EMPLOYEE_ID = '00187';
EMPLOYEE_ID SALARY_AMOUNT SALARY_START SALARY_END
00187 $12,000.00 7-Aug-1979 29-Nov-1980
00187 $12,513.00 27-Jul-1981 22-Jul-1982
00187 $13,067.00 22-Jul-1982 NULL
00187 $12,390.00 29-Nov-1980 27-Jul-1981
4 rows selected
! Delete salary history row with min salary_amount for employee '00187'.
DELETE FROM SALARY_HISTORY S
WHERE EMPLOYEE_ID = '00187' AND
SALARY_AMOUNT = (SELECT MIN(SALARY_AMOUNT) FROM SALARY_HISTORY S2
WHERE S2.EMPLOYEE_ID = S.EMPLOYEE_ID);
2 rows deleted
The previous update query deletes two rows instead of
one; this is an anomalous update. What caused this is the
following process. Rdb/VMS started with the first salary
history row and found the minimum salary amount from a
set of four rows. Because the salary amount in the first
row matched the minimum found, the first row was deleted.
Next, for the second row minimum salary, the amount was
found from a set of three rows. Because this salary amount
did not match the minimum found, it was not deleted. The
same thing happened with the third row. Finally, for the
fourth row, the minimum was found from a set of three rows.
Because the salary amount matched the minimum found, it was
also deleted.
! Log of query run using Rdb V4.1.
SELECT * FROM SALARY_HISTORY S WHERE EMPLOYEE_ID = '00187';
1-20 New and Changed Features
EMPLOYEE_ID SALARY_AMOUNT SALARY_START SALARY_END
00187 $12,000.00 7-Aug-1979 29-Nov-1980
00187 $12,513.00 27-Jul-1981 22-Jul-1982
00187 $13,067.00 22-Jul-1982 NULL
00187 $12,390.00 29-Nov-1980 27-Jul-1981
4 rows selected
! Delete salary history row with min salary_amount for employee '00187'.
DELETE FROM SALARY_HISTORY S
WHERE EMPLOYEE_ID = '00187' AND
SALARY_AMOUNT = (SELECT MIN(SALARY_AMOUNT) FROM SALARY_HISTORY S2
WHERE S2.EMPLOYEE_ID = S.EMPLOYEE_ID);
1 row deleted
In V4.1, the same update query correctly deletes one row.
Because the determination of subquery result is separated
from decision to update each row, the minimum salary amount
is found just once from a set of the initial four rows. The
same minimum is compared against the salary amount of each
row and when a match is found, that row is deleted.
1.7 Using Global Buffers
With global buffering, user processes map global sections
to their virtual memory instead of making copies of the
buffer in physical memory. Although more than one user
process can read a page at the same time, there is only
one copy of the page in the global buffer pool. Improved
performance is achieved because I/O operations are reduced
and memory is better utilized.
Prior to Version 4.1, all page buffering was local to the
user process. Beginning with Version 4.1, global page
buffering is optional. Existing buffer qualifiers have
not changed. Page-locking protocol has been adjusted to
support global buffering on VAXclusters. By default, global
buffering is not enabled, and local buffering is used.
Note that in a VAXcluster environment, local buffering is
enabled by default for all nodes in the VAXcluster. You
can optionally enable global buffering for all nodes and
then individually tune each node. However, you cannot mix
the use of global and local buffers together on individual
nodes within the same VAXcluster environment. That is, you
exclusively use either global buffers or local buffers for
all nodes in the VAXcluster environment, but not both.
New and Changed Features 1-21
1.7.1 Enabling Global Buffers
To enable global buffering, establish the number of buffers
for a database, and set a global buffer limit per user, a
new qualifier has been added to the RMU/OPEN command, and
a new clause has been added to the SQL CREATE DATABASE and
ALTER DATABASE statements:
RMU/OPEN/GLOBAL_BUFFERS = (TOTAL = integer,USER_LIMIT = integer)
See Section 1.22.15 for a brief description of this new
qualifier and the VAX Rdb/VMS RMU Reference Manual for more
information.
In the SQL CREATE DATABASE or ALTER DATABASE statement,
global buffers are specified as follows:
(GLOBAL BUFFERS ARE ENABLED|DISABLED
NUMBER IS number-glo-buffers,
USER LIMIT IS max-glo-buffers)
See the VAX Rdb/VMS SQL Reference Manual, and the VAX
Rdb/VMS Guide to Database Performance and Tuning for
more information on specifying and setting global buffer
parameters.
Global buffers may require a higher global section quota.
See the VAX Rdb/VMS Installation Guide for information
on defining the maximum number of pages for the global
sections with the GBLPAGFIL system parameter. See the VAX
Rdb/VMS SQL Reference Manual for more information on the
SQL CREATE and ALTER statements. See the VAX Rdb/VMS Guide
to Database Design and Definition for more information on
how database pages are buffered with global buffering.
1.7.2 Applications That Benefit from Using Global Buffers
Global buffers are most suitable for applications in
which shared data is predominant. When global buffers are
enabled, this can save I/O operations, especially in read
applications. The benefits, however, are not restricted to
transactions that use snapshot files.
Tests were run to stress-test the global buffers feature
and these tests provided the following results and
conclusions.
1-22 New and Changed Features
In V4.1 and all previous versions of Rdb/VMS, Rdb/VMS
always creates a global section; that is, with the first
database attach, the Rdb/VMS monitor creates a global
section. Before V4.1, Rdb/VMS used this global section
to maintain the database root data structures.
With Rdb/VMS V4.1 applications using global buffers, this
global section is extended to include the global buffer
pool and the necessary data structures required to maintain
the integrity of its resident data. These data structures
are used to maintain the following information:
Global buffer control blocks
Global buffer page table
Allocate set block counted lists
Global buffer journaling logs
Process local lock (PLL) for system-owned DB and storage
area locks
The following series of questions and explanations
describes how to determine the number of global buffers
you need and how to verify this value, what VMS parameters
to check, how global buffers are used, and the performance
gains that might be expected from their use.
o How many global buffers do you need?
The following example shows how to enable and set global
buffer parameters.
SQL> ALTER DATABASE FILENAME MYDABASE GLOBAL BUFFERS ARE ENABLED
cont> (NUMBER 1000, USER LIMIT 30);
There are two parameters the database admnistrator (DBA)
specifies when altering the database to enable global
buffers.
The NUMBER parameter determines the total number of
global buffers the DBA deems necessary to accommodate
the full set of data pages intended for sharing. Because
global buffers share the root global section and because
the number of data structures maintained in this global
section can vary widely, the DBA should focus on how
much space is needed to store the information; that is,
determine the size of the database data that must reside
in the global section and allow Rdb/VMS to determine the
overall space required for the overall global section.
New and Changed Features 1-23
The USER LIMIT parameter is the maximum number of global
buffers to which a user's allocate-set can grow. If the
maximum number is set to 100 buffers and the DBA defines
the logical name RDM$BIND_BUFFERS to be 100, then the
user will be allocated 100 buffers. A user cannot exceed
this limit.
o What VMS parameters should you watch for?
Whether global buffers are enabled or not, one global
section is allocated for the database root on your
system when the first user attaches to the database.
Since global buffers share this same section, there is
no impact on the number of global sections (GBLSECTIONS)
specific to Rdb/VMS V4.1.
The GBLPAGES is the parameter that determines the number
of global page entries and the global pages that can
be created on the system. This and VIRTUALPAGECNT are
probably the most critical parameters. VIRTUALPAGECNT
determines the number of pages a process can map on the
system.
The GBLSECTIONS, GBLPAGES, and VIRTUALPAGECNT parameters
are modifiable; however, because of their nondynamic
nature, any change requires a system reboot. Digital
recommends that any modifications to the SYSGEN
parameters be made through the MODPARAMS.DAT file and
the VMS AUTOGEN facility. It is possible to hang a
system on reboot due to hardcoded parameters.
o How do you verify these parameters?
The use of GBLPAGES and GBLSECTIONS may be verified
using the VMS INSTALL utility. Issuing the command
"LIST/GLOBAL" at the INSTALL> prompt before and after
attaching to the database provides the information
necessary to determine the number of global page entries
allocated for your database.
$ INSTALL
INSTALL> LIST/GLOBAL/SUMMARY
290 Global Sections Used, 104340/68260 Global Pages Used/Unused
This means that 290 global sections are used out of the
total defined on the system using the SYSGEN utility;
68260 global page entries are available.
1-24 New and Changed Features
The INSTALL utility can be quite verbose on systems with
too many installed products. It will list the whole set
of products installed before displaying the information
you need. There are two ways of avoiding this problem:
o Use the INSTALL utility and specify the LIST command
with the /GLOBAL/SUMMARY qualifiers to obtain used
global page and global section summary information.
o Define lexical functions to obtain unused global page
and global section information.
When you specify the LIST command with the /GLOBAL
/SUMMARY qualifiers, the following used estimates of
the global page and global section parameters display:
$ INSTALL
INSTALL> LIST/GLOBAL/SUMMARY
Summary of Local Memory Global Sections
290 Global Sections Used, 104340/68260 Global Pages Used/Unused
The lexical function F$GETSYI provides a way of easily
getting unused estimates of the global page and global
section parameters. Define symbols as follows and invoke
them at the DCL level or in interactive RDO and SQL to
display the values of GBLPAGES and GBLSECTIONS.
$ GBLPAGES :== "write sys$output f$getsyi("""free_gblpages""")
$ GBLSECTIONS :== "write sys$output f$getsyi("""free_gblsects""")
With these symbols defined, you can check how many
GBLPAGES and GBLSECTIONS are available on your system:
$GBLPAGES
122634
$GBLSECTIONS
109
These results indicate the number of global page entries
still available; that is, 122634 entries out of the
150000 entries that the VMS System Generation Utility
(SYSGEN) has on the system. The total number of global
sections available is 109 out of 400 that SYSGEN has on
this system.
New and Changed Features 1-25
Before altering your database to enable global
buffering, perform a simple database attach with local
buffers enabled. This can reveal that you have allocated
a global section and a number of global page entries to
the database root structures, as the following example
shows:
$SQL
SQL> ATTACH 'FILE MYDATABASE';
SQL>$GBLSECTIONS
108
SQL>
SQL> $GBLPAGES
122112
The "$GBLSECTIONS" shows that you have allocated the
109th section and the GBLSECTIONS left on your system
now stands at 108. The "GBLPAGES" shows that the number
of global page entries dropped from 122634 to 122112
(522 entries have been allocated).
Next, enable global buffers and allocate 1000 buffers
with a buffer size of 4 blocks.
$ SQL
SQL> ALTER DATABASE FILENAME MYDATABASE GLOBAL BUFFERS ARE ENABLED
cont> (NUMBER 1000, USER LIMIT 30);
Before attaching to the database, the number of global
page entries stood at 122634. What is this value after
attaching to the database? A process using interactive
SQL attaches to the database then checks the VMS
parameters:
$ SQL
SQL> ATTACH 'FILE MYDATABASE';
SQL> $GBLPAGES
117344
SQL> $GBLSECTIONS
308
The number of GBPAGES dropped from 122634 to 117344
entries; 5290 entries are allocated to the 1000 buffers
of 4 blocks each. The ALTER DATABASE statement is
expected to allocate 4000 entries (1000 * 4 blocks).
It did this plus an additional 1290 entries on behalf of
1-26 New and Changed Features
the data structures used to maintain the global buffer
pool.
o Is it possible to reduce the GBLPAGES entries?
The size of the global page pool allocated when global
buffers are active depends on many factors, such as
the number of global buffers, the maximum number of
global buffers per user, the number of users, the number
of storage areas in the database, and so forth. These
factors and others make it difficult to provide a simple
formula that you can use.
The number of users is one database parameter the DBA
has control over. Often, the DBA tends to leave certain
database parameters set as default values because
these values may be acceptable and have had no real
impact on system resources or performance. When a
database is created with no explicit total number of
users parameter specified, the default is set to 160.
Most applications do not require that high a value,
especially applications running on a single node, or
applications running with a TP monitor (such as ACMS).
For systems where memory is a critical resource, the DBA
may want to manually adjust the total number of users to
a more realistic value and conserve memory.
The following table shows how the GBLPAGES entries
allocated increases as the number of users increases.
Request made for 500 buffers at 4 blocks each
Number of users Global pages allocated.
20 2742
60 2872
80 3110
100 3156
200 3378
300 3600
400 3824
500 4048
o What other default values should a DBA watch for?
New and Changed Features 1-27
The following is an extract from the RMU/DUMP command
for a database.
- Global buffers are enabled
- Global buffer count is 1024
- Maximum global buffer count per user is 5
- Default database buffer count is 45
The overriding global buffer count is the maximum count
per user that defaults to 5. With global buffers,
the defined or default database buffer count can be
overridden with the logical name RDM$BIND_BUFFERS.
However, the maximum global buffer count per user
can override the value defined by this logical name.
With local buffers, the logical name RDM$BIND_BUFFERS
overrides the defined or default database buffer count.
Using an SQL ALTER DATABASE or RDO CHANGE DATABASE
statement, it is best to explicitly specify the maximum
global buffer count per user at or slightly over what
you would expect users to have, then use the logical
name RDM$BIND_BUFFERS to adjust the user allocate set
as needed. Otherwise, users will get 5 buffers each in
their allocate set regardless of what the DBA specifies
with the RDM$BIND_BUFFERS logical name.
Occasionally a user may require 100 buffers, although
in most cases 20 would suffice. For example, report
generation done nightly may require data from multiple
tables and, therefore, 100 buffers may be needed. Daily
activities may only require 20 or 30 buffers. Setting
the maximum at 100 allows the user to switch as needed.
Setting the limit to 20 or 30 does not meet the report
generation requirements.
Similarly, when the maximum global buffer count is set
high, the DBA must ensure that enough buffers are in the
global buffer pool for all database users; otherwise,
processes with even a low allocate set will fail. For
example, if the DBA sets the total number of global
buffers to 1000 buffers, sets the user limit to 100,
then defines RDM$BIND_BUFFERS to 100, an 11th process
attaching to the database is rejected. The advice to the
DBA is to keep a few spare buffers for that occasional
extra user (11th process in this case). In this example,
1-28 New and Changed Features
an additional 100 buffers would be good to accommodate
extra users who may need only 2 to 3 buffers each.
o Are there any other defaults to watch for?
In Rdb/VMS V4.1, the carry-over lock optimization is
on by default. With this optimization on, a process
does not relinquish locks on resources unless they are
requested by a contending process. This optimization
works well in cases where a process updates the same
set of pages often, and there is little or no contention
over the resources this process owns.
In tests, users randomly generated keys and performed
updates based on records with those randomly generated
keys. Using the RMU/SHOW STATISTICS command, you can
observe that the system throughput reaches a peak,
drops to zero every few seconds, then goes back up
to peak throughput and so forth. The carry-over lock
optimization is the reason for this behavior. When the
database is altered and you turn off the carry-over lock
optimization, the system maintains its peak throughput
throughout the test. In high contention situations,
carry-over locks may not be useful. The DBA may want to
verify the default setting in V4.1 to determine whether
they are suitable for their particular application.
Note that this problem is not specific to only global
buffers.
o What happens when you allocate more global buffers than
your system parameters can handle?
Rdb/VMS does not give you an error message when you
alter the database and specify a number of global
buffers larger than your system can handle. However,
on the first attempt to attach to the database, you will
get the following or a similar error message:
%RDB-F-SYS_REQUEST, error from system services request
-RDMS-F-EXQUOTA, exceeded quota
-LIB-F-INSVIRMEM, insufficient virtual memory
Your database at this time is in a limbo state because
the DBA cannot attach to the database using either SQL
or RDO to alter the database global buffer parameters.
To recover from this situation, use the RMU/OPEN command
New and Changed Features 1-29
and lower the global buffer parameters to a level your
system can handle.
This same user action also applies to cases where a
database backup is restored on a smaller system or a
system configured with low SYSGEN parameters relative to
your database and application requirements. An example
of the RMU/OPEN command follows:
$ RMU/OPEN/GLOBAL_BUFFERS = (TOTAL=n,USER_LIMIT=z) MYDATABASE
where the parameter n is a value likely to match your system parameters.
You must then follow up with the necessary changes to
the system parameters using the MODPARAMS.DAT file and
the AUTOGEN utility.
o Are there a lot of page faults with global buffers?
With global buffers active, a higher than usual number
of GLOBAL VALID faults is observed. A GLOBAL VALID FAULT
indicates that the page that caused the fault is in the
global buffer pool but is in some other user's allocate
set. These are soft faults and are harmless. The VMS
monitor (MONITOR PAGE) and VAX SPM give a breakdown of
page faults by category.
o Are there more lock operations with global buffers than
with local buffers?
Global buffers do incur more lock operations than
local buffers. Some of the additional lock operations
are system-owned page locks, which can be costly,
while others are local locks. The system-owned page
locks maintain page version numbers among nodes in a
VAXcluster. Local inexpensive locks synchronize access
to the global buffer data structures. The increase in
locks that is noticed in these tests are mostly page
locks and some record locks. The DBA should ensure the
LOCKIDTBL has enough entries in it to accommodate the
additional locking. Use the VMS MONITOR LOCK command
to show the total locks on the system, then verify this
total against the LOCKIDTBL entries on the system.
o What performance gains can be expected from global
buffering?
1-30 New and Changed Features
Because global buffers require a number of data
structures to maintain data integrity and consistency,
you should expect a certain level of performance
overhead. The following tests compared global buffers
to local buffers.
The first set of tests consisted of loading 60,000
records in 10 storage areas, submitting 15 users to
execute light update transactions against the database
with redo logging, checkpointing, and commit to journal
optimization applied.
The database was partitioned among 15 users such that
there was no data sharing whatsoever. With each process
working in a separate database partition, there was no
use for global buffering. In fact, global buffers added
overhead and caused the TPS rate to drop from 68.5 to
62.8 or approximately a 9% drop in throughput.
Global buffering was exercised in read-only environments
and the results were compared to local buffers. One
thousand records were loaded in several database storage
areas and ten batch processes submitted to perform read
transactions. Ten users randomly generated keys between
1 and 1000. Each key was then applied to retrieve a
record from the database. The exercise consisted of
retrieving two, three, and four records using unique
keys. The results for two-, three-, and four-record
retrievals are as follows:
Read-Only Environment VAX 6320 - 128MB memory
Number of GBL
Test style TPS Read I/Os Memory Locks %CPU %Increase
Local Buffers 60 2 20.8% 32.6 100%
Global buffers 72 0 22.2% 42.7 100% 20
Local Buffers 42.7 3 21.6% 35.4 100%
Global buffers 51.5 0 22.2% 50.9 100% 22
Local Buffers 32.8 4 22.2% 65.2 100%
Global buffers 40.5 0 22.2% 85.2 100% 23
With global buffers, the full set of records was cached
and no read I/O operations were performed. With local
buffers it would have taken 1000 buffers for each of the
10 users to achieve no I/O operation read status.
New and Changed Features 1-31
In read-only environment tests, global buffers increased
locking. With zero reads in all cases and in spite of
overhead due to the additional locking, an approximately
20% performance increase was achieved over the use of
local buffers. No memory savings were observed in these
tests.
In read/write environment tests, users performed an
update on each record they read with instances of two-,
three-, and four-record updates being performed. The
results are as follows:
Read/Write Environment VAX 6320 - 128MB memory
GBL
Test style TPS I/Os Memory Locks %CPU %Increase
R W
Local Buffers 35.5 2 2 21.6% 39.3 ~100%
Global buffers 38.5 0 2 22.5% 53.3 ~100% 8
Local Buffers 25.5 3 3 22.7% 56.8 ~100%
Global buffers 28.0 0 3 23.1% 76.9 ~100% 10
Local Buffers 19.8 4 4 22.4% 79.4 ~100% 12.5
Global buffers 22.3 0 4 23.4% 106.5 ~100%
In read/write environment tests with global buffers
there were no read I/O operations because all data was
cached in global memory. There were, however, as many
writes as with local buffers. The tests were set up such
that no page in a user's allocate set was modified twice
by the same process in the same checkpoint period. The
random nature of the key generation was such that pages
did not last long in the user's allocate set. Modified
data pages leaving the user's allocate set were flushed
to disk because there was no mechanism by which to flush
all modified pages database-wide.
As with the read-only environment tests, global buffers
do incur more overhead than local buffers as shown in
the locking column. However, where there is a level of
data sharing, global buffers do save on I/O operations
and can achieve better throughput than local buffers.
1-32 New and Changed Features
________________________ Note ________________________
To ensure a proper comparison of global buffers
to local buffers performance test results, the
equivalence case should be kept in mind. That is,
a special case of the settings is when the local and
global buffers settings are equivalent. The basis
of this equivalence is memory usage and this can be
expanded into the following two rules.
o The total number of buffers used by all the
users must be equal for local and global
buffers.
o The number of buffers used by each user must be
equal for local and global buffers.
One way to set up buffering parameters is as follows.
Set up the default number of buffers to be B. Set up
the "maximum global buffers per user" also to be B.
If you are going to have N users for the performance
test, set up the number of global buffers to be B
times N. Do not use any buffering logicals to keep
it simple. Following these guidelines permits you to
enable and disable global buffers and run performance
tests.
______________________________________________________
To help determine the benefits from enabling global
buffers, the RMU/SHOW STATISTICS command is enhanced to
include both I/O and locking information. When you select
the PIO Statistics Screen, the "found in gb pool" field
describes the number of times that the requested page
was found in the database global buffer pool; that is,
the number of I/O operations saved due to enabling global
buffers because the requested page was found in the global
buffer page.
The global buffer page table (GBPT) slot lock is a new
type of lock in V4.1. It is a VMS local lock mastered on
the same node and therefore is not as expensive as other
distributed locks in a VAXcluster environment. The GBPT
slot locks protect global buffer data structures from being
inconsistently updated by different users. GBPT slot locks
are requested and released whenever a database page is
brought into or purged from the allocate sets of users.
New and Changed Features 1-33
The RMU/SHOW STATISTICS command is enhanced to show both
the GBPT slot lock type information for all lock operations
as well as showing information about a particular lock
operation for all lock types that includes the GBPT lock
type.
The "found in gb pool" statistic represents the savings
in I/O due to global buffers, whereas the total number of
GBPT slot locks represents the locking overhead associated
with global buffers. Thus, you can use the new statistics
to determine if global buffers are helpful for your
application.
Note that the global buffer defaults are derived in the
following manner. As previously mentioned, setting the
total number of global buffers is determined by system
quotas (GBLPAGES and GBLPAGFIL), available physical memory,
and process VM quotas. Therefore, a low enough number of
global buffers, 250, is chosen as the default value because
that value can be accommodated by existing system settings.
The default maximum global buffer count per user, 5, is
determined by dividing the default value (250) by the
default number of users (50). Because these default values
are not good for every system, your system manager and
database administrator need to determine values for these
two global buffer parameters appropriate for your system
and database application.
1.8 Journal Fast Commit Transaction Processing and Commit to
Journal Optimization
Rdb/VMS V4.1 enables you to improve database performance
in some environments, by specifying how and when Rdb/VMS
writes updated pages from memory buffers to disk. This is
called journal fast commit transaction processing.
With Rdb/VMS V4.0 and earlier versions, Rdb/VMS writes
updated database pages to disk each time a transaction
executes the COMMIT statement. If a transaction fails
before committing, Rdb/VMS performs a rollback (undo)
of the failed transaction; it never has to redo previous
successful transactions. This type of undo/noredo recovery
is still the default recovery procedure for V4.1.
1-34 New and Changed Features
You can change the default Rdb/VMS V4.1 recovery procedure
by enabling journal fast commit transaction processing.
With this procedure, Rdb/VMS retains updated pages in
the buffer pool and does not write the pages to disk when
a transaction commits. The updated pages can remain in
the buffer pool until a database administrator-specified
threshold, called a checkpoint, is reached. At checkpoint,
all the pages a process has updated, for multiple
transactions, are flushed to disk. If a transaction fails,
Rdb/VMS must undo the current, failed transaction and redo
all committed transactions since the last checkpoint.
This feature is suited to stable environments where a
process updates the same rows for multiple transactions.
Because pages containing updated rows remain in the buffer
pool until checkpoint, you save page I/Os. You also save
RUJ I/Os because the RUJ buffer does not get flushed to the
.RUJ file after each transaction.
See Section 1.17.30, Section 1.19.6, the VAX Rdb/VMS SQL
Reference Manual, and the VAX Rdb/VMS Guide to Database
Performance and Tuning for more information.
The following series of questions and explanations
describes journal fast commit transaction processing,
logging, and checkpointing, how each is used, and the
performance gains that can be expected from their use.
o What is journal fast commit transaction processing? What
is checkpointing? With journal fast commit transaction
processing, updates are submitted to the .AIJ file
at transaction commit time, which in a multiuser
environment adheres to the Rdb/VMS I/O grouping feature.
Updated pages are marked and maintained in the user
buffer pool, and at checkpoint, the database must be
updated. Otherwise the recovery mechanism would have a
large amount of updates to redo in case of failure. At
checkpoint, all committed updates in the user pool are
flushed to disk.
Checkpointing may be controlled by the number of blocks
written to the .AIJ file, or by setting a time interval
between checkpoints. Both options are applicable using
the SQL ALTER DATABASE statement. A third and probably
better way of setting a checkpoint is based on the
New and Changed Features 1-35
number of transactions between checkpoints. This is
defined using the following logical name:
DEFINE RDM$BIND_CKPT_TRANS_INTERVAL n
where n is the number of transactions between
checkpoints.
o Can I have all three options set? What happens then?
The first one to become true will trigger the checkpoint
and all three options (number of blocks written to the
.AIJ file, the time interval between checkpoints, and
the number of transactions between checkpoints) are then
reset.
o What should I set my checkpoint interval to?
Setting a checkpoint interval depends on:
- Your application
- The pattern of update transactions you perform
- The buffer pool size you allocate
For transactions where updates or inserts are performed
on new data pages, a large checkpoint interval would
incur too many I/O operations that would delay your
process at each checkpoint. A process that modifies 100
different pages between checkpoints must flush 100 data
pages at checkpoint. A process that performs 100 updates
at the rate of 2 updates to the same page must flush 50
pages to disk at checkpoint. A process that performs 100
updates on the same 1 or 2 pages will only write 1 or
2 pages at checkpoint; thus this process can support a
very long checkpoint period.
The gauge of how to set your checkpoint interval is
your average throughput for your typical types of
transactions. If the gap between your system peak
and average throughput is wide, and if you notice an
unsteady average throughput, chances are your checkpoint
is set too high and requires adjusting.
o What role does buffer pool size play in checkpointing?
1-36 New and Changed Features
If a checkpoint is set to occur every 60 transactions,
and the transaction pattern consists of performing one
update to the same page and a second update to a new
page for every transaction, at least 61 buffers would
be needed in the process's buffer pool or allocate set
to maintain updated data until checkpoint time. As a
general rule, you should have a buffer pool slightly
larger than the number of transactions per checkpoint.
That is, the number of buffers per user cannot be
smaller than the data the user would like to maintain
in memory until checkpoint time. If a user is updating
the same 10 pages over and over again, and all pages fit
in 5 buffers, then only 5 buffers are needed.
o What happens when my buffer pool size is lower than the
checkpoint?
Updated pages are flushed to disk prematurely. Rdb/VMS
implemented an optimization that performs a premature
flush of all committed data when it determines a buffer
slot is needed for incoming data. No .RUJ file I/O
operations are required in this case.
o Under what circumstance will RUJ I/O operations be
performed?
If an updated page in the users buffer is required by
another process, then it is possible that the page
will be flushed to disk so that the other process can
read it into its own local buffer pool. Under these
circumstances, the .RUJ file needs to be written to
allow the uncommitted changes on the flushed page to
roll back.
o What impact will checkpointing have on my I/O
operations?
If your transaction updates many pages, and your
checkpoint is set too high, performance will degrade at
checkpoint time, particularly if all the I/O operations
are flushed to a single device. Check the disk response
times, and spread the checkpoint I/O operations
across multiple spindles to alleviate this overhead.
Asynchronous batch writes will improve performance in
this case. When 100 writes are made to the same device,
the I/O operations are sorted first before they are
New and Changed Features 1-37
written to disk so that the head sweeps the disk only
once.
o What performance gains should I expect from journal fast
commit transaction processing and checkpointing?
With journal fast commit transaction processing,
there are few, if any, .RUJ file I/O operations.
This will shorten your transaction path length. The
performance gains due to checkpointing will depend on
your application and page update patterns. Journal fast
commit transaction processing and checkpointing have
shown performance increases of 25% to 30% in local tests
and with TPC benchmarks.
See Section 1.9 for information about recovering from a
lost .AIJ file if you have journal fast commit transaction
processing enabled.
1.8.1 Commit to Journal Optimization
Commit to journal optimization is a new strategy that
provides a significant increase in commit processing speed
by eliminating the majority of I/O to the database root.
Committing a single transaction in previous versions of
Rdb/VMS included the following general steps:
1. RUJ buffers written to .RUJ file
2. Updated pages written to disk
3. AIJ buffers written to .AIJ file (if AIJ is enabled)
4. Commit information written to root, requiring a minimum
of one I/O and four VMS lock requests
Enabling journal fast commit processing (required for
commit to journal optimization) eliminates steps 1 and
2; enabling commit to journal optimization eliminates step
4.
Enabling commit to journal optimization entails the
following restrictions:
o Journal fast commit processing must be enabled.
o AIJ must be enabled.
o Snapshots must be disabled or enabled deferred.
1-38 New and Changed Features
o Online database backup is not possible.
o No-quiet-point AIJ backup is not possible.
Because a transaction does not write to the database root
for commit to journal optimization, users are preassigned a
block of transaction sequence numbers (TSNs). The number
of TSNs assigned is specified when commit to journal
optimization is enabled using either the SQL ALTER DATABASE
or the RDO CHANGE DATABASE statements.
The commit to journal optimization is for update-only
applications. This optimization requires that snapshots
be disabled or deferred. For those approximately 1% of
Rdb/VMS applications that can take advantage of this
feature, the performance gains have been between 15% to
20% over and above the fast commit performance gains (and
with local buffers) as observed in local tests and with TPC
benchmarks.
1.9 Recovering from a Lost .AIJ File When Journal Fast Commit
Transaction Processing Is Enabled
Losing the .AIJ file represents a single point of permanent
failure in a database system. This means that if only one
component of Rdb/VMS fails, such as access to the .AIJ
file, the database system fails; if this loss is due to
a disk head crash, the loss is considered permanent. This
scenario assumes that the .AIJ file is not on a disk that
is part of a shadow set, in which case no failure occurs.
For versions of Rdb/VMS previous to V4.1 or for V4.1 when
journal fast commit transaction processing is disabled,
if the .AIJ file is lost, all current transactions are
rolled back. In V4.1, when journal fast commit transaction
processing is enabled, the procedure for recovering
the database after losing the .AIJ file is changed from
previous versions because all updates to database pages are
written only to the .AIJ file. If the .AIJ file is lost,
for example due to a disk head crash, and a transaction
is in the commit processing phase, a file access error
(FILACCERR) is returned because Rdb/VMS attempted to write
the updates to the .AIJ file. If a process receives this
error while attempting to write to the .AIJ file, instead
of just signaling an exception and allowing the process to
terminate, the buffer pool is first flushed. This action
New and Changed Features 1-39
forces all committed updates to be written to disk and
then the process is allowed to terminate. This recovery
procedure guarantees that access to the .AIJ file is not
necessary. Because updates are already written to the
database pages, redo recovery is not necessary.
In addition, the process that receives the file access
error (FILACCERR) while writing to the .AIJ file, also
sets the (AIJ_CORRUPT) flag in the database root file. When
this flag is set, it lets all users know that the current
.AIJ file is corrupt. When the DBR process begins for the
terminating user process, it first checks the AIJ_CORRUPT
flag. If this flag is set, the DBR process does not try
to access the .AIJ file because it knows that before the
user process terminated, it flushed its buffer pool and
consequently did not need to perform redo recovery.
For other users on the system, a database freeze takes
effect. All users receive a <database monitor forced image
exit to protect database> error message and are forced off
the database. Each user process in turn detects that the
AIJ_CORRUPT flag is set. Therefore, no user can write to
the .AIJ file, and each is forced to flush its buffer pool
to disk. Then each user detaches from the database. Note
that no user process needs to do redo recovery.
If the AIJ_CORRUPT flag is set, users cannot attach to the
database and receive the <database monitor forced image
exit to protect database> error message. If you display
the database header portion of the root file when the AIJ_
CORRUPT flag is set, two new lines appear at the beginning
of the journaling section:
1-40 New and Changed Features
Journaling...
- Current AIJ file is inaccessible <--- new line
- AIJ file must be modified <--- new line
- After-image journaling is enabled
- Default AIJ filename is "DUA1$:[DB]"
- Current AIJ filename is "$111$DUA1:[DB]MFPERS.AIJ;3"
- Initial AIJ file allocation is 0 blocks
- AIJ file extension is 512 blocks
- AIJ roll-forward sequence number is 0
- AIJ backup sequence number is 0
- AIJ file has never been backed up
- No AIJ file backup in progress
- No default recovery-unit journal filename
The AIJ_CORRUPT flag can be cleared only by disabling the
.AIJ file or creating a new .AIJ file. Because no users
can attach to the database, the DBA must first use the
RMU/ALTER command to delete the AIJ entry in the root
file, which essentially disables the .AIJ file. For more
information on using the RdbALTER utility to disable the
.AIJ file, see the VAX Rdb/VMS RMU Reference Manual and
the VAX Rdb/VMS Guide to Database Maintenance. After this
change is committed to the database using the RdbALTER
utility, the DBA can use an SQL ALTER DATABASE or RDO
CHANGE DATABASE statement to create a new .AIJ file.
Once the DBA disables the .AIJ file or creates the new .AIJ
file, these first two lines in the journaling section of
the root file header no longer display.
________________________ Note ________________________
When the .AIJ file is lost, any earlier backups of
the database are invalidated. This is because a backup
is only useful if you have the correct sequence of
.AIJ files to bring the database to its most recent
state. If you lose an .AIJ file, then the sequence
of .AIJ files is broken and you can never bring the
database to its most current state. Therefore, the
last step the DBA must perform before making the
database available to users again is to do a complete
database backup operation.
______________________________________________________
New and Changed Features 1-41
To prevent the loss of the .AIJ file from causing this
single point of permanent failure when journal fast commit
transaction processing is enabled, you should shadow the
.AIJ file. If one of the disks has a disk head crash,
VMS automatically uses the other disk, which it has
kept up-to-date with the first disk. For this reason,
Digital recommends that if you enable journal fast commit
transaction processing in V4.1, that you shadow the .AIJ
file as a preventative measure.
If a double point of failure occurs, that is, if in
addition to losing the .AIJ file due to a disk head crash,
the system crashes, data may be lost. If the system crashes
after all user processes' buffers are flushed to disk,
no data is lost because recovery was complete before the
system crashed. In this instance, these two points of
failure are really two closely occurring single points
of failure. However, if the system crashes before or as
buffers are flushed to disk, then data is lost. This is
because data in buffers was neither written to the .AIJ
file nor flushed to disk or was incompletely flushed to
disk. This is a double point of failure. The only recourse
in this situation is to restore the database and roll it
forward. Rdb/VMS provides for recovery without loss of data
from any single point of failure but does not in the case
of a double point of failure.
1.10 Carry-Over Locks Optimization Can Be Disabled
The carry-over locks option is a database-wide parameter
that allows you to disable carry-over lock optimization.
This optimization (implemented in Rdb/VMS Version 4.0) is
enabled by default. Although this is an advantage in most
environments, it can result in false lock conflicts in some
applications.
Carry-over lock optimization holds area and record locks
across transactions and depends on NOWAIT transactions
asking for and acquiring the NOWAIT lock. This can result
in long delays if concurrent users are executing long
verbs. You should consider disabling the carry-over lock
optimization if transactions experience noticeable delays
in acquiring the NOWAIT lock (as seen in the output of the
RMU/SHOW STATISTICS command). Note that if you do disable
the carry-over locks option, there may be some performance
1-42 New and Changed Features
degradation because transactions will acquire and release
area and top level ALG locks for every transaction.
The carry-over locks option, CARRY OVER LOCKS ARE [ENABLED
| DISABLED], is available with either the SQL CREATE
DATABASE or ALTER DATABASE statements, or the RDO DEFINE
DATABASE or CHANGE DATABASE statements.
1.11 After-Image Journal Optimization
An .AIJ file can be optimized to eliminate unneeded and
duplicate journal records, and to order journal records
by using the RMU/OPTIMIZE/AFTER_JOURNAL command. See
Section 1.22.16 and the VAX Rdb/VMS RMU Reference Manual
for more information about this command. The optimized .AIJ
file (.OAIJ) will provide better rollforward performance
and thus greater database availability. The optimizations
used to optimize an .AIJ file are:
o Elimination of journal records from transactions that
rollback
Transactions in an .AIJ file that rollback are
not applied during the rollforward process (they
are ignored). So, the journal records from these
transactions can be eliminated from the .AIJ file.
o Elimination of duplicate journal records (duplicate
journal records are journal records that update the same
database record)
During the rollforward of an .AIJ file, duplicate
journal records cause a database record to be updated
multiple times. Because each successive update
supercedes the previous update, only the "last" update
is relevant. Therefore, all but the "last" update to a
database record can be eliminated from an .AIJ file.
o Journal records are ordered by physical dbkey
Ordering journal records by physical dbkey improves I/O
performance at rollforward time.
AIJ optimization uses VMS SORT to sort journal records.
To improve the efficiency of the sort operation, the
number and location of the workfiles used by the VMS
Sort utility (SORT) can be set by the user. The number
of workfiles to be used is controlled by the RDBMS$BIND_
New and Changed Features 1-43
SORT_WORKFILES logical name. Allowable values are 2-
10 with a default of 2. Specify the location of these
workfiles with device specifications using the SORTWORKn
logical names. See the VMS Sort/Merge Utility Manual
for more information on the VMS Sort utility. See the
VAX Rdb/VMS Guide to Database Performance and Tuning for
more information on using these logical names.
________________________ Note ________________________
Because an optimized .AIJ file is not functionally
equivalent to the original .AIJ file, an .AIJ file
should not be discarded after it has been optimized.
Rollback records are automatically not included in
an .OAIJ file. In addition, duplicate commit journal
records are eliminated in the .OAIJ file based on
the order in which they appear in the .AIJ file, so
that only the last update to a database record is
retained in the .OAIJ file. This is done in two ways:
by sorting records within each transaction ID (TID)
and sorting all TIDs, and retaining only the last
duplicate record within the same TID or from among all
TIDs in the .OAIJ file. For this reason, an .OAIJ file
cannot be used, for example, to perform an RMU/RECOVER
/UNTIL command.
______________________________________________________
The RMU/OPTIMIZE/AFTER_JOURNAL utility has the following
restrictions:
o The current .AIJ file cannot be optimized.
o An optimized .AIJ file cannot be optimized.
o Any .AIJ file with the possibility of containing
incomplete transactions cannot be optimized. Incomplete
transactions can occur in an .AIJ file under the
following circumstances:
- If the .AIJ file is backed up with a no-quiet-point
backup
- If the previous .AIJ file was backed up with a no-
quiet-point backup
1-44 New and Changed Features
- If the .AIJ file has unresolved distributed
transactions
There are no workarounds to these restrictions.
The following are restrictions for recovery using optimized
.AIJ (.OAIJ) files:
o No by-area recovery (RMU/RECOVER/AREA) is possible with
an .OAIJ file.
o No until recovery (RMU/RECOVER/UNTIL) is possible with
an .OAIJ file.
o No recovery with an .OAIJ file is possible if the
database or storage areas or both are inconsistent with
respect to the .OAIJ file.
A workaround to these restrictions is to use the original,
unoptimized .AIJ file instead of using the .OAIJ file to do
the recovery operation.
1.12 Changes to the EXPORT/IMPORT Statements for V4.1
The following changes have been made to the SQL and RDO
EXPORT/IMPORT statements for V4.1.
1.12.1 SQL and RDO IMPORT and EXPORT Statements Support All New
Rdb/VMS V4.1 Features
The SQL and RDO IMPORT statements import interchange files
(RBR) created using the SQL and RDO EXPORT statements in
V4.1 and includes support for the following new information
in the interchange file:
o Multischema databases created by SQL
o New database-wide attributes
- Global buffers
- Carry-over locks
- Lock timeout interval
- Journal fast commit transaction processing and commit
to journal optimization
- Adjustable lock granularity (although not new, this
attribute is only exported and imported by Rdb/VMS
V4.1 and later)
New and Changed Features 1-45
o Storage area attributes
- Write-once attribute is restored (output device
should be a WORM device)
- Mixed area thresholds (although not new, this
attribute is only exported and imported by Rdb/VMS
V4.1 and later)
- SPAM intervals (although not new, this attribute
is only exported and imported by Rdb/VMS V4.1 and
later)
o Storage map attributes
- Logical area thresholds
o New data type support
- DATE, TIME, TIMESTAMP, and INTERVAL data types are
imported, although these data types cannot be defined
or used directly in RDO
o SERdb database attributes
o An informational message is now issued naming the
original database's .AIJ file
________________ Permanent Restriction ________________
The RDO IMPORT statement cannot be used to disable
MULTISCHEMA. You must use the SQL IMPORT statement and
the MULTISCHEMA IS OFF clause to perform this task.
______________________________________________________
These features should be set in the same way in the newly
created database. That is, information about the new V4.1
features is not lost during the sequence:
o SQL EXPORT DATABASE FILENAME file INTO file2;
o SQL DROP DATABASE FILENAME file;
o SQL IMPORT DATABASE FROM file2 FILENAME file;
The user is informed of the settings of all new features in
the created database, with the exception of new STORAGE MAP
and STORAGE AREA options and the date-time data types.
1-46 New and Changed Features
The RDO EXPORT statement also backs up complete information
about these features. The RDO EXPORT statement is
essentially identical to the SQL EXPORT statement. The
first items in the previous example could be replaced by
the following without any change in the results:
o RDO EXPORT file INTO file2
o RDO DELETE DATABASE FILENAME file.
1.12.2 Changed Syntax for the SQL IMPORT Statement
In earlier versions of SQL, the IMPORT statement began with
the following syntax:
IMPORT [AUTHORIZATION auth-id] SCHEMA FROM file-spec -
[option-list]
For V4.1, the preferred syntax is:
IMPORT DATABASE FROM file-spec FILENAME file-name [WITH ALIAS alias] -
[option-list]
Both formats now offer most of the new options available in
the SQL CREATE DATABASE statement to override information
found in the interchange file. These extensions include
such clauses as MULTISCHEMA is ON, METADATA is NOT SINGLE
LEVEL, GLOBAL BUFFERS are DISABLED, and JOURNAL CHECKPOINT
is ENABLED. New CREATE clauses allowed within the CREATE
DATABASE statement are not included in the V4.1 IMPORT
syntax, though extensions to the CREATE STORAGE AREA,
CREATE STORAGE MAP, and CREATE INDEX statements may be
used.
This extended syntax is useful to enable new V4.1 features
when using an interchange file created by an earlier
version of Rdb/VMS, and to change settings found in an
exported V4.1 database.
1.12.3 Changed Syntax for the SQL EXPORT Statement
In earlier versions of SQL, the EXPORT statement began with
the following syntax:
EXPORT SCHEMA [AUTHORIZATION auth-id] INTO file-spec
For V4.1, the preferred syntax is:
EXPORT DATABASE [ALIAS alias] INTO file-spec
New and Changed Features 1-47
1.12.4 NO EXTENSIONS Error Checking
If the NO EXTENSIONS clause is specified in the EXPORT
statement (SQL or RDO), some database information will not
simply be ignored. The following conversions will occur for
some of the new data types:
o SIGNED BYTE (TINYINT) domains (global fields) will be
converted to the SIGNED WORD (SMALLINT) type. A message
is output informing the user of this change.
o Domains (global fields) of the new DATE, TIME, or
TIMESTAMP data types will be exported as "generic" DATE
VMS data. The user is not informed of this conversion.
These conversions allow the information in the database
to be referenced without error if the IMPORT operation
is done in a database system (such as Rdb/ELN) that does
not support these new data types. Note, however, that
complete type information about the affected columns is
irretrievably lost.
In addition, any INTERVAL domain (global field) found in
the database will cause the EXPORT operation to fail. These
data types are not supported in any fashion by the database
systems to which a NO EXTENSIONS interchange file is
targeted. Digital recommends either removing references to
these data types from the database, including all INTERVAL
columns (fields) and domains (global fields); or omitting
the NO EXTENSIONS qualifier from the EXPORT statement.
All new Rdb/VMS 4.1 features are not exported when the NO
EXTENSIONS clause is specified.
1.13 Online DBA Activities
The following database activities can now be performed with
the database on line and users attached to the database:
o Perform online backup of the database, either full
backup or incremental
o Perform online restore operations of storage areas
o Perform online recover operations of restored storage
areas
o Perform online spooling of the .AIJ file (that is,
truncate and back up contents of the .AIJ file)
1-48 New and Changed Features
o Set the following security auditing characteristics
- Enable or disable security auditing
- Enable or disable security alarms
- Set audit FIRST event flag
- Set audit FLUSH event flag
- Set security audit event class flags
- Set audit security alarm names
- Set the security audit file name
o Create an online copy of a database
o Monitor and record performance statistics
o Display any database area (header, indexes, data areas)
o Change the lock timeout interval
o Change the following database characteristics
- Change the OPEN MODE of database
- Change the default number of database buffers per
user (a deferred action that is seen only by new
users)
- Change the default number of database recovery
buffers
- Change allocation and extension characteristics for
the .AIJ file
- Change the global buffer count (a deferred action
that is seen only by new users)
- Change the maximum number of global buffers per user
(a deferred action that is seen only by new users)
o Change the following storage area characteristics
- Enable or disable volume spreading
- Enable or disable expansion and set expansion
parameters (minimum, maximum, and percent)
- Extend a storage area
New and Changed Features 1-49
o Change the following storage area characteristics only
with exclusive access to the storage area
- Set the read-only and write-once attributes for all
storage areas with the exception of the read-only
and read/write attributes for the RDB$SYSTEM storage
area, which must be done with the database closed or
off line
- Change SPAM thresholds
- Truncate a snapshot file
- Initialize a storage area
- Delete a storage area
- Enable or disable SPAMs
o Start an RMU/VERIFY check of database integrity (this
operation must wait for exclusive and batch-update
transactions to complete)
o Update metadata
- Create, alter, or drop catalogs
- Create, alter, or drop collating sequences
- Create or drop constraints
- Create, alter, or drop domains
- Create, alter, or drop indexes
- Grant or revoke protection
- Create, alter, or drop tables
- Create, alter, or drop schemas
- Create or drop triggers
- Create, alter (certain parameters as previously
specified), or drop storage areas, all with only
exclusive access to the storage area
- Create, alter, or drop storage maps
- Create or drop views
o Move a storage area
1-50 New and Changed Features
1.14 Offline DBA Activities
The following database activities can only be performed
with the database off line because they require the
database to be closed down:
o Create, alter, or drop a database
You can alter any of the database objects and database
characteristics as stated in Section 1.13 with the
database on line and while users are attached to the
database.
o Change the following database characteristics:
- Change the maximum number of database users
- Change the maximum number of VAXcluster nodes that
can access the database
- Change the space allocation characteristics such as
extension values of the database
- Enable or disable snapshots
- Change the snapshots deferred characteristic
- Change the global buffer enabled characteristic
- Change the statistics enabled characteristic
- Change the read-only or read/write attribute for
RDB$SYSTEM
- Change the lock carry-over optimization
characteristic
- Change journal fast commit processing parameters
- Change checkpointing parameters
- Change journal optimization parameters
________________________ Note ________________________
Note that the database can dynamically extend as
required, but the default value of the size to extend
by can be altered by the DBA.
______________________________________________________
New and Changed Features 1-51
- Change the database locking characteristics (whether
or not automatic locking granularity is enabled or
disabled)
- Change the after-image journaling (change the file
name used or the enable/disable option)
- Change the usage of snapshot files (enabled or
disabled, and if enabled, whether immediate or
deferred)
- Change the allocation characteristics for a snapshot
file
- Change the requirement for a dictionary to be used
when metadata is changed
1.15 Rdb/VMS Now Supports Creating Storage Areas for Segmented
Strings and List Data on WORM Optical Disks
Beginning with V4.1, Rdb/VMS supports several WORM
optical disk drives as a storage media for storing lists
or segmented string data. See Section 1.16.2 for more
information. In addition, Rdb/VMS supports all maintenance
tasks for write-once storage areas on write-once, read-
many (WORM) optical disks that are currently supported for
read/write and read-only database storage areas.
Rdb/VMS reads and writes using the VMS QIO system services.
Any optical disk drive and device driver that supports
the QIO system service and responds to QIOs performing
virtual I/O operations and to QIOs specifying FILES-11
ACP functions as well as the atomicity requirements of the
I/O operations from the database system can be used with
Rdb/VMS. To optimize space allocation on a WORM optical
disk device, the device driver must be able to preallocate
storage area space by reserving space on the drive relative
to a specified size. By doing this, Rdb/VMS does not have
to do a file extension/write operation to the end of the
file for each write operation. Using the VMS QIO system
service, Rdb/VMS assumes that:
o Unwritten pages must return all zeros when read
o Modified pages are revectored (important for file
extensions that need modifications to the file header)
1-52 New and Changed Features
o A WORM optical disk device must read and write as if it
is regular read/write disk media
To Rdb/VMS, a WORM optical disk device is seen as just
another read/write device. For this reason, RMU requires
the user to specify special qualifiers (/WORM and /NOSPAMS)
when writing to a WORM optical disk device using certain
RMU commands (RMU/RESTORE, RMU/RESTORE/ONLY_ROOT, RMU/COPY_
DATABASE, and RMU/MOVE_AREA). Using the /NOSPAMS qualifier
prevents SPAM pages from being written to WORM optical disk
devices. If you use the RMU/MOVE command to move a storage
area to a WORM optical disk device, you must set the read
attribute to read-only, not write-once. See Section 3.7.4
for other restrictions on using RMU commands with the /WORM
and /NOSPAMS qualifiers.
1.16 Other New and Changed Features for V4.1 That Affect All
Interfaces
Sections 1.16.1 through 1.16.18 describe other new and
changed features for V4.1 that affect all interfaces.
1.16.1 Performance Improvement for Creating Tables and Views
Prior to Version 4.1, Rdb/VMS performed excessive I/O
operations when creating tables (relations). These I/O
operations were expended while verifying that columns in
the table were uniquely named.
In V4.1, this checking is performed against an internal
cache, which will improve performance of CREATE TABLE
(DEFINE RELATION), CREATE VIEW (DEFINE VIEW), and IMPORT
statements. The larger the number of columns in the table,
the greater the I/O operation savings.
Impact: The internal cache requires virtual memory, so if
the table or view being created has many fields this will
be reflected in higher page fault rates, and may require
larger working set (WSQUO), page file quota (PGFLQUO), and
virtual page count (VIRTUALPAGECNT).
New and Changed Features 1-53
1.16.2 New On-Disk Format for Segmented Strings (List of Byte
Varying)
Rdb/VMS V4.1 now supports two on-disk formats for segmented
strings. The new format is particularly important for
storage of large objects on write-once, read-many (WORM)
devices. See Section 1.15 for more information. In
addition, this new format will be used as the basis for
future enhancements in the area of segmented strings.
The programming interface to segmented strings has not
changed and application behavior will not change regardless
of the on-disk format.
The following sections describe the new and old segmented
string formats. See also Chapter 12 of the VAX Rdb/VMS
Guide to Database Maintenance for a description of these
two segmented string formats.
Chained Format
The original format for segmented strings (introduced
in Rdb/VMS V1.0) was a chained list of segments in this
chained format. The first segment contains a pointer to the
second segment, the second segment contains a pointer to
the third, and so forth. The final segment contains a null
pointer. The first segment contains extra information that
includes:
o The number of segments in the chain
o The length of the largest segment
o The total number of data bytes in the entire segmented
string
This information is returned in the SQLCA structure when
SQL is used to open a list cursor. The structure of the old
segmented string format is as follows:
1-54 New and Changed Features
+------------+--------------+-------------------------+
| statistics | next segment | data | (Primary Segment)
+------------+--------------+-------------------------+
|
v
+--------------+--------------------------+
| next segment | data | (Secondary Segments)
+--------------+--------------------------+
|
v
+--------------+-----------------+
| next segment | data |
+--------------+-----------------+
|
v
+--------------+--------------------------+
| next segment | data |
+--------------+--------------------------+
|
X (null pointer)
This chain is normally written in reverse order so that
the on-disk address of the segment can be placed in
the preceding segment. Therefore, segmented strings are
buffered to allow this reverse writing of the chain. The
buffer size is controlled by the logical name RDMS$BIND_
SEGMENTED_STRING_BUFFER. If a large number of segments are
written, then the buffer may be flushed periodically; this
will require a later update to those segments that were
written to disk before the on-disk address of the adjacent
segment was known.
WORM devices by nature are not updateable (that is, the
information can be written only once to the same location
on-disk). The WORM devices supported by Rdb/VMS are
revisable, but revision is at the expense of leaving a
disk block unusable. If updates are required, the device
driver revectors the disk block to a new location and
writes the revised data to the new location. This behavior,
if not managed correctly, results in fewer and fewer disk
blocks over time. The WORM support added to Rdb/VMS V4.1
is sensitive to this issue. Because the chained (old style)
segmented strings inherently require updating, they do not
lend themselves to WORM devices. For instance, the initial
New and Changed Features 1-55
segment contains information that is available only after
all segments are written to disk.
Indexed Format
The new on-disk format was designed to alleviate this
revision problem. Data segments no longer contain a pointer
to the next segment. Instead the pointers are maintained in
special segments called pointer segments. A pointer segment
contains only pointers to data segments. The structure of
the new indexed segmented string format is as follows.
+------------+---------------+-----------+-----------+-----+
| statistics | next psegment | pointer 1 | pointer 2 | ... | (Pointer Segment)
+------------+---------------+-----------+-----------+-----+
| | |
X | |
v |
+--------------------------+ | }
| data | | }
+--------------------------+ v } (Data
+--------------------+ } Segments)
| data | }
+--------------------+ }
The pointer segments allow Rdb/VMS to write the data
segments to WORM devices without the need to revise them
at some later stage due to a buffer overflow. A pointer
segment is sized according to the free space on a page
of the storage area. If there is no more free space on
the page to store a data segment, the pointer is chained
with a new pointer segment. This new format, combined with
buffering and large page sizes, virtually eliminates the
need to revise pages on WORM devices.
The new indexed format is the default for all segmented
strings created by Rdb/VMS. Features added in future
versions of Rdb/VMS will take advantage of this new
structure but will work with both segmented strings
formats.
1-56 New and Changed Features
Notes
Rdb/VMS system metadata still uses the old chained format.
This is reasonable because most system metadata segmented
strings are single segments and receive no advantage from
the indexed format.
To retain the old format (chained) segmented strings as
the default for all segmented strings, the logical name
RDMS$USE_OLD_SEGMENTED_STRING must be defined. When this
logical name is defined, it causes the application to write
the chained format segmented strings to all read/write
media. Note that if a write-once storage area is used, then
it will always use the new indexed style segmented strings.
The value for the logical name is not examined by Rdb/VMS.
$ DEFINE RDMS$USE_OLD_SEGMENTED_STRING YES
If you wish to use the new format (indexed) segmented
string later, then you must deassign this logical name.
$ DEASSIGN RDMS$USE_OLD_SEGMENTED_STRING
Mixing of old and new format segmented strings is supported
in Rdb/VMS. Language interfaces such as the SQL LIST CURSOR
and RDO START_SEGMENTED_STRING and FOR loop process either
format transparently.
1.16.3 Storing Lists Randomly Across Storage Areas
You can store lists (segmented strings) randomly across
several storage areas. In addition, you can specify that
some columns with the LIST data type are stored only in one
storage area, but that all other columns with the LIST data
type are stored randomly. For more information, see the VAX
Rdb/VMS Guide to Database Design and Definition.
1.16.4 Change in Utilization of SPAM Thresholds
Prior to V4.1 of Rdb/VMS, SPAM thresholds were utilized
only when a SPAM search was used to store a page within a
mixed storage area. In V4.1 this is changed. Records are
no longer stored on database pages once the third ("full")
threshold is met.
New and Changed Features 1-57
In V4.0, when Rdb/VMS stores records in mixed storage
areas, it attempted to store the record on a target
page. If this attempt failed, the pages from the clump
of database pages surrounding the target page were also
analyzed as candidates for record storage. A target
page is a page passed into the record storage subsystem
from Rdb/VMS. This can be either the last page used in
storing into this logical area or a page calculated from
a placement algorithm (PLACEMENT VIA INDEX clause in
a storage map statement). During this record storage
operation, the record would be stored on the database page
if there was enough free space on the page to accommodate
the record. If there was no space on the target page or
on neighboring pages in the buffer, a SPAM search would
be initiated. SPAM thresholds were then used to determine
candidate pages in the database file. Even after a data
page had reached a fullness of threshold 3 (full), records
could still be stored on the page.
V4.1 uses the same basic storage algorithm as previously
described for record storage. The one exception is that
when analyzing the target page and other pages in the
buffer clump for storage, Rdb/VMS calculates the current
SPAM threshold from known thresholds for this logical
area, the page size, and the free space on the page. Pages
are rejected as candidates for storage if this calculated
threshold is 3.
This change allows users to set SPAM thresholds and have
record storage reflect this setting. SPAM thresholds can
now be used to reserve a percentage of the page space.
1.16.5 Nontext Fields with Collating Sequences Are No Longer
Allowed
Prior to Rdb/VMS V4.1, nontext fields such as signed
word could be defined with collating sequences. This is
incorrect, as collating sequences do not make sense with
nontext fields. This combination has been found to produce
incorrect results with constraints.
For 4.1, the following changes have been made:
o Nontext fields cannot be defined with collating
sequences.
1-58 New and Changed Features
o The result of a CHANGE FIELD statement cannot be a
nontext field with a collating sequence. If a database
that used nontext fields with collating sequences is
converted to V4.1, then these fields cannot be changed
if the result is a nontext field with a collating
sequence.
o An import operation strips collating sequences
from nontext fields with collating sequences. An
informational message is also displayed, indicating
the field from which the collating sequence was dropped.
1.16.6 Default Lock Timeout Interval
Rdb/VMS Version 4.0 provides two ways to set the amount
of time a transaction waits for locks to be released:
the logical name RDM$BIND_LOCK_TIMEOUT_INTERVAL, and the
WAIT <interval> clause of the SET TRANSACTION or DECLARE
TRANSACTION statement. The interval specified by the WAIT
clause supersedes the interval specified by the logical
name.
Rdb/VMS Version 4.1 provides syntax to set a database-
wide default lock timeout interval, which functions as an
upper limit on the amount of time you can set with the WAIT
clause or the RDM$BIND_LOCK_TIMEOUT_INTERVAL logical name.
This database-wide lock timeout interval is set with the
LOCK TIMEOUT INTERVAL IS n SECONDS parameter to the SQL
CREATE or ALTER DATABASE statement, or the RDO DEFINE or
CHANGE DATABASE statement.
See the VAX Rdb/VMS SQL Reference Manual and the VAX
Rdb/VMS Guide to Distributed Transactions for more
information.
1.16.7 Batch Writes to Disk
The batch-write concept combines marked buffers in the
local buffer pool of a process that need to be written to
disk and flushes them to disk asynchronously in a batch.
So, instead of stalling for each write operation, Rdb/VMS
stalls only once (although for a longer time) for the whole
batch of write operations.
New and Changed Features 1-59
In versions previous to V4.1, Rdb/VMS always performed
asychronous write operations in a batch mode at commit
time when all the marked buffers were flushed out. However,
synchronous write operations were performed when a buffer
was flushed out due to buffer replacement. Potentially this
could result in a synchronous write operation for every
read operation from disk.
Beginning with V4.1, asychronous write operations are
performed when a buffer is flushed out due to buffer
replacement, as well as when a checkpoint occurs. If
the checkpoint interval is tuned or if it is too short,
batch writes will be triggered only by checkpoint. If the
checkpoint interval is too long, however, it is possible
that one synchronous write will be done for every written
buffer, thus negating what the batch-write operation saves
on the average stall time per write. It may be possible to
dynamically figure out the checkpoint interval using batch
writes. That is, you could trigger checkpoints based on
batch writes, which would be an automatic way to determine
the checkpoint interval. This approach would also be
adaptable to the page fetch sequence and read versus write
characteristics of the application, unlike the current
methods of triggering checkpoints.
Performing asychronous write operations at commit time, at
a checkpoint, and when marked buffers are flushed out due
to buffer replacement, means that you could potentially
increase I/O operations. However, because these operations
are asychronous, performance should improve because of the
single stall ocurring for a whole batch of write operations
instead of the synchronous write operations.
In addition, assuming that a batch-write operation just
occurred because a buffer was marked, all the buffers
in the user's local buffer pool are unmarked. The next
asychronous batch-write operation would occur only when the
next buffer to be updated reaches the end of the queue.
At the time this buffer is updated, it is put at the
head of the least recently used (LRU) queue. This buffer
needs to migrate to the end of that queue and be selected
as a victim buffer for a batch-write operation to occur
again. Hence, batch-write operations should occur very
infrequently compared to versions previous to V4.1, in
which Rdb/VMS always did a synchronous I/O operation each
1-60 New and Changed Features
time there was a marked victim buffer and a buffer was
flushed due to buffer replacement.
If, for example, 20 marked buffers are flushed one at a
time, then you must wait for a total of 660 (20 * 33)
milliseconds. Using batch writes, all 20 writes may be
completed in approximately 150 milliseconds. This advantage
is due to record ordering optimizations performed at the
disk controller level that minimize disk head movement.
If the writes that comprise the batch go to different
disks, the parallelism of the physical disks can also be
exploited by batch writes. For example, if there are 10
writes to be done and the 10 writes go to 10 different
disks and if each is written one after another, you stall
10 times for 330 (10 * 33) milliseconds. However, if you do
batch writes, all writes happen in parallel and get done in
33 milliseconds. The advantage here is due to parallelism.
Note that batch-write operations benefit from both
parallelism among disks and disk controller optimizations.
1.16.8 Minimum Value for the SPAM Interval Is Changed from 256 to
216
The minimum value for the SPAM interval is changed from 256
to 216 pages. This change allows a user to define a 1-block
page size and 1-block buffer size.
1.16.9 Table Metadata Lock WAIT/NOWAIT Behavior Is Changed and a
Single User Request for a Freeze Lock Is Now Queued
The behavior of the table metadata locks is changed for
V4.1. In V3.1 and V4.0, table metadata locks were always
requested NOWAIT. Beginning with V4.1, table metadata locks
are requested in the WAIT mode specified as part of the
transaction.
Another change to V4.1 is that for V3.1 and V4.0 it was
possible for spurious "lock conflict on freeze" error
messages to be returned for SQL SET TRANSACTION NOWAIT
or RDO START TRANSACTION NOWAIT statement even when only
one user was attached to the database. An optimization
that was added to V3.1 meant that a single user would never
re-request a nowait lock for the freeze lock on the false
assumption that it would always be denied access. Beginning
with V4.1, the algorithm is changed so that a request for
New and Changed Features 1-61
the freeze lock for a single user is always queued, whether
or not a WAIT or NOWAIT clause is specified.
These changes reduce the number of "lock conflict
on freeze" errors generated by V3.1 and V4.0. See
Section 2.2.74 for more information.
1.16.10 Change in Operation of Index Deletion
Prior to Rdb/VMS V4.1, a SORTED index would be deleted by
erasing every node in the B-tree. This action is usually
an expensive I/O operation. Therefore, in V4.1, the action
of DROP INDEX (or DELETE INDEX) has been optimized in the
following cases:
o If a sorted index has a storage map (that is, a STORE
clause) then each index (or each of partition of each
index) will be allocated a unique logical area. If the
index is stored in storage areas with uniform page
format, Rdb/VMS can optimize the index deletion by
marking the logical area as deleted. In this way, the
space will be reused without having to scan the storage
area and remove each index node.
In versions of Rdb/VMS previous to V4.1, Rdb/VMS would
scan the uniform area and erase each index node. The
behavior in V4.1 will enable much faster index deletion
for this class of indexes.
o If a sorted index does not have a storage map (that is,
a STORE clause) then it will be mapped automatically
to the RDB$SYSTEM storage area. All indexes for a table
created in this way will share a single logical area.
Therefore, it is only the final index deletion that can
be optimized as previously described. For example, if
three indexes (EMP_INDEX1, EMP_INDEX2, and EMP_INDEX3)
are defined without storage maps for the EMPLOYEES
table, a DROP INDEX EMP_INDEX1 statement will erase
all index nodes for the EMP_INDEX1 index. DROP INDEX
EMP_INDEX2 statement will erase all index nodes for
the EMP_INDEX2 index. Finally, a DROP INDEX EMP_INDEX3
statement will simply mark the logical area as deleted.
1-62 New and Changed Features
In versions of Rdb/VMS previous to V4.1, Rdb/VMS would
perform this optimization only if all indexes for a
table defaulted to RDB$SYSTEM. If just one index was
created for a table that had a storage map, then a full
index scan would be used for each index deletion for the
table. This behavior is corrected for Rdb/VMS V4.1.
________________________ Note ________________________
This sharing of logical areas is maintained for
compatibility with older versions of Rdb/VMS in which
single file databases did not have storage maps for
indexes. Digital recommends that storage maps be used
with all indexes so that the delete behavior described
previously can be achieved for all sorted indexes in
uniform storage areas.
______________________________________________________
For any index stored in a mixed storage area, Rdb/VMS
must scan each area and delete the index nodes (sorted
indexes) or hash buckets (hashed indexes), as components of
these indexes are mixed with records from other tables and
indexes. Therefore, no optimization is possible in these
cases.
1.16.11 Buffers for Storage Areas Are No Longer Invalidated When
the Lock Mode Is Changed (from CR <=> CU)
In versions of Rdb/VMS previous to V4.1, if a read-only
transaction is started, Rdb/VMS locks all storage areas in
concurrent read (CR) mode. When this transaction commits,
Rdb/VMS carries this lock over to the next transaction.
If the next transaction is read/write, Rdb/VMS then locks
storage areas in concurrent update (CU) mode. Rdb/VMS does
this lock conversion as CR -> NULL -> CU. This transition
to NULL always required Rdb/VMS to invalidate the buffer
pool for the storage area because it gave up the storage
area's lock.
In Rdb/VMS V4.1, all storage area lock conversions from
CR <=> CU are done directly (that is, without demoting to
NULL). This means that the buffers for that storage area
are not invalidated when the lock mode changes (from CR
<=> CU). Any applications that intersperse read-only and
New and Changed Features 1-63
read/write transactions should see considerably reduced I/O
operations on the storage areas.
1.16.12 Change in Getting Event Flags from the
RDB$RDBSHR_EVENT_FLAGS Logical Name
The following changes are noted in getting event flags from
the RDB$RDBSHR_EVENT_FLAGS logical name.
First get the event flags from the RDB$RDBSHR_EVENT_FLAGS
logical name. The RDB$RDBSHR_EVENT_FLAGS logical name is in
the following format:
"<ef>,<ef>,<ef>...", where <ef> is the event flag.
Rdb/Dispatch will get the first three event flags in the
logical name. The following actions are followed:
1. The event flags are assigned in the following order:
o RDB$$GA_COMMON_EFN
o RDB$$GA_STALL_EFN
o RDB$$GA_REMOTE_EFN
2. If there is an error in translating the logical name,
the event flags will get their default values.
3. If there is an error in the event flag number or the
number is out of range, the event flag will get its
default value.
The following are the event flags and their default values:
EVENT FLAG DEFAULT VALUE
========== =============
RDB$$GA_COMMON_EFN 29
RDB$$GA_STALL_EFN 30
RDB$$GA_REMOTE_EFN 31
1.16.13 .RUJ Files Are No Longer Created with the UIC of the
Process That Created the File
For V4.0, .RUJ files were created with the UIC of the
process creating the .RUJ file. For versions previous to
V4.0, the .RUJ file inherited the UIC of the directory
in which it was created. The V4.1 behavior is changed to
the way it was for versions previous to V4.0. This change
will alleviate the problem of having to grant disk quotas
1-64 New and Changed Features
to users who otherwise would not need them. For example,
users using CDD/Plus dictionaries with Rdb/VMS V4.0 could
not create .RUJ files without the intervention of a system
manager to grant them disk quota.
1.16.14 Changes to RDB$FIELDS and RDB$FIELD_VERSIONS System
Relations
It is possible in Rdb/VMS V4.1 that the RDB$EDIT_STRING
and RDB$QUERY_NAME fields in the RDB$FIELDS and RDB$FIELD_
VERSIONS system relations are NULL. In previous versions
of Rdb/VMS, these fields were always present and set to
spaces, or had the length of the varying string set to
zero.
In V4.1 these fields, if not used, are set to NULL.
Therefore, SQL queries on the RDB$FIELDS and RDB$FIELD_
VERSIONS system relations should now provide an indicator
variable, even though this may not have been necessary in
previous versions. Failure to do this will result in the
error:
%SQL-F-NULLNOIND, Tried to store null into host variable with
no indicator variable
Queries from RDO, RDBPRE and RDML receive the default
missing value (spaces) in the host variable.
1.16.15 .AIJ File Is Now Initialized Upon Creation
The AIJ initialization algorithms have been modified, such
that the .AIJ file is initialized when the .AIJ file is
created. This means that the .AIJ file is no longer zero-
length, but is always set to its initial allocation size.
Functionally, this change does not affect the operation of
the database in any way. The concept of an empty .AIJ file
is still maintained, even though the .AIJ file is always
fully initialized.
New and Changed Features 1-65
1.16.16 VAXcluster Nodes up to 96 Are Now Supported
Rdb/VMS now allows the clause NUMBER OF VAXCLUSTER NODES
to specify up to 96 nodes. The value can be specified
in the SQL CREATE/ALTER DATABASE statements or the RDO
DEFINE/CHANGE DATABASE statements, the SQL or RDO IMPORT
statement, and the RMU/RESTORE command.
The V4.1 VAX Rdb/VMS Guide to Database Performance and
Tuning has not been updated with this change; all other
books in the V4.1 documentation set have been updated.
1.16.17 Change to SYS REQUEST Error Called from 113
Prior to V4.1, if there was an error starting a distributed
transaction, the following error was returned:
RDB-E-SYS_REQUEST_CAL, error from system services request, called from 113
followed by the system service secondary error.
Beginning with V4.1, the following error is now returned:
RDB-E-SYS_REQUEST_CAL, error from system services request, called from 113
RDB-E-DECDTMERR, DECdtm system service call error
followed by the system service secondary error.
1.16.18 Prototype Syntactic Forms of Vertical Record Partitioning
Are Not Possible in V4.1 Storage Map Statements
Previous versions of Rdb/VMS (V4.0) permitted certain
syntactic forms of storage map statements to be defined,
which resulted in an unsupported storage map. Use of these
unsupported syntactic forms of storage maps beginning with
V4.1 will return an error message of the form "corruption
in the query string". This change is not expected to cause
any inconvenience to users, who had requested documentation
for these specific syntactic forms of the storage map
statements because they were unsupported.
1.17 SQL: New and Changed Features and Statements for V4.1
SQL provides LSE template support for most of these new
features and statements in both interactive SQL and module
language. See the VAX Rdb/VMS SQL Reference Manual for more
information.
1-66 New and Changed Features
1.17.1 Multischema Databases
Although single-schema databases are still the default for
Rdb/VMS, you can now optionally group your data definitions
within multiple schemas, contained in catalogs within one
database. This enables you to use ANSI/ISO standard naming
conventions.
Use the CREATE DATABASE MULTISCHEMA IS ON clause to create
a new database with the multischema attribute, and the
CREATE CATALOG and CREATE SCHEMA statements to create
catalogs and schemas within the multischema database.
Use the SET CATALOG and SET SCHEMA statements to specify
the default catalog and schema for subsequent statements.
Use the DROP CATALOG and DROP SCHEMA statements to delete
catalogs, schemas, and (optionally) the elements they
contain.
Rdb/VMS automatically assigns unique names to definitions
in a multischema database so that you can access them
easily, using applications that do not support multischema
naming. However, if you prefer to assign your own names,
you can use the EXTERNAL NAME IS clause for each database
element CREATE statement.
Because one database may contain multiple schemas, the
SCHEMA keyword is obsolete syntax in some SQL statements
for Version 4.1. For example, the ALTER SCHEMA statement is
superseded by the ALTER DATABASE statement. A complete list
of obsolete syntax appears in the VAX Rdb/VMS SQL Reference
Manual.
If you use the SET CATALOG or SET SCHEMA statements, you do
not have to qualify the names of elements that are in the
current catalog and schema, but you must qualify names of
elements that are contained in other catalogs and schemas.
When you use the MULTISCHEMA IS ON clause, SQL creates
the default catalog, RDB$CATALOG, and the default schema,
RDB$SCHEMA. The default catalog contains the default
schema; the default schema contains elements such as the
system relations and system indexes.
New and Changed Features 1-67
See the VAX Rdb/VMS Guide to Database Design and Definition
and the multischema sample database named CORPORATE_DATA
(see Section 1.17.2) for an example of how to create a
multischema database.
1.17.2 Sample Multischema Database
You can create the sample multischema database,
CORPORATE_DATA, by using the command procedure
SQL$SAMPLES:PERSONNEL.COM. Refer to the file
SQL$SAMPLE:ABOUT_SAMPLE_DATABASES.TXT on the kit for
specific information about the sample database and how
to build it. See the VAX Rdb/VMS Guide to Database Design
and Definition for more information about designing and
defining a multischema database.
1.17.3 Authorization Identifiers
Prior to Version 4.1, SQL documentation and syntax used
the term authorization-identifier or auth-id to mean an
arbitrary name assigned to a database object by a user who
referenced it. (RDO documentation referred to such a name
as a database handle.) In Version 4.1, the arbitrary name
assigned by a user to a database object is known as an
alias.
In Version 4.1, the SQL DECLARE SCHEMA statement, which
specified the authorization identifier in prior versions,
is deprecated syntax. Note that a deprecated feature is a
feature that is currently allowed but will not be allowed
in future versions of SQL; that is, it will be obsolete.
The SQL statement DECLARE ALIAS replaces the DECLARE SCHEMA
statement in SQL module language DECLARE sections, in
precompiler context files, and in SQL statements embedded
in host language programs to be precompiled. The SQL
statement ATTACH replaces the DECLARE SCHEMA statement in
interactive and dynamic SQL, and the SQL statement CONNECT
replaces the DECLARE SCHEMA RUNTIME FILENAME statement.
In Version 4.1, an authorization identifier for a database
object corresponds to the user name of the creator for
that object. When the user specifies the multischema
option for a database, SQL bases privilege checking on
the authorization identifier of the default schema.
1-68 New and Changed Features
You can specify the /ANSI_AUTHORIZATION option on the
module language command line or /SQLOPTIONS=ANSI_
AUTHORIZATION on the precompiler command line if you wish
to specify that a module can be executed only by the person
who compiled it.
1.17.4 Multischema Naming
When you use an alias to qualify the name of a catalog,
schema, or object in a multischema database, you must
separate subordinate names from the alias and from each
other with a period (.) after each name and use double
(") quotation marks to delimit the leftmost name pair.
SQL does not allow double quotation marks unless you first
issue a SET ANSI QUOTING ON statement. In the following
example, CORPORATE is an alias for a database that contains
the catalog MARKETING, the schema DEPT1, and the domain
LAST_NAME.
SQL> SET ANSI QUOTING ON;
SQL> SHOW DOMAIN "CORPORATE.MARKETING".DEPT1.LAST_NAME;
SQL> ! or
SQL> SHOW DOMAIN "CORPORATE.LAST_NAME":
You can also use the /ANSI_QUOTING argument to the module
language command line or the /SQLOPTIONS=(ANSI_QUOTING)
on the precompiler command line to allow double quotation
marks. By default, SQL uses multischema naming when you
attach to a multischema database. In the ATTACH statement
or the DECLARE ALIAS statement, the MULTISCHEMA IS OFF
clause lets you attach to a multischema database without
using multischema naming.
1.17.5 Run-Time SQL Kit
The Rdb/VMS run-time only (RTO) software is designed
to let users run existing executable DML programs that
access existing Rdb/VMS databases. The Version 4.1 RTO
kit provides interactive SQL access to such databases. The
VAX Rdb/VMS SQL Reference Manual lists the SQL statements
available on the run-time only kit.
New and Changed Features 1-69
1.17.6 Reserved Words as Identifiers
The interactive SQL SET ANSI IDENTIFIERS statement, the
/ANSI_IDENTIFIERS option on the module language command
line, and the /SQLOPTIONS=(ANSI_IDENTIFIERS) option on the
precompiler command line cause SQL to check subsequent
statements for reserved words from the ANSI/ISO 1989
Standard and the ANSI/ISO SQL2 Draft Standard. After you
enable reserved word checking, SQL issues an informational
message if it encounters reserved words used as identifiers
in subsequent statements, unless you delimit such words
with double quotation marks (").
You must use a SET ANSI QUOTING ON statement before using
double quotation marks in interactive or dynamic SQL. You
can use /ANSI_QUOTING on the module language command line
or /SQLOPTIONS=(ANSI_QUOTING) on the precompiler command
line to allow double quotation marks.
For example, if you do not have ANSI_IDENTIFIERS turned
on, you receive a deprecated feature message on the CREATE
TABLE statement as follows:
CREATE TABLE A (DATE INTEGER);
%SQL-I-DEPR_FEATURE, Deprecated Feature: Keyword date used as an identifier
If you select columns that have names that are keywords
such as DATE, TIME, INTERVAL, TIME, TIMESTAMP and so forth
and you do not have ANSI_IDENTIFIERS turned on, the CREATE
TABLE statement fails and you receive a message such as
follows:
SELECT DATE FROM A;
^
%SQL-W-LOOK_FOR_STT, Syntax error, looking for:
%SQL-W-LOOK_FOR_CON, VMS, ANSI, string-literal,
%SQL-F-LOOK_FOR_FIN, found FROM instead
The problem is that the parser thinks that you are trying
to select a date literal (in this case) or a time,
timestamp or interval literal (in other, easily testable
cases).
1-70 New and Changed Features
There are two workarounds to this problem:
o If you are using ANSI_QUOTING, that is, double quotes
may be used to delimit an identifier, the following will
work:
SELECT "DATE" FROM A;
o The column name may be qualified by the table name, so
the following will also work:
SELECT A.DATE FROM A;
1.17.7 Nested View Updates with CHECK OPTION
SQL does not allow updates to nested views when the update
violates the WITH CHECK OPTION constraint of any of the
nested views. See Section 2.2.70 for more information.
1.17.8 RESTRICT Argument Now Implicit for DROP TABLE Statement
In Version 4.1, the default behavior of the DROP TABLE
statement is a restricted delete, not a cascaded delete.
If you do not specify CASCADE, you must delete all items
(views, constraints, indexes, or triggers) that refer to
the specified table before you can delete that table.
1.17.9 SINGLE Predicate Replaces UNIQUE Predicate
The predicate that tests whether the result table contains
exactly one row, called UNIQUE in previous versions of SQL,
is now called SINGLE. If you specify the UNIQUE predicate,
SQL issues a deprecated feature message.
1.17.10 SQLDA2 Structure
SQL provides an extended version of the SQLDA, called
the SQLDA2, which supports additional fields and field
sizes. You can use either the SQLDA or the SQLDA2 in any
dynamic SQL statement that calls for a descriptor area. You
should use the SQLDA2 when the data type of the column is
DATE, DATE VMS, DATE ANSI, TIME, TIMESTAMP, or any of the
INTERVAL data types.
New and Changed Features 1-71
1.17.11 ORDER BY and LIMIT TO Clauses Are Allowed in Subqueries
Prior to Version 4.1, the ORDER BY and LIMIT TO clauses
were allowed only in cursors, views, and interactive
SELECT statements. You can now use the ORDER BY and LIMIT
TO clauses in query expressions, which are the form of a
select expression referred to in predicates.
1.17.12 Constraint Keyword Changes
The SQL SET ALL CONSTRAINTS statement keywords ON and OFF
have changed to IMMEDIATE and DEFERRED to comply with the
ANSI/ISO SQL2 Draft Standard. The ON and OFF keywords are
allowed, but their use is not standard. The new keywords
also apply to the precompiler SQLOPTIONS=(CONSTRAINTS=ON
/OFF) and the module language /CONSTRAINTS=ON/OFF
qualifiers.
1.17.13 Connections
Connections allow you to control when applications
attach and detach from an Rdb/VMS database, permit SQL
access to request handles for migration of RDBPRE or
RDML applications to SQL, and provides a global database
context, called an database environment, letting you write
an SQL module for concurrent access to one or more Rdb/VMS
databases.
The ATTACH and DECLARE ALIAS statements add databases
to the default database environment for subsequent SQL
statements. The CONNECT and DISCONNECT statements let you
add databases to or remove databases from the default
environment. (The FINISH statement is superseded by
DISCONNECT DEFAULT.) The SET CONNECTION statement lets you
select a new default connection from any active connections
that you have specified.
Use the /CONNECT option on the SQL module language
processor command line or the /SQLOPTION=(CONNECT) clause
on the precompiler command line to provide a global
database context for modules in your application.
1-72 New and Changed Features
1.17.14 Extended Dynamic Cursors
In Version 4.1, you can supply a string name as the
extended dynamic statement identifier as an alternative
to the integer statement handle currently returned. You
can specify the statement name as the parameter in the
following statements:
o SQL PREPARE statement
o SQL DESCRIBE statement
o SQL RELEASE statement
o SQL Extended Dynamic DECLARE CURSOR statement.
o SQL EXECUTE statement
1.17.15 Optional Colons in Module Language Parameters
SQL now allows optional use of colons as prefixes to module
language parameters, as required by the ANSI/ISO SQL2 Draft
Standard for module language processors.
The SQL module language processor switch /WARN=DEPRECATED
causes use of parameters not prefixed with colons to be
flagged with an informational message.
A new qualifier has been added to the SQL module language
processor command line. The /ANSI_PARAMETERS qualifier
specifies that all declarations of references to parameter
names must begin with a colon (:). The /NOANSI_PARAMETERS
qualifier (the default in Rdb/VMS V4.1), specifies that no
parameter name can begin with a colon. Violation of one of
these rules results in a compile-time error.
The defaulting of the /ANSI_PARAMETERS flag to /NOANSI_
PARAMETERS is immediately deprecated. In the future, /ANSI_
PARAMETERS will be the default because it allows processing
of ANSI standard modules.
1.17.16 Error Literals
SQL now provides symbolic literals for the numeric error
values (for example, SQLCODE$DEADLOCK for -913) returned
in the SQLCODE structure or numeric data type values
(for example, SQLDA$_CHAR for 453) returned in the SQLDA
structure. Rdb/VMS supplies symbolic literals in an include
file for each major computer language. Because you will not
New and Changed Features 1-73
have to hardcode error values in programs, error processing
and detection will be easier.
The files are included on the kit and available as two sets
of files in SYS$LIBRARY. The literals constants are now
compile-time constants instead of link-time constants. The
file contents are the only thing that have changed. The
file names are:
SQL_LITERALS.ADA
SQL_LITERALS.BAS
SQL_LITERALS.FOR
SQL_LITERALS.H
SQL_LITERALS.LIB
SQL_LITERALS.PAS
SQL_LITERALS.PLI
The other files contain the SQLCA strucure that can be
included with the host language program when using SQL
module language. The files are named as follows:
SQL_SQLCA.ADA
SQL_SQLCA.BAS
SQL_SQLCA.FOR
SQL_SQLCA.H
SQL_SQLCA.LIB
SQL_SQLCA.PAS
SQL_SQLCA.PLI
The files are included in the same way as in versions of
Rdb/VMS previous to V4.1. For example, the statement to
include a file from a COBOL program is as follows:
COPY 'SYS$LIBRARY:SQL_LITERALS'
1.17.17 Date-Time Data Types and Date Arithmetic
SQL provides new date-time data types that allow you to
execute more complex queries that reference dates, times,
and intervals.
o DATE-evaluated as DATE VMS by default
o DATE ANSI
o DATE VMS
o TIME
o TIMESTAMP
1-74 New and Changed Features
o INTERVAL-has thirteen subtypes
You can use date-time and interval variables and constants
in arithmetic expressions.
RDO provides only minimal support for the date-time data
types. You can use RDO IMPORT and EXPORT to process SQL-
generated databases containing instances of these data
types and display them with RDO PRINT and SHOW commands.
________________________ Note ________________________
These new date-time data types are not supported by
CDD/Plus V4.3. See Section 3.9.3 for more information.
______________________________________________________
The date-time data types work as follows with respect to
the SET ANSI statements with the DATE ON and DATE OFF
arguments using the CAST function for converting column
data to the DATE data type. When the SET ANSI statement is
specified with the DATE ON argument and the CAST function
is specified as DATE, it is assumed to mean DATE ANSI-
style (year, month, date). When the SET ANSI statement is
specified with the DATE OFF argument and the CAST function
is again specified as DATE, the VMS-style date results.
In this case, the VMS-style date cannot participate in a
date-time expression, and SQL returns the following error
message:
SQL> SET ANSI DATE OFF
SQL> SELECT CAST(JOB_START AS DATE),
cont> (CURRENT_DATE - CAST(JOB_START AS DATE)) DAYS(5)
cont> FROM JOB_HISTORY LIMIT TO 10 ROWS;
%SQL-F-UNSDATXPR, Unsupported date expression
-SQL-F-DATESUBILL, Operands of date/time subtraction are incorrect
1.17.18 Date-Time Functions
SQL provides CAST, CURRENT_DATE, CURRENT_TIME, CURRENT_
TIMESTAMP, and EXTRACT functions to let you extract date-
time fields, convert text into date and time fields, and
fetch the current system values for date and time. See the
VAX Rdb/VMS Guide to Using SQL for more information.
New and Changed Features 1-75
1.17.19 QUADWORD Is Changed to BIGINT
In Version 4.1, the QUADWORD data type is changed to
BIGINT. Although BIGINT is the preferred usage, QUADWORD
is still accepted.
1.17.20 C Language and CDD Support for CHAR Data Types
SQL module language and the SQL precompiler feature
qualifiers to support new interpretations of CHAR data
type fields extracted from data dictionary definitions and
used in C programs. For syntax, see the VAX Rdb/VMS SQL
Reference Manual.
1.17.21 ALTER DOMAIN Now Allows DATE to Be Converted to VARCHAR
In previous versions of Rdb/VMS it was possible to alter
a domain (and related columns) from the DATE data type to
CHAR, but not to VARCHAR. This restriction has been lifted
for V4.1.
The new SQL date-time types DATE, TIME, TIMESTAMP, and
INTERVAL can also be altered to type VARCHAR in Rdb/VMS
V4.1.
The RDO data type DATE can now also be changed to the
VARYING STRING data type.
1.17.22 Segmented String Storage on Write-Once Read-Many (WORM)
Devices
You can now create storage areas for segmented string and
list data on write-once read-many (WORM) optical disks.
Specify the WRITE ONCE storage area parameter in the CREATE
STORAGE AREA, CREATE DATABASE, ALTER DATABASE, and ALTER
STORAGE AREA statement.
1.17.23 Logical Area Thresholds for Uniform Areas
You can specify a different threshold for each logical
area that is created by a storage map or index using the
THRESHOLDS ARE clause of the CREATE STORAGE MAP or CREATE
INDEX statements. If fewer than three threshold values
are supplied, Rdb/VMS adds 100 for the higher missing
thresholds, as the following example shows.
THRESHOLDS ARE (40) is identical to THRESHOLDS ARE (40,100,100)dK THRESHOLDS ARE (40,70) is identical to THRESHOLDS ARE (40,70,100) ! 1-76 New and Changed Features"
P T ? 1.17.24 Interactive SET EXECUTE and SHOW EXECUTION MODEO Statements B You can use the SQL SET EXECUTE statement to controlD whether SQL executes interactive commands as you issueI them or merely returns diagnostic or optimization messages. E The SQL SHOW EXECUTION MODE statement shows the currentg% default execution mode.u H 1.17.25 Interactive SET WARNING and SHOW WARNING MODE Statements I The SQL SET WARNING DEPRECATE statement displays diagnostictE messages when you issue a statement containing obsoletegB syntax in interactive SQL. The SQL SHOW WARNING MODE? statement shows the current default warning mode.e H 1.17.26 Interactive SET FLAGGER and SHOW FLAGGER MODE Statements B The SQL SET FLAGGER statement displays informationalA messages (FIPS flagging) when you issue a statementMB containing nonstandard syntax (Digital extensions toE the ANSI/ISO standard syntax) in interactive SQL. These I messages are disabled by default. The SQL SHOW FLAGGER MODE6? statement shows the current default flagger mode.E # 1.17.27 SET ALIAS Statement E For Rdb/VMS Version 4.1, the SQL SET ALIAS statement is * documented only in SQL Help. I The SET ALIAS statement specifies the default alias used byeH SQL when you do not qualify a database object name with an alias. 2 You can use the SET ALIAS statement: # o In interactive SQLn E o Embedded in host language programs to be precompiled 8 o As part of a procedure in an SQL module I o In dynamic SQL as a statement to be dynamically executed D You can use any of the following to specify the alias: B o A character string literal consisting of an alias3 enclosed in single quotation marksn H o A host language variable in precompiled SQL or a formal1 parameter that contains an alias I New and Changed Features 1-77
e C o A parameter marker (?) in a dynamic SQL statement. ThesA alias parameter marker must specify a parameter thatD contains an alias.K B The following example shows how you can avoid qualifying2 object names by setting a default alias. ; SQL> ATTACH 'ALIAS CORP FILENAME CORPORATE_DATA'; / SQL> SELECT LAST_NAME FROM EMPLOYEES;h6 %SQL-F-NODEFDB, There is no default databaseS SQL> ! You must qualify the table name because you attached with an alias 4 SQL> SELECT LAST_NAME FROM CORP.EMPLOYEES; LAST_NAME AmesL Andriola Babbin-typebox ( ) .typebox ( ) .typebox ( ) . typebox ( ) typebox (S)typebox (Q)typebox (L)> typebox (S)typebox (E)typebox (T) typebox (A)typebox (L)typebox (I)typebox (A)typebox (S) typebox (')typebox (C)typebox (O)typebox (R)typebox (P)typebox (')typebox (;)typebox (H)typebox ( ) typebox (S)typebox (Q)typebox (L)> typebox (!) typebox (N)typebox (o)┬ typebox (y)typebox (o)┤ typebox (d)typebox (o) ┼typebox (o)├ ┼typebox (e)typebox (e)typebox (d) ├typebox (o) ─┤typebox (a)┌typebox (i)typebox (f)typebox (y) ├typebox (h)typebox (e) ├typebox (a)typebox (b)┌typebox (e) ┼typebox (a)└typebox (e) typebox (E)typebox (M)typebox (P)typebox (L)typebox (O)typebox (Y)typebox (E)typebox (E)typebox (S)4typebox ( ) typebox (S)typebox (Q)typebox (L)> typebox (S)typebox (Q)typebox (L)> typebox (S)typebox (E)typebox (L)typebox (E)typebox (C)typebox (T) typebox (L)typebox (A)typebox (S)typebox (T)typebox (N)typebox (A)typebox (M)typebox (E) Ftypebox (R)typebox (O)typebox (M) typebox (E)typebox (M)typebox (P)typebox (L)typebox (O)typebox (Y)typebox (E)typebox (E)typebox (S)typebox (;)typebox ()typebox ( ) typebox (L)typebox (A)typebox (S)typebox (T)typebox (N)typebox (A)typebox (M)typebox (E)