HP 3000 Manuals HP 3000 Manuals
Table of Contents
ALLBASE/SQL Database Administration Guide
ALLBASE/SQL Database Administration Guide : COPYRIGHT NOTICE
Ch 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
Defining Logs
Logs and Recovery
LOG FULL Condition
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
Starting SQLUtil
JCWs Set by SQLUtil
Using SQLGEN
Starting SQLGEN
SQLGEN Commands
SQLGEN Schema Files
SQLGEN Conventions
Entering Object Names
JCWs Set by SQLGEN
Using SQLMigrate
Running SQLMigrate
Types of Forward Migration
Steps for Forward Migration
Steps for Backward Migration
JCWs Set by SQLMigrate
Using SQLINSTL
Using SQLMON
Using SQLVer
Using SQLAudit
SQLAudit Conventions
Understanding Audit Points
Example of Getting Audit Points
Establishing SQLAudit Log Locks
Performing an Audit
SQLAudit Result Files
Checking the Exit Status
System Administration for ALLBASE/SQL
Shared Memory Usage
Native Language Support
Network Administration
Using NETUtil
Ch 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
Planning Joins
Final Form of Sample Database Tables
Defining Columns
Defining Column Names
Defining Column Data Type
Defining Column Size
Defining Null Values For Columns
Designing Views
Designing Indexes
Determining Index Keys
How Index Keys are Used
Determining Index Type
Defining Unique Indexes
Defining Clustering Indexes
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
Determining Group Membership
Defining Classes
Differences Between Groups and Classes
Guidelines for Creating Classes
Defining the DBEnvironment Scope
Ch 3. Physical Design
Calculating Storage for Database Objects
Understanding DBEFile Characteristics
Calculating Storage for Tables
Calculating Row Length
Calculating Rows per Page
Calculating Number of Pages
Calculating Directory Overhead
Calculating Storage for Indexes
Calculating the Index Key Length
Calculating the Size of the Index Header
Calculating the Number of Rows per Leaf Page
Calculating the Number of Rows per Non-Leaf Page
Calculating the Number of Leaf Pages
Calculating the Number of Non-Leaf Pages
Calculating the Number of Directory Overhead Pages
Calculating Total Number of Index Pages
Arranging Tables and Indexes in DBEFileSets
Grouping Tables in DBEFileSets
Choosing DBEFile Types and Devices
Using a Single MIXED DBEFile
Using Separate DBEFiles for Tables and Indexes
Using Different Storage Devices
Estimating DBEFile Size
Calculating Storage for Hash Structures
Calculating Primary Pages
Allowing for Overflow
Calculating the Size of DBEFiles for Hash Structures
Allocating DBEFiles for Hash Structures
Mapping Logical Page Number to Physical File Location in Hash Structures
Calculating Storage for Integrity Constraints
Unique Constraints
Referential Constraints
Hashing on Constraints
Check 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
Determining the Number of Log Files
Estimating Log File Size
Example
Calculating Temporary Disk Space
Controlling the Use of Temporary Space
Estimating Shared Memory Requirements
Estimating Runtime Control Block Buffer Pages
Effects of Page and Row Level Locking
Running out of Shared Memory
Estimating Data Buffer Pages
Setting the Memory Resident Buffer Flag
Estimating Log Buffer Pages
Estimating the Number of Transactions
Implementing the Design
Ch 4. DBEnvironment Configuration and Security
Required MPE/iX Capabilities
Using START DBE NEW
Supplying Startup Parameters with START DBE NEW
Log Files
Dual Logging
Archive Logging
Multiple Log Files
Specifying a Native Language Parameter
Looking at the DBEnvironment Elements
Examining MPE Files
Examining DBECon Parameters
Examining the System Catalog
Examining Log File Characteristics
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
DBA Functions
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
Starting a DBE Session in Single-User Mode
Overriding DBECon Parameters
Starting DBE Sessions without Autostart
Rollback Recovery with START DBE
Connecting to a DBE
Terminating a DBE Session
Using RELEASE
Using STOP DBE
Using TERMINATE USER
Terminating Transactions and Queries
Setting Timeout Values
Remote Database Access
Ch 5. Database Creation and Security
Creating Tables
Table Type
Revoking and Granting Authorities on PUBLICROW and PUBLIC Tables
Altering Table Type
Owner and Table Name
Column Definition
Column Name and Data Type
Language Clause
DEFAULT Clause
Constraint Definitions
Unique Constraints
Referential Constraints
Check Constraints
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
Authorities for Single Users
Authorities for Groups
Creating Classes
Revoking Table and View Authorities
Controlling Table Access with Views
Using the GRANT OPTION Clause
Orphaned Privileges
Using the WITH GRANT OPTION Clause and Authorization Groups
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
Ch 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
Log File Names
Identifier Numbers
Sequence Numbers
Using Nonarchive Logs
Multiple Files in Nonarchive Mode
Using Archive Logs
Multiple Files in Archive Mode
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
Rollback Recovery
Full Rollforward Recovery
Partial Rollforward Recovery
ALLBASE/SQL Interface to True Online Backup
Managing Log Files
Monitoring the Log with SHOWLOG
Displaying Files in the Log
Log File Status Types
Displaying Available File Space
Using the CHECKPOINT command
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
Single Logs
Dual Logs
Selecting Log Files when the DBECon File is Inaccessible
Creating a DBEnvironment
Wrapping the DBEnvironment Around the Log Files
Example of Setting Up a Wrapper DBE
Ch 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
From TABLE or INDEX to MIXED
From MIXED to TABLE or INDEX
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
Information in the System Catalog on Validity of Sections
Monitoring File Space for Modules and Sections
Causes for Invalidation of Sections
Avoiding the Need for Re-Preprocessing
Determining Available Space for Sections
Determining Number of Sections in the DBEnvironment
Module Related Authorities
Sharing Modules Between DBEnvironments
Dropping Modules
Maintaining a Nonstop Production System
DBEFiles in Different Groups and Volumes
Moving DBEFiles to Different Groups
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
Ch 8. System Catalog
Views owned by SYSTEM and CATALOG
Summary of System Catalog Views by Function
Using the System Catalog
System Catalog Views
Views owned by SYSTEM and CATALOG (Continued)
Using UPDATE STATISTICS on System Views
Locking of the System Catalog
Storedsect.System
Storedsect.DBEFileSetName
System.Account
Example
System.Call
Example
Views owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.CheckDef
Example
System.Colauth
Example
System.Coldefault
Example
System.Column
Example
Views owned by SYSTEM and CATALOG (Continued)
Locking of the System Catalog (Continued)
System.Constraint
Example
System.Constraintcol
Example
System.ConstraintIndex
Example
System.Counter
Example
System.DBEFile
Example
Views owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.DBEFileSet
Example
System.Group
Example
System.Hash
Example
System.Imagekey
Example
System.Index
Views owned by SYSTEM and CATALOG (Continued)
Locking of the System Catalog (Continued)
System.Index (Continued)
Example
System.Installauth
Example
System.Modauth
Example
System.Paramdefault
Example
System.Parameter
Example
System.Partition
Views owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.Plan
Example
System.Procauth
Example
System.Procedure
Example
System.ProcedureDef
Example
System.ProcResult
Example
System.Rule
Example
Views owned by SYSTEM and CATALOG (Continued)
Locking of the System Catalog (Continued)
System.RuleColumn
Example
System.RuleDef
Example
System.Section
Example
System.Setoptinfo
Example
Views owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.Spaceauth
Example
System.Spacedefault
Example
System.Specauth
Example
System.Tabauth
Example
Views owned by SYSTEM and CATALOG (Continued)
Locking of the System Catalog (Continued)
System.Table
Example
System.TempSpace
Example
System.TPIndex
System.Transaction
Example
System.User
Example
View owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.ViewDef
Example
Ap A. ALLBASE/SQL Limits
Ap B. Authorities Required by ALLBASE/SQL Statements
Ap C. 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)
SQL Syntax Summary (Continued)
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
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
SQL Syntax Summary (Cont.)
REFETCH
RELEASE
REMOVE DBEFILE
REMOVE FROM GROUP
RENAME COLUMN
RENAME TABLE
RESET
RETURN
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
SQL Syntax Summary (Continued)
START DBE NEW
DBEFile0Definition
DBELogDefinition
START DBE NEWLOG
NewLogDefinition
STOP DBE
TERMINATE QUERY
TERMINATE TRANSACTION
TERMINATE USER
TRANSFER OWNERSHIP
TRUNCATE TABLE
UPDATE
LongColumnIOString
UPDATE STATISTICS
UPDATE WHERE CURRENT
LongColumnIOString
VALIDATE
WHENEVER
WHILE
Ap D. ISQL Syntax Summary
Ap D. ISQL Syntax Summary (cont.)
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 E. Locks Held on the System Catalog by SQL Statements
Ap F. SQLUtil
ALTDBE
ATTACHFILE
CHANGELOG
DETACHFILE
ENDRECOVERY
EXIT
HELP
MOVEFILE
MOVELOG
PURGEALL
PURGEDBE
PURGEFILE
PURGELOG
QUIT
RECOVERLOG
RESCUELOG
RESTORE
RESTORELOG
RESTORE PARTIAL
SET
SETDBEMAINT
SETUPRECOVERY
SETUPRECOVERY PARTIAL
SHOWACCESS
SHOWDBE
SHOWDBE-ALL
SHOWDBE-EXIT
SHOWDBE-HELP
SHOWDBE-LANG
SHOWDBE-MAINT
SHOWDBE-QUIT
SHOWDBE-STARTPARMS
SHOWLOG
SHOWSET
STORE
STOREINFO
STORELOG
STOREONLINE
STOREONLINE PARTIAL
STORE PARTIAL
SYSTEM
WRAPDBE
Ap G. SQLGEN
EDITOR
EXIT
GENERATE ALL
GENERATE DBE
GENERATE DBEFILES
GENERATE DEFAULTSPACE
GENERATE GROUPS
GENERATE INDEXES
GENERATE INSTALLAUTH
GENERATE LOAD
GENERATE MODAUTH
GENERATE MODOPTINFO
GENERATE PARTITION
GENERATE PROCAUTH
GENERATE PROCEDURES
GENERATE PROCOPTINFO
GENERATE RULES
GENERATE SPACEAUTH
GENERATE SPECAUTH
GENERATE STATISTICS
GENERATE TABAUTH
GENERATE TABLES
GENERATE TEMPSPACES
GENERATE VIEWAUTH
GENERATE VIEWS
HELP
RELEASE
SET ECHO_ALL OFF
SET ECHO_ALL ON
SET EDITOR
SET EXIT_ON_DBERR OFF
SET EXIT_ON_DBERR ON
SET SCHEMA
STARTDBE
:
Ap H. SQLMigrate
ADD DBEFILE
CREATE DBEFILE
EXIT
HELP
MIGRATE
PREVIEW
QUIT
REPAIR
SET
SHOW 'DBEnvironmentName' VERSION
SHOW VERSIONS
:
AUDIT
EDITOR
EXIT
GET AUDITPOINT
HELP
LOCK AUDITPOINT
MODIFY AUDITPOINT
QUIT
SET
SET DBENVIRONMENT
SET ECHO_ALL
SET EDITOR
SET EXIT_ON_DBERR
SET RECOVERFILE
SHOW AUDITPOINT
UNLOCK AUDITPOINT
INDEX
Index