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
Performance Benefits of Partitioning
• Partitions are independent of one another and only the accessed partition needs to be available.
The query plan stores the primary partition. If the primary partition cannot be found, the
alternate partitions are checked, starting with local partitions.
• Partitions improve transaction throughput by allowing simultaneous disk access to different
partitions of the same table.
• Partitions require no special access procedures. NonStop SQL/MX manages partition access
for you automatically.
• Partitions enable NonStop SQL/MX to more readily process queries in parallel.
• Partitions allow you to have tables larger than the size of a single disk volume.
Partitioning the indexes of a table enables NonStop SQL/MX to take maximum advantage of
parallel index updates. You can either partition index rows to reside on the same disks (co-location)
or on separate disks.
For more information, see the “Optimizing Index Use” (page 302).
Restrictions on Creating and Placing Partitions
• When you create a hash-partitioned table with a single partition, you can add additional
hash partitions to the table. However, you cannot change the partitioning scheme to range
partitioning.
• If you do not specify a partitioning scheme when you create a single-partition table, you can
only add range partitions to the table. You cannot add hash partitions to the table.
• You cannot create SQL/MX tables or other SQL/MX objects on an SMF volume. Nor can you
use SMF to manage the distribution of partitions across physical disk volumes.
• You can put more than one partition on a single disk, but this approach slows down parallel
query processing and is not recommended in a production environment.
Naming Partitions
Use the NAME clause of CREATE TABLE or CREATE INDEX to name a partition. The partition name
is an SQL identifier that must be unique among the set of partitions for a given table or index.
All table and index partitions must have names. If you do not specify one, a system-generated
name is assigned to the partition. For more information, see the SQL/MX Reference Manual.
Recommended Maximum Number of Partitions
HP recommends that you limit the number of partitions in an SQL/MX table or index to no more
than 512. If you exceed this recommended limit, you might get an MXCMP internal error because
of a shortage of virtual memory space.
Prohibition Against Using Floating-Point Columns in Partitioning Keys
The floating-point data type is an approximate—not an exact—data type. Any key expression that
results in a floating-point value that is close to a partitioning key boundary might fail to access the
correct partition.
Therefore, NonStop SQL/MX does not support the use of floating-point data type columns in
partitioning keys. Attempts to create SQL/MX tables or indexes with floating-point partitioning key
columns fail with error message 1124. Attempts to perform DML from NonStop SQL/MX on
SQL/MP tables with floating-point partitioning key columns fail with error message 1120. For more
information, see the SQL/MX Messages Manual.
Creating SQL/MX Tables 83