BMC Mainframe: Db2 for z/OS - Introduction, Concepts & Facilities
The course is developed and delivered by © RSM Technology.
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.
Major release:
BMC Mainframe Infrastructure Platform Training
Good for:
Administrators
Course Delivery:
Instructor-Led Training (ILT) | 16 hours
Course Modules
-
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 datasets & 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
- DBIi 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
- Db2ioption 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 ontrolled.
-
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
- Dataset sizing
- Dataset 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