HPlogo

ALLBASE/SQL Database Administration Guide: HP 3000 MPE/iX Computer Systems

» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

 » Index

HP Part Number: 36216-90214

Edition: Seventh Edition

Published: Printed in: U.S.A August 1997


Table of Contents

Preface
1 DBA Tasks and Tools
Tasks for the DBA
Creating Logical and Physical Objects
Starting and Stopping DBE Sessions
Establishing Multiple DBEnvironment Connections
Managing ALLBASE/SQL Logs
Maintaining Buffers for Data and Log Pages
Managing Transactions and Locks
Setting Parameters in the DBECon File
Monitoring the System Catalog
Managing Nonstop Production Systems
Backing Up and Restoring DBEnvironments
Migrating DBEnvironments Between Releases of ALLBASE/SQL
Tools for the DBA
Using ISQL
Using SQLUtil
Using SQLGEN
Using SQLMigrate
Using SQLINSTL
Using SQLMON
Using SQLVer
Using SQLAudit
Checking the Exit Status
System Administration for ALLBASE/SQL
Shared Memory Usage
Native Language Support
Network Administration
2 Logical Design
Identifying the Data for Tables
Normalizing the Data
First Normal Form
Second Normal Form
Third Normal Form
Arranging Data in Tables
Defining Tables
Defining Columns
Designing Views
Designing Indexes
Determining Index Keys
Determining Index Type
Designing Hash Structures
Understanding the Hash Function
Choosing Hash Keys
Choosing the Number of Primary Pages
Designing Integrity Constraints
Defining Security Levels
Authority Types
Special Authorities
Owner Authority
Table and View Authorities
RUN Authority
EXECUTE Authority
Space Authorities
Defining Authorization Groups
Defining Classes
Defining the DBEnvironment Scope
3 Physical Design
Calculating Storage for Database Objects
Understanding DBEFile Characteristics
Calculating Storage for Tables
Calculating Storage for Indexes
Arranging Tables and Indexes in DBEFileSets
Calculating Storage for Hash Structures
Calculating Storage for Integrity Constraints
Calculating Storage for the System Catalog
Storage of Definitions for Newly Created Objects
Number of Columns in Tables
Definitions of Rules, Procedures, Constraints, and Views
Storage of Sections
Calculating Space Needed for Sections
Monitoring System Catalog Size
Calculating Storage for Logging
Understanding Log File Characteristics
Log Records and Transactions
Using Archive or Nonarchive Logs
Using Single or Dual Logging
Using Multiple Log Files
Sample Log Configuration
Disk Space for the Log
Calculating Temporary Disk Space
Controlling the Use of Temporary Space
Estimating Shared Memory Requirements
Estimating Runtime Control Block Buffer Pages
Estimating Data Buffer Pages
Setting the Memory Resident Buffer Flag
Estimating Log Buffer Pages
Estimating the Number of Transactions
Implementing the Design
4 DBEnvironment Configuration and Security
Required MPE/iX Capabilities
Using START DBE NEW
Supplying Startup Parameters with START DBE NEW
Log Files
Specifying a Native Language Parameter
Looking at the DBEnvironment Elements
Creating Audit DBEnvironments
Example of Setting Up an Audit DBEnvironment
Defining Additional Audit DBEnvironment Log Files
Disabling Audit Logging
Creating DBEFileSets and DBEFiles
Creating DBEFileSets
Assigning Default DBEFileSets
Creating DBEFiles
Adding DBEFiles to DBEFileSets
Allocating Expandable DBEFile Space
Allocating Expandable DBEFile Space in the SYSTEM DBEFileSet
Partial DBEFile Expansion
Obtaining Information about Expandable DBEFiles
Creating the DBEnvironment Security Scheme
Creating Authorization Groups
Managing Authorization Groups
Using DBA Authority
The DBECreator
Granting Authorities to PUBLIC
Granting/Revoking CONNECT Authority
Granting/Revoking RESOURCE Authority
Granting/Revoking RUN Authority
Granting/Revoking EXECUTE Authority
Granting/Revoking SECTIONSPACE or TABLESPACE Authority
Verification of Authority
Managing DBEnvironment Sessions
Using Autostart
Using START DBE
Connecting to a DBE
Terminating a DBE Session
Setting Timeout Values
Remote Database Access
5 Database Creation and Security
Creating Tables
Table Type
Owner and Table Name
Column Definition
Constraint Definitions
DBEFileSet Name
Examining Table Attributes
Defining Partitions and Tables
Creating Views
Creating Hash Structures
Creating Indexes
Creating Procedures and Rules
Creating a Procedure
Creating a Rule
Creating the Database Security Scheme
Controlling Table Access with Authorities
Controlling Table Access with Views
Using the GRANT OPTION Clause
Creating a Database Schema
Loading Tables
Loading from an External File
Loading from an Internal File
Loading Tables with Constraints on Them
Loading Tables with Rules Built on Them
Using Command Files for Loading
6 Backup and Recovery
Choosing an Approach to Backup and Recovery
Choosing Nonarchive Logging
Choosing Archive Logging
Understanding Log File Types
Understanding the LOG FULL Condition
Using Single or Dual Logs
Using Multiple Log Files
Log Names and Numbers
Using Nonarchive Logs
Using Archive Logs
Choosing Full or Partial Backup Procedures
Choosing Full or Partial Recovery Procedures
Backup and Recovery Procedures for Nonarchive Logging
Nonarchive Backup Procedures
Adding Files to the Nonarchive Log
Nonarchive Full Recovery Procedures
Nonarchive Partial Recovery Procedures
Backup and Recovery Procedures for Archive Logging
Online Backup Procedures in Archive Mode
Static Full or Partial Backup Procedures in Archive Mode
Adding Files to the Archive Log
Archive Recovery Procedures
ALLBASE/SQL Interface to True Online Backup
Managing Log Files
Monitoring the Log with SHOWLOG
Adding Log Files with ADDLOG
Storing Log Files with STORELOG
Rescuing Log Files with RESCUELOG
Restoring Log Files with RESTORELOG
Purging Log Files with PURGELOG
Moving Log Files with MOVELOG
Starting a New Log
Monitoring the Log with SQLMON
Setting up a Wrapper DBEnvironment
Selecting Valid Log Files with SHOWLOG
Creating a DBEnvironment
Wrapping the DBEnvironment Around the Log Files
Example of Setting Up a Wrapper DBE
7 Maintenance
Using Simple and Complex Maintenance Operations
Maintaining the DBEnvironment
Adjusting Startup Values
Determining Behavior of Rules in a DBEnvironment Session
Updating System Catalog Statistics
Changing System Table Lock Types
Managing DBEFiles and DBEFileSets
Adding a New DBEFile
Changing DBEFile Type
Dropping a DBEFile
Maintaining Tables
Changing a Table's Locking Behavior
Dropping Tables
Adding Columns
Deleting Columns
Removing Rows from a Table
Merging Tables
Dividing Tables
Renaming Tables or Columns
Dropping and Recreating Hash Structures
Maintaining Indexes
Monitoring Index Space
Monitoring the Cluster Count
Dropping and Recreating an Index
Reloading a Table to Improve Index Performance
Altering the Index Key
Maintaining Constraints
Adding Constraints
Dropping Constraints
Maintaining Rules and Procedures
Granting and Revoking Procedure Authorities
Examining the Inventory of Rules and Procedures
Dropping and Recreating Rules and Procedures
Validating Procedure Sections
Maintaining Sets of Interrelated Objects
Maintaining Applications
Invalidation and Revalidation of Sections
Monitoring File Space for Modules and Sections
Module Related Authorities
Sharing Modules Between DBEnvironments
Dropping Modules
Maintaining a Nonstop Production System
DBEFiles in Different Groups and Volumes
Detaching and Attaching Database Files
Using a Host Variable with the CHECKPOINT Statement
Using Console Message Files
Making Changes to a New Log File
Checking the System Catalog
Maintaining Security
Disabling Data Definition
Judging Maintenance Expenses
Cleaning Up after Abnormal Termination
8  System Catalog
Views owned by SYSTEM and CATALOG
Summary of System Catalog Views by Function
Using the System Catalog
System Catalog Views
Using UPDATE STATISTICS on System Views
Locking of the System Catalog
Storedsect.System
Storedsect.DBEFileSetName
System.Account
Example
System.Call
Example
System.CheckDef
Example
System.Colauth
Example
System.Coldefault
Example
System.Column
Example
System.Constraint
Example
System.Constraintcol
Example
System.ConstraintIndex
Example
System.Counter
Example
System.DBEFile
Example
System.DBEFileSet
Example
System.Group
Example
System.Hash
Example
System.Imagekey
Example
System.Index
Example
System.Installauth
Example
System.Modauth
Example
System.Paramdefault
Example
System.Parameter
Example
System.Partition
System.Plan
Example
System.Procauth
Example
System.Procedure
Example
System.ProcedureDef
Example
System.ProcResult
Example
System.Rule
Example
System.RuleColumn
Example
System.RuleDef
Example
System.Section
Example
System.Setoptinfo
Example
System.Spaceauth
Example
System.Spacedefault
Example
System.Specauth
Example
System.Tabauth
Example
System.Table
Example
System.TempSpace
Example
System.TPIndex
System.Transaction
Example
System.User
Example
System.ViewDef
Example
A ALLBASE/SQL Limits
B Authorities Required by ALLBASE/SQL Statements
C SQL Syntax 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 - 1
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
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
D ISQL Syntax Summary
CHANGE
DO
EDIT
END
ERASE
EXIT
EXTRACT
HELP
HOLD
INFO
INPUT
INSTALL
LIST FILE
LIST HISTORY
LIST INSTALL
LIST SET
LOAD
RECALL
REDO
RENAME
SELECTSTATEMENT
SET
SQLGEN
SQLUTIL
START
STORE
SYSTEM
UNLOAD
E Locks Held on the System Catalog by SQL Statements
F SQLUtil
ADDLOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
ALTDBE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
ATTACHFILE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
CHANGELOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
DETACHFILE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
ENDRECOVERY
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
EXIT
Scope
SQLUtil Syntax
Description
Example
HELP
Scope
SQLUtil Syntax
Parameters
Description
Example
MOVEFILE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
MOVELOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
PURGEALL
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
PURGEDBE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
PURGEFILE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
PURGELOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
QUIT
Scope
SQLUtil Syntax
Description
Example
RECOVERLOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
RESCUELOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
RESTORE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
RESTORELOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Examples
RESTORE PARTIAL
Scope
SQLUtil Syntax - RESTORE PARTIAL
Parameters
Description
Authorization
Example
SET
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
SETDBEMAINT
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
SETUPRECOVERY
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
SETUPRECOVERY PARTIAL
Scope
SQLUtil Syntax - SETUPRECOVERY PARTIAL
Parameters
Description
Authorization
Example of Partial Recovery
SHOWACCESS
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
SHOWDBE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
SHOWDBE-ALL
Scope
Syntax
Description
Authorization
Example
SHOWDBE-EXIT
Scope
Syntax
Description
Authorization
Example
SHOWDBE-HELP
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
SHOWDBE-LANG
Scope
SQLUtil Syntax
Description
Authorization
Example
SHOWDBE-MAINT
Scope
SQLUtil Syntax
Description
Authorization
Example
SHOWDBE-QUIT
Scope
Syntax
Description
Authorization
Example
SHOWDBE-STARTPARMS
Scope
SQLUtil Syntax
Description
Authorization
Example
SHOWLOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Examples
SHOWSET
Scope
SQLUtil Syntax
Parameters
Description
Example
STORE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
STOREINFO
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
STORELOG
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
STOREONLINE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
STOREONLINE PARTIAL
Scope
SQLUtil Syntax - STOREONLINE PARTIAL
Parameters
Description
Authorization
Example
STORE PARTIAL
Scope
SQLUtil Syntax - STORE PARTIAL
Parameters
Description
Authorization
Example
SYSTEM
Scope
SQLUtil Syntax
Parameters
Description
Example
WRAPDBE
Scope
SQLUtil Syntax
Parameters
Description
Authorization
Example
G SQLGEN
EDITOR
Scope
SQLGEN Syntax
Description
Example
EXIT
Scope
SQLGEN Syntax
Description
Example
GENERATE ALL
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE DBE
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE DBEFILES
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE DEFAULTSPACE
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE GROUPS
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE INDEXES
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE INSTALLAUTH
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE LOAD
Scope
SQLGEN Syntax
Parameters
Description
Example
UNLOAD Schema File Produced
LOAD Schema File Produced
GENERATE MODAUTH
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE MODOPTINFO
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE PARTITION
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE PROCAUTH
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE PROCEDURES
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE PROCOPTINFO
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE RULES
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE SPACEAUTH
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE SPECAUTH
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE STATISTICS
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE TABAUTH
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE TABLES
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE TEMPSPACES
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
GENERATE VIEWAUTH
Scope
SQLGEN Syntax
Parameters
Example
Schema File Produced
GENERATE VIEWS
Scope
SQLGEN Syntax
Parameters
Description
Example
Schema File Produced
HELP
Scope
SQLGEN Syntax
Parameters
Description
Example
RELEASE
Scope
SQLGEN Syntax
Description
Example
SET ECHO_ALL OFF
Scope
SQLGEN Syntax
Description
Example
SET ECHO_ALL ON
Scope
SQLGEN Syntax
Description
Example
SET EDITOR
Scope
SQLGEN Syntax
Description
Example 1
Example 2
SET EXIT_ON_DBERR OFF
Scope
SQLGEN Syntax
Description
Example
SET EXIT_ON_DBERR ON
Scope
SQLGEN Syntax
Description
Example
SET SCHEMA
Scope
SQLGEN Syntax
Parameters
Description
Example
STARTDBE
Scope
SQLGEN Syntax
Parameters
Description
Example
:
Scope
SQLGEN Syntax
Parameters
Description
Example
H SQLMigrate
ADD DBEFILE
Scope
SQLMigrate Syntax
Parameters
Description
Example
CREATE DBEFILE
Scope
SQLMigrate Syntax
Parameters
Description
Example
EXIT
Scope
SQLMigrate Syntax
Description
Example
HELP
Scope
SQLMigrate Syntax
Description
Example
MIGRATE
Scope
SQLMigrate Syntax
Parameters
Description
Authorization
Example 1
Example 2
PREVIEW
Scope
SQLMigrate Syntax
Parameters
Description
Authorization
Example 1
Example 2
QUIT
Scope
SQLMigrate Syntax
Description
Example
REPAIR
Scope
SQLMigrate Syntax
Parameters
Description
Example
SET
Scope
SQLMigrate Syntax
Parameters
Description
Example 1
Example 2
SHOW 'DBEnvironmentName' VERSION
Scope
SQLMigrate Syntax
Parameters
Example
SHOW VERSIONS
Scope
SQLMigrate Syntax
Description
Example
:
Scope
SQLMigrate Syntax
Parameters
Description
Example
I SQLAudit
AUDIT
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
EDITOR
Scope
SQLAudit Syntax
Description
Authorization
Example
EXIT
Scope
SQLAudit Syntax
Description
Authorization
Example
GET AUDITPOINT
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
HELP
Scope
SQLAudit Syntax
Parameter
Description
Authorization
Example
LOCK AUDITPOINT
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
MODIFY AUDITPOINT
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
QUIT
Scope
SQLAudit Syntax
Description
Authorization
Example
SET
Scope
SQLAudit Syntax
Description
Authorization
Example
SET DBENVIRONMENT
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
SET ECHO_ALL
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
SET EDITOR
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
SET EXIT_ON_DBERR
Scope
SQLAudit Syntax
Description
Authorization
Example
SET RECOVERFILE
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
SHOW AUDITPOINT
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
UNLOCK AUDITPOINT
Scope
SQLAudit Syntax
Parameters
Description
Authorization
Example
Index

List of Tables

1-1 JCWs Set by SQLUtil
1-2 SQLGEN General Conventions
1-3 SQLGEN Name Entry Conventions
1-4 JCWs Set by SQLGEN
1-5 JCWs Set by SQLMigrate
1-6 SQLVERERR JCW
1-7 SQLAudit General Conventions
2-1 Sample Database Tables
2-2 Column Attributes for Two Tables
3-1 Data Type Storage Requirements
3-2 Page Requirements for Table Data
3-3 Page Requirements for Index Data
3-4 Logical Page Number and DBEFile Location in Hash Structure
3-5 Maximum Numbers of Locks Obtained at Different Granularities
4-1 DBECon Default Startup Parameters
5-1 Table and View Authorities
6-1 SQLMON Log Monitoring Tasks
6-2 Example Log File Names and Sequence Numbers
7-1 DBECon Parameters
8-1 System Catalog Views by Function
8-2 System.Account
8-3 System.Call
8-4 System.CheckDef
8-5 System.Colauth
8-6 System.Coldefault
8-7 System.Column
8-8 System.Constraint
8-9 System.ConstraintCol
8-10 System.ConstraintIndex
8-11 System.Counter
8-12 System.DBEFile
8-13 System.DBEFileSet
8-14 System.Group
8-15 System.Hash
8-16 System.Imagekey
8-17 System.Index
8-18 System.Installauth
8-19 System.Modauth
8-20 System.Paramdefault
8-21 System.Parameter
8-22 SYSTEM.PARTITION
8-23 System.Plan
8-24 System.Procauth
8-25 System.Procedure
8-26 System.ProcedureDef
8-27 System.ProcResult
8-28 System.Rule
8-29 System.RuleColumn
8-30  System.RuleDef
8-31 System.Section
8-32 System.Setoptinfo
8-33 System.Spaceauth
8-34 System.Spacedefault
8-35 System.Specauth
8-36 System.Tabauth
8-37  System.Table
8-38 System.TempSpace
8-39  System.TPIndex
8-40 System.Transaction
8-41 System.User
8-42  System.ViewDef
A-1 System Control Limits
A-2 Logical Data Limits
A-3 Space Management Limits
B-1 Authorities Required By ALLBASE/SQL Statements
E-1 Mapping the System Views to the Base System Tables
E-2 Locks Held on the System Catalog by SQL Statements
Feedback to webmaster