Overview

This 5-day 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.

Read more

Prerequisites

  • NonStop SQL/MX Basics
  • NonStop SQL/MX Quick Start


Who Should Attend?

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

Delegates will learn how to

  • 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
Read more

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
  • 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
Read more

Why choose QA

Dates & Locations

Frequently asked questions

See all of our FAQs

How can I create an account on myQA.com?

There are a number of ways to create an account. If you are a self-funder, simply select the "Create account" option on the login page.

If you have been booked onto a course by your company, you will receive a confirmation email. From this email, select "Sign into myQA" and you will be taken to the "Create account" page. Complete all of the details and select "Create account".

If you have the booking number you can also go here and select the "I have a booking number" option. Enter the booking reference and your surname. If the details match, you will be taken to the "Create account" page from where you can enter your details and confirm your account.

Find more answers to frequently asked questions in our FAQs: Bookings & Cancellations page.

How do QA’s virtual classroom courses work?

Our virtual classroom courses allow you to access award-winning classroom training, without leaving your home or office. Our learning professionals are specially trained on how to interact with remote attendees and our remote labs ensure all participants can take part in hands-on exercises wherever they are.

We use the WebEx video conferencing platform by Cisco. Before you book, check that you meet the WebEx system requirements and run a test meeting (more details in the link below) to ensure the software is compatible with your firewall settings. If it doesn’t work, try adjusting your settings or contact your IT department about permitting the website.

Learn more about our Virtual Classrooms.

How do QA’s online courses work?

QA online courses, also commonly known as distance learning courses or elearning courses, take the form of interactive software designed for individual learning, but you will also have access to full support from our subject-matter experts for the duration of your course. When you book a QA online learning course you will receive immediate access to it through our e-learning platform and you can start to learn straight away, from any compatible device. Access to the online learning platform is valid for one year from the booking date.

All courses are built around case studies and presented in an engaging format, which includes storytelling elements, video, audio and humour. Every case study is supported by sample documents and a collection of Knowledge Nuggets that provide more in-depth detail on the wider processes.

Learn more about QA’s online courses.

When will I receive my joining instructions?

Joining instructions for QA courses are sent two weeks prior to the course start date, or immediately if the booking is confirmed within this timeframe. For course bookings made via QA but delivered by a third-party supplier, joining instructions are sent to attendees prior to the training course, but timescales vary depending on each supplier’s terms. Read more FAQs.

When will I receive my certificate?

Certificates of Achievement are issued at the end the course, either as a hard copy or via email. Read more here.

Contact Us

Please contact us for more information