HPE NonStop SQL/MX Database Management

Module 1: Overview of SQL/MX Architecture

• SQL/MX logical structure, catalogs, schemas, and objects
• SQL/MX physical structure: SMD volumes, schema subvolumes, data forks and resource forks
• SQL/MX processes and utilities
• Lab exercise: Overview of SQL/MX Architecture
• Identify the system catalog, schemas, and objects
• Identify user catalogs, schemas, and objects
• Locate the underlying files for SQL/MX objects
• Metadata basics

Module 2: SQL/MX Installation

• Hardware and software requirements for installing SQL/MX
• Installing SQL/MX with the InstallSqlmx utility
• Verifying the SQL/MX Installation
• Create the SQL/MX sample database
• Lab exercise: Perform a post-installation check for SQL/MX.

Module 3: Metadata Migration to SQL/MX R3.0

• Migrating considerations
• Post upgrade scenarios
• FIXRCB operation
• UPGRADE utility

Module 4: Creating SQL/MX Objects

• Creating catalogs, schemas, tables, constraints, indexes, and views
• Viewing object metadata
• Lab exercise: Creating SQL/MX Objects with mxci or NSM/web

Module 5: Referential Integrity Constraints

• Referential Integrity
• Creating referential integrity constraints as column or table constraints
• Lab exercise: Creating Referential Integrity constraints with mxci or NSM/web

Module 6: Creating SQL/MX Partitioned Tables and Indexes

• Range and hash partitioned tables
• Creating range and hash partitioned tables and indexes
• Advantages and Disadvantages of each type of partitioning.
• Decoupled keys, co-partitioned tables, and co-located partitions
• Lab exercise: Creating range and hash partitioned tables with mxci or NSM/web

Module 7: Creating SQL/MX Triggers

• Trigger terminology
• Creating BEFORE and AFTER triggers
• Cascading and conflicting triggers
• Lab exercise: Creating SQL/MX Triggers

Module 8: SQL/MX Security

• Object ownership and security rules
• Object Privileges
• Use of Security Administrator’s Group
• Change object ownership
• SQL/MX GRANT and REVOKE commands
• Lab exercise: Granting and Revoking Privileges with mxci or NSM/web

Module 9: Import and Populate Index Utilities

• Importing fixed and delimited data with the import utility
• Importing data in parallel
• Populating (loading) indexes
• Lab exercise: Importing Data into Range and Hash Partitioned Tables and Indexes

Module 10: Reorganizing SQL/MX Tables and Indexes

• Reorganizing data
• Determining file fragmentation
• FUP RELOAD and INFO commands
• Lab exercise: Reorganizing Data

Module 11: Updating Statistics

• Histogram Statistics and histogram metadata tables
• Generating single column and multicolumn statistics
• Using sampling with the update statistics utility
• Lab exercise: Updating and Viewing Statistics

Module 12: Database Protection and Recovery with TMF

• TMF Protection and Recovery Strategies
• MXGNAMES Utility
• Performing Online Dumps
• Recovering dropped SQL/MX Objects
• Lab exercise: SQL/MX Protection and Recovery Using TMF

Module 13: Compiling SQL/MX Programs in the OSS Environment

• Compiling SQL/MX Programs!Preprocessing source files for SQL/MX
• Language compiling SQL/MX annotated source files
• SQL/MX compiling module definition files
• Global or local module files
• Defining search paths for module files
• Lab exercise: Compiling Embedded SQL/MX C or COBOL Programs in the OSS environment

Module 14: PURGEDATA Command and MODIFY Utility

• Purging data from range and hash partitioned tables
• Adding, splitting, merging, and dropping range partitions
• Adding and dropping hash partitions
• Lab exercise: PURGEDATA Command and MODIFY Utility

Module 15: SQL/MX Utilities

• DUP tables, indexes, and constraints
• RECOVER utility
• mxtool (INFO, VERIFY, FIXUP, and GOAWAY) utility
• Lab exercise: SQL/MX Utilities

Module 16: NonStop MXDM

• Features and requirements of MXDM
• Installing and uninstalling
• Example screens

Module 17: Managing SQL/MX Applications

• Name references in programs
• Compile time and late name resolution
• Similarity checking
• Automatic recompilation
• Methods for moving SQL/MX applications
• Lab exercise: Managing Embedded SQL/MX Programs

Module 18: SQL/MX Distributed Databases

• Distributed databases
• Transparency, visibility, and availability
• ANSI name translation for local and remote objects
• REGISTER and UNREGISTER CATALOG commands
• Lab exercise: SQL/MX Distributed Database Demonstration

Module 19: Adding, Altering, and Dropping SQL/MX Objects

• Authorization for adding, altering, and dropping SQL/MX objects
• Adding SQL/MX objects
• Altering SQL/MX objects
• Dropping SQL/MX objects
• Lab exercise: Altering and dropping SQL/MX objects

Module 20: Open Source Tools

• Overview of open source tools available for SQL/MX usage
• Describe usage of Eclipse to access SQL.MX
• Overview of use of DBVisualizer to access SQL/MX