DB2 SQL Application Programming
Course: DB2U
Duration: 5 Days
Level: I
On our website at:
http://www.verhoef-training.com/courses/DB2U.html
Course Summary
Suitable for the BCS Mainframe Technology professional Exams for level 2 Senior Developer.
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
- 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
- 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
- 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.
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.