Up and Running with ALLBASE/SQL: HP 3000 and HP 9000 Computer Systems

HP Part Number: 36389-90016

Edition: Edition 2

Published: Printed in: U.S.A. June 1992

Notice

Table of Contents

Preface

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
SQLGEN
ALLBASE/4GL
Preprocessors
ALLBASE/Net
ALLBASE/Turbo CONNECT
PC API
Other HP Products
Software from Independent Vendors

What Is a DBEnvironment?

The DBECon File
DBEFiles
DBEFileSets
Databases
Tables and Indexes
System Catalog
Log Files

How Do I Create a DBEnvironment?

How Do I Create a Database?

Statements to Create Databases

How Do I Access a Database?

Queries and Other Data Manipulation

How Do I Control Database Access?

Where Can I Get Help with ALLBASE/SQL?

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
Modifying the Table Design
Table Descriptions

Defining Indexes

Designing Database Security Schemes

Estimating Table and Index Size

Designing Applications

Further Information...

3 Setting Up a Database with ISQL

Running ISQL

Creating a DBEnvironment

DBECon File
DBEFile0
Log File

Creating DBEFileSets

Creating DBEFiles for Table and Index Data

Committing Work

Adding DBEFiles to DBEFileSets

Adding a DBEFile to the SYSTEM DBEFileSet

Creating Tables

Creating the Albums Table
Creating the Titles Table

Entering Data into Tables

Entering Data with the SQL INSERT Statement
Entering Data with the ISQL LOAD Command

Performing Queries

Creating Views

Granting Authorities

Creating an Index

Location of Tables and Indexes

Examining the System Catalog

Updating Statistics in the System Catalog

In Review...

4 Building an ALLBASE/SQL\COBOL Application in MPE/iX

How ALLBASE/SQL Programming Works

Creating an Embedded SQL COBOL Application

Coding the INCLUDE Statement
Coding the Host Variable Declarations
Coding the CONNECT and RELEASE Statements
Coding the SELECT Statement
Coding Statements to Display Data
Using the Preprocessor
Compiling and Linking the Program
Using MPE/iX UDC's for Preprocessing and Compiling
Debugging the Program
Summary of Steps

Adding a Multiple Row SELECT Statement

Adding Additional Host Variable Declarations
Adding the Cursor Declaration
Adding a FETCH and Display Loop
Preprocessing and Compiling the Revised Application

Adding an Explicit Error Checking Routine

Coding the Routine
Creating an Error Condition for the Application
Testing the Error Checking Routine

More Information...

5 Building an ALLBASE/SQL\C Application in HP-UX

How ALLBASE/SQL Programming Works

Creating a C Application in HP-UX

Coding the INCLUDE Statement
Coding the Host Variable Declarations
Coding the CONNECT and RELEASE Statements
Coding the SELECT Statement
Coding Statements to Display Data
Using the Preprocessor
Compiling and Linking the Program
Debugging the Program
Summary of Steps

Adding a Multiple Row SELECT Statement

Adding Additional Host Variable Declarations
Adding the Cursor Declaration
Adding a FETCH and Display Loop
Preprocessing and Compiling the Revised Application

Adding an Explicit Error Checking Routine

Coding the Routine
Creating an Error Condition for the Application
Testing the Error Checking Routine

More Information...

6 Practice with ALLBASE/SQL Using PartsDBE

Setting up PartsDBE

Using SQLSetup
Using Setup Scripts
Looking at the Files Created for PartsDBE

Examining PartsDBE

Examining the Tables and Views
View Definitions
Using the INFO Command
Examining Indexes
Examining the Authority Structure
Column Authorizations

Using the Preprocessors

Sample Application Programs
For HP-UX:
For MPE/iX:

Examining Startup Parameters with SQLUtil

Creating a Schema File with SQLGEN

Purging PartsDBE

7 Comparing ALLBASE/SQL with TurboIMAGE

Basic Structures

Procedures for Starting Up

Use of a Schema
Root File versus DBECon File and System Catalog
Data Files for Data sets versus DBEFiles for Tables
Naming Conventions

Tables and Indexes versus Data Sets

Automatic Masters versus Indexes
Manual Masters versus Hash Structures
Master/Detail versus Referential Integrity
Sort Items versus Indexes

Mapping of Data Types

Basic Mapping
Compound Items
Null Handling

Differences in Security

TurboIMAGE Security
Granting and Revoking Authorities
Defining ALLBASE/SQL Groups
Defining Views in ALLBASE/SQL

Differences in Accessing Databases

Interactive Access
Programmatic Access
4GL
Differences in Concurrency Control
Locking Mechanisms

Sample Mapping of a TurboIMAGE Database to an ALLBASE/SQL DBEnvironment

Using ALLBASE/Turbo CONNECT

8 Glossary of Terms in ALLBASE/SQL

Ad Hoc Query
Archive Logging
Attribute
Authority
Authorization Group
Base Table
Check Constraint
Class
Clustering Index
Column
Column Authorization
Column List
Concurrency
Constraint
Cursor Stability (CS)
Data Analysis
Database
Database Administrator (DBA)
Database Design
Data Control Language
Data Definition
Data Definition Language
Data Manipulation
Data Manipulation Language
Data Type
DBA Authority
DBCore
DBECon File
DBECreator
DBEFile
DBEFileSet
DBEnvironment
DBEUserID
Embedded SQL Program
Entity
Explicit Locking
Expression
Foreign Key
Group
Hash Structure
Host Variable
Implicit Locking
Index
Index Scan
Integrity Constraint
ISQL
Isolation Level
Join
Key
Key Column
Key Value
Locking
Logging
Message Catalog
Message File
Modified Source File
Module
Native Language
Nonarchive Logging
Normalization
Object
Optimizer
Owner
Predicate
Preprocessor
Primary Key
Procedure
Projection
Query
Query Language
Query Result
Read Committed (RC)
Read Uncommitted (RU)
Referential Constraint
Relation
Relational Operations
Relationship
Repeatable Read (RR)
Result Table
Rollback Recovery
Rollforward Recovery
Row
Rule
Run Authority
Schema
Section
Serial Scan
Special Authority
SQL
SQLCore
SQLGEN
SQLMigrate
SQLUtil
Structured Query Language
Subquery
SYSTEM
System Catalog
System Table
System View
Table
Table Authority
Transaction
Unique Constraint
Unique Index
Validation
View

Index

List of Figures

1-1 Relational Operations
1-2 ALLBASE/SQL DBEnvironment
3-1 ISQL Banner
3-2 System.Table Display
6-1 SQLSetup Menu
6-2 Information on Tables and Views
6-3 View Definitions in the System Catalog
6-4 Output of the INFO Command
6-5 System Catalog Information on Indexes
6-6 Groups in the System Catalog
6-7 Table Authorities in the System Catalog
6-8 Column Authorities in the System Catalog
6-9 SQLUtil Banner
6-10 SQLGEN Banner
7-1 TurboIMAGE Architecture
7-2 ALLBASE/SQL Architecture
7-3 Using ALLBASE/Turbo CONNECT

List of Tables

1-1 Employee Accounts
1-2 Employees Table
1-3 Telephone Table
1-4 Accounts Table
1-5 Information about ALLBASE/SQL Tasks
2-1 Attributes for Four Entities
2-2 Columns and Keys for Four Tables
2-3 Albums Table
2-4 Titles Table
2-5 Composers Table
2-6 Log Table
7-1 Mapping of TurboIMAGE and ALLBASE/SQL Data Types

Up and Running with ALLBASE/SQL: HP 3000 and HP 9000 Computer Systems

Technical documentation

» Table of Contents

» Index