introduction to db2 2 copyright © 2005, infosys technologies ltd er/corp/crs/db01/003 version...

Introduction to DB2

2Copyright © 2005, Infosys Technologies Ltd

ER/CORP/CRS/DB01/003 Version No:2.0b

Course Objective

• To illustrate DB2 Architecture

• To explain DB2 Objects

– Database, Table space, Table, Index

• Datatypes DB2 support

• Integrity

– Foreign keys

• Security

– Views

– Grant and Revoke



Course Objective

• Embedded SQL

• Program Preparation

– Precompile, Bind

– DBRM's, Plans, and Packages

– Compile a Cobol-DB2 Program

• Concurrency and utilities



Course Plan

• Day 1: Overview of DB2

• Day 2: SPUFI, DCLGEN & Embedded SQL

• Day 3: Program Preparation and Execution

• Day 4: OLTP Issues

• Day 5: Project Evaluation and Final Test



Prerequisites

• MVS and TSO

• Programming in COBOL

• RDBMS Concepts

• Working knowledge of SQL

• OLTP Concepts



Session Plan

• To illustrate DB2 Architecture

• To explain DB2 Objects

– Database, Tablespace, Table, Index


• Integrity

– Foreign keys

• Security

– Views




RDBMS Review

• Database

• Database Management System

• Database Models

• Relational Database Management System



Introduction to DB2

• What is DB2?

– A subsystem of the MVS operating system

– An abbreviation for ‘IBM Database 2’

– Was announced in June 1983

– Supports SQL (Structured Query Language)



System Architecture

• Major Components of DB2

– SSAS (System Services Address Space)

• Thread creation, Program tracing, Logging

– DBAS (Database Services Address Space)

• Execution of SQLs, Database objects management, buffer management, Data read/write, etc.

– IRLM (IMS Resource Lock Manager)

• Locking



System Architecture

Major Components of DB2

– DDF (Distributed data facility component ) is optional

• Distributed Database functionality

– SPAS (Stored Procedure Address Space)

• For the execution of the stored procedures

• Each of these components runs in a separate address space and is made up of numerous sub-components.



Logging

Attachmentco-ordination

MVS Common Area

Free Space

SSAS

DSNMSTR

Locking

MVS Common Area

Free Space

IRLM

IRLMPROC

Distributed Requests

MVS Common Area

Free Space

DDF (Optional)

DSNDDF

Database functions

Buffering

MVS Common Area

Free Space

DBAS

DSNDBM1

DB 2 System Architecture



System services component- (Contd..)

• Handles DB2 startup and shutdown.

• Control and co-ordinate connections to other MVS subsystems such as

CICS, IMS and TSO.

• Manages the System log. The system log is a set of datasets that

used to record information about every transaction.

• Is responsible for establishing and maintaining all the threads for DB 2.



System services component - (Contd..)

• Has a sub-component called Instrumentation facility.

• The instrumentation facility gathers statistical information about the work

done by DB 2 at user specified intervals of time.

• This information is either

– Written to a System Management facility (SMF) or Generalized Trace

Facility (GTF) dataset or

– Passed to a performance monitor program provided some third party

vendor.



Database services component

• Is responsible for execution of SQL statements and management of Buffer pools.

• Comprises of 3 major sub-components Viz.

1.Relational Data System (RDS)

2.Data Manager (DM) and

3.Buffer Manager (BM)

• The RDS manages Stage 2 predicates , does Auth check , SQL statement checking , Sorting and Optimizer

• The DM is responsible for managing the data at the physical level. Does Stage 1 predicates , Indexable predicates and DML

• The DM invokes other system components for performing functions such as locking, logging, etc.,



Database services component - (Contd..)

• The BM manages all the Buffer pools required by DB 2 for database

operations. It keeps the frequently used pages in the buffer.

• The BM is responsible for opening and closing all the datasets used by

application data and work areas.

• The BM instructs the Data Facility Product (DFP) to do a physical I/O as and

when required.



STAGE 1 PREDICATES

COLUMN_NAME operator VALUE

COLUMN_NAME IS NULL

COLUMN_NAME BETWEEN val1 AND val2

COLUMN_NAME IN List

COLUMN_NAME LIKE pattern

COLUMN_NAME LIKE :Host-variable

A.COLUMN_NAME1 operator B.COLUMN_NAME2

COLUMN_NAME Operator (non correlated sub query)

COLUMN_NAME Operator (non column expression)



STAGE 1 / 2 PREDICATES

CORRELATED

SELECT EMP,LASTNAME

FROM EMP A

WHERE EXISTS

(SELECT 1 FROM DEPT B

WHERE A.DEPTNO=B.DEPTNO

AND DEPTDESC=‘EDUCATION’)

NON CORRELATED

SELECT EMP,LASTNAME

FROM EMP A

WHERE DEPTNO IN

(SELECT DEPTNO FROM DEPT

WHERE DEPTDESC=‘EDUCATION’)



FROM RDS to DM to BM and back-- > SQL

RelationalData

System

Data Manager

Buffer Manager

Optimized SQL

Read BufferOr Request Data

DATA

Apply Stage 1 Predicates

Apply Stage 2 Predicates & Sort Data

VSAM MediaManager

Results



Locking Services Component

• The locking services are provided by a component called Integrated

Resource Lock Manager (IRLM).

• The IRLM takes care of all the concurrency control issues.



Distributed Data Facility Component

• Provides distributed database functionality.

• Is an optional component.



Threads

• Are memory structures used by DB 2 to communicate with an application

program.

• Serves as links between DB 2 and application programs.

• Application programs send all requests to DB 2 through threads.

• DB 2 communicates the status of each SQL statement to the application

using threads through SQLCA.



Threads

DB2

Call Attach

pgm

CICS PGM

DB2Utility

TSO O/L

PGM

QMF Or

DB2I

TSOBatchpgm

IMS Batchpgm

IMS/DCPGM



DB2 Objects

DATABASE

TABLESPACE

TABLE

VIEW

SYNONYMINDEX



DB 2 Objects Hierarchy

STOGROUP

DATABASE

TABLESPACE

TABLE

COLUMN

INDEX

SYNONYM

ALIAS

VIEW



Storage Groups

• Are a collection of one or more (maximum of 133) DASD volumes of the

same type.

• Provides space for storing all the datasets that DB 2 uses.

• Are created by System administrator as shown below.

CREATE STOGROUP STOUDB6VOLUMES (DAVP7C, DAVP8C, DAVP9E)PASSWORD infosys;



Storage Groups - (Contd..)

• The number volumes in a storage group can be changed dynamically as

shown below.

ALTER STOGROUP STOUDB6ADD VOLUMES (DAVP9F, DAVP8C, DAVP9E)REMOVE VOLUMES (DAVP7C, DAVP8C)PASSWORD infosys;



Database

• Is a collection of one or more Tablespaces and Index spaces.

• Generally every application will have a unique database. This simplifies

administrative tasks and also improves application performance.

• One DB 2 system can manage up to 65,279 databases.



Creating a Database

• To create a database useCREATE DATABASE EMPDB;Maximum 8 characters name

• To remove a database useDROP DATABASE DBENR;

• To change the definition useALTER DATABASE EMPDB

CREATE DATABASE DBENRSTOGROUP STOUDB6BUFFERPOOL (BP0, BP32);

ALTER DATABASE DBENRROSHARE {OWNER, NONE}STOGROUP STOUDB6;



Database Descriptor (DBD)

• Is a DB 2 component that is created whenever a database is created.

• Stores control and descriptive information about every object in the

corresponding database.

• Whenever a database is in use, the associated DBD is buffered.

• To maintain database integrity the DBD is locked whenever an object is being

created, updated or deleted in the corresponding database.

• Instead of accessing DB2 catalog for object information , DBD which is housed

in DB2 directory is accessed which is more efficient.



Tablespace

• A Tablespace is one or more VSAM datasets

• Three types

– Segmented TS (default)

– Simple TS

– Partitioned TS (for large databases)



Creating a TS

• Created under an existing DB using

CREATE TABLESPACE EMPTS IN EMPDB

PRIQTY 10000SECQTY 1000PCTFREE 10FREEPAGE 63LOCKSIZE ANYBUFFERPOOL BP0SEGSIZE 64



More on TS

• To remove a tablespace useDROP TABLESPACE EMPTS

• To change the definition useALTER TABLESPACE EMPTS

ALTER TABLESPACE DBENR.ENRPRIQTY 200SECQTY 200ERASE YESLOCKSIZE ANYBUFFERPOOL BP1;



Simple TS

• Can house one or more tables

• There is no limit for number of tables

• Rows from multiple tables can be interleaved on a page



Segmented TS

• Can house one or more tables

• TS is divided into segments of 4 to 64 pages in increments of 4

• One segment contains data from exactly one table

• One table can occupy many segments

• Good for performance and this is DB2 default



Partitioned tablespace

• allows a table to be divided by rows into partitions

• Each partition can be placed on different storage devices



Table

• A tablespace within a database can have any number of tables

• Create table using

CREATE TABLE EMPLOYEE

(EMP_NO SMALLINT NOT NULL,

EMP_NAME CHAR(15),

EMP_ADDRESS VARCHAR(25) NOT NULL WITH DEFAULT,

PRIMARY KEY (EMP_NO));



More on Table

• To remove a table

DROP TABLE EMPLOYEE

• To change the definition use

ALTER TABLE EMPLOYEE

For example, to add a new field

ALTER TABLE EMPLOYEE

ADD EMP_SALARY INTEGER



Data types

Data Type

String Datetime Numeric

Character Graphic

FixedLength

VariableLength

Date Timestamp Time

Integer DecimalFloating

Point

Small Large Single Double



Data types- (Contd..)

• Integer

– 4 bytes (5 if nullable)

– PIC S9(9) COMP

• Smallint


– PIC S9(4) COMP




• Char(n)

– max. 254 bytes

– PIC X(n)

• Varchar(n)

– max. 4046 bytes

– A structure containing

PIC S9(4) COMP for length and

PIC X(n) for the data




• Time


– PIC X(8)

• Date


– PIC X(10)

• Timestamp


– PIC X(26)



Nulls

• DB2 adds an extra byte to all nullable columns

• This extra byte has the information whether the field contains NULL or not

• The NULL values do not participate while taking AVERAGE, SUM, etc.

• Need to have special care while inserting, updating, or retrieving nullable

fields in the host language program



WITH DEFAULT

• DB2 puts the default value of the data type in that field while inserting a record

• For character fields, spaces,

for numeric fields, zeros,

for date fields, current date

for time fields, current time

... and so on



Index

• Is a structure used for faster retrieval of data.

• Can be unique or non-unique.

• In DB 2, we need to explicitly create a unique index for the primary key.

• Is stored in B - Tree format.



Root PageLevel 0

1000 NL12000 NL23000 NL34000 NL4

Non-Leaf Page1Level 1

100 L1200 L2300 L3400 L4

Non-Leaf Page2Level 1

1100 L111200 L121300 L131400 L14

Leaf Page 1Level 2

10 DP120 DP230 DP340 DP4

Leaf Page 2 Level 2

101 DP10.1102 DP10.2 . . . . . . . . . . .151 DP20.1

Leaf Page 12Level 2

1101 DP50.11102 DP50.2 . . . . . . . . . . . . . . . . . . . . . . . .

101 Roopa 19102 Deeptha 20. . . . . . . . . . . . . . . . . .

151 Bhavana 20. . . . . . . . . . . . . . . . . .

Data Page 10 Data Page 201101 Vijay 221102 Harish 25. . . . . . . . . . . . . . . . . .

Data Page 50

Index



• Root Page

Only one root page is available per Index.It should exist at the highest

level of the hierarchy.It can be structured as Leaf pages or Non leaf

pages.

• Non Leaf pages

Non Leaf pages are intermediate-level Index pages in the b-tree

hierarchy. It need not exist.If they exist they contain the pointers to the

Leaf pages.

• Leaf Pages

Leaf pages contain pointers to the data rows of a table.Leaf page must

always exist. In a single page Index, the root page is a Leaf page

Index – (Contd…)



• Columns that are good for indexing

1. Primary key and Foreign key columns,

2. Column having unique values,

3. Columns that are frequently used in the WHERE clause and

4. Columns that frequently used in an ORDER BY, GROUP BY and

DISTINCT clauses.

• Columns that are not good for indexing

1. Frequently updated,

2. Longer than 30 characters and

3. Containing redundant values.

Index – (Contd…)



Creating Index 1 of 2

• DB2 creates Index spaces for every index (one index space for one

index)

• Is a page set used to store information about one index.

• Only one index per index space.

• Index space pages are 4k pages.

• Contains value from indexed columns and an RID to the corresponding

row.



Creating Index 2 of 2

• An index and its associated table must be in the same database

• Index pages can be locked by sub page increments (1/2, 1/4, 1/8 and

1/16). The default is 1/4.

• Often the response time will be slow due to Indexspace lock contention

rather than Tablespace lock contention. In such cases the response time

can be improved by increasing the value of the SUBPAGE parameter.

• Indexes are created using

CREATE [UNIQUE] INDEX EMPNOINDX

ON EMPLOYEE(EMP_NO ASC)



More on Index

• To remove an index

DROP INDEX EMPNOINDX

• To change the definition use

ALTER INDEX EMPNOINDX



• Data is physically ordered in the sequence of the index.

• Only one clustering index per table.

• Good for columns

– used in BETWEEN , >, <, LIKE

– used in GROUP BY, ORDER BY, DISTINCT

– for PRIMARY and FOREIGN KEYS

Clustered index (1 of 2)



Clustered index – (Contd…)

CREATE INDEX CLUSTER_EMP_INX ON ENR.EMP (EMPNO ASC) PRIQTY 36 CLUSTER (PART 1 VALUES('H99'), PART 2 VALUES('P99'), PART 3 VALUES('Z99'), PART 4 VALUES('999')) BUFFERPOOL BP1;



Catalog Tables

• When tables and other objects are created, DB2 records all these information

into a set of system tables called “catalog tables”

• SYSDATABASE, SYSTABLESPACE, SYSTABLES, SYSCOLUMNS,

SYSINDEXES, SYSVIEWS, etc. are examples

• If you have authority, you can query on these tables as any other table



Synonyms and Aliases

• Are used for creating alternate names for existing tables.

• Synonyms can refer only to local tables.

• Aliases can refer to both local as well as remote tables. In fact aliases were

designed for distributed environment to avoid references to the location

qualifier.

• When a table is dropped all the synonyms get dropped automatically where as

aliases on that table remains.



Synonyms and Aliases

• CREATE SYNONYM EMP FOR EMPDB.EMPLOYEE

• CREATE ALIAS EMP FOR BANGALORE.EMPDB.EMPLOYEE

• Use DROP to drop these objects



Integrity



Referential Integrity

• Suppose you want to make sure that all the employee numbers in

PROJ_ALLOCATION(PROJ_ID,EMP_NO) are valid employees and

valid projects (i.e., they belong to EMPLOYEE(EMP_NO,EMP_NAME)

and PROJECT(PROJ_ID,PROJ_NAME) master tables

• You use referential integrity support DB2 provides to enforce this

(through definition of foreign keys)



Referential Integrity - Keys

• Unique Key

A unique key is defined as a column (or set of columns) where no two values

same.

The columns of a unique key cannot contain null values.

A table can have multiple unique keys.

Unique keys are optional and can be defined in CREATE TABLE or ALTER

TABLE statements.




• Primary Key

A primary key is a unique key that is a part of the definition of the table.

A table cannot have more than one primary key, and the columns of a primary

key cannot contain null values.

Primary keys are optional and can be defined in CREATE TABLE or ALTER

TABLE statements.



• Foreign Key

A foreign key is a column (or set of columns) which is a primary key in another table.

• Unique Constraint

A unique constraint ensures that values of a key are unique within a table. Unique constraints are optional, and can be defined CREATE TABLE or

ALTER TABLE statements by specifying the PRIMARY KEY or UNIQUE clause.

For example, Unique constraint can be defined on the employee number column of a table to ensure that every employee has a unique number.




Creating a Foreign key

• CREATE TABLE PROJ_ALLOCATION

(PROJ_ID CHAR(8) NOT NULL,

EMP_NO SMALLINT NOT NULL,

PRIMARY KEY(PROJ_ID,EMP_NO),

FOREIGN KEY PROJ_FK(PROJ_ID) REFERENCES PROJECT(PROJ_ID),

FOREIGN KEY EMP_FK(EMP_NO) REFERENCES

EMPLOYEE(EMP_NO)

)



Delete Rules

• What happens if one employee is deleted from employee master table?

• DB2 supports 3 delete rules

– DELETE RESTRICT

– DELETE CASCADE

– DELETE SET NULL

• When defining foreign keys give these rules

FOREIGN KEY PROJ_FK(PROJ_ID) REFERENCES PROJECT(PROJ_ID)

DELETE CASCADE



Referential Integrity

Dept No EmpName1000 Hari1001 Girish1002 Sunitha

Dept No Dept Name1000 Sales1001 Marketing1002 Training1004 Finance

Foreign keyPrimary key

1005 Usha

Invalid Record

Employee Table

Department Table



Foreign Key Rules – Delete on Cascade

Dept No Dept name

1000 Training2000 Sales

When dept No : 1000 is deleted then..

Empname Dept NoTim 1000Jim 1000Tom 2000Mary 1000

The child records are also deleted automatically

Department Table

Employee Table



Foreign Key Rules – Delete on Restrict

Dept No Dept name



It gives an error and cannot delete as there are child records for the parent record.

Empname Dept NoTim 1000Jim 1000Tom 2000Mary 1000

Department Table

Employee Table



Foreign Key Rules – Delete on SET NULL

Dept No Dept name



The child records foreign key values are set to Null

Department Table

Employee Table

Empname Dept NoTimJimTom 2000Mary



Self Referencing Tables

• Consider this table

EMPLOYEE(EMP_NO, PL_EMP_NO)

• PL_EMP_NO is a foreign key referencing to the EMP_NO field of the same table

• DB2 does not allow creation of these foreign keys while creating tables

• We need to use ALTER TABLE to add the foreign key constraint later



Security



Security

• Security means the protection of the data in the data base against

unauthorized disclosure, alteration or destruction.



Views

• Unlike actual tables, views defined on a table are just definitions

• DB2 keeps all view definitions in SYSVIEWS catalog table

• All the views depend on one or more tables or views

• We can have views created using other views



Creating a View

CREATE VIEW RICH_EMP AS

SELECT EMP_NO, EMP_NAME

FROM EMPLOYEE

WHERE SALARY > 25000

• Now we can use

SELECT EMP_NO, EMP_NAME

FROM RICH_EMP

in our program

• Views provide

– security, independence from the base tables



Updating using a View

• DB2 allows updation of only “simple” views

• You can issue update statement as if you are updating an actual table

• DB2 does not allow you to update

– views using joins

– views using DISTINCT and aggregates

– views using GROUP BY



More on View

• Like any other object, use

DROP VIEW RICH_EMP to delete a view

• When a table or a view is dropped all dependent views are automatically

dropped

• DB2 does not allow you to create views with ORDER BY, FOR UPDATE OF,

or UNION



GRANT

• Use GRANT keyword to grant permissions on database objects

GRANT SELECT

ON EMPLOYEE TO PUBLIC;

GRANT UPDATE (EMP_ADDRESS)

ON EMPLOYEE TO SMITH;

GRANT INSERT, DELETE

ON EMPLOYEE TO TRAINEES;

• A table creator has implicit authority to

– alter, drop, create a view/index, select/insert/update/delete



REVOKE

• Use REVOKE keyword to take away permissions on database objects

REVOKE SELECT

ON EMPLOYEE FROM PUBLIC;

REVOKE UPDATE (EMP_ADDRESS)

ON EMPLOYEE FROM SMITH;

REVOKE INSERT, DELETE

ON EMPLOYEE FROM TRAINEES;

REVOKE ALL

ON EMPLOYEE FROM PUBLIC;



More on Permissions

• You can GRANT a privilege to someone with GRANT option

GRANT ALL ON EMPLOYEE TO PUBLIC

WITH GRANT OPTION;

• Suppose Jones grants select permission on view RICH_EMP to Smith with

grant option; Smith in turn grants select permission to Adams on the same

view; What happens if Jones revokes permission from Smith?



BUNDLED PRIVILEGES

• SYSDM

• SYSCTRL

• DBADM

• DBCTRL

• DBMAINT

• SYSOPR



Summary

• DB2 Architecture

• DB2 Objects

– Database, Tablespace, Table, Index


• Integrity

– Foreign keys

• Security

– Views




Thank You!

introduction to db2 2 copyright © 2005, infosys technologies ltd er/corp/crs/db01/003 version...

Documents

db2 objectsdatabase

db2 architectureto

overview of db2 day

coboldb2 programconcurrency

handles db2 startup

database objects management

ibm database

system log