HP 3000 Manuals HP 3000 Manuals
Table of Contents
ALLBASE/SQL Reference Manual
ALLBASE/SQL Reference Manual : COPYRIGHT NOTICE
Ch 1. Introduction
ALLBASE/SQL Components
Utility Programs
ALLBASE/SQL Databases
Logical Concepts
Physical Concepts
ALLBASE/SQL Data Access
Using Queries
ALLBASE/SQL Objects
ALLBASE/SQL Users
SQL Language Structure
Using Comments within SQL Statements
SQL Statement Categories
Error Conditions in ALLBASE/SQL
Severity of Errors
Atomicity of Error Checking
Setting the Atomicity to the Row Level
Deferring Error Checking beyond the Statement Level
Additional Information about Errors
Native Language Support
Ch 2. Using ALLBASE/SQL
Creating DBEnvironments
Specifying a Native Language Parameter
Initial Privileges
Starting and Terminating a DBE Session
Sessions with Autostart
Sessions without Autostart
Terminating DBE Sessions
Creating Physical Storage
Defining How Data is Stored and Retrieved
Creating a Table
Choosing the Locking Mode and Default Access Authorities
Naming the Table and Columns
Defining the Columns
Specifying Data Types
Specifying Column Options
Specifying a DBEFileSet
Specifying Native Language Tables and Columns
Creating a View
Creating Indexes
Specifying Integrity Constraints
Creating Procedures
Creating Rules
Understanding Data Access Paths
Serial Access
Indexed Access
Hashed Access
Differences between Hashed and Indexed Access
When to Use a Hash Structure
TID Access
Controlling Database Access
Authorities
Obtaining Authorization
DBA Authority
Grants
Grantable Privileges
Ownership
Default Owner Rules
Ownership Privileges
Authorization Groups
Classes
Differences between Groups and Classes
Manipulating Data
Inserting Data
Updating Data
Deleting Data
Managing Transactions
Objectives of Transaction Management
Ensuring Logical Data Integrity
Maximizing Concurrency
Facilitating Recovery
Starting Transactions
Ending Transactions
Using COMMIT WORK
Using ROLLBACK WORK
Using SAVEPOINT
Scoping of Transaction and Session Attributes
Transaction Limits and Timeouts
Monitoring Transactions
Tips on Transaction Management
Auditing DBEnvironments
Partitions in Audit DBEnvironments
Using Wrapper DBEnvironments
Using SQLAudit
Application Programming
Preprocessor
Authorization
DBEnvironment Changes
Host Variables
Multiple-Row Manipulations
Using Multiple Connections and Transactions with Timeouts
Connecting to DBEnvironments
Setting the Current Connection
Setting Timeout Values
Setting the Transaction Mode
Using Single-Transaction Mode
Using Multi-Transaction Mode with Multiple DBEnvironments
Using Multi-Transaction Mode with One DBEnvironment
Disconnecting from DBEnvironments
Administering a Database
Understanding the System Catalog
Ch 3. SQL Queries
Using the SELECT Statement
Simple Queries
Complex Queries
UNION Queries
Using Character Constants with UNION
Subqueries
Special Predicates
Quantified Predicate
Using the ANY or SOME Quantifier with a Value List
Using ANY or SOME with a Subquery
Using the ALL Quantifier
IN Predicate
Using the IN Predicate with a Value List
Using the IN Predicate with a Subquery
EXISTS Predicate
Correlated Versus Noncorrelated Subqueries
Outer Joins
Outer Joins Using Explicit JOIN syntax
Outer Joins Using the UNION Operator
Symmetric Outer Join Using the UNION Operator
Using GENPLAN to Display the Access Plan
Generating a Plan
Displaying a Query Access Plan
Interpreting a Display
Updatability of Queries
Ch 4. Constraints, Procedures, and Rules
Using Integrity Constraints
Unique Constraints
Referential Constraints
The Referenced Table
The Referencing Table
Check Constraints
Examples of Integrity Constraints
Inserting Rows in Tables Having Constraints
How Constraints are Enforced
Using Procedures
Understanding Procedures
Creating Procedures
Executing Procedures
Procedures and Transaction Management
Using SQL Statements in Procedures
Specifying Parameters
Using Local Variables in Procedures
Using Built-in Variables in Procedures
Queries inside Procedures
Using a Simple SELECT
Using a Select Cursor
Using a Procedure Cursor in ISQL
Error Handling in Procedures Not Invoked by Rules
Using RAISE ERROR in Procedures
Recommended Coding Practices for Procedures
Using Rules
Understanding Rules
Creating Rules
Techniques for Using Procedures with Rules
Using a Chained Set of Procedures and Rules
Executing the Chained Set of Procedures and Rules
Using a Single Procedure with Cursors
Error Handling in Procedures Invoked by Rules
Using RAISE ERROR in Procedures Invoked by Rules
Enabling and Disabling Rules
Special Considerations for Procedures Invoked by Rules
Transaction Handling in Rules
Effects of Rule Chaining
Invalidation of Sections
Changing Session Attributes
Performance Considerations
Differences between Rules and Integrity Constraints
Ch 5. Concurrency Control through Locks and Isolation Levels
Defining Transactions
Understanding ALLBASE/SQL Data Access
Use of Locking by Transactions
Basics of Locking
Locks and Queries
Locks on System Catalog Pages
Locks on Index Pages
Costs of Locking
Defining Isolation Levels between Transactions
Repeatable Read (RR)
Cursor Stability (CS)
Read Committed (RC)
Read Uncommitted (RU)
Details of Locking
Lock Granularities
Types of Locks
Lock Compatibility
Weak Locks
What Determines Lock Types
Type of SQL Statement
Locking Structure Implicit at CREATE TABLE Time
Use of the LOCK TABLE Statement
Choice of a Scan Type
Choice of Isolation Level
Neighbor Locking
Updatability of Cursors or Views
Use of Sorting
Scope and Duration of Locks
Examples of Obtaining and Releasing Locks
Simple Example of Concurrency Control through Locking
Sample Transactions Using Isolation Levels
Example of Repeatable Read
Example of Cursor Stability
Example of Read Committed
Example of Read Uncommitted
Resolving Conflicts among Concurrent Transactions
Lock Waits
Deadlocks
Table Type and Deadlock
Table Size and Deadlock
Avoiding Deadlock
Avoiding Deadlock by Using the Same Order of Execution
Avoiding Deadlock by Reading for Update
Avoiding Deadlock by Using the LOCK TABLE Statement
Avoiding Deadlock on Single Tables by Using PUBLICREAD and PRIVATE
Avoiding Deadlock by Using the KEEP CURSOR Option
Undetectable Deadlock
Monitoring Locking with SQLMON
MONITOR Authority
Monitoring Tasks
Ch 6. Names
Basic Names
Native Language Object Names
DBEUserIDs
Owner Names
Authorization Names
Compound Identifiers
Host Variable Names
Local Variable Names
Parameter Names
DBEnvironment and DBECon File Names
DBEFile and Log File Identifiers
TempSpace Names
Special Names
Ch 7. Data Types
Type Specifications
Value Comparisons
Overflow and Truncation
Underflow
Type Conversion
Null Values
Decimal Operations
Date/Time Operations
Examples
Use of Date/Time Data Types in Arithmetic Expressions
Use of Date/Time Data Types in Predicates
Date/Time Data Types and Aggregate Functions
Binary Operations
Long Operations
Defining LONG Column Data with CREATE TABLE or ALTER TABLE
Defining Input and Output with the LONG Column I/O String
Using INSERT with LONG Column Data
Using INSERT with No Specified File Options
Using INSERT with the Overwrite Option
Using INSERT with the Append Option
Using INSERT with the Wildcard Option
Using INSERT with Heap Space Input and Output
Using SELECT with LONG Column Data
Using UPDATE with LONG Column Data
Examples
Native Language Data
Ch 8. Expressions
Expression
Add Months Function
Aggregate Functions
CAST Function
Constant
Current Functions
Date/Time Functions
Long Column Functions
String Functions
TID Function
Ch 9. Search Conditions
Search Condition
BETWEEN Predicate
Comparison Predicate
EXISTS Predicate
IN Predicate
LIKE Predicate
NULL Predicate
Quantified Predicate
Ch 10. SQL Statements
SQL Statement Summary
ADD DBEFILE
ADD TO GROUP
ADVANCE
ALTER DBEFILE
ALTER TABLE
Assignment (=)
BEGIN
BEGIN ARCHIVE
BEGIN DECLARE SECTION
BEGIN WORK
CHECKPOINT
CLOSE
COMMIT ARCHIVE
COMMIT WORK
CONNECT
CREATE DBEFILE
CREATE DBEFILESET
CREATE GROUP
CREATE INDEX
CREATE PARTITION
CREATE PROCEDURE
CREATE RULE
CREATE SCHEMA
CREATE TABLE
CREATE TEMPSPACE
CREATE VIEW
DECLARE CURSOR
DECLARE Variable
DELETE
DELETE WHERE CURRENT
DESCRIBE
DISABLE AUDIT LOGGING
DISABLE RULES
DISCONNECT
DROP DBEFILE
DROP DBEFILESET
DROP GROUP
DROP INDEX
DROP MODULE
DROP PARTITION
DROP PROCEDURE
DROP RULE
DROP TABLE
DROP TEMPSPACE
DROP VIEW
ENABLE AUDIT LOGGING
ENABLE RULES
END DECLARE SECTION
EXECUTE
EXECUTE IMMEDIATE
EXECUTE PROCEDURE
FETCH
GENPLAN
GOTO
GRANT
IF
INCLUDE
INSERT
Labeled Statement
LOCK TABLE
LOG COMMENT
OPEN
PREPARE
PRINT
RAISE ERROR
REFETCH
RELEASE
REMOVE DBEFILE
REMOVE FROM GROUP
RENAME COLUMN
RENAME TABLE
RESET
RETURN
REVOKE
ROLLBACK WORK
SAVEPOINT
SELECT
SET CONNECTION
SET CONSTRAINTS
SET DEFAULT DBEFILESET
SET DML ATOMICITY
SET MULTITRANSACTION
SETOPT
SET PRINTRULES
SET SESSION
SET TRANSACTION
SET USER TIMEOUT
SQLEXPLAIN
START DBE
START DBE NEW
START DBE NEWLOG
STOP DBE
TERMINATE QUERY
TERMINATE TRANSACTION
TERMINATE USER
TRANSFER OWNERSHIP
TRUNCATE TABLE
UPDATE
UPDATE STATISTICS
UPDATE WHERE CURRENT
VALIDATE
WHENEVER
WHILE
Ap A. SQL Syntax Summary
SQL Syntax Summary
ADD DBEFILE
ADD TO GROUP
ADVANCE
ALTER DBEFILE
ALTER TABLE
AddColumnSpecification
AddConstraintSpecification
DropConstraintSpecification
SetTypeSpecification
SetPartitionSpecification
Assignment (=)
BEGIN
BEGIN ARCHIVE
BEGIN DECLARE SECTION
BEGIN WORK
CHECKPOINT
CLOSE
COMMIT ARCHIVE
COMMIT WORK
CONNECT
CREATE DBEFILE
CREATE DBEFILESET
CREATE GROUP
CREATE INDEX
CREATE PARTITION
CREATE PROCEDURE
ParameterDeclaration
ResultDeclaration
CREATE RULE
CREATE SCHEMA
CREATE TABLE
Column Definition
Unique Constraint (Table Level)
Referential Constraint (Table Level)
Check Constraint (Table Level)
CREATE TEMPSPACE
CREATE VIEW
DECLARE CURSOR
DECLARE Variable
DELETE
DELETE WHERE CURRENT
SQL Syntax Summary (Continued)
DESCRIBE
DISABLE AUDIT LOGGING
DISABLE RULES
DISCONNECT
DROP DBEFILE
DROP DBEFILESET
DROP GROUP
DROP INDEX
DROP MODULE
DROP PARTITION
DROP PROCEDURE
DROP RULE
DROP TABLE
DROP TEMPSPACE
DROP VIEW
ENABLE AUDIT LOGGING
ENABLE RULES
END DECLARE SECTION
EXECUTE
HostVariableSpecification
EXECUTE IMMEDIATE
EXECUTE PROCEDURE
ActualParameter
FETCH
BULK HostVariableSpecification
Non-BULK HostVariableSpecification
GENPLAN
GOTO
GRANT
Grant RUN or EXECUTE Authority
Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority
Grant DBEFileSet Authority
IF
INCLUDE
INSERT - 1
SingleRowValues
LongColumnIOString
BulkValues
Dynamic Parameter Substitution
INSERT - 2
Labeled Statement
LOCK TABLE
LOG COMMENT
OPEN
PREPARE
PRINT
RAISE ERROR
REFETCH
RELEASE
REMOVE DBEFILE
REMOVE FROM GROUP
RENAME COLUMN
RENAME TABLE
RESET
RETURN
SQL Syntax Summary (Cont.)
REVOKE
Revoke Table or View Authority
Revoke RUN or EXECUTE or Authority
Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority
SQL Syntax--Revoke DBEFileSet Authority
ROLLBACK WORK
SAVEPOINT
SELECT
Select Statement Level
Subquery Level
Query Expression Level
Query Block Level
SelectList
HostVariableSpecification--With BULK Option
HostVariableSpecification--Without BULK Option
FromSpec
TableSpec
SET CONNECTION
SET CONSTRAINTS
SET DEFAULT DBEFILESET
SET DML ATOMICITY
SET MULTITRANSACTION
SETOPT
Scan Access
Join Algorithm
SET PRINTRULES
SET SESSION
SET TRANSACTION
SET USER TIMEOUT
SQLEXPLAIN
START DBE
START DBE NEW
DBEFile0Definition
DBELogDefinition
START DBE NEWLOG
NewLogDefinition
SQL Syntax Summary (Continued)
STOP DBE
TERMINATE QUERY
TERMINATE TRANSACTION
TERMINATE USER
TRANSFER OWNERSHIP
TRUNCATE TABLE
UPDATE
LongColumnIOString
UPDATE STATISTICS
UPDATE WHERE CURRENT
LongColumnIOString
VALIDATE
WHENEVER
WHILE
Ap B. ISQL Syntax Summary
ISQL Syntax Summary
CHANGE
DO
EDIT
END
ERASE
EXIT
EXTRACT
HELP
HOLD
INFO
INPUT
INSTALL
LIST FILE
LIST HISTORY
LIST INSTALL
LIST SET
LOAD
ExternalInputSpec
RECALL
REDO
Subcommands
RENAME
SELECTSTATEMENT
SET
Options and Values
SQLGEN
SQLUTIL
START
STORE
SYSTEM
UNLOAD
ExternalOutputSpec
Ap C. Sample DBEnvironment
Setting Up PartsDBE
Using SQLSetup
Creating PartsDBE
Using CREASQL
Listings of ISQL Command Files
STARTDBE Command File
CREATABS Command File
LOADTABS Command File
CREAINDEX Command File
CREASEC Command File
Data in the Sample DBEnvironment
ManufDB.SupplyBatches Table
ManufDB.TestData Table
PurchDB.Inventory Table
PurchDB.OrderItems Table
PurchDB.Orders Table
PurchDB.Parts Table
PurchDB.Reports Table
PurchDB.SupplyPrice Table
PurchDB.Vendors Table
RecDB.Clubs Table
RecDB.Events Table
RecDB.Members Table
Sample Program Files
Ap D. Standards Flagging Support
Introduction
Non-standard Statements and Extensions
Non-Standard Data Types
Non-Standard Expression Extensions
Non-Standard Syntax Rules
INDEX
Index