←Part 21 Part 23→
The HP 3000--For Complete Novices
Part 22: Working with Datasets
 |
by George Stachnik
In the last installment, we created the root file using DBSCHEMA, and learned that the root file contains a good deal of information about the structure of the database. The next step is to create the various datasets (masters and details) that make up the database. Figure 1 shows how this is done. The first step is to run a program called DBUTIL.PUB.SYS. Like DBSCHEMA, DBUTIL is bundled on every HP 3000 system, and like DBSCHEMA, it expects to read input data from $STDIN. But unlike DBSCHEMA, the information that DBUTIL expects to read is very simple. So you generally won't need to redirect $STDIN the way we did with DBSCHEMA. If you enter the :RUN command shown in Figure 1, DBUTIL will begin by displaying a banner. As was the case with DBSCHEMA, this banner contains information about the version of IMAGE/SQL you're running. Figure 1: DBUTIL
:RUN DBUTIL.PUB.SYS
HP30391C.07.04 TurboIMAGE/XL: DBUTIL (C) COPYRIGHT HEWLETT-PACKARD COMPANY 1987
>>CREATE ORDERS
Database ORDERS has been CREATED.
>>EXIT
END OF PROGRAM
:LISTFILE,2
ACCOUNT= STACHNIK GROUP= PUB
FILENAME CODE -----------LOGICAL RECORD------------ ----SPACE-----
SIZE TYP EOF LIMIT R/B SECTORS#X MX
ORDERS PRIV 128W FB 8 8 1 16 1 1
ORDERS01 PRIV 512W FB 21 21 1 96 1 1
ORDERS02 PRIV 512W FB 6 6 1 32 1 1
ORDERS03 PRIV 512W FB 83 83 1 336 1 2
SCHEMA 80B FA 48 48 3 16 1 2
Note that the banner refers to IMAGE/SQL by its old name,
"TurboIMAGE/XL." This might lead you to believe that
we are running some old version of the DBMS. In fact, we are
using the "latest and greatest" version. The version
number (HP30391C.07.04) confirms this. The fact that the old
name still shows up in the banner means nothing more than that
the R&D organization hasn't kept up with the latest name
change from marketing. Personally, I'd much rather have them
fixing bugs and keeping the product working than worrying about
marketing anyhow.
After the banner, DBUTIL displays a double angle bracket prompt
(>>) on your terminal. This is your signal to enter a DBUTIL
command. DBUTIL has a rich command language, and we'll be returning
to this utility again and again in the next couple of months.
For now, we're going to introduce only one of the DBUTIL commands,
and that's the CREATE command, shown in Figure 1.
>>CREATE ORDERSThis simple command tells DBUTIL to look for a root file called ORDERS. (Remember that this is the file we created using DBSCHEMA.) DBUTIL will then open that file and create a database using the specifications that were stored there by DBSCHEMA. If all goes well, the message "Database ORDERS has been CREATED." will be displayed on your terminal screen, as shown in Figure 1. DBUTIL will then display the ">>" prompt again, allowing us to enter other DBUTIL commands. But since we haven't learned any other commands yet, and since I'm not planning to show you any more of them in this article, let's leave DBUTIL and move on to something else. By typing the command "EXIT" we will exit DBUTIL and return to an MPE/iX colon prompt. The next thing you see in Figure 1 is a :LISTFILE command. This MPE/iX command displays all the files that currently reside in our logon group. In addition to the root file (ORDERS) and the schema, DBUTIL has created three new files named ORDERS01, ORDERS02, and ORDERS03. If you're sharp, you've probably already figured out what these three files are. (Think about it before you go on reading.) Did you figure it out? ORDERS01, ORDERS02, and ORDERS03 are files that contain the three datasets that make up the ORDERS database. One of them will be used to store the data that goes into the CUSTOMER dataset. One will be used for the DATE-MASTER dataset, and one will be used for the SALES dataset. Looking at this :LISTFILE listing, you can't really tell which file represents which dataset. Nor can you tell which of them is a detail dataset or which ones are masters. In fact, the :LISTFILE listing tells you only two things that are useful: 1. These are all PRIV files--so there's no point in trying to display their contents with the :PRINT command, or with FCOPY, EDITOR, or any of the other file system utilities that we've learned to use with ordinary files such as the SCHEMA. It's important to keep in mind the fact that IMAGE/SQL databases are much more than collections of ordinary flat files. For one thing, IMAGE/SQL databases can be accessed only by using IMAGE/SQL programs such as DBUTIL. 2. The sizes of the files are displayed on the far right under the heading "SECTORS." At this time, there is no user data in the database--it's a big empty box. In spite of this, the numbers shown in Figure 1 are pretty large. This is another way that IMAGE/SQL databases differ from ordinary flat files. With an IMAGE/SQL database, the numbers displayed under the SECTORS heading will not change when you begin adding data to the database. This is because, when you build an ordinary file, you allocate only a part of the disk space that will be required (at least by default). Then, as you add records to the file, it will grow and additional disk space will be allocated as it is needed. By contrast, when you build an IMAGE/SQL database, IMAGE/SQL calculates how much disk space will be required to hold the number of data items that you specified in the schema (the CAPACITY). This is the amount of disk space that will be allocated by DBUTIL when you create the database. This may seem like a waste of disk space, but it actually makes a lot of sense. For one thing, you can be certain that you'll never be unable to add a record to the database because you ran out of disk space. All the necessary space is allocated for you before even one record is added--when the database is created. Having created our root file and the database, we're now ready to move on and start adding records (data entries) to the database. There are at least four ways to do this. 1. IMAGE/SQL databases can be backed up to tape or other media using a variety of utilities, some of which are available from HP, and some of which come from third parties. If you were loading a database from a backup, you'd use one of these utilities. Again, a future article will discuss database backups in detail. 2. Probably the most common way to load an IMAGE/SQL database is to run some kind of a user-written application program. For example, you might want to use a program that accepts input from a terminal and writes it to the database. In order to learn how to write such a program, you're going to need to learn a database language. IMAGE/SQL is a bilingual database. That is, you can access it using either of two different database languages: a) IMAGE/SQL's "native" language is a proprietary database language called DDL (Data Definition Language). It's proprietary because it's unique to the HP 3000. The disadvantages of proprietary languages have been well documented, but don't stop reading yet. If you really want to understand IMAGE/SQL, you need to learn at least the rudiments of its proprietary language. We'll be exploring DDL in future articles. b) IMAGE/SQL databases can also be accessed using the industry standard Structured Query Language (SQL). We'll be discussing ways of doing this in future articles.3. There are many fourth-generation languages (4GLs) on the market that allow you to access IMAGE/SQL databases without having to write a program or learn a database language such as DDL or SQL. Because every one of these is different, we will not be discussing them any further in these articles. 4. Finally, there is a program called QUERY.PUB.SYS. As we'll see, QUERY is not an ideal tool for loading an IMAGE/SQL database. In spite of this, we are going to explore QUERY in this article because it's a very good tool for learning about IMAGE/SQL. QUERY.PUB.SYS--The Poor Man's 4GLQUERY shares some characteristics with 4GLs. Like a 4GL, it allows you to read and write data in an IMAGE/SQL database without the need to write a program. Unlike 4GLs, QUERY is bundled with MPE. It resides on every HP 3000, and you don't have to pay extra for it. QUERY is not a true 4GL. It is not trivial to learn to use. In fact compared to modern 4GLs, it is positively obtuse and difficult. It can be a real performance hog. So as a tool for use in a production environment, QUERY leaves much to be desired. However, unlike most 4GLs, QUERY demands an understanding of the underlying database management system. So as an educational tool for learning how IMAGE/SQL works, it's pretty good (and the price is certainly right). So with that in mind, let's see how one might use QUERY to do a few basic database operations.Opening a Database Using QUERYRunning QUERY is simple enough. The :RUN command shown in Figure 2 will do the job. As we saw with DBUTIL, QUERY will display a prompt on your terminal, only this time it's a single angle bracket: (>). This tells you that you're ready to run QUERY commands. Figure 2: QUERY
:RUN QUERY.PUB.SYS
HP32216D.03.11 QUERY/3000 TUE, SEP 7, 1999, 11:30 AM
COPYRIGHT HEWLETT-PACKARD CO. 1976
>DATA-BASE=ORDERS
PASSWORD = >>
MODE = >>1
>FORM SETS
DATA BASE: ORDERS TUE, SEP 7, 1999, 11:30 AM
DATA BASE LANGUAGE ATTRIBUTE: NATIVE-3000
ITEM CURRENT ENTRY ENTRY BLOCKING
SETS: TYPE COUNT CAPACITY COUNT LENGTH FACTOR
CUSTOMER M 6 201 0 39 10
DATE-MASTER A 1 211 0 3 36
SALES D 8 1245 0 26 15
Usually, the first thing you will want to do is to tell QUERY what
database you wish to access. As you can see in Figure 2 this is
done using the DATA-BASE command. The hyphen is required, and the word
DATA-BASE should be followed by an equal sign ("=") and the name of the
database you wish to access (that is, the filename of the root file). In
Figure 2, this command looks like this:
>DATA-BASE=ORDERSEarlier, we said that IMAGE/SQL databases could not be accessed by file system utilities like FCOPY, but only by IMAGE/SQL programs such as DBUTIL. QUERY is also such a utility. We also said that IMAGE/SQL had its own security structure and that in order to access an IMAGE/SQL database, you had to pass IMAGE/SQL's own rules for security. We're about to come face to face with IMAGE/SQL security. When you enter the DATA-BASE command, QUERY will attempt to open the database that you specified. It will immediately prompt you for the database password. The password that it's referring to was initially in the PASSWORDS section of the schema. In our sample schema, we defined three passwords: CLERK, SUPER and DO-ALL. 4 CLERK; << SALES CLERK >> 8 SUPER; << SUPERVISOR OF CUSTOMER ORDERS DEPARTMENT >> 15 DO-ALL; << PROGRAMMER/ANALYST - CREATOR OF DATA BASE>>In an earlier article in this series, we discussed how these passwords are used in some detail. If you still have that article, this might be a good time to review it. Depending on which password you specify at this point, QUERY will grant you a different level of access. For example, if we respond to QUERY's PASSWORD prompt by typing "CLERK," then we will be assigned to user class number 4. In our schema, users in class 4 were granted read access to virtually every dataset and data item in the database, but write access to nothing. Since we want to be able to write new records into our database, we will respond to QUERY's PASSWORD prompt with the password DO-ALL. This assigns us to user-class 15, which grants us write access to every level of the database. Note that in Figure 2, the string "DO-ALL" does not appear. This is because database passwords are not echoed to your terminal when you type them. QUERY does this in order to prevent "shoulder-surfing" (stealing passwords by looking over somebody's shoulder as they're typing a password). While we're on the subject of securing your database passwords, it's worth mentioning in passing that although you originally coded the database passwords in the schema, IMAGE/SQL maintains them in the database's root file. In fact, once you've created the root file, IMAGE/SQL has no further use for the schema. You should never leave the schema lying around in the same group as the database itself, or anyplace else where it's subject to unauthorized access. This is because the schema contains valuable information (such as the database passwords). Remember that the schema is not protected by IMAGE/SQL's security system--anyone who has access to the file containing the schema can display it with an ordinary MPE command. IMAGE/SQL will not use the schema again unless you decide to recreate the database. So it's a good practice to keep a backup copy of the schema in a secure location, and get it off of your system. As soon as you've typed a password, QUERY will prompt you for a "mode." In Figure 2, we've responded to this prompt with the number 1. IMAGE/SQL allows database access in a variety of different modes, and each one is identified with a number (1, 2, 3, and so forth). The mode that you select will depend upon whether or not you want to be able to add records to the database, update existing records, or share the database with other users. For now, we're going to use mode 1 without any explanation beyond the fact that mode 1 will allow us to add records to the database (assuming that we've entered an appropriate password). We'll discuss these modes in a lot more detail in a future article. Displaying Your Database's Structure: FORM SETSOnce you've opened a database with the DATA-BASE command and provided an appropriate password and mode, you're ready to begin using QUERY to access the database. The first thing we'll do is to display the database structure. You can do this using the command FORM SETS, as shown in Figure 2. FORM SETS displays a line of information about each of the three datasets that make up our database: CUSTOMER, DATE-MASTER, and SALES. You'll recognize a good deal of this as being the same information that came out of DBSCHEMA when we created the root file. One column that's new is the ENTRY COUNT. This tells us how many data entries (i.e., records) there are in each dataset. Figure 2 shows an ENTRY COUNT of zero for each of the three datasets. This is because we haven't added any data to the database yet.Displaying a Dataset's StructureThe FORM SETS command displays information about each of the datasets in the database. What if you want to find out about the structure of an individual dataset? You can do this by typing the FORM command, followed by the name of the dataset. For example, Figure 3 shows the result of typing the command FORM CUSTOMER. Figure 3: Using FORM
>FORM CUSTOMER
DATA BASE: ORDERS TUE, SEP 7, 1999, 11:30 AM
DATA BASE LANGUAGE ATTRIBUTE: NATIVE-3000
SET NAME:
CUSTOMER,MANUAL
ITEMS:
ACCOUNT, X6 <<KEY ITEM>>
LASTNAME, X16
FIRSTNAME, X10
STREET, X26
CITY, X14
ZIPCODE, X6
CAPACITY: 201 ENTRIES: 0
Once again, virtually all of the information shown here came
straight from the schema. We see the CUSTOMER is a MANUAL master
dataset, and just below that, we see the names of the various
data items that make up the data entries (records) of CUSTOMER.
Remember that manual master datasets serve as indices to other
datasets. In this example, one of the items (ACCOUNT) is a "key-item."
Every record in the CUSTOMER dataset will contain the various
items shown in Figure 3, as
well as a pointer to entries in other datasets that share the
same value in ACCOUNT. In this case, those entries are in the
sales dataset. To see how this looks, let's do a FORM SALES next.
Figure 4 shows the result
of using the FORM command on the SALES dataset. SALES is a detail
dataset. Detail datasets cannot index any other dataset in the
database. Instead, detail datasets can be (and almost invariably
are) indexed by one or more master datasets. Looking at the items
shown in Figure 4, we see that two of them are labelled
"search items": ACCOUNT and DATE.
Figure 4: Using FORM on the SALES Dataset
FORM SALES
DATA BASE: ORDERS TUE, SEP 7, 1999, 11:30 AM
DATA BASE LANGUAGE ATTRIBUTE: NATIVE-3000
SET NAME:
SALES,DETAIL
ITEMS:
ACCOUNT, X6 <<SEARCH ITEM>>
DATE, X6 <<SEARCH ITEM>>
PART-NO, X8
PART-NAME, X14
QTY, J2
PRICE, J2
TOTAL, J2
DELIV-DATE, X6
CAPACITY: 1245 ENTRIES: 0
One of them (ACCOUNT) is the very item that we just saw in
the CUSTOMER dataset. The other one (DATE) appears in the DATE-MASTER
dataset, as you can see in Figure 5.
Figure 5: DATE-MASTER Dataset
FORM DATE-MASTER
DATA BASE: ORDERS TUE, SEP 7, 1999, 11:30 AM
DATA BASE LANGUAGE ATTRIBUTE: NATIVE-3000
SET NAME:
DATE-MASTER,AUTOMATIC
ITEMS:
DATE, X6 <<KEY ITEM>>
CAPACITY: 211 ENTRIES: 0
Thus far, everything that we've done with QUERY has been geared
toward displaying information about the database--we haven't
actually done anything yet. In particular, we haven't added any
records to the database.
So if it's 4 am as you're reading this, and if you've
been called into the office in the middle of the night to add
a single record to an IMAGE/SQL database in order to recover
from some horrible failure, and if you've been feverishly following
the steps I've been giving you--well, I hate to tell you this,
but you didn't really need to do any of the things I've shown
you thus far.
We've just been sort of noodling our way around to see what
the database looked like before we actually tried to do anything
serious like add a record to it. But now I think we're almost
ready. But before we do, we need to review a few of the rules.
The RulesWhen you're adding data entries (or records, if you prefer) to an IMAGE/SQL database, there are three rules that must be followed: 1. Here's the first rule. When you're adding a record to an IMAGE/SQL database, there are only two possibilities: you can add a record either to a detail dataset (such as SALES) or to a manual master dataset (such as CUSTOMER).a. If you're sharp, then you are already asking yourself this question: "IMAGE/SQL databases are made up of three different kinds of datasets--details, manual masters, and automatic masters--isn't there a third possibility? Can't I add records to automatic master datasets?" b. And if you're really sharp, then you remember from a previous article that you can't add records to automatic masters--IMAGE/SQL takes care of them (you guessed it) automatically.2. Here's the second rule: You cannot add a record to a detail dataset unless every search item in the record has a matching record in the corresponding manual master dataset. In our example (see Figure 4) the SALES detail dataset contains two search items named DATE and ACCOUNT. The fact that these are search items tells you that each of them must also appear in a master dataset. a. You don't have to worry about the DATE item, because DATE is indexed by an automatic master (DATE-MASTER--see Figure 5). When you add a record to SALES, IMAGE/SQL will check DATE-MASTER to ensure that there's an entry that already has that date, and if not, it will add one for you--automatically. b. But you do have to worry about the ACCOUNT search item. Before you can add a record to SALES, you must already have a matching record in CUSTOMER (the corresponding manual master). For example, if you're trying to insert a record into SALES in which the ACCOUNT item contains the string GEORGE, then there must already be a record in CUSTOMER with GEORGE in the ACCOUNT item. Otherwise, IMAGE/SQL will not allow you to add the record to the SALES dataset.3. This rule is the converse of the previous one: If you add a record to a manual master, make sure that you understand that you've also made it possible to add matching records to the corresponding detail datasets. a. Think of manual master datasets as tables of all valid values. For example, by adding a record to a manual master with the string GEORGE in the ACCOUNT item, you've effectively just created a new customer for your company named GEORGE. b. You've made it possible to add records to any corresponding detail datasets (such as SALES) with a matching ACCOUNT value of GEORGE. Adding a RecordTo illustrate these rules, let's try to break them. The QUERY command to add a record to a dataset is shown in Figure 6. We type the commandADD SALESThis tells QUERY that we want to add a record to the SALES dataset. QUERY obligingly prompts us with the names of each of the data-items in the dataset. It prompts us for ACCOUNT, and so we type a value: "123." It goes on to prompt us for each item (DATE and so on), building the record one item at a time. When the last item (DELIV-DATE) has been entered, it tries to insert the record in the SALES dataset, only to find that there is no record in the CUSTOMER dataset with the value "123" in the ACCOUNT item. Since CUSTOMER is a manual master, the operation fails. Figure 6: Adding a Record
>ADD SALES
ACCOUNT =>>123
DATE =>>9/7/99
PART-NO =>>12345
PART-NAME =>>WIDGET
QTY =>>1
PRICE =>>12.34
TOTAL =>>12.34
DELIV-DATE =>>9/8/99
MISSING SEARCH KEY VALUE FOR ACCOUNT IN MASTER CUSTOMER
==============| ENTRY CANNOT BE ADDED |==============
"CTRL-Y"
>ADD CUSTOMER
ACCOUNT =>>GEORGE
LASTNAME =>>STACHNIK
FIRSTNAME =>>GEORGE
STREET =>>10955 PRUNERIDGE
CITY =>>CUPERTINO
ZIPCODE =>>95014
"CTRL-Y"
>FORM SETS
DATA BASE: ORDERS TUE, SEP 7, 1999, 11:33 AM
DATA BASE LANGUAGE ATTRIBUTE: NATIVE-3000
ITEM CURRENT ENTRY ENTRY BLOCKING
SETS: TYPE COUNT CAPACITY COUNT LENGTH FACTOR
CUSTOMER M 6 201 1 39 10
DATE-MASTER A 1 211 0 3 36
SALES D 8 1245 0 26 15
In order to add a record to SALES, we must first create a
corresponding entry in CUSTOMER. To do this, we press CTRL-Y
to break out of the ADD SALES command, and enter a new command
also shown in Figure 6:
>ADD CUSTOMERThis command tells IMAGE/SQL that you're going to add a record to the CUSTOMER dataset. Once again, IMAGE/SQL prompts you with the names of the items in the dataset. This time, however, everything goes according to plan. We now have a record in the CUSTOMER dataset. We can verify this by entering the FORM SETS command again. Compare the FORM SETS output in Figure 6 to that which we saw earlier in Figure 2. There's only one difference: the ENTRY COUNT for the CUSTOMER dataset has changed--in Figure 2 it was zero, but in Figure 6 it's 1. Now we can add a record to SALES, and as long as we're careful about the values we add, there should be no problems. Figure 7 shows us trying the ADD SALES command again. This time when it asks us for ACCOUNT, we enter the string GEORGE (the same account value we added to CUSTOMER in Figure 6). The record is inserted without incident. One more interesting point about Figure 7. At the bottom of this figure is another example of the FORM SETS command. Note the entry counts. There's one record in CUSTOMER and one in SALES. There's also a new record in DATE-MASTER. You might be wondering how that got there. We didn't do an ADD DATE-MASTER. And it's a good thing we didn't because it wouldn't have worked. Remember that DATE-MASTER is an automatic master. That means you can't add records to it the way we added a record to CUSTOMER. Then how did the ENTRY COUNT for DATE-MASTER get bumped up to one? Easy--when we added our record to SALES, it had a value of "9/8/99" in the DATE item. IMAGE/SQL automatically created a corresponding record in DATE-MASTER. In this article we've walked you through the basic steps of creating an IMAGE/SQL database, and of inserting new records in that database using QUERY. Next time out we'll drill down deeper into IMAGE/SQL, and learn more about how to find and display existing records. Figure 7: Adding a Record to SALES
>ADD SALES
ACCOUNT =>>GEORGE
DATE =>>9/8/99
PART-NO =>>123
PART-NAME =>>WIDGET
QTY =>>1
PRICE =>>12.34
TOTAL =>>12.34
DELIV-DATE =>>9/9/99
ACCOUNT =>>
< CONTROL Y >
>FORM SETS
DATA BASE: ORDERS TUE, SEP 7, 1999, 11:34 AM
DATA BASE LANGUAGE ATTRIBUTE: NATIVE-3000
ITEM CURRENT ENTRY ENTRY BLOCKING
SETS: TYPE COUNT CAPACITY COUNT LENGTH FACTOR
CUSTOMER M 6 201 1 39 10
DATE-MASTER A 1 211 1 3 36
SALES D 8 1245 1 26 15
George Stachnik works in technical training in HP's Network Server Division. |
![[3khat]](/img/3khat16.ico)
3kRanger