Know DB2

Db2 for z/OS - Database Administration

From Start to Finish: Part 1

Db2 Database Administration Part 1 is a customized hands-on SQL and Command workshop that is designed to take the student through coding complex SQL statements, developing SQL performance tuning methods and using Db2 Commands.

Topics Covered:

Duration: 5 days, Hands-on labs

Audience: Anyone who will be coding complex joins and queries and is concerned with optimum performance; programmers, application designers, database administrators, system administrators.

Prerequisites: Working knowledge of basic SQL, TSO, SPUFI and optionally QMF.

Db2 for z/OS Database Administration

Part 1 Class Outline:

Db2 Concepts & Facilities Review

  • Db2 Environment- Subsystem Look
  • Products and Utilities Review
  • Data Structures – A Detail Look into Tables, Table spaces, and Indexes
  • Db2 Catalog for Programmers
  • RUNSTATS – what it does
  • Structured Query Language Review
  •  

Complex Single Table SQL

  • Data Retrieval & Result tables
  • SELECT Statement Structure
  • Using Functions and Expressions
  • Ordering the result table rows
  • Merging intermediate results
  • Special Registers
  • Workshop
  •  

Introduction to Performance

  • What is the Optimizer?
  • Predicate Types (Indexable, Stage 1 and Stage 2)
  • Access Paths - how they work
  • Matching Indexes to Predicates
  • Explain - the Basics and more
  • Workshop
  •  

Working with Multiple Tables

  • Joining Data from multiple Tables
  • Inner join
  • Full Outer Join
  • Left Outer Join
  • Right Outer Join
  • Using Multiple join types in a Query
  • Nested Table Expressions
  • Using Global Temporary Tables
  • Workshop
  •  

Tuning Complex Queries

  • Complex Access Paths
  • -Nested Loop Joins
  • -Merge Scan Joins
  • -Hybrid Joins
  • Intermediate Tables & the DSNDB07
  • Explain - Complex
  • Workshop
  •  

Summary Processing, Functions & Advanced Coding

  • Column Functions
  • Correlated subqueries
  • Non-correlated subqueries
  • Scalar Functions & Case Expression
  • Statement Cost Comparisons
  • Recursive SQL
  • Volatile Tables
  • Materialized Query Tables (MQT)
  • SQL Limits
  • Multi-Row Insert, Update, Fetch
  • Insert within Select
  • Dynamic Scrollable Cursors
  • Get Diagnostics
  • Current Package Path
  • Db2 Universal Drivers
  • Query Performance Enhancements
  • Visual Explain New Features
  • EXPLAIN Global Prepare Cache
  • More and More changes to SQL
  • Workshop
  •  

Advanced Tuning - Parallelism, Preserving & Influencing Access Paths

  • Partitioned Table spaces
  • Universal Table Spaces
  • Column Correlation
  • Preserving a Prior Access Path
  • Filter Factors
  • Influencing the Access Path Optimizer
  • Dynamic SQL Governor
  • Workshop
  •  

Embedded SQL Coding &Tuning

  • Update with Subselect
  • Using Save points
  • Using Global Temporary Tables
  • Cursor Processing: Open, Fetch, Close
  • Deleting, Updating using a Cursor
  • Cursor with Hold and Read Only
  • -Scrolling
  • -Using OPTIMIZE FOR
  • -Existence Checking
  • Code Structure / Coding Techniques
  •  

Tuning Your Application for Optimum Concurrency

  • Aspects of Locking
  • Bind Options – Package & Plans
  • Database Design
  • Coding Techniques
  • SQLCA & SQLCODE handling
  • Restartability in programs
  •  

Applying techniques learned to your data

  • NOTE: Each attendee should bring 2 or more complex queries to work with in class.
  •  

Operator Commands

In each of the following sections we will:

  • Review of the syntax of each command
  • Look at Resource Codes for Messages
  • Privileges and authorizations Required
  • Work with Commands in our labs
  • Review How and When to use Commands
  •  

Commands in Db2

  • -DISPLAY DATABASE (DB2)
  • -START DATABASE (DB2)
  • -DISPLAY THREAD (DB2)
  • -CANCEL THREAD (DB2)
  • Workshop - Commands
  •  

More Commands

  • -RECOVER INDOUBT (DB2)
  • -RECOVER POSTPONED (DB2)
  • -RESET INDOUBT (DB2)
  • -DISPLAY UTILITY (DB2)
  • -ALTER UTILITIY (DB2)
  • -TERM UTILITY (DB2)
  • Workshop - Commands
  •  

More Commands

  • -ALTER BUFFERPOOL (DB2)
  • -DISPLAY BUFFERPOOL (DB2)
  • -DISPLAY GROUP BUFFERPOOL (DB2)
  • -ALTER GROUP BUFFERPOOL (DB2)
  • -DISPLAY GROUP (DB2)
  • Workshop - Commands
  •  

    • More Commands

  • -DISPLAY ARCHIVE (DB2)
  • -SET ARCHIVE (DB2)
  • -DISPLAY LOG (DB2)
  • -SET LOG (DB2)
  • -ARCHIVE LOG (DB2)
  • -RECOVER BSDS (DB2)
  • Workshop - Commands
  •  

More Commands

  • BIND PACKAGE (DSN)
  • -REBIND PACKAGE (DSN)
  • BIND PLAN (DSN)
  • REBIND PLAN (DSN)
  • -FREE PACKAGE (DSN)
  • -FREE PLAN (DSN)
  • DCLGEN (DSN)
  • Workshop - Commands
  •  

More Commands

  • -DISPLAY ARCHIVE (DB2)
  • -DISPLAY DDF (DB2)
  • -DISPLAY LOCATION (DB2)
  • -DISPLAY PROCEDURES (DB2)
  • -STOP PROCEDURES (DB2)
  • -DISPLAY RLIMIT (DB2)
  • -STOP RLIMIT (DB2)
  • Workshop - Commands
  •  

More Commands

  • -DISPLAY TRACE (DB2)
  • -START TRACE (DB2)
  • -MODIFY TRACE (DB2)
  • -SET SYSPARM (DB2)
  • Workshop - Commands
  •  

More Commands

  • -START DB2 (DB2)
  • -STOP DB2 (DB2)
  • -START DDF (DB2)
  • -STOP DDF (DB2)
  • -STOP FUNCTION SPECIFIC (DB2)
  • -STOP irlmproc (z/OS IRLM) (DB2)
  •  

Class Review

  • © Computer Business International, Inc.

 

 

Would you like to schedule or customize this class?
Please call our toll free number 1.866.224.4968 or email us at info@cbi4you.com.