DB2 UDB for z/OS and OS/390 V7 Database Administration Workshop

course content details

	Introduction

	explain the fundamental difference between static and dynamic SQL programs
	define the major responsibility of any application program
	define the major responsibilities of the DB2 system
	describe the basic principles of DB2 recovery
	discuss the basic roles people play in a DB2 shop

	Setting up a DB2 database

	describe the DB2 objects that make up a DB2 database
	select the most appropriate parameters for these DB2 database objects so that they are implemented with the most appropriate attributes
	create storage groups, data bases, table spaces, tables, views, indexes, synonyms, and aliases
	alter the attributes of DB2 database objects as requirements change over time
	describe how data is stored in a DB2 database

	Exercise 1 - creating databases and table spaces

	create a database
	create a simple table space
	create a segmented table space
	create a partitioned table space

	Exercise 2 - creating tables

	create tables and define table check constraints
	become familiar with the type of information held in the DB2 catalog for tables

	Exercise 3 - creating and using views

	create views
	explain how SELECT, INSERT, UPDATE, and DELETE statements are processed when executed against a view

	Exercise 4 - creating indexes

	create different types of indexes
	become familiar with the type of information held in the DB2 catalog for indexes

	Referential Integrity (RI)

	describe the concepts of PRIMARY KEY, UNIQUE KEY, and FOREIGN KEY
	implement these keys in DB2
	describe the concepts of REFERENTIAL CONSTRAINT and RI
	implement REFERENTIAL CONSTRAINTS on new or existing tables with the appropriate DELETE rules
	drop REFERENTIAL CONSTRAINTS and their enforcing indexes
	query the appropriate catalog tables for RI details
	describe the relationships that require special handling

	Exercise 5 - creating referential structures

	define primary keys
	define foreign keys
	define referential constraints
	become familiar with the type of information held in the DB2 catalog for a referential structure

	Getting data into and out of DB2

	describe the importance of free space and implement an appropriate quantity in DB2 table spaces and index spaces
	describe how free space is used by DB2 during SQL INSERT, DELETE, and UPDATE processing and how free space is managed by DB2 during LOAD, REORG, RECOVER INDEX, and REBUILD INDEX
	describe how DB2 utilities may by executed, monitored, terminated, and restarted
	run the DB2 LOAD utility and make full use of its many features
	run the CHECK DATA utility to check DB2 table space data for any violations of RI constraints or table check constraints
	run the DB2 UNLOAD utility and make full use of its many features in a DB2 UDB for z/OS and OS/390 Version 7 system, or later

	Exercise 6 - running LOAD and CHECK DATA

run the CHECK DATA utility and the LOAD utility

	Keeping your DB2 data in good shape

	describe and interpret the statistics collected by RUNSTATS
	run RUNSTATS with an appropriately chosen set of options
	describe why it is necessary to periodically REORG your table spaces and indexes
	run REORG with an appropriately chosen set of options

	Exercise 7 - running RUNSTATS and REORG

run the RUNSTATS utility and the REORG utility

	Application data recovery basics

make DB2 user data available again following media failure

	Exercise 8 - running COPY and RECOVER

run the COPY utility and the RECOVER utility

	ESA data compression

	describe the data compression feature
	specify compression for a table space
	use the DSN1Comp utility to determine if its specification will help conserve Direct Access Storage Device (DASD) space

	Program preparation / bind

describe the main steps needed to prepare a program that accesses DB2 with embedded static SQL

	Security

	describe DB2 global security
	describe the various DB2 authids and how they are established
	explain how DB2 security controls access to data
	describe and implement DB2 privileges and authorities appropriately
	describe how ownership is established in DB2
	describe DB2 authorization checking
	describe and implement security for plans and packages
	describe the advantages and considerations for using the Resource Access Control Facility (RACF) / DB2 external security module

	Exercise 9 - security

become familiar with how security checking is implemented within DB2

	Access paths

	describe the two major access paths, table scan and matching index scan
	use EXPLAIN to determine the access path chosen by the optimizer

	Exercise 10 - obtaining access path information

	create a plan table
	obtain access path information using EXPLAIN
	query the information placed in the plan table

	Serialization

	describe the purpose of S- and X-locks
	describe what an isolation level is
	describe the main lock sizes

	Exercise 11 - locking

become familiar with how locking is implemented within DB2