Objectives

• use Command Center or other SQL execution environments (e.g. WinSQL) to code SQL statements
• use the System Catlaog
• describe and control the locking strategy used by DB2 to maintain data integrity
• list the different development environments available
• explain the use and implementation of Stored Procedures and UDFs using the Development Center
• list the different types of indexes and the use of Index Advisor
• use Visual Explain
• use and understand the Health Center
• describe the development and execution of Utilities
• explain the use of Journals
• describe the Replication Center
• explain the use of the Task Center
• describe the use the Event Analyzer
• explain the Indoubt Transaction Manager
• use the Memory Visualizer
• describe the Configuration Assistant.

Who Should Attend

All those who are exposed to, or are required to write and perform activities associated with application development and implementation, in a DB2 UDB Linux, UNIX, AIX or Windows environment.

Prerequisites

Those attending the course should be familiar with computing environments.

Duration

3 days

Fee

£1375 (ex. VAT)

Course Code

DBWA

Contents

Day 1

Introduction to DB2 under Linux/UNIX/Windows
SQL Compatibility feature; Versions; System architecture; Terms/Terminology; Tablespace: Regular/System temporary/User temporary/Long; Tables; Table partitioning ( 9); Views: Merge, Materialisation; MQT; Columns; Nulls; Referential Integrity; DB2 System Catalog; Buffer Pools, including Scan Sharing; Alias; Attributes: Numeric / Character, Date/Time, ROWID, IDENTITY/ SEQUENCE, UDT, LOBs, UNICODE, XML.

SQL Execution Environment
Command line processing; COMMAND CENTER; CONTROL CENTER (WIZARD).

Data Definition Language
CONNECT; DATABASE; TABLESPACE; TABLE; ALTER; TRUNCATE Table; Generate DDL; Set Integrity.
Followed by Practical Exercise 1.

Day 2

DML Consolidation
Complex SQL
Table joins; EQ JOIN; SUBSELECT; CORRELATED SUBSELECT; UNION.
Followed by Practical exercise 2.
Left/Right/Full Joins; In Line Tables; CASE; Remainder of DML statements: UPDATE/DELETE/INSERT/MERGE; CTEs.
Followed by Practical Exercise 3.

Embedded SQL
Static and Dynamic SQL; Cursors; Development cycle: Precompile, DBRMs, BIND, PLANS/PACKAGES, Execution of executable program.
Followed by Practical Exercise 4.

Locking Strategy
Introduction; What resources can be locked?; The different locks available; Duration of a lock; Commit/rollback; Locking notes.

Indexing
Physical data management; Tablespace Object; Page; Record identifier; Indexes Type 1(removed 9.7) and Index Type 2; Index Compression; Partitioned Indexes; RUNSTATS/REORG; MDC; Range Clustered Tables; Statistical Views; Access strategy; Multi table access path; EXPLAIN; VISUAL EXPLAIN.
Followed by Practical Exercise 5.

Day 3

Extra Facilities
OPTIMIZE FOR; HANDLING NULLS; TEMPORARY TABLES; Remote and Distributed Environments.
Followed by Practical Exercise 6.

Logs
Types of Logging; Configuring Database; Logging Information; DB2DIAG; Manual Reading of the Log; Snapshot & Event Monitor; TRACE DB2TRC; REORGCHK; Output Table Stats; Output Index Stats.
Followed by Practical Exercise 7.

Security
Levels Of Security; GRANT/REVOKE; Security levels (9); Table check constraints; TRIGGERS; STORED PROCEDURES; Utilities: BACKUP / RESTORE / LOAD, IMPORT/EXPORT.
Followed by Practical Exercise 8.

DB2 LUW Tools Overview
Design Advisor; Index Advisor; Activity Monitor; Memory Visualization; Health Center (Data Studio Administrative Console); Remote Administration; Replication Center; Task Center; Event Analyzer; Recommendation Adviser.