SQL/MX 3.2 Management Manual (H06.25+, J06.14+)
Table Of Contents
- HP NonStop SQL/MX Release 3.2 Management Guide
- Contents
- About This Manual
- 1 Introduction to SQL/MX Database Management
- 2 Understanding and Planning SQL/MX Tables
- Planning Object Naming
- Understanding SQL/MX Table and File Structures
- Using Keys in SQL/MX Tables and Indexes
- The Key-Sequenced File Structure
- Planning Table and Index Partitioning
- Determining a Database Layout
- Using SQL/MX Tables
- Using Views
- Determining When to Use Indexes
- 3 Planning Database Security and Recovery
- Planning Database Security
- Planning Database Recovery
- Using TMF for Transaction Recovery, Database Consistency, and Database Recovery
- Using RDF to Set Up a Backup System
- Using Backup and Restore 2 for SQL/MX Database Recovery
- Frequency of Backups
- Backup Objects Hierarchy
- SQL/MX Objects Backed Up Explicitly
- SQL/MX Objects Backed Up Implicitly
- SQL/MX Objects Not Restored by BRCOM
- Other Restrictions on Backup and Restore 2 Operations
- Backing Up and Restoring OSS Program Files and SPJs
- Using the RESTORE SHOWDDL ON Option to Re-Create Objects
- 4 Reviewing and Setting System Defaults
- 5 Creating an SQL/MX Database
- Preparing to Create Your SQL/MX Database
- SQL/MX Subvolume and File Naming Guidelines
- Creating SQL/MX Metadata
- Creating Catalogs
- Creating Schemas
- Access Privileges for SQL/MX Database Objects
- Creating SQL/MX Tables
- Security Rules for Creating and Altering SQL/MX Tables
- Creating and Using Keys
- Creating and Using a Primary Key
- Example for Creating an SQL/MX Table With a User-Defined Primary Key
- Creating and Using a Clustering Key
- Example for Creating an SQL/MX Table Using the STORE BY Clause
- Using the SYSKEY
- Creating and Using a Partitioning Key
- Partially Decoupling the Clustering Key and the Partitioning Key
- Creating and Managing Partitions for SQL/MX Tables
- Performance Benefits of Partitioning
- Restrictions on Creating and Placing Partitions
- Naming Partitions
- Recommended Maximum Number of Partitions
- Prohibition Against Using Floating-Point Columns in Partitioning Keys
- Using HASHPARTFUNC to Plan and Build Hash-Partitioned Tables
- Using DDL_DEFAULT_LOCATIONS to Distribute Primary Range Partitions
- Special Considerations for Decision Support Systems (DSS) Applications
- Examples for Creating Partitioned SQL/MX Tables
- Additional Guidelines for Creating Tables
- Managing Table Data
- Creating Indexes for SQL/MX Tables
- Creating Constraints on SQL/MX Tables
- Creating Views of SQL/MX Tables
- Creating Triggers
- Creating Stored Procedures in Java
- Database Design Guidelines for Improving OLTP Performance
- Accessing an SQL/MP Database From NonStop SQL/MX
- 6 Querying SQL/MX Metadata
- Commands for Displaying Information
- SQL/MX Metadata Tables
- Understanding ANSI External and Internal Names
- Displaying System Schema Information
- Displaying Catalog Information
- Displaying Schema Information
- Displaying Users With Catalog and Schema Creation Rights
- Displaying Table Information
- Displaying Information About Views
- Displaying Information About SQL/MP Aliases
- Displaying Information About SPJs
- Displaying Index Information
- Displaying Partition Information
- Displaying Constraint Information
- Displaying Column Information
- Displaying all Columns in a Table
- Displaying all Columns in a View
- Displaying all Columns in an Index
- Displaying all Columns in a Primary Key or Unique Constraint
- Displaying all Columns in a NOT NULL Constraint
- Displaying all Columns in a Referential Integrity Constraint
- Displaying the Attributes of a Column
- Displaying all Tables Containing a Selected Column
- Displaying Information About Privileges
- Displaying Object Integrity and Consistency
- Displaying Version Numbers
- 7 Adding, Altering, and Dropping SQL/MX Database Objects
- Planning Operations on SQL/MX Objects
- Adding Objects to an SQL/MX Database
- Authorization Requirements for Adding Database Objects
- Adding Catalogs
- Adding Columns to an SQL/MX Table
- Adding Constraints
- Adding Indexes to SQL/MX Tables
- Adding Partitions to SQL/MX Tables and Indexes
- Adding Schemas
- Adding SQL/MP Aliases
- Adding Stored Procedures in Java (SPJs)
- Adding SQL/MX Tables
- Adding Triggers
- Adding Views
- Altering Objects in an SQL/MX Database
- Authorization Requirements for Altering Database Objects
- Altering SQL/MX Indexes
- Altering Partitions for SQL/MX Tables and Indexes
- Altering SQL/MP Aliases
- Altering SPJs
- Altering System Defaults
- Altering SQL/MX Tables
- Altering Triggers
- Altering Views
- Dropping Objects From an SQL/MX Database
- Authorization Requirements for Dropping Database Objects
- Dropping Catalogs
- Dropping Columns from an SQL/MX Table
- Dropping Constraints
- Dropping SQL/MX Indexes
- Dropping Partitions for SQL/MX Tables and Indexes
- Dropping Schemas
- Dropping SQL/MP Aliases
- Dropping SPJs
- Dropping SQL/MX Tables
- Dropping Triggers
- Dropping Views
- 8 Reorganizing SQL/MX Tables and Maintaining Data
- Purging Dropped Tables From the DDL Directory
- Using FUP RELOAD to Reorganize Tables
- DDL Lock Considerations for MODIFY, import, POPULATE INDEX, DUP, FASTCOPY, and PURGEDATA
- Using MODIFY to Manage Table and Index Partitions
- MODIFY and EMS Messages
- Online and Offline Partition Management
- MODIFY and TMF
- MODIFY and Table Reloading
- Recovering a Failed MODIFY Request and Resetting Flags
- Using MODIFY to Manage Range-Partitioned Tables and Indexes
- Examples of Using MODIFY With Range-Partitioned Tables
- Example of Adding a New Range Partition
- Example of Dropping an Existing Empty Range Partition
- Example of Moving an Existing Partition to a New Location
- Example of Splitting an Existing Range Partition and Merging the First or Last Part to an Existing Partition
- Example of Merging Two Adjacent Range Partitions Into One Range Partition
- Example of Reusing an Existing Range Partition by Setting the FIRST KEY Values to New Values
- Examples of Using MODIFY With Range-Partitioned Indexes
- Examples of Using MODIFY With Range-Partitioned Tables
- Using MODIFY to Manage Hash-Partitioned Tables and Indexes
- Managing System-Clustered Tables and Indexes
- Using import to Load SQL/MX Tables
- Guidelines for Using import
- Summary of import Options
- Other import Features
- Recommended Practices for Improving import Performance
- Running import on Empty Tables
- Running import on Populated Tables
- Managing Partitions to Improve import Performance
- Managing Constraints to Improve import Performance
- Managing Indexes to Improve import Performance
- Managing Triggers to Improve import Performance
- Managing Data Types to Improve import Performance
- Using import to Load Partitions
- Support for restarting import
- Examples of Using import to Load an SQL/MX Table
- Using import to Append Data to Tables or Partitions
- Using DUP to Copy Tables Into Tables
- Using FASTCOPY to Copy Tables into Tables
- Using PURGEDATA to Delete Data From Tables
- 9 Managing Database Applications
- Writing SQL/MX Applications to Recover From Temporary Network or Hardware Service Interruptions
- Moving Programs From Development to Production
- Distributing Programs Across Nodes
- Ensuring Proper Name Resolution
- Assigning Permissions for Running Database Applications
- Maintaining Query Execution Plan Validity
- Managing Modules
- Producing Locally Placed Modules or Globally Placed Modules
- Securing User Modules
- Checking Module Dependencies with DISPLAY USE OF
- Displaying all Modules and Dependent Objects
- Display all Modules and Their Corresponding Source SQL File Names
- Displaying Dependent Objects for One or More Specified Modules
- Display Source SQL File Name for a Given Module
- Display All Modules Dependent on a Specific Object
- Display all Modules and Their Corresponding Source SQL File Names Associated With an Object
- Display all the Invalid Modules and Their Corresponding Source SQL File Names Associated With an Object
- Display Source SQL File for a Given Module in a Given OSS Directory
- Display all Modules and Their Corresponding Source SQL Files in a Given OSS Directory
- Display Invalid Modules from a Given Directory (Along With Their Corresponding Source SQL Files) for a Given Object
- Display all Modules From a Given directory (Along With Their Corresponding Source SQL Files) for a Given Object
- Displaying the Modules in a Local Directory
- Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations
- Removing Modules
- Converting Globally Placed Modules to Locally Placed Modules
- Managing Module Files and Their Applications During Fallback From SQL/MX Release 3.2
- Backing Up and Restoring Programs
- 10 Performing Recovery Operations
- Recovering Databases After Disk or Node Failures
- Recovering Disk Volumes
- Recovering Files
- Recovering Metadata
- Recovering Database Objects
- Restoring Objects With BRCOM RESTORE
- Repairing Damaged SQL/MX Metadata and Objects
- Using GOAWAY to Delete Damaged Objects
- Recovering From SCF Commands
- 11 Managing an SQL/MX Distributed Database
- Managing a Locally Distributed SQL/MX Database
- Managing a Network-Distributed SQL/MX Database
- Creating a Distributed SQL/MX Database
- Altering Distributed Objects
- Dropping Distributed Objects
- Enhancing the Performance of a Distributed Database
- Changing Network Environments
- Managing Mixed Versions of NonStop SQL/MX
- 12 Measuring Performance
- 13 Enhancing SQL/MX Database Performance
- Using Queries in an SQL/MX Database
- Understanding the Implications of Concurrency
- Keeping Statistics Current
- Managing SQL/MX Buffer Space
- How DP2 Manages and Reuses Query Plan Fragments
- Causes and Symptoms of Query Plan Fragment Reuse Failures
- Reduction of Plan Fragment Size for Unique Queries
- Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures
- Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute
- Strategies for Reducing Reuse Failures
- Managing DP2 Data Cache Memory Size
- Maximizing Disk Process Prefetch Capabilities
- Optimizing SQL/MX Memory Management
- Optimizing Index Use
- Performing FUP RELOADs to Generate More Accurate Query Plans
- Checking Data Integrity
- Creating Logical Views of Data
- Adding and Dropping Partitions
- Avoiding Automatic Recompilations
- Matching Block Split Operation to Table Usage
- A Using Guardian Names with TMF, RDF, and Measure
- Using the MXGNAMES Utility
- MXGNAMES Input Files
- MXGNAMES Output Files
- MXGNAMES Examples
- TMF Example 1: Input Is a List of SQL/MX Table Names
- TMF Example 2: Input Is SHOWDDL Text
- TMF Example 3: Input Is an SQL/MX Table Name
- TMF Example 4: Input Is a List of SQL Names with File Length Specified
- Restore Example 1: Input Is a List of SQL/MX Table Names
- Restore Example 2: Input Is SHOWDDL Text
- Restore Example 3: Input Is an SQL/MX Table Name
- Using the MXGNAMES Utility
- Index
High Availability
These SQL/MX features help to ensure high availability for databases:
• Online dumps using the TMFCOM DUMP FILES command, with complete support for TMF file
recovery to recover a database in case of a disaster.
• Online database reorganization capabilities such as online partition moving, partition splitting,
and table and index row redistribution with concurrent read and update capability. For more
information, see “Reorganizing SQL/MX Tables and Maintaining Data” (page 176).
• Parallel table reorganization (using FUP RELOAD) and index loads (using POPULATE INDEX)
to reduce the time required to load the object.
• Automatic recompilation, which eliminates the need to terminate program execution when
changes in database structure or the environment make rebinding necessary.
• The ability to defer name resolution in SQL/MX statements until execution time.
Naming Database Objects
ANSI Logical Names
Names for SQL/MX objects are organized hierarchically. Database objects exist in schemas,
which are themselves contained in catalogs. Catalogs are collections of schemas. Schema names
must be unique within a given catalog.
You name tables, views, and other SQL/MX objects by using a three-part ANSI logical name:
catalog-name.schema-name.object-name
In this three-part name, catalog-name is the name of the catalog, schema-name is the name
of the schema, and object-name is the simple name of the object. Each of the parts is an SQL
identifier.
NonStop SQL/MX automatically qualifies an object name with the current default catalog and
schema name unless you explicitly specify catalog and schema names with the object name. These
terminology guidelines apply to ANSI names:
• A fully qualified or three-part ANSI name uses all three of its parts
(catalog-name.schema-name.object-name).
• A two-part ANSI name omits the catalog name (schema-name.object-name).
• A one-part or simple ANSI name omits the catalog and schema names (object-name).
The two-part name schema-name.object name is qualified implicitly by the current default
catalog. The one-part name object-name is qualified implicitly by the default schema and
catalog.
For more information, see the SQL/MX Reference Manual.
Guardian Physical Names
The underlying Guardian physical names of SQL/MX objects and SQL/MP objects are qualified
with the system node, volume, and subvolume names. For example, SQL/MP tables and views are
created with Guardian physical names of the form:
[\node.] [[$volume.]subvol.]filename
In this four-part name, \node is the name of a node on an HP NonStop system, $volume is the
name of a disk volume, subvol is the name of a subvolume, and filename is the name of a
Guardian disk file or the name of an SQL/MP table or view.
SQL/MX data is stored in files with Guardian physical names. In turn, these files are associated
with ANSI names.
Naming Database Objects 21