Overview

Db2 12 for z/OS DBAs can develop intermediate skills or recognition through lectures and hands-on exercises of:

• TSO/E and ISPF
• Data sets
• Db2 Objects
• Structured Query Language
• Db2 Commands
• JCL and SDSF
• Db2 Utilities
• Db2 Logging
• Db2 Program Preparation

The course materials cover Db2 12 for z/OS.

Audience

This intermediate Db2 course is for z/OS database administrators who need to acquire the skills required to administer a Db2 database in a z/OS environment.

Prerequisites

Objective

• Describe several services provided in a z/OS environment
• Explain workloads in the batch environment
• Explain workloads in the online environment
• Describe job roles in a z/OS environment
• Log On to TSO/E
• Navigate TSO/E and ISPF
• Use TSO commands
• Setup and utilize JCL (Job Control Language)
• Utilize SDSF
• Describe the different types of data sets in a z/OS environment
• Allocate data sets and list data set information and content
• Create and modify data set content
• Give an overview of the Db2 9 environment
• Describe and utilize Db2 Objects
• Describe and utilize several DB2 Utilities
• Describe the Db2I (Db2 Interactive Facility) environment
• Use SPUFI (SQL Processing Using File Input) to compose and execute SQL
• Setup and execute Db2 Commands
• Understand Db2 Logging
• Describe Db2 program preparation process
• Understand Db2 startup and shutdown
• Understand and utilize Db2 recovery strategies

Course Outline

Unit 1Introduction

• The modern Mainframe Environment
• Defining z/OS
• What are the benefits of z/OS?
• Typical z/OS workloads
• Batch processing
• Online Transaction Processing
• Job roles in the Mainframe environment
• z/OS Components and Services
• UNIX System Services
• The Parallel Sysplex Environment
• z/OS Management Interfaces

Unit 2TSO/E and ISPF

• Emulators
• z/VM systems
• 2.1. TSO/E
• TSO Logon panel
• Changing passwords
• Additional logon panel options
• The Reconnect option
• TSO READY prompt
• TSO HELP command
• LISTALC command
• PROFILE command
• Changing PROFILE
• LOGOFF command
• Scripting support
• 2.2. ISPF
• What is ISPF
• ISPF command
• ISPF Primary Option Menu
• Primary Option Menu Areas
• Action Bar
• Options option
• ISPF Settings
• Global Color Change Utility
• Function Keys
• Option or Command Prompt
• Keylist Utility
• F3 Exit
• F1 Help
• F2 Split
• ISPF Navigation Techniques
• PFSHOW
• More ISPF commands
• Option listing
• ISPF Command Shell
• TSO Command Prefix

Unit 3Data Sets

• Data sets
• Types of data sets
• DASD versus Tape
• Volumes
• Storage allocation
• Data set naming conventions
• Catalogs
• Storage Management
• Checkpoint
• 3.2. Sequential Data Sets
• Creating data sets
• Utility Selection Panel
• Data Set Utility Panel
• Creating a data set
• Allocate New Data Set
• Creating a sequential data set
• Editing a sequential data set
• ISPF Editor
• ISPF Editor primary commands
• ISPF Editor line commands
• Data Set List Utility
• Data set name wild cards
• Data Set List Utility
• DSLIST results
• Select Action
• HELP on Line Commands
• DSLIST by volume
• 3.3. Partitioned Data Sets
• Partitioned data sets
• Creating a PDS
• PDS members
• Creating a PDS member Library Utility Panel
• DSLIST Utility
• PDS Member List
• Member Actions
• 3.4. VSAM Data Sets
• VSAM data sets
• VSAM data set components
• Creating a VSAM data set

Unit 4JCL and SDSF

• 4.1. JCL
• Batch Jobs versus Online Transactions
• Batch job activities
• Creating the batch job
• JCL: Job Control Language
• Statement format
• JOB statement
• EXEC statement
• DD statement
• Instream Input Data Set
• Instream Output Data Set
• DD DUMMY
• Sample Jobstream 1
• Sample Jobstream 2
• Submitting the job
• Notification
• 4.2. SDSF
• SDSF
• SDSF Primary Option Menu
• Output and Held queues
• PREFIX command
• Working with queue query
• Select command
• ? command
• Canceling or Purging output
• Sample jobstream 1 revisited
• Sample jobstream 2 revisited

Unit 5Db2 Relational Database Concepts

• Relational Database Systems
• Db2 for z/OS and Db2 family
• The Db2 Optimizer
• Benefits of Db2 for z/OS
• Database
• Tables
• Indexes
• Primary Key
• Relationships
• Referential Integrity
• Integrity Rules
• Data domains
• Table scan access
• Index access
• Interacting with Db2
• Transactions

UNIT 6 Structured Query Language (SQL) and SPUFI

• Structured Query Language (SQL)
• SQL categories
• SELECT statement
• SELECT list
• FROM clause
• Simple SELECT statement
• SUBSTR function
• SELECT with SUBSTR function
• WHERE clause
• SELECT with WHERE clause
• IN operator
• WHERE with IN operator
• BETWEEN operator
• WHERE with BETWEEN operator
• LIKE operator
• WHERE with LIKE operator
• ORDER BY clause
• The NULL value
• SELECT with ORDER BY
• Putting it all together
• Joining tables
• Correlation Names
• Additional criteria
• Cartesian products
• Executing SQL
• SQLCODE

Unit 7Db2 Objects

• 7.1. Databases and Table Spaces
• Db2 objects
• The Db2 catalog
• Storage group
• Database
• Table Space
• Segmented table space
• Partitioned table space
• Partition By Growth table space
• Partition By Range table space
• VSAM data sets
• 7.2. Tables, Indexes and Views
• Table
• Partitioned table
• Index
• Clustering index
• Index space
• Partitioned index
• View
• 7.3. Qualified names, Implicit Object Creation, and SQL statements
• Qualyfing Db2 objects
• Implicit object creation
• DML statements
• Insert statement
• UPDATE statement
• DELETE statement
• COMMIT statement
• ROLLBACK statement

Unit 8The Db2 System

• The Db2 system
• System parameters
• Required address spaces
• Optional address spaces
• Started tasks
• Master address space
• System databases
• Directory
• Catalog
• System database data sets
• Data pages
• Buffer pools
• Buffer pools: Read operations
• Buffer pools: Write operations
• System buffer pools
• Db2 logging
• Bootstrap data set
• Db2 locking
• Integrity issues
• Transaction locks
• Isolation Levels
• User interfaces

Unit 9Db2 Commands and Program Preparation

• Accessing Db2
• 9.1. Db2 Commands
• Db2 commands
• Command format
• DISPLAY DATABASE
• DISPLAY DATABASE SPACENAM
• Other DISPLAY DATABASE options
• DISPLAY BUFFERPOOL
• DISPLAY DDF
• DISPLAY LOG
• DISPLAY THREAD
• ARCHIVE LOG
• STOP DATABASE
• START DATABASE
• STOP DATABASE SPACENAM
• START DABATASE SPACENAM
• Executing commands
• Executing commands: TSO
• Executing commands: Batch
• Executing commands: SDSF
• 9.2. Program Preparation
• Db2 program preparation
• Source program
• Precompiler
• Compile and link edit
• Database Request Module
• Bind plan
• Bind plan
• EXPLAIN
• Program execution

Unit 10 Db2 Utilities

• Utility categories
• Utility and Tools Packaging
• Standalone utilities
• Service aids and samples
• Online utilities
• UNLOAD utility
• UNLOAD control statement
• LOAD utility
• LOAD utility steps
• Restrictive states
• Db2| Utility Generator
• Utility Panel One
• Utility Panel Two
• Utility batch job
• DSNUPROC
• SYSUTIL table
• DISPLAY UTILITY command

Unit 11 Db2 Shutdown, Startup, and Recovery

• Buffer Pools and Logging
• Normal Db2 Shutdown
• Restart phases
• Starting Db2 after a normal shutdown
• Abnormal Db2 shutdown
• Starting Db2 after an abnormal shutdown
• Log initialization
• Current status rebuild
• Forward log recovery
• Backward log recovery
• Object backup and recovery
• COPY utility
• Table space recovery
• RECOVER utility

Unit 12 Course Summary

• z/OS topics
• Db2 for z/OS topics
• Other Db2 courses
• Db2 for z/OS certifications
• Course Evaluation