18 May, 2012
Text Size

DB2 SQL Application Programming

Audience

Application programmers and designers seeking more in-depth knowledge about IBM's DB2 database under z/OS and LUW platforms (up to DB2 10 on z/OS and 9.7 LUW).

Prerequisites

The participant should have some knowledge of the relevant application language (eg. COBOL, Java, C++ etc...). Please inform Verhoef of the language and platform in use when booking the course.

Duration

5 days. Hands on.

Course Objectives

This course focuses on using efficient SQL on all platforms and using appropriate host languages in the business applications environment. Through a combination of lectures and a progressive series of workshops, the audience will gain a solid grounding in SQL and how it is used interactively and embedded in host language programs. Many other DB2 issues are also covered, including how to address performance concerns, exploring the system catalog, batch and interactive issues, and DB2 product enhancements in the latest versions.

Course Content

Introduction to DB2 and the Relational Model
Origin and history of the RDBMS and DB2.
Advantages and disadvantages of the Relational Model.
DB2's basic logical and physical objects.
Subsystems/Instances
Databases
Tablespaces
storage groups
tables
views
MQT
Temporal Tables
 
The SQL Language
ANSI standard SQL data-types and relational extenders.
Principles of SQL data-retrieval
Projection
Selection
Join
Basic SQL data-retrieval.

Boolean Operators
· ORDER BY
· GROUP BY
· HAVING
· UNION
· LIKE
· DISTINCT
· BETWEEN, etc.
Case Expressions
· Using Case in the Select list, and in the WHERE clause.
Built-In Functions
Aggregate functions
Scalar functions
User-defined functions
Full sets
Sub-Queries
Standard and correlated nested sub-queries.
Joins
Inner
Joins
Nested table expressions
Common table expressions (CTE's)
Resolving join performance issues
Data modification
INSERT, UPDATE, and DELETE statements
Rules of referential integrity
Introduction to SPUFI and/or QMF for z/OS or Control Center and/or Command Editor and/or Command Prompt for LUW
Data Studio
Hands on progressive SQL workshops

Embedded SQL
Host language variables and the DCLGEN facility.
Structured Error-handling techniques.
SQLCA information, WHENEVER, and related issues.
Working with NULLS.
Cursor Processing statements.
· DECLARE
· OPEN
· CLOSE
· FETCH
. ROWSETS
Working with Updateable cursors.
Commit and Rollback processing.
2-phase commit protocol.
Compiling embedded SQL programs.
Host language considerations.
The DB2 precompiler and BINDing
Options
Issues

Locking protocols, isolation levels, etc.
Stored Procedures.
When and how to utilise, in each applicable version.
The Development Center and cross-platform procedures.
Triggers

Performance Issues
Understanding the relationship between design and performance.
Normalization and de-normalization.
Row layout and free-space considerations.
Understanding the DB2 Optimizer.
Detailed examination of access paths in DB2.
Query and CPU parallelism.
RUNSTATS/REORG
Optimizer enhancements in the latest release.
Stage 1 vs. Stage 2 predicates.
Indexes.
Types of indexes

Clustered
Partitioned
Type of Index
Volatile

Advantages and disadvantages of indexes.

Locking and contention issues
Locking Strategy
Handling deadlocks
Update anomalies
Lock escalation
As Security Labels if applicable (z/OS only)
 
Overview of DB2 internals
DB2 internals from the standpoint of performance.
Examining
Data Manage
RDS
Buffer Manager
 

EXPLAIN / OPTIMIZATION SERVICE CENTER
Examining and interpreting EXPLAIN data
New EXPLAIN columns in the latest release.
DSN_STATEMNT_TABLE (z/OS only)
The DB2 System Catalog and History Tables
Examining the key metadata affecting optimisation decisions.

XML
XML Layout
Table Creation
Indexes
Xpath Functions
Programming with XML
XML utilities

Upcoming Database Courses

Course Contact Form

Please complete the form to enquire about running a course at your premises or one of our training facilities

Name:

Email:

Telephone:

Organisation:


Comment: