DB2 Design and Administration

About this class

The aim of this course is to give the delegate experience in the design process for DB2 tables and in physical database design, day-to-day administration, and to give ideas in defining a DB2 environment in an z/OS (OS/390) environment. A review of the normalisation process is also covered.

Who will the lesson benefit?

Database Administrators, Application Developers and Database Designers.

What delegates will learn

On successful completion, the delegate will be able to design DB2 tables from a data model, define a physical database, code JCL to run DB2 utilities and investigate performance issues.

By The End Of This Course You Will Have Learned

• Table design
• tablespace creation
• table creation
• index creation
• performance implications
• DB2 utilities.

What prerequisites are required

A basic knowledge of z/OS (OS/390) facilities and familiarity with using TSO/ISPF. A basic knowledge of SQL.

Contents of this class

• Review of normalisation
• Denormalisation
• Creating tablespaces and tables

• Freespace considerations
• performance aspects
• sizing the tablespace
• column ordering
• data types
• VARCHAR usage
• date and time
• null columns
• data compression
• data validation
• row lengths

• Creating views
• CHECK option; updates using views
• DB2 indexes

• Creating indexes
• sizing the indexspace
• use of multiple indexes
• clustering indexes
• composite indexes
• primary and foreign keys

• DB2 security and auditing
• DB2 utilities

Load utility, reorg utility, runstats utility, etc

• DB2 catalog tables
• Performance

The EXPLAIN command, the PLAN table, data and index structures, when to reorganise, access paths

• Indexing for performance

• Matching and non-matching index scans
• selecting which columns to index!