Order Number: AA-GQ93F-TE
VAX Rdb/VMS
Release Notes,
Version 3.1
December 1989
This document contains the release notes for VAX Rdb/VMS Version
3.1. It describes new and changed features, software errors fixed,
and problems, restrictions, and other information relating to
Version 3.1.
OPERATING SYSTEM: VMS
SOFTWARE VERSION: VAX Rdb/VMS Version 3.1
Digital Equipment Corporation
__________
Copyright ©1984, 1985, 1986, 1987, 1988, 1989
The following are trademarks of Digital Equipment Corporation:
ACMS MASSBUS ULTRIX
ALL-IN-1 MicroVAX UNIBUS
DATATRIEVE PDP VAX
DEC P/OS VAX CDD
DEC/CMS Professional VAX FMS
DEC/MMS Rainbow VAXcluster
DECforms RALLY VAXELN
DECintact Rdb/ELN VAXstation
DECmate Rdb/VMS VIDA
DECnet ReGIS VMS
DECUS RSTS VT
DECwindows RSX Work Processor
DECwriter RT
DIBOL TDMS DIGITAL
This document was prepared using VAX DOCUMENT, Version 1.2
Contents
PREFACE xv
CHAPTER 1 CONVERSION TO RDB/VMS VERSION 3.1 1-1
1.1 RDB/VMS VERSION 3.1 REQUIRES VMS VERSION 5.2 OR
HIGHER 1-1
1.2 REMOTE IVP MAY FAIL ON INSTALLATION OF RDB/VMS
VERSION 3.1 1-1
1.3 DATABASE FORMAT INCOMPATIBLE WITH PREVIOUS VERSION
OF RDB/VMS 1-3
1.4 V3.0, V3.0A, AND V3.0B DATABASES MUST BE CONVERTED
USING THE RMU/CONVERT COMMAND 1-4
1.5 RMU/CONVERT COMMAND REQUIRES PRIVILEGES 1-5
1.6 CHANGES IN THE RMU/CONVERT COMMAND FOR V3.1 1-6
1.7 CONVERTING CDD/PLUS DATABASES IF THE RMU/CONVERT
COMMAND FAILS 1-7
1.8 COMMAND PROCEDURE FOR AUTOMATIC CONVERSION OF ALL
EXISTING DATABASES 1-9
CHAPTER 2 NEW AND CHANGED FEATURES 2-1
2.1 VAX SQL AND VAX SQL/SERVICES NOW PART OF VAX
RDB/VMS 2-1
2.2 REFERENTIAL INTEGRITY SUPPORT 2-2
2.3 TRIGGERS 2-3
2.3.1 Note on the Timing of Trigger Actions 2-4
2.3.2 Cautions on Selecting Column Name Lists for
SQL UPDATE or RDO MODIFY Statement 2-5
2.4 READ-ONLY STORAGE AREAS 2-6
2.5 INTERNATIONALIZATION SUPPORT 2-7
iii
Contents
2.5.1 Controlling Input and Display Format 2-8
2.5.2 Specifying Collating Sequence 2-8
2.6 CONCURRENT METADATA OPERATIONS 2-11
2.7 DESCENDING INDEXES 2-20
2.8 AUTOLOCKING IMPLICITLY RESERVES TABLES 2-21
2.9 SQL SUPPORT FOR VAX DATA DISTRIBUTOR 2-22
2.10 DISABLING DATA DICTIONARY USE DURING DATABASE
CREATION 2-22
2.11 NEW RDB/VMS LOGICAL NAMES 2-23
2.11.1 RDMS$DEBUG_FLAGS_OUTPUT 2-23
2.11.2 RDM$BIND_RUJ_EXTEND_BLKCNT 2-23
2.11.3 RDMS$DIAG_FLAGS 2-24
2.11.4 RDMS$BIND_VM 2-24
2.11.5 RDMS$BIND_VALIDATE_CHANGE_FIELD 2-24
2.12 SQL: NEW AND CHANGED STATEMENTS 2-25
2.13 SQL: UNION OPERATOR NOW SUPPORTED 2-31
2.14 SQL: DEFAULT VALUES 2-31
2.15 SQL: PRECOMPILER 2-33
2.15.1 SQL: Precompiler Support for Pascal 2-33
2.15.2 New Command Line Qualifiers 2-34
2.16 SQL: MODULE PROCESSOR 2-35
2.16.1 New Command Line Qualifiers 2-35
2.17 SQL: VAX LANGUAGE-SENSITIVE EDITOR (LSE) SUPPORT 2-38
2.18 SQL: CHANGES FOR ANSI/ISO COMPATIBILITY 2-38
2.19 SQL: ANSI-STYLE PRIVILEGES 2-40
2.20 SQL: ALL AND ANY BOOLEANS 2-41
2.21 SQL: NEW PACKAGES SUPPORTED FOR THE ADA
PRECOMPILER 2-42
2.21.1 New Package for Ada Host Language Variables 2-42
2.21.2 New Calendar Package for the Ada Precompiler 2-46
iv
Contents
2.22 RDO: NEW AND CHANGED STATEMENTS 2-46
2.23 RDBPRE: NEW AND CHANGED STATEMENTS 2-51
2.23.1 RDBPRE Module Is Now Allowed to Be Compiled
with the /NOINITIALIZE_HANDLE Option 2-51
2.23.2 Autolocking Options in the RESERVING Clause of
the START_TRANSACTION Statement 2-52
2.23.3 REQUEST_HANDLE SCOPE Clause 2-52
2.24 RDML: NEW AND CHANGED STATEMENTS 2-52
2.24.1 RDML and Rdb/ELN: EPascal Default Changed for
/LINKAGE Qualifier 2-52
2.24.2 Autolocking Options in the RESERVING Clause of
the START_TRANSACTION Statement 2-53
2.24.3 REQUEST_HANDLE SCOPE Clause 2-53
2.25 LINKING RDML APPLICATIONS 2-54
2.25.1 Linking RDML/C Applications 2-55
2.25.1.1 Linking Applications Compiled with the /G_FLOATING
Qualifier 2-55
2.25.1.2 Linking Applications Compiled without the
/G_FLOATING Qualifier 2-56
2.25.2 Linking RDML/Pascal Applications 2-57
2.26 RMU: NEW AND CHANGED COMMANDS 2-58
2.27 OBSOLETE RDO STATEMENTS 2-70
2.27.1 RDO BACKUP and RESTORE Statements No Longer
Supported 2-70
2.27.2 RDO SPOOL Statement No Longer Supported 2-71
2.27.3 RDO ANALYZE, RDO CLOSE, RDO OPEN, and RDO
RECOVER Statements Are Obsolete 2-71
2.28 CHANGES RELATED TO THE SAMPLE PERSONNEL DATABASE 2-71
2.28.1 Creating the Sample Database 2-72
2.28.2 Files Used in Creating the Sample Database 2-73
2.28.3 Differences Between SQL-Defined and
RDO-Defined Databases 2-74
2.29 QUERY OPTIMIZER IMPROVEMENTS 2-75
v
Contents
2.29.1 Query Solution Placement of Boolean
Expressions Was Inefficient 2-75
2.29.2 New Query Optimizer Feature, BEFORE-GET
SELECTION 2-75
2.29.3 Query Optimizer Chooses Between Ascending and
Descending Indexes 2-76
2.29.4 Index Cost Calculations Have Been Further
Extended to Favor Index Retrieval Over
Sequential Scans 2-76
2.30 SUMMARY OF DOCUMENTATION ADDITIONS AND CHANGES 2-77
CHAPTER 3 SOFTWARE ERRORS FIXED 3-1
3.1 GENERAL (ALL INTERFACES) DATABASE ADMINISTRATION
AND MAINTENANCE 3-2
3.1.1 RUJ Files Were Not Being Deleted Once Rdb/VMS
Was Finished with Them 3-2
3.2 GENERAL (ALL INTERFACES) DATA DEFINITION AND DATA
MANIPULATION 3-3
3.2.1 Partitioning Multisegmented Sorted Indexes
Across Storage Areas Caused Several Problems
to Occur 3-3
3.2.2 Storage Map Information Was Lost After
Importing a Database Exported Prior to V3.1 3-3
3.2.3 Defining a Storage Map with a Name Previously
Used Caused a Bugcheck Dump 3-4
3.2.4 Defining a Storage Map Incorrectly Allowed Two
Placement Clauses 3-4
3.2.5 Rdb/VMS Did Not Check Minimum Node Size at
Index Definition Time 3-4
3.2.6 Misplaced SORTED BY Clauses Produced Incorrect
Results or Bugcheck Dumps 3-5
3.2.7 Joining Multiple Tables with Equalities Could
Return Wrong Results 3-6
3.2.8 Queries That Used Views and Nested Views Could
Return Wrong Results 3-7
vi
Contents
3.2.9 A Query to Retrieve Data Using the MISSING
Operator Used a Sequential Scan Rather Than a
Defined Hashed Index 3-7
3.2.10 Query Optimizer Did a Poor Job with a Query in
Which Neither of the Segments Was the First 3-7
3.2.11 Access to a View Was Denied Even Though You
Had Read/Write Access 3-8
3.2.12 During Remote Database Access, the ROLLBACK
Statement Caused RALLY to Bugcheck Dump on the
Local Node 3-8
3.2.13 When an Invalid (Read-Only) Message Vector Was
Passed to a RDB$DATABASE_INFO Call, Rdb/VMS
Stalled 3-8
3.2.14 Change in the Way Query Optimizer Computed
Index Cost 3-9
3.2.15 Query with Index Retrieval Returned Extraneous
Data 3-9
3.2.16 Rdb/VMS Not Correctly Generating the Code
for the Assignment Statements Within the FOR
Loop 3-10
3.2.17 Excessive Direct I/O at the First STORE
Operation 3-10
3.2.18 Form-Feed Characters Were Incorrectly
Interpreted in RDO 3-11
3.2.19 Constraints Sometimes Were Improperly Selected
for Evaluation 3-11
3.2.20 There Was a Problem with the SHOW Statement 3-11
3.2.21 There was a Problem Deleting the Employees
Relation in the V3.0A MF_PERSONNEL Sample
Personnel Database 3-11
3.3 SQL 3-12
3.3.1 Data Definition and Data Manipulation 3-12
3.3.1.1 GROUP BY and Aggregate Expression Queries Could
Return Wrong Results, No Results, or Bugcheck Dumps
vii
Contents
3-12
3.3.1.2 There Were Problems with the Quantified Predicates
ANY and ALL 3-12
3.3.1.3 There Was a Problem When You Ran Out of Disk Space
When Exporting the Database 3-13
3.3.1.4 Under Some Circumstances, Using Interactive SQL to
Display Dbkeys Caused a Bugcheck Dump 3-13
3.3.1.5 In a CREATE SCHEMA Statement, the Value Specified
for the NUMBER OF RECOVERY BUFFERS Clause Was
Ignored 3-13
3.3.1.6 Altering a Storage Map with a Limit Clause Caused a
Bugcheck Dump to Occur 3-13
3.3.1.7 There Was a Problem with the COMMENT ON Statement
for a Hashed Index 3-13
3.3.1.8 The SHOW STORAGE MAP Statement Incorrectly
Displayed Compression Characteristics for Tables
3-14
3.3.1.9 Using Interactive SQL Caused Inaccurate Values in
Queries and Produced Bugcheck Dumps Under Some
Circumstances 3-14
3.3.1.10 Defining a Schema with a DICTIONARY IS REQUIRED
Clause Resulted in Errors When Integrating into the
Dictionary 3-14
3.3.1.11 SQL$PRE Now Correctly Differentiates "-" and "_"
Characters 3-15
3.3.2 Programming 3-15
3.3.2.1 EXEC SQL INCLUDE FROM DICTIONARY Statement Returned
an Error Message from the COBOL FILLER Attribute
3-15
3.3.2.2 During a FORTRAN Precompilation, Code Was Ignored
After an END Statement 3-16
3.3.2.3 /NOG_FLOATING Qualifier Did Not Function Properly
with SQL Dynamic C 3-16
3.3.2.4 Use of the Word DBKEY as a Column Name in a
SELECT Expression Caused SQL to Produce an Access
viii
Contents
Violation 3-16
3.3.2.5 SQL Precompiler Did Not Support Prototype
Definitions for C 3-17
3.3.2.6 Using SQLPRE/FORTRAN with the /NOG_FLOAT Qualifier
Caused a Bugcheck Dump 3-17
3.3.2.7 SQL$GET_ERROR_TEXT Passed Back the Error Text and a
Signed Longword When It Should Have Passed Back a
Signed Word 3-17
3.3.2.8 FORTRAN Precompiler Split CDD/Plus Path Names
Across Lines Incorrectly 3-17
3.3.2.9 When More Than Six Schemas Were Declared in a
Program, a Macro Branching Error Occurred 3-18
3.3.2.10 There Was a Problem in the Module Language When the
First Reference to Any Database Object Was a Domain
3-18
3.3.2.11 Ada Did Not Handle Lines More Than 120 Characters
in Length 3-18
3.3.2.12 Precompiler Sometimes Generated Bad Ada Package
Names 3-18
3.3.2.13 Ada Programs That Had No SQL Statements in Them
Caused the Precompiler to Have Problems 3-18
3.3.2.14 Ada Precompiler Did Not Support the Use of
Host Language Variables Declared Using CONSTANT
Variables 3-19
3.3.2.15 Precompiler Did Not Recognize the Ada Calendar
Packages 3-19
3.3.2.16 SQL Module Language Misinterpreted the Scale Factor
for Parameters Defined from Domains 3-19
3.3.2.17 Query Using a SELECT DISTINCT Clause on a Table of
More Than 65 Columns Caused a Bugcheck Dump 3-19
3.3.2.18 SQL Precompiler and Module Language Did Not Return
Success Status 3-20
3.4 RDO, RDBPRE, AND RDML 3-20
3.4.1 Data Definition and Data Manipulation 3-20
ix
Contents
3.4.2 There were Conversion Problems Between Stored
Data and Newly Defined Metadata 3-20
3.4.2.1 Storage Maps Partitioned by DATE Problem 3-21
3.4.2.2 Defining SEGMENTED STRING Global Fields Within
a DEFINE RELATION Statement Sometimes Produced
Incorrect Results 3-21
3.4.2.3 COMPUTED BY Fields Were Not Evaluated Correctly3-22
3.4.2.4 SHOW STORAGE MAP and SHOW INDEX Statements Did Not
Show the Complete STORE Clause of Any Storage Area
Exported from V3.0 3-22
3.4.2.5 START_TRANSACTION Statement Not Working with
Extremely Long Statements 3-22
3.4.3 Programming 3-23
3.4.3.1 RDBPRE Precompiler Generated an Incorrect DSRI
Parameter Block 3-23
3.4.3.2 Using RDBPRE INVOKE with RUNTIME FILENAME and Dbkey
Clauses Produces a Fatal Error 3-23
3.4.3.3 RDBPRE Did Not Handle Boolean Expression Errors in
GET Statements 3-23
3.4.3.4 FETCH Statement Did Not Get Executed in RDBPRE 3-24
3.4.3.5 RDBPRE Returned a Syntax Error for a
START_SEGMENTED_STRING Statement Within a
FOR Statement 3-24
3.4.3.6 RDB$INTERPRET Produced Error Messages If Unsigned
Longword Data Types Were Used as Database or
Transaction Handles 3-24
3.4.3.7 RDML Checked Data Type with Segmented String FOR or
STORE Statement 3-25
3.4.3.8 RDML Generated a Fatal Error Message When a Context
Variable Was Omitted 3-25
3.4.3.9 RDML Generated Incorrect Code When an Access
Control String Was Used 3-26
3.5 RDB/VMS MANAGEMENT UTILITY (RMU) 3-26
3.5.1 Using the RMU/BACKUP/AFTER_JOURNAL Command
on an Empty AIJ File Caused a Fatal Error to
Occur 3-26
x
Contents
3.5.2 Using the RMU/BACKUP/INCREMENTAL/ONLINE
Command Did Not Back Up Allocated, But Empty,
Pages 3-26
3.5.3 RMU/VERIFY Command Produced a Bugcheck Dump
When a Storage Area Was Deleted 3-27
3.5.4 RMU/VERIFY Command Did Not Do a Complete
Integrity Check of Indexes 3-27
3.5.5 When the RMU/ANALYZE/INDEX Command Was Run
Interactively, It Caused a Bugcheck Dump 3-27
3.5.6 RMU/ANALYZE Command Was Not Counting
Fragmented Records Correctly 3-28
3.5.7 RMU/VERIFY Command Produced an End-of-File
Error Following a Restore Operation 3-28
3.5.8 RMU/RESTORE/USERS_MAX=nnn Command with
Single-File Databases Caused RMU to Fail on
Certain Operations on the Restored Database 3-28
3.5.9 RMU/RESTORE/CONFIRM Command Produced A
Bugcheck Dump 3-29
3.5.10 RMU/VERIFY/CONSTRAINTS Command Worked
Incorrectly 3-29
3.5.11 RMU/BACKUP/ONLINE Command with Deleted Storage
Area 3-29
3.5.12 RMU/RESTORE Command Would Restore Deleted
Storage Area Files 3-29
CHAPTER 4 PROBLEMS, RESTRICTIONS, AND OTHER NOTES 4-1
4.1 GENERAL INFORMATION 4-1
4.1.1 Object Modules Created with V3.1 Are Not
Downward-Compatible 4-2
4.1.2 FIRST n Is Not Considered During
Optimization 4-2
4.1.3 Constraints Are Evaluated When Modifying a
Column with the Same Value 4-3
4.1.4 Performance Considerations for Using VARYING
STRING or COLLATING SEQUENCE Attribute for
Index Keys 4-3
xi
Contents
4.1.5 Index Retrieval of Tables from Views with
FIRST n Improperly Uses Booleans from Outer
Queries 4-5
4.1.6 Sorting or Any Implied Sorting for Projection
on a Dbkey Is Not Worthwhile 4-6
4.1.7 Many Attaches to and Detaches from the Same or
Multiple Databases While Using Search Lists
to Point to the Database Uses Up I/O Channel
Quota 4-6
4.1.8 Do Not Disable ASTs If You Want to Access a
Database Remotely 4-7
4.1.9 Unexpected Setting of the NULL Attribute After
an IMPORT Operation 4-7
4.1.10 IMPORT Statement Generates Bugcheck Dumps If
Index Definition Fails 4-8
4.1.11 RDO IMPORT Statement Will Not Accept an SQL
EXPORT File with ANSI-Style Protections
Defined 4-10
4.1.12 IMPORT Statement Failed to Complete Index
Definition With Users Bound to the Database 4-10
4.1.13 Using LIB$DT_INPUT_FORMAT to Change Date Input
Format Sometimes Causes Access Violation 4-10
4.1.14 Operations on F-Floating Data Round to Whole
Numbers 4-11
4.1.14.1 Rdb/VMS Interaction with Data Distributor V2.1 4-11
4.1.15 Batch-Update Transactions Can Cause a Bugcheck
Dump to Occur If an Index Definition Fails 4-12
4.1.16 Rdb/VMS Logical Name, RDMS$BIND_WORK_VM, Has
an Upper Limit of 65,000 Bytes 4-12
4.1.17 Reserving a Table in Exclusive Mode May
Prevent Operations from Being Performed on
Other Tables in the Same Storage Area 4-13
4.1.18 There Is a Problem Defining COLLATING SEQUENCE
IS NORWEGIAN NORWEGIAN 4-13
4.1.19 Rdb/VMS and VMS Debugger Interaction 4-15
4.1.20 RDB$DBKEY_LENGTH System Field Incorrect for
Certain Views 4-17
xii
Contents
4.1.21 Views with GROUP BY Cannot Be Retrieved by
Dbkey 4-17
4.1.22 Problem with the Use of Virtual Memory 4-18
4.2 NOTES AND RESTRICTIONS RELATED TO DSRI 4-18
4.2.1 RCI Instantiation Number Must Be Zero for
Remote Access 4-18
4.2.2 Having Context Variables That Are Not Unique
Within a Request Causes Invalid BLR 4-19
4.3 NOTES AND RESTRICTIONS RELATED TO CDD/PLUS 4-20
4.3.1 Incompatibilities Between Rdb/VMS V3.1 and
CDD/Plus 4-20
4.3.2 CDD/Plus V4.0 Interprets the Data Type
Incorrectly When a COMPUTED BY Field Is
Included in an Rdb/VMS Relation 4-21
4.3.3 CDD/Plus COMPUTED BY Fields Are Not Currently
Supported in Rdb/VMS Relations or Views 4-23
4.3.4 CDD/Plus Problem with the CHANGE RELATION
DEFINE FIELD Statement in Which Fields Added
Are Stored in the Data Dictionary in Reverse
Order 4-23
4.3.5 EXPORT WITH NOEXTENSIONS Statement Can Corrupt
the CDD$DATABASE 4-26
4.3.6 "Attribute Not Found" Error After
EXPORT/IMPORT of CDD$DATABASE 4-26
4.4 SQL PROBLEMS, RESTRICTIONS, AND NOTES 4-28
4.4.1 Database Administration and Maintenance 4-28
4.4.1.1 Disable VAX SQL/Services V1.0 Startup Procedure4-28
4.4.2 Data Definition and Data Manipulation 4-28
4.4.2.1 DDL Statements Cannot Refer to Objects Before Their
Creation 4-28
4.4.2.2 Deleting Metadata in Rdb/VMS 4-29
4.4.2.3 SQL Schema Compilation Fails on the First Fatal
Error 4-29
4.4.2.4 COMMENT ON Statement Cannot Be Used in CREATE
SCHEMA Statement 4-30
xiii
Contents
4.4.2.5 Problem with DROP TABLE Statement with Constraints
4-30
4.4.3 Programming 4-31
4.4.3.1 Dynamic Cursors Cannot Access Views Created with
GROUP BY or UNION Clause 4-31
4.4.3.2 When Using the BETWEEN Operator, the Lower Value
Must Be Specified First 4-31
4.4.3.3 Cannot Use INCLUDE Statement in Variable
Declaration 4-32
4.4.3.4 SQL Ada Precompiler Does Not Support Overloading of
Subprograms Correctly 4-32
4.4.3.5 SQL Precompiler Will Not Evaluate Expressions in
Variable Declarations or Understand Literals 4-33
4.4.3.6 SQL Ada Precompiler Does Not Support the Use of
Named Literals or Ranges 4-34
4.4.3.7 Limiting Length of File Names 4-34
4.4.3.8 Limiting Number of Characters Per Line 4-34
4.4.3.9 Limiting Number of Continuation Lines Per Record
4-35
4.4.3.10 SQL Module Language Processor Fails on the First
Fatal Error 4-35
4.5 RDO, RDBPRE, AND RDML PROBLEMS, RESTRICTIONS, AND
NOTES 4-35
4.5.1 Database Handle Problem on START_STREAM 4-36
4.5.2 RDO CHANGE INDEX Restriction Is Now Signaled 4-36
4.5.3 Problem of Different Optimizations of the Same
Query from Different Environments 4-37
4.5.4 Restrictions on Using Missing Value Fields in
Nested Queries 4-38
4.5.5 STORE WITHIN and DISABLE/ENABLE COMPRESSION
Clauses Cannot Both Be Specified 4-39
4.5.6 When Entering an ACL entry, Argument to the
Position Clause Can Not Exceed 255 4-40
4.5.7 Variables Cannot Be Database Handles 4-40
4.5.8 RDML Run-Time Object Library No Longer
Requires You to Link Against VAXCRTL or
xiv
Contents
VAXCRTLG Object Libraries or Shareable
Images 4-43
4.5.9 RDML/EPascal Ignores /LINKAGE=PROGRAM_SECTION
Qualifier 4-43
4.5.10 RDML Does Not Support MCS Characters in
Database Object Names 4-43
4.5.11 RDML Does Not Detect Incorrect Context
Variables in the WITH Clause 4-43
4.5.12 RDML/Pascal Does Not Understand Some Character
String Value Expressions 4-44
4.5.13 RDML/Pascal Does Not Accept All Possible Valid
Pascal Host Language Variables 4-45
4.5.14 RDML TOTAL Function Only Uses Numeric Data
Types 4-45
4.5.15 RDML Does Not Allow Nested Comments 4-46
4.6 RDB/VMS MANAGEMENT UTILITY (RMU) 4-46
4.6.1 Use of the /USERS_MAX and /NODES_MAX
Qualifiers with the RMU/RESTORE Command
Requires That Both Qualifiers Must Be on the
First Line of DCL Input 4-46
4.6.2 A Snapshot File Name, File Type, or Version
Number Cannot Be Changed for Single-File
Databases 4-47
4.6.3 There Is a 17-Character Limit for File Names
When Backing Up Databases to Tape 4-47
4.6.4 RMU/DUMP/BACKUP Command Specifying a Value of
1 or 2 for the /ACTIVE_IO Qualifier Causes the
AIJ Dump to Stall 4-48
4.6.5 RMU/SHOW STATISTICS Command Does Not Record
All Statistics in the Binary File 4-49
4.6.6 RMU/CONVERT Command Restriction and Notes 4-49
4.6.7 Dumping the AIJ File Is Incompatible with
Normal Usage 4-50
4.6.8 RMU/RESTORE Command May Initialize the SPAM
Thresholds in One or More Storage Areas 4-51
4.7 RDB/VMS DOCUMENTATION ERRORS 4-51
xv
Contents
4.7.1 Documentation for RDM$MONITOR Logical Name was
Incorrect 4-52
4.7.2 Corrections to the VAX Rdb/VMS Multifile
Databases Poster 4-52
4.7.3 Additional Information for /UNTIL Qualifier in
RMU Help File 4-53
4.7.4 Correction to the Usage Note on Constraints
with the CREATE TABLE Statement 4-54
4.7.5 Using Rdb/VMS from a VMS Detached Process 4-54
APPENDIX A SQL/SERVICES V3.1 RELEASE NOTES A-1
A.1 NEW AND CHANGED FEATURES A-1
A.1.1 A New ULTRIX API for the DECstation Series of
Processors A-1
A.2 PROBLEMS, RESTRICTIONS, AND OTHER NOTES A-3
A.2.1 Filter Expressions in SQL/Services A-3
A.2.2 RISC/ULTRIX API Requires Applications to
Provide User Name A-3
APPENDIX B INTERNATIONALIZATION SUPPORT IN V3.1 B-1
B.1 DATE AND TIME SUPPORT B-1
B.1.1 Input Date Formatting B-2
B.1.2 Output Date Formatting B-7
B.1.3 Summary of Statements Used to Change Date and
Time B-10
B.1.3.1 RDO: SHOW DATE_FORMAT Statement B-10
B.1.3.2 RDO: SET DATE_FORMAT Statement B-15
B.1.3.3 RDO: SET LANGUAGE Statement B-17
B.1.3.4 RDO: SHOW LANGUAGE Statement B-19
B.1.3.5 SQL: SHOW DATE FORMAT Statement B-19
B.1.3.6 SQL: SET DATE FORMAT Statement B-22
B.1.3.7 SQL: SET LANGUAGE Statement B-24
B.1.3.8 SQL: SHOW LANGUAGE Statement B-25
xvi
Contents
B.1.4 Other Date Usage B-25
B.1.4.1 RDO Usage B-25
B.1.4.2 SQL Usage B-27
B.1.4.3 RMU Usage B-28
B.2 COLLATING SEQUENCE SUPPORT B-28
B.2.1 How to Define Collating Sequences B-28
B.2.2 Using Collating Sequences B-31
B.3 MCS CHARACTERS IN RDB/VMS METADATA NAMES B-32
EXAMPLES
1-1 Procedure for Automatic Conversion of Rdb/VMS
Databases 1-11
B-1 Input Date Formatting B-3
B-2 Showing the Default Format B-4
B-3 Error Caused by Alternate Date Format B-4
B-4 Effects of the SQL SET LANGUAGE Statement B-5
B-5 Use of TOMORROW Date Literal in Interactive RDO B-6
B-6 RDO Output Date Formatting B-8
B-7 SQL Input and Output Date Formatting B-9
B-8 Log of RDO SHOW DATE_FORMAT Output B-11
B-9 RDO SHOW DATE_FORMAT Errors B-15
B-10 Use of Different Date and Time Settings B-17
B-11 Output of RDO SHOW DATE_FORMAT Statement for
Different Languages B-18
B-12 Log of SQL SET and SHOW DATE FORMAT Statements B-19
B-13 Output of SQL Query In Different Date Formats B-24
xvii
Contents
TABLES
2-1 SQL Sample and IVP Directories 2-2
2-2 The Metadata Updates Where Concurrency Is
or Is Not Allowed and Whether the Metadata
Update Operation Must Execute in a Read/Write
Transaction 2-12
3-1 Differences in Relational Terminology 3-1
xviii
_____________________________________________________________________
Preface
VAX Rdb/VMS software, Version 3.1, often referred to as Rdb/VMS
V3.1 in this manual, is a general purpose database management
system based on the relational data model.
This manual describes new and changed features, problems fixed in
this release, and current problems, restrictions, and other notes.
NOTE
The release notes are supplied both in printed form and
in online form (in SYS$HELP). For version 3.1, the online
form was prepared after the printed form; thus, informa-
tion in the online copy in SYS$HELP is more up-to-date.
Differences between the online copy and the printed copy
are highlighted with change bars in the online copy.
The version previous to V3.1 is referred to throughout
this manual as Version 3.0. The term "Version 3.0" refers
to Version 3.0 and any updates to Version 3.0; thus, for
example, a reference to "the Version 3.0 behavior" of a
statement refers to the behavior under Version 3.0 and any
of its updates.
References to a specific update (for example, Version 3.0A
or Version 3.0B) 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.
xv
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 and RDO 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.
The VAX Rdb/VMS Introduction and Master Index contains a more
detailed list of SQL terms and their RDO equivalents.
___________________________________________________________________
Operating System Information
Information about the versions of the operating system and related
software that are compatible with this version of Rdb/VMS is
included in the Rdb/VMS media kit and the VAX Rdb/VMS Installation
Guide.
For information of the compatibility of other software products
with this version of Rdb/VMS, refer to the System Support Addendum
(SSA) that comes 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.
xvi
Preface
___________________________________________________________________
Structure
This manual contains four chapters and two appendixes:
Chapter 1 Describes information on converting databases
from previous versions of Rdb/VMS.
Chapter 2 Summarizes the new and changed features of
Rdb/VMS V3.1.
Chapter 3 Describes known software errors that were fixed
in versions prior to Version 3.1.
Chapter 4 Describes problems, restrictions, and workarounds
known to exist in Rdb/VMS; may also include other
information.
Appendix A Contains information for users of SQL/Services,
which is included with Rdb/VMS V3.1.
Appendix B Contains detailed information on the implementa-
tion of internationalization support.
___________________________________________________________________
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
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 Database Design and Definition
xvii
Preface
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.
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 product that allows pro-
grams running on various types of computers to access DIGITAL
Standard Relational Interface (DSRI) compliant databases on
other computers.
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.
xviii
Preface
o VAX Rdb/VMS SQL Quick Reference Guide
Summarizes the information in the VAX Rdb/VMS SQL Reference
Manual.
xix
Preface
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.
o VAX Rdb/VMS Installation Guide
Describes how to install Rdb/VMS.
___________________________________________________________________
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.
xx
Preface
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.
< > 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 VAX ACMS software is referred to as ACMS.
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 or the data
dictionary.
o VAX COBOL software is referred to as COBOL.
o VAX DATATRIEVE software is referred to as DATATRIEVE.
xxi
Preface
o VAX Data Distributor software is referred to as Data Distributor.
o VAX FORTRAN software is referred to as FORTRAN.
o VAXELN Pascal and VAX Pascal are both referred to as Pascal
except when the use of a Relational Data Manipulation Language
(RDML) statement is not the same in the VAXELN and VMS environ-
ments. In the latter case, either VAXELN Pascal or VAX Pascal
is specified.
o VAX RALLY software is referred to as RALLY.
o VAX Rdb/VMS software is referred to as Rdb/VMS. Version 3.1 of
VAX Rdb/VMS software is often referred to as Rdb/VMS V3.1.
o VAX SQL software is referred to as VAX SQL whenever it is
correct to refer to Version 2.0 or earlier of SQL. The use
of SQL by itself indicates the SQL interface now included as
part of the VAX Rdb/VMS Version 3.1 is referred to as SQL.
o VAX TEAMDATA software is referred to as TEAMDATA.
o VAX TDMS software is referred to as TDMS.
o VIDA software is referred to as VIDA.
xxii
Chapter 1
Conversion to Rdb/VMS Version 3.1
This chapter provides information on converting databases from
previous Rdb/VMS versions and the factors you must consider when
upgrading your system to Rdb/VMS Version 3.1.
1.1 Rdb/VMS Version 3.1 Requires VMS Version 5.2 or Higher
The Rdb/VMS V3.1 kit will require VMS Version 5.2 (V5.2) or
higher.
1.2 Remote IVP May Fail on Installation of Rdb/VMS Version 3.1
If VMS V5.2 and a previous version of Rdb/VMS were installed on
your system, the remote Installation Verification Procedure (IVP)
may fail when you install Rdb/VMS V3.1. If the IVP fails for the
remote account, you may receive the following errors:
Conversion to Rdb/VMS Version 3.1 1-1
.
.
.
Running the remote database test.
***** The remote database test failed *****
See SYS$UPDATE:RDBIVP_REMOTE.LOG for errors.
.
.
.
After the VMSINSTAL is finished type out the log from the failed
IVP as follows:
$ TYPE SYS$UPDATE:RDBIVP_REMOTE.LOG
%RDB-F-IO_ERROR, input or output error
-SYSTEM-F-INVLOGIN, login information invalid at remote node
%RDO-F-INVNOTDON, no DATABASE invoked yet, please issue a DATABASE command
If you get the errors shown in the preceding example, it is most
likely that the RDB$REMOTE account is set up with the DISUSER
flag. VMS V5.2 now sets up the default account with the DISUSER
flag enabled. Due to security considerations you will have to
manually disable the DISUSER flag, as shown in the following
example:
$ SET DEFAULT SYS$SYSTEM
$ RUN AUTHORIZE
UAF> SHOW RDB$REMOTE
1-2 Conversion to Rdb/VMS Version 3.1
Username: RDB$REMOTE Owner:
Account: UIC: [377,101] ([RDB$REMOTE])
CLI: DCL Tables: DCLTABLES
Default: SYS$COMMON:[RDB$REMOTE]
LGICMD: SYS$SYSTEM:RDB$REMOTE_LOGIN.COM
Login Flags: Disctly Defcli Lockpwd Disuser Dismail Disreconnect
.
.
.
UAF> MODIFY RDB$REMOTE/FLAG=NODISUSER
UAF-I-MDFYMSG, user record(s) updated
UAF> SHOW RDB$REMOTE
Username: RDB$REMOTE Owner:
Account: UIC: [377,101] ([RDB$REMOTE])
CLI: DCL Tables: DCLTABLES
Default: SYS$COMMON:[RDB$REMOTE]
LGICMD: SYS$SYSTEM:RDB$REMOTE_LOGIN.COM
Login Flags: Disctly Defcli Lockpwd Dismail Disreconnect
.
.
.
Once the DISUSER flag is disabled in the RDB$REMOTE account, the
IVP can be rerun. The IVP failure in this case does not indicate
an installation error.
1.3 Database Format Incompatible with Previous Version of Rdb/VMS
To support major new capabilities available in Rdb/VMS V3.1, the
RMU/CONVERT command makes significant changes to the Rdb/VMS sys-
tem relations. These changes include adding new system relations
(for example RDBVMS$COLLATIONS, RDBVMS$TRIGGERS), new fields for
many tables (such as RDBVMS$COLLATION_NAME, RDB$DEFAULT_VALUE2),
and several new indexes.
Conversion to Rdb/VMS Version 3.1 1-3
1.4 V3.0, V3.0A, and V3.0B Databases Must Be Converted Using the
RMU/CONVERT Command
This section assumes that the databases to be converted are in
Version 3.0, Version 3.0A, or Version 3.0B format. If you have
databases in a format prior to Version 3.0 that you wish to use
with Version 3.1, see Section 4.6.6.
NOTE
The conversion applies to all CDD/Plus databases (CDD$DATABASE.RDB)
that you may be using including those in the directories
CDD$TEMPLATE and CDD$COMPATIBILITY. Follow the conver-
sion instructions in Section 1.7 before performing any
operations that require the use of CDD/Plus.
NOTE
The conversion steps in this section also apply to any VAX
Data Distributor databases that you may be using, because
Data Distributor creates transfer databases in Rdb/VMS
database format. Use the SQL or RDO EXPORT and IMPORT
statements for VAX Data Distributor, VAX DECdecision, and
VAX TEAMDATA transfer databases as you would for any other
Rdb/VMS database.
The pre-installation and post-installation steps are covered in
the VAX Rdb/VMS Installation Guide. Briefly, you perform these
tasks:
1.Make sure all existing Rdb/VMS databases are recovered before
installing the software. The recovery-unit journal (RUJ) files
created prior to Rdb/VMS V3.1 cannot be applied to Version 3.1,
Rdb/VMS databases due to internal structure differences.
2.Back up all existing databases by using the RMU/BACKUP command,
including CDD/Plus and Data Distributor databases.
1-4 Conversion to Rdb/VMS Version 3.1
NOTE
The installation procedure now asks if all databases,
especially CDD/Plus databases, were exported. If the
response is no, that the databases have not exported,
the installation will terminate.
3.Use the EXPORT statement in RDO or SQL on each of your existing
Rdb/VMS databases. (This step is necessary if the RMU/CONVERT
operation fails; if it does, you use the IMPORT statement after
the installation on each of these databases.)
4.Install Rdb/VMS V3.1 software.
5.Use the RMU/CONVERT command. If it fails for some reason,
submit a Software Performance Report (SPR) and use the IMPORT
statement in SQL or RDO to convert each exported database into
a database with the new format.
6.After the conversion, again back up all existing databases with
the RMU/BACKUP command so that a post-conversion backup file
exists.
IMPORTANT
Once an Rdb/VMS V3.0 database file is converted to Version
3.1, you cannot use that file with Version 3.0.
1.5 RMU/CONVERT Command Requires Privileges
The RMU/CONVERT command makes extensive modifications to the
Rdb/VMS system relations and must be run with SYSPRV enabled.
This will either require privileged users (such as the system
manager) to convert each database or have the RMU/CONVERT image
installed with SYSPRV.
$ INSTALL ADD SYS$SYSTEM:RMUCONVERT/PRIV=SYSPRV
Conversion to Rdb/VMS Version 3.1 1-5
The latter alternative will allow each user to convert their own
private databases. However, it will also allow them to convert any
database on the VMS system.
The RMU/CONVERT command is a single-function utility, that nor-
mally has a short useful lifetime on any system. Once all the
databases have been converted, it is no longer useful. If the sys-
tem manager installs RMUCONVERT.EXE, Digital recommends that the
image be deinstalled a few weeks after the installation to save
system resources.
1.6 Changes in the RMU/CONVERT Command for V3.1
The RMU/CONVERT command now behaves slightly differently than in
previous versions of Rdb/VMS.
If the Version 3.0 database to be converted has after-image jour-
naling enabled, RMU prompts you to determine if you want after-
image journaling disabled so that the conversion can continue. If
you reply Y (for YES), RMU disables after-image journaling, con-
verts the database, and then enables after-image journaling again
with an AIJ file of the same name and next higher version number.
If you reply N (for NO), the RMU/CONVERT operation fails and RMU
responds with an error message telling you the database was not
converted.
The following example shows that a database with after-image
journaling enabled is not converted if you choose not to disable
after-image journaling:
$ RMU/CONVERT PERSONNEL.RDB
Are you satisfied with your backup of DISK2:[USER]PERSONNEL.RDB;2 [N]? Y
After-image journaling will be disabled if the RMU/CONVERT of
DISK2:[USER]PERSONNEL.RDB;2 continues. Do you wish to proceed [N]? N
%RMU-F-DB_NOTCVT, database not converted
%RMU-F-SEVERRDET, a severe error was detected
1-6 Conversion to Rdb/VMS Version 3.1
The following example shows that a database with after-image
journaling enabled is converted and that a new version of the
AIJ file, A.AIJ;2, is created if you choose to disable after-image
journaling:
$ RMU/CONVERT PERSONNEL.RDB
Are you satisfied with your backup of DISK2:[USER]PERSONNEL.RDB;2 [N]? Y
After-image journaling will be disabled if the RMU/CONVERT of
DISK2:[USER]PERSONNEL.RDB;2 continues. Do you wish to proceed [N]? Y
%RMU-I-CVT_PROCEED, convert proceeding
%RDMS-I-LOGCREAIJ, create after-image journal file. DVD01:[USER1]A.AIJ;2
%RMU-S_DB_CVT_SCS, database successfully converted
from version 3.0 to 3.1
It is important to remember to perform an RMU/BACKUP operation
after database conversion so a new, post-conversion backup file
exists.
1.7 Converting CDD/Plus Databases If the RMU/CONVERT Command Fails
The steps outlined in Section 1.3 apply to conversion of CDD/Plus
databases also. This section describes pre-installation and con-
version details specifically for CDD/Plus databases. The method of
using the IMPORT statement to convert databases is necessary only
if the RMU/CONVERT command fails.
Before installing Rdb/VMS V3.1, perform the following operations
for each CDD/Plus dictionary anchor:
1.Make sure there are no active users of your dictionary.
2.Give yourself VMS SYSPRV or BYPASS privileges.
3.Depending on which user interface you use, SQL or RDO:
o In SQL:
Conversion to Rdb/VMS Version 3.1 1-7
$ RUN SYS$SYSTEM:SQL$
SQL> EXPORT SCHEMA FILENAME anchor:CDD$DATABASE INTO SAVE.BCK;
or
o In RDO:
$ RUN SYS$SYSTEM:RDO
RDO> EXPORT anchor:CDD$DATABASE INTO SAVE.BCK
NOTE
Make sure you do not use the NOEXTENSIONS argument on
the EXPORT statement. See Section 4.3.5.
4.
$ RENAME CDD$DATABASE.RDB, .SNP SAVE.*
1-8 Conversion to Rdb/VMS Version 3.1
Install Rdb/VMS V3.1 and attempt the RMU/CONVERT operation. If the
RMU/CONVERT operation fails, perform the following steps:
1.Depending on which user interface you use, SQL or RDO:
o In SQL:
$ RUN SYS$SYSTEM:SQL
SQL> IMPORT SCHEMA FROM SAVE.BCK FILENAME CDD$DATABASE.RDB;1;
or
o In RDO:
$ RUN SYS$SYSTEM:RDO
RDO> IMPORT SAVE.BCK CDD$DATABASE.RDB;1
cont> DICTIONARY IS NOT USED
cont> END IMPORT.
2.
$ DELETE SAVE.*;
1.8 Command Procedure for Automatic Conversion of All Existing
Databases
This section describes a sample command procedure that can be used
to automatically convert all databases on your system. The command
procedure is an example and is intended for use on large systems
with many Rdb/VMS databases.
NOTE
Digital recommends that before using an automatic conver-
sion procedure, you use the RDO or SQL IMPORT statement ,
and the RMU/BACKUP command to preserve the databases before
upgrading to a new version of Rdb/VMS. See the VAX Rdb/VMS
Installation Guide for details.
Conversion to Rdb/VMS Version 3.1 1-9
To automatically convert all databases on your system, perform
these steps:
1.Provide the command procedure with a list of disks, and it will
locate all database root (RDB) files.
2.Submit the procedure to the batch queue or interactively exe-
cute the procedure.
If the job is submitted to the batch queue, you should re-
view the log file and correct any errors that may have been
reported.
3.If any database fails to convert, rebuild it using the RDO
or SQL IMPORT statement using an EXPORT file created with the
previous version of Rdb/VMS.
For automatic conversion of all the Rdb/VMS databases on a system,
use a command procedure similar to Example 1-1.
1-10 Conversion to Rdb/VMS Version 3.1
Example 1-1: Procedure for Automatic Conversion of Rdb/VMS
Databases
__________________________________________________________________
$ SAVE_VERIFY = 'F$VERIFY(0)'
$!-----------------------------------------------------------------------------
$! CONVERT_ALL
$! This procedure processes all the .RDB files on the named
$! disk and converts then using RMU/CONVERT
$!
$! Parameters:
$! P1 list of disks to process. Defaults to
$! login disk of the submitter. Disk names
$! should be separated by commas (,).
$!
$!-----------------------------------------------------------------------------
__________________________________________________________________
Example 1-1 Cont'd on next page
Conversion to Rdb/VMS Version 3.1 1-11
Example 1-1 (Cont.): Procedure for Automatic Conversion of
Rdb/VMS Databases
__________________________________________________________________
$
$ COMMA = ","
$
$ IF P1 .EQS. ""
$ THEN
$ !
$ ! The default disk is used
$ !
$ DISK_LIST = "SYS$DISK"
$ ELSE
$ DISK_LIST = F$EDIT(P1,"UPCASE,COLLAPSE")
$ ENDIF
$ SET PROCESS/PRIV=SYSPRV
$
$ SET NOON
$ LOOP = 0
$
$ WRITE SYS$OUTPUT "----------------------------------------------------"
$ WRITE SYS$OUTPUT "Database Converter procedure"
$ WRITE SYS$OUTPUT "(Please ignore possible DCL errors like"
$ WRITE SYS$OUTPUT -
" %DCL-W-SKPDAT, image data (records not beginning with "$") ignored)"
$ WRITE SYS$OUTPUT ""
$
$ GET_DISK:
$ !
$ ! Fetch the next disk name
$ !
$ DISK = F$ELEMENT(LOOP, COMMA, DISK_LIST)
$ IF DISK .EQS. COMMA THEN GOTO ENDGET_DISK
$ DISK = DISK - ":" + ":"
$ LOOP = LOOP + 1
__________________________________________________________________
Example 1-1 Cont'd on next page
1-12 Conversion to Rdb/VMS Version 3.1
Example 1-1 (Cont.): Procedure for Automatic Conversion of
Rdb/VMS Databases
__________________________________________________________________
$
$ DEVICE = F$PARSE(DISK,,,"DEVICE")
$ FULL_DEVICE = F$GETDVI(DEVICE,"FULLDEVNAM")
$ DISPLAY_DEVICE = F$GETDVI(DEVICE,"DISPLAY_DEVNAM")
$
$ !
$ ! Display disk name
$ !
$ WRITE SYS$OUTPUT "----------------------------------------------------"
$ WRITE SYS$OUTPUT F$FAO("Processing !AS (!AS)", DEVICE, DISPLAY_DEVICE)
$ WRITE SYS$OUTPUT F$FAO(" at !%D", 0)
$ WRITE SYS$OUTPUT "----------------------------------------------------"
$
$ FILE_COUNT = 0
$
$ GET_FILE:
$ DATABASE = F$SEARCH(FULL_DEVICE + "[000000...]*.RDB;*")
$ IF DATABASE .EQS. "" THEN GOTO ENDGET_FILE
$ FILE_COUNT = FILE_COUNT + 1
$ WRITE SYS$OUTPUT ""
$ WRITE SYS$OUTPUT F$FAO("Converting !AS", DATABASE)
$ DEFINE/USER SYS$COMMAND SYS$INPUT
$ !
$ ! Convert the database. Ignore possible "image data ignored" errors
$ ! because there is an optional second prompt if the AIJ is enabled.
$ !
$ RMU/CONVERT 'DATABASE'
$ Y
$ Y
$ GOTO GET_FILE
$ ENDGET_FILE:
__________________________________________________________________
Example 1-1 Cont'd on next page
Conversion to Rdb/VMS Version 3.1 1-13
Example 1-1 (Cont.): Procedure for Automatic Conversion of
Rdb/VMS Databases
__________________________________________________________________
$
$ WRITE SYS$OUTPUT ""
$ WRITE SYS$OUTPUT F$FAO("!UL databases processed.", FILE_COUNT)
$ WRITE SYS$OUTPUT ""
$ GOTO GET_DISK
$
$ ENDGET_DISK:
$ WRITE SYS$OUTPUT "----------------------------------------------------"
$ WRITE SYS$OUTPUT F$FAO("Completed Processing at !%D", 0)
$ WRITE SYS$OUTPUT "----------------------------------------------------"
$ SET ON
$ IF SAVE_VERIFY THEN SET VERIFY
$ EXIT
__________________________________________________________________
1-14 Conversion to Rdb/VMS Version 3.1
Chapter 2
New and Changed Features
This chapter provides a summary of the new features and technical
changes in Rdb/VMS V3.1.
2.1 VAX SQL and VAX SQL/Services Now Part of VAX Rdb/VMS
VAX SQL and VAX SQL/Services are no longer sold as separate prod-
ucts. The installation for Rdb/VMS V3.1 also installs the SQL
interface and the SQL/Services server.
Effective with Version 3.1, Rdb/VMS no longer uses the following
directories:
o SYS$SYSROOT:[SYSHLP.EXAMPLES.SQL]
o SYS$COMMON:[SYSTEST.SQL]
Instead, all SQL-related example and IVP files are placed in
directories with Rdb/VMS naming orientations, as shown in
Table 2-1.
New and Changed Features 2-1
Table_2-1:__SQL_Sample_and_IVP_Directories___________________________
Old_Location_________________New_Location____________________________
SYS$SYSROOT:[SYSHLP.EXAMPLES.SYS$COMMON:[SYSHLP.EXAMPLES.RDBVMS]
SYS$COMMON:[SYSTEST.SQL]_____SYS$COMMON:[SYSTEST.RDBVMS]_____________
Note that the logical name SQL$SAMPLE is still defined; however,
it now has the same equivalence name as the logical name RDM$DEMO,
that is, SYS$COMMON:[SYSHLP.EXAMPLES.RDBVMS]. Files from the old
SQL sample directory that are still used with Version 3.1 are
placed in this common directory.
The installation procedure does not delete any files from the
old SQL IVP or sample directories. Thus, to conserve disk space,
you may wish delete the contents of these old directories. (If
you have stored any private files in those directories, save them
first.)
2.2 Referential Integrity Support
Referential integrity refers to the consistency of related pieces
of information across multiple tables in a database. Normally
this involves ensuring the integrity of primary key and for-
eign key relationships. Using the sample personnel database
(see Section 2.28.1) as an example, you want each piece of salary
history information in the SALARY_HISTORY table to match an ac-
tual employee listed in the EMPLOYEES table; it would violate
the referential integrity of the database to have a row in the
SALARY_HISTORY table whose EMPLOYEE_ID column does not match the
EMPLOYEE_ID value in a row in the EMPLOYEES table.
With Rdb/VMS V3.1, you can designate a column (or group of
columns) in a table as the primary key. The primary key uniquely
identifies any row in the table; the primary key must contain val-
ues that are unique (no duplicates) and not null. You can also
specify one or more columns in a table as having foreign key rela-
tionships, that is, as referencing a column in another table. If
2-2 New and Changed Features
a column is so designated, then there must be at least one row in
the other table with a matching column value for every row in this
table (for example, a row in the EMPLOYEES table with an EMPLOYEE_
ID column match for every row in the SALARY_HISTORY table).
The SQL and RDO statements that apply to referential integrity are
included in the lists in Section 2.12 and Section 2.22; they are
fully documented in the VAX Rdb/VMS SQL Reference Manual and the
VAX Rdb/VMS RDO and RMU Reference Manual.
Note that this capability was previously available using the RDO
DEFINE CONSTRAINT statement, but that this additional approach
provides SQL syntax that conforms with the ANSI/ISO standard (SQL-
89 formerly known as SQL-86 Addendum-1). The RDO functionality is
still available.
2.3 Triggers
A trigger is a definition that specifies one or more actions to
be performed automatically whenever a certain operation is per-
formed. Triggers are often used to ensure that logically related
operations involving multiple tables are done automatically at
the same time, thus avoiding the risk of an inconsistent database
and reducing the burden on programmers writing database applica-
tions. For example, to ensure that all information related to an
employee is deleted when an employee leaves the company, you can
define a trigger specifying that whenever a row is deleted from
the EMPLOYEES table, all rows with an EMPLOYEE_ID column match
should also be deleted from the JOB_HISTORY, SALARY_HISTORY, and
DEGREES tables-this type of operation is sometimes called a cas-
cading deletion. (In fact, such a trigger is defined in the sample
database that you can create-see Section 2.28.1.)
The SQL and RDO statements that apply to triggers are included
in the lists in Section 2.12 and Section 2.22; they are fully
documented in the VAX Rdb/VMS SQL Reference Manual and the VAX
Rdb/VMS RDO and RMU Reference Manual.
New and Changed Features 2-3
2.3.1 Note on the Timing of Trigger Actions
The execution of a trigger action is not guaranteed to occur at
any specific point within the transaction; the only guarantee
is that the cumulative impact of any trigger actions will be
in effect when the transaction is committed. Thus, you should
not assume that any specific trigger action will be executed
immediately after the statement that triggered it.
For example, assume that the following trigger has been defined to
calculate the next sequence number to be assigned (by adding 1 to
the count of orders):
SQL> CREATE TRIGGER SEQUENCE_NUM_TRIG AFTER INSERT ON ORDERS_TABLE
cont> (UPDATE SEQ_TABLE SET SEQ_TABLE.NUMBER = (SELECT COUNT (*)
cont> FROM ORDERS_TABLE) + 1) FOR EACH ROW;
Assume that the ORDERS_TABLE table contains 99 rows, and the value
of SEQ_TABLE.NUMBER is 100. Your application then stores 10 new
rows in the ORDERS_TABLE table within a single transaction. Under
the current implementation, each row insertion causes the SEQ_
TABLE.NUMBER value to be updated; thus, after the 100th ORDERS_
TABLE row is inserted, the value of NUMBER is set to 101; after
the 101st ORDERS_TABLE row is inserted, the value of NUMBER is set
to 102; and so forth.
However, this implementation may change in the future so that
the trigger actions are performed at the end of the transaction,
thus causing the value of SEQ_TABLE.NUMBER to increase from 100
to 110 only when the transaction is committed (that is, after all
10 insertions). Therefore, be sure to design applications that
do not depend on a particular timing of trigger actions within a
transaction.
2-4 New and Changed Features
2.3.2 Cautions on Selecting Column Name Lists for SQL UPDATE or RDO
MODIFY Statement
Each SQL UPDATE or RDO MODIFY statement specifies some number of
columns or fields to be changed, either explicitly by using a list
of column names or field names, or implicitly as in the RDO MODIFY
USING * statement where the modification occurs at the record
level, implying all fields in the relation.
When changing the values of columns (fields) in a table (rela-
tion), make sure to only specify those columns whose data values
are to actually be changed, to avoid potentially unnecessary ac-
tions such as:
o Overlaying the data with itself within a row
o Writing to the database (even though none of columns in the row
has actually changed values)
o Evaluating constraints that apply to columns in the UPDATE
column list which have not changed values
o Evaluating UPDATE triggers that apply to columns in the UPDATE
column list that have not changed values
o Evaluating RDO VALID IF clauses for fields in the MODIFY field
list that have not changed values
If there is a possibility that any of the columns in an UPDATE
column list will not actually be changed, the triggered actions
for any pertinent UPDATE triggers should be defined accordingly.
For those cases in which a triggered action performs an operation
based on the changed value for a particular column, the action
should include a conditional that prevents execution of the oper-
ation if no value change occurs. The conditional, which compares
the old value with the new value, should appear as part of the
triggered action's SQL WHEN clause or RDO WITH clause.
New and Changed Features 2-5
The following example shows an UPDATE trigger with two actions,
where the first action is defined to account for the situation
where the triggering UPDATE statement has not actually changed the
value for the pertinent column (EMPLOYEE_ID). This trigger:
o Causes a cascading update of the EMPLOYEES table EMPLOYEE_ID
value to the JOB_HISTORY table
The cascading update will only occur when the EMPLOYEE_ID value
actually changes due to the presence of the WHEN clause.
o Logs each UPDATE operation to the EMPLOYEES table in the table
LOG
CREATE TABLE LOG (TYPE CHAR(10), TBL CHAR(31), WHO CHAR(40));
CREATE TRIGGER TRIG1
AFTER UPDATE OF EMPLOYEES_ID ON EMPLOYEES
REFERENCING NEW AS NEW_EMP OLD AS OLD_EMP
WHEN NEW_EMP.EMPLOYEE_ID <> OLD_EMP.EMPLOYEE_ID
(UPDATE JOB_HISTORY JH
SET JH.EMPLOYEE_ID = NEW_EMP.EMPLOYEE_ID
WHERE JH.EMPLOYEE_ID = OLD_EMP.EMPLOYEE_ID)
FOR EACH ROW
(INSERT INTO LOG VALUES ('Update', 'EMPLOYEES', USER))
FOR EACH ROW;
2.4 Read-Only Storage Areas
You can designate a storage area of a multifile database as read-
only to improve performance of data retrieval applications that do
not need to add, delete, or modify information in tables stored in
that storage area. If you designate a storage area as read-only,
Rdb/VMS does not perform certain checks and locking operations,
thus reducing software overhead for the application. (To write to
a read-only storage area, you must first explicitly designate the
storage area as read/write.)
2-6 New and Changed Features
Read-only storage areas are designed to be especially beneficial
for data stored on CDROM disks. Because CDROM disks are inherently
read-only (after initial storage of data), there are performance
benefits and no disadvantages in designating storage areas on
CDROM disks as read-only.
In addition to specifying storage areas for user data as read-
only, you can also change the RDB$SYSTEM storage area to be read-
only. This eliminates I/O operations that the Rdb/VMS software
performs to update system relations (primarily the cardinality
values). However, while the RDB$SYSTEM storage area is designated
read-only, you cannot execute data definition statements because
those statements update system relations.
Making the RDB$SYSTEM storage area read-only may be appropriate
and beneficial when the database is large, when improved appli-
cation performance is important, and when you expect to make no
further changes to the database metadata (or when such changes
will be infrequent).
When RDB$SYSTEM is made read-only, automatic updates to table and
index cardinality are disabled. If you use read-only storage areas
and if there are any insert and delete operations affecting tables
and indexes in those storage areas, you may need or want to update
the cardinality values. To do so, use the RMU/ANALYZE/CARDINALITY
command. See Section 2.26.
The SQL and RDO statements that apply to read-only storage areas
are included in the lists in Section 2.12 and Section 2.22; they
are fully documented in the VAX Rdb/VMS SQL Reference Manual and
the VAX Rdb/VMS RDO and RMU Reference Manual.
2.5 Internationalization Support
Rdb/VMS V3.1 provides several options that are useful when the
data in the database is not in English or when the user's primary
language is not English. These options fall into two categories:
o Statements to control the format of date and time display
New and Changed Features 2-7
o Collating sequence specification (to control sorting and com-
parisons)
2.5.1 Controlling Input and Display Format
You can enter statements to modify the input and display format
for the following:
o Radix point character
o Digit separator character (SQL only)
o Currency indicator character (SQL only)
o Date and time format
o Language used for various input and displays, such as day
names, month names, and so on
The SET and SHOW statements related to these features are included
in the lists in Section 2.12, and Section 2.22, and Appendix B;
they are fully documented in the VAX Rdb/VMS SQL Reference Manual
and the VAX Rdb/VMS RDO and RMU Reference Manual.
2.5.2 Specifying Collating Sequence
By default, Rdb/VMS uses the ASCII collating sequence for all
sorting and Boolean operations; however, you can override this
default by specifying one of the following:
o One of the language-specific collating sequences supplied by
the VMS National Character Set (NCS) Utility
o A user-defined collating sequence using NCS
2-8 New and Changed Features
You can specify collating sequences for particular domains. The
collating sequence determines how rows are sorted when the column
is used as a sort key. The collating sequence also determines
the behavior of Boolean operations that compare two columns or a
column with a literal value.
The following list details some specific behavior for collating
sequences:
o CONTAINING predicate
CONTAINING is not sensitive to diacritical markings nor is it
case sensitive. Thus "a" matches "A","á", "à", "ä", "Á", "À",
"Â" and so on. (Note that in Norwegian, "ä" is treated as if it
were "ae".)
In Spanish, "ch" and "ll" are treated as if they were individ-
ual unique single letters. Thus, CONTAINING "C" will find "C",
"c", "ç", and "Ç" but not "CH", "ch", "Ch" and "cH".
o MATCHING predicate
MATCHING is not sensitive to diacritical markings nor is it
case sensitive. Thus "a" matches "A","á", "à", "ä", "Á", "À",
"Â" and so on. (Note that in Norwegian, "ä" is treated as if it
were "ae".)
In Spanish, the combinations "ch" and "ll" are each treated as
individual unique single letters. If you define your collating
sequence as SPANISH, the percent sign (%) matches any single
letter, including "ch" and "ll". "C%" and "C*" do not match
"CH", "ch", "Ch" or "cH".
o STARTING WITH predicate
Because STARTING WITH is case sensitive, searches for uppercase
multinational characters will not include lowercase multina-
tional characters, and vice versa. For example, STARTING WITH
"Ç" will retrieve a set of records that is different from those
retrieved by STARTING WITH "ç".
New and Changed Features 2-9
In Spanish, "ch" and "ll" are treated as if they were individ-
ual unique single letters. For example, if a domain is defined
with the collating sequence SPANISH, then STARTING WITH "c"
will not retrieve the word "char" but will retrieve the word
"cat".
o LIKE predicate
For Version 3.1, the LIKE predicate does not recognize the
Spanish combinations of "ch" and "ll" each as one character.
o "ñ" is always treated as different from "n".
o "ç" is treated the same as "c".
o "ü" is treated the same as "u" for most languages; however, it
is treated the same as "y" (between "x" and "z") for Danish,
Norwegian, and Finnish.
o The German "ß" matches a double s, "ss".
o The multinational "÷" (oe ligature) matches "oe".
o The Norwegian "ä" matches "ae" (not to be confused with "æ",
the ae diphthong).
o "ß", "÷", "ä" match their respective doubles (such as "ss",
"oe", and "ae") and match "%%", but do not match "%".
o "*ss*" and "*s*" match and contain "ss" and "ß".
o "*oe*", "*e*", and "*o*" match and contain "oe" and "÷".
2-10 New and Changed Features
2.6 Concurrent Metadata Operations
New for Version 3.1 is the ability to perform some data definition
operations while other users are attached to the database. No
longer do all data definition operations require exclusive access
to databases.
The purpose of allowing concurrent metadata operations is to per-
mit data definitions in one portion of the database to be main-
tained while other definitions are simultaneously being accessed
by user applications. It does not permit changes to the same table
or other definitions that other users are accessing.
The reason for this restriction is that Rdb/VMS loads informa-
tion about a table into memory when a user accesses it and does
not renew or release the memory until the user issues a FINISH
statement. Thus, concurrent metadata operations will work only
on tables that other users have not accessed or that other users
have accessed but for which they have released memory by issuing a
FINISH statement.
Note also that, during concurrent metadata operations, there is
no change to transaction-oriented locking semantics on system
relations.
Table 2-2 indicates the metadata changes during which other users
are now allowed to be attached to the database and those during
which they are not. In addition, the table shows what metadata
updates must execute in a read/write transaction.
New and Changed Features 2-11
Table 2-2: The Metadata Updates Where Concurrency Is or Is Not
Allowed and Whether the Metadata Update Operation Must
____________Execute_in_a_Read/Write_Transaction___________________
Con-
currency
Executes
Allowed
in a
Metadata or Read/Write
Update______Not[1,2]TransactComments______________________________
Collating Yes Yes You can alter a collating sequence in
sequence an ALTER DOMAIN statement. You cannot
CREATE delete a collating sequence if the
ALTER database or domain in the database
DROP uses that collating sequence.
Constraints Yes Yes You cannot delete a constraint when
there are active transactions that ac-
CREATE cess the the table or tables involved.
DROP
__________________________________________________________________
[1]Concurrency allowed means other users can attach to the
database while the metadata update occurs. Note that other re-
strictions may apply. See the comments for each metadata item
and refer to the VAX Rdb/VMS SQL Reference Manual and VAX Rdb/VMS
RDO and RMU Reference Manual for additional restrictions that may
apply.
[2]Concurrency not allowed means no users are allowed to be at-
tached to the database.
2-12 New and Changed Features
Table 2-2 (Cont.): The Metadata Updates Where Concurrency Is
or Is Not Allowed and Whether the Metadata
Update Operation Must Execute in a Read/Write
____________________Transaction___________________________________
Con-
currency
Executes
Allowed
in a
Metadata or Read/Write
Update______Not[1,2]TransactComments______________________________
Domains Yes Yes You cannot delete a domain that refers
CREATE to a column definition of a table.
ALTER You can alter a collating sequence in
DROP an ALTER DOMAIN statement. You cannot
delete a collating sequence if the
database or domain in the database
uses that collating sequence.
Indexes Yes Yes You cannot delete an index definition
CREATE when there are active transactions
ALTER that access the tables involved; you
DROP cannot delete an index if a stor-
age map specifies this index in a
PLACEMENT VIA INDEX clause.
__________________________________________________________________
[1]Concurrency allowed means other users can attach to the
database while the metadata update occurs. Note that other re-
strictions may apply. See the comments for each metadata item
and refer to the VAX Rdb/VMS SQL Reference Manual and VAX Rdb/VMS
RDO and RMU Reference Manual for additional restrictions that may
apply.
[2]Concurrency not allowed means no users are allowed to be at-
tached to the database.
New and Changed Features 2-13
Table 2-2 (Cont.): The Metadata Updates Where Concurrency Is
or Is Not Allowed and Whether the Metadata
Update Operation Must Execute in a Read/Write
____________________Transaction___________________________________
Con-
currency
Executes
Allowed
in a
Metadata or Read/Write
Update______Not[1,2]TransactComments______________________________
Protection Yes Yes Granting or revoking a privilege takes
effect after the user detaches and
GRANT attaches to the database again.
REVOKE
__________________________________________________________________
[1]Concurrency allowed means other users can attach to the
database while the metadata update occurs. Note that other re-
strictions may apply. See the comments for each metadata item
and refer to the VAX Rdb/VMS SQL Reference Manual and VAX Rdb/VMS
RDO and RMU Reference Manual for additional restrictions that may
apply.
[2]Concurrency not allowed means no users are allowed to be at-
tached to the database.
2-14 New and Changed Features
Table 2-2 (Cont.): The Metadata Updates Where Concurrency Is
or Is Not Allowed and Whether the Metadata
Update Operation Must Execute in a Read/Write
____________________Transaction___________________________________
Con-
currency
Executes
Allowed
in a
Metadata or Read/Write
Update______Not[1,2]TransactComments______________________________
Tables Yes Yes Using the SQL interface, Rdb/VMS au-
CREATE tomatically deletes any view, con-
ALTER straint, index, or trigger definition
DROP that refers to a table when it deletes
the table definition. Using the RDO
interface, you must first delete any
views, constraints, and triggers de-
fined for the table before you can
delete the table. Any index that is
not a placement index and based on a
column or columns of the table is au-
tomatically deleted when the table is
deleted. Placement indexes must have
the storage map modified specifying NO
PLACEMENT VIA INDEX before the table
can be deleted along with any indexes
defined for column or columns on that
table.
New and Changed Features 2-15
Table 2-2 (Cont.): The Metadata Updates Where Concurrency Is
or Is Not Allowed and Whether the Metadata
Update Operation Must Execute in a Read/Write
____________________Transaction___________________________________
Con-
currency
Executes
Allowed
in a
Metadata or Read/Write
Update______Not[1,2]TransactComments______________________________
__________________________________________________________________
[1]Concurrency allowed means other users can attach to the
database while the metadata update occurs. Note that other re-
strictions may apply. See the comments for each metadata item
and refer to the VAX Rdb/VMS SQL Reference Manual and VAX Rdb/VMS
RDO and RMU Reference Manual for additional restrictions that may
apply.
[2]Concurrency not allowed means no users are allowed to be at-
tached to the database.
2-16 New and Changed Features
Table 2-2 (Cont.): The Metadata Updates Where Concurrency Is
or Is Not Allowed and Whether the Metadata
Update Operation Must Execute in a Read/Write
____________________Transaction___________________________________
Con-
currency
Executes
Allowed
in a
Metadata or Read/Write
Update______Not[1,2]TransactComments______________________________
Triggers Yes Yes You cannot delete a trigger definition
CREATE when there are active transactions
DROP using the trigger or that reference
the tables involved.
Schema No No Note that if you or another user is
CREATE attached to the database, you will
ALTER get the following error message if
DROP you attempt to update this metadata,
-SYSTEM-W-ACCONFLICT, file access
conflict.
__________________________________________________________________
[1]Concurrency allowed means other users can attach to the
database while the metadata update occurs. Note that other re-
strictions may apply. See the comments for each metadata item
and refer to the VAX Rdb/VMS SQL Reference Manual and VAX Rdb/VMS
RDO and RMU Reference Manual for additional restrictions that may
apply.
[2]Concurrency not allowed means no users are allowed to be at-
tached to the database.
New and Changed Features 2-17
Table 2-2 (Cont.): The Metadata Updates Where Concurrency Is
or Is Not Allowed and Whether the Metadata
Update Operation Must Execute in a Read/Write
____________________Transaction___________________________________
Con-
currency
Executes
Allowed
in a
Metadata or Read/Write
Update______Not[1,2]TransactComments______________________________
Storage No No You cannot delete a storage area that
areas is referred to in a storage map. You
CREATE create, alter, or delete storage areas
ALTER from within a CREATE SCHEMA, ALTER
DROP SCHEMA, or DROP SCHEMA statement.
Note that if you or another user is
attached to the database, you will
get the following error message if
you attempt to update this metadata,
-SYSTEM-W-ACCONFLICT, file access
conflict.
__________________________________________________________________
[1]Concurrency allowed means other users can attach to the
database while the metadata update occurs. Note that other re-
strictions may apply. See the comments for each metadata item
and refer to the VAX Rdb/VMS SQL Reference Manual and VAX Rdb/VMS
RDO and RMU Reference Manual for additional restrictions that may
apply.
[2]Concurrency not allowed means no users are allowed to be at-
tached to the database.
2-18 New and Changed Features
Table 2-2 (Cont.): The Metadata Updates Where Concurrency Is
or Is Not Allowed and Whether the Metadata
Update Operation Must Execute in a Read/Write
____________________Transaction___________________________________
Con-
currency
Executes
Allowed
in a
Metadata or Read/Write
Update______Not[1,2]TransactComments______________________________
Storage Yes Yes You cannot delete a storage map that
maps refers to a storage area that contains
CREATE data.
ALTER
DROP
Views Yes Yes Deleting a view does not affect active
CREATE users until you commit your transac-
DROP tion, users detach from the database
and attach to the database again.
__________________________________________________________________
[1]Concurrency allowed means other users can attach to the
database while the metadata update occurs. Note that other re-
strictions may apply. See the comments for each metadata item
and refer to the VAX Rdb/VMS SQL Reference Manual and VAX Rdb/VMS
RDO and RMU Reference Manual for additional restrictions that may
apply.
[2]Concurrency not allowed means no users are allowed to be at-
tached to the database.
__________________________________________________________________
New and Changed Features 2-19
2.7 Descending Indexes
With Rdb/VMS V3.1, you can create descending indexes, that is,
indexes in which index keys are stored in descending (rather
than the default ascending) sequence. This capability can improve
the performance of queries that retrieve records with key values
sorted in descending order.
Assume, for example, that you wish to retrieve the last five
records from a relation, and you enter the following statement:
RDO> FOR FIRST 5 E IN EMPLOYEES SORTED BY DESCENDING E.EMPLOYEE_ID
cont> PRINT E.* END_FOR
If you wanted to list all of the EMPLOYEES rows in descending
EMPLOYEE_ID order, you enter the following statement:
SQL> SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE_ID DESC;
Under previous versions of Rdb/VMS, with an ascending index de-
fined on the EMPLOYEE_ID field (or the SQL equivalent), execution
of this query was slowed because Rdb/VMS needed to sort all the
index records in descending sequence before returning the informa-
tion. However, if you define a descending index on the EMPLOYEE_ID
field using Version 3.1, this query executes much faster because
the index records are already sorted in descending sequence, and
the Rdb/VMS query optimizer recognizes that the descending index
is the most efficient method for performing this operation.
Note that you can have both ascending and descending indexes on
the same field. The query optimizer will choose the appropriate
one for the specified query. However, this added benefit means
that performance on updates may degrade.
You can specify a descending index by using the DESC keyword with
the CREATE INDEX statement (SQL), or the DESCENDING keyword with
the DEFINE INDEX statement (RDO). See the VAX Rdb/VMS RDO and RMU
Reference Manual and the VAX Rdb/VMS SQL Reference Manual for more
information.
2-20 New and Changed Features
2.8 Autolocking Implicitly Reserves Tables
Effective with Rdb/VMS V3.1, autolocking is the default behavior
with the RESERVING clause of the SET TRANSACTION statement (SQL)
and the START_TRANSACTION statement (RDO). Autolocking causes
tables referred to by constraints and triggers, but not appearing
in the RESERVING clause, to be automatically locked when accessed
from a constraint or trigger.
With SQL, autolocking always occurs; there is no way to specify no
autolocking. With RDO, you can explicitly use the RESERVING . ..
WITH NOAUTOLOCKING clause to disable autolocking and maintain the
same behavior as in Rdb/VMS V3.0.
Note the following usage information about autolocking:
o If autolocking is in effect and any of the tables referenced in
a trigger or constraint definition also appear on the list of
explicitly reserved tables, the explicitly specified lock mode
must not conflict with the lock mode required by the constraint
or trigger that references the table.
o In a read-only transaction, any RESERVING clause can only
declare read locks, and autolocking has no effect.
o In a read/write transaction with autolocking, Rdb/VMS deter-
mines the lock specification for each table accessed by a con-
straint or trigger when the table is first accessed with a data
manipulation statement from a constraint or trigger.
New and Changed Features 2-21
2.9 SQL Support for VAX Data Distributor
Data Distributor Version 2.1 supports not only the Relational
Database Operator (RDO) utility interface but also the interac-
tive SQL utility of Rdb/VMS. The focus of the VAX Data Distributor
Handbook has been changed from the RDO interface to that of inter-
active SQL. The text and examples in the VAX Data Distributor
Handbook represent the new SQL focus. Chapter 8 of the hand-
book contains reference information about interactive SQL.
Chapter 9 of the handbook describes the syntax for RDO.
2.10 Disabling Data Dictionary Use During Database Creation
There are two supported ways to disable the data dictionary:
o Use the SQL CREATE SCHEMA or IMPORT statement.
SQL does not update the data dictionary unless you specify
the PATHNAME clause. This applies to both the CREATE SCHEMA
statement and the IMPORT SCHEMA statement. Digital recommends
the use of SQL to create and import databases (even if you have
used the RDO EXPORT statement). For example:
IMPORT SCHEMA FROM RALLY$COMMERCE.RBR
NO CDD LINKS
NO ACL
FILENAME RALLY$COMMERCE !** note no PATHNAME clause
DICTIONARY IS NOT REQUIRED
;
o Use the RDO DICTIONARY IS NOT USED clause.
To prevent any metadata being written to the data dictionary
when using RDO you will need to use the option DICTIONARY IS
NOT USED (which also implies the option IS NOT REQUIRED). This
option is supported by both the IMPORT and DEFINE DATABASE
statements.
2-22 New and Changed Features
In previous versions of Rdb/VMS, the RDO DEFINE DATABASE and
IMPORT statements always tried to create or update a CDD/Plus
CDD$DATABASE object. For example, to turn off access to the
dictionary during an import operation, enter the following:
IMPORT RALLY$COMMERCE.RBR INTO RALLY$COMMERCE
NOCDD_LINKS
NOACL
DICTIONARY IS NOT USED !**** new for V3.1
END IMPORT.
2.11 New Rdb/VMS Logical Names
2.11.1 RDMS$DEBUG_FLAGS_OUTPUT
There is a new Rdb/VMS logical name RDMS$DEBUG_FLAGS_OUTPUT. This
logical name allows you to name an output file in which to collect
the output from RDMS$DEBUG_FLAGS when you run your program. You
must have write access to the directory and the disk device must
exist for this logical name to be successful. This is useful when
the application is forms oriented, or runs in an ACMS server, for
example.
2.11.2 RDM$BIND_RUJ_EXTEND_BLKCNT
A new Rdb/VMS logical name, RDM$BIND_RUJ_EXTEND_BLKCNT, can be
used to pre-extend RUJ files for each process using a database.
For example, you can define the new block count to be a value
between 0 and 9999 blocks or accept the default of 100 blocks.
New and Changed Features 2-23
2.11.3 RDMS$DIAG_FLAGS
A new Rdb/VMS logical name, RDMS$DIAG_FLAGS, can be used to pro-
vide assistance in locating the erroneous queries.
$ DEFINE RDMS$DIAG_FLAGS S
When the RDMS$DIAG_FLAGS logical name is defined, the query
compiler will check for sort keys that are totally defined by
contexts external to the record selection expression (RSE) that
includes the sort clause. When such a case is encountered, the
query compiler will produce the following error diagnostic:
%RDB-E-INVALID_BLR, request BLR is incorrect at offset n
-RDMS-F-SORTKEYEXT, sort key is external to RSE context
| For more information, see Section 3.2.6.
|
| 2.11.4 RDMS$BIND_VM_SEGMENT
|
| A new logical name, RDMS$BIND_VM_SEGMENT,
| controls the handling of virtual
| memory segments by Rdb/VMS.
|
| DEFINE RDMS$BIND_VM_SEGMENT 1
|
| The definition of this logical name will cause Rdb/VMS to execute
| a different code path in its handling of virtual memory.
|
| For more information, see Section 4.1.22.
|
| 2.11.5 RDMS$BIND_VALIDATE_CHANGE_FIELD
|
| A new logical name, RDMS$BIND_VALIDATE_CHANGE_FIELD, causes the
| CHANGE FIELD statement always to validate the actual data records
| and convert them to the new metadata definition.
|
| DEFINE RDMS$BIND_VALIDATE_CHANGE_FIELD 1
|
| For more information, see Section 3.4.2.
2-24 New and Changed Features
2.12 SQL: New and Changed Statements
The following SQL statements and other language elements are new
for Rdb/VMS V3.1:
o CREATE TRIGGER, DROP TRIGGER, SHOW TRIGGER
See Section 2.3 for more information on triggers.
o SET/SHOW LANGUAGE
SET/SHOW RADIX POINT
SET/SHOW DIGIT SEPARATOR
SET/SHOW CURRENCY INDICATOR
SET/SHOW DATE FORMAT
CREATE/SHOW COLLATING SEQUENCE
See Section 2.5 and Appendix B for more information on features
designed to enhance the international use of Rdb/VMS.
o DROP CONSTRAINT
Use the DROP CONSTRAINT statement to delete constraints that
were defined with previous versions of VAX SQL or with RDO
(V3.1 or earlier). There is no CREATE CONSTRAINT statement in
SQL for Rdb/VMS V3.1; in SQL, constraints must be included in
the table definition (CREATE TABLE or ALTER TABLE)-for example,
using the PRIMARY KEY keyword or CHECK clause with a column
definition.
o PRINT
Use the PRINT statement to display a message to the user during
execution of an SQL command procedure. For example:
! Trigger definition statements are next.
PRINT "Creating trigger definitions for the database";
CREATE TRIGGER EMPLOYEE_ID_CASCADE_DELETE
.
.
.
New and Changed Features 2-25
o SET ALL CONSTRAINTS {ON|OFF}
The ANSI/ISO SQL standard specifies that constraints be eval-
uated after each SQL statement as well as at commit time. The
default in the SQL interface to Rdb/VMS is at COMMIT time only.
The SET ALL CONSTRAINTS ON statement causes all commit-time
constraints to be evaluated when the SET ALL CONSTRAINTS ON
statement executes, as well as at the end of each statement and
at commit-time, until the SET ALL CONSTRAINTS OFF statement is
issued or until the transaction completes.
If you use the precompiler or module processor command line
qualifiers to specify that the commit-time constraints are
evaluated at the end of each statement, you may occasionally
want to defer constraint checking so that you can insert some
data that may violate a constraint. In this situation, you can
use the SET ALL CONSTRAINTS OFF statement.
The following SQL statements and other language elements have the
following additions and modifications for Rdb/VMS V3.1:
o CREATE DOMAIN and ALTER DOMAIN:
- Accept the DEFAULT VALUE clause.
- Accept the CHECK and CONSTRAINT clauses.
- Accept the COLLATING SEQUENCE clause.
- Accept the EDIT STRING clause.
o ALL and ANY Booleans
The behavior of the ALL and ANY predicates has changed in
Rdb/VMS V3.1. See Section 2.20.
o ALTER SCHEMA SNAPSHOT ALLOCATION IS 0
2-26 New and Changed Features
You can set the snapshot allocation to 0 pages. Note that
at least one page must be allocated to the snapshot file to
store the header information. You may want to set the snapshot
allocation to 0 pages if you have changed a read/write storage
area to read-only or disabled snapshots for some other reason.
By setting the snapshot allocation to 0 in such a case, the
snapshot file is not used and you can save space.
o ALTER STORAGE MAP
Accepts the REORGANIZE clause.
o ALTER STORAGE MAP REORGANIZE
Using the ALTER STORAGE MAP statement and not specifying the
STORE clause but specifying REORGANIZE will now use the ex-
isting storage map definition. So the exact same partition and
PLACEMENT VIA INDEX clause as written in the CREATE STORAGE MAP
statement will now be used.
o ALTER STORAGE AREA, ALTER SCHEMA (ALTER STORAGE AREA statement)
Accept the READ ONLY and READ WRITE clauses.
See Section 2.4 for more information on read-only storage
areas.
o ALTER STORAGE MAP NO PLACEMENT VIA INDEX
The NO PLACEMENT VIA INDEX clause negates the PLACEMENT VIA
INDEX clause so that subsequent records that are stored are not
stored using the index named in the PLACEMENT VIA INDEX clause.
o CREATE INDEX
Accepts the DESC keyword. For more information on descending
indexes see the VAX Rdb/VMS SQL Reference Manual
o CREATE TABLE and ALTER TABLE:
- Accept the PRIMARY KEY keyword for a column or group of
columns.
New and Changed Features 2-27
- Accept the REFERENCES clause for a column.
- Accept the DEFAULT VALUE clause.
- Accept the CHECK and CONSTRAINT clauses for a column.
- Accept the COLLATING SEQUENCE clause (see Section 2.5).
- Accept the CONSTRAINT keyword.
The DIAGNOSTIC keyword that was used in ALTER TABLE and
CREATE TABLE statements in the previous version of SQL
to define constraints has been replaced by the CONSTRAINT
keyword.
For example, in the previous version of SQL, you could
define the following table using the DIAGNOSTIC keyword
to define constraints:
SQL> CREATE TABLE TEMP1
cont> (COL1 REAL NOT NULL UNIQUE DIAGNOSTIC C1,
cont> COL2 REAL NOT NULL UNIQUE DIAGNOSTIC C2,
cont> COL3 REAL NOT NULL UNIQUE DIAGNOSTIC C3 );
With the Rdb/VMS V3.1 SQL interface, you define the same
table using the CONSTRAINT keyword to define constraints:
SQL> CREATE TABLE TEMP1
cont> (COL1 REAL NOT NULL UNIQUE CONSTRAINT C1,
cont> COL2 REAL NOT NULL UNIQUE CONSTRAINT C2,
cont> COL3 REAL NOT NULL UNIQUE CONSTRAINT C3 );
Note that for Rdb/VMS V3.1, you can use either the
CONSTRAINT or DIAGNOSTIC keyword in the ALTER TABLE and
CREATE TABLE statements to define constraints. However, you
are encouraged to use the CONSTRAINT keyword, and to re-
place the DIAGNOSTIC keyword with CONSTRAINT in SQL command
procedures that you expect to use with future versions of
Rdb/VMS.
2-28 New and Changed Features
o CREATE SCHEMA
- Permits 2032 users as the maximum value in the NUMBER OF
USERS IS clause. (The maximum permitted for Version 3.0 was
508.)
- Accepts the COLLATING SEQUENCE clause (see Section 2.5).
- Accepts the READ ONLY and READ WRITE keywords in the CREATE
STORAGE_AREA clause.
See Section 2.4 for more information on read-only storage
areas.
o CREATE VIEW
Accepts the WITH CHECK OPTION clause.
Specifying WITH CHECK OPTION causes any data insertion or
modification operation using a view to be validated against
the view definition.
o IMPORT
- Accepts the COLLATING_SEQUENCE clause (see Section 2.5).
- Accepts the DICTIONARY IS [NOT] USED clause.
- Supports ANSI-style privileges.
o LIKE operator
Is now case sensitive by default; you can override by specify-
ing the IGNORE CASE clause.
The LIKE operator also now includes support for the asterisk
(*) character and the escape key.
o GRANT statement (ANSI-style)
Is supported for Version 3.1 for databases created using CREATE
SCHEMA . .. PROTECTION IS ANSI. See Section 2.19.
New and Changed Features 2-29
The WITH GRANT OPTION clause allows the user who has been
granted a privilege the option of granting that privilege to
other users.
o OPEN Cursor Statement
Has changed behavior.
Previously, if you opened a cursor, fetched a row, then opened
the same cursor (that is, using the same name) and fetched
a row, retrieval would start again at the first row, because
the OPEN statement implicitly closed the cursor and opened it
again.
Now, if you do the same thing, retrieval after the second OPEN
statement starts after the last row that was retrieved (for
example, at row 2 if you only fetched one row before). This
approach is ANSI compliant.
o REVOKE statement (ANSI-style)
Is supported for Version 3.1 for databases created using CREATE
SCHEMA . .. PROTECTION IS ANSI. See Section 2.19.
o SELECT
- Accepts the UNION operator (see Section 2.13).
- Accepts the USER literal with views.
o SHOW PRIVILEGES statement
- Accepts the COLUMN ON column-name clause.
- Supports ANSI-style protection. It shows which privileges
are granted WITH GRANT OPTION and which privileges are not.
2-30 New and Changed Features
2.13 SQL: UNION Operator Now Supported
The UNION operator is supported in SQL for Rdb/VMS V3.1. When you
want to combine the results of more than one query into one record
stream, you can use the UNION operator. With this operator, you
can combine the values of columns in one table with the values of
columns in other tables.
For example, you can select all employees who are managers and all
employees who have a PhD and combine the results into one table:
SELECT EMPLOYEE_ID FROM CURRENT_JOB WHERE JOB_CODE = "DMGR"
UNION
SELECT EMPLOYEE_ID FROM DEGREES WHERE DEGREE="PhD";
In the preceding example, if the CURRENT_JOB and the DEGREES ta-
bles list the same employee with the same employee ID, the result
table would display that employee's ID only once. Rdb/VMS selects
rows from one query and then from the other query; however, you
cannot make any assumptions as to which query Rdb/VMS performs
first. To specify the sort order, use the ORDER BY clause.
The UNION operator is documented in the section on the SELECT
statement in the VAX Rdb/VMS SQL Reference Manual.
2.14 SQL: Default Values
With Rdb/VMS V3.1, SQL allows you to specify a default value for
a domain or for a column in a table, or both. If you specify a
default value for a column in a table, that value overrides any
default value specified for the domain on which the column is
based.
The default value of a column is the value that is stored in the
database if an insert operation on a row specifies no value for
that column. You might have any of several possible reasons for
specifying a default value for a column. For instance, you may
want to store the most commonly used value, or you may want to
store a value that highlights (in visual displays) the fact that
New and Changed Features 2-31
no value was stored. Consider the following examples, given the
fact that in the sample personnel database most employees live
in New Hampshire (state code NH). If you want to store NH in the
STATE column of an EMPLOYEES row whenever a data entry clerk does
not enter a value, specify a default value of NH. On the other
hand, if you want to treat the absence of a STATE entry as an
unusual condition (for example, if the data entry clerk does not
know the new employee's state of residence when initially entering
the information, but should find it out later), you could specify
a default value of "??".
The default value for a column "Rdb default" using SQL is not
the same as the "missing value" using RDO. If you do not specify
a value for a column with a default value, the default value is
actually stored in the database. If you do not specify a value for
a column that has no default value defined, Rdb/VMS stores nothing
in that column and sets an internal null flag. If you use RDO to
specify a missing value for a field (column), then that is the
value displayed by RDO when the field has no value stored and the
internal null flag is set. SQL, however, does not recognize any
missing value specified by RDO; if the column has no value stored
and the null flag is set, then SQL displays NULL for the column,
regardless of whether or not you specified any missing value with
RDO.
In summary, if you used SQL to specify a default value for a
column, that value is always stored if you do not specify a value
for the column. If you do not use SQL to specify a default value
and you do use RDO to specify a missing value, then nothing is
stored if you insert without specifying a value; rather, the null
flag is set for that instance of the column. In this case, SQL
queries selecting that instance of the column will display NULL,
and RDO queries will display whatever missing value you specified.
One implication of the way in which Rdb/VMS handles default values
is that if you change the default value for a column, it has no
effect on any existing data in the database; that is, rows stored
with columns containing the old default value are not changed. By
contrast, changing the missing value does change what is displayed
2-32 New and Changed Features
by based on RDO applications for fields that have no value stored
and that have the null flag set.
For information on specifying a default value, see the sections
on the CREATE DOMAIN, ALTER DOMAIN, CREATE TABLE, and ALTER TABLE
statements in the VAX Rdb/VMS SQL Reference Manual.
2.15 SQL: Precompiler
2.15.1 SQL: Precompiler Support for Pascal
For Rdb/VMS V3.1, Pascal is added to the languages supported with
the SQL$PRE precompiler.
To precompile a Pascal program, use the following command:
$ SQLPRE :== $SQL$PRE
$ SQLPRE/PASCAL PASCAL_PROG
The file type for the Pascal input file is SPA. When you precom-
pile a Pascal program, the precompiler generates an output file
with a file type PAS. You link and run Pascal host language pro-
grams in the same way as you link and run other host language
programs.
If you follow the general rules outlined in the VAX Rdb/VMS Guide
to Using SQL, you can embed almost any SQL statement in a Pascal
source file.
You should be aware of the following:
o The precompiler does not support the use of the INCLUDE FROM
DICTIONARY statement.
o End all SQL statements in Pascal programs with a semicolon
(;). This includes SQL statements within a Pascal IF-THEN-ELSE
statement. For example, if you embed an SQL statement before
the ELSE clause, you must surround the SQL statement with a
BEGIN-END block.
New and Changed Features 2-33
o The SQL$PRE precompiler supports block structure in Pascal
programs. This means you can declare parameters to which SQL
statements refer, such as SQLCODE, in multiple procedures in
the same Pascal source file, and the precompiler will recognize
them as independent parameters.
The SQL$PRE precompiler recognizes most Pascal rules and state-
ments. However, you should be aware of the following restrictions:
o The precompiler only recognizes the HIDDEN attribute for Pascal
host language variables.
o The precompiler supports only one level of pointers.
2.15.2 New Command Line Qualifiers
The following new command line qualifiers were implemented for
Rdb/VMS V3.1:
o /SQLOPTIONS = (CONSTRAINTS=ON)
/SQLOPTIONS = (CONSTRAINTS=OFF)
These qualifiers set the default constraint evaluation mode for
commit-time constraints. (This qualifier does not affect the
evaluation of verb-time constraints.) The default is OFF.
This qualifier can be overridden by the SET ALL CONSTRAINTS
statement (described earlier in Section 2.12).
It is expected that most Rdb/VMS users will accept the default
that constraints are set OFF. However, users who require ANSI-
standard SQL compatibility should set constraints ON.
o /SQLOPTIONS=FLAG_NONSTANDARD
/SQLOPTIONS=NOFLAG_NONSTANDARD
These qualifiers determine whether or not the SQL pre-
compiler produces informational messages that indicate
Digital extensions to the ANSI/ISO standard syntax. The
/SQLOPTIONS=NOFLAG_NONSTANDARD qualifier is the default. If
2-34 New and Changed Features
you specify /SQLOPTIONS=FLAG_NONSTANDARD, the SQL precompiler
writes the messages to your terminal and to the language file.
o /SQLOPTIONS=VIDA
/SQLOPTIONS=NOVIDA
These qualifiers determine whether or not the SQL precom-
piler recognizes and correctly processes a program that
will be used to access a VIDA database. You must specify the
/SQLOPTIONS=VIDA qualifier if your program will only be used
against a VIDA database. If your program will sometimes be
run against a VIDA database, but will also be run against an
Rdb/VMS database, then you must specify the /VIDA qualifier
in the DECLARE SCHEMA statement rather than on the precom-
piler command line. If you do not specify the VIDA qualifier
in at least one of these places, when your program tries to
access a VIDA database, you will get an error message. The
/SQLOPTIONS=NOVIDA qualifier is the default.
The SQL precompiler lets you specify whether COBOL source files
are in terminal format or ANSI/ISO format on the command line.
The default is terminal format. If your source file is in ANSI/ISO
format, specify /ANSI_FORMAT on the command line.
2.16 SQL: Module Processor
2.16.1 New Command Line Qualifiers
The following new command line qualifiers were implemented for
Rdb/VMS V3.1:
o /CONSTRAINTS=ON
/CONSTRAINTS=OFF
These qualifiers set the default constraint evaluation mode for
commit-time constraints. (This qualifier does not affect the
evaluation of verb-time constraints.) The default is OFF.
This qualifier can in turn be overridden by the SET ALL
CONSTRAINTS statement (described earlier in Section 2.12).
New and Changed Features 2-35
It is expected that most Rdb/VMS users will accept the default
that constraints are set OFF. However, users who require ANSI-
standard SQL compatibility should set constraints ON.
o /FLAG_NONSTANDARD
/NOFLAG_NONSTANDARD
These qualifiers determine if the SQL module processor gives
informational messages that indicate Digital extensions to the
ANSI/ISO standard syntax. The /NOFLAG_NONSTANDARD qualifier
is the default. If you specify /FLAG_NONSTANDARD, the SQL
module processor writes the messages to your terminal and to
the listing file.
2-36 New and Changed Features
o /VIDA
/NOVIDA
These qualifiers determine if the SQL module processor recog-
nizes and correctly processes a program that will be used to
access a VIDA database. You must specify the /VIDA qualifier
if your program will be used only against a VIDA database. If
your program will sometimes be run against a VIDA database, and
sometimes against an Rdb/VMS database, then you must specify
/VIDA qualifier in the DECLARE SCHEMA statement rather than on
the module processor command line. If you do not specify VIDA
in at least one of these places, when your program tries to
access a VIDA database you will get an error message. If you do
not specify VIDA anywhere, then the default is taken from the
command line for the module processor. The /NOVIDA qualifier is
the default.
When you specify the /VIDA qualifier, certain features of
Rdb/VMS will function differently. These features are:
- Case sensitivity of the LIKE operator.
- USER literal cannot be used in data definition statements.
- ANY and ALL keywords will return FALSE instead of null
values when there are null values in the result table.
- SUM function will return FALSE instead of null values when
there are no records in the stream.
- Truncation will not always be indicated in the indicator
variables.
- No error will be returned if a subquery used as a value
expression returns more than one value.
- Combining tables using UNION.
- Statement level evaluation of constraints.
New and Changed Features 2-37
- The FINISH statement will not clear the request handles in
other modules that are using the database.
- VIDA will not know whether or not the data dictionary is
being maintained.
2.17 SQL: VAX Language-Sensitive Editor (LSE) Support
SQL now supports VAX Language-Sensitive Editor (LSE) templates
that guide you in entering correct SQL commands and statements.
Rdb/VMS V3.1 includes support for two sets of LSE templates,
one for interactive SQL and one for the SQL module language. For
further information see the VAX Rdb/VMS SQL Reference Manual.
2.18 SQL: Changes for ANSI/ISO Compatibility
Several changes have been made to SQL to conform with the require-
ments of the ANSI/ISO standard.
o For Rdb/VMS V3.0, VAX SQL V2.0 interpreted the value specified
with the FLOAT data type as the number of decimal digits to be
represented. For Rdb/VMS V3.1, the value specified is inter-
preted by SQL as the number of binary digits to be represented.
Thus, the maximum precision is now 24 for REAL data and 53 for
DOUBLE PRECISION data. (For V3.0, the maximum precisions were 7
and 15, respectively.)
o For Rdb/VMS V3.0, an attempt to open a cursor that was already
opened generates a warning and an SQLCODE value of 1001. For
Rdb/VMS V3.1, this condition generates an error with an SQLCODE
value of -502.
o For Rdb/VMS V3.1, the requirement that all SQL statements
start with EXEC SQL is strictly enforced. Thus, the terms EXEC
and SQL must be on the same line, and there must not be any
comments between EXEC and SQL. (The EXEC SQL flag, however,
2-38 New and Changed Features
is not required to be on the same line as the SQL statement it
precedes.)
o For Rdb/VMS V3.1, the SQL$PRE precompiler supports ANSI COBOL
format in addition to the previously supported terminal COBOL
format.
o For Rdb/VMS V3.1, the SQL$PRE precompiler supports sign-
leading, separate-indicator variables in COBOL and longword-
indicator variables in Pascal. (However, in dynamic SQL modules
in Pascal, indicator variables must still be word length.)
These enhancements are made to permit program conformance with
ANSI requirements.
o With Rdb/VMS V3.0, VAX SQL V2.0 did not raise an error if a
subquery in a value expression returned more than one value;
rather, SQL simply returned the first value. With Rdb/VMS V3.1,
SQL returns an error in such cases, as required by the current
ANSI standard.
An example of a query whose behavior might change from Version
3.0 to Version 3.1 is:
SQL> SELECT * FROM SPJ WHERE PNUM = (SELECT PNUM FROM P);
Under Version 3.0, if (SELECT PNUM FROM P) returned more than
1 row, the value of PNUM in the first row was returned. Under
Version 3.1, an error is returned.
o ANSI standard requires a colon (:) before the label name in
the GOTO clause of a WHENEVER statement. In Rdb/VMS V3.0, VAX
SQL 2.0 did not allow the colon in this context. In Rdb/VMS
V3.1, SQL allows an optional colon in this context. (Note that
the colon is permitted only if the label is a name, not if it
is a number.)
New and Changed Features 2-39
2.19 SQL: ANSI-Style Privileges
All Rdb/VMS databases through Version 3.0 have used ACL-style
privileges as a database security mechanism. In Version 3.1, the
user can specify at database creation time whether ANSI-style or
ACL-style privileges will be used.
ANSI-style databases:
o Grant access to the creator when an object is created. As a
result of the fact that only the creator is granted access to
the newly created object, additional access must be granted
explicitly.
o Support only the PUBLIC identifier as a wildcard.
o Support only rights identifiers that translate to a VMS User
Identification Code (UIC).
ANSI-style privileges are implemented through the PROTECTION IS
ANSI clause of the CREATE SCHEMA statement and through the ANSI-
style versions of the GRANT and REVOKE statements. The ANSI-style
GRANT and REVOKE statements are described in the VAX Rdb/VMS SQL
Reference Manual.
For ANSI-style databases, the access privilege set is not order-
dependent. The user matches the entry in the set of privileges and
gets whatever privileges have been granted on the schema, table,
or column, and also gets the privileges defined for PUBLIC. A user
without an entry in the access privilege set will get only the
privileges defined for PUBLIC. There is always an entry for PUBLIC
in the access privilege set, even if PUBLIC has no access to the
schema, table, or column.
The following displays illustrate the contrast between the protec-
tion established on the EMPLOYEES table if the PERSONNEL database
were to be created with ANSI-style protection and with ACL-style
protection. (In both cases the database creator/owner has the UIC
of [RDB,TOLIVER].) First, the display with ANSI-style protection:
2-40 New and Changed Features
SQL> show protection on employees;
Protection on Table EMPLOYEES
[RDB,TOLIVER]:
With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATETAB,ALTER,
DROP,DBCTRL,OPERATOR,DBADM,REFERENCES
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
Next, the display with ACL-style protection. Note the many types
of access given to the public ([*,*]), in contrast to NONE with
ANSI-style protection:
SQL> show protection on employees;
Protection on Table EMPLOYEES
(IDENTIFIER=[RDB,TOLIVER],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATETAB+
ALTER+DROP+DBCTRL+OPERATOR+DBADM+REFERENCES)
(IDENTIFIER=[*,*],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATETAB+ALTER+
DROP+OPERATOR+DBADM+REFERENCES)
To specify ANSI-style privileges, specify CREATE SCHEMA . ..
PROTECTION IS ANSI at database creation time. If no protection
clause is specified, the default is ACL style.
This feature is only implemented in SQL; you cannot specify ANSI-
style privileges if you create the database using RDO.
2.20 SQL: ALL and ANY Booleans
Effective with Rdb/VMS V3.1, the behavior of the ALL and ANY
Boolean expressions has been changed to conform with the re-
quirements of the ANSI/ISO standard for SQL. Note that to obtain
the new results from ALL or ANY queries in a precompiled program
requires recompilation.
New and Changed Features 2-41
Under Rdb/VMS V3.0 some ALL or ANY Boolean expressions return a
truth value of FALSE in cases where the current ANSI SQL standard
states that a truth value of UNKNOWN should be returned. Effective
with Rdb/VMS V3.1, these expressions returned a truth value of
UNKNOWN.
For an example of a Boolean expression that returns a truth value
of FALSE in V3.0 but UNKNOWN in V3.1, consider the following:
P.PNUM = ANY (SELECT SPJ.PNUM FROM SPJ);
If there are no rows of SPJ in which the value of SPJ.PNUM is
equal to P.PNUM, and if there are some rows of SPJ in which
SPJ.PNUM is NULL, then the truth value of this expression should
be UNKNOWN. In these circumstances in Version 3.0, the expres-
sion has a truth value of FALSE; however, in Version 3.1, it has a
truth value of UNKNOWN.
For another example, consider the following expression:
P.PNUM = ALL (SELECT SPJ.PNUM FROM SPJ);
If there is no row of SPJ in which SPJ.PNUM is not equal to
P.PNUM, but there are rows of SPJ in which SPJ.PNUM is NULL, the
truth value of this expression should be UNKNOWN. In these circum-
stances in Version 3.0, the expression has a truth value of FALSE;
however, in Version 3.1, it has a truth value of UNKNOWN.
2.21 SQL: New Packages Supported for the Ada Precompiler
2.21.1 New Package for Ada Host Language Variables
The Ada precompiler now allows users to define host language
variables used in SQL statements in terms of the definitions in
another package. This package is defined in the new ANSI standard
for embedded SQL. The name of the package is SQL_STANDARD, and
its definition is in the file SYS$COMMON:[SYSLIB]SQL$STANDARD.ADA.
Users must compile this package and add it to their library if
they want to use it.
2-42 New and Changed Features
This package should be used by those concerned with standards
conformance. Only the use of this package is supported by the
standard.
The definition of the package is:
PACKAGE SQL_STANDARD is
package CHARACTER_SET renames STANDARD;
subtype CHARACTER_TYPE is CHARACTER_SET.CHARACTER;
type CHAR is array (POSITIVE range <>) of CHARACTER_TYPE;
type SMALLINT is range -32_768 .. 32_767;
type INT is range -2_147_483_648 .. 2_147_483_647;
type REAL is digits 6;
type DOUBLE_PRECISION is digits 15;
type SQLCODE_TYPE is range -2_147_483_648 .. 2_147_483_647;
subtype SQL_ERROR is SQLCODE_TYPE range SQLCODE_TYPE'FIRST..-1;
subtype NOT_FOUND is SQLCODE_TYPE range 100..100;
subtype INDICATOR_TYPE is SMALLINT;
END SQL_STANDARD;
An example of a program that uses this package is:
--
-- This program tests using the SQL_STANDARD package.
--
-- It uses dtptests.rdb
--
exec sql declare schema for filename 'dtptests';
with sql_standard; use sql_standard;
with sql_user; use sql_user;
with text_io; use text_io;
with sql_int_io; use sql_int_io;
with smallint_io; use smallint_io;
with real_io; use real_io;
with double_precision_io; use double_precision_io;
with sqlcode_io; use sqlcode_io;
procedure ada_dml_hv4 is
SQLCODE : SQL_STANDARD.SQLCODE_TYPE;
New and Changed Features 2-43
FFLOAT : REAL;
GFLOAT : DOUBLE_PRECISION;
LWORD : INT;
TEXT : CHAR(1..31);
WORD : SMALLINT;
IND1, IND2, IND3, IND4, IND5 : INDICATOR_TYPE;
BEGIN
exec sql whenever sqlerror goto err;
put(item=>"do inserts:");
new_line;
ind1 := 0;
lword := -1;
ind2 := -1;
word := -2;
ind3 := 0;
ffloat := -3.0;
ind4 := -1;
gfloat := -4.0;
ind5 := 0;
text := "insert using char(31) ";
exec sql insert into all_dtps
(lword, word, ffloat, gfloat, text)
values (:lword:ind1, :word:ind2, :ffloat:ind3, :gfloat:ind4, :text:ind5);
ind1 := -1;
ind2 := 0;
ind3 := -1;
ind4 := 0;
ind5 := -1;
exec sql insert into all_dtps
(lword, word, ffloat, gfloat, text)
values (:lword:ind1, :word:ind2, :ffloat:ind3, :gfloat:ind4, :text:ind5);
put(item=>"dump table:");
new_line;
exec sql declare c cursor for
select lword, word, ffloat, gfloat, text from all_dtps order by 1, 2, 3, 4;
2-44 New and Changed Features
exec sql open c;
exec sql fetch c
into :lword indicator :ind1,
:word indicator :ind2,
:ffloat indicator :ind3,
:gfloat indicator :ind4,
:text indicator :ind5;
while sqlcode /= 100 loop
new_line;
put(item=>"lword: ");
if (ind1 < 0) then put(item=>"null"); else put(item=>LWORD); end if;
new_line;
put(item=>"word: ");
if (ind2 < 0) then put(item=>"null"); else put(item=>WORD); end if;
new_line;
put(item=>"ffloat: ");
if (ind3 < 0) then put(item=>"null"); else put(item=>FFLOAT); end if;
new_line;
put(item=>"gfloat: ");
if (ind4 < 0) then put(item=>"null"); else put(item=>GFLOAT); end if;
new_line;
put(item=>"text: ");
if (ind5 < 0) then put(item=>"null"); else put(item=>string(TEXT)); end if;
new_line;
exec sql fetch c
into :lword indicator :ind1,
:word indicator :ind2,
:ffloat indicator :ind3,
:gfloat indicator :ind4,
:text indicator :ind5;
end loop;
EXEC SQL CLOSE C;
exec sql rollback work;
return;
New and Changed Features 2-45
<<err>>
put(item=>"unexpected error. SQLCODE: ");
put(item=>SQLCODE);
sql_user.sql_signal;
exec sql whenever sqlerror continue;
exec sql rollback work;
return;
end ada_dml_hv4;
2.21.2 New Calendar Package for the Ada Precompiler
The Ada precompiler now supports the Ada standard Calendar
package. This new feature now allows Ada users access to the
Calendar.Time data type. This allows the use of the Calendar.Split
routine, as well as compatibility with other Ada procedures which
declare their arguments as Calendar.Time.
Note that Calendar.Time may be used as the data type for variables
which are to receive data from SQL DATE columns. The internal
representation is the VMS-standard ADT format, and the precompiler
recognizes the data type as consistent with the SQL DATE data
type.
2.22 RDO: New and Changed Statements
The following RDO statements are new for Rdb/VMS V3.1:
o DEFINE TRIGGER
o DELETE TRIGGER
o SHOW TRIGGER
See Section 2.3 for more information on triggers.
2-46 New and Changed Features
The following RDO statements and other language elements have the
following additions and modifications effective with Rdb/VMS V3.1:
o CHANGE DATABASE (CHANGE STORAGE AREA clause)
Accepts the READ_ONLY and READ_WRITE keywords.
See Section 2.4 for more information on read-only storage
areas.
o CHANGE DATABASE SNAPSHOT ALLOCATION IS 0
Sets the snapshot allocation to 0 pages. Note that at least one
page must be allocated to the snapshot file to store the header
information. Two cases where you may want to set the snapshot
allocation to 0 pages are:
- If you have disabled snapshots. By setting the snapshot
allocation to 0, you may save space.
- If you have changed a read/write storage area to read-only.
The snapshot file is not used and you can save space.
o CHANGE FIELD VALID IF
Adds or changes a VALID IF clause with the CHANGE FIELD state-
ment. If the database already contains data that violates the
VALID IF clause, the VALID IF clause is rejected. Depending on
the type of change you make, you may have to reprocess and re-
link application programs that refer to changed fields. See the
VAX Rdb/VMS RDO and RMU Reference Manual for more information.
o CHANGE STORAGE MAP
Accepts the REORGANIZE clause.
o CHANGE STORAGE MAP NO PLACEMENT VIA INDEX
Negates the PLACEMENT VIA INDEX clause so that subsequent rows
that are stored are not stored using the index named in the
PLACEMENT VIA INDEX clause.
New and Changed Features 2-47
o CHANGE STORAGE MAP REORGANIZE
Uses the exact same partition and PLACEMENT VIA INDEX clause as
written in the define storage map statement. Using the CHANGE
STORAGE MAP statement and not specifying the STORE clause but
specifying REORGANIZE will now use the existing storage map
definition.
o DEFINE DATABASE:
- Accepts the COLLATING_SEQUENCE clause (see Section 2.5).
- Accepts the DICTIONARY IS [NOT] USED clause.
- Accepts the READ_ONLY and READ_WRITE keywords in the CREATE
STORAGE AREA clause.
- permits a maximum value of 2032 in the NUMBER OF USERS IS
clause. (The maximum permitted for Version 3.0 was 508.)
o DEFINE INDEX
Accepts the DESCENDING and ASCENDING keywords.
o DEFINE RELATION and CHANGE RELATION:
- Accept the PRIMARY KEY keyword for a field or group of
fields.
- Accept the FOREIGN KEY keyword for a field or group of
fields.
- Accept the REFERENCES clause for a field.
- Accept the UNIQUE, USING, and CONSTRAINT clauses for a
field.
- Accept the COLLATING_SEQUENCE clause (see Section 2.5).
2-48 New and Changed Features
o INVOKE DATABASE
Accepts the FINISH or DEFAULT keywords on the REQUEST_HANDLE
SCOPE clause.
o IMPORT:
- Accepts the COLLATING_SEQUENCE clause (see Section 2.5).
- Accepts the DICTIONARY IS [NOT] USED clause.
o SET DATE and TIME format
See Section 2.5 and Appendix B for more information.
o SET/SHOW LANGUAGE
SET/SHOW RADIX_POINT
SET/SHOW DATE_FORMAT
SET/SHOW COLLATING_SEQUENCE
See Section 2.5 and Appendix B for more information on features
designed to enhance the international usefulness of Rdb/VMS.
o SHOW
Changes have been made to the way information is displayed
resulting from an RDO SHOW statement.
o SHOW DATABASE db-handle
Displays more information. The following example shows the
output:
RDO> INVOKE DATABASE FILENAME 'DB$:PERSONNEL31'
RDO> SHOW DATABASE
Database with filename DB$:PERSONNEL31
New and Changed Features 2-49
RDO> SHOW DATABASE RDB$DBHANDLE
Database with db_handle RDB$DBHANDLE (default handle)
File: DISK1:[SMITH.DATABASES]PERSONNEL31.RDB;1
Segmented String Storage area: RDB$SYSTEM
Number of users: 50
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>
RDO> INVOKE DATABASE TEST = PATHNAME '[SMITH.DICTIONARY]PERSONNEL'
RDO> SHOW DATABASE
Database with db_handle TEST in path DISK1:[SMITH.DICTIONARY]PERSONNEL;1
The CDD is being maintained
RDO> SHOW DATABASE TEST
Database with db_handle TEST
Path: DISK1:[SMITH.DICTIONARY]PERSONNEL;1
The CDD is being maintained
File: DISK1:[SMITH.DATABASES]PERSONNEL31.RDB;1
Segmented String Storage area: RDB$SYSTEM
Number of users: 50
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> FINISH
o SHOW STORAGE_AREA
2-50 New and Changed Features
Indicates which storage area was named in the SEGMENTED STRING
STORAGE AREA clause of the DEFINE DATABASE or IMPORT statement.
Previously, this information was only available through the
RMU/DUMP/LAREA command.
2.23 RDBPRE: New and Changed Statements
2.23.1 RDBPRE Module Is Now Allowed to Be Compiled with the
/NOINITIALIZE_HANDLE Option
In Version 3.1, a new feature has been added to RDBPRE that now
allows you to access a database from both a shareable image and
the application program.
RDBPRE supplies two qualifiers: /INITIALIZE_HANDLES, the default,
and /NOINITIALIZE_HANDLES. These qualifiers let you determine
whether or not the preprocessor will automatically initialize
database, transaction, and request handles. These qualifiers have
no effect on if or when handles are initialized in the generated
code; they only control initialization of handles in declaration
statements. Furthermore, they only affect database, transaction,
and request handles that the preprocessor declares; user-specified
transaction and request handles will not be initialized when you
use the /INITIALIZE_HANDLES qualifier.
When compiling your programs, you can now use the /NOINITIALIZE_
HANDLE option on the command line. This option now allows you to
link the shareable image and your application program and have
them share database handles.
NOTE
In RDBPRE, when you use the /NOINITIALIZE_HANDLES quali-
fier, any handle you specify in your application program
must also be specified in the shareable image.
New and Changed Features 2-51
In Rdb/VMS V3.1, RDBPRE initializes database handles supplied
by the user and by RDBPRE when their scope is GLOBAL or LOCAL.
Database handles with EXTERNAL scope are never initialized.
NOTE
In versions prior to Rdb/VMS, you cannot override these
initializations in RDBPRE.
2.23.2 Autolocking Options in the RESERVING Clause of the
START_TRANSACTION Statement
Note that this is the same new feature that is now supported in
RDML. See Section 2.24.2 for more information.
2.23.3 REQUEST_HANDLE SCOPE Clause
Note that this is the same new feature that is now suported in
RDML. See Section 2.24.3 for more information.
2.24 RDML: New and Changed Statements
2.24.1 RDML and Rdb/ELN: EPascal Default Changed for /LINKAGE
Qualifier
This note pertains only to sites that install Rdb/VMS V3.1 and
that use Rdb/ELN. The change does not affect sites that use
Rdb/ELN but that do not install Rdb/VMS V3.1.
The Relational Data Manipulation Language (RDML) now uses
/LINKAGE=GLOBAL_SYMBOLS qualifier as the default for EPascal.
(The default for VAX Pascal and VAX C is still /LINKAGE=PROGRAM_
SECTIONS.) Moreover, RDML for EPascal only supports GLOBAL_SYMBOLS
for the /LINKAGE qualifier. If you specify /LINKAGE=PROGRAM_
SECTIONS with EPascal, RDML issues the following informational
message:
2-52 New and Changed Features
%RDML-I-PSECTNOEFF, Linkage type PROGRAM_SECTIONS is not supported with EPASCAL
-- using linkage type GLOBAL_SYMBOLS
2.24.2 Autolocking Options in the RESERVING Clause of the
START_TRANSACTION Statement
If you use the WITH AUTO_LOCKING option (the default), constraints
and triggers defined on the reserved relations will be able to
access additional relations that do not appear in the list of
reserved relations. The WITH AUTO_LOCKING option will not work for
other relations not referred to in the reserving clause.
NOTE
If you use the RESERVING clause and the WITH NOAUTO_LOCKING
option, you can access only those relations that you have
explicitly reserved. If you access multiple databases with
a single START_TRANSACTION statement and use the RESERVING
clause for one or more databases, you can access only the
reserved relations in a database for which you reserve
relations.
2.24.3 REQUEST_HANDLE SCOPE Clause
The following clause of the RDML DATABASE statement was new for
Rdb/VMS V3.1:
REQUEST_HANDLE SCOPE IS scope-name
Scope-name is either DEFAULT or FINISH. The REQUEST_HANDLE SCOPE
clause determines whether system or user request handles are set
to zero when a FINISH statement is issued.
The default is DEFAULT; the value of the request handle is not
set to zero after the RDML FINISH statement executes. Specifying
REQUEST_HANDLE SCOPE IS FINISH causes the value of the request
handle to be set to zero when a FINISH statement is issued.
New and Changed Features 2-53
When the user process detaches from the database (for example,
with FINISH), any request handles that were used in queries
against that database during that attach become invalid. If you
wish to reuse any of those request handles again in a subsequent
database attach, they must be reinitialized to zero first.
Before the REQUEST_HANDLE SCOPE clause was available, if you
wished to use any request handle again in RDBPRE, or some re-
quest handles in RDML, you were forced to initialize them to zero
yourself. This in turn forced you to name them explicitly. The
REQUEST_HANDLE SCOPE IS FINISH option causes all request handles
automatically to be reinitialized to zero when a FINISH statement
is issued. Using this option means that you have less need to use
explicit request handles.
2.25 Linking RDML Applications
Effective with Rdb/VMS V3.1, RDML applications no longer require
linking with VAXCRTLG library. RDML/Pascal applications never need
to be linked with the VAXCRTLG library, and RDML/C applications
need to be linked with the VAXCRTLG library only if the host
application code requires it. RDML/C applications that use VAX
C library calls but do not use the G-floating data type no longer
require linking with the VAXCRTLG library, but may instead link
with the VAXCRTL (D-floating) library.
Note that all RDML applications still need to be linked with the
RDMLRTL.OLB library.
Applications that do use G-floating data type still must be com-
piled specifying the /G_FLOATING qualifier. For example:
$ CC :== CC/G_FLOATING
$ PASCAL :== PASCAL/G_FLOATING
2-54 New and Changed Features
In some cases it is necessary that code not be compiled with the
G-floating data type. This is generally due to reasons determined
by the programmer such as using a library that does not support G-
floating data types. When compiling such programs, the /G_FLOATING
qualifier must not be used, and the RDML application cannot refer
to fields in the database that are of G-floating data type.
CAUTION
Reading or writing into fields of G-floating data type in
an application that is not compiled with the /G_FLOATING
qualifier will produce undefined results. In those cases,
data from fields defined in the database of data type G-
floating must not be accessed in the program. Applications
that disregard this are not supported by Digital.
In any RDML application, any mixing of files compiled with
the /G_FLOATING qualifier and files compiled without the
/G_FLOATING qualifier are not supported by Digital.
2.25.1 Linking RDML/C Applications
An RDML/C application is linked differently depending upon whether
or not it was compiled with the /G_FLOATING qualifier. Generally,
an RDML application should be compiled with the /G_FLOATING quali-
fier.
2.25.1.1 Linking Applications Compiled with the /G_FLOATING
Qualifier
To link RDML/C applications compiled with the G-floating data
type, use the following two lines in a linker options file or a
command procedure file:
SYS$LIBRARY:RDMLRTL.OLB/LIBRARY
SYS$SHARE:VAXCRTLG.EXE/SHARE
New and Changed Features 2-55
For example, using an options file:
$!
$! RDMLOPT_C.OPT
$!
SYS$LIBRARY:RDMLRTL.OLB/LIBRARY
SYS$SHARE:VAXCRTLG.EXE/SHARE
To link an application named INVENT, type the following at the DCL
prompt:
$ LINK INVENT,RDMLOPT/OPT
As an alternative, you could use a command procedure file:
$!
$! MYLINK_C.COM
$!
$ LINK 'P1',SYS$INPUT:/OPT
SYS$LIBRARY:RDMLRTL.OLB/LIBRARY
SYS$SHARE:VAXCRTLG.EXE/SHARE
$ EXIT
Then run the command procedure to link the application INVENT:
$ @MYLINK_C INVENT
2.25.1.2 Linking Applications Compiled without the /G_FLOATING
Qualifier
To link RDML/C applications compiled without the G-floating data
type, use the following two lines in a linker options file or a
command procedure file:
SYS$LIBRARY:RDMLRTL.OLB/LIBRARY
SYS$SHARE:VAXCRTL.EXE/SHARE
2-56 New and Changed Features
For example, using an options file:
$!
$! RDMLOPT_C_NOG.OPT
$!
SYS$LIBRARY:RDMLRTL.OLB/LIBRARY
SYS$SHARE:VAXCRTL.EXE/SHARE
To link an application named INVENT, type the following at the DCL
prompt:
$ LINK INVENT,RDMLOPT_C_NOG/OPT
As an alternative, you could use a command procedure file:
$!
$! MYLINK_C_NOG.COM
$!
$ LINK 'P1',SYS$INPUT:/OPT
SYS$LIBRARY:RDMLRTL.OLB/LIBRARY
SYS$SHARE:VAXCRTL.EXE/SHARE
$ EXIT
Then run the command procedure to link the application named
INVENT:
$ @MYLINK_C_NOG INVENT
2.25.2 Linking RDML/Pascal Applications
To link RDML/Pascal applications, you need to direct the linker to
use the RDMLRTL support library (as with RDML/C applications). For
example, to link an application named SALARY, type the following
at the DCL prompt:
$ LINK SALARY,SYS$LIBRARY:RDMLRTL/LIBRARY
New and Changed Features 2-57
2.26 RMU: New and Changed Commands
The following Rdb/VMS Management Utility (RMU) commands are new
for Rdb/VMS V3.1. See the VAX Rdb/VMS RDO and RMU Reference Manual
for detailed descriptions of the commands.
2-58 New and Changed Features
o RMU/UNLOAD and RMU/LOAD
These commands allow you to unload data from a table (all
or selected columns) into a specially formatted VMS Record
Management Services (RMS) file, and then load the data into
another table in the same database or a different database.
The RMU/LOAD command also allows you to load data from an
RMS file into a table in an Rdb/VMS database. (To do this,
you need to create a special RMS record definition file using
RMU/UNLOAD.) This use of RMU/LOAD can provide an alternative to
using a program or DATATRIEVE for database load operations from
RMS files.
Applications can include the same CDD/Plus record definition
as was used to define the relation in RDO or SQL. This may
simplify the creation of loadable files, or reading files
created by RMU/UNLOAD command.
o RMU/OPEN and RMU/CLOSE
The RMU/OPEN command optimizes database attach operations for
all database users by mapping the database root file for each
process. Overhead normally charged to your process is absorbed
by Rdb/VMS when you use the RMU/OPEN command.
The RMU/CLOSE command controls the process of eliminating
active user access to a specific database. You can specify
whether access to the database is curtailed for users on a sin-
gle node or for users on all nodes in a VAXcluster environment.
The RMU/OPEN and RMU/CLOSE commands are intended to replace the
RDO OPEN and CLOSE statements, respectively. You are encouraged
to use these two RMU commands instead of the RDO statements be-
cause the RDO OPEN and CLOSE statements might not be supported
in future releases of Rdb/VMS.
o RMU/RECOVER
The RMU/RECOVER command reconstructs a database by entering
past transactions again from the after-image journal file in a
database restored from a backup copy.
New and Changed Features 2-59
The RMU/RECOVER command is intended to replace the RDO RECOVER
statement. You are encouraged to use this RMU command instead
of the RDO statement because the RDO RECOVER statement might
not be supported in future releases of Rdb/VMS.
The following RMU commands have these additions and modifications
effective with Rdb/VMS V3.1. For more detail on all RMU commands,
see the VAX Rdb/VMS RDO and RMU Reference Manual. For tutorial
information, see the VAX Rdb/VMS Guide to Database Maintenance and
Performance.
2-60 New and Changed Features
o RMU/ANALYZE
RMU/ANALYZE has new /BINARY_OUTPUT, /PLACEMENT, and /CARDINALITY
qualifiers:
- /BINARY_OUTPUT
Specifying the /BINARY_OUTPUT qualifier allows you to output
the summary results to a binary file, and to create a record
definition file that is compatible with CDD/Plus for the
binary output file. The binary output can be loaded into an
Rdb/VMS database with the RMU/LOAD/RMS_RECORD_DEF command
for use by a user-written management application or pro-
cedure. The binary output can also be used directly by the
user-written application or procedure.
The default is the /NOBINARY_OUTPUT qualifier, which does
not create an output file.
The valid file options are:
o FILE=file-spec
The FILE option causes the ANALYZE data to be output to
an RMS file that contains a fixed-length binary record
for each storage area and logical area analyzed. The
default file type for the binary output file is UNL. The
following command creates the binary output file ANALYZE_
OUT.UNL:
$ RMU/ANALYZE/BINARY_OUTPUT=FILE=ANALYZE_OUT MF_PERSONNEL.RDB
o RECORD_DEFINITION=file-spec
The RECORD_DEFINITION option causes the ANALYZE data
record definition to be output to an RMS file. The output
file contains the definition in a subset of the CDD/Plus
CDO command format, a format very similar to RDO field
and relation definitions. The default file type for
the record definition output file is RRD. The following
command creates the output file ANALYZE_OUT.RRD:
New and Changed Features 2-61
$ RMU/ANALYZE/BINARY_OUTPUT=RECORD_DEFINITION=ANALYZE_OUT -
_$ MF_PERSONNEL.RDB
You can specify both file options in one command by
separating them with a comma and enclosing them within
parentheses:
$ RMU/ANALYZE/BINARY_OUTPUT= -
_$ (FILE=ANALYZE_OUT,RECORD_DEFINITION=ANALYZE_OUT) -
_$ MF_PERSONNEL.RDB
- The /PLACEMENT qualifier allows you to generate formatted
displays of statistical information describing the record
placement relative to the index structures defined for the
database.
- The /CARDINALITY qualifier reports on and optionally updates
the cardinality values for all tables and indexes, or for
those you specify. The output is a table with the following
columns:
INDEX_NAME RELATION_NAME STORED ACTUAL
The format for the command is:
RMU/ANALYZE/CARDINALITY [/[NO]UPDATE] [/[NO]CONFIRM] [/OUTPUT=file]
root-file [table-or-index,...]
The /UPDATE qualifier stores the calculated cardinalities in
the metadata. It does so even if the RDB$SYSTEM storage area
is read-only. The default is /NOUPDATE.
The /CONFIRM qualifier permits the user to selectively
update the stored cardinality, substitute another value,
or quit. The default is /NOCONFIRM.
The /OUTPUT qualifier specifies an alternate file for the
output of the /ANALYZE/CARDINALITY qualifier. The default is
/OUTPUT=SYS$OUTPUT.
o RMU/BACKUP
2-62 New and Changed Features
The RMU/BACKUP command has new /EXCLUDE and /INCLUDE,
/CHECKSUM_VERIFICATION and /NOCHECKSUM_VERIFICATION qualifiers,
and changes and additions to the /CRC qualifier:
- /EXCLUDE and /INCLUDE
The /EXCLUDE qualifier allows you to specify the storage
areas that you want to exclude from the backup file. The
default for /EXCLUDE is to exclude no storage areas.
The /INCLUDE qualifier allows you to specify the storage
areas that you want to include in the backup file. Because
the default for /INCLUDE is all storage areas, you do not
need to use this qualifier when you are backing up all the
storage areas in your database.
- /CHECKSUM_VERIFICATION and /NOCHECKSUM_VERIFICATION (de-
fault)
The /CHECKSUM_VERIFICATION qualifier requests that the
RMU/BACKUP command verify the CHECKSUM stored on each
database page before it is backed up, thereby providing
end-to-end error detection on the database I/O operations.
The default is /NOCHECKSUM_VERIFICATION.
You can save CPU resources by specifying the /CHECKSUM_
VERIFICATION qualifier only when you are experiencing, or
have experienced disk, HSC, or CI port hardware problems.
The effect of specifying /CHECKSUM_VERIFICATION in
the RMU/BACKUP command is similar to performing an
RMU/VERIFY/AREA/CHECKSUM_ONLY command prior to the backup
operation.
- /CRC=CHECKSUM
The default for the /CRC (cyclic redundancy check) qualifier
has changed for HSC tape drives (TA78, TA79, and TA81) from
/NOCRC to /CRC. The default remains /NOCRC for other GCR
(6250 bpi) tape drives and for TA90 (IBM 3480 class) drives.
New and Changed Features 2-63
This new option uses ones-complement addition, which is
the same computation used to checksum the database pages on
disk. This is the default for TA78, TA79, and TA81 drives.
These HSC drives have adequate error detection capability,
but CI contention may cause data underruns and unrecover-
able restore errors unless end-to-end error detection is
employed.
The /CRC=CHECKSUM option adequately detects data underrun
errors and is about six times faster than the CRC microcode
instruction.
- /CRC[=AUTODIN_II]
This new option uses the AUTODIN_II polynomial for the 32-
bit CRC calculation and provides the most reliable end-
to-end error detection. This is the default for NRZ/PE
(800/1600 bpi) tape drives.
If your CPU has the CRC instruction implemented in mi-
crocode, it will be used. If your CPU does not have the CRC
instruction implemented in microcode, RMU will automatically
use a high performance software procedure (/CRC=AUTODIN_II)
instead of the CRC instruction.
- /NOCRC
This option disables end-to-end error detection. This is the
default for other GCR (6250 bpi) tape drives and TA90 (IBM
3480 class) drives.
NOTE
The overall effect of the /CRC=AUTODIN_II, /CRC=CHECKSUM,
and /NOCRC defaults is to raise tape reliability to
an even level with disk reliability. If you retain
your tapes longer than 1 year, the /NOCRC default
may not be adequate. For tapes retained longer than 1
year, use the /CRC=CHECKSUM qualifier.
If you retain your tapes longer than 3 years, you
should always use the /CRC=AUTODIN_II qualifier.
2-64 New and Changed Features
Tapes retained longer than 5 years could be deterio-
rating and should be copied to fresh media.
New and Changed Features 2-65
o RMU/CONVERT
The RMU/CONVERT command now behaves slightly differently for
Rdb/VMS V3.1. See Section 1.6 for details.
o RMU/RESTORE
The RMU/RESTORE command has a new /AREA qualifier and automati-
cally calls RMU/CONVERT when restoring a Version 3.0 database:
- /AREA
The /AREA qualifier allows you to specify that only the
storage areas listed in the storage-area-name parameter on
the command line or in the /OPTIONS file are to be restored.
By default, the /AREA qualifier is not specified, and all
storage areas in the backup file are restored.
- The RMU/RESTORE command will automatically call the
RMU/CONVERT command to convert a Versopm 3.0 database to
Versopm 3.1 format.
o RMU and the /UNTIL qualifier
The following RMU commands with the /UNTIL qualifier now accept
VMS V5.0 date and time strings as date input:
o RMU/SHOW STATISTICS/UNTIL
o RMU/BACKUP/AFTER/UNTIL
o RMU/RECOVER/UNTIL
The /UNTIL qualifier now accepts international dates. Refer to
the VMS RTL Library (LIB$) Manual for more information. Some
examples are:
2-66 New and Changed Features
$ DEFINE LIB$DT_INPUT_FORMAT "!DB!MAAU-!Y4 !H04:!M0:!S0.!C2"
$ RMU/RECOVER/UNTIL=10-JUN-1989 PERSO_BACKUP
$ RMU/RECOVER/UNTIL="10-JUN-1989 12:56" PERSO_BACKUP
$ DEFINE LIB$DT_INPUT_FORMAT "!MAU !DD, !Y4 !H02:!M0:!S0:!C2 !MIU"
$ RMU/SHOW STATISTICS/UNTIL="1 April, 1989 10:20 PM" PERSONNEL
$ DEFINE SYS$LANGUAGE "SPANISH"
$ RMU/BACKUP/AFTER/CONTINUOUS/UNTIL="1 abril, 1989" PERSONNEL PERSO_BACKUP
o RMU/VERIFY
The RMU/VERIFY command has been enhanced and has some new
qualifiers:
- General enhancements
1.A more complete index structure check for both sorted and
hashed indexes.
2.Generation of specific error messages when certain prob-
lems are detected such as missing duplicate B-tree nodes
and a complete listing of all invalid areas in a list.
3.Detection of duplicate names within the set of table
names and within the set of storage area names.
4.A more graceful exit from the verify operation such as
when a metadata corruption error condition is detected;
when this situation occurs, more specific information is
returned to the user.
5.Records of elapsed time for area verification and com-
plete database verification. The RMU/VERIFY log messages
indicate the time it takes to verify an area or a com-
plete database. By saving logs from a previous RMU/VERIFY
session, you can determine how long it takes to verify
various areas and decide what verifications to perform
in the time available. In general, the elapsed times are
New and Changed Features 2-67
consistent only for the same command because of the op-
timizations. For example, local area verification time
depends on if area verification is also specified.
6.Verification of hashed indexes within the same area in
one pass.
Digital strongly recommends that you verify all databases to
determine if they will still continue to verify successfully
with the enhanced RMU/VERIFY command.
- /TRANSACTION=READ_ONLY
You will no longer be able to specify a transaction type of
BATCH for the RMU/VERIFY command. The Version 3.0 BATCH op-
tion is being renamed to the READ_ONLY option. For example:
RMU/VERIFY/TRANSACTION_TYPE=BATCH PERSONNEL /AREAS=* ! Rdb/VMS V3.0
RMU/VERIFY/TRANSACTION_TYPE=READ_ONLY PERSONNEL /AREAS=* ! Rdb/VMS V3.1
No behavior has changed. When you specify the READ_ONLY
transaction type, RMU uses snapshot files when it verifies
the storage areas. This is the same behavior as TRANSACTION_
TYPE=BATCH in Rdb/VMS V3.0.
- /AREA/SNAPSHOTS
A new qualifier, /SNAPSHOTS, has been added to the
RMU/VERIFY command. If you specify the /SNAPSHOTS qualifier
with the /AREAS qualifier, the snapshot areas correspond-
ing to the areas specified in the /AREAS qualifier are also
verified. The /SNAPSHOTS qualifier can be used only with
the /AREAS qualifier. Only the page header is verified for
snapshot pages if the /SNAPSHOTS qualifier is specified. The
/ALL qualifier includes verification of the snapshot areas.
NOTE
If you use the /INCREMENTAL qualifier with the
RMU/VERIFY command, Digital Equipment Corporation
2-68 New and Changed Features
recommends that you use it only with the /ALL quali-
fier and not with any other qualifiers.
The date and time stamps in the database root file
are updated during full and incremental verifications
only when the /ALL qualifier is specified. Therefore,
if you do not specify the /ALL qualifier, two suc-
cessive incremental verifications of the same storage
area of the database perform the same verifications.
This means that the second incremental verification
will not skip pages verified by the first incremental
verification, contrary to what you might expect.
- /INDEX/[NO]DATA
A new qualifier /DATA causes data records to be fetched
while verifying indexes. The /NODATA qualifier means data
records are not fetched. Verification of indexes takes much
longer if /DATA is specified. The default is /DATA.
- /CHECKSUM_ONLY
A new qualifier, /CHECKSUM_ONLY, has been added to the
RMU/VERIFY command. You must specify the /CHECKSUM_ONLY
qualifier with the /AREAS qualifier. By default, the
RMU/VERIFY command performs full verification of database
pages. Specifying the /CHECKSUM_ONLY qualifier with the
/AREAS qualifier reduces the degree of verification done
on a database page and causes the RMU/VERIFY command to ex-
ecute faster. This qualifier allows you to make tradeoffs
between the performance of verification and the thoroughness
of verification.
You may also specify the /INCREMENTAL, /START, and /END
qualifiers with the /CHECKSUM_ONLY qualifier. For example:
RMU/VERIFY/AREAS=*/CHECKSUM_ONLY/LOG MY_LARGE_DATABASE
New and Changed Features 2-69
If the RMU/VERIFY/CHECKSUM_ONLY command detects a problem
with a certain page, then that page may be verified in depth
by using other qualifiers such as the /INDEX and /LAREA
qualifiers.
2.27 Obsolete RDO Statements
The BACKUP, RESTORE, and SPOOL statements are no longer supported
for the Relational Database Operator (RDO) interface. The Version
3.0 release notes stated that these statements were obsolete. The
statements were maintained for V3.0 to give users time to change
applications that used these statements.
The following sections provide further information on these obso-
lete statements.
2.27.1 RDO BACKUP and RESTORE Statements No Longer Supported
Effective with Rdb/VMS V3.1, the BACKUP and RESTORE statements
are no longer supported with the RDO interface. The RDO EXPORT
statement replaces the RDO BACKUP statement and the RDO IMPORT
statement replaces the RDO RESTORE statement.
The EXPORT and IMPORT statements are used primarily for restruc-
turing of databases. The EXPORT and IMPORT statements can also be
used for migrating a database from one DIGITAL Standard Relational
Interface (DSRI) system to another, for example, from Rdb/ELN
to Rdb/VMS. For information on these statements, see the VAX
Rdb/VMS RDO and RMU Reference Manual. For regular backups and
restore operations of Rdb/VMS databases, use the RMU/BACKUP and
RMU/RESTORE commands described in the VAX Rdb/VMS RDO and RMU
Reference Manual.
2-70 New and Changed Features
2.27.2 RDO SPOOL Statement No Longer Supported
Effective with Rdb/VMS V3.1, the SPOOL statement is no longer
available with the RDO interface. The RMU/BACKUP/AFTER_JOURNAL
command replaces the RDO SPOOL statement. For information on RMU
commands, see the VAX Rdb/VMS RDO and RMU Reference Manual.
2.27.3 RDO ANALYZE, RDO CLOSE, RDO OPEN, and RDO RECOVER Statements
Are Obsolete
The RMU/ANALYZE, RMU/CLOSE, RMU/OPEN, and RMU/RECOVER commands
replace the now obsolete RDO ANALYZE, RDO CLOSE, RDO OPEN and RDO
RECOVER statements, which will be retired in a future release.
For information on RMU commands, see the VAX Rdb/VMS RDO and RMU
Reference Manual.
2.28 Changes Related to the Sample Personnel Database
As in previous releases, with Version 3.1 the sample personnel
database is referred to extensively in Rdb/VMS documentation and
training materials, and you can still create both the single-file
(PERSONNEL) and multifile (MF_PERSONNEL) forms of the database.
However, with Version 3.1 significant changes have been made in
the following areas:
o The way you create the sample database
o The files used to create the database
o Differences in data definitions depending on whether you chose
SQL or RDO statements to create the database
The following sections explain these changes.
New and Changed Features 2-71
2.28.1 Creating the Sample Database
You use a single command procedure (RDM$DEMO:PERSONNEL.COM) to
create the database, and you can specify parameters when you
invoke the procedure to specify certain options, such as whether
you want the single-file or multifile version and whether you want
the database created using SQL or RDO statements. The format of
the command you enter to create the sample database is as follows:
$ @RDM$DEMO:PERSONNEL interface-language database-form dictionary-use
The three parameters and their defaults are as follows:
o interface-language: SQL or RDO. Default: SQL.
o database-form: S (single-file) or M (multifile). Default: S.
o dictionary-use: CDD (use CDD/Plus dictionary) or NOCDD (do
not use dictionary). Default: Allow the user to choose. The
procedure also displays the approximate number of disk blocks
that will be used and allows the user to exit the procedure;
thus, you may wish to omit the dictionary-use parameter.
You may use upper case, lower case, or mixed case to specify the
parameters. All parameters are optional; for example, to create
a single-file database using SQL definitions and have a menu ask
about dictionary use, you can simply enter:
$ @RDM$DEMO:PERSONNEL
However, if you want to specify the second or third parameter, you
must also specify any preceding parameters. For example, to create
a single-file database using RDO definitions, you must enter:
$ @RDM$DEMO:PERSONNEL RDO S
Other examples:
$ @RDM$DEMO:PERSONNEL SQL M
$ @RDM$DEMO:PERSONNEL RDO M
2-72 New and Changed Features
Regardless of the interface language used, PERSONNEL.COM cre-
ates a database named PERSONNEL.RDB if you are creating a single-
file database, and it creates a database named MF_PERSONNEL.RDB
(plus related storage area files) if you are creating a multi-
file database. Note also that you can use either the SQL or the
RDO interface to work with the resulting database or databases,
regardless of whether the database was created using SQL or RDO
command files.
NOTE
The log of the database definition statements used in cre-
ating the database is placed in a file called PERSONNEL.LOG
in the same directory as the database files. The file is
named PERSONNEL.LOG regardless of which options you spec-
ified or accepted as defaults (for example, regardless of
whether you created a single-file or multifile database).
2.28.2 Files Used in Creating the Sample Database
All of the files used in creating the sample database are in
the directory with the logical name RDM$DEMO. (The logical name
SQL$SAMPLE also points to this directory; you may use the logical
names RDM$DEMO and SQL$SAMPLE interchangeably if you wish.)
The command procedure file PERSONNEL.COM uses the parameters to
invoke a series of SQL or RDO command files to build the appropri-
ate form of the database (single-file or multifile). To see a list
of the SQL and RDO command files in RDM$DEMO directory, you can
enter the following commands:
$ DIRECTORY RDM$DEMO:*.SQL
$ DIRECTORY RDM$DEMO:*.RDO
You may wish to study these files, as well as the PERSONNEL.COM
file, for possible use a models when you create database defini-
tion files.
New and Changed Features 2-73
NOTE
MF_PERSONNEL.COM and SQL$PERSONNEL.COM are no longer re-
quired or supplied with Rdb/VMS V3.1.
2.28.3 Differences Between SQL-Defined and RDO-Defined Databases
The data definitions for the sample personnel database are almost
the same regardless of the interface language used or the form
of the database. (The multifile form has the same definitions as
the single-file form, but includes some additional definitions
specific to a multifile implementation, such as storage areas,
hashed indexes, and record placement by index value.) There are,
however, some differences between sample databases of the same
form depending on whether you used the SQL or RDO definitions in
creating the database. The significant differences are as follows:
o Sample databases created with the SQL definitions do not in-
clude the RESUME domain, the RESUMES table, or any triggers or
other structures referring to RESUMES. The reason is that in
Rdb/VMS V3.1, SQL does not include support for the segmented
string data type. The RDO command files define RESUME as a
global field of segmented string data type, and also define a
RESUMES relation (table) and other related structures.
If you wish to use segmented strings with Rdb/VMS V3.1, you
must use RDO, RDBPRE, or RDML to create and manipulate them.
o The SQL command files define default values for certain do-
mains, whereas the RDO procedures define missing values for
the corresponding global fields. Section 2.14 discusses the
distinction between a default value and a missing value.
o The SQL command files define constraints to enforce the refer-
ential integrity of the database as part of the CREATE TABLE
statements.
2-74 New and Changed Features
2.29 Query Optimizer Improvements
2.29.1 Query Solution Placement of Boolean Expressions Was
Inefficient
For aggregate and GROUP BY queries and subqueries, the Boolean
expressions used to restrict the rows delivered from the vari-
ous record streams were not placed efficiently within the query
strategy.
These Boolean expressions are now placed within the strategies so
that they more effectively reduce the number of rows delivered to
the outer elements of the strategy. This new placement reduces the
overall time needed to produce results.
2.29.2 New Query Optimizer Feature, BEFORE-GET SELECTION
The Rdb/VMS optimizer now uses a new strategy for matching boolean
expressions, or predicates, in WITH (RDO) or SELECT (SQL) clauses.
This new strategy uses fields from the index to decide if it is
beneficial to perform further disk I/O.
Use of this new strategy is indicated by the text (key-only
Boolean) appearing in the RDMS$DEBUG_FLAGS "S" strategy dis-
play. The text will appear at the end of text related to the
index retrieval, and it should be interpreted as described in
this section.
Key-only Boolean means that as many Boolean expressions as pos-
sible are evaluated on the index segments before attempting to
fetch the actual data record. In many cases, this will result in
fewer I/O operations when evaluating queries which have complex
record selection expressions, and that can be solved using index
retrieval.
New and Changed Features 2-75
For example, if you have three segments (fields) in an index
[ORDER_NUMBER, CUSTOMER_NUMBER, SHIP_DATE], and you do a lookup
that does not specify all the index segments (for example one us-
ing only ORDER_NUMBER and SHIP_DATE) then the optimizer will do
an index lookup using the one leading specified segment. The op-
timizer will then do a comparison with SHIP_DATE from the index
prior to fetching the data record. In previous versions I/O opera-
tions would be performed to fetch the data record before doing the
Boolean selection. If the SHIP_DATE fails the comparison, then the
I/O to the data record is avoided.
In a more general index, such as [S1, S2, S3], the Boolean parts
good for index-time selection are:
S3 = 9
S2 = S3
S2 <> S3
S2 + S3 < 10
S1 containing "xyz"
S3 = 9 or S2 * S3 < 100
2.29.3 Query Optimizer Chooses Between Ascending and Descending
Indexes
The query optimizer now chooses the appropriate strategy for
ascending and descending indexes. Note that you can have both
ascending and descending indexes on the same field. The query
optimizer will choose the appropriate one for the specified query.
However, this added benefit means that performance for updates may
degrade.
2.29.4 Index Cost Calculations Have Been Further Extended to Favor
Index Retrieval Over Sequential Scans
The index cost calculations for relations stored in mixed format
areas have been modified so that these calculations are in line
with the uniform area index cost calculations.
2-76 New and Changed Features
The basic intent of this change in index cost calculation is to
favor index retrieval over sequential scan of a relation whenever
there is a Boolean expression on the first segment of an index
in the query. This bias favoring index retrieval existed for
relations stored in uniform areas with Version 3.0. Now with
Version 3.1, the bias toward using an index will be extended to
relations stored either in uniform or in mixed format areas.
2.30 Summary of Documentation Additions and Changes
This section describes the highlights of changes to the Rdb/VMS
V3.1 documentation. Many changes reflect the inclusion of VAX
SQL and VAX SQL/Services products as part of VAX Rdb/VMS and the
emphasis on SQL as the preferred interface to Rdb/VMS:
o VAX SQL Reference Manual
- Renamed to VAX Rdb/VMS SQL Reference Manual
- Two-volume format:
* A command dictionary of all SQL statements (Part 1)
* Information on SQL module language and the precompilers
(Part 2)
- Highlights extensions to the SQL ANSI standard X3.135-1986
o VAX Rdb/VMS Guide to Database Maintenance and Performance
- Revised with examples of both SQL and RDO data definition
and data manipulation statements
- Two-volume format:
* Database maintenance information (Part 1)
New and Changed Features 2-77
* Database performance information (Part 2)
- New organization includes:
* New chapter on verifying your database
* Expanded chapters and sections describing new RMU com-
mands and qualifiers
o VAX SQL User's Guide
- Includes information from the VAX Rdb/VMS Guide to Data
Manipulation
- Renamed to the VAX Rdb/VMS Guide to Using SQL
o VAX Rdb/VMS Reference Manual
- Two-volume format:
* Information on RDO language elements, RDO functions,
value expressions, records selection expressions, field
attributes, and on RMU commands (Part 1)
* Information on Rdb/VMS statements, reserved words, error
message files, RDO run-time license, and on values for
database and storage area parameters (Part 2)
- Renamed to the VAX Rdb/VMS RDO and RMU Reference Manual
o VAX Rdb/VMS Guide to Programming
- Renamed to the VAX Rdb/VMS Guide to Using RDO, RDBPRE, and
RDML
- Includes information from the VAX Rdb/VMS Guide to Data
Manipulation
2-78 New and Changed Features
- Programming section contains much material describing new
features of the RDO, RDML and RDBPRE interfaces
o VAX Rdb/VMS Guide to Database Design and Definition
- Revised with examples of SQL data definition instead of RDO
- New chapter on data reorganization
o VAX Rdb/VMS Introduction and Master Index
- Introduces and illustrates the SQL interface, while also
mentioning RDO, RMU and RDML
- Newly expanded glossary
- Master index now includes SQL
o VAX Rdb/VMS Guide to Data Manipulation
Removed and information incorporated into the VAX Rdb/VMS Guide
to Using RDO, RDBPRE, and RDML and the VAX Rdb/VMS Guide to
Using SQL
o VAX Rdb/VMS Installation Guide
Describes the combined installation of Rdb/VMS and the former
SQL and SQL/Services products.
o VAX Rdb/VMS Release Notes
- Combines information for Rdb/VMS, SQL, and SQL/Services
o Quick Reference Guides
- Adds the VAX Rdb/VMS SQL Quick Reference Guide
New and Changed Features 2-79
- Drops the RDML Quick Reference Guide and the RDO Quick
Reference Guide
2-80 New and Changed Features
Chapter 3
Software Errors Fixed
The following sections describe problems with previous versions of
the Rdb/VMS software that are fixed in Version 3.1. These software
problems no longer exist.
The chapter begins with information pertinent to all users. Later
sections contain material specifically for users of SQL, RDO, and
RDML. Therefore, the notes of problems fixed may use different
database terms to mean the same thing. For example, some terms
used by SQL differ from terms used by other interfaces, such as
RDO or RDML. Table 3-1 shows some of the different terms used.
Table_3-1:__Differences_in_Relational_Terminology_________________
SQL______________RDO,_RDML________ANSI/ISO_SQL_STANDARD___________
Alias Context vari- Alias
able
Authorization Database handle Authorization identifier
identifier
Column Field Column
Software Errors Fixed 3-1
Table_3-1_(Cont.):__Differences_in_Relational_Terminology_________
SQL______________RDO,_RDML________ANSI/ISO_SQL_STANDARD___________
Column select Record selec- Column select expression
expression tion expression
Parameter Host language Parameter
variable
Predicate Conditional Predicate
expression
Result table Record stream Result table
Row Record Row
Table____________Relation_________Table___________________________
3.1 General (All Interfaces) Database Administration and Maintenance
The following sections describe corrected software errors that
pertain to database administration and maintenance.
3.1.1 RUJ Files Were Not Being Deleted Once Rdb/VMS Was Finished
with Them
Rdb/VMS was not able to delete recovery-unit journal (RUJ) files
once it was finished with them because of the access control list
(ACL) assigned to the RUJ directory.
This problem is fixed. Rdb/VMS now creates ACLs on RUJ files
so these files can be deleted when the user detaches from the
database.
3-2 Software Errors Fixed
3.2 General (All Interfaces) Data Definition and Data Manipulation
The following sections describe corrected software errors that
pertain to data definition and data manipulation.
3.2.1 Partitioning Multisegmented Sorted Indexes Across Storage
Areas Caused Several Problems to Occur
There were several problems with the optimizer that occurred when
you accessed data through a multisegmented sorted index that
is partitioned across storage areas. The problems included the
inability to fetch the same data within a single attach or getting
a bugcheck dump when trying to access index nodes in different
partitions.
Queries worked using a partitioned multisegmented sorted index
until you tried to fetch data from a storage area in a lower
partition. If you fetched data from a storage area in a higher
partition and then tried to fetch data from a storage area in a
lower partition, no data was found in the lower partition. Note
that if the multisegmented sorted index was redefined to be in
one storage area, the query worked correctly. The index structure
was correct, but the optimizer did not use it correctly in Rdb/VMS
V3.0A.
This problem is fixed.
3.2.2 Storage Map Information Was Lost After Importing a Database
Exported Prior to V3.1
The RDO and SQL statements, SHOW INDEX and SHOW STORAGE MAPS,
did not display the partitioning information after a database was
created again using the IMPORT statement. This problem was caused
by an error in the EXPORT statement which failed to record the
original storage map information with other metadata.
This problem is fixed in Rdb/VMS V3.1. However, the EXPORT file
must be created using Rdb/VMS V3.1.
Software Errors Fixed 3-3
3.2.3 Defining a Storage Map with a Name Previously Used Caused a
Bugcheck Dump
If you defined a storage map with a name previously used, Rdb/VMS
produced a bugcheck dump.
This problem is fixed. If you use a storage area name that has
been previously used, Rdb/VMS displays the correct error message.
3.2.4 Defining a Storage Map Incorrectly Allowed Two Placement
Clauses
If you defined a storage map with two placement clauses, Rdb/VMS
did not return an error.
This problem is fixed. Rdb/VMS detects the existence of two or
more PLACEMENT VIA INDEX clauses and the combination of PLACEMENT
VIA and NO PLACEMENT VIA clauses. An appropriate error message is
returned.
3.2.5 Rdb/VMS Did Not Check Minimum Node Size at Index Definition
Time
There was a problem in the way that Rdb/VMS checked for minimum
node size of indexes to be defined. This led to some inconsistent
behavior such as the following:
%RDB-E-IMP_EXC, facility-specific limit exceeded
-RDMS-F-INDEX_S_MIN, user requested node size of 10 bytes for index needing 368
This problem is fixed. There is now error checking necessary to
calculate the minimum number of bytes for defining or changing
an index at definition time rather than when the first record is
stored in the index. You will get the same error message as shown
in the preceding example at the time when the index is defined
instead of when the index is first used.
3-4 Software Errors Fixed
3.2.6 Misplaced SORTED BY Clauses Produced Incorrect Results or
Bugcheck Dumps
There was a problem with misplaced SORTED BY clauses that produced
incorrect results or bugcheck dumps. In Rdb/VMS V2.1 through V3.0,
the query compiler made an erroneous effort to reposition the
SORTED BY clause to any outer query RSE which defined the context
used by the ANY subquery of the sort keys. This problem occurred
most commonly with queries constructed using RDO.
In the following query, the SORTED BY clause is syntactically part
of the RSE for the relation DEGREES, for which the sort keys did
not vary. So the query caused wasted effort to get the results for
the ANY subquery.
FOR E IN EMPLOYEES
WITH ANY D IN DEGREES WITH D.EMPLOYEE_ID = E.EMPLOYEE_ID
SORTED BY E.LAST_NAME,E.EMPLOYEE_ID
PRINT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME
END_FOR
However while the ANY subquery would work properly for the example
shown and produced what might be considered to be the intended
sort order, this technique would commonly produce wrong results
or bugcheck dumps when the sort keys were obtained from multiple
query or subquery RSE contexts. This effort was also in direct
violation of the DSRI specification, which allows a sort key to be
a value expression from any available context.
These problems have been fixed. For Version 3.1, Rdb/VMS will not
attempt to reposition SORTED BY clauses and will adhere to the
DSRI specification. However, this change will cause any previously
affected query to return different results until the query is
correctly re-specified.
To provide assistance in locating the erroneous queries, a new
logical name, RDMS$DIAG_FLAGS, can be defined:
$ DEFINE RDMS$DIAG_FLAGS S
Software Errors Fixed 3-5
When the RDMS$DIAG_FLAGS logical name is defined, the query com-
piler will check for sort keys that are totally defined by con-
texts external to the RSE, which includes the SORTED BY clause.
When such a case is encountered, the query compiler will produce
the following error message:
%RDB-E-INVALID_BLR, request BLR is incorrect at offset n
-RDMS-F-SORTKEYEXT, sort key is external to RSE context
3.2.7 Joining Multiple Tables with Equalities Could Return Wrong
Results
There was a problem when a query required a sort (any query with a
SORTED BY clause), potentially required a sort (contains a REDUCED
TO clause or ORDER BY clause), or used a strategy that usually
required a sort (a match strategy).
In the following example, two RSEs that effectively formulated the
same query returned different results. If you swapped the column
sources in the equality so that 1 becomes 3 and changed the GROUP
BY clause so that clause 2 became clause 4, then:
the following example returned employee ID "00165":
SELECT E.EMPLOYEE_ID, COUNT (*) FROM EMPLOYEES E, JOB_HISTORY J
WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID 1 AND E.LAST_NAME = "Toliver"
GROUP BY E.EMPLOYEE_ID; ------------------------------------ 2
the following example, however, returned employee ID "00164":
SELECT J.EMPLOYEE_ID, COUNT (*) FROM EMPLOYEES E, JOB_HISTORY J
WHERE J.EMPLOYEE_ID = E.EMPLOYEE_ID 3 AND E.LAST_NAME = "Toliver"
GROUP BY J.EMPLOYEE_ID;------------------------------------- 4
This problem is fixed.
3-6 Software Errors Fixed
3.2.8 Queries That Used Views and Nested Views Could Return Wrong
Results
There have been problems with queries in views and nested views
that returned wrong results.
This problem is fixed. Numerous general causes of incorrect re-
sults returned by queries using views or views of views have been
eliminated.
3.2.9 A Query to Retrieve Data Using the MISSING Operator Used a
Sequential Scan Rather Than a Defined Hashed Index
A query that used the MISSING operator to retrieve data from a
table with the value missing used a sequential scan of the table
rather than using the defined hashed index.
This problem is fixed. Both the RDO IS MISSING and SQL IS NULL
predicates are now treated as equality matches. The result will be
that a defined hashed index will now be used to retrieve data for
queries under these circumstances.
3.2.10 Query Optimizer Did a Poor Job with a Query in Which Neither
of the Segments Was the First
The optimizer did a poor job with the following query:
FOR C IN CUSTOMER_ACCESS_CONTROL
WITH C.VAX_CLUSTER_NAME = "ZSSW94" AND C.GCIS_USER = "CONNOR"
SORTED BY C.CUSTOMER_CODE
PRINT C.* END-F
There is an index scan performed but because neither of the seg-
ments in the key was the first, the query optimizer could not make
any matches and had to fetch each data record pointed to in the
index.
This problem is fixed. It is actually a new feature. See
Section 2.29.2 for more information.
Software Errors Fixed 3-7
3.2.11 Access to a View Was Denied Even Though You Had Read/Write
Access
When accessing a view in an Rdb/VMS database, if you did not have
any access using access control lists (ACLs) to the underlying
base relation, and you tried to start a read/write transaction,
access to the view was denied, even though you did have read/write
access to the view. However, if you first started a read-only
transaction (that reserved the view only), then committed the
transaction, you could then start a read/write transaction suc-
cessfully. The problem was that privileges were checked when the
metadata for the underlying relation was being loaded.
This problem is fixed in Version 3.1. Now Rdb/VMS only checks the
ACL protection for relations and views specified in the RESERVING
clause, or for those which are accessed by queries.
3.2.12 During Remote Database Access, the ROLLBACK Statement Caused
RALLY to Bugcheck Dump on the Local Node
During remote database access the ROLLBACK statement caused RALLY
to produce a bugcheck dump on the local node and resulted in the
following message:
%RDB-E-BAD_TRANS_HANDL, invalid transaction handle
The problem is fixed.
3.2.13 When an Invalid (Read-Only) Message Vector Was Passed to a
RDB$DATABASE_INFO Call, Rdb/VMS Stalled
Passing an invalid (read-only) message vector to a RDB$DATABASE_
INFO call caused Rdb/VMS to stall. Rdb/VMS checked this vector
for write access before storing any values in it and returned an
access violation if it could not write to the vector.
3-8 Software Errors Fixed
A major design assumption to the mechanism for permitting user
mode asynchronous traps (ASTs) was that Rdb/VMS could write a
status code into the message vector in order to return to the
user. Without that ability to write to the message vector, Rdb/VMS
stalls.
This problem is fixed. A change has been made so that Rdb/VMS
will make one additional check for write access to the message
vector at a point before requiring the use of this synchronization
mechanism and will return a code of SS$_ACCVIO to the user if the
vector does not permit write access.
This means that this return code will be in the status code in
read-only and not in the message vector. This means that a call to
LIB$SIGNAL with the message vector as an argument will probably
result in a meaningless and erroneous diagnostic message.
3.2.14 Change in the Way Query Optimizer Computed Index Cost
The problem of using a full index scan instead of using a range
of index values occurred because of the way the index cost was
being computed. The optimizer chose a full index scan only when
the table cardinality was in a certain narrow range of values.
Any value above or below this cardinality range did not have any
problem.
This problem is fixed. The cost formula has been modified in
Rdb/VMS V3.1.
3.2.15 Query with Index Retrieval Returned Extraneous Data
When a Boolean operator on an index segment was specified with
a segment value greater than the length of the index segment,
extraneous data was returned. This problem, shown in the following
example, occurred only when the data type of the index segment was
a TEXT data type consisting of five characters:
Software Errors Fixed 3-9
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = '00166XX'
PRINT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME
END_FOR
EMPLOYEE_ID FIRST_NAME LAST_NAME
00166 Rick Dietrich
This problem is fixed. The segment value is still used for index
retrieval but the Boolean operator involving this index segment is
tested after the key is fetched. This test can eliminate extrane-
ous data from the result.
3.2.16 Rdb/VMS Not Correctly Generating the Code for the Assignment
Statements Within the FOR Loop
Rdb/VMS was not correctly generating the code for the assignment
statements within the FOR loop. In the following example, the
exception returned when there was a FOR loop in BLR code that did
not contain an assignment:
***** Exception at 0005B071 : FINISH_ASSIGN + 0000005E
%SYSTEM-F-ACCVIO, access violation, reason mask=00,
virtual address=00000004, PC=0005B071, PSL=01400004
This problem is fixed.
3.2.17 Excessive Direct I/O at the First STORE Operation
In certain databases, Rdb/VMS caused an excessive number of di-
rect I/O operations at the first STORE operation to a relation.
Additional store operations to the relation in the same process
did not incur this overhead. If the process was started again, the
first STORE transaction again incurs an excessive number of direct
I/O operations.
This problem is fixed.
3-10 Software Errors Fixed
3.2.18 Form-Feed Characters Were Incorrectly Interpreted in RDO
In RDO, form-feed characters were treated as comment characters.
This problem is fixed in Version 3.1 so that form-feed characters
will be correctly treated as spaces by the RDO interface.
3.2.19 Constraints Sometimes Were Improperly Selected for Evaluation
In situations where a stream or FOR statement contained multiple
update data manipulation language (DML) statements (ERASE/DELETE,
MODIFY/UPDATE, or STORE/INSERT), constraints were either selected
for evaluation when not needed, or occasionally not selected for
evaluation when needed, depending on the order of the various DML
statements within the stream or FOR statement.
This problem is fixed.
3.2.20 There Was a Problem with the SHOW Statement
It appeared that a user had to perform two SET DICTIONARY commands
to change the default dictionary. This was a problem with the SHOW
command.
| This problem is fixed.
|
| 3.2.21 There was a Problem Deleting the Employees Relation in the
| V3.0A MF_PERSONNEL Sample Personnel Database
|
| There was a problem deleting the EMPLOYEES relation in the V3.0A
| MF_PERSONNEL sample personnel database after deleting the con-
| straints and views which are dependent upon employees.
|
| This problem is fixed.
Software Errors Fixed 3-11
3.3 SQL
This section describes corrections that are of interest to users
of the SQL interface.
3.3.1 Data Definition and Data Manipulation
The following sections describe corrected software errors that
pertain to data definition and data manipulation.
3.3.1.1 GROUP BY and Aggregate Expression Queries Could Return Wrong
Results, No Results, or Bugcheck Dumps
This problem occurred most commonly with views containing GROUP BY
or aggregate expressions. For example, the following would return
0 instead of 6:
SELECT COUNT (*) FROM CURRENT_JOB WHERE JOB_CODE = "SANL";
The following example would produce a bugcheck dump:
SELECT COUNT (*) FROM JOB_HISTORY J, CURRENT_JOB CJ
WHERE J.EMPLOYEE_ID = CJ.EMPLOYEE_ID;
This problem is fixed. Numerous general causes of incorrect re-
sults returned by queries using GROUP BY or aggregate expressions
have been eliminated.
3.3.1.2 There Were Problems with the Quantified Predicates ANY and
ALL
A number of quantified predicate ANY and ALL problems are fixed.
ANY and ALL now behave as specified in the ANSI standard.
3-12 Software Errors Fixed
3.3.1.3 There Was a Problem When You Ran Out of Disk Space When
Exporting the Database
When there was not enough space on the disk to export the
database, no error message was generated.
This problem is fixed. An error message is now generated.
3.3.1.4 Under Some Circumstances, Using Interactive SQL to Display
Dbkeys Caused a Bugcheck Dump
Displaying dbkeys in interactive SQL under some circumstances
would cause a bugcheck dump to occur.
This problem is fixed.
3.3.1.5 In a CREATE SCHEMA Statement, the Value Specified for the
NUMBER OF RECOVERY BUFFERS Clause Was Ignored
When a user specified the NUMBER OF RECOVERY BUFFERS clause in a
CREATE SCHEMA statement, the value was ignored.
This problem is fixed.
3.3.1.6 Altering a Storage Map with a Limit Clause Caused a Bugcheck
Dump to Occur
A bugcheck dump resulted when you altered a storage map with a
limit clause.
This problem is fixed.
3.3.1.7 There Was a Problem with the COMMENT ON Statement for a
Hashed Index
The COMMENT ON statement for a hashed index did not work.
This problem is fixed.
Software Errors Fixed 3-13
3.3.1.8 The SHOW STORAGE MAP Statement Incorrectly Displayed
Compression Characteristics for Tables
The SHOW STORAGE MAP statement incorrectly displayed the compres-
sion characteristics for a table.
This problem is fixed.
3.3.1.9 Using Interactive SQL Caused Inaccurate Values in Queries
and Produced Bugcheck Dumps Under Some Circumstances
Executing the following SQL statement produced bugchecks dumps
until the process was stopped:
SQL> SELECT COUNT(*) FROM INCOME WHERE ID2 LIKE "x";
0
1 row selected
This problem is fixed.
3.3.1.10 Defining a Schema with a DICTIONARY IS REQUIRED Clause
Resulted in Errors When Integrating into the Dictionary
While using SQL, if the schema was originally defined with the
DICTIONARY IS REQUIRED clause, use of the statement INTEGRATE
SCHEMA PATHNAME <pathname> ALTER FILES, resulted in the following
errors:
CDD-E-INTFAIL
RDB-E-NO_META_UPDATE
RDMS-F-CDDISREQD
This problem is fixed. You can create a schema (with the
DICTIONARY REQUIRED option) and then integrate it into the dictio-
nary without first altering the schema.
3-14 Software Errors Fixed
3.3.1.11 SQL$PRE Now Correctly Differentiates "-" and "_" Characters
In versions of SQL earlier than Version 3.1, the documentation
correctly noted that the "-" and "_" characters are not inter-
changeable in names created by SQL. However, the SQL$PRE/COBOL
preprocessor interpreted the "-" and "_" as being equivalent char-
acters in, for example, column names specified in embedded SQL
statements.
This problem is fixed. The Version 3.1 SQL$PRE preprocessor cor-
rectly returns an error when a minus sign (-) is substituted for
an underscore (_) in SQL names.
However, it is possible for some source programs that precompiled
without errors using earlier versions of the SQL$PRE preproces-
sor to return syntax errors when processed using this version of
SQL$PRE. In this case, you must edit the source program to cor-
rectly specify an underscore where required. If VAX ACMS Version
3.1 is installed on your system and you wish to build the sam-
ple ACMS application in the ACMS$SAMPLES directory, please note
that the VR_LOAD_REGIONS.SCO program requires editing to replace
hyphens with underscores in column names.
3.3.2 Programming
The following sections describe corrected software errors that
pertain to programming.
3.3.2.1 EXEC SQL INCLUDE FROM DICTIONARY Statement Returned an Error
Message from the COBOL FILLER Attribute
When SQL uses FILLER field attributes for RMS file records defini-
tion in the data dictionary, the EXEC SQL INCLUDE FROM DICTIONARY
statement returned an error message, "Attribute not found", and
aborted precompiler processing in the COBOL SQL precompiler. The
COBOL COPY FROM DICTIONARY statement worked fine. But SQL state-
ments that referred to the RMS file records returned errors at
precompile time.
Software Errors Fixed 3-15
This problem is fixed.
3.3.2.2 During a FORTRAN Precompilation, Code Was Ignored After an
END Statement
In a FORTRAN program, it was always true that only one source
module would precompile and any code after a FORTRAN END statement
was just ignored, but there was no message to that effect.
This problem is fixed. The precompiler now terminates with a
diagnostic message indicating that something important may have
been ignored.
3.3.2.3 /NOG_FLOATING Qualifier Did Not Function Properly with SQL
Dynamic C
When using DYNAMIC SQL with the C language under VMS, the /NOG_
FLOATING qualifier did not function properly. You could insert
data into the database, and D_FLOATING data was converted prop-
erly. However, when you tried to generate G_FLOATING data, it was
not properly translated to D_FLOATING data.
This problem is fixed.
3.3.2.4 Use of the Word DBKEY as a Column Name in a SELECT
Expression Caused SQL to Produce an Access Violation
The following syntax caused SQL to display ACCVIO error message:
SELECT COUNT (DISTINCT DBKEY) FROM EMPLOYEES;
This problem is fixed.
3-16 Software Errors Fixed
3.3.2.5 SQL Precompiler Did Not Support Prototype Definitions for C
The SQL precompiler did not support prototype definitions for C in
the previous version of SQL.
This problem is fixed. In the current version of SQL, the precom-
piler supports prototype definitions for C. For example:
foo(char *x)
{
EXEC SQL INSERT INTO ABC VALUEs (:x);
}
3.3.2.6 Using SQLPRE/FORTRAN with the /NOG_FLOAT Qualifier Caused a
Bugcheck Dump
In SQL precompiled programs in FORTRAN, if you used the /NOG_FLOAT
qualifier when you invoked the precompiler, a bugcheck dump would
result.
This problem is fixed.
3.3.2.7 SQL$GET_ERROR_TEXT Passed Back the Error Text and a Signed
Longword When It Should Have Passed Back a Signed Word
SQL$GET_ERROR_TEXT passed back the error text and a signed long-
word that indicated how long the error text was when it should
have passed back a signed word.
This problem is fixed. SQL$GET_ERROR_TEXT now passes back a signed
word.
3.3.2.8 FORTRAN Precompiler Split CDD/Plus Path Names Across Lines
Incorrectly
The FORTRAN precompiler was splitting CDD/Plus path names across
lines incorrectly.
This problem is fixed.
Software Errors Fixed 3-17
3.3.2.9 When More Than Six Schemas Were Declared in a Program, a
Macro Branching Error Occurred
Declaring more than six schemas in a program caused a macro
branching error to occur.
This problem is fixed.
3.3.2.10 There Was a Problem in the Module Language When the First
Reference to Any Database Object Was a Domain
The module language resulted in bugcheck dumps when the first
reference to any database object, that is a table, domain, and so
forth, was a domain.
This problem is fixed.
3.3.2.11 Ada Did Not Handle Lines More Than 120 Characters in Length
Lines of more than 120 characters in length caused a problem in
Ada.
This problem is fixed.
3.3.2.12 Precompiler Sometimes Generated Bad Ada Package Names
When the precompiler generated package names from a file name, it
sometimes generated bad Ada package names.
This problem is fixed.
3.3.2.13 Ada Programs That Had No SQL Statements in Them Caused the
Precompiler to Have Problems
The precompiler caused problems if an Ada program had no SQL
statements in it.
This problem is fixed.
3-18 Software Errors Fixed
3.3.2.14 Ada Precompiler Did Not Support the Use of Host Language
Variables Declared Using CONSTANT Variables
The SQL Ada precompiler did not correctly handle an error of
host language variable declared using the CONSTANT variable. This
invalid host language variable declaration caused a run-time error
while not generating an error message at compile time.
In the following example, STMT is invalid because it is based on
ST, which contains a constant in its declaration:
MAX_STRLNG : constant integer := 30;
subtype ST is STRING(1..MAX_STRLNG);
subtype ET is STRING(1..255);
This problem is fixed. An invalid host language variable declara-
tion, such as the preceding example, now generates a compile-time
error message.
3.3.2.15 Precompiler Did Not Recognize the Ada Calendar Packages
The Ada calendar packages were not understood by the precompiler.
This problem is fixed.
3.3.2.16 SQL Module Language Misinterpreted the Scale Factor for
Parameters Defined from Domains
The SQL module language did not correctly interpret the scale
factor for parameters defined from domains.
This problem is fixed.
3.3.2.17 Query Using a SELECT DISTINCT Clause on a Table of More
Than 65 Columns Caused a Bugcheck Dump
SQL queries using the SELECT DISTINCT clause on a table larger
than 65 columns produced a bugcheck dump.
This problem is fixed.
Software Errors Fixed 3-19
3.3.2.18 SQL Precompiler and Module Language Did Not Return Success
Status
The SQL precompiler and module language DID Not Return a success
status for the following error:
%SQL-W-LANUNSDTP, (1) FORTRAN does not support the datatype for parameter
DBKEY
db_key quadword
1
This error caused a problem with Module Management System (MMS),
which checked the status and stopped if the status indicated the
statement was not successful.
This problem is fixed. The SQL precompiler and module language now
return a success status if the objects have been produced and can
be linked.
3.4 RDO, RDBPRE, and RDML
This section describes corrections that are of interest to users
of the RDO interface and the RDBPRE and RDML preprocessors.
3.4.1 Data Definition and Data Manipulation
The following sections describe corrected software errors that
| pertain to data definition and data manipulation.
|
| 3.4.2 There were Conversion Problems Between Stored Data and Newly
| Defined Metadata
|
| In previous releases of Rdb/VMS, when changing field attributes
| of a relation, Rdb/VMS changed the metadata within the system
| relations, but Rdb/VMS converted the actual data stored in the
| relation gradually, tuple by tuple, at the time of the next update
| operation. There were usually no problems associated with this
| approach.
3-20 Software Errors Fixed
| However, sometimes users made changes to metadata that could not
| be supported when they could not read their data due to conversion
| problems between stored data and newly defined metadata.
|
| Rdb/VMS users now have the option to validate all data definition
| language (DDL) changes by defining the logical RDMS$BIND_VALIDATE_
| CHANGE_FIELD as shown in the following example:
|
| DEFINE RDMS$BIND_VALIDATE_CHANGE_FIELD 1
|
| With this logical defined, CHANGE FIELD will always validate
| the actual data records and convert them to the new metadata
| definition.
3.4.2.1 Storage Maps Partitioned by DATE Problem
There was a problem that occurred with storage maps partitioned
by date fields. The storage map definition required the use of
the DSRI date format of YYYYMMDD... If you had put it in other
formats, such as DD-MMM-YYYY, the value in the stored relation
was converted to DSRI format, and then compared to the DD-MMM-
YYYY format, which produced undesirable results and incorrect
partitioning.
This problem is fixed. Dates in storage maps now accept the full
international format for date and time.
3.4.2.2 Defining SEGMENTED STRING Global Fields Within a DEFINE
RELATION Statement Sometimes Produced Incorrect Results
When a field was defined within a DEFINE RELATION statement,
Rdb/VMS attempted to base the field upon an existing GLOBAL field
if one existed with the same name and attributes. In the case of
SEGMENTED STRING fields, it was possible for RDO to incorrectly
base fields upon a GLOBAL field, with a mismatching SEGMENTED_
LENGTH attribute.
This problem is fixed. A message is now returned when you attempt
to define a global field with the same name.
Software Errors Fixed 3-21
3.4.2.3 COMPUTED BY Fields Were Not Evaluated Correctly
There was a problem with COMPUTED BY fields in tables where the
COMPUTED BY value, when stored in another relation, turned out to
be 0 (or NULL for SQL). The problem was isolated to queries with
multiple contexts and COMPUTED BY fields from an outer context
used in an inner context; the COMPUTED BY field was not actually
evaluated.
This problem is fixed.
3.4.2.4 SHOW STORAGE MAP and SHOW INDEX Statements Did Not Show the
Complete STORE Clause of Any Storage Area Exported from V3.0
The RDO SHOW STORAGE MAP and SHOW INDEX statements did not show
the STORE WITHIN part of any storage area placed in a database
that had been exported from Rdb/VMS V3.0.
This problem is fixed.
3.4.2.5 START_TRANSACTION Statement Not Working with Extremely Long
Statements
The START_TRANSACTION statement would not work consistently with
extremely long statements.
At times, this problem would produce the following error:
%SYSTEM-F-ROPRAND, reserved operand fault at PC=0045158, PSL=00001
Improperly handled condition, image exited.
.
.
.
This problem is fixed.
3-22 Software Errors Fixed
3.4.3 Programming
The following sections describe corrected software errors that
| pertain to programming.
|
| 3.4.3.1 RDBPRE Precompiler Generated an Incorrect DSRI Parameter
| Block
|
| In V3.0 of Rdb/VMS the RDBPRE precompiler generated an incorrect
| DSRI Database Parameter Block (DPB). Although the incorrect DPB
| did not cause problems in the Rdb/VMS environment, it sometimes
| caused problems when running these programs against other DSRI
| platforms such as VIDA.
|
| This problem is fixed.
3.4.3.2 Using RDBPRE INVOKE with RUNTIME FILENAME and Dbkey Clauses
Produces a Fatal Error
In RDBPRE programs in the INVOKE statement, if you had a RUNTIME
FILENAME clause with a DBKEY SCOPE IS clause, you received a fatal
error in the compilation of your program:
%RDO-F-BUGCHK, there has been a fatal error; please submit an SPR; no
dump was produced
This problem is fixed.
3.4.3.3 RDBPRE Did Not Handle Boolean Expression Errors in GET
Statements
RDBPRE did not handle Boolean expression errors in GET statements
and produced a bugcheck dump.
This problem is fixed. RDBPRE produces an error when it encounters
Boolean expressions (they are not allowed within GET statements).
Software Errors Fixed 3-23
3.4.3.4 FETCH Statement Did Not Get Executed in RDBPRE
Under certain circumstances in RDBPRE, a FETCH statement did not
get executed. When the FETCH statement was taken out, the program
compiled correctly but failed with a run-time error:
RDB-F-REQ_SYNC, host program out of synchronization with specified request
This problem is fixed.
3.4.3.5 RDBPRE Returned a Syntax Error for a START_SEGMENTED_STRING
Statement Within a FOR Statement
There was a problem using a FOR loop surrounding a START_
SEGMENTED_STRING/END_SEGMENTED_STRING sequence in the RDBPRE
precompiler, which resulted in an RDO bugcheck dump.
This problem is fixed.
3.4.3.6 RDB$INTERPRET Produced Error Messages If Unsigned Longword
Data Types Were Used as Database or Transaction Handles
One of the following two error messages was returned if unsigned
longword data types were passed by descriptor to RDB$INTERPRET:
%RDO-F-TRAHDLINV, transaction handle datatype invalid, must be longword
%RDO-F-DB_HANLON, the DB handle must be a longword
This problem is fixed. RDB$INTERPRET will now accept database
handles or transaction handles that are either signed or unsigned
longword data types. It is now possible to call RDB$INTERPRET
from RDML applications passing declared handles using the RDML
predefined data type, RDML$HANDLE_TYPE.
3-24 Software Errors Fixed
3.4.3.7 RDML Checked Data Type with Segmented String FOR or STORE
Statement
RDML did not check that the field specified in a segmented string
FOR statement or STORE statement was actually of the type seg-
mented string.
This problem is fixed. If RDML encounters such a situation, it
issues the following message:
%RDML-E-NOTSSFIELD, <field-name> is not a segmented string field name
3.4.3.8 RDML Generated a Fatal Error Message When a Context Variable
Was Omitted
RDML generated a fatal error message when the context variable was
omitted, as shown in the following example:
FOR E IN EMPLOYEES SORTED BY LAST_NAME
Earlier versions of RDML generated the following message:
%RDML-F-NO_META_DATA, Cannot access compile-time metadata
in file 'unknown datatype in field generation'
-RDML-I-BLR_GENERATION, Error occurred during BLR generation
This problem is fixed. RDML now generates the following message:
%RDML-E-NOSUCHREL, Relation 'LAST_NAME' does not exist
%RDML-I-ATLINE, at line 31 in the file DISK4:[JONES]TEST.RC;
%RDML-I-NODMLOUTPUT, No output file generated due to errors
%RDML-I-SUMMARY, Completed with 1 Error, 0 warnings, and
1 informational message
Software Errors Fixed 3-25
3.4.3.9 RDML Generated Incorrect Code When an Access Control String
Was Used
RDML generated incorrect C code when an access control string,
such as the one shown in the following example, was used:
DATABASE FILENAME 'ABCDE\"BIRD PASSWORD\"::PERSONNEL.RDB';
Earlier versions of RDML did not generate correct C code for the
RDML$VC_INITIALIZE parameter that specifies the database. These
versions of RDML generated the following:
"ABCDE"BIRD PASSWORD"::PERSONNEL.RDB".
This problem is fixed. RDML generates the string as the user
supplies it.
3.5 Rdb/VMS Management Utility (RMU)
The following sections describe corrected software errors that
pertain to the Rdb/VMS Management Utility (RMU).
3.5.1 Using the RMU/BACKUP/AFTER_JOURNAL Command on an Empty AIJ
File Caused a Fatal Error to Occur
This problem is fixed. When RMU/BACKUP/AFTER_JOURNAL finds the AIJ
file to be empty it now displays an informational error message
instead of a fatal error.
3.5.2 Using the RMU/BACKUP/INCREMENTAL/ONLINE Command Did Not Back
Up Allocated, But Empty, Pages
With the RMU/BACKUP/INCR/ONLINE command, RMU did not backup allo-
cated, but empty, pages. As a result of this problem, the restored
database generated warnings when the RMU/VERIFY command was used.
This problem is fixed.
3-26 Software Errors Fixed
3.5.3 RMU/VERIFY Command Produced a Bugcheck Dump When a Storage
Area Was Deleted
Whenever a storage area that has held data for a relation was
deleted using the RDO CHANGE DATABASE statement, the RMU/VERIFY
command produced a bugcheck dump.
This problem is fixed.
3.5.4 RMU/VERIFY Command Did Not Do a Complete Integrity Check of
Indexes
The RMU/VERIFY/INDEXES command verified the internal consistency
of the indexes (sorted and hashed), but did only minimal verifica-
tion of the relationship between the index and the data records.
Only for unique entries in level one nodes of B-tree indexes were
the actual data records fetched. For entries in duplicate nodes,
and for hashed indexes, the data records were not fetched.
This problem is fixed. The RMU/VERIFY/INDEX command now performs
consistently by always retrieving the data records in addition
to verifying the index structures. This may mean more I/O op-
erations for index verification to fetch the data records and
therefore the index verification operation may require more time
to verify the database. See Section 2.26 for more information on
RMU/VERIFY/INDEX/NODATA.
3.5.5 When the RMU/ANALYZE/INDEX Command Was Run Interactively, It
Caused a Bugcheck Dump
If you used the RMU/ANALYZE/INDEX command on a multifile database
with hashed indexes, RMU could return an exception message as
shown here, when you attempted to run this command interactively:
***** Exception at 02DA8E : DIO$FETCH_DBKEY + 06E
RMU-F-BUGCHECK, fatal, unexpected error detected
This problem is fixed.
Software Errors Fixed 3-27
3.5.6 RMU/ANALYZE Command Was Not Counting Fragmented Records
Correctly
The RMU/ANALYZE command was counting each fragment as a separate
record.
This problem is fixed.
3.5.7 RMU/VERIFY Command Produced an End-of-File Error Following a
Restore Operation
You could get the following error message from using the
RMU/VERIFY command after restoring the database:
%RMU-F-FILACCERR, error reading disk file
-SYSTEM-W-ENDOFFILE, end of file
This problem occurred after an RMU/RESTORE operation with an
after-image journal (AIJ) file that was empty.
This particular example would produce this problem:
$RMU/BACKUP PERSONNEL
$DELETE PERSONNEL.RDB;*,PERSONNEL.SNP;*,PERSONNEL.AIJ;*
$RMU/RESTORE/NOCDD/LOG PERSONNEL
$RMU/VERIFY/ALL PERSONNEL
This problem is fixed.
3.5.8 RMU/RESTORE/USERS_MAX=nnn Command with Single-File Databases
Caused RMU to Fail on Certain Operations on the Restored
Database
Using the /USERS_MAX=nnn qualifier with the RMU/RESTORE command
with single-file databases could cause RMU to fail for certain
operations on the restored database, such as the RMU/BACKUP opera-
tion, the RMU/DUMP operation, and the RMU/VERIFY operation.
This problem is fixed.
3-28 Software Errors Fixed
3.5.9 RMU/RESTORE/CONFIRM Command Produced A Bugcheck Dump
A bugcheck occurred if the user entered the RMU/RESTORE/CONFIRM
command and responded N to the question: "Do you really want to do
an incremental restore?"
This problem is fixed. If the user answers N, the user exits from
RMU.
3.5.10 RMU/VERIFY/CONSTRAINTS Command Worked Incorrectly
The RMU/VERIFY/CONSTRAINTS and RMU/VERIFY/ALL commands did not
correctly verify constraints in some cases (usually where a table
had several constraints defined).
This problem is fixed.
3.5.11 RMU/BACKUP/ONLINE Command with Deleted Storage Area
The RMU/BACKUP/ONLINE command sometimes failed when the database
contained a storage area that had been deleted (that is, when
a storage area had been marked for deletion with the SQL DROP
STORAGE AREA statement or the RDO DELETE STORAGE_AREA statement).
This problem is fixed.
3.5.12 RMU/RESTORE Command Would Restore Deleted Storage Area Files
When a RMU/RESTORE command was issued for a backup file made from
a database in which a storage area was deleted, RMU would restore
deleted storage area and snapshot (RDA and SNP) files.
This problem is fixed and the RMU/RESTORE no longer restores these
deleted files.
Software Errors Fixed 3-29
Chapter 4
Problems, Restrictions, and Other Notes
This chapter describes problems and restrictions relating to
Rdb/VMS V3.1, and includes workarounds where appropriate. It
also contains other information not discussed in the preceding
chapters.
The chapter begins with information pertinent to all users. Later
sections contain material specifically for users of SQL, RDO,
and RDML. Therefore, the notes in this chapter may use different
database terms to mean the same thing. For example, some terms
used by SQL differ from terms used by other interfaces, such as
RDO or RDML. Table 3-1 lists the different terms used.
4.1 General Information
This section contains notes and problem descriptions of a general
nature.
Problems, Restrictions, and Other Notes 4-1
4.1.1 Object Modules Created with V3.1 Are Not Downward-Compatible
Due to enhancements in Rdb/VMS V3.1, object modules created by the
precompilers RDBPRE, SQL$PRE, and the SQL module language compiler
SQL$MOD are not downward-compatible with previous versions of
Rdb/VMS. Therefore, executable images created using these object
modules also will not function correctly if run on a system with a
previous version of Rdb/VMS installed.
These precompilers now generate extra parameters on all the DSRI
interface calls. Attempts to move and execute these modules will
result in errors similar to the following:
%RDB-E-WRONUMARG, wrong number of arguments on call to facility
Object modules created by the precompiler RDML are not affected.
However, executable images linked against the RDML object library
(SYS$SYSTEM:RDMLRTL.OLB) will not run on versions prior to Rdb/VMS
Version 3.1.
NOTE
Note that remote access through DECnet is not affected in
any way by these changes. This restriction only applies to
the movement of compiled and linked modules. Modules that
use the relational call interface (RCI) directly will not
be affected.
4.1.2 FIRST n Is Not Considered During Optimization
The strategies selected by the optimizer do not take into account
the potential reduction in the number of rows to be delivered that
might be stipulated by any FIRST n clause.
4-2 Problems, Restrictions, and Other Notes
4.1.3 Constraints Are Evaluated When Modifying a Column with the
Same Value
There is a problem that causes constraints to be unnecessar-
ily evaluated when modifying a column with the same value.
Modification of the employee record in the following example
causes the EMPLOYEE_ID_REQUIRED constraint to be unnecessarily
evaluated:
SHOW CONSTRAINT EMPLOYEE_ID_REQUIRED
EMPLOYEE_ID_REQUIRED FOR E IN EMPLOYEES
REQUIRE NOT E.EMPLOYEE_ID MISSING.
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = "00164"
MODIFY E USING E.EMPLOYEE_ID = "00164" END_MODIFY
END_FOR
4.1.4 Performance Considerations for Using VARYING STRING or
COLLATING SEQUENCE Attribute for Index Keys
Database designers should be aware of the following optimizer
restrictions concerning references to fields with the COLLATING
SEQUENCE attribute, or fields whose data type is VARCHAR (VARYING
STRING). These restrictions affect performance with respect to I/O
operations.
The optimizer Index Only Retrieval and Key-Only Boolean strategies
are disabled if any field in the index has a collating sequence
defined, or is a VARYING STRING field. These two retrieval strate-
gies require Rdb/VMS to return data stored in the index node, or
perform comparisons based on the index node key fields thus sav-
ing I/O operations to the data record. However, the original user
data can not be reconstructed from the encoded index if these at-
tributes are used. Therefore, the optimizer forces a Retrieval by
Index strategy instead which requires I/O operations to the data
record.
Problems, Restrictions, and Other Notes 4-3
These restrictions may affect the choice of data type for fields
to be used in indexes. For example, PRODUCT_ID which has a data
type of CHAR(20) is part of an index P_INDEX. A query which uses
STARTING WITH against PRODUCT_ID allows the user to enter a par-
tial product code. It then fetches the matched PRODUCT_ID field
for display to the user, but does not fetch any other fields. This
query would normally be optimized to reference the index PRODUCT_
ID_IX only (that is, using an Index Only Retrieval strategy).
However, if the field were defined as VARCHAR(20), the opti-
mizer would be required to reference the data record to fetch
the PRODUCT_ID. This will add some extra I/O operations to the
translation query. Therefore, CHAR (TEXT) data type may be prefer-
able to VARCHAR (VARYING STRING) if the field is involved in index
retrieval.
The following example demonstrates this simple case. Note that the
optimizer strategy as displayed when the RDMS$DEBUG_FLAGS logical
is set to "S" has been inserted after each query.
SQL> SHOW TABLE PRODUCTS
Columns for table PRODUCTS:
Column Name Data Type Domain
----------- --------- ------
PRODUCT_ID_V VARCHAR(20) PRODUCT_ID_V
PRODUCT_ID_T CHAR(20) PRODUCT_ID_T
.
.
.
Indexes on table PRODUCTS:
P_INDEX_T with column PRODUCT_ID_T
duplicates are allowed
type is sorted
P_INDEX_V with column PRODUCT_ID_V
duplicates are allowed
type is sorted
.
.
.
4-4 Problems, Restrictions, and Other Notes
SQL>
SQL> SELECT PRODUCT_ID_T
cont> FROM PRODUCTS
cont> WHERE PRODUCT_ID_T STARTING WITH "AAA";
Conjunct Get Index only retrieval
Retrieval by index of relation PRODUCTS Index name P_INDEX_T
00000001 Segments in low Ikey 00000001 Segments in high Ikey
0 rows selected
SQL>
SQL> SELECT PRODUCT_ID_V
cont> FROM PRODUCTS
cont> WHERE PRODUCT_ID_V STARTING WITH "AAA";
Conjunct Get Retrieval by index of relation PRODUCTS
Index name P_INDEX_V 00000001 Segments in low Ikey
00000001 Segments in high Ikey
0 rows selected
SQL>
SQL> COMMIT;
NOTE
Most queries use indexes as a fast access method to refer-
ence rows (records) of data so that an I/O operation to the
data record will normally be required.
4.1.5 Index Retrieval of Tables from Views with FIRST n Improperly
Uses Booleans from Outer Queries
There is a problem with index retrieval of tables from views with
FIRST n. Rdb/VMS improperly uses Boolean expressions from outer
queries.
In the following example, Rdb/VMS prints the Employee ID as
"00167".
Problems, Restrictions, and Other Notes 4-5
FOR FIRST 1 E IN EMPLOYEES SORTED BY E.EMPLOYEE_ID
PRINT E.EMPLOYEE_ID
END_FOR
DEFINE VIEW EV FOR FIRST 1 E IN EMPLOYEES SORTED BY E.EMPLOYEE_ID.
E.EMPLOYEE_ID.
END.
FOR X IN EV WITH X.EMPLOYEE_ID = "00167"
PRINT X.EMPLOYEE_ID
END_FOR
This situation does not occur when an index is not used.
4.1.6 Sorting or Any Implied Sorting for Projection on a Dbkey Is
Not Worthwhile
Sorting (or any implied sorting for projection) will not sort
dbkeys in such a way that the dbkeys can be used to retrieve
records in sequential order.
The reason for this behavior is that dbkeys are treated as fixed-
length text strings of 8 * n bytes, where n in the number of ta-
bles concerned (may be one or more for views). Therefore, sorting
dbkeys will order the text bytes according to the default ASCII
collating sequence.
4.1.7 Many Attaches to and Detaches from the Same or Multiple
Databases While Using Search Lists to Point to the Database
Uses Up I/O Channel Quota
Continually attaching to or detaching from the same database,
or to or from multiple databases, that are referred to by log-
ical names that contain search lists, will cause your process
to eventually exceed its channel quota, and a message such as
%SYSTEM-E-NOIOCHAN will be issued.
4-6 Problems, Restrictions, and Other Notes
This problem may occur frequently while using a RALLY application
and search lists because RALLY can attach to or detach from a
database quite frequently while the application is running. This
problem can occur elsewhere.
The problem is occurring because one of the logical names that is
used to point to the database is a search list. The logical name
is SPACE$DB, which is defined to be:
"SPACE$DB" [exec] = "$1$DUA12:" (LNM$SYSTEM_TABLE)
= "$1$DUA5:"
If you remove this search list, you do not have the problem.
4.1.8 Do Not Disable ASTs If You Want to Access a Database Remotely
An Rdb/VMS routine never completes if asynchronous traps (ASTs)
are disabled and Rdb/VMS is attempting to access a database across
DECnet.
The remote interface for Rdb/VMS requires the use of ASTs in
order to send messages asynchronously. The remote interface is
a client/server model. Each program issues an AST read on the
network channel that connects them. If a message is delivered by
DECnet, the AST ensures that the message is handled immediately.
If the message is a normal database message, a new AST is issued
and the message that was received is processed normally.
The server has the capability of serving multiple remote requests;
this would not be possible with synchronous communication.
4.1.9 Unexpected Setting of the NULL Attribute After an IMPORT
Operation
Section 2.2.2 of the Rdb/VMS V3.0A release notes suggests using
the RDO EXPORT WITH NOEXTENSIONS statement if you needed to ex-
port and have the import operation create a single-file database.
However, this type of export file does not include enough infor-
mation to exactly rebuild the database contents for the NULL flag
Problems, Restrictions, and Other Notes 4-7
(MISSING VALUE information) for each field (or the SQL equiva-
lent). This information is often required by applications and may
cause apparent database inconsistencies after an IMPORT operation.
See Section 4.3.5 for the effects of this problem on exporting
CDD/Plus dictionary databases.
This problem only occurs for fields that are defined without the
MISSING_VALUE attribute. This is often the case when the definer
assumes the use of the Rdb/VMS default missing value literals
(spaces for text fields, "17-NOV-1858 00:00:00.00" for dates and
zeros for numeric fields). The IMPORT statement compares the field
values with the defined missing value for the field and sets the
NULL flag upon finding a match. If the definer did not specify a
MISSING_VALUE string, then the field is not set to NULL.
This means that after an IMPORT operation, queries testing for
IS NULL (SQL), or IS MISSING (RDO), or using the DSRI PARAMETER2
feature may retrieve different records or detect fields as not
null that are different from what might be expected previous to
the IMPORT operation or when the database was first created.
The RDO EXPORT WITH EXTENSIONS statement can be used to work
around this problem. The extensions to the original export proto-
col now record the setting of the NULL flags for each field.
In general, Digital recommends the use of the EXPORT WITH
EXTENSIONS statement in all cases, unless the target system is
Rdb/ELN or a version of Rdb/VMS prior to Version 3.1.
4.1.10 IMPORT Statement Generates Bugcheck Dumps If Index Definition
Fails
In versions of the IMPORT statement prior to Rdb/VMS V3.1, in-
dexes were created in EXCLUSIVE WRITE transactions, even if the
batch-update option was used. This meant that large recovery-unit
journal (RUJ) files could be created on some import operations.
4-8 Problems, Restrictions, and Other Notes
In Rdb/VMS V3.1, indexes are now defined by default within batch-
update transactions to prevent the creation of these large RUJ
files. When a failure occurs during a batch-update transaction, it
cannot be rolled back, and this leaves the database in a corrupt
state. In this case, the current implementation of the IMPORT
statement generates a bugcheck dump rather than simply reporting
the error.
If this happens, Digital suggests the use of the IMPORT NOBATCH_
UPDATE option so that failures like this will be reported cor-
rectly, and only the offending index definition will be lost.
Digital is investigating ways to report this error, rather than
generating a bugcheck dump when BATCH_UPDATE is being used.
If a bugcheck dump is generated, the following DCL command can be
used to extract the reason for the failure:
$ SEARCH/WINDOW RDSBUGCHK.DMP "** Ex"
***** Exception at 0030E307 : RDMS$$KOD_CREATE_HASHED_INDEX + 00000286
%RDB-E-NO_DUP, index field value already exists; duplicates not allowed for
CUST_ACCESS_NUMBER_KEY
In many cases, the exception will indicate the reason for the
index failure. Some common reasons are:
o An index is defined as part of the IMPORT statement that re-
quires a UNIQUE (NO DUPLICATES ALLOWED) index when duplicates
in fact do exist (as in the previous example).
User action: The index definition should be changed to allow
duplicates.
o A hashed index is defined in the database, but the storage area
has had its characteristics changed during the import operation
from mixed page format to uniform page format.
User action: The import operation should not change the storage
area page format characteristic.
o During the record sort for the index build, the VMS Sort util-
ity exceeds the disk quota or fails to create a work file.
Problems, Restrictions, and Other Notes 4-9
User action: The sort work files will need to be placed on disk
with sufficient disk quota. For more information see the VMS
Sort/Merge Utility Manual.
4.1.11 RDO IMPORT Statement Will Not Accept an SQL EXPORT File with
ANSI-Style Protections Defined
When you export an SQL schema with ANSI-style protections defined,
the SQL EXPORT statement produces files that can not be imported
| to RDO.
|
| 4.1.12 IMPORT Statement Failed to Complete Index Definition With
| Users Bound to the Database
|
| There was problem caused by users binding to the database before
| an IMPORT statement was complete. In that event, the Rdb/VMS
| displayed the following messages:
|
| RDO-E-NOIDXREV, unable to import index indexname
| RDB-E-LOCKCONFLICT, request failed ..
| RDB-E-NOMETAUPDATE, ...
| RDMS-F-LCKCONFLCT, lock conflict on client.
|
| These messages appeared on two of the indexes (both sorted), and
| the IMPORT statment failed to create those indexes.
|
| The behavior of the IMPORT statement requires that no other users
| bind to the database while it is being imported, or alternately,
| that the indexes are defined in a separate operation.
4.1.13 Using LIB$DT_INPUT_FORMAT to Change Date Input Format
Sometimes Causes Access Violation
There is a problem with the VMS V5.2 Run-Time Library function
LIB$CONVERT_DATE_STRING. This is the routine Rdb/VMS uses in
precompiled programs, SQL module language, and the RDO and SQL
interactive interfaces to convert dates to internal format. When
the logical name LIB$DT_INPUT_FORMAT is used to change the date
4-10 Problems, Restrictions, and Other Notes
input format, the run-time library sometimes causes an access vi-
olation that probably prevents the precompiler or module language
compiler from continuing, but does not cause any loss of data from
interactive SQL. The access violation is shown in the following
example:
%SQL-F-DATCONERR, Data conversion error
-SYSTEM-F- ACCVIO, access violation, ...
The problem is in LIB$CONVERT_DATE_STRING, in the logic that
handles meridian indicators. The only workaround is to use a
different date format.
4.1.14 Operations on F-Floating Data Round to Whole Numbers
When Rdb/VMS performs any arithmetic addition, and either or both
of the operands is in F-floating format, the result is rounded to
the nearest whole number. For example, if the statistical function
TOTAL is used on a field (or the SQL equivalent) defined as F-
floating, and if data with decimal values is stored in that field,
the result is rounded to a whole number. If the F-floating field
contained the values 4.51 and 5.01, TOTAL would return the value
10, rather than 9.52 (the actual sum).
To avoid this rounding of floating-point results, use another data
type (such as quadword) for the fields rather than the F-floating
data type.
4.1.14.1 Rdb/VMS Interaction with Data Distributor V2.1
Under certain circumstances, Rdb/VMS generates bugcheck dumps
when interacting with VAX Data Distributor V2.1. Typically, this
situation occurs when you execute an RDO STORE, MODIFY, or ERASE
statement (or their SQL equivalents), and have many Boolean ex-
pressions on transfers for one relation. The exception for the
bugcheck is usually RDMS$$EXECUTE_ECON + ??. The problem is that
the amount of code generated to test the Boolean expressions for
the relation is greater than 32767 bytes. A BRW instruction is
Problems, Restrictions, and Other Notes 4-11
used to exit the Boolean comparison, and the BRW has a range of
-32768 to 32767.
The following workarounds are suggested:
o Use fewer transfers on the relation, or use fewer Boolean
expressions.
o Create a dummy transfer to transfer the entire relation to a
local database. This will cause Rdb/VMS to ignore the Boolean
comparison, as all records must be journaled.
4.1.15 Batch-Update Transactions Can Cause a Bugcheck Dump to Occur
If an Index Definition Fails
A batch-update transaction that attempts to define an index but
fails can cause a bugcheck dump to occur. An example is a batch-
update transaction that defines an index that specifies DUPLICATES
NOT ALLOWED when in fact there are duplicates. The transaction
fails, causing a bugcheck dump to occur, corrupting the database.
This situation can also occur within an IMPORT statement. You
must be certain that when attempting batch-update transactions in
such cases the definitions are correct for the set of data you are
using. See Section 4.1.10 for more information about the IMPORT
statement.
4.1.16 Rdb/VMS Logical Name, RDMS$BIND_WORK_VM, Has an Upper Limit
of 65,000 Bytes
The Rdb/VMS logical name, RDMS$BIND_WORK_VM, has an upper limit
of 65,000 bytes. Specifying values greater than 65,000 bytes can
produce unpredictable results.
4-12 Problems, Restrictions, and Other Notes
4.1.17 Reserving a Table in Exclusive Mode May Prevent Operations
from Being Performed on Other Tables in the Same Storage Area
There is presently a restriction in that reserving a table in the
exclusive mode may prevent you from performing some operations on
other tables in the same storage area.
The workaround is to reserve the tables in the shared mode, which
enables the snapshot mechanism.
4.1.18 There Is a Problem Defining COLLATING SEQUENCE IS NORWEGIAN
NORWEGIAN
There is a problem in the collating sequence file currently pro-
vided by the VMS kit for the Norwegian language.
The following example produces a bugcheck dump:
CREATE SCHEMA ... COLLATING SEQUENCE IS NORWEGIAN NORWEGIAN;
You can correct this problem on your system by doing the follow-
ing:
1.Use the command NCS/OUT=NORWEGIAN/EXT=NORWEGIAN to obtain a
file, NORWEGIAN.NCS. This file contains the following assign-
ments:
Problems, Restrictions, and Other Notes 4-13
..............................................................
NORWEGIAN = CS(
SEQUENCE = (%X00-"N", "Ñ", "O"-"Z", "Æ", "Ö", "Å", "["-"`", "{"-"¿", %XD0,
%XDE, %XF0, %XFE-%XFF),
MODIFICATIONS=("a"-"z" = "A"-"Z", "À"-"Ã" = "A", "Ç" = "C", "È"-"Ë" = "E",
"Ì"-"Ï" = "I", "Ò"-"Õ" = "O", "Ø" = "Ö", "Ù"-"Û" = "U", "Ü"-"Ý" = "Y"
"à"-"ã" = "A", "Å"-"æ" = "Å"-"Æ", "ç" = "C", "è"-"ë" = "E",
"ì"-"ï" = "I", "ñ" = "Ñ", "ò"-"õ" = "O", "ö" = "Ö", "ø" = "Ö",
"ù"-"û" = "U", "ü"-"ý" = "Y", "Ä" = "AE", "×" = "OE", "ß" = "SS",
"ä" = "Ä", "÷" = "×", "" < %X00))
+ CS( SEQUENCE = (%X00-"A", "À"-"Ä", "B"-"C", "Ç", "D"-"E", "È"-"Ë",
"F"-"I", "Ì"-"Ï", "J"-"N", "Ñ", "×", "O", "Ò"-"Ö", "P"-"R", "ß",
"S"-"U", "Ù"-"Ü", "V"-"Y", "Ý", "Z", "Æ", "Ø", "Å", "["-"`", "{"-"¿",
%XD0, %XDE, %XF0, %XFE-%XFF),
MODIFICATIONS=("a"-"z" = "A"-"Z", "à"-"ï" = "À"-"Ï", "ñ"-"ý" = "Ñ"-"Ý"))
+ REVERSE(_NATIVE);
..............................................................
2.Correct the assignment: "Ä" = "AE" to "Ä" = "Æ".
3.Insert or replace the corrected definition in your NCS.NLB.
One way to insert the corrected definition is to create a file,
NORWEGIAN_CORRECTED.NCS, that contains a corrected sequence:
4-14 Problems, Restrictions, and Other Notes
..............................................................
NORWEGIAN_CORRECTED = CS(
SEQUENCE = (%X00-"N", "Ñ", "O"-"Z", "Æ", "Ö", "Å", "["-"`", "{"-"¿", %XD0,
%XDE, %XF0, %XFE-%XFF),
MODIFICATIONS=("a"-"z" = "A"-"Z", "À"-"Ã" = "A", "Ç" = "C", "È"-"Ë" = "E",
"Ì"-"Ï" = "I", "Ò"-"Õ" = "O", "Ø" = "Ö", "Ù"-"Û" = "U", "Ü"-"Ý" = "Y"
"à"-"ã" = "A", "Å"-"æ" = "Å"-"Æ", "ç" = "C", "è"-"ë" = "E",
"ì"-"ï" = "I", "ñ" = "Ñ", "ò"-"õ" = "O", "ö" = "Ö", "ø" = "Ö",
"ù"-"û" = "U", "ü"-"ý" = "Y", "Ä" = "Æ", "×" = "OE", "ß" = "SS",
"ä" = "Ä", "÷" = "×", "" < %X00))
+ CS( SEQUENCE = (%X00-"A", "À"-"Ä", "B"-"C", "Ç", "D"-"E", "È"-"Ë",
"F"-"I", "Ì"-"Ï", "J"-"N", "Ñ", "×", "O", "Ò"-"Ö", "P"-"R", "ß",
"S"-"U", "Ù"-"Ü", "V"-"Y", "Ý", "Z", "Æ", "Ø", "Å", "["-"`", "{"-"¿",
%XD0, %XDE, %XF0, %XFE-%XFF),
MODIFICATIONS=("a"-"z" = "A"-"Z", "à"-"ï" = "À"-"Ï", "ñ"-"ý" = "Ñ"-"Ý"))
+ REVERSE(_NATIVE);
..............................................................
4.Issue the VMS command NCS/INS NORWEGIAN_CORRECTED.
This command inserts the corrected sequence in your NCS.NLB.You
may then choose to replace the erroneous sequence in your
NCS.NLB with this corrected one.
4.1.19 Rdb/VMS and VMS Debugger Interaction
There are reports often of unexpected interaction between Rdb/VMS
and the VMS Debugger when application programmers are debugging
code. Programs running under the control of the debugger, usually
with a watch point enabled, will possibly receive one of the
following error messages:
%RDB-F-BAD_DB_HANDLE, invalid database handle
%RDB-F-BAD_REQ_HANDLE, invalid request handle
%RDB-F-BAD_TRANS_HANDLE, invalid transaction handle
%RDB-F-NOARGACC_WRITE, database facility cannot write to argument !UL
%RDB-F-NOARGACC_READ, database facility cannot read argument !UL
Problems, Restrictions, and Other Notes 4-15
The application runs as expected if the application is modified
slightly or when no trace or watch points are established in the
debugger.
To explain what is happening in Rdb/VMS, it helps to understand
how the VMS Debugger implements the SET WATCH command, and this
involves some understanding of the VMS operating system.
Pages in the working set allow access to four security levels:
o Kernel mode-the most privileged
o Executive mode-most Record Management System (RMS) code, the
Rdb/VMS executive, most System services
o Supervisor mode-mostly DCL
o User mode-most user written application code
When a watch point is established, the VMS Debugger changes the
protection of the page in P1 space so that the image running in
user mode will not be able to write to the specified address.
When the page is accessed by the application, an exception occurs
that is handled by the debugger's exception handler. The address
of the access violation is examined to see if it is being watched
and, if so, the debugger executes the required trace actions and
executes the failed instruction again.
Privileged code such as the Rdb/VMS executive and the VMS system
services perform operations on behalf of nonprivileged users run-
ning in user mode. For security reasons, code running in executive
and kernel mode must ensure that the memory locations passed from
the user application not only exists, but can also be accessed
from the user's current (usually nonprivileged) mode. The VMS in-
struction set provides two instructions to check access rights to
memory locations-PROBER (probe for read access) and PROBEW (probe
for write access).
4-16 Problems, Restrictions, and Other Notes
The Rdb/VMS executive uses these instructions to ensure that all
application memory locations can be accessed at the appropriate
security level (sometimes access must be at user mode). If the ac-
cess fails, Rdb/VMS will signal an error. This checking completely
bypasses the exception and trapping required by the debugger.
Therefore, the application's behavior is different because of the
changed page protections.
Usually the programmer is not actually watching any Rdb/VMS vari-
ables, such as transaction and request handles, so why does
Rdb/VMS signal an error? The protection is at the page level,
which is rather coarse, and the location being traced happens to
fall on the same page as the Rdb/VMS variables.
It is possible for a knowledgeable programmer to avoid this prob-
lem by defining a large array so that the data locations are
pushed onto separate pages. This is the reason the error mes-
sage often disappears after editing and recompiling the code or
simply recompiling with a /NOOPTIMIZE qualifier.
For more information, see the sections on Watchpoint Options, and
How the Debugger Controls Program Execution in the VMS Debugger
Manual.
4.1.20 RDB$DBKEY_LENGTH System Field Incorrect for Certain Views
Rdb/VMS may assign incorrect values to the RDB$DBKEY_LENGTH field
of the RDB$RELATIONS system relation for views defined using func-
tions in RDO or SQL, or using the SQL GROUP BY or UNION clauses.
4.1.21 Views with GROUP BY Cannot Be Retrieved by Dbkey
An attempt to retrieve rows from a view containing a GROUP BY
clause will now return the diagnostic:
VIEWNORET, view cannot be retrieved by database key
Problems, Restrictions, and Other Notes 4-17
This situation exists because the GROUP BY clause produces a new
table of data grouped together from the various input streams
and any associated functions. Rdb/VMS then returns rows from
this table (AGGREGATE stream) as stream values, for which there
are no dbkeys because the rows are not derived directly from the
| database.
|
|
| 4.1.22 Problem with the Use of Virtual Memory
|
| There was a problem in the way virtual memory was allocated and
| freed within Rdb/VMS. This problem showed up in certain appli-
| cations that repeatedly recompiled identical information. These
| applications would eventually fail because they had exhausted
| their virtual memory quotas (VIRTUALPAGCNT).
|
| A workaround to the problem, on the system experiencing the prob-
| lem or in the login.com of the users experiencing the difficulty,
| is to define the following logical name:
|
| DEFINE RDMS$BIND_VM_SEGMENT 1
|
| This definition will cause Rdb/VMS to execute a different code
| path in its handling of virtual memory.
4.2 Notes and Restrictions Related to DSRI
This section contains notes and problems related to the DIGITAL
Standard Relational Interface (DSRI).
4.2.1 RCI Instantiation Number Must Be Zero for Remote Access
DSRI allows multiple instances of a request to execute against
different databases. Rdb/VMS supports only a single instance for
each database attach. However, the Relational Call Interface (RCI)
does allow the specification of INSTANTIATION for each request.
In the current version of Rdb/VMS, this instantiation number must
have the value 0 for remote access to succeed. This problem exists
4-18 Problems, Restrictions, and Other Notes
in the remote access server, so local database access is not
affected.
The Rdb/VMS precompilers always generate a value of 0 for the
instantiation number, so this restriction will be observed only by
Rdb/VMS users who use the RCI directly from application programs.
4.2.2 Having Context Variables That Are Not Unique Within a Request
Causes Invalid BLR
A program that contained context variables that are not unique
and ran in a Rdb/VMS V3.0 system, now generates an invalid binary
length record (BLR) error in Rdb/VMS V3.1.
Due to enhancements being made to Rdb/VMS V3.1, the DSRI rule that
context variables be unique within a request must be enforced.
This restriction requires applications that generate BLR to gen-
erate a unique context variable for each BLR$K_RELATION, BLR$K_
RELATION_ID, BLR$K_FETCH, BLR$K_ERASE, BLR$K_STORE, BLR$K_STORE2,
or BLR$K_MODIFY used in a single request. These context variables
are represented as unsigned byte data types, therefore a request
might have as many as 256 context variables.
An INVALID BLR exception is now generated under these cir-
cumstances. The exception status will be returned from the
RDB$COMPILE_REQUEST call and the message vector will contain
the offset in the BLR string of the duplicate context variable.
For example, the message vector, when formatted with SYS$PUTMSG,
appears as:
%RDB-E-INVALID_BLR, request BLR is incorrect at offset 301
NOTE
This restriction does not apply to code (BLR) generation
performed by Digital language processors, RDBPRE, RDML,
SQL$PRE, and SQL$MOD, which always generate unique context
variables when compiling requests.
Problems, Restrictions, and Other Notes 4-19
4.3 Notes and Restrictions Related to CDD/Plus
This section describes problems and restrictions relating to the
use of Rdb/VMS with CDD/Plus.
4.3.1 Incompatibilities Between Rdb/VMS V3.1 and CDD/Plus
The following data definition features for Rdb/VMS V3.1 cannot be
used with some or all versions of VAX CDD/Plus.
This means that, with the versions of CDD/Plus noted, you can-
not use the features in conjunction with the dictionary (after
declaring a schema with the PATHNAME option); you must specify the
FILENAME option with the DECLARE SCHEMA (SQL) or INVOKE DATABASE
(RDO) statements.
Incompatibilities with VAX CDD/Plus V4.1 will be corrected in a
future release of VAX CDD/Plus.
- Referential integrity
VAX CDD/Plus, V4.0 and earlier, does not accept or store defi-
nitions of triggers and relation-level constraints.
- Descending and ascending indexes
VAX CDD/Plus, V4.1 and earlier, does not accept or store index
definitions created with the ASCENDING or DESCENDING keywords
in SQL or RDO. Specifying the ASCENDING or DESCENDING ordering
clause causes the definition to fail.
Index definitions that omit the ordering clause, and thus use
the default of ASCENDING, will work.
- Collating sequences
VAX CDD/Plus, V4.1 and earlier, does not accept or store defi-
nitions of collating sequences.
4-20 Problems, Restrictions, and Other Notes
- Views containing UNION
VAX CDD/Plus, V4.1 and earlier, does not accept or store views
defined with the SQL UNION operator.
- VIEW constraints
VAX CDD/Plus, V4.1 and earlier, does not accept or store views
defined with the SQL WITH CHECK OPTION clause.
4.3.2 CDD/Plus V4.0 Interprets the Data Type Incorrectly When a
COMPUTED BY Field Is Included in an Rdb/VMS Relation
If you include a COMPUTED BY field in an Rdb/VMS relation,
CDD/Plus V4.0 will interpret the data type incorrectly. Specifically,
Rdb/VMS will define a data type of quadword or G-floating and
CDD/Plus V4.0 will define a data type of word or F-floating for
the same value.
This problem has been fixed in CDD/Plus V4.1, but is not retroac-
tive.
A new database that contains a relation with a COMPUTED BY field
will look the same in CDD/Plus as in Rdb/VMS. The COMPUTED BY
field's data types will now be interpreted correctly. The same
will hold true for any new relations with COMPUTED BY fields
where the new relation is defined in existing databases. However,
existing relations in databases that have already been linked
to CDD/Plus V4.0 will need to go through a few steps in order to
delete these pointers before the correction will work.
For instance, if you have seen this COMPUTED BY field problem
before, perhaps after you have integrated a database, upgrading to
CDD/Plus V4.1 and integrating again will not fix the data type.
Defining a new dictionary anchor and integrating the old database
into the new dictionary will not correct the data type either.
After upgrading CDD/Plus to V4.1, new Rdb/VMS relations containing
COMPUTED BY fields will be correct even if the database has been
previously integrated into CDD/Plus V4.0.
Problems, Restrictions, and Other Notes 4-21
To verify that a COMPUTED BY field has the wrong data type, use
the CDO command CDO SHOW RECORD/FULL <record name> FROM DATABASE
<database name>.
If CDD/Plus V4.1 is the first version of CDD/Plus you have in-
stalled on your system, or if you have never tried to link your
Rdb/VMS definitions to the data dictionary, these steps are not
necessary. There should be no problem with COMPUTED BY fields.
Also, if your applications do not use COMPUTED BY fields, they
will not be affected by these problems.
To fix COMPUTED BY fields that have the wrong data type, you must
do the following:
1.Digital recommends that you perform a full backup operation of
the database directory and the data dictionary before proceed-
ing.
2.This step is only necessary for records containing COMPUTED BY
fields that fall into one of the following two categories:
a.Rdb/VMS defined relations that have been entered into the
dictionary with the CDO ENTER command and contain COMPUTED
BY fields
b.CDO defined records that have been entered into the database
with the RDO DEFINE FIELD/RECORD field name or record name
FROM PATH path name statement
Each of these elements must be removed one at a time with the
CDO REMOVE command
3.IN CDO, delete the CDD Dictionary database file.
DELETE GENERIC CDD$DATABASE <database name>
4.IN RDO, integrate.
INTEGRATE DATABASE <file name> IN PATHNAME <path name>
4-22 Problems, Restrictions, and Other Notes
5.Optionally, enter the records and fields again that were previ-
ously removed using the CDO ENTER command.
4.3.3 CDD/Plus COMPUTED BY Fields Are Not Currently Supported in
Rdb/VMS Relations or Views
If you define a COMPUTED BY field in CDO and use it in a record,
that record will not be usable in Rdb/VMS. Incompatible data type
errors will occur if you try to use an RDO INTEGRATE statement
when the record containing the COMPUTED BY field will be used as
an Rdb/VMS relation.
RDO COMPUTED BY fields are computed in the context of a relation,
while CDO COMPUTED BY fields are not. The two concepts do not have
a semantic mapping. This is a restriction that did not get docu-
mented in CDD/Plus documentation. It is currently not considered
a problem but rather a possible enhancement to be considered for
some future release.
It is possible to use Rdb/VMS compatible COMPUTED BY fields in
record definitions stored in the data dictionary if they are
actually defined in RDO and then entered into the data dictionary.
The COMPUTED BY field may be defined in Rdb/VMS as part of a
relation using the INVOKE PATH option. A record with a COMPUTED
BY field defined in RDO and entered in the data dictionary can be
included in other databases.
4.3.4 CDD/Plus Problem with the CHANGE RELATION DEFINE FIELD
Statement in Which Fields Added Are Stored in the Data
Dictionary in Reverse Order
There is a problem with CDD/Plus where fields added to a relation
go in reverse order in the data dictionary, but in the correct
order in the Rdb/VMS metadata. This can cause problems with users
who execute a GET statement specifying the asterisk with RDBPRE or
RDML. This was not a problem with Rdb/VMS V2.3 and CDD, but is a
known problem with CDD/Plus.
Problems, Restrictions, and Other Notes 4-23
The following example illustrates this problem:
DEFINE DATABASE 'ORDER' IN 'ORDER'.
INVOKE DATABASE PATHNAME 'ORDER'
START_TRANSACTION READ_WRITE
DEFINE FIELD TEXT_FIELD DATATYPE IS TEXT SIZE IS 5.
DEFINE FIELD DATE_FIELD DATATYPE IS DATE.
DEFINE RELATION RELATION1.
FIELD1 BASED ON TEXT_FIELD.
FIELD2 BASED ON DATE_FIELD.
END RELATION1 RELATION.
COMMIT
START_TRANSACTION READ_WRITE
CHANGE RELATION RELATION1.
DEFINE FIELD3 BASED ON TEXT_FIELD.
DEFINE FIELD4 BASED ON DATE_FIELD.
DEFINE FIELD5 BASED ON TEXT_FIELD.
DEFINE FIELD6 BASED ON DATE_FIELD.
DEFINE FIELD7 BASED ON TEXT_FIELD.
DEFINE FIELD8 BASED ON DATE_FIELD.
END RELATION1 RELATION.
COMMIT
START_TRANSACTION READ_ONLY
SHOW FIELDS FOR RELATION1
Fields for relation RELATION1
FIELD1 text size is 5
based on global field TEXT_FIELD
FIELD2 Date
based on global field DATE_FIELD
FIELD3 text size is 5
based on global field TEXT_FIELD
FIELD4 Date
based on global field DATE_FIELD
FIELD5 text size is 5
based on global field TEXT_FIELD
FIELD6 Date
based on global field DATE_FIELD
FIELD7 text size is 5
4-24 Problems, Restrictions, and Other Notes
based on global field TEXT_FIELD
FIELD8 Date
based on global field DATE_FIELD
ROLLBACK
EXIT
$DMU
EXTRACT/RECORD ORDER.RDB$RELATIONS.RELATION1 REL1.REC
EXIT
$TYPE REL1.REC
DEFINE RECORD RELATION1.
RELATION1 STRUCTURE.
FIELD1 DATATYPE IS TEXT
SIZE IS 5 CHARACTERS.
FIELD2 DATATYPE IS DATE.
FIELD8 DATATYPE IS DATE.
FIELD7 DATATYPE IS TEXT
SIZE IS 5 CHARACTERS.
FIELD6 DATATYPE IS DATE.
FIELD5 DATATYPE IS TEXT
SIZE IS 5 CHARACTERS.
FIELD4 DATATYPE IS DATE.
FIELD3 DATATYPE IS TEXT
SIZE IS 5 CHARACTERS.
END RELATION1 STRUCTURE.
END RELATION1 RECORD.
A possible workaround is to add the fields one at a time.
This problem is fixed by installing CDD/Plus V4.1 and using the
RDO INTEGRATE DATABASE statement again.
Problems, Restrictions, and Other Notes 4-25
4.3.5 EXPORT WITH NOEXTENSIONS Statement Can Corrupt the
CDD$DATABASE
Section 2.2.2 of the Rdb/VMS V3.0A release notes suggests using
the EXPORT WITH NOEXTENSIONS statement if you need to export and
have the import operation create a single-file database. However,
this type of export file does not include enough information to
exactly rebuild the database contents for the NULL flag (MISSING
VALUE information) for each field. This information is required
by CDD/Plus and causes dictionary corruptions during the import
operation. See Section 4.1.9 for more information about the IMPORT
statement operation.
The RDO EXPORT WITH EXTENSIONS statement can be used to work
around this problem. The extensions to the original export proto-
col include the actual setting of the NULL flags for each field.
4.3.6 "Attribute Not Found" Error After EXPORT/IMPORT of
CDD$DATABASE
It is possible to receive the "attribute not found" error mes-
sage after using RDO or SQL EXPORT and IMPORT statements of the
CDD/Plus dictionary database CDD$DATABASE.
For example, when compiling a VAX BASIC program, an error similar
to the following may be generated when processing the %INCLUDE
%FROM %CDD directives:
%BASIC-E-CDDACCERR, CDD access error
-CDD-E-ATTNOTFND, attribute not found
Attempting to use the DICTIONARY OPERATOR (CDO) command EXTRACT on
the records will generate the following error messages:
%CDO-E-ERREXTRACT, error during extract
-CDD-E-ERRGET, can't retrieve requested entities
-RDB-E-NO_RECORD, access to dbkey failed because dbkey
is no longer associated with a record
-RDMS-F-NODBK, 1:1030:15 does not point to a data record
4-26 Problems, Restrictions, and Other Notes
The problem is that in CDD/Plus, a copy of the fields of a record
are stored as a sub-object of the record. This allows CDD/Plus
to read the entire record without having to read the fields sepa-
rately, which has certain performance advantages.
Now such attributes as access control lists (ACLs) are normally
stored as segmented strings. A copy of the ACL attribute is not
taken when the sub-object is created. When the field within the
record is read as a sub-object, it contains a dbkey pointer that
cannot be resolved just by looking within the sub-object. Rdb/VMS
does not know about CDD/Plus sub-objects and the dbkey of the
ACL becomes out-of-date after an EXPORT/IMPORT operation on the
dictionary database.
More simply stated, the EXPORT/IMPORT operation causes data to end
up on different pages and so the sub-object has become corrupt.
This behavior will be corrected in a future version of CDD/Plus
after Version 4.1.
This corruption problem only occurs in a record definition where a
field is used and that same field is used by another field in the
record, and then, only when the based-on field is used first. For
example:
CDO> DEFINE FIELD B1
cont> DESCRIPTION 'this is the base field'
cont> DATATYPE TEXT 7.
CDO> DEFINE FIELD B2B1
cont> DESCRIPTION 'based on b1' BASED ON B1.
CDO> DEFINE RECORD BREC.
cont> B1.
cont> B2B1.
cont> END.
Problems, Restrictions, and Other Notes 4-27
4.4 SQL Problems, Restrictions, and Notes
The following sections describe problems, restrictions, and other
information of interest to users of the SQL interface.
4.4.1 Database Administration and Maintenance
This section describes problems and restrictions related to
database administration and maintenance.
4.4.1.1 Disable VAX SQL/Services V1.0 Startup Procedure
If VAX SQL/Services Version 1.0 was installed on your VMS system,
log in to a privileged account, edit the SYS$MANAGER:SYSTARTUP_
V5.COM command procedure, and delete or comment out the following
line:
$ @SYS$MANAGER:SQLSRV$STARTUP.COM
SQL/Services startup now occurs in SQL$STARTUP, which is called
from RMONSTART.COM.
4.4.2 Data Definition and Data Manipulation
This section describes problems and restrictions related to data
definition and data manipulation.
4.4.2.1 DDL Statements Cannot Refer to Objects Before Their Creation
CREATE SCHEMA and CREATE TABLE statements in programs must pre-
cede in the source file all other data definition language (DDL)
statements that refer to the schema or table, respectively.
4-28 Problems, Restrictions, and Other Notes
4.4.2.2 Deleting Metadata in Rdb/VMS
There is a problem deleting metadata items in Rdb/VMS because
there are dependencies among metadata items. For example, if a
table has an index on it, that index is used in a storage map. In
the following example T1 is a table in a mixed area that uses a
hashed index I1. If you try to drop this table T1, the index I1
will also be deleted and Rdb/VMS will give you an error message:
CREATE SCHEMA FILENAME FOO
CREATE STORAGE AREA A1
PAGED FORMAT IS MIXED
CREATE TABLE T1 ( A INTEGER, B INTEGER)
CREATE INDEX I1 ON T1 (A) STORE IN A1
CREATE STORAGE MAP M1 FOR T1 PLACEMENT VIA INDEX I1;
DROP TABLE T1;
%RDB-E-NO-META-UPDATE, metadata update failed
%RDMS-F-INDINMAP, index I1 is used in storage map definition
Whereas RDO says that you must first delete maps and indexes, then
the table; SQL says that you can just delete the table and SQL
will take care of deleting everything else for you. The problem
is that sometimes SQL tries to delete an index on which something
else is dependent.
SQL is currently reevaluating the strategy for deleting metadata
items that depend on each other. If users encounter the problem
shown in the preceding example, then they must delete the metadata
items by hand according to the rules described in the VAX Rdb/VMS
RDO and RMU Reference Manual.
4.4.2.3 SQL Schema Compilation Fails on the First Fatal Error
When compiling schemas, SQL fails on the first fatal error. That
means it is necessary to compile more than once to find multiple
fatal errors.
There is no way in the current version to avoid the inconvenience
of needing to do multiple compilations.
Problems, Restrictions, and Other Notes 4-29
4.4.2.4 COMMENT ON Statement Cannot Be Used in CREATE SCHEMA
Statement
The COMMENT ON statement cannot be used in a CREATE SCHEMA state-
ment.
4.4.2.5 Problem with DROP TABLE Statement with Constraints
The following SQL procedure shows a problem with the DROP TABLE
statement with constraints. If the constraint is not used, then
the statement succeeds and there is no error. If you issue a
COMMIT statement before the DROP TABLE statement, then all is
fine.
$SQL
CREATE DATABASE MIKE;
!
! THIS GENERATES AN ERROR MESSAGE
!
CREATE TABLE MIKE (FOO INTEGER NOT NULL);
INSERT INTO MIKE VALUES (1);
SELECT * FROM MIKE;
DROP TABLE MIKE;
ROLLBACK;
!
! THIS WORKS
!
CREATE TABLE MIKE (FOO INTEGER);
INSERT INTO MIKE VALUES (1);
SELECT * FROM MIKE;
DROP TABLE MIKE;
ROLLBACK;
EXIT
4-30 Problems, Restrictions, and Other Notes
4.4.3 Programming
This section describes problems and restrictions related to pro-
gramming database applications.
4.4.3.1 Dynamic Cursors Cannot Access Views Created with GROUP BY or
UNION Clause
Views that contain a GROUP BY or UNION clause in their definition
cannot be accessed using dynamic cursors. Note that in interac-
tive SQL, these views may be accessed with the SELECT statement;
in precompiled SQL or SQL module language, these views may be
accessed with singleton SELECT statements and with non-dynamic
cursors. The problem shows up only with dynamic cursors. If a user
attempts to access one of these views with a dynamic cursor, the
following error will be returned when the cursor is opened:
"RDMS-F-VIEWNORET,view cannot be retrieved by database key".
The workaround for this problem is to use non-dynamic cursors to
access the view. If a dynamic cursor must be used, the statement
should access the base tables that make up the view (with the
GROUP BY and UNION clauses, as appropriate) and not the view
itself.
4.4.3.2 When Using the BETWEEN Operator, the Lower Value Must Be
Specified First
With the current version of SQL, the BETWEEN operator returns the
same results whether you specify the higher or lower value first.
However, because future versions of SQL may evaluate the BETWEEN
operator differently to comply with the ANSI/ISO standard, Digital
recommends that you always specify the lower value first:
BETWEEN 10 AND 20
If you do not always specify the lower value first, it is unlikely
that your SQL application program will produce correct results in
a future version of Rdb/VMS.
Problems, Restrictions, and Other Notes 4-31
4.4.3.3 Cannot Use INCLUDE Statement in Variable Declaration
The SQL$PRE precompiler will not process an INCLUDE statement in
the middle of a variable declaration. The following segment from a
COBOL program illustrates an INCLUDE statement that does not get
processed:
01 dept_rec pic x(24).
01 commarea.
EXEC SQL INCLUDE "A.DAT" END-EXEC.
4.4.3.4 SQL Ada Precompiler Does Not Support Overloading of
Subprograms Correctly
The SQL Ada precompiler does not support overloading (overlaying)
of subprograms correctly. It treats all of the overloaded programs
as a single name space.
There are three workarounds to this problem in the current version
of the SQL interface to Rdb/VMS:
1.The best workaround is to use the module language interface
instead of the precompiler. The module language is an interface
for defining procedures to execute SQL statements. You then
import these procedures into Ada and use them as you would use
any other external subprogram. Because SQL no longer processes
the Ada program, using the module language removes all of the
compile-time restrictions imposed by the precompiler on what
Ada features you can use. The run-time performance and features
of the module language interface are identical to the run-time
performance and features of the precompiled interface.
2.The second workaround is to use the separate compile-time fea-
ture of Ada for all of your loaded subprograms. Using this
approach, all of your overloaded subprograms would be precom-
piled separately so the Ada precompiler would handle the name
spaces correctly. The disadvantage of this approach is that the
SQL statements in the subprogram would not be able to refer to
4-32 Problems, Restrictions, and Other Notes
types, variables, and so forth, that were declared in the main
program unit because SQL would not know anything about them.
3.The third workaround is to make sure that all of the names used
in SQL statements in the overloaded procedures are unique in
all of the overloaded procedures. If you want to limit yourself
to using ANSI standard features, the names of all host language
variables used in SQL statements must be unique in the entire
file.
4.4.3.5 SQL Precompiler Will Not Evaluate Expressions in Variable
Declarations or Understand Literals
The SQL$PRE precompiler for the C language gives the following
error message when an SQL statement refers to a host language
variable declared as a character array whose declaration includes
anything other than a straight numeric value:
%SQL-F-BAD_ARRAY, Host variable address contains an array syntax error
in its declaration.
For example, this error occurs when the declaration contains a
named constant or an expression:
#define NAME_LEN (20)
#define ADDRESS_LEN (31)
char name [NAME_LEN + 1] /* This cannot be used */
char address [ADDRESS_LEN] /* This cannot be used */
Note that this has always been a restriction.
There is a workaround that requires two actions:
1.Remove the expressions from the declarations and update the
#defines accordingly; also remove the parentheses from the
#defines:
Problems, Restrictions, and Other Notes 4-33
#define NAME_LEN 21
#define ADDRESS_LEN 31
char name [NAME_LEN]
char address [ADDRESS_LEN]
2.Run the C code through the C preprocessor before invoking the
SQL precompiler. This will force all named constants to be
translated before the precompiler tries to use them:
CC/PREPROCESS=filename.SCP filename.SC
SQL$PRE/CC filename.SCP
4.4.3.6 SQL Ada Precompiler Does Not Support the Use of Named
Literals or Ranges
The SQL Ada precompiler does not support the use of named literals
or ranges.
As a workaround, it is possible to avoid this restriction by
using the module language interface instead of the precompiled
interface.
4.4.3.7 Limiting Length of File Names
Limit the length of the file name of an Ada precompiler file
(SQLADA) to 27 characters. The Ada compiler limits file names
to 31 characters; however, the SQL precompiler adds the prefix
"SQL_" to the file name to create a package name.
4.4.3.8 Limiting Number of Characters Per Line
In precompiled FORTRAN programs, the SQL precompiler adheres to a
restriction of 72 characters per line. If a statement is longer
than 72 characters, enter a continuation character in column 6 and
continue the statement on the next line.
4-34 Problems, Restrictions, and Other Notes
4.4.3.9 Limiting Number of Continuation Lines Per Record
In precompiled FORTRAN programs, the SQL precompiler adheres to
a restriction of 98 or fewer continuation lines in a statement
if you use the /CONTINUATIONS qualifier. The default number of
continuation lines is 19.
If a program uses a record definition, the SQL precompiler unpacks
the record into individual elements and places each one on a sepa-
rate line. If the number of elements in the record is greater than
the maximum number of continuation lines, the FORTRAN compiler
generates an error.
If this happens, increase the number of continuation lines using
the /CONTINUATIONS qualifier in the FORTRAN command line. If the
record contains more elements than the maximum allowed by FORTRAN
(99 elements), you can edit the intermediate file (file type FOR)
to place more than one element on a line.
4.4.3.10 SQL Module Language Processor Fails on the First Fatal
Error
The SQL module processor fails on the first fatal error. That
means it is necessary to perform multiple compilations to find
multiple fatal errors.
There is no way in the current version to avoid the inconvenience
of needing to do multiple compilations.
4.5 RDO, RDBPRE, and RDML Problems, Restrictions, and Notes
The following sections describe problems, restrictions, and other
information of interest to users of RDO, RDBPRE, and RDML.
Problems, Restrictions, and Other Notes 4-35
4.5.1 Database Handle Problem on START_STREAM
Both the RDML and RDO reference manuals state that the default
database handle scope is GLOBAL. This is true except for the
following cases:
o RDBPRE
- The default database handle is declared GLOBAL by default.
INVOKE DATABASE FILENAME 'PERSONNEL'
- A named database handle is declared as LOCAL by default.
INVOKE DATABASE PERS = FILENAME 'PERSONNEL'
o RDML
- Both the default and named database handle is declared as
GLOBAL by default.
4.5.2 RDO CHANGE INDEX Restriction Is Now Signaled
In Versions 3.0A and 3.0B, Rdb/VMS allowed the user to attempt
to use the CHANGE INDEX statement on an index that was defined to
be placed in the default RDB$SYSTEM storage area of a multifile
database (that is, the DEFINE INDEX statement was used without a
STORE WITHIN clause). This statement should not have been allowed,
and in Rdb/VMS V3.1 is a restriction.
If a DEFINE INDEX statement is used to create an index in a multi-
file database, and the STORE WITHIN clause is not specified, any
attempt to use the CHANGE INDEX statement to change the storage
area in which the index is stored is not allowed:
4-36 Problems, Restrictions, and Other Notes
DEFINE INDEX I1 FOR R1.
F1.
END INDEX.
CHANGE INDEX I1 STORE WITHIN STORAGE_AREA1.
This will result in the following error:
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-CHGIDXNOADDMAP, INDEX may not be altered/changed to have map
In Rdb/VMS V3.1, an index without a map specification cannot be
changed to have a map.
4.5.3 Problem of Different Optimizations of the Same Query from
Different Environments
In some cases, the same query is optimized differently from dif-
ferent environments (RDO and RDBPRE/COBOL, for example). This
problem is due to the fact that queries are handled differently by
the interfaces, in this case, RDO and RDBPRE, and different BLRs
are generated.
RDBPRE is a batch environment as opposed to the interactive RDO
environment, and therefore, in some cases, can consolidate two
or more statements into one, and produces a more compact BLR than
RDO. For example, for the following query, RDBPRE will generate a
single BLR identifying three output fields:
START_STREAM S USING R IN REL WITH R.FLD1 < 123
FETCH S
GET
HOST_VAR1 = R.FLD1;
HOST_VAR2 = R.FLD2;
HOST_VAR3 = R.FLD3
END_GET
Problems, Restrictions, and Other Notes 4-37
An equivalent RDO query produces two BLRs, one for the START_
STREAM statement and one for the FETCH and PRINT statements:
START_STREAM S USING R IN REL WITH R.FLD1 < 123
FETCH S
PRINT R.FLD1, R.FLD2, R.FLD3
4.5.4 Restrictions on Using Missing Value Fields in Nested Queries
Within a single context, such as the context of a single request,
if an arithmetic expression contains the MISSING operator, the
resulting expression will evaluate to MISSING. In the following
example, A.FIELD_1 contains missing (unknown) values, and the
query correctly interprets the values in A.FIELD_1 as missing
(unknown), causing the expression A.FIELD_3 = VARIABLE + A.FIELD_1
to evaluate to MISSING:
RDO> FOR A IN RELATION_A
cont> MODIFY A USING
cont> A.FIELD_3 = VARIABLE + A.FIELD_1
cont> END_MODIFY
cont> END_FOR
However, in nested queries that use multiple database requests
such as the following example, if B.FIELD_2 contains missing (un-
known) values, the expression A.FIELD_3 = VARIABLE + B.FIELD_2
returns different results. The second query (which begins with
FOR A) retrieves a value, in this case the value defined as the
field's MISSING_VALUE, from B.FIELD_2 for its RSE. However, be-
cause of RDO language limitations, the second query cannot use
the fact that the field B.FIELD_2 has an unknown value and in-
stead uses the missing value defined for the field with the DEFINE
FIELD or CHANGE FIELD statement. Using this value for B.FIELD_2
instead of treating the value as unknown means that the A.FIELD_3
= VARIABLE + B.FIELD_2 expression does not evaluate to MISSING.
4-38 Problems, Restrictions, and Other Notes
RDO> FOR B IN RELATION_B
cont> FOR A IN RELATION_A WITH A.FIELD_1 = B.FIELD_1
cont> MODIFY A USING
cont> A.FIELD_3 = VARIABLE + B.FIELD_2
cont> END_MODIFY
cont> END_FOR
cont> END_FOR
The workaround is to use the SQL interface to Rdb/VMS. You can
use the SQL indicator variables to detect the NULL attribute of
the column (field) and therefore set the appropriate value for the
column.
4.5.5 STORE WITHIN and DISABLE/ENABLE COMPRESSION Clauses Cannot
Both Be Specified
The STORE WITHIN and the DISABLE/ENABLE COMPRESSION clauses can
not both be specified in the same CHANGE STORAGE MAP statement.
The restriction is shown in the following example:
INVOKE DATABASE FILENAME MF_PERSONNEL
!
! The following statement is not allowed.
!
CHANGE STORAGE MAP CANDIDATES_MAP
STORE WITHIN EMPIDS_MID DISABLE COMPRESSION
END.
!
! However, this is the workaround. Use two CHANGE STORAGE MAP
! statements.
!
CHANGE STORAGE MAP CANDIDATES_MAP STORE WITHIN EMPIDS_MID
END.
CHANGE STORAGE MAP CANDIDATES_MAP DISABLE COMPRESSION
END.
!
FINISH
Problems, Restrictions, and Other Notes 4-39
| 4.5.6 When Entering an ACL entry, Argument to the Position Clause
| Can Not Exceed 255
|
| When defining entries in an Access Control List, if you use an
| integer higher than 255 as an argument to the position clause,
| Rdb/VMS produces the following error message in RDO:
|
| %LIB-F_INTOVF, integer overflow error occurs defining ACLS in RDO
|
| The POSTION clause in the corresponding SQL statement accepts inte-
| gers higher than 255.
4.5.7 Variables Cannot Be Database Handles
The example program shown here illustrates a problem with RDML. It
is written in VAX C, and although the precompilation is clean, the
C compiler gives errors at the READY statement. This problem only
occurs when the READY statement contains a database handle that
is incorrectly specified as a variable rather than specified in a
DATABASE statement.
This program works if a database handle specified in one of the
database statements is used in the READY statement, whether the
READY statement is in a function or a main module.
4-40 Problems, Restrictions, and Other Notes
#include stdio
DATABASE first_db = FILENAME 'the_first';
DATABASE second_db = FILENAME 'the_second';
main()
{
one_ready(first_db);
one_ready(second_db);
printf("%d\n",first_db);
printf("%d\n",second_db);
START_TRANSACTION READ_WRITE;
COMMIT;
}
one_ready(the_handle)
unsigned long the_handle;
{
READY the_handle ON ERROR printf("an error\n"); END_ERROR;
return(the_handle);
}
The READY statement, as documented on page 6-99 of the RDML
Reference Manual (Rdb/VMS V3.0), states that the database han-
dle (or multiple database handles) used in the READY statement
must be specified in a DATABASE statement. Digital does not sup-
port user-specified database handles in RDML; database handles
are automatically declared and used in RDML as a result of their
specification in a DATABASE statement (which is really a decla-
ration). This program attempts to use a database handle that is
declared explicitly (as opposed to being specified in a DATABASE
statement), and RDML therefore does not recognize it as a database
handle. Because a READY statement by itself is valid, RDML simply
recognizes that the READY statement syntax has terminated at that
point, and so it fails to detect the ON ERROR clause later in the
same line. (It assumes that the rest of the line was host language
syntax.)
Problems, Restrictions, and Other Notes 4-41
To achieve the desired result of RDML recognizing your database
handle, associate a unique number with each database handle, and
use it to identify which database handle to use. The example shown
here is a possible approach:
#include <stdio.h>
DATABASE first_db = FILENAME 'PERSONNEL';
DATABASE second_db = FILENAME 'PERSONNEL';
main()
{
one_ready(1);
one_ready(2);
printf("%d\n", first_db);
printf("%d\n", second_db);
START_TRANSACTION READ_WRITE;
COMMIT;
}
one_ready(int which_handle)
{
switch (which_handle)
{
case 1:
READY first_db ON ERROR printf("an error\n"); END_ERROR;
break;
case 2:
READY second_db ON ERROR printf("an error\n"); END_ERROR;
break;
}
}
4-42 Problems, Restrictions, and Other Notes
4.5.8 RDML Run-Time Object Library No Longer Requires You to Link
Against VAXCRTL or VAXCRTLG Object Libraries or Shareable
Images
The RDMLRTL.OLB no longer requires you to link against either the
VAXCRTL or VAXCRTLG object libraries or shareable images. However,
if you are programming in RDML/C, then you must still link against
one of these because VAX C requires it.
4.5.9 RDML/EPascal Ignores /LINKAGE=PROGRAM_SECTION Qualifier
RDML/EPascal ignores the /LINKAGE=PROGRAM_SECTION qualifier.
RDML/EPascal only supports /LINKAGE=GLOBAL_VARIABLE mechanism for
linking multiple modules. RDML/EPascal issues a warning message in
this case.
4.5.10 RDML Does Not Support MCS Characters in Database Object Names
The Digital multinational character set (MCS) characters in
database object names are not supported by the RDML preprocessor
in Rdb/VMS V3.1.
Database object names that can be referred to from RDML include
relations, fields, and constraint names. Additional restrictions
that apply to all precompilers are listed in Section B.3.
4.5.11 RDML Does Not Detect Incorrect Context Variables in the WITH
Clause
RDML does not detect incorrect context variables in statements
such as:
FOR E1 IN EMPLOYEES WITH E.LAST_NAME = name
/* some statements */
END_FOR;
Problems, Restrictions, and Other Notes 4-43
In the preceding example, E.LAST_NAME is an incorrect field ref-
erence that should be E1.LAST_NAME, and name is a host language
variable. The incorrect statement will result in an error during
query (BLR) generation after the parsing phase is complete. The
error is:
%RDML-F-RDML_ABORT, Fatal Preprocessor Utility Error
Aborted because: unknown datatype in field generation
-RDML-I-BLR_GENERATION, Error occurred during BLR generation
This error is reported because RDML cannot determine the data type
of the expression E.LAST_NAME = name when generating BLR for the
query.
4.5.12 RDML/Pascal Does Not Understand Some Character String Value
Expressions
RDML/Pascal does not generate the correct length for a character
string value expression in the form:
FOR E IN EMPLOYEES WITH E.LAST_NAME = ('T' | host_variable)
... some statements ...
END_FOR;
This statement generates an error in from VAX Pascal, such as:
00470 0 0 RDB$PORT_FIELD_0 : VARYING[0] OF CHAR;
1
%PASCAL-E-MAXLENRNG, (1) Max-length must be in range 1..65535
%PASCAL-E-ENDDIAGS, Pascal completed with 1 diagnostic
To avoid this problem, construct the value needed before issuing
the query, using a method such as the following:
host_variable1 := 'T' + host_variable2;
FOR E IN EMPLOYEES WITH E.LAST_NAME = host_variable1
... some statements ...
END_FOR;
4-44 Problems, Restrictions, and Other Notes
This method is recommended for all RDML statements when possible
because it will generally improve the performance of the query.
4.5.13 RDML/Pascal Does Not Accept All Possible Valid Pascal Host
Language Variables
RDML/Pascal does not accept all possible valid Pascal host lan-
guage variables, and it issues the following error if one it does
not accept is encountered:
%RDML-W-HOST_VARIABLE, error detected in host variable syntax
The set of possible values is limited to the syntax described
in the RDML Reference Manual, with the restriction that the
expression allowed in an array index must be a simple name or
expression. The following illustrates an unacceptable statement.
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = emparray[empstruct.index]
... some statements...
END_FOR;
In the preceding example, empstruct.index is a reference to a
structure member. To avoid this problem, you would assign emp-
struct.index to an intermediate variable and use that variable in
the FOR statement WITH clause.
4.5.14 RDML TOTAL Function Only Uses Numeric Data Types
The RDML TOTAL function can be used only with numeric data types.
The value expression that follows the TOTAL function does not
accept host language variables.
Problems, Restrictions, and Other Notes 4-45
4.5.15 RDML Does Not Allow Nested Comments
The RDML C and Pascal preprocessors do not allow nested comments
and now identify such comments with an informational message.
RDML chose not to allow comments because they are not allowed by
the ANSI/ISO C standard or by VAX C (when you use the /STANDARD=
PORTABLE qualifier).
4.6 Rdb/VMS Management Utility (RMU)
The following sections contain problems, restrictions, and other
notes that pertain to the Rdb/VMS Management Utility (RMU).
4.6.1 Use of the /USERS_MAX and /NODES_MAX Qualifiers with the
RMU/RESTORE Command Requires That Both Qualifiers Must Be on
the First Line of DCL Input
When the /USERS_MAX and /NODES_MAX qualifiers are used with the
RMU/RESTORE command and both appear on the second or subsequent
lines in the DCL input command, both qualifiers have been shown to
fail for both single-file and multifile databases.
Both of these qualifiers must be on the first line of DCL input or
the qualifiers will not be applied.
Avoid using these qualifiers when using the RMU/RESTORE command to
restore a single-file database. Instead, use the NUMBER OF USERS
clause and the NUMBER OF VAXCLUSTER NODES clause with the SQL or
RDO IMPORT statement. For multifile databases, use the NUMBER OF
USERS clause and the NUMBER OF VAXCLUSTER NODES clause within the
SQL ALTER SCHEMA or the RDO CHANGE DATABASE statement to change
these values.
4-46 Problems, Restrictions, and Other Notes
4.6.2 A Snapshot File Name, File Type, or Version Number Cannot Be
Changed for Single-File Databases
If you change the file name, file type, or version of a snap-
shot file and then try to access the database through the RDO
interface, you get the following error messages:
RDO> data file testrdb
%RDB-F-SYS_REQUEST, error from system services request
-RDMS-F-FILACCERR, error opening storage area file
DUA0:[TEST.RDB]TESTRDB.SNP;1
-RMS-E-FNF, file not found
RDO>
The name of the snapshot file in a single-file database is always
derived from the root file name. Users must not change the file
name, file type, or version of the snapshot file for a single-file
database.
It is possible to use logical names to relocate the snapshot file
to another directory or device. The RMU/RESTORE/SNAPSHOT=(FILE= . ..
) command permits this relocation. Users must define the appropri-
ate logical name to relocate the snapshot file.
If you experience this restriction, simply rename the snapshot
file (using the VMS RENAME command ) to its correct file name,
file type, and version number.
4.6.3 There Is a 17-Character Limit for File Names When Backing Up
Databases to Tape
When you back up your database to magnetic tape, Digital recom-
mends that you supply a name for the backup file that is 17 or
fewer characters in length. File names longer than 17 characters
may be truncated. The VMS operating system supports four file-
header labels: HDR1, HDR2, HDR3, and HDR4. In HDR1 labels, the
file identifier field contains the first 17 characters of the file
name you supply. The remainder of the file name is written into
the HDR4 label, provided that this label is allowed. If no HDR4
Problems, Restrictions, and Other Notes 4-47
label is supported, a file name longer than 17 characters will be
truncated. See the information on file-header labels in the Guide
to VMS Files and Devices.
The following RMU commands are acceptable:
RMU/BACKUP/REWIND/LABEL=TAPE MF_PERSONNEL MUA0:WEDNESDAYS_BACKUP.
RMU/RESTORE/REWIND/LABEL=TAPE MUA0:WEDNESDAYS_BACKUP.
The terminating period (.) for the backup file name is not
counted as a character, and the default file type of RBF is as-
sumed. Therefore, VMS interprets the file name as WEDNESDAYS_
BACKUP, which is 17 characters in length.
The following RMU commands are not acceptable:
RMU/BACKUP/REWIND/LABEL=TAPE MF_PERSONNEL MUA0:WEDNESDAYS_BACKUP
RMU/RESTORE/REWIND/LABEL=TAPE MUA0:WEDNESDAYS_BACKUP
Because no terminating period (.) is supplied, VMS supplies a
period and a file type of RBF, and interprets the backup file name
as WEDNESDAYS_BACKUP.RBF, which is 20 characters in length
4.6.4 RMU/DUMP/BACKUP Command Specifying a Value of 1 or 2 for the
/ACTIVE_IO Qualifier Causes the AIJ Dump to Stall
The RMU/DUMP/BACKUP command with an /ACTIVE_IO qualifier of either
1 or 2 does not seem to work; the process stalls in an LEF state.
Values 3 through 5 work properly. The two commands that fail are
shown here:
$ RMU/DUMP/BACKUP/REWIND /ACTIVE_IO=1 TEST$TAPE:MF_PERSONNEL.RBF
$
$ RMU/DUMP/BACKUP/REWIND /ACTIVE_IO=2 TEST$TAPE:MF_PERSONNEL.RBF
The problem is not that the /ACTIVE_IO qualifier does not work,
but rather, that enough buffers are required to read in the entire
root file from the backup file before the AIJ dump operation
begins. If the /ACTIVE_IO qualifier specifies a value that is
too low, the root file cannot be buffered and the AIJ dump stalls.
4-48 Problems, Restrictions, and Other Notes
4.6.5 RMU/SHOW STATISTICS Command Does Not Record All Statistics in
the Binary File
The following restrictions were not previously documented
for the RMU/SHOW STATISTICS command. When using the RMU/SHOW
STATISTICS/OUTPUT command, the following information is not
recorded in the binary file, and therefore can not be replayed
using the /INPUT qualifier:
o The information contained in the Stall Messages screen
The information displayed on the Stall Messages screen is
highly dynamic, and is not recorded in the binary file.
o Individual storage area I/O statistics
Currently all the file I/O statistics are combined into a
single gross value for the database.
Digital recognizes the fact that individual file statistics are
considerably more useful for tuning in a multifile database.
The RMU/SHOW STATISTICS command will be enhanced in a future
release of Rdb/VMS to record I/O statistics for individual
storage areas.
4.6.6 RMU/CONVERT Command Restriction and Notes
Effective with Version 3.1, you can use the RMU/CONVERT command
only with Version 3.0 databases, not with databases created with a
version of Rdb/VMS prior to Version 3.0.
To convert databases created prior to Version 3.0, you must first
use the RDO EXPORT statement to back up the database. You must
then complete the conversion process by choosing one of the fol-
lowing options:
o If you have Version 3.0 of Rdb/VMS available (but not in-
stalled) at your site, install Version 3.0 and use the
RMU/CONVERT command to convert the database to Version 3.0.
Problems, Restrictions, and Other Notes 4-49
Then install Version 3.1 of Rdb/VMS and use the RMU/CONVERT
command again to convert the database to Version 3.1.
o If you do not have V3.0 of Rdb/VMS available at your site,
install V3.1 of Rdb/VMS and use the RDO IMPORT statement to
convert the database to V3.1.
Note the following regarding the converted database:
o Fragmentation is very likely to occur during the conversion
of a database using the RMU/CONVERT command, and is expected
behavior. This fragmentation is not harmful, but if may mean
extra I/O operations per relation per database attach. If you
wish, you can remedy the fragmentation by performing an RDO
EXPORT and IMPORT operation of the database.
o The database (RDB) file (or RDB$SYSTEM storage area) will be
larger after an RMU/CONVERT operation because of the extra
information stored in the database (such as an extra field
RDBVMS$COLLATION_NAME for every field). The growth size varies
with the complexity of the database definition.
4.6.7 Dumping the AIJ File Is Incompatible with Normal Usage
Dumping the AIJ file from one process and writing to the AIJ file
from another process are mutually exclusive actions within the
current Rdb/VMS architecture.
For example, if you try to modify a record in a database for which
after-image journaling is enabled while someone else is dumping
the AIJ file with the RMU/DUMP/AFTER command, then it is possible
for your transaction to cause a bugcheck dump when it attempts to
access the AIJ file:
***** Exception at 001F5796 : AIJ$OPEN + 000002C9
%RDMS-F-FILACCERR, error opening after-image journal file DUA0:[SMITH.RDB]M
-SYSTEM-W-ACCONFLICT, file access conflict"
4-50 Problems, Restrictions, and Other Notes
To prevent this error from occurring, ensure that there are no ac-
tive users updating the database before issuing an RMU/DUMP/AFTER
command, or make a copy of the AIJ file and dump that copy.
4.6.8 RMU/RESTORE Command May Initialize the SPAM Thresholds in One
or More Storage Areas
The RMU/RESTORE command may initialize the SPAM thresholds for
some data pages of some uniform areas to values that are not
acceptable to the RMU/VERIFY command. This occurs when some of the
data pages in a logical area are restored before the logical area
definition (Area Inventory). This is not a frequent occurrence,
and when it does happen, the consequences are usually cosmetic
(the RMU/VERIFY command issues a warning message for each page
affected). However, if very many pages are affected, the volume of
warnings may cause a real problem to be overlooked. Moreover, in
some cases, this may result in additional I/O operations when new
data is stored in an affected relation.
As a workaround, RMU now offers a REPAIR command that reconstructs
the SPAM pages in one or more storage areas. This corrects the
condition caused by the RMU/RESTORE command as well as other SPAM
page corruptions.
The RMU/REPAIR command has the following syntax:
RMU/REPAIR [/AREA=(area-name,...)] root_filename
The default for the /AREA qualifier is an asterisk parendchar(*),
which means all the storage areas.
The RMU/REPAIR command requires VMS SYSPRV privilege. It also
requires complete and exclusive access to the database.
4.7 Rdb/VMS Documentation Errors
This section describes errors or omissions in the Rdb/VMS manuals
and documents.
Problems, Restrictions, and Other Notes 4-51
4.7.1 Documentation for RDM$MONITOR Logical Name was Incorrect
Previous versions of Rdb/VMS documentation described the use of
the RDM$MONITOR logical name to control placement of the monitor
log file RDMMON.LOG.
Rdb/VMS does not support the RDM$MONITOR logical name.
4.7.2 Corrections to the VAX Rdb/VMS Multifile Databases Poster
The VAX Rdb/VMS Multifile Databases poster for Version 3.0 docu-
mentation shows the employee IDs as simple numbers in parentheses
with STORE USING clauses. For example, the definitions to create
the MF_PERSONNEL database include:
CREATE UNIQUE INDEX EMPLOYEES_HASH
! Hashed index for EMPLOYEES table
ON EMPLOYEES (EMPLOYEE_ID)
TYPE IS HASHED
STORE USING (EMPLOYEE_ID)
IN EMPIDS_LOW WITH LIMIT OF (200)
IN EMPIDS_MID WITH LIMIT OF (400)
OTHERWISE IN EMPIDS_OVER;
!
CREATE STORAGE MAP EMPLOYEES_MAP FOR EMPLOYEES
! Employees partitioned by 200 400 *
STORE USING (EMPLOYEE_ID)
IN EMPIDS_LOW WITH LIMIT OF (200)
IN EMPIDS_MID WITH LIMIT OF (400)
OTHERWISE IN EMPIDS_OVER
PLACEMENT VIA INDEX EMPLOYEES_HASH;
These statements do not generate any syntax or other errors;
however, they do not result in placement of records in the desired
storage area. In fact, all data and index structures get put into
the EMPIDS_LOW.RDA storage area and nothing is placed into the
EMPIDS_MID.RDA or EMPIDS_OVER.RDA storage areas. To obtain the
desired distribution of records, the employee ID must be placed in
double quotation marks as follows:
4-52 Problems, Restrictions, and Other Notes
CREATE UNIQUE INDEX EMPLOYEES_HASH
! Hashed index for EMPLOYEES table
ON EMPLOYEES (EMPLOYEE_ID)
TYPE IS HASHED
STORE USING (EMPLOYEE_ID)
IN EMPIDS_LOW WITH LIMIT OF ("00200")
IN EMPIDS_MID WITH LIMIT OF ("00400")
OTHERWISE IN EMPIDS_OVER;
!
CREATE STORAGE MAP EMPLOYEES_MAP FOR EMPLOYEES
! Employees partitioned by 200 400 *
STORE USING (EMPLOYEE_ID)
IN EMPIDS_LOW WITH LIMIT OF ("00200")
IN EMPIDS_MID WITH LIMIT OF ("00400")
OTHERWISE IN EMPIDS_OVER
PLACEMENT VIA INDEX EMPLOYEES_HASH;
The RDO definitions place the employee IDs in quotation marks
because the ID is a character (alphanumeric) field; SQL does not
currently do this.
4.7.3 Additional Information for /UNTIL Qualifier in RMU Help File
The RMU help file supplies incomplete information on the /UNTIL
qualifier for the following three RMU commands:
o RMU/BACKUP/AFTER_JOURNAL
o RMU/RECOVER
o RMU/SHOW STATISTICS
For these three commands, the RMU help file neglects to mention
that the /UNTIL qualifier accepts all VMS V5.0 date and time
strings, as well as international dates. The date and time strings
specified must be in quotation marks because they can contain
spaces and other DCL syntax characters such as commas. A colon
separator between the time and date is no longer valid.
Problems, Restrictions, and Other Notes 4-53
4.7.4 Correction to the Usage Note on Constraints with the CREATE
TABLE Statement
A usage note on page 4-145 of the VAX Rdb/VMS SQL Reference Manual
for Version 3.1 incorrectly states that "transactions that create
tables containing constraints must have EXCLUSIVE access to the
database."
In fact, Rdb/VMS only requires exclusive access to all the tables
referenced by the constraint, not to the entire database.
4.7.5 Using Rdb/VMS from a VMS Detached Process
Rdb/VMS V3.1 documentation omits necessary detail on running
Rdb/VMS from a detached process.
Applications run from detached processes must ensure that the
VMS environment is established correctly before running Rdb/VMS,
otherwise Rdb/VMS will not execute.
Attempts to attach to a database and execute an Rdb/VMS query from
applications running as detached processes will result in an error
similar to the following:
%RDB-F-SYS_REQUEST, error from system services request
-SORT-E-OPENOUT, error opening !AS as output
-RMS-F-DEV, error in device name or inappropriate device type for operation
The problem occurs because a detached process does not normally
have the logical names SYS$LOGIN, or SYS$SCRATCH defined.
There are two methods that can be used to correct this:
1.Solution 1:
$ RUN/DETACH/AUTHORIZE SYS$SYSTEM:LOGINOUT/INPUT=RUN-PROCEDURE
4-54 Problems, Restrictions, and Other Notes
The DCL command procedure RUN-PROCEDURE runs the ACCOUNTS
application:
$ RUN ACCOUNTS_REPORT
This solution executes SYS$SYSTEM:LOGINOUT so that the default
command language interface (CLI) is activated (this is usually
DCL). This causes the logical names SYS$LOGIN and SYS$SCRATCH
to be defined for the detached process. The /AUTHORIZE quali-
fier also ensures that the users' process quota limits (PQLs)
are used from the system authorization file rather than re-
lying on the default PQL system parameters, which are often
insufficient for Rdb/VMS.
2.Solution 2:
If DCL is not desired, and SYS$LOGIN and SYS$SCRATCH are not
defined, then prior to executing any Rdb/VMS statement, you
must define the following logical names:
- RDMS$RUJ
Rdb/VMS tries to locate the RUJ file using the process
logical SYS$LOGIN. You should define the logical RDMS$RUJ to
avoid a reference to this nonexistent logical name.
For more information on RDMS$RUJ, see the VAX Rdb/VMS Guide
to Database Maintenance and Performance.
- SORTWORK0, SORTWORK1
The VMS Sort utility attempts to create sort work files
in SYS$SCRATCH. If the SORTWORK logical names exist, the
utility will not require the SYS$SCRATCH logical. However,
note that not all queries will require sorting, and that
some sorts will be completed in memory and so will not
necessarily require disk space.
If you use the logical RDMS$BIND_SORT_WORKFILES, you will
need to define further SORTWORK logical names as de-
scribed in the VAX Rdb/VMS Guide to Database Maintenance
and Performance.
Problems, Restrictions, and Other Notes 4-55
You should also verify that sufficient process quotas are
specified on the RUN/DETACH command line, or defined as
system PQL parameters to allow Rdb/VMS to execute.
4-56 Problems, Restrictions, and Other Notes
Appendix A
SQL/Services V3.1 Release Notes
Online versions of the SQL/Services release notes are available in
the following locations:
__________________________________________________________________
System________________Location____________________________________
VAX/VMS SYS$HELP:SQLSRV$031.RELEASE_NOTES
MS-DOS SQS031.RN
ULTRIX,_RISC/ULTRIX___sqs031u.rn__________________________________
A.1 New and Changed Features
A.1.1 A New ULTRIX API for the DECstation Series of Processors
The SQL/Services component of Rdb/VMS Version 3.1 includes a
new Application Programming Interface (API), one for RISC ma-
chines running the ULTRIX operating system. The new RISC/ULTRIX
client API joins the currently supported MS-DOS, VMS, and ULTRIX
interfaces, bringing to four the number of APIs developed for
SQL/Services.
SQL/Services V3.1 Release Notes A-1
To install the RISC/ULTRIX API, follow the ULTRIX API installation
procedures described in the VAX Rdb/VMS Installation Guide; the
RISC/ULTRIX API and the ULTRIX API install in the same way. Refer
to the VAX Rdb/VMS Installation Guide for complete instructions.
A-2 SQL/Services V3.1 Release Notes
A.2 Problems, Restrictions, and Other Notes
A.2.1 Filter Expressions in SQL/Services
Filter expressions that contain two or more string or date scalar
functions return incorrect results in SQL/Services. This will be
fixed in a future release of Rdb/VMS.
A.2.2 RISC/ULTRIX API Requires Applications to Provide User Name
When using the RISC/ULTRIX client API to access the SQL/Services
server, you must provide a user name and password in your appli-
cation. The SQL/Services RISC/ULTRIX client API does not support
default user names and passwords, such as those used with proxy
accounts or those containing null characters.
SQL/Services V3.1 Release Notes A-3
Appendix B
Internationalization Support in V3.1
This appendix describes the implementation of internationalization
support in Rdb/VMS V3.1 in the following areas:
o Date and time support
o Collating sequence support
o Use of MCS characters in database object names
B.1 Date and Time Support
Date and time support in Rdb/VMS V3.1 has undergone considerable
change. These changes have been implemented in the language pro-
cessors and the interactive utilities so that Rdb/VMS becomes more
closely aligned with VMS Version 5.
This section describes these changes and highlights the side ef-
fects that will inevitably be encountered because of such radical
changes.
Please note that these changes only affect date string text liter-
als and their conversion to and from binary dates. Dates supplied
by host languages in 64-bit (8-byte) VMS date and time are treated
as they were in previous versions.
Internationalization Support in V3.1 B-1
When evaluating the effect of these changes, it should be kept
in mind that most applications do not use quoted date string
literals, but rather the standard 8-byte binary data. Therefore,
the date and time formatting is most useful in the interactive
environment.
B.1.1 Input Date Formatting
The interactive utilities RDO and SQL, along with the language
processors RDBPRE, SQL$PRE and SQL$MOD, have been modified to
convert date strings literals (that is, quoted text) using the new
VMS version 5 RTL routine LIB$CONVERT_DATE_STRING, as opposed to
the SYS$BINTIM system service as was used in previous versions of
Rdb/VMS.
The following new capabilities should be noted:
o If the user defines appropriate date and time strings for
the RTL conversion routines in their environment, it is now
possible to provide input dates in a format different from the
VMS traditional format, as shown in Example B-1.
B-2 Internationalization Support in V3.1
Example B-1: Input Date Formatting
__________________________________________________________________
$ DEFINE LIB$DT_INPUT_FORMAT "!MAU !DD, !Y4 !H02:!M0:!S0:!C2 !MIU"
$ RDO
RDO>invoke database filename PERSONNEL
RDO>for e in employees with e.birthday > "june 2, 1957"
cont> print e.birthday, e.employee_id
cont>end-for
BIRTHDAY EMPLOYEE_ID
3-JUN-1957 00:00:00.00 00170
4-JUL-1958 00:00:00.00 00195
12-FEB-1958 00:00:00.00 00213
15-MAY-1959 00:00:00.00 00230
20-DEC-1958 00:00:00.00 00242
9-MAY-1958 00:00:00.00 00247
11-JUL-1957 00:00:00.00 00276
10-JAN-1960 00:00:00.00 00345
__________________________________________________________________
These input formats are described in the VMS RTL Library (LIB$)
Manual Chapter 3, Date/Time Manipulation.
Either Interactive SQL or RDO could have been used in this
example.
NOTE
The input date format cannot be set from within RDO
or SQL. You must define the logical name LIB$DT_INPUT_
FORMAT at the DCL level.
o The default action of the VMS date and time formats should
remain the same. That is, on a standard VMS system without
alternate date and time formats the input string '24-FEB-1989
12:00' will be converted as before.
Internationalization Support in V3.1 B-3
Example B-2 shows the default format as defined by a systemwide
logical name.
Example B-2: Showing the Default Format
__________________________________________________________________
$ show logical lib$dt_input_format
"LIB$DT_INPUT_FORMAT" = "!DB-!MAAU-!Y4 !H04:!M0:!S0.!C2" (LNM$SYSTEM_TABLE)
__________________________________________________________________
If this logical is not defined, then the RTL defaults to the
traditional format.
o However, if the user has defined an alternate format, then
RDO and SQL procedures that worked before may now fail. For
example, assuming the definition of LIB$DT_INPUT_FORMAT as
shown previously, the query shown in Example B-3 fails.
Example B-3: Error Caused by Alternate Date Format
__________________________________________________________________
RDO> for e in employees with e.birthday > "2-JUN-1957"
cont> print e.birthday, e.employee_id
cont> end-for
%RDO-F-DATCONERR, data conversion error
-LIB-F-AMBDATTIM, ambiguous date-time
__________________________________________________________________
o The setting of the logical name SYS$LANGUAGE or the RDO or SQL
statement SET LANGUAGE will affect the translation of month
names, and other language-dependent text. For an SQL example,
see Example B-7.
NOTE
The SET LANGUAGE statement cannot be used from
RDB$INTERPRET, therefore the programmer must define
the SYS$LANGUAGE logical name themselves using either
the LIB$SET_LOGICAL or SYS$CRELNM routines.
B-4 Internationalization Support in V3.1
Example B-4 demonstrates that acceptable input is sensitive to
the language setting.
Example B-4: Effects of the SQL SET LANGUAGE Statement
__________________________________________________________________
SQL>set language Spanish
SQL>!
SQL>! Who started work yesterday
SQL>!
SQL>select first_name, last_name, employees.employee_id
cont>from job_history, employees
cont>where job_start = 'ayer'
cont> and job_history.employee_id = employees.employee_id;
0 rows selected
SQL>
__________________________________________________________________
o One side effect of this new date support is that the literals
TODAY, TOMORROW, and YESTERDAY (and other language equivalents)
are now accepted in queries processed by these tools.
For SQL (interactive and dynamic), RDO, and RDB$INTERPRET,
this has an advantage because the user need not specify the
exact date. Example B-5 shows a query that can be included in a
command procedure and executed correctly every day:
Internationalization Support in V3.1 B-5
Example B-5: Use of TOMORROW Date Literal in Interactive RDO
__________________________________________________________________
RDO> print count of o in orders with o.ship_date = 'Tomorrow'
__________________________________________________________________
The following example emphasizes the fact that these relative
date strings can be in any language the same query can be
executed for a Spanish environment.
RDO> set language Spanish
RDO> print count of o in orders with o.ship_date = 'mañana'
It is assumed that the string literals are appropriate for the
language used. The following example shows that if the language
is set to SPANISH and the literal "YESTERDAY" is used, then a
conversion error will result.
SQL>set language Spanish
SQL>!
SQL>! Who started work yesterday
SQL>!
SQL>select first_name, last_name, employees.employee_id
cont>from job_history, employees
cont>where job_start = 'yesterday'
cont> and job_history.employee_id = employees.employee_id;
%SQL-F-DATCONERR, Data conversion error
-LIB-F-AMBDATTIM, ambiguous date-time
The unexpected side effect is that the date strings are trans-
lated at compile time by RDBPRE, SQL$MOD, and SQL$PRE. So a
query in an application program that has the date literal "to-
morrow" has tomorrow's date saved in the query. That is, today
if the compile date is 24-FEB-1989 so that tomorrow's date is
25-FEB-1989, then the query will process against the 25-FEB-
1989 until the application is recompiled. This is usually not
what the user expects.
B-6 Internationalization Support in V3.1
The date strings are not parsed by the language processors
but are simply dispatched to the RTL date conversion routine
for translation. It would not be useful to do any parsing for
these special quoted strings unless it was done for all foreign
languages.
NOTE
Digital recommends that the YESTERDAY, TODAY, and
TOMORROW literals not be used in precompiled applica-
tions. These literals are not re-evaluated at run time,
their use in compiled applications is not supported by
Rdb/VMS, and you use them at your own risk and design.
The existence of these strings is a side effect of the
extended date support; they are not intended for use in
language processors.
Digital also does not guarantee that the current behav-
ior of the YESTERDAY, TODAY, and TOMORROW strings will
remain the same in future releases of Rdb/VMS.
B.1.2 Output Date Formatting
The following new features should be noted:
o The formatting of quadword dates as text is now performed by a
call to LIB$FORMAT_DATE_TIME rather than by the system service
SYS$ASCTIM, which was used in previous versions of Rdb/VMS.
Example B-6 shows how to define the LIB$FORMAT_DATE_TIME system
logical and the resulting date formatting.
Internationalization Support in V3.1 B-7
Example B-6: RDO Output Date Formatting
__________________________________________________________________
$ DEFINE LIB$DT_FORMAT LIB$DATE_FORMAT_036, LIB$TIME_FORMAT_020
$ RDO
RDO>invoke database filename PERSONNEL
RDO>for e in employees with e.birthday > '2-JUN-1957'
cont> print e.birthday, e.first_name, e.last_name
cont>end_for
BIRTHDAY FIRST_NAME LAST_NAME
1957/06/03 0 h 0 min 0 s Brian Wood
1958/07/04 0 h 0 min 0 s Daniel Emery
1958/02/12 0 h 0 min 0 s Len Mercier
1959/05/15 0 h 0 min 0 s Louis Tarbassian
1958/12/20 0 h 0 min 0 s Daniel Vormelker
1958/05/09 0 h 0 min 0 s Christine Ulrich
1957/07/11 0 h 0 min 0 s Christine Watters
1960/01/10 0 h 0 min 0 s James Stornelli
__________________________________________________________________
The date formatting in SQL also uses the same mechanism, how-
ever, interactive SQL has implemented EDIT_STRING support. If
an EDIT_STRING is defined for the domain, then it will be used
in preference to the VMS date formatting. To reproduce the out-
put shown in the preceding RDO example, the SQL statement ALTER
DOMAIN STANDARD_DATE was used to remove the EDIT_STRING clause.
Example B-7 shows an example of output in SQL.
B-8 Internationalization Support in V3.1
Example B-7: SQL Input and Output Date Formatting
__________________________________________________________________
$ DEFINE LIB$DT_INPUT_FORMAT "!MAU !DD, !Y4 !H02:!M0:!S0:!C2 !MIU"
$ SQL
SQL> set language Spanish
SQL> show language
Language is SPANISH.
SQL> declare schema filename PERSONNEL;
cont>select birthday, last_name
cont>from employees
cont>where birthday > "abril 1, 1957";
BIRTHDAY LAST_NAME
1957/06/03 0 h 0 min 0 s Wood
1958/07/04 0 h 0 min 0 s Emery
1958/02/12 0 h 0 min 0 s Mercier
1959/05/15 0 h 0 min 0 s Tarbassian
1958/12/20 0 h 0 min 0 s Vormelker
1958/05/09 0 h 0 min 0 s Ulrich
1957/07/11 0 h 0 min 0 s Watters
1960/01/10 0 h 0 min 0 s Stornelli
8 rows selected
__________________________________________________________________
Example B-7 demonstrates that the input and output formatting
can be combined, and also how to use the SET LANGUAGE to allow
the Spanish month "abril" to be used.
o The default output date formatting is the same as the VMS
traditional format, and is defined by a systemwide logical
name:
$ show logical lib$dt_format
"LIB$DT_FORMAT" = "LIB$DATE_FORMAT_001" (LNM$SYSTEM_TABLE)
= "LIB$TIME_FORMAT_001"
Internationalization Support in V3.1 B-9
These date and time logical names translate to the following
formats:
$ show logical/table=LNM$DT_FORMAT_TABLE LIB$DATE_FORMAT_001
(LNM$DT_FORMAT_TABLE)
"LIB$DATE_FORMAT_001" = "!DB-!MAAU-!Y4"
$ show logical/table=LNM$DT_FORMAT_TABLE LIB$TIME_FORMAT_001
(LNM$DT_FORMAT_TABLE)
"LIB$TIME_FORMAT_001" = "!H04:!M0:!S0.!C2"
B.1.3 Summary of Statements Used to Change Date and Time
The following sections show the new statements in RDO and SQL that
can be used to change the date and time output display.
B.1.3.1 RDO: SHOW DATE_FORMAT Statement
The SHOW DATE_FORMAT statement produces the date and/or time
format number with an example of the format. It also displays
the current date input format that cannot be set from within
RDO. The output of this statement is sensitive to the setting
of SYS$LANGUAGE or the SET LANGUAGE statement.
Example B-8 shows the output from the RDO SHOW DATE_FORMAT state-
ment when the date and language settings are changed using either
the SET DATE_FORMAT statement or VMS logical name definitions.
B-10 Internationalization Support in V3.1
Example B-8: Log of RDO SHOW DATE_FORMAT Output
__________________________________________________________________
$!--------------------------------------------------------------------------
$! Use Logical names to set DATE and TIME formats
$!--------------------------------------------------------------------------
$! Default
$ RDO SHOW DATE_FORMAT
Output date and time formats are:
DATE = 1 (for example: 7-JUL-1989)
TIME = 1 (for example: 17:10:58.03)
Input date and time format is:
DD-MONTH-YYYY4 HH:MM:SS.CC2
$
$! Selection of formats
$ DEFINE LIB$DT_FORMAT LIB$DATE_FORMAT_012, LIB$TIME_FORMAT_011
$ RDO SHOW DATE_FORMAT
Output date and time formats are:
DATE = 12 (for example: JULY 7, 1989)
TIME = 11 (for example: 05:11 PM)
Input date and time format is:
DD-MONTH-YYYY4 HH:MM:SS.CC2
__________________________________________________________________
Example B-8 Cont'd on next page
Internationalization Support in V3.1 B-11
Example B-8 (Cont.): Log of RDO SHOW DATE_FORMAT Output
__________________________________________________________________
$
$! Change language
$ DEFINE SYS$LANGUAGE SPANISH
$ RDO SHOW DATE_FORMAT
Output date and time formats are:
DATE = 12 (for example: JULIO 7, 1989)
TIME = 11 (for example: 05:11 PM)
Input date and time format is:
DD-MES-AAAA4 HH:MM:SS.CC2
$
$! Define my own output date and time format, and only define one segment
$ DEFINE/EXEC/TABLE=LNM$DT_FORMAT_TABLE -
LIB$DATE_FORMAT_501 "!Y4!MN0!D0!H04!M0!S0!C2"
%DCL-I-SUPERSEDE, previous value of LIB$DATE_FORMAT_501 has been superseded
$ DEFINE LIB$DT_FORMAT LIB$DATE_FORMAT_501
%DCL-I-SUPERSEDE, previous value of LIB$DT_FORMAT has been superseded
$ RDO SHOW DATE_FORMAT
Output date and time formats are:
DATE = 501 (for example: 1989070717111106)
Input date and time format is:
DD-MES-AAAA4 HH:MM:SS.CC2
__________________________________________________________________
Example B-8 Cont'd on next page
B-12 Internationalization Support in V3.1
Example B-8 (Cont.): Log of RDO SHOW DATE_FORMAT Output
__________________________________________________________________
$
$ DEASSIGN LIB$DT_FORMAT
$ DEASSIGN SYS$LANGUAGE
$
$!--------------------------------------------------------------------------
$! Use SET DATE_FORMAT to change formats
$!--------------------------------------------------------------------------
$
$ RDO
! Default
SHOW DATE_FORMAT
Output date and time formats are:
DATE = 1 (for example: 7-JUL-1989)
TIME = 1 (for example: 17:11:15.45)
Input date and time format is:
DD-MONTH-YYYY4 HH:MM:SS.CC2
! Selection of formats
SET DATE_FORMAT DATE 12 TIME 11
SHOW DATE_FORMAT
Output date and time formats are:
DATE = 12 (for example: JULY 7, 1989)
TIME = 11 (for example: 05:11 PM)
Input date and time format is:
DD-MONTH-YYYY4 HH:MM:SS.CC2
__________________________________________________________________
Example B-8 Cont'd on next page
Internationalization Support in V3.1 B-13
Example B-8 (Cont.): Log of RDO SHOW DATE_FORMAT Output
__________________________________________________________________
! Change language
SET LANGUAGE SPANISH
SHOW DATE_FORMAT
Output date and time formats are:
DATE = 12 (for example: JULIO 7, 1989)
TIME = 11 (for example: 05:11 PM)
Input date and time format is:
DD-MES-AAAA4 HH:MM:SS.CC2
SET DATE_FORMAT DATE 501
SHOW DATE_FORMAT
Output date and time formats are:
DATE = 501 (for example: 1989070717111568)
Input date and time format is:
DD-MES-AAAA4 HH:MM:SS.CC2
$
$ EXIT
__________________________________________________________________
B-14 Internationalization Support in V3.1
If no translations exist or an error occurs, then the following
messages are displayed. Example B-9 shows errors.
Example B-9: RDO SHOW DATE_FORMAT Errors
__________________________________________________________________
RDO>show date_format
Output date and time formats are not defined
Input date and time format is not defined
RDO>show date
Output date and time formats are:
DATE = 1 (for example: 28-JUN-1989)
TIME = 1 (for example: 18:25:51.44)
Input date and time formats are not defined
%RDO-F-BADDTFMT, Date or time format is not set up correctly
-LIB-F-ILLFORMAT, illegal format, too many or not enough fields
__________________________________________________________________
B.1.3.2 RDO: SET DATE_FORMAT Statement
This statement will accept either the date format number or the
time format number, or both. This statement does not alter the
environment outside RDO.
Some examples of the different syntax combinations:
o SET DATE_FORMAT
Sets the default date and time formats LIB$DATE_FORMAT_001 and
LIB$TIME_FORMAT_001, which look like the traditional VMS date
and time DD-MMM-YYYY HH:MM:SS.CC
o SET DATE_FORMAT DATE 12
Sets the date only portion. This establishes LIB$DATE_FORMAT_
012 as the format. This will mean that DATE variables will
have only the DATE portion displayed (unless the format also
includes time components. See Example B-12 at the end of this
section).
Internationalization Support in V3.1 B-15
o SET DATE_FORMAT TIME 9
Sets the time only portion. This establishes LIB$TIME_FORMAT_
009 as the format. This will mean that DATE variables will
have only the TIME portion displayed (unless the format also
includes time components. See Example B-12 at the end of this
section).
o SET DATE_FORMAT DATE 12 TIME 9
SET DATE_FORMAT TIME 9 DATE 12)
These two statements set both components of the date and time
format. Both statements establish LIB$DATE_FORMAT_012 and
LIB$TIME_FORMAT_009 as the format.
NOTE
The SET DATE_FORMAT can be used to override the definition
of the VMS logical name LIB$DT_FORMAT. It is only used to
change the output of dates displayed by RDO such as data
displayed by the PRINT command, and output in various SHOW
statements. It does not change the input format, this is
only controlled by defining the logical name LIB$DT_INPUT_
FORMAT.
Here are some other RDO statements and queries which display
differently depending on the date and time setting.
The SHOW FIELD statement will display the MISSING_VALUE attribute
for DATE data types using the selected date and time. This may be
a little confusing if only the time component is selected.
RDO> SET DATE_FORMAT DATE 12 TIME 9
RDO> SHOW FIELD STANDARD_DATE
STANDARD_DATE Date
Description: Generic date field
Missing value: NOVEMBER 17, 1858 0.00
B-16 Internationalization Support in V3.1
In Example B-10 the same query is repeated using different date
and time settings.
Example B-10: Use of Different Date and Time Settings
__________________________________________________________________
RDO> SET DATE_FORMAT DATE 12 TIME 9
RDO> FOR E IN EMPLOYEES WITH E.LAST_NAME = "Toliver"
cont> PRINT E.LAST_NAME, E.FIRST_NAME, E.BIRTHDAY
cont> END_FOR
LAST_NAME FIRST_NAME BIRTHDAY
Toliver Alvin MARCH 28, 1947 0.00
RDO> SET DAT TIM 9
RDO> FOR E IN EMPLOYEES WITH E.LAST_NAME = "Toliver"
cont> PRINT E.LAST_NAME, E.FIRST_NAME, E.BIRTHDAY
cont> END_FOR
LAST_NAME FIRST_NAME BIRTHDAY
Toliver Alvin 0.00
RDO> SET DAT DATE 12
RDO> FOR E IN EMPLOYEES WITH E.LAST_NAME = "Toliver"
cont> PRINT E.LAST_NAME, E.FIRST_NAME, E.BIRTHDAY
cont> END_FOR
LAST_NAME FIRST_NAME BIRTHDAY
Toliver Alvin MARCH 28, 1947
__________________________________________________________________
B.1.3.3 RDO: SET LANGUAGE Statement
The SET LANGUAGE statement changes the current language in use for
date and time display and the interpretation of input dates con-
taining text fields. This statement does not alter the environment
outside RDO.
Example B-11 shows the output of the SHOW DATE_FORMAT statement
after using the SET LANGUAGE statement.
Internationalization Support in V3.1 B-17
Example B-11: Output of RDO SHOW DATE_FORMAT Statement for
Different Languages
__________________________________________________________________
RDO> show lang
Language in use for date and time input/output formatting is ENGLISH
RDO> show date
Output date and time formats are:
DATE = 1 (for example: 10-JUL-1989)
TIME = 1 (for example: 15:44:11.24)
Input date and time format is:
DD-MONTH-YYYY4 HH:MM:SS.CC2
RDO> set lang spanish
RDO> show date
Output date and time formats are:
DATE = 1 (for example: 10-jul-1989)
TIME = 1 (for example: 15:44:19.74)
Input date and time format is:
DD-MES-AAAA4 HH:MM:SS.CC2
RDO> show lan
Language in use for date and time input/output formatting is SPANISH
RDO> set lang german
RDO> show lan
Language in use for date and time input/output formatting is GERMAN
RDO> show date
Output date and time formats are:
DATE = 1 (for example: 10-JUL-1989)
TIME = 1 (for example: 15:44:38.02)
Input date and time format is:
tt-Monat-jjjj4 hh:mm:ss.cc2
RDO> set lang finnish
%RDO-F-NOSUCHLANG, specified language not defined on this system
RDO> exit
__________________________________________________________________
NOTE
The SET LANGUAGE statement does not affect collating se-
quences and has nothing to do with sorting data.
B-18 Internationalization Support in V3.1
B.1.3.4 RDO: SHOW LANGUAGE Statement
Displays the currently selected language. See Example B-11 for
sample output from the SHOW LANGUAGE statement.
B.1.3.5 SQL: SHOW DATE FORMAT Statement
The SHOW DATE FORMAT statement produces the date and/or time
format number.
Example B-12 shows the output from SHOW DATE FORMAT when the date
and language settings are changed using either the SET DATE FORMAT
statement or VMS logical name definitions.
Example B-12: Log of SQL SET and SHOW DATE FORMAT Statements
__________________________________________________________________
__________________________________________________________________
Example B-12 Cont'd on next page
Internationalization Support in V3.1 B-19
Example B-12 (Cont.): Log of SQL SET and SHOW DATE FORMAT
Statements
__________________________________________________________________
$!--------------------------------------------------------------------------
$! Use Logical names to set DATE and TIME formats
$!--------------------------------------------------------------------------
$! Default
$ SQL$ SHOW DATE FORMAT
Date format is DATE 1, TIME 1.
$
$! Selection of formats
$ DEFINE LIB$DT_FORMAT LIB$DATE_FORMAT_012, LIB$TIME_FORMAT_011
$ SQL$ SHOW DATE FORMAT
Date format is DATE 12, TIME 11.
$
$! Define my own output date and time format, and only define one segment
$ DEFINE/EXEC/TABLE=LNM$DT_FORMAT_TABLE -
LIB$DATE_FORMAT_501 "!Y4!MN0!D0!H04!M0!S0!C2"
%DCL-I-SUPERSEDE, previous value of LIB$DATE_FORMAT_501 has been superseded
$ DEFINE LIB$DT_FORMAT LIB$DATE_FORMAT_501
%DCL-I-SUPERSEDE, previous value of LIB$DT_FORMAT has been superseded
$ SQL$ SHOW DATE FORMAT
Date format is DATE 501.
$
$ DEASSIGN LIB$DT_FORMAT
$
$!--------------------------------------------------------------------------
$! Use SET DATE FORMAT to change formats
$!--------------------------------------------------------------------------
$
$ SQL$
DECLARE SCHEMA FILENAME 'PERSONNEL';
__________________________________________________________________
Example B-12 Cont'd on next page
B-20 Internationalization Support in V3.1
Example B-12 (Cont.): Log of SQL SET and SHOW DATE FORMAT
Statements
__________________________________________________________________
! Default
SHOW DATE FORMAT
Date format is DATE 1, TIME 1.
! Selection of formats
SET DATE FORMAT DATE 12, TIME 11
SHOW DATE FORMAT
Date format is DATE 12, TIME 11.
! Change language
SET LANGUAGE SPANISH
SELECT LAST_NAME, BIRTHDAY
FROM EMPLOYEES
WHERE LAST_NAME = 'Toliver';
LAST_NAME BIRTHDAY
Toliver MARZO 28, 1947 00:00 AM
1 row selected
SET DATE FORMAT DATE 501
SHOW DATE FORMAT
Date format is DATE 501.
SELECT LAST_NAME, BIRTHDAY
FROM EMPLOYEES
WHERE LAST_NAME = 'Toliver';
LAST_NAME BIRTHDAY
Toliver 1947032800000000
1 row selected
$
$ EXIT
__________________________________________________________________
If no translations exist or an error occurs in translation, then
an error message is generated.
Internationalization Support in V3.1 B-21
B.1.3.6 SQL: SET DATE FORMAT Statement
This statement will accept either the date format number, or the
time format number, or both. This statement does not alter the
environment outside SQL.
Some examples of the different syntax combination are:
o SET DATE FORMAT
Sets the default date and time formats LIB$DATE_FORMAT_001 and
LIB$TIME_FORMAT_001 which look like the traditional VMS date
and time DD-MMM-YYYY HH:MM:SS.CC.
o SET DATE FORMAT DATE 12
Sets the date only portion. This establishes LIB$DATE_FORMAT_
012 as the format. This will mean that DATE variables will
have only the DATE portion displayed (unless the format also
includes time components-see the SHOW DATE FORMAT example at
the end of this section).
o SET DATE FORMAT TIME 9
Sets the time only portion. This establishes LIB$TIME_FORMAT_
009 as the format. This will mean that DATE variables will
have only the TIME portion displayed (unless the format also
includes time components-see SHOW DATE FORMAT example below).
o SET DATE FORMAT DATE 12 TIME 9
SET DATE FORMAT TIME 9 DATE 12
Set both components of the date-time format. Both statements
establish LIB$DATE_FORMAT_012 and LIB$TIME_FORMAT_009 as the
format.
NOTE
The SET DATE FORMAT statement can be used to override the
definition of the VMS logical name LIB$DT_FORMAT. It is
only used to change the output of dates displayed by SQL.
For example, it can change data displayed by the SELECT
B-22 Internationalization Support in V3.1
statement and output of various SHOW statements. It does
not change the input format; this is only controlled by
defining the logical name LIB$DT_INPUT_FORMAT.
Here are some other SQL statements and queries that display dif-
ferently depending on the date and time setting.
The SHOW DOMAIN statement will display the DEFAULT value and the
MISSING_VALUE attribute for DATE data types using the selected
date and time. This may be a little confusing if only the time
component is selected.
SQL> SET DATE FORMAT DATE 12, TIME 9
SQL> CREATE DOMAIN STANDARD_DATE DATE DEFAULT "1-JUN-1989 12:00";
SQL> SHOW DOMAIN STANDARD_DATE
STANDARD_DATE DATE
Rdb default: JUNE 1, 1989 12.00
Example B-13 repeats an SQL query using different date and time
settings.
Internationalization Support in V3.1 B-23
Example B-13: Output of SQL Query In Different Date Formats
__________________________________________________________________
SQL> SET DATE FORMAT DATE 12, TIME 9
SQL> SELECT LAST_NAME, FIRST_NAME, BIRTHDAY
cont> FROM EMPLOYEES
cont> WHERE LAST_NAME = "Toliver";
LAST_NAME FIRST_NAME BIRTHDAY
Toliver Alvin MARCH 28, 1947 0.00
1 row selected
SQL>
SQL> SET DAT FORM TIM 9
SQL> SELECT LAST_NAME, FIRST_NAME, BIRTHDAY
cont> FROM EMPLOYEES
cont> WHERE LAST_NAME = "Toliver";
LAST_NAME FIRST_NAME BIRTHDAY
Toliver Alvin 0.00
1 row selected
SQL>
SQL> SET DAT FORM DATE 12
SQL> SELECT LAST_NAME, FIRST_NAME, BIRTHDAY
cont> FROM EMPLOYEES
cont> WHERE LAST_NAME = "Toliver";
LAST_NAME FIRST_NAME BIRTHDAY
Toliver Alvin MARCH 28, 1947
1 row selected
__________________________________________________________________
B.1.3.7 SQL: SET LANGUAGE Statement
The SET LANGUAGE statement changes the current language in use for
date and time display and the interpretation of input dates con-
taining text fields. This statement does not alter the environment
outside SQL.
NOTE
The SET LANGUAGE statement does not affect collating se-
quences and has nothing to do with sorting data.
B-24 Internationalization Support in V3.1
B.1.3.8 SQL: SHOW LANGUAGE Statement
The SHOW LANGUAGE statement displays the currently selected lan-
guage. For example:
SQL> show language
Language is ENGLISH.
SQL> set language sp
%SQL-F-NOSUCHLANG, Language SP does not exist.
SQL> set language spanish
SQL> show language
Language is SPANISH.
B.1.4 Other Date Usage
B.1.4.1 RDO Usage
Date literals can be specified in the following places in RDO:
o Within RSEs
FOR JH IN JOB_HISTORY WITH JH.START_DATE > '1-JUN-1960'
...
END_FOR
This includes within view and constraint definitions.
o Within a store or modify assignment
STORE E IN EMPLOYEES USING
...
E.BIRTHDATE = '11-nov-1978';
...
END_STORE
o Within trigger definitions
These can occur as part of the WHEN clause as a comparison, or
as an assigned value in an action.
Internationalization Support in V3.1 B-25
o Within a storage map or index definition
The date literal can be specified as part of the WITH LIMIT
clause.
DEFINE STORAGE MAP ORDERS_MAP FOR ORDERS
STORE USING PROCESS_DATE WITHIN
ARCHIVE_1986 WITH LIMIT OF "31-DEC-1986 23:59:59.99";
ARCHIVE_1987 WITH LIMIT OF "31-DEC-1987 23:59:59.99";
ARCHIVE_1988 WITH LIMIT OF "31-DEC-1988 23:59:59.99";
ARCHIVE_1989 WITH LIMIT OF "31-DEC-1989 23:59:59.99";
ARCHIVE_1990 WITH LIMIT OF "31-DEC-1990 23:59:59.99";
ARCHIVE_1991
END ORDERS_MAP.
COMMIT
There are two statements within RDO that currently do not support
the new date format:
o DEFINE TRANSFER . .. START time .. . AT time
The DEFINE TRANSFER statement was not modified to accept the
new date and time literals. SQL is the preferred interface
to Rdb/VMS. The VAX Data Distributor commands have been im-
plemented in SQL for Version 3.1. These commands accept full
multinational dates if the date strings are enclosed in quota-
tion marks.
o RECOVER . .. UNTIL time
The RDO RECOVER statement is also obsolete, replaced by the
RMU/RECOVER command. It will be retired in a future release.
B-26 Internationalization Support in V3.1
B.1.4.2 SQL Usage
Date literals can be specified in the following places in SQL:
o Within RSEs
SELECT *
FROM JOB_HISTORY
WHERE START_DATE > '1-JUN-1960';
This includes within view and check constraint definitions.
o Within an insert or update assignment
INSERT
INTO EMPLOYEES (..., BIRTHDATE, ...)
VALUES (..., '11-NOV-1978', ...);
o Within trigger definitions
This can occur as part of the WHEN clause as a comparison, or
as an assigned value in an action.
o Within a storage map or index definition
The date literal can be specified as part of the WITH LIMIT
clause.
create storage map ORDERS_MAP FOR ORDERS
using (PROCESS_DATE)
in ARCHIVE_1986 WITH LIMIT OF ("31-DEC-1986 23:59:59.99")
in ARCHIVE_1987 WITH LIMIT OF ("31-DEC-1987 23:59:59.99")
in ARCHIVE_1988 WITH LIMIT OF ("31-DEC-1988 23:59:59.99")
in ARCHIVE_1989 WITH LIMIT OF ("31-DEC-1989 23:59:59.99")
in ARCHIVE_1990 WITH LIMIT OF ("31-DEC-1990 23:59:59.99")
otherwise in ARCHIVE_1991;
o CREATE TRANSFER . .. START time .. . AT time
Internationalization Support in V3.1 B-27
If the absolute times are specified as quoted string literals,
then the new format is accepted by SQL.
B.1.4.3 RMU Usage
RMU has only one qualifier that accepts dates and times. The
/UNTIL qualifier will now accept the new date formats defined by
LIB$DT_INPUT_FORMAT. This qualifier is available on the following
commands:
o RMU/BACKUP/AFTER_JOURNAL/UNTIL
o RMU/RECOVER/UNTIL
o RMU/SHOW STATISTICS/UNTIL
B.2 Collating Sequence Support
Collating sequences allow text fields to be sorted in an order
different from the standard ASCII collating sequence.
NOTE
The setting of the logical name SYS$LANGUAGE or the SQL/RDO
SET LANGUAGE statements have absolutely no effect on col-
lating sequences. The collating sequence is part of the
data and is used to encode sorted indexes. They cannot be
modified per user session.
B.2.1 How to Define Collating Sequences
The collating sequence can be defined database-wide using the SQL
CREATE SCHEMA (RDO DEFINE DATABASE) statement so that all text
fields (RDO: TEXT and VARYING STRING, SQL: CHAR, VARCHAR, and LONG
VARCHAR) will be ordered appropriately for the environment.
B-28 Internationalization Support in V3.1
create schema
filename 'SUOMI_DB'
collating sequence SUOMI FINNISH;
define database 'SUOMI_DB'
collating_sequence SUOMI
description is /* This is a Finnish database */
FINNISH.
Some applications may require several collating sequences that
can be defined using the CREATE COLLATING SEQUENCE (RDO: DEFINE
COLLATING_SEQUENCE) clause and applied to specific domains (which
also overrides the database-wide default).
create collating sequence NORSK NORWEGIAN;
create collating sequence ENGLISH ENGLISH;
create collating sequence ESPAÑOL SPANISH;
define collating_sequence NORSK NORWEGIAN.
define collating_sequence ENGLISH ENGLISH.
define collating_sequence ESPAÑOL SPANISH.
The collating sequences in these examples (NORWEGIAN, ENGLISH
and SPANISH) will be copied from the VMS system NCS library in
SYS$LIBRARY:NCS$LIBRARY. Once defined, the collating sequences
are referred to by their Rdb/VMS object names (NORSK, ENGLISH, and
ESPAÑOL).
It is also possible for users to define their own collating se-
quences and store them in their own libraries.
NOTE
The NCS support in VMS also allows the definition of con-
version functions, however, these are not used by Rdb/VMS.
The actual collating sequence is defined using the VMS version 5
NCS utility. Rdb/VMS copies the collating sequence named from the
NCS library into the relation RDBVMS$COLLATIONS. If the collating
sequence in the NCS library is modified then the database or at
Internationalization Support in V3.1 B-29
least the affected relations will need to be rebuilt. This can be
achieved by manually changing the database:
o Deleting all indexes defined for the table which reference
the field or fields which need a changed collating sequence. A
query on the system relations can be performed to display all
affected relations and indexes.
FOR FI IN RDB$FIELDS WITH
FI.RDBVMS$COLLATION_NAME <> " " AND
FI.RDB$FIELD_NAME NOT MATCHING "RDB*$*"
SORTED BY FI.RDB$FIELD_NAME
PRINT ""
PRINT "Global field: " | FI.RDB$FIELD_NAME
PRINT "Collating Seq: " | FI.RDBVMS$COLLATION_NAME
PRINT ""
!
! Look at dependency on fields with collating sequences
!
FOR RE IN RDB$RELATIONS CROS
RF IN RDB$RELATION_FIELDS WITH
RE.RDB$RELATION_NAME = RF.RDB$RELATION_NAME AND
RF.RDB$FIELD_SOURCE = FI.RDB$FIELD_NAME
PRINT "- Used by relation " | RE.RDB$RELATION_NAME
FOR IS IN RDB$INDEX_SEGMENTS WITH
IS.RDB$FIELD_NAME = RF.RDB$FIELD_NAME
PRINT "- Used by index " | IS.RDB$INDEX_NAME
PRINT " Segment: " | IS.RDB$FIELD_NAME
END_FOR
END_FOR
END_FOR
o Define a new collating sequence.
RDO> define collating_sequence NORSK2 NORWEGIAN2.
B-30 Internationalization Support in V3.1
o Use the RDO CHANGE FIELD . .. COLLATING_SEQUENCE statement
or the SQL ALTER DOMAIN . .. COLLATING SEQUENCE statement to
modify the collating sequence for the field (column).
o The indexes can now be redefined.
B.2.2 Using Collating Sequences
Fields will inherit the database-wide collating sequence if it
is defined (unless the clause NO COLLATING_SEQUENCE is used),
otherwise it must be selectively applied to global fields.
define field NOR_WORD
datatype is TEXT size is 10 characters
collating_sequence NORSK.
define field SPAN_WORD
datatype is TEXT size is 10 characters
collating_sequence ESPAÑOL.
create domain ENGL_WORD
char(10)
collating sequence ENGLISH;
create domain ASCII_WORD
char(20)
no collating sequence;
When these domains or global field definitions are used in table
definitions, the table will inherit column-level collating se-
quences.
The SORTED BY (or ORDER BY) clause will now sort according to the
collating sequence. As a side effect REDUCED TO (or DISTINCT) will
also sort differently.
The index nodes in a sorted index are now also different. To
support the more complex collating sequence the length of the
indexed fields is reduced. The size depends upon the complexity of
the collating sequence.
Internationalization Support in V3.1 B-31
B.3 MCS Characters in Rdb/VMS Metadata Names
Rdb/VMS V3.0 supports the use of the multinational characters set
(MCS) in database object names such as relations, fields (or the
SQL equivalents), indexes, and constraints. Context variables can
also contain MCS characters.
Required support in the language processors includes: fields,
relations (or the SQL equivalents) and context variables.
Restrictions:
o Database handles are declared as host language variables and
used as PSECT names so these cannot contain MCS characters due
to current VMS and host language restrictions.
o Transaction and request handles are assumed to be host lan-
guage variables so these can not contain MCS characters due to
current host language restrictions.
o The included CDD/Plus record may not be acceptable to the host
language if the fields contain MCS characters.
The CDO DEFINE FIELD syntax allows the NAME FOR {language}
clause to be used for some VMS languages to override the de-
fault field name. This will allow records to be included based
on Rdb/VMS relations to succeed even if MCS characters are
used.
o The GET *, STORE *, MODIFY * statements assume that the record
field names (from CDD/Plus or declared manually) match the
relation field names precisely.
This mean that fields with MCS characters will generate illegal
host language variable names.
The workaround is to specify each field specifically and avoid
the wildcard field syntax.
B-32 Internationalization Support in V3.1