DB2 UDB for Linux/UNIX/Windows Programmers

Objectives
On successful completion of this course, attendees will be able to:

• explain optimal table design
• choose optimal index design
• reduce query elapsed times via efficient SQL design
• choose optimal SQL code
• select optimal locking strategy
• describe package and plan implications
• explain the implementation and use of DB2 object-oriented facilities.

Who Should Attend
Programmers, applications developers, and designers who are exposed to, or required to write and perform activities associated with application development and implementation in a DB2 environment.

Course Code
DBWU
Contents
Day 1 - Introduction to DB2
Versions: system architecture; terminology: instance/database, tablespace, tables, views, columns, nulls, referential integrity, Buffer Pools, alias, attributes: SQL Execution Environment: Command line processing, COMMAND CENTER, CONTROL CENTER(WIZARD); Data Definition Language: CONNECT, DATABASE, TABLESPACE, TABLE, ALTER.


Day 2 - DML
*/ALL/COLUMNS/DISTICT/CONCATENATOR/literals/ORDER BY; FETCH FIRST; Efficiency aspects; Predicates; Built in function; Scalar Functions; Examples and use of some of them e.g. SUBSTR/VALUE etc.; Complex SQL; Table joins: EQ JOIN, SUBSELECT, CORRELATED SUBSELECT, UNION; Left/Right/Full Joins; In Line Tables; CASE; Remainder of DML statements; UPDATE/DELETE/INSERT.


Day 3 - Embedded SQL
Error handling; Whenever; SQLCODE/SQLSTATE test; Singleton selects; uncursored SELECTS (Singleton SELECTS); scrollable cursors; CURSOR; CURSOR WITH UPDATE/DELTE; development cycle; precompile; DBRMs; BIND; PLANS/PACKAGES; execution of executable program; LOCKING STRATEGY; Introduction; what resources can be locked?; the different locks available: duration of a lock, commit/rollback, locking notes.


Day 4 - Indexing
Physical data management; Tablespace Object; Page; Record identifier; Indexes Type 1/2; RUNSTATS/REORG; MDC; access strategy; multi table access path; EXPLAIN; VISUAL EXPLAIN; extra facilities; READ/FETCH ONLY; OPTIMIZE FOR; HANDLING NULLS; TEMPORARY TABLES; GRANT/REVOKE; table check constraints; TRIGGERS; STORED PROCEDURES; utilities; BACKUP; RESTORE; LOAD; IMPORT/EXPORT.