SAP SYBASE IQ FOR DEVELOPERS
Duration: 5 days
Goals:
1. Implement an optimum table and index configuration through the use of best design practices, the Index Advisor, and database options
2. Understand the methods of loading and changing data
3. Analyze IQ data with OLAP functions
4. Program stored procedures, custom functions, and events
5. Understand the design and functionality of the IQ query engine and optimization process
6. Interpret query plans and timing diagrams
7. Identify friction points in query evaluation, table joins, and other plan operators
8. Describe parallelism in IQ
9. Influence the optimizer to improve query performance
Content:
1. SAP IQ Overview
SAP IQ Overview
2. Introduction to Watcom-SQL
Identify the differences between SQL dialects
Identify the language elements and conventions of Watcom-SQL
Describe IQ joins
3. Creating Tables
Create, drop, and alter tables
Create and drop indexes
Create, drop, and merge table partitions
Use temporary tables and system tables
4. 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
5. 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
6. 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
7. 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
8. 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
9. Timing and Execution
Create query timing diagrams
Identify segments of query execution to be tuned
10. Query Plan Nodes
Read a query plan
Describe the nodes of a query plan
Describe how predicates and indexes interact in queries
11. Joins
Explain join node functionality
Identify how joins are executed in a query
Identify join costs
12. Other Operators
Group By and equivalent nodes
Describe parallel query operators
13. Tuning
Controlling optimization with database options
Influencing optimization in queries