Description
Course information
IBM code: CLA91G
Category: IBM Db2 / Advanced SQL
Modality: Online and Presential
Duration: 3 days
Level: Advanced
Certification: IBM Official
Who is it aimed at?
This advanced course is specially designed for:
- Database administrators who want to optimize the performance of their systems
- Experienced SQL developers looking for advanced techniques
- Data analysts working with large volumes of information
- Data architects designing complex solutions
- Technical consultants implementing business solutions in Db2
- Business Intelligence professionals who need to optimize their queries
Prerequisites
To take full advantage of this advanced training, participants must have:
- Solid experience in relational database concepts
- Ability to write basic and intermediate SQL queries
- Knowledge of joins, subqueries and aggregate functions
- Recommended: Completion of SQL Workshop Basic (CLA22G) or equivalent.
- Experience working in Db2 environments for Linux, UNIX or Windows
Learning objectives
At the end of this advanced course, participants will have mastered:
- Implement OLAP ranking functions (RANK, DENSE_RANK, ROW_NUMBER) for sophisticated analysis
- Optimizing queries using expression-based indexes for calculated values
- Master set operators such as UNION, EXCEPT, and INTERSECT for advanced data merging
- Leveraging summary tables and MQTs to precompute and store aggregated data
- Apply EXISTS, NOT EXISTS, IN and NOT IN predicates for efficient conditional retrieval
- Execute complex numerical and mathematical operations, including logarithmic and trigonometric calculations
- Implementing recursive SQL to process complex hierarchical relationships
- Using ARRAY data types to pass structured lists in queries and procedures
- Analyzing and optimizing SQL execution plans with EXPLAIN and VISUAL EXPLAIN
- Apply advanced join techniques such as hash joins and lateral joins
Complete syllabus
Module 1: OLAP Functions and Advanced Analysis
- Ranking functions: RANK, DENSE_RANK, ROW_NUMBER
- Window Functions for temporal analysis
- LISTAGG, PERCENTILE_CONT and PERCENTILE_DISC for statistics
- Partitioning and sorting in analytical functions
Module 2: Advanced Query Optimization
- Db2 system catalog navigation and analysis
- Indexing strategies for complex queries
- Indexes based on expressions for calculated values
- Predicate pushdown techniques
Module 3: Advanced Join Techniques
- Optimized inner and outer joins
- Hash joins for large data volumes
- Lateral joins for correlated queries
- Performance optimization in multiple joins
Module 4: Set Operators and Data Fusion
- UNION, INTERSECT and EXCEPT for comparative analysis
- Deduplication techniques and combination of results
- Optimization of queries with multiple sets
- Business data fusion case studies
Module 5: Advanced Subqueries and Predicates
- Correlated subqueries for complex analysis
- EXISTS/NOT EXISTS for existence check
- Quantified predicates (ANY, ALL, SOME)
- Performance optimization in nested subqueries
Module 6: Mathematical and Numerical Operations
- Logarithmic, exponential and trigonometric functions
- Advanced statistical calculations
- Precision handling and rounding
- Operations with complex dates and time
Module 7: Recursive SQL and Hierarchical Structures
- Recursive Common Table Expressions (CTEs)
- Hierarchical data processing
- Navigation in data trees
- Business use cases with recursive structures
Module 8: ARRAY Data Types and Advanced Structures
- Implementation and management of ARRAY types
- Structured parameter passing
- Manipulation of arrays in queries and procedures
- Performance optimization with arrays
Module 9: Summary Tables and MQTs
- Design and implementation of summary tables
- Materialized Query Tables (MQTs) for Optimization
- Refreshment and maintenance strategies
- Use cases in analytical environments
Module 10: Execution Plan Analysis
- EXPLAIN and VISUAL EXPLAIN tools
- Interpretation of consultation access plans
- Identification of bottlenecks
- Plan-based optimization strategies
Advanced practical methodology
Our approach is designed for experienced professionals seeking to master the most sophisticated techniques:
- Intensive laboratories with real optimization cases
- Performance analysis in simulated business environments
- Solving complex problems with large volumes of data
- Benchmarking of queries before and after optimization
- Case studies based on real implementations
- Simulation of critical scenarios and their resolution
Our differential value
At SIXE we train enterprise-level SQL experts. Our certified instructors provide:
- Real-world experience optimizing queries in production environments
- Advanced techniques developed in high performance projects
- Best practices for critical and high availability environments
- Exclusive documentation with techniques not officially documented
Training modalities
Online training
Intensive sessions with high-performance remote labs and access to enterprise Db2 environments. Perfect for senior professionals looking for flexibility without compromising technical quality.
On-site training
Immersive experience in our specialized labs with dedicated servers and enterprise configurations. Available at our locations:
Spain: Madrid, Barcelona, Valencia, Seville, Bilbao, Malaga
Latin America: Mexico City, Buenos Aires, Bogota, Santiago, Lima, Quito, and more
Latin America: Mexico City, Buenos Aires, Bogota, Santiago, Lima, Quito, and more
Become an advanced SQL expert in Db2
Become one of the professionals who master the most advanced SQL techniques. Very limited places in order to guarantee personalized attention.
