EncartaLabs

HPE NonStop SQL/MX

( Duration: 5 Days )

This HPE NonStop SQL/MX training course provides an introduction to SQL, relational database principles, and the HPE NonStop SQL/MX product and serves as a prerequisite to more advanced NonStop SQL/ MX courses. Hands-on lab sessions provide practical experience with generating SQL/MX queries to access data and creating database objects (catalogs, tables, indexes, views, and constraints).

By attending HPE NonStop SQL/MX workshop, delegates will learn to:

  • Describe relational database concepts and terminology
  • Describe the HPE NonStop SQL/MXprocesses and objects
  • Use a mxci session and reference ANSI names for SQL/MX database objects
  • Describe the basic process to write queries and the tools to evaluate the query performance
  • Use the mxci SELECT statement and predicates to retrieve data from single tables
  • List the types of functions supported in SQL/MX
  • Retrieve data from:
    • multiple tables using joins and union operations
    • derived tables using Query Expression by using subqueries
  • Create a SQL/MX database (Catalog, Schema, Tables, Indexes, Views, and Constraints)
  • Modify data in a table using SQL/MX INSERT, UPDATE, and DELETE statements
  • Describe the SQL/MX access options and isolation levels
  • Describe SQL/MX database management functions
  • Understand key relational database concepts and terminology
  • Be able to use mxci commands to create SQL/MX objects
  • Be able to use SQL statements to retrieve or modify data in SQL/MX tables

  • Attend a training on Concepts and Facilities for HPE NonStop Systems or equivalent practical experience

The HPE NonStop SQL/MX class is ideal for:

  • Anyone requiring an introduction to SQL and working with the NonStop SQL/MX product

COURSE AGENDA

1

Introduction to SQL Relational Databases

  • Definition of a relational database
  • Components of a relational database table
  • Forming relationships in a relational database
  • Types of relationships
  • Description of Structured Query Language: Data Definition Language, Data Manipulation Language, Data Control Language, and Transaction Control Language
  • Characteristics of a Relational Database Management System (RDBMS)
2

Overview of SQL/MX Architecture

  • SQL/MX Architecture
  • SQL/MX System Metadata
  • User Metadata (UMD) Tables
  • User Catalog and Schemas
  • SQL/MX User Tables, Objects, Tables, Indexes, Views, Constraints, Triggers, Object Namespaces-Object Type, Security Model, Process Architecture, Components, Catalog Manager, DDL Operations, Utilities
  • NSM/web Architecture
3

Introduction to mxci

  • SQL/MX Help Facilities
  • Starting an mxci session
  • mxci Prompts and Termination Character
  • SQL/MX Identifiers
  • Logical (ANSI) Names
  • Specifying ANSI
  • Using Logical Names in an mxci Session
  • mxci:
    • SET NAMETYPE Command (ANSI)
    • SET CATALOG Command
    • SET SCHEMA Command
  • mxci cd Command, Is Commands
  • mxci—LOG Command
4

Query Writing Process

  • Overview of query execution
  • Overview of query development process
  • Analyzing the query objective, Generating the query, Executing the query
  • Verifying the results, Assessing performance
5

Retrieving Data from a Single Table

  • Data Types, Character Data Types, Numeric Data Types-Exact and Approximate, Datetime Data Types, Interval Data Types
  • INVOKE Command
  • SELECT Statement-Clauses, Syntax, Select List
  • SELECT-Select List, ALL or DISTINCT Rows, [ANY N] or [FIRST N]
  • FROM and WHERE Clause
  • Predicates
  • Row-Value-Constructor
  • Comparison Predicates-Syntax, Examples
  • LIKE, BETWEEN, and IN Predicates
  • Boolean Operators and Compound Predicates
  • NULL Values
  • IS [NOT] Predicate
  • ORDER BY, GROUP By, HAVING Clause
6

Functions and Expressions

  • Aggregate functions
  • Character functions
  • Datetime functions
  • Mathematical functions
  • Types of expressions
  • Literal expressions
  • Numeric expressions
7

Retrieving Data from Multiple Tables

  • Generating the following types of joins: CROSS, NATURAL, INNER, EQUI, LEFT, RIGHT, Self
  • Correlation Names
  • Join with Additional Search Conditions
  • UNION Operation
8

Query Expressions

  • Query expression: Definition, Types, Joined Table, Syntax
  • Non-Joined Query Expression Table: VALUES Statement, TABLE Statement, SELECT Query Specification
  • Simple Table—SELECT expression
  • Subquery: Definition, Non-Correlated, Correlated, Evaluation of a Correlated Subquery, Classification, SELECT Form of a subquery
  • Predicates: Subquery, Comparison, BETWEEN, IN, and EXISTS, and EXISTS Examples
  • Subqueries using the Comparison, BETWEEN, and IN Predicates
  • Subquery key points
9

Creating SQL/MX Objects

  • Creating SQL/MX Objects
  • SQL/MX Object Naming
  • CREATE CATALOG Command—Syntax, REGISTER CATALOG Command—Syntax, UNREGISTER CATALOG Command—Syntax, Catalog Considerations
  • CREATE SCHEMA Command—Syntax
  • Rules for Naming SQL/MX Schema Subvolumes, Schema Considerations, Creating a User Schema
  • Creating a SQL/MX Table—Topics
  • Column Definitions, Column Name Rules, Character Sets, Default Value, ISO88591 Character Set Examples
  • SYSTEM_DEFAULTS Table— NOT_NULL_CONSTRAINT_DROPPABLE_OPTION
  • Constraints, Constraints Names, Table Constraints
  • Specifying Physical Location and Name for the Underlying Guardian File
  • Specifying a Clustering Key, Specifying a Clustering Key—STORE BY Clause, Terminology
  • Clustering Key—No STORE BY Clause and No Primary Key Specified, Clustering Key—STORE BY PRIMARY KEY: Primary Key Specified As DROPPABLE
  • Specifying Guardian File Attributes
  • CREATE INDEX—Syntax, CREATE VIEW—Syntax, CREATE VIEW—Example, Considerations for Creating a View
10

Inserting Data and Updating Statistics

  • Methods for Loading Multiple Rows of Data
  • Inserting Data into the Database, INSERT Statement—Syntax, Inserting a Single Row, Inserting Multiple Rows, INSERT Considerations
  • SQL/MX Histogram Statistics, Statistics Tables, mxci UPDATE STATISTICS Utility, Examples of mxci UPDATE STATISTICS
11

Modifying Data

  • Maintaining Database Consistency
  • Transaction Management Statements
  • Explicit Transaction: User-Defined Transaction, INSERT, UPDATE, DELETE
  • Implicit Transaction: System-Defined Transactions, SELECT, INSERT, UPDATE, DELETE
  • Modifying Existing Data
  • UPDATE Statement—Syntax, Updating a Single Row, Updating Multiple Rows, UPDATE Statement—Scalar Subquery, UPDATE Considerations
  • Removing Data from the Database
  • DELETE Statement—Syntax, Deleting Data, DELETE Considerations
12

Access Options and Isolation Levels

  • Concurrency Control and Contention
  • Locking Considerations, Dirty Reads, Non-Repeatable Reads, Phantoms
  • Access Options and Isolation Levels, READ UNCOMMITTED Access Option, READ COMMITTED Access Option, READ COMMITTED Considerations, SERIALIZABLE or REPEATABLE READ Access Option
  • Lock Modes, Access Options and Lock Modes
  • SET TRANSACTION Statement, SET TRANSACTION Statement—Example, Transaction Isolation-Level Rules
  • DEADLOCK, Viewing Locks on a Table
13

Management Functions

  • SQL/MX Object Dependencies
  • SQL Authorization ID
  • Object Ownership and Security Rules
  • Granting Privileges to Users—Example
  • Altering SQL/MX Objects in a SQL/MX Database
  • Authorization Requirements for Altering Database Object
  • Altering TABLE or INDEX FILE Attributes, Adding Columns to a Table, Altering Considerations
  • Removing SQL/MX Database Objects, Dropping SQL/MX Objects From a SQL/MX Database, Removing Your Database Objects
  • Managing Data
  • mxtool VERIFY Utility, mxtool VERIFY Utility—Security Considerations, mxtool VERIFY Utility—Syntax
  • Performance, Monitoring Performance
  • Using the EXPLAIN Function with a Prepared Query
  • EXPLAIN statement with OPTIONS ‘f’
  • NSM/web Connectivity Services, Visual Query Planner DISPLAY STATISTICS
14

Advanced Topics

  • Referential Integrity (RI)
  • Trigger Definition
  • Partitioning—Range Partitioning, Hash Partitioning
  • Publish and Subscribe Services
  • Rowsets
  • Compound Statements
  • SELECT statement—TRANSPOSE Clause, SAMPLE Clause
  • Sequence Function
15

MXDM

  • Features and requirements of MXDM
  • Installing and Uninstalling MXDM
  • Example screens

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top