The DB2Night Show Episode #Z132: Temporary Tables - Declare or Create?

The SQL concept of ‘temporary tables’ allows for creation of powerful SQL. Understanding the different types of temporary tables will allow the end-user the option to create useful SQL that may be relatively simpler to understand and allows great flexibility.

· DECLARED GLOBAL TEMPORARY TABLES (DGTT)
· CREATED GLOBAL TEMPORARY TABLES (CGTT)
· common table expressions (CTE)
· other forms of inline SQL

The presentation will attempt to clearly drive home the difference between DGTT and CGTT. I will share the experience of how a simple DGTT was used in a new high-volume transaction and cost was relatively high. Switching to CGTT fantastically reduced cost!

The presentation will also touch the concepts of Common Table Expressions (CTE) and “derived tables”. CTE allows one to write ‘big’ SQL that are easy to understand. CTE are my favorite SQL concept to exploit and use for producing ad-hoc complex SQL reports!