| Decus presentation, Fall 1988.
DM058
VAX Data Distributor Technical Tutorial
ABSTRACT
--------
The technology presented here is a key component of Digital's
distributed data management program.
This session covers, in-depth, methods of distributing data from
centralized Rdb or VIDA databases (any DSRI based database) to
remote sites using VAX Data Distributor, a member of the VAX
Information Architecture. This session discusses the types of
applications that can benefit from such distribution.
Slide Ordering Information
------
The order numbers for the 35mm slides are: SE944.001 to SE944.094
Contact the Corporate Photo Library. EXIT26::PHOTO
If a script is available, you will receive it automatically.
Slides with script
------------------
VAX DATA DISTRIBUTOR (slide 1) (SE944.001)
VAX DATA DISTRIBUTOR
******************************************
SCRIPT
Welcome.
VAX Data Distributor is part of a family of database products
offered by Digital.
Applications can use VAX Data Distributor to move data from a
centralized relational database system to remote sites.
There, the data can be directly accessed locally by users at those
sites.
VAX Data Distributor is an extension of Digital's style of
computing in that it locates the data close to those who use it and
distributes the processing load among the available processors in a
network.
At the conclusion of this talk you should have a basic
understanding of the functionality provided by VAX Data Distributor
and how you could use it.
******************************************
VAX DATA DISTRIBUTOR (slide 2) (SE944.002)
(motivational example)
International Widget Co., Inc.
SYDNEY FRANKFURT
ST. LOUIS
LOS ANGELES GENEVA
Personnel
database
******************************************
SCRIPT
Put yourself in this picture.
You work for the International Widget Company, which has its
personnel database at the corporate site in St. Louis.
Remote sites, such as the offices in Sydney, Frankfurt, Los
Angeles, and Geneva, access the central database for general
querying and reporting.
It is supposed that they do not need up to the minute information.
******************************************
VAX DATA DISTRIBUTOR (slide 3) (SE944.003)
International Widget Co., Inc.
PROBLEMS:
o Slow Response Time on Central Database
o Slow Response Time on Remote Sites
o Network Links to Remote Sites May Fail
o Network Links May Be Costly
******************************************
SCRIPT
There are several potential problems with this centralized
approach.
o Users at the corporate site might experience slow response to
their queries.
With the database being accessed by local and remote users
alike, there simply might be too many of them competing for
limited central cpu and disk resources.
o Meanwhile, users at the remote sites can also experience slow
response time because their access to the central site is over
slow communication lines.
o If communication links fail, remote users can't get access to
the information they need.
o And, finally, meeting the response time and availability
requirements of the remote users could require a costly
communications network.
******************************************
VAX DATA DISTRIBUTOR (slide 4) (SE944.005)
o Automated Database Extraction
and Replication Facility
o Automated Database Extraction
Rollup
******************************************
SCRIPT
VAX Data Distributor can be used to alleviate these problems.
It is an automated database extraction and replication facility.
Later in this talk, I'll explain just what this means.
Data Distributor also provides an automatic extraction rollup
facility to move subsets of databases from remote sites to a
central site.
******************************************
VAX DATA DISTRIBUTOR (slide 5) (SE944.004)
Corporate Remote
Site Sites
Source Targets
******************************************
SCRIPT
Data Distributor moves data from a source database at the corporate
site to target databases at the remote sites in a timely and
orderly fashion.
Response time improves at all sites because users at the remote
sites are processing queries using local resources.
Except for the time when Data Distributor transfers are scheduled
to occur, there are no network lines involved.
This improves availability of the data at the remote sites.
Finally, the company might be able to use a cheaper communications
network, because the response time and availability requirements
are reduced.
The capability of distributing subsets of a source database to
multiple target databases is a one to many relationship.
******************************************
VAX DATA DISTRIBUTOR (slide 6) (SE944.093)
Corporate Remote
Site Sites
Target Source
******************************************
SCRIPT
Data Distributor can also be used to extract data from multiple
source databases and to roll them up into a single, composite
target database.
This is an example of a many to one relationship.
The several source databases can reside at the same site or can be
geographically distributed.
******************************************
VAX DATA DISTRIBUTOR (slide 7) (SE944.006)
o Automated Database Extraction
and Replication Facility
o Automated Database Extraction
Rollup
o Layered as Option to Rdb/VMS
******************************************
SCRIPT
VAX Data Distributor is built as an option to Rdb/VMS.
******************************************
VAX DATA DISTRIBUTOR (slide 8) (SE944.007)
o Automated Database Extraction
and Replication Facility
o Automated Database Extraction
Rollup
o Layered as Option to Rdb/VMS
o Statements Entered Through RDO
Utility of Rdb/VMS
******************************************
SCRIPT
VAX Data Distributor does not have its own user interface.
All statements are available through RDO.
RDO (The Relational Database Operator) is a utility program
provided with Rdb/VMS and is used to create and manipulate Rdb/VMS
databases.
There are eight statements in RDO which are specifically for VAX
Data Distributor, and these will be discussed later.
******************************************
VAX DATA DISTRIBUTOR (slide 9) (SE944.008)
o Automated Database Extraction
and Replication Facility
o Automated Database Extraction
Rollup
o Layered as Option to Rdb/VMS
o Statements Entered Through RDO
Utility of Rdb/VMS
o Used with DSRI Databases
- Rdb/VMS
- VIDA
******************************************
SCRIPT
VAX Data Distributor can be used with DSRI compliant databases,
such as, Rdb/VMS and VIDA for extraction and extraction rollup
transfers.
Replication transfers apply only to Rdb/VMS databases.
******************************************
VAX DATA DISTRIBUTOR (slide 10) (SE944.009)
Methods of Distribution
o Extractions
o Replications
******************************************
SCRIPT
Three methods of data distribution are provided: Extraction,
extraction rollup, and replication.
Since Extraction and Extraction Rollup have many common features,
they are treated alike in the next few slides.
******************************************
VAX DATA DISTRIBUTOR (slide 11) (SE944.012)
Extractions and Replications
Similarities
o Defined using Same Statements
******************************************
SCRIPT
To reduce complexity, VAX Data Distributor makes the management of
extractions and replications as similar as possible.
Both use the same statements available in RDO.
These statements are used to define extraction and replication
transfers and to define schedules for their execution.
******************************************
VAX DATA DISTRIBUTOR (slide 12) (SE944.013)
Extractions and Replications
Similarities
o Defined using Same Statements
o Subsets Can Be Transferred
******************************************
SCRIPT
Both allow database subsets to be transferred.
The database subset definition is part of the transfer definition,
and is declared using a simplified form of an RDO record selection
expression, or RSE.
******************************************
VAX DATA DISTRIBUTOR (slide 13) (SE944.014)
Extractions and Replications
Similarities
o Defined using Same Statements
o Subsets Can Be Transferred
o Started On-Demand or with Predefined
Schedule
******************************************
SCRIPT
Once defined, a transfer can be started on-demand by entering a
single statement.
This can be useful if you are trying to synchronize a transfer with
some other event, such as the execution of another program.
Transfers which are to be repeated on a regular basis can have a
schedule defined for them.
They can be scheduled to start every day, every week, every month;
or one can specify a list of weekdays, such as, Monday, Wednesday,
etc.
******************************************
VAX DATA DISTRIBUTOR (slide 14) (SE944.015)
Extractions and Replications
Similarities
o Defined using Same Statements
o Subsets Can Be Transferred
o Started On-Demand or with Predefined
Schedule
o Automatic Retry if Target Node Not
Available
******************************************
SCRIPT
Finally, one can specify an alternate schedule to be used in case
the transfer should fail.
For instance, when VAX Data Distributor is trying to execute a
transfer, and the target node is not available, VAX Data
Distributor will automatically retry the execution at a later time.
That time is specified by you in the schedule definition.
******************************************
VAX DATA DISTRIBUTOR (slide 15) (SE944.016)
Extractions and Replications: Differences
Extractions
o Entire Subset Copied Each Time
o Target Database Can Be Updated
o Multiple Sources into a Single Target
******************************************
SCRIPT
Extractions and replications do have their differences.
With an extraction or an extraction rollup, the entire database
subset is copied to the target node each time the transfer is
executed.
With each execution, a new version of the database is created at
the target site.
After the transfer completes, the target database has no further
ties with the source database or databases.
And, since there are no further dependencies between source and
target, you are free to update the target database as well as read
it.
However, any changes you make to the target will not be
automatically "rolled up" to the source database(s).
If the extraction is reexecuted after you've made such local
changes, the new copy of the target database will not have your
changes in it.
Extractions can be done using more than one source database; this
is called an extraction rollup transfer.
******************************************
VAX DATA DISTRIBUTOR (slide 16) (SE944.017)
Extractions and Replications: Differences
Replications
o Entire Subset Copied the First Time
o Only Changed Records are Moved Thereafter
o Target Database is Read-only
o Source Database Must Be Rdb/VMS
******************************************
SCRIPT
At the first execution of a replication transfer, the results are
effectively the same as when performing an extraction -- a target
database is created.
However, on all subsequent executions of the transfer, only the
changes made to the source are applied to the target.
And, since there is a much tighter bond between the target
replication database and the source database, the target database
is available only for read access.
Recall that replication can be done only from an Rdb/VMS source
database.
******************************************
VAX DATA DISTRIBUTOR (slide 17) (SE944.018)
o Components
o Statements
o Tying it All Together
o Cluster Support
o Questions?
******************************************
SCRIPT
This is an outline of the remainder of my talk.
o First we'll discuss the components that make up VAX Data
Distributor.
o Then we'll look at the statements that are available in RDO to
manage the transfers.
o Next we'll tie everything together, specifically looking at
which components get activated when you enter a VAX Data
Distributor statement, and which components get activated when
a transfer gets executed.
o Finally, we will examine how VAX Data Distributor takes
advantage of the cluster environment to allow the continued
execution of your transfers despite node failures.
******************************************
VAX DATA DISTRIBUTOR (slide 18) (SE944.019)
Components
******************************************
SCRIPT
Components of Data Distributor include such things as
1. databases used,
2. definitions of what you want transferred and when,
3. programs that run on your behalf, and
4. special relational tables needed to manage the transfers and
keep track of the changes in the source database.
******************************************
VAX DATA DISTRIBUTOR (slide 19) (SE944.092)
Components
Source Node | Target Node
|
|
RDO> Transfer Copy | Rdb/Remote
Monitor Process |
|
|
|
|
|
Source Transfer | Target
Database Database | Database
******************************************
SCRIPT
This slide shows how all the components fit together.
You enter requests with RDO.
These requests are processed by a transfer monitor by storing or
manipulating information in a transfer database managed by Data
Distributor.
(The transfer database is not a user database but exists just to
support the operation of VAX Data Distributor.)
At the appropriate time the transfer monitor starts up a copy
process to perform the transfer.
The copy process gets information about the transfer from the
transfer database.
Finally, the copy process transfers metadata and data from the
source database to the target database, using the Rdb/REMOTE
interface where appropriate.
******************************************
VAX DATA DISTRIBUTOR (slide 20) (SE944.026)
Source Database(s)
o Origin of all Data and Metadata Copied to Target Database
o Name May be Specified in Several Transfer Definitions
o Contains Special System Relations Used for Replications
o DSRI Compliant Database
- Rdb/VMS
- VIDA (Extractions)
******************************************
SCRIPT
Source databases are the origin of all Data and Metadata to be
copied to target databases.
A single source database may be named in several transfer
definitions, each definition possibly transferring a different
subset.
If the source database is used for replications, then special
system relations are created in the source database and used by VAX
Data Distributor.
A source database may be any DSRI compliant database, such as,
Rdb/VMS or VIDA with IDMS/R. VIDA is a database interoperability
product to access cullinet's IDMS/R on IBM system.
VIDA may only be used as the source of extractions, not for
replications.
For extractions and extraction rollups, the source databases may be
local to or remote from the site where the transfer is defined.
For replications, the source database must be local.
******************************************
VAX DATA DISTRIBUTOR (slide 21) (SE944.025)
Target Database
o Stores All Data and Metadata Copied from a
Source Database
o Name may be Specified Only in a Single
Transfer Definition
o Contains Additional Metadata When Target
of a Replication
o Always Rdb/VMS
o May be Local or Remote
******************************************
SCRIPT
The target database stores all the data and metadata that is copied
from a source database or databases.
The name chosen for the target database may only be specified in a
single transfer definition.
You cannot use independent transfers to copy data subsets into the
same target database.
If the target database is the result of a replication, it contains
VAX Data Distributor managed metadata, which is used along with the
special system relations in the source to keep the source and
target databases synchronized.
The target database is always an Rdb/VMS database, and it may be
local to or remote from the place where the transfer is defined.
******************************************
VAX DATA DISTRIBUTOR (slide 22) (SE944.020)
Transfer Definition
o Source and Target Database Names
o Subset Specification
o Type of Transfer: Extraction,
Replication, or Extraction Rollup
o Pre- and Post-Transfer
Command Procedures
******************************************
SCRIPT
Now consider the transfer definition.
It contains all the information needed to execute a transfer:
o The names of the source and target databases,
o A subset specification (which relations to copy, which fields
in those relations, and which data records), and
o The type of transfer -- extraction, extraction rollup, or
replication.
o Optionally, a transfer definition may name pre- and
post-transfer procedures.
******************************************
VAX DATA DISTRIBUTOR (slide 23) (SE944.089)
Pre- and Post-Transfer Procedures
o Notification
o Log Errors to OPCOM
o Integrate Target Metadata to CDD
o Bring Up Line for Transfer Only
o Purge Files After Transfer
o Coordinate With Another Event
o Create Target Database Indexes
and Constraints
o Define Target Database Protection
******************************************
SCRIPT
These pre- and post-transfer procedures may be executed directly
before or after the actual transfer.
The command procedures must be written in DCL, but they may execute
programs, invoke RDO or SQL, MAIL or whatever you want.
Here is a list of ways in which such procedures might be used:
- They can be used for notification of the completion of a
transfer. For example, a post-transfer procedure could send
out mail to interested parties.
- Errors can be logged to OPCOM, the system operator's console.
- They could be used to integrate the target database data
definitions into the dictionary at the target site.
- If you don't need the network line to be up continuously, such
procedures could be used to connect and disconnect the line at
transfer execution time. This could reduce communications
costs.
- Such command procedures can be used for housekeeping functions,
such as, purging disk files.
- It is possible to set and test success/failure indicators
during execution of these procedures and of the transfer
itself. Based on this, you can then coordinate the execution
of other procedures where there is a mutual dependency.
- Post transfer procedures are useful for defining indexes,
constraints, different views, and access protection tailored to
the needs of the target database environment.
This must be done after the target database has been created
and after data records have been moved.
******************************************
VAX DATA DISTRIBUTOR (slide 24) (SE944.021)
Schedule Definition
o When to First Execute the Transfer
o When to Repeat the Transfer
o Alternate Schedule in Case of Failure
******************************************
SCRIPT
A component that goes hand in hand with the transfer definition is
the schedule definition.
The schedule definition contains all the information necessary to
schedule the excecution of a transfer:
o The time to first execute the transfer,
o How often to repeat the transfer, and
o After what delay and how many times to repeat the transfer in
case of failure.
******************************************
VAX DATA DISTRIBUTOR (slide 25) (SE944.022)
Transfer Database
o Contains Transfer and Schedule Definitions and Status
o Maintained by Transfer Monitor
o Rdb/VMS Database
o Relations
- DDAL$INVOKE_TIME
- DDAL$TRANSFERS
- DDAL$TRANSFER_STATUS
- DDAL$TRANSFER_FIELDS
- DDAL$TRANSFER_RELATIONS
- DDAL$TRANSFER_SCHEDULE
- DDAL$TRANSFER_VIEWS
- DDAL$ROLLUP_DATABASES
******************************************
SCRIPT
The transfer database holds the transfer and schedule definitions.
It also contains status information such as the last time the
transfer executed and the next time it is scheduled to execute.
It is an Rdb/VMS database which is maintained by a process called
the Transfer Monitor.
The transfer database uses several relations to store and manage
the information it uses.
These relations are listed here, but will not be discussed.
******************************************
VAX DATA DISTRIBUTOR (slide 26) (SE944.085)
Transfer Monitor
o Maintains Transfer Database
o Responds to RDO User's Request
o Starts Copy Processes
o Logs Activity to a Transfer Monitor
Log File
******************************************
SCRIPT
The transfer monitor is a program which maintains the transfer
database.
It responds to requests from RDO users to create, manipulate, and
execute transfer definitions and schedules.
It is a program which services concurrent requests from several
users. To do so, it runs under VMS as a detached process.
When a transfer is ready to be executed, the transfer monitor
starts a copy process to do the actual work.
The transfer monitor logs all of its activity to a log file.
******************************************
VAX DATA DISTRIBUTOR (slide 27) (SE944.023)
RDO
o Rdb/VMS Utility Program
o Used to Define and Manipulate
Transfers
o Interactively or via Callable RDO
(RDB$INTERPRET)
o Communicates with Transfer Monitor
using VMS Mailboxes
o VAX Data Distributor Must be
Installed Locally
******************************************
SCRIPT
RDO is an Rdb/VMS utility program used for data definition, data
manipulation, and database operation and maintenence.
The VAX Data Distributor statements in RDO are used to define and
manipulate transfers.
For example, you would use the DEFINE TRANSFER and the DEFINE
SCHEDULE statements to create transfer and schedule definitions.
VAX Data Distributor statements are available via callable as well
as interactive RDO.
When an RDO user enters VAX Data Distributor statements, RDO
communicates with the transfer monitor through the mechanism of VMS
mailboxes.
If you expect to use RDO to enter VAX Data Distributor statements,
then Data Distributor must be installed locally to you, that is, on
your cpu.
******************************************
VAX DATA DISTRIBUTOR (slide 28) (SE944.024)
Copy Process
o Executes the Transfer
o Started by the Transfer Monitor
o Installed with Privileges
o Run as a Detached Process
o Runs under the Definer's Username
******************************************
SCRIPT
The Copy Process is started by the transfer monitor to perform a
transfer.
There is one copy process created for each transfer that is run.
It is installed as an executable image with privileges, and it runs
as a detached process using the username of the transfer definer.
The copy process executes the LOGIN.COM procedure of the definer.
This allows it to "see" logical symbols that may be defined solely
for your process.
******************************************
VAX DATA DISTRIBUTOR (slide 29) (SE944.027)
Special System Relations
o Maintained in Source Database
o Rdb/VMS Uses for Journalling of Updates
o Used by Copy Process When it Executes a Replication
o Relations
- RDB$CHANGES
- RDB$CHANGES_MAX_TSER
- RDB$TRANSFERS
- RDB$TRANSFER_RELATIONS
******************************************
SCRIPT
The VAX Data Distributor special system relations are maintained in
a source database when it is used for replications.
Among other things, the special system relations are used by
Rdb/VMS to journal updates to records that will eventually be
tranferred as part of some replication transfer.
They are created by the first copy process that executes a
replication transfer against that particular source database, and
they are accessed by each copy process which is executing a
replication on that source.
The relations are listed here, but I won't go into any detail about
them.
******************************************
VAX DATA DISTRIBUTOR (slide 30) (SE944.029)
Components
Source Node | Target Node
|
|
RDO> Transfer Copy | Rdb/Remote
Monitor Process |
|
|
|
|
|
Source Transfer | Target
Database Database | Database
******************************************
SCRIPT
Let me show once again how the components fit together.
You enter requests with RDO.
These requests are processed by the transfer monitor by storing or
manipulating information in the transfer database.
At the appropriate time the transfer monitor starts up a copy
process to perform the transfer.
The copy process gets information about the transfer from the
transfer database.
Finally, the copy process transfers metadata and data from the
source database to the target database, using the Rdb/REMOTE
interface where appropriate.
******************************************
VAX DATA DISTRIBUTOR (slide 31) (SE944.030)
Statements
******************************************
SCRIPT
Now we will discuss the VAX Data Distributor statements in RDO.
Rather than get into a lot of detail, I'll just give an overview.
After that, I will go through several examples to show you how the
most important statements are used.
******************************************
VAX DATA DISTRIBUTOR (slide 32) (SE944.031)
Types of Statements
o Definition
o Operation
o Monitoring
******************************************
SCRIPT
There are three types of statements:
o those used to define,
o those used to manage the operation of, and
o those used to monitor the operation of transfers.
******************************************
VAX DATA DISTRIBUTOR (slide 33) (SE944.032)
Definition Statements
o Define Transfer
o Define Schedule
o Delete Transfer and Delete Schedule
******************************************
SCRIPT
There are four definition statements:
o DEFINE TRANSFER is used to create a transfer definition.
o DEFINE SCHEDULE is used to create a schedule for an existing
transfer.
o Corresponding to these are the DELETE TRANSFER and DELETE
SCHEDULE statements.
After presenting an overview of the other VAX Data Distributor
statements, I will give several examples showing how the DEFINE
TRANSFER and DEFINE SCHEDULE statements are used.
******************************************
VAX DATA DISTRIBUTOR (slide 34) (SE944.033)
Operation Statements
o STOP TRANSFER
o START TRANSFER
o REINITIALIZE TRANSFER
******************************************
SCRIPT
These statements are used to manipulate currently existing
transfers.
STOP TRANSFER aborts a currently executing transfer or keeps one
from executing at its next scheduled time.
START TRANSFER is used to start a transfer on-demand.
It's also used to resume the normal schedule of a transfer that was
suspended using the STOP TRANSFER statement.
REINITIALIZE TRANSFER is used for replication transfers to force
the next execution to create a new copy of the target database
rather than just transfer the changes to an existing copy.
You would use this statement if the source and target databases had
for some reason become unsynchronized.
******************************************
VAX DATA DISTRIBUTOR (slide 35) (SE944.034)
Monitoring Statements
SHOW TRANSFER
******************************************
SCRIPT
There is one monitoring statement -- SHOW TRANSFER.
It is used to display transfer and schedule definitions and their
associated status.
The status information includes such things as:
o the time that the transfer last executed,
o the completion status of that execution, and
o the time that the transfer will next execute.
******************************************
VAX DATA DISTRIBUTOR (slide 36) (SE944.035)
DEFINE TRANSFER
******************************************
SCRIPT
Let's look at the DEFINE TRANSFER statement more closely with a few
examples.
******************************************
VAX DATA DISTRIBUTOR (slide 37) (SE944.036)
DEFINE TRANSFER Examples
Define a replication transfer to move the employee records of those
employees who live in Geneva.
******************************************
SCRIPT
The first example shows how a relatively simple transfer definition
can be created.
Suppose you want to define a replication transfer to move the
employee records of those employees who live in Geneva into a
database located in Geneva from the corporate database in St.
Louis.
******************************************
VAX DATA DISTRIBUTOR (slide 38) (SE944.037)
DEFINE TRANSFER Examples
DATABASE FILENAME pers$disk:[somedir]personnel
DEFINE TRANSFER to_geneva_rep REPLICATION
DESCRIPTION IS /* Replication for Geneva */
TO geneva::disk2:[anydir]geneva_personnel
MOVE RELATION E IN EMPLOYEES
WITH e.city = "Geneva"
SELECT FIELDS ALL
LOG FILE IS pers$disk:[dba]to_geneva_rep.log
END.
******************************************
SCRIPT
The first thing you need to do is attach to the source database.
******************************************
VAX DATA DISTRIBUTOR (slide 39) (SE944.038)
DEFINE TRANSFER Examples
DATABASE FILENAME pers$disk:[somedir]personnel
DEFINE TRANSFER to_geneva_rep REPLICATION
DESCRIPTION IS /* Replication for Geneva */
TO geneva::disk2:[anydir]geneva_personnel
MOVE RELATION E IN EMPLOYEES
WITH e.city = "Geneva"
SELECT FIELDS ALL
LOG FILE IS pers$disk:[dba]to_geneva_rep.log
END.
******************************************
SCRIPT
The transfer is given a name.
In this case, I have called it to_geneva_rep.
I have indicated that it is a replication mode transfer by using
the keyword REPLICATION.
As with other DEFINE statements available in RDO the DEFINE
TRANSFER statement is terminated with the END clause.
******************************************
VAX DATA DISTRIBUTOR (slide 40) (SE944.039)
DEFINE TRANSFER Examples
DATABASE FILENAME pers$disk:[somedir]personnel
DEFINE TRANSFER to_geneva_rep REPLICATION
DESCRIPTION IS /* Replication for Geneva */
TO geneva::disk2:[anydir]geneva_personnel
MOVE RELATION E IN EMPLOYEES
WITH e.city = "Geneva"
SELECT FIELDS ALL
LOG FILE IS pers$disk:[dba]to_geneva_rep.log
END.
******************************************
SCRIPT
The DESCRIPTION clause specifies informational text to be stored
with the transfer definition.
The description will be displayed when you issue a SHOW TRANSFER
statement.
******************************************
VAX DATA DISTRIBUTOR (slide 41) (SE944.040)
DEFINE TRANSFER Examples
DATABASE FILENAME pers$disk:[somedir]personnel
DEFINE TRANSFER to_geneva_rep REPLICATION
DESCRIPTION IS /* Replication for Geneva */
TO geneva::disk2:[anydir]geneva_personnel
MOVE RELATION E IN EMPLOYEES
WITH e.city = "Geneva"
SELECT FIELDS ALL
LOG FILE IS pers$disk:[dba]to_geneva_rep.log
END.
******************************************
SCRIPT
The TO clause specifies the target database name.
Here we are creating a database called geneva_personnel on the
GENEVA node.
******************************************
VAX DATA DISTRIBUTOR (slide 42) (SE944.041)
DEFINE TRANSFER Examples
DATABASE FILENAME pers$disk:[somedir]personnel
DEFINE TRANSFER to_geneva_rep REPLICATION
DESCRIPTION IS /* Replication for Geneva */
TO geneva::disk2:[anydir]geneva_personnel
MOVE RELATION E IN EMPLOYEES
WITH e.city = "Geneva"
SELECT FIELDS ALL
LOG FILE IS pers$disk:[dba]to_geneva_rep.log
END.
******************************************
SCRIPT
The MOVE RELATION clause specifies three things to be transferred:
1. The relation,
2. The records within that relation, and
3. The fields within that relation
In this case, we're moving only those records from the employees
relation where the city is Geneva.
We select all the fields in the relation so that all those fields
will be transferred.
If the employees relation in the source contains 10 fields, then
there will be 10 fields in the target database.
We could have chosen to list individual fields rather than using
the keyword ALL.
That will be seen in the next example.
******************************************
VAX DATA DISTRIBUTOR (slide 43) (SE944.042)
DEFINE TRANSFER Examples
DATABASE FILENAME pers$disk:[somedir]personnel
DEFINE TRANSFER to_geneva_rep REPLICATION
DESCRIPTION IS /* Replication for Geneva */
TO geneva::disk2:[anydir]geneva_personnel
MOVE RELATION E IN EMPLOYEES
WITH e.city = "Geneva"
SELECT FIELDS ALL
LOG FILE IS pers$disk:[dba]to_geneva_rep.log
END.
******************************************
SCRIPT
The last clause is the specification of a log file.
This log file is used by the copy process to log information about
the execution of this particular transfer.
It may be useful for diagnosing the cause of a transfer failure.
******************************************
VAX DATA DISTRIBUTOR (slide 44) (SE944.043)
DEFINE TRANSFER Examples
Define an Extraction to Move the
Records of Those Employees Who Live
in Geneva and the Employee ID and Year
Given for All Employees Who Have Degrees
******************************************
SCRIPT
Let's look at a slightly more complex example.
It shows that you can move subsets of data from more than one
relation.
It also shows that individual fields of a relation can be selected
for transferring.
In this example, I want to define an extraction transfer to move
the employee records of those employees who live in Geneva.
Additionally, for all employees who have degrees, I want to move
the employee id and the year in which the degree was given.
It is also possible to transfer the definition of VIEWs; but that
is not shown in these examples.
******************************************
VAX DATA DISTRIBUTOR (slide 45) (SE944.086)
DEFINE TRANSFER Examples
DEFINE TRANSFER to_geneva_ext EXTRACTION
DESCRIPTION IS /* Extraction for Geneva */
TO geneva::disk2:[anydir]geneva_personnel_deg
MOVE RELATION e IN employees
WITH e.city = "Geneva"
SELECT FIELDS ALL;
MOVE RELATION d IN degrees
SELECT FIELDS d.employee_id,
d.year_given
LOG IS pers$disk:[dba]to_geneva_ext.log
PROLOGUE FILE IS pers$disk:[dba]connect.com
EPILOGUE FILE IS pers$disk:[dba]disconnect.com
END.
******************************************
SCRIPT
In this case, since we are defining an extraction, the EXTRACTION
keyword is used.
******************************************
VAX DATA DISTRIBUTOR (slide 46) (SE944.044)
DEFINE TRANSFER Examples
DEFINE TRANSFER to_geneva_ext EXTRACTION
DESCRIPTION IS /* Extraction for Geneva */
TO geneva::disk2:[anydir]geneva_personnel_deg
MOVE RELATION e IN employees
WITH e.city = "Geneva"
SELECT FIELDS ALL;
MOVE RELATION d IN degrees
SELECT FIELDS d.employee_id,
d.year_given
LOG IS pers$disk:[dba]to_geneva_ext.log
PROLOGUE FILE IS pers$disk:[dba]connect.com
EPILOGUE FILE IS pers$disk:[dba]disconnect.com
END.
******************************************
SCRIPT
Here we have the same MOVE clause as before: we are moving the
records of those employees who live in Geneva.
******************************************
VAX DATA DISTRIBUTOR (slide 47) (SE944.045)
DEFINE TRANSFER Examples
DEFINE TRANSFER to_geneva_ext EXTRACTION
DESCRIPTION IS /* Extraction for Geneva */
TO geneva::disk2:[anydir]geneva_personnel_deg
MOVE RELATION e IN employees
WITH e.city = "Geneva"
SELECT FIELDS ALL;
MOVE RELATION d IN degrees
SELECT FIELDS d.employee_id,
d.year_given
LOG IS pers$disk:[dba]to_geneva_ext.log
PROLOGUE FILE IS pers$disk:[dba]connect.com
EPILOGUE FILE IS pers$disk:[dba]disconnect.com
END.
******************************************
SCRIPT
This second MOVE RELATION clause moves all the records in the
degrees relation, but only the employee_id and year_given fields
will be created in the target database.
The prologue names a DCL command procedure which establishes a
network connection to the target site.
The prologue procedure executes before the actual data transfer.
After the data transfer, an epilogue procedure is used to break the
network connection.
******************************************
VAX DATA DISTRIBUTOR (slide 48) (SE944.094)
DEFINE TRANSFER Example
INVOKE DATABASE DB1=FILENAME
tokyo::disk2:[somedir]tokyo_sales.rdb
INVOKE DATABASE DB2=FILENAME
sydney::disk2:[somedir]sydney_sales.rdb
DEFINE TRANSFER rollup_from_branches EXTRACTION ROLLUP
DESCRIPTION IS /* SALES from all branch offices */
TO sales$disk:[dba]rollup_from_branches
MOVE RELATION s in db1.sales INTO all_sales
SELECT FIELDS s.part_number,s.quantity_sold,
s.dollar_amount;
MOVE RELATION s in db2.sales INTO all_sales
SELECT FIELDS s.part_number,s.quantity_sold,
s.dollar_amount
LOG IS sales$disk:[dba]rollup_from_branches.log
END.
******************************************
SCRIPT
Here is an example of an extraction rollup transfer.
First it is necessary for you to invoke each source database and
specify for each a unique database handle.
The slide is in error here; the handles should be DB1 and DB2.
The DEFINE TRANSFER line shows the type of transfer by the keyword
phrase EXTRACTION ROLLUP.
Note the form of both MOVE RELATION clauses.
Each specifies the "sales" relation in a particular source
database.
Both move the sales records into the same target relation,
"all_sales".
******************************************
VAX DATA DISTRIBUTOR (slide 49) (SE944.047)
DEFINE SCHEDULE
******************************************
SCRIPT
Let's look at an example using the DEFINE SCHEDULE statement.
******************************************
VAX DATA DISTRIBUTOR (slide 50) (SE944.048)
DEFINE SCHEDULE Example
Define a Schedule to Start the TO_GENEVA_EXT
Transfer on 1-Oct-1987 at 2:00 P.M. and
Execute it Every Monday Thereafter at
1:30 A.M.
If it Fails While Executing, Retry the
Execution Every 1 Hour and 15 Minutes
Up to 5 Times before Going Back to
the Regular Schedule
******************************************
SCRIPT
Here our problem is to define a schedule for the to_geneva_ext
transfer that first executes on 1-Oct at 2 in the afternoon.
It should execute every Monday thereafter at 1:30 AM.
If the execution fails, it should not wait until the following
Monday to execute.
Instead it should wait an hour and 15 minutes and then try again.
It should retry no more than five times before going back to the
regular schedule.
******************************************
VAX DATA DISTRIBUTOR (slide 51) (SE944.050)
DEFINE SCHEDULE Example
DEFINE SCHEDULE FOR to_geneva_ext
START 01-OCT-1987 14:00:00
EVERY MONDAY AT 01:30:00
RETRY 5 TIMES
RETRY EVERY 0 01:15:00
END.
******************************************
SCRIPT
We are defining a schedule for the to_geneva_ext transfer, so we
use that transfer name.
As with the DEFINE TRANSFER statement, DEFINE SCHEDULE is
terminated with an END clause.
The transfer is due to START October 1, 1987, at 2 p.m, and will
repeat EVERY MONDAY thereafter at 1:30 a.m.
Note that we could have listed several days of the week, for
example, EVERY MONDAY AT 1:30 and every THURSDAY at 4:00.
If a transfer that starts at 1:30 a.m. on Monday fails part way
through, say, at 2:00 a.m., then without the RETRY information the
transfer would not reexecute until the following Monday at 1:30.
With the RETRY information, it would wait an hour and a quarter
following the failure, until 3:15 a.m., and try again.
******************************************
VAX DATA DISTRIBUTOR (slide 52) (SE944.055)
Tying it All Together
********************************************
SCRIPT
Let's tie together what we've discussed so far.
I'll show which components get used when you enter a VAX Data
Distributor statement.
Also, I'll show which components get activated when a transfer is
executed.
We'll begin with the steps for an extraction, and then we'll look
at a replication.
**********************************************************
VAX DATA DISTRIBUTOR (slide 53) (SE944.056)
Extraction Transfer
RDO Communicates with the Transfer Monitor
RDO> DEFINE TRANSFER
VMS TRANSFER
RDO> DEFINE SCHEDULE Mailbox MONITOR
***************************************************
SCRIPT
You are at a terminal and enter DEFINE TRANSFER and DEFINE SCHEDULE
to define and schedule the to_geneva_ext transfer.
RDO, using a VMS mailbox, sends messages to the transfer monitor.
The messages contain the definitional information.
***************************************************
VAX DATA DISTRIBUTOR (slide 54) (SE944.057)
Extraction Transfer
Transfer Monitor Stores Definitions in Transfer Database
Transfer --------> Transfer Database
Monitor Transfer:
TO_GENEVA_EXT
***************************************************
SCRIPT
The transfer monitor stores the transfer and schedule definitions
for the to_geneva_ext transfer in the transfer database.
Remember that the transfer database is the repository for all
transfer and schedule definitions and their associated status
information.
***************************************************
VAX DATA DISTRIBUTOR (slide 55) (SE944.058)
Extraction Transfer
Transfer Monitor Starts a Copy Process
Transfer -------------> Copy
Monitor Process
**************************************************
SCRIPT
At the scheduled time, for example, October 1 at 2:00 PM, the
transfer monitor starts up a copy process to execute the transfer.
**************************************************
VAX DATA DISTRIBUTOR (slide 56) (SE944.059)
Extraction Transfer
Copy Process Reads Transfer Database
Copy <--------- Transfer Database
Process Transfer:
TO_GENEVA_EXT
*************************************************
SCRIPT
The copy process reads information about the transfer from the
transfer database.
This would include, among other things:
o the source database name,
o the target database name, and
o the database subset specification.
**************************************************
VAX DATA DISTRIBUTOR (slide 57) (SE944.060)
Extraction Transfer
Copy Process Creates Target Database
Copy --------> Target
Process Database
**************************************************
SCRIPT
The Copy Process creates the target database using the target
database name obtained from the transfer definition.
It reads the transfer definition from the transfer database.
**************************************************
VAX DATA DISTRIBUTOR (slide 58) (SE944.061)
Extraction Transfer
Copy Process Transfers Data to Target Database
Copy --------------> Target Database
Process Relations:
Employees
\ Degrees
\
Source Database
Relations:
Employees
Degrees
Salary_History
**************************************************
SCRIPT
Using the subset specification in the transfer definition, the copy
process extracts the appropriate metadata and data from the source
database and transfers it to the target database.
For example, for the to_geneva_ext transfer, the employees and
degrees relations are created in the target database.
**************************************************
VAX DATA DISTRIBUTOR (slide 59) (SE944.062)
Extraction Transfer
Status Information Updated
SS$_NORMAL
Copy ----------------> Transfer
Process Monitor
/
/
Transfer Database
Transfer:
TO_GENEVA_EXT
Status
Success
**************************************************
SCRIPT
Finally, the copy process sends back status information to the
transfer monitor, which stores the status in the transfer database.
In this case, the transfer was successful as indicated by the
status of SS$_NORMAL.
Had the transfer failed, an error status would have been returned.
Once the transfer completes, successfully or not, the transfer
monitor checks the transfer's schedule and status to determine the
time the transfer should next be attempted.
**************************************************
VAX DATA DISTRIBUTOR (slide 60) (SE944.063)
Two Types of
Replication Transfers
o Initial Transfer
o Update Transfer
**************************************************
SCRIPT
With replications, there are two types of transfers.
The initial transfer is like an extraction in that the whole
database subset is transferred.
The update transfer is one in which only those records which
changed since the last transfer execution are transferred.
**************************************************
VAX DATA DISTRIBUTOR (slide 61) (SE944.064)
Initial Replication Transfer
Like Extraction Transfer Except
o Special System Relations Created in
Source if Necessary
o Extra Fields, Relations, and Indices
Created in Target
**************************************************
SCRIPT
With respect to the source database, the initial replication
transfer is like an extraction, except that the special system
relations are created in the source if necessary.
Then, information about the transfer will be stored in the special
system relations so that changes can be tracked.
As with an extraction, the initial run of a replication transfer
creates a new target database.
Both extractions and replications create special fields and
relations in a target database. In addition for replications,
special indices are added.
This additional metadata contained in the source and the target is
used by VAX Data Distributor to keep the source database and target
database synchronized.
***************************************************
VAX DATA DISTRIBUTOR (slide 62) (SE944.065)
Update Replication Transfer
Interesting Records Journalled to Special System Relations
Update
User ----------> Source Database
Relations:
Rdb$CHANGES
Employees
***************************************************
SCRIPT
Let's follow what happens during a replication update.
After the initial transfer, any changes to records which are
relevant to the replication transfer will be journalled to the
special system relations in the source database.
For example, since the to_geneva_rep replication is interested in
those employee records associated with Geneva, any changes made to
records of employees from Geneva will be journalled to the special
system relations.
Also, if an employee record with a city other than Geneva is
changed to Geneva, then that record would also be journalled.
***************************************************
VAX DATA DISTRIBUTOR (slide 63) (SE944.066)
Update Replication Transfer
Reads from Special System Relations and
Applies to Target Database
Copy
Process ---------> Target Database
Relations:
\ Employees
\ Degrees
Source Database
Relations:
Rdb$CHANGES
Employees
Degrees
Salary_History
***************************************************
SCRIPT
The time comes to execute the transfer.
As with extractions, a copy process is started by the transfer
monitor.
The copy process reads information about the transfer from the
transfer database.
Then using information stored in the special system relations, it
transfers the changed records from the source and applies them to
the target.
***************************************************
VAX DATA DISTRIBUTOR (slide 64) (SE944.067)
Update Replication Transfer
Copy Process Updates Data Distributor Information
Copy
Process ----------> Target Database
Relations:
\ RDB$VINTAGE
\
Source Database
Relations:
RDB$TRANSFERS
RDB$VINTAGE_TSER
RDB$CHANGES
RDB$TRANSFER_RELATIONS
***************************************************
SCRIPT
Finally, the copy process updates the special system relations in
the source and the VAX Data Distributor managed metadata in the
target.
**************************************************
VAX DATA DISTRIBUTOR (slide 65) (SE944.068)
Cluster Support
**************************************************
SCRIPT
Vax Data Distributor has several features that take advantage of
the multiple cpu shared disk environment of a cluster.
**************************************************
VAX DATA DISTRIBUTOR (slide 66) (SE944.069)
Cluster Support
o Single Transfer Database per Cluster
***************************************************
SCRIPT
In a cluster environment, you would typically have a single
transfer database on a disk shared by all members of the cluster,
and a transfer monitor running on each node of the cluster.
***************************************************
VAX DATA DISTRIBUTOR (slide 67) (SE944.070)
Cluster Support
o Single Transfer Database per Cluster
o Transfers are Load-Balanced
***************************************************
SCRIPT
The transfer monitors load balance the transfer executions because
they all take part in scheduling copy process execution.
Even though a transfer may be defined by a user from one node of a
cluster, a copy process which executes the transfer may actually
run on another node in the cluster.
***************************************************
VAX DATA DISTRIBUTOR (slide 68) (SE944.071)
Cluster Support
o Single Transfer Database per Cluster
o Transfers are Load-Balanced
o Running Transfers Failover if
Transfer Fails
***************************************************
SCRIPT
VAX Data Distributor provides a type of failover protection to
handle machine crashes.
If a node in a cluster crashes, the transfers that were executing
on that node will be restarted on one of the remaining nodes of the
cluster.
***************************************************
VAX DATA DISTRIBUTOR (slide 69) (SE944.084)
Questions?
***************************************************
SCRIPT
Just to quickly summarize the key points...
o VAX Data Distributor is an automated extraction and replication
facility
o It is used to move subsets of data to remote sites in a timely
and orderly fashion
o Extractions and replications are defined using the familiar RDO
utility
This concludes the talk. Are there any questions?
***************************************************
|
| Decus presentation, Fall 1988.
DM064
Designing Rdb/VMS Database Applications Using VAX SQL
ABSTRACT
--------
VAX SQL is now an integral part of VAX Rdb/VMS. This presentation
covers the new capabilities and benfits of VAX SQL and Rdb/VMS.
This session discusses how to design your VAX Rdb/VMS databases
and applications making use of various interfaces of VAX SQL.
Details of interactive SQL, SQL precompiler and SQL module
language are presented with the help of examples. Also discussed
are methods for using VAX SQL to implement the performance tuning
features of VAX Rdb/VMS.
Slide Ordering Information
------
The order numbers for the 35mm slides are: EN445.001 ... EN445.057
Contact the Corporate Photo Library. EXIT26::PHOTO
If a script is available, you will receive it automatically.
SCRIPT
------
DESIGNING FUTURE Rdb/VMS DATABASES
WITH VAX SQL
**************************************************
< Introduce myself: >
The Goals of this talk are to give you a
o QUICK Introduction to VAX SQL.
o Concentrating on the concepts, and not on physical implementation.
o My Main Focus is going to be on NEW Rdb/VMS / VAX SQL features.
o Towards the later portion of this talk, I will be presenting
a walk through of the process of building an Rdb/VMS
application using VAX SQL.
o Some of you may be happy to note that I am NOT going
to use the PERSONNEL database in this talk.
o For those who have attended the Rdb/VMS talk given by
Jeff Balboni on Monday, this talk will complement it through an
example application.
o VAX SQL will support ALL the new features of the latest release of
Rdb/VMS as mentioned in Jeff's talk.
What I don't intend doing in this talk is :
o To present tuning or configuration guidelines for Rdb/VMS.
This is not a tutorial on VAX SQL syntax and
This is not an internals talk.
Please, keep the detailed questions to the end but stop me at any time
for clarifications.
****************************************************
SQL OVERVIEW
o What is SQL?
o VAX SQL and the VAX INFORMATION ARCHITECTURE
o VAX SQL Interfaces
o NEW FEATURES
*******************************************************
Here is the overview of the talk.
I am going to briefly cover What SQL is and How VAX SQL
is related to the digital's family of database products
commonly known as the VAX Information Architecture.
I will be talking about the different interfaces of VAX SQL namely
the interactive SQL, static and dynamic SQL and the Module
language of SQL.
There will be a strong emphasis on the NEW features that
are available in the latest versions of Rdb/VMS and VAX SQL.
********************************************************
WHAT IS SQL?
o Structured Query Language
o Relational Database LANGUAGE
o ANSI STANDARD X3.135-1986
o ISO STANDARD 9075-1987
o Implemented across a wide range of systems
********************************************************************
SQL, which is sometimes pronounced as SEQUEL, stands for
structured query language. It is a
relational database language developed by IBM in 1970's.
It was released as part of a commercial product with SQL/DS in
1981. In 1983, IBM introduced DB2 which also had an SQL interface.
In 1986, the American National Standards Institute, commonly known
as ANSI, approved a standard for SQL that was basically a subset of
DB2 with a few differences. This ANSI standard is called X3.135-1986
or SQL-86 and so on.
Subsequently, ISO, the International Standards Organization also
adopted the same SQL with no modifications as their standard relational
database language.
This standard SQL is now being implemented across a wide range of
systems by several vendors.
But at this time there is no implementation that I am aware of,
that is certified to be compliant with the ANSI Standard.
Now, Lets talk about VAX SQL.
********************************************************************
WHAT IS VAX SQL?
o DIGITAL's implementation of ANSI SQL layered on Digital's Relational
Database Architecture
o Provides both an INTERACTIVE and a PROGRAMMING interface
********************************************************************
VAX SQL is Digital's implementation of ANSI SQL layered on
Digital's Relational Database Architecture.
VAX SQL provides both an interactive as well as a programming
interface to access Rdb/VMS and VIDA databases.
You must be already familiar with VIDA if you had been to the talk
on tuesday.
VIDA with IDMS/R is basically a database interoperability
product that allows direct access to cullinet's IDMS/R database
as if that data were actually present in a local Rdb/VMS database.
********************************************************************
VAX SQL AND DIGITAL'S RELATIONAL DATABASE ARCHITECTURE
+-----------+ +---------+ + -------+
|INTERACTIVE| | SQL | | SQL |
| SQL | | PRECOMP | | MODULE |
+-----------+ +---------+ +--------+ DTR TEAMDATA RALLY RDO RDML
| | | | | | | |
+--------------+------------+---------+---------+------+------+------+
|
|
+-------------------------------+
| DIGITAL'S STANDARD RELATIONAL |
| DATABASE INTERFACE |
+-------------------------------+
|
|
+--------------------------------------------------+
| |
Rdb/VMS VIDA WITH IDMS/R
DATABASE EXECUTIVE DATABASE EXECUTIVE
***********************************************************************
There are three SQL interfaces that speak through digital's standard
relational interface which allows you to access Rdb/VMS and VIDA
databases. They are interactive SQL, SQL Precompiler and
SQL Module language.
And as you can see, RDO and RDML access these databases in the
same method as VAX SQL.
VAX SQL can be used to access and maintain the databases that were
created by Teamdata, Rally or Datatrieve.
In the next few slides, I am going to give you detailed descriptions
of the three VAX SQL interfaces.
***********************************************************************
INTERACTIVE VAX SQL
o Prototyping Environment
o Full Data Definition and Data Manipulation
Languages
o On-line help (learning tool)
o Same* Performance as RDO
***********************************************************************
When do you use Interactive SQL?
It is very useful for prototyping an environment. By making use
of interactive SQL you could get the results of ad-hoc queries
immediately and thus allows you to test the queries before
embedding them in programs.
Interactive SQL has both Data Definition and Data Manipulation
statements.
The on-line help is very useful as a learning tool.
We expect the same performance for SQL queries as those in RDO
because both SQL and RDO speak through the same architecture.
***********************************************************************
VAX SQL PREPROCESSORS
o Support for popular programming languages
- VAX ADA
- VAX C
- VAX COBOL
- VAX FORTRAN
- VAX PL/I
o One common precompiler for all languages
o Interface is similar to RDBPRE
o Same* performance as RDBPRE/RDML
***********************************************************************
VAX SQL preprocessors provide a way to embed
the SQL statements in a programming language.
VAX SQL preprocessors support embedding of SQL statements in a
number of popular programming languages such as VAX ADA, VAX C,
VAX COBOL, VAX FORTRAN, VAX PL/1 .
Support for ADA was added in the latest version of the product.
Since ADA doesn't have a default parameter passing mechanism,
VAX SQL implemented ADA binding as proposed in ANSI SQL binding.
If a default ADA binding is published in future we plan to support it.
All languages make use of a common precompiler called SQLPRE.
The SQLPRE interface is similar to RDBPRE, which is the precompiler
used for RDO or RDML queries embedded in programming languages.
Since both SQLPRE and RDBPRE speak through the same architecture,
similar queries thru these interfaces are expected to have the same
performance.
***********************************************************************
VAX SQL DYNAMIC INTERFACE
o Allows SQL program to accept or generate SQL statements
at run time
o Embedded interface
o Similar to DB2's dynamic interface
o Available from Precompiler or Module Language
***********************************************************************
VAX SQL dynamic interface can be used if you do not know what the
queries are until run time. e.g., if you are creating a special
end user query interface, where you do not know what the query is
until the user types some information, then dynamic interface
becomes useful.
Both Static SQL and dynamic SQL have the same interface in the
sense that both have to be embedded in a programming language.
The difference is how they are executed.
In case of static SQL, Queries are parsed and compiled at compile
time and executed at run time.
In case of dynamic SQL, Queries are parsed,compiled and executed at
run time.
The dynamic interface of VAX SQL is similar to that of IBM's DB2
and this interface is available form both the precompiler and
SQL module language.
***********************************************************************
VAX SQL MODULE LANGUAGE
o Allows development of independent SQL modules
o Does not require preprocessors
o Acessable from ANY language that follows the
VAX calling standards.
***********************************************************************
MODULE language is a new feature of VAX SQL version 2.0
This allows development of independent SQL modules which
contain pure SQL code.
Since, this is like any other programming language, no other
preprocessor is required.
Procedures in SQL Module language are accessible from any language
that conforms to VAX calling standards.
This language is very useful when precompiler support is not available,
as is the case with VAX basic and VAX pascal.
Use of Module language is illustrated in the case study.
< This is a big advantage in mixed language shops since you only need to
maintain one SQL module instead of embedding the queries in several
languages.>
***********************************************************************
VAX SQL BACKGROUND:
o VAX SQL V1.0 shipped April 1987
Priorities: DB2 compatibility
ANSI SQL draft
o VAX SQL V1.1 shipped November 1987
Priorities: ANSI STANDARD
DB2 compatibility*
o VAX SQL Future Versions
Priorities: ANSI SQL STANDARD
Digital's Database Architecture
DB2 compatibility*
*************************************************************
Little bit of background on VAX SQL.
Version 1.0 was shipped in April 1987.
Since only the working draft of the standard was available,
more prority was given for DB2 compatibility.
Version 1.1 got shipped in Nov 1987.
Version 2.0 of VAX SQL along with Rdb/VMS 3.0 were announced in July
1988 as part of the DECTP announcement. Since Rdb/VMS and SQL are
now packaged together, if you buy Rdb/VMS, VAX SQL comes with it.
You may have noticed that starting from version 1.1, higher
priority was being given to ANSI SQL,
to emphasize the fact that Digital is committed to
the industry standards efforts. At the same time, we believe that
it is important to provide DB2 compatibility.
In future, we are going to extend VAX SQL to take advantage of
Digital's database architecture as it evolves.
<You cannot be fully compatible with DB2 and at the same time
fully compatible with the ANSI standard because of the way some
of the features are defined. e.g., in case of LIKE statement,
ANSI: It is case sensitive
DB2 : case insensitive.>
Between RDML and SQL, it is your personal choice to choose
any one of them.
In order to take advantage of the standard database language,
we recommend that ANSI SQL be chosen for all new database
applications.
*************************************************************
MAJOR NEW FEATURES
o Ada Precompiler
o Module Language
o Improved Formatting in Interactive SQL
o *GROUP BY* Performance improved
o Enhanced Data Definition Language
o Closer compliance to ANSI Standard
o Domains
o Referential Integrity
o Support for Language Sensitive Editor (limited)
**********************************************************
These are some of the major new features of VAX SQL V2.0.
VAX SQL Precompiler now supports Ada programs with embedded SQL
statements.
Module language is provided which allows access to
Rdb/VMS database from any language that conforms to
VAX Calling standard.
Formatting in interactive SQL is improved.
Group by performance is improved since the architecture
now supports aggregates. In the previous versions, we had
to emulate the architecture support for group by.
New constructs are added to the DDL statements.
As mentioned before, no one is ANSI validated.
but the conformance of VAX SQL has been greatly improved
in this version.
The last three features, domains, referential integrity
and support for LSE, will be seen in a later example.
**********************************************************
NEW FEATURES OF Rdb/VMS AND VAX SQL HIGHLIGHTED IN THE EXAMPLE
o Multi-file
o Indexes
o Storage Maps
o Record Clustering
o Module Language
o Enhanced Formatting
o Domains
o Constraints
o Use of LSE
***********************************************************************
These are some of the major new features of Rdb/VMS all of
which are fully supported by VAX SQL.
I am only going to very briefly touch upon these features.
A little later, I am going to explain them in more detail
as we go through the case study.
***********************************************************************
MULTI-FILE
o Allows users to create data and snapshot files for
specified data.
o Allows users to balance the database over many disks
o Removes the disk bottleneck from database applications
******************************************************************
The top prority feature for Rdb/VMS was to improve the
performance in its latest version.
Rdb eliminated its performance bottleneck
by implementing the multi-file database.
Basically, this feature allows users to create multiple
data and snapshot files for specific data objects.
And, thus, it allows users to balance their data on many disks.
Disk bound or I/O intensive queries no longer need to be queued
to just one disk since there are now many other data areas.
These data areas are referred to as storage areas.
******************************************************************
STORAGE AREAS
o Data files for Rdb/VMS databases
******************************************************************
Storage area is just another name for a file in a
multifile database.
******************************************************************
INDEXES
o Allows more control over index structure
o New index type allows faster single record retrieval
o Allows user to control where the index gets stored
******************************************************************
VAX SQL now gives you more control over your B-tree index
structures. Now, You can specify fill factor for b-trees
so that updates can be efficiently performed. On the other
hand, you could specify whether the index is used for
update or query activity in which case Rdb/VMS will select
the appropriate fill factor for the B-Tree.
Previously, you could only use a sorted B-tree index. But, now,
VAX SQL will provide you with a new type of index called
HASHED index. This structure allows faster exact match
retrievals.
CREATE INDEX statement now has a STORE clause which determines where
the index records get stored. And hence, you, now, have more control
over where and how the index structure gets stored.
This will be illustrated in an example later.
******************************************************************
STORAGE MAPS
o Gives users control over where the data gets stored
o Gives the user some control on where the rows get
placed
o Allows the user to cluster related rows from diffrent
tables together
******************************************************************
Storage maps are links between rows of tables and storage areas.
They tell the database executive, how the logical tables
get phisically stored.
In order to improve the performance,
users or DBA's could store rows from different tables
together on the same storage area or distribute rows from a
single table over several storage areas.
******************************************************************
RECORD CLUSTERING
o Gives the user the control to place related rows from
different tables together
o Gives the user the control to place an index and its
row together
******************************************************************
As mentioned in the previous slide, by making use of
storage maps, related rows from different tables could be
stored on the same storage area, which is known as record clustering.
For example, a table and its index structure could be stored
in the same storage area. And thus, you could potentially
retrieve rows from a table and its index in a single
I/O operation.
******************************************************************
DOMAINS
o A definition that is database wide and on which column
definitions for specific tables are defined
o Data types
o Formatting: Query headers, Edit strings
******************************************************************
Domains are objects which are defined database wide.
Columns from different tables could be defined based on
a single domain definition.
Domains, which are basically data types, are analogous
to global fields in RDML.
One of the benefits of domains is that if you need to change
a data type of an attribute, you need to ALTER only one
domain definition instead of ALTERing the definitions of
all the tables that contain the attribute.
By making use of Query Header, Edit String clause in the
domain definition, we can provide much better output
formatting of the column values of a table.
Domains are illustrated in the next slide.
******************************************************************
DOMAINS
CREATE TABLE ORDER (
ORDER_NUM CHAR(25),
ORDER_CUST CHARACTER(10),
CREATE DOMAIN ORDER_NUMBER
CHARACTER(25)
EDIT STRING IS "XXX-XXX-XXX-XXXXXXX-XXXX-XX-XXX"
QUERY HEADER IS "Order"/"Number"
;
CREATE DOMAIN CUSTOMER_ID
CHARACTER(10)
EDIT STRING IS "XX-XXXX-XXX"
QUERY HEADER IS "Customer"/"Number"
;
CREATE TABLE ORDER (
ORDER_NUM ORDER_NUMBER,
ORDER_CUST CUSTOMER_ID,
******************************************************************
The first create statement is a simple table definition
with two columns ORDER_NUM as CHAR 25 and ORDER_CUST with
char 10.
The next two statements defined ORDER_NUMBER and
CUSTOMER_ID domains. And the last statement is another
way of defining the same ORDER table making use of the
domains. As you can see, the domain definitions allow us
to specify query headers and edit strings. What exactly these
query headers and edit strings mean can be clarified by looking
at the next slide.
******************************************************************
DOMAINS / FORMATING
SQL> Select order_num, order_cust from order
where order_num = 021axb89500000012321qw544;
Without domains
---------------
ORDER_NUM ORDER_CUST
021axb89500000012321qw544 010000114
1 row(s) selected
With domains
------------
Order Customer
Number Number
021-axb-895-0000001-2321-qw-544 01-0000-114
1 row(s) selected
******************************************************************
Here we have a simple select statement to retrieve a record
from ORDER table.
without domains, the query headers are same as the column names
and the column values are printed as they were stored.
With the domains as shown in the previous slide, we would get
the output as shown with different query headers. The column
values are edited according to the edit string specification.
******************************************************************
CONSTRAINTS
o A condition that restricts values that can be stored in a table
CREATE TABLE PARTS_TABLE
PART_ID CHARACTER(10) NOT NULL UNIQUE,
.
.
.
.
o Referential Integrity
ALTER TABLE MANUFACTURE_ORDER
ADD CONSTRAINT CHECK
(O_NUM IN (SELECT ORDER_NUM FROM ORDER) );
******************************************************************
Constraints are conditions that restrict the values that
can be stored in a table. Constraints help in automatically
ensuring the integrity of the database.
In our create table we are placing a constraint NOT NULL
UNIQUE on PARTS_TABLE. Other constraint predicates such as
NOT NULL, NOT IN, LIKE, BETWEEN, CONTAINING, EXISTS are also
available.
The second bullet illustrates referential integrity.
Referential Integrity constraints make sure that updates on a table
do not violate some conditions on other related tables.
One example of a referential integrity constraint is this:
We do not want to enter an order in the manufacture_order table,
unless theorder_num already exists in ORDER table.
This could be achieved by defining the constraint check as
shown in the 2nd bullet. The database system will give an
error if this referential integrity constraint is violated.
Now that I have talked about a number of new features and concepts,
I will try to present a hypothetical environment and application
of most of these features so that they become clearer.
******************************************************************
DESCRIPTION OF SAMPLE ENVIRONMENT
o Over-simplified combination of Manufacturing and
Order-entry
o Used for demonstration purposes only
o Rdb/VMS database used to hold data for the four
main phases of the process
o VAX SQL used for database creation and data manipulation
******************************************************************
Please, bear with me for all the simplifications that are
made here. This environment is a combination of manufacturing
and order_entry situations.
Rdb/VMS database is used to hold data in all the phases
of this application.
VAX SQL is used for database creation and data manipulation.
******************************************************************
DATA TO BE USED
o Inventory of parts
o Orders
o Manufacturing process
o Shipment Archive
******************************************************************
The data that is used by this environment has 4 different flavours.
Inventory of parts needs to be maintained.
- There are three basic part types 1, 2 and 3.
- Information such as serial number, color, date of completion,
location is kept for each part.
- Queries could be like find all blue parts of type 3 and so on.
- There are many parts to keep track of and each part with
many attributes such as color, serial number etc.
ORDERS need to be maintained.
- Orders are entered nightly.
- Orders are queried for status.
- Orders need to be deleted after archival.
Manufacturing process needs to be kept track of.
- Order information is inserted.
- Information for each part is updated.
- Status is queried regularly
000 - idle or non-active orders
100 - incomplete/processing orders
200 - completed orders
- Orders are deleted after they are archived.
Phase 4
-------
Shipment archive
- inserts when manufacturing finished an order.
******************************************************************
ENVIRONMENT
+---------------+
| PARTS |
+---------------+
|
|
+-------------+ +---------------+ +-------------+
| ORDER |-------| Manufacturing |-------| ARCHIVE |
+-------------+ +---------------+ +-------------+
******************************************************************
Another picture of the environment.
Manufacturing gets information from ORDERS and PARTS.
Once the order is completed, the information is archived.
******************************************************************
DESIGN STEPS
1. Throw together a schema definition
2. Load DATABASE with prototype data
3. Prototype queries in Interactive SQL
4. Incorporate queries into Module Language
5. Write the host language program
6. Restructure schema for performance
7. Final test
8. Ship it
******************************************************************
These are some of the design steps that one has to go through to build
the environment.
And we had to follow the same steps to create even this
simplified case study.
Define the schema with the necessary tables
and load some sample data.
Test the queris in interactive SQL.
Incorporate those queries into module language.
Write and test the host program in a language of your choice.
Restructure the schema for improving performance.
e.g., add indices or create more storage areas and so on.
Finally, Test and ship it for production.
******************************************************************
FIRST SCHEMA DEFINITION
CREATE SCHEMA AUTHORIZATION TEST_DB
FILENAME TEST
CREATE TABLE PARTS_TABLE (
PART_TYPE SMALLINT,
PART_COLOR CHAR(6),
PART_ID CHARACTER(10),
PART_DATE DATE
)
CREATE TABLE ORDER (
ORDER_NUM CHAR(25),
ORDER_CUST CHARACTER(10),
ORDER_STATUS SMALLINT,
P1_NUMBER SMALLINT,
P1_COLOR CHAR(6),
P2_NUMBER SMALLINT,
P2_COLOR CHAR(6),
P3_NUMBER SMALLINT,
P3_COLOR CHAR(6)
)
******************************************************************
We created a table called PART_TABLE for storing the
parts and another table ORDER for storing the orders.
Smallint has a size of one word. 32767 to -32767
Date declaration is same as in RDML.
******************************************************************
CREATE TABLE ARCHIVE (
CUSTOMER CHARACTER(10),
ORDER CHAR(25),
SHIPPED DATE,
P1_NUMBER SMALLINT,
P1_COLOR CHAR(6),
P2_NUMBER SMALLINT,
P2_COLOR CHAR(6),
P3_NUMBER SMALLINT,
P3_COLOR CHAR(6)
)
CREATE TABLE MANUFACTURE_ORDER (
O_NUM CHAR(25),
STAT SMALLINT
)
CREATE TABLE MANUFACTURE_PART (
O_NUM CHAR(25),
P_TYPE SMALLINT,
P_ID CHARACTER(10),
P_LOC SMALLINT
)
;
******************************************************************
ARCHIVE table is basically same as ORDER table, except that
it contains an additional column for SHIPPED date.
We have two tables MANUFACTURE_ORDER and MANUFACTURE_PART.
******************************************************************
PICTURE
+---------------+
| PARTS |
+---------------+
|
|
+-------------+ +---------------+ +-------------+
| ORDER |-------| MANUF. ORDER |-------| ARCHIVE |
+-------------+ +---------------+ +-------------+
| MANUF. PARTS |
+---------------+
******************************************************************
So, here is our environment after the tables
the tables were created in the schema.
******************************************************************
PROTOTYPE YOUR QUERIES IN INTERACTIVE SQL
$ run sys$system:sql$
SQL> DECLARE SCHEMA FILENAME TEST;
SQL> DECLARE FINISHED_MANUFACTURE CURSOR FOR
cont> SELECT O_NUM FROM MANUFACTURE_ORDER
cont> WHERE STAT = 200;
SQL> OPEN FINISHED_MANUFACTURE;
SQL> FETCH FINISHED_MANUFACTURE;
O_NUM
021axb89500000012321qw456
******************************************************************
You can run SQL by typing $RUN sys$system:sql$ at the DCL prompt.
DECLARE SCHEMA statement attaches to the schema called TEST.
In programs, you typically have to use cursors to process select
statements that retrieve several rows.
DECLARE CURSOR statement specifies the select statement that you want
to process.
OPEN statement actually retrieves the data and positions the
cursor before the first row of the result table.
Programs can loop through the result table with a series of
Fetch statements to process all the rows.
In this example, the select statement is trying to retrieve records
of completed orders from manufacture_order table.
If you remember, 200 was the code for completed orders.
Here we fetched the order number of one completed order.
More details on this order can be obtained by retreiving the
corresponding row of ORDER table.
******************************************************************
PROTOTYPE YOUR QUERIES IN INTERACTIVE SQL
SQL> SELECT ORDER_NUM, ORDER_CUST,
cont> P1_NUMBER, P1_COLOR,
cont> P2_NUMBER, P2_COLOR,
cont> P3_NUMBER, P3_COLOR
cont> FROM ORDER WHERE ORDER_NUM = "021axb89500000012321qw678";
ORDER_NUM ORDER_CUST P1_NUMBER P1_COLOR P2_NUMBER
P2_COLOR P3_NUMBER P3_COLOR
021axb89500000012321qw678 010000234 3 blue 1
yellow 8 red
1 row(s) selected
******************************************************************
We want to find that order that is ready to be archived such as
the one we found in the previous slide.
< Explain the details from the slide>
If you forget the syntax at any time, use the on-line help...
******************************************************************
USE ON-LINE HELP
SQL> HELP INSERT
INSERT
INSERT INTO --+-> table-name --+-+------------------------------+-+
+-> view-name ---+ +-> ( -+-> column-name -+-> ) -+ |
+------ , <------+ |
+---------------------------------------------------------------+
+--> VALUES --> ( -++-> literal ---++-> ) -+-+
| |+-> parameter -+| | |
| |+-> NULL ------+| | |
| |+-> USER ------+| | |
| +---- , <--------+ | |
+--> select-expr --------------------------+ |
+--------------------------------------------+
++---------------------------------------------+-> ;
+--> RETURNING DBKEY --> INTO --> parameter --+
Additional information available:
More column_name RETURNING_DBKEY select_expr
VALUES
******************************************************************
This slide shows how to get help on insert statement.
This is the way it really looks.
More information can be obtained by typing the subtopics.
<NEXT slide is on editor>
VAX SQL allows you to use the editor for corrections.
******************************************************************
USE YOUR EDITOR FOR CORRECTIONS
Support for:
EDT
TPU
LSE
$ DEFINE SQL$EDIT LSE
$
******************************************************************
We support one of the three editors EDT, TPU or LSE.
You can make editor of your choice as the default editor of
VAX SQL by defining the logical SQL$edit .
If LSE or TPU is used, all section files, symbols and special
keys that you defined are active just as if you were
invoking these editors at DCL level.
******************************************************************
USE YOUR EDITOR FOR CORRECTIONS cont.
SQL> INSERT INTO MANUFACTURE_ORDER O_NUM VALUES ("10");
INSERT INTO MANUFACTURE_ORDER O_NUM VALUES ("10");
^
%SQL-W-LOOK_FOR_STT, Syntax error, looking for:
%SQL-W-LOOK_FOR_CON, ., (, VALUES, SELECT,
%SQL-F-LOOK_FOR_FIN, found O_NUM instead
SQL> EDIT
******************************************************************
Here we have a simple insert command with a syntax
error. This could be corrected by going into the
editor.
******************************************************************
INSERT INTO MANUFACTURE_ORDER O_NUM VALUES ("10");
>>INSERT INTO MANUFACTURE_ORDER (O_NUM) VALUES ("10");
[End of file]
* Exiting the editor executes the commands in the buffer
******************************************************************
EDIT defaults to one line. You can specify any number of lines.
After you finish with all the queries,
you can go back to the editor and write out
all the queries that you tried out into a file, to be
later used for writing the module language program.
******************************************************************
PLACE COMMANDS INTO AN SQL MODULE AND EDIT
--
-- Module language example for DECUS
-- The calling language is VAX C
--
MODULE DECUS_SQLMOD
LANGUAGE C
AUTHORIZATION RDB$DBHANDLE
******************************************************************
Now, lets see how a module language program looks like.
Module language program contains pretty much the same
queries that you typed in ISQL, with a few more
parameters and procedure constructs.
You need to state the language you are using in order
to provide default parameter passing mechanism. Specification
of the language allows the SQL MODULE processor to detect
errors if unsupported data types are declared.
This doesn't mean that you can't use this module from
another language, you just won't get the default passing
mechanism.
******************************************************************
PLACE COMMANDS INTO AN SQL MODULE AND EDIT (cont.)
-- Declares
DECLARE SCHEMA FILENAME TEST
DECLARE FINISHED_MANUFACTURE CURSOR FOR
SELECT O_NUM
FROM MANUFACTURE_ORDER
WHERE STAT = 200
******************************************************************
These commands are placed in this module via the editor
as mentioned before.
******************************************************************
PLACE COMMANDS INTO AN SQL MODULE AND EDIT (cont.)
-- Procedures
PROCEDURE OPEN_CURSOR SQLCODE;
OPEN FINISHED_MANUFACTURE;
.
.
.
PROCEDURE FIND_FINISHED_ORDER
SQLCODE
ORDER_NUMBER CHAR(25)
CUSTOMER_NUMBER CHAR(10)
P1_NUM SMALLINT
P1_COLR CHAR(6)
P2_NUM SMALLINT
P2_COLR CHAR(6)
P3_NUM SMALLINT
P3_COLR CHAR(6);
SELECT ORDER_CUST, P1_NUMBER, P1_COLOR,
P2_NUMBER, P2_COLOR, P3_NUMBER, P3_COLOR
INTO CUSTOMER_NUMBER, P1_NUM, P1_COLR, P2_NUM, P2_COLR,
P3_NUM, P3_COLR
FROM ORDER WHERE ORDER_NUM = ORDER_NUMBER;
.
.
.
******************************************************************
There are two procedures written in this module.
OPEN_CURSOR is used to open the cursor.
The parameter SQLCODE is the RETURN STATUS variable used
for all the procedures.
FIND_FINISHED_ORDER contains a little more complicated query
with parameters defined to be used in this query.
This query is same as before except for the INTO clause
which contains the formal parameters of this procedure.
Values in these variables are passed back to the host
program.
******************************************************************
COMPILE THE VAX SQL MODULE
$ RUN SYS$SYSTEM:SQL$MOD
INPUT FILE> DECUS_SQLMOD
$
******************************************************************
Module language program is compiled by running SQL$MOD.
Now, it is time to write the host language program to
call the procedure.
******************************************************************
WRITE HOST LANGUAGE PROGRAM
.
.
.
OPEN_CURSOR( &sqlcode );
if (sqlcode < 0) handle_err( sqlcode );
/*PROCEDURE OPEN_CURSOR SQLCODE;
OPEN FINISHED_MANUFACTURE; */
.
.
.
******************************************************************
Calling an SQL procedure is similar to calling any other
procedure in the host language.
As you can easily recognize, the host language used here
is C.
OPEN_CURSOR procedure is called from here and the parameter
SQLCODE is passed by address. Its value can be checked for
handling errors.
******************************************************************
WRITE HOST LANGUAGE PROGRAM cont.
STORE_ARCHIVE( &sqlcode, &customer_number, &order_number,
&p1_num, &p1_color, &p2_num, &p2_color,
&p3_num, &p3_color );
if (sqlcode < 0) handle_err(sqlcode);
/*
PROCEDURE STORE_ARCHIVE
SQLCODE CUSTOMER_NUM CHAR(10) ORDER_NUM CHAR(25)
P1_NUM SMALLINT P1_COLR CHAR(6) P2_NUM SMALLINT P2_COLR CHAR(6)
P3_NUM SMALLINT P3_COLR CHAR(6);
INSERT INTO ARCHIVE ( CUSTOMER, ORDER, P1_NUMBER, P1_COLOR,
P2_NUMBER, P2_COLOR, P3_NUMBER, P3_COLOR )
VALUES (CUSTOMER_NUM, ORDER_NUM, P1_NUM, P1_COLR,
P2_NUM, P2_COLR, P3_NUM, P3_COLR );
*/
******************************************************************
This is an example of how to pass parameters to a slightly more
complicated SQL procedure.
The proceudre STORE_ARCHIVE that you see with in comment
delimiters /* */ is the procedure present in our SQL
module.
******************************************************************
COMPILE HOST PROGRAM ---> LINK ---> AND RUN
$ CC DECUS_HOST
$ LINK DECUS_HOST, DECUS_SQLMOD
$ RUN DECUS_HOST
Processing order 021axb89500000012321qw678
done
$
******************************************************************
All we need to do is to link the host language object
And SQL module object and run the image.
The experts that we are in writing bug free code, we didn't
find any errors and it is time to restructure the database
for better performance.
******************************************************************
RESTRUCTURE SCHEMA FOR PRODUCTION
o Through the ALTER command
ALTER SCHEMA
ALTER STORAGE MAP
ALTER STORAGE AREA
ALTER INDEX
.
.
.
o Through IMPORT/EXPORT STATEMENTS
SQL> EXPORT SCHEMA FILENAME TEST INTO IMPORT_TEST;
SQL> DROP SCHEMA FILENAME TEST;
SQL> IMPORT SCHEMA FROM IMPORT_TEST FILENAME TEST
cont> CREATE STORAGE AREA YELLOW_PARTS
cont> CREATE STORAGE MAP ARCHIVE_MAP FOR ARCHIVE
.
.
.
******************************************************************
VAX SQL allows you to restructure your schema in two ways.
In the first method, you can use the ALTER command.
With this command you can make simple changes to the schema.
However, for more complex and complete restructuring, use the
IMPORT/EXPORT commands. These commands are not as restricting
as the ALTER commands.
As can be seen in the slide, several metadata objects such as
storage areas, storage maps, indexes can be created in an
IMPORT statement.
You can create a multi file database from a single file database
by using export and import statements.
<ALTER command does not allow single file database to multifile
database and vice versa. Root file parameters, storage area
parameters cannot be changed using ALTER>
******************************************************************
RESTRUCTURE SCHEMA FOR PRODUCTION
CREATE SCHEMA AUTHORIZATION DECUS_DB
FILENAME TEST;
CREATE STORAGE AREA PART_ONE
FILENAME P1_AREA
CREATE STORAGE AREA PART_TWO
FILENAME P2_AREA
CREATE STORAGE AREA PART_THREE
FILENAME P3_AREA
CREATE STORAGE AREA SPECIAL_PARTS
******************************************************************
This is how the final create schema would look like:
These statements make use of the multifile features of
Rdb/VMS.
Since we know that really large number of records are going
to be present in the Parts table, we could define 4 different
storage areas to hold all the records. By allocating different disks
for each of these storage areas, a disk bottle neck
can be avoided.
******************************************************************
RESTRUCTURE SCHEMA FOR PRODUCTION (cont.)
CREATE STORAGE AREA ORDERS_ONE
FILENAME ORDERS1
CREATE STORAGE AREA ORDERS_TWO
FILENAME ORDERS2
CREATE STORAGE AREA ORDERS_THREE
FILENAME ORDERS3
CREATE STORAGE AREA ARCHIVE_AREA
PAGE FORMAT IS MIXED
CREATE STORAGE AREA MANUFACTURING
FILENAME MANUFACTURE_AREA
PAGE FORMAT IS MIXED
******************************************************************
Three more storage areas are defined for ORDERS table.
One storage area is defined for Archive and another
storage area is defined for Manufacturing.
You may note, that Archive storage area has page format
of type MIXED. This format is required if you want Record
clustering or Hashed indexes to be stored on this area.
Our Manufacture area also has a mixed format.
******************************************************************
RESTRUCTURE SCHEMA FOR PRODUCTION (cont.)
CREATE DOMAIN ORDER_NUMBER
CHARACTER(25)
EDIT STRING IS "XXX-XXX-XXX-XXXXXXX-XXXX-XX-XXX"
QUERY HEADER IS "Order"/"Number"
CREATE DOMAIN STATUS
SMALLINT
EDIT STRING IS "ZZZZZ"
QUERY HEADER IS "Order"/"Status"
CREATE DOMAIN CUSTOMER_ID
CHARACTER(10)
EDIT STRING IS "XX-XXXX-XXX"
QUERY HEADER IS "Customer"/"Number"
******************************************************************
AS illustrated in an earlier example, the new schema definitions
are going to make use of domains.
You may notice that
EDIT strings and Query headers are being used for better
output format of the queries for interactive users.
******************************************************************
RESTRUCTURE SCHEMA FOR PRODUCTION (cont.)
.
.
.
CREATE TABLE PARTS_TABLE (
PART_TYPE SMALLINT,
PART_COLOR COLOR,
PART_ID PART_SERIAL_NUMBER,
PART_DATE DATE
)
CREATE TABLE ORDER (
ORDER_NUM ORDER_NUMBER,
ORDER_CUST CUSTOMER_ID,
ORDER_STATUS STATUS,
P1_NUMBER SMALLINT,
P1_COLOR COLOR,
P2_NUMBER SMALLINT,
P2_COLOR COLOR,
P3_NUMBER SMALLINT,
P3_COLOR COLOR
)
******************************************************************
You can notice the domains ORDER_NUMBER, CUSTOMER_ID etc
are being used in Table definitions.
As a result of adding these domains, interactive users doing
Ad-hoc queries will have more readable results.
One important thing to notice here is that this formatting
will have no effect on our module language application.
******************************************************************
RESTRUCTURE SCHEMA FOR PRODUCTION (cont.)
.
.
.
CREATE INDEX ARCHIVE_INDEX ON ARCHIVE (CUSTOMER)
TYPE IS HASHED
STORE IN ARCHIVE
CREATE INDEX PART_TYPE_INDEX ON PARTS_TABLE (PART_TYPE)
TYPE IS SORTED
NODE SIZE 100
PERCENT FILL 40
USAGE UPDATE
******************************************************************
We have a simple statement to create a HASHED INDEX for our
MANUFACTURE area.
Our Part-type-index uses the B-Tree index method. Here we can
specify the node size, %fill OR its usage.
******************************************************************
RESTRUCTURE SCHEMA FOR PRODUCTION (cont.)
.
.
.
CREATE STORAGE MAP ARCHIVE_MAP FOR ARCHIVE
STORE IN ARCHIVE_AREA
CREATE STORAGE MAP ORDER_MAP FOR ORDER
STORE RANDOMLY ACROSS ( ORDERS_ONE, ORDERS_TWO, ORDERS_THREE )
CREATE STORAGE MAP PARTS_MAP FOR PARTS_TABLE
STORE USING (PART_TYPE)
IN PART_ONE WITH LIMIT OF (1)
IN PART_TWO WITH LIMIT OF (2)
IN PART_THREE WITH LIMIT OF (3)
OTHERWISE IN SPECIAL_PARTS
******************************************************************
Here are a few examples of Storage Maps with three different
types of STORE clauses.
STorage Map called Archive_Map in the first example is a
direct mappinmg of an entire table into one storage area.
In the second example, records of table ORDER are going to
be stored randomly across three different storage areas.
In the third example, Records of table Parts_Table are being
stored in 4 different storage areas based on the value of
part_type.
I've presented quite a few new concepts to you ..
But the next three slides will be pictorial illustrations of
How these storage areas will work... Hopefully, this will make
things clearer for you.
******************************************************************
***************************
Table Archive is stored in one storage area called Archive.
*****************************
***************************
Table Orders is stored randomly across 3 different
storage areas.
*****************************
***************************
Parts table is distributed across 4 storage areas
based on the value of the part_type.
*****************************
CLUSTER RELATED ROWS FROM DIFFERENT TABLES
.
.
.
CREATE INDEX MANU_ORDER_INDEX ON MANUFACTURE_ORDER (O_NUM)
TYPE IS HASHED
STORE IN MANUFACTURING
CREATE STORAGE MAP MANU_ORDER_MAP FOR MANUFACTURE_ORDER
STORE IN MANUFACTURING
PLACEMENT VIA INDEX MANU_ORDER_INDEX
CREATE INDEX MANU_PART_INDEX ON MANUFACTURE_PART (O_NUM)
TYPE IS HASHED
STORE IN MANUFACTURING
CREATE STORAGE MAP MANU_PART_MAP FOR MANUFACTURE_PART
STORE IN MANUFACTURING
PLACEMENT VIA INDEX MANU_PART_INDEX
.
.
******************************************************************
This is an example of how to achieve record clustering.
The first two definitions illustrate how a table and its
index structure could be clustered together in the same
storage area.
The PLACEMENT VIA INDEX clause directs the database system
to store the rows of the table in such a way that access thru
the index is optimized.
Becuase of the HASHED index ,we will get faster exact match
retrievals.
Because of the record clustering, the index records and the data
records could be read in a single I/O operation.
MANUFACTURE_ORDER and MANUFACTURE_PART table are stored in the
same storage area so that join operations between these two
tables have optimal performance.
******************************************************************
STORAGE MAPS
+------------------------+
| |
| |
| MANU_ORDER_ INDEX |
| |
| MANUFACTURE_ORDER |
| |
| MANU_PART_INDEX |
| |
| MANUFACTURE_PART |
| |
| |
+------------------------+
******************************************************************
This is a pictorial representation of the storage area
called manufacturing. It stores all the rows corresponding
to the tables manufacture_part and manufacture_order along
with their hashed index structures.
******************************************************************
TEST
$ RUN DECUS_HOST
Processing order 021axb89500000012321qw678
done
$
******************************************************************
- Finally, run the host program to make sure that
the queries still run as we expected.
- One important point to note is that,
no recompilation is necessary of the program or
SQL module in order to use the new schema structure.
******************************************************************
LOOKS GREAT using VAX SQL!!
SHIP IT !!!!
******************************************************************
In Conclusion,
VAX SQL is digital's implementation of the industry standard
ANSI SQL, layered on digital's relational database architecture.
VAX SQL is highly compliant with the ANSI standard.
VAX SQL supports all the new features that are present in the
latest version of Rdb/VMS 3.0
< Any reminders for other talks>
QUESTIONS
|
| Decus presentation, Fall 1988.
VAX - TO - IBM Database Interoperability
ABSTRACT
--------
This presentation discusses usage, benefits, and limitations of
the production interoperability products, VIDA for IDMS/R and
VAXlink. The VIDA product provides VAX users with read access
and table upload capability to an IDMS/R database on IBM systems.
The VAXlink product allows VAX users to extract selected data from
IMS and VSAM databases through a menu-driven interface.
Slide Ordering Information
------
The order numbers for the 35mm slides are:
For VIDA with IDMS/R: BPM265
For VAXlink: BPM336
Contact the Corporate Photo Library. EXIT26::PHOTO
SCRIPT
------
VAX to IBM Database Interoperability
* VIDA with IDMS/R
* VAXlink
* Accessing DB2
*******************************************************
SCRIPT
Welcome to the talk on VAX to IBM Database Interoperability.
The purpose of this talk is to describe how Digital products
allow a VAX user to access various types of databases that
reside on an IBM mainframe. I'll be discussing Digital's
two current product offerings in this area: VIDA with IDMS/R
and VAXlink. Lastly, I'll briefly mention our intentions in
the area of DB2 access. This summer Digital announced it's
intention to support access to DB2 databases from a VAX.
I will not be discussing this topic in depth except to say
that you can expect an actual product announcement sometime
this winter. If you would like more specific information
regarding DB2 access you can contact me after this session
or contact your Digital sales representative.
*******************************************************
1
Digital's Complementary Computing Strategy
Typical Corporate Computer Installation
Corporate Departmental Personal
*******************************************************
SCRIPT
What sort of an environment could a product like VIDA with
IDMS/R be used in?
The environment that VIDA is suitable for is that of a large
corporate computer installation which contains
* large IBM mainframes holding corporate databases
* departmental computer (VAXes) for departmental computing
*******************************************************
2
Digital's Complementary Computing Strategy
Growing need to share data
* Ad hoc and flexible
* Fast and efficient
* Performed by end users
* Data format and location transparent
*******************************************************
SCRIPT
Within such an environment, there is a growing need to share
data. In particular, there is often a requirement to make
corporate data available to users throughout the installation.
Ideally, access to corporate data should be:
* ad hoc and flexible, and adaptable to the requirements of
different departmental users. In some cases, users may
want to make one-time, ad-hoc queries against the data in
the corporate databases. In others, they may wish to copy
some corporate data onto their departmental systems and
apply their familiar information management tools to that
data. There could also be a requirement to move data back
to the corporate database from the departments. So, the
data access should accomodate different requirements of
different departments.
* Obviously, data access should be fast and efficient, so
that users get their data in a timely manner.
* finally, data access should be data format and location
transparent, so that end users need not be concerned with
the physical characteristics and the location of data.
Ideally, departmental users should be able to apply their
familiar tools to the corporate data coming from an IBM
system in the same way that they work with local data.
*******************************************************
3
Problem: Differences in environments
* Different physical location
* Different database structure
(Data definitions, data formats, data types)
* Different query languages
*******************************************************
SCRIPT
The problem is differences in environments. First of all,
the data is in different physical locations. The database
structures are different: different data types, data
storage formats, and data definitions. Finally, the
database query languages are likely to be different.
*******************************************************
4
What is VIDA WITH IDMS/R ?
VAX software that provides VAX users with
transparent access to Cullinet IDMS/R databases
on an IBM system.
* Relational Retrieval access
* Data Copy facility
*******************************************************
SCRIPT
VIDA with IDMS/R is VAX software which provides VAX users
with transparent access to Cullinet IDMS/R databases on
IBM systems.
VIDA provides RETRIEVAL ACCESS TO IDMS/R DATABASES
THROUGH FAMILIAR VAX RELATIONAL INTERFACES supplied by
products such as VAX SQL, VAX Datatrieve, TEAMDATA,
Rdb/VMS precompiler-supported languages and more.
Users of these interfaces may issue queries, produce reports,
or perform extracts of IBM-resident data to RMS files,
DBMS databases, or Rdb/VMS databases.
VIDA with IDMS/R also features an optionally installable Copy
Facility (VIDA COPY) which allows the user at the DCL level
to move an entire relation or view from an Rdb/VMS database
on a VAX system to an IDMS/R data table on an IBM system.
*******************************************************
5
VIDA with IDMS/R
Provides an easy-access, multi-vender solution
for the information center
* Transparent access to IBM data through
Cullinet's C/ICMS system
* VAX/VMS Layered Software
* Member of VAX Information Architecture
* Allows VMS applications and users to
use familiar (VIA) tools and techniques to
access IBM data
* Data COPY facility for moving data to C/ICMS
*******************************************************
SCRIPT
VIDA with IDMS/R provides an easy-access, multi-vender
solution for the information center.
VIDA resolves incompatibilites that exist in a heterogeneous
database environment. VIDA is VAX/VMS layered software which
provides transparent access to IBM data through Cullinet's
C/ICMS
software.
It is a member of the VAX Information Architecture family of
products. The VAX Information Architecture family is a group
of software products that work together to solve information
management needs. It includes the database products Rdb/VMS
and DBMS, data dictionary CDD, Datatrieve for query and
report writing, ACMS for transaction processing, and others.
VIDA allows VMS applications and users to use familiar tools
and techniques to access IBM data, and contains a data COPY
facility for moving data to C/ICMS.
*******************************************************
6
VIDA with IDMS/R
|
|
V |
I <--|-----> C/ICMS
User <-----> D |
Interface A |
|
|
V A X | I B M
*******************************************************
SCRIPT
The major function of VIDA is to manage, transparently to
the user, the inevitable incompatibilities that exist
in multi-vendor environments.
This slide summarizes what VIDA does.
The user enters a request using a familiar interface. VIDA
converts the request into something C/ICMS can understand,
sends the request to C/ICMS, receives the reply, converts
the reply into VAX terms, and gives it to the user interface.
*******************************************************
7
VIDA with IDMS/R
Resolves incompatibilities in
multi-vender environment
* Software protocol conversion
* Data definition conversion
* Data type conversion
* Character set conversion
* Login procedure
*******************************************************
SCRIPT
The main function of VIDA is to resolve incompatibilities
between between different database management systems.
It makes a C/ICMS database appear to the VAX user as if
it were a Digital relational database.
VIDA performs SOFTWARE PROTOCOL CONVERSION - Since Digital
software and Cullinet software use different query protocols,
VIDA converts VAX user requests into a form understood by
Cullinet software on the IBM system.
DATA DEFINITION CONVERSION - The VAX and IBM systems apply
different rules to data definitions. One such difference
is in field names. Both the VAX and IBM systems allow
characters that are illegal on the other system. VIDA
automatically resolves these differences. VIDA maps C/ICMS
data definitions to Digital relational data definitions.
DATA TYPE CONVERSION - Data types on VAX systems differ from
those on the IBM. VIDA performs all necessary conversions
between differing datatypes on VAX and IBM systems.
CHARACTER SET CONVERSION - VIDA automatically performs
character set translation between the IBM system's EBCDIC
character set and VAX ASCII values.
LOGIN PROCEDURE - VIDA provides a login utility which assists
the user in creating a file which contains the site-specific
information for logging into the IBM system. This file is
then used at run time to automate much of the login procedure.
The net effect is that the database is presented to the user
8
as if it were a database within the Digital environment. No
work is required on the part of the user to perform any
conversions.
*******************************************************
9
VIDA with IDMS/R Features
* Direct Access to Data Managed by Cullinet's
C/ICMS System (IDMS/R and VSAM)
* Self-describing - Utilizes IBM Data Definitions
* Allows Population of CDD with RDO Integrate
Command
* Automatic ASCII/EBCDIC Text Translation
* Customized Translation Tables
*******************************************************
SCRIPT
* VIDA provides direct access to data managed by Cullinet's
C/ICMS system (IDMS/R databases and VSAM files).
* VIDA obtains data definitions by querying C/ICMS. It
automatically converts C/ICMS data definitions to Digital
relational data definitions.
* Data definitions can then be stored in the Common Data
Dictionary using the RDO>INTEGRATE command.
* VIDA performs automatic ASCII/EBCDIC text translation
using a default translation table.
* VIDA allows users to supply their own translation table is
the default is not appropriate.
*******************************************************
10
VIDA with IDMS/R Features
* Digital Standard Relational Interface (DSRI)
Compliant Read-only Database System
* Standard Relational Operators for retrival
(Select, Join, Project)
* COPY utility for upload
*******************************************************
SCRIPT
* VIDA is a read-only implementation of the Digital Standard
Relational Interface or DSRI. DSRI is a common
architecture for Digital's Relational database products.
* Among the provisions of DSRI is a set of operations for
data manipulation which implementations of DSRI must
support. Included among the operations defined by DSRI
are the standard relational operators of select, join,
and project. Through VIDA, users have a powerful set of
operations with which to query IBM data.
* For moving data to the IBM system, VIDA provides a COPY
facility which is invoked at DCL level by the user.
*******************************************************
11
D
Queries Relational Databases
Applications S
4GLs VIDA Databases
3rd Party Products R
Future Products Future Products
I
Digital Standard Relational Interface
* Calling standard to which Digital's
relational database implementations adhere
* Allows interface compatibility between
applications and relational products
* Applications built with one relational
product are compatible with others
*******************************************************
SCRIPT
This slides further explains DSRI.
DSRI defines a calling standard to which Digital's
relational database implementations adhere. It allows
interface compatibility between applications and relational
products.
All relational products (on the right) can process calls
presented according to the DSRI standard. Interfaces (on
the left) issue calls to the database product according to the
DSRI standard.
The net result is
* Users of a database product have a choice of interfaces.
* An interface works with several database products.
Applications built with one relational product are compatible
with others. Current applications will be compatible with
future relational database products.
*******************************************************
12
VIDA with IDMS/R
Datatrieve | V <--> C/ICMS
VAX SQL | I <--> IDMS/R, VSAM
Languages |<--> D
RDO | A via CULPRIT: IMS
DSRI Interface | DL1
TEAMDATA | TOTAL
VAX Data Distributor | others...
Choice of user interface
* Datatrieve for queries and reports
* VAX SQL (embedded and interactive)
* Rdb/VMS precompilers for 3GL programs
* Rdb/VMS RDO utility
* DSRI applications
* VAX TEAMDATA
* VAX Data Distributor
*******************************************************
SCRIPT
So, VIDA users have a choice of user interface. They can
use any interface which issues calls according to the DSRI
standard:
* Datatrieve for queries and reports
* SQL, either embedded SQL in 3GL language programs or
interactive SQL
* Embedded Rdb/VMS DML in 3GL language program
* RDO - the relational database oparator utility of Rdb/VMS
* Application which directly issues DSRI calls
* VAX TEAMDATA
* VAX Data Distributor
VAX Data Distributor is a Digital product, layered on
Rdb/VMS. When used with VIDA, it allows users to
distribute extracts of IBM data on a predefined schedule
to nodes within a DECnet network.
On the IBM side, VIDA can directly access data managed by
C/ICMS, that is, IDMS/R databases and VSAM files. Other
13
data must be imported into C/ICMS. That can be accomplished
using Cullinet's CULPRIT software product.
*******************************************************
14
VIDA with IDMS/R Hardware View
* VAX/VMS system
Converts query to something IBM side
can understand
* SNA Gateway (or VMS/SNA)
Physical link to carry query to IBM
and return data to VAX
* Cullinet Software's C/ICMS on IBM
Extracts data and sends back to VAX
*******************************************************
SCRIPT
The discussion so far has talked about the user interface
at one end, Cullinet's C/ICMS system at the other end,
and VIDA in between. There is a little more to it than
that.
The basic hardware components required for VIDA are: a
VAX/VMS system, an SNA gateway (or VMS/SNA), and an IBM
machine.
On the VAX, VAX-side software converts the user query to
something the IBM side can understand.
The SNA gateway is the physical link to carry the query
to the IBM system and data to the VAX. The gateway is a
node in both Digital's DECnet network and IBM's SNA network.
Cullinet software C/ICMS on the IBM system extracts the data
and sends it to the VAX.
*******************************************************
15
VIDA with IDMS/R Software View
3GL Applications
with Rdb/VMS or Other DSRI
DATATRIEVE VAX SQL DML Interfaces
| | |
| | |
-----------------------------------
|
VIDA with IDMS/R
|
DECnet/SNA Software
|
- - - - - - - - - - - - - - -
|
IBM/SNA Software
|
----------------------------------
| | |
| | |
CICS -------> IDMS/DC/UCF <------ TSO
|
|
C/ICMS: Cullinet Database Software
(IDMS/R network, Relational)
(VSAM)
*******************************************************
SCRIPT
This is a general picture of the software components:
On the VAX - user interface, VIDA, and DECnet/SNA software
On the IBM side -
* IBM/SNA network software,
* TP monitor: CICS, IDMS/DC/UCF, or TSO (the most direct
route is to go through IDMS/DC. Alternatively, users can
go through CICS to IDMS/DC/UCF or TSO to IDMS/DC/UCF).
* Cullinet Database software - C/ICMS
*******************************************************
16
Prerequisite Digital Software
* User Interface Software
(Datatrieve, RDO, SQL, TEAMDATA,
User developed application with Rdb/VMS or SQL DML,
or other DSRI layered products)
* DECnet/SNA 3270 Data Stream PI
* DECnet (unless used with VMS/SNA stand-alone)
* DECnet/SNA Gateway Management
* DECnet/SNA Gateway or VMS/SNA software
*******************************************************
SCRIPT
This is a detailed list of prerequisite Digital software:
* User Interface Software (Datatrieve, RDO, SQL, User
developed application with Rdb/VMS DML, or other DSRI
layered products)
(and DECnet/SNA software)
* DECnet/SNA 3270 Data Stream PI (This is Digital's
LU2 programming interface)
* DECnet (unless used with VMS/SNA stand-alone)
* DECnet/SNA Gateway Management
* DECnet/SNA Gateway or VMS/SNA software
*******************************************************
17
Prerequisite IBM System Software
* Operating System
MVS/SP or MVS/XA (IBM)
* SNA Network Software
VTAM (IBM)
* Teleprocessing Monitor
CICS or TSO (IBM) or IDMS/DC (Cullinet)
* Database Software
C/ICMS (Cullinet)
*******************************************************
SCRIPT
This is a detailed list of prerequisite IBM software:
* Operating System: MVS/SP or MVS/XA (IBM)
* SNA Network Software: VTAM (IBM)
* Teleprocessing Monitor: CICS or TSO (IBM) or
IDMS/DC (Cullinet)
* Database Software: C/ICMS (Cullinet)
*******************************************************
18
Accessing a VIDA Database
Users must supply values for parameters that define:
* access path
- Gateway name
- TP monitor name
* IBM and C/ICMS login and logout information
(site-specific)
* C/ICMS folder name
VIDA - - - gateway ---> Tp monitor ----> C/ICMS ---> folder name
*******************************************************
SCRIPT
To access a database within C/ICMS, VAX users have to go
through several layers of software. At each point, they
must supply certain information to get to the next point.
So, users must supply
* gateway name
* TP monitor name (this gets them to the IBM system)
Now they have to log in to the TP monitor by entering
user id and password. The actual login sequence will
vary from site to site.
* Next, the user has to get into C/ICMS by selecting a task
and specifying whatever login information is required.
* Finally, the user supplies a folder name (the equivalent
of a database name).
*******************************************************
19
Accessing a VIDA database
* Access information is stored in the
VIDA Access File
* Access files are set up using the
VIDA Login Utility
* Access file name is specified as part of
database name at runtime
*******************************************************
SCRIPT
IBM access information is stored in a file called the VIDA
access file, so it does not need to be entered each time
by the user. The access file is like a script for a login
sequence. It contains answers to questions that the user is
asked while logging into C/ICMS.
Access files are set up using a utility called the VIDA login
utility. The Access file name is then specified as part of
the database name at run time.
*******************************************************
20
VIDA Login Utility
Facilitates automatic connection to IBM system
* Specialized 3270 Terminal Emulator
* Teaches VIDA how to login and logout
* Tool for creation of VIDA Access Files
* Infrequent low-volume usage
* IBM passwords need not be stored on
VAX Disks
*******************************************************
SCRIPT
The VIDA login utility is a specialized 3270 terminal
emulator. It is used to record the login and logout sequence
and store it in the VIDA access file.
The utility is used infrequently. Once an access file has
been created, the utility is not needed unless the login
sequence changes or more access files are needed.
Not all login information has to be stored in the access file.
The user can select which information to store. Values not
stored in the access file must be supplied at run time.
In particular, passwords do not have to be stored in the
access file.
*******************************************************
21
VIDA Login Utility
*******************************************************
SCRIPT
This is an example of what you would see when using the
login utility. The IBM screen is on the left. On the
right, the user is prompted for replies and is asked whether
the entered information is to be stored in the access file.
The login utility provides a way of recording an installation
dependent login sequence.
*******************************************************
22
VIDA Database name
/TYPE=VIDA/FILE=file_spec[/optional-qualifier=value]...
* /TYPE
identifies DB name as VIDA
* /FILE
file_spec of VIDA access file created using VLU
* optional-qualifiers
- overrides to values appearing in VIDA access file
(/GATEWAY=SNAGWY)
- values for parameters not stored in access file
(passwords)
*******************************************************
SCRIPT
When referring to a VIDA database, the user specifies a
database name in a particular format:
* The /TYPE identifies the database name as VIDA
* /FILE is the file spec of the VIDA access file created
using the VIDA Login Utility. This file contains the
sequence of commands required to log into C/ICMS
* optional qualifiers can be
- overrides to values appearing in the VIDA access file
(for example, using a different gateway)
- values for parameters not stored in the access file
(for example, passwords)
*******************************************************
23
Using VIDA with IDMS/R
* Easy to use for non-technical users
* Interactive or batch access
* Choice of user interface for retrieval
access
* DCL-level command for write access
*******************************************************
Now, what is it like for the end-user to use VIDA?
VIDA is easy to use, even for non-technical users.
It can be used interactively or in batch.
There is a choice of user interface for retrieval access.
There is a DCL-level command for write access.
*******************************************************
24
Using VIDA with VAX SQL
* Industry-standard interface
* Similar to Rdb/VMS access
* DECLARE DATABASE FILENAME
"/TYPE=VIDA/FILE=CICS.VAF"
* Read only access to VIDA databases
* Interactive SQL or Embedded SQL
*******************************************************
SCRIPT
VAX SQL is Digital's implementation of the Structured Query
Language, an ANSI standard interface to relational database
products.
For those who have used VAX SQL with Rdb/VMS, from the user
point of view, accessing a VIDA database using VAX SQL is
similar to accessing an Rdb/VMS database.
The user first attaches to the database, for example,
DECLARE DATABASE FILENAME "/TYPE=VIDA/FILE=CICS.VAF"
He starts a read_only transaction and goes on to do queries
using interactive SQL or write programs using embedded SQL.
A SQL application can access both a VIDA database and an
Rdb/VMS database. For example, you can write SQL statements
to extract data from a VIDA database and store it in an
Rdb/VMS database.
*******************************************************
25
VIDA and Rdb/VMS
* Rdb/VMS is not a prerequisite
* Different implementation but same architecture
* Rdb/VMS RDO Utility can be used for application
development and prototyping
* Rdb/VMS Precompilers and Rdb/VMS DML can be
used with VIDA
* A single application can access both an Rdb/VMS
and a VIDA database
* Read only access to VIDA databases
*******************************************************
SCRIPT
The utilities and precompilers that come with Rdb/VMS can be
used as the user interface to VIDA.
First note, however, that Rdb/VMS is not a prerequisite to
VIDA. VIDA and Rdb/VMS are different implementations of the
same architecture. That means that user interfaces and
utilities written to the architecture standard (DSRI) work
with both VIDA and Rdb/VMS.
Rdb/VMS comes with a utility called RDO and language
precompilers. The Rdb/VMS RDO utility can be used for
developing and prototyping VIDA applications. The Rdb/VMS
precompilers and Rdb/VMS DML can be used with VIDA.
A single application can access both an Rdb/VMS database and a
VIDA database; for example, you can write a program to
extract data from a VIDA database and store it in an Rdb/VMS
database.
*******************************************************
26
VIDA COPY facility
* Optional, chosen at installation time
* Move an entire Rdb/VMS relation or view
to a C/ICMS data table
- Create a new C/ICMS table
- Replace an existing C/ICMS data table
- Delete a C/ICMS data table
* User requires authorization from C/ICMS
Administrator
*******************************************************
The VIDA COPY facility is an optional VIDA feature, chosen
at installation time, which allows data to be copied from
VAX Rdb/VMS databases to IDMS/R data tables.
COPY allows the user to move an entire Rdb/VMS relation or
view to an IDMS/R data table. The user can create a
new C/ICMS data table, replace all the data in an existing
table, or delete a data table.
Note that you may only move an entire relation at a time;
record level insert or modify operations are not allowed.
Updating data tables requires authorization from the C/ICMS
administrator. The C/ICMS administrator must grant the
proper update privileges to the user.
*******************************************************
27
VIDA COPY facility
* Invoked at DCL level:
$ DEFINE IBMDB "/TYPE=VIDA/FILE=IBM.VAF"
$ VIDA COPY/RELATION=EMPLOYEES
Source database: VAXDB
Destination database: IBMDB
*******************************************************
The VIDA COPY facility is invoked at DCL level. You
specify the relation name and the source and destination
databases.
In the example, VAXDB is an Rdb/VMS database.
EMPLOYEES is a relation in the database. The whole
relation will be moved to a table within the C/ICMS database
defined by IBMDB.
A scenario for using VIDA COPY is as follows: Using VIDA
with SQL, a department transfers a portion of the IDMS/R
corporate database into an Rdb/VMS database. In the course
of a week, say, the department modifies some data. Then
modifications are copied into a data table within C/ICMS
using VIDA COPY. This data table is then used as a
transaction file with which to update the master database.
*******************************************************
28
VIDA with IDMS/R
Performance Considerations
* Performance Factors:
- Complexity of request
- Communication line speed
- IBM system load and tuning
* Some queries may require a considerable
amount of temporary disk cache space
* Moderate sized data extracts
or low-volume interactive requests
*******************************************************
SCRIPT
Performance of queries will vary depending on: the complexity
of the request, communication line speed, and IBM system
load and tuning.
Some queries can require a considerable
amount of cache space. Data is cached when a query has to
be decomposed into subqueries, and VIDA has to store the
results of some subqueries while processing others. This
would happen in the last example involving the two relations.
Taking performance factors into consideration, VIDA is most
suatable for moderate sized data extracts or low-volume
interactive requests.
*******************************************************
29
VIDA with IDMS/R Security
* VIDA Access files protected by VMS security
* Sensitive information need not be stored in
Access files
* Optional installation of VIDA COPY
* VIDA works with existing IBM security mechanisms
* Passkeys control access to data within C/ICMS
*******************************************************
SCRIPT
Access to the IBM system through VIDA may be as restricted
or as open as required. VIDA does not in any way circumvent
or compromise IBM system security. VIDA allows full
utilization of existing security mechanisms on VAX and IBM
systems.
On the VAX, VIDA Access files which contain login information
can be protected in the usual VMS fashion through access
control lists and VMS file protection. Passwords do not have
to be stored in the access file. They can be supplied at run
time by the user.
Without a valid VIDA access file, a user cannot
log into the IBM system. Only those VAX users authorized to
access the IBM system will be able to do so.
The installation of VIDA COPY is optional when installing
VIDA. By omitting the installation of VIDA COPY, a site can
insure that no tables can be copied to or deleted from an
IDMS/R database through VIDA.
On the IBM side, VIDA works with whatever security mechanisms
are in place at a particular site. VIDA use requires an IBM
account and is subject to privileges granted to it. To access
IBM data through VIDA, VIDA must first log in the user. VIDA
users go through all the same security checks as other IBM
users.
There are security mechanisms within C/ICMS. The database
administrator controls access to the IDB task that VIDA
communicates with, and controls which data tables are
available to which users.
To summarize, access to IBM data through VIDA is completely
controlled.
30
*******************************************************
31
VIDA with IDMS/R Diagnostic Tools
* VIDA logs
* VIDA Bugcheck Dumps (Digital Internal)
* Other tools (SNATRACE, NCP, SNAP, etc.)
*******************************************************
SCRIPT
The following disgnostic tools are available for
troubleshooting problems: VIDA logs (I'll say more about
logs in a minute), VIDA bugcheckdumps, intended for digital
internal use to diagnose software problems.
SNATRACE and SNAP are SNA gateway disgnostic tools. NCP
(network control program) can help to disgnose problems
within DECnet.
*******************************************************
32
VIDA with IDMS/R Logging
* VIDA log traces communications and
database activity, logs errors,
and isolates problems
* Logging options
- Session - Database
- Calls - Process
- Timestamp - Blocks
- Requests - Data
- Errors - All
- Counters - None
*******************************************************
SCRIPT
The VIDA log is a diagnostic tool which is part of the VIDA
software. The VIDA log traces communications and database
activity, logs errors, and isolates problems.
There are several logging options. Logging can be switched
off entirely or some or all options can be enabled. For
example:
* Session logs every message sent back and forth between
VIDA and the IBM system
* Calls logs all calls made to VIDA
* Errors logs all error messages
* Blocks lists internal VIDA control blocks
* Counters provide a count of the number of data packets,
records, and bytes transferred for each user request.
etc.
Some logging options, for example, SESSION, which logs
every message, can generate a considerable amount of
information. This is useful for troubleshooting problems,
but it can represent an overhead to the application. So
it's a good idea not to enable options which generate a large
amount of data during normal operation.
*******************************************************
33
VIDA with IDMS/R Limitations
* Direct access only to data managed by
C/ICMS (IDMS/R Network and Relational
or VSAM). IMS or other data must be
unloaded to VSAM or imported to IDMS/R
* No record level update
* Not a high speed file transfer
*******************************************************
SCRIPT
VIDA provides direct access only to data managed by C/ICMS,
that is, IDMS/R databases and VSAM files. IMS or other data
must be unloaded to VSAM or imported to IDMS/R.
The VIDA with IDMS/R COPY facility allows the user to move an
entire Rdb/VMS relation to C/ICMS. Record level update to
C/ICMS tables is not supported.
It is not a high speed file transfer.
VIDA provides a powerful facility for querying IBM data.
However, because of limitations in communications line speeds
and the protocol used by VIDA, it is not intended as a high
speed file transfer.
*******************************************************
34
VIDA with IDMS/R
Summary
* Easy access to IBM data
* Relational Retrieval
* Selective retrieval on records and fields
* Choice of user interface
* DSRI compliant
* Data COPY facility
*******************************************************
SCRIPT
To summarize, VIDA provides a very easy way to access
IBM data.
Once everything is set up, access is transparent to
the end user. The end user does not have to know anything
about communications protocols, SNA, or Cullinet software.
VIDA users have a powerful set of relational
operations available to them with which to query IBM data.
They can select exactly the fields and records they require.
Users can use familiar tools to query IBM data. They have a
choice of user interfaces; they can do either interactive or
batch access.
VIDA with IDMS/R is compatible with current and future
Digital products which comply to the Digital Standard
Relational interface.
The COPY facility provides a means of transferring data back
to the IBM system.
*******************************************************
35
VAXlink
Provides transparent access to:
* IMS
* VSAM
*******************************************************
SCRIPT
VAXlink is a data retrieval tool that extracts subsets
of data from IMS databases and VSAM files on an IBM
mainframe and stores the data into Rdb/VMS databases
on a VAX computer.
*******************************************************
36
VAXLINK
(picture from page 2-2 of user's guide)
*******************************************************
VAXlink refers specifically to the software that runs
on the VAX. It interacts with the Extractor, companion
software that runs on the IBM mainframe. VAXlink and
the Extractor work together to extract data from IBM
resident databases and make if available to VAX users.
Using VAXlink on a VAX computer, the user creates
information requests, called tasks, which are transmitted
over a communication line to the Extractor on the IBM
mainframe. The Extractor retrieves the requested
information from the database and holds onto it until the
user asks for it to be transferred to the VAX computer.
The user then checks to see if the task has completed
and if so, transfers the requested information to the
VAX computer. The information is then stored in an
Rdb/VMS database designated by the user.
*******************************************************
37
VAXlink Features:
* Menu-driven User Interface
* Scripted access to IBM mainframe
* Selection and extraction of IMS and VSAM data
structures
* Storage of retrieved data into an Rdb/VMS database
*******************************************************
SCRIPT
VAXlink has an easy-to-use menu-driven user interface
that is supplied with the product. This is the only
user interface that VAXlink works with. VAXlink is
not intended to "plug-into" DSRI compliant user
interfaces. However, the menu-driven user interface
that is provided with VAXlink is very easy to master
and can be tailored to individual needs.
VAXlink allows the user to set up scripts to connect
to and disconnect from the IBM mainframe. A script
is very similar to the VIDA Access File and the VAXlink
menus take you through the script creation process
step-by-step. Creating a script using VAXlink differs
from creating a VIDA Access File with the VIDA Login
Utility in that VAXlink does not actually do a live
login as the script is created so the user needs to
know in advance the information to be entered into
the script.
VAXlink allows the user to select data from IBM-
resident databases through boolean expressions. And
once the data is stored into an Rdb/VMS database the
metadata can be easily integrated into the CDD.
Lastly, VAXlink stores the retrieved data in an Rdb/VMS
database so that is may be accessed later by any DSRI-
compliant interface including: VAX SQL, RDO, Datatrieve,
Teamdata, 3GL application, etc. So, although VAXlink is
not DSRI-compliant, the retrieved data can be accessed by
any DSRI-compliant user interface.
*******************************************************
38
VAXlink
IBM Environment
* Operating System: MVS, MVS/XA
* TP Monitors: CICS, IMS/DC, TSO
* Data Structures: IMS/DB, VSAM
*******************************************************
39
Using VAXlink
Setting Up Communications with the Mainframe
(see Figure 3-4 on page 3-11)
*******************************************************
Setting up communications with the mainframe is done
by first defining the host environment. The user must
select the TP-monitor under which the Extractor runs.
The choices include: IMS/DC, CICS, and TSO. Also
required are the user id, VTAM application name, and
gateway node name. The VAXlink documentation explains
how to determine what values to enter in each of these
fields.
*******************************************************
40
Using VAXlink
Constructing the Connect and Disconnect Scripts
(see Figure 3-6 on page 3-15)
*******************************************************
Using VAXlink
Defining a Task
(see Figure 4-5 on page 4-9)
*******************************************************
Using VAXlink
Specifying the Data to be Extracted
(see figure 4-9 on page 4-15)
*******************************************************
Using VAXlink
Specifying the Data to be Extracted
(see figure 4-10 on page 4-16)
*******************************************************
Using VAXlink
Specifying the Data to be Extracted
(see figure 4-11 on page 4-17)
*******************************************************
Using VAXlink
Processing a Task
(see figure 4-13 on page 4-22)
41
*******************************************************
Using VAXlink
Downloading Mainframe Data to Your Rdb/VMS Database
(see figure 4-14 on page 4-24)
*******************************************************
VAXlink Security
* User Profiles protect IBM data
* Script files are encripted
* VMS files protected by VMS security
* Login id and password required
*******************************************************
VAXlink Limitations
* VAXlink operates in menu-mode only
* Extractor operates in batch-mode only
* Cannot upload data
* Not DSRI compliant
* Must store retrieved data in an Rdb/VMS database
*******************************************************
Required Software
Same as for VIDA minus a user interface.
*******************************************************
42
|