Course ID 00086597
HP product number U8529S
Course format ILT
Course length 5 days
Skill level Expert
Delivery languages English
Related certifications
In preparation for these exams
Selected items from this course are included in these exams:
Register for this course.
Find this course in the Training calendar and click the “Register” link in the last column to begin your registration.

Course description

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 course is 60 percent lecture and 40 percent hands on labs using HP servers.

Who should attend

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

Prerequisites

For complete prerequisites and requirements to achieve any of the related certifications or upgrade paths, see the certification description on the HP ExpertOne website.

Topics

  • 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 group by, 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

Objectives

After completing this course, you should be able to do the following:
  • Understand and use tools to analyze a variety of SQL/MX query plans
  • 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
  • Understand 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

How to register

View the HP ExpertOne Global Training Calendar to register for the trainings offering that best meets your needs.

Policies, fees and cancellations

Course fees may vary and are established and collected by the training center delivering the course. Cancellation fees may apply. Contact your HP Authorized Training Partner for their respective policies.