• Describe HP NonStop SQL/MX architecture
• Install SQL/MX and upgrade SQL/MX metadata to current release
• Create, alter, and drop SQL/MX database objects (catalogs, schemas, tables, constraints, indexes, views, and triggers)
• Grant and revoke access privileges to SQL/MX database objects
• Import data into SQL/MX non-partitioned and range and hash partitioned tables
• Protect and recover SQL/MX database objects with HP NonStop Transaction Management Facility
• Use the MODIFY utility to add, split, merge, and drop SQL/MX range and hash partitioned tables
• Describe and use the SQL/MX utilities (DUP, import, migrate, MODIFY, mxtool, mxexportddl, MXGNAMES, POPULATE INDEX, and PURGEDATA)
• Describe the SQL.MX distributed database architecture

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
• HP NonStop Integrity Server or NonStop Blade Server or later that supports 1 GB of memory and IEEE floating point
• H06.22 or later Operating System and DDL Licensing Product T0394
• Projecting equipment, computer to display MS Power Point slides and access to a TACL process
• Student computers with access to a TACL process

• Database administrators
• System managers
• Personnel responsible for the availability of NonStop SQL/MX databases and applications.

• NonStop SQL/MX Basics (U4184S)
• NonStop SQL/MX Quick Start (U4185S)