VAX SQL Release Notes
Order Number: AA-JM35C-TE
July 1988
This manual discusses problems and restrictions
that apply to this version of VAX SQL and are not
documented elsewhere.
Operating System: VMS
Software Version: VAX SQL Version 2.0
digital equipment corporation maynard, massachusetts
________________________
__________
The information in this document is subject to change
without notice and should not be construed as a
commitment by Digital Equipment Corporation. Digital
Equipment Corporation assumes no responsibility for
any errors that may appear in this document.
The software described in this document is furnished
under a license and may be used or copied only in
accordance with the terms of such license.
No responsibility is assumed for the use or
reliability of software on equipment that is not
supplied by Digital Equipment Corporation or its
affiliated companies.
__________
Copyright (c)1987, 1988 by Digital Equipment Corporation
All Rights Reserved.
Printed in U.S.A.
__________
The postpaid READER'S COMMENTS form on the
last page of this document requests the user's
critical evaluation to assist in preparing future
documentation.
The following are trademarks of Digital Equipment
Corporation:
DEC DIBOL UNIBUS
DEC/CMS EduSystem VAX
DEC/MMS IAS VAXcluster
DECnet MASSBUS VMS
DECsystem-10 PDP VT
DECSYSTEM-20 PDT
DECUS RSTS
DECwriter RSX DIGITAL
DB2 and IBM are trademarks of International Business
Machines Corporation.
This document was prepared using VAX DOCUMENT, Version
1.0
_______________________________________________________
Contents
_________________________________________________
PREFACE vii
_______________________________________________________
CHAPTER 1 RELEASE NOTES FOR VAX SQL VERSION
2.0 1-1
_________________________________________________
1.1 NEW FEATURES AND TECHNICAL CHANGES
IN VERSION 2.0 1-1
1.1.1 Support for Rdb/VMS
Multifile Database
Features 1-2
1.1.2 Support for CDD/Plus
Features 1-3
1.1.2.1 CREATE TABLE Statement Can Use
CDD/Plus Record for Definition
1-3
1.1.2.2 CREATE DOMAIN Statement Can Use
CDD/Plus Field for Definition 1-4
1.1.2.3 INTEGRATE Statement Updates
Dictionary and Schema
Definitions 1-4
1.1.2.4 DROP PATHNAME Statement Deletes
Dictionary Definitions 1-4
1.1.2.5 New Dictionary Format Requires
Deletion of Old Path Names 1-5
1.1.3 Support for ANSI Standard
SQL and SQL2 Features 1-5
1.1.3.1 Module Language 1-5
1.1.3.2 Schema Definition Language:
CREATE SCHEMA 1-7
1.1.3.3 Domains (Global Fields) 1-8
1.1.3.4 Constraints 1-8
1.1.3.5 SET TRANSACTION Statement 1-9
iii
Contents
1.1.3.6 Use of Column Select Expressions
as Value Expressions 1-9
1.1.3.7 BEGIN DECLARE and END DECLARE
Statements 1-10
1.1.3.8 Fewer Restrictions on Updating
Cursors 1-10
1.1.4 VAX SQL Compatible with
Version 5.0 of VMS 1-11
1.1.5 IMPORT and EXPORT
Statements Load and Unload
Databases 1-11
1.1.6 Support for Ada in the VAX
SQL Precompiler 1-12
1.1.7 Formatting Enhancements in
Interactive SQL 1-12
1.1.8 RELEASE Statement Frees
Resources in Dynamic SQL 1-12
1.1.9 QUIT Statement Exits
Interactive SQL and Rolls
Back Changes 1-13
1.1.10 FINISH Statement
Explicitly Detaches from
Declared Schemas 1-13
1.1.11 Interactive SQL EDIT
Statement Can Now Invoke
LSE 1-13
1.1.12 Interactive SQL SHOW
Statement Gives More
Information 1-14
1.1.13 COMMENT ON Statement
Allows Comments on
Indexes 1-14
1.1.14 Support for CONTAINING,
STARTING WITH, and UNIQUE
Predicates 1-14
1.1.15 Additional Online Sample
Programs 1-14
iv
Contents
1.1.16 Other Technical Changes
That Affect Applications 1-16
1.1.16.1 Support for Rdb/VMS Database
Keys 1-17
1.1.16.2 Declaring VIDA Databases
Affects Declared Transactions
Differently 1-18
1.1.16.3 ALTER TABLE ALTER No Longer
Requires Data Type 1-18
1.1.16.4 C Precompiler Support for
Pointer Variables and Block
Structure 1-19
1.1.16.5 SQL No Longer Performs Text
Arithmetic 1-19
1.1.16.6 DECLARE SCHEMA PATHNAME Now
Uses Fully Expanded File
Specification 1-19
1.1.16.7 MCS Characters Permitted in
User-Supplied Names 1-20
1.1.16.8 Support for Rdb/VMS Version 3.0
Requires Conversion of Databases
1-20
1.1.16.9 Installing SQL as Shareable
Image Uses More Global Pages 1-21
_________________________________________________
1.2 BUGS FIXED IN VAX SQL VERSION 2.0 1-21
_________________________________________________
1.3 KNOWN PROBLEMS WITH VAX SQL VERSION
2.0 1-24
1.3.1 No Data Type Checking on
WITH LIMIT Clause 1-24
1.3.2 REDEFINES Clause Not
Supported in COBOL
Declarations 1-25
1.3.3 SHOW DICTIONARY Does Not
Always Display Correct
Path Name 1-26
v
Contents
1.3.4 CREATE SCHEMA Statement
in Programs Must be First
Definition 1-26
1.3.5 DDL Statements Cannot
Refer to Objects Before
Their Creation 1-26
_________________________________________________
1.4 RDB/VMS FEATURES NOT SUPPORTED BY
VAX SQL 1-27
_________________________________________________
1.5 DOCUMENTATION FOR VAX SQL VERSION
2.0 1-28
1.5.1 Documentation Errors
Omissions 1-31
1.5.1.1 STORE Clause Syntax of Storage
Map and Associated Index Should
Not Always Be Identical 1-31
_________________________________________________
1.6 RDB/VMS BUGS FIXED THAT AFFECTED
VAX SQL 1-33
_________________________________________________
1.7 KNOWN PROBLEMS WITH RDB/VMS THAT
AFFECT VAX SQL 1-35
1.7.1 Precompiled Cursors with
Many Functions Can Cause
Bugcheck Dump 1-36
1.7.2 Complex Join with GROUP BY
Clause Can Cause Bugcheck
Dump 1-37
1.7.3 Conversion of Null CHAR
Column to DATE Gives
Invalid Error 1-37
1.7.4 GROUP BY Query Based on
Multiple Views Causes
Bugcheck Dump 1-37
vi
Contents
1.7.5 Subqueries Referring to
Views May Cause Bugcheck
Dump 1-38
_______________________________________________________
TABLES
1-1 Language Sources Available for
Sample Programs 1-16
1-2 Changes in Keywords and
Terminology for Version 2.0 1-30
vii
_______________________________________________________
Preface
This manual contains information on the known
limitations, restrictions, and workarounds to existing
problems in this version of VAX SQL.
The VAX SQL installation procedure creates
an online version of these release notes in
SYS$HELP:SQL$020.RELEASE_NOTES.
__________________________________________________________________
Intended Audience
These Release Notes are intended for all users of VAX
SQL.
__________________________________________________________________
Operating System Information
You can find information about the versions of the
operating system and optional software that are
compatible with this version of VAX SQL in Chapter
1 of the VAX SQL Installation Guide.
For information on the compatibility of other software
products with this version of VAX SQL, 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 VAX SQL.
Contact your DIGITAL representative if you have
questions about the compatibility of other software
products with this version of VAX SQL.
vii
Preface
__________________________________________________________________
Related Manuals
You can find additional information about VAX SQL in
the following manuals:
o VAX SQL Reference Manual
o VAX SQL User's Guide
o VAX SQL Installation Guide
__________________________________________________________________
References to Products
The VAX SQL documentation to which this manual belongs
often refers to products by their abbreviated names:
o VAX ACMS software is referred to as ACMS.
o VAX Ada software is referred to as Ada.
o VAX BASIC software is referred to as BASIC.
o VAX C software is referred to as C.
o VAX CDD/Plus software is referred to as the data
dictionary.
o VAX COBOL software is referred to as COBOL.
o VAX DATATRIEVE software is referred to as
DATATRIEVE.
o VAX DBMS software is referred to as DBMS.
o VAX FORTRAN software is referred to as FORTRAN.
o VAX PASCAL software is referred to as PASCAL.
o VAX PL/I software is referred to as PL/I.
o VAX Rdb/ELN software is referred to as Rdb/ELN.
o VAX Rdb/VMS software is referred to as Rdb/VMS.
o VAX SQL software is referred to as SQL.
o VAX TDMS software is referred to as TDMS.
viii
Preface
o VIDA with IDMS/R software is referred to as VIDA.
ix
_______________________________________________________
Release Notes for VAX SQL Version 2.0
The VAX SQL installation procedure creates
an online version of these release notes in
SYS$HELP:SQL$020.RELEASE_NOTES.
Note: VAX SQL is no longer available as a separate
product beginning with this release. The VAX SQL
software and documentation are now packaged with
Rdb/VMS.
For Rdb/VMS Version 3.0, users are receiving VAX
SQL Version 2.0 software and documentation along
with Rdb/VMS Version 3.0 software and documentation.
Please install the Rdb/VMS Version 3.0 software first,
then install the VAX SQL software. The VAX Rdb/VMS
Installation Guide contains information on installing
VAX Rdb/VMS; the VAX SQL manuals contain detailed
information on installing and using VAX SQL.
__________________________________________________________________
New Features and Technical Changes in Version 2.0
This section summarizes new features in Version 2.0
of VAX SQL and tells where to find more complete
documentation on those features. It also describes
other technical changes in Version 2.0 that can affect
users and applications.
1-1
Release Notes for VAX SQL Version 2.0
___________________________
Support for Rdb/VMS Multifile Database Features
VAX SQL Version 2.0 supports features added to Rdb/VMS
Version 3.0 for improved database performance.
Several new statements in VAX SQL allow you to create
multiple data and snapshot files and to control
which parts of which tables are stored in each data
file. This additional flexibility can greatly improve
database performance if carefully managed:
o You can tailor the characteristics of individual
data files to accommodate the tables that will
reside in them.
o You can spread the data for different tables,
or data for a single table, among data files on
different disks to reduce input/output bottlenecks.
o You can cluster rows for related tables together
to speed simultaneous retrieval of rows from those
tables.
o You can cluster table rows with their associated
indexes to speed retrieval.
The VAX SQL statements that implement these features
are:
o CREATE SCHEMA, ALTER SCHEMA
Creates or alters definitions for a multifile
database. Both statements allow creation of storage
areas, the individual data and snapshot files that
make up a multifile database.
o CREATE STORAGE MAP, ALTER STORAGE MAP
Associates a table with one or more storage areas.
Allows storage of table rows to speed retrieval by
a particular index. DROP STORAGE MAP deletes the
storage map.
o CREATE INDEX, ALTER INDEX
1-2
Release Notes for VAX SQL Version 2.0
Creates or alters an index and allows specification
of a storage map to cluster an index with its
associated table. Can create hashed indexes to
improve performance of queries that directly access
a particular row.
For more detail on the CREATE and ALTER statements,
see the sections on those statements in Chapter 4 of
the VAX SQL Reference Manual. For more information
on multifile databases, see the VAX Rdb/VMS Guide to
Database Maintenance and Performance
___________________________
Support for CDD/Plus Features
This section introduces features in VAX SQL Version
2.0 that support VAX CDD/Plus Version 4.0. For general
information about how VAX CDD/Plus works with Rdb/VMS,
read the following:
o The chapter on using VAX CDD/Plus in the VAX
Rdb/VMS Guide to Database Design and Definition
o The chapter on using Rdb/VMS with CDD/Plus in the
VAX CDD/Plus User's Guide
o The VAX CDD/Plus Common Dictionary Operator
Reference Manual
_____________________
1.1.2.1 CREATE TABLE Statement Can Use
CDD/Plus Record for Definition
The CREATE TABLE statement now lets you specify a
CDD path name for a record definition to copy to the
database for a table definition.
See the section on the CREATE TABLE statement in
Chapter 4 of the VAX SQL Reference Manual for syntax.
1-3
Release Notes for VAX SQL Version 2.0
_____________________
1.1.2.2 CREATE DOMAIN Statement Can Use
CDD/Plus Field for Definition
The CREATE DOMAIN statement now lets you specify a
CDD path name for a field definition to copy to the
database for a domain definition.
See the section on the CREATE DOMAIN statement in
Chapter 4 of the VAX SQL Reference Manual for syntax.
_____________________
1.1.2.3 INTEGRATE Statement Updates Dictionary
and Schema Definitions
The INTEGRATE statement makes definitions in a schema
and data dictionary consistent with each other.
INTEGRATE allows these operations:
o Updating definitions (created with the FROM
clause) in a schema to reflect changes to the data
dictionary record and field definitions to which
they are linked (INTEGRATE SCHEMA PATHNAME ALTER
FILES)
o Updating data dictionary record and field
definitions to reflect changes in the schema
definitions to which they are linked (INTEGRATE
SCHEMA PATHNAME ALTER DICTIONARY)
o Creating a data dictionary schema definition with
copies of schema definitions from the database
system root file (INTEGRATE SCHEMA FILENAME CREATE
PATHNAME)
See the section on the INTEGRATE statement in Chapter
4 of the VAX SQL Reference Manual for more detail.
_____________________
1.1.2.4 DROP PATHNAME Statement Deletes
Dictionary Definitions
The DROP PATHNAME statement deletes all defintions for
a schema in the data dictionary. See the section on
the DROP PATHNAME statement in Chapter 4 of the VAX
SQL Reference Manual for more detail.
1-4
Release Notes for VAX SQL Version 2.0
_____________________
1.1.2.5 New Dictionary Format Requires
Deletion of Old Path Names
VAX CDD/Plus dictionaries use a new format that is
incompatible with the dictionary format supported
by past versions of VAX SQL. This means that you
must delete any dictionary path names for databases
created before Rdb/VMS Version 2.0 of VAX SQL before
you install Version 3.0 and convert those databases
to the format required by Rdb/VMS Version 3.0 (see
Section 1.1.16.8).
To delete path names for databases, invoke Version 2.3
of RDO and use the DELETE PATHNAME statement.
___________________________
Support for ANSI Standard SQL and SQL2 Features
Several new features in Version 2.0 bring VAX SQL
into closer compliance with the ANSI standard for SQL,
and closer to the ANSI/ISO working draft standard for
SQL2.
_____________________
1.1.3.1 Module Language
The SQL module language provides a calling mechanism
for host language programs to execute SQL statements
contained in a separate file called an SQL module
file. The file contains elements that specify a
single SQL module. The module includes one or
more procedures, each of which contains parameter
declarations and an SQL statement.
The SQL module procedures can be called from any
host language, including those not supported by the
SQL precompiler. A call to a procedure in an SQL
module causes the SQL statement in the procedure to
be executed.
The SQL module language offers these advantages to
precompiled SQL:
o Languages not supported by the SQL precompiler can
use the SQL module language instead.
1-5
Release Notes for VAX SQL Version 2.0
o Programs that use the SQL module language can
isolate all SQL statements in SQL modules to
improve modularity and avoid using two languages
in the same source file.
o Programs can work around restrictions of the SQL
precompiler by calling SQL modules:
- Programs that support pointer variables can take
full advantage of dynamic SQL and use the SQLDA
with the SQL module language.
- SQL module language does not restrict use of
host language features not supported by the
precompiler (such as pointer variables in C,
macros, user-defined types, references to array
elements).
o Programs written in languages for which there is
an ANSI standard can avoid embedding nonconformant
code by isolating SQL statements in SQL modules.
For more information about the SQL module language,
read the following:
o The chapter on creating and processing programs
with the SQL module language in the VAX SQL User's
Guide
o The chapter on the SQL module language in the VAX
SQL Reference Manual
1-6
Release Notes for VAX SQL Version 2.0
_____________________
1.1.3.2 Schema Definition Language: CREATE
SCHEMA
The CREATE SCHEMA statement in Version 2.0 replaces
the CREATE DATABASE statement in earlier versions.
CREATE DATABASE is supported for upward compatibility
only and is no longer documented.
A schema is a collection of definitions that comprises
a database. The CREATE SCHEMA statement complies with
ANSI syntax to specify data definition statements
within a single CREATE SCHEMA statement. The CREATE
SCHEMA statement also has extensions for defining
Rdb/VMS databases and can include any of the following
statements within it:
o CREATE DOMAIN
o CREATE INDEX
o CREATE STORAGE MAP
o CREATE TABLE
o CREATE VIEW
o GRANT
In addition, the DROP, DECLARE, and SHOW SCHEMA
statements replace the DROP, DECLARE, and SHOW
DATABASE statements, which are no longer documented.
For more information about schema definition, read the
following:
o The chapter on defining a database in the VAX SQL
User's Guide
o The sections on the CREATE, DECLARE, and DROP
SCHEMA statements in Chapter 4 of the VAX SQL
Reference Manual
1-7
Release Notes for VAX SQL Version 2.0
_____________________
1.1.3.3 Domains (Global Fields)
Version 2.0 supports SQL2 syntax for creating
domain definitions. A domain definition in VAX SQL
corresponds to a global field in the underlying
Rdb/VMS database.
For more information about domains, read the
following:
o The chapter on defining a database in the VAX SQL
User's Guide
o The sections on the CREATE, ALTER, and DROP DOMAIN
statements in Chapter 4 of the VAX SQL Reference
Manual
_____________________
1.1.3.4 Constraints
Version 2.0 supports SQL2 syntax for defining
constraints as part of CREATE and ALTER TABLE
statements. A constraint specifies a condition that
restricts the values that can be stored in a table.
VAX SQL supports these types of constraints:
o NOT NULL and NOT NULL UNIQUE constraints on column
definitions that restrict values in individual
columns to non-null values
o NOT NULL UNIQUE constraints on column definitions
that restrict values in individual columns to
unique values
o UNIQUE (column-list) constraints on an entire table
that restrict the values in the columns listed to
unique combinations
o CHECK (predicate) constraints on columns and
tables that specify a predicate that column values
inserted into the column or table must satisfy
For more information about constraints, read the
following:
o The chapter on defining a database in the VAX SQL
User's Guide
1-8
Release Notes for VAX SQL Version 2.0
o The sections on the CREATE and ALTER TABLE
statements in Chapter 4 of the VAX SQL Reference
Manual
_____________________
1.1.3.5 SET TRANSACTION Statement
Version 2.0 supports SQL2 syntax for specifying
transaction characteristics with the SET TRANSACTION
statement. SET TRANSACTION also has extensions
to the SQL2 syntax for DSRI-compliant databases.
SET TRANSACTION has the same options as DECLARE
TRANSACTION (which is still fully supported), but
unlike DECLARE TRANSACTION, SET TRANSACTION is an
executable statement that starts a transaction.
SET TRANSACTION is thus parallel with the START
TRANSACTION statement for RDO and RDML.
For more information about constraints, read the
following:
o The chapter on managing schema and transaction
context in the VAX SQL User's Guide
o The sections on the DECLARE and SET TRANSACTION
statements in Chapter 4 of the VAX SQL Reference
Manual
_____________________
1.1.3.6 Use of Column Select Expressions as
Value Expressions
Version 2.0 allows column select expressions wherever
syntax allows a value expression. Earlier versions
of VAX SQL allowed column select expressions only in
certain predicates.
Because value expressions must correspond to a single
value, a column select expression used as a value
expression should generate a one-value result table.
If the column select expression returns more than
a single value, SQL evaluates the value expression
based on the first value returned by the column select
expression. For the same reason, a column select
expression used as a value expression cannot include
GROUP BY or HAVING clauses.
1-9
Release Notes for VAX SQL Version 2.0
For more information, read the sections on value
expressions and column select expressions in Chapter 3
of the VAX SQL Reference Manual.
_____________________
1.1.3.7 BEGIN DECLARE and END DECLARE
Statements
The ANSI SQL standard requires that all host variables
referred to in an SQL statement be declared between a
BEGIN DECLARE statement and an END DECLARE statement.
Earlier versions of VAX SQL allowed these statements
to be declared anywhere in the program. VAX SQL
Version 2.0 gives a warning message under these
conditions:
o There is a BEGIN DECLARE . . . END DECLARE section.
o An embedded SQL statement refers to a host variable
that was declared outside of that section.
Programs with no BEGIN DECLARE . . . END DECLARE
section will continue to behave as they did in earlier
versions.
See the sections on the BEGIN and END DECLARE
statements in Chapter 4 of the VAX SQL Reference
Manual for more information.
_____________________
1.1.3.8 Fewer Restrictions on Updating Cursors
ANSI standard SQL specifies that the WHERE CURRENT OF
clause in UPDATE statements can refer to any cursors
that are not read only, and that any columns in the
table on which the cursor is based can be updated.
Earlier versions of VAX SQL allowed UPDATE statements
to refer only to cursors that included the FOR UPDATE
clause, and then only to the columns named in the FOR
UPDATE clause.
VAX SQL Version 2.0 allows updates of cursors that do
not have a FOR UPDATE clause. However, if a cursor has
a FOR UPDATE clause, only the columns listed in the
FOR UPDATE clause can be updated.
1-10
Release Notes for VAX SQL Version 2.0
See the sections on the DECLARE CURSOR and UPDATE
statements in Chapter 4 of the VAX SQL Reference
Manual for more information.
___________________________
VAX SQL Compatible with Version 5.0 of VMS
Version 2.0 of VAX SQL runs on Version 5.0 of VMS. It
also runs on Version 4.5 or later of VMS.
___________________________
IMPORT and EXPORT Statements Load and Unload Databases
The IMPORT and EXPORT statements load and unload
databases and allow you to make changes to Rdb/VMS
databases that cannot be made any other way. EXPORT
unloads a database to an .RBR file. IMPORT re-creates
the database with changes not allowed through ALTER
statements. The IMPORT statement allows you to do the
following:
o Convert a single-file database to multifile and
vice versa
o Change root file parameters you cannot change with
ALTER SCHEMA
o Change storage area parameters you cannot change
with ALTER SCHEMA
o Reload tables with existing rows to take advantage
of newly created hashed indexes
o Reload tables to take advantage of new or changed
storage maps
o Move a database to another directory or disk
structure
o Convert a database from another DSRI-compliant
implementation to an Rdb/VMS database
1-11
Release Notes for VAX SQL Version 2.0
See the section on the IMPORT and EXPORT statements
in Chapter 4 of the VAX SQL Reference Manual for more
detail.
___________________________
Support for Ada in the VAX SQL Precompiler
The SQL precompiler will now process VAX Ada programs
containing embedded SQL statements. See Chapter 5 in
the VAX SQL Reference Manual and Chapters 10 and 12 of
the VAX SQL User's Guide for information.
___________________________
Formatting Enhancements in Interactive SQL
Interactive SQL displays of data now reflect
formatting specified in EDIT STRING and QUERY
HEADER clauses in CREATE and ALTER TABLE and DOMAIN
statements. Those clauses formerly applied only to VAX
DATATRIEVE queries.
The EDIT STRING clause specifies how SQL displays
data stored in table columns. The QUERY HEADER clause
specifies headers for table columns. See the section
on formatting clauses in Chapter 3 of the VAX SQL
Reference Manual for more detail.
___________________________
RELEASE Statement Frees Resources in Dynamic SQL
The RELEASE statement releases all resources used by a
prepared statement in dynamic SQL. When you prepare an
SQL statement for dynamic execution, you cannot delete
any schema definitions (such as constraints, indexes,
or tables) referred to directly or indirectly by the
statement until you release the statement.
The RELEASE statement gives you a way to explicitly
release prepared statements. Note that SQL also
implicitly releases dynamic SQL statements in the
following circumstances:
1-12
Release Notes for VAX SQL Version 2.0
o After an EXECUTE IMMEDIATE statement
o When a PREPARE statement refers to an already
prepared statement name
o When the same authorization identifier is declared
for a schema referred to in a prepared statement
___________________________
QUIT Statement Exits Interactive SQL and Rolls Back
Changes
The QUIT statement stops an interactive SQL session,
rolls back any changes you have made, and returns you
to the DCL prompt.
___________________________
FINISH Statement Explicitly Detaches from Declared
Schemas
The FINISH statement detaches from declared schemas
when you are done working with them and frees the
authorization identifiers used by the declarations.
See the section on the FINISH statement in Chapter 4
of the VAX SQL Reference Manual for more detail.
___________________________
Interactive SQL EDIT Statement Can Now Invoke LSE
The EDIT statement in interactive SQL now invokes the
VAX Language-Sensitive Editor (LSE) if you define LSE
as the SQL$EDIT logical name. If LSE is installed on
your system, define the logical name SQL$EDIT:
$ DEFINE SQL$EDIT "LSE"
VAX SQL supports LSE only by allowing you to invoke
it with the EDIT statement. VAX SQL does not supply
language templates or support other features of LSE.
1-13
Release Notes for VAX SQL Version 2.0
___________________________
Interactive SQL SHOW Statement Gives More Information
Version 2.0 implements the following new SHOW
statements:
o SHOW STORAGE AREA
o SHOW STORAGE MAPS
In addition, the SHOW statements for other schema
definitions provide more detail than in previous
versions of SQL.
___________________________
COMMENT ON Statement Allows Comments on Indexes
The COMMENT ON statement now allows you to enter
comments that are displayed by the SHOW INDEX
statement.
___________________________
Support for CONTAINING, STARTING WITH, and UNIQUE
Predicates
Version 2.0 includes support for the CONTAINING,
STARTING WITH, and UNIQUE relational operators in
predicates. See the section describing predicates in
the VAX SQL Reference Manual for details.
___________________________
Additional Online Sample Programs
Version 2.0 includes the following sample programs in
the SQL$SAMPLE directory:
o SQL$LOAD_EMPLOYEES
Loads the EMPLOYEES table from a data file. Also
shows how to set a CHAR column to null based on a
field value (spaces) in the data file.
o SQL$LOAD_JOBHIST
1-14
Release Notes for VAX SQL Version 2.0
Loads the JOB_HISTORY table from a data file. Also
shows how to set a DATE column to null based on a
field value in the data file.
o SQL$REPORT
Writes a salary report with control breaks on
department code and job code to print average
salary per job code and total salary per
department. Also writes grand total of department
salary totals.
o SQL$TERMINATE
Illustrates the update necessary in the SQL_
PERSONNEL database when an employee is terminated.
(Uses host language statements to get EMPLOYEE_
ID value from terminal input, then starts a
transaction to change values in the WORK_STATUS
column of a row in EMPLOYEES, the JOB_END column
of a row in JOB_HISTORY, and the SALARY_END column
of a row in SALARY_HISTORY.) SQL$TERMINATE also
illustrates a variety of error-handling techniques,
such as accessing user-defined message files,
declaring and using symbolic error codes for
RDB$LU_STATUS, declaring and using SQLCODE, and
using the SQL$GET_ERROR_TEXT and SQL$SIGNAL calls.
o SQL$ALL_DATATYPES
Illustrates INSERT and UPDATE statements,
variations in null-handling techniques, and
SQL-host language data type correspondences and
conversions.
o SQL$DYNAMIC
Illustrates dynamic SQL. This program consists
of two files, SQL$DYNAMIC and SQL$DYNAMIC_DRIVER.
The Ada module language version of this program
provides a comprehensive example of a general
dynamic SQL application. The PL/I version provides
a more limited example.
1-15
Release Notes for VAX SQL Version 2.0
Table 1-1 shows which sample programs are available
for VAX SQL Version 2.0 in which languages.
Table 1-1 Language Sources Available for Sample
Programs
_______________________________________________________
________________________Precompiled___________Module_Language
Program_________COBOL__FORTRAN__PL/IC___Ada_Ada_PASCAL_ BASIC
SQL$LOAD_ Yes Yes Yes No No No No No
EMPLOYEES
SQL$LOAD_ Yes Yes Yes Yes Yes Yes No No
JOBHIST
SQL$REPORT Yes Yes Yes Yes Yes Yes No No
SQL$TERMINATE Yes Yes Yes Yes Yes Yes Yes Yes
SQL$ALL_ Yes Yes Yes Yes Yes Yes No No
DATATYPES
SQL$DYNAMIC No No Yes No No Yes No No
SQL$DYNAMIC_ No No Yes No No Yes No No
DRIVER
_______________________________________________________
___________________________
Other Technical Changes That Affect Applications
This section describes other features implemented for
VAX SQL Version 2.0.
1-16
Release Notes for VAX SQL Version 2.0
_____________________
1.1.16.1 Support for Rdb/VMS Database Keys
VAX SQL Version 2.0 supports database keys. Database
keys are internal pointers to specific table rows in
a database. Application programs can use the DBKEY
keyword in SQL statements to refer to the database key
for a table row.
SQL statements that retrieve rows by specifying their
database keys have the following advantages:
o Fast access: retrieval through database keys is
direct and bypasses any indexed or sequential
searches.
o Reduced locking of data: because access is direct,
the database system locks only the row retrieved.
o Assurance of uniqueness: within the database key
scope specified in the CREATE or DECLARE SCHEMA
statements, database keys are guaranteed to be
unique. This means singleton SELECT statements
based on database keys will never return more than
a single row, and that they will return the same
row, or an error if it was deleted.
You can specify the DBKEY keyword:
o As a select list item
o In a basic predicate that equates another value
expression to the DBKEY keyword
o In the RETURNING DBKEY clause in an INSERT
statement, which directs SQL to return the database
key for the row inserted
For more information about database keys, read the
following:
o The chapter on "Retrieving Data" in the VAX SQL
User's Guide
1-17
Release Notes for VAX SQL Version 2.0
o The section on database keys in Chapter 3 of the
VAX SQL Reference Manual, the section on the
DECLARE SCHEMA statement in Chapter 4 of the
Reference Manual, and the section on the INSERT
statement in Chapter 4.
_____________________
1.1.16.2 Declaring VIDA Databases Affects
Declared Transactions Differently
Version 2.0 of VAX SQL changes the way a DECLARE
SCHEMA statement for a VIDA database affects a
declared transaction.
In interactive and dynamic SQL, VAX SQL continues
to automatically extend the scope of a transaction
that is declared but not started to encompass schemas
declared after the transaction was declared.
However, earlier versions of VAX SQL always declared
default transaction characteristics for the additional
schemas (READ WRITE WAIT CONCURRENCY). For VIDA
databases, those defaults are inappropriate. In
Version 2.0 of VAX SQL, when you issue a DECLARE
SCHEMA statement after a transaction is declared,
VAX SQL checks to see if the declared schema is
a VIDA database. If it is a VIDA database, SQL
declares transaction characteristics of READ ONLY WAIT
CONSISTENCY LEVEL 2.
_____________________
1.1.16.3 ALTER TABLE ALTER No Longer Requires
Data Type
Version 2.0 removes the restriction that ALTER TABLE
statements that alter a column definition must always
specify a data type clause, even if the data type is
not changing.
1-18
Release Notes for VAX SQL Version 2.0
_____________________
1.1.16.4 C Precompiler Support for Pointer
Variables and Block Structure
The SQL precompiler now supports block structure and
pointer variables in C programs containing embedded
SQL statements. See the chapters describing the
precompiler in the VAX SQL Reference Manual and VAX
SQL User's Guide for details.
_____________________
1.1.16.5 SQL No Longer Performs Text Arithmetic
Earlier versions of SQL allowed text fields, literals,
and parameters to be used in arithmetic expressions.
Version 2.0 no longer allows text arithmetic.
_____________________
1.1.16.6 DECLARE SCHEMA PATHNAME Now Uses Fully
Expanded File Specification
The DECLARE SCHEMA PATHNAME statement now uses the
complete file specification stored in the data
dictionary during the schema's creation. Previous
versions of SQL used the file specification as it
was entered in the CREATE SCHEMA (CREATE DATABASE)
statement. This change makes DECLARE SCHEMA consistent
with the Rdb/VMS INVOKE DATABASE statement and avoids
problems like the following:
$ MCR RDO
RDO> INVOKE DATABASE P1 = PATHNAME 'UPR_DATABASE'
RDO> EXIT
$ MCR SQL$
SQL> DECLARE SCHEMA PATHNAME 'UPR_DATABASE';
%SQL-F-NOT_A_DB, SYS$COMMON:[CDDPLUS]BRAD.UPR_DATABASE;1 is not the name of a
-SQL-F-ERRATTDEC, Error attaching to declared schema SYS$COMMON:[CDDPLUS]BRAD.
Note that this change is incompatible with Version
1 of VAX SQL, because earlier DECLARE DATABASE
statements would translate logical names in the file
specification as it was entered in CREATE SCHEMA
(CREATE DATABASE). Version 2.0 will not.
1-19
Release Notes for VAX SQL Version 2.0
_____________________
1.1.16.7 MCS Characters Permitted in
User-Supplied Names
Prior versions of VAX SQL did not permit the use of
Digital's Multinational Character Set in user supplied
names. These characters are now permitted.
Note, however, that MCS collation is not implemented.
For example, when sorting is specified for a field,
the values are sorted according to the ASCII character
set; also, ASCII sequence is used for the output of
SHOW statements.
_____________________
1.1.16.8 Support for Rdb/VMS Version 3.0
Requires Conversion of Databases
VAX SQL Version 2.0 requires Version 3.0 of VAX
Rdb/VMS.
Before you use VAX SQL Version 2.0, you must convert
databases created with Version 1.0 or Version 1.1 to
a structure compatible with Version 3.0 of Rdb/VMS.
You must install Version 3.0 of Rdb/VMS before running
Version 2.0 of VAX SQL.
Before using Version 2.0 of VAX SQL, follow these
steps:
1 Make sure all existing Rdb/VMS databases are
recovered before installing the Version 3.0
software. The recovery-unit journal (.RUJ) files
created prior the Version 3.0 cannot be applied
to Version 3.0 Rdb/VMS databases due to internal
structure differences.
2 Back up all existing databases by using the the VMS
Backup utility. For any existing databases that you
plan to restructure into multifile databases, use
the Version 2.3 RDO BACKUP statement to back them
up after using the VMS Backup utility.
3 For any databases created using CDD, use the DELETE
PATHNAME statement in Version 2.3 of RDO to delete
the path names from the dictionary.
1-20
Release Notes for VAX SQL Version 2.0
4 Install VAX Rdb/VMS Version 3.0.
5 Use RMU/CONVERT to convert V2.3 single-file
databases into Version 3.0 single-file databases.
Use the RDO IMPORT statement to convert and
restructure a V2.3 single-file database into a
Version 3.0 multifile database.
Note: Once a database file is converted to a later
version, you cannot use that database file with an
earlier version of Rdb/VMS or VAX SQL.
_____________________
1.1.16.9 Installing SQL as Shareable Image Uses
More Global Pages
Version 1.1 of VAX SQL used 1500 global pages of
memory if it was installed as a shareable image.
Version 2.0 requires 2000 global pages.
__________________________________________________________________
Bugs Fixed in VAX SQL Version 2.0
The following bugs are fixed in Version 2.0:
o Select expressions containing the GROUP BY clause
were unacceptably slow in earlier versions of
VAX SQL. GROUP BY performance in Version 2.0 is
substantially better.
Note that views that have the GROUP BY clause in
their definitions must be dropped and created
again to take advantage of the improved GROUP BY
performance.
o In earlier versions of VAX SQL, the C precompiler
did not correctly handle character strings passed
as an argument to a procedure. For example, the
following procedure gave unpredictable results:
bugexample(stringarg)
char stringarg[100];
{ exec sql execute immediate stringarg; }
Version 2.0 fixes the problem.
1-21
Release Notes for VAX SQL Version 2.0
o In Version 1.1, dynamic SQL compiled and released
the request for a prepared non-SELECT statement
every time the statement was dynamically executed.
This redundant compilation and release of requests
hurt performance.
In Version 2.0, dynamic SQL compiles the request
the first time a statement executes and releases
the request only when a PREPARE statement executes
for the same statement name.
Note that the redundant compilation and release
of requests was only a problem with dynamically
executed non-SELECT statements in Version 1.1.
SQL correctly handled dynamically-executed SELECT
statements.
o In earlier versions of VAX SQL, interactive and
dynamic SQL queries did not always correctly
determine the size of arithmetic expression
results. For instance:
SQL> SH TABLE xx
Columns for table XX
C1 SMALLINT
C2 INTEGER
No Constraints found
No Indexes Found
SQL> SELECT C1, C2, C1+C2 FROM XX;
C1 C2
32000 999999999 100003
1 row(s) selected
This problem is fixed in Version 2.0.
o Dynamic INSERT or UPDATE statements no longer
generate a bugcheck dump when they include
parameter markers in the value expression to be
assigned to a column.
1-22
Release Notes for VAX SQL Version 2.0
o COBOL allows names that begin with numbers, such
as 001-MY-VAR. Earlier versions of VAX SQL only
supported such names as paragraph or section names
in the WHENEVER statement. Version 2.0 of VAX SQL
supports such names without restriction.
o Earlier versions of VAX SQL did not recognize
the TPU$SECTION logical name used by VAXTPU-
based editors with Version 5 of VMS. Version 2.0
recognizes the TPU$SECTION logical as well as the
TPUSECINI logical name used by VAXTPU editors on
VMS Version 4 systems.
o VAX SQL now uses fully expanded file names stored
in the data dictionary for DECLARE SCHEMA PATHNAME
statements. See Section 1.1.16.6 for details.
o Earlier versions of VAX SQL had a problem that
sometimes generated warnings when programs linked:
LINK-I-BASDUERRS, basing image due to error to relocatable reference
LINK-W_ADRWRTDAT, adress in shareable writeable section in psect
RDB$VARIABLES offset %X00000014 in module SQL$EXH
file sys$common:[SYSLIB]SQL$USER.OLB;3
This problem is fixed in Version 2.0.
o In earlier versions, the SQL precompiler generated
errors if the logical name SQL$DATABASE was not
defined and the program did not explicitly declare
a database. This problem is fixed in Version 2.0.
o The DROP DATABASE statement in earlier versions
of VAX SQL generated an incorrect %LIB-F-SIGNO_ARG
error if it referred to a schema that was opened
but not attached (such as through the Rdb/VMS RMU
SHOW STATISTICS command). The DROP SCHEMA statement
now gives a correct error message.
1-23
Release Notes for VAX SQL Version 2.0
o In earlier versions, the SQL precompiler
incorrectly retrieved values from VARCHAR columns
not created or declared in a program. Instead of
retrieving the actual length of values from VARCHAR
columns in a database, the precompiler incorrectly
converted the VARCHAR field to a CHAR field of the
maximum length for the VARCHAR field and assigned
the CHAR field to the host variable specified in
the FETCH statement.
o In earlier versions, the SQL precompiler
incorrectly processed INSERT statements that
attempted to store null values from a text host
variable in a DATE column. The precompiler ignored
negative values in the indicator variable for the
main variable associated with the DATE column and
generated an error when it attempted to store
a spurious value in the column. This problem is
fixed.
__________________________________________________________________
Known Problems with VAX SQL Version 2.0
This section lists known problems with Version 2.0 of
VAX SQL.
___________________________
No Data Type Checking on WITH LIMIT Clause
If you use one or more WITH LIMIT OF clauses with the
CREATE STORAGE MAP or ALTER STORAGE MAP statement, or
with the CREATE INDEX or ALTER INDEX statement, SQL
does not check whether the limit specification uses a
data type incompatible with the column definition.
Therefore, you must be sure to use the proper
specification.
1-24
Release Notes for VAX SQL Version 2.0
In the following example, for instance, the quotation
marks around the limits are necessary because the
domain on which EMPLOYEE_ID is based is defined as
text, not numeric.
CREATE STORAGE MAP EMPLOYEE_MAP FOR EMPLOYEES
STORE USING (EMPLOYEE_ID)
IN EMPID_LOW WITH LIMIT OF ("00200")
IN EMPID_MID WITH LIMIT OF ("00500")
OTHERWISE IN EMPID_OVER;
If there is an incompatibility between the data type
specified in the WITH LIMIT OF clause and the column
definition, no error is returned, and unpredictable
results can occur in the storage of records for the
relation.
___________________________
REDEFINES Clause Not Supported in COBOL Declarations
Embedded SQL statements in precompiled COBOL programs
cannot refer to fields in COBOL host structures
declared with the REDEFINES clause. For example:
01 F EXTERNAL.
02 F1 PIC S9(4) COMP.
02 F2 PIC X(20).
02 F2_R REDEFINES F2.
* SQL statements cannot refer to REDEFINES
* structures such as F2_R and its subfields. However, SQL
* statements can refer to F2 or F, and F2_R can
* be used by COBOL statements.
03 G1 PIC X(10).
03 G2 COMP-2.
02 F3 PIC S9(9) COMP.
1-25
Release Notes for VAX SQL Version 2.0
___________________________
SHOW DICTIONARY Does Not Always Display Correct Path
Name
The SHOW DICTIONARY statement does not display the
correct data dictionary path name after you issue a
SET DICTIONARY statement. In addition, the display
from SHOW DICTIONARY does not translate the logical
name CDD$DEFAULT and displays several blank lines.
___________________________
CREATE SCHEMA Statement in Programs Must be First
Definition
The SQL precompiler and module processor incorrectly
give errors if they encounter a CREATE SCHEMA after
other CREATE statements in a program or SQL module
file. CREATE SCHEMA statements should not have to
precede other CREATE STATEMENTS, but unless they
do, SQL generates these errors when it processes the
program or SQL module file:
EXEC SQL CREATE DATABASE ZZZ;
%SQL-F-TRAACT_1, A schema cannot be created while a transaction is active
-SQL-F-TRAACT_2, ROLLBACK or COMMIT the transaction
To work around this problem, make sure CREATE SCHEMA
statements are the first CREATE statements in program
or SQL module files.
___________________________
DDL Statements Cannot Refer to Objects Before Their
Creation
CREATE SCHEMA and CREATE TABLE statements in programs
must precede in the source file all other data
definition statements that refer to the schema or
table, respectively.
1-26
Release Notes for VAX SQL Version 2.0
This known problem has been made less restrictive
in Version 2.0. Earlier versions of VAX SQL did not
allow DECLARE DATABASE, DECLARE CURSOR, and DECLARE
TRANSACTION statements to refer to tables or schemas
before CREATE statements for the table or schemas.
Now, data manipulation statements can precede data
definition statements to which they refer.
__________________________________________________________________
Rdb/VMS Features Not Supported by VAX SQL
VAX SQL is an alternative to DIGITAL proprietary
interfaces to DSRI-compliant database systems,
including VAX Rdb/VMS. It is a goal of VAX SQL to
support all the features of the DIGITAL proprietary
interfaces to Rdb/VMS. However, VAX SQL does not
support these features available in Rdb VMS Version
3.0:
o Segmented string definition or retrieval
o VALID IF clauses in domain (global field)
definitions
o String concatenation
o Any construct comparable to nested FOR loops
o FIRST n row retrieval from tables
o User-supplied request and transaction handles
o COMPUTED BY columns in table definitions (you can
work around the lack of this feature by creating
a view with a column based on an arithmetic
expression)
1-27
Release Notes for VAX SQL Version 2.0
__________________________________________________________________
Documentation for VAX SQL Version 2.0
All documentation for VAX SQL was substantially
revised and updated for Version 2.0:
o VAX SQL User's Guide
All chapters were revised to reflect changes and
new features. In particular, Part IV, Application
Programming, was reorganized and expanded. It now
contains the following chapters:
- Developing Host Language Programs
Provides general information about including SQL
statements in programs and introduces subsequent
chapters
- Creating and Processing Programs with the SQL
Precompiler
Discusses considerations specific to developing
precompiled programs
- Creating and Processing Programs with the SQL
Module Processor
Describes how to write SQL modules, call them
from programs, and process them with the SQL
module processor
- Creating an Image
Discusses image creation and program debugging
- Declaring and Using Parameters
Describes steps to ensure data type
compatibility
- Handling Run-Time Errors and the ROLLBACK
Statement
Describes how to detect run-time errors,
retrieve the accompanying error messages,
and either recover from errors or roll back a
transaction
1-28
Release Notes for VAX SQL Version 2.0
o VAX SQL Reference Manual
- New sections in Chapter 4 on all new statements
- New chapter on SQL module language
- New chapter on SQL precompiler that describes
precompiler command line syntax and contains
sections describing supported host language
variable declarations (these sections were
formerly part of the chapter on language and
syntax elements)
- New appendix listing logical names that SQL
recognizes
- Other chapters and appendixes revised to reflect
new features
o VAX SQL Installation Guide
Updated appendixes showing new sample installations
and listings of files created by the installation
o Online Help in interactive SQL
Revised to reflect new statements and features
o Online error message documentation
Incorporates new and changed error messages
o Online sample programs:
Table 1-1 shows which programs are available for
Version 2.0.
Because a goal of VAX SQL is to comply with the ANSI
SQL standard, a number of keywords change in Version
2.0 to different, ANSI-compliant keywords that are
the equivalent of the old keywords. Although the old
keywords are still supported for compatibility with
old versions, documentation for Version 2.0 does
not describe the old keywords and uses only the new
keywords and terminology.
1-29
Release Notes for VAX SQL Version 2.0
In addition, some other keywords and terminology
changed to make their meaning clearer.
Table 1-2 summarizes the changes in keywords and
terminology.
Table 1-2 Changes in Keywords and Terminology for
Version 2.0
_______________________________________________________
Old_Keyword_or_Term_____________New_Keyword_or_Term____
DATABASE SCHEMA
DB_HANDLE AUTHORIZATION
db-handle auth-id
(in syntax diagrams and
arguments)
database handle authorization
identifer
authorization id
READ_WRITE READ WRITE
READ_ONLY READ ONLY
CONCURRENCY CONSISTENCY LEVEL 2
CONSISTENCY CONSISTENCY LEVEL 3
OUTPUT SELECT LIST
(PREPARE, DESCRIBE statements)
output parameter select list item
(PREPARE, DESCRIBE statements)
INPUT MARKERS
(DESCRIBE statement)
1-30
Release Notes for VAX SQL Version 2.0
Table 1-2 (Cont.) Changes in Keywords and Terminology
for Version 2.0
_______________________________________________________
Old_Keyword_or_Term_____________New_Keyword_or_Term____
input parameter parameter marker
(DESCRIBE statement)
host variable parameter
(when used to refer
to both host variables
in programs and actual
or formal parameters
in programs and SQL
module procedures)
plan file context file
_______________________________________________________
___________________________
Documentation Errors Omissions
This section describes errors and omissions in the VAX
SQL Version 2.0 documentation set.
_____________________
1.5.1.1 STORE Clause Syntax of Storage Map and
Associated Index Should Not Always Be
Identical
The VAX SQL User's Guide and VAX SQL Reference Manual
contain text and examples that incorrectly describe
the relationship between a storage map that includes a
PLACEMENT VIA INDEX clause and the STORE clause in the
associated index definition.
The Reference Manual syntax for the CREATE INDEX
statement correctly indicates that the STORE RANDOMLY
ACROSS clause cannot be included in definitions of
storage maps for hashed indexes. The Reference Manual
also says that when STORE RANDOMLY ACROSS is specified
in table storage maps, it means that rows are stored
randomly among the listed areas. Note that it is
the selection of the area that is random, not the
1-31
Release Notes for VAX SQL Version 2.0
selection of the page in the area. At least in storage
areas with MIXED page format, pages are filled in
sequential order.
However, the Reference Manual fails to note that the
preceding description of the STORE RANDOMLY ACROSS
clause applies only when the table storage map does
not include a PLACEMENT VIA INDEX clause. When a
storage map meets all the following conditions, the
rows are rows are not stored randomly at all.
o It specifies the STORE RANDOMLY ACROSS (area-name-
list) clause
o It also specifies the PLACEMENT VIA INDEX clause
o The area-name-list in the STORE RANDOMLY ACROSS
clause has the same number of areas as does the
STORE clause in the index definition
Under these conditions, instead of random placement,
row storage is controlled by the PLACEMENT VIA INDEX
clause.
Furthermore, both the Reference Manual and the User's
Guide state that if a table storage map specifies a
hashed index in a PLACEMENT VIA INDEX clause, then
the STORE clause of the table storage map should be
identical to the STORE clause of the hashed index
definition. However, this is not true when index and
table are partitioned across multiple storage areas.
Consider the following example, which is typical of
the multifile database definitions included in the VAX
SQL User's Guide:
CREATE ind1 INDEX ON table1 (col1)
TYPE IS HASHED
STORE USING (col1)
IN area1 WITH LIMIT OF (lit1)
IN area2 WITH LIMIT OF (lit2)
OTHERWISE IN area3;
1-32
Release Notes for VAX SQL Version 2.0
CREATE STORAGE MAP map1 FOR table1
STORE USING (col1)
IN area1 WITH LIMIT OF (lit1)
IN area2 WITH LIMIT OF (lit2)
OTHERWISE IN area3;
PLACEMENT VIA INDEX ind1;
In the preceding example, the STORE USING clause,
which must be followed by both IN, WITH LIMIT OF, and
OTHERWISE sections, is the only option for defining
the hashed index. However, redundant specification of
USING and WITH LIMIT OF sections in the table storage
map imposes an unnecessary performance penalty when
the table is loaded.
Assuming the same index definition, the following
storage map definition produces the same row placement
results as the preceding one, but without adding a
performance penalty to the load operation:
CREATE STORAGE MAP map1 FOR table1
STORE RANDOMLY ACROSS (area1,area2,area3)
PLACEMENT VIA INDEX ind1;
SQL documentation will be corrected in a future
release. In addition, expect that the keyword
RANDOMLY, which is now required, may become optional
in future releases of VAX SQL software.
__________________________________________________________________
Rdb/VMS Bugs Fixed That Affected VAX SQL
The following bugs that affected VAX SQL are fixed in
Version 3.0 of Rdb/VMS:
o A view that based on a SELECT statement that used a
subquery as a value expression produced incorrect
results. For instance, this view used to return
rows even though its result table was empty:
1-33
Release Notes for VAX SQL Version 2.0
SQL> CREATE VIEW BUM_VIEW AS
cont> SELECT * FROM SALARY_HISTORY WHERE EMPLOYEE_ID <>
cont> (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE
cont> FIRST_NAME = 'noname');
This problem is fixed.
o SELECT statements that included a GROUP BY clauses
with complex HAVING clause gave incorrect results.
For instance, the following query used to return
blanks instead of values:
SQL> select snum, pnum
cont> from spj
cont> group by snum, pnum
cont> having count(*) = 4 and count(distinct jnum) = 3 and
cont> count(distinct qty) = 3 and avg(qty) = 375 and
cont> avg(distinct qty) = (466+(2/3)) and max(qty) = 1000 and
cont> min(qty) = 100 and sum(qty) = 1500 and
cont> sum(distinct qty) = 1400 and max(jnum) = 'JZ' and
cont> min(jnum) = 'JX';
SNUM PNUM
..... ......
1 row(s) selected
SQL>
This problem is fixed.
o VAX SQL returns the number of rows affected after
INSERT, UPDATE, and DELETE statements execute.
It assigns the value to the third element of the
SQLERRD array in the SQLCA. However, because of a
bug, this field was not updated after the second
and subsequent times SQL executed a particular
statement. This problem is fixed.
o The following example shows a view definition and
a query based on that view. The query formerly
generated a bugcheck dump:
1-34
Release Notes for VAX SQL Version 2.0
SQL> CREATE VIEW TOTAL_SALARY_BUDGET (GRAND_TOTAL)
cont> AS SELECT SUM (SALARY_AMOUNT)
cont> FROM SALARY_HISTORY
cont> WHERE SALARY_END IS NULL;
SQL> !
SQL> SELECT JH.DEPARTMENT_CODE,
cont> (SUM (SH.SALARY_AMOUNT) / TSB.GRAND_TOTAL) * 100,
cont> "%"
cont> FROM JOB_HISTORY JH, SALARY_HISTORY SH, TOTAL_SALARY_BUDGET TSB
cont> WHERE SH.SALARY_END IS NULL
cont> AND JH.JOB_END IS NULL
cont> AND SH.EMPLOYEE_ID = JH.EMPLOYEE_ID
cont> GROUP BY JH.DEPARTMENT_CODE, TSB.GRAND_TOTAL
cont> ORDER BY JH.DEPARTMENT_CODE
cont> ;
o SELECT statements that use a subquery as a value
expression no longer produce incorrect results if
the result table for the subquery is empty.
o Schemas created with the SNAPSHOT IS DISABLED
clause causedan access violation error if you
declared a READ ONLY transaction on the schema and
issued an INSERT or UPDATE statement. This problem
is fixed.
o SELECT statements that contained both a DISTINCT
clause and subqueries in the WHERE or HAVING
clauses sometimes produced incorrect results. This
problem is fixed.
__________________________________________________________________
Known Problems with Rdb/VMS That Affect VAX SQL
This section describes problems related to Rdb/VMS
Version 3.0 that affect VAX SQL. These problems will
be addressed in a release of Rdb/VMS subsequent to
Version 3.0.
1-35
Release Notes for VAX SQL Version 2.0
___________________________
Precompiled Cursors with Many Functions Can Cause
Bugcheck Dump
OPEN statements in precompiled programs may cause
bugcheck dumps if the cursor to which they refer
are based on a SELECT expression that contain many
function and host variable references. For example,
opening the cursor declared in the following example
generates a bugcheck dump.
EXEC SQL DECLARE X2 CURSOR FOR
SELECT MAX(QTY),
MAX(DISTINCT QTY),
MAX(DISTINCT JNUM),
MAX(QTY * 4),
MAX(QTY + 4),
MAX(QTY * 5),
MAX(QTY * :m1),
MAX(QTY + :m1),
MAX(QTY * :m2),
MAX(QTY * (SELECT WEIGHT FROM P WHERE P.PNUM = SPJ.PNUM) ),
MAX(QTY * (SELECT WEIGHT * 454 FROM P WHERE P.PNUM=SPJ.PNUM) ),
MIN(QTY),
MIN(DISTINCT QTY),
MIN(DISTINCT JNUM),
MIN(QTY * 4),
MIN(QTY + 4),
MIN(QTY * 5),
MIN(QTY * :m1),
MIN(QTY + :m1),
MIN(QTY * :m2),
MIN(QTY * (SELECT WEIGHT FROM P WHERE P.PNUM = SPJ.PNUM) ),
MIN(QTY * (SELECT WEIGHT * 454 FROM P WHERE P.PNUM=SPJ.PNUM) )
FROM SPJ
GROUP BY PNUM;
1-36
Release Notes for VAX SQL Version 2.0
___________________________
Complex Join with GROUP BY Clause Can Cause Bugcheck
Dump
The following SELECT statement produces a bugcheck
dump:
SELECT COUNT(*), AVG(C.SALARY_AMOUNT)
FROM EMPLOYEES E, CURRENT_SALARY C, CURRENT_JOB D
WHERE E.EMPLOYEE_ID=C.EMPLOYEE_ID AND E.EMPLOYEE_ID=D.EMPLOYEE_ID
GROUP BY DEPARTMENT_CODE;
___________________________
Conversion of Null CHAR Column to DATE Gives Invalid
Error
The following example illustrates that attempts
to convert a null CHAR column to a DATE column
incorrectly generate a truncation error:
SQL> CREATE TABLE T (T CHAR(16));
SQL> CREATE TABLE D (D DATE);
SQL> INSERT INTO T (T) VALUES (NULL);
1 row(s) inserted
SQL> INSERT INTO D (D) SELECT T FROM T;
%RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime
-SYSTEM-F-IVTIME, invalid time
___________________________
GROUP BY Query Based on Multiple Views Causes Bugcheck
Dump
Using the GROUP BY clause to refer to columns in views
that are themselves based on other views may cause a
bugcheck dump:
SQL> SELECT DEPARTMENT, SUM(SALARY)
cont> FROM CURRENT_INFO
cont> GROUP BY DEPARTMENT;
[Bugcheck dump here]
1-37
Release Notes for VAX SQL Version 2.0
%C74-W-NOMSG, Message number 001DF580
%RDB-E-BAD_REQ_HANDLE, invalid handle for request
You can work around this problem by not using view
columns in a GROUP BY clause that are based on other
view columns. For instance, the following statement
is equivalent to the previous statement but does not
encounter the problem:
SQL> SELECT D.DEPARTMENT_NAME,
cont> SUM (S.SALARY_AMOUNT)
cont> FROM DEPARTMENTS D,
cont> SALARY_HISTORY S,
cont> JOB_HISTORY JH
cont> WHERE D.DEPARTMENT_CODE = JH.DEPARTMENT_CODE
cont> AND JH.EMPLOYEE_ID = S.EMPLOYEE_ID
cont> AND S.SALARY_END IS NULL
cont> GROUP BY D.DEPARTMENT_NAME;
___________________________
Subqueries Referring to Views May Cause Bugcheck Dump
In some cases, a subquery that includes a reference to
a view causes an Rdb/VMS bugcheck dump.
1-38