Introduction to. NonStop SQL/MP D30

(1)

Introduction to

NonStop SQL/MP

Abstract This manual introduces the basic features of NonStop SQL/MP and is intended for nontechnical and technical readers. Readers do not have to be familiar with Tandem systems.

Part Number 113425

Edition Fourth

Published March 1995

Product Version NonStop SQL/MP D30

Release ID D30.01

(2)

Ordering Information For manual ordering information: domestic U.S. customers, call 1-800-243-6886; international customers, contact your local sales representative.

Document Disclaimer Information contained in a manual is subject to change without notice. Please check with your authorized Tandem representative to make sure you have the most recent information.

Export Statement Export of the information contained in this manual may require authorization from the U.S. Department of Commerce. Examples Examples and sample programs are for illustration only and may not be suited for your particular purpose. Tandem does

not warrant, guarantee, or make any representations regarding the use or the results of the use of any examples or sample programs in any documentation. You should verify the applicability of any example or sample program before placing the software into productive use.

U.S. Government Customers

FOR U.S. GOVERNMENT CUSTOMERS REGARDING THIS DOCUMENTATION AND THE ASSOCIATED SOFTWARE: These notices shall be marked on any reproduction of this data, in whole or in part.

NOTICE: Notwithstanding any other lease or license that may pertain to, or accompany the delivery of, this computer software, the rights of the Government regarding its use, reproduction and disclosure are as set forth in Section 52.227-19 of the FARS Computer Software-Restricted Rights clause.

RESTRICTED RIGHTS NOTICE: Use, duplication, or disclosure by the Government is subject to the restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at DFARS 52.227-7013. RESTRICTED RIGHTS LEGEND: Use, duplication or disclosure by the Government is subject to restrictions as set forth in paragraph (b)(3)(B) of the rights in Technical Data and Computer Software clause in DAR 7-104.9(a). This computer software is submitted with “restricted rights.” Use, duplication or disclosure is subject to the restrictions as set forth in NASA FAR SUP 18-52 227-79 (April 1985) “Commercial Computer Software — Restricted Rights (April 1985).” If the contract contains the Clause at 18-52 227-74 “Rights in Data General” then the “Alternate III” clause applies. U.S. Government Users Restricted Rights — Use, duplication or disclosure restricted by GSA ADP Schedule Contract.

Second 053988 NonStop SQL C30 C30.07 December 1991 Third 107933 NonStop SQL C30 C30.07 December 1994 Fourth 113425 NonStop SQL/MP D30 D30.01 April 1995 New editions incorporate any updates issued since the previous edition.

A plus sign (+) after a release ID indicates that this manual describes function added to the base release, either by an interim product modification (IPM) or by a new product version on a .99 site update tape (SUT).

(3)

Section 2

How to Use NonStop SQL/MP

Querying the Database 2-1 Sources of Queries 2-1 Writing Queries 2-1 Selecting Data 2-2 Joining Tables 2-4

Using the UNION Operator 2-5 Using Views 2-5

Shorthand Views 2-6 Protection Views 2-7 Modifying Data 2-8

Concurrent Updates and Locking 2-8 Transaction Management 2-9

Summary 2-9

Section 3

NonStop SQL/MP Architecture

Physical Database Structure 3-1 Logical Schema 3-1 Physical Structure 3-1 Table Organization 3-3 Indexes 3-4

Partitions 3-7

What Happens When a Query Is Submitted? 3-9 The SQL Compiler and SQL Optimizer 3-10 The SQL Executor 3-13

Parallel Execution 3-15

The Master Executor and ESPs 3-16 Parallel Index Maintenance 3-18 Other Architectural Features 3-18

The Data Access Manager (DP2 Disk Process) 3-18 Sequential Block Buffering 3-21

Cache Optimizations for Sequential Access 3-23 Summary 3-23

(5)

Figures

Figure 1-1. A Table in a Relational Database 1-2

Figure 1-2. Comparing Traditional DBMS Architecture With NonStop SQL/MP Architecture 1-6

Figure 1-3. NonStop SQL/MP Processing a Local and a Remote Request in a Scalable Network 1-9

Figure 1-4. Distributed Data Dictionary 1-13 Figure 2-1. Personnel Database Example 2-2 Figure 2-2. A Simple Query 2-3

Figure 2-3. Selecting Data From Two Tables 2-4

Figure 2-4. A Shorthand View Derived From Two Tables 2-6 Figure 2-5. A Protection View 2-7

Figure 3-1. Database Structure 3-2

Figure 3-2. An Index for Faster Sorted Access 3-6 Figure 3-3. A Partitioned Table 3-8

Figure 3-4. Components of NonStop SQL/MP 3-10

Figure 3-5. Selecting an Access Plan With the Lowest Execution Cost 3-12 Figure 3-6. NonStop SQL/MP Components That Execute a Query 3-14 Figure 3-7. Parallel Execution of a Query Using an Aggregate Function 3-17 Figure 3-8. Single-Table Query Evaluation Performed by the Data Access

Manager 3-20

Figure 3-9. Virtual Sequential Block Buffering (VSBB) Access 3-22

(6)

(7)

About This Manual

This manual introduces the NonStop SQL/MP relational database management system, which uses the standard Structured Query Language (SQL) approved by the American National Standards Institute (ANSI) and the International Standards Organization (ISO) to describe and manipulate data. NonStop SQL/MP can be used for online transaction processing (OLTP), decision support systems (DSS), scalable electronic commerce applications, and other business applications.

NonStop SQL/MP provides high performance, high availability, and scalability for production applications, open access to popular personal computer software tools, and the ability to distribute data over geographically distributed Tandem NonStop

systems.

Manual Organization

This manual contains a general overview of NonStop SQL/MP. If you are a

nontechnical reader, you will learn how NonStop SQL/MP can help your business or organization manage its data more effectively. If you are interested in the technical aspects of database management, this manual will help you understand how NonStop SQL/MP works. The manual is organized as follows:

Section 1 summarizes NonStop SQL/MP features and explains how a business or organization can benefit from using NonStop SQL/MP. This section is meant for nontechnical readers.

Section 2 explains how to develop simple queries and briefly describes the relational model and the logical database architecture. This section is meant for nontechnical and technical readers.

Section 3 describes the physical architecture of a NonStop SQL/MP database, the basic internal components of NonStop SQL/MP, and run-time features and benefits such as parallel query execution. This section is intended for technical readers.

Audience

This manual is intended for anyone interested in a broad overview of

NonStop SQL/MP. Only a minimal understanding of computer software technology and database management techniques is required to understand the concepts

presented in this manual. For a general understanding of Tandem software, you should read Introduction to Tandem NonStop Systems. For a list of definitions of the terms used in NonStop SQL/MP, you should read NonStop SQL/MP Glossary.

(8)

Related Manuals

This manual is part of the NonStop SQL/MP library of manuals. The library includes the following manuals:

NonStop SQL/MP Glossary describes the terminology used in NonStop SQL/MP documentation.

NonStop SQL Quick Start describes how to run SQLCI, how to execute simple queries on a database, how to modify data, and how to produce a formatted report.

NonStop SQL/MP Reference Manual describes the SQL language elements, expressions, functions, and statements. (See this manual for the complete

description and syntax of the report writer commands and the SELECT statement.) NonStop SQL/MP Installation and Management Guide explains how to plan, install, create, and manage a NonStop SQL database; describes the syntax of installation and management commands; and describes NonStop SQL catalogs and file structures.

NonStop SQL/MP Query Guide describes how to retrieve and modify data in a NonStop SQL/MP database and how to analyze and improve query performance. NonStop SQL/MP Report Writer Guide describes how to use report writer

commands and SQLCI options to design and produce reports.

NonStop SQL/MP Version Management Guide describes the rules governing version management for the NonStop SQL software, catalogs, objects, messages,

programs, and data structures.

NonStop SQL/MP Programming Manual for C and COBOL85 and the NonStop SQL Programming Manual for TAL and Pascal describes the programmatic interface for the particular language.

NonStop SQL/MP Messages Manual describes NonStop SQL/MP messages for the conversational interface, the application programming interface, and utilities. The following document is also helpful to users of NonStop SQL/MP:

Query Processing Using NonStop SQL/MP describes how queries are executed within the DBMS.

(9)

Related Manuals

Figure 1. NonStop SQL/MP Library

002 Introductory Manuals NonStop SQL Quick Start (C30.07)* NonStop SQL/MP Install and Management Guide Programming Manuals NonStop SQL/MP Query Guide NonStop SQL/MP Report Writer Guide NonStop SQL/MP Programming Manual for COBOL85 NonStop SQL Programming Manual for Pascal (C30.07)* NonStop SQL/MP Programming Manual for C Usage Guides NonStop SQL/MP Reference Manual NonStop SQL/MP Messages Manual Reference Manuals

* C30-level documentation - does not include information about D30 enhancements NonStop SQL Programming Manual for TAL (C30.07)* Introduction to NonStop SQL/MP NonStop SQL/MP Version Management Guide

(10)

Related Manuals

The following manuals provide overviews of other software systems used with NonStop SQL and discussed in this manual:

FastSort Manual describes FastSort, the Tandem sort-merge product for NonStop systems.

Introduction to Data Management introduces the Tandem data management products.

Introduction to Tandem Data Communications presents an overview of the data communications products provided by Tandem.

Introduction to Transaction Manager/MP (TM/MP) introduces the transaction management facility used to monitor transactions and manage backup and recovery.

NonStop ODBC Server Manual describes the interface product that allows a variety of client applications to communicate with a NonStop SQL/MP database.

(11)

1 Introduction to NonStop SQL/MP

The NonStop SQL/MP relational database management system combines the standard features of a relational database with additional features that provide data integrity, high availability, high performance, and excellent scalability. NonStop SQL/MP is specifically designed for large data processing environments that maintain a variety of applications performing critical business tasks.

This section briefly describes a few types of business applications and shows how NonStop SQL/MP satisfies the needs of those applications. It then discusses the features that allow NonStop SQL/MP to provide high performance and availability.as well as parallel processing, distributed processing, and support for open standards.

What Is a Relational

Database?

A relational database, like any database management system, is used to manage the storage and retrieval of data. A relational system differs from other database management systems in the way the end user or programmer accesses the data. In a relational database, all data can be treated as two-dimensional tables, sometimes called relations, consisting of rows and columns, similar in form to a personal computer spreadsheet. Each row contains pieces of related data such as one employee’s name, identification number, and salary. Each column contains data of the same type. For example, one column contains all the employee names; another contains all the salaries.

When you retrieve data from a table, the result of your operation is returned as another two-dimensional table. You can therefore perform sophisticated data analysis by passing the table resulting from one simple operation to another operation,

continuing until the resultant table contains exactly the information you need. In addition, you can access a whole set of data with a single command. A single SQL statement can retrieve or modify a set of records matching selection criteria. Thus, it can often replace several lines of code that read and process one record at a time. In a relational database, data combined from multiple tables is joined by columns of common data rather than by links and pointers to physical locations. A relational database separates the application and the user view of data from the physical

organization and storage of data. Ideally, you can reorganize the physical data storage without changing existing applications, or you can recombine the data for a new application by creating a logical view that joins data from multiple tables.

Figure 1-1 shows a relational table that makes data easy to visualize and easy to access. For example, you can select from the EMPLOYEE table only the rows describing employees in department 1000 or only the columns containing employee names and salaries.

(12)

Why Use NonStop SQL/MP?

Figure 1-1. A Table in a Relational Database

001 Table Name Column Names Rows Columns ROGER JERRY GREEN HOWARD 9000 1000 100 100 175500.00 137000.10 EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY EMPLOYEE Table 1 23 JESSICA CRINER 3500 300 39500.00 568 ••• ••• ••• ••• ••• ••• 29 JANE RAYMOND 3000 100 136000.00

Why Use

NonStop SQL/MP?

Many vendors offer relational databases. All these database systems support basic two-dimensional tables; they differ in the additional features they offer.

NonStop SQL/MP contains features that make it especially appropriate for three general types of applications: high availability online transaction processing (OLTP), decision support systems (DSS), and scalable electronic commerce.

Scenario: High Availability Online Transaction Processing (OLTP)

Suppose your company provides financial services. Customers call your customer service representatives to inquire about account balances, transfer funds from one account to another, and authorize electronic payments. The account balances must be accurate and each transaction against an account must fully complete before the account balance can be updated.

You want to provide services whenever customers want them, not only during traditional banking hours. Many customers call at 9 a.m., but some call at midnight. Your services must be available 24 hours a day, 7 days a week, 365 days a year, and you must be able to handle the peak calling loads that occur every morning.

Because your customers usually call in their requests, they want fast responses. They cannot wait for several minutes while you process their requests. You need very fast response time for both inquiries and updates to the database.

Because of your excellent record in customer service, your business has grown rapidly. Originally, you had only 50 customer service representatives. You now employ over 1,000 customer service representatives who are all entering customer requests at the same time. You need a system that can handle a large volume of simultaneous transactions.

(13)

Solution

Reliability and transaction integrity. NonStop SQL/MP works with the NonStop Kernel operating system and the NonStop Transaction Manager/MP (TM/MP) subsystem to ensure that every piece of data exactly matches what was originally input and that a transaction either completes or the database is returned to its state just before the transaction started. If a disk drive fails, NonStop SQL/MP automatically uses the mirrored copy of the disk drive, thus ensuring that no data is lost or misinterpreted because of the hardware failure.

Availability. NonStop SQL/MP is designed so that it can be in use continuously. You can perform all administration and database management functions while end users are updating the database. (Some operations that change the structure of the database can cause a very brief downtime for OLTP transactions.)

Scalability. You can easily increase the size of a NonStop SQL/MP database to accommodate peak loads and the demands of a growing application. The same data can be accessed by multiple processors, and no extra programming is required to support the additional processors. Therefore, you can add processors and disk drives to a system without having to change the application. Moreover, benchmarks have shown over 99 percent scalability for systems that scale to more than 100 processors. That is, when you add a processor, over 99 percent of that processor’s power is available to your application.

Fast response. NonStop SQL/MP is tuned for OLTP performance. NonStop SQL Release 1.0 was the first relational database to provide acceptable OLTP performance. Since then, enhancements in parallel processing techniques and operations such as joining tables and sorting data continue to improve response time.

Concurrency. NonStop SQL/MP can lock data at the individual row level. A lock ensures that while one user updates data, no other users can access or update it. Thus, many users can access the same table with minimal contention, because each user is likely to access a different record. In addition, NonStop SQL/MP offers special locks that allow users to read the data without affecting concurrent writes or updates. Most OLTP applications also involve batch updates and reports. Through the mixed workload feature of NonStop SQL/MP, batch jobs or long queries can be run at low priority while the OLTP tasks are executing, without adversely affecting the response time of the OLTP tasks.

Scenario: Decision Support Systems (DSS)

Suppose your company operates a chain of retail stores. You are responsible for ordering products to be sold in these stores. You must offer the products customers want to buy and have the right amount of product on your shelves--enough to satisfy demand, but not so much that you will have to sell outdated stock below your cost. You have a separate system that manages your point-of-sale (POS) devices and tracks each item sold in each store. Your stores sell over a million items a day.

Some items such as toothpaste turn over regularly. However, other items such as Valentine’s Day gifts have dramatically different sales volumes in different stores and different weeks leading up to the holiday. You monitor each day’s sales and place

(14)

orders for additional product to be delivered from the manufacturer directly to your stores.

You work best with visual images, using a mouse to point and click on icons and menu items. You want to access data from your PC and do not want to have to issue SQL commands.

Solution

Technology for large tables. NonStop SQL/MP is especially appropriate for the large tables used by decision support applications. For example, NonStop SQL/MP includes features that eliminate the need to sort large tables in order to join them to other tables or to compute aggregate values such as SUM or MAXIMUM.

Fast loading. By using parallel processing and buffers, NonStop SQL/MP can load large amounts of data into a table at high rates. If the data is coming from

environments outside of Tandem, Tandem and third-party vendors can provide tools to convert and edit the data before it is loaded into the database; these tools can also facilitate parallel loading operations. In addition, NonStop SQL/MP allows you to create indexes in parallel, which contributes to the fast loading of DSS data.

Availability. Although availability requirements are not as high for decision support as for OLTP, many businesses now rely on their data warehouses (decision support databases) for quick decisions. These businesses therefore require their data warehouses to be available at all times. In addition, some queries can need several hours or even days to complete. NonStop SQL/MP is designed to remain available, even during long-running tasks, so it does not need to be restarted if a hardware failure occurs.

Client tools. Query tools available on the PC and the Macintosh computer allow users to select data using point-and-click technology (such as a mouse) and display data in graphical format. NonStop SQL/MP supports the most popular methods for

accessing data from PC client programs. (These data access methods include ODBC, DAL, EDA/SQL, SQL Server, and SQL*Connect.) In addition, Tandem has a

Verification Center to ensure that PC applications work as they are supposed to with NonStop SQL/MP.

Scenario: Scalable Electronic Commerce

Your company provides a service for manufacturers by accepting electronic orders and routing them to the appropriate supplier. Billing and shipment acknowledgements are also handled electronically.

Basically, your company receives an Electronic Data Interchange (EDI) message, examines the message to determine its destination, and ensures that the message gets delivered to its destination. Messages can come in bursts, and you need to make sure you do not lose any messages.

Your service has been so popular that business has doubled every six months. You need to handle this increased volume with the same speed and reliability that got you the business in the first place.

You have expanded your operations and maintain databases at six locations around the world. Some messages include data in European languages, some in Asian

(15)

languages. You need to store these messages in their original languages and forward them to the appropriate geographical location. Because your business is growing rapidly, you do not know how many geographical locations you will have in the future.

Solution

Reliability and transaction integrity. As in an OLTP environment, NonStop SQL/MP uses architectural features such as mirrored disks as well as extensive data checking to ensure that data is stored and retrieved accurately and that transactions are not lost even if they are executed on remote systems.

Availability. NonStop SQL/MP is designed for continuous availability. Through the use of Tandem technology, application requests are automatically resubmitted if a processor failure occurs. In addition, SQL statements are automatically recompiled if a change in the environment renders the execution plans invalid. Also, you can increase the size of the database and build new indexes while the database is in full use. Scalability. By adding processor boards and disks, you enable the Tandem system to keep pace with growing demand and maintain a response time equivalent to the one you had originally.

Distributed database. You can partition NonStop SQL/MP tables so that some

partitions reside on a local system and some on remote systems. Programs operate as if all the data were local and, therefore, do not need to specify the location of the data. Thus, you can add new partitions at remote locations without changing any

applications. In addition, if a query requires only local data, you do not need to access remote systems. NonStop SQL/MP ensures transaction integrity across system boundaries by using a two-phase commit protocol, which commits changes to the database only when all systems are able to complete their portions of the transaction. Multiple character set support (MCSS). NonStop SQL/MP supports the use of a variety of character sets, including the ISO 8859 family of character sets as well as double-byte character sets. You can save and retrieve data in its original character set, allowing you to maintain different portions of the data in many different languages.

Online database configuration. NonStop SQL/MP allows the database administrator (DBA) to redistribute data while the database is in use. For example, the Move

Partition operation moves a portion of the database from one location to another. This feature allows you to open a new data center for a particular part of the world; you can move some data from a central location to the new data center.

(16)

A High Performance DBMS

A High Performance

DBMS

The NonStop SQL/MP database management system (DBMS) achieves high performance because it operates at the lowest levels of the NonStop server architecture. Instead of operating as a software layer above the operating system, NonStop SQL/MP is integrated with NonStop Kernel system processes such as the file system, message system, and data access manager (DP2 disk process).

Figure 1-2 compares the architecture of a traditional DBMS with that of

NonStop SQL/MP. In a traditional DBMS, the DBMS must perform most of the query processing, communicating with low-level system processes only to retrieve the data. With NonStop SQL/MP, the low-level system processes themselves perform most of the query processing. The NonStop SQL/MP architecture considerably shortens the path length to the data residing on disk, significantly enhancing performance.

Figure 1-2. Comparing Traditional DBMS Architecture With NonStop SQL/MP Architecture

002 NonStopSQL/MP Data Traditional DBMS Data I/O Drivers Disk Access File System DBMS Application SQL Data Access Manager I/O Drivers Message System Application SQL File System SQL Data Access Manager DBMS

The data access manager (also called the DP2 disk process) evaluates SQL requests made on a single table or table partition. Because the data access manager is fully responsible for data access to a single disk volume, it can be said that the access is encapsulated—that is, no other process can access the data and thus subvert the DBMS.

(17)

A High Performance DBMS

A data access manager can retrieve data from the table based on the selection criteria in the query; it can also delete and update data. The data access manager executes SQL requests as close as possible to the data, eliminating message traffic to and from a higher-level DBMS layer, as happens in most DBMS systems. (See Figure 1-2.)

To optimize performance, NonStop SQL/MP is distributed within the NonStop server so multiple processes can execute separate SQL requests simultaneously or divide a large request into separate tasks and process the tasks in parallel. NonStop SQL/MP uses the message system to pass requests to the data access managers and return data to the processors in which the requests originated.

When the desired data resides on a remote system in a distributed network, the data access managers on the remote system filter the data, returning only what you requested. This design is especially beneficial for network performance because it reduces the size and number of messages sent across the communication lines. NonStop SQL/MP organizes special memory pages, called cache, where data can be stored temporarily in memory. Data buffers retrieved by the data access manager are read into cache so that the most frequently used data is in memory. This strategy reduces the frequency with which data must be retrieved from disk, thereby

improving performance for both transactional (random) and sequential access to data. When database records need to be read sequentially to satisfy an SQL request, the data access manager can buffer the records, sending blocks of data rather than a single record at a time. A valuable function of the data access manager is that it can filter the data as soon as it is retrieved from disk and then send buffered blocks of data. Both types of buffering reduce the messages and data sent back to the SQL file system, improving performance for sequential access.

In addition, the data access manager can perform bulk (56-kilobyte) I/O transfers during sequential retrievals of data. It can also prefetch data into cache while the application is processing the previous block of data so that the new block is available in memory as soon as the application requires it.

(18)

Scalability

A NonStop SQL/MP database is scalable—you can expand the size of the database simply by adding processors and disk volumes to the system. You do not have to change application code when you scale up the database. As the database and application grow, you can maintain the high performance of the original, smaller database. Moreover, you can speed up the performance of a database that is not growing—again, simply by adding processors and disk volumes.

Tandem’s Parallel Hardware Architecture

The scalability of a NonStop SQL/MP database is founded on the parallel hardware architecture of Tandem systems. A Tandem system contains from 2 to 16 processors, each with its own memory and disk storage, linked by a pair of high-speed

interprocessor buses (IPBs). Because the processors do not share memory or disks, this is sometimes called a shared-nothing architecture.

The processors communicate by sending messages over the IPB, following a model similar to the client/server model. A client process executing in any processor submits a request for a service. A server process executing in the same processor or another processor responds to the request.

A typical transaction or request for database services comprises several client/server interactions. Suppose an end user running a program on a PC requests data. An application process on the Tandem host serves the request by calling

NonStop SQL/MP, which passes the request to the data access manager (DP2), which retrieves the data from disk and passes it back. These processes can be distributed across the system (or across a network) so that no single processor is excessively burdened by a request.

When you add a processor to the system, the expansion in performance is linear—that is, the performance you get from the added processor is exactly the same as that of the first processor in the system. Linear expandability is possible because the processors do not share resources (memory and disks). A shared resource can cause a

performance bottleneck when you expand the system, as the additional components all contend for use of the shared resource.

The Tandem TorusNet networking technology allows you to expand beyond a single system and connect up to 4,080 processors in a single network. A process executing anywhere in the network has easy access to any other process or resource in the network. Because of the message-based operating system, it makes no difference whether the process serving a request is located in the local system or in a system elsewhere on the TorusNet network.

Figure 1-3 shows how an application process in one system requests data from NonStop SQL/MP, which uses the message system to process the request, retrieving data from both a local and a remote system.

(19)

Scalability

Figure 1-3. NonStop SQL/MP Processing a Local and a Remote Request in a Scalable Network

SQL Data Access Manager I/O Drivers Application Message System SQL File System 003 NonStop SQL/MP Table 1 Partition 1 NonStop SQL/MP Table 1 Partition 2 NonStop SQL/MP Table 1 Partition 3 Message System SQL File System Message System SQL File System SQL Data Access Manager

I/O Drivers I/O Drivers SQL Data Access

Manager

Full Integration With System Software

NonStop SQL/MP is fully integrated with the NonStop Kernel operating system so that it can take advantage of the distributed processing made possible by the Tandem hardware architecture. Through the NonStop Kernel message system, a

NonStop SQL/MP process accesses a remote resource simply by qualifying the

resource with a node name. The NonStop Kernel, together with the TorusNet network software, present a single system image to every process in the system. (A separate copy of the NonStop Kernel runs in each processor.)

NonStop SQL/MP is also fully integrated with other Tandem software to ensure that high performance as well as the integrity and availability of the data is maintained. Integration with other parallel products is especially important in a large, distributed system in which many NonStop SQL/MP processes execute in parallel.

For example, the NonStop Transaction Services/MP (TS/MP) application

management system ensures that application server processes are evenly distributed among processors in a system. NonStop SQL/MP processes, which serve requests sent by the application servers, are also distributed among processors. As system activity changes, these products dynamically create (or delete) copies of the required processes.

(20)

Scalability

NonStop SQL/MP uses the the NonStop Transaction Manager/MP (TM/MP) subsystem to maintain data consistency amid concurrent transactions. The

NonStop TM/MP subsystem ensures that data modifications are either completely applied to the database or not performed at all. NonStop TM/MP automatically backs out any incomplete transactions and manages recovery in case of system or media failures. A NonStop SQL/MP database thus remains in a consistent state even if, for example, a communication line failure interrupts a transaction.

NonStop SQL/MP is also integrated with system utilities that help configure and manage software in a Tandem system, with the Measure performance measurement tool, which collects performance statistics on processes and tables, and with system monitoring and event management tools.

Table Partitioning and Parallel Execution

NonStop SQL/MP allows you to partition the data in a table across many disk volumes. You can configure your hardware system so that the disk volumes (and, thus, the table partitions) are managed by different processors within a system or different processors across a distributed network. Partitioning the database allows NonStop SQL/MP to take full advantage of the parallel processing provided by the Tandem multiprocessor architecture.

Multiple NonStop SQL/MP executor processes can execute in parallel in separate processors. The SQL executors can process multiple small queries in parallel. For a large query, an SQL master executor process can create executor server processes that divide up the query into smaller tasks and process the tasks in parallel.

The SQL executors can use separate data access managers to retrieve data from the partitions associated with their processor. This strategy balances the I/O workload among the multiple processors, greatly improving both throughput (the total data the system can process at one time) and response time (the elapsed time it takes to complete one user transaction). In addition, you can partition indexes across disk volumes to provide faster access to the data.

NonStop SQL/MP also performs the following tasks in parallel:

Parallel join operations by the SQL executor during query processing Parallel index maintenance, which reduces the effect of multiple indexes on performance

Parallel sorting of large amounts of data by the FastSort utility

These features make it possible for a Tandem system to perform massively parallel processing against a very large database. Moreover, these features allow you to scale a database to a larger and larger size as your application requirements grow, without affecting performance.

(21)

A Highly Available Database

A Highly Available

Database

A NonStop SQL/MP database is highly available, remaining online for current users even when, for example, you scale up the database to accommodate growing business. NonStop SQL/MP provides availability, just as it provides high performance and scalability, by combining features inherent in the Tandem system architecture and NonStop Kernel with features specific to NonStop SQL/MP.

Availability Features of the NonStop Kernel

The NonStop Kernel operating system uses process pairs to provide fault tolerance. A system process, or an application process that requests database services from an application server process, can have a backup process executing in another CPU. If a fault occurs in the primary CPU, the backup process can take over without

interrupting the application. Moreover, process pairs do not require special

programming. For transactions that access the database, NonStop TM/MP helps to ensure availability by recovering the transactions in case of a system failure. You can enhance the availability of the database by storing the data for each disk volume on two identical disks. This feature, called disk mirroring, protects data from hardware faults that could affect any individual disk. If one disk becomes

inaccessible, the mirrored disk continues to be available, and there is no impact on the application.

Tandem also provides software to protect against a disaster affecting an entire site. NonStop SQL/MP works with the Remote Duplicate Database Facility (RDF) product, which lets you maintain a duplicate database on a separate system at a remote

location. As users modify the primary database, RDF replicates the changes on the duplicate (backup) database. You can configure RDF so that multiple remote systems maintain backup databases for one another, providing data replication. Each system can then support primary application activity (such as OLTP or query processing) as well as providing for disaster recovery for another system.

Availability Features of NonStop SQL/MP

NonStop SQL/MP provides several features that make it possible to keep the database continuously available to your application. Process pairs (mentioned earlier) are especially valuable when applied to the data access manager. You can configure the data access manager to have a backup process in another processor. If a failure should occur in the CPU in which the primary data access manager resides, the alternate data access manager can immediately take over the job of retrieving data so that the in-process query can complete successfully.

In addition, NonStop SQL/MP ensures that application programs never have to fail when they access database objects that have been changed by the DBA. The active data dictionary, which records all changes made to database objects and reflects the current status of the database, flags each application program that accesses a modified object. (Database objects include tables, indexes, views, programs, and collations.) NonStop SQL/MP automatically recompiles an SQL query if the existing execution plan (the compiled, executable code) has been invalidated because of a modified database object.

If a modification does not significantly alter a database object, the existing execution plan may execute acceptably, if perhaps inefficiently. NonStop SQL/MP provides an option to allow the existing plan to execute even after a table or other object the plan

(22)

Distributed Database Architecture

accesses is changed. For example, if a table is moved from one disk volume to another or if a new index is created, application programs will still run. However, some SQL queries might benefit by using the new index. You can choose when to schedule a recompilation of the SQL queries to take advantage of the new database environment. In a distributed database, NonStop SQL/MP can continue to process a query that accesses several table partitions even if a partition is unavailable. That is, the database remains available to the user even when a portion of the database is unavailable. The user can optionally skip unavailable data. If some of the requested data resides on the unavailable partition, the user still gets partial results.

NonStop SQL/MP also allows you to manage and reconfigure physical components of the database online; that is, users can continue full read and write access to the

database while these management operations take place. For example, users can continue to access the database while the DBA performs backup and restore

operations, loads data, and reorganizes files to maintain efficient access. Also, these operations can be executed at a lower priority than user queries so that they have a minimal impact on the performance of user applications.

You can perform the following tasks while the database is available for read and write access:

Backing up the database

Restoring a part of the database from backup Loading a portion of the database

Adding a partition to increase database size

The database is also available to user applications while you perform the following tasks:

Moving a portion of a table’s data to another disk (possibly a larger or faster disk) to balance I/O performance. This task is accomplished by splitting partitions or moving partition boundaries.

Creating an index on a table or reorganizing an existing table or index for optimal performance

At the end of these operations, however, the database is unavailable very briefly while database file labels are updated to reflect the changed structure. You can schedule the downtime so that it does not affect the execution of user queries.

Distributed Database

Architecture

A NonStop SQL/MP database can be fully distributed. You can partition tables and indexes across systems in a network. (A system is called a node when it is part of a network.) Moreover, NonStop SQL/MP provides location independence. You can store data where it is used most frequently, and your applications can retrieve and change the data regardless of where it is located. Accessing distributed data is as simple as accessing local data: the applications provide the names of the tables that contain the data. NonStop SQL/MP determines the location of the data as well as the best way to retrieve it.

(23)

Distributed Database Architecture

Applications can also be distributed. A client (or requester) process can run on one system, while the server processes run on other systems and perform I/O operations on the distributed database.

When a transaction updates distributed data, you want to be sure that an interruption at a remote node does not leave the data in an inconsistent state. The

NonStop TM/MP subsystem protects all transactions, local and remote, for consistency and automatic recovery. NonStop TM/MP uses a two-phase commit protocol to ensure that changes are committed to the database only when all systems are able to complete their portions of the transaction.

NonStop SQL/MP maintains a distributed data dictionary that describes the objects in a distributed database. If a table or index is partitioned among several nodes,

NonStop SQL/MP duplicates the data descriptions in catalogs residing on each node, so that each partition is described on its local system. Each catalog also identifies the location of all other partitions. The data dictionary thus provides a unified logical database schema that gives applications complete access to the distributed data. Figure 1-4 shows an example of a distributed data dictionary. At each location, the dictionary maintains information about all the nodes in the distributed database.

Figure 1-4. Distributed Data Dictionary

Database Tables Catalog Tables TABLE 1 (PARTITION 1) — NEW YORK TABLE 1 (PARTITION 2) — LOS ANGELES TABLE 1 (PARTITION 3) — MONTREAL TABLE 1 (PARTITION 1) TABLE 1 (PARTITION 1) — NEW YORK TABLE 1 (PARTITION 2) — LOS ANGELES TABLE 1 (PARTITION 3) — MONTREAL TABLE 1 (PARTITION 2) TABLE 1 (PARTITION 1) — NEW YORK TABLE 1 (PARTITION 2) — LOS ANGELES TABLE 1 (PARTITION 3) — MONTREAL TABLE 1 (PARTITION 3)

New York Los Angeles Montreal

In addition, NonStop SQL/MP provides local autonomy. In a distributed database, all NonStop SQL/MP systems in the network cooperate with one another, yet each system can manage and access its own local data independently of the other systems.

(24)

Support for Open Standards

Support for Open

Standards

By using standard programming tools and network protocols, you can minimize training for multiplatform application development, and you can take advantage of a wide variety of tools that adhere to industry standards.

Easy-to-Use ANSI SQL Access to NonStop SQL/MP is through ANSI-standard and ISO-standard SQL, a language specifically designed for easy access to relational databases.

SQL is easy to learn. It is a concise language comprising a limited number of Data Definition Language (DDL) and Data Manipulation Language (DML) statements. You use only four DML statements to manipulate data—SELECT, INSERT, DELETE, and UPDATE. Yet you can use these statements to write complex queries to satisfy a variety of application needs.

Moreover, you can write DML statements that focus on the logical organization of the data without having to worry about the specifics of the physical database. For example, you do not need to be overly concerned about the order of WHERE clauses in a query, nor do you need to specify that an index should be used.

NonStop SQL/MP automatically optimizes your queries to execute efficiently against the physical NonStop SQL/MP database.

Open System Services (OSS)

You can develop applications using either the traditional Tandem Guardian services or using industry-standard services as provided by Tandem’s Open System Services (OSS). Programs in COBOL using traditional Guardian calls and programs in C using POSIX calls (in the OSS environment) can concurrently access the same data and be governed by the same concurrency constraints and transaction controls. The active data dictionary dynamically manages both Guardian-based programs and OSS-based programs. Programs from either environment have equal access to NonStop SQL/MP data.

Accessing Data With Desktop Software

You can also develop applications using the latest in PC and workstation client tools. NonStop SQL/MP supports a variety of industry-standard client/server application program interfaces (APIs) such as ODBC, EDA/SQL, DAL, SQL Server, and

SQL*Connect.

Because many popular client applications are compatible with these APIs, you can, in many cases, use software tools you are already familiar with to access

NonStop SQL/MP data. Further, business-oriented end users do not have to know anything about NonStop SQL/MP, because Tandem API products such as the NonStop ODBC Server automatically translate their requests into NonStop SQL/MP queries.

With these packaged software tools, end users can easily create queries by pointing and clicking on icons and menu items. In addition, a number of application

development tools provide a standardized environment in which client programmers can develop more specialized queries to satisfy their company’s requirements. Both types of tools have open access to NonStop SQL/MP.

(25)

Additional Features

The following additional features help make NonStop SQL/MP a powerful tool for large applications requiring high performance, high availability, scalability, and data integrity:

Cost-based query optimization Locking

Mixed workload environment Support for national languages Active data dictionary

Constraints

Performance features for DSS Tools for database administration FastSort sorting utility

Cost-Based Query Optimization

The SQL optimizer, a component of the SQL compiler, determines the best (and quickest) way to retrieve the data you want. The optimizer examines the SQL query you have given it and combines this information with information about the physical database configuration. For example, the optimizer evaluates the size of tables, whether the tables are local or remote, and which indexes on the tables are available. Based on this information, it evaluates several alternatives and chooses the execution plan that will use the fewest system resources.

As an end user, you do not need to worry about the order in which you refer to tables and conditions, or whether an index exists that might improve the performance of the query. You simply issue the query, and the optimizer develops the plan that will execute your request most efficiently.

Locking If an application has hundreds or thousands of users, it is likely that several users will want to access or modify the same data at the same time. In such cases it is important to protect the integrity of the data so that unexpected changes do not occur.

Suppose you send your bank a change of address form and one clerk updates your address while another clerk is updating your account balance. Without some controls, the clerk who is updating your account balance could accidently overwrite your new address with the old one. Now suppose a bank manager simultaneously queries the customer account database to compare levels of business activity at various branches. If possible, the database should remain available to this read-only query even while update controls are in effect.

NonStop SQL/MP provides several mechanisms to ensure that multiple users do not create surprises for one another. Moreover, these locking mechanisms allow

NonStop SQL/MP to sustain both OLTP and DSS workloads—that is, both updates and read-only access.

(26)

Additional Features

First, NonStop SQL automatically issues a locking statement whenever you update, insert, or delete data. The lock ensures that while you are updating the data, no one else can access it or update it.

Second, NonStop SQL/MP locks individual records rather than a page of records, allowing more users to access the database concurrently. Users only wait on a lock if they want the particular record being locked. They do not have to wait if the record they want is adjacent to a record that is locked.

Third, you can control the characteristics of locks. For example, you can specify exclusive or shared access to data. You can also specify whether a lock will be released after you finish accessing an individual record or held until you are finished with all the records you are accessing.

Mixed Workload Environment

Most applications perform a variety of tasks concurrently. Some tasks can require immediate responses and others can be less critical. For example, the database activity performed by an order entry clerk needs a high priority, whereas the database

retrievals needed to produce the nightly status report can execute at a lower priority. Or, possibly, the CEO needs a report for a board meeting in 15 minutes, whereas an end user might need a report by the next business day.

The data access manager (DP2), the system process that accesses the data on each disk volume, contains a scheduling algorithm through which it can temporarily suspend work on low-priority tasks in order to satisfy high-priority tasks.

Support for National Languages

Increasingly, companies operate around the globe, and end users want to access data in their own languages, whether it is English, French, German, or Japanese.

NonStop SQL/MP supports a variety of character sets so that data entered in the major European and Asian languages can be represented in the database.

In addition, you can define your own collation sequence to determine the order in which characters appear when you sort data. (The collation sequence differs in different languages.) Moreover, different users can define and employ their own collation sequences on the same system.

Active Data Dictionary The NonStop SQL/MP data dictionary contains descriptions of all the tables, views, indexes, collations, and SQL object programs that make up the database. Whenever you change any of these objects, NonStop SQL/MP immediately updates the data dictionary so that all database operations use consistent definitions.

Also, if your tables are distributed on remote systems, NonStop SQL/MP ensures that the data dictionaries on all systems have consistent definitions of the tables.

Even currently running programs can be flagged when you change any database objects. NonStop SQL/MP can then recompile the invalid (flagged) programs and produce new SQL execution plans if the current plans are inconsistent with the altered environment.

(27)

Additional Features

Constraints Constraints are conditions stored in the data dictionary to control the data values added to a database. You specify constraints to keep invalid data out of the database. For example, you can ensure that a particular code in the database is limited to values 1 or 2. By defining constraints in the data dictionary, you can avoid duplicating program code to check for invalid data, because the data is checked by

NonStop SQL/MP rather than by the program.

Performance Features for DSS

A database used for decision support needs special features not required for online transaction processing. Typically, users examine large amounts of data rather than accessing a single record at a time, as in OLTP.

NonStop SQL/MP provides several types of query execution plans that speed up scanning of large tables, take full advantage of the Tandem parallel architecture, and efficiently calculate aggregate values such as SUM, MINIMUM, MAXIMUM, or AVERAGE.

Often, DSS queries combine data from a very large table with one or more small tables. For example, you may join data from a sales history table with a table containing store names and addresses. NonStop SQL/MP provides three methods for joining tables: nested-loop, sort-merge, and hash join. Hash joins are particularly efficient for large-scale DSS queries for which sorting is inefficient. A hash join works by building a hash table in memory to perform the join. Another feature, cross product join, prejoins several small tables so you only need a single join operation with the big table.

In addition, DSS queries frequently request summarized data derived by grouping and aggregating the data. In NonStop SQL/MP, aggregate values can be calculated directly as data is being read from the disk or by using special hash algorithms to eliminate the need to sort data.

In most decision support systems, you load data from another operational database into a separate DSS database. NonStop SQL/MP provides a utility for loading data. This utility uses special techniques to process large blocks of data rather than single rows at a time. You can use the utility to load data into multiple partitions in parallel to further improve the performance of the load operation.

Tools for Database Administration

The database administrator (DBA) can use SQL Data Definition Language (DDL) statements to create, alter, rename, and delete SQL objects. These statements can create new indexes, add columns to tables, and change security.

In addition, NonStop SQL/MP provides database operations that can move data from one disk volume to another while users are updating the database. These partition-configuration operations help to optimize system performance and can be executed online; they do not affect application availability.

NonStop SQL/MP and third-party software products also provide utilities that help to convert data, manage a database in a distributed environment, reorganize a table or database, and examine which programs use which tables in the database.

NonStop SQL/MP is integrated with system administration tools for the NonStop Kernel. These tools measure performance and manage backup and recovery of database files.

(28)

Summary

Parallel Sorting Utility If a query requires that the data be sorted, NonStop SQL/MP determines whether to sort the data in the virtual memory allocated to the user or to call an external sort routine. If you need to sort more than 32,767 rows, NonStop SQL/MP sends your data to the FastSort utility. FastSort can perform the sort in parallel so that multiple

processors can work on the sort concurrently. Also, if a sort that is started in the user process space runs out of memory, the partial results are gracefully migrated to the FastSort utility, which completes the task.

Summary

NonStop SQL/MP is ideally suited for database applications requiring easy access to the data, high performance, high availability, and the ability to scale up the database as the business grows. Conforming to industry-standard programming tools and protocols and accessible from popular PC software tools, NonStop SQL/MP combines open access with the parallel, distributed processing environment made possible by Tandem’s multiprocessor architecture.

(29)

2 How to Use NonStop SQL/MP

This section describes the basic tools for querying a NonStop SQL/MP database. It shows how the relational model makes it easy to retrieve data by letting you use joins and views. Finally, it discusses how to modify data and maintain data integrity and consistency by using NonStop SQL/MP locking mechanisms and transaction management statements.

Querying the Database

The NonStop SQL/MP relational structure is simple and flexible. Data appears in the familiar form of tables with rows and columns. To retrieve data, you can select columns and rows from one or more tables.

The SQL language includes simple statements, called Data Manipulation Language (DML) statements, that you use for querying the database. In NonStop SQL/MP, a query can modify data as well as retrieve it.

Sources of Queries There are several ways to send a query to the NonStop SQL/MP subsystem. Many PC-based or workstation-based query tools can easily build and export SQL queries to a back-end server using popular interfaces such as ODBC or SQL Server. A business professional can build a query simply by clicking icons and menu items in this type of packaged client application. The application will automatically translate the request into an SQL query and send it to the necessary interface such as the NonStop ODBC Server, which delivers the query to NonStop SQL/MP. Thus, this user can access the database without knowing anything about the SQL language.

A database administrator (DBA) or programmer can submit a query directly to NonStop SQL/MP by using the SQL Conversational Interface (SQLCI).

These two types of queries are likely to use dynamic SQL. NonStop SQL/MP compiles and executes a dynamic SQL statement as soon as it is submitted.

A programmer can also embed SQL statements in an application program written in a high-level language such as C or COBOL. This method, called static SQL, allows NonStop SQL/MP to store the compiled query with the compiled program object file. The compiled query can be executed quickly, multiple times, with no overhead for compilation. Static SQL is ideal for large business applications such as high-volume OLTP.

The rest of this section describes how to write queries to retrieve and modify data. It focuses on the logical functions of SQL statements, without regard to the method you use to access NonStop SQL/MP or the format of the returned data. (Where and how the returned data is displayed, printed, or manipulated depends on the application.)

Writing Queries You write a query by using a SELECT statement in which you specify columns and rows in a particular table or tables. To do this, you need to know the logical structure of the database—the way information is distributed in tables and the relationships among the tables. Generally, a database administrator (DBA) creates and manages the tables and other SQL objects, which are cataloged in the data dictionary. The DBA furnishes the logical picture of the database.

(30)

The DBA also manages the physical configuration of database files on the Tandem system. Because NonStop SQL/MP keeps the logical database structure independent of the underlying physical database structure, the query writer does not need to know anything about the physical structure.

Selecting Data The SELECT statement enables you to write queries that select particular rows and columns, join two or more tables into a single result, and even make further selections from the result. In a SELECT statement, you can specify the result you want in the following ways:

Select columns by naming the columns (this is called projection)

Select rows by specifying conditions for selection (this is called restriction) Join tables by naming the tables, specifying the columns in the result, specifying the join conditions (the columns on which the join is performed), and specifying conditions for selection

Specify a union operation between the results of two SELECT statements Figure 2-1 shows a sample personnel database. The examples in this manual use the personnel database.

Figure 2-1. Personnel Database Example

004

DEPT Table

LOCATION DEPTNUM DEPTNAME MANAGER RPTDEPT

1000 FINANCE 23 1500 PERSONNEL 213 1 9000 CORPORATE CHICAGO CHICAGO CHICAGO ••• ••• ••• 9000 1000 9000 ••• ••• JOB Table 100 MANAGER 250 ASSEMBLER 900 SECRETARY JOBCODE JOBDESC JOB Table ••• ••• ROGER JERRY JESSICA GREEN HOWARD CRINER 9000 1000 3500 100 100 300 175500.00 137000.10 39500.00 EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY EMPLOYEE Table

1 23 568

(31)

Suppose you want to know the names and employee numbers of all employees who work in department 4000. To answer this request, you can enter the following statement:

>> SELECT FIRST_NAME, LAST_NAME, EMPNUM +> FROM EMPLOYEE

+> WHERE DEPTNUM = 4000 ;

The SELECT statement specifies the columns, the FROM clause identifies the table, and the WHERE clause specifies the desired rows.

Figure 2-2 shows the sample query and the list of employees returned by NonStop SQL/MP.

Figure 2-2. A Simple Query

005

Simple Query

SELECT FNAME, LNAME, EMPNUM FROM EMPLOYEE

WHERE DEPTNUM = 4000 ;

SELECT FIRST_NAME, LAST_NAME, EMPNUM FROM EMPLOYEE WHERE DEPTNUM = 4000 ; LNAME --- ---HOWARD EMPNUM RACHEL MCKAY 65 ERIC BROWN 87 ...

FIRST_NAME LAST_NAME EMPNUM ---- ---

---Data

In a large table, it is important to use the WHERE clause to limit the amount of returned data (called the result). If the sample query did not have a WHERE clause, the result would include every employee name and number in the organization. The application would then have to manipulate the result to give you only the employees in department 4000. By providing the means to write highly specific queries,

NonStop SQL/MP relieves the application of extra work and improves application performance.

(32)

Joining Tables

When a query selects data from two or more tables, the SELECT statement effectively joins the tables to form a single, combined result table. For example, to list the managers of each department and the department name, you need information from the EMPLOYEE and DEPT tables.

Figure 2-3 illustrates a SELECT statement that joins these two tables using the employee number, a value common to both tables. (The MANAGER column in the DEPT table lists the employee number of each department manager.)

Figure 2-3. Selecting Data From Two Tables

SELECT FIRST_NAME, LAST_NAME, DEPTNAME FROM EMPLOYEE, DEPT

WHERE EMPLOYEE.EMPNUM = DEPT.MANAGER ORDER BY DEPT.DEPTNUM ;

006

EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE ... EMPLOYEE Table

FIRST_NAME LAST_NAME DEPTNAME JERRY ••• ROGER HOWARD ••• GREEN FINANCE •••

JANE RAYMOND MARKETING CORPORATE MARY MILLER SHIPPING 1 23 29 ••• 234 ••• 111 ROGER JERRY JANE ••• MARY ••• SHERRIE GREEN HOWARD RAYMOND ••• MILLER ••• 9000 1000 3000 ••• 2500 ••• 3500 100 100 100 ••• 100 ••• 100 MANAGER ... DEPTNUM DEPTNAME PERSONNEL SHIPPING CORPORATE MARKETING DEPT Table FINANCE 3000 1000 1500 2500 9000 23 213 234 29 ASIA SALES 3500 111 1 ••• ••• ••• WONG

SHERRIE WONG ASIA SALES ••• ••• ••• ••• ••• ••• ••• ••• Select Columns Tables to Be Joined Select Rows in Which the Employee Is a Manager

(33)

Using Views

Although the join shown in Figure 2-3 occurs on the employee number column, that column does not have to be part of the result. The sample statement selects from the joined tables only those columns that contain employee names and department names and only those rows that contain manager names. The result is sorted by department number.

Using the UNION

Operator

The UNION operator combines the end results of two SELECT statements. You can

use union operations to combine data from logically similar tables.

Suppose, for example, that a table called EMPLOYE1 contains information about corporate employees in North America. A separate table, EMPLOYE2, contains information about employees in Asia. To retrieve the names and employee numbers of both groups of employees who work in department 4000, you can specify the following query:

>> SELECT FIRST_NAME, LAST_NAME, EMPNUM +> FROM \NY.$VOL1.PERSNL.EMPLOYE1 +> WHERE DEPTNUM = 4000

>> UNION

>> SELECT FIRST_NAME, LAST_NAME, EMPNUM +> FROM \TOKYO.$VOL2.PERSNL.EMPLOYE2 +> WHERE DEPTNUM = 4000 ;

To specify a union operation, you must specify the same number of columns in each select list. Also, columns in corresponding positions must have compatible data types.

Using Views

A view is a specification of columns and rows from one or more base tables.

NonStop SQL/MP does not store the data in a view separately but retrieves the data from the underlying base tables. Thus, a view is a virtual table. The database administrator (DBA) stores a view definition in the data dictionary with the CREATE VIEW statement, which assigns a view name to a SELECT statement.

You can select columns and rows from a view to retrieve part of the data represented by that view. Selecting from a predefined view is simpler and less error prone than writing a new SELECT statement each time you want to see a particular view of the data.

Views let you customize the database to suit your business needs. When you use one database for different applications, each application can access the database through views that make the database seem designed for that application.

In addition, views require no replication of data. The DBA does not have to keep many copies of the same data for different users.

If you frequently query the same table or related tables, creating a view saves time and effort. Instead of repeatedly rewriting the query, you can refer to the named view. In addition to enhancing productivity, views can improve performance because the DBA can save the most efficient form of the query as a view.

(34)

Using Views

Shorthand Views A shorthand view (the type of view discussed so far) provides convenient data

selection from any number of tables and other views. (You cannot update data using a shorthand view.)

Figure 2-4 shows a shorthand view derived from the EMPLOYEE and DEPT tables.

Figure 2-4. A Shorthand View Derived From Two Tables

007

View contains only manager rows.

Asterisk selects all columns and rows in the view.

ERIC ROGER JERRY RACHEL MARY JANE THOMAS •••

SELECT * FROM MGRLIST ;

View defines new column name. •••

FIRST_NAME LAST_NAME DEPTNAME MGRLIST View BROWN GREEN HOWARD MCKAY MILLER RAYMOND RUDLOFF ••• PLANNING CORPORATE FINANCE PERSONNEL SHIPPING MARKETING INVENTORY •••

CREATE VIEW MGRLIST (FIRST_NAME, LAST_NAME, DEPTNAME) AS SELECT FIRST_NAME, LAST_NAME, DEPTNAME

FROM EMPLOYEE, DEPT

WHERE EMPLOYEE.EMPNUM = DEPT.MANAGER CATALOG PERSNL ;

The CREATE VIEW statement in Figure 2-4 stores a join as a view named MGRLIST. (The SELECT statement in Figure 2-4, while similar to the one shown in Figure 2-3, does not order the result by department number.)

After you create a view, you can refer to it as if it were a table. For example, you can request the name of the personnel manager by entering the following SELECT statement:

SELECT FIRST_NAME, LAST_NAME FROM MGRLIST

(35)

Using Views

Protection Views NonStop SQL/MP provides another type of view, the protection view, for security and privacy. A protection view provides column-level security by restricting access to individual columns.

Consider, for example, the salary information in the EMPLOYEE table. You can prevent unauthorized users from seeing employee salaries by securing the table to prevent access to the SALARY column. For general users, you can create a protection view of the EMPLOYEE table without salary information. You can secure the view so that general users can retrieve and update data in the view columns. Only users with access to the underlying table can view or update the SALARY column.

Figure 2-5 illustrates a protection view.

Figure 2-5. A Protection View

008 EMPPUB View ROGER JERRY JANE ••• ••• BEN JESSICA GREEN HOWARD RAYMOND ••• ••• HENDERSON CRINER 9000 1000 3000 ••• ••• 5000 3500 FIRST LAST DEPTNUM

Retrieves data from the table through the view. Only one table allowed. Specifies protection view.

CREATE VIEW EMPPUB (EMPNUM, FIRST, LAST, DEPTNUM, JOBCODE)

AS SELECT EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, JOBCODE

FROM EMPLOYEE FOR PROTECTION ;

SELECT FIRST, LAST, DEPTNUM FROM EMPPUB ;

Suppose a user who has access to the EMPPUB protection view, but not to the underlying table, issues the following query:

SELECT FIRST, LAST, SALARY FROM EMPPUB ;

NonStop SQL/MP returns an error message explaining that the SALARY column does not exist.

(36)

Modifying Data

A shorthand view can refer to a protection view just as it refers to a table. That means you can simplify data access at the same time as you restrict data access.

Table 2-1 compares protection views and shorthand views.

Table 2-1. Comparison of Protection Views and Shorthand Views

Protection View Shorthand View

Derived from only one table Derived from any number of tables and other views

Can be secured separately from the table on which it is based

Derives its security from tables or views on which it is based

Allows modification of data Allows reading but not modification of data

Modifying Data

NonStop SQL/MP allows you to insert, delete, and update data as well as retrieve it. For example, to add information about a new employee, you can use the INSERT statement to add a row to the EMPLOYEE table. To give certain employees a five-percent raise, you can issue the following UPDATE statement:

UPDATE EMPLOYEE

SET SALARY = SALARY * 1.05

WHERE SALARY BETWEEN 20000.00 AND 45000.00 ; Concurrent Updates and

Locking

In large business applications, particularly OLTP applications, hundreds or thousands of users can modify the database at any time. Suppose, for example, that many customers simultaneously withdraw cash from the hundreds of ATMs belonging to a large bank. NonStop SQL/MP has the power to retrieve and modify the data for all these transactions and quickly respond to each customer.

This flexibility, however, raises the issue of data integrity and consistency. Each user must be able to finish modifying a particular value (for example, debiting a checking account by a certain amount) without interference from other concurrent

modifications.

NonStop SQL/MP provides efficient locking mechanisms, including row-level and range locking, to ensure that only one user at a time can update a particular row (or rows). When a row is updated, NonStop SQL/MP automatically chooses an exclusive lock, preventing any other users from having acce

Introduction to. NonStop SQL/MP D30

Introduction to

U.S. Government Customers

Contents

Section 2

Section 3

Figures

About This Manual

Audience

Figure 1. NonStop SQL/MP Library

1 Introduction to NonStop SQL/MP

Figure 1-1. A Table in a Relational Database

Why Use

Scenario: High Availability Online Transaction Processing (OLTP)

Solution

Scenario: Decision Support Systems (DSS)

Solution

Scenario: Scalable Electronic Commerce

Solution

A High Performance

Figure 1-2. Comparing Traditional DBMS Architecture With NonStop SQL/MP Architecture

Scalability

Tandem’s Parallel Hardware Architecture

Figure 1-3. NonStop SQL/MP Processing a Local and a Remote Request in a Scalable Network

Table Partitioning and Parallel Execution

A Highly Available

Availability Features of the NonStop Kernel

Availability Features of NonStop SQL/MP

Distributed Database

Figure 1-4. Distributed Data Dictionary

Support for Open

Open System Services (OSS)

Additional Features

Cost-Based Query Optimization

Mixed Workload Environment

Performance Features for DSS

Tools for Database Administration

Summary

Figure 2-1. Personnel Database Example

Figure 2-2. A Simple Query

Joining Tables

Figure 2-3. Selecting Data From Two Tables

Using the UNION

Using Views

Figure 2-4. A Shorthand View Derived From Two Tables

CREATE VIEW MGRLIST (FIRST_NAME, LAST_NAME, DEPTNAME) AS SELECT FIRST_NAME, LAST_NAME, DEPTNAME

SELECT FIRST_NAME, LAST_NAME FROM MGRLIST

Figure 2-5. A Protection View

FROM EMPLOYEE FOR PROTECTION ;

Table 2-1. Comparison of Protection Views and Shorthand Views

Modifying Data

UPDATE EMPLOYEE