DB2 SQL Application Programming

Course: DB2U

Duration: 5 Days

Level: I

Course Summary

Suitable for the BCS Mainframe Technology professional Exams for level 2 Senior Developer.

« Hide The Details

Topics Covered In This Course

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

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

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
Resolving join performance issues
Data modification
INSERT, UPDATE, and DELETE statements
Rules of referential integrity
CTE's

Introduction to SPUFI and/or QMF for z/OS or Control Center and/or Command Editor and/or Command Prompt for LUW

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
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/ VISUAL EXPLAIN

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 optimization decisions

What You Can Expect

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 versions 8 and 9.

Who Should Take This Course

Application programmers and designers seeking more in-depth knowledge about IBM's DB2 database (versions 8 and 9) under z/OS and LUW platforms.

Recommended 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.

« Hide The Details

Related Courses

Code	Course Title	Duration	Level
DB2UDBI	DB2 for LUW (Linux, UNIX, Windows)	2 Days	I	Details
DB2LUW	DB2 LUW (Linux, UNIX, Windows) FOR PROGRAMMERS	4 Days	I	Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.

Notify me the next time this course is confirmed!

Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]