[Search for users]
[Overall Top Noters]
[List of all Conferences]
[Download this site]
| Title: | DEC Rdb against the World |
|
| Moderator: | HERON::GODFRIND |
|
| Created: | Fri Jun 12 1987 |
| Last Modified: | Thu Feb 23 1995 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 1348 |
| Total number of notes: | 5438 |
913.0. "SQL/DS place" by DATABS::JOEDAD::NEEDLEMAN (today nas/is, tomorrow...) Fri Apr 19 1991 15:44
From: CIVIC::CIS_PROD "18-Apr-1991 1033" 18-APR-1991 11:07:24.85
To: PBSVAX::NEEDLEMAN
CC:
Subj: CIS DELIVERS ... SQL/DS Version 1 Release 2 from IBM
----- SQL/DS Version 1 Release 2 from IBM
|C I S|
-----
Author: Alban, H. Source : MIC
Type : Research Service
Date : 01-MAR-90
Report ID: 3017648
Section Topic
------- -----------------------------------------------------------------------
Intro Summary of Report (Supplied by CIS Editor)
1 IBM's RDBMS for VM and VSE
2 Operating Environments
3 Operating Modes
4 Single User Mode
5 Multiple User Mode
6 Multiple Database Mode
7 Distributed Database
8 Database Architecture
9 Data Types
10 Field Attributes
11 Views
12 Referential Integrity
13 Physical Storage - Storage Structures
14 Clustering
15 Clustered Indexes
16 Physical Integrity and Security
17 Catalog
18 Performance
19 Report Writing
20 Application Programming
21 Preprocessors
22 Dynamically Defined SQL Commands
23 Extended Dynamic Statements
24 Interface for VSAM
25 Multiple Language Support
26 Pricing
27 Table 1 - SQL/DS Pricing
28 Evaluation
29 Comparison with DB@
+ - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - +
| Please be advised that the information contained within this |
+ report is copyrighted material. The following policies must +
| be adhered to: |
+ +
| - No reformatting of the data segments |
+ - No external distribution +
| - Internal use only in accordance with vendor agreements |
+ - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - +
MIC
( 1 )
----- SQL/DS Version 1 Release 2 from IBM
|C I S|
-----
Author: Alban, H. Source : MIC
Type : Research Service
Date : 01-MAR-90
Report ID: 3017648
30 Conclusion
31 Table 2 - SQL/DS Compared to DB@
+ - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - +
| Please be advised that the information contained within this |
+ report is copyrighted material. The following policies must +
| be adhered to: |
+ +
| - No reformatting of the data segments |
+ - No external distribution +
| - Internal use only in accordance with vendor agreements |
+ - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - +
MIC
( 2 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
Intro: Summary of Report (Supplied by CIS Editor)
SQL/Data System (SQL/DS) is IBM's relational database management system for
the VM and VSE environments. It is designed for both interactive and batch
applications. Enhancements focus on ease of in- stallation and operation,
since many VM installations do not have a full-time programming department.
Because it implements SAA and participates in IBM's integrated distributed
database architecture, refinements and extensions to SAA's Database Manager
will be reflected in new releases of SQL/DS. SQL/DS provides the following
facilties: the SQL programming language; the interactive SQL language;
preprocessors for Cobol, C, Fortran, PL/1, and Assembler; extended dynamic SQL
for preprocessing SQL requests and the SQL/DS database services utility.
Section: 1 IBM's RDBMS for VM and VSE
SQL/Data System (SQL/DS) is IBM's relational database management system (DBMS)
for the VM and VSE environments. It is designed for both interactive and
batch applications. Enhancements focus on ease of installation and operation,
since many VM installations do not have a full-time programming department.
SQL/DS occupies a strategic position for the SAA-implementing VM operating
system and is comparable to DB2 for MVS environments. Both were built along
the same R* System model. It implements SAA and participates in IBM's
integrated distributed database architecture. Therefore, refinements and
extensions to SAA's Database Manager will be reflected in new releases of
SQL/DS. By the same token, SQL/DS benefits from products that comply with SAA
standards in the areas of communication, connectivity, and user interface.
Furthermore, SQL/DS is an early provider of distributed database, capable of
executing a remote unit of work as well as some remote operations.
To ensure the mutual compatibility of DBMS products on the four SAA platforms
(MVS, VM, OS/400, and OS/2), IBM has formed its internal SQL Language Council.
Representatives from the four relational DBMSs must agree to some level of
compliance before any change is made to the SQL language of any one product.
SQL/DS is now present in over 7,000 installations. These installations are
generally concentrated in large companies. IBM now wants better penetration
into mid-size and small companies. IBM recognizes that many of these
companies do not have a full-time database administrator and, therefore,
require systems that are easy to install, monitor, and administer, and that do
not require much training. (For example, space management is easier on SQL/DS
than on DB2.) Enhancements simplify operations and ease of use. Since SQL/DS
will be used extensively for information-center type functions, performance
enhancements tend to favor better response time rather than better overall
MIC
( 3 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
throughput.
SQL/DS provides the following facilities:
o The SQL programming language for data definition, data control, and data
manipulation. IBM's SQL language is developed "with consideration to" the
ANSI standard.
o The Interactive SQL (ISQL) language that adds report formatting commands to
the basic SQL data language.
o Preprocessors for COBOL, C, FORTRAN, PL/I, and Assembler.
o Extended Dynamic SQL for preprocessing SQL requests that are not part of a
3GL application, or for creating preprocesors for other languages.
o The SQL/DS Database Services Utility.
SQL/DS can be used in conjunction with QMF, CSP, DBRAD, and other IBM
productivity tools.
Section: 2 Operating Environments
Under VM/SP, SQL/DS Version 2 Release 2 requires VM/SP Release 5 or later
(with or without HPO). The processors must support conditional swapping and
extended precision floating point. A minimum of 3MB real memory is required.
Application programs accessing SQL/DS operate in virtual machines and can be
interactive (online) or noninteractive (batch). These programs are controlled
by CMS, by CICS/ VM, or by equivalent facilities. When VM/ XA is used,
application programs can employ 24-bit or 31-bit addressing and can operate in
virtual machines with over 16MB of memory.
Each SQL/DS database machine operates against only one database at a time.
All tables, indexes, and access modules (automatically generated during
program preprocessing) that are used together must be held in a single
database. Multiple databases, each with its own database machine, can run
simultaneously.
Under VSE, SQL/DS 2.2 requires VSE/Advanced Functions Version 2.1 or later
with VSE/VSAM Release 3 or later. CICS/DOS/ VS Version 1.6 is required for
online support and ISQL, and Version 1.7 is required for double byte character
set (DBCS) characters.
MIC
( 4 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
VSE/AF can run as a guest operating system under VM. When this is the case,
VM/SP and VSE users and application programs can share a VM/SP-controlled
SQL/DS database.
Section: 3 Operating Modes
SQL/DS can run in single user mode, multiple user mode, or multiple database
mode.
Section: 4 Single User Mode
In this mode, a single application performs in the same virtual machine as
SQL/DS, avoiding communication, recovery, and sharing (locking) overhead.
This exceptional mode of operation is useful for special SQL/DS Database
Maintenance Services utilities used during installation and for loading or
unloading bulk data.
In single-user mode, only one application can access SQL/DS at a time, and it
must be running in the same virtual machine as SQL/DS. This mode is useful
for running high-speed batch updates to a database.
Section: 5 Multiple User Mode
In multiple user mode, several users or applications can concurrently access
the same database. In this mode, SQL/DS executes in its own virtual machine
while applications execute in their own virtual machines. Users can
communicate with SQL/DS through a system-dependent communication facility or
link. The link is transparent to applications.
Isolating SQL/DS improves security and integrity; users and applications
cannot inadvertently or maliciously modify SQL/DS code, data, or a database.
Section: 6 Multiple Database Mode
Multiple database support allows several SQL/ DS databases to operate at the
same time. Different databases can be set up for different application areas
or user groups, for access in either single or multiple user mode.
Multiple user mode applications can be written to access more than one
database. A database switching facility allows a single SQL/DS application to
access several databases. The switch is invoked through the SQL/DS CONNECT
statement. Only one database, local or remote, can be connected at a time;
MIC
( 5 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
the application must disconnect from one database before accessing another.
Database switching requires VM/SP Release 5 or later in multiple user mode.
VM/XA supports multiple databases on a single system, but applications
cannot switch databases dynamically.
Multiple database support improves SQL/DS availability since database
maintenance on one database does not affect normal user access to other
databases. Multiple database support allows different groups of users to each
have their own independent database. For example, one group may have a test
database while another has a production database.
Section: 7 Distributed Database
In explaining its distributed database strategy, IBM has defined four levels
of distributed database capability: remote request, remote unit of work,
distributed unit of work, and distributed request (a request is defined as a
single SQL statement).
SQL/DS supports the remote unit of work. The remote request level enables a
user to read and update data at a single remote system. The user can also
group several requests into a unit of work and transmit the unit of work to a
remote site for execution. However, the entire unit of work can access only
one remote site. An application can read or update data at several locations,
but all requests within each unit of work must be executed on a single
machine.
In addition, SQL/DS can perform some administrative functions remotely. For
example, a single DBA can ship an application to another SQL/DS site without
the need for recompile. It is also possible for a central site to coordinate
backup of remote VM-SQL/DS DBMSs.
IBM has established a naming convention that has raised some question about
the quality of the location transparency that its distributed database offers.
Specifically, the naming convention for distributed database includes the
location of a physical table as part of its name.
Traditional, non-distributed IBM relational databases uses a two-part scheme
for naming tables that generally includes a user ID. In DB2, for example,
table names not explicitly qualified by the user are implicitly qualified by
DB2. To illustrate, if user SMITH creates a table named ABC with no
qualifier, DB2 uses SMITH as the qualifier and considers that the table name
is SMITH.ABC. User SMITH cannot own another table or view called ABC. A
different SQL ID (say, JONES) can create another table or view ABC; DB2
MIC
( 6 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
recognizes the second table as JONES.ABC.
With distributed databases, the naming convention is expanded to include three
parts: unique name, owner ID, and location. While some analysts regard this
scheme as a compromise to location transparency, in actuality the ability to
recognize the location of a table from its name does not in itself violate
location independence. First, it is possible to physically move the table to
another location without changing its name (of course, the naming convention
is violated). Second, it is possible to assign a location-free name to the
table by defining a view on it. In either case, neither location transparency
nor location independence is compromised.
Section: 8 Database Architecture
SQL/DS is a relational database management system in which data is perceived
as one or more tables, with each table consisting of rows and columns.
Structured Query Language (SQL), its database language for data definition,
retrieval, manipulation, and control operations, accepts multi-tiered view
definitions, allowing a great deal of data independence.
Section: 9 Data Types
SQL/DS supports the following data types: decimal (total number of digits,
digits to the right of decimal point), integer (largest number is
2,147,483,647), smallint, float, char, varchar, long varchar, graphic (DBCS),
vargraphic, long vargraphic (maximum number of characters is 16,383), date,
time, and timestamp. Data not fitting these standard data types can be stored
as bit strings (binary data) in character columns. When a column contains bit
data, its bit (binary data) pattern will not be modified when moving table
data between ASCII and EBCDIC environments. This option currently applies
only to char, varchar, and long varchar columns.
Section: 10 Field Attributes
Field attributes include null value support and entity integrity (guaranteed
uniqueness).
Null value support allows the DBA to distinguish between an entry of zero (for
numeric columns), blank (for character columns), and a non-entry (NULL for
either numeric or character columns). The ability to distinguish between
these values is important at face value, as well as to ensure the accuracy of
statistical counts. For example, a user might want to distinguish between
paid-up accounts and accounts that were never used.
MIC
( 7 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
Section: 11 Views
A view is a logical, or virtual, table that is derived from one or more
tables. Views support logical data definition that is independent of physical
considerations and, thus, constitute a major criterion for evaluating the
quality of a relational DBMS. Views give the DBA an opportunity to allow
controlled access to data by end users. By eliminating sensitive columns from
a user's view, the user can freely access the remaining fields, without
requiring an application. Views also simplify programming, since data
selection, joining, and filtering are pre-defined at the DBMS level.
SQL/DS views also can be derived from other views or combinations of views and
tables. Views that reference multiple base tables cannot be updated through
the view; update, insert, and delete operations must be done directly on the
stored tables. A view can contain a maximum of 140 columns.
The logical representation of a view definition is stored in an access module
table. The access module contains the best access path for each SQL command
in the program and is automatically created when a program is pre-processed.
Section: 12 Referential Integrity
Referential integrity ensures the consistency of data values between related
columns of two different tables. Suppose a user wants to ensure that for
every department number in the EMPLOYEE table there must be an equal and
unique department number in the DEPARTMENT table. Such a constraint defined
on the EMPLOYEE table is called a referential constraint.
In SQL/DS, referential constraints are specified in the referential-constraint
block of the table definition. They are defined by means of foreign key,
constraint-name, references-name, and ON DELETE clause. The following
additional options can be specified: DROP [PRIMARY KEY, FOREIGN KEY], ACTIVATE
[ALL, PRIMARY KEY, FOREIGN KEY], and DEACTIVATE [ALL, PRIMARY KEY, FOREIGN
KEY].
Delete options are as follows:
o CASCADE: the deletion of a row in the parent table will cause the deletion
of any dependent rows. SQL/DS allows single level cascades only.
o RESTRICT: All dependent rows must be deleted before the parent row can be
deleted. This is the default option if none is specified.
MIC
( 8 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
o SET NULL: The deletion of a row in a parent table causes the corresponding
values of the foreign key in dependent rows to be null.
The following implicit update rules are enforced:
o When a primary key is updated, it must be unique and not null.
o All dependent rows must be deleted or updated before the primary key of the
parent row can be updated.
o When a foreign key is updated, it must have a matching primary key in the
parent table or be a null key. A foreign key is considered null when any of
its column values becomes null.
Users can deactivate referential constraints to improve performance when
loading large volumes of data. When the constraints are reactivated, SQL/DS
validates the references in the data.
Section: 13 Physical Storage - Storage Structures
SQL/DS provides four storage structures for controlling the physical placement
of data in the database: storage pools, DBEXTENTs, DBSPACEs, and tables. Each
database resides in one or more storage pools, or collections of minidisks. A
storage pool is physically divided into physical DBEXTENTs. Data and indexes
are assigned not to the physical DBEXTENTS, however, but rather to logical
DBSPACEs.
An administrator can use storage pools to control distribution of the database
across DASDs. S/he can store frequently referenced data on different physical
devices to decrease DASD access arm contention.
A DBSPACE is not a physical address space. It represents a linear address
space that is mapped to real DASD storage only when tables and indexes are
created, and when data is inserted into it. The maximum size of a DBSPACE is
set when it is defined.
Up to 255 SQL/DS tables can be assigned to a DBSPACE, but each table resides
in only one DBSPACE.
Section: 14 Clustering
SQL/DS supports clustering of table rows that have common or similar keys.
This capability can be used effectively to support performance of joins and/or
MIC
( 9 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
performance of ordered retrieval along an index sequence.
When a primary key is defined, SQL/DS automatically creates a unique index. A
single table can have multiple unique indexes. Additional indexes are created
and maintained by SQL/DS as the result of explicit user requests. Indexes
enable SQL/DS to search data in user-defined order, depending on the data
values in one or more columns. The user does not specify indexes in queries;
SQL/DS automatically decides which indexes, if any, to use in satisfying a
given request.
A table can have many indexes. Each index can refer to up to 16 columns of a
table. Any one index applies to only one table.
Section: 15 Clustered Indexes
A clustered index is an index whose keys are in approximately the same
sequence as the data records that it references. SQL/DS maintains information
that indicates the degree of clustering and uses this information when
selecting an access strategy for a query.
Section: 16 Physical Integrity and Security
SQL/DS maintains a log on disk as a safeguard against incomplete transactions,
media failures, and other disasters. Both single- and dual-disk logging are
available.
SQL/DS includes a facility for archiving a database and the log to tape or to
another disk device. Archiving can be done while the system is operating.
In the event of a failure, SQL/DS uses the log to automatically back out
uncommitted transactions and restore the affected local and remote tables to
their earlier condition. It then re-initiates the updates.
During restoration, the operator can override automatic operations, as in
repairing or manually backing out committed database changes that are known to
be in error.
Automatic Rollback
SQL/DS respects the Logical Unit of Work (LUW) in ad hoc operations as well as
in programmed operations. When a group of related changes is made to the
database, SQL/DS ensures that either all the changes are made or none at all.
A financial transaction that subtracts from one account and adds to another
MIC
( 10 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
account is an example of related changes. The programmer defines the LUW by
means of the COMMIT WORK statement.
Taking advantage of CMS work units, SQL/DS allows a single user or application
to maintain more than one LUW at one time. For example, the user can run a
program and, in the middle of a LUW, have that program call a second program.
The second program runs in a separate CMS work unit. When work is committed
in the second program, it does not affect the active LUW in the first program.
VM/SP Release 6 is required for CMS work unit support.
Accounting
SQL/DS includes an accounting facility that reports usage by user and also
nonspecific usage that cannot be attributed to any given user: cycles consumed
by utilities, operators, and so on. Thus, a periodic SQL/DS report can
furnish appropriate charge-back data for accounting purposes.
User Access
The security authorization facilities of SQL/DS can be used to specify the
data each user can access and the type of access (like read or update) the
user is allowed.
SQL/DS supports three types of authorities on databases: CONNECT, RESOURCE,
and DBA. It supports the following privileges on tables and views: SELECT,
INSERT, DELETE, and UPDATE. The following privileges apply to tables only:
ALTER, INDEX, and REFERENCES.
In addition, SQL/DS has a Security Audit Trace facility that records who is
using SQL/DS, the authorization level of the user, and how SQL/DS users have
made use of their authorization. User management is responsible for the
selection, application, and adequacy of controls and security features for its
environment.
Levels of Administration
It is possible for one person to administer all of the data, or for multiple
administrators to share the tasks. When multiple administrators are employed,
each can look after a separate application. Individual users can perform some
administrative functions for their private data.
Most administrative functions can be done while other users and programs are
accessing and changing the database.
Locks
MIC
( 11 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
Locks are assigned according to logical units of work. All logical units of
work automatically acquire exclusive locks on data that they modify and share
locks on data that they read. When changes are made to dependent tables, the
parent table is locked as well. In general, locks are held to the end of the
logical unit of work in which they are acquired. The size of the lockable
units is specified in the ACQUIRE/ALTER DBSPACE statements; it can be page,
dbspace, or row.
The LOCK statement overrides the SQL/DS automatic locking mechanism. It
explicitly acquires a lock on a table or DBSPACE. The LOCK statement is
useful mainly for avoiding the overhead of acquiring many small locks when
scanning a table.
The current release of SQL/DS supports a new type of lock, called an UPDATE
lock, that reduces the number of deadlocks that sometimes occur with
concurrent user access. It is used by SQL/DS components in appropriate
circumstances and works with any isolation level.
Section: 17 Catalog
SQL/DS maintains a set of tables, called the system catalog, that describes
the database. Catalog tables are dynamically updated to reflect the current
status of the database. When a user defines a database object, for example,
the SQL/DS catalog tables are automatically updated; users do not have to make
entries in the catalog. A catalog is automatically created when an SQL/DS
database is generated. Each SQL/DS catalog table can be accessed through the
same SQL commands that are used to access data.
The Catalog does not cross-reference applications to data. However, it does
maintain authorization information about SQL/DS access modules.
Specifically, the SYSPROGAUTH table of the Catalog records the following
authorization information: GRANTOR of the RUN privilege, GRANTEE, CREATOR,
PROGNAME, TIMESTAMP, and RUNAUTH.
Section: 18 Performance
SQL/DS provides several facilities and techniques for optimizing performance.
These include statistics, reorganize capabilities, and application-level
sequential prefetch. These facilities and techniques are in addition to the
clustered data, clustered indexes, locking control, and storage pool
specifications described elsewhere in this report. The DBA or application
developer can also test the performance alternate wordings of a request with
the EXPLAIN statement and the ISQL query cost estimate.
MIC
( 12 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
Statistics
The SQL/DS Catalog keeps usage statistics on tables, and it uses these
statistics when determining access paths. These statistics are not maintained
automatically. Statistics are not automatically maintained; the user must run
the UPDATE STATISTICS statement in order to update statistics on a table. The
UPDATE STATISTICS statement can be applied to individual tables or to
DBSPACEs.
Sequential Prefetch
SQL/DS does not automatically execute sequential prefetch. It does, however,
allow the application programmer to specify that it be used in individual
applications. S/he does this by specifying the BLOCK parameter when
preprocessing the program or, for extended dynamic statements, by putting the
BLOCK parameter into the CREATE PROGRAM statement.
Blocking improves performance in multiple user mode because data is sent
between the program and SQL/DS in blocks of rows, rather than in individual
rows. This reduces overhead from communication between the database machine
and the user machine.
It is not necessary to compute and specify a block size. SQL/DS always uses
an 8KB block for each cursor that is open, sending as many rows as will fit
into the 8KB block. It is also unnecessary for the program to explicitly
handle the blocks; SQL/DS manages the blocks transparently for both insert and
retrieval operations.
Section: 19 Report Writing
SQL/DS provides report writing facilities via the DBS Utility and ISQL.
Alternatively, reports can be created within applications.
The ISQL FORMAT command is used to create reports from the results of a SELECT
statement. It allows the user to specify the following report attributes:
o Bottom and top titles. The top line defaults to the first 100 characters of
the SELECT statement; the bottom title defaults to blanks.
o Totals and subtotals.
o Column separation.
o Outlining. When specified, successive duplicate values for a desired column
MIC
( 13 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
need not be repeated unless they start a new screen or output page.
o Column characteristics. The user specifies column width, column headings,
as well as the number of decimal places and zero suppress for numeric data
columns.
Section: 20 Application Programming
SQL/DS programs benefit from being SAA-compliant and from the virtues of the
VM operating system. Because it is SAA-compliant, programs developed here can
be moved easily to other platforms. Because it operates under VM, the VM
operating system provides the isolation and safety of a virtual machine while
still allowing access to databases for testing. Because development under
SQL/DS occurs in a virtual machine by itself, the worst scenario is that the
virtual machine must be restarted, an event unnoticeable by others sharing the
database. SQL/DS automatically rolls back the unit of work in progress when
the application fails.
Programmers developing under SQL/DS work in the CMS environment, which
furnishes access to compilers, editors, and other tools of the trade. SQL/DS
supports COBOL, FORTRAN, C, PL/I, APL, and Assembler.
SQL Data Manipulation Statements
SQL/DS includes scalar and column functions as part of its data manipulation
language. The following column functions are supported: COUNT, AVG, MAX, MIN,
and SUM. The user can request DISTINCT or ALL values meeting the stated
criteria. The following scalar functions are supported: CHAR (returns a
string representation of a date/time value), DATE, DAYS, DECIMAL, DIGITS
(returns a character string representation of a number), FLOAT, HEX, HOUR,
INTEGER, LENGTH, MICROSECOND, MINUTE, MONTH, SECOND, STRIP (removes blanks or
another specified character from the beginning and/or end of a string),
SUBSTR, TIME, TIMESTAMP, TRANSLATE (translates one or more characters in a
string expression into other characters), VALUE (returns the first non-null
result in a series of expressions), VARGRAPHIC, and YEAR. The argument of a
scalar function can be a function.
Section: 21 Preprocessors
The embedded SQL commands in a program must be analyzed and converted by
SQL/DS before the normal host language compilation. Preprocessors perform two
functions:
MIC
( 14 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
o They generate a new version of the source code for normal source program
processing (e.g., compilation). This new version of the source code
contains the SQL commands in comment form. It also contains the host
language code for invoking a communication routine in place of the SQL
commands.
o They convert the SQL commands into access modules. An access module is a
control structure produced during program preparation that is used to
process SQL statements during application execution. Access modules are
stored in the database and can be transferred from one database to another.
Applications can be distributed without distributing the source programs.
The access module contains the best access path for each SQL command in the
program. This access path is automatically determined by SQL/DS based on
available indexes and data statistics. Application programmers need not (and
actually cannot) select the particular indexes to be used for retrieval. SQL
commands embedded in a program do not refer to indexes for tables used by the
program.
Because the access module is stored in a database, it can be monitored by
SQL/DS security mechanisms and automatic change-management facilities. Also,
the access module is available when the program needs it.
The programmer can specify two optional parameters at preprocessing time which
affect runtime performance. One, a blocking option, specifies whether SQL/DS
will retrieve and insert rows in groups. The other parameter, called the
isolation level, controls the locking mechanism and tells SQL/DS whether to
let other users update data that the program has finished reading in its
current logical unit of work. A program can also control its own ("mix")
isolation levels by passing the isolation level value to SQL/DS through a
program variable that can change during the course of execution.
Section: 22 Dynamically Defined SQL Commands
SQL/DS also supports applications that allow an interactive user to type in
his/her own queries in the course of execution. In this case, the SQL
commands cannot be embedded in the program and cannot be preprocessed because
the commands are read from a terminal when the program is running. SQL/DS
PREPARE and EXECUTE commands handle such commands that are specified at
runtime. For SELECT statements entered from the terminal, the programmer must
specify the SQL command INCLUDE SQLDA. This command, when used after the
DESCRIBE command, automatically analyzes the request and allocates buffers of
the correct size and type to receive the result of a SELECT query. The
DESCRIBE command is not supported by COBOL and FORTRAN.
MIC
( 15 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
Section: 23 Extended Dynamic Statements
In addition to the SQL/DS preprocessors, SQL/DS provides a facility to build
and maintain access modules without going through a separate processing step.
This is the SQL/DS Extended Dynamic Statement facility.
With extended dynamic statements, programmers can write their own
pre-processors. The facility could be used to develop preprocessors for other
high-level languages, such as Pascal or BASIC.
Extended dynamic statements can also be used to create preprocessed database
interface routines. In this way, data languages other than SQL could be used
in programs to access an SQL/DS database. The preprocessor must be able to
map the data language to SQL.
Section: 24 Interface for VSAM
The SQL/DS Application Interface for VSAM is a bridge between VSAM data and
SQL/DS. It permits the movement of data from VSAM data sets into SQL/DS
tables, so that the data is usable as SQL/DS data while it continues to be
usable by application programs using VSAM.
The Interface services CICS/VM environments. It eases the migration of VSAM
definitions and data to SQL/DS running under VM/ SP. It permits VSAM
application programs to run unchanged against data that has been moved from
VSAM to SQL/DS.
Sometimes, installations choose to keep VSAM data sets on their MVS machines
and to convert copies of the data to SQL/DS running under VM. Data can be
accessed and updated on the VM system and then converted back to VSAM for MVS.
When migrating data sets to SQL/DS, size limitations are as follows:
o Primary key or alternate key indexes must be 254 bytes or fewer.
o A maximum of nine files are allowed in a logical unit of work.
o A maximum of 1,000 files can be open at any one time in an application.
o A maximum of seven alternate indexes can be specified for migration.
o All keys must be contained within the first 4,072 bytes of a VSAM record.
MIC
( 16 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
o No more than 255 columns can be defined for an SQL/DS table.
o The maximum record size for a VSAM data set is 99,999 bytes.
o An SQL/DS statement cannot exceed 8,192 characters. This maximum may occur
in the Table Generation function if many columns or long column names are
specified.
In addition, the SQL/DS application Interface does not support the following
VSAM facilities:
o Control interval processing is not supported.
o Addressed access to a KSDS is not supported.
o Alternate indexes can be used only on VSAM base clusters.
o Application programs that calculate RBA values for direct access to records
will not work properly under emulation.
o Applications that depend on logically deleted ESDS records will not work
properly under emulation.
o With sequential retrieval of ESDS records on non-unique alternate indexes,
the Interface may not return records in the same order as VSAM.
Section: 25 Multiple Language Support
With this support, multiple versions of SQL/ DS message text can be installed,
each in a different national language. Each user and the operator can choose
the language in which SQL/DS informational and error messages are displayed.
Section: 26 Pricing
SQL/DS pricing appears in Table 1.
Section: 27 Table 1 - SQL/DS Pricing
SQL/DS Pricing:
Product Processor Group One-Time Charge Monthly Charge
MIC
( 17 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
SQL/DS Version 2, 10 $ 9,345 $ 854
Basic Offering
15 $12,465 $ 899
18 $18,690 $ 947
20 $21,800 $ 997
30 $31,500 $ 1,050
40 $57,840 $ 1,205
50 $85,870 $ 1,385
60 $98,890 $ 1,595
SQL/DS Version 2, 10 $ 7,005 $ 640
Distributed
Offering 15 $ 9,345 $ 674
18 $14,010 $ 710
20 $16,350 $ 747
30 $23,620 $ 787
40 $43,380 $ 903
50 $64,400 $ 1,035
60 $74,167 $ 1,196
Section: 28 Evaluation
SQL/DS offers a good implementation of the relational model, closely related
to DB2's SQL. It offers both entity and referential integrity to protect data
from logical infractions. The same tools can be used for SQL/DS as for DB2,
so that installations know that they are working within the mainstream and
that their investment is protected. Customers can be assured of IBM's
commitment to the product by its participation in SAA and distributed database
strategies.
SQL/DS capitalizes on certain strengths of the VM environment. First, SQL/DS
MIC
( 18 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
focuses on and exploits the ease-of-use features built into the VM operating
system. Second, users of a guest VSE system can use and access VM-SQL/DS.
Finally, application developers can take advantage of VM's compilers and
editors.
In terms of connectivity, SQL/DS was the first IBM DBMS to begin implementing
distributed database. This ability today extends to the remote unit of work,
remote operational functions, and the ability to act as a host to VSE users.
These capabilities will grow together with IBM's distributed database
capabilities. This strategy includes the ability to load data from VAX
machines through DXT.
Section: 29 Comparison with DB@
Compared to DB2, SQL/DS offers similar relational capabilities (see Table 2).
Both support multi-tiered views of the data as well as retrieval capabilities
for views based on multiple tables, and both implement referential integrity.
Both also offer good protection of data from disaster and from unauthorized
access. However, DB2 allows processing routines to be attached to individual
fields as part of the CREATE TABLE statement. In addition, DB2 supports
partitioned and replicated tables for better management of disk storage and
for better performance. It also supports user authorization to be defined in
terms of user groups, simplifying administration. These features are
hallmarks of DB2 and are not generally offered by competing products. They are
mentioned here to delimit SQL/DS capabilities to readers who know DB2, but
they do not significantly impact SQL/DS's marketing position in the VM world.
Section: 30 Conclusion
The VM marketplace offers a large area for growth and penetration. Many
installations, especially in mid-size and smaller companies, have no DBMS at
all at the present time. With the recent shakeout of DBMS products, only a
few competitors remain. Of these, Oracle and Ingres have made their biggest
marks in non-IBM environments. About half a dozen other DBMSs cannot be
considered true competitors to SQL/DS because their relational implementations
are based on early models at best. The only two remaining competitors in the
VM marketplace, therefore, are Computer Associates and Cincom.
When IBM went after the MVS world with DB2, part of its strategy was to
educate customers on the benefits of the relational approach to database
management. This strategy is necessary in the VM world as well. More and
more businesses are providing excellent customer service in the form of
account information and other reports. Consumers appreciate this higher level
of service and become accustomed to it. Even small businesses will find that
MIC
( 19 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
they require the benefits of relational DBMS--easy availability of information
in a variety of formats and on an ad hoc basis, if necessary--in order to
remain competitive.
The attractiveness of SQL/DS goes beyond the individual features of
implementation to its position within IBM's vision and future strategy.
SQL/DS participates in the distributed database that will span all
SAA-implementing operating systems. Even those companies who are not
interested in accessing remote sites will certainly benefit from a distributed
database consisting of VM-based SQL/DS and OS/2-based Database Manager. They
will reap the benefits of integrated data resources and cooperative
processing.
Section: 31 Table 2 - SQL/DS Compared to DB@
SQL/DS Compared to DB2:
Vendor IBM IBM
Product SQL/DS Version 2.2 DB2 Version 2.2
Structure relational relational
Schema Layers multiple multiple
Multi-Table Views retrieve only retrieve only
Database Language SQL SQL
Domain Integrity no yes
Entity Integrity yes yes
(uniqueness)
Null Value Support yes yes
Referential Integrity DBMS level DBMS level
DBMS-level Procedures no FIELDPROC, VALIDPROC,
EDITPROC
Database Interfaces VSAM Transparency VSAM Transparency
DXT extracts data from DXT extracts data from
IMS, DB2, VSAM IMS, SQL/DS, VSAM
MIC
( 20 )
----- SQL/DS Version 1 Release 2 from IBM | 01-MAR-90
|C I S| | MIC
-----
Language Interfaces COBOL, FORTRAN, C, COBOL, FORTRAN, C,
PL/I, APL, Assembler PL/I, APL, Assembler
Automatic yes yes
Rollforward/back
Access through User no yes
Groups
Locking Levels row, page, DBSPACE page, table, tablespace
Sequential Prefetch application level yes
Partitioned/Replicated no yes
Tables
(c) MIC
MIC
( 21 )
| T.R | Title | User | Personal
Name | Date | Lines
|
|---|