Logo

68 ATLANTIS BUILDING 3RD MAIN RD VYALIKAVAL HBCS LAYOUT
OPPOSITE MANYATA EMBASSY PARK NAGAWARA BANGALORE 560045

:+91-9686750721
: db2dbaimsdba@gmail.com

Training & Development

Online Training

Accelerate your career with online computer training with 24x7 Lab Facility connectivity from anywhere !

Mainframe Professionals offers one of the broadest range of technology training courses in the industry. We offer customized instructor led training courses that are offered at your site with 100% Quality and Commitment.

 

Our courses include:

 

  • DB2 Database Administration on z/OS
  • DB2 System Administration on z/OS
  • IMS Database Administration on z/OS
  • IMS System Administration on z/OS
  • Mainframe Training – COBOL JCL VSAM DB2 PL/1 IMS DB/DC REXX
  • Mainframe Testing
  • Mainframe Tools
Course Details
  • DB2 Database Administration on z/OS
  • DB2 System Administration on z/OS
  • IMS DB / DC
  • IMS DBA
  • DB2 for z/OS Application Programming
  • DB2 Advanced Application Programming
  • DB2 for z/OS Stored Procedures
  • DB2 for z/OS Utilities for Database Administrators
  • DB2 for z/OS Application Data Recovery
  • DB2 for z/OS Application Performance and Tuning
  • DB2 for z/OS for Systems and Operations Personnel
  • DB2 for z/OS Index Modeling and Design
  • Application programming & System Programming

DB2 Database Administration on z/OS

DB2 Introduction

OVERVIEW OF DB2 AND EVALUATION
OVERVIEW OF DB2 DBA CAREER
DIFFERENCES BETWEEN APPLICATION DB2 DBA AND DB2 SYSTEMS DBA
MAINFRAME CONNECTIVITY SETUP AND TSO LOG ON TO MAINFRAMES

DB2 Address spaces

System Services Address Space (DSNMSTR)
Database Services Address space (DSNDBM1)
Internal Resources Lock Manager (IRLMPROC)
Distributed Database Facility (DSNDIST)
Administrative Task Address Space (DSNADMT)

DB2 Objects

Storage Group
Database
Different Types of Tablespaces

  • Simple Tablespace
  • Segmented Tablespace
     
Classic Partitioned Index Controlled and Classic Partitioned Table Controlled.
Universal Table Spaces ( Partition by Range and Partition by Growth ).
Tablespace paramters - PRIQTY , SECQTY , DEFINE , PCTFREE , FREEPAGE , TRACKMOD , BUFFERPOOL and others

Tables
  • Creating Tables
  • Creating Tables with LIKE
  • Defining NULLS
  • Handling Nulls in Input File
  • Global Temporary Tables types - Created and Declared
  • Identity Columns & Sequences
  • RENAME COLUMN and RENAME INDEX statements
  • Clone Tables
  • Automatic creation of objects
  • Views
  • Synonyms
  • Alias
Indexes
  • Creating Unique Index
  • Creating Duplicate Index
  • Partitioning Index
  • Non Partitioning Index NPI
  • Data Partitioned Secondary Index DPSI
  • Indexspace parameters - COPY , DEFER , PADDED , NON PADDED and others
  • Index on Expressions
     
DB2 Admin Tool Navigation
Familiarity with Catalog Tables

DB2 Commands – START , STOP, DISPLAY , TERM and others
Lab

Referential Integrity:

Primary Key
Foreign Key
Unique Key
REFERENTIAL INTEGRITY
Delete Rules
Lab

Utilities Working with DB2 Data:

Getting Data Into and Out of DB2
Running LOAD , UNLOAD , CHECK DATA , CHECK INDEX and DSNTIAUL
Lab

Keeping Your Data in Good Shape:

Running RUNSTATS , REORG and REPAIR
Lab

Application Data Recovery Basics:

Running COPY ,COPYTOCOPY, MERGECOPY ,QUIESCE and RECOVER
MODIFY RECOVERY
MODIFY STATISTICS
STOSPACE
ADRDSSU
REBUILD INDEX
REPORT RECOVERY
Utility Pending States
Lab

Generic Utility jobs using LISTDEF and TEMPLATE

Lab

Access Paths

Explain
Plan Table
Statement Table
Lab

Security

Grants and Revokes
Secondary Authorization
System Privileges and Database privileges
Lab

Program Preparation

Bind
Package
Collection
Plan
Isolation Level Parameters
Reopt Enhancements
Histogram Statistics
PLAN Management - APREUSE(WARN),APREUSE(ERROR),APCOMPARE(WARN),FREE PACKAGE options
Skip Locked Data
Lab

Locking and Serialization

LOCKSIZE
LOCKMAX
CLAIMERS
DRAINERS
DISPLAY COMMANDS ASSOCIATED
Lab

LOAD and REORG Performance Parameters

Lab

Performance Tuning

Materialized Query Tables - MQTs
Performance Tips
Lab

Mainframe to Mainframe Communication

Setting up of Communications Database
Remote connectivty
Remote Bind
Lab

DB2 Stand Alone and Catalog Manipulation Utilities

Familiarity with Directory Tables - SPT01 , SCT02 , SYSLGRNX and SYSUTLIX
BSDS , Active Logs , Archive Logs , Buffer pools ,Work files
DSN1LOGF
DSNJU003
DSNJU004
DSN1CHKR
Data Compression - DSN1COMP
DSN1COPY
DSN1PRNT
DSNTEP2
DSNTEP4
DSNTIAD
DSNTIAUL
CATENFM
CATMAINT
DSNJCNVB
Lab

Introduction to DB2 Governor Facility DSNRLST

DSNRLST
DSNRLMT

Triggers , WLM Stored Procedures and Native Stored Procedures , UDFs and UDTs

WLM RELATED COMMANDS
Display Procedure
Display Function
Lab

Introduction to Large Objects LOB and XML

Lab

Migration Process

Migration
DSNTIJPM Reports Detailed explanation and Preparation steps for new version

DB2 10 New Features

Online Schema changes
Pending Defination changes - SYSPENDING catalog table
Index Include
MEMBER CLUSTER
Hash Technique
Row Permission and Column Masking
Temporaral Data Support
Program preparation
Other stuff in DB2 10
Lab

DB2 11 New Features

Conversion to UTS Tablespaces - Online Schema changes
Utilities Enhancements
Release(DEALLOCATE)
DROP colummn support and DROP PENDING CHANGES
Archive Tables
PCTFREE for UPDATE
Adding Partitions and Rotatate and Rebalance
Altering Limit keys
Display output
Automatic creation of Mapping Table
SYSSTATFEEDBACK
Extended RBA and LRSN log records
Support for Indexes that exclude NULL KEYS
Support for Arrays
Support for user defined global variables
APPLCOMPAT
Lab

Other Topics

RTS Catalog Tables
Data Sharing
OPTHINT
Other DB2 COMMANDS
CALL ATTACHMENT FACILITIES
Utilities Restart prodcedures
INTRODUCTION TO ZPARMS
Data Refresh
DBA check outs - Version Migration time , Disaster Recovery time, z/OS upgrade time , Tools upgrade time
Sharing dumps of DB2 11 DB2 DBA exams 312 , DB2 10 exams- 610 Family Fundamentals and 612 DB2 DBA
Resume Preparation
Interview Questions & Answers

Tools covered

DB2 Admin Tool
Demonstration of Omegamon Tool based on feasibility
Demonstration of BMC suite of Tools based on feasibility
Demonstration of CA suite of Tools based on feasibility

Introduction to different DB2 Traces

ACCOUNTING
AUDIT
GLOBAL
MONITOR
PERFORMANCE
STATISTICS
Recap and Wrapping up the training on a high level overview

DB2 System Administration on z/OS

DB2 subsystem regions & DB2 region procedures/JCLs
DB2 Modes
DB2 Early code and Subsystem definition in z/OS
DB2 Logs and BSDS datasets
DB2 System Utilities – BSDS Inventory and Print
DB2 Traces
DB2 Zparms
DB2 Catalog/Directory and Work file databases
DB2 Catalog/Directory Image copy job
SMP/e – Installation tool for all ISV products on z/OS
SMP/e libraries
DB2 libraries naming convention
Report Missing fixes
How to register in IBM ShopZseries
Run Install Clist
DB2 install/upgrade
DB2 tools customization at CM and NFM modes
Data-sharing concepts
Practical Lab – DB2 install
DR drill

IMS DB / DC

Course Contents of IMS DB Training will include the below:

Unit 1 - DL/I Environment , DB Organization and Access Methods
Unit 2 - DL/I Call Processing
Unit 3 - DL/I Retrieval Calls
Lab 1 - DL/I Retrieval
Unit 4 - DL/I Update Calls
Lab 2 - DL/I Update
Unit 5 - Programming Techniques I
Unit 6 - Advanced SSAs I
Unit 7 - DL/I Test Program
Lab 3 - DL/I Test Program and Command Codes
Unit 8 - Programming Techniques II
Unit 9 - Advanced SSAs II
Lab 4 - Command Codes
Unit 10 - System Service Calls
Unit 11 - Programming Standards
Lab 5 - Advanced Command Codes
Unit 12 - Secondary Indexing and Logical Relationship
Lab 6 - How to Implement Secondary Indexing and Logical Relationship
Unit 12 – Fast Path and High Availability Large Database Access
Lab 7 - How to Implement HALDB


Course Contents of IMS DC Training will include the below:

Unit 1 - IMS TM System Overview
Unit 2 - Message Processing Programming
Lab 1 - Get/Send Terminal Data
Unit 3 - Message Format Services
Lab 2 - Create and use MFS control blocks
Unit 4 - IMS/TM Additional Facilities

IMS DBA

UNIT 1 - IMS CAREER PATH

IMS Training Career path
Certification Details
Pattern of Certification Exam
Sharing IMS Version details and Manuals

UNIT 2 - INTRODUCTION TO IMS

Basic Introduction of IMS DB / DC Concepts
Difference between DBCTL , DCCTL and DB/DC CTL
IMS Control Region and IMS Address spaces
DLI/I Control Blocks (DBD, PSB, ACB)
Database Creation DBD
PSB Creation PSB
ACB Creation
Processing Options
SSA'S - Qualified and Unqualified
Function codes
Command codes
Application Program overview
Programming Examples
Concepts of Secondary Indexing and Logical relationship and Implementation
IMS JCL coding and System Datasets
DDLT0 Sample Test Program
Lab Exercise

UNIT 3 - IMS ACCESS METHODS

HDAM
HIDAM
HSAM
HISAM
GSAM
Datset Groups
Choosing between VSAM / OSAM for Dataset Groups
Lab Exercise

UNIT 4 - HALDB ( HIGH AVAILABLITY LARGE DATABASE)

PHDAM
PHIDAM
How to create Partitioning Databases
Lab Exercise

UNIT 5 - DATABASE REORGANIZATION

Overview
When to Reorganize Databases - Reactive and Proactive Reorgs
Offline Reorg and Offline Reorg
Reorg Process for different kind of databases

UNIT 6 - UTILITIES

HISAM REORGANIZATION UNLOAD
HISAM REORGANIZATION RELOAD
HD REORGANIZATION UNLOAD
HD REORGANIZATION RELOAD
PARTIAL DB REORGANIZATION
DB PRE-REORGANIZATION
DB SCAN
DB PREFIX RESOLUTION
DB PREFIX UPDATE
INDEX BUILDER UTILITY
POINTER CHECKER UTILITY
DBD MAPPER UTILITY
PSB MAPPER UTILITY
Lab Exercise

UNIT 7 - DATABASE RECOVERY PROCESS

Overview of the Database Recovery Process
IMS Backup and Recovery Utilities
Database Image copy Utility
Database Change Accumulation Utility
Database Recovery Utility
Batch Backout Utility
Lab Exercise

UNIT 8 - IMS DC CONCEPTS

Difference between IMS DB and IMS DC
Resources that IMS-DC Controls - Application Resources and Data Communications Resources
Data Communications Messages
IMS DB/DC Operating Environment
IMS Control Region , Message Processing Regions and BMP Message Processing Regions
Message Processing
Flow of Information through the System
Sending Information to Terminals and Other Terminals
Program to Program Message Switching
IMS Message Scheduling Techniques
Overview of Conversational Programming
Overview of Sample Message Processing Program
IO-PCB
Alternative PCB
SPA - Scratch Pad Area
MFS Components - MID , MOD , DIF and DOF
Lab Exercise - How to test the Screen in the Online Region, Sample Transaction and Online Program

UNIT 9 - IMS SYSGEN PROCESS

STAGE 1 Process
STAGE 2 Process
JCLIN Processing
Concepts of ETO
Dynamic Resource Definition
IMS Online Configuration Changes PSB , ACB , FORMAT
IMS Commands - Starting Region , Stopping Region , Stopping Databases , Starting Databases , DBR Databases , DISPLAY status and others
DSECTS
Lab Exercise

UNIT 10 - IMS LOGGING

Overview of IMS System Logging
IMS System Checkpoints
IMS Log Datasets
Online Log Datasets
Write-Ahead Datasets
System Log Datasets
Recovery Log Datasets
IMS Restart Dataset
IMS Log Buffers

UNIT 11 - DBRC - DATABASE RECOVERY CONTROL

Overview of DBRC and DBRC Tasks
DBRC Components - RECON , DATABASE RECOVERY CONTROL UTILITY (DSPURX00) , SKELETAL JCL
Overview of RECON Datasets
Defining and Creating RECON Datsets
Initializing RECON Datasets
Allocating the RECON Datasets to IMS System
Maintaining the RECON Datasets
Reorganizing RECON Datasets
Re-Creating RECON Datasets
Recommendations for RECON Datasets
DBRC Commands
DBRC Functions
Lab Exercise

UNIT 12 - FAST PATH DATABASES

Overview of DEDB
Overview of MSDB - Terminal and Non Terminal
Performance Features and Expedited Message Handling

UNIT 13 - IMS DBA ACTIVTIES

IMS DBD Changes , IMS PSB Changes ( Batch and Online ) , IMS ACLIB
IMS Abends and Fixes
Testing activities for IMS related to z/OS migration , IMS Version migrations as well as related to IBM tools upgrade to be compatible to IMS new versions for all the systems
Disaster Recovery testing activities as well as paper walk through for IMS.

UNIT 14 - OTHERS

Questions and Answers
Interview Questions
Resume Preparation
Mock Interview

DB2 for z/OS Application Programming

Objective
Incorporate static SQL statements in an application program
Prepare the program for execution
Validate execution results are correct
Produce code to support multiple rows being returned from the database manager using cursors
Identify considerations regarding units of work, concurrency, and restart of programs
Identify differences between static and dynamic SQL
Provide test data for applications
Discuss program and DB2 options relative to performance of static SQL
Key topics
DB2 Concepts
Identify DB2 family products
Explain DB2 workstation component functions
Identify DB2 objects
Identify the key differences between static SQL and other application alternatives for accessing DB2 data
Program Structure I
Embed INSERT, UPDATE, DELETE and single-row SELECT statements in application programs
Effectively communicate with DB2 when processing NULL values and determining success of statement execution
Demonstrate use of DB2 coding aids
Code CONNECT statements within an application program
Identify connection types and impacts on a unit of work
Program for the Call Attach Facility (CAF)
Program Preparation
Identify the additional steps necessary to prepare a program that contains embedded SQL for execution
Describe the functions of the DB2 PRECOMPILE and BIND processes
Describe factors relevant to the BIND process, including RUNSTATS positioning, package status, parameters, and authorization requirements
Program Structure II
Use DECLARE, OPEN, FETCH, and CLOSE CURSOR statements to handle select criteria that may return multiple rows in application programs
Issue positioned UPDATE and DELETE statements
Identify how scrollable cursors can be used
Recovery and Locking Concepts
Define a unit of recovery
Identify the basic locking strategies used by DB2
Dynamic SQL Introduction Describe the difference between static and dynamic SQL
List the types of dynamic statements
Code dynamic SQL in a program
Managing Test Data
Identify methods to insert data into a table
Use the LOAD or IMPORT utility
Identify the purpose of the RUNSTATS utility
Identify the purpose of the REORG utility
Performance Considerations
Use programming techniques that enhance DB2 application performance by following general guidelines, using indexable predicates, and avoiding unnecessary sorts
Identify the access paths available to DB2
List common causes of deadlocks and avoid such causes when possible
Use the EXPLAIN tools as aids to develop applications that emphasize performance

DB2 Advanced Application Programming

Objective
Develop high performing database applications that align with the organizations application growth plan and maintenance strategy
Process SQL statements using the DB2 Pre-compiler or DB2 Coprocessor
Code, prepare and run dynamic SQL programs
Code applications that coordinate updates across multiple servers
Debug applications using the DEBUG tool for z/OS
Describe some components and architectural characteristics of Web applications and the role that DB2 plays in the Web application environment
Describe how your program can interact with the Web
Manipulate large objects
Facilitate the exchange of large objects between DB2 and local data sets
Develop applications that minimize locks on resources to support concurrent programs
Use scrollable cursors efficiently
Use stored procedures
Consider using tools as an alternative to writing a program
Write applications that do not hinder other applications
Key topics
Application Architecture
Application Architecture Exercise
Program Preparation
Dynamic SQL
Dynamic SQL Exercise
Distributed Data Access
Distributed Data Access Exercise
DEBUG Tool for z/OS
DEBUG Tool for z/OS Exercise
DB2 and the Web
Large Objects
Large Objects Exercise
Locking
Locking Exercise
Scrollable Cursors
Scrollable Cursors Exercise
Using Stored Procedures
Using Stored Procedures Exercise
Tools versus Applications
Tools versus Applications Exercise
Performance
Performance Exercise

DB2 for z/OS Stored Procedures

Objective
Describe DB2 stored procedures and discuss z/OS-related platform requirements and dependencies
Discuss and demonstrate IBM Data Studio capabilities
Describe and demonstrate Debug tool for z/OS capabilities
Create and test-drive a stored procedure
Create and run stored procedure client programs
Create and test-drive a stored procedure that returns result sets
Create and run stored procedure client programs that receive result sets
Key topics

Introduction to DB2 Stored Procedures
Describe what a stored procedure is
Determine when a stored procedure should be used
Describe execution and application flow using stored procedures
Have a basic understanding of z/OS components that are required to run stored procedures
Describe DB2 interfaces for controlling and defining stored procedures
Locate stored procedures-related DB2 Catalog information
Debugging Stored Procedures written in Legacy Programming Languages (Debug Tool for z/OS)
Describe Debug Tool for z/OS
Identify the capabilities of Debug Tool for z/OS
Use Debug Tool for z/OS: DB2 for z/OS stored procedures and Invokers of stored procedures
Writing COBOL Stored Procedures
Understand the stored procedure parameter types and styles
Use stored procedure performance and build options: Program Type SUB/MAIN, STAY RESIDENT YES, Language Environment parms (RUN OPTION), and Compile/Linkedit
Decide on the error handling and reporting to be used in your stored procedure
Develop and test a stored procedure that: Receives and returns parameters, Uses indicator variables to pass nullable parameters, and Uses PARAMETER STYLE SQL
Invoking Stored Procedures
Identify correct CALL syntax
Set up for Client CALL to stored procedure
Develop a Client Program that invokes a stored procedure
Returning Result Sets
Develop a stored procedure that returns result sets to its client application
Use sequential files as input for stored procedures result sets
Return a cursor that joins non-DB2 resources with DB2 tables
Receiving Result Sets
Describe the two alternatives for receiving result sets
Identify the preparation steps that must be taken in a client application to receive: A fixed number of result sets and An unknown number of result sets
Prepare and test a client program that receives a fixed number of result sets
IBM Data Studio
Use IBM Data Studio to create, edit, and deploy SQL external and native stored procedures
Use IBM Data Studio to run a stored procedure
Use IBM Data Studio to display schema objects
Use DB2 command line processor to invoke DB2 for z/OS stored procedures
SQL Stored Procedures
Create an external SQL procedure
Create a native SQL procedure
Avoid ambiguity with procedure names
Debugging Native SQL Stored Procedures using the Unified Debugger
Alter or create a Native SQL Stored Procedure to: Allow Debug Mode, Disallow Debug Mode, and Disable Debug Mode
Use the Unified Debugger to: View Source, Set Breakpoints, and View and change variable values

DB2 for z/OS Utilities for Database Administrators

Objective
Use RUNSTATS to accelerate the performance of a given SQL access
Employ DSN1COPY, REPAIR, and DIAGNOSE for DBA tasks which are not recovery-oriented
Use parallel index build with the LOAD, REORG and REBUILD INDEX utilities
Load partitions in parallel
Determine the appropriate LOAD options to use while considering concurrency and performance requirements
Choose appropriate options with REORG and UNLOAD to achieve optimal performance and availability
Provide appropriate LISTDEF, TEMPLATE, and OPTIONS utility control statements for use in DB2 utilities
Key topics
RUNSTATS, DSN1COPY,REPAIR, and DIAGNOSE
LOAD and REBUILD INDEX performance and availability
Online CHECK DATA
REORG performance and availability
UNLOAD performance and availability
Generic utility jobs (LISTDEF and TEMPLATE)
Clone tables
The course includes extensive machine exercises.

DB2 for z/OS Application Data Recovery

Objective
Design and implement application data backup and recovery procedures for their installations
Key topics
Backup and recovery basics
Describe the major situations requiring the recovery of data
Explain the basic DB2 logging concept
Explain the basic DB2 backup and recovery concept
Describe the restrictive and advisory states relevant for backup and recovery
Explain the purpose of the Logical Page List and of Write Error Page Ranges
Normal backup of table and index spaces (normal backup in this context means the creation of DB2 image copies using the COPY utility)

Create full and incremental image copies or inline copies for table spaces
Create conditional image copies for table spaces
Create image copies for indexes
Establish additional copies of image copies
Merge full and incremental image copies to obtain new full or incremental image copies
Delete image copies no longer needed
Normal recovery of table and index spaces (normal recovery in this context means the recovery using DB2 image copies and the RECOVER utility)

Prepare the recovery of a table space or index space
Recover a table space or COPY enabled index space to current (an entire table space or index space; a partition or data set; an error range)
Replace defective volumes during recovery
Understand the fallback recovery concept
Rebuild indexes
Point-in-time recovery (normal recovery to an earlier point in time for table spaces and index spaces)

Plan for point-in-time recoveries
Prepare point-in-time recoveries
Perform point-in-time recoveries involving table spaces other than LOB table spaces
 
Generic utility jobs (LISTDEF, TEMPLATE, and OPTIONS as far as they are relevant for backup and recovery)

Provide reusable list definitions for use by the DB2 utilities
Use list definitions in utility control statements and understand how they expand
Describe the concept and working of templates replacing utility Dataset Definition (DD) statements
Use templates in utility jobs to simplify Job Control Language (JCL)
Use templates together with list definitions
Use the OPTIONS utility control statement to control execution parameters for utility jobs
 
Using nonstandard copies (for the backup and recovery of table spaces or index spaces)

Establish DB2 Data Facility Storage Management Subsystem (DFSMS) concurrent copies using the CONCURRENT option of the COPY utility
Restore DB2 DFSMS concurrent copies established using the CONCURRENT option
Understand the prerequisites for establishing nonstandard copies
Establish and restore nonstandard copies using the DSN1COPY stand-alone utility
Establish and restore nonstandard copies using native DFSMS Data Set Services (DFSMSdss)
Apply log changes to restored copies
 
Special recovery situations (including down-level data, the recovery of dropped objects, and the repair of pages)

Handle down-level data
Prevent the accidental dropping of tables, table spaces, or databases
Recover accidentally dropped table spaces
Recover accidentally dropped tables
Print and analyze pages of table spaces or partitions
Repair pages of table spaces if necessary
Application recovery (how application programs should be structured to preclude recovery problems and what can be done if they do not follow these recommendations)

Convince application programmers to structure programs into committable units
Teach application programmers how to make programs restartable
Advise system programmers in cancelling threads without backout being performed
Instruct system programmers how to postpone backout work during DB2 restarts
Cancel the postponed backout for objects being recovered or replaced

DB2 for z/OS Application Performance and Tuning

Objective
Design better indexes
Determine how to live with the optimizer (avoid pitfalls, help when necessary)
Avoid locking problems
Use accounting traces to find significant performance problems in an operational application
Key topics
Introduction to Application Performance and Tuning
Why Performance Disappoints
Causes of Performance Problems
A Simple Example
SQL EXPLAIN Information
Modify the Index
The Accounting Trace
Accounting Trace Information
Filter Factors
Another Index Modification
Who Detects Problems?
When Should Problems be Detected?
VQUBE2
What is a Touch?
Further Analysis (1 of 2)
Further Analysis (2 of 2)
The Message
Performance Analysis Tools
Components of Response Time
Methodology Formulas
VQUBE2
Simple Example in VQUBE2
SQL EXPLAIN
The Plan Table
Running the Explain
Interpreting the Results
The Accounting Trace
Reading an Accounting Trace
The Bubble Chart
Monitoring Execution
Performance Thresholds
Tuning Potential
Towards Better Indexes
Section 3.1 DB2 Index Structure and Usage, Part 1
Section 3.2 DB2 Index Structure and Usage, Part 2
Section 3.3 Tuning Methodology and Index Cost
Section 3.4 Lab 1: Customer Order Application
Section 3.5 Index Design
Section 3.6 Lab 2: Poorly Performing Application
Machine Exercise 1 - Designing Indexes
Section 3.7 Advanced Access Paths
Multiple Table Access
Section 4.1 Joining Issues
Section 4.2 Lab 3: Joining Tables
Section 4.3 Subqueries and Global Query Optimization
Section 4.4 Lab 4: Different Implementations of One Problem
Section 4.5 Union, Except, and Intersect
Machine Exercise 2 - Subqueries
Towards Better Tables
Section 5.1 Table Design
Section 5.2 Materialized Query Tables
Machine Exercise 3 - MQTs and Denormalized Tables
Learning to Live with the Optimizer
Section 6.1 Dangerous Predicates
Machine Exercise 4 - NonIndexable and NonBoolean Predicates
Section 6.2 Lab 5: Introduction to Browsing
Section 6.3 Filter Factors
Section 6.4 Helping the Optimizer
Machine Exercise 5 - Helping the Optimizer
Massive Batch
Section 7.1 Batch Performance Issues
Section 7.2 Benefit Analysis
Section 7.3 Massive Deletes
Locking Issues
Section 8.1 Serialization
Section 8.2 Transation Locking
Section 8.3 Application Design
Machine Exercise 6 - Locking Issues
Course Summary
Components of CPU Time

DB2 for z/OS for Systems and Operations Personnel

Introduction to DB2 for z/OS for Systems and Operations Personnel

Objective
Describe a DB2 for z/OS environment
Use SPUFI to create and execute SQL statements
Create and utilize DB2 Objects
Identify and execute DB2 commands
Set up and execute DB2 utilities
Identify and utilize DB2 concurrency options
Understand and utilize DB2 logging and recovery processes
Describe DB2 program preparation
Perform basic DB2 database administration tasks
Key topics
Relational Database Concepts
Structured Query Language
DB2 Object Definition
DB2 Environment Components
DB2 Locking
DB2 Commands
DB2 Utilities
DB2 Logging
DB2 Startup and Shutdown
DB2 Program Preparation

DB2 for z/OS Index Modeling and Design

Objective
Describe different index formats and reasons for using indexes
Design indexes for matching, screening, index only, clustering, list prefetch, partitioning, and avoiding unnecessary sorts
Use RUNSTATS options related to indexes
Model the best indexes for Star Schema
Recognize superfluous indexes and eliminate them
Evaluate benefits of index compression
Run the operational utilities for indexes
Key topics

Generalities
Describe the general structure of DB2 indexes
Discuss the advantages of Type 2 indexes used in DB2
Different Index Forms and Distinct Reasons for Using Indexes
List many kinds of indexes that can be created
Differentiate between index matching and index screening
Discuss advantages and pitfalls of list prefetch access
Understand the meaning of multiple index access
Create indexes for index only access and for avoiding sort
Highlight the importance of clustering index
Distinguish between partitioning and partitioned indexes
Index Enhancements in DB2 V9
Discuss the indexing enhancements introduced in DB2 V9
Uniqueness and parallelism for Data Partitioned Secondary indexes
Index on expression
Automatic creation of indexes
Asymmetrical split of index pages
Detection of unused indexes
Renaming an index
RANDOM index keys
Index page size larger than 4 KB
Index compression
Page range screening enhancements
Online REBUILD INDEX
Indexes for Joins
Create indexes to enable joins to perform efficiently
Create indexes to enable subqueries to perform efficiently
Appreciate the importance of clustering and impact on joins
Index Versioning
Discuss index versioning
Understand when new index versions are created
Less Known Index Features
Discuss some of the less known index features
Differentiate between creating index with DEFINE NO and DEFER YES options
Explain index look aside technique and when it is used
Understand the importance of system parameter NPGTHRUSH prior to DB2 V8
Understand the significance of volatile tables since DB2 V8
Operational Utilities for Indexes
Describe the operational considerations for indexes
Enable indexes for image copies
Differentiate between RECOVER INDEX and REBUILD INDEX
Index Cost
Perform the cost-benefit analysis of indexes
Take steps to reduce index costs without compromising on performance
Indexes for Star Schema
Create indexes on FACT table and Dimension tables
Highlight enhancements introduced in DB2 V9 for Star Joins

Agenda

Day 1

Welcome
Unit 1 - Generalities
Unit 2 - Different index formats and distinct reasons for using indexes

Day 2

Unit 2 - Different index formats and distinct reasons for using indexes
Unit 3 - Indexing enhancements in DB2 V9

Day 3

Unit 4 - Indexes for join
Unit 5 - Index versioning
Unit 6 - Less known index features
Unit 7 - Operational utilities for indexes
Unit 8 - Index cost
Unit 9 - Indexes for Star Schema

Sl. No. Training Start Date Timing Eligibility
1 Mainframe Fresher   Weekdays B.E, MCA (Freshers)
2 Mainframe – Application DB2 DBA Training   Weekdays / Weekends Mainframe 2+ exp. Professionals
3 Mainframe – Systems DB2 DBA Training   Weekdays / Weekends Mainfram 2+ exp. Professionals
4 Mainframe – Application IMS DBA Training   Weekdays / Weekends Mainfram 2+ exp. Professionals
5 Mainframe – Systems IMS DBA Training   Weekdays / Weekends Mainfram 2+ exp. Professionals

Application programming & System Programming

Duration:2 Months

Application Programming

Sl. No. Modules Duration
1 TSO/ISPF 2 Days
2 JCL 3 Days
3 COBOL 5 Days
4 VSAM 3 Days
5 DB2 7 Days
6 CICS 5 Days
7 COBOL JCL VSAM DB2 CICS Project 5 Days
  Total Duration 30 Days

System Operations, Tools & Utilities, Project work

Sl. No. Modules
1 z/OS / JES / RACF / DFSMS
2 Xpeditor/Endevor/Fileaid
3 Soft skill – Aptitude tests / GD etc..
4 Live Project
5 Technical /Interview Preparation
  Total Duration 20 Days

Advantages at Mainframe Professionals

  • Unlimited Live Mainframe access.
  • Course ware
  • Technical training by senior trainers
  • Aptitude tests, Group discussions
  • Soft skill training by corporate trainers.
  • Interview preparations by industry experts.
  • 100% Placement assistance

Eligibility: MF with 2+ experienced professionals. Weekend / part time batches.

Duration: 4-5 weeks.

© All rights reserved | Privacy policy Login Sitemap