900 Series HP 3000 Computer Systems Getting Started with HP IMAGE/SQL ABCDE Printed in U.S.A.
Company or product names followed by R are U.S. registered trademarks of the respective company. Company or product names followed by TM are registered trademarks of the respective company. Forest & TreesTM is a registered trademark of Trinzic, Incorporated. Microsoft WindowsTM and Microsoft R are U.S. registered trademarks of Microsoft Corporation. PowerBuilderTM is a registered trademark of Powersoft Corporation. The information contained in this document is subject to change without notice.
Printing History 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 Edition Second Edition June 1993 December 1994 36385-B.F0.
This book describes how to start using HP IMAGE/SQL. Preface HP IMAGE/SQL is o ered on HP 3000 computers using the MPE/iX operating system. MPE/iX, Multiprogramming Executive with Integrated POSIX, is the latest in a series of forward-compatible operating systems for the HP 3000 line of computers. MPE/iX is a superset of MPE XL. All programs written for MPE XL will run without change under MPE/iX. Note Hereafter in this manual, HP IMAGE/SQL will be referred to as IMAGE/SQL.
Additional Documentation Refer to the listed manuals for additional information: HP IMAGE/SQL Administration Guide (36385-90001) This guide explains how to administer and maintain IMAGE/SQL. For this manual, you should be familiar with and have a general knowledge of relational databases. TurboIMAGE/XL Database Management System Reference Manual (30391-90001) This manual describes the TurboIMAGE/XL Database Management System for the HP 3000 Series 900 computer.
This manual presents the syntax and semantics of SQL on HP 3000 computers. This manual contains basic information about ALLBASE/SQL, as well as in-depth information about ALLBASE/SQL data types and statements. The rst three chapters are for all readers, including new users of ALLBASE/SQL. The remaining chapters are for experienced SQL users and SQL application programmers.
Conventions UPPERCASE In a syntax statement, commands and keywords are shown in uppercase characters. The characters must be entered in the order shown; however, you can enter the characters in either uppercase or lowercase. For example: COMMAND can be entered as any of the following: command italics Command COMMAND In a syntax statement or an example, a word in italics represents a parameter or argument that you must replace with the actual value.
Conventions [ ] (continued) In a syntax statement, brackets enclose optional elements. In the following example, OPTION can be omitted: COMMAND FileName [OPTION] When several elements are stacked within brackets, you can select one or none of the elements. In the following example, you can select OPTION or Parameter or neither. The elements cannot be repeated. COMMAND FileName [ . ..
Contents 1. Introducing IMAGE/SQL What Is IMAGE/SQL? . . . . . . . . . . . . . What Are the Components of IMAGE/SQL? . . . TurboIMAGE/XL . . . . . . . . . . . . . . ALLBASE/SQL . . . . . . . . . . . . . . . HP PC API for ALLBASE/SQL and IMAGE/SQL ALLBASE/NET . . . . . . . . . . . . . . . IMAGE/SQL Utility . . . . . . . . . . . . . ISQL . . . . . . . . . . . . . . . . . . . . SQLGEN . . . . . . . . . . . . . . . . . . SQLUtil . . . . . . . . . . . . . . . . . . . Preprocessors . . . . . . . . . . . . . . .
3. Moving from TurboIMAGE/XL to IMAGE/SQL Basic Structures . . . . . . . . . . . . . . . . Creating Databases . . . . . . . . . . . . . . . Root File versus DBECon File and System Catalog Naming Conventions . . . . . . . . . . . . . Tables versus Data Sets . . . . . . . . . . . . . Mapping of Data Types . . . . . . . . . . . . . Basic Mapping . . . . . . . . . . . . . . . . Compound Items . . . . . . . . . . . . . . . Default Values . . . . . . . . . . . . . . . . De ning Security . . . . . . . . . . . . .
ATCLOG Reference . . . . . . . . . Logging IMAGE/SQL Utility Commands . . Using IMAGE/SQL Utility Command Files . Accessing TurboIMAGE/XL Data with ISQL Maintaining the ATCINFO File . . . . . . . . . . . . . . . . . . . . . 5-20 5-21 5-24 5-26 5-27 6. Practicing with IMAGE/SQL Using MusicDBE Setting Up MUSIC and MusicDBE . . . . . . Examining The Database . . . . . . . . . . Examining Table Descriptions . . . . . . . Examining the Authority Structure . . . . . Adding Data to the Database . . . . . . .
Figures 1-1. 2-1. 2-2. 3-1. 3-2. 4-1. 5-1. 5-2. 6-1. 6-2. 6-3. 6-4. 6-5. 6-6. 6-7. 6-8. 6-9. 6-10. 6-11. 6-12. Overview of IMAGE/SQL . . . . . . . . . Relational Operations . . . . . . . . . . . Structure of a DBEnvironment . . . . . . . TurboIMAGE/XL Architecture . . . . . . IMAGE/SQL Architecture . . . . . . . . . Database Design . . . . . . . . . . . . . IMAGE/SQL Utility Command Prerequisites ISQL Banner . . . . . . . . . . . . . . SQLSetup Menu . . . . . . . . . . . . .
1 Introducing IMAGE/SQL This chapter introduces IMAGE/SQL: What is IMAGE/SQL? What are the components of IMAGE/SQL? What are the bene ts of using IMAGE/SQL? How do IMAGE/SQL and ALLBASE/SQL coexist? What Is IMAGE/SQL? IMAGE/SQL is one of Hewlett-Packard's relational database management systems. IMAGE/SQL provides relational access to your TurboIMAGE data using the industry-standard Structured Query Language (SQL).
Figure 1-1 shows how IMAGE/SQL, TurboIMAGE/XL, and ALLBASE/SQL are related. Figure 1-1.
IMAGE/SQL includes TurboIMAGE/XL, certain components of ALLBASE/SQL, and a database administration tool that links them together. The database administration tool is the IMAGE/SQL utility. The components of IMAGE/SQL are described below: What Are the Components of IMAGE/SQL? TurboIMAGE/XL ALLBASE/SQL HP PC API for ALLBASE/SQL and IMAGE/SQL ALLBASE/NET IMAGE/SQL Utility ISQL A set of programs and procedures for de ning, creating, accessing, and maintaining TurboIMAGE/XL databases.
SQLGEN SQLUtil Preprocessors 1-4 Introducing IMAGE/SQL A database administrator's tool that generates the commands used to recreate all or part of an existing DBEnvironment. It also generates LOAD/UNLOAD and UPDATE STATISTICS statements. These commands are placed in one or more data les called schema les, which can then be used as ISQL command les to recreate the DBEnvironment (not the TurboIMAGE/XL database.
What Are The Benefits of Using IMAGE/SQL? Using IMAGE/SQL, you bene t in the following ways: By leveraging your investment| Your existing investment in TurboIMAGE/XL is preserved while you take advantage of relational technology. You can develop new applications in SQL to access TurboIMAGE/XL databases through IMAGE/SQL. At the same time, your current TurboIMAGE/XL access is completely compatible with the SQL access to TurboIMAGE/XL.
2 Basic Concepts This chapter familiarizes you with these basic ideas, tasks, and concepts before venturing on to IMAGE/SQL tasks: What is a database? What is a relational database? What is SQL? What is TurboIMAGE/XL? What is a DBEnvironment? How do I create a DBEnvironment? How do I create a database? How do I access a database through SQL? How do I control database access? What Is a Database? A database is a structured arrangement of data elements designed for the easy selection of information.
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.
Sample Database Table The following is a portion of a database table consisting of names and account balances for an employee credit union: Employee Accounts Last Name First Name Telephone Employee Number Balance Harrison Gerald 7233 2432099 142.59 Abelson Annette 4312 3510044 2345.09 Stanley Peter 1235 3540011 321.98 Walters Georgia 2554 9124772 1230.10 Each column can accept data of a speci c type and size.
Using Several Tables You can put the same data into several di erent tables, as shown below: Table 1. Employees Table Last Name First Name Employee Number Harrison Gerald 2432099 Abelson Annette 3510044 Stanley Peter 3540011 Walters Georgia 9124772 Table 2. Telephone Table Last Name First Name Telephone Harrison Gerald 7233 Abelson Annette 4312 Stanley Peter 1235 Walters Georgia 2554 Table 3. Accounts Table Employee Number Account Balance 2432099 142.59 3510044 2345.
What Is SQL? SQL is the acronym for Structured Query Language, which is de ned by ANSI standards in the United States and by X/OPEN standards in Europe. The way into a relational database is through a query language, which is a set of operators, expressions, and statements that let you manipulate the database in various ways. You create queries, as well as other kinds of statements, in IMAGE/SQL by using SQL.
What Is a DBEnvironment? In IMAGE/SQL, you create one or more databases in a structure called a DBEnvironment. The structure of a DBEnvironment is shown in Figure 2-2. Figure 2-2. Structure of a DBEnvironment The following objects are the most important parts of the DBEnvironment: The DBECon le DBEFiles DBEFileSets Databases Tables Authorities System catalog Log les Objects are structures created and stored in an IMAGE/SQL DBEnvironment.
The DBECon file DBEFiles DBEFileSets Databases When you create a DBEnvironment, IMAGE/SQL creates a physical le known as the DBEnvironment Con guration File or DBECon le. This le contains basic information that is used every time the DBEnvironment is opened. The DBECon le has the same name as the DBEnvironment itself. If the DBEnvironment is like a library, the DBECon le is like a building directory that points to the other DBEnvironment components.
Tables The most important objects you create are tables. A table is like a periodical stored in a library, and the rows in the table are like individual articles in the periodical. Tables have only logical names; a table name is like the title of the periodical. When you create a table, you only need to de ne its name and characteristics and associate it with a DBEFileSet. This is like adding a new periodical to a subject category in the library.
How Do I Create a DBEnvironment? You use the SQL START DBE NEW statement to create a DBEnvironment: START DBE 'DBEnvironmentName' NEW Once the DBEnvironment exists, you can create databases within it. The START DBE NEW statement lets you supply options to specify many of the run-time characteristics of the DBEnvironment. The simple form of the statement shown above uses default values for these options.
How Do I Access a Database through SQL? You use the SQL CONNECT statement to establish a connection to a DBEnvironment. This statement must be executed by some type of SQL program, such as: ISQL Application programs you create yourself Third-party applications Existing programs During database access, you perform queries or other operations that manipulate data by inserting, deleting, or modifying rows in tables. This process is called data manipulation.
How Do I Control Database Access? You use data control language to determine who has access to the information in a database. This is very important for security. Data control language confers authorities on speci c users to perform speci c tasks. The most powerful authority is DBA authority (database administrator authority). The DBECreator, that is, the person who creates a new DBEnvironment, automatically has DBA authority in that DBEnvironment.
3 Moving from TurboIMAGE/XL to IMAGE/SQL If you are coming to IMAGE/SQL from the world of TurboIMAGE/XL, this chapter will help you make the transition. This chapter describes comparative terminology and discusses steps in using IMAGE/SQL. At the completion of this chapter, you will have the knowledge of IMAGE/SQL needed to complete the tasks in Chapters 4, 5, and 6.
Although IMAGE/SQL and TurboIMAGE/XL use di erent terminology, they share several similar concepts. Table 3-1 lists IMAGE/SQL terms and lists the equivalent TurboIMAGE/XL concept. The IMAGE/SQL terms listed here are de ned in more detail in the Glossary. Table 3-1.
Basic Structures Figure 3-1 shows the basic architecture of the TurboIMAGE/XL system. Figure 3-1. TurboIMAGE/XL Architecture The TurboIMAGE/XL database manager accesses data in each data set as needed, based on information given in the root le.
Figure 3-2 shows the IMAGE/SQL architecture. Figure 3-2.
Creating Databases In TurboIMAGE/XL, you create a database using the following steps: 1. Create a schema. A common way of doing this is to enter the schema into a text le created with an editor. 2. Run DBSCHEMA to generate a root le from the schema. 3. Run DBUTIL to create data sets based on the root le. 4. Create a DBEnvironment. With TurboIMAGE/XL, a schema is required to de ne a database.
Root File versus DBECon File and System Catalog With TurboIMAGE/XL, the root le, which is generated by DBSCHEMA from the schema, contains security information and de nitions of all the data sets in the database. The user executing DBSCHEMA becomes the database creator. The DBECon le in ALLBASE/SQL is created when you issue the START DBE NEW statement in ISQL.
Tables versus Data Sets In TurboIMAGE/XL, the access mechanism is the data set, which consists of a set of entries containing an ordered series of data items. A data set is either a detail or a master, and, if it is a master, it is either manual or automatic. In SQL, a table is an unordered set of rows containing columns. In IMAGE/SQL, a table is synonymous with a data set. Tables are not labeled manual or automatic, master or detail.
Defining Security TurboIMAGE/XL Security Granting and Revoking IMAGE/SQL Authorities TurboIMAGE/XL and IMAGE/SQL di er markedly in their implementation of security systems. For external security, database users must be valid users in the account where the root le resides or have access to it through system security measures. For internal security, passwords are assigned to numbered classes. These classes can be associated with read and write permissions to items and data sets.
Methods of Accessing Databases Interactive Access Programmatic Access TurboIMAGE/XL and IMAGE/SQL both o er a variety of tools for accessing databases and both provide techniques for concurrency control, which regulates access by more than one user at a time. TurboIMAGE/XL interactive access is through Query/3000, which lets you nd database entries and report on them using the Query command language.
Locking Mechanisms In TurboIMAGE/XL, you use the DBLOCK intrinsic in certain access modes to provide locking at the database level, the data set level, or the data item level. Locking must be explicitly requested by the user; it is required for concurrent updates. You can request locks conditionally in TurboIMAGE/XL, which means that the call returns if the lock request fails.
4 A Database Example This chapter presents a simple database example that will be used in the remaining chapters of this manual.
The Example Application Suppose a radio station wants to have a database of classical music recordings for use by the program director and announcers. The station needs this information to plan a schedule of broadcasts, to maintain a log of what is played, and to publish a monthly listener guide.
Logical Data Entities To logically order the above information, each attribute must be analyzed in terms of how it will be used. These logical groupings of attributes are called entities. The program director needs to know what albums are available, the selections on each, how long each selection is, and some general information about the style of performance. The announcers need to know which selections are on which albums, how long each selection is, and some information about the composers and artists.
Identifying Relationships Between Entities After subdividing the data by entities, the next step of the design is to identify meaningful relationships between the entities described so far. For each relationship you identify, an attribute or group of attributes must support the relationship. This may mean adding one or more attributes to support the relationship.
From a relationship point of view, this is called referential integrity, where an occurrence of information in one entity is required to support the existence of information in another entity. To demonstrate referential integrity, suppose the station receives a new album containing Beethoven's greatest symphonies as performed by the San Francisco Symphony Orchestra. This album is entered into the Albums entity.
Defining the Data Sets Once you have added the necessary key attributes that support the relationships, you have completed the majority of the work in designing the TurboIMAGE/XL database. Before the database can be completed, paths, a distinction of TurboIMAGE/XL, must be de ned. Paths are xed relationships between entities that provide fast access to data and de ne referential integrity.
Also, Automatic Master key values are added and deleted automatically; their function is to provide fast access paths to data. Manual Master key values and their associated attributes must be added and deleted explicitly, thereby supporting the referential integrity concept discussed earlier and providing fast access to data. The TurboIMAGE/XL database designed so far is shown in Figure 4-1. Figure 4-1.
Defining the Security Scheme The radio station manager wants to have complete access to all the information in the database. Announcers need to read information about Albums, Selections, Composers, and the Log. The program director adds information about new albums and decides what will be played for publication in the monthly listener guide. A security scheme will allow database accessors to do only what has been assigned or expected of them and nothing more.
Defining the TurboIMAGE/XL Database A TurboIMAGE/XL schema is made up of three parts: Password Item Set The Password Part was discussed in the previous section. The Item Part contains the list of attributes, called items, in the database. Only a single de nition is required, even if the item is used in the database in more than one set. Also included in the Item Part is each item's data type and length.
Sets: Name: Albums, Manual(10,20,30/10,20); Entry: AlbumCode (2), AlbumTitle, Medium, AlbumCost, RecordingCo, DateRecorded, MfgCode, Comment; Capacity: 1000; Name: Composers, Manual (10,20,30/10,20); Entry: ComposerName (1), Birth, Death, Birthplace, Comment; Capacity: 1000; Name: Selections-A, Automatic; Entry: SelectionName(2); Capacity: 1000; Name: Selections, Detail (10,20,30/10,20); Entry: AlbumCode (Albums), SelectionName (Selections-A), ComposerName (Composers), Timing, Performers, Comment; Capacity:
The next step is to create a root le for this database with DBSCHEMA as follows: :FILE DBSTEXT = MUSICSCH.SAMPLEDB.SYS :RUN DBSCHEMA.PUB.
Creating the TurboIMAGE/XL The last step to creating the database is to create the sets. This is done with DBUTIL: Database :RUN DBUTIL.PUB.SYS HP30391C.04.09 TurboIMAGE/XL: DBUTIL (C) COPYRIGHT HEWLETT-PACKARD >>CREATE MUSIC Database MUSIC has been CREATED. >>EXIT Your TurboIMAGE/XL database now exists. This chapter covers a very small part of creating databases with the TurboIMAGE/XL Database Management System.
5 IMAGE/SQL Tasks This chapter shows selected IMAGE/SQL tasks. See Chapter 6, \Practicing with IMAGE/SQL Using MusicDBE," for more examples using MusicDBE.
Figure 5-1 shows the prerequisites needed to issue all IMAGE/SQL utility commands. For complete IMAGE/SQL utility command syntax, refer to the HP IMAGE/SQL Administration Guide . Figure 5-1.
This task shows how to set up a new database environment using the IMAGE/SQL utility and ISQL, the interactive component of Hewlett-Packard's SQL products. Setting Up a Database with ISQL The steps to create the database environment are: Running ISQL Creating a DBEnvironment The example chosen to illustrate these steps is a database of information about record albums. This database environment will be used again in Chapter 6, \Practicing with IMAGE/SQL Using MusicDBE.
Leave ISQL by typing the following: isql=>EXIT; 4Return5 If ISQL asks whether or not you want to commit work, you must reply either Y or N and press 4Return5. Entering Y makes the work you have done permanent. Creating a DBEnvironment To create a new DBEnvironment, use the START DBE NEW command at the ISQL prompt. This is explained in the next task.
Configuring a DBEnvironment This task describes how to con gure a DBEnvironment so that you can access your TurboIMAGE/XL database(s) with IMAGE/SQL (see Task Reference). Getting Ready When all of the TurboIMAGE/XL databases to be attached to the DBEnvironment are created by the same user in one group and account, it is convenient to con gure the DBEnvironment in this group and account. If this is not the case, several other issues should be considered.
Performing the Task Log on in the same group and account as the TurboIMAGE/XL database(s) and run ISQL. At the ISQL prompt, enter the START DBE NEW command. For example, to con gure a DBEnvironment named MusicDBE, enter the following: isql=> START DBE 'MUSICDBE' MULTI NEW > MAXIMUM TIMEOUT = 10 SECONDS > DEFAULT TIMEOUT = 5 SECONDS > DBEFILE0 DBEFILE DBEFILE0 (=DBEFile0De nition > WITH PAGES = 500, (= . . > NAME = 'MusicF0', (= . . > LOG DBEFILE DBELOG1 (=DBELog0De nition > WITH PAGES = 500, (= . .
Listing the Newly Created Files When you display the les in your current group, note that there are three newly created les: MUSICDBE, DBEFILE0, and DBELOG1. The rst le 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. DBEFILE0 is a le containing the data for the SYSTEM DBEFileSet, which contains the system catalog. (You'll examine the system catalog later.
The SQLUtil SETDBEMAINT command is used to set a maintenance word. The syntax for this command is as follows: >> SETDBEMAINT DBEnvironment Name: DBEnvironmentName Current Maintenance Word: OldMaintenanceWord New Maintenance Word: NewMaintenanceWord Retype New Maintenance Word: NewMaintenanceWord >>EXIT When no current maintenance word exists, enter a carriage return at the \Current Maintenance Word:" prompt.
This task describes how to attach a TurboIMAGE/XL database. Attaching a TurboIMAGE/XL Database Note If you are attaching a database with the same name but in a di erent group and/or account from a database already attached to the DBEnvironment, you must specify an alternative owner name at attach time (see Task Reference). This is because in a mapped table, by default, the owner name is the database name, and duplicate table names are not allowed within the same database.
:RUN IMAGESQL.PUB.SYS HP36385B X.F0.12 IMAGE/SQL Utility (C) COPYRIGHT HEWLETT-PACKARD COMPANY 1993 THU, APR 8, 1993, 10:30 AM >>DISPLAY OPTIONS Current base : Current SQLDBE : Echo : ON Command Logging : ON Log File : ATCLOG.PUB.TURBONM Because there is no current database or DBEnvironment, issue the SET command as follows: >>SET SQLDBE MUSICDBE.PUB.TURBONM >>SET TURBODB MUSIC.PUB.
Performing the Task Once you are sure the appropriate SET command have been speci ed and that the correct MPE/iX security is in place, issue the IMAGE/SQL utility ATTACH command. >>ATTACH Split 1 compound source fields (ATCWARN 32063). Mapped 15 source table/source field names (ATCWARN 32062). Mapped 1 incompatible source types (ATCWARN 32061). >> Messages issued at attach time inform you if any mapping has been done (see Task Reference).
Data item and data set names Some characters allowed in TurboIMAGE/XL names (speci cally: + 0 * / ? ' % & ) are not valid SQL names. Therefore, whenever IMAGE/SQL utility encounters such a character in a TurboIMAGE/XL name, it converts it to an underscore ( ). Data types TurboIMAGE/XL data types are mapped to SQL data types. When inexact or imprecise mapping is necessary, an `I' appears in the NOTES section of the DISPLAY MAP display.
Detaching a TurboIMAGE/XL Database This task describes how to detach a TurboIMAGE/XL database from a DBEnvironment. Getting Ready Before detaching a TurboIMAGE/XL database, do the following: Obtain exclusive access to the DBEnvironment. Make sure the appropriate SET SQLDBE and SET TURBODB commands have been issued. To check the status of these commands, use the DISPLAY OPTIONS command: >>DISPLAY OPTIONS Current base : Current SQLDBE : Echo : ON Command Logging : ON Log File : ATCLOG.PUB.
Task Reference There are several reasons to detach a TurboIMAGE/XL database from a DBEnvironment: If the database is to be restructured. (This includes restructuring with DBChange and other third-party restructuring tools.) If ALLBASE/SQL is to be migrated back to a version that does not support IMAGE/SQL. If you wish to reset all mapping information to default values.
Displaying IMAGE/SQL Utility Information This task describes how to display IMAGE/SQL information using several IMAGE/SQL utility commands. These commands include: DISPLAY OPTIONS Displays the options in e ect for your current IMAGE/SQL utility session. DISPLAY TURBODB Displays all the TurboIMAGE/XL databases associated with a speci c DBEnvironment. DISPLAY MAP Displays the current data type mapping information for a speci c TurboIMAGE/XL database.
Example 1: Displaying Database Information To see all the TurboIMAGE/XL databases and mapped tables currently associated with MusicDBE, use the DISPLAY TURBODB command. :RUN IMAGESQL.PUB.SYS >>DISPLAY TURBODB TABLES FOR MUSICDBE.PUB.TURBONM DBEnvironment : MUSICDBE.PUB.TURBONM TURBOIMAGE/XL DATABASE ---------------------- OWNER ----- MAPPED TABLE ------------ MUSIC.PUB.
Example 3: Displaying DBEnvironment To see the ALLBASE/SQL DBEnvironment currently associated with MusicDBE, use the DISPLAY SQLDBE command. Information >>DISPLAY SQLDBE ATTACHED BASES : MUSIC.PUB.TURBONM DBENVIRONMENT ------------MUSICDBE.PUB.TURBONM Example 4: Displaying User Information To see the current user information associated with MusicDBE, use the DISPLAY USER command. >>DISPLAY USER ATTACHED BASES DBEnvironment : MUSIC.PUB.TURBONM : MUSICDBE.PUB.
Issuing MPE/iX Commands from the IMAGE/SQL Utility This task describes how to issue MPE/iX commands from the IMAGE/SQL utility. Performing the Task To issue an MPE/iX command, at the IMAGE/SQL utility prompt enter a colon (:) and the name of the MPE/iX command you wish to issue.
Setting IMAGE/SQL File Equations Setting a File Equation for ATCINFO This task describes how to set IMAGE/SQL utility le equations for two les: ATCINFO and ATCLOG. Performing the Task An ATCINFO le equation can only be set before the le is created (before any TurboIMAGE/XL databases are attached to the DBEnvironment; see Task Reference). Only the le name can be speci ed. Other parameters of the FILE command will not be valid at attach time.
Task Reference ATCINFO Reference ATCINFO is a permanent privileged le containing mapping information about data set and eld names, data types, and user security. Its formal le designator is ATCINFO. The default name of the ATCINFO le is DBEnvironment-name CR. One ATCINFO le exists for each DBEnvironment. It is created in the same group and account as the DBEnvironment and is considered part of the DBEnvironment. The ATCINFO le equation can only be used to specify a di erent le name.
Logging IMAGE/SQL Utility Commands This task describes how to log IMAGE/SQL utility commands and how to save frequently used command sequences in di erent log les so they can easily be reissued in batch or interactive mode. Performing the Task By default, IMAGE/SQL utility commands are logged to an ASCII le, ATCLOG, which you can read and edit. You can change log les within IMAGE/SQL utility by issuing a le equation for ATCLOG.
To close this log le and write to a new one, issue another MPE/iX FILE command from within IMAGE/SQL utility. For example: >>:FILE ATCLOG=UPDATYPE;SAVE >> The DISPLAY OPTIONS command con rms that commands are now being logged to UPDATYPE: >>DISPLAY OPTIONS Set Turbodb : Set Sqldbe : Echo : Command Logging : Log File : MUSIC.PUB.TURBONM MUSICDBE.PUB.TURBONM ON ON UPDATYPE.PUB.
Task Reference The section \Setting IMAGE/SQL File Equations" in this chapter contains more information about issuing IMAGE/SQL le equations. The section \Using IMAGE/SQL Utility Command Files" in this chapter contains more information about using the IMAGE/SQL utility XEQ command interactively or in batch to execute IMAGE/SQL utility commands. When an IMAGE/SQL utility session begins, logging is on.
Using IMAGE/SQL Utility Command Files This task describes how to execute an IMAGE/SQL utility command le in interactive or batch mode. Getting Ready An IMAGE/SQL utility command le can be created with a text editor or can be produced as part of the logging process (see Task Reference). If you create or edit this le with an editor, note that it must be kept unnumbered. In the following examples, the le UPDATYPE contains the following commands: SET TURBODB MUSIC.PUB.TURBONM SET SQLDBE MUSICDBE.PUB.
You can also issue XEQ commands in batch mode. The following job stream le contains XEQ commands that execute the commands in the UPDATYPE le. !job JIMAGESQL,USER2/KEVIN.ATC/MGR,PUB/ALL !comment************************************************************** !comment* This job executes an IMAGE/SQL command file. !comment************************************************************** ! !tell USER2.ATC; /-->Start JIMAGESQL for MUSIC ! !run IMAGESQL.PUB.
Accessing TurboIMAGE/XL Data with ISQL This task brie y describes how IMAGE/SQL users access TurboIMAGE/XL data with ISQL. Getting Ready To successfully select TurboIMAGE/XL data with ISQL, IMAGE/SQL users need to know the following: How to use the IMAGE/SQL interface provided for them. The examples in this manual use ISQL, which also requires familiarity with the SQL SELECT statement. The names of the mapped tables and/or views to which they have access (see Task Reference).
Maintaining the This task describes how to maintain the ATCINFO le. ATCINFO File Getting Ready Maintenance for the ATCINFO le (DBEnvironment-name CR) may be necessary in either of the following situations: If a crash occurs while the ATCINFO le is being modi ed. This may be the case if a crash takes place when the IMAGE/SQL administrator is in the midst of an IMAGE/SQL utility command that updates the ATCINFO le. When this occurs, the RECOVER command can be used to reconstruct the ATCINFO le.
6 Practicing with IMAGE/SQL Using MusicDBE IMAGE/SQL software includes scripts and les for creating a sample database and DBEnvironment. This chapter shows you how to execute the script and create the TurboIMAGE/XL MUSIC database and MusicDBE DBEnvironment. From there, you can examine the database and insert and select data. After completing this chapter, you should be more comfortable with the IMAGE/SQL environment.
This section shows you the steps to set up the database and DBEnvironment. Setting Up MUSIC and MusicDBE Before beginning, change into the group and account where you want to create MUSIC and MusicDBE. For clarity, use an empty group, if possible. This chapter uses the group PUB and the account ACCOUNT. A script is provided with the IMAGE/SQL product that simpli es the database creation and purging processes. To execute the script, type: :RUN IMSQL.SAMPLEDB.
Table 6-1. Menu Options Option Description 1 Creates the TurboIMAGE/XL MUSIC database in your current group and account. 2 Displays the MUSIC schema. 3 Uses IMAGE/SQL utility to attach MUSIC to MusicDBE. 4 Displays catalog information about MusicDBE. 5 Purges MUSIC. You must be the Creator or System Manager to use this option. 6 Purges MusicDBE. You must be the DBECreator or System Manager to use this option. 7 Displays the current help screen. 0 Exists IMSQL.
In this section, you will examine the objects that were created within MusicDBE|tables, views, and authority structures. Information about all these objects is in the system catalog, which is automatically created by IMAGE/SQL when the DBEnvironment is created and con gured. Examining The Database Run ISQL, then connect to MusicDBE using this CONNECT command: isql=>CONNECT TO 'musicdbe'; 4Return5 Now examine the system catalog by creating queries on the system views.
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 Albums table: Examining Table Descriptions isql=>INFO music.
An authority structure consists of many elements. Some of these elements are shown below: Group de nitions Table authorization for select, insert, update, and delete operations on tables Column authorization for permission to update speci c columns Examining the Authority Structure Use the following query to examine each authorization group in MusicDBE and their members: isql=>SELECT * FROM SYSTEM.GROUP; 4Return5 d The query result is shown below: a select * from system.
The information above is set up by the ATTACH command. There are, however, three levels of security de ned in the TurboIMAGE/XL database: MGR, DIR, and ANNCR. To use these levels, you must add additional users with the IMAGE/SQL utility as shown below: :RUN IMAGESQL.PUB.SYS 4Return5 HP36385B X.F0.
You can view these changes and additions with the DISPLAY USERS command: >>DISPLAY USERS 4Return5 d c ATTACHED BASES DBEnvironment a : MUSIC.PUB.ACCOUNT : MUSICDBE.PUB.ACCOUNT USER LOGON ---------- DBOPEN MODE USER PASSWORD ----------- ------------- USER CLASS ---------- MGR@ACCOUNT DIR@ACCOUNT ANNCR@ACCOUNT 1 1 1 10 20 30 MGR DIR ANNCR b Figure 6-5. Changes to Levels of Security Re-invoke ISQL and enter the following command to look at the UserID information: isql=>SELECT * FROM system.
The next step is to add data to the database. Because no applications exist for this database, your choice for adding data is with the interactive tools that come with the product. For standard access to TurboIMAGE/XL, your tool is QUERY. For SQL access to IMAGE/SQL, your tool is ISQL. You can add data using the SQL INSERT statement or ISQL LOAD command. Adding Data to the Database In Chapter 4, \A Database Example", the concept of referential integrity was presented.
The COMMIT WORK statement is necessary because SQL processes statements in units known as a transaction. When you issue the rst SQL statement in a sequence, a transaction begins, and that transaction continues until you do a COMMIT WORK or ROLLBACK WORK. The use of transactions guarantees the consistency of data within the DBEnvironment. Using the LOAD Command In addition to the INSERT statement, you can use the LOAD command to add data with ISQL. The LOAD command has two options: EXTERNAL and INTERNAL.
d a select * from music.albums; -----------+----------------------------------------+------+------------+-ALBUMCODE |ALBUMTITLE |MEDIUM|ALBUMCOST |RE -----------+----------------------------------------+------+------------+-2002|Famous Bel Canto Arias |ca | 22.00|dg 2003|Concertos for Diverse Instruments |cd | 19.00|rc 2004|Symphonies and Chamber Works III |cd | 29.00|rc 2005|Nielsen Symphonies 4 & 5 |ca | 13.00|llo 2006|Lenontyne Price: a Christmas Offering |ca | 13.
d c a select * from music.selections; -----------+----------------------------------------+----------------+----ALBUMCODE |SELECTIONNAME |COMPOSERNAME |TIMIN -----------+----------------------------------------+----------------+----2008|Der Saenger |Schubert | 2008|Fruehlingslied |Schubert | 2008|Fruehlingslaube |Schubert | 2008|Vor Meiner Wiege |Schubert | 2008|Drang in die Ferne |Schubert | 2008|Der Musensohn |Schubert | 2008|Viola |Schubert | 2008|Vergissmeinnicht |Schubert | 2010|Klaviersonate Nr.
You can now select and combine various columns of information by joining tables based on common column values. For example, suppose you wanted to see all album titles, selections, composer, and the medium. This data is found in two tables, but can be joined by albumcode, as follows: Selecting Data isql=>SELECT albumtitle, medium, selectionname, composername4Return5 >FROM music.albums, music.selection 4Return5 >WHERE music.albums.albumcode = music.selections.albumcode4Return5 >AND music.albums.
The UPDATE command changes column values. A quick look at some of our albums with the following query shows three albums not on 'cd': Modifying Data isql=>SELECT * FROM music.albums WHERE medium <> 'cd';4Return5 d a select * from music.albums where medium 'cd'; -----------+----------------------------------------+------+------------+-ALBUMCODE |ALBUMTITLE |MEDIUM|ALBUMCOST |RE -----------+----------------------------------------+------+------------+-2002|Famous Bel Canto Arias |ca | 22.
To change the column values of these albums to 'lp' for 'Long Play' records, use the following: isql=>UPDATE music.albums SET medium = 'lp' WHERE medium = 'ca';4Return5 Number of rows processed is 3 isql=>COMMIT WORK; 4Return5 isql=>SELECT * FROM music.albums WHERE medium <>'cd';4Return5 d a select * from music.albums where medium <> 'cd'; ALBUMCODE |ALBUMTITLE |MEDIUM|ALBUMCOST |RE -----------+----------------------------------------+------+------------+-2002|Famous Bel Canto Arias |lp | 22.
Deleting Data Like the UPDATE command, the DELETE command is also very simple to use. The following command deletes entries with the albumcode of 2002: isql=>DELETE FROM music.albums WHERE albumcode = 2002; 4Return5 Conclusion In this chapter, you learned to use these simple SQL statements in an IMAGE/SQL environment: INSERT LOAD SELECT UPDATE DELETE The INSERT, SELECT, UPDATE, and DELETE statements have many other options and o er a great deal of exibility in the IMAGE/SQL environment.
A Tools For IMAGE/SQL This appendix categorizes the available tools and lists some of the tools IMAGE/SQL users can use. Note This appendix is for your convenience only; Hewlett-Packard does not endorse any particular product or company. The tools available to IMAGE/SQL users function primarily in a client/server environment using Microsoft Windows. Most of the tools use HP PC API for ALLBASE/SQL and IMAGE/SQL. The tools fall into three categories, as summarized in Table A-1. Table A-1.
Tools Impromptu by Cognos NewWave Access by Hewlett-Packard Q + E by Pioneer Software Quest by Gupta Executive Forest & Trees by Trinzic Decision Support Information System (EIS) Tools Application Development Tools PowerBuilder by Powersoft SQLWindows by Gupta on the PC Application Development Tools on the Server A-2 Tools For IMAGE/SQL ALLBASE Toolset by Hewlett-Packard Focus by Information Builders JAM by JYACC Powerhouse by Cognos Speedware by Speedware Transact by Hewlett-Packard Uniface by
Glossary ATC The acronym, ATC, stands for ALLBASE/Turbo CONNECT. The terms Turbo CONNECT and ALLBASE/Turbo CONNECT are synonymous with IMAGE/SQL. The acronym, ATC, appears in error messages for IMAGE/SQL (for example, ATCERR or ATCWARN). ATCINFO A permanent privileged le containing mapping information about data types and user security. By default, it is named DBEnvironment-name CR. If you want to set a le equation for this le, you must do so before attaching any TurboIMAGE/XL databases.
Check Constraint An integrity constraint that enforces a condition that must not be false for the columns of a table. Any value you attempt to insert into a column that has a check constraint de ned on it must either satisfy the condition or be NULL. Class Special category of IMAGE/SQL owner that is neither a particular DBEUserID nor a group. You do not explicitly create a class; you create it implicitly by creating objects owned by it. A class does not have members like a group.
Database Design The creation of a speci c arrangement of data in tables or data sets with an appropriate security structure. Data Control Language The set of SQL statements that control access to data. This includes the ADD, REMOVE, GRANT, and REVOKE statements, as well as the statements to create, manage, and drop authorization groups. Also known as DCL. Data Manipulation The process of accessing data within a database. Data Manipulation Language The set of SQL statements that access data.
DBEFileSets Logical grouping of DBEFiles. DBEnvironment A collection of les containing one or more databases. Files include the DBECon le, which holds startup parameters and log le names; DBEFile0, which contains the system catalog; and log les. A DBenvironment may also contain additional DBEFiles for table and index data. The DBEnvironment is the maximum scope of a transaction within IMAGE/SQL. DBEFiles Operating system les that hold DBEnvironment data.
Logging The use of log les to record operations that modify database les. IMAGE/SQL Database A TurboIMAGE/XL database attached to an SQL DBEnvironment. Implicit Locking 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. Integrity Constraint A constraint placed on the columns of a table to ensure that a database contains only valid data.
Logging The use of log les to record operations that modify database les. Logging is of two kinds: nonarchive logging , and archive logging . Both kinds permit you to roll back incomplete transactions following a system failure. This maintains data integrity by backing out changes to the database that were not committed. Only archive logging allows you to roll forward from an earlier version of a DBEnvironment by reapplying all committed transactions up to a speci c recovery time.
Projection Relational operation that extracts a subset of columns from one or more tables. Query Request for information from database tables. A typical example is a SELECT statement. Query Language A set of operators, expressions, and commands that let you manipulate a database. The query language of IMAGE/SQL and ALLBASE/SQL is SQL. Query Result The rows retrieved by a SELECT statement. Query results are also known as result tables.
Repeatable Read (RR) An isolation level that enforces the highest level of separation between the transactions of di erent users. This level guarantees that when you re-read any data you have read previously in the same transaction, the value seen in the second read will be the same as the value seen in the rst read. In practical terms, this means that other users may not update any data you have read at this isolation level until you COMMIT WORK. Result Table See Query Result.
SQLUtil A utility program for database administrators that assists with DBEnvironment maintenance, backup, and recovery. SQLUtil also lets you modify the startup parameters for a DBEnvironment. Structured Query Language A standard query language syntax de ned by ANSI standards in the United States and X/OPEN standards in Europe. The relational database query language used by IMAGE/SQL and ALLBASE/SQL. Subquery A query within another query. An example is a subquery embedded in the predicate of another query.
Transaction A unit of work. Also, a unit of DBEnvironment logging and recovery. A transaction is started with a BEGIN WORK statement and is ended by a COMMIT WORK statement. The BEGIN WORK statement may be implicitly issued by IMAGE/SQL if no other transaction is current when an SQL statement is executed. Unique Constraint An integrity constraint that requires that no two rows in a table have the same values in a speci ed column or columns. View A table derived by placing a \window" over one or more tables.
Index A access interactive, 3-9 programmatic, 3-9 to a database, 2-10 accounts attaching from di erent, 5-5 adding data to the sample database, 6-9 ALLBASE/NET component of IMAGE/SQL, 1-3 ALLBASE/SQL DBA authority, when to grant, 5-5 DBEFILE0, size considerations, 5-5 DBELOG1, size considerations, 5-5 DBEnvironment, setting a maintenance word, 5-8 de ned, 1-3 ATC de ned, Glossary-1 ATCINFO le, 5-19 maintaining, 5-27 reference information for, 5-20 setting a le equation for, 5-19 when purged, 5-14 ATCLOG le
B C D Index-2 base table de ned, Glossary-1 basic mapping, 3-7 batch using IMAGE/SQL utility command les in, 5-25 bene ts of IMAGE/SQL, 1-5 CHAR data types, 3-7 check constraint de ned, Glossary-2 class de ned, Glossary-2 column corresponding TurboIMAGE/XL term, 3-2 de ned, Glossary-2 in a relational database table, 2-2 column authorization de ned, Glossary-2 described, 6-6 column list de ned, Glossary-2 command les, 5-24 COMMIT WORK example, 6-9, 6-10, 6-11 why necessary, 6-10 compound items, 3-7 concu
administrator (DBA), explained, 1-4 controlling access, 2-11 creating, 2-9, 3-5 creating a TurboIMAGE/XL, 4-12 creator, 3-8 de ned, Glossary-2 design, basic explanation, 2-4 design, de ned, Glossary-3 example, 4-1 explained, 2-1, 2-7 in IMAGE/SQL and TurboIMAGE/XL, 3-5 setting up with ISQL, 5-3 data control language de ned, Glossary-3 explained, 2-11 data entities, 4-3 data item corresponding IMAGE/SQL term, 3-2 data manipulation de ned, Glossary-3 example, 2-10 data manipulation language de ned, Glossary-3
explained, 2-7 DBEFile0 DBEFile containing system catalog, 2-8 name and content, 5-7 DBEFileset explained, 6-4 DBEFileSets de ned, Glossary-4 explained, 2-7 DBEnvironment basic explanation, 2-9 corresponding TurboIMAGE/XL term, 3-2 creating, 2-9, 5-4 default les in, 5-7 de ned, Glossary-4 di erences from TurboIMAGE/XL, 3-5 explained, 2-6 starting, 2-9 DBEnvironment-name CR, 5-20 DBEUserID corresponding TurboIMAGE/XL term, 3-2 de ned, Glossary-4 DBLOCK intrinsic, 3-10 DBSCHEMA, 3-5, 3-6, 4-11 DBUTIL, 3-5, 4-
E F G H embedded SQL programming, 3-9 de ned, Glossary-4 entities, 4-3 de ned, Glossary-4 entry corresponding IMAGE/SQL term, 3-2 environment, 4-1 explicit locking de ned, Glossary-4 expression de ned, Glossary-4 le equations examples of setting, 5-19 reference information for, 5-20 setting for ATCINFO, 5-19 setting for ATCLOG, 5-19 les DBECon le, 5-7 DBEFile0, 5-7 default in a DBEnvironment, 5-7 log le, 5-7 FLOAT data types, 3-7 getting ready for detaching a TurboIMAGE/XL database, 5-13 displaying IMAG
I Index-6 IMAGE/SQL and ALLBASE/SQL, 1-5 basic concepts, 2-1 basic structure, 3-4 bene ts, 1-5 command, 5-24 compared with TurboIMAGE/XL, 3-1 components, 1-3 de ning groups, 3-8 de ning views, 3-8 environment, 4-1 introduction, 1-1 locking, 3-10 permissions, 2-11 sample DBEnvironment, 6-1 tasks, 5-1 tools, A-1 IMAGE/SQL utility component of IMAGE/SQL, 1-3 displaying information about, 5-15 example of using in batch mode, 5-25 example of using interactively, 5-24 issuing commands, 5-2 logging commands, 5-2
J joining de ned, Glossary-5 K key de ned, Glossary-5 L M N LOAD description, 6-10 EXTERNAL option, 6-10 INTERNAL option, 6-11 locking de ned, Glossary-5 mechanisms in IMAGE/SQL and TurboIMAGE/XL, 3-10 log le de ned, 2-8 name and content, 5-7 logging de ned, Glossary-5, Glossary-6 explained, 2-8 logging IMAGE/SQL utility commands example of, 5-21 reference information for, 5-23 logical data entities, 4-3 maintaining the ATCINFO le example of, 5-27 getting ready, 5-27 reference information for, 5-27 m
O P Q R Index-8 object de ned, Glossary-6 in an IMAGE/SQL DBEnvironment, 2-6 owner de ned, Glossary-6 password corresponding IMAGE/SQL term, 3-2 part of TurboIMAGE/XL database, 4-9 PC API component of IMAGE/SQL, 1-3 permissions in IMAGE/SQL explained, 2-11 predicate de ned, Glossary-6 preprocessor component of IMAGE/SQL, 1-4 de ned, Glossary-6 prerequisites for issuing IMAGE/SQL utility commands, 5-2 primary key de ned, Glossary-6 PRIV les, 6-3 procedure de ned, Glossary-6 programmatic access to data i
explained, 2-2 relational operations de ned, Glossary-7 joining, 2-2 projection, 2-2 selection, 2-2 relationship de ned, Glossary-7 relation (table) de ned, Glossary-7 in a relational database, 2-2 repeatable read (RR) de ned, Glossary-8 resetting ATCLOG le equations from within IMAGE/SQL utility, 5-19 result table de ned, Glossary-8 REVOKE authorities, 3-8 ROLLBACK WORK, 6-10 root le, 4-11 corresponding IMAGE/SQL term, 3-2 di erences from IMAGE/SQL DBECon le, 3-6 row corresponding TurboIMAGE/XL term, 3-2 d
SMALLINT data types, 3-7 SQL CONNECT, 2-10 de ned, Glossary-8 explained, 2-5 SQLGEN component of IMAGE/SQL, 1-4 de ned, Glossary-8 SQLUtil, 6-3 component of IMAGE/SQL, 1-4 de ned, Glossary-9 using to set DBEnvironment maintenance word, 5-8 standards ANSI and X/OPEN, 2-5 START DBE, 2-9 structured query language de ned, Glossary-9 explained, 2-5 types of statements, 2-5 subquery de ned, Glossary-9 SYSTEM DBEFileSet containing system catalog, 2-8 de ned, Glossary-9 system catalog corresponding TurboIMAGE/XL te
tasks accessing TurboIMAGE/XL data with ISQL, 5-26 adding data to the sample database, 6-9 attaching a TurboIMAGE/XL database, 5-9 committing work, 6-9 con guring a DBEnvironment, 5-5 creating a DBEnvironment, 5-4 deleting data, 6-16 detaching a TurboIMAGE/XL database, 5-13 displaying IMAGE/SQL utility information, 5-15 displaying TurboIMAGE/XL database information, 5-15 examining authority structure, 6-6 examining table description, 6-5 examining the database, 6-4 issuing MPE/iX commands from IMAGE/SQL uti
V Index-12 view de ned, Glossary-10 de ning, 3-8
