chapter 12: database managers on z/os
DESCRIPTION
Chapter 12: Database Managers on z/OS. Chapter objectives. Be able to: Explain how databases are used in a typical online business. Describe two models for network connectivity for large systems. List common DB2 data structures. Compose simple SQL queries to run on z/OS. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/1.jpg)
Introduction to z/OS Basics
© 2006 IBM Corporation
Chapter 12: Database Managers on z/OS
![Page 2: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/2.jpg)
Chapter 12 Databases
© 2006 IBM Corporation2
Chapter objectives
Be able to: Explain how databases are used in a
typical online business. Describe two models for network
connectivity for large systems. List common DB2 data structures. Compose simple SQL queries to run on
z/OS. Give an overview of application
programming with DB2. Describe the IMS DB components List common IMS DB structures
![Page 3: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/3.jpg)
Chapter 12 Databases
© 2006 IBM Corporation3
Key terms in this chapter
bind DBMS EXPLAIN modified source database DBMS root segment
SPUFI SQL SYSADM view SQL
![Page 4: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/4.jpg)
Chapter 12 Databases
© 2006 IBM Corporation4
What is a database?
A database provides for the storing and control of business information, independent from (but not separate from the processing requirements of) one or more applications.
![Page 5: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/5.jpg)
Chapter 12 Databases
© 2006 IBM Corporation5
Database example
Relationships
Shipment Customer Customer Order
Part
Purchase Order
Shipment
to Customer
Customer
Orders Parts
Order for Part
Purchase of Part
Attributes
Part No
Name
Unit Price
Shipment No
Dispatch
Date
Customer No
Customer
Address
Order No
Quantity
Delivery
Address
Order No
Quantity
EntitiesAttributes
Relationshipsentity
Note: An attribute is always dependent onan entity – it has no meaning by itself
{ one-to-one one-to-many
many-to-manyNote: Relationships can
be recursive
![Page 6: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/6.jpg)
Chapter 12 Databases
© 2006 IBM Corporation6
Why use a database?
Reduce programming effort Manage data more efficiently Easy to separate confidential/sensitive info Provide a greater level of security Access & update simultaneously Ensure consistency Provide backup and recovery Utilities to monitor and tune Structure change does not impact existing developments
![Page 7: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/7.jpg)
Chapter 12 Databases
© 2006 IBM Corporation7
Role of the database administrator
Provides standards for databases;administers
databases
Determines rules for accessing data and monitors its security
Approves the use of any programs that access production
databases
Guides, reviews and approves database
designs
Controls database integrity & availability; monitors activities for backup and recover
DBADBA
What usually is the DBA not responsible for?
![Page 8: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/8.jpg)
Chapter 12 Databases
© 2006 IBM Corporation8
Databases: terminology
Entities
Data attributes
Entity relationships
Application functions
Access paths
![Page 9: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/9.jpg)
Chapter 12 Databases
© 2006 IBM Corporation9
Databases on z/OS
Hierarchical databases, such as IMS
Relational database management system (RDBMS), such as DB2
Is VSAM considered a database ?
![Page 10: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/10.jpg)
Chapter 12 Databases
© 2006 IBM Corporation10
Hierarchical DB : Relationships & sequenceLevel 1 ROOT
(1)
Segment B(7)
Segment C(9)
Segment A(2)
Segment F(8)
Segment D(3)
Segment E(4)
Segment G(10)
Segment H(12)
Segment I(5)
Segment J(6)
Segment K(11)
Level 2
Level 3
Level 4
![Page 11: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/11.jpg)
Chapter 12 Databases
© 2006 IBM Corporation11
Hierarchical data structure
Level 1 (or root)PART
STOCK PURCHASEORDER
DETAIL DETAIL
Level 2
Level 3
Parent of STOCKand
PURCHASE ORDER
Child of Partand
Parent of DETAIL
Child of PURCHASE
ORDER
![Page 12: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/12.jpg)
Chapter 12 Databases
© 2006 IBM Corporation12
Segment types and their relationships
STOCK12
DETAIL112
PART1
STOCK11
ORDER11
DETAIL111
Record 1
PART2
STOCK21
ORDER21
DETAIL211
Record 2
PART1
STOCK31
ORDER31
DETAIL311
Record 3
ORDER22
These are twins
Siblings
All segments are dependents of PART
Parent of DETAIL
DETAIL is:Dependent of ORDERDependent of PARTChild of ORDERGrandchild of PART
![Page 13: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/13.jpg)
Chapter 12 Databases
© 2006 IBM Corporation13
DB2 – The Relational Database
Relational Structures include:
Database: A logical grouping of data for one or more applications
Table: A logical structure composed of rows and columns
Index(es): an ordered set of pointers to rows of a table (ensures uniqueness)
Keys: One or more columns that are identified as such in the creation of a table or used for
referential integrity
![Page 14: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/14.jpg)
Chapter 12 Databases
© 2006 IBM Corporation14
Example of a DB2 Department Table i.e. “owner.DEPT”
At the intersection of every column and row is aspecific data item called a value or more preciselyan atomic value
![Page 15: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/15.jpg)
Chapter 12 Databases
© 2006 IBM Corporation15
DB2 Administration (transactional interfaces)
SQL Processor Using File Input (SPUFI) A SQL interface through TSO providing a means for a
transactional facility used by DBAs. This requires knowledge of ISPF and basic PDS.
Pronounced “Spoo Fee”
Query Management Facility (QMF) Is a tightly integrated, powerful, and reliable tool that performs
query and reporting for DB2. It offers an easy-to-learn, interactive interface. Users with little or no data processing experience can easily retrieve, create, update, insert, or delete data that is stored in DB2.
![Page 16: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/16.jpg)
Chapter 12 Databases
© 2006 IBM Corporation16
DB2I SPUFI Panel
![Page 17: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/17.jpg)
Chapter 12 Databases
© 2006 IBM Corporation17
The SPUFI edit panel: After entering an SQL statement
![Page 18: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/18.jpg)
Chapter 12 Databases
© 2006 IBM Corporation18
SPUFI Result Dataset from previous SQL
![Page 19: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/19.jpg)
Chapter 12 Databases
© 2006 IBM Corporation19
Query Management Facility
![Page 20: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/20.jpg)
Chapter 12 Databases
© 2006 IBM Corporation20
QMF provides results in 4 easy steps
![Page 21: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/21.jpg)
Chapter 12 Databases
© 2006 IBM Corporation21
Relational DBMS: Codds relational principles
Primary key
Referential Integrity
Easy to use query language
Nulls Normalization/Denormalization
– 1NF: structure of a table– 2NF: 1-to-1– 3NF: 1-to-many relationships– 4NF, 5NF: many-to-many relationships
![Page 22: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/22.jpg)
Chapter 12 Databases
© 2006 IBM Corporation22
Relational DBMS: data structures and SQL Data Structures Databases Tables : column, row and value Indexes Keys
– Primary Key – only one because it defines the entity (i.e. Lastname)– Unique Key – another key also used for access (i.e. SSN)– Foreign Key – used for referential integrity between keys of different tables
SQL: High level language for relational structures DML: SELECT, UPDATE, INSERT, DELETE DDL: CREATE, ALTER, DROP DCL: GRANT, REVOKE
![Page 23: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/23.jpg)
Chapter 12 Databases
© 2006 IBM Corporation23
A database comparison:
IMS:– Data is relatively static
– Navigational : need to know the structure to get to the right data
DB2:– Changeable info
– Change in structure : no impact on existing application
– Non-Navigational : no need to know the structure to get to the right data (just tablename and columnname(s))
![Page 24: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/24.jpg)
Chapter 12 Databases
© 2006 IBM Corporation24
Summary
Interaction with the computer happens online through the help of a transaction manager.
Many transaction managers and database managers exist, but their principles are similar.
Data can be stored in a flat file, but this can result in duplication or inconsistent data. It is better to create central databases, which can be accessed (reading and changing) from different places.
The handling of consistency, security, etc. is done by the database management system.
![Page 25: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/25.jpg)
Chapter 12 Databases
© 2006 IBM Corporation25
Elements of DB2
Data Structures – used to organize user data
VIEW TABLESPACE INDEXSPACE STORAGE GROUP System Structures – controlled by DB2
DB2 is a multi-address space subsystem requiringa minimal of three address spaces -System Services- Database Services- Lock Manager Services (IRLM)
Note: Distributed Data Facility (DDF) is used to communicate with other DB2 Subsystems
Address spaces
![Page 26: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/26.jpg)
Chapter 12 Databases
© 2006 IBM Corporation26
Address Spaces and Component Interfaces
![Page 27: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/27.jpg)
Chapter 12 Databases
© 2006 IBM Corporation27
Basic Functions of each Service 1 of 3
![Page 28: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/28.jpg)
Chapter 12 Databases
© 2006 IBM Corporation28
Basic Functions of each Service 2 of 3
DB2 Sys A
DB2 Sys B
VTAM Open ACB
![Page 29: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/29.jpg)
Chapter 12 Databases
© 2006 IBM Corporation29
Basic Functions of each Service 3 of 3
![Page 30: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/30.jpg)
Chapter 12 Databases
© 2006 IBM Corporation30
DB2 Design Concepts
Resource Managers (RMID) - Software constructs responsible for managing a particular resource i.e. DASD, Main Storage, System Service (RDS)
DB2 tasks and Agents - Subcomponents that run inside the Allied Address Space (ie. Attachment Facilities) having task structures dictated by their particular function
* Allied Agents (originating in Allied address space) * System Agents (work requests internal to DB2)
Resource Locking - Latching: Used for short term serialization of internal DB2 resources performed by agent services manager (i.e. storage or control blocks) - Locking: The Lock Manager (IRLM) used to protect sections of a database (i.e. P-Locks / L-Locks)
![Page 31: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/31.jpg)
Chapter 12 Databases
© 2006 IBM Corporation31
How users communicate with DB2
Attachment Facilities* CICS Attachment facility (CA)* Call Attachment Facility (CAF)* IMS Attachment Facility (IA)* TSO Attachment Facility (TA)* Recoverable Resource Manager Services attachment facility (RRSAF)
Note: In a data sharing environment, each DB2 subsystem that is a member of the data sharing group can run on a different MVS system in the sysplex. BUT, the DB2 attachment interfaces only attach to a DB2 subsystem running on the same MVS system as the application.
DB2 Sys A
DB2 Sys B
i.e.Local CICS
i.e.LocalIMS
![Page 32: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/32.jpg)
Chapter 12 Databases
© 2006 IBM Corporation32
DB2 Connection Process
•Levels of authorization (identity) - MVS Subsystem Interface Facility (RACF) - Program Call (PC) Linkages • Sign On for CICS or IMS user connection only - Authorization Exit Routine N/A to TSO - able to access DB2 resources
• Thread Creation (control structure that connects an agent to a DB2 resource) - Application Plan (or Plan) Data Base request Module (DBRM) - Application Package (subset of a plan)
![Page 33: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/33.jpg)
Chapter 12 Databases
© 2006 IBM Corporation33
DB2 CatalogThe DB2 catalog consists of tables of data about everythingdefined to the DB2 system. The DB2 catalog is contained in system database DSNDB06.
To illustrate the use of the catalog, here is a brief description of some of what happens when the employee table is created:
* To record the name of the structure, its owner, its creator, its type (alias, table, or view), the name of its table space, and the name of its database, DB2 inserts a row into the catalog table SYSIBM.SYSTABLES.* To record the name of the table to which the column belongs, its length, its data type, and its sequence number in the table, DB2 inserts rows into SYSIBM.SYSCOLUMNS for each column of the table.* To increase by one the number of tables in the table space DSN8S51E, DB2 updates the row in the catalog table SYSIBM.SYSTABLESPACE.* To record that the owner (DSN8510) of the table has all privileges on the table, DB2 inserts a row into table SYSIBM.SYSTABAUTH.
Because the catalog consists of DB2 tables in a DB2 database, you can use SQLstatements to retrieve information from it.
System Structure
![Page 34: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/34.jpg)
Chapter 12 Databases
© 2006 IBM Corporation34
DB2 DirectoryThe DB2 directory contains information required to start DB2, and DB2 uses the directory during normal operation. You cannot access thedirectory using SQL. The structures in the directory are not described in the DB2 catalog.
The directory consists of a set of DB2 tables stored in five table spaces in system database DSNDB01. Each of the following table spaces is contained in a VSAM linear data set:
1. SCT02 is the skeleton cursor table space (SKCT). 2. SPT01 is the skeleton package table space. 3. SYSLGRNX is the log range table space. 4. SYSUTILX is the system utilities table space. 5. DBD01 is the database descriptor (DBD) table space.
System Structure
![Page 35: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/35.jpg)
Chapter 12 Databases
© 2006 IBM Corporation35
DB2 Hierarchy Structure
![Page 36: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/36.jpg)
Chapter 12 Databases
© 2006 IBM Corporation36
DB2 Concepts: Data Structures
VSAMLDS
VSAMLDS
Storage group
Data base
Table Space
Table
Index Space
Index
Views
![Page 37: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/37.jpg)
Chapter 12 Databases
© 2006 IBM Corporation37
DB2 Table Create and inserting a row (record)
![Page 38: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/38.jpg)
Chapter 12 Databases
© 2006 IBM Corporation38
Creating a primary key (index)
![Page 39: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/39.jpg)
Chapter 12 Databases
© 2006 IBM Corporation39
Sample of index set and pointers
![Page 40: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/40.jpg)
Chapter 12 Databases
© 2006 IBM Corporation40
Sample DDL for a DB2 Table/View
Table VIEW
![Page 41: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/41.jpg)
Chapter 12 Databases
© 2006 IBM Corporation41
Schema structures
User-defined Data Type (UDT) User-defined Function (UDF) Triggers Large Object (LOB) Stored Procedure
![Page 42: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/42.jpg)
Chapter 12 Databases
© 2006 IBM Corporation42
Referential Integrity defining table relationships
![Page 43: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/43.jpg)
Chapter 12 Databases
© 2006 IBM Corporation43
System Structure
Catalog & Directory: stores ALL DB2 information
Buffer Pool Active and Archive Logs Bootstrap data set (BSDS)
![Page 44: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/44.jpg)
Chapter 12 Databases
© 2006 IBM Corporation44
Examples of other Data Definition Language (DDL)
![Page 45: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/45.jpg)
Chapter 12 Databases
© 2006 IBM Corporation45
DB2 for z/OS Architecture
DB2 Address Spaces– System Service address space (SSAS)
– Database Service address space (DBAS)
– Internal Resource Lock Manager (IRLM)
DB2 Attachment Facilities– CICS
– IMS
– TSO
![Page 46: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/46.jpg)
Chapter 12 Databases
© 2006 IBM Corporation46
Invoke SQL on z/OS: SPUFI
Select option 1 to enter SPUFI
First you need to create the output file (if not existing)
![Page 47: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/47.jpg)
Chapter 12 Databases
© 2006 IBM Corporation47
Invoke SQL on z/OS: SPUFI (CONT…)
Enter the input and output dataset, if they are not yet in place.Change the member of the PDS, if you want to enter a new SQLDefaults are set to NO from YES.
![Page 48: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/48.jpg)
Chapter 12 Databases
© 2006 IBM Corporation48
Invoke SQL on z/OS: SPUFI (CONT…)
Enter the SQL statement you want to execute. Press F3 to return to the previous screen (to execute the SQL).
![Page 49: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/49.jpg)
Chapter 12 Databases
© 2006 IBM Corporation49
Invoke SQL on z/OS: SPUFI (CONT…)
When you get back to this screen, the “edit input” is put to “*”.Press ENTER to execute the SQL and to see the output.
![Page 50: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/50.jpg)
Chapter 12 Databases
© 2006 IBM Corporation50
Invoke SQL on z/OS: SPUFI (CONT…)
F8 brings the rest of the results on your screen
![Page 51: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/51.jpg)
Chapter 12 Databases
© 2006 IBM Corporation51
Invoke SQL on z/OS: SPUFI (CONT…)
![Page 52: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/52.jpg)
Chapter 12 Databases
© 2006 IBM Corporation52
Application Programming: the flow
SourceSourceProgramProgram
Precompile
IncludeIncludeMemberMember
Compile
Linkedit
ModifiedSource
Object Module
Load Module
DBRM
Bind
Package
Bind
Plan
DCLGEN
RUN
![Page 53: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/53.jpg)
Chapter 12 Databases
© 2006 IBM Corporation53
Get the access path: EXPLAIN
EXPLAIN ALL SET QUERYNO = 1 SELECT EMPNO, LASTNAME FROM EMP WHERE LASTNAME = 'MILLER';
-The query is NOT executed-The access path is placed in userid.PLAN_TABLE, if it exists
![Page 54: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/54.jpg)
Chapter 12 Databases
© 2006 IBM Corporation54
Creating an Explain Table
![Page 55: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/55.jpg)
Chapter 12 Databases
© 2006 IBM Corporation55
Managing DB2: System Administration (SYSADM)
Installation System Object Management System and Disaster Recovery Monitoring System Performance
“Contains all privileges for the entire DB2 Subsystem”
![Page 56: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/56.jpg)
Chapter 12 Databases
© 2006 IBM Corporation56
Managing DB2: Database Administration (DBADM)
Creation & Management of DB2 Objects for a particular DB2 Database
Execution of Utilities:–Data Organization
–Backup & recovery
–Data Consistency Commands
![Page 57: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/57.jpg)
Chapter 12 Databases
© 2006 IBM Corporation57
Administrative Authorities
![Page 58: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/58.jpg)
Chapter 12 Databases
© 2006 IBM Corporation58
Example of LOAD Utility
![Page 59: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/59.jpg)
Chapter 12 Databases
© 2006 IBM Corporation59
Note: This is the TSO Background Program
DB2 uses TSO IKJEFT01
This example we are terminating a suspended utility job
![Page 60: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/60.jpg)
Chapter 12 Databases
© 2006 IBM Corporation60
Reorg Utility Example
![Page 61: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/61.jpg)
Chapter 12 Databases
© 2006 IBM Corporation61
DSNUPROC – creating JCL yourself
![Page 62: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/62.jpg)
Chapter 12 Databases
© 2006 IBM Corporation62
DB2 Commands Part 1 of 2
![Page 63: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/63.jpg)
Chapter 12 Databases
© 2006 IBM Corporation63
DB2 Commands Part 2 of 2
![Page 64: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/64.jpg)
Chapter 12 Databases
© 2006 IBM Corporation64
Information Management System
![Page 65: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/65.jpg)
Chapter 12 Databases
© 2006 IBM Corporation65
Functions of the IMS database manager
A DBMS provides:– Multiple-user access to a single copy of data
– Integrity for all updates
– Minimal hardware and OS access method dependencies
– Reduced data redundancy
![Page 66: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/66.jpg)
Chapter 12 Databases
© 2006 IBM Corporation66
Implementation of IMS Databases
Depending on user' requirements Technologies :
– IMS DB or DL/I or DL1 or Full Function Database
– IMS DEDB or Data Entry DB or Fast Path Database
– IMS Main storage database (MSDB)
– IBM DB2
Database Recovery Control (DBRC)
![Page 67: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/67.jpg)
Chapter 12 Databases
© 2006 IBM Corporation67
Structure of IMS DB Subsystem
System Address SpaceApplication Region Address Space
Up to 99 in total
DLISeparateAddressSpace
DBRCRegion
MPP IFP BMP
ApplicationProgram
Full Function DBs RECONs
DependentRegion
AddressSpace
IMS System
IMS Libraries
IMS Message Queues
Logs
Fast Patch DBs
ControlRegion
AddressSpace
Network
JavaMessage
Processing
JavaBatch
ProcessingApplicationProgram
ApplicationProgram
![Page 68: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/68.jpg)
Chapter 12 Databases
© 2006 IBM Corporation68
Databases used by IMS: Database basics
Access paths Normalization within IMS
– Unique entities
– 1 occurrence only
– No many-to-many relationships
![Page 69: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/69.jpg)
Chapter 12 Databases
© 2006 IBM Corporation69
Databases used by IMS: DB Model
Sequence to access the segments
ROOT(1)
Segment A2(8)
Segment A1(2)
Segment D3(9)
Segment D2(4)
Segment E3(7)
Segment D1(3)
Segment E2(6)
Segment E1(5)
Segment B2(14)
Segment B1(10)
Segment G2(12)
Segment H1(13)
Segment G1(11)
![Page 70: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/70.jpg)
Chapter 12 Databases
© 2006 IBM Corporation70
Databases used by IMS: DB model
Additional access paths to segments– Logical relationships
– Secondary indices
![Page 71: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/71.jpg)
Chapter 12 Databases
© 2006 IBM Corporation71
Application programming overview
Program is subroutine of IMS region controller– Needs a program specification block (PSB)
– Uses services:• Send/receive message from terminals• Access db• Issue IMS commands• Issue IMS service calls
e.g. Checkpoint calls, Sync call
![Page 72: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/72.jpg)
Chapter 12 Databases
© 2006 IBM Corporation72
Program Structure
Segments Segments to/from to/from
databasesdatabases
ENTRy
EXIT
DLI modules
Call infoCall infofrom DLIfrom DLI
PCB-Mask
IO AREA
Application Program
PROGRAM ENTRYDEFINE PCB AREASGET INPUT RECORDS FROM INPUT FILECALLS TO DL/I DB FUNCTIONS
RETRIEVEINSERTREPLACEDELETE
CHECK STATUS CODESPUT OUTPUT RECORDSTERMINATION
![Page 73: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/73.jpg)
Chapter 12 Databases
© 2006 IBM Corporation73
IMS & the World Wide Web Message flow in IMS transaction
3270 Terminal
1,2
VTAM IMS MPP
3 4,5
8,9 7 6
NCP
![Page 74: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/74.jpg)
Chapter 12 Databases
© 2006 IBM Corporation74
IMS & the World Wide WebMessage flow between Web Browser & Web Server
Web Browser
1,2
WebServer
CGIProgram
3 4,5
8,9 7 6
TCP/IP TCP/IP
![Page 75: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/75.jpg)
Chapter 12 Databases
© 2006 IBM Corporation75
IMS & the World Wide Web
Message flow IMS transaction & Web Server CGI Programs
3270 Terminal
1,2
VTAM IMS MPP
3 4,5
8,9 7 6
NCP
Web Browser
1,2
WebServer
CGIProgram
3 4,5
8,9 7 6
TCP/IP TCP/IP
Program to ProgramCommunications
![Page 76: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/76.jpg)
Chapter 12 Databases
© 2006 IBM Corporation76
Summary
The relational database is the predominant approach to data organization in today's business world.
IBM’s DB2 implements such relational principles as primary keys, referential integrity, a language to access the database (SQL), nulls, and normalized design.
In a relational database, the most fundamental structure is the table with columns and rows.
![Page 77: Chapter 12: Database Managers on z/OS](https://reader038.vdocuments.us/reader038/viewer/2022103007/56815ae7550346895dc8aa09/html5/thumbnails/77.jpg)
Chapter 12 Databases
© 2006 IBM Corporation77
Summary (continued)
The only way to access the data in DB2 databases is with SQL.
On the mainframe, SPUFI is a tool used to enter SQL statements.
The DBRM performs a bind process that determines the access path and stores this executable SQL code in a package.
SQL can handle both static and dynamic statements, and EXPLAIN can be used to find out what access path the optimizer chose for the SQL.