z/OS Introduction and Workshop

DB2 for z/OS

© 2017 IBM Corporation

© 2009 IBM Corporation
 Unit Objectives
After completing this unit, you should be able to:
•Describe DB2
•List 4 types of SQL
•List SQL statements
•Understand DB2 Programming Language APIs
•Understand DB2 Structures
•Understand Active and Archive Logging
•List 4 DB2 bufferpool sizes

2 © 2017 IBM Corporation

 Relational Data Base Technology
Previous database systems stored information in one long text file,
called a tab delimited file. Each entry in the tab delimited file is
separated by a special character and contained multiple pieces of
information (fields) about a particular object.

The text file makes it difficult to search for specific information

or to create reports that include only certain fields from each record.

A relational database allows you to easily find specific information.

It also allows you to sort based on any field and generate reports that
contain only certain fields from each record. Relational databases use
tables to store information. The standard fields and records are
represented as columns (fields) and rows (records) in a table.

Relational databases are created, updated and read using a special

computer language, structured query language (SQL), that is the
standard for database SQL is the foundation for all of the popular
database applications available today.

3 © 2017 IBM Corporation

 Relational Data Base Technology
Edgar Frank "Ted" Codd invented the relational model for database management
and the theoretical basis for relational databases while working for IBM.

While all relational databases follow Codd's theoretical basis, implementation

differences exist between the various relational databases. As a result porting
from one relational database platform to another relational database platform
frequently requires some SQL adjustments.

The good news is that if you understand any specific relational database, then
you can use that knowledge to quickly learn other relational database systems.

4 © 2017 IBM Corporation

 DB2 Concepts: Structured Query Language (SQL)

Database Administrator (DBA)

DDL - Data Definition Language(Structures)
DCL - Data Control Language (Security)

Application Developer
DML - Data Manipulation Language
TCL - Transactional Control Language

5 © 2017 IBM Corporation

 DB2 Concepts: Structured Query Language (SQL)
DDL - Data Definition Language
It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL - Data Control Language

It is used to create roles, permissions, and referential integrity as well it is used to control
access to database by securing it.

Examples: GRANT, REVOKE statements

6 © 2017 IBM Corporation

 DB2 Concepts: Structured Query Language (SQL)

DML - Data Manipulation Language.

It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

TCL - Transactional Control Language.

It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

7 © 2017 IBM Corporation

 DB2 Application Programming

Languages Application Program Interface (API)

COBOL EXEC SQL ….
Java
C/C++ DML select insert update
REXX DDL create drop alter
Assembler DCL grant revoke
PL/I TCL commit rollback

8 © 2017 IBM Corporation

 DB2 - Application Programming

SS oo uur r ccee
PP r r oo ggr r aamm

M o d if ie d P r e c o m p ile D BR M
S o ur ce

II nn cc ll uu dd ee B in d
C o m p ile
MM eemm bb eer r

D C LG EN
O b je c t P a ckage
M o d u le

L in k e d it B in d

L oad R U N P la n
M o d u le
9 © 2017 IBM Corporation
 DB2 - Application Programming

10 © 2017 IBM Corporation

 DB2 for z/OS Interfaces

DB2 tool set (3270 based)

SPUFI
DCLGEN
Bind/Rebind
Command Processor
Utilities
Defaults
Administration
Performance Expert
Control Center (GUI)
RDz (IDE) DB2 Workbench (GUI)
Data Studio (GUI)

11 © 2017 IBM Corporation

 DB2 for z/OS Disk Storage Allocation (volume)
z/OS controls many disk devices with unique volume labels

A DB2 created STOGROUP is assigned to use specific volume labels

z/OS DB2 DDL - CREATE STOGROUP name ADD VOLUME(……

z/OS DB2 DDL – ALTER STOGROUP name ADD|DELETE VOLUME(….

SYSDEFLT STOGROUP exists (best practice - never use it)

Each Database name is assigned to a STOGROUP

create [Link] is allocated on a volume from assigned STOGROUP

If DBA (SYSADM) creates databases without specifying STOGROUP,

then [Link] will use SYSDEFLT (which may have little disk space)

12 © 2017 IBM Corporation

 zOS DB2 One Table per Tablespace (Best Practice)
While it is common to create many tables in a single LUW tablespace,
DB2 for z/OS best practice is only one table per tablespace.

Yes – this means DDL needs to include:

CREATE TABLESPACE
for every
CREATE TABLE

Negative performance impact is the result of multiple tables in a single

tablespace in DB2 for z/OS environment.

13 © 2017 IBM Corporation

 DB2 for z/OS Tablespace Data Set Name

When a tablespace is created, a VSAM Linear Data Set is defined

with the following data set name format:

[Link].y0001.z001 (Cluster name)

[Link].y0001.z001 (Data component of cluster)

y0001.z001 (instance#. dataset #)

tsname (tablespace name)

dbname (database name)

vcat (typically an ‘alias’ in the master catalog)

14 © 2017 IBM Corporation

 DB2 for z/OS Data Structures

S to ra g e g ro u p

V S A M V S A M
LD S LD S

D a ta b a se

Tab le S p ace
V ie w s

Tab le

In d ex S p ace

In d ex

15 © 2017 IBM Corporation

 DB2 for z/OS Data Structures

‘*’ SMS managed storage (ACS routine) Create Stogroup name

volume label (s) Create Database name

‘*’
Create Tablespace name

[Link].** Create Table name

(table_name)

Create Index name

[Link].**
(index_name)
DB2 Catalog
(metadata)

16 © 2017 IBM Corporation

 DB2 – DBA View

1. Create Storage Group

a. disk volumes specified
b. catalog alias name (VCATNAME) specified
2. Create Database
a. storage group specified
b. buffer pool specified
3. Create Tablespace
a. database name specified
b. buffer pool specified
c. primary and secondary disk space quantity specified
4. Create Table
a. fields and respective data types specified
b. [Link] specified to contain table

17 © 2017 IBM Corporation

 DB2 DDL – CREATE STOGROUP and DATABASE

CREATE STOGROUP ACCTDISK VOLUMES (VPARC1) VCAT ACCOUNTS;

-------------------------------------------------------------------------------------------------------------
COMMIT ;
--------------------------------------------------------------------------------------------------------------
CREATE DATABASE ACCOUNTS STOGROUP ACCTDISK BUFFERPOOL BP0;

18 © 2017 IBM Corporation

 DB2 DDL – CREATE TABLESPACE and TABLE
CREATE TABLESPACE TS01 IN ACCOUNTS
USING STOGROUP ACCTDISK PRIQTY 20 SECQTY 20 ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0 CLOSE NO COMPRESS YES;
----------------------------------------------------------------------------------------------------
COMMIT;
----------------------------------------------------------------------------------------------------
CREATE TABLE ZIBM001.TBL01
(ACCTNO CHAR(8) NOT NULL,
LIMIT DECIMAL(9,2),
BALANCE DECIMAL(9,2),
SURNAME CHAR(20) NOT NULL,
FIRSTN CHAR(15) NOT NULL,
ADDRESS1 CHAR(25),
ADDRESS2 CHAR(20),
ADDRESS3 CHAR(15),
RESERVED CHAR(7),
COMMENTS CHAR(50),
PRIMARY KEY(ACCTNO))
IN ACCOUNTS.TS01;
19 © 2017 IBM Corporation
 DB2 DDL – CREATE INDEX
CREATE UNIQUE INDEX ZIBM001.XTBL01
ON ZIBM001.TBL01 (ACCTNO ASC)
USING STOGROUP ACCTDISK PRIQTY 12 ERASE NO
BUFFERPOOL BP0 CLOSE NO;

20 © 2017 IBM Corporation

 DB2 DCL – GRANT

GRANT DBADM ON DATABASE ACCOUNTS TO PUBLIC;

GRANT USE OF STOGROUP ACCTDISK TO PUBLIC;
GRANT STATS ON DATABASE ACCOUNTS TO PUBLIC;
GRANT USE OF TABLESPACE ACCOUNTS.TS01 TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE ZIBM001.TBL01 TO PUBLIC;

21 © 2017 IBM Corporation

 DB2 – DBA and Systems Programmer View

 System Parameters and DB2 ZPARMs

 JCL PROCLIB
 Boot Strap Data Set (BSDS)
 Active and Archive Logs
 Buffer Pools
 Distributed Data Facility (DDF)
 DB2 Directory (DSNDB01)
 DB2 Default Databases (DSNDB04)
 DB2 Catalog (DSNDB06)

22 © 2017 IBM Corporation

 DB2 for z/OS – Systems Programmer View

z/OS System Parameters - SSID and DB2 ZPARMs

SubSystem Name ID = SSID

PARMLIB (IEFSSNxx)
DB9G,DSN3INI,'DSN3EP,-DB9G,S,DB9G'

MVS command to start DB2 environment

-DB9G START DB2

[Link](DSNTIJUZ)
DSNZPARM – aka ZPARMs global parameter values

23 © 2017 IBM Corporation

 DB2 – DBA and Systems Programmer View

JCL PROCLIB
ssnmMSTR - DB2 Master Facility
ssnmDBM1 - DB2 Data Base Manager Facility
ssnmDIST - DB2 Distributed Data Facility
ssnmIRLM - DB2 Lock Manager Facility
ssnmWLMx - DB2 Work Load Manager Facility
where ssnm (subsystem name) is selected during installation process

//DB9GMSTR PROC
//IEFPROC EXEC PGM=DSNYASCP,PARM='ZPARM(DSNZPARM)‘
//STEPLIB DD DISP=SHR,DSN=[Link]
// DD DISP=SHR,DSN=[Link]
//BSDS1 DD DISP=SHR,DSN=DSN910.DB9G.BSDS01
//BSDS2 DD DISP=SHR,DSN=DSN910.DB9G.BSDS02

24 © 2017 IBM Corporation

 DB2 – DBA and Systems Programmer View

Boot Strap Data Set (BSDS)

The bootstrap data set (BSDS) is a VSAM key-sequenced

data set (KSDS) that contains information critical to DB2.

Specifically, the BSDS contains an inventory of all active and

archive log data sets known to DB2. DB2 uses this information
to track the active and archive log data sets. DB2 also uses this
information to locate log records to satisfy log read requests
during normal DB2 system activity and during restart and
recovery processing.

25 © 2017 IBM Corporation

 DB2 – DBA and Systems Programmer View

Active and Archive Logs -DB9G DISPLAY LOG

DSNJ370I -DB9G DSNJC00A LOG DISPLAY

Active log data sets record CURRENT COPY1 LOG = DSN910.DB9G.LOGCOPY1.DS01 IS 18% FULL
CURRENT COPY2 LOG = DSN910.DB9G.LOGCOPY2.DS01 IS 18% FULL
significant events and data H/W RBA = 0003C23D1819
changes. Active log data sets H/O RBA = 0003C1DFFFFF
are periodically offloaded to the FULL LOGS TO OFFLOAD = 0 OF 6
OFFLOAD TASK IS (AVAILABLE)
archive log. Therefore, the DSNJ371I -DB9G DB2 RESTARTED [Link] FEB 24, 2010
storage requirements for your RESTART RBA 0003C23CD000
CHECKPOINT FREQUENCY 50000 LOGRECORDS
active log data sets depend on LAST SYSTEM CHECKPOINT TAKEN [Link] FEB 24, 2010
how often DB2 data is changed DSN9022I -DB9G DSNJC001 '-DISPLAY LOG' NORMAL COMPLETION
at your site and how often DB2
offloads those changes to the
archive log.

26 © 2017 IBM Corporation

 DB2 – Buffer Pools

Buffer pools are areas of virtual storage in which DB2 temporarily stores
pages of table spaces or indexes. When an application program accesses
a row of a table, DB2 retrieves the page containing that row and places the
page in a buffer. If the needed data is already in a buffer, the application
program does not have to wait for it to be retrieved from disk, significantly
reducing the cost of retrieving the page.

Buffer pools require monitoring and tuning. The size of buffer pools is
critical to the performance characteristics of an application or group of
applications that access data in those buffer pools.

27 © 2017 IBM Corporation

 DB2 – Buffer Pools

80 virtual buffer pools -DB9G DISPLAY BUFFERPOOL(BP0)

DSNB401I -DB9G BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 10
DSNB402I -DB9G BUFFER POOL SIZE = 20000 BUFFERS AUTOSIZE = NO
50 4K page buffer pools
ALLOCATED = 20000 TO BE DELETED = 0
BP0–BP49 IN-USE/UPDATED = 0 BUFFERS ACTIVE = 120
10 8K page buffer pools
BP8K0–BP8K9
-DB9G DISPLAY BUFFERPOOL(BP1)
10 16K page buffer pools
DSNB401I -DB9G BUFFERPOOL NAME BP1, BUFFERPOOL ID 1, USE COUNT 0
BP16K0-BP16K9 DSNB402I -DB9G BUFFER POOL SIZE = 0 BUFFERS AUTOSIZE = NO
10 32K page buffer pools ALLOCATED = 0 TO BE DELETED = 0
BP32K0-BP32K9 IN-USE/UPDATED = 0 BUFFERS ACTIVE = 0

28 © 2017 IBM Corporation

 DB2 – DBA and Systems Programmer View

-DB9G DISPLAY DDF

DSNL080I -DB9G DSNLTDDF DISPLAY DDF REPORT FOLLOWS:

DSNL082I LOCATION LUNAME
DSNL083I DALLAS9 USASDV02.DB9GLU1
DSNL084I TCPPORT=5025 SECPORT=0 RESPORT=5026
DSNL085I IPADDR=::[Link]
DSNL086I SQL DOMAIN=[Link]

29 © 2017 IBM Corporation

 Starting DB2 for z/OS
-DB9G START DB2

S DB9GMSTR
$HASP373 DB9GMSTR STARTED
DSNY024I -DB9G DSNYASCP MSTR INITIALIZATION IS STARTING
DSNZ002I -DB9G DSNZINIT SUBSYSTEM DB9G SYSTEM PARAMETERS LOAD MODULE
NAME IS DB9GPARM

S DB9GIRLM
$HASP373 DB9GIRLM STARTED
DXR117I DJ9G001 INITIALIZATION COMPLETE
DSNY001I -DB9G SUBSYSTEM STARTING
DSNJ127I -DB9G SYSTEM TIMESTAMP FOR BSDS= 10.055 [Link].86
DSNJ001I -DB9G DSNJW007 CURRENT COPY 1 ACTIVE LOG
DATA SET IS DSNAME=DSN910.DB9G.LOGCOPY1.DS01,
STARTRBA=0003C1E00000,ENDRBA=0003C3FBFFFF
DSNJ099I -DB9G LOG RECORDING TO COMMENCE WITH
STARTRBA=0003C23CD000

S DB9GDBM1
$HASP373 DB9GDBM1 STARTED
DSNY024I -DB9G DSNYASCP DBM1 INITIALIZATION IS STARTING

S DB9GDIST
$HASP373 DB9GDIST STARTED
DSNY024I -DB9G DSNYASCP DIST INITIALIZATION IS STARTING
DSNR001I -DB9G RESTART INITIATED
30 © 2017 IBM Corporation
 DB2 for z/OS in EXECTION

SDSF STATUS DISPLAY ALL CLASSES

PREFIX=DB* DEST=(ALL) OWNER=*

NP JOBNAME JobID Owner Prty Queue
DB9GDIST STC01095 STCOPER 15 EXECUTION
DB9GDBM1 STC01094 STCOPER 15 EXECUTION
DB9GIRLM STC01093 STCOPER 15 EXECUTION
DB9GMSTR STC01092 STCOPER 15 EXECUTION

31 © 2017 IBM Corporation

 DB2 Professional Manuals and Documentation

32 © 2017 IBM Corporation

 Unit summary
Having completed this unit, you should be able to:
Describe DB2
List 4 types of SQL
List SQL statements
Understand DB2 Programming Language APIs
Understand DB2 Structures
Understand Active and Archive Logging
List 4 DB2 bufferpool sizes

33 © 2017 IBM Corporation