DB2 SQL Workshop for Experienced Users

Description

At SIXE, we have been delivering official IBM training worldwide for over 15 years. Get the best advanced SQL for DB2 training, delivered by our specialist engineers across Europe and Latin America.

Course details: Advanced SQL for DB2 (CE131)

Who should attend this advanced DB2 SQL course?

This intermediate course is designed for professionals already working with SQL who need to advance their skills in IBM DB2 environments. It is ideal for application developers, database administrators (DBAs), data analysts and technical support staff who require advanced SQL proficiency in DB2.

Prerequisites

Participants should have hands-on experience coding and executing basic SQL statements. These skills can be acquired by completing the DB2 SQL Workshop (CE120) or through equivalent real-world experience.

Our instructors: specialised IBM DB2 engineers

The vast majority of our IBM courses are delivered directly by engineers with real-world experience in DB2 deployments, migrations and administration. This allows us to offer practical, problem-oriented training. We supplement all training with proprietary materials and hands-on labs developed from years of enterprise project experience.

The SIXE difference

Our courses are tailored to the specific role of each participant. We understand that a development team’s needs differ significantly from those of infrastructure and operations teams. Beyond theory, we focus on solving the challenges that arise in day-to-day work, providing the competencies and skills required for each project. All our documentation is based on the latest available version of DB2.

Course syllabus: Advanced SQL for DB2 (CE131)

1. Introduction to advanced SQL in DB2

• SELECT statement clauses: advanced purpose and usage of each clause in the SELECT statement.
• IBM DB2 platforms: key differences between DB2 platforms (Db2 for z/OS, Db2 for LUW, Db2 for i).
• OLAP functions in DB2: advanced grouping functions (CUBE, ROLLUP) and analytical functions (RANK, DENSE_RANK, ROW_NUMBER).

2. Creating and managing DB2 objects

• Tables and views: DDL statements to create and manage tables and views in DB2.
• Altering tables and indexes: modifying structures, creating indexes and defining referential integrity (RI).
• Triggers and constraints: defining triggers (BEFORE, AFTER, INSTEAD OF) and check constraints (CHECK).
• Referential integrity impact: cascade delete rules and practical considerations for triggers and constraints.

3. Advanced joins in DB2

• Inner and outer joins: retrieving data from multiple tables using INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN.
• ANTI JOINS: techniques to identify unmatched records between tables.
• Self-joins: joining a table to itself for hierarchical and comparative queries.
• Set operators: using UNION, UNION ALL, EXCEPT and INTERSECT.

4. CASE, CAST, summary tables and MQTs

• CASE expressions: using CASE in SELECT lists and WHERE clauses for conditional logic.
• CAST specifications: data type conversion with CAST.
• Materialized Query Tables (MQTs): creating and using MQTs to improve performance of frequently run queries.
• Temporary tables: using global and declared temporary tables in DB2.

5. Subqueries in DB2

• Subqueries with ALL, ANY/SOME and EXISTS: writing scalar, row and table subqueries.
• Correlated subqueries: techniques for subqueries that reference the outer query.
• Choosing the right subquery type: criteria for selecting the most efficient subquery for each scenario.

6. Scalar functions in DB2

• Data manipulation: advanced functions for arithmetic data, date values and character data.
• Key functions: SUBSTR, POSSTR, COALESCE/VALUE, DECIMAL, ROUND, DIGITS, CHAR, DATE, TIME and more.

7. Common Table Expressions (CTEs) and recursive SQL

• Common Table Expressions (CTEs): WITH syntax to simplify complex queries.
• Nested table expressions: using subqueries in the FROM clause.
• Views vs. CTEs: when to use each approach.
• Recursive SQL: writing recursive queries and controlling recursion depth.

8. User-defined types/functions and performance tuning

• UDTs, UDFs and stored procedures: concepts and practical applications in DB2.
• Query optimisation: predicting index usage and improving query performance.
• Predicate processing: understanding predicate evaluation order in DB2.
• Index structure: fundamental indexing concepts and general best practices for DB2 performance tuning.

This comprehensive syllabus equips participants with advanced SQL skills for DB2, tailored to their roles and project requirements.

Customise your DB2 training

Need to tailor this syllabus to your team’s requirements? Interested in other official IBM courses? Contact us and we will design a bespoke training plan.

On-site training locations

Europe

• Spain: Madrid, Seville, Valencia, Barcelona, Bilbao, Málaga
• Portugal: Lisbon, Braga, Porto

Latin America

• Argentina: Buenos Aires, Córdoba
• Bolivia: La Paz
• Chile: Santiago de Chile
• Colombia: Bogotá, Medellín, Cartagena, Cali
• Costa Rica: San José
• Ecuador: Quito
• Mexico: Mexico City, Guadalajara, Monterrey
• Paraguay: Asunción
• Peru: Lima
• Uruguay: Montevideo