DB2 for z/OS: Introduction, Concepts & Facilities


This course introduces, describes and explains the fundamental principles of DB2 for z/OS. The course also explains the relational theory and concepts, and shows how DB2 adheres to them. The concepts of DB2 data storage, the DB2 system and its operation, Structured Query Language, the use of DB2 Interactive (DB2I), and the management of DB2 data using standard utility tasks are also introduced and explained. The course includes demonstrations and practical, hands-on exercises, and is relevant to all releases of DB2 for z/OS up to and including Version 11.

Objectives

On successful completion of this course you will be able to:

  • describe the relational concepts and theory, and the advantages of a RDBMS
  • describe the components of the DB2 system, their function, and how they are controlled
  • identify the objects used to store DB2 data and the relationships between them
  • code the SQL statements needed to create and modify DB2 data objects, and control access to them
  • code SQL statements to read and modify DB2 data and interrogate the DB2 catalog
  • describe and fully utilise all the functions available with DB2 Interactive (DB2I)
  • use online utilities to backup and restore data, and perform other data management tasks
  • describe the use of stand-alone utilities and identify situations where they may be required.

Who Should Attend

All IT professionals who need to understand and work with DB2 for z/OS.

Prerequisites

A basic understanding of IT, with some exposure to database technology as a user. Whilst not essential, a knowledge of TSO/ISPF would be beneficial.

Duration

2 days

Fee (per attendee)

£850 (ex VAT)

Course Code

DC

Contents

DBMS Overview

Database Management Systems; Hierarchical database structure; Network database structure; Relational database structure; Database comparison; Table structure; A brief history of DB2.
This segment compares and contrasts the RDBMS with other database methodologies.

Relational Theory & Concepts

Dr. E F Codd's 12 rules; The relational model; Structure - tables; Structure - rules for columns and rows; Structure: candidate keys, primary and alternate key, foreign keys; Manipulation: relational algebra, projection, restriction, join, union, intersection, difference, product; Integrity - domain and user-defined integrity; Integrity - entity and referential integrity; Referential integrity - terminology; Tablespace set; Referential integrity - defining constraint; Constraint definition restrictions; Data manipulation restrictions; Catalog entries; Referential Integrity and INSERT; Referential Integrity and UPDATE; Referential Integrity and DELETE; Referential Integrity review.
This segment discusses the relational theory, and DB2's adherence to it.

DB2 System Architecture

Architecture overview; Working Storage Areas; Buffer, Sort, RID & EDM pools; Buffer Pools - 64 bit addressing; EDM pool; Sort pool; RID pool; DB2 Attachments; System data sets & databases; DB2 objects; Object characteristics; SQL overview; DB2 Interactive (DB2I); Basic DB2 operations; DB2 commands; DB2I commands panel.
This segment introduces the main components of the DB2 system, and how they are controlled.

Introduction to DB2 Interactive (DB2I)

DB2I primary option menu; DB2I option D - DB2I defaults; DB2I option 1 - SPUFI; Current SPUFI defaults; SPUFI SQL statement creation; Browsing SPUFI output; SPUFI commit or rollback panel; SPUFI AUTOCOMMIT options; DB2I option 2 - DCLGEN (Declarations Generator); DCLGEN output; DB2I option 3 - Program Preparation; DB2I option 4 - Precompile; DB2I option 5 - Bind / Rebind / Free; DB2I option 6 - Run; DB2I option 7 - DB2 commands; DB2I option 8 - DB2 utilities; Query Management Facility; Reporting options - SPUFI versus QMF 154; SPUFI report; QMF report.
This segment introduces DB2I and explains the various functions it provides.This segment introduces the main components of the DB2 system, and how they are controlled.

DB2 Data Objects

DB2 objects; The DB2 database; Tablespace; Simple tablespace; Segmented tablespace; Partitioned tablespace; Universal tablespace; Large object (LOB) & XML tablespaces; Determining tablespace type; Table; Synonym & alias; View; Index; Index structure; Storage group; User defined data sets; Schema; Trigger; DB2 catalog & directory.
This segment describes the main components used in DB2 data storage, and explains the need for and use of the DB2 catalog and directory databases

Introduction to SQL

Structured Query Language; Data Definition Language; Data Control Language; Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE, MERGE; COMMIT / ROLLBACK; DB2 optimizer.
This segment introduces SQL and gives examples of the most commonly used syntax

Managing DB2 Data with Utilities

DB2 utilities; Data backup & recovery utilities; System backup & recovery utilities; Data integrity & consistency utilities; Other online utilities; Stand-alone utilities; Using online utilities; Utility control statements; DB2I utilities - main menu; DB2I utilities - Data Set Names panel; DB2I utilities - Control Statement Data Set Names panel; Controlling utilities; Using LISTDEF & TEMPLATE; Using pattern matching; LISTDEF syntax; TEMPLATE; Data set sizing; Data set naming variables; TEMPLATE syntax; OPTIONS syntax.
This segment introduces the utility jobs provided with DB2 and demonstrates their use in managing DB2 data.

Hands-on Exercises

Using DB2I to code and execute SQL; Interrogating the DB2 catalog; using utilities to back-up and recover DB2 data; issuing DB2 commands.
These exercises are designed to reinforce understanding of the topics covered.

Q & A Session

Course wrap-up and Q & A session.


What the students say

Just what I was looking for.

Database Administrator

Produban UK

© RSM Technology 2017