DB2 for z/OS SQL for Query Users

Objectives
On successful completion of this course, attendees will be able to:

• code SQL SELECT statements including those using techniques such as joins, nested table expressions, subqueries, unions and case expressions.
• code SQL INSERT, UPDATE and DELETE statements to modify data.

Who Should Attend
All of those requiring a thorough understanding of coding SQL data manipulation statements using a medium such as SPUFI or QMF. The course is not suitable for those needing to embed the SQL in application programs.

Prerequisites
Attendees should have experience with a relational database environment or have attended the Course DB2 Concepts.

Duration
3 days

Course Code
DSQL

Contents
DB2 SQL, DB2I and QMF overview
Basic SQL format; submitting SQL using DB2I and SPUFI, QMF overview.
Discuss the basics of SQL and the online environments from where it may be submitted.

Basic DML
Selecting all columns; row & column control; multiple conditions; special operators; the escape character; arithmetic in DML; using constants; special registers; concatenation, date and time columns
Provides sufficient knowledge to code basic SELECT statements.

SQL built-in functions
Column functions; scalar functions; 'group by' and 'having' clauses.
Expands on previous segment to introduce built-in functions.

Joins and nested table expressions
Joins; correlation names; Cartesian product; inner and outer joins; nested table expressions.
Introduces some of the more complex SQL code.

Union and case expressions
Union, union all, and case expressions.
Further expansion of the SELECT statement.

Subqueries
Simple subqueries; correlated subqueries; 'IN', 'NOT IN' 'NOT EXISTS'; quantified predicates, effect of nulls on SQL statements
Introduces the nesting of statements.

Table modification commands
Insert; inserting multiple rows; update and delete statements; the impact of referential integrity constraints.
Completes the DML syntax covering how to make changes to data

Access path evaluation and EXPLAIN
DB2 optimizer, EXPLAIN facility, available access paths, catalog columns affecting access path, EXPLAIN output, PLAN_TABLE, DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE contents.
This segment describes how the EXPLAIN feature may be used to ensure that SQL is performing as efficiently as possible.

Practicals
Hands-on exercises for basic DML, built-in functions, joins and unions, subselects and subqueries, table modification commands, and EXPLAIN.