About this course

Course code TPU4185S
Duration 5 Days

This 5 day course is to help SQL/MP users transition to SQL/MX. The course describes the HP NonStop SQL/MX Release 2.0 architecture and processes.

Prerequisites

  • NonStop SQL/MP experience in a production environment
  • Concepts and Facilities course (U4147S)
  • HP NonStop SQL/MP Essentials course (U4181S)

Audience

Persons with practical experience creating databases, and generating queries using the NonStop SQL/MP product wanting to develop new applications using the ANSI-92 features of the NonStop SQL/MX product

Delegates will learn how to

  • Describe how to run an mxci session and basic mxci commands
  • Describe how to start an mxci session, use ANSI names to access SQL/MX objects and Guardian names, SQL/MP aliases, and DEFINEs to access NonStop objects
  • Describe SQL/MX DLM statements, data types, identifiers, functions, and isolation levels
  • Describe how to compile embedded SQL/MX C/C and COBOL programs
  • Describe how to use SQL/MX compound statements, Rowsets, and Publish and Subscribe services in embedded SQL/MX programs
  • Describe SQL/MX module management features: targeting, versioning, and grouping
  • Describe and use SQL/MX late name resolution, similarity checking, and auto-recompiling
  • Describe and use SQL/MX data mining functions: transpose, sampling, and sequence functions
  • Describe SQL/MX optimization, operators, plan fragments and use EXPLAIN function and Visual Query Planner to display query plans

Outline

Module 1 Overview of NonStop SQL/MX Release 2.0 Architecture

  • SQL/MX Release 2 Architecture - ANSI Catalogs and Schemas
  • Underlying physical files that support the SQL/Mx objects
  • SQL/MX Release 2 objects, processes, and utilities.
  • Lab exercise:
  • Lab 1.1 Overview of SQL/MX Release 2.0 Architecture Using mxci
  • Lab 1.2 Overview of SQL/MX Release 2.0 Architecture Using NSM/web

Module 2 Running an mxci Session

  • Running an mxci session and accessing SQL/MX and SQL/MP objects
  • SQL/MX object naming methods
  • mxci commands
  • Lab exercise: Lab 2 Running an mxci Session

Module 3 SQL/MX DML Statements and Options

  • SQL/MX Data types and functions
  • DML statements and options
  • SQL/MX Join operations
  • SQL/MX access options and isolation levels
  • SQL/MX implicit and explicit transaction management
  • Lab exercise: Lab 3 DML Statements

Module 4 Compiling Embedded SQL/MX

  • Programs in the OSS Environment
  • Module Directive and embedding SQL/MX statements
  • Preprocessing, language compiling and SQL/MX compiling embedded SQL/MX COBOL and C programs
  • Lab exercise:
  • Lab 4.1 Compiling SQL/MX COBOL Programs
  • Lab 4.1 Compiling SQL/MX C Programs

Module 5 Preprocessing SQL/MX Programs on a

  • Windows NT/2000/XP Workstation
  • Preprocessing embedded SQL/MX COBOL and C program

Module 6 SQL/MX Compound Statements

  • Compound statement
  • Assignment statement
  • IF statement
  • Lab exercise:
  • Lab 6.1 Compound Statements in an SQL/MX COBOL Program
  • Lab 6.2 Compound Statements in an SQL/XM C Program

Module 7 SQL/MX Rowsets

  • Describe and implement rowsets in embedded DML statements in SQL/MX COBOL and C programs
  • Lab exercise:
  • Lab 7.2 Rowsets in an SQL/MX C Program
  • Lab 7.1 Rowsets in an SQL/MX COBOL Program

Module 8 SQL/MX Queuing and Publish and Subscribe Services

  • Describe publish and subscribe services and transactional queuing
  • Create publishing, subscribing, and dequeuing applications
  • Lab exercise:
  • Lab 8.1 SQL/MX Queuing and
  • Publish/Subscribe Services in an SQL/MX COBOL Program
  • Lab 8.2 Dequeuing Services in an SQL/MX COBOL program
  • Lab 8.3 SQL/MX Queuing and

Module 9 SQL/MX Module Management

  • Describe module management
  • Use targeting, versioning, and grouping module management options
  • Lab exercise:
  • Lab 9.1 Module Management Using an SQL/MX COBOL Program
  • Lab 9.2 Module Management Using an SQL/MX C Program

Module 10 Managing SQL/MX Applications

  • Compile-time name resolution
  • Late-name resolution
  • Automatic recompilaton
  • Lab exercise:
  • Lab 10.1 Managing Embedded SQL/MX
  • COBOL Applications
  • Lab 10.2 Managing Embedded SQL/MX C Applications

Module 11 SQL/MX Transpose Clause

  • Transpose clause syntax and semantics
  • Lab exercise: Lab 11 SQL/MX Transpose Clause

Module 12 SQL/MX Sample Clause

  • Sample clause and semantics
  • Sampling methods: Random, First, and Periodic
  • Lab exercise: Lab 12 Sample Clause

Module 13 SQL/MX Sequence Functions

  • OFFSET, DIFF, RUNNING and MOVING functions
  • Lab exercise: Lab 13 Sequence Functions

Module 14 NonStop SQL/MX Query Optimization

  • Compilation phases
  • Data flow model
  • Physical operators
  • Type 1 and type 2 joins
  • Query analysis tools
  • Methods for influencing the optimizer
  • Lab exercise: Lab 14 SQL/MX Query Optimization

Onsite-Delivery Equipment Requirements

  • NonStop Server with G06-25 or later operating system installed
  • Instructor PC with: Window 2000 or XP PowerPoint software OutsideView
  • Projection system to display PowerPoint slides from the instructor PC

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.

Find dates and prices

Online booking is currently not available for this course, to find out more please call us on 0345 074 7998 or email us at info@qa.com to discuss how we can help.

Trusted, awarded and accredited

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

All third party trademark rights acknowledged.