Target Audience
This course is aimed at Database Administrators and applications developers who need to understand the new features and facilities of DB2 for LUW Version 9.1. This lecture based one-day course covers all of the new features in detail.
Pre-requisites
A strong working knowledge of DB2 for LUW Version 8 is required to attend this course.
Course Objectives
The aim of this course is to provide the delegate, familiar with DB2 V8, with the necessary skills required to use the new Version 9.1 features.
Course Environment
The lectures apply to DB2 running in a Linux, Unix or Windows environment.
Course Details
- XML SUPPORT / PUREXML
- eXtensible Markup Language Introduction
- XML Terminology
- An XML Document Example
- XML Data Model
- Well Formed Documents
- Valid Documents
- Version 9 Enhancements - Overview of pureXML
- XML Data Type
- Adding XML Columns to a Table
- Node Storage
- Relational Data vs XML (Hierarchical) Data
- The DB2 XML Parser
- Schema Validation - The XML Schema Repository (XSR)
- Accessing XML Data
- Summary Of Xml Functions
- Sample Data used in Examples
- XMLSerialize Function
- XMLElement Function
- XMLAttributes Function
- XMLForest Function
- XMLConcat Function
- XMLAgg Function
- XMLNamespaces Function
- XMLComment Function
- XMLPi Function
- XMLText Function
- XMLDocument Function
- XMLParse Function
- XPath Specification
- XML Document used in Examples
- Xpath Function - XMLQuery
- Xpath Function - XMLExists
- Xpath Function - XMLCast
- Xpath Function - XMLTable
- XPath Axes
- XPath Functions
- XML Indexes
- Logical and Physical Indexes
- Index Considerations
- Schema Validation
- XQuery Examples
- XPath Examples
- FLOWR Examples
- Application development
- Utilities
- XQUERY
- XQuery Introduction
- XPath Expressions
- XPath Terminology
- Relationship of Nodes
- Selecting Nodes
- Specifying XPath Predicates
- Selecting Unknown Nodes
- Selecting Several Paths
- XPath Axes
- Location Path Expression
- XPath Operators
- Using XQuery with DB2
- Using XQuery with Xpath Expressions
- Further XQuery / DB2 Functions
- FLWOR Constructs
- FLWOR Constructs - FOR Clause
- FLWOR Constructs - WHERE Clause
- FLWOR Constructs - RETURN Clause
- FLWOR Constructs - ORDER BY Clause
- FLWOR Constructs - LET Clause
- FLWOR Constructs - Built In Functions
- Combining FLWOR and SQL Statements
- DEVELOPER WORKBENCH
- The Developer Workbench
- Connecting To DB2 from the Workbench
- Creating a Project
- Creating a Stored Procedure
- Parameter Specification
- Running the Stored Procedure
- Debugging the Stored Procedure
- Using the Workbench to Develop XML Queries
- Constructing the XML Query
- Running the XML Query
- Filetring the XML Query
- DEVELOPMENT ENHANCEMENTS
- DB2 JCC Java Driver
- JCC Driver Enhancements
- Binary / Varbinary / DecFloat - Data Type Support
- Command Line Processor - New SQL Statement Limit
- New Scalar Function - COLLATION_KEY_BIT
- New Scalar Functions - UPPER / LOWER
- New Scalar Function - TRIM
- New Scalar Function - STRIP
- Scalar Functions List
- ADMINISTRATION ENHANCEMENTS
- Self Tuning Memory Allocation
- Automatic Storage Support - Review
- Automatic Storage Tablespaces - Review
- Create Tablespace Parameters
- Automatic Storage Tablespace Example
- Automatic Storage Support - Enhancements
- Automatic Statistics Collection
- Automatic Statistics Collection Considerations
- Automatic Statistics Collection - Maintenance Window
- Runstats - Statistics Profiling - Review
- Runstats - Throttling - Review
- Runstats Profiling Examples
- Automatic Statistics Profile Generation
- Automatic Table / Index Reorg Enhancements
- Further GUI Automatic Reorg Features
- Prefetch and Page Cleaner Enhancement
- Alter Table Enhancements
- Altering the Data Type of a Column
- Allowable Data Type Changes
- Support for Copying Schemas
- Copying Schemas with DB2Move
- DB2Move Examples
- Copying Schemas with Admin_Copy_Schema
- Supported Administrative SQL Routines and Views
- Summary of Activity Monitor SQL Routines
- Summary of Health Snapshop SQL Routines
- Summary of Snapshot Related SQL Routines
- Summary of ADMIN_CMD Related SQL Routines
- Summary of Configuration Related SQL Routines
- Summary of Environment Related SQL Routines
- Summary of WebSphere MQ Related SQL Routines
- Summary of Security Related SQL Routines
- Summary of Stored Procedure Related SQL Routines
- Summary of Redistribution Related SQL Routines
- Summary of Management Tool Related SQL Routines
- Summary of Miscellaneous Tool Related SQL Routines
- Examples Of SQL Functions / Views
- Enhanced Diagnosis of Lock Timeout
- Lock Timeout Report File Example
- Enhancements to Command DB2PD
- DB2PD and DB2PDCFG Authority
- Further Support for Long Object Names
- Log File Size Enhancements
- Update Database Configuration - Manual Keyword
- Index Size Enhancements
- Larger Temporary Tables
- Table Size Limits
- PARTITIONING ENHANCEMENTS
- Introduction and Terminology
- The DB2Nodes.cfg File
- Example Configurations
- Creating Parallel Databases
- Database Partition Groups
- Creating a Database Partition Group
- Listing Nodes and Database Partition Groups
- Creating Partitioned Tablespaces
- Creating Partitioned Tables
- Hash Partitioning
- Partitioning Maps
- The Redistribute Database Partition Group Command
- Using Backup and Restore
- Table Partitioning
- Benefits Of Table Partitioning
- Table Partitioning Syntax
- Partitioning at Automated Intervals
- Adding Partitions to a Partitioned Table
- Attaching and Detaching Partitions
- Converting Non-Partition Tables to Partitioned
- Using Database and Table Partitioning Together
- Enabling Parallelism
- SECURITY ENHANCEMENTS
- Secadm Authority
- Label-Based Access Control
- LBAC - Step 1 - Define the Security Label Component
- LBAC - Step 2 - Define the Security Policy
- LBAC - Step 3 - Define the Security Labels
- LBAC - Step 4 - Define the Table to use Security Labels
- LBAC - Step 5 - Grant Security Labels to Users
- LBAC - Step 6 - Use the Table
- Label-Based Access Control - Column Protection
- LBAC Catalog Tables
- The Transfer Ownership Statement
- The SetSessionuser Privilege
- The Set Session Statement
- Creating Restrictive Databases
- New SERVER_ENCRYPT_AES Authentication Type
- LDAP Based Authentication Support
- PERFORMANCE ENHANCEMENTS
- Version 8 Recap - Null and Default Value Compression
- Version 9 - Row Compression
- Row Compression - Estimating savings
- Enabling compression - Building the Dictionary
- Removing Compression
- Has Compression Been Switched On?
- Row Compression Considerations
- Statistical Views
- Statistical View Considerations
- Data Load Enhancements using SourceUserExit
- Load from Cursor with Remote Fetch
- Materialized Query Table (MQT) Enhancements
- Set Integrity Enhancements
Course Format
The course is purely lecture based and contains no practical exercises. A comprehensive Student Guide is supplied which contains detailed documentation of the new version together with many syntax examples where relevant. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.
|