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.
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
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
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