SQLAdvanced Topics for DB2 UDB

LENGTH OF COURSE:
2-3 Days

DESCRIPTION: This course is designed to be a comprehensive review of SQL and SQL Advanced Topics. The course begins with an intense review of SQL components and concludes with advanced performance issues. Students will have the opportunity to evaluate queries for performance and determine how to modify their queries to improve performance.

PREREQUISITES: Students are expected to have some working knowledge of SQL.

INSTRUCTION TECHNIQUES: A combination of Lecture, Exercises, and Labs (when/where available).

AUDIENCE

Application Users, Developers, Managers, and Technicians that need to access and format relational data. The course will concentrate on how to use SQL to access relational data rather than concentrating on the technical aspects of SQL.
KEY HIGHLIGHTS:

  • Understand the Relational database architecture
  • Describe the three SQL subdivisions
  • Understand the types of data
  • Build Complex SELECT Statements
  • Define conditions and boundaries
  • Build compound conditions
  • Define Operators
  • JOIN tables
  • Define Queries within Queries
  • Define and use VIEWs
  • Create new rows and delete rows
  • Advanced Programming Techniques
    • CASE expressions
    • NULLIF and STRIP functions
    • KEEP UPDATE LOCKS
    • Temporary Tables
    • Design Enhancements
    • Large Tablespaces
    • Maximum Rows per Page
    • Rename Table
    • Partition Locking
    • Foreign Keys
    • View Check Option


  • Non-Partitioning Index Placement
  • Use the Catalog
  • Cursor Processing
  • Dynamic SQL
  • Stored Procedure Concepts
  • DB2/UDB Performance
  • DB2 Utilities
  • Physical Data Management
    • MVS Related Products
    • Table Terminology
    • Physical Storage Hierarchy
    • Tablespace and Page Types
    • Data Page and Data Record Formats
    • B-Tree Index
    • Clustering Index
  • Optimizer and Explain Features
    • How the Optimizer Works
    • The Plan Table
    • Sample Selects Illustration
    • Explain Statement Shortcomings


  • Commit Processing and Locking Strategies
    • Units of Recovery
    • Page, Table, & Tablespace Locks
    • Program Isolation Levels
    • Lock Table Statement
    • Factors Affecting Locking
    • Locking Trade-offs
  • Performance Considerations
    • DML Coding
    • Program Preparation
    • DDL Suggestions
    • Design Tips
    • Environment Issues
    • Performance Factors
  • Referential and Explain Feature
    • Referential Integrity Terminology
  • DB2 Utility Programs Overview
  • What’s new in DB2

Return to Home Page.
Return to Alphabetic Listing of Courses.
Return to Courses by Subject.
Request More Information or Contact Us.