This course provides a detailed explanation of the methodology to be followed in order to design and implement efficient Db2 for z/OS databases. The course discusses all aspects of database design from the logical data model and implementation of the physical design, through to ongoing performance monitoring of the resultant database.<br><br>This course is also available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.
Database Management Systems; Hierarchical database structure; Network database structure; Relational database structure; Database comparison; Table structure; A brief history of Db2.
Relational Theory & ConceptsDr. E F Codd's 12 rules; The relational model; Structure - tables; Structure - rules for columns and rows; Structure - candidate keys; Structure - primary and alternate keys; Structure - foreign keys; Manipulation - relational algebra; Manipulation: projection, restriction, join, union, intersection, difference, product; Integrity - domain and user-defined integrity; Integrity - entity and referential integrity; Referential integrity - terminology; Tablespace set; Referential integrity - defining constraint; Constraint definition restrictions; Data manipulation restrictions; Catalog entries; Referential Integrity and INSERT; Referential Integrity and UPDATE; Referential Integrity and DELETE; Referential Integrity review.
Table DesignConventional file systems; Database systems; Data concepts; Entity-relationship diagram; Functional dependence; Normalisation; First normal form; Second normal form; Third normal form; Normalisation summary; De-normalisation; De-normalisation integrity exposures; Table creation; Db2 data types; Null attributes; Variable length columns; Variable length row formats; Large object data; XML data; Identity columns; GENERATED options; Column considerations; Constraint and domain management; Constraint catalog tables; Temporary tables; Created temporary tables; Declared temporary tables; Table comparisons.
Db2 Index Usage & DesignPredicates; Accessing data - table or tablespace scan; Sequential prefetch; Index structure; Index page splits; Using index - matching index scan; Using index - non-matching index scan; Using direct row access; Indexable and non-indexable predicates; Statement processing; Stage 1 and stage 2 predicates; Summary of predicate processing; Predicate evaluation sequence; List prefetch; Index lookaside; Index considerations; Creating indexes; Composite keys; Clustering; Variable length index keys; Index compression; Index key randomisation; Indexes on expressions; Non-key columns in unique index; Further considerations; Reorganising indexes; Hash access; Hash table structure; Defining a hash table.
Physical Database Design & Data Definition LanguagePagesets; Pageset structure; Tablespace types; Non-segmented tablespace; Segmented tablespace; Partitioned tablespace; Universal tablespace; Large object tablespace; XML tablespace; Data compression; Db2 data objects overview; Storage group; Database; Tablespace; Determining tablespace type; Table; Indexspace / index; Views; Synonym; Alias; Deleting objects; Performance considerations: space allocations, data set placement, free space, statistics, buffer pools.
Db2 Locking & ConcurrencyControlling concurrent access; Claims and drains; Claim classes; Drains; Utility restrictive states; Transaction locking; Reasons for locking - preventing lost updates; Reasons for locking - preventing reads of uncommitted data; Reasons for locking - allowing repeatable reads of data; Lock control; Lock options - installation parameters; Lock options - tablespace creation; Lock options - SQL statements; Locking hierarchy; Lock compatibility - row and page locks; Lock compatibility - table and tablespace locks; Lock options - BIND parameters; BIND - ACQUIRE and RELEASE parameters; BIND - ISOLATION; BIND - CURRENTDATA; Lock avoidance; Lock avoidance example; Locking and concurrency recommendations; Monitoring locking - Db2 commands; Monitoring locking - LOCKINFO; Monitoring locking - Db2PM / Db2PE reports.
Db2 EXPLAINEXPLAIN; EXPLAIN table enhancements; EXPLAIN syntax enhancements; PLAN_TABLE (Db2 version 1); PLAN_TABLE (Versions 2 to 6); PLAN_TABLE (Db2 versions 7 to 11); DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN example 1 (basic access paths); EXPLAIN example 2 (multi-index access); EXPLAIN example 3 (nested queries).
Join our public courses in our New Zealand facilities. Private class trainings will be organized at the location of your preference, according to your schedule.