Chapter 7: Further Reading

IBM Official Documentation

DB2 for LUW

• IBM Db2 12.1 Knowledge Center: Aggregate Functions https://www.ibm.com/docs/en/db2/12.1?topic=functions-aggregate The definitive reference for every aggregate function DB2 supports, including less common ones like LISTAGG, XMLAGG, and REGR_ functions. Bookmark this page; you will return to it regularly.

• IBM Db2 12.1 Knowledge Center: GROUP BY Clause https://www.ibm.com/docs/en/db2/12.1?topic=queries-group-by-clause Covers GROUP BY syntax including ROLLUP, CUBE, and GROUPING SETS with formal syntax diagrams and behavior rules.

• IBM Db2 12.1 Knowledge Center: HAVING Clause https://www.ibm.com/docs/en/db2/12.1?topic=queries-having-clause The formal specification for HAVING, including its interaction with GROUP BY and aggregate functions.

• IBM Db2 12.1 Knowledge Center: Materialized Query Tables https://www.ibm.com/docs/en/db2/12.1?topic=tables-materialized-query Covers creating, refreshing, and optimizing MQTs. Essential reading if you manage reporting workloads on large databases.

DB2 for z/OS

• Db2 13 for z/OS: Column Functions (Aggregate Functions) https://www.ibm.com/docs/en/db2-for-zos/13?topic=functions-column-aggregate DB2 for z/OS uses the term "column functions" for what LUW calls "aggregate functions." The behavior is identical, but the documentation organization differs.

• Db2 13 for z/OS: Super Groups (ROLLUP, CUBE, GROUPING SETS) https://www.ibm.com/docs/en/db2-for-zos/13?topic=clause-super-groups Covers the z/OS-specific syntax and optimization behavior for advanced grouping. The syntax is compatible with LUW, but the optimizer implementation details differ.

Books

• Itzik Ben-Gan, T-SQL Querying (Microsoft Press, 2015) Chapters 7 and 8 cover aggregation, grouping, and windowing with a depth and rigor that transcends the SQL Server focus. The mathematical treatment of ROLLUP, CUBE, and GROUPING SETS is excellent and applies directly to DB2. The discussion of logical query processing order is the clearest in any SQL book.

• C.J. Date, SQL and Relational Theory, 3rd ed. (O'Reilly, 2015) Chapter 7 examines aggregation from a relational theory perspective, including the subtle issues with NULLs in aggregation and why SUM of an empty set is NULL rather than zero. Essential reading for understanding the why behind the behavior.

• Joe Celko, SQL for Smarties, 5th ed. (Morgan Kaufmann, 2015) Chapters on aggregate functions and GROUP BY provide hundreds of practical patterns, many derived from real-world consulting engagements. Celko's treatment of GROUPING SETS and CUBE is particularly thorough, with cross-tabulation examples.

• Sheryl Larsen and Susan Graziano, DB2 Developer's Guide, 6th ed. (IBM Press, 2017) Covers DB2-specific aggregation behavior, performance tuning for GROUP BY operations, and the interaction between aggregation and the DB2 optimizer. Includes z/OS-specific considerations.

Technical Articles and Papers

• Jim Gray, Surajit Chaudhuri, Adam Bosworth, et al., "Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals" (1997) The original research paper that introduced the CUBE operator. Readable and practical, with clear examples showing why existing GROUP BY was insufficient for OLAP workloads. Available online through academic paper repositories.

• IBM Redbooks: "DB2 for z/OS: Data Sharing in a Nutshell" (SG24-8480) While focused on data sharing, Chapter 7 covers performance considerations for aggregation queries in shared-data environments, including how GROUP BY interacts with parallel processing and coupling facility caching.

• IBM Redbooks: "Optimizing DB2 Queries with IBM Data Studio" (SG24-8296) Practical guide to using Visual Explain to understand how DB2 executes aggregation queries. Shows how to identify sort overflows, hash-based grouping, and index-only access for aggregate queries.

Online Resources

• DB2 SQL Cookbook by Graeme Birchall (free PDF) http://db2-sql-cookbook.org/ An extensive collection of DB2 SQL patterns organized by topic. The aggregation chapter includes dozens of examples ranging from basic COUNT/SUM to complex ROLLUP/CUBE patterns with detailed commentary on DB2-specific behavior.

• Stack Overflow — [db2] Tag https://stackoverflow.com/questions/tagged/db2 The [db2] tag on Stack Overflow contains thousands of aggregation-related questions with community-vetted answers. Search for "db2 group by rollup" or "db2 having vs where" for targeted results.

Topics to Explore Next

After mastering this chapter's content, these related topics will expand your aggregation capabilities: