Course ID 00086595
HP product number U8617S
Course format ILT
Course length 5 day(s)
Skill level Expert
Delivery languages English
Related certifications
In preparation for these exams
Selected items from this course are included in these exams:
Register for this course.
Find this course in the Training calendar and click the “Register” link in the last column to begin your registration.

Course description

This course introduces tasks used by database administrators or system managers to manage and protect a NonStop SQL/MX database and applications that access the database.

Who should attend

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

Prerequisites

• NonStop SQL/MX Basics (U4184S)
• HP NonStop SQL/MX database for SQL/MP users (U4185S)
For complete prerequisites and requirements to achieve any of the related certifications or upgrade paths, see the certification description on the HP ExpertOne website.

Topics

  • 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

Objectives

After completing this course, you should be able to do the following:
  • 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

How to register

View the HP ExpertOne Global Training Calendar to register for the trainings offering that best meets your needs.

Policies, fees and cancellations

Course fees may vary and are established and collected by the training center delivering the course. Cancellation fees may apply. Contact your HP Authorized Training Partner for their respective policies.