Detailed Course Outline
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