HP NonStop SQL/MX database management

Course 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.

Prerequisites

• NonStop SQL/MX Basics (U4184S)
• HP NonStop SQL/MX database for SQL/MP users (U4185S)

Audience

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

Ways to save

Course Objectives

• Describe HP NonStop SQL/MX Release 2 architecture
• Install SQL/MX Release 2 and migrate SQL/MX Release 1.x metadata to SQL/MX Release 2
• Create, alter, and drop SQL/MX Release 2 database objects (catalogs, schemas, tables, constraints, indexes, views, and triggers)
• Grant and revoke access privileges to SQL/MX database objects
• Importing data into SQL/MX non-partitioned and range and hash partitioned tables
• Protecting and recovering 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 Release 2.0 distributed database architecture
  

Benefits to you

• Create and manage SQL/MX Release 2 objects
• Become familiar with the SQL/MX Release 2 architecture using SQL/MX native tables

Why education services from HP?

• Focus on job-specific skills
• Comprehensive student materials
• More than 80 training locations worldwide
• Online instructor-led and self-paced training at http://www.hp.com/education
• State-of-the-art classroom facilities
• Hands-on practice
• Experienced and best-in-the-field HP instructors
• Customized on-site delivery

Course Outline

Module 1 Overview of SQL/MX Release 2 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 Release 2 Architecture
• Identify the system catalog, schemas, and objects
• Identify user catalogs, schemas, and objects
• Locate the underlying files for SQL/MX objects

Module 2 SQL/MX Release 2 Installation

• Hardware and software requirements for installing SQL/MX Release 2
• Installing SQL/MX Release 2 with the InstallSqlmx utility
• Install the SQL/MX sample database
• Lab exercise: SQL/MX Installation
• Run the InstallSqlmx utility

Module 3 Metadata Migration from SQL/MX Release 1.8 to SQL/MX Release 2.0

• Migrating SQL/MX Release 1.8 metdata, MP Aliases, defaults, ODBC/MX metadata, and stored procedures (procs and params) to SQL/MX Release 2.0
• Migrate Utility
• Lab exercise: Metadata migration from SQL/MX R1.8 to SQL/MX R2.0
• Run the migrate 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 ind
• Decoupled keys, copartitioned tables, and colocated 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 trigger
• Cascading and conflicting triggers
• Lab exercise: Creating SQL/MX Triggers

Module 8 SQL/MX Security

• Object ownership and security rules
• Object Privileges
• 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 Release 2.0 and 1.x Programs
• Preprocessing source files for SQL/MX Release 2 or SQL/MX Release 1.x
• Language compiling SQL/MX annotated source files
• SQL/MX compiling Release 2 module definitions or SQL/MX Release 1.x module definition files
• Global or local 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 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 17 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
  
• Module 18 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

Onsite-Delivery Equipment Requirements

• HP NonStop S72000 Server or later that supports 1 GB of memory and IEEE floating point
• G06.25 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