Advanced Queries for DB2

CompanyPresentationsBooksConsultancyArticlesContact Us

 

 
[Home][Presentations][Advanced Queries]

 

 

Subtitle: Everything you always wanted to know about joins and subqueries

Introduction

With each version of DB2, IBM expands the query capabilities. Especially the features related to specifying subqueries, joins and groupings have been extended dramatically. For example, subqueries in the from- and select-clause are now allowed, and different types of joins have been added. The capabilities of the group by-clause have also been extended. We can even query deleted and updated records. For reporting many powerful featurues have been added such as rollup, cube, super-groups, and sequence numbers.

Unfortunately, out of habit, many DB2 programmers and DBA's still limit themselves to simple select statements, and are therefore not exploiting the full capabilities of those latest DB2 versions. This session teaches how those new features can and should be used.

Note: This session can be presented for DB2 for LUW and for DB2 for Z/OS.

Subjects

  • Subqueries in the from- and select-clause: why would you use them?
  • The logic of different types of joins, including outer-joins, joins on non-existing tables, and joins on subqueries
  • Grouping rows with cubes, rollups, grouping-sets, and super-groups
  • Recursive queries (for bill-of-material problems) made simple
  • When can the with clause replace views?
  • Multiple sorting operations in de select clause
  • Queries that do not access tables or views: why would you use them?
  • Generating test data out of thin air
  • The new Merge statement
  • How to ask for the deleted, updated and inserted rows?
  • Comparison of views and materialized query tables
  • How to write useful queries with double not exists

 

[Previous][Up][Next]

Copyright (c) 2009 R20/Consultancy B.V.. All rights reserved.