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
Defining Numeric Data
Numeric data types are either exact or approximate. Exact numeric data types can represent a
value exactly. Approximate numeric data types do not. NonStop SQL/MX supports these exact
and approximate numeric data types:
Exact numeric data typesNUMERIC
SMALL INT
INTEGER
LARGEINT
DECIMAL
PICTURE
Approximate numeric data typesFLOAT
REAL
DOUBLE PRECISION
FLOAT is compatible with other numeric data types. NonStop SQL/MX performs implicit data
conversion from other numeric types to handle arithmetic or comparison operations when required.
A column of an exact numeric type can accept a floating-point number. Also, a column of the
FLOAT data type can accept either a floating-point number or an exact numeric type. These rules
apply both to columns of an SQL/MX object and to a host variable field.
SQL/MX tables require IEEE floating-point data. SQL/MP tables require Tandem floating-point
data. For more information, see the SQL/MX Reference Manual.
When NonStop SQL/MX performs arithmetic operations on operands that have mixed data types,
the data type allowing the largest value is used to evaluate the numbers. For instance, if a REAL
number is used and REAL is the data type that allows the largest value, all other numeric data types
are converted first to REAL and then used in the expression.
Defining Datetime and Time Interval Data
The data types for datetime data are:
Contains a date in the external form yyyy-mm-dd.DATE
Contains a time in the external form hh:mm:ss.TIME
Contains a timestamp in the external form yyyy-mm-dd hh:mm:ss[.ffffff]. The
optional fractional seconds portion can be from zero to six digits long.
TIMESTAMP
The data type for time interval data is:
Duration of timeINTERVAL
Columns of the DATE, TIME, TIMESTAMP, and INTERVAL data types contain information about
dates, times, and time intervals. A comparison of these data types follows, denoting in each case
the data type, its meaning, and the range of data allowed in fields of a value of this type. The
fields in these data types are not equivalent to columns.
A column value of type DATE is made up of these contiguous fields:
0001 to 9999YearYEAR
01 to 12Month of yearMONTH
01 to 31Day of MonthDAY
The maximum value of DAY depends on the length of the month.
90 Creating an SQL/MX Database