Description
IBM course code:CE131G
Delivery:Online, classroom or onsite
Duration:3 days (24 hours)
Platform:Cross-platform (Db2 z/OS, LUW, iSeries)
Who it is for:Application developers, DBAs, data analysts and technical support staff with basic SQL experience who need to master advanced SQL on Db2.
Recommended background:Hands-on experience writing and running basic SQL statements (equivalent to CE121G).
Delivery:Online, classroom or onsite
Duration:3 days (24 hours)
Platform:Cross-platform (Db2 z/OS, LUW, iSeries)
Who it is for:Application developers, DBAs, data analysts and technical support staff with basic SQL experience who need to master advanced SQL on Db2.
Recommended background:Hands-on experience writing and running basic SQL statements (equivalent to CE121G).
Who this course fits
- SQL developers with a solid base who will work with OLAP functions (RANK, ROLLUP, CUBE) and recursive CTEs.
- DBAs reviewing application SQL before a performance audit.
- BI engineers moving complex reports from another engine to Db2.
Course content
- Advanced SQL on Db2: SELECT clauses, Db2 platforms (z/OS, LUW, i), OLAP functions (CUBE, ROLLUP, RANK, DENSE_RANK, ROW_NUMBER)
- Creating and managing objects: DDL, ALTER, triggers (BEFORE/AFTER/INSTEAD OF), CHECK constraints
- Advanced joins: INNER, LEFT, RIGHT, FULL OUTER, anti joins, self joins, set operators (UNION, UNION ALL, EXCEPT, INTERSECT)
- CASE, CAST, summary tables and MQTs (materialised query tables)
- Subqueries with ALL, ANY/SOME, EXISTS; correlated subqueries
- Scalar functions: SUBSTR, POSSTR, COALESCE, DECIMAL, ROUND, CHAR, DATE, TIME
- CTEs (WITH) and recursive SQL; nested table expressions
- UDTs, UDFs and stored procedures; query tuning and index structure
Course agenda
- Day 1: advanced SQL, object management, triggers and constraints
- Day 2: advanced joins, CASE/CAST/MQTs, subqueries, scalar functions
- Day 3: CTEs and recursive SQL, UDTs and UDFs, and performance tuning
Training with SIXE, IBM Business Partner
- Labs on Db2 z/OS, LUW or iSeries, depending on where your team will write the SQL.
- Taught directly by IBM Champion instructors from the SIXE team.
- Credly badges available: credly.com/organizations/sixe/badges
- Flexible format — online, classroom or onsite — in closed groups, priced per attendee, in the language of your choice (ES / EN / FR).
Instructors specialised in IBM Db2 and advanced SQL
Engineers from the SIXE team with real experience tuning complex SQL — OLAP, CTEs, MQTs — on Db2 z/OS and LUW. Several recognised as IBM Champion.
Frequently asked questions
Do I need CE121G first?
CE121G or equivalent experience writing intermediate SQL. Joins, grouping functions and simple subqueries are assumed.
Does it apply to Db2 z/OS, LUW and iSeries?
Yes, it is cross-platform. The platform-specific differences are covered in class.
Group size and language?
Closed groups of four to ten people, in Spanish, English or French. We run it with fewer than four as well, though it usually pays to wait and fill the group.
Related courses
- CV964G — Db2 12 for z/OS SQL Performance and Tuning (tuning SQL on the mainframe)
- CLA96G — IBM Db2 12 Foundation for Relational DBAs (if the role moves to LUW administration)
- CE121G — Db2 SQL Workshop (groundwork, if the team needs levelling first)
