About this course

Course code TPU8617S
Duration 5 Days

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.

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

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

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

5 Days

Duration

This is a QA approved partner course

Delivery Method

Delivery method

Classroom

Face-to-face learning in the comfort of our quality nationwide centres, with free refreshments and Wi-Fi.

Trusted, awarded and accredited

Fully accredited to ensure we provide the highest possible standards in learning

All third party trademark rights acknowledged.