Course announcements

  • Students that complete EDB775 and practice the skills learned will be able to develop and tune queries in an SAP IQ system. This course will teach you how to design, load, analyze, and manipulate data in IQ databases.

Goals

  • Implement an optimum table and index configuration through the use of best design practices, the Index Advisor, and database options
  • Understand the methods of loading and changing data
  • Analyze IQ data with OLAP functions
  • Program stored procedures, custom functions, and events
  • Understand the design and functionality of the IQ query engine and optimization process
  • Interpret query plans and timing diagrams
  • Identify friction points in query evaluation, table joins, and other plan operators
  • Describe parallelism in IQ
  • Influence the optimizer to improve query performance

Audience

  • Query Developers

Prerequisites

Essential

  • Understanding of database concepts and database fundamentals
  • Familiarity with any SQL dialect

Recommended

  • None

Course based on software release

  • SAP IQ 16 Service Pack 3

Content

  • SAP IQ Overview
    • SAP IQ Overview
  • Introduction to Watcom-SQL
    • Identify the differences between SQL dialects
    • Identify the language elements and conventions of Watcom-SQL
    • Describe IQ joins
  • Creating Tables
    • Create, drop, and alter tables
    • Create and drop indexes
    • Create, drop, and merge table partitions
    • Use temporary tables and system tables
  • Designing Indexes
    • Explain how indexes work in IQ
    • Recognize the best indexes to use based on query & data type
    • Use the Index Advisor to improve index designs
    • Monitor indexes to determine effectiveness
  • Manipulating Data
    • Load data into tables
    • Describe and resolve issues related to loading data
    • Use the UPDATE statement
    • Delete data from a table
    • Export data from an IQ database
  • Functions and Procedures
    • Identify the types of built-in functions supported by IQ
    • Create user-defined functions
    • Identify IQ procedures and how they are used
  • Analyzing Data
    • Explain the benefits of using the OLAP functions
    • Identify how to use GROUP BY ROLLUP and CUBE operators
    • Use windowed functions to analyze data
  • Query Processing Overview
    • Describe the IQ query engine architecture
    • Explain index-based evaluation
    • Explain data projection
    • Describe how data flows through a query tree
    • Identify query tree operators
    • Generate query plans
  • Timing and Execution
    • Create query timing diagrams
    • Identify segments of query execution to be tuned
  • Query Plan Nodes
    • Read a query plan
    • Describe the nodes of a query plan
    • Describe how predicates and indexes interact in queries
  • Joins
    • Explain join node functionality
    • Identify how joins are executed in a query
    • Identify join costs
  • Other Operators
    • Group By and equivalent nodes
    • Describe parallel query operators
  • Tuning
    • Controlling optimization with database options
    • Influencing optimization in queries