DB20 - DB2 z/OS Database Design - 3 Days

Virtual or On-site course enquiry (from £165 pp per day)

Course Description

This course provides advanced DB2 training, and is aimed at staff requiring a detailed knowledge of the DB2 Logical and Physical design issues, and training in how efficient DB2 applications are implemented and maintained.

Pre-requisites

Familiarity with using DB2 on a z/OS platform is advantageous but not essential.

Objectives

The course looks at the wider and more advanced issues applicable to efficient DB2 design, including Logical and Physical Database design issues, locking strategies, and the definition of database constraints. The course includes many practical sessions, designing, implementing, and refining a DB2 system.

On completion of this course the student will be able to:

use design techniques such as Normalisation
understand the performance issues involved in constructing a system
implement an efficient tablespace, table and index design
choose the correct locking strategy
implement referential and table check constraints
use Identity Columns & Sequences
design and create Materialized Query Tables
design and create triggers
understand the performance issues involving the use of large objects

Environment

The course runs in the following environment:

IBM Mainframe
DB2 for z/OS Versions 10, 11 or 12

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

THE DESIGN PROCESS: Introduction - The Basic Elements of a Database; The Flat File Database; The Hierarchical Database; The Relational Database; Relational Database Advantages; Database Design Phases; Key Roles in the Design Process
CONCEPTUAL DESIGN: Why Analyse?; Who Will be Involved in the Analysis?; Entities; Entity Relationships - A Worked Example; Entity Relationship Diagrams; Relationship Types; Many to Many Relationships - Association Entities; Common Parentage; Attributes
LOGICAL DATABASE DESIGN: Overview of Normalisation; Benefits of Normalisation; Primary and Foreign Keys; Primary Key Considerations; The Normalisation Process; First Normal Form; Second Normal Form; Third Normal Form; Fourth Normal Form; Fifth Normal Form; Normalisation Check - Homonyms and Synonyms; Conversion of Entities to Base Tables; Attributes; Identification of Keys
PHYSICAL DATABASE DESIGN: Introduction; Gathering of Required Information; Data Flow Diagrams; Access Path Requirements; Entity Life Histories; Data And Relationship Volumes; Denormalisation Considerations; Denormalisation Techniques; RI Requirements; Index Requirements; Locking Requirements; Locking Considerations; View Requirements
DATABASE DESIGN: The Structure Of Db2 Objects; Definition Of Db2 Objects - Ddl; Storage Groups; Databases
TABLESPACE DESIGN: Tablespaces; Pages; Tablespace Organisation; Page And Row Organisation; The Simple Tablespace; The Segmented Tablespace; The Partitioned Tablespace; Maximum Number Of Partitions; Create Tablespace Parameter Reference; Version 9 - Universal Tablespaces; Universal Tablespace Benefits; Partition-By-Growth Tablespace; Creating A Partition-By-Growth Tablespace; Partition-By-Growth Tablespaces - Behaviour; Partition-By-Growth - Partition Allocation; Partition-By-Growth Tablespaces - Insert; Partition-By-Growth Tablespaces - Reorg; Partition-By-Growth Tablespaces - Other Utilities; Partition-By-Range Tablespace; Partition-By-Range Tablespaces - Considerations; Universal Tablespaces - Catalog Information; Storing Large Objects; The Lob Tablespace; LOB Base Table Definition; LOB Tablespace Definition; LOB Auxiliary Table Definition; LOB Auxiliary Table - Index Definition; LOB Enhancements – Inline LOBS; Data Compression; Compress on Insert / Merge / Load; Pending Changes
TABLE DESIGN: Tables; Copying Table Definitions; Rename Table; Db2 Column Types; Design Tips for Columns; Implicitly Hidden Columns; Reordered Row Format; Moving to Reordered Row Format; Null Values; Nulls - Design Tips; Variable Timestamp Precision; Variable Timestamp Precision – Current Timestamp; Variable Timestamp Precision – Row Change Timestamps; Implicit Time Zone; Timestamp with Time Zone – Assignment and Comparison; Global Temporary Tables; Declared Temporary Tables; Declared Temporary Table Considerations; Declared Temporary Tables - Comparisons; Db2 Synonym; Db2 Alias; Views; Read Only Views; Views - With Check Option; Creating A View Of Two Tables; View Materialisation; View Design Considerations; Altering a Table; Altering Tables and Indexes; Data Type Changes; Alter Data Type - Performance Implications; Column Renaming; Column Renaming Restrictions; The Drop Statements; Create Table - Implicit Database Creation; Implicit Database Creation - Considerations; Create Table - Implicit Tablespace Creation; The Truncate Statement; Truncate Examples; Hash Tables; Tablespaces for Hash Tables; Creating a Hash Table
CLONE TABLE FUNCTIONALITY: Clone Tables - Fast Data Replacement; Why Use Clone Tables?; Creating A Clone Table; Dropping A Clone Table; Clone Table Considerations; Creating a Clone - Catalog Activity; Creating a Clone - Index and Lob Catalog Activity; Clone Tables - The Exchange Command; Clone Tables - Exchange Considerations; Clone Tables - Locking Considerations; Clone Tables - DB2 Commands; Clone Tables - Authority
INDEX DESIGN: Db2 Indexes; Index Organisation - The B Tree Index; Backwards Index Scan; Index Clustering; Changing the Clustering Sequence; Non-Unique Indexes; Defining An Index; Index Definition - Parameter Reference; Partitioning Indexes; Index Page Size; Index Page Size Specification; Index Compression; Index Compression Considerations; Index Compression - Estimation; Index Compression - Dsn1 Sample Output; Table vs. Index Compression Comparisons; Index On Expression; Index On Expression Considerations; Index Design Considerations; Altering a Column's Data Type - Impact Upon Indexes; Alter Data Type - Index Availability; Index Renaming
DESIGNING PARTITION MANAGEMENT: Increased Number Of Partitions; Maximum Number Of Partitions; Considerations when Partitioning; Data Set Names; Table-Controlled Partitioning; Table-Controlled Partitioning Example; Altering a Table to Add Table Partitioning; Converting from Index to Table Partitioning; Table-controlled Partitioning Catalog Changes; Index-controlled Partitioning Terminology; Table-controlled Partitioning Terminology; Index classification; Partitioned / Non-Partitioned Example; Partitioned / Partitioning Example; Clustering; Clustering Within Partition; Changing the Clustering Sequence; Data Partitioned Secondary Indexes; Creating a DPSI; Design Considerations - Why Partition At All?; Design Considerations - Non-Partitioned Index Problems; Design Considerations - DPSI Benefits; DPSI Benefits - Partition Pruning; Design Considerations - DPSI Problems; DPSIs and Utilities; DPSIs and Planning; Partition Management; Adding Partitions; Adding Partitions - Considerations; Index-controlled to Table-controlled partitioning; Rotating Partitions; Rotate Partition Syntax; Rotating Partitions - Considerations; Altering Partition Boundaries; Rebalancing Partitions using Reorg; Rebalancing Partitions - Considerations; Considerations for User Applications; Display Database Command - Increased Partition Support; Display Database Examples
TEMPORAL TABLES: Temporal Tables and Versioning; Temporal Tables – Versioning Example; System Temporal Tables – Data Access; Temporal Tables – Considerations; Temporal Tables – Application Controlled; Business Temporal Tables – Data Selection; Business Temporal Tables – Update and Delete; Business Temporal Tables – Update Example; Business Temporal Tables – Delete Example
DESIGNING SYSTEMS WITH IDENTITY COLUMNS AND SEQUENCES: Identity Columns; Identity Columns - Examples; Altering Identity Columns; Altering Identity Columns - Parameters; Identity Columns - Retrieving the Generated Number; Identity Columns - Data Sharing Implications; Using Identity Columns with the Load Utility; Sequences; Create Sequence Syntax; Sequence Ordering; Altering Sequences; Dropping Sequences; Using Sequences in Applications; Sequences - Considerations and Restrictions; Sequence Application Examples; Sequences and Identity Columns Comparison
TRIGGERS: Triggers; Trigger Parts; Before and After Triggers; Trigger Examples; Allowable Combinations; Error Handling; Trigger Cascading; Ordering of Multiple Triggers; Trigger Authorisation; Catalog Information for Triggers; Removing Triggers
REFERENTIAL INTEGRITY: What Is Referential Integrity?; Parent and Dependent Tables; The Primary Key; The Foreign Key; Referential Constraint Rules; Constraint Names; More Complex Referential Structures; Check Pending Status; Resetting Check Pending Status; Referential Integrity Access; Design Considerations
TABLE CHECK CONSTRAINTS: Overview of Check Constraints; Constraint Syntax; Allowable Constraints; When are Constraints Enforced?; Current Rules; When Is Check Pending Set; Catalog Changes; Authority Changes
MATERIALIZED QUERY TABLES: What Are Materialized Query Tables?; MQT Features; Creating an MQT; Create MQT Example; Altering an MQT; Alter MQT Example; MQT Fullselect Features / Restrictions; Refresh Table; Populating User Maintained MQTs; Automatic Query Rewrite using MQT; Enabling Automatic Query Rewrite; Enabling Automatic Query Rewrite - DDL Options; Enabling Automatic Query Rewrite - Special Registers; AQR - Using Both Registers; Enabling Automatic Query Rewrite - System Properties; Enabling Automatic Query Rewrite - Query Properties; Determining if Query Rewrite Occurred; MQTs and Referential Integrity; MQTs and RI - Informational Constraints
DATABASE SECURITY DESIGN: System Privileges; Database Privileges; Use Privileges; Table Privileges; Plan / Package Privileges; Primary, Secondary And Current Authids; Implicit Privileges Of Object Owners; Revoking Privileges; DB2 Multilevel Security - Seclabel Definition; Seclabel Behaviour; Seclabel Behaviour with Sql; The Existing 3 Tier Security Model; 3 Tier Problems; Security Enhancement - Trusted Contexts / Roles; Trusted Contexts; Creating Trusted Contexts; Roles; Trusted Context / Role Examples; Row and Column Access Control; Scalar Functions for Row / Column Permissions; Row Access Control - Defining Row Permissions; Activating Row Permissions; Column Access Control - Defining Column Masks; Activating Column Masks; Row and Column Access – Restrictions; Row and Column Access – Explain Output; Row and Column Access – Catalog Changes
EXPLAIN OVERVIEW: The Explain Function; SQL Explain Statement; Plan Table Layout; Plan Table Column Definitions; Predicate Evaluation; Indexable Predicates; Stage 1 and Stage 2 Predicates

Course Format

Practical sessions make up a large part of the course, allowing delegates to demonstrate and reinforce the lectures given. During these sessions the delegate will follow a case study to design and build an efficient database application system.

The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.

Get in touch