Db2 Database: Programming in Linux/UNIX/Windows Environments

This course is designed for those applications programmers, designers, analysts and DBAs who are new to Db2 for LUW, or who have not used DB2 for a long time. The course combines formal tuition sessions with a comprehensive workshop, which introduces all aspects of relational technology as implemented by Db2.

This course is available for one-company, on-site presentations.

What you will learn

On successful completion of this course you 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.

Prerequisites

Attendees should be familiar with developing or writing applications.

Duration

4 days

Fee (per attendee)

P.O.A.

Course Code

DBWU

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/DISTINCT/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.