We are a family owned USA based Corporate Training Company determined to help professionals, teams, and organizations improve.

Let us help you find the training program you are looking for.

If you can't find what you are looking for, contact us, we'll help you find it. We have over 800 training programs to choose from.

banner-img
Home » Database » Db2 for z/OS Database Administration Basics

Course Skill Level:

Foundational

Course Duration:

5 day/s

  • Course Delivery Format:

    Live, instructor-led.

  • Course Category:

    Database

  • Course Code:

    DB2DABN22J02

Who should attend & recommended skills:

Those who will be coding with basic experience in SQL, TSO, SPUFI, and QMF

Download Course Outline

Who should attend & recommended skills

  • Programmers, application designers, database administrators, system administrators – anyone who will be coding complex joins and queries and is concerned with optimum performance
  • SQL, TSO, SPUFI, and optionally QMF: Basic (1-2 years’ experience)

About this course

Db2 Database Administration Basics 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.

Skills acquired & topics covered

  • Db2 Concepts and Facilities Review
  • Complex Single Table SQL
  • Working with Multiple Tables
  • Performance Tuning and Explain
  • Tuning Embedded SQL
  • Working with DB2 Commands

Course breakdown / modules

  • 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

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

  • 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

  • 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

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

  • 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
  • More and More changes to SQL
  • Workshop
  • Partitioned Table spaces
  • Universal Table Spaces
  • Column Correlation
  • Preserving a Prior Access Path
  • Filter Factors
  • Influencing the Access Path Optimizer
  • Dynamic SQL Governor
  • Workshop

  • 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
  • Workshop – SQL Coding/Tuning
  • Aspects of Locking
  • Bind Options – Package and Plans
  • Database Design
  • Coding Techniques
  • SQLCA and SQLCODE handling
  • Restartability in programs 
  • NOTE: Each attendee should bring 2 or more complex queries to work with in class.

  • 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

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

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

  • ALTER BUFFERPOOL (DB2)
  • DISPLAY BUFERPOOL (DB2)
  • DISPLAY GROUP BUFFERPOOL (DB2)
  • ALTER GROUP BUFFERPOOL (DB2)
  • DISPLAY GROUP (DB2)
  • Workshop – Commands

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

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

Browse our programs to take the next step toward advancing yourself, your team, and organization.

Browse Courses