Up and Running with ALLBASE/SQL

ManualsBrandsHP ManualsSoftwareMPE/iX 6.5 Operating System

Summary of content (106 pages)

PAGE 1
HP 3000 and HP 9000 Computer Systems Up and Running with ALLBASE/SQL ABCDE Printed in U.S.A.
PAGE 2
The information contained in this document is subject to change without notice. HEWLETT-PACKARD MAKES NO WARRANTY OF ANY KIND WITH REGARD TO THIS MATERIAL, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Hewlett-Packard shall not be liable for errors contained herein or for incidental or consequential damages in connection with the furnishing, performance, or use of this material.
PAGE 3
Print History Note DRAFT 9/12/97 The following table lists the printings of this document, together with the respective release dates for each edition. The software version indicates the version of the software product at the time this document was issued. Many product releases do not require changes to the document. Therefore, do not expect a one-to-one correspondence between product releases and document editions. Edition Date Software Version First December 1990 36217-02A.E0.
PAGE 4
iv DRAFT 9/12/97 20:40
PAGE 5
DRAFT 9/12/97 v 20:40
PAGE 6
Preface This book describes how to get an ALLBASE/SQL DBEnvironment up and running in the shortest possible time. ALLBASE/SQL is Hewlett-Packard's relational database management system, which is o ered on HP 3000 computers using the MPE XL operating system and on HP 9000 computers using the HP-UX operating system. This manual contains basic information about ALLBASE/SQL database design, creation, and administration. It is intended for new users of ALLBASE/SQL.
PAGE 7
Contents 1. Very Basic . . . What Is a Database? . . . . . . . . . . . What Is a Relational Database? . . . . . . Rows and Columns . . . . . . . . . . . Sample Database Table . . . . . . . . . Data Types and Sizes . . . . . . . . . . Using Several Tables . . . . . . . . . . What Is SQL? . . . . . . . . . . . . . . What Is ALLBASE/SQL? . . . . . . . . . SQLCore and DBCore . . . . . . . . . ISQL . . . . . . . . . . . . . . . . . ALLBASE/Query . . . . . . . . . . . SQLUtil . . . . . . . . . . . . . . . .
PAGE 8
2. Looking at Data Understanding the Process . . . . . . A Small Sample Database . . . . . . How Will the Data Be Used? . . . . Distinguishing Entities and Attributes . Listing Entities . . . . . . . . . . . Listing Attributes . . . . . . . . . Identifying Relationships between Entities Locating Distinguishing Key Items . . . From Entities to Tables . . . . . . . . Creating the Table Design . . . . . . . Data Type and Size . . . . . . . . . Character Data . . . . . . . . . . NULL Values . . . . . . . . . .
PAGE 9
4. Practice with ALLBASE/SQL Using PartsDBE Setting up PartsDBE . . . . . . . . . . . . Using SQLSetup . . . . . . . . . . . . . Creating PartsDBE . . . . . . . . . . . Using Setup Scripts . . . . . . . . . . . . HP-UX Systems . . . . . . . . . . . . MPE XL Systems . . . . . . . . . . . Looking at the Files Created for PartsDBE . HP-UX Systems . . . . . . . . . . . . MPE XL Systems . . . . . . . . . . . Examining PartsDBE . . . . . . . . . . . . Examining the Tables and Views . . . . . . View De nitions . .
PAGE 10
4GL . . . . . . . . . . . . . . . . . . . . Di erences in Concurrency Control . . . . . . . Locking Mechanisms . . . . . . . . . . . . . Sample Mapping of a TurboIMAGE Database to an ALLBASE/SQL DBEnvironment . . . . . . . Using ALLBASE/Turbo CONNECT . . . . . . . 6. Glossary of Terms in ALLBASE/SQL Ad Hoc Query . . . . . . . . . Archive Logging . . . . . . . . Attribute . . . . . . . . . . . Authority . . . . . . . . . . . Authorization Group . . . . . . Base Table . . . . . . . . . . Class . . . . . . . .
PAGE 11
Integrity Constraint . . . ISQL . . . . . . . . . . Isolation Level . . . . . . Join . . . . . . . . . . Key . . . . . . . . . . . Key Column . . . . . . . Key Value . . . . . . . . Locking . . . . . . . . . Logging . . . . . . . . . Message Catalog . . . . . Message File . . . . . . . Modi ed Source File . . . Module . . . . . . . . . Native Language . . . . . Nonarchive Logging . . . . Normalization . . . . . . Object . . . . . . . . . Optimizer . . . . . . . . Owner . . . . . . . . . Predicate . . . . .
PAGE 12
System Table . . System View . . . Table . . . . . . Table Authority . Transaction . . . Unique Constraint Unique Index . . Validation . . . . View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
PAGE 13
Figures 1-1. 1-2. 3-1. 3-2. 4-1. 4-2. 4-3. 4-4. 4-5. 4-6. 4-7. 4-8. 4-9. 4-10. 5-1. 5-2. 5-3. Relational Operations . . . . . . . . . . ALLBASE/SQL DBEnvironment . . . . . ISQL Banner . . . . . . . . . . . . . System.Table Display . . . . . . . . . . SQLSetup Menu . . . . . . . . . . . . Information on Tables and Views . . . . . View De nitions in the System Catalog . . Output of the INFO Command . . . . . System Catalog Information on Indexes . . Groups in the System Catalog . . . . . .
PAGE 14
PAGE 15
1 Very Basic ...
PAGE 16
What Is a Relational Database? A relational database is a collection of data arranged in tables, also known as relations. Tables are subject to the following relational operations, each of which lets you retrieve data in a speci c way: Selection, which lets you extract a subset of rows. Projection, which lets you extract a subset of columns. Joining, which lets you extract from more than one table at a time. In practice, these operations frequently appear together.
PAGE 17
Figure 1-1. Relational Operations Rows and Columns Sample Database Table When you look at data in relational terms, you can assume several things: Tables are arranged in rows and columns, which are like records and elds in an ordinary le. Each column has a speci c data type and size. Each row contains one element for every column. A column can contain NULL values if you allow it to.
PAGE 18
Data Types and Sizes Each column can accept data of a speci c type and size. Here is the breakdown for the sample table above: Column Name Data Type Last Name VARCHAR(15) First Name VARCHAR(15) Telephone SMALLINT Employee Number INTEGER Balance DECIMAL(10,2) Data types are described further in chapter 2. Using Several Tables You can put the same data into several di erent tables such as the following: Table 1.
PAGE 19
In data analysis, you investigate the various ways your data can be used. In database design, you create speci c table structures based on your analysis. The design phase results in a set of table descriptions, sometimes known as a schema, for your database. Chapter 2 presents an introduction to data analysis and database design.
PAGE 20
ISQL An interactive command processor which lets you enter SQL commands at the keyboard and observe query results, messages, and other information on a video display. Remember that SQL is a language|not a software system. So you need an interactive way to submit SQL commands to SQLCore and DBCore for processing. ISQL is the main tool used by ALLBASE/SQL programmers and database administrators to create and modify the schema of an ALLBASE/SQL DBEnvironment.
PAGE 21
ALLBASE/Net ALLBASE/Turbo CONNECT Other Products DRAFT 9/12/97 Software that permits you to set up and maintain DBEnvironments in networks. Through the NETUtil program, you make DBEnvironments on host systems available to local users. Software on MPE XL systems that lets you attach a TurboIMAGE database to a DBEnvironment and then use SQL to query the TurboIMAGE database as if it were a set of ALLBASE/SQL tables. Software that makes use of ALLBASE/SQL to meet specialized needs.
PAGE 22
What Is a DBEnvironment? In ALLBASE/SQL, you create one or more databases in a structure called a DBEnvironment. The structure of a DBEnvironment is shown in Figure 1-2. Figure 1-2. ALLBASE/SQL DBEnvironment The following objects are the most important parts of the DBEnvironment: The DBECon le. DBEFiles. DBEFileSets. Databases. Tables and indexes. Authorities. System catalog. Log les. Objects are structures created and stored in an ALLBASE/SQL DBEnvironment.
PAGE 23
To better understand the DBEnvironment and its objects, imagine that it is like a library, which is used to store books, periodicals, or other information. Like a library, the DBEnvironment is a physical location for information, so you need to set aside physical space for it. Also like a library, the DBEnvironment uses a logical method for storing and retrieving information.
PAGE 24
DBEFiles DBEFileSets Databases 1-10 Very Basic You must allocate physical storage space by creating DBEFiles| operating system les that hold table data, index data, or both. Like the individual bookcases in the library, they have a speci c capacity. DBEFiles have both physical names|operating system names| and logical names, by which the les are known internally to the ALLBASE/SQL system catalog, to be explained shortly. DBEFiles are grouped together in logical groupings known as DBEFileSets.
PAGE 25
When you create an object in the DBEnvironment, you are its owner by default, and therefore it belongs to your database. Tables and Indexes The most important objects you create are tables. The table is like a periodical stored in the library, and the rows in the table are like individual articles in the periodical. The index on a table is like the index to the content of a group of periodicals in the bookcase.
PAGE 26
much as you would use the card catalog in a library to look up the bookcase and shelf number for a book or periodical. Log Files ALLBASE/SQL provides logging of all transactions that take place in the DBEnvironment. Like the library's record of items checked out and returned, the log le is a record of the rows in database tables that are added, deleted, or changed.
PAGE 27
How Do I Create a Database? To create a database, you need to perform at least some of the following tasks: Create tables. Create views. Create indexes. These tasks are part of the data de nition process, which also includes placing the tables in speci c DBEFilesets, and using the SQL DROP command to remove tables or views that are no longer needed. Most data de nition tasks are carried out when you are setting up the database for the rst time.
PAGE 28
Queries and Other Data Manipulation Data manipulation commands access the data in databases. An example is a query using the SQL SELECT command, which displays a selection of data from database tables. Here is a simple query for information from the Employees table.
PAGE 29
Where Can I Get Help with ALLBASE/SQL? Because ALLBASE/SQL has so many components and tasks, it is helpful to know where to nd information about each one.
PAGE 30
Information about ALLBASE/SQL Tasks Task Title Creating a DBEnvironment Creating, Dropping Tables Accessing Databases Granting Authorities Loading, Unloading Data Maintaining DBEnvironments Migrating DBEnvironments Creating ALLBASE applications Setting up and Using ALLBASE/SQL Networks Setting up and Using TurboIMAGE Access Checking Syntax Understanding Warnings and Errors 1-16 Very Basic Section Reference DBA DBA SQL ISQL SQL ABQ APG SQL ISQL DBA DBA DBA DBA DBA DBA APG, 4GL NET \DBEnvironment Co
PAGE 31
2 Looking at Data This chapter presents the basic steps in data analysis and database design: Understanding the process. Distinguishing entities and attributes. Identifying relationships between entities. Locating distinguishing key items. Creating the table design. De ning indexes. De ning views. Estimating table and index size. Designing applications. These terms are explained as each step is discussed in the following sections.
PAGE 32
Understanding the Process Designing a database to be built with ALLBASE/SQL means examining the data you wish to store and then putting it into a form that ALLBASE/SQL can understand. In other words, you look at the logical relationships that exist within the data and then create a relational design (tables, views, indexes, etc.). The process can be complex, and you could use a number of formal design methodologies.
PAGE 33
How Will the Data Be Used? Distinguishing Entities and Attributes Listing Entities A typical user of the database would be the program director, who might ask questions like the following: What selections do I have by Beethoven that are less than 20 minutes long? What did we broadcast last year on Beethoven's birthday? How many di erent versions of Beethoven's Fifth Symphony do we have, who are the conductors, and what are the timings? What composers represented in our library were born in March? What se
PAGE 34
Listing Attributes Next, you need to de ne the attributes of each entity, which are the useful pieces of information to be stored in tables. In addition to supplying informational detail, some attributes are used to distinguish one entity from another. As you subdivide your data, make sure that for each entity you de ne, at least one attribute can uniquely identify an instance of the entity. This attribute or group of attributes is known as a key.
PAGE 35
Locating Distinguishing Key Items From Entities to Tables Each entity should have one or more attributes which can uniquely distinguish a particular occurrence of that entity. Each distinguishing attribute or group of attributes is known as a key value. Also, for each relationship you have de ned, an attribute or group of attributes should specify the relationship by forming a link between the entities. In the case of Station Log and Selection , the links are \Selection Name" and \Selection Title.
PAGE 36
Creating the Table Design Data Type and Size The next step is to de ne the characteristics of each column you have de ned. For each potential column value, you need to answer the following questions: What is the data type and size? For character data, should values be xed or variable length? Are null values allowed? Some possible data types in ALLBASE/SQL are: CHAR Fixed length character string. VARCHAR Variable length character string. INTEGER Four-byte integer values. SMALLINT Two-byte integer values.
PAGE 37
Modifying the Table Design One further consideration: Is all the data within a particular entity accessed at the same time? If not, it may be wise to consider subdividing as you convert the entity into a table description. You might move some of the information in the Albums table to a di erent table if it is not used very often. A related consideration: Is some of the data from two or more tables always accessed together? In this case, consider combining two tables into one.
PAGE 38
Composers Table Table Name Column Name Composers ComposerName Birth Death Birthplace Comment Data Type NOT NULL VARCHAR(16) NOT NULL DATE DATE VARCHAR(40) VARCHAR(80) Size 16 bytes 16 bytes 16 bytes 40 bytes 80 bytes Log Table Table Name Column Name Log AlbumCode SelectionName StartTime EndTime Announcer Data Type NOT NULL Size INTEGER VARCHAR(40) DATETIME DATETIME VARCHAR(40) NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL 4 bytes 40 bytes 16 bytes 16 bytes 40 bytes The sizes shown here are
PAGE 39
rows with the same album code. That is, an album can contain many selections. Some tables may also be good candidates for creation as hash structures, which are essentially self-indexed. For more information on this topic, refer to the chapter \Using SQL" in the ALLBASE/SQL Reference Manual . Designing Database Security Schemes You can provide security for data at the level of the DBEnvironment itself, or at the level of individual tables.
PAGE 40
Estimating Table and Index Size In order to implement your table design, you need to estimate the amount of disk space required for your tables and any indexes you plan to create. Then, when you create DBEFiles to contain the data, specify a size that is big enough to hold the rows you want to store plus some room for growth. The tutorial in chapter 3 uses le sizes that are more than adequate for the sample database you are creating.
PAGE 41
3 Setting Up a Database with ISQL This chapter is a tutorial on setting up your own database using ISQL, the interactive component of ALLBASE/SQL. The tutorial takes you through the steps you need to follow in creating any ALLBASE/SQL database, large or small. Here are the steps: Running ISQL. Creating a DBEnvironment. Creating DBEFileSets. Creating DBEFiles for table and index data. Adding DBEFiles to DBEFileSets. Creating tables. Entering data into tables. Performing queries. Creating views.
PAGE 42
d a IIIIIIII II II II II II IIIIIIII SSSSSSSS SS SS SSSSSS SS SS SSSSSSS QQQQQQQ QQ QQ QQ QQ QQ QQ QQ Q QQ QQ QQ QQQQQQ QQ LL LL LL LL LL LL LLLLLLLLL WED, AUG 08, 1990, 4:14 PM HP36217-02A.07.00.17 Interactive SQL/9000 HP SQL/HP-UX (C)COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989. ALL RIGHTS RESERVED. isql=> c b Figure 3-1.
PAGE 43
DBEFILE0. DBELOG1. DBECon File DBEFile0 Log File The rst of these is the DBECon le or DBEnvironment con guration le. This has the same name you assigned to the DBEnvironment in the START DBE command. The DBECon le contains startup parameters for the DBEnvironment. For complete information about startup parameters, refer to the \DBEnvironment Con guration and Security" chapter of the ALLBASE/SQL Database Administration Guide.
PAGE 44
previous example), all appear as uppercase in directory displays in MPE XL, regardless of whether you entered these names in upper, lower, or mixed case. HP-UX does not upshift le names, so they appear in directory displays exactly as you enter them. Rembember that in the CONNECT statement, the name of the DBEnvironment is case-sensitive in HP-UX, but it is not case-sensitive in MPE XL.
PAGE 45
Now issue the next command: isql=> CREATE DBEFILE AlbumIndex 4Return5 > WITH PAGES=30, 4Return5 > NAME='ALBUMI1', TYPE=INDEX; 4Return5 This command creates a new DBEFile known internally to ALLBASE/SQL as AlbumIndex and to the operating system as ALBUMI1. This le contains thirty 4096-byte pages, and is available for storage of indexes only. Notes The third DBEFile type|MIXED|can store both tables and indexes. DBEFILE0 is an example of a MIXED DBEFile.
PAGE 46
Adding DBEFiles to DBEFileSets After creating DBEFiles, you must add them to a DBEFileSet before they can be used. In MUSICDBE, two choices are available: SYSTEM, already created when you issued the START DBE command. ALBUMFS, which you created in an earlier step.
PAGE 47
Creating the Albums Table Use the following command to create the Albums table: isql=> CREATE PUBLIC TABLE Albums 4Return5 > (AlbumCode INTEGER NOT NULL, 4Return5 > AlbumTitle VARCHAR(40) NOT NULL, 4Return5 > Medium CHAR(2), 4Return5 > AlbumCost DECIMAL(6,2), 4Return5 > RecordingCo CHAR(10) NOT NULL, 4Return5 > DateRecorded DATE, 4Return5 > MfgCode VARCHAR(40), 4Return5 > Comment VARCHAR(80)) 4Return5 > IN ALBUMFS; 4Return5 Did the command complete without errors? If not, did you do the following: Use a s
PAGE 48
Entering Data into Tables Entering Data with the SQL INSERT Command Once you have created tables, you can get data into them in several ways. Try the following two methods: The SQL INSERT command. The ISQL LOAD command. Use the SQL INSERT command to add rows to the tables you have created. Try the following entry for the Albums table: isql=> INSERT INTO Albums 4Return5 > VALUES (2001, 4Return5 > 'Serenades from the 17th Century', 4Return5 > 'ca', 30.
PAGE 49
Comment: 'Lute improvisations' Note that INSERT is an SQL command that processes a single row of data at a time. If you want to insert many rows at a time, use an application program of your own design, or else try the ISQL LOAD command, explained below. Entering Data with the ISQL LOAD Command Use the ISQL LOAD command to insert data from an ordinary le into your tables. Two kinds of LOAD operation are possible: LOAD INTERNAL. LOAD EXTERNAL. The next sections show an example of each.
PAGE 50
LOADing from an EXTERNAL File Use the LOAD command with the EXTERNAL option to load data from plain ASCII les into a table. You must enter the names of the columns in the table you are loading into and the starting location in the le where each data item starts, together with the data item's length. If the column permits null values, the data le must contain null indicator characters for any entry that is null. In the following example, the question mark (?) is used as a null indicator.
PAGE 51
For complete information about loading tables from INTERNAL and EXTERNAL les, refer to the ALLBASE/ISQL Reference Manual. Performing Queries After loading your tables, you are ready to perform some queries to see the result of your e orts at data de nition and data entry. Use the SELECT command to display the information you need.
PAGE 52
Creating Views When you need to perform a query frequently, you can de ne a view that incorporates the column list and the predicate; then you can select from the view with a simpler command. As an example, create a view of the Albums and Titles tables that includes all selections: isql=> CREATE VIEW Selections AS 4Return5 > SELECT AlbumTitle, Selection, 4Return5 > Composer FROM Albums, Titles 4Return5 > WHERE Albums.AlbumCode = 4Return5 > Titles.
PAGE 53
isql=> CREATE GROUP Librarians; 4Return5 isql=> ADD Ann, Peter TO 4Return5 > GROUP Librarians; 4Return5 isql=> CREATE GROUP Announcers; 4Return5 isql=> ADD Fred, Julia TO 4Return5 > GROUP Announcers; 4Return5 isql=> GRANT CONNECT TO 4Return5 > Announcers, Librarians; 4Return5 isql=> GRANT ALL ON Albums 4Return5 > TO Librarians; 4Return5 isql=> GRANT ALL ON Titles 4Return5 > TO Librarians; 4Return5 isql=> GRANT SELECT ON Albums 4Return5 > TO Announcers; 4Return5 isql=> GRANT SELECT ON Titles 4Return5 > TO An
PAGE 54
isql=> CREATE UNIQUE INDEX AlbCodeIndex 4Return5 > ON ALBUMS (AlbumCode); 4Return5 Use the following command to create a non-unique index on the AlbumCode column of the Titles table: isql=> CREATE INDEX TitleCodeIndex 4Return5 > ON TITLES (AlbumCode); 4Return5 Location of Tables and Indexes 3-14 Each index is created in the same DBEFileSet as the table it is indexing.
PAGE 55
As shown in the illustration, indexes and tables always appear in the same DBEFileSet (shelf area). They may be in di erent DBEFiles, however. Note When you issue a query, you do not tell ALLBASE/SQL to use an index. Instead, the SQLCore optimizer decides when the use of an existing index is the best way to access a speci c set of data. For more information about indexes, refer to the discussion of \Providing Data Access Paths" in the chapter \Using ALLBASE/SQL" of the ALLBASE/SQL Reference Manual .
PAGE 56
Examining the System Catalog Whenever you create objects in ALLBASE/SQL, their characteristics are stored in the system catalog , which is a special system-created database. The system catalog is like a listing of the contents of a DBEnvironment. (See the illustration below.) You can look at the system catalog by performing queries on system tables. Issue the following query to see entries for the tables and views you have just created: isql=> SELECT * FROM SYSTEM.
PAGE 57
d a select * from system.
PAGE 58
In Review ... In this tutorial chapter, you created a default DBEnvironment structure consisting of a DBECon le, a log, and a DBEFILE0 associated with DBEFileSet SYSTEM to contain system catalog data. You created the ALBUMFS DBEFileSet, and two DBEFiles associated with it|AlbumData, for the Albums and Titles table data, and AlbumIndex, for the Albums and Titles indexes. You also created and loaded the tables themselves, created groups and granted authorities, and de ned a unique and a non-unique index.
PAGE 59
4 Practice with ALLBASE/SQL Using PartsDBE The ALLBASE/SQL software includes a sample DBEnvironment known as PartsDBE and a set of sample application programs which illustrate much of the functionality of ALLBASE/SQL. Most of the examples in the ALLBASE/SQL documentation use PartsDBE. This chapter shows you how to get practice using the components of ALLBASE/SQL by creating a version of PartsDBE which you can use to try out the examples on your own system.
PAGE 60
Here are the topics covered: Setting up PartsDBE. Examining PartsDBE. Using the preprocessors. Examining startup parameters with SQLUtil. Creating a schema le with SQLGEN. Purging PartsDBE. Setting up PartsDBE Before beginning, change into the group and account or directory where you want to create PartsDBE. Use an empty group or directory if possible. Then choose one of the following two methods for setting up PartsDBE: Using SQLSetup. Using setup scripts.
PAGE 61
d a Options for Setting Up ALLBASE/SQL Sample DBEnvironments =============================================================== Choose one: 1. 2. 3. 4. 5. 6. 7. 0.
PAGE 62
Using Setup Scripts The following paragraphs describe an alternate method for setting up PartsDBE using setup scripts that are available on all systems. HP-UX Systems Use the following command to set up PartsDBE: $ /usr/lib/allbase/hpsql/setup 2 4Return5 You will see a display of messages showing the progress of the setup script. A listing of setup appears in Appendix C of the ALLBASE/SQL Reference Manual . MPE XL Systems Use the command le CREASQL to stream a job that sets up PartsDBE.
PAGE 63
with hpdb as owner.
PAGE 64
MPE XL Systems List the les in the current group and account.
PAGE 65
Examining PartsDBE In this section, you will examine the objects that were created within PartsDBE|tables, views, indexes, and authority structure. Information about all these objects is in the system catalog, which is automatically created by ALLBASE/SQL as the DBEnvironment is con gured. Run ISQL, then CONNECT to PartsDBE. (If you are using HP-UX, rst change back to the directory from which you ran the script to create PartsDBE. You must have write permission in the directory from which you CONNECT.
PAGE 66
Use the following query exactly as shown to look at all the tables and views created by the setup script: Examining the Tables and Views isql=> SELECT NAME, OWNER, 4Return5 > DBEFILESET, TYPE 4Return5 > FROM SYSTEM.TABLE 4Return5 > WHERE OWNER <> 'SYSTEM'; 4Return5 d The result table is shown below. a select name, owner, dbefileset, type from system.
PAGE 67
You can see the view de nitions by issuing the following query exactly as shown: View Definitions isql=> SELECT VIEWNAME, SELECTSTRING 4Return5 > FROM SYSTEM.VIEWDEF WHERE 4Return5 > OWNER = 'PURCHDB'; 4Return5 d The query result is shown in the next gure. a select viewname,selectstring from system.
PAGE 68
You can see individual table descriptions by using the INFO command, which returns the column de nition of a table. Use the following command for the Vendors table: Using the INFO Command isql=> INFO PURCHDB.VENDORS; 4Return5 d The output from this ISQL command is shown below: a isql=> info purchdb.
PAGE 69
The following query shows the indexes on tables in PartsDBE: Examining Indexes isql=> SELECT INDEXNAME, TABLENAME, 4Return5 > UNIQUE, CLUSTER FROM SYSTEM.INDEX; 4Return5 d The query result is shown in the next gure: a select indexname,tablename,unique,cluster from system.
PAGE 70
An authority structure consists of many elements. Some of these elements are shown below: Group de nitions. Table authorizations for select, insert, update, and delete operations on tables. Column authorizations for permission to update speci c columns. Examining the Authority Structure Groups Use the following query to examine the authorization groups in PartsDBE and their members: isql=> SELECT * FROM SYSTEM.GROUP; 4Return5 d The query result is shown below: a select * from system.
PAGE 71
Table Authorities Use the following query exactly as shown to examine the authorizations on the PurchDB.Inventory table: isql=> SELECT USERID, SELECT, INSERT, 4Return5 > UPDATE, DELETE, ALTER, INDEX 4Return5 > FROM SYSTEM.TABAUTH WHERE 4Return5 > NAME = 'INVENTORY'; 4Return5 d The query result is shown below: a select userid, select, insert, update,delete, alter, index from system.
PAGE 72
d a select userid, tablename, owner, colname from system.
PAGE 73
Using the Preprocessors Sample Application Programs For large-scale database access or for batch operation, ALLBASE/SQL includes a set of preprocessors, which let you embed SQL commands into the source code for your own applications. Before compiling your program, you use the preprocessor to prepare the program for runtime database accesses. Separate preprocessors are available for C, COBOL, FORTRAN, and Pascal.
PAGE 74
For HP-UX: First, change into the hpsql directory created when you set up the sample DBEnvironment PartsDBE, as shown in an earlier step. Then change into the programs directory if it exists. If it does not exist, create it, then change into it as follows: mkdir programs cd programs Issue the following commands, one at a time (some will take a few moments to execute): $ rm cex2.sql 4Return5 Only needed if this le exists already. $ cp /usr/lib/allbase/hpsql/programs/cex2 cex2.sql 4Return5 $ psqlc ..
PAGE 75
First, make sure you are in the group and account where PartsDBE exists. Then issue the following commands, one at a time (some take a few moments to execute): For MPE XL: : : : : : : PURGE CEX2 4Return5 Only needed if this le exists already. FCOPY FROM=CEX2.SAMPLEDB.
PAGE 76
Respond to the prompts as in the following dialog. For DBEName, enter hpsql/sampledb/PartsDBE in HP-UX, or PartsDBE in MPE XL.
PAGE 77
The following display should appear (some details can vary on your system): Maintenance word: DBEnvironment Language: n-computer AutoStart: ON User Mode: MULTI DBEFile0 Name: PartsF0 DDL Enabled: YES No. of Runtime Control Block Pages: 37 No. of Data Buffer Pages: 100 No. of Log Buffer Pages: 24 Max. Transactions: 5 >> E 4Return5 Use the E command to exit from SQLUtil and return to your operating system.
PAGE 78
Use option 4 from the SQLSetup menu to create a le that shows all the data de nition commands that were used to build PartsDBE. As an alternative, run the SQLGEN program, as follows: Creating a Schema File with SQLGEN sqlgen 4Return5 SQLGEN displays its banner (some details can be di erent on your system): d a WED, AUG 08, 1990, 11:30 AM HP36217-02A.07.00.04 SQL Command Generator HP SQL/UX (C) COPYRIGHT HEWLETT-PACKARD CO. 1986,1987,1988,1989 >> c b Figure 4-10.
PAGE 79
Purging PartsDBE Use SQLUtil to purge PartsDBE when you no longer need it. Within SQLUtil, you use the PURGEALL command: >> purgeall 4Return5 DBEnvironment Name: PartsDBE 4Return5 Purge DBEnvironment and Log Files (y/n)? y 4Return5 DBEnvironment and Log Files purged. >> exit 4Return5 You can also use SQLSetup option 6 to purge PartsDBE.
PAGE 80
PAGE 81
5 Comparing ALLBASE/SQL with TurboIMAGE If you are coming to ALLBASE/SQL from the world of TurboIMAGE, this chapter should help you make the necessary translations from the terminology of a network DBMS to the terminology of a relational DBMS. The following topics are covered: Basic structures. Procedures for starting up. Tables and indexes versus data sets. Mapping of data types. Di erences in security. Di erences in accessing databases.
PAGE 82
Basic Structures Figure 5-1 shows the basic architecture of the TurboIMAGE system. Figure 5-1. TurboIMAGE Architecture The TurboIMAGE database manager accesses data in each data set as needed, based on information given in the root le.
PAGE 83
Figure 5-2 shows the ALLBASE/SQL architecture. Figure 5-2. ALLBASE/SQL Architecture ALLBASE/SQL has two components which manage access to databases, SQLCore and DBCore. The data is stored in tables which reside physically in DBEFiles and logically in DBEFileSets, as shown in Figure 5-2. These elements are described further in the next paragraphs. Procedures for Starting Up In TurboIMAGE, you start up a database with the following steps: 1. Create a schema.
PAGE 84
The DBECon le contains information about startup parameters for the DBEnvironment and its logs. START DBE NEW also creates a structure within the DBEnvironment known as a system catalog, which is a set of information about all the databases in the DBEnvironment. Use of a Schema In TurboIMAGE, a schema is required to de ne a database. Most users create the schema in an ASCII le. Once the database exists, the schema can serve as a record of its contents.
PAGE 85
you need additional space for a growing table, you add DBEFiles to the DBEFileSet in which the table was created. This increases the capacity of the table. (An exception to this is tables created as hash structures, described in a later section.) Naming Conventions Tables and Indexes versus Data Sets The names of data items and data sets within a TurboIMAGE schema may contain some characters which are not allowed in ALLBASE/SQL.
PAGE 86
Manual Masters versus Hash Structures Each TurboIMAGE manual master data set has a unique primary key item, which provides calculated access to the data in the master, and chained access to the data in the detail data set (if one exists). A manual master may contain data items other than just the key item; therefore, it cannot be automatically updated when detail data changes.
PAGE 87
Both TurboIMAGE and ALLBASE/SQL have data types that do not map exactly to a type in the other system. But a satisfactory mapping with appropriate conversions can easily be done for most TurboIMAGE data types. Mapping of Data Types Basic Mapping Table 5-1 shows the mapping of the most common data types from TurboIMAGE to ALLBASE/SQL: Table 5-1.
PAGE 88
TurboIMAGE and ALLBASE/SQL di er markedly in their implementation of security systems. Differences in Security TurboIMAGE Security Granting and Revoking Authorities The security of TurboIMAGE databases is determined partly by passing MPE le system security and partly by the assignment of user classes and passwords within the schema. Externally, database users must be valid users in the account where the root le resides.
PAGE 89
TurboIMAGE and ALLBASE/SQL both o er a variety of tools for accessing databases, and both provide techniques for concurrency control, to regulate access by more than one user at a time. Differences in Accessing Databases Interactive Access TurboIMAGE interactive access is through Query/V, which lets you nd database entries and report on them using the Query command language.
PAGE 90
addition, tables have an access mode, which you specify when you create them. Tables may be PUBLIC, PUBLICREAD, or PRIVATE, as follows: PUBLIC may be read or updated by anyone who has authority to CONNECT to the DBEnvironment. PUBLICREAD may be read by anyone but only updated by one user at a time. PRIVATE may only be read or updated by a single user at a time.
PAGE 91
Sample Mapping of a TurboIMAGE Database to an ALLBASE/SQL DBEnvironment Consider the following TurboIMAGE schema: BEGIN DATABASE TIPART; PASSWORDS: 12 BUYER; 14 CLERK; 18 DO-ALL; ITEMS: BINNUMBER COUNTCYCLE ITEMCOUNT LASTCOUNTDATE PARTNAME PARTNUMBER SALESPRICE WAREHOUSE , , , , , , , , K 3I I2 X8 X32 X16 P12 X32 (12,14/18); (12/18); (/14,18); (12/18); (14/12,18); (14/12,18); (/12,18); (/12,18); SETS: NAME: PARTS, MANUAL; ENTRY: PARTNUMBER(1), PARTNAME, SALESPRICE; CAPACITY: 301; NAME: INVENTORY, DETAI
PAGE 92
PAGES=200, NAME='DATAF1', TYPE=MIXED; ADD DBEFILE DATAF1 TO DBEFILESET DATAFS; CREATE PUBLIC TABLE INVENTORY (PARTNUMBER CHAR(16) NOT NULL REFERENCES PARTS (PARTNUMBER), BINNUMBER INTEGER NOT NULL, ITEMCOUNT INTEGER NOT NULL, WAREHOUSE CHAR(32) NOT NULL, LASTCOUNTDATE CHAR(8) NOT NULL, COUNTCYCLE_1 SMALLINT NOT NULL, COUNTCYCLE_2 SMALLINT NOT NULL, COUNTCYCLE_3 SMALLINT NOT NULL) IN DATAFS; COMMIT WORK; CREATE DBEFILESET HASHFS; CREATE DBEFILE HASHF1 WITH PAGES=350, NAME= 'HASHF1'; ADD DBEFILE HASHF1 TO DBE
PAGE 93
Using ALLBASE/Turbo CONNECT ALLBASE/Turbo CONNECT allows you to select TurboIMAGE/XL data using ALLBASE/SQL. Using the utility program ATCUtil, you attach the TurboIMAGE database to an ALLBASE/SQL DBEnvironment as shown in Figure 5-3. The attachment process creates a mapping of data sets to tables like the one shown previously in this chapter. Figure 5-3.
PAGE 94
PAGE 95
6 Glossary of Terms in ALLBASE/SQL Ad Hoc Query Archive Logging Attribute DRAFT 9/12/97 Type of query that is issued for the needs of a particular moment. It is usually not stored for later use or built into an application. Ad hoc queries are important in the use of relational databases for decision support. Logging method that uses log les to roll back incomplete transactions after a system failure and to roll forward from an earlier DBEnvironment backup.
PAGE 96
Authority Authorization Group Base Table Class Clustering Index Column Column Authorization Column List Concurrency Constraint Cursor Stability (CS) 6-2 Permission to access speci c objects for speci c purposes within an ALLBASE/SQL DBEnvironment. Three major types are SPECIAL authority, TABLE authority, and RUN authority. See Group. Table upon which a view is based. Special category of ALLBASE/SQL owner that is neither a particular DBEUserID nor a group.
PAGE 97
Data Analysis Database A structured arrangement of data elements designed for the easy selection of information. In ALLBASE/SQL, a database is a collection of tables, views, and indexes having the same ownership in a DBEnvironment. A DBEnvironment may contain several databases. (DBA) The individual with DBA authority who creates and maintains objects in a DBEnvironment.
PAGE 98
DBA Authority DBCore DBECon File DBECreator DBEFile DBEFileSet DBEnvironment DBEUserID 6-4 The most powerful authority within an ALLBASE/SQL DBEnvironment. Includes the authority to create new objects, drop all existing objects, and grant or revoke all authorities for other users. DBA authority implies co-ownership of all objects within the DBEnvironment. A central component of ALLBASE/SQL that performs physical le access and logging.
PAGE 99
Embedded SQL Program Entity Explicit Locking Expression Foreign Key Group Hash Structure Host Variable DRAFT 9/12/97 An application program incorporating SQL statements for programmatic access to ALLBASE/SQL databases. Each embedded SQL statement begins with the keywords EXEC SQL. Embedded SQL programs are preprocessed, then compiled before execution. For most SQL commands, the preprocessor stores a section, or runtime version of the command, in the DBEnvironment.
PAGE 100
Implicit Locking Index Index Scan Integrity Constraint ISQL Isolation Level Join 6-6 Locking of tables in transactions according to table type and isolation level. For example, PRIVATE tables are locked exclusively for all access; PUBLIC tables are locked exclusively only for write operations. A data structure that potentially speeds access to table data through the use of an index scan. The four types of index are: unique, clustering, unique and clustering, and non-unique and non-clustering.
PAGE 101
Key Value Locking Logging Message Catalog Message File Modified Source File Module Native Language DRAFT 9/12/97 The value contained in the columns of a key. Key values are stored in index pages along with pointers to the location of rows in data pages. A technique for concurrency control through which ALLBASE/SQL restricts access to data by one individual when the data is being used by another. Locks are of three types: shared, exclusive, or shared with intent to become exclusive.
PAGE 102
Nonarchive Logging Normalization Object Optimizer Owner Predicate The default logging method. Uses log les to roll back (that is, undo) incomplete transactions that were not committed at the time of a system failure. A formal process of adjusting table design in relational databases by examining and adjusting the relationships among columns. A structure created and stored in an ALLBASE/SQL DBEnvironment. The most common objects are tables, views, indexes, and groups.
PAGE 103
Projection Query Query Language Query Result Read Committed (RC) Read Uncommitted (RU) Referential Constraint Relation Relational Operations Relationship Repeatable Read (RR) DRAFT 9/12/97 Relational operation that extracts a subset of columns from a table. Request for information from database tables. A typical example is a SELECT statement. A set of operators, expressions, and commands that let you manipulate a database. The query language of ALLBASE/SQL is SQL.
PAGE 104
Result Table Rollback Recovery Rollforward Recovery Row Run Authority Schema See Query Result. A process by which ALLBASE/SQL backs out of incomplete transactions using a log le. If a DBEnvironment stops while some transactions are still in progress, they must be undone the next time the DBEnvironment starts up. A process by which ALLBASE/SQL reapplies transactions to a DBEnvironment from a log le. Rollforward recovery requires the use of archive logging. Horizontal division within a database table.
PAGE 105
SQL SQLCore SQLGEN SQLMigrate SQLUtil Structured Query Language Subquery SYSTEM See Structured Query Language. A central component of ALLBASE/SQL. SQLCore checks the syntax of commands and prepares them for processing. SQLCore also optimizes queries, that is, chooses the best access path to the data. A utility program for database administrators that generates the SQL commands necessary to re-create all or part of a DBEnvironment.
PAGE 106
System Table System View Table Table Authority Transaction Unique Constraint Unique Index Validation View 6-12 See System View. A component view within the system catalog. You can issue queries on the views in the system catalog just as you would on ordinary database tables to display information about the DBEnvironment. Basic unit of data storage in a relational database. Also known as a relation. Tables consist of rows and columns. A result table is a query result displayed in tabular form.