Unit Objectives
After completing this unit, you should be able to describe the main components of a DB2 environment and the roles they play: Users Data Programs (Static, Dynamic) System
Copyright IBM Corporation 2007
What Is DB2 for z/OS?
Relational Data Base Management System (RDBMS) Data base consists of tables
Simple concepts Dynamic relationships High level
Structured query language (SQL)
Data Manipulation Language (DML) Data Definition Language (DDL) Data Control Language (DCL)
User specifies WHAT not HOW
RDBMS facilities
Integrity Dynamic definition of DB2 objects Active catalog Recovery / restart Continuous operations Security Interactive tools
Copyright IBM Corporation 2007
The Users View
000010 000020 000030 000060 ... 000050 Chris Mike Sally Irving John I L A F B A00 B01 C01 D11 ... D01 Haas Thomson Kwain Stern Geyer A00 B01 C01 D11 E01 3978 3476 4738 6423 6542 1965-01-01 1973-10-10 1975-04-05 1973-09-14 1972-02-02 ...
Spiffy Computer Ser. Div. Planning Information Center Manufacturing System Development Center
000010 000020 000030 000060 ?
SELECT LASTNAME FROM EMP WHERE EMPNO = 000050
Result Data : Geyer
QMF / SPUFI
Copyright IBM Corporation 2007
Another Kind of User
000010 000020 000030 000060 ... 000050 Chris Mike Sally Irving John A00 B01 C01 D11 ... D01 I L A F B Haas Thomson Kwain Stern Geyer A00 B01 C01 D11 E01 3978 3476 4738 6423 6542 1965-01-01 1973-10-10 1975-04-05 1973-09-14 1972-02-02 ...
GET ... MOVE ... SELECT LASTNAME FROM EMP WHERE EMPNO = :EMPNO
Spiffy Computer Ser. Div. Planning Information Center Manufacturing System Development Center
000010 000020 000030 000060 ?
EMP NR ?
EMP NR ? 000050
LASTNAME : Geyer
Copyright IBM Corporation 2007
The Static Programs View
000010 000020 000030 000060 ... 000050 Chris Mike Sally Irving I L A F Haas Thomson Kwain Stern A00 B01 C01 D11 3978 3476 4738 6423 1965-01-01 1973-10-10 1975-04-05 1973-09-14
SELECT LASTNAME FROM EMP WHERE EMPNO = :EMPNO
EMP NR ? PUT
John A00 B01 C01 D11 ... D01
Geyer
E01
6542 000010 000020 000030 000060 ?
1972-02-02 ...
Spiffy Computer Ser. Div. Planning Information Center Manufacturing System Development Center
DL/1 VSAM
GET MOVE
EMP NR ? 000050
EXECUTE
SEQUENTIAL DATA SET
PUT
LASTNAME : Geyer
Copyright IBM Corporation 2007
The Dynamic Programs View
000010 000020 000030 000060 ... 000050 Chris Mike Sally Irving I L A F Haas Thomson Kwain Stern A00 B01 C01 D11 3978 3476 4738 6423 1965-01-01 1973-10-10 1975-04-05 1973-09-14
John A00 B01 C01 D11 ... D01
Geyer
E01
6542 000010 000020 000030 000060 ?
1972-02-02 ...
ENTER SQL: PUT
Spiffy Computer Ser. Div. Planning Information Center Manufacturing System Development Center
DL/1 VSAM
GET
SELECT ... FROM EMP WHERE ...
EXECUTE
SEQUENTIAL DATA SET
PUT
Geyer
Copyright IBM Corporation 2007
A Program's Responsibility A Logical Unit of Work
Logical Unit of Work
UPDATE A UPDATE B
COMMIT
A 2000 1000 B 3000 3000
UPDATE A
1000 4000
UPDATE B
1000 4000
ROLLBACK
A 2000 1000 B 3000 3000
1000 4000
Copyright IBM Corporation 2007
2000 3000
A program transfers $1000 from account A to account B
The Systems View
Distributed Applications
Utility Utility WebSphere Application Server TSO BATCH DB2 CICS IMS Trxb
Appl2
Prog2
Trxa
Appl1 Trx2
Prog1
Trx1
DB2 receives MANY requests from MANY user programs to access potentially the SAME data Besides USER PROGRAMS, DB2 also receives requests from UTILITIES
Copyright IBM Corporation 2007
Utilities
COPY
IMAGE COPY
REORG
LOAD
SEQUENTIAL DATASET
Work at the OBJECT level Run in BATCH Compete with user programs for resources Specify your requirements using CONTROL STATEMENTS mostly
Copyright IBM Corporation 2007
DB2s Responsibilities - SECURITY
DB2 must verify whether a request is LEGITIMATE DB2
UPDATE T1 SET ...
STOP
Pierre
OK
SELECT ... FROM T1
Jan
GRANT SELECT ON TABLE T1 TO JAN, PIERRE ; GRANT UPDATE ON TABLE T1 TO JAN ;
DB2 CATALOG Table T1 T1 User Jan Pierre Select Update Y Y Y N
Copyright IBM Corporation 2007
Data integrity and concurrency
EMPLOYEE Table
Prog1 Prog2
1
UPDATE
EXCLUSIVE LOCK
Row
STOP
... WAIT ... RELEASE LOCK
3
COMMIT
OK
SHARED LOCK
SELECT
...
4
DB2 must maintain data integrity and also provide maximum concurrency
Copyright IBM Corporation 2007
DB2 System Log Data Set
DB2
Data Buffer DATA
X Program
UPDATE INSERT Log Buffer COMMIT
X ASYNC
XXX SYNC
LOG DATASET
X X X
before
Copyright IBM Corporation 2007
after
COPY/RECOVER Utilities
UPDATE
UPDATE
DELETE
RECOVER
Log Time
COPY
DISK CRASH
IMAGE COPY
Copyright IBM Corporation 2007
DB2s Responsibilities - PERFORMANCE
Program
BIND WHAT HOW
DB2
Optimizer
Load Module
EXECUTION
Access Path
Catalog Statistics
DB2 must translate a users or programs SQL request into ACCESS STRATEGIES
Copyright IBM Corporation 2007
DB2s Responsibilities - METADATA
DB2 uses a centralized CATALOG and DIRECTORY
System Administrator Database Administrator Application Programmer
SQL
DB2 CATALOG
Data Definitions
Security Definitions
Recovery Information
Copyright IBM Corporation 2007
DB2 for z/OS Data Sharing
z/OS DB2 z/OS DB2 z/OS DB2
z/OS DB2 SHARED DASD
z/OS DB2
z/OS DB2
z/OS DB2
z/OS DB2
Improved availability Huge queries feasible SYSPLEX hardware required Single shared catalog
Incremental processing growth Configuration flexibility Dynamic workload balancing
Copyright IBM Corporation 2007
Roles and Interfaces in DB2
Security Administrator Operations System Administration
Functional Designer Technical Designer Database Administrator Programmer
Data Administrator Capacity Planner z/OS Sysprog CICS/IMS Sysprog TP Specialist
Copyright IBM Corporation 2007
Unit Summary
Having completed this unit, you should be able to describe the main components of a DB2 environment and the roles they play: Users Data Programs (Static, Dynamic) System
Copyright IBM Corporation 2007
