Descripiton

This course teaches database administrators to perform further database administrative tasks using DB2 LUW.

Topics covered include creating application related database objects such as XML storage, IDENTITY COLUMNS, SEQUENCES, TRIGGERS, REFERENTIAL INTEGRITY, CHECK CONSTRAINTS, MATERIALIZED QUERY TABLES and TEMPORAL TABLES.

The course also covers Application performance issues including optimization techniques, packages, REORG, RUNSTATS, REORGCHK, BIND and REBIND.

The course covers using EXPLAIN tools to review the access paths for SQL statements and SQL performance tuning techniques. Various performance related and diagnostic methods are covered, including using db2pd and db2top commands, and Db2 monitoring functions.

Finally, the course covers how locking is performed by Db2 and the effect the application isolation level has on locking and lock wait conditions.

Prerequisites

Knowledge of basic DBA commands is essential (such as object creation, data movement and recovery).

Audience

Database Administrators

Objectives

The aim of this course is to provide the delegate with further skills required to function as a DBA within an LUW environment. After completing this course, participants should be able to:

• Understand and create further DB2 objects including XML columns, IDENTITY COLUMNS, SEQUENCES, TRIGGERS, REFERENTIAL INTEGRITY, CHECK CONSTRAINTS, MATERIALIZED QUERY TABLES, and TEMPORAL TABLES
• define Referential Integrity constraints and Table Check constraints
• resolve integrity violations using the SET INTEGRITY statement
• understand the application development process
• bind plans and packages
• investigate system, application and SQL performance issues understand and resolve locking issues
• run performance related commands RUNSTATS, REORGCHK, REORG and BIND
• monitor database activity
• use db2pd and db2top commands

Topics

• APPLICATION PROGRAMMING ISSUES
• Db2 Environments
• Development Cycle For Embedded Sql in C
• SQLJ Programming - Development Cycle
• Precompiling A C Program
• Binding A C Program
• Levels Of Optimisation

• LOCKING
• Implications Of Concurrent Processing
• Database Manager Locks
• Objects Of Locks
• Isolation Levels
• Lock Modes
• Lock Compatibility
• Lock Example
• Change in Cursor Stability Behaviour
• Selecting Data with Update / Exclusive Locks
• The Lock Table Statement
• Lock Escalation
• Lock Wait And Deadlocks
• Commit Points
• Savepoints
• Savepoints - Considerations and Restrictions
• Optimistic Locking using Row Change Timestamps
• Optimistic Locking Example
• Row Change Timestamp Manipulation
• The Quiesce Utility

• DATA INTEGRITY
• Referential Integrity
• The Primary Key
• The Foreign Key
• Referential Integrity Rules
• Referential Integrity Constraint Names
• Self Referencing Referential Structures
• Referential Integrity Performance
• Check Constraints
• Check Constraint Syntax
• Allowable Check Constraints
• When Are Check Constraints Enforced
• Integrity Pending (aka Check Pending) State
• The Set Integrity Command
• Running Set Integrity Via The Gui
• Load Utility Considerations
• Informational Constraints

• IDENTITY COLUMNS AND SEQUENCES
• Identity Columns
• Identity Columns - Examples
• Altering Identity Columns
• Altering Identity Columns - Parameters
• Identity Columns - Retrieving The Generated Number
• Sequences
• Create Sequence Syntax
• Altering Sequences
• Using Sequences In Applications
• Sequence Application Examples
• Sequences And Identity Columns Comparison

• TRIGGERS
• Triggers
• Trigger Parts
• Before And After Triggers
• Trigger Examples
• Allowable Combinations
• Error Handling
• Trigger Cascading
• Instead Of Triggers
• Instead Of Triggers - Restrictions
• Trigger Authorisation
• Removing Triggers

• 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
• Registering Existing Tables As Mqt
• Maintenance Of Mqts Using Staging Tables

• TEMPORAL TABLES
• 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
• Bi Temporal Tables

• XML SUPPORT
• Extensible Markup Language Introduction
• Xml Terminology
• An Xml Document Example
• Xml Data Model
• Well Formed Documents
• Valid Documents
• Xml Data Type
• Accessing Xml Data
• Summary Of Xml Functions
• Xml Generation - Xmlserialize Function
• Xml Generation - Xmlelement Function
• Xml Generation - Xmlattributes Function
• Xml Generation - Xmlforest Function
• Xml Generation - Xmlconcat Function
• Xml Generation - Xmlagg Function
• Xml Generation - Xmlnamespaces Function
• Xml Generation - Xmlcomment Function
• Xml Generation - Xmldocument Function
• Xml Generation - Xmlpi Function
• Xml Generation - Xmltext Function
• Xml Document Processing - Xpath Specification
• Xml Document Used In Examples
• Xmlparse Function
• Xpath Function - Xmlquery
• Xpath Function - Xmlexists
• Xpath Function - Xmlcast
• Xpath Function - Xmltable
• Xpath Axes
• Xpath Functions
• The Db2 Xml Parser
• Schema Validation - The Xml Schema Repository (Xsr)
• Schema Validation
• Updating Xml Data
• Xml Indexes
• Logical And Physical Indexes
• Index Considerations

• APPLICATION PERFORMANCE
• The Db2 Optimizer
• Levels Of Optimisation
• Operational Utilities
• Rebinding
• The Runstats Utility
• Runstats Parameters
• Runstats - Sampling Options
• Runstats - Statistics Profiling
• Runstats - Throttling
• Runstats Profiling Examples
• Statistical Views
• Statistical View Considerations
• Automatic Statistics Collection
• Automatic Statistics Profile Generation
• Collection of Real Time Statistics
• Volatile Tables
• The Reorgchk Utility
• The Reorg Utility
• Offline / Online Table Reorg
• Index Reorg
• Automatic Table / Index Reorg
• Maintenance Window Features
• The Admin_Cmd Procedure

• SYSTEM PERFORMANCE
• Database Performance Configuration Parameters
• Self-tuning memory manager (STMM)
• Data Sorts
• Concurrent Application Tuning
• Asynchronous Page Cleaner
• Blocking Data
• The Database Configuration Advisor

• MONITORING
• Error Logging
• Database Monitoring
• Monitoring Elements
• System Monitoring Table Functions
• System Monitoring Example
• Activity Monitoring Table Functions
• Database Monitoring Table Functions
• Database Monitoring Example
• Monitoring Table Functions Summary
• Administrative Views
• Administrative View Examples
• Event Monitors
• Event Monitor Types
• The Create Event Monitor Command
• Event Monitor Example
• Activating Monitors
• Formatting File Monitor Output
• Monitor Example - Investigating Poorly Performing SQL
• Event Monitors - Writing to SQL tables
• Snapshot Monitoring - Pre DB2 9.7
• Snapshot Monitor Switches
• Snapshot Commands
• Taking a Snapshot using Sql
• Monitoring SQL Routines
• Examples Of SQL Functions / Views
• Other Diagnosis Features - Diagnosis of Lock Timeout
• Lock Timeout Report File Example
• The DB2PD Command
• The DB2TOP Command

• SQL PERFORMANCE AND TUNING
• SQL Explain Tools
• Explain Tables
• The Explain Command
• The Db2 Explain Bind Option
• The Db2expln Tool
• Interpreting DB2Expln Output
• The Db2advis Tool - Index Advisor
• The Query Tuner
• The Visual Explain Tool
• The Explain Operator Details Window
• Visual Explain Operators
• Visual Explain - The Table Statistics Window
• Visual Explain for Packages
• Access Paths - Tablespace Scan (Relational Scan)
• Non-Matching Index Scan
• Matching Index Scan
• Multiple Index Access
• Index Only Access
• Table Join Methods
• Merge Scan Join
• Nested Loop Join
• Hash Join