About this course

Course code TPDC
Duration 2 Days

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.

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.

Delegates will learn how 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.

Outline

DBMS Overview

Database Management Systems; Hierarchical database structure; Network database structure; Relational database structure; Database comparison; Table structure; A brief history of DB2.

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.

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.

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.

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.

Introduction to SQL

Structured Query Language; Data Definition Language; Data Control Language; Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE, MERGE; COMMIT / ROLLBACK; DB2 optimizer.

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.

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.

Q & A Session

Course wrap-up and Q & A session.

2 Days

Duration
Training delivered by an IBM Global Training Provider
Delivery Method

Delivery method

Classroom

Face-to-face learning in the comfort of our quality nationwide centres, with free refreshments and Wi-Fi.

Trusted, awarded and accredited

Fully accredited to ensure we provide the highest possible standards in learning

All third party trademark rights acknowledged.