At a glance
View schedule & enroll Sorted by: location or date
Course number U8529S
Length 5 days
Delivery method Virtual Instructor-Led Training (VILT)
Instructor-led training (ILT)
Onsite dedicated training (OST)
Price USD $3,500
CAD $3,850
*Courses are supported in the delivery formats above, but are not necessarily scheduled in every delivery format listed. Please click the schedule links at the top of the page to see which delivery formats are currently scheduled.

Course overview

This course analyzes a variety of SQL/MX query plans and their features, such as the data flow architecture, process boundaries, query plan parallelism, and executor functionality in DP2. Topics also include how rowsets, compound statements, Referential Integrity (RI) constraints, and triggers are implemented in a query plan. Learn to display and analyze query plans and the methods for forcing (changing) query plans. The 5-day course is 60 percent lecture and 40 percent hands-on labs using HP servers.


  • HP NonStop SQL/MX basics (U4184S)


  • Anyone responsible for SQL/MX query analysis, tuning, and programming

Ways to save

Course Objectives

  • Understand and use tools to analyze a variety of SQL/MX query plans
  • Learn the methods to influence or modify SQL/MX query plans with Control Query Default and Control Query Shape statements
  • Understand some of the opportunities for tuning SQL/MX queries in the following areas: query design, application design, and database design
  • Become familiar with SQL/MX query plan fundamentals, such as query compilation phases, query tree operators functions, data flow architecture, process boundaries, and parallel execution of a query plan

Benefits to you

  • Analyzing a variety of SQL/MX query plans: the operators, data flow, and parallelism
  • Utilizing query analysis tools: Visual Query Planner, Explain Function, and Display_explain statement
  • Modifying query plans using Control Query Defaults and Control Query Shape

Course outline

  • Module 1: SQL/MX Query Fundamentals
    • NonStop SQL/MX process architecture
    • SQL/MX compilation phases
    • Branch and bound optimization
    • Query tree data flow model
    • SQL/MX executor and its data flow model
    • Forms of parallel execution used in SQL/MX query plans
  • Module 2: Viewing Database Object Definitions
    • SQL/MX database object characteristics
    • Primary and clustering keys
    • Range and hash partitioning
    • Referential Integrity (RI)
    • Triggers
    • Utilities to display database definitions
    • Lab: viewing database object definitions
  • Module 3: Viewing SQL/MX Query Plans
    • Visual Query Planner
    • Explain function and Explain command
    • Lab: viewing SQL/MX query plans
  • Module 4: File Scan and Exchange Operators
    • Basic operators that form a query plan: root operator, file scan operators, and exchange operators
    • Statement atomicity
    • Exchange operators and process boundaries
    • Lab: analyzing basic query plans
  • Module 5: Index Scan Operators and MDAM
    • Index scan operators and their tokens
    • Alternate index access path
    • OR optimization
    • MultiDimensional Access Method (MDAM)
    • Forcing an access path to a table or index
    • Lab: analyzing queries that use index scan operators and MDAM
  • Module 6: SQL/MX Delete Operators
    • Delete operators and their tokens
    • Query plans that use delete operators to delete rows in a table with and without indexes
    • Lab: analyzing delete queries
  • Module 7: Update Operators
    • Update operators and their tokens
    • Query plans that use update operators to update rows in a table with and without indexes
    • Lab: analyzing update queries
  • Module 8 - Insert Operators
    • Insert operators and their tokens
    • Query plans that use insert operators to insert rows in a table with and without indexes
    • Lab: analyzing insert queries
  • Module 9: Non-parallel joins
    • Join operators (nested join, merge join, ordered hash join, and hash join) and their tokens
    • Query plans that use join operators
    • System defaults for controlling joins
    • Control Query Shape statement for joins
    • Lab: analyzing nonparallel join queries
  • Module 10: Parallel joins
    • Type 1 parallel joins; matching partitions, logical partition grouping, logical sub partitioning, range repartitioning, and hash repartitioning
    • Type 2 parallel joins: replicate no broadcast and replicate via broadcast
    • System defaults for controlling ESP parallelism
    • Lab: Analyzing parallel join queries
  • Module 11: Subquery Operators
    • Semi join and anti semi join operators and their tokens
    • Query plans that use the semi join and anti semi join operators
    • Lab: analyzing subqueries
  • Module 12: Sorting and Grouping Operators
    • Sort, sort groupby, hash groupby operators and their tokens
    • Sort partial aggr, sort scalar aggr, and shortcut scalar aggr operators and their tokens
    • Query plans that use the sort and groupby operators
    • Lab: analyzing queries that use sorting and grouping operators
  • Module 13: Rowsets and Compound Statements
    • Rowset operators and tokens
    • Compound statements
    • Query plans that use input and output rowset arrays and compound statements
    • Labs: rowsets operators and compound statements
  • Module 14: Referential Integrity and Triggers
    • Referential Integrity (RI)
    • Triggers
    • Insert, update, and delete queries on tables that have referential integrity constraints and triggers
    • Labs: referential integrity and triggers
  • Module 15: Forcing Query Plans
    • Analyzing query plans
    • Changing and modifying query plans
    • Forcing query plans
    • Lab: forcing query plans
  • Module 16: Tuning
    • Properties and costs
    • TP queries and OLT optimizations
    • Basic query, application, and database design for better performance
  • Onsite-Delivery Equipment Requirements
    • HP NonStop S72000 server or later that supports 1 GB of memory and IEEE floating point
    • H06.22 or later operating system with SQL/MX 3.0+ and DDL licensing product T0394
    • Student desktop software:
      • Access to NonStop lab server using a terminal emulator such as MrWin6530 or OutsideView
      • Visual Query Planner
      • NonStop ODBC MX 3.0+ Administrator

U8529S - D.00