Target Audience
This training course teaches the delegate how to write efficient SQL statements that can be used to read, manipulate and join DB2 tables. It is aimed at those who have little or no previous SQL experience.
Pre-requisites
A working knowledge of the DB2 host environment is advantageous but not essential.
Course Objectives
The aim of this course is to provide the delegate with the necessary skills to perform simple and complex queries using the DB2 for LUW workbench.
On completion of this course the student will be able to:
-
describe the main objects that make up the DB2 environment
-
describe the data types available when defining DB2 columns
-
describe the importance of an Index for certain queries
-
use the Command Line Processor, Command Window or Command Centre to run queries
-
write SELECT, UPDATE, DELETE and INSERT SQL statements
-
join tables together
-
use inner joins and outer joins
-
write non-correlated and correlated subqueries
-
use DB2 functions and the CASE statement
-
understand the issues that determine SQL performance
-
write efficient queries
-
produce EXPLAIN output
Course Environment
The course applies to DB2 running in a Linux, Unix or Windows environment.
Course Details
- GETTING STARTED WITH DB2 for LUW
- The LUW Components
- DB2 Interactive Tools
- What is An Instance?
- The Structure of DB2 Objects
- Definition of DB2 Objects - DDL
- What is An Database?
- Catalog Tables
- What is An Tablespace?
- Table Placement
- Page and Row Organisation
- Table Definition
- Copying Table Definitions
- Column Types
- BIGINT
- INTEGER
- SMALLINT
- DECIMAL
- CHAR
- VARCHAR
- DATE
- DATE formats
- TIME
- TIMESTAMP
- CLOBs and BLOBs
- Null Values
- User Defined Default Values
- The Alter Table Statement
- The Importance of Indexes
- The Drop Statements
- DATA MANIPULATION LANGUAGE
- SQL - Structured Query Language
- SQL Features
- SQL Query Results
- The SELECT Statement
- The 'As' Clause
- Column concatenation
- Expressions
- Functions
- Special Registers
- The WHERE Clause
- Special Operators
- NOT Operand
- IN Operand
- LIKE Operand
- BETWEEN Operand
- Statements Using NULLS
- Column Functions
- SQL Built-In Functions
- Using 'DISTINCT'
- The GROUP BY Clause
- Additional GROUP BY Features
- GROUP BY ROLLUP
- The GROUPING Function
- GROUP BY CUBE
- GROUP BY Grouping Sets
- The HAVING Clause
- The ORDER BY Clause
- Fetch First 'n' Rows Only Clause
- Special Registers
- Current Date
- Current Time
- Current Timestamp
- User Keyword
- The UPDATE Statement
- Update with Subselect
- The DELETE Statement
- The INSERT Statement
- The Mass Insert Statement
- The VALUES Statement
- Functions
- Scalar Functions
- Columns Functions
- Date, Time and Timestamp Functions
- The CASE Statement
- Joins
- Inner Joins
- Cartesian Joins
- Outer Joins
- Left Join
- Right Join
- Full Join
- Outer Join - Where Clause
- Nested Table Expression
- UNION
- UNION and UNION ALL
- INTERSECT and EXCEPT
- Subqueries
- Non Correlated vs Correlated
- Subqueries Using In
- EXISTS
- The 'All' Subquery
- The 'Any' Or 'Some' Subquery
- Common Table Expressions
- Common Table Expression Example
- Recursive SQL
- Recursive SQL Example
- Recursive SQL - Controlling Depth of Recursion
- RUNNING SQL STATEMENTS
- The Control Center
- The Command Center
- Command Center Settings
- The Access Plan Panel
- The Command Line Processor
- The Command Window
- The Command Center
- CLP Syntax
- On-Line Help
- Interactive / Non-Interactive Modes
- CLP Option Flag
- CLP Termination
- WRITING EFFICIENT SQL STATEMENTS
- SQL Explain Tools
- Explain Tables
- The DB2EXPLN Tool
- Interpreting Explain Output
- The DB2ADVIS Tool - Index Advisor
- The Visual Explain Tool
- The DYNEXPLN Tool
- Access Paths Overview
- Tablespace Scan (Relational Scan)
- Non-Matching Index Scan
- Matching Index Scan
- Multiple Index Access
- Index Only Access
|