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
Assumptions
For the purpose of the examples, suppose that:
• Two NonStop server systems, \DEV and \PROD, are connected by an Expand network. For
more information, see the Introduction to Networking for HP NonStop NS-Series Servers.
• The development node catalogs and schemas are not visible from the production node and
vice versa. To ensure this, do not use the REGISTER CATALOG statement from either node to
create an empty catalog reference on the other node. For more information, see the “Using
REGISTER CATALOG to Access Objects on Remote Nodes” (page 216).
• The program is a single file named myprog.exe for a C, C++, or COBOL program. For
more information about creating a single-file application for deployment, see the SQL/MX
Programming Manual for C and COBOL.
• The program is in the OSS directory /usr/bin on the development system, and it will be
moved to a directory of the same name on the production system.
Instructions
To move a program without compiled modules to a production system:
1. Copy required program files and executables from a directory on the development system to
an OSS directory on the production system:
NOTE: If you generated a module definition file (.m), make sure to copy that file to the
production system as well.
• Issue this OSS command to copy the executable of an embedded SQL program in C,
C++, or COBOL to the production system:
cp /E/DEV/usr/bin/myprog.exe \
/E/PROD/usr/bin/
For a list of the program files required for execution, see the SQL/MX Programming Manual
for C and COBOL.
2. If the application refers to SQL/MP tables or views by using DEFINEs, use the OSS add_define
command or the TACL ADD DEFINE command to set up the class MAP DEFINEs in the
production environment. See “Setting DEFINEs” (page 217).
3. If the application refers to SQL/MP tables or views by using SQL/MP aliases, create the
SQL/MP aliases in the production environment. See “Setting SQL/MP Aliases” (page 217).
4. Generate a new module or modules for the program:
• For an embedded SQL program in C, C++, or COBOL, run the mxCompileUserModule
utility on the production system to SQL compile an embedded module definition:
mxCompileUserModule myprog.exe
Alternately, run the mxcmp command to SQL compile a module definition file:
mxcmp sqlprog.m
For more information, see the SQL/MX Programming Manual for C and COBOL.
The program is now ready for execution in the production environment.
Moving a Program and Reprocessing Modules Using the mxrpm Tool
When you move a program and its compiled modules to the production system, the query execution
plans undergo a Similarity Check and possible automatic recompilation on execution on the
production system. The SQL/MX executor initiates a Similarity Check because the compile time
definition timestamp of the query execution plan, which is based on tables on the development
system, differs from the runtime definition timestamp of the tables on the production system.
214 Managing Database Applications