Order Number: AA-GQ93H-TE
VAX Rdb/VMS
Release Notes
December 1990
This document contains the Release Notes for VAX Rdb/VMS Version
4.0. It describes new and changed features; software errors fixed;
and problems, restrictions, and other information relating to
Version 4.0.
Revision/Update Information This manual is a revision and super-
sedes previous versions.
Operating System: VMS
Software Version: VAX Rdb/VMS Version 4.0
Digital Equipment Corporation
__________
Copyright ©1984, 1985, 1986, 1987, 1988, 1989, 1990
The following are trademarks of Digital Equipment Corporation:
ALL-IN-1, CDD/Plus, DEC, DEC/CMS, DECdecision, DECdtm, DECforms,
DECintact, DEC/MMS, DECnet, DECtp, DECtrace, DECwindows, MicroVAX,
ULTRIX, UNIBUS, VAX, VAX ACMS, VAX Ada, VAX BASIC, VAX C, VAX CDD,
VAXcluster, VAX COBOL, VAX DATATRIEVE, VAX DBMS, VAXELN, VAX FMS,
VAX FORTRAN, VAX Pascal, VAX RALLY, VAX Rdb/ELN, VAX Rdb/VMS, VAX
RMS, VAX SCAN, VAX SPM, VAXstation, VAX TEAMDATA, VIDA, VMS, VT,
and the DIGITAL Logo.
IBM and OS/2 are registered trademarks of International Business
Machines Corporation. Macintosh is a trademark of Apple Computer,
Inc. MS-DOS is a registered trademark of Microsoft Corporation.
This document is available in printed and online versions.
This document was prepared using VAX DOCUMENT, Version 1.2
Contents
PREFACE xvii
CHAPTER 1 NEW AND CHANGED FEATURES 1-1
1.1 TWO-PHASE COMMIT PROTOCOL 1-1
1.1.1 Using SQL with Distributed Transactions 1-3
1.1.2 Using RDBPRE with Distributed Transactions 1-4
1.1.3 Using RDML with Distributed Transactions 1-5
1.1.4 RMU Enhancements 1-7
1.1.5 DISTRIBTRAN Privilege 1-8
1.2 NEW SECURITY FEATURES 1-8
1.3 DYNAMIC OPTIMIZATION 1-12
1.4 OTHER OPTIMIZER ENHANCEMENTS 1-19
1.4.1 More Efficient Strategies for Queries Whose
WHERE Expression Has OR Booleans 1-19
1.4.2 Improved Performance for Tables Stored in
Mixed Format Storage Areas 1-20
1.4.3 Optimization and Aggregates 1-20
1.4.4 Constraint Evaluation 1-22
1.5 JOURNALING OF METADATA UPDATES 1-23
1.6 MULTIPLE SEGMENTED STRING STORAGE AREAS 1-24
1.7 LOCK TIMEOUT MECHANISM 1-26
1.8 COMPRESSED INDEXES 1-26
1.9 NEW DATA TYPE-SQL TINYINT AND RDO SIGNED BYTE 1-31
1.10 EXPORT NOW SUPPORTS THE DATA OPTION 1-32
1.11 IMPORT NOW SUPPORTS DATA AND TRACE OPTIONS 1-33
1.12 RDB/VMS V4.0 GIVES SPECIAL TREATMENT FOR CDD/PLUS
DICTIONARIES 1-35
iii
Contents
1.13 SHOW STORAGE AREA SHOWS A DIFFERENT STORAGE AREA
ALLOCATION 1-37
1.14 SETTING LINE LENGTHS FOR A FILE OR OUTPUT DEVICE 1-38
1.15 CHANGES TO RUN-UNIT JOURNAL (RUJ) FILES 1-38
1.16 CHANGE TO BATCH-UPDATE AND EXCLUSIVE TRANSACTION
BEHAVIOR 1-40
1.17 DECTRACE SUPPORT FOR TRIGGER AND CONSTRAINT
STATISTICS 1-40
1.18 SQL: NEW AND CHANGED FEATURES AND STATEMENTS 1-40
1.18.1 Support for the Two-Phase Commit Protocol 1-40
1.18.2 New SQL Syntax 1-41
1.18.3 String Concatenation Operator 1-45
1.18.4 Substring Manipulation 1-45
1.18.5 SQL Supports Segmented Strings 1-46
1.18.6 Computed Columns in Tables 1-47
1.18.7 SQL Changes Values Used in SQLERRD Array 1-48
1.18.8 Module Language Record Support 1-49
1.18.9 SQL C Precompiler Supports VARCHAR Host
Variables 1-51
1.18.10 SQL Statements Have Been Updated to Support
VAX Data Distributor V2.2 1-52
1.18.11 Diagnostic Messages for Obsolete Features 1-52
1.18.12 Sample Programs in Precompiled SQL and SQL
Module Language 1-52
1.19 RDO: NEW AND CHANGED FEATURES AND STATEMENTS 1-53
1.19.1 Support for the Two-Phase Commit Protocol 1-54
1.19.2 RDO Changes for CDD/Plus Compatibility 1-54
1.19.3 RDO SHOW DATABASE Statement Displays the Node
Name for Remote Databases 1-54
1.20 RDBPRE: NEW AND CHANGED FEATURES AND STATEMENTS 1-55
1.20.1 Support for the Two-Phase Commit Protocol 1-55
1.20.2 RDBPRE/MESSAGE_MAP Works for BASIC 1-55
1.21 RDML: NEW AND CHANGED FEATURES AND STATEMENTS 1-55
1.21.1 Support for the Two-Phase Commit Protocol 1-55
iv
Contents
1.21.2 Support for Internationalization 1-56
1.22 RMU: NEW AND CHANGED FEATURES AND STATEMENTS 1-56
1.22.1 RMU/ALTER Command 1-57
1.22.2 RMU/BACKUP Command 1-57
1.22.3 RMU/CLOSE/PATH Command 1-60
1.22.4 RMU/CONVERT Command 1-60
1.22.5 RMU/COPY_DATABASE Command 1-64
1.22.6 RMU/DUMP/AFTER_JOURNAL Command 1-65
1.22.7 RMU/DUMP/AFTER_JOURNAL/STATE=PREPARED 1-65
1.22.8 RMU/DUMP/BACKUP_FILE/LABEL=(label-name-list)
Command 1-65
1.22.9 RMU/DUMP/USERS/STATE=BLOCKED Command 1-65
1.22.10 RMU/MOVE_AREA Command 1-65
1.22.11 RMU/OPEN/PATH Command 1-66
1.22.12 RMU/RECOVER/RESOLVE Command 1-67
1.22.13 RMU/REPAIR Command 1-67
1.22.14 RMU/RESOLVE Command 1-67
1.22.15 RMU/RESTORE/LABEL=(label-name-list) Command 1-67
1.22.16 Security Auditing 1-68
1.22.17 RMU/SHOW STATISTICS Command 1-68
1.23 SQL/SERVICES: NEW AND CHANGED FEATURES 1-69
1.23.1 Process Pooling 1-70
1.23.2 API Support 1-70
1.23.3 API Routines 1-70
1.23.4 Server Access 1-71
1.23.5 List Cursors 1-71
1.23.6 SQL/Services VMS API Linkable Libraries 1-72
1.23.7 Two New Installation Questions 1-72
1.23.8 SQL/Services Help Available from DCL HELP
Facility 1-72
1.24 CHANGES RELATED TO THE SAMPLE PERSONNEL DATABASE 1-73
1.25 NEW AND CHANGED RDB/VMS LOGICAL NAMES 1-74
1.25.1 Disabling the Two-Phase Commit Protocol with
the New SQL$DISABLE_CONTEXT Logical Name 1-74
1.25.2 Lock Timeout Mechanism Using a New
RDM$BIND_LOCK_TIMEOUT_INTERVAL Logical Name 1-74
v
Contents
1.25.3 Restricting the Creation of Databases Using
the New RDBVMS$CREATE_DB Logical Name 1-75
1.25.4 Remote Access to Rdb/VMS Using a New Logical
Name RDB$REMOTE_BUFFER_SIZE 1-75
1.25.5 Changes to the RDM$BIND_BUFFERS Logical Name 1-76
1.25.6 Changes to the
RDMS$BIND_SEGMENTED_STRING_BUFFER
Logical Name 1-76
1.25.7 RDMS$AUTO_READY Logical Name Is No Longer
Used 1-77
1.26 OBSOLETE STATEMENTS AND FEATURES 1-78
1.26.1 SQL Obsolete Features 1-78
1.26.2 RDO Obsolete Statements and Features 1-81
1.26.3 SQL/Services Obsolete Features 1-82
1.27 SUMMARY OF DOCUMENTATION ADDITIONS AND CHANGES 1-84
CHAPTER 2 SOFTWARE ERRORS FIXED 2-1
2.1 SOFTWARE ERRORS FIXED THAT APPLY TO ALL INTERFACES 2-1
2.1.1 EXPORT Statement Did Not Save the Maximum
Storage Area Allocation 2-1
2.1.2 During IMPORT, Storage Areas Were Always
Created Using the Default Page Size of 2
Blocks 2-2
2.1.3 IMPORT Did Not Restore the RDB$SYSTEM Storage
Area Correctly 2-2
2.1.4 Rdb/VMS V4.0 Requires That Certain Constraints
Be Redefined 2-4
2.1.5 Constraints Were Not Always Executed As
Required 2-6
2.1.6 Too Many Constraints Could Be Selected or
Executed 2-6
2.1.7 Constraints Could Be Executed Too Many Times 2-7
2.1.8 Indexes or Constraints Could Be Ignored by
Trigger Actions 2-7
2.1.9 Indexes or Constraints Could Be Defined When
Illegal Due to Active Queries 2-7
vi
Contents
2.1.10 The Combination of Using a Trigger and
Disabling Compression Did Not Update the
Index 2-8
2.1.11 Triggers Can No Longer Delete Subject Table
Rows 2-8
2.1.12 VAX Data Distributor Replications and
Triggered Actions Were Not Performing at the
Right Time 2-8
2.1.13 DBADM (ADMINISTRATOR) Privilege Was Granted by
Rdb/VMS Default Protection 2-9
2.1.14 CHANGE FIELD (RDO) or ALTER DOMAIN (SQL) Could
Cause Query ACCVIOs 2-9
2.1.15 A Problem Existed with Disabled Compression
and the Store Clause of the SQL ALTER and RDO
CHANGE STORAGE MAP Statements 2-9
2.1.16 Defining a Database with a Buffer Size of 1
Caused a Bugcheck 2-10
2.1.17 View Optimization Has Been Restored in V4.0 2-10
2.1.18 RDB$DBKEY_LENGTH System Relation Field Was
Incorrect for Certain Views 2-11
2.1.19 Errors Retrying Failed Multidatabase
Transactions 2-12
2.1.20 Problem with Exclusive Locking on a Multifile
Database Produced a Bugcheck 2-13
2.1.21 Non-Fatal Bugcheck Resulted from Rdb/VMS
Overflowing the EXEC STACK 2-13
2.1.22 The RMUCONVERT.EXE File No Longer Exists 2-14
2.1.23 Remote Access to a V3.n Database and Starting
a Transaction Reserving Many Relations Caused
the Buffer to Be Exceeded and the Statement to
Fail 2-14
2.1.24 RDML and RDBPRE Applications Stored Segmented
Strings in the Default Storage Area Even When
a Storage Area Was Already Defined 2-15
2.1.25 Storing a Segmented String Greater in Size
than 65,508 Bytes Caused a Bugcheck 2-16
2.1.26 Programs with Calls to Both Rdb/VMS and DBMS
Returned Link Errors 2-16
vii
Contents
2.1.27 Problem with Partitioned Indexes 2-17
2.1.28 Retrieving Records Using a Multisegmented
Partitioned Sorted Index on Uniform Storage
Areas Caused a Bugcheck to Occur 2-17
2.1.29 Queries with FIRST n Containing Aggregates and
SORT Executed Incorrectly 2-18
2.1.30 A Bugcheck Was Generated When the Query
Optimizer Tried to Match a Computed Field
Before Evaluating the Expression 2-18
2.1.31 Query Optimizer Chose to Use a Hashed Index
When It Should Not Have 2-18
2.1.32 VALID IF Statements Might Not Compile or
Evaluate Properly for DEFAULT VALUEs 2-19
2.1.33 A STARTING WITH Predicate on a Descending
Index Returned No Data 2-19
2.1.34 A Query Using Sort Returned Rows in Field Size
Order for VARCHAR Fields with a Collating
Sequence Specified 2-19
2.1.35 A Compiled Query Returned Incorrect Data When
Using an Index 2-20
2.1.36 Wrong Result Was Returned by a Query with a
Predicate on a Computed Field 2-21
2.1.37 Query Did Not Use an Index-Only Retrieval Even
If All the Fields Were in an Index 2-22
2.1.38 A Join Query Matched Nulls to Zeros or Blanks
and Produced Incorrect Results 2-22
2.1.39 Batch-Update or Exclusive Transactions Failed
When RDB$SYSTEM Was Read-Only 2-23
2.2 SQL SOFTWARE ERRORS FIXED 2-24
2.2.1 SQL$.EXE Failed to Return Status on Failure of
an Import Operation 2-24
2.2.2 SQL Did Not Correctly Detect Errors in a
CREATE INDEX Statement 2-24
2.2.3 Generated Constraint Names and Check Option
Constraint Names Were Not Checked 2-25
2.2.4 SQL Quantified Predicates Did Not Function in
Trigger WHEN Predicates 2-25
viii
Contents
2.2.5 Problem with INTEGRATE Statement When SQL
Database Is Defined with DICTIONARY IS
REQUIRED Clause 2-25
2.2.6 Problems with INTEGRATE SCHEMA . . . CREATE
PATH in SQL 2-26
2.2.7 Short Char Variable Was Not Padded Correctly 2-27
2.2.8 SQL C Precompiler Generated Incorrect Code 2-27
2.2.9 SQLPRE Changed Names of the SQLDAs to
Uppercase 2-28
2.2.10 SQL$PRE Hung in Batch Mode When an Invalid
File Name Was Given 2-28
2.3 RDO AND RDBPRE SOFTWARE ERRORS FIXED 2-28
2.3.1 RDO EXPORT Now Prints the Reason for Failure 2-28
2.3.2 IMPORT Statement Could Fail to Import Views or
Tables with COMPUTED BY Fields 2-29
2.3.3 RDO IMPORT Statement Did Not Support
ANSI-Style Protection 2-29
2.3.4 RDO IMPORT Would Appear to Stall in an
Infinite Loop 2-30
2.3.5 RDO DEFINE STORAGE MAP DESCRIPTION IS Clause
Was Ignored 2-30
2.3.6 RDO SHOW RELATION Failed with INVALID_BLR for
VIDA Databases 2-30
2.3.7 Views with FIRST n Could Use Outer Query
Booleans for Indexed Retrieval 2-31
2.3.8 Problem Creating a View with a WITH CHECK
OPTION Clause Without a WHERE Predicate in the
SELECT Clause 2-32
2.3.9 Problem with Multikey Partitioned Indexes 2-32
2.3.10 Index Scan Problem Caused a FOR Loop to Loop
Infinitely 2-32
2.3.11 A Bugcheck Resulted When Using the PLACE Verb
Within a FOR Loop Using RDO 2-32
2.3.12 The /NOINITIALIZE Qualifier in FORTRAN
Required That Four Characters Be Specified 2-33
2.3.13 RDBPRE BASIC Preprocessor Generated Ambiguous
Code 2-33
ix
Contents
2.3.14 When You Entered an ACL Entry, an Argument to
the Position Clause Could Not Exceed 255 2-34
2.3.15 COMPUTED BY Clause in Aggregate Subqueries
Produced Bugcheck Dumps 2-34
2.3.16 Query on System Relations Caused a Loop 2-34
2.4 RDML SOFTWARE ERRORS FIXED 2-35
2.4.1 RDML Did Not Recover from Statistical Function
Keywords Used as Host Variables 2-35
2.4.2 RDML Did Not Display the Names of Context
Variables Improperly Referenced by SORTED BY
and REDUCED TO Clauses 2-36
2.4.3 RDML Context Variables Could Not Be Named the
Same as the Relation 2-36
2.4.4 Using Concatenated Expression Failed in C with
Invalid Operand Error 2-37
2.4.5 RDML REDUCED TO Clause Generated BLR in
Reverse Order 2-37
2.4.6 RDML Generated Incorrect Code for a STORE
Statement in a Pascal Program 2-38
2.4.7 In SORTED BY or REDUCED TO Clauses RDML
Did Not Check That Only Local Fields Were
Included 2-38
2.4.8 Incompatible Data Types in VAX Pascal
Generated Code 2-38
2.4.9 DECLARE_VARIABLE Restriction Removed 2-41
2.5 RMU SOFTWARE ERRORS FIXED 2-41
2.5.1 Problem Existed with the RMU/LOAD Operation 2-41
2.5.2 RMU/BACKUP and VMS DCL COPY of Single-File
Databases Made the Database Unusable 2-42
2.5.3 RMU/BACKUP/ONLINE Had Lock Conflict Problems 2-42
2.5.4 RMU/BACKUP/ONLINE Waited Indefinitely for a
Quiet Point Lock 2-42
2.5.5 RMU/BACKUP/ONLINE /CHECKSUM_VERIFICATION
Qualifier Would Occasionally Fail 2-43
2.5.6 A By-Area Backup Was Always Performed Against
the Last Full Backup 2-43
x
Contents
2.5.7 Problem Existed with RMU By-Area Backup
Function 2-43
2.5.8 Problem Existed with RMU/BACKUP or RMU/RESTORE
with More Than 268 Database Storage Areas 2-44
2.5.9 CHANGE FIELD COLLATING_SEQUENCE Did Not Work
on RMU/RESTORE Command That Used Convert 2-44
2.5.10 Problem Existed with RMU/RESTORE/INCREMENTAL
and Adding a Storage Area 2-45
2.5.11 RMU/RESTORE Did Not Ignore Logical Areas
Outside the Range of 1 to 16,384 and Produced
a Bugcheck 2-45
2.5.12 RMU/RECOVER By-Area Did Not Work Correctly 2-45
2.5.13 RMU/RECOVER By-Area Command Has Changed
Semantics 2-46
2.5.14 RMU/VERIFY Could Not Verify the Root File If
the AIJ File Was Open 2-47
2.5.15 Errors Occurred During RMU/VERIFY 2-47
2.5.16 Database Verification Returned %RMU-W-BADDBPRO
Error 2-47
2.5.17 RMU/REPAIR Corrupted Databases 2-48
CHAPTER 3 KNOWN PROBLEMS, RESTRICTIONS, AND OTHER NOTES 3-1
3.1 KNOWN PROBLEMS AND RESTRICTIONS FOR ALL INTERFACES 3-1
3.1.1 Improving the Performance of the EXPORT
Operation Using the DCL SET Command to Change
the Default Extend Parameter Value 3-1
3.1.2 Undetected Deadlock with Distributed
Transactions 3-2
3.1.3 Restrictions on Distributed Transactions
Related to the DISTRIBTRAN Security
Privilege 3-3
3.1.4 SNAPSHOTS DEFERRED May Stall Transactions 3-3
3.1.5 PLACEMENT VIA INDEX Clause Prohibits Shadow
Clustering 3-4
3.1.6 Using the CREATE INDEX Statement Locks the
Database If Snapshots Are Deferred 3-5
xi
Contents
3.1.7 Source Attributes for Storage Maps Are Not
Saved in Pre-V3.1 SQL IMPORT Operations 3-5
3.1.8 RDB$SYSTEM Storage Area Cannot Be Read-Only
When a Relation Is Readied in Exclusive or
Batch-Update Mode 3-7
3.1.9 Joined Relations Do Not Allow "MODIFY" If
Using the WITH Clause 3-8
3.1.10 Using RDML/C to Update a VIEW Returns Errors 3-10
3.1.11 Range Query Returns Unexpected Results 3-11
3.1.12 DECLARE and START Stream Are No Longer Allowed
for Certain Views 3-12
3.1.13 A Clarification of the Storage Algorithm for
Mixed Pages 3-12
3.1.14 Adjustment of Cardinalities in V4.0 Is Likely
to Cause Poorer Optimizer Performance 3-13
3.1.15 SELECT on SCHEMA (READ on DATABASE) ACE Is Now
Required 3-13
3.1.16 Rdb/VMS Network Link Failure Does Not Allow
FINISH to Tidy Up Transactions 3-14
3.1.17 Passwords for the RDB$REMOTE Account in UAF
and for the RDBSERVER Object in NCP Must Be
the Same 3-14
3.1.18 RDB$REMOTE Account Is Now Non-Privileged 3-15
3.1.19 VAX Data Distributor Copy Processes Do
Not Process Database Pages Ahead of an
Application 3-15
3.1.20 Setting an Appropriate WSEXTENT Relative to
WSQUOTA for SORT/MERGE Operations 3-16
3.1.21 Attempting to Acquire a Lock Could Cause an
Infinite Loop 3-17
3.2 SQL KNOWN PROBLEMS AND RESTRICTIONS 3-17
3.2.1 SQL Applications Involved in Distributed
Transactions Must Have DISTRIBTRAN Privilege 3-17
3.2.2 SQL Allows False Redefinition of the DATE Data
Type 3-18
3.2.3 Problem Adding a New Field to CDO Defined
Record and Not to Last Position 3-19
xii
Contents
3.2.4 Module Language Passes Extraneous Characters
with Inexact Dynamic Descriptors 3-20
3.2.5 Close List Cursor Before Fetching Rows from
Table Cursor 3-21
3.2.6 SELECT Statement with GROUP BY Clause Did Not
Return Date Fields in EDIT STRING Format 3-22
3.2.7 When Using the BETWEEN Operator, You Should
Specify the Lower Value First 3-23
3.2.8 Cautions on Using ANY, ALL, or IN Clauses in
Constraint Definitions 3-24
3.2.9 SQL Does Not Recognize a Record During SQL
Precompile Time 3-24
3.2.10 An SQLMOD Query Returns Empty Rows 3-25
3.2.11 Input VARCHAR Parameter Actual Value Is Longer
Than Procedure Parameter 3-26
3.2.12 Release of Cursor Statements Referencing
Prepared Statements Causes Problems 3-27
3.2.13 SQL Does Not Translate Logical Names
Referencing Source Databases 3-27
3.2.14 Problem with Transferring a Table to an
Existing Database Containing the Same Table
Name 3-28
3.3 RDO AND RDBPRE KNOWN PROBLEMS AND RESTRICTIONS 3-29
3.3.1 RDO SHOW USERS and SHOW MONITOR Statements Do
Not Work for Remotely Accessed Databases 3-30
3.4 RDML KNOWN PROBLEMS AND RESTRICTIONS 3-30
3.4.1 RDML Generates an Error Message When
Attempting to Store or Modify Read-Only
(COMPUTED BY) Fields 3-30
3.5 RMU KNOWN PROBLEMS AND RESTRICTIONS 3-31
3.5.1 Non-Updatable Fields Cannot Be Unloaded Using
the RMU/UNLOAD Command 3-31
3.5.2 Single-File Databases Require the /ROOT
Qualifier When Using the RMU/MOVE_AREA
Command 3-32
xiii
Contents
3.5.3 The RMU/BACKUP/AFTER_JOURNAL /CONTINUOUS
/UNTIL="TOMORROW:+00:50" Command Fails for
V3.1A and VMS V5.3 or V5.4 3-32
3.5.4 Shortened Form of RMU/RESTORE Command Does Not
Work Properly 3-32
3.5.5 Installing RMU with Privileges 3-33
3.5.6 The Returned DCL $STATUS Is Inconsistent
Between RMU Commands 3-33
3.6 SQL/SERVICES KNOWN PROBLEMS AND RESTRICTIONS 3-34
3.6.1 SQL/Services Database Class Server Is Not
Supported in Rdb/VMS V4.0 3-34
3.6.2 SQL/Services V4.0 Server Uses Proxy-Like and
Default Access to Authorize V3.0 or V3.1
Client Applications 3-35
3.6.3 Invalid Length Is Returned by SQLSRV_VARBYTE
Data Type 3-35
3.6.4 Allocating Space for SQLSRV_VARCHAR and
SQLSRV_VARBYTE Data Types 3-36
3.6.5 SQL/Services V4.0 Server Error -2031 Returned
to V3.1 Client APIs 3-36
3.6.6 TYPE Not CLASS Keyword Used in Configuration
File 3-36
3.6.7 SQLSRV_ASCII_STRING Data Type Is Not
Terminated with a NULL Character 3-37
3.6.8 Filter Expressions Return Incorrect Results 3-37
3.6.9 Using Group/System-Not Process-Logical Names
in SQL/Services 3-37
3.6.10 API sqlsrv_fetch_many Routine 3-38
3.6.11 SQLSRV$SRV Default Account Must Be Present for
SQL/Services to Start Automatically 3-38
3.6.12 DECnet Default File Access for SQL/Services 3-38
3.7 CDD/PLUS RESTRICTIONS 3-39
3.7.1 Minimum Supported Version of CDD/Plus 3-39
3.7.2 Using CDD/Plus with PERSONNEL.COM 3-39
3.7.3 Some Views Are Not Accepted by VAX CDD/Plus
V4.2 3-39
xiv
Contents
3.7.4 GRANT and REVOKE Statements Generate
MBLRSYNERR Message If Attached by Path Name 3-40
3.7.5 Using CDD/Plus to Specify Collating
Sequences 3-40
3.8 RESTRICTIONS LIFTED BY CDD/PLUS VERSION 4.2 3-41
3.8.1 Incompatibilities Between Rdb/VMS V4.0 and
CDD/Plus That Have Been Lifted by VAX CDD/Plus
Version 4.2 3-41
3.9 RDB/VMS DOCUMENTATION ERRORS AND OMISSIONS 3-41
3.9.1 Error in Table D-1, Fields in the SQLDA 3-41
3.9.2 Error in COL-DEFINITION Syntax Diagram 3-43
3.9.3 Cursors Containing ORDER BY Clauses Are Not
Read-Only 3-44
3.9.4 LIST OF BYTE VARYING Segment Size Correction 3-44
3.9.5 LIST OF BYTE VARYING in SQLTYPE Field of
SQLDA 3-44
3.9.6 Value Returned by AVG Function 3-45
3.9.7 NULL Characters May Terminate Character Data
Type Columns 3-45
3.9.8 ORDER BY and LIMIT TO Clauses Are Missing from
SQL Quick Reference Guide 3-45
3.9.9 INSERT Statement Can Be Triggered 3-46
3.9.10 PRINT Statement Is Missing from Table 2-8 3-46
3.9.11 SQL$PRE FORTRAN AVG Function Returns Rounded
Integer Value 3-46
3.9.12 Error Exists in Privilege Table 3-47
3.9.13 Clarification of Constraint Semantics 3-47
3.9.14 Declaration of the Distributed Transaction
Identifier (TID) in FORTRAN Is Incorrect 3-47
3.9.15 Using RDML and the Two-Phase Commit Protocol
by Calling the DECdtm System Service Calls
Implicitly and Explicitly Is Not Fully
Documented 3-48
3.9.16 REQUEST_HANDLE Clause in Rdb/ELN VAXELN Pascal
Applications 3-48
3.9.17 Host Language Multipath Statements and RDML
Update Statements 3-48
xv
Contents
3.9.18 C Host Variable Syntax Diagram 3-51
3.9.19 Method to Create Dummy AIJ or RUJ Files to
Replace One of These Missing Files Is No
Longer Supported 3-51
3.9.20 Improving the Performance of Import/Export
Operations 3-52
3.9.21 Changes to RMU/ALTER That Are Not Documented
in V4.0 3-52
3.9.22 Description of the Storage Algorithm for
Storing Records in Mixed Storage Areas When
Target Pages Are Selected 3-54
3.10 SQL/SERVICES DOCUMENTATION ERRORS AND OMISSIONS 3-56
3.10.1 SQLSRV$DEFAULT_ACCESS Logical Name Is
Incorrectly Documented 3-57
3.10.2 API sqlsrv_sqlca_sqlerrd Routine Is Omitted 3-57
3.10.3 List Cursor SQLCA Return Values for
sqlsrv_open_cursor Routine 3-58
3.10.4 SQL/Services sqlsrv_sqlda_bind_data Routine
Error 3-59
3.11 RESTRICTIONS RETAINED FROM V3.1 3-60
3.11.1 Object Modules Created with V3.1 and V4.0 Are
Not Downward-Compatible 3-60
3.11.2 FIRST n Is Not Considered During
Optimization 3-60
3.11.3 Do Not Add Fields to Relations, Define
Indexes, Triggers, and Other Database Objects
Based on System Relation Fields 3-61
3.11.4 Performance Considerations for Using VARYING
STRING or COLLATING SEQUENCE Attribute for
Index Keys 3-61
3.11.5 Sorting or Implied Sorting for Projection on a
Dbkey Is Not Worthwhile 3-63
3.11.6 Many Attaches to and Detaches from the Same or
Multiple Databases While Using Search Lists
to Point to the Database Use Up I/O Channel
Quota 3-63
xvi
Contents
3.11.7 Do Not Disable ASTs If You Want to Access a
Database Remotely 3-64
3.11.8 Unexpected Setting of the NULL Attribute After
an IMPORT Operation 3-65
3.11.9 IMPORT Statement Generates Bugcheck Dumps If
the Index Definition Fails 3-66
3.11.10 IMPORT Statement Failed to Complete Index
Definition with Users Attached to the
Database 3-67
3.11.11 Using LIB$DT_INPUT_FORMAT to Change Date Input
Format Sometimes Causes Access Violation 3-68
3.11.12 Operations on F-Floating Data Round to Whole
Numbers 3-68
3.11.13 Rdb/VMS Interaction with Data Distributor V2.1
May Generate Bugcheck Dumps 3-69
3.11.14 Batch-Update Transactions Can Cause a Bugcheck
Dump to Occur If an Index Definition Fails 3-69
3.11.15 Rdb/VMS Logical Name, RDMS$BIND_WORK_VM, Has
an Upper Limit of 65,000 Bytes 3-70
3.11.16 Reserving a Table in Exclusive Mode May
Prevent Operations from Being Performed on
Other Tables in the Same Storage Area 3-70
3.11.17 There Is a Problem Defining COLLATING SEQUENCE
IS NORWEGIAN NORWEGIAN 3-70
3.11.18 Rdb/VMS and VMS Debugger Interaction 3-72
3.11.19 RDB$DBKEY_LENGTH System Field Incorrect for
Certain Views 3-74
3.11.20 Problem with the Use of Virtual Memory 3-75
3.11.21 Using the /USERS_MAX and /NODES_MAX Qualifiers
with the RMU/RESTORE Command Requires Both
Qualifiers on the First Line of DCL Input 3-75
3.11.22 A Snapshot File Name, File Type, or Version
Number Cannot Be Changed for Single-File
Databases 3-76
3.11.23 There Is a 17-Character Limit for File Names
When You Back Up Databases to Tape 3-76
xvii
Contents
3.11.24 RMU/DUMP/BACKUP Command Specifying a Value of
1 or 2 for the /ACTIVE_IO Qualifier Causes the
AIJ Dump to Stall 3-77
3.11.25 RMU/SHOW STATISTICS Command Does Not Record
All Statistics in the Binary File 3-78
3.11.26 Dumping the AIJ File Is Incompatible with
Normal Usage 3-79
3.11.27 RMU/RESTORE Command May Initialize the SPAM
Thresholds in One or More Storage Areas 3-79
3.11.28 Correction to the Usage Note on Constraints
with the CREATE TABLE Statement 3-80
3.11.29 Using Rdb/VMS from a VMS Detached Process 3-80
3.11.30 Disable VAX SQL/Services V1.0 Startup
Procedure 3-82
3.11.31 DDL Statements Cannot Refer to Objects Before
Their Creation 3-82
3.11.32 Deleting Metadata in Rdb/VMS 3-83
3.11.33 SQL Schema Compilation Fails on the First
Fatal Error 3-83
3.11.34 COMMENT ON Statement Cannot Be Used in CREATE
SCHEMA Statement 3-84
3.11.35 Dynamic Cursors Cannot Access Views Created
with GROUP BY or UNION Clause 3-84
3.11.36 Cannot Use INCLUDE Statement in Variable
Declaration 3-84
3.11.37 SQL Ada Precompiler Does Not Support the
Correct Overloading of Subprograms 3-85
3.11.38 SQL Precompiler Does Not Evaluate Expressions
in Variable Declarations or Understand
Literals 3-86
3.11.39 SQL Ada Precompiler Does Not Support Named
Literals or Ranges 3-87
3.11.40 Limiting Length of File Names 3-87
3.11.41 Limiting Number of Continuation Lines per
Record 3-87
3.11.42 SQL Module Language Processor Fails on the
First Fatal Error 3-88
3.11.43 Database Handle Problem on START_STREAM 3-88
xviii
Contents
3.11.44 RDO CHANGE INDEX Restriction Is Now Signaled 3-88
3.11.45 Problem of Different Optimizations of the Same
Query from Different Environments 3-89
3.11.46 Restrictions on Using Missing Value Fields in
Nested Queries 3-90
3.11.47 STORE WITHIN and DISABLE/ENABLE COMPRESSION
Clauses Cannot Both Be Specified 3-91
3.11.48 Variables Cannot Be Database Handles 3-92
3.11.49 RDML Run-Time Object Library No Longer
Requires You to Link with VAXCRTL or VAXCRTLG
Object Libraries or Shareable Images 3-95
3.11.50 RDML/EPascal Ignores /LINKAGE=PROGRAM_SECTION
Qualifier 3-95
3.11.51 RDML/Pascal Does Not Understand Some Character
String Value Expressions 3-95
3.11.52 RDML/Pascal Does Not Accept All Possible Valid
Pascal Host Language Variables 3-96
3.11.53 RDML Does Not Allow Nested Comments 3-96
3.11.54 C Host Variables 3-97
3.11.55 C String Continuation Character 3-98
3.11.56 Path Name and the DATABASE Statement 3-98
3.12 DSRI NOTES AND RESTRICTIONS RETAINED FROM V3.1 3-99
3.12.1 RCI Instantiation Number Must Be Zero for
Remote Access 3-99
3.12.2 Context Variables That Are Not Unique Within a
Request Cause Invalid BLR 3-99
3.13 CDD/PLUS RESTRICTIONS RETAINED FROM RDB/VMS V3.1 3-100
3.13.1 CDD/Plus COMPUTED BY Fields Are Not Currently
Supported in Rdb/VMS Relations or Views 3-101
3.13.2 EXPORT WITH NOEXTENSIONS Statement Can Corrupt
the CDD$DATABASE 3-101
xix
Contents
APPENDIX A PROCESS POOLING A-1
A.1 PROCESS POOLING OVERVIEW A-2
A.2 PROCESS POOLING COMPONENTS A-3
A.3 COMMUNICATION SERVER PROCESS A-6
A.3.1 The Default Configuration File A-7
A.3.2 Modifying the Configuration File A-13
A.3.2.1 Procedures for Modifying the Configuration FileA-13
A.3.2.2 Rules for Modifying the Configuration File A-14
A.4 EXECUTION SERVER PROCESSES A-15
A.4.1 Choosing an Execution Server Process A-15
A.4.2 Generic Class Execution Server Processes A-17
A.4.3 Database Class Execution Server Processes A-17
A.4.3.1 System Management Tasks: Setting Up Database
Servers A-18
A.4.3.2 Programmer Tasks: Setting Up Database Servers A-22
A.4.4 The Dynamic Nature of Execution Servers A-23
A.5 METHODS OF SERVER ACCESS A-25
A.6 HOW TO ENABLE SERVER ACCESS A-26
A.6.1 Explicit Access A-26
A.6.2 SQL/Services Proxy Access A-27
A.6.3 SQLSRV$SRV Default Account Access A-28
A.7 REINITIALIZING PROXY AND DEFAULT ACCESS A-28
EXAMPLES
A-1 Definition of Generic Execution Server Process A-8
A-2 Definition of Database Execution Server Process A-9
xx
Contents
TABLES
1 Database Terms xviii
1-1 Sample Programs in Precompiled SQL and SQL Module
Language 1-52
1-2 Obsolete Keywords for DECLARE and SET
TRANSACTION 1-80
1-3 Obsolete Statements 1-82
3-1 Fields in the SQLDA 3-42
A-1 Process Pooling Components A-5
A-2 Configuration File Parameters A-10
A-3 Methods of Accessing the Server System A-25
xxi
_____________________________________________________________________
Preface
VAX Rdb/VMS software, Version 4.0, often referred to as V4.0 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.0, 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.0 is referred to throughout
this manual as Version 3.1 or V3.1. The term "Version 3.1"
refers to Version 3.1 and any updates to Version 3.1; thus,
for example, a reference to "the Version 3.1 behavior" of a
statement refers to the behavior under Version 3.1 and any
of its updates.
References to a specific update (for example, Version 3.1A
or Version 3.1B) are made only where it is necessary to be
precise.
___________________________________________________________________
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.
xvii
Preface
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.
___________________________________________________________________
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:_Database_Terms___________________________________________
SQL______________RDO,_RDML________ANSI/ISO_SQL_STANDARD___________
Alias Context vari- Alias
able
Authorization Database handle Authorization identifier
identifier
Column Field Column
Column select Record selec- Column select expression
expression tion expression
Domain Global field Domain (SQL2)[1]
__________________________________________________________________
[1]SQL2 is the industry standard currently being developed.
xviii
Preface
Table_1_(Cont.):_Database_Terms___________________________________
SQL______________RDO,_RDML________ANSI/ISO_SQL_STANDARD___________
Parameter Host language Parameter
variable
Predicate Conditional Predicate
expression
READ ONLY READ_ONLY READ ONLY
READ WRITE READ_WRITE READ WRITE
Result table Record stream Result table
Row Record Row
Storage area Storage area N/A[2]
Storage map Storage map N/A[2]
Table Relation Table
__________________________________________________________________
[2]N/A means that the term is not applicable or not used with the
listed product, standard, or system.
__________________________________________________________________
___________________________________________________________________
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.
xix
Preface
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 one appendix:
Chapter 1 Summarizes the new and changed features of Rdb/VMS
V4.0.
Chapter 2 Describes known software errors in versions prior to
V4.0 that were fixed in V4.0.
Chapter 3 Describes problems, restrictions, and workarounds
known to exist in Rdb/VMS. This chapter also in-
cludes restrictions retained from previous versions of
Rdb/VMS.
Appendix A Describes how process pooling works and the way to
manage pooling at your site. It also describes how
the communication server authorizes client application
access to the server system.
___________________________________________________________________
Related Manuals
For more information on VAX Rdb/VMS, see the following manuals in
the Rdb/VMS documentation set:
o VAX Rdb/VMS Introduction and Master Index
xx
Preface
Introduces Rdb/VMS and explains major terms and concepts.
Includes a glossary, a directory of Rdb/VMS documentation,
and a master index that combines entries from all the Rdb/VMS
manuals.
o VAX Rdb/VMS Guide to Distributed Transactions
Describes the two-phase commit protocol and distributed trans-
actions, explains how to start and complete distributed trans-
actions 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 trans-
late that design into a physical database using Rdb/VMS data
definition statements.
o VAX Rdb/VMS Guide to Database Maintenance and Performance
Provides guidelines for maintaining good database performance
and explains how to use the database maintenance utilities to
perform backup and recovery operations, restore journals, and
analyze the database.
xxi
Preface
o VAX Rdb/VMS Guide to Database Tuning
Introduces the concept of tuning, and explores how tuning the
system, the database, and the application can affect database
performance. Outlines a series of steps to follow in identify-
ing, analyzing, isolating, and solving a performance problem,
and in monitoring the resulting solution. Includes a set of
decision trees that provide an organized approach to solving
some common database tuning problems.
o VAX Rdb/VMS Guide to Using RDO, RDBPRE, and RDML
Describes how to use the features of Rdb/VMS to retrieve,
store, change, and erase data. Shows how to write programs that
use Rdb/VMS as a data access method; contains information on
writing programs in high-level languages that are supported by
Rdb/VMS preprocessors, including Relational Data Manipulation
Language (RDML); and describes Callable RDO, an interactive
utility for languages without preprocessors.
o VAX Rdb/VMS Guide to Using SQL
Introduces the Rdb/VMS SQL (structured query language) in-
terface, and shows how to retrieve, store, and update data
interactively and through application programs.
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
that allows programs, from various remote computers running
the MS-DOS, OS/2, ULTRIX, ULTRIX for RISC, or VMS operating
systems, to access Rdb/VMS or VIDA databases on a VMS server
system.
o VAX Rdb/VMS SQL Reference Manual
Provides reference material and a complete description of the
statements, the interactive, dynamic, and module language in-
terfaces, and the syntax for SQL, the structured query language
interface for Rdb/VMS.
xxii
Preface
o VAX Rdb/VMS SQL Quick Reference Guide
Summarizes the information in the VAX Rdb/VMS SQL Reference
Manual.
o VAX Rdb/VMS RDO and RMU Reference Manual
Provides reference material and a complete description of the
statements and syntax of the Rdb/VMS Relational Database
Operator (RDO) interface and the commands of the Rdb/VMS
Management Utility (RMU).
o RDML Reference Manual
Describes the syntax and use of the Relational Data Manipulation
Language (RDML), which can be embedded in VAX C or VAX Pascal
programs to access Rdb/VMS or Rdb/ELN databases.
xxiii
Preface
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 atten-
tion on the statements or commands themselves.
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 speci-
fied letter key.
<RETURN> This symbol in examples indicates the RETURN key.
<TAB> This symbol in examples indicates the TAB key.
A vertical ellipsis in an example means that information
. not directly related to the example has been omitted.
.
.
. . . A horizontal ellipsis in statements or commands means
that parts of the statement or command not directly
related to the example have been omitted.
xxiv
Preface
< > 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 RdbExpert for VMS software is referred to as RdbExpert.
o DECtrace for VMS software is referred to as DECtrace.
o The SQL interface 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 now included as part
of VAX Rdb/VMS V3.1 and higher.
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.
o VAX CDD/Plus software is referred to as CDD/Plus, the data
dictionary, or the dictionary.
xxv
Preface
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 VAX Pascal and VAXELN Pascal software are referred to as
Pascal. When the use of a statement is not the same in both
the VAXELN and VMS environments, that statement is specified as
VAXELN Pascal or VAX Pascal.
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. Version 4.0 of
VAX Rdb/VMS software is often referred to as V4.0.
o VAX TDMS software is referred to as TDMS.
o VAX TEAMDATA software is referred to as TEAMDATA.
o VIDA software is referred to as VIDA.
xxvi
Chapter 1
New and Changed Features
This chapter provides a summary of the new features and technical
changes in VAX Rdb/VMS Version 4.0.
Section 1.1 through Section 1.17 describe new and changed features
that apply to all Rdb/VMS interfaces. The other sections describe
new features and specific changes to each interface, changes to
the sample personnel database, new and changed Rdb/VMS logical
names, obsolete statements and features for each interface, and a
summary of additions and changes to the documentation.
1.1 Two-Phase Commit Protocol
The two-phase commit protocol coordinates the activity of com-
ponents of a transaction to ensure that every required operation
is completed before a transaction is made permanent, even if the
transaction is a distributed transaction. A distributed transac-
tion groups more than one database or database attachment into one
transaction, even if the databases are located on different nodes.
If one operation in a transaction cannot be completed, none of the
operations are completed. This "all or nothing" approach guaran-
tees that distributed databases remain logically consistent with
one another. This feature has been implemented for all Rdb/VMS
interfaces: SQL, RDO, RDBPRE, and RDML.
New and Changed Features 1-1
The major benefit of using the two-phase commit protocol is that
you can divide a large Rdb/VMS database into several smaller
databases, or you can create applications that access several
different databases, without compromising the integrity or consis-
tency of your data.
For more information, see the VAX Rdb/VMS Guide to Distributed
Transactions.
NOTE
Rdb/VMS V4.0 requires VMS V5.4, which includes DECdtm
Services, in order to run the two-phase commit protocol.
Rdb/VMS returns the following error if you attempt to use
DECdtm Services while running under VMS Version 5.3:
%RDB-F-NODECDTM, DETdtm is not installed on your system
Rdb/VMS may attempt to invoke DECdtm Services implicitly if
you use the SQL interface and access two or more databases
in a transaction. This causes the previous error to be re-
turned if you are running VMS Version 5.3. You can disable
the two-phase commit protocol and avoid this error message
by defining the following logical name:
$ DEFINE SQL$DISABLE_CONTEXT TRUE
However, be sure to deassign this logical name after you
install VMS Version 5.4 to receive the benefit of two-phase
commit.
Section 1.1.1 to Section 1.1.5 document the new features and
changes that support the two-phase commit protocol.
1-2 New and Changed Features
1.1.1 Using SQL with Distributed Transactions
Beginning with Rdb/VMS V4.0, when you recompile an application
program using the SQL module processor or the SQL precompiler,
transactions use the two-phase commit protocol by default in one
of the following ways:
o By implicitly calling DECdtm system service calls
For application programs that were compiled under Rdb/VMS
V3.1 or earlier, use the two-phase commit protocol simply by
recompiling your programs. When you do, Rdb/VMS invokes the
DECdtm system service calls for your application.
o By explicitly calling the DECdtm system service calls and using
variables to pass the value of the distributed transaction
identifier (TID)
How you pass the value of the distributed TID to the applica-
tion depends upon whether you are using precompiled SQL or the
SQL module language. You must write new application programs or
modify existing programs to incorporate the following:
- In SQL module language
You must declare a context structure in the host language
program and you must pass it by reference to module proce-
dures that contain certain statements.
The context structure contains the distributed TID as one of
its elements.
In addition, you must use the /CONTEXT qualifier in the SQL
module processor command line when you process the program.
The /CONTEXT qualifier tells SQL that it should execute
module language procedures in the context of a particular
distributed transaction.
- In precompiled SQL
New and Changed Features 1-3
You must declare a context structure that contains the
distributed TID as one of its elements. In addition, you
must include a USING CONTEXT clause in most executable SQL
statements involved in the distributed transaction. The
USING CONTEXT clause associates a context structure with
a particular SQL statement and tells SQL that it should
execute the SQL statement in the context of a particular
distributed transaction.
1.1.2 Using RDBPRE with Distributed Transactions
To use the two-phase commit protocol with RDBPRE, you must either
recompile any existing application programs that were compiled
with Rdb/VMS V3.1 or earlier, or you must write new application
programs. With RDBPRE, the two-phase commit protocol is not the
default.
Applications can use the two-phase commit protocol by calling
the DECdtm system service calls implicitly or explicitly. RDBPRE
provides the following ways for application programs to use the
two-phase commit protocol:
o By implicitly calling DECdtm system services calls in the
following two ways:
- For application programs that were written under Rdb/VMS
V3.1 or earlier, use the two-phase commit protocol simply by
recompiling your programs. You must use the /DISTRIBUTED_
TRANSACTION qualifier in the precompiler command line. When
you do this, Rdb/VMS invokes the DECdtm system service calls
for your application.
- For new application programs that invoke only Rdb/VMS
databases, or Rdb/VMS databases and VIDA databases, use
the DISTRIBUTED_TRANSACTION keyword in the START_TRANSACTION
statement. When you do this, Rdb/VMS invokes the DECdtm
system service calls for your application. For example,
1-4 New and Changed Features
to recompile the COBOL program SAMPLE.RCO with the RDBPRE
preprocessor, use the following command:
$ RUN SYS$SYSTEM:RDBPRE
INPUT FILE> SAMPLE /COBOL /DISTRIBUTED_TRANSACTION
o By explicitly calling the DECdtm system service calls and using
variables to pass the value of the distributed transaction
identifier (TID)
If your application starts a distributed transaction that
includes other read/write database management products that
support the two-phase commit protocol, your application must
explicitly invoke the DECdtm system service calls. For example,
if your application starts a distributed transaction using
Rdb/VMS and VAX DBMS, your application must explicitly call
SYS$START_TRANS and SYS$END_TRANS.
In addition, you must use the full DISTRIBUTED_TRANSACTION
clause in the START_TRANSACTION statement.
1.1.3 Using RDML with Distributed Transactions
The implementation of RDML with distributed transactions is very
similar to using RDBPRE with distributed transactions. See the
VAX Rdb/VMS Guide to Distributed Transactions for a complete
description of using RDBPRE with distributed transactions and
the RDML Reference Manual and the VAX Rdb/VMS Guide to Using RDO,
RDBPRE, and RDML for more information.
To use the two-phase commit protocol with RDML, you must either
recompile any existing application programs that were compiled
with Rdb/VMS V3.1 or earlier, or you must write new application
programs. With RDML, the two-phase commit protocol is not the
default.
New and Changed Features 1-5
Applications can use the two-phase commit protocol by calling
the DECdtm system service calls implicitly or explicitly. RDML
provides the following ways for application programs to use the
two-phase commit protocol:
o By implicitly calling DECdtm system services calls in the
following two ways:
- For application programs that were written under Rdb/VMS
V3.1 or earlier, use the two-phase commit protocol simply by
recompiling your programs. You must use the /DISTRIBUTED_
TRANSACTION qualifier in the precompiler command line. When
you do this, Rdb/VMS invokes the DECdtm system service calls
for your application.
- For new application programs that invoke only Rdb/VMS
databases, or Rdb/VMS databases and VIDA databases, use
the DISTRIBUTED_TRANSACTION keyword in the START_TRANSACTION
statement. When you do this, Rdb/VMS invokes the DECdtm sys-
tem service calls for your application. For example, to re-
compile the C program SAMPLE.RC with the RDML preprocessor,
use the following command:
$ RDML :== $RDML/C
$ RDML
SOURCE FILE> SAMPLE /DISTRIBUTED_TRANSACTION
o By explicitly calling the DECdtm system service calls and using
variables to pass the value of the distributed transaction
identifier (TID)
If your application starts a distributed transaction that
includes other read/write database management products that
support the two-phase commit protocol, your application must
explicitly invoke the DECdtm system service calls. For example,
if your application starts a distributed transaction using
Rdb/VMS and VAX DBMS, your application must explicitly call
SYS$START_TRANS and SYS$END_TRANS.
1-6 New and Changed Features
In addition, you must use the full DISTRIBUTED_TRANSACTION
clause in the START_TRANSACTION statement.
1.1.4 RMU Enhancements
To support Rdb/VMS distributed transactions, two new RMU commands
(RMU/RECOVER/RESOLVE and RMU/RESOLVE) have been added, and the
RMU/DUMP command has been enhanced. These changes are as follows:
o RMU/RECOVER/RESOLVE
In the after-image journal file, you can modify the state of
records that are associated with blocked distributed trans-
actions. The RMU/RECOVER/RESOLVE command allows you either to
commit or abort blocked transactions in the after-image journal
file. See the RMU/RECOVER/RESOLVE command in the VAX Rdb/VMS
RDO and RMU Reference Manual for details.
o RMU/RESOLVE
You can modify the state of unresolved distributed transac-
tions in the database. The RMU/RESOLVE command allows you to
either commit or abort unresolved transactions in the database.
See the RMU/RESOLVE command in the VAX Rdb/VMS RDO and RMU
Reference Manual for details.
o RMU/DUMP/AFTER_JOURNAL/STATE=PREPARED
You can use the /STATE=PREPARED qualifier of the RMU/DUMP
/AFTER_JOURNAL command to generate a list of records associated
with unresolved transactions in the AIJ file. Depending upon
your application, you might need to generate the list for more
than one AIJ file. See the RMU/DUMP command in the VAX Rdb/VMS
RDO and RMU Reference Manual for details.
o RMU/DUMP/USER/STATE=BLOCKED
New and Changed Features 1-7
You can generate a list of unresolved distributed transactions
using the /USER/STATE=BLOCKED qualifiers of the RMU/DUMP com-
mand. See the RMU/DUMP command in the VAX Rdb/VMS RDO and RMU
Reference Manual for details.
1.1.5 DISTRIBTRAN Privilege
The DISTRIBTRAN privilege has been added to Rdb/VMS for all inter-
faces. This privilege allows distributed transactions that use the
two-phase commit protocol to be run on a particular database by a
particular user. If you want to start a distributed transaction on
a particular database, you must have the DISTRIBTRAN privilege for
that database or schema. Note that converted or imported databases
do not inherit this privilege automatically.
1.2 New Security Features
Rdb/VMS now includes support for the following new security fea-
tures:
o Default protection
Some enhancements to Rdb/VMS security access cause a major
change to the way in which Rdb/VMS grants default access privi-
leges to new tables. When new tables are created they will now
have a default PUBLIC access of NONE. The database will also
have default PUBLIC access of NONE. To override the default
PUBLIC access for newly created tables, provide a DATABASE
identifier with the name DEFAULT. The access that is given
to this identifier will then be assigned to any newly created
tables. This is shown in the following example:
SQL> GRANT SELECT, INSERT
cont> ON SCHEMA AUTHORIZATION TEST
cont> TO [DEFAULT];
See the VAX Rdb/VMS Guide to Database Design and Definition for
more information on default protection.
1-8 New and Changed Features
o Schema SELECT privilege
You cannot attach to an Rdb/VMS database unless you have the
schema SELECT privilege (SQL) or the READ privilege (RDO).
For more information on the SELECT privilege, see the GRANT
statement in the VAX Rdb/VMS SQL Reference Manual. For more
information on the READ privilege, see the DEFINE PROTECTION
statement in the VAX Rdb/VMS RDO and RMU Reference Manual.
o Privileges that have override capability (role privileges)
The SQL DBADM and OPERATOR (RDO ADMINISTRATOR and OPERATOR)
database privileges are role-oriented in Rdb/VMS Version 4.0.
The SECURITY privilege has the ability to override the SQL
DBCTRL (RDO CONTROL) privilege for the database, tables, and
views.
For an explanation of the tasks that can be accomplished with
these database privileges, see the GRANT statement in the VAX
Rdb/VMS SQL Reference Manual and the VAX Rdb/VMS Guide to
Database Design and Definition. For more information on the
DEFINE PROTECTION statement, see the VAX Rdb/VMS RDO and RMU
Reference Manual.
o RMU privileges
Several RMU commands require different privileges than they did
with previous versions of Rdb/VMS. The privileges required for
each RMU command are documented in the Usage Notes for the RMU
command's description in the VAX Rdb/VMS RDO and RMU Reference
Manual, and in the VAX Rdb/VMS Guide to Database Design and
Definition.
The reason that some required privileges for RMU commands
have changed is to decrease dependency on the use of elevated
VMS privileges. Many commands now use Rdb/VMS role-oriented
privileges wherever possible.
New and Changed Features 1-9
With V4.0, RMU performs tape label verification and no longer
requires SYSPRV for RMU/RESTORE or RMU/DUMP/BACKUP. All that is
be required is read access to the backup file (or tape volume
set). The change to handle tape label verification allows these
RMU commands to work without the elevated VMS SYSPRV privilege
(file/tape read access is sufficient).
o Security auditing
The RMU/SET AUDIT command enables Rdb/VMS security auditing.
When security auditing is enabled, Rdb/VMS sends security
alarm messages to terminals that have been enabled as security
operators and makes entries in the database security audit
journal whenever specified audit events are detected.
The RMU/SHOW AUDIT command displays the set of security audit-
ing characteristics that have been established with the RMU/SET
AUDIT command.
The RMU/LOAD/AUDIT command allows you to load records from an
Rdb/VMS audit journal into an Rdb/VMS relation.
You can obtain information about the audit characteristics of a
particular database by dumping the header of the database root
file, as shown in the following example.
1-10 New and Changed Features
$ RMU/DUMP/HEADER MF_PERSONNEL
.
.
.
Database parameters
.
.
.
Security auditing parameters:
- Security auditing is disabled
- Security alarm is disabled
- No audit journal filename is specified
- No alarm name is specified
- Synchronous audit record flushing is disabled
- Audit every access
.
.
.
Security audit context information is also output in the KODA
portion of a bugcheck dump file immediately following the
physical I/O (PIO) context information.
For more information on security auditing, see the description
of the RMU/SET AUDIT and RMU/SHOW AUDIT commands in the VAX
Rdb/VMS RDO and RMU Reference Manual. A tutorial on using
security auditing is in the VAX Rdb/VMS Guide to Database
Maintenance and Performance.
Some of these new security features affect Rdb/VMS installa-
tion. See the VAX Rdb/VMS Installation Guide for details.
New and Changed Features 1-11
1.3 Dynamic Optimization
Dynamic optimization is a new strategy for accessing a single
table at the leaf level of the execution tree to deliver the
best performance for each instance of the table access. Dynamic
optimization improves upon traditional optimization techniques by
providing better methods of:
o Correctly choosing index access or sequential access
o Using the correct index or combination of indexes
WARNING
Dynamic optimization now attempts to approach optimal query
performance by relying heavily on the statistics collected
during query execution. If index and table cardinalities
are artificially shifted away from their correct values,
they will contradict dynamically collected statistics and
cause quite an unpredictable selection of strategies and
sequence of their evaluation. Hence any adjustment of car-
dinalities in V4.0 will most probably result in poorer
optimizer performance.
The query optimizer uses two aspects of dynamic optimization:
o Dynamic OR optimization
o Dynamic leaf-level optimization
Dynamic OR optimization significantly improves performance of
OR index retrieval in cases where two or more index ranges are
specified on the same index by the user. The most practical cases
fall into this category.
The benefits of performing these dynamic OR operations are:
o There is one open/close operation (instead of INDEX SCAN).
1-12 New and Changed Features
o There are N*Log2(N)+N comparisons for sort and collapsing key
ranges instead of (N - 1)/2 * Retrievals comparisons (assuming
singletons.)
o The substantial overhead of traveling through N-way concatena-
tion of multiple streams is eliminated.
o There are no unnecessary I/O operations for overlapping ranges.
o The result always comes up in sorted index order.
Implementing the dynamic OR optimization technique has resulted in
new notation for high Ikey and low Ikey segments that can be seen
in output that displays as part of the index retrieval strategy.
See the VAX Rdb/VMS Guide to Database Maintenance and Performance
for more information on notation changes.
With the addition of dynamic optimization as a new strategy for
accessing a single table at the leaf level of an execution tree,
you should no longer avoid the use of the BETWEEN relational
operator. The BETWEEN relational operator performs as expected
in V4.0.
The example, (X BETWEEN X AND Z) still translates to:
((X >= Y) AND (X <= Z)) OR ((X >= Z) AND (X <= Y))
However, with dynamic optimization, the two X ranges (one in-
verted) are merged either during compilation of the query or at
query execution time into a single range (note that one of the
two ranges is empty). Therefore, a single scan of an index fetches
all the required data. So the BETWEEN operator now performs as
expected.
In versions previous to V4.0, use of the BETWEEN relational opera-
tor caused two separate index scans to take place (with one of the
two scans returning zero data as expected). The result of the two
scans was then merged. The merging of data spoiled the index key
order of the data and sometimes forced an extra sort of the data.
New and Changed Features 1-13
The primary benefit of dynamic leaf-level optimization is to
provide near-optimal performance for each instance of the table
access even within the same query run regardless of:
o The data distribution
o The columns correlation
o When zero or small numbers of selected rows are delivered in
one instance
o When all or many rows are delivered in the other instance of
the same leaf execution
Dynamic leaf-level optimization is much different from the tradi-
tional data access techniques found in Rdb/VMS V3.1 and previous
versions. These techniques unavoidably made mistakes (sometimes
fatal) in choosing between index or sequential access or in se-
lecting the wrong index or wrong combination of indexes.
Four leaf types comprise dynamic leaf-level optimization:
o Background-only leaf
The most important component of dynamic optimization is a
background process that scans ranges of one or more indexes
and delivers a list of dbkeys to be used at the final stage
of retrieval for fetching data rows. Each index scan continues
only to the point where random data row fetches done by an
already built portion of a dbkey list become more expensive
than a sequential retrieval. If such a cost break point is
reached, this index scan is abandoned and other indexes are
tried. If all indexes are abandoned, a sequential retrieval is
then performed.
When at least one dbkey list is successfully built before
reaching a cost break point, the dbkey lists of the other
index scans do not need to include the dbkeys missing from any
previously completed dbkey lists. Such AND logic operations
of dbkey lists leads to a quick reduction of the complete
1-14 New and Changed Features
dbkey lists' sizes, hence cutting the biggest cost portion
of retrieval, that is, the cost of random data row fetches.
The AND logic operations of dbkey lists is done either by dbkey
bitmap filtering or by direct checking against the stored dbkey
lists in the buffers. The bitmap filtering technique is used
for long dbkey lists which are stored into temporary tables.
Index ranges are scanned interactively in ascending order
of their estimated selectivities. This insures faster dbkey
list reduction, which leads to a faster exclusion of long
index scans with a potential scan cost exceeding the cost
of the final data row fetches based on the shortest complete
dbkey list. To compensate for inaccuracy of the selectivity
estimates, at each iteration, two indexes (current and next)
are scanned simultaneously for a short while. If one of the two
scans completes during this period, the first to finish takes
over; otherwise the scan of the current index of this iteration
continues alone until it succeeds or is rejected.
The background process provides a reliable way for delivering
the shortest dbkey list (the result of AND logical operations)
or selects a sequential scan as the optimal strategy for the
final stage of delivering the data rows. However, when the
cost of providing optimality becomes higher than the profit,
the perfectness of the final strategy is sacrificed in favor
of balancing experimental and productive costs. The ultimate
criterion of this self-sensing background optimization is to
minimize the total cost of retrieving the entire collection of
requested data rows. This case of dynamic leaf-level optimiza-
tion is called the background-only total time optimization:
Delivers a list of dbkeys or selects a sequential scan - BgrOnly leaf type
o Fast-first leaf
In contrast to total time optimization, some queries or user
applications may only need to look at the first or first sev-
eral data rows of a potentially large resulting set of rows.
For example, the EXISTS predicate only checks for the presence
of the first data row, and an interactive user typically looks
New and Changed Features 1-15
at the first screen or the first several screens, not thousands
of them, before canceling the query.
In this case, dynamic optimization runs the background process
"borrowing" dbkeys from it, fetches and delivers data rows, and
allows the user or query operators like EXISTS to end the re-
trieval when it is convenient. However, such "borrowing" takes
place for only a limited time because of the potential overhead
of unproductive fetches. Above this limit, the background pro-
cess takes over, stops data delivery, and continues in the most
efficient manner for total time optimization. This method of
optimizing the speed of delivering the first several rows (and
total delivery time when the user does not care to terminate
the query after receiving the first several rows) is called the
fast-first optimization:
Fast-first several rows delivery is required - FFirst leaf type
o Sorted-leaf
The query's ORDERED BY clause or the query's execution strategy
requirements may request a specific order of retrieval when
an index exists that is capable of providing this order. Such
a sorted index is scanned to the end of its range, fetching
and delivering rows while scanning. When other useful indexes
are present, dynamic optimization takes on the form of a par-
allel run of the "sorted" index scan and a background process
based on these other useful indexes. The "sorted" index scan is
called a foreground process because it is visible and control-
lable from outside of the retrieval. Parallelism is imitated by
quick switching between the foreground and background processes
allowing for synchronization of the speed of the two processes.
When the background process completes any of its index scans, a
filter built at this scan is immediately used by the foreground
process for rejecting the dbkeys not in the filter before
fetching a data row. The benefit of foreground filtering can be
substantial because again, row fetches comprise the most costly
part of retrieval. The reason for parallel running is to avoid
the extra background cost when the foreground range is small
1-16 New and Changed Features
and the foreground finishes earlier than the background. This
type of optimization is called sorted dynamic optimization:
Rows are needed in sorted index order - Sorted leaf type
CAUTION
With the sorted-leaf strategy, the execution trace lines
may incorrectly show some extra 'CUT and/or 'ABA words
at the ends of lines.
o Index-only leaf
Quite often an index is available containing all restrictions
and resulting columns within itself. If, in addition to such an
"index-only" index, other useful indexes are also available,
dynamic optimization scans the index-only index in parallel
with a running background process. This is a direct competition
between the two processes. No extra filtering is needed for
an index-only scan because the index contains all the columns
required for full restriction evaluation and therefore does not
need to perform any fetches. The parallel run is limited and is
used only for detecting the first to finish in order not to pay
a very high price for an incorrect selection of a background
strategy versus the foreground index retrieval scan.
On the background side, its quick success is determined with
only a fraction of the total expense, which includes the costly
final stage fetches. Because of this, the "experimental" double
run overhead is still only a relatively small portion of the
cost incurred by an incorrect strategy selection. This type of
optimization is called index-only dynamic optimization:
Only the columns from an index-only key are needed - NdxOnly leaf type
Two new debug flags, in combination with the "S" (access strategy
print) debug flag, allow you to observe the behavior of dynamic
optimization.
New and Changed Features 1-17
o The "E" debug flag prints the execution trace dump that con-
sists of:
- One line at the conclusion or termination of each background
index
- One line at the conclusion or termination of the foreground
index
- One line at the conclusion or termination of the final
phase
In certain situations, any of the preceding lines may be absent
in the "E" debug flag dump, indicating that a given phase was
not needed for a given leaf run. However, at least one line is
always printed.
o The "\" debug flag sets the internal dbkey buffer size to a
very small (testing) value of 10 dbkeys. This allows you to
test dynamic optimization with small tables by forcing the
usage of temporary tables for storing dbkey lists.
WARNING
The "\" debug flag should be used only for testing or
problem solving. It is not intended for production use
because it slows down the system.
Note that the phrase Card=(cardinality-value) appears in the
display output when you perform queries with the access strategy
flag "S" set. The cardinality displays on the Leaf# lines in the
output and provides the values for relations from which rows are
retrieved. For example, it appears as follows:
Leaf#01 FFirst RDB$RELATIONS Card=19
This indicates that the cardinality of (number of records in)
relation RDB$RELATIONS is 19.
1-18 New and Changed Features
For more information on dynamic optimization, see Chapter 17 on
the query optimizer and RDMS$DEBUG_FLAGS in the VAX Rdb/VMS Guide
to Database Maintenance and Performance.
1.4 Other Optimizer Enhancements
Section 1.4.1 through Section 1.4.4 describe enhancements to the
query optimizer.
1.4.1 More Efficient Strategies for Queries Whose WHERE Expression
Has OR Booleans
The optimizer will generate more efficient strategies for queries
whose WHERE expression has OR Booleans. In particular, the same
efficient strategies will be produced independent of how the join
Booleans are specified with respect to the OR Booleans within
the WHERE expression. For instance, the same strategies will be
produced for the following two queries whose WHERE expressions are
in different form but are equivalent.
SELECT *
FROM EMPLOYEES E, SALARY_HISTORY S
WHERE (E.EMPLOYEE_ID = S.EMPLOYEE_ID AND E.EMPLOYEE_ID = '00166')
OR
(E.EMPLOYEE_ID = S.EMPLOYEE_ID AND E.EMPLOYEE_ID = '00200')
SELECT *
FROM EMPLOYEES E, SALARY_HISTORY S
WHERE E.EMPLOYEE_ID = S.EMPLOYEE_ID AND (E.EMPLOYEE_ID = '00166'
OR
E.EMPLOYEE_ID = '00200')
Before V4.0, the strategies produced for these two queries were
different and highly dependent on how join Booleans were speci-
fied in relation to OR Booleans within the WHERE expression. The
strategy produced for the first query whose WHERE expression is
in disjunctive normal form (DNF) was highly inefficient. In V4.0,
New and Changed Features 1-19
the optimizer factors the common join Booleans out of the OR ex-
pression, thus producing a more efficient strategy for the first
query.
1.4.2 Improved Performance for Tables Stored in Mixed Format Storage
Areas
Starting with V4.0, for a table stored in a mixed format storage
area the optimizer will consider each of its sorted indexes as a
potential index retrieval method. This is done to avoid a sequen-
tial scan of the table, which causes each page in the mixed format
area to be accessed.
As an example, consider the following query:
SELECT LAST_NAME FROM EMPLOYEES;
In versions prior to V4.0, a sequential retrieval of the EMPLOYEES
table was done. This was because the optimizer did not consider
an index as useful if none of its index segments was referenced
in the query. Therefore, for the preceding query, the index EMP_
EMPLOYEE_ID was never used to retrieve data from the EMPLOYEES
table.
For the same query in V4.0, the optimizer chooses the EMP_
EMPLOYEE_ID index to do index retrieval of the EMPLOYEES table.
However, if the size of the table is large relative to the size of
the mixed storage area, then the optimizer may choose sequential
scan of the table as the optimal method of retrieval.
1.4.3 Optimization and Aggregates
The following changes have been made to aggregates:
o Aggregates are joined at optimal positions in the solution
plan.
1-20 New and Changed Features
Aggregates are now joined at the outermost possible position
in the solution plan, allowing faster execution of queries
involving aggregates that return invariant values, or have
dependencies on contexts that are outermost to the query in
which they are specified. This effectively un-nests many cases
of nested aggregates.
o Queries involving numerous aggregates are optimized faster
Queries involving many aggregation functions, for instance
printing a table with numerous COMPUTED BY fields whose value
expressions contain aggregates, now optimize within a reason-
able amount of time (instead of hours).
o ANY and UNIQUE Booleans use retrieval limits
The ANY class Booleans (RDO ANY; SQL ANY, SOME, and ALL) now
terminate the query on the first row that matches the Boolean
query criteria; and the RDO UNIQUE Boolean now terminates the
query on the second matching row (it previously selected all
matching rows and evaluated count against 0 or 1). The use of
the limit retrievals can be seen in strategy dumps where the
aggregate is now represented as:
for ANY : Aggregate-F1
for UNIQUE : Aggregate-F2
o Better match strategies are produced when matching aggregates
The match strategy with inner leg as the aggregate eliminates
the need for a temporary table when not sorting, and uses zig-
zag matching with any index that can be used for the match
criteria.
New and Changed Features 1-21
1.4.4 Constraint Evaluation
The following improvements have been made to constraint evalua-
tion:
o Constraint evaluation on a MODIFY or UPDATE clause
Rdb/VMS evaluates constraints on a MODIFY or UPDATE clause
only when the column values change. Rdb/VMS no longer evaluates
constraints if the columns referenced by the constraints have
not actually been modified.
o Constraints consume less compilation and execution resources
Constraint compilation for any given update request now takes
up to 50% less time, and the resultant constraint execution
structures take up to 50% less virtual memory. In addition, the
number of constraints queued for deferred execution (CHECK ON
COMMIT) is frequently less, by a factor of the number of rows
updated per the request.
o Constraints execute reliably
Prior to V4.0, constraints might not be executed as required,
could be executed too many times, and too many constraints
might be selected or executed (see Chapter 2 for details).
o FOREIGN KEY constraints optimized for verb time evaluation
Updates and deletions to PRIMARY KEY values referenced by
FOREIGN KEY constraints no longer cause a full scan of the
PRIMARY KEY table when the FOREIGN KEY constraint is executed
at verb time. Such constraints are optimized to compare the
FOREIGN KEY column values against only the old values of the
PRIMARY KEY columns. This optimization is not available for
constraints whose evaluation is deferred until SQL statement
end or COMMIT.
o DELETE CONSTRAINT 'active query' diagnostic was misleading
1-22 New and Changed Features
Attempts to delete a constraint in RDO might return an excep-
tion indicating that queries involving a table referenced in
the constraint were active. Although this situation can oc-
cur, the far more common case is that the constraint occurs
in the list of constraints deferred till COMMIT time, and the
transaction must first be terminated before the constraint can
be deleted. This situation is indicated by the following new
exception.
CMTCONNOTDEL cannot delete constraint 'constraint-name'
queued for COMMIT evaluation
1.5 Journaling of Metadata Updates
Most metadata updates are journaled in both RUJ and AIJ files and
execute in a read/write transaction (including storage area and
schema updates)
These metadata updates complete the transaction with an explicit
COMMIT or ROLLBACK statement except for storage area and schema
updates, which complete with an implicit commit operation.
NOTE
In V4.0, if no AIJ records for a transaction are submitted
to the AIJ file, a rollback record is not written. In ver-
sions previous to V4.0, any write operation to a database
page followed by a rollback would have written a rollback
record for this transaction to the AIJ file.
The following changes to the database parameters are not jour-
naled:
o Changing the number of users
o Changing the number of nodes
o Adding or deleting a storage area
New and Changed Features 1-23
o Changing the name of the AIJ file
NOTE
If you plan to change any of the database parameters that
are not journaled in your database application, Digital
recommends that you back up your database before attempting
these changes. This is because if the change fails for some
reason while not being journaled, the database will become
corrupt on a rollforward after a restore operation. If you
have backed up your database, you can restore your database
from the backup copy.
Once you have made these changes, it is necessary to
backup your database again in case you have to restore
your database from the backup at some time in the future.
1.6 Multiple Segmented String Storage Areas
You can now define multiple storage areas to be used for segmented
strings, specifying some areas as read, some as write, and some
as read/write. You can specify segmented strings to be stored in
tables or as separate fields in storage areas using the storage
map statement.
The following is an example of the SQL syntax for this feature:
CREATE STORAGE MAP name
STORE LISTS IN area1 FOR (TABLE1,TABLE2)
IN area2 FOR (table1.column)
IN RDB$SYSTEM;
The RDO syntax for this feature is:
1-24 New and Changed Features
DEFINE STORAGE MAP name FOR SEGMENTED STRINGS
STORE
WITHIN area1 FOR relation1, relation2;
WITHIN area2 FOR relation1.field;
RDB$SYSTEM
END.
In the preceding examples, segmented strings for relation1
and relation2 are stored within area1, segmented strings for
table1.column or relation1.field are stored in area2, and other
segmented strings are stored in the RDB$SYSTEM storage area. Note
that the default storage area selected with the SQL CREATE SCHEMA
or RDO DEFINE DATABASE statement must be named and must remain the
default.
If a relation is named for more than one storage area, Rdb/VMS
selects an area at random.
If multiple areas do not have any relations specified, the storage
area to be used is selected at random. However, RDB$SYSTEM or
whatever area was specified as the default at database creation is
used for the system relations' segmented strings.
Options normally used by storage maps, such as ENABLE COMPRESSION
and WITH LIMIT OF clauses cannot be used with multiple storage
area segmented strings.
The CHANGE STORAGE MAP statement can delete an area only if there
are no segmented strings in the area. You cannot move a segment
from one area to another. Changing the area or areas assigned to
a relation has no effect on current segmented strings, only on
future storage.
New and Changed Features 1-25
1.7 Lock Timeout Mechanism
To help avoid distributed deadlock, Rdb/VMS provides the new
logical name, RDM$BIND_LOCK_TIMEOUT_INTERVAL, to set the amount of
time a transaction waits for locks to be released.
Another method that Rdb/VMS provides to set the amount of time a
transaction waits for locks to be released is the WAIT <interval>
clause of the SET TRANSACTION or DECLARE TRANSACTION statement.
See the VAX Rdb/VMS Guide to Distributed Transactions for more
information.
1.8 Compressed Indexes
Work has been done to improve the functionality of Rdb/VMS sorted
and hashed indexes. As a result, Rdb/VMS is able to provide
smaller index nodes. There are three new types of index compres-
sion that are possible:
o Compressed key suffixes in indexes
Users can now specify that the "first n" characters of a cer-
tain key are to be used in the index. For example, to place an
index on a 100-byte field that is generally unique to the first
20 bytes, you could specify the first 20 bytes and save as much
as 80 bytes per entry.
Compressed key suffixes also enable the user to use fields
longer than 254 characters as index keys.
o Null bit bitwise compression
Rdb/VMS uses only a single bit (the lowest bit of the null
byte) to maintain null bit information within a database.
o Numeric integers bitwise compression
1-26 New and Changed Features
Rdb/VMS has reduced the number of bits needed to store indexes
of all-numeric fields by translating the field values into a
more compactly encoded form.
Index compression results in the following benefits:
o Much lower storage requirements for some applications
o Fewer I/O operations to retrieve data, since more user index
nodes may be included in buffers
o More efficient index-only retrieval, since more data may rea-
sonably be included in an index
In SQL, to create a compressed index for columns that use the CHAR
or VARCHAR data types, use the SIZE IS clause of the CREATE INDEX
statement for the column or columns being indexed, as shown in the
examples. Also, to create a compressed index on CHAR or VARCHAR
data type columns, you must not use the unique clause, which is
the default.
In SQL:
CREATE INDEX PS_DESCRIPTION ON PRODUCT_SCHEDULE
(PRODUCT_DESCR SIZE IS 30);
In RDO, to create a compressed index for columns that use the TEXT
or VARYING STRING data types, use the SIZE IS clause of the DEFINE
INDEX statement for the column or columns being indexed, as shown
in the examples. Also, to create a compressed index on TEXT or
VARYING STRING data type columns, you must use the DUPLICATES ARE
ALLOWED clause, which is the default.
In RDO:
New and Changed Features 1-27
DEFINE INDEX PS_DESCRIPTION
DESCRIPTION IS
/*
Product description starts with
30 characters of unique identification
*/
FOR PRODUCT_SCHEDULE.
PRODUCT_DESCR SIZE IS 30.
END.
In both SQL and RDO, the column size must be the same length or
greater in length than the value specified in the SIZE IS clause.
In the RDO example at the end of this section, the COUNTRY and
CITY columns must be of data type TEXT or VARYING TEXT, and they
must be equal to or larger than 10 and 5 characters in length,
respectively.
In SQL, to create a compressed index for columns that use TINYINT,
SMALLINT, and INTEGER data types, use the MAPPING VALUES clause
of the CREATE INDEX statement for the column or columns being
indexed, as shown in the examples. You can use the UNIQUE clause
with an integer compressed index. In the examples, PRODUCT_ID,
YEAR_NUMBER, and PRODUCT_DESCR are the three columns that are
defined with the UNIQUE clause.
In SQL:
CREATE UNIQUE INDEX PS_DATE_2 ON PRODUCT SCHEDULE
(PRODUCT_ID,
YEAR_NUMBER MAPPING VALUES 1970 to 2070,
PRODUCT_DESCR SIZE is 20);
In RDO, to create a compressed index for columns that use SIGNED
BYTE, SIGNED WORD, and SIGNED LONGWORD data types, use the MAPPING
VALUES clause of the DEFINE INDEX statement for the column or
columns being indexed, as shown in the examples. You can use
the DUPLICATES ARE NOT ALLOWED clause with an integer compressed
index. In the examples, PRODUCT_ID, YEAR_NUMBER, and PRODUCT_DESCR
are the three columns that are defined with the DUPLICATES ARE NOT
ALLOWED clause.
1-28 New and Changed Features
In RDO:
DEFINE INDEX PS_DATE_2 FOR PRODUCT_SCHEDULE
DUPLICATES ARE NOT ALLOWED.
PRODUCT_ID.
YEAR_NUMBER MAPPING VALUES 1970 to 2070.
PRODUCT_DESCR SIZE IS 20.
END.
Note that you can mix mapped and unmapped columns, but the most
storage space is gained by building indexes of multiple columns of
data type in SQL of SMALLINT and INTEGER or in RDO of SIGNED WORD
or SIGNED LONGWORD. Rdb/VMS attempts to pack all such fields into
the smallest possible space.
The following additional RDO examples define several text and
integer compressed indexes.
!
! Define the integer compressed index PORT_NUM_CINDEX on the PORT_NUM
! field of the PORT relation:
!
DEFINE INDEX PORT_NUM_CINDEX FOR PORT DUPLICATES NOT ALLOWED.
PORT_NUM MAPPING VALUES 90 TO 1000.
END.
!
! Define the text compressed index PORT_COUNTRY_CITY_INDEX on the
! COUNTRY and CITY fields of the PORT relation:
!
DEFINE INDEX PORT_COUNTRY_CITY_CINDEX FOR PORT.
COUNTRY SIZE 10.
CITY SIZE 5.
END.
New and Changed Features 1-29
!
! Produce a list of ports sorted by COUNTRY and CITY name from
! the PORT relation:
!
FOR P IN PORT SORTED BY P.COUNTRY, P.CITY PRINT P.* END_FOR
!
! Define the integer compressed index CHANNEL_DEPTH_CINDEX on the
! CHANNEL_DEPTH field of the PORT relation:
!
DEFINE INDEX CHANNEL_DEPTH_CINDEX FOR PORT.
CHANNEL_DEPTH MAPPING VALUES 20 TO 100.
END.
!
! Define the integer compressed index DISTANCE_CINDEX on the
! DISTANCE field of the PORT relation:
!
DEFINE INDEX DISTANCE_CINDEX FOR PORT.
DISTANCE MAPPING VALUES 0 TO 30000.
END.
!
! Define the integer compressed index TRANS_COST_CINDEX on the
! TRANS_COST field of the PORT relation:
!
DEFINE INDEX TRANS_COST_CINDEX FOR PORT.
TRANS_COST MAPPING VALUES 0 TO 10000.
END.
!
! Define the integer compressed index PNUM_CDEP_DIST_TCOST_CINDEX
! on the PORT_NUM, CHANNEL_DEPTH, DISTANCE, and TRANS_COST fields
! of the PORT relation:
!
DEFINE INDEX PNUM_CDEP_DIST_TCOST_CINDEX FOR PORT
DUPLICATES NOT ALLOWED.
PORT_NUM MAPPING VALUES 100 TO 1000.
CHANNEL_DEPTH MAPPING VALUES 20 TO 100.
DISTANCE MAPPING VALUES 0 TO 30000.
TRANS_COST MAPPING VALUES 0 TO 10000.
END.
1-30 New and Changed Features
!
! Define the CHANNEL_DEPTH_DESC_CINDEX index on the CHANNEL_DEPTH
! field of the PORT relation. The DESCENDING keyword causes descending
! index segments to be created for the CHANNEL_DEPTH field.
!
DEFINE INDEX CHANNEL_DEPTH_DESC_CINDEX FOR PORT.
CHANNEL_DEPTH DESCENDING MAPPING VALUES 20 TO 100.
END.
1.9 New Data Type-SQL TINYINT and RDO SIGNED BYTE
A new data type, TINYINT (SQL) and SIGNED BYTE (RDO), has been
added to Rdb/VMS. This data type occupies a single byte and can
be used for data with small ranges and limited values. The values
(unscaled) must fall in the range -128 through 127.
Some examples of columns where this data type can be useful in-
clude:
o Ages
o Department codes
o Status information
o Tax rates (if range is 0.00 to 12.7)
o Number of children
o Mortgage payment 'days of grace'
VAX CDD/Plus does not currently support SIGNED BYTE for Rdb/VMS
databases. Therefore, you must define the field while attached
by FILENAME. This will be corrected in a version of VAX CDD/Plus
after Version 4.2.
Some example definitions using this new data type in SQL and RDO
are as follows:
New and Changed Features 1-31
In SQL:
CREATE DOMAIN DEPARTMENT_CODE TINYINT DEFAULT -128;
CREATE DOMAIN STATUS TINYINT;
CREATE DOMAIN TAX_RATE TINYINT(1);
CREATE TABLE PAYMENT_DUE
(ACCOUNT ACCOUNT_NUMBER,
AMOUNT MONEY,
DAYS_OF_GRACE TINYINT,
CHECK(DAYS_OF_GRACE BETWEEN 0 AND 31));
In RDO:
DEFINE FIELD AGE
DATATYPE SIGNED BYTE
VALID IF (AGE > 0) AND (AGE NOT MISSING).
DEFINE FIELD NUMBER_CHILDREN
DATATYPE SIGNED BYTE
VALID IF (NUMBER_CHILDREN >= 0) OR (NUMBER_CHILDREN MISSING).
DEFINE FIELD DEPARTMENT_CODE
DATATYPE SIGNED BYTE SCALE 1
MISSING_VALUE -1280.
1.10 EXPORT Now Supports the DATA Option
A new option has been added to the SQL and RDO EXPORT statements.
o DATA and NO DATA
Export allows a database to be exported without the data. This
new feature of the EXPORT statement simplifies the task of
creating a duplicate copy of a database, and it allows a DBA to
experiment with storage areas and storage maps.
In SQL, the new NO DATA option can be specified with either the
EXTENSIONS or NO EXTENSIONS option. The default option is DATA.
1-32 New and Changed Features
SQL> EXPORT SCHEMA
cont> FILENAME db-root-file
cont> INTO rbr-file
cont> WITH EXTENSIONS, NO DATA;
In RDO, the new NODATA option can be specified with either the
EXTENSIONS or NOEXTENSIONS option. The default option is DATA.
RDO> EXPORT db-root-file
cont> INTO rbr-file
cont> WITH EXTENSIONS, NODATA
1.11 IMPORT Now Supports DATA and TRACE Options
New options have been added to the SQL and RDO IMPORT statements.
o DATA and NO DATA
The IMPORT NO DATA option is similar to the EXPORT NO DATA op-
tion. The IMPORT NO DATA option allows a database administrator
to create an empty database with the same characteristics as an
existing database.
IMPORT reads the interchange file (RBR) and skips over any
data in the file. The resulting database is useful for testing
IMPORT options and creating testing databases.
The default is DATA.
In SQL:
SQL> IMPORT SCHEMA
cont> FROM PERSONNEL.RBR
cont> FILENAME PERSONNEL
cont> NO DATA;
In RDO:
RDO> IMPORT PERSONNEL.RBR INTO PERSONNEL
cont> NOACL NOBATCH_UPDATE NODATA
cont> END IMPORT.
New and Changed Features 1-33
o TRACE and NOTRACE
Several phases of the IMPORT operation require disk I/O op-
erations and can be time consuming. The significant phases
include:
- Loading the data from the RBR file
This phase requires I/O operations to the RBR file, the
database storage areas, and possibly index lookups if the
PLACEMENT VIA INDEX clause is used in a storage map.
- Defining indexes
This phase requires reading data from the database and
sorting it before creating the index.
- Defining constraints
When constraints are defined they must be validated against
the existing data that required I/O operations to the
database storage areas.
The TRACE option allows the database administrator to deter-
mine which phase and which entities require the most resources.
After each relation, index, and constraint is defined statis-
tics are displayed showing page faults, CPU time, and direct
I/O operations used. A final statistics line summarizes the
total disk I/O operations used and the total time of the IMPORT
operation.
The default for the IMPORT statement is NO TRACE.
In SQL:
SQL> IMPORT SCHEMA
cont> FROM PERSONNEL.RBR
cont> FILENAME PERSONNEL
cont> DATA TRACE;
1-34 New and Changed Features
In RDO:
RDO> IMPORT PERSONNEL.RBR INTO PERSONNEL
cont> TRACE
cont> END IMPORT.
1.12 Rdb/VMS V4.0 Gives Special Treatment for CDD/Plus Dictionaries
Three changes have been made to Rdb/VMS V4.0 to better support
CDD/Plus dictionary databases.
o CDD/Plus flags new databases
CDD/Plus dictionary databases (CDD$DATABASE.RDB) are now
flagged as special databases by Rdb/VMS.
o Changes to the EXPORT statement
This flag is saved in the interchange file (RBR) by the SQL and
RDO EXPORT operation.
The EXPORT statement option WITH NOEXTENSIONS is not permitted
for CDD/Plus dictionary databases. Attempts to use the WITH
NOEXTENSIONS option result in an error message as shown in
the next example. (Using this option on CDD/Plus dictionaries
caused numerous CDD/Plus dictionary database corruptions be-
cause the NULL bit settings were lost by this type of EXPORT
operation.)
The EXPORT option WITH NODATA is also not permitted for
CDD/Plus dictionary databases. Attempts to use the WITH NODATA
option result in an error message as shown in the next example.
The metadata without the data is not useful for CDD/Plus.
o Changes to the IMPORT statement
This flag is restored by the SQL and RDO IMPORT operation.
New and Changed Features 1-35
If the flag is set, then the IMPORT NODATA option is illegal.
This will prevent creation of unusable dictionary databases.
NOTE
CDD/Plus dictionary databases created prior to V4.0 are not
flagged as special databases. A future version of CDD/Plus
will set this flag as part of its upgrade procedure.
The following examples show error messages generated if illegal
operations are attempted on CDD/Plus dictionary databases.
$ RDO
!
! Try to do an EXPORT with NODATA (Should FAIL with RDO-F-EXP_CDD_NODATA)
!
EXPORT TEST$CDD:CDD$DATABASE.RDB INTO TEST_EXPORT.RBR WITH NODATA
%RDO-F-DELBACKUP, EXPORT errors, interchange file deleted
-RDO-F-EXP_CDD_NODATA, Exporting a CDD dictionary with NODATA is not allowed
!
! Try to do an EXPORT with NOEXTENSIONS (Should FAIL with RDO-F-EXP_CDD_NOEXT)
!
EXPORT TEST$CDD:CDD$DATABASE.RDB INTO TEST_EXPORT.RBR WITH NOEXTENSIONS
%RDO-F-DELBACKUP, EXPORT errors, interchange file deleted
-RDO-F-EXP_CDD_NOEXT, Exporting a CDD dictionary with NOEXTENSIONS is
not allowed
!
! Try to do an EXPORT with DATA (Should SUCCEED) and then try and
! do an IMPORT
!
EXPORT TEST$CDD:CDD$DATABASE.RDB INTO TEST_EXPORT.RBR WITH DATA, EXTENSIONS
1-36 New and Changed Features
!
! IMPORT the database with NODATA (Should FAIL with RDO-F-IMP_CDD_NODATA)
!
IMPORT TEST_EXPORT.RBR
INTO TEST$SCRATCH:TEST_IMPORT.RDB
DICTIONARY IS NOT USED
NODATA
END IMPORT.
Exported by Rdb/VMS V4.0-0 Export/Import utility
A component of Rdb/VMS V4.0-0
Previous name was TEST$CDD:CDD$DATABASE.RDB
It was logically exported on 16-APR-1990 17:00:10.95
Database page size is 8 blocks
Database NUMBER OF USERS is 200
Database NUMBER OF VAXCLUSTER NODES is 16
Database NUMBER OF DBR BUFFERS is 20
Database SNAPSHOT is ENABLED
Database SNAPSHOT is IMMEDIATE
Database BUFFER SIZE is 24 blocks
Database NUMBER OF BUFFERS is 30
IMPORTing STORAGE AREA: RDB$SYSTEM
%RDO-F-IMP_CDD_NODATA, Importing a CDD dictionary with NODATA is not allowed
1.13 SHOW STORAGE AREA Shows a Different Storage Area Allocation
In SQL and RDO, the SHOW STORAGE AREA statement now displays
the current maximum area allocation for the storage area, not
the initial allocation as in previous releases of Rdb/VMS. This
provides the database administrator with a much better picture of
storage area disk usage.
New and Changed Features 1-37
1.14 Setting Line Lengths for a File or Output Device
In SQL and RDO, you can specify the default line length in the
interactive interfaces for output from the SQL SELECT and RDO
PRINT statements by using either the SQL SET LINE LENGTH or RDO
SET LINE_LENGTH statement.
RDO> SET LINE_LENGTH n !underscore
SQL> SET LINE LENGTH n !no underscore in SQL
This statement allows you to specify an alternate line length for
a file or alternate output device.
1.15 Changes to Run-Unit Journal (RUJ) Files
The following changes have been made to RUJ file default creation:
o RUJ file naming convention changed
The RUJ file naming convention is changed to:
<database_name>$<timestamp generated number>.RUJ
This prevents multiple versions of RUJ files from using the
same file name and the possibility of RUJ files being lost due
to a random purge of directories.
o RUJ file placement changed
RUJ file placement no longer defaults to SYS$LOGIN. The RUJ
files now default to their own top level directory [RDM$RUJ]
on the device in which SYS$LOGIN is defined. This alleviates
the problem of RUJ files being scattered around in users'
directories. If a system manager wishes to change the default
placement semantics, the RDMS$RUJ logical name can be defined
systemwide to SYS$LOGIN, and the "old" placement schemes used
by Rdb/VMS versions previous to V4.0 are used.
1-38 New and Changed Features
The RDMS$RUJ logical name used for RUJ placement has not
changed. The [RDM$RUJ] directory is created automatically by
the first user on the disk that creates an RUJ file. It will
remain there indefinitely. It is owned by the [SYSTEM] iden-
tifier. Be certain that the [SYSTEM] identifier has write (W)
and execute (E) privileges to create this top level directory
[RDM$RUJ]. You can create ACLs on it if you wish, as the users
are given privileges internal to Rdb/VMS to create or delete
RUJ files. The RUJ files created within the directory are owned
by the individual users.
CAUTION
In Section 9.3 of the VAX Rdb/VMS Guide to Database
Maintenance and Performance, information is presented de-
scribing what you can do if you have lost your AIJ file
because of a disk drive problem. A solution is documented
suggesting you can create a dummy AIJ file in the direc-
tory where the root file says it should be located to get
your database up and running again. Though not documented,
this method can also be used for missing RUJ files in ver-
sions previous to V3.1. The risk involved in this method is
that your database may be left inconsistent and no message
displays to indicate this fact.
Beginning with V3.1 this technique is no longer sup-
ported because it could leave your database inconsis-
tent. Beginning with V4.0, RMU/ALTER has syntax (see
Section 3.9.21) to support this technique as a workaround.
However, the database is marked as "eternally corrupt." The
only method of clearing an "eternally corrupt" database is
to restore and recover from a clean backup and AIJ file.
New and Changed Features 1-39
1.16 Change to Batch-Update and Exclusive Transaction Behavior
The following change has been made to batch update and exclusive
transaction behavior:
o Batch and exclusive update transactions cannot be started if
RDB$SYSTEM is read-only. When a transaction reserves a relation
in exclusive or batch-update mode and the SYSTEM area is read-
only, you now receive the error message:
BATCH or EXCLUSIVE UPDATE access is not allowed if RDB$SYSTEM is READ ONLY
Because Rdb/VMS now does not allow exclusive transactions
from starting when the database is read-only, active read-only
transactions can proceed without being affected.
See Section 2.1.39 for more information.
1.17 DECtrace Support for Trigger and Constraint Statistics
Rdb/VMS has provided DECtrace with support for trigger and con-
straint statistics.
1.18 SQL: New and Changed Features and Statements
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.
See information in Section 1.1 through Section 1.17 for additional
new and changed SQL features.
1.18.1 Support for the Two-Phase Commit Protocol
SQL supports the two-phase commit protocol. See Section 1.1
for more information. See the VAX Rdb/VMS Guide to Distributed
Transactions for more information.
1-40 New and Changed Features
1.18.2 New SQL Syntax
The following SQL statements and language elements are new for
Rdb/VMS V4.0:
o ALTER TABLE and CREATE TABLE
The ALTER TABLE and CREATE TABLE statements accept the new
COMPUTED BY clause, which associates a value expression with a
column name in a table definition.
o BETWEEN predicate
SQL now evaluates the BETWEEN predicate to comply with the
ANSI/ISO standard. In the following example, you must specify
the lower value (value2) before the higher one (value3).
value1 BETWEEN value2 AND value3
o LIMIT TO argument
The LIMIT TO argument has been added to the select-expression
clause of the SQL SELECT statement and the SQL DECLARE CURSOR
statement.
o COMPUTED BY clause
The COMPUTED BY clause has been added to the SQL CREATE TABLE
statement and the SQL ALTER TABLE statement.
o CURRENT_TIMESTAMP user literal
You can use the CURRENT_TIMESTAMP user literal anywhere that
an expression is allowed in SQL to specify the date and time
currently defined in Rdb/VMS.
The following example of an SQL command inserts the date into
the JOB_START field of an employees record:
SQL> INSERT INTO EMPLOYEES(JOB_START ...)
VALUES (CURRENT_TIMESTAMP, ...);
o CREATE VIEW and DECLARE CURSOR
New and Changed Features 1-41
The CREATE VIEW and DECLARE CURSOR statements accept the new
LIMIT TO argument, which limits the number of rows in the
result table.
1-42 New and Changed Features
o DECLARE CURSOR Statement, Extended Dynamic
SQL Version 4.0 documentation includes a new section on the
extended dynamic DECLARE CURSOR statement. In Version 3.1
and earlier documentation, this statement was included in the
description and syntax diagram in the section on DECLARE CURSOR
Statement, Dynamic.
o DECLARE TRANSACTION and SET TRANSACTION
The DECLARE TRANSACTION and SET TRANSACTION statements accept
the new WAIT <interval> clause. The WAIT <interval> clause lets
you set the amount of time a transaction waits for locks to be
released, to help you avoid distributed deadlock.
o Segmented string support
The following SQL statements have new syntax that lets you
manipulate lists, the SQL equivalent of segmented strings:
- DECLARE CURSOR
- Dynamic DECLARE CURSOR
- FETCH
- INSERT
- OPEN
These statements and the Extended Dynamic DECLARE CURSOR state-
ment also have new syntax that lets you distinguish between
read-only cursors, insert-only cursors, and update cursors.
For information about when to use each cursor mode, see the VAX
Rdb/VMS SQL Reference Manual.
The sample program, SQL$RESUMES.SCO, demonstrates the use of
lists. Using SQL$RESUMES, a user can load resumes from a file
into the database, and then later display the resume. You can
find this program in RDM$DEMO.
New and Changed Features 1-43
Additional details about lists and list cursors appears in
Section 1.18.5.
o SELECT (select expression clause)
The SELECT statement accepts the new LIMIT TO argument, which
limits the number of rows in the result table.
o SET DEFAULT CONSTRAINT MODE
The SET DEFAULT CONSTRAINT MODE statement allows you to set
the default for constraint evaluation. After you execute this
statement, any transactions that are started have the initial
constraint evaluation mode you set. Before you issue this
statement, the default mode is set to off.
SET DEFAULT CONSTRAINT [ MODE ] { ON | OFF}
1-44 New and Changed Features
o SHOW CONSTRAINT MODE
This statement displays the default setting for constraint
evaluation. If there is a current transaction, the SHOW
CONSTRAINT statement displays the constraint evaluation mode
for the current transaction.
1.18.3 String Concatenation Operator
The SQL string concatenation operator (|), is an arithmetic oper-
ator that lets you link two value expressions with character data
types. The value expressions must be one of the three character
data types: CHAR, VARCHAR, or LONG VARCHAR.
If the value of either of the concatenated expressions is null,
then the result of the concatenation is also null. Otherwise the
result of the concatenation is the value of the first expression
followed by the value of the second expression. If the resulting
string exceeds 65535 bytes (the maximum string length SQL allows),
SQL signals a truncation error, unless all of the characters to
the right of the 65535th character are spaces. In this case, the
result will be the leftmost 65535 characters.
1.18.4 Substring Manipulation
You can use substrings to manipulate portions of character value
expressions. For example, you can use substrings to select records
in which the value in a column matches a certain number of charac-
ters in the substring. A substring must have the data type CHAR,
VARCHAR, or LONG VARCHAR.
To specify a substring, you must specify the value expression,
and the FROM keyword, followed by the start position of the value
expression. You can optionally add a FOR clause after the FROM
clause to specify the length of the value expression after the
start position.
New and Changed Features 1-45
If you specify a length longer than the string, SQL returns only
valid characters in the string, and terminates the returned sub-
string after the last valid character.
If either operand of the substring is the null value, the result-
ing value is also null.
The following example uses a substring in the WHERE clause of a
SELECT statement. One of the SELECT statement conditions is that
four characters starting at position nine must equal the string
"Math", which is extracted using the substring feature.
SQL> SELECT * FROM DEGREES WHERE SUBSTRING(DEGREE_FIELD
cont> FROM 9 FOR 4) = 'Math' AND YEAR_GIVEN > 1980;
1.18.5 SQL Supports Segmented Strings
The SQL data type LIST of BYTE VARYING, like the RDO data type
SEGMENTED STRING, lets you handle large data objects with a seg-
mented internal structure.
List cursors enable users to scan a very large data structure from
within a language that does not provide support for objects of
such size. Because lists exist as a set of elements within a row,
a cursor must refer to a table cursor because the table cursor
provides the row context.
When you specify a table cursor that provides row context for a
list cursor, you must be sure to specify the column name, as shown
in the following example:
DECLARE ONE INSERT ONLY TABLE CURSOR FOR SELECT EMPLOYEE_ID, RESUME
FROM RESUMES;
DECLARE TWO INSERT ONLY LIST CURSOR FOR SELECT RESUME
WHERE CURRENT OF ONE;
1-46 New and Changed Features
This feature allows dynamic users to get all metadata for all
columns, which contrasts with previous versions of Rdb/VMS when
this was not the case. If you fetch a table cursor that references
a list cursor column name, SQL returns the segmented string ID.
NOTE
The value returned is not guaranteed to be valid across all
releases. Segmented string IDs cannot be used outside of
the scope of the cursor.
You can see the contents of the list column if you open and fetch
the appropriate list cursor after fetching the table cursor. (Be
sure to close the list cursor before you attempt to fetch a new
row from the table cursor.) For more information on this, see
Section 3.2.5.
You must check the indicator from the table cursor fetch to find
out if a list is null. SQLCODE returns end-of-stream on the first
fetch of a null list.
You should be aware of the following features:
o Read-only table cursors are valuable in dynamic and interactive
statements because they do not retrieve database keys.
1.18.6 Computed Columns in Tables
In SQL you can associate a value expression with a column name in
a table definition.
o In the CREATE TABLE statement, SQL now allows a value expres-
sion in a table's column definition (the clause where you spec-
ify either base data type or domain name). Specify each value
expression with the keywords COMPUTED BY.
o In the ALTER TABLE statement, you can specify a value expres-
sion in the add-col-definition clause. However, you cannot
specify a value expression in the alter-col-definition clause.
New and Changed Features 1-47
You can use the COMPUTED BY clause to create a table with one
or more computed columns or to alter a table to add one or more
computed columns.
Examples of computed columns are as follows:
SQL> CREATE TABLE TABLE1 (A INT, B INT, C COMPUTED BY A + B, D);
SQL> CREATE TABLE TABLE2 (A INT, C COMPUTED BY (SELECT MAX (B) FROM
TABLE1));
SQL> ALTER TABLE TABLE1 ADD COLUMN D COMPUTED BY B-A;
You cannot refer to a column before you define it. The following
example is invalid:
SQL> CREATE TABLE TABLE3 (C COMPUTED BY (A+B), A INT, B INT)
1.18.7 SQL Changes Values Used in SQLERRD Array
The SQLERRD[x] field of the SQLCA is a zero-based array of six
integers. The only elements of the array that SQL uses are the
second through sixth elements (SQLERRD[1], SQLERRD[2], SQLERRD[3],
SQLERRD[4], and SQLERRD[5]) in the display from SHOW SQLCA. The
remainder of the elements are provided for compatibility with DB2.
SQL puts a value in the third element (SQLERRD[2]) after success-
ful execution of the following statements:
o INSERT: the number of rows stored by the statement
o UPDATE: the number of rows modified by the statement
o DELETE: the number of rows deleted by the statement
o FETCH: the number of the row on which the cursor is currently
positioned
o OPEN: zero
1-48 New and Changed Features
o SELECT: the number of rows in the result table formed by the
SELECT statement (Note: SQLERRD is not updated for dynamic
SELECT statements.)
SQL puts the following values in the second, fourth, fifth, and
sixth elements successful execution of an OPEN statement that
opens a list cursor:
o SQLERRD[1]: Longword length of longest actual segment
o SQLERRD[3]: Longword number of segments
o SQLERRD[4,5]: Two contiguous longwords comprising quadword
number of total bytes in all list segments
SQL puts no meaningful data in the sixth element of the SQLERRD
array after successful execution of a FETCH statement within a
list cursor. For a FETCH statement within a list cursor, you
must pass an indicator parameter to receive information about
the length or truncation of data in segments.
1.18.8 Module Language Record Support
SQL Version 4.0 allows you to pass records and indicator arrays
to SQL module language procedures and retrieve CDD/Plus record
declarations. This new functionality enables ACMS users to write
task definitions that call SQL module procedures directly and
also provides integration for DECforms applications, which pass
information through record structures.
Module language syntax for record support has the following for-
mat.
New and Changed Features 1-49
MODULE -+-------->--------+-> LANGUAGE language-name -+
+-> module-name --+ |
+------------------------------------------------------+
+-> AUTHORIZATION auth-id --+------------>-----------+-+
++-> declare-statement -++ |
+--------- <-----------+ |
+------------------------------------------------------+
++-> PROCEDURE procedure-name +-> param-decl +-> ; sql-statement ; -+->
| +----+------+--+ |
| +-- , <+ |
+--------------------------------<---------------------------------+
param-decl =
-+-> parameter -+-> data-type ---++--------------->-------------+-+->
| +-> domain-name -++-> BY DESCRIPTOR +----->----++ |
| +-> record-type -+ +-> CHECK -+ |
+-> SQLCA -------------------------------------------------------+
+-> SQLCODE -----------------------------------------------------+
+-+--------------+----> SQLDA -----------------------------------+
+-> parameter -+
--> RECORD -++-> item-name --+-> data-type ----+--++-> END RECORD ->
|| +-> record-type --+ ||
|+-------------+--------+-------------+|
| +-- , <--+ |
| |
+--> FROM path-name -------------------+
| |
+--> INDICATOR ARRAY OF ---+ |
+--------------------------+ |
+> array-length -> exact-numeric-type -+
exact-numeric-type=
-------+--> SMALLINT ---+----+--------+----------------+->
+--> INTEGER ----+ +-> n ---+ |
+--> QUADWORD ---+ |
+--> TINYINT ----+ |
+--> DECIMAL --+-+----------------------------+-+
+--> NUMERIC --+ +-> ( --> n -+--------+-> ) -+
+-> , n -+
1-50 New and Changed Features
A record definition cannot contain an SQLDA or an SQLCODE.
You should have at least as many elements in your indicator array
as in the record definition. If a record reference has an indi-
cator, it must be an indicator array. You can only use indicator
arrays on the right side of the INDICATOR keyword. You cannot
use indicator arrays as a record. You cannot refer to individual
elements within an indicator array. For this reason, you cannot
use indicator arrays in UPDATE statements or in WHERE clauses. An
indicator array cannot be contained within another record.
You can use decimal and numeric fields for indicators. The module
language standard for COBOL does not permit binary data types.
Each element name in a record must be different from any other
element name at the same level in the same record declaration.
The path name represents the location of a record description
within the dictionary. A record description cannot contain repeat-
ing items (OCCURS, arrays). It can contain only valid Rdb/VMS or
SQL data types.
When you refer to an element within a parameter declaration in a
procedure, the reference must be fully qualified. In other words,
it must include the parameter name and all intervening element
names, separated with periods.
When you include a CDD/Plus record that contains character strings
in a C module, the module language assumes that the last char-
acter of the string is for the null character. For example, if
a CDD/Plus character string is 10 characters, you can fit only a
9-character SQL field in it in a C module.
1.18.9 SQL C Precompiler Supports VARCHAR Host Variables
The SQL C precompiler now supports VARCHAR host variables. To
declare a VARCHAR, use the following syntax:
$SQL_VARCHAR(max_length_of_varchar)
New and Changed Features 1-51
For example, the following statement defines a VARCHAR variable
that is a word length followed by 10 bytes of data.
$SQL_VARCHAR(10) x, y, z;
1.18.10 SQL Statements Have Been Updated to Support VAX Data
Distributor V2.2
SQL statements in the VAX Rdb/VMS SQL Reference Manual have been
updated to support VAX Data Distributor V2.2.
1.18.11 Diagnostic Messages for Obsolete Features
When you use obsolete SQL syntax, you receive a diagnostic mes-
sage. Appendix H in the VAX Rdb/VMS SQL Reference Manual lists
the obsolete features and those features that supersede them, and
tells how to suppress the diagnostic messages.
1.18.12 Sample Programs in Precompiled SQL and SQL Module Language
Table 1-1 lists the sample SQL programs, whether they are written
in precompiled SQL or SQL module language, and the host languages
in which they are written.
Table 1-1: Sample Programs in Precompiled SQL and SQL Module
___________Language_______________________________________________
Precompiled SQL
SQL Module
Sample_Program_____Ada__COBOL_C__FORTRANPascalPL/I_Language_______
SQL$ALL_ X X X X X X Ada
DATATYPES
SQL$DIST_TRANS X X COBOL, FORTRAN
1-52 New and Changed Features
Table 1-1 (Cont.): Sample Programs in Precompiled SQL and SQL
___________________Module_Language________________________________
Precompiled SQL
SQL Module
Sample_Program_____Ada__COBOL_C__FORTRANPascalPL/I_Language_______
SQL$DYNAMIC Ada, C, PL/I
SQL$INSERT_ COBOL
DEGREES
SQL$LOAD_ X X X
EMPLOYEES
SQL$LOAD_JOBHIST X X X X X Ada
SQL$LOAD_JOBS X
SQL$LOAD_SALHIST X
SQL$MULTI_STMT_DYN Ada
SQL$REPORT X X X X X Ada
SQL$RESUMES X X
SQL$TERMINATE X X X X X Ada, BASIC,
___________________________________________________Pascal_________
1.19 RDO: New and Changed Features and Statements
See information in Section 1.1 through Section 1.17 for additional
new and changed RDO features.
New and Changed Features 1-53
1.19.1 Support for the Two-Phase Commit Protocol
RDO supports the two-phase commit protocol. See Section 1.1
for more information. See the VAX Rdb/VMS Guide to Distributed
Transactions for more information.
1.19.2 RDO Changes for CDD/Plus Compatibility
The RDO SHOW FIELD and SHOW RELATION statements have been enhanced
to provide full path name displays.
1.19.3 RDO SHOW DATABASE Statement Displays the Node Name for Remote
Databases
The RDO SHOW DATABASE statement displays the node name if the
database is accessed remotely. For example:
RDO> DATABASE FILE 'VAXA"SMITH SECRET"::DISK1:[SMITH.DATABASES]PM'
RDO> SHOW DATABASE RDB$DBHANDLE
Database with db_handle RDB$DBHANDLE (default handle)
File: DISK1:[SMITH.DATABASES]PM.RDB;1
This database is accessed remotely on node VAXA
Default segmented string storage area: RDB$SYSTEM
Number of users: 4
Number of nodes: 16
Buffer Size (blocks/buffer): 6
Number of Buffers: 20
Number of Recovery Buffers: 20
Snapshots are Enabled Immediate
Dictionary Not Required
ACL based protections
RDO> Exit
1-54 New and Changed Features
1.20 RDBPRE: New and Changed Features and Statements
See information in Section 1.1 through Section 1.17 for additional
new and changed RDBPRE features.
1.20.1 Support for the Two-Phase Commit Protocol
RDBPRE supports the two-phase commit protocol. See Section 1.1
for more information. See the VAX Rdb/VMS Guide to Distributed
Transactions for more information.
1.20.2 RDBPRE/MESSAGE_MAP Works for BASIC
The /MESSAGE_MAP qualifier of the RDBPRE preprocessor command
line currently works for BASIC. The /MESSAGE_MAP qualifier is
used to declare variables as global, so that a message compiled in
one module is still recognized in a called procedure in another
module.
See Section 1.1 for more information on compiling RDBPRE applica-
tions for distributed transactions.
1.21 RDML: New and Changed Features and Statements
See information in Section 1.1 through Section 1.17 for additional
new and changed RDML features.
1.21.1 Support for the Two-Phase Commit Protocol
RDML supports the two-phase commit protocol. See Section 1.1 and
the RDML Reference Manual for more information.
New and Changed Features 1-55
1.21.2 Support for Internationalization
RDML supports internationalization in the following areas:
o Digital multinational character set (MCS) characters in
database object names
Database object names that can be referred to from RDML include
context variables, relations, fields, and constraint names.
See the RDML Reference Manual for more information.
o Date and time literals appearing in RDML queries
These date and time literals are converted at compile time
using the VMS V5.0 RTL routine LIB$CONVERT_DATE_STRING.
If the logical name SYS$LANGUAGE is set to the appropriate
language (SPANISH in the following example) and appropriate
LIB$DT_INPUT_FORMAT is defined, then RDML compiles the query.
If the language or input format is set incorrectly, RDML gener-
ates an appropriate date conversion error.
FOR E IN EMPLOYEES
WITH E.BIRTHDAY = '1 abril, 1990'
END_FOR
o Date and time support for headers in RDML programs
If the logical name SYS$LANGUAGE is set to the appropriate
language and the logical name LIB$DT_FORMAT is defined, the
date and time appearing in RDML listing files is formatted
using the formats specified in LIB$DT_FORMAT.
1.22 RMU: New and Changed Features and Statements
Rdb/VMS Management Utility (RMU) commands and qualifiers that are
new for Rdb/VMS V4.0 appear in the following sections. See the VAX
Rdb/VMS RDO and RMU Reference Manual for detailed descriptions of
these commands and qualifiers.
1-56 New and Changed Features
1.22.1 RMU/ALTER Command
The RMU/ALTER command invokes the RdbALTER utility, which allows
you to patch a database or move it from one device to another. The
RdbALTER chapter in the VAX Rdb/VMS RDO and RMU Reference Manual
provides syntax and a description of each RdbALTER command. A
tutorial on using the RdbALTER utility is in the VAX Rdb/VMS Guide
to Database Maintenance and Performance. See Section 3.9.21 of the
Release Notes for a description of additional RMU/ALTER features
that are not documented in V4.0.
1.22.2 RMU/BACKUP Command
The RMU/BACKUP command includes the following new and changed
qualifiers:
o /OWNER=user-id
The /OWNER=user-id qualifier specifies the OWNER of the volume
set (the user that is permitted to restore the database). The
"user-id" is a VMS user id. It has the form "[group_name,user_
name]", "[name]","[n,m]","name", or "%Xhhhhhhhh". The default
value for the OWNER is the user's own "user-id". This qualifier
is incompatible with a disk backup. When used with tapes, the
qualifier applies to all continuation volumes, and to the
initial volume only if /REWIND was specified. (If /REWIND
is not specified, the BACKUP command appends the file to a
previously labeled tape, so the first volume of the file may
have a different protection.)
o /TAPE_EXPIRATION=date-time
The /TAPE_EXPIRATION=date-time qualifier specifies the ex-
piration date of the backup file. The default value for this
qualifier is "NOW". That is, the tape may be overwritten imme-
diately. This is also incompatible with disk backup.
o Semantics for the /INCLUDE and /EXCLUDE qualifiers of the
RMU/BACKUP command have changed
New and Changed Features 1-57
If neither the /INCLUDE and /EXCLUDE qualifiers are speci-
fied in an RMU/BACKUP command, a full and complete backup is
performed.
The /INCLUDE, /EXCLUDE, and /INCLUDE/EXCLUDE qualifiers with no
storage area list specified are all equivalent to specifying
neither qualifier. They include all storage areas in the backup
file and exclude none.
The /INCLUDE=area-list qualifier includes only those storage
areas specified in the list and excludes all other storage
areas from the backup file. This means that the qualifiers
/INCLUDE=area-list and /EXCLUDE are equivalent.
The qualifier /EXCLUDE=area-list excludes only those storage
areas specified in the list and includes all other storage ar-
eas. This means that the qualifiers /INCLUDE and /EXCLUDE=area-
list are equivalent.
The qualifiers /EXCLUDE=area-list and /INCLUDE=area-list,
when used together on the same RMU/BACKUP command line, are
considered an error.
o Changes to the /INCREMENTAL qualifier
The RMU/BACKUP/INCREMENTAL command now allows incremental
backups by area. The /INCREMENTAL=BY_AREA qualifier changes
dates. The RMU/BACKUP/INCREMENTAL command determines the extent
of the backup to be performed. The four possible options are:
- /NOINCREMENTAL (full backup)
If you do not specify any of the possible incremental op-
tions, the default is the /NOINCREMENTAL option. With the
/NOINCREMENTAL option, a full and complete backup of the
database is performed.
- /INCREMENTAL
If you specify the /INCREMENTAL option, an incremental
backup of the storage areas that have changed since the
last full backup of the database is performed.
1-58 New and Changed Features
- /INCREMENTAL=COMPLETE
If you specify the /INCREMENTAL=COMPLETE option, you get an
incremental backup of the storage areas that have changed
since the last full and complete backup of the database was
performed. Selecting the /INCREMENTAL=COMPLETE option is the
same as selecting the /INCREMENTAL option.
- /INCREMENTAL=BY_AREA
The /INCREMENTAL=BY_AREA option determines the time window
in which changes to the database should be included in the
backup.
/INCREMENTAL=COMPLETE means from last full and com-
plete backup. /INCREMENTAL=BY_AREA means from last full
area backup independent of whether it was complete. The
/INCREMENTAL=BY_AREA option backs up those database pages
that have changed in each selected storage area since the
last full backup of the area. The last full backup of the
area is the later of the following:
* The last full and complete backup of the database
* The last full by-area backup of the area
With an incremental by-area backup, each storage area could
have been backed up in a different time window.
Following a full database backup, each subsequent incremental
backup replaces all previous incremental backups.
A backup plus the last incremental backup gives you the current
state of the database. The /FULL qualifier changes the backup
date in the root file affecting all future incremental backups.
The /AREA qualifier changes the date relative to the areas
being backed up. This behavior affects future /INCREMENTAL
backups to those areas.
o /LOCK_TIMEOUT=seconds qualifier of the RMU/BACKUP/ONLINE com-
mand
New and Changed Features 1-59
The /LOCK_TIMEOUT=seconds qualifier determines the maximum
time the backup operation waits for the quiet point lock dur-
ing online backup operations. When you specify the /LOCK_
TIMEOUT=seconds qualifier, you must specify the number of
seconds to wait for the quiet point lock. If the time limit
expires, an error is signaled and the backup operation fails.
When the /LOCK_TIMEOUT=seconds qualifier is not specified, the
backup operation waits indefinitely for the quiet point lock
during an online backup operation.
The /LOCK_TIMEOUT=seconds qualifier is ignored for offline
backup operations.
For more information, see Section 2.5.7.
1.22.3 RMU/CLOSE/PATH Command
The /PATH qualifier has been added to the RMU/CLOSE command.
Use the /PATH qualifier to close a database by specifying the
database's path name.
1.22.4 RMU/CONVERT Command
Rdb/VMS includes a new database conversion utility that converts
V3.0, V3.0A, V3.0B, V3.1, V3.1A, and V3.1B databases to V4.0.
The mode of operation for this utility is entirely different than
conversion operations in previous versions of Rdb/VMS. Unlike the
previous RMU/CONVERT command, there is no RMUCONVERT executable.
The syntax of the new RMU/CONVERT command is:
RMU/CONVERT[/[NO]COMMIT][/[NO]ROLLBACK][/[NO]CONFIRM]/PATH database-list
o /COMMIT
1-60 New and Changed Features
Causes the conversion to be committed. That is, after you
specify the option /COMMIT, the database is at V4.0 and cannot
be returned to the previous version. /NOCOMMIT permits the
database either to be committed to V4.0 or rolled back to the
previous version at a later time. /COMMIT is the default.
New and Changed Features 1-61
o /ROLLBACK
Returns the converted but not yet committed database to the
previous version. This is useful if you want to return to
the previous version for technical or business reasons.
The /NOROLLBACK qualifier inhibits the rollback function.
/NOROLLBACK is the default.
o /CONFIRM
Enables interaction with the user. It causes confirmation to
be sought before the operation begins and if after-image jour-
naling must be disabled. /NOCONFIRM inhibits the confirmation
requests. The default is /CONFIRM if the CONVERT command is
executed interactively, and /NOCONFIRM if the CONVERT command
is executed from a batch job.
o /PATH
Indicates that the database is being specified by the CDD/Plus
path name and not by its file specification. This assumes that
the CDD/Plus dictionary database has already been converted.
o database-list parameter
Specifies a list of databases to be converted. An item of the
list may be either the file specification of a database root
file (wild cards are allowed), or a CDD/Plus path name with a
/PATH qualifier (wild cards not allowed).
In the following example, RMU/CONVERT converts all the databases
in DISK:[ME] and its subdirectories and the SPECIAL_DB identified
by its CDD/Plus path name.
RMU/CONVERT DISK:[ME...]*.RDB,CDD$TOP.ME.SPECIAL_DB/PATH
In addition, note the following changes in the conversion process:
o The RMU/CONVERT command was designed so that if the conversion
is incomplete (for example as a result of a system crash or
1-62 New and Changed Features
Rdb/VMS MONITOR shutdown) it can be reexecuted later. Half-
converted databases that are essentially corrupted are no
longer possible.
o The RMU/CONVERT command operates by creating a converted copy
of the system relations and indexes. This implies that the
RDB$SYSTEM storage area may grow during the conversion, but
it is no longer likely that the system relations will be frag-
mented by the conversion process.
o Because a copy of the system relations is made, the time taken
by the conversion is proportional to the size of the system
relations. Conversion typically requires only a few minutes per
database, but if the database has very large system relations
the conversion can be costly. If the problem is a large number
of versions of some relations, it might be more efficient to
use the EXPORT/IMPORT operation to convert the database.
o After the conversion, both copies of the system relations
are stored in the database. The /COMMIT qualifier selects
the converted copy and deletes the original copy, while the
/ROLLBACK clause selects the original copy and deletes the
converted copy. Either the commit or rollback operations can be
performed at a later time if the /NOCOMMIT clause was selected
when the database was converted.
o While both copies of the system relations exist, the database
is usable under V4.0, but not under the earlier version. DDL
operations to the database are not allowed. This last restric-
tion is required to insure that both copies remain consistent.
After the commit or rollback operation completes, DDL opera-
tions are once again enabled.
o RMU/CONVERT requires that the RMU executable be installed with
SYSPRV privilege or that the user performing the conversion
have SYSPRV enabled.
New and Changed Features 1-63
See the VAX Rdb/VMS Installation Guide and the Before You Install
letter provided with the V4.0 software for more information about
converting your database to this version of Rdb/VMS.
1.22.5 RMU/COPY_DATABASE Command
The RMU/COPY_DATABASE command can be used to create a duplicate
database. Like the RMU/RESTORE command, the RMU/COPY_DATABASE com-
mand allows you to modify certain area parameters when the move
is performed. As with the RMU/BACKUP command, all the files are
processed simultaneously. The RMU/COPY_DATABASE command has per-
formance similar to that of the RMU/BACKUP command and eliminates
the inconvenience of having to use intermediate storage media.
RMU/COPY_DATABASE requires ADMIN or OPER Rdb privilege, and access
to the database files. The latter requires use of default ACLs,
SYSPRV, or installation of the RMU image with SYSPRV.
The syntax for RMU/COPY_DATABASE is:
RMU/COPY_DATABASE
[/[NO]AFTER_JOURNAL [=file-spec]]
[/PAGE_BUFFERS=n]
[/DIRECTORY=directory-spec]
[/OPTION=file-spec]
[/NODES_MAX=n]
[/USERS_MAX=n]
[/[NO]LOG]
[/ROOT=file-spec]
[/[NO]CHECKSUM_VERIFICATION]
[/BLOCKS_PER_PAGE=n]
[/FILE=file-spec]
[/THRESHOLDS=(n,n,n)]
[/SNAPSHOTS=FILE=file-spec]
[/[NO]ONLINE]
root-file-spec
storage-area-list
1-64 New and Changed Features
1.22.6 RMU/DUMP/AFTER_JOURNAL Command
The RMU/DUMP/AFTER_JOURNAL command contains changed output format
for the AIJ file.
1.22.7 RMU/DUMP/AFTER_JOURNAL/STATE=PREPARED
You can use the /STATE=PREPARED qualifier of the RMU/DUMP/AFTER_
JOURNAL command to generate a list of records associated with
unresolved transactions in the AIJ file. Depending upon your
application, you might need to generate the list for more than
one AIJ file.
1.22.8 RMU/DUMP/BACKUP_FILE/LABEL=(label-name-list) Command
The RMU/DUMP/BACKUP_FILE command includes a /LABEL=(label-name-
list) qualifier.
1.22.9 RMU/DUMP/USERS/STATE=BLOCKED Command
You can generate a list of unresolved distributed transactions
using the /USERS/STATE=BLOCKED qualifiers of the RMU/DUMP command.
See the RMU/DUMP command in the VAX Rdb/VMS RDO and RMU Reference
Manual for details.
1.22.10 RMU/MOVE_AREA Command
The RMU/MOVE_AREA command permits you to relocate one or more
areas, and optionally the root file, to different disks. Like
RMU/RESTORE, it permits the modification of certain area parame-
ters when the move is performed. As with the RMU/BACKUP command,
all the files are processed simultaneously. The performance of
the RMU/MOVE_AREA command is similar to that of RMU/BACKUP and
eliminates the inconvenience of having to use intermediate storage
media.
New and Changed Features 1-65
RMU/MOVE_AREA requires ADMIN or OPER Rdb/VMS privilege and access
to the database files. The latter requires use of default ACLs,
SYSPRV, or installation of the RMU image with SYSPRV.
The syntax for RMU/MOVE_AREA is:
RMU/MOVE_AREA
[/[NO]AFTER_JOURNAL[=file-spec]]
[/PAGE_BUFFERS=n]
[/[NO]AREA]
[/DIRECTORY=directory-spec]
[/OPTION=file-spec]
[/NODES_MAX=n]
[/USERS_MAX=n]
[/[NO]LOG]
[/ROOT=file-spec]
[/[NO]CHECKSUM_VERIFICATION]
[/BLOCKS_PER_PAGE=n]
[/FILE=file-spec]
[/THRESHOLDS=(n,n,n)]
[/SNAPSHOTS=FILE=file-spec]
root-file-spec
storage-area-list
1.22.11 RMU/OPEN/PATH Command
The /PATH qualifier has been added to the RMU/OPEN command.
Use the /PATH qualifier to open a database by specifying the
database's path name. See the RMU/OPEN command in the VAX Rdb/VMS
RDO and RMU Reference Manual for details.
1-66 New and Changed Features
1.22.12 RMU/RECOVER/RESOLVE Command
In the after-image journal file, you can modify the state of
records that are associated with blocked distributed transactions.
The RMU/RECOVER/RESOLVE command allows you to either commit or
abort blocked transactions in the after-image journal file. See
the RMU/RECOVER/RESOLVE command in the VAX Rdb/VMS RDO and RMU
Reference Manual for details.
1.22.13 RMU/REPAIR Command
The RMU/REPAIR command allows you to repair several types of SPAM
page corruption and ABM page errors. It also repairs page tail
errors to the satisfaction of RMU/VERIFY and corrects performance
problems that might otherwise have to be corrected by exporting
and importing the database.
1.22.14 RMU/RESOLVE Command
You can modify the state of unresolved distributed transactions
in the database. The RMU/RESOLVE command allows you to either
commit or abort unresolved transactions in the database. See
the RMU/RESOLVE command in the VAX Rdb/VMS RDO and RMU Reference
Manual for details.
1.22.15 RMU/RESTORE/LABEL=(label-name-list) Command
The RMU/RESTORE command now includes a /LABEL=(label-name-list)
qualifier. This is analogous to the /LABEL qualifier on the BACKUP
command. It specifies the order in which the tapes were written.
Normally, the /LABEL qualifier on the RESTORE command will be a
duplicate of the qualifier used on the BACKUP command.
New and Changed Features 1-67
1.22.16 Security Auditing
Rdb/VMS now gives you the ability to enable security auditing for
a database, to display the security auditing that is enabled for
the database, and to load records from an audit journal into an
Rdb/VMS relation, using the following commands:
o RMU/SET AUDIT
The RMU/SET AUDIT command enables Rdb/VMS security auditing
to send security alarm messages and to make entries in the
database security audit journal whenever specified audit events
are detected by Rdb/VMS.
o RMU/SHOW AUDIT
The RMU/SHOW AUDIT command displays the set of security au-
diting characteristics that have been established with the
RMU/SET AUDIT command.
o RMU/LOAD/AUDIT
The RMU/LOAD/AUDIT command allows you to load records from an
Rdb/VMS audit journal into an Rdb/VMS relation.
See Section 1.2 for more information on security auditing. See the
information for the RMU/SET AUDIT, RMU/SHOW AUDIT, and RMU/LOAD
/AUDIT commands for more information on the new auditing features
that can be enabled for your Rdb/VMS database.
1.22.17 RMU/SHOW STATISTICS Command
A change has been made in Rdb/VMS V4.0 so that now only processes
on the node on which the RMU/SHOW STATISTICS command is being run
can take up a line on the stall messages display. In Rdb/VMS V3.1
and lower, database users on another node in a VAXcluster take up
blank lines in the RMU/SHOW STATISTICS stall display. Wasted space
on the stall display minimized the number of processes that could
be observed.
1-68 New and Changed Features
For Rdb/VMS V4.0, an additional change has been made so that the
stall screen displays only processes that are actively stalling.
Once a process finishes stalling, it disappears from the screen.
Processes that are still stalling, appear nearer to the top of
the display, so that the longest stalling processes appear at
the top of the display. Newer stalling processes are added to the
end of the display. Thus, all users on this node share the same
stall display lines; only the actively stalling processes appear.
This allows you to monitor many more stalling processes than
before. Now, the only constraint to the number of concurrently
stalling processes you can observe is the number of lines in your
RMU/SHOW STATISTICS display. A database with no stalling processes
occurring will show a blank stall messages display.
In Rdb/VMS V3.1 and lower, once a process attaches to the
database, it holds a particular line on the stall statistics
display regardless of whether it is stalling or not. The first
column ("Process . . . ") contains the process ID (PID) and stream
ID (STID) to identify that user. If that process stalls, the stall
message appears on the line along with the time when the stall
began. Once the stall completes, only the "since . . . " column is
deleted, leaving the old stall message on the screen, even though
the stall is over. This method limits the number of processes that
you can monitor to the number of lines on the display screen. For
databases that have hundreds of users, this method does not allow
you to observe all the desired information.
1.23 SQL/Services: New and Changed Features
The new features and technical changes in SQL/Services for VAX
Rdb/VMS Version 4.0 including process pooling, API support, API
routines, server access, list cursors, installation questions, and
Help, are discussed in the following sections.
New and Changed Features 1-69
1.23.1 Process Pooling
In previous versions of SQL/Services, the server on a VAX com-
puter created a VMS process and activated an image in that process
for each association request sent from the client system. Process
pooling software, introduced in this release, provides a more
efficient way of handling client application requests. Using a
multithreaded communications component and an execution component
consisting of pre-started, reusable request processors, process
pooling improves client application response time (by increas-
ing request throughput) and reduces the load on server system
resources.
1.23.2 API Support
SQL/Services has developed two new Application Programming
Interfaces (APIs), one for computers running the Macintosh op-
erating system and the other for computers using the IBM OS/2
operating system. The two APIs join the currently supported MS-
DOS, ULTRIX, ULTRIX for RISC, and VMS interfaces, bringing to six
the number of APIs that SQL/Services supports.
1.23.3 API Routines
A new set of portable API routines provides a functional interface
to information previously accessed only by direct program refer-
ence to the SQLCA and SQLDA structures. The functional interface
allows API programmers to write portable applications across all
client platforms, including Macintosh and OS/2. API programmers
can still use C language constructs to access SQLCA and SQLDA
structures directly on all platforms except on the Macintosh.
Digital recommends, however, that API application programmers use
the functional interface routines when developing applications
with any of the supported APIs. There are two primary reasons for
this:
o To create fully portable applications for all API environments
1-70 New and Changed Features
o To make transparent for applications any future changes made in
the SQLCA and SQLDA structures
Finally, although at present SQL/Services allows direct access
to structures from all API environments except the Macintosh, in
the future Digital may provide access to structures through API
routines only.
1.23.4 Server Access
SQL/Services API client applications can access the server system
by explicit, proxy-like, or default access. The current release
adds proxy-like and default access to the already supported ex-
plicit access method. The two additional access routes permit
programmers (and nontechnical end users) more convenient entry to
SQL/Services. Your system management team can enable or disable
either or both access avenues at any time.
1.23.5 List Cursors
SQL/Services now supports SQL list cursors (segmented strings), an
extension to SQL for Rdb/VMS segmented strings. Enhancements to
the SQL/Services API for list cursors include:
o An explicit sqlsrv_declare_cursor routine
o Two new data types:
- SQLSRV_LIST_VARBYTE
- SQLSRV_VARBYTE
o Information in the SQLDA.SQLIND column and the SQLCA structure
New and Changed Features 1-71
The new sqlsrv_declare_cursor routine lets API programmers ex-
plicitly name the cursor type (table or list) and the mode.
Table cursors let you specify three modes: update, read-only, and
insert-only. List cursors allow two modes: read-only and insert-
only. SQL/Services continues to implicitly declare cursors as type
table and mode update when an sqlsrv_open_cursor routine is not
preceded explicitly by an sqlsrv_declare_cursor call.
1.23.6 SQL/Services VMS API Linkable Libraries
For Rdb/VMS Version 4.0, SQL/Services provides VMS API libraries
(.OLBS) with the Rdb/VMS development kit in addition to share-
able libraries shipped since the Rdb/VMS Version 3.1 release.
Programmers now have the option of linking the VMS API libraries
directly into their executable images. This allows the image to
run on Rdb/VMS run-time and interactive systems, for which the
SQL/Services shareable images are not provided.
1.23.7 Two New Installation Questions
The Rdb/VMS Version 4.0 installation procedure displays two new
questions for SQL/Services. One question lets you choose a User
Identification Code (UIC) for the SQLSRV$SRV account, and the
other lets you name a device for the account. SQL/Services creates
the SQLSRV$SRV account to run the communication server during
request processing. It also uses the account during installation
to run the IVP.
1.23.8 SQL/Services Help Available from DCL HELP Facility
The DCL HELP facility now includes SQL/Services help information.
Type HELP SQL_SERVICES at the DCL command level prompt for a
brief introduction to SQL/Services and pointers to additional
information.
1-72 New and Changed Features
1.24 Changes Related to the Sample Personnel Database
The following changes have been made to the sample SQL PERSONNEL
database:
o Single-file database:
- Creates a new RESUMES domain (RESUME_DOM) using LIST of BYTE
VARYING (VARBYTE) data type
- Adds a new RESUMES table
The RESUMES table contains the EMPLOYEE_ID and RESUME
columns.
- Loads three rows of the RESUMES table
o Multifile database:
- Creates a new RESUMES domain (RESUME_DOM) using LIST of BYTE
VARYING (VARBYTE) data type
- Adds a new RESUMES table
The RESUMES table contains the EMPLOYEE_ID and RESUME
columns.
- PERSONNEL.COM loads three rows of the RESUMES table.
- Adds two new storage areas, RESUME_LISTS and RESUMES
The RESUME_LISTS storage area contains the RESUME column of
the RESUMES table.
The RESUMES storage area contains the EMPLOYEE_ID column
of the RESUMES table. The EMPLOYEE_ID column has a UNIQUE
constraint defined for it.
New and Changed Features 1-73
1.25 New and Changed Rdb/VMS Logical Names
This section describes new Rdb/VMS logical names and changes to
other Rdb/VMS logical names.
1.25.1 Disabling the Two-Phase Commit Protocol with the New
SQL$DISABLE_CONTEXT Logical Name
You can disable the two-phase commit protocol by defining the
logical name SQL$DISABLE_CONTEXT to be "TRUE", as shown in the
following example:
$ DEFINE SQL$DISABLE_CONTEXT TRUE
This logical name is useful for turning off distributed trans-
actions when you want to run batch-update transactions. Because
batch-update transactions do not write to recovery-unit journal
(RUJ) files, batch-update transactions cannot be rolled back and
therefore cannot be used in a distributed transaction. For more
information on using this logical name, see Section 4.1.1 in the
VAX Rdb/VMS Guide to Distributed Transactions.
1.25.2 Lock Timeout Mechanism Using a New
RDM$BIND_LOCK_TIMEOUT_INTERVAL Logical Name
A new logical name, RDM$BIND_LOCK_TIMEOUT_INTERVAL, can be used
to set the amount of time a transaction waits for locks to be re-
leased. Using this logical name helps avoid distributed deadlock.
NOTE
Another method that Rdb/VMS provides to set the amount of
time a transaction waits for locks to be released is the
WAIT <interval> clause of the SET TRANSACTION or DECLARE
TRANSACTION statement.
See the VAX Rdb/VMS Guide to Distributed Transactions for more
information.
1-74 New and Changed Features
1.25.3 Restricting the Creation of Databases Using the New
RDBVMS$CREATE_DB Logical Name
You can restrict the creation of databases by users by defining
the RDBVMS$CREATE_DB logical name, and a rights identifier of the
same name.
WARNING
When you define this logical name, other installed Digital
and third-party products will not be able to use Rdb/VMS to
create Rdb/VMS databases. Therefore you must deassign this
logical name whenever users of such products need to create
an Rdb/VMS database.
To restrict the creation of Rdb/VMS databases, you must first
define the SYSTEM/EXECUTIVE logical name RDBVMS$CREATE_DB by
entering the following command:
$ DEFINE/SYSTEM/EXECUTIVE RDBVMS$CREATE_DB
Use the rights identifier, RDBVMS$CREATE_DB, to control which
users will be able to create databases using the SQL CREATE SCHEMA
or RDO DEFINE DATABASE statements. For more information on using
this logical name, adding the RDBVMS$CREATE_DB rights identifier,
and restrictions on its use, see Section 6.10 of the VAX Rdb/VMS
Guide to Database Design and Definition.
1.25.4 Remote Access to Rdb/VMS Using a New Logical Name
RDB$REMOTE_BUFFER_SIZE
Prior to V4.0 of Rdb/VMS, the buffer size of network transfers was
limited to 1024 bytes. With V4.0, the buffer size is increased to
a default setting of 2048 bytes.
Also, by setting the logical name RDB$REMOTE_BUFFER_SIZE before
running an application you can increase the buffer size to your
system quota limits. For example, to increase the buffer size to
10,000 bytes, enter:
New and Changed Features 1-75
$ DEFINE RDB$REMOTE_BUFFER_SIZE 10000
You cannot set the buffer size to less than 2048 bytes. Accessing
a database with an earlier version than V4.0 reduces the buffer
size to 1024 bytes. Accessing a V4.0 database from a V3.n version
of Rdb/VMS also sets the buffer size to 1024 bytes.
It may be desirable to increase the buffer size if you transfer
large data blocks in or out of the database. Increasing the buffer
size reduces the number of network I/O operations used when large
data transfers are made.
1.25.5 Changes to the RDM$BIND_BUFFERS Logical Name
For the RDM$BIND_BUFFERS logical name, you can specify a value
between 2 and 32,768. In V3.1, the upper limit was 1024.
1.25.6 Changes to the RDMS$BIND_SEGMENTED_STRING_BUFFER Logical Name
When using the RDML and RDBPRE precompilers, be sure to define a
sufficiently large value for the RDMS$BIND_SEGMENTED_STRING_BUFFER
logical name. An adequate buffer size is needed to store large
segmented strings (using segmented string storage maps) in storage
areas other than the default RDB$SYSTEM storage area. The minimum
acceptable value for the RDMS$BIND_SEGMENTED_STRING_BUFFER logical
name must be equal to the sum of the length of the segments of
the segmented string. For example, if you know that the sum of the
length of the segments is one megabyte, then 1,048,576 bytes is an
acceptable value for this logical name.
You must specify the logical name value because when RDML and
RDBPRE precompilers store segmented strings, Rdb/VMS does not know
which table contains the string until after the entire string is
stored. Rdb/VMS buffers the entire segmented string, if possible,
and does not store it until the STORE statement executes.
1-76 New and Changed Features
If the segmented string remains buffered, it is stored in the
appropriate storage area. If the string is not buffered (because
it is larger than the defined value for the logical name or the
default value of 10,000 bytes), it is not stored in the default
storage area and the following exception message is displayed:
%RDB-F-IMP_EXC, facility-specific limit exceeded
-RDMS-E-SEGSTR_AREA_INC, segmented string was stored incorrectly
To avoid this error, set the value of the RDMS$BIND_SEGMENTED_
STRING_BUFFER logical name to a sufficiently large value. Note
that a value of up to 500 MB can be specified for this logical
name. See the VAX Rdb/VMS RDO and RMU Reference Manual for more
information on defining storage areas.
NOTE
The SQL interface for lists (segmented strings) does not
require you to define the value for this logical name.
Before the list is brought into the buffer, SQL knows the
column that the list is associated with and the table it is
stored in. However, for large lists, defining this logical
name with a value large enough to hold the entire list may
improve the handling performance of storing the list.
1.25.7 RDMS$AUTO_READY Logical Name Is No Longer Used
The logical name RDMS$AUTO_READY is now obsolete and therefore is
not available for V4.0. Its use was previously recommended under
only very limited circumstances to reduce the overhead of lock
management in situations where no reserving clause was specified
and a two-tiered structure of locking was in effect in which an
area is accessed for concurrent read and then upgraded to concur-
rent update. The use of the RDMS$AUTO_READY logical name defined
as "U" (Update) in this situation automatically converted all ini-
tial read requests to update requests, and thereby avoided this
two-tiered locking structure. This was recommended so that the
reserving clause would not go through and lock all the partitions
of the relations, but would auto ready them as required. Because
New and Changed Features 1-77
users accessing a relation would "ready" (lock) the individual
relation partitions only when they were accessed and in the order
in which they were accessed, a disadvantage of using this logical
name was that deadlocking potential was increased.
However, beginning with V3.1, locks are cached so that there
is no longer any benefit to using the RDMS$AUTO_READY logical
name defined as "U" (Update) in read/write transactions that do
not specify a reserving clause. Starting in V3.1 Rdb/VMS uses
an internal lock cache scheme with relation partitions; this
change provides the benefits of the RDMS$AUTO_READY logical while
minimizing the potential for deadlock.
1.26 Obsolete Statements and Features
Obsolete statements and features are items that Rdb/VMS is phas-
ing out. Over the last few releases Digital has begun moving the
database administrator commands out of RDO and into the RMU util-
ity to make these functions independent of the language being used
(RDO or SQL). This trend will continue.
Many statements were declared as obsolete but were still supported
for a period of time to give users time to change the applications
that used these statements. Those obsolete statements and features
will not be supported in the release following V4.0.
1.26.1 SQL Obsolete Features
This section describes SQL statements that were allowed in earlier
versions of SQL, but will be flagged with diagnostic messages
in SQL V4.0 when you use them in programs. SQL refers to such
statements as obsolete features, and may not support them in
future versions.
1-78 New and Changed Features
If you use the obsolete keywords, you receive a diagnostic mes-
sage.
SQL> SET TRANSACTION READ_ONLY;
1
%SQL-I-DEP_FEATURE, (1) Deprecated Feature
The following features are deprecated for V4.0:
o UNIQUE predicate
o Use of double quotation marks for string literals
Use single (') instead of double (") quotation marks to de-
limit a string.
o Not using colons before host variables in embedded SQL
In SQL statements embedded in programs to be processed by the
SQL precompiler, you must precede parameters with a colon (:)
to distinguish them from a column or table name.
In the following cases, earlier versions of SQL did not require
that a host variable be preceded by a colon:
- The INTO clause of a FETCH or singleton SELECT statement
- The VALUES clause of an INSERT statement
- The LIKE clause of a predicate
- The IN value-list clause of a predicate
- A PREPARE or EXECUTE statement
The optional colon preceding the host variable does not work
when:
- The host variable reference has more than two levels of
qualification (for example: a.b.c.d.).
New and Changed Features 1-79
- The host variable is not a standard SQL token (for example:
a lower or mixed case C variable or a COBOL variable that
contains hyphens).
For this reason, Digital recommends that you always precede pa-
rameter names with a colon. Future versions of SQL may require
that you use a colon before all host variables.
o DATABASE keyword
For V4.0 the SCHEMA keyword replaces the DATABASE keyword.
Use DECLARE SCHEMA, CREATE SCHEMA, and DROP SCHEMA statements
instead of DECLARE DATABASE, CREATE DATABASE, and DROP DATABASE
statements.
o Underscores in DECLARE TRANSACTION and SET TRANSACTION keywords
Table 1-2 lists obsolete keywords and preferred substitutes for
V4.0 SQL DECLARE and SET TRANSACTION statements.
Table_1-2:_Obsolete_Keywords_for_DECLARE_and_SET_TRANSACTION______
Obsolete
Keyword__________V4.0_Preferred_Keyword___________________________
VERB_TIME VERB TIME
COMMIT_TIME COMMIT TIME
READ_ONLY READ ONLY
READ_WRITE_______READ_WRITE_______________________________________
o Not using the CASCADE keyword
For V4.0, the CASCADE keyword is added to the DROP statement.
The default behavior for the SQL DROP TABLE and DROP VIEW
statements is to drop all items that refer to the table or
view, then to drop the table or view itself. A new keyword,
CASCADE, has been added to specifically request this behavior.
1-80 New and Changed Features
For example, if you specify DROP TABLE NO CASCADE, only the
table will be dropped. If other items (views, constraints,
indexes, etc.) refer to the specified table, the drop will
fail.
You will get an informational message in interactive SQL,
precompiled SQL, and SQL module language if you do not specify
either CASCADE or NO CASCADE.
1.26.2 RDO Obsolete Statements and Features
Table 1-3 summarizes the list of obsolete statements, the ones
that replace them, and the version in which the transition is
effective. See the VAX Rdb/VMS RDO and RMU Reference Manual for
more information.
New and Changed Features 1-81
Table_1-3:_Obsolete_Statements____________________________________
Statement_or_Feature__Replaced_by___________Effective_____________
RDO RECOVER RMU/RECOVER V3.1
RDO OPEN RMU/OPEN V3.1
RDO CLOSE RMU/CLOSE V3.1
RDO STOP MONITOR LOG RMU/MONITOR STOP V3.0
RDO REFRESH MONITOR RMU/MONITOR REOPEN V3.0
RDO SHOW MONITOR RMU/SHOW SYSTEM V3.0
RDO SHOW USERS RMU/SHOW USER V3.0
RDO BACKUP EXPORT V3.0
RDO RESTORE IMPORT V3.0
RDO SPOOL RMU/BACKUP/AFTER_ V3.0
JOURNAL
RDO_CONVERT___________RMU/CONVERT___________V3.0__________________
The VAX Rdb/VMS RDO and RMU Reference Manual includes an appendix
that shows the syntax of RDO statements no longer supported in
Rdb/VMS Version 3.1 and Rdb/VMS Version 4.0.
1.26.3 SQL/Services Obsolete Features
The following features are no longer supported in SQL/Services:
o Proxy access to SQL/Services server
1-82 New and Changed Features
For security reasons, client API applications can no longer use
proxy accounts to access the SQL/Services server system. In its
place, SQL/Services adds proxy-like and default access to the
already supported explicit access method. The two additional
access routes permit programmers (and nontechnical end-users)
more convenient (but more secure) entry to SQL/Services. Your
system management team can enable and disable either or both
access avenues at any time.
New and Changed Features 1-83i
l o SERVER_LOG parameter @ The SQL/Services SERVER_LOG parameter in the ASSOCIATE_STR? structure is no longer supported. You can, however, stilld? enable and disable logging on the client system using the : CLIENT_LOG parameter of the ASSOCIATE_STR structure. C Refer to the VAX Rdb/VMS Guide to Using SQL/Services for fur- B ther information about the logging provided by SQL/Services. 4 1.27 Summary of Documentation Additions and Changes B This section describes the highlights of changes to the Rdb/VMSE V4.0 documentation. The reference manuals and the guides each con-TC tain a technical changes and new features section in the prefaceED that describes specific changes in each book. You should refer toC a specific book for more specific information on changes and newf features for that book. C The following books are new to the Rdb/VMS documentation set for