DB2 SQL Application Programming

This course focuses on using efficient SQL on all platforms and using appropriate host languages in the business applications environment.

Application programmers and designers seeking a solid grounding in SQL and how it is used interactively and embedded in host language program using IBM's DB2 database under both z/OS and LUW platforms.  The course is delivered at the latest release level, previous releases can be covered on request.

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

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.

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.
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.
Nested table expressions.
Common table expressions (CTE's).
Resolving join performance issues.
Data modification:
• INSERT.
• UPDATE.
• DELETE.
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 Manager.
• RDS.
• Buffer Manager.

Explain / Optimization Service Centre
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.